public bool Run(PostingEngineEnvironment env)
{
var sqlHelper = new SqlHelper(env.ConnectionString);
var sqlParams = new SqlParameter[]
{
new SqlParameter("@year", env.ValueDate.Year - 1),
};
var journals = new List <Journal>();
var dataTable = sqlHelper.GetDataTables("EndOfYear", CommandType.StoredProcedure, sqlParams);
var valueDate = env.ValueDate;
var prevValueDate = env.PreviousValueDate;
foreach (DataRow row in dataTable[0].Rows)
{
int offset = 0;
var yearEndResult = new
{
AccountCategory = Convert.ToString(row[offset++]),
AccountType = Convert.ToString(row[offset++]),
Fund = Convert.ToString(row[offset++]),
Debit = Convert.ToDecimal(row[offset++]),
Credit = Convert.ToDecimal(row[offset++]),
};
var credit = Convert.ToDouble(yearEndResult.Credit);
var debit = Convert.ToDouble(yearEndResult.Debit);
var balance = Convert.ToDouble(yearEndResult.Debit - yearEndResult.Credit);
var netIncomePrev = $"Retained Earnings {(valueDate.Year - 1)}";
var netIncomeCurrent = $"Net Income Current Year";
if (AccountType.Find(AccountCategory.AC_EQUITY, netIncomePrev, false) == null)
{
// Need to create the Account Type
var createdAccountType = AccountType.FindOrCreate(AccountCategory.AC_EQUITY, netIncomePrev);
new AccountUtils().Save(env, createdAccountType);
}
if (AccountType.Find(AccountCategory.AC_EQUITY, netIncomeCurrent, false) == null)
{
// Need to create the Account Type
var createdAccountType = AccountType.FindOrCreate(AccountCategory.AC_EQUITY, netIncomeCurrent);
new AccountUtils().Save(env, createdAccountType);
}
var accountName = $"[{yearEndResult.AccountCategory}]{yearEndResult.AccountType}";
journals.AddRange(ReverseCredit(env, valueDate, yearEndResult, yearEndResult.AccountType, netIncomePrev, accountName, credit));
journals.AddRange(ReverseDebit(env, valueDate, yearEndResult, yearEndResult.AccountType, netIncomePrev, accountName, debit));
}
if (journals.Count() > 0)
{
env.CollectData(journals);
journals.Clear();
}
return(true);
}
public bool Run(PostingEngineEnvironment env)
{
var dates = "select minDate = min([when]), maxDate = max([when]) from vwJournal";
env.CallBack?.Invoke("ExpencesAndRevenues Calculation Started");
var table = new DataTable();
// read the table structure from the database
using (var adapter = new SqlDataAdapter(dates, new SqlConnection(env.ConnectionString)))
{
adapter.Fill(table);
adapter.Dispose();
};
var valueDate = Convert.ToDateTime(table.Rows[0]["minDate"]);
var endDate = Convert.ToDateTime(table.Rows[0]["maxDate"]);
using (var cc = new SqlConnection(env.ConnectionString))
{
cc.Open();
SetupEnvironment.Setup(cc);
cc.Close();
}
var journals = new List <Journal>();
var connection = new SqlConnection(env.ConnectionString);
connection.Open();
var transaction = connection.BeginTransaction();
var sqlHelper = new SqlHelper(env.ConnectionString);
var rowsCompleted = 1;
var numberOfDays = (endDate - valueDate).Days;
while (valueDate <= endDate)
{
if (!valueDate.IsBusinessDate())
{
valueDate = valueDate.AddDays(1);
rowsCompleted++;
continue;
}
try
{
var sqlParams = new SqlParameter[]
{
new SqlParameter("@startDate", new DateTime(valueDate.Year, 1, 1)),
new SqlParameter("@businessDate", valueDate),
new SqlParameter("@prevbusinessDate", valueDate.PrevBusinessDate()),
};
var dataTable = sqlHelper.GetDataTables("DayOverDayIncome", CommandType.StoredProcedure, sqlParams.ToArray());
foreach (DataRow row in dataTable[0].Rows)
{
int offset = 0;
var expencesAndRevenues = new
{
Fund = Convert.ToString(row[offset++]),
Credit = Convert.ToDecimal(row[offset++]),
Debit = Convert.ToDecimal(row[offset++]),
Balance = Convert.ToDecimal(row[offset++]),
};
var accountType = $"Net Income Current Year";
if (AccountType.Find(AccountCategory.AC_EQUITY, accountType, false) == null)
{
// Need to create the Account Type
var createdAccountType = AccountType.FindOrCreate(AccountCategory.AC_EQUITY, accountType);
new AccountUtils().Save(env, createdAccountType);
}
var balance = Convert.ToDouble(expencesAndRevenues.Balance);
var account = new AccountUtils().GetAccount(env, accountType, new string[] { env.BaseCurrency }.ToList());
var debit = new Journal(account, "expences-revenues", valueDate)
{
Source = "calculated-data",
Fund = expencesAndRevenues.Fund,
Quantity = balance,
FxCurrency = env.BaseCurrency,
Symbol = env.BaseCurrency,
SecurityId = -1,
FxRate = 0,
StartPrice = 0,
EndPrice = 0,
// If this number is +ve then its actually a Debit and this is going into a Equity account which needs to be -ve and not +ve
Value = balance * -1,
CreditDebit = env.DebitOrCredit(account, balance * -1),
};
journals.AddRange(new List <Journal>(new[] { debit }));
}
}
catch (Exception ex)
{
env.CallBack?.Invoke($"Exception on {valueDate.ToString("MM-dd-yyyy")}, {ex.Message}");
}
env.CallBack?.Invoke($"Completed ExpencesAndRevenues for {valueDate.ToString("MM-dd-yyyy")}", numberOfDays, rowsCompleted++);
valueDate = valueDate.AddDays(1);
}
if (journals.Count() > 0)
{
env.CollectData(journals);
journals.Clear();
}
transaction.Commit();
connection.Close();
return(true);
}