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 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 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);
}