// GET: IncomeExpenseReport public ActionResult Index(int?id) { IncomeExpensesReport ierData = null; IEReport dM = new IEReport(); if (id == 1) { ierData = dM.GetDailyReport(_context, DateTime.Today); } else if (id == 7) { ierData = dM.GetWeeklyReport(_context); } else if (id == 30) { ierData = dM.GetMonthlyReport(_context, DateTime.Today); } else if (id == 365) { ierData = dM.GetYearlyReport(_context, DateTime.Today); } else { ierData = dM.GetDailyReport(_context, DateTime.Today); } return(View(ierData)); }
public IncomeExpensesReport GetYearlyReport(AprajitaRetailsContext db, DateTime onDate) { IncomeExpensesReport ierData = new IncomeExpensesReport { OnDate = onDate, IncomeExpensesReportId = 1, //Income TotalSale = db.DailySales.Where(c => c.SaleDate.Year == onDate.Year && !c.IsManualBill && !c.IsTailoringBill).Sum(c => c.Amount), TotalManualSale = db.DailySales.Where(c => c.SaleDate.Year == onDate.Year && c.IsManualBill && !c.IsTailoringBill).Sum(c => c.Amount), TotalTailoringSale = db.DailySales.Where(c => c.SaleDate.Year == onDate.Year && c.IsTailoringBill).Sum(c => c.Amount), TotalCashRecipts = db.CashReceipts.Where(c => c.InwardDate.Year == onDate.Year).Sum(c => c.Amount), TotalRecipts = db.Receipts.Where(c => c.RecieptDate.Year == onDate.Year).Sum(c => c.Amount), TotalOtherIncome = 0, //Expenses TotalExpenses = db.Expenses.Where(c => c.ExpDate.Year == onDate.Year).Sum(c => c.Amount) + db.PettyCashExpenses.Where(c => c.ExpDate.Year == onDate.Year).Sum(c => c.Amount), TotalOthersExpenses = 0, TotalPayments = db.Payments.Where(c => c.PayDate.Year == onDate.Year).Sum(c => c.Amount), TotalStaffPayments = db.SalaryPayments.Where(c => c.PaymentDate.Year == onDate.Year).Sum(c => c.Amount) + db.StaffAdvancePayments.Where(c => c.PaymentDate.Year == onDate.Year).Sum(c => c.Amount), TotalTailoringPayments = db.TailoringSalaryPayments.Where(c => c.PaymentDate.Year == onDate.Year).Sum(c => c.Amount) + db.TailoringStaffAdvancePayments.Where(c => c.PaymentDate.Year == onDate.Year).Sum(c => c.Amount), TotalCashPayments = db.CashPayments.Where(c => c.PaymentDate.Year == onDate.Year && c.Mode.Transcation != "Home Expenses").Sum(c => c.Amount), TotalHomeExpenses = db.CashPayments.Where(c => c.PaymentDate.Year == onDate.Year && c.Mode.Transcation == "Home Expenses").Sum(c => c.Amount), //Dues TotalDues = db.DuesLists.Include(c => c.DailySale).Where(c => c.DailySale.SaleDate.Year == onDate.Year).Sum(c => c.Amount), TotalRecovery = db.DueRecoverds.Where(c => c.PaidDate.Year == onDate.Year).Sum(c => c.AmountPaid) }; ierData.TotalPendingDues = ierData.TotalDues - ierData.TotalRecovery; return(ierData); }
public IncomeExpensesReport GetWeeklyReport(AprajitaRetailsContext db) { var start = DateTime.Now.StartOfWeek(); var end = DateTime.Now.EndOfWeek(); IncomeExpensesReport ierData = new IncomeExpensesReport { OnDate = DateTime.Today, IncomeExpensesReportId = 1, //Income TotalSale = (decimal?)db.DailySales.Where(c => c.SaleDate.Date >= start.Date && c.SaleDate.Date <= end.Date && !c.IsManualBill && !c.IsTailoringBill).Sum(c => c.Amount) ?? 0, TotalManualSale = (decimal?)db.DailySales.Where(c => c.SaleDate.Date >= start.Date && c.SaleDate.Date <= end.Date && c.IsManualBill && !c.IsTailoringBill).Sum(c => c.Amount) ?? 0, TotalTailoringSale = (decimal?)db.DailySales.Where(c => c.SaleDate.Date >= start.Date && c.SaleDate.Date <= end.Date && c.IsTailoringBill).Sum(c => c.Amount) ?? 0, TotalCashRecipts = (decimal?)db.CashReceipts.Where(c => c.InwardDate.Date >= start.Date && c.InwardDate.Date <= end.Date).Sum(c => c.Amount) ?? 0, TotalRecipts = (decimal?)db.Receipts.Where(c => c.RecieptDate.Date >= start.Date && c.RecieptDate.Date <= end.Date).Sum(c => c.Amount) ?? 0, TotalOtherIncome = 0, //Expenses TotalExpenses = (decimal?)db.Expenses.Where(c => c.ExpDate.Date >= start.Date && c.ExpDate.Date <= end.Date).Sum(c => c.Amount) ?? 0 + (decimal?)db.CashExpenses.Where(c => c.ExpDate.Date >= start.Date && c.ExpDate.Date <= end.Date).Sum(c => c.Amount) ?? 0, TotalOthersExpenses = 0, TotalPayments = (decimal?)db.Payments.Where(c => c.PayDate.Date >= start.Date && c.PayDate.Date <= end.Date).Sum(c => c.Amount) ?? 0, TotalStaffPayments = (decimal?)db.Salaries.Where(c => c.PaymentDate.Date >= start.Date && c.PaymentDate.Date <= end.Date).Sum(c => c.Amount) ?? 0 + (decimal?)db.StaffAdvancePayments.Where(c => c.PaymentDate.Date >= start.Date && c.PaymentDate.Date <= end.Date).Sum(c => c.Amount) ?? 0, TotalTailoringPayments = (decimal?)db.TailoringSalaries.Where(c => c.PaymentDate.Date >= start.Date && c.PaymentDate.Date <= end.Date).Sum(c => c.Amount) ?? 0 + (decimal?)db.TailoringStaffAdvancePayments.Where(c => c.PaymentDate.Date >= start.Date && c.PaymentDate.Date <= end.Date).Sum(c => c.Amount) ?? 0, TotalCashPayments = (decimal?)db.CashPayments.Where(c => c.PaymentDate.Date >= start.Date && c.PaymentDate.Date <= end.Date && c.Mode.Transcation != "Home Expenses").Sum(c => c.Amount) ?? 0, TotalHomeExpenses = (decimal?)db.CashPayments.Where(c => c.PaymentDate.Date >= start.Date && c.PaymentDate.Date <= end.Date && c.Mode.Transcation == "Home Expenses").Sum(c => c.Amount) ?? 0, //Dues TotalDues = (decimal?)db.DuesLists.Include(c => c.DailySale).Where(c => c.DailySale.SaleDate.Date >= start.Date && c.DailySale.SaleDate.Date <= end.Date).Sum(c => c.Amount) ?? 0, TotalRecovery = (decimal?)db.Recoverds.Where(c => c.PaidDate.Date >= start.Date && c.PaidDate.Date <= end.Date).Sum(c => c.AmountPaid) ?? 0 }; ierData.TotalPendingDues = ierData.TotalDues - ierData.TotalRecovery; return(ierData); }
public IncomeExpensesReport CalculateIncomeExpenes(AprajitaRetailsContext db, DateTime onDate, bool IsMonth, bool IsYear, bool IsWeekLy = false) { IncomeExpensesReport ierData = null; if (IsMonth) { ierData = new IncomeExpensesReport { OnDate = onDate, //Income TotalManualSale = db.DailySales.Where(c => c.IsManualBill && (c.SaleDate).Month == (onDate).Month).Sum(c => c.Amount), TotalSale = db.DailySales.Where(c => c.SaleDate.Month == (onDate).Month && !c.IsManualBill && !c.IsTailoringBill).Sum(c => c.Amount), TotalTailoringSale = db.DailySales.Where(c => c.IsTailoringBill && (c.SaleDate).Month == (onDate).Month).Sum(c => c.Amount), TotalOtherIncome = 0, //Expenses TotalExpenses = db.Expenses.Where(c => (c.ExpDate).Month == (onDate).Month).Sum(c => c.Amount), TotalHomeExpenses = db.CashPayments.Where(c => (c.PaymentDate).Month == (onDate).Month && (c.Mode.Transcation == "Home Expenses" || c.Mode.Transcation == "Amit Kumar "|| c.Mode.Transcation == " Mukesh(Home Staff")).Sum(c => c.Amount), TotalOthersExpenses = db.CashPayments.Where(c => (c.PaymentDate).Month == (onDate).Month && (c.Mode.Transcation != "Home Expenses" && c.Mode.Transcation != "Amit Kumar " && c.Mode.Transcation != " Mukesh(Home Staff")).Sum(c => c.Amount), TotalCashPayments = db.PettyCashExpenses.Where(c => (c.ExpDate).Month == (onDate).Month).Sum(c => c.Amount), //Payments TotalPayments = db.Payments.Where(c => (c.PayDate).Month == (onDate).Month).Sum(c => c.Amount), //Staff/Tailoring TotalStaffPayments = db.SalaryPayments.Where(c => (c.PaymentDate).Month == (onDate).Month).Sum(c => c.Amount) + db.StaffAdvancePayments.Where(c => (c.PaymentDate).Month == (onDate).Month).Sum(c => c.Amount), TotalTailoringPayments = db.TailoringSalaryPayments.Where(c => (c.PaymentDate).Month == (onDate).Month).Sum(c => c.Amount) + db.TailoringStaffAdvancePayments.Where(c => (c.PaymentDate).Month == (onDate).Month).Sum(c => c.Amount), //Reciepts TotalRecipts = db.Receipts.Where(c => (c.RecieptDate).Month == (onDate).Month).Sum(c => c.Amount) + db.StaffAdvanceReceipts.Where(c => (c.ReceiptDate).Month == (onDate).Month).Sum(c => c.Amount) + db.TailoringStaffAdvanceReceipts.Where(c => (c.ReceiptDate).Month == (onDate).Month).Sum(c => c.Amount), TotalCashRecipts = db.CashReceipts.Where(c => (c.InwardDate).Month == (onDate).Month).Sum(c => c.Amount), TotalRecovery = db.DuesLists.Where(c => c.RecoveryDate.Value.Month == onDate.Month).Sum(c => c.Amount), //Dues TotalDues = db.DuesLists.Include(c => c.DailySale).Where(c => c.IsRecovered == false && c.DailySale.SaleDate.Month == onDate.Month).Sum(c => c.Amount), TotalPendingDues = db.DuesLists.Where(c => !c.IsRecovered).Sum(c => c.Amount), }; } else if (IsYear) { ierData = new IncomeExpensesReport { OnDate = onDate, //Income TotalManualSale = db.DailySales.Where(c => c.IsManualBill && (c.SaleDate).Year == (onDate).Year).Sum(c => c.Amount), TotalSale = db.DailySales.Where(c => c.SaleDate.Year == (onDate).Year && !c.IsManualBill && !c.IsTailoringBill).Sum(c => c.Amount), TotalTailoringSale = db.DailySales.Where(c => c.IsTailoringBill && (c.SaleDate).Year == (onDate).Year).Sum(c => c.Amount), TotalOtherIncome = 0, //Expenses TotalExpenses = db.Expenses.Where(c => (c.ExpDate).Year == (onDate).Year).Sum(c => c.Amount), TotalHomeExpenses = db.CashPayments.Where(c => (c.PaymentDate).Year == (onDate).Year && (c.Mode.Transcation == "Home Expenses" || c.Mode.Transcation == "Amit Kumar "|| c.Mode.Transcation == " Mukesh(Home Staff")).Sum(c => c.Amount), TotalOthersExpenses = db.CashPayments.Where(c => (c.PaymentDate).Year == (onDate).Year && (c.Mode.Transcation != "Home Expenses" && c.Mode.Transcation != "Amit Kumar " && c.Mode.Transcation != " Mukesh(Home Staff")).Sum(c => c.Amount), TotalCashPayments = db.PettyCashExpenses.Where(c => (c.ExpDate).Year == (onDate).Year).Sum(c => c.Amount), //Payments TotalPayments = db.Payments.Where(c => (c.PayDate).Year == (onDate).Year).Sum(c => c.Amount), //Staff/Tailoring TotalStaffPayments = db.SalaryPayments.Where(c => (c.PaymentDate).Year == (onDate).Year).Sum(c => c.Amount) + db.StaffAdvancePayments.Where(c => (c.PaymentDate).Year == (onDate).Year).Sum(c => c.Amount), TotalTailoringPayments = db.TailoringSalaryPayments.Where(c => (c.PaymentDate).Year == (onDate).Year).Sum(c => c.Amount) + db.TailoringStaffAdvancePayments.Where(c => (c.PaymentDate).Year == (onDate).Year).Sum(c => c.Amount), //Reciepts TotalRecipts = db.Receipts.Where(c => (c.RecieptDate).Year == (onDate).Year).Sum(c => c.Amount) + db.StaffAdvanceReceipts.Where(c => (c.ReceiptDate).Year == (onDate).Year).Sum(c => c.Amount) + db.TailoringStaffAdvanceReceipts.Where(c => (c.ReceiptDate).Year == (onDate).Year).Sum(c => c.Amount), TotalCashRecipts = db.CashReceipts.Where(c => (c.InwardDate).Year == (onDate).Year).Sum(c => c.Amount), TotalRecovery = db.DuesLists.Where(c => c.RecoveryDate.Value.Year == onDate.Year).Sum(c => c.Amount), //Dues TotalDues = db.DuesLists.Include(c => c.DailySale).Where(c => c.IsRecovered == false && c.DailySale.SaleDate.Year == onDate.Year).Sum(c => c.Amount), TotalPendingDues = db.DuesLists.Where(c => !c.IsRecovered).Sum(c => c.Amount), }; } else if (IsWeekLy) { DateTime startDate = onDate.StartOfWeek(); DateTime endDate = onDate.EndOfWeek(); ierData = new IncomeExpensesReport { OnDate = onDate, //Income TotalManualSale = db.DailySales.Where(c => c.IsManualBill && (c.SaleDate).Date >= startDate.Date && c.SaleDate.Date <= endDate.Date).Sum(c => c.Amount), TotalSale = db.DailySales.Where(c => c.SaleDate.Date >= startDate.Date && c.SaleDate.Date <= endDate.Date && !c.IsManualBill && !c.IsTailoringBill).Sum(c => c.Amount), TotalTailoringSale = db.DailySales.Where(c => c.IsTailoringBill && (c.SaleDate).Date >= startDate.Date && c.SaleDate.Date <= endDate.Date).Sum(c => c.Amount), TotalOtherIncome = 0, //Expenses TotalExpenses = db.Expenses.Where(c => (c.ExpDate).Date >= startDate.Date && c.ExpDate.Date <= endDate.Date).Sum(c => c.Amount), TotalHomeExpenses = db.CashPayments.Where(c => (c.PaymentDate).Date >= startDate.Date && c.PaymentDate.Date <= endDate.Date && (c.Mode.Transcation == "Home Expenses" || c.Mode.Transcation == "Amit Kumar "|| c.Mode.Transcation == " Mukesh(Home Staff")).Sum(c => c.Amount), TotalOthersExpenses = db.CashPayments.Where(c => (c.PaymentDate).Date >= startDate.Date && c.PaymentDate.Date <= endDate.Date && (c.Mode.Transcation != "Home Expenses" && c.Mode.Transcation != "Amit Kumar " && c.Mode.Transcation != " Mukesh(Home Staff")).Sum(c => c.Amount), TotalCashPayments = db.PettyCashExpenses.Where(c => (c.ExpDate).Date >= startDate.Date && c.ExpDate.Date <= endDate.Date).Sum(c => c.Amount), //Payments TotalPayments = db.Payments.Where(c => (c.PayDate).Date >= startDate.Date && c.PayDate.Date <= endDate.Date).Sum(c => c.Amount), //Staff/Tailoring TotalStaffPayments = db.SalaryPayments.Where(c => (c.PaymentDate).Date >= startDate.Date && c.PaymentDate.Date <= endDate.Date).Sum(c => c.Amount) + db.StaffAdvancePayments.Where(c => (c.PaymentDate).Date >= startDate.Date && c.PaymentDate.Date <= endDate.Date).Sum(c => c.Amount), TotalTailoringPayments = db.TailoringSalaryPayments.Where(c => (c.PaymentDate).Date >= startDate.Date && c.PaymentDate.Date <= endDate.Date).Sum(c => c.Amount) + db.TailoringStaffAdvancePayments.Where(c => (c.PaymentDate).Date >= startDate.Date && c.PaymentDate.Date <= endDate.Date).Sum(c => c.Amount), //Reciepts TotalRecipts = db.Receipts.Where(c => (c.RecieptDate).Date >= startDate.Date && c.RecieptDate.Date <= endDate.Date).Sum(c => c.Amount) + db.StaffAdvanceReceipts.Where(c => (c.ReceiptDate).Date >= startDate.Date && c.ReceiptDate.Date <= endDate.Date).Sum(c => c.Amount) + db.TailoringStaffAdvanceReceipts.Where(c => (c.ReceiptDate).Date >= startDate.Date && c.ReceiptDate.Date <= endDate.Date).Sum(c => c.Amount), TotalCashRecipts = db.CashReceipts.Where(c => (c.InwardDate).Date >= startDate.Date && c.InwardDate.Date <= endDate.Date).Sum(c => c.Amount), TotalRecovery = db.DuesLists.Where(c => c.RecoveryDate.Value.Date == onDate.Date).Sum(c => c.Amount), //Dues TotalDues = db.DuesLists.Include(c => c.DailySale).Where(c => c.IsRecovered == false && c.DailySale.SaleDate.Date >= startDate.Date && c.DailySale.SaleDate.Date <= endDate.Date).Sum(c => c.Amount), TotalPendingDues = db.DuesLists.Where(c => !c.IsRecovered).Sum(c => c.Amount), }; } else { ierData = new IncomeExpensesReport { OnDate = onDate, //Income TotalManualSale = db.DailySales.Where(c => c.IsManualBill && (c.SaleDate).Date == (onDate).Date).Sum(c => c.Amount), TotalSale = db.DailySales.Where(c => c.SaleDate.Date == (onDate).Date && !c.IsManualBill && !c.IsTailoringBill).Sum(c => c.Amount), TotalTailoringSale = db.DailySales.Where(c => c.IsTailoringBill && (c.SaleDate).Date == (onDate).Date).Sum(c => c.Amount), TotalOtherIncome = 0, //Expenses TotalExpenses = db.Expenses.Where(c => (c.ExpDate).Date == (onDate).Date).Sum(c => c.Amount), TotalHomeExpenses = db.CashPayments.Where(c => (c.PaymentDate).Date == (onDate).Date && (c.Mode.Transcation == "Home Expenses" || c.Mode.Transcation == "Amit Kumar "|| c.Mode.Transcation == " Mukesh(Home Staff")).Sum(c => c.Amount), TotalOthersExpenses = db.CashPayments.Where(c => (c.PaymentDate).Date == (onDate).Date && (c.Mode.Transcation != "Home Expenses" && c.Mode.Transcation != "Amit Kumar " && c.Mode.Transcation != " Mukesh(Home Staff")).Sum(c => c.Amount), TotalCashPayments = db.PettyCashExpenses.Where(c => (c.ExpDate).Date == (onDate).Date).Sum(c => c.Amount), //Payments TotalPayments = db.Payments.Where(c => (c.PayDate).Date == (onDate).Date).Sum(c => c.Amount), //Staff/Tailoring TotalStaffPayments = db.SalaryPayments.Where(c => (c.PaymentDate).Date == (onDate).Date).Sum(c => c.Amount) + db.StaffAdvancePayments.Where(c => (c.PaymentDate).Date == (onDate).Date).Sum(c => c.Amount), TotalTailoringPayments = db.TailoringSalaryPayments.Where(c => (c.PaymentDate).Date == (onDate).Date).Sum(c => c.Amount) + db.TailoringStaffAdvancePayments.Where(c => (c.PaymentDate).Date == (onDate).Date).Sum(c => c.Amount), //Reciepts TotalRecipts = db.Receipts.Where(c => (c.RecieptDate).Date == (onDate).Date).Sum(c => c.Amount) + db.StaffAdvanceReceipts.Where(c => (c.ReceiptDate).Date == (onDate).Date).Sum(c => c.Amount) + db.TailoringStaffAdvanceReceipts.Where(c => (c.ReceiptDate).Date == (onDate).Date).Sum(c => c.Amount), TotalCashRecipts = db.CashReceipts.Where(c => (c.InwardDate).Date == (onDate).Date).Sum(c => c.Amount), TotalRecovery = db.DuesLists.Where(c => c.RecoveryDate.Value.Date == onDate.Date).Sum(c => c.Amount), //Dues TotalDues = db.DuesLists.Include(c => c.DailySale).Where(c => c.IsRecovered == false && c.DailySale.SaleDate.Date == onDate.Date).Sum(c => c.Amount), TotalPendingDues = db.DuesLists.Where(c => !c.IsRecovered).Sum(c => c.Amount), }; } return(ierData); }