public static DataTable GetVendorStatement(string VendorCode, DateTime?DateFrom, DateTime?DateTo, out decimal VendorDebit, out decimal VendorCredit) { DataTable dt = new DataTable(); dt.Columns.Add("v1", typeof(string)); dt.Columns.Add("v2", typeof(string)); dt.Columns.Add("v3", typeof(string)); dt.Columns.Add("v4", typeof(string)); PostedTransactionsManager manager = new PostedTransactionsManager(); IQueryable <PostedTransactions> PostedTransactionsList = manager.GetAll(); if (!string.IsNullOrEmpty(VendorCode)) { PostedTransactionsList = manager.GetAll().Where(p => (p.accCode.Contains("-2-" + VendorCode) || p.accCode == "2-" + VendorCode)); } if (DateFrom != null && DateTo != null) { PostedTransactionsList = PostedTransactionsList.Where(p => (p.PostedDate.Value >= DateFrom.Value && p.PostedDate.Value <= DateTo.Value)); } foreach (PostedTransactions item in PostedTransactionsList) { dt.Rows.Add(item.PostedDate.Value.ToShortDateString(), item.PostedTime, item.DebitAmount, item.CreditAmount); } VendorDebit = PostedTransactionsList.Sum(p => p.DebitAmount) == null ? 0 : (decimal)PostedTransactionsList.Sum(p => p.DebitAmount); VendorCredit = PostedTransactionsList.Sum(p => p.CreditAmount) == null ? 0 : (decimal)PostedTransactionsList.Sum(p => p.CreditAmount); return(dt); }
public static DataTable GetAccountStatement(string AccountCode, DateTime?DateFrom, DateTime?DateTo, out decimal AccountDebit, out decimal AccountCredit) { DataTable dt = new DataTable(); dt.Columns.Add("v1", typeof(string)); dt.Columns.Add("v2", typeof(string)); dt.Columns.Add("v3", typeof(string)); dt.Columns.Add("v4", typeof(string)); PostedTransactionsManager manager = new PostedTransactionsManager(); List <PostedTransactions> PostedTransactionsList = new List <PostedTransactions>(); if (!string.IsNullOrEmpty(AccountCode)) { foreach (PostedTransactions item in manager.GetAll()) { string accCode = item.accCode.Split('-')[0]; if (accCode == AccountCode) { PostedTransactionsList.Add(item); } } } if (DateFrom != null && DateTo != null) { PostedTransactionsList = PostedTransactionsList.Where(p => (p.PostedDate.Value >= DateFrom.Value && p.PostedDate.Value <= DateTo.Value)).ToList(); } foreach (PostedTransactions item in PostedTransactionsList) { dt.Rows.Add(item.PostedDate.Value.ToShortDateString(), item.PostedTime, item.DebitAmount, item.CreditAmount); } AccountDebit = PostedTransactionsList.Sum(p => p.DebitAmount) == null ? 0 : (decimal)PostedTransactionsList.Sum(p => p.DebitAmount); AccountCredit = PostedTransactionsList.Sum(p => p.CreditAmount) == null ? 0 : (decimal)PostedTransactionsList.Sum(p => p.CreditAmount); return(dt); }
public DataTable DisplayTrialBalance(DateTime?DateFrom, DateTime?DateTo) { DataTable dt = new DataTable(); dt.Columns.Add("v1", typeof(string)); //accCode dt.Columns.Add("v2", typeof(string)); //accName dt.Columns.Add("v3", typeof(string)); //Begining Debit Balance dt.Columns.Add("v4", typeof(string)); //Begining credit Balance dt.Columns.Add("v5", typeof(string)); //Debit Balance dt.Columns.Add("v6", typeof(string)); //Credit Balance dt.Columns.Add("v7", typeof(string)); //Closing Debit Balance dt.Columns.Add("v8", typeof(string)); //Closing credit Balance List <PostedTransactions> listBalance = new List <PostedTransactions>(); List <PostedTransactions> listBegin = new List <PostedTransactions>(); PostedTransactionsManager posted = new PostedTransactionsManager(); IQueryable <PostedTransactions> BalanceTransactions = DateFrom == null?posted.GetAll() : posted.GetAll().Where(p => p.PostedDate >= DateFrom && p.PostedDate <= DateTo); IQueryable <PostedTransactions> BeginingTransactions = DateFrom == null?posted.GetAll() : posted.GetAll().Where(p => p.PostedDate < DateFrom); foreach (Accounts acc in new AccountsManager().GetAll()) { listBalance.Clear(); listBegin.Clear(); foreach (PostedTransactions item in BalanceTransactions) { if (acc.accCode == item.accCode.Split('-')[0]) { listBalance.Add(item); } } if (DateFrom != null) { foreach (PostedTransactions item in BeginingTransactions) { if (acc.accCode == item.accCode.Split('-')[0]) { listBegin.Add(item); } } } else { if (BeginingTransactions.FirstOrDefault(b => b.TransactionHeader.JournalCode == 5) != null) { listBegin.Add(BeginingTransactions.FirstOrDefault(b => b.TransactionHeader.JournalCode == 5)); } } if (listBalance.Count > 0) { decimal AccountDebit = listBalance.Sum(p => p.DebitAmount) == null ? 0 : (decimal)listBalance.Sum(p => p.DebitAmount); decimal AccountCredit = listBalance.Sum(p => p.CreditAmount) == null ? 0 : (decimal)listBalance.Sum(p => p.CreditAmount); decimal BeginAccountDebit = listBegin.Count > 0 ? listBegin.Sum(p => p.DebitAmount) == null ? 0 : (decimal)listBegin.Sum(p => p.DebitAmount) : 0; decimal BeginAccountCredit = listBegin.Count > 0 ? listBegin.Sum(p => p.CreditAmount) == null ? 0 : (decimal)listBegin.Sum(p => p.CreditAmount) : 0; dt.Rows.Add(acc.accCode, acc.accName, BeginAccountDebit > BeginAccountCredit ? (BeginAccountDebit - BeginAccountCredit).ToString() : "-", BeginAccountCredit > BeginAccountDebit ? (BeginAccountCredit - BeginAccountDebit).ToString() : "-", AccountDebit == 0 ? "-" : AccountDebit.ToString(), AccountCredit == 0 ? "-" : AccountCredit.ToString(), AccountDebit > AccountCredit ? (AccountDebit - AccountCredit).ToString() : "-", AccountCredit > AccountDebit ? (AccountCredit - AccountDebit).ToString() : "-"); } } return(dt); }