public List <CashBook> GetMontlyCashBook(AprajitaRetailsContext db, DateTime date)
{
List <CashBook> book = new List <CashBook> ();
DateTime oDate = new DateTime(date.Year, date.Month, 1);
decimal OpnBal = 0;
decimal ColBal = 0;
CashWork worker = new CashWork();
try
{
ColBal = worker.GetClosingBalance(db, oDate.AddDays(-1));
OpnBal = (decimal?)db.CashInHands.Where(c => (c.CIHDate) == (oDate)).FirstOrDefault().OpenningBalance ?? 0;
if (OpnBal != ColBal)
{
OpnBal = ColBal;
}
}
catch (Exception)
{
OpnBal = ColBal;
}
//income
var dSale = db.DailySales.Where(c => c.PayMode == PayModes.Cash && (c.SaleDate).Month == (date).Month).OrderBy(c => c.SaleDate); //ok
var dRec = db.Receipts.Where(c => c.PayMode == PaymentModes.Cash && (c.RecieptDate).Month == (date).Month).OrderBy(c => c.RecieptDate); //ok
var dCashRec = db.CashReceipts.Where(c => (c.InwardDate).Month == (date).Month).OrderBy(c => c.InwardDate); //ok
var dSRec = db.StaffAdvanceReceipts.Include(e => e.Employee).Where(c => c.PayMode == PayModes.Cash && (c.ReceiptDate).Month == (date).Month).OrderBy(c => c.ReceiptDate); //ok
var dWit = db.BankWithdrawals.Include(C => C.Account).Where(c => (c.DepoDate).Month == (date).Month).OrderBy(c => c.DepoDate);
var dTalRec = db.TailoringStaffAdvanceReceipts.Include(c => c.Employee).Where(c => c.PayMode == PayModes.Cash && (c.ReceiptDate).Month == (date).Month).OrderBy(c => c.ReceiptDate); //ok
foreach (var item in dTalRec)
{
CashBook b = new CashBook()
{
EDate = item.ReceiptDate, CashIn = item.Amount, Particulars = item.Employee.StaffName, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
foreach (var item in dSale)
{
CashBook b = new CashBook()
{
EDate = item.SaleDate, CashIn = item.Amount, Particulars = item.InvNo, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
foreach (var item in dRec)
{
CashBook b = new CashBook()
{
EDate = item.RecieptDate, CashIn = item.Amount, Particulars = item.ReceiptFrom, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
foreach (var item in dCashRec)
{
CashBook b = new CashBook()
{
EDate = item.InwardDate, CashIn = item.Amount, Particulars = item.ReceiptFrom, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
foreach (var item in dSRec)
{
CashBook b = new CashBook()
{
EDate = item.ReceiptDate, CashIn = item.Amount, Particulars = item.Employee.StaffName, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
foreach (var item in dWit)
{
CashBook b = new CashBook()
{
EDate = item.DepoDate, CashIn = item.Amount, Particulars = "Bank=> " + item.Account.Account, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
//Expenses
var eCPay = db.CashPayments.Where(c => (c.PaymentDate).Month == (date).Month).OrderBy(c => c.PaymentDate); //ok
var ePay = db.Payments.Where(c => c.PayMode == PaymentModes.Cash && (c.PayDate).Month == (date).Month).OrderBy(c => c.PayDate);
var eStPay = db.StaffAdvancePayments.Include(e => e.Employee).Where(c => c.PayMode == PayModes.Cash && (c.PaymentDate).Month == (date).Month).OrderBy(c => c.PaymentDate);
var eSal = db.SalaryPayments.Include(e => e.Employee).Where(c => c.PayMode == PayModes.Cash && (c.PaymentDate).Month == (date).Month).OrderBy(c => c.PaymentDate);
var eexp = db.Expenses.Where(c => c.PayMode == PaymentModes.Cash && (c.ExpDate).Month == (date).Month).OrderBy(c => c.ExpDate);
var eDepo = db.BankDeposits.Include(C => C.Account).Where(c => (c.DepoDate).Month == (date).Month).OrderBy(c => c.DepoDate);
var eDue = db.DuesLists.Include(e => e.DailySale).Where(c => c.IsRecovered == false && (c.DailySale.SaleDate).Month == (date).Month).OrderBy(c => c.DailySale.SaleDate);
var eCashEx = db.PettyCashExpenses.Where(c => (c.ExpDate).Month == (date).Month).OrderBy(c => c.ExpDate);
var eTalSal = db.TailoringSalaryPayments.Include(e => e.Employee).Where(c => c.PayMode == PayModes.Cash && (c.PaymentDate).Month == (date).Month).OrderBy(c => c.PaymentDate);
var eTalStPay = db.TailoringStaffAdvancePayments.Include(e => e.Employee).Where(c => c.PayMode == PayModes.Cash && (c.PaymentDate).Month == (date).Month).OrderBy(c => c.PaymentDate);
foreach (var item in eTalStPay)
{
CashBook b = new CashBook()
{
EDate = item.PaymentDate, CashIn = 0, Particulars = item.Employee.StaffName, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eTalSal)
{
CashBook b = new CashBook()
{
EDate = item.PaymentDate, CashIn = 0, Particulars = item.Employee.StaffName, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eexp)
{
CashBook b = new CashBook()
{
EDate = item.ExpDate, CashIn = 0, Particulars = item.Particulars, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eDepo)
{
CashBook b = new CashBook()
{
EDate = item.DepoDate, CashIn = 0, Particulars = "Bank Depo=> " + item.Account.Account, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eCashEx)
{
CashBook b = new CashBook()
{
EDate = item.ExpDate, CashIn = 0, Particulars = item.Particulars, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eDue)
{
CashBook b = new CashBook()
{
EDate = item.DailySale.SaleDate, CashIn = 0, Particulars = "Dues=>" + item.DailySale.InvNo, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eCPay)
{
CashBook b = new CashBook()
{
EDate = item.PaymentDate, CashIn = 0, Particulars = item.PaidTo, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in ePay)
{
CashBook b = new CashBook()
{
EDate = item.PayDate, CashIn = 0, Particulars = item.PaymentPartry, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eStPay)
{
CashBook b = new CashBook()
{
EDate = item.PaymentDate, CashIn = 0, Particulars = item.Employee.StaffName, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eSal)
{
CashBook b = new CashBook()
{
EDate = item.PaymentDate, CashIn = 0, Particulars = item.Employee.StaffName, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
return(CorrectBalCashBook(book, OpnBal));
}
//StoreBased Action Reviewed
public List <CashBook> GetMontlyCashBook(eStoreDbContext db, DateTime date, int Store)
{
List <CashBook> book = new List <CashBook>();
DateTime oDate = new DateTime(date.Year, date.Month, 1);
decimal OpnBal = 0;
decimal ColBal = 0;
CashWork worker = new CashWork();
try
{
ColBal = worker.GetClosingBalance(db, oDate.AddDays(-1), Store);
OpnBal = (decimal?)(db.CashInHands.Where(c => c.CIHDate == oDate && c.StoreId == Store).Select(c => c.OpenningBalance).FirstOrDefault()) ?? 0;
if (OpnBal != ColBal)
{
OpnBal = ColBal;
}
}
catch (Exception)
{
OpnBal = ColBal;
}
//income
var dSale = db.DailySales.Where(c => c.PayMode == PayMode.Cash && c.SaleDate.Month == date.Month && c.SaleDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.SaleDate);
var dRec = db.Receipts.Where(c => c.PayMode == PaymentMode.Cash && c.OnDate.Month == date.Month && c.OnDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.OnDate);
var dCashRec = db.CashReceipts.Where(c => c.InwardDate.Month == date.Month && c.InwardDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.InwardDate);
var dSRec = db.StaffAdvanceReceipts.Include(e => e.Employee).Where(c => c.PayMode == PayMode.Cash && c.StoreId == Store && c.ReceiptDate.Year == date.Year && c.ReceiptDate.Month == date.Month).OrderBy(c => c.ReceiptDate);
var dWit = db.BankWithdrawals.Include(C => C.Account).Where(c => c.OnDate.Month == date.Month && c.OnDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.OnDate);
foreach (var item in dSale)
{
CashBook b = new CashBook()
{
EDate = item.SaleDate, CashIn = item.Amount, Particulars = item.InvNo, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
foreach (var item in dRec)
{
CashBook b = new CashBook()
{
EDate = item.OnDate, CashIn = item.Amount, Particulars = item.PartyName, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
foreach (var item in dCashRec)
{
CashBook b = new CashBook()
{
EDate = item.InwardDate, CashIn = item.Amount, Particulars = item.ReceiptFrom, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
foreach (var item in dSRec)
{
CashBook b = new CashBook()
{
EDate = item.ReceiptDate, CashIn = item.Amount, Particulars = item.Employee.StaffName, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
foreach (var item in dWit)
{
CashBook b = new CashBook()
{
EDate = item.OnDate, CashIn = item.Amount, Particulars = "Bank=> " + item.Account.Account, CashOut = 0, CashBalance = 0
};
book.Add(b);
}
//Expenses
var eCPay = db.CashPayments.Where(c => c.PaymentDate.Month == date.Month && c.PaymentDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.PaymentDate);//ok
var ePay = db.Payments.Where(c => c.PayMode == PaymentMode.Cash && c.OnDate.Month == date.Month && c.OnDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.OnDate);
// var eStPay = db.StaffAdvancePayments.Include(e => e.Employee).Where(c => c.PayMode == PayMode.Cash && c.PaymentDate.Month == date.Month && c.PaymentDate.Year == date.Year).OrderBy(c => c.PaymentDate);
var eSal = db.SalaryPayments.Include(e => e.Employee).Where(c => c.PayMode == PayMode.Cash && c.PaymentDate.Month == date.Month && c.PaymentDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.PaymentDate);
var eexp = db.Expenses.Where(c => c.PayMode == PaymentMode.Cash && c.OnDate.Month == date.Month && c.OnDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.OnDate);
var eDepo = db.BankDeposits.Include(C => C.Account).Where(c => c.OnDate.Month == date.Month && c.OnDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.OnDate);
var eDue = db.DuesLists.Include(e => e.DailySale).Where(c => c.IsRecovered == false && c.DailySale.SaleDate.Month == date.Month && c.DailySale.SaleDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.DailySale.SaleDate);
var eCashEx = db.Expenses.Where(c => c.OnDate.Month == date.Month && c.OnDate.Year == date.Year && c.StoreId == Store).OrderBy(c => c.OnDate);
foreach (var item in eexp)
{
CashBook b = new CashBook()
{
EDate = item.OnDate, CashIn = 0, Particulars = "Exp_ID# " + item.ExpenseId + " Particulars: " + item.Particulars, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eDepo)
{
CashBook b = new CashBook()
{
EDate = item.OnDate, CashIn = 0, Particulars = "Bank Depo# " + item.BankDepositId + " Acc: " + item.Account.Account, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eCashEx)
{
CashBook b = new CashBook()
{
EDate = item.OnDate, CashIn = 0, Particulars = "PCE_ID:# " + item.ExpenseId + " Particulars: " + item.Particulars, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eDue)
{
CashBook b = new CashBook()
{
EDate = item.DailySale.SaleDate, CashIn = 0, Particulars = "Dues# " + item.DuesListId + " InvNo: " + item.DailySale.InvNo, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in eCPay)
{
CashBook b = new CashBook()
{
EDate = item.PaymentDate, CashIn = 0, Particulars = "CP_ID:# " + item.CashPaymentId + " PartyName: " + item.PaidTo, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
foreach (var item in ePay)
{
CashBook b = new CashBook()
{
EDate = item.OnDate, CashIn = 0, Particulars = "PMT_ID:# " + item.PaymentId + " P_Party: " + item.PartyName, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
//foreach (var item in eStPay)
//{
// CashBook b = new CashBook() { EDate = item.PaymentDate, CashIn = 0, Particulars = "SAP_ID:# " + item.StaffAdvancePaymentId + " SN: " + item.Employee.StaffName, CashOut = item.Amount, CashBalance = 0 };
// book.Add(b);
//}
foreach (var item in eSal)
{
CashBook b = new CashBook()
{
EDate = item.PaymentDate, CashIn = 0, Particulars = "Sal_ID:# " + item.SalaryPaymentId + " SN: " + item.Employee.StaffName, CashOut = item.Amount, CashBalance = 0
};
book.Add(b);
}
return(CorrectBalCashBook(book, OpnBal));
}