public static AccountVerifierGeneralLedgerViewModel Refresh(Account account, DateTime asOf)
{
string storedProcedure = "sp_account_verifier_general_ledger";
var sqlparams = new List <SqlParameter>();
DateTime forwardedDate = GeneralLedgerBalance.MaxDocumentDate();
if (forwardedDate != Convert.ToDateTime(string.Format("12/31/{0}", asOf.Year - 1)))
{
// present_database VARCHAR(40), previous_database VARCHAR(40), cutoff_date DATE, account_code varchar(10), as_of date
storedProcedure = "sp_account_verifier_general_ledger_opening_year";
sqlparams.Add(new SqlParameter("present_database", DatabaseController.GetDatabaseByYear(asOf.Year)));
sqlparams.Add(new SqlParameter("previous_database",
DatabaseController.GetDatabaseByYear(forwardedDate.Year)));
sqlparams.Add(new SqlParameter("account_code", account.AccountCode));
sqlparams.Add(new SqlParameter("start_date", forwardedDate.AddDays(1)));
sqlparams.Add(new SqlParameter("end_date", asOf));
}
else
{
sqlparams.Add(new SqlParameter("ts_account_code", account.AccountCode));
sqlparams.Add(new SqlParameter("td_as_of", asOf));
}
DataTable dataTable = DatabaseController.ExecuteStoredProcedure(storedProcedure, sqlparams);
var collection = new GeneralLedgerAccountCollection();
decimal endBalance = 0m;
decimal totalDebit = 0m;
decimal totalCredit = 0m;
foreach (DataRow dataRow in dataTable.Rows)
{
var item = new GeneralLedgerAccount();
item.AccountCode = account.AccountCode;
item.AccountTitle = account.AccountTitle;
item.VoucherDate = DataConverter.ToDateTime(dataRow["voucher_date"]);
item.VoucherType = DataConverter.ToString(dataRow["voucher_type"]);
item.VoucherNumber = DataConverter.ToInteger(dataRow["voucher_number"]);
item.Debit = DataConverter.ToDecimal(dataRow["debit"]);
item.Credit = DataConverter.ToDecimal(dataRow["credit"]);
item.Balance = DataConverter.ToDecimal(dataRow["balance"]);
item.CheckNumber = DataConverter.ToString(dataRow["check_number"]);
endBalance = item.Balance;
totalCredit += item.Credit;
totalDebit += item.Debit;
collection.Add(item);
}
var viewModel = new AccountVerifierGeneralLedgerViewModel();
viewModel.Collection = collection;
viewModel.Account = account;
viewModel.EndBalance = endBalance;
viewModel.TotalCredit = totalCredit;
viewModel.TotalDebit = totalDebit;
return(viewModel);
}
public void RefreshCollection()
{
var collection = new ObservableCollection <GeneralLedgerBalance>();
var query = "SELECT * FROM `glbal` ORDER BY ACC_CODE";
var dataTable = Database.DatabaseController.ExecuteSelectQuery(query);
foreach (System.Data.DataRow dataRow in dataTable.Rows)
{
var item = new GeneralLedgerBalance();
item.SetPropertiesFromDataRow(dataRow);
collection.Add(item);
}
Collection = collection;
}
public GeneralLedgerBalanceView(int id)
: this()
{
DateTime loginDate = MainController.LoggedUser.TransactionDate;
_currentItem = new GeneralLedgerBalance
{
DocumentDate = new DateTime(loginDate.Year - 1, 12, 31),
DocumentNo = 0,
DocumentType = VoucherTypes.BG.ToString(),
};
if (id > 0)
{
_currentItem.Find(id); // edit mode
btnUpdate.Content = "Update";
}
else
{
btnUpdate.Content = "Create";
}
DataContext = _currentItem;
}
