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