private void btnPreview_Click(object sender, EventArgs e) { try { double PMProfit = 0; double CMProfit = 0; double TotalExpense = 0; double TotalAmt = 0; double TotalIncomeAmt = 0; double NetExpense = 0; double NetIncome = 0; DateTime fromDate = dtpMonthly.Value; DateTime toDate = dtpDaily.Value; TransactionalDataSet.dtDailyCashbookLedgerDataTable dt = new TransactionalDataSet.dtDailyCashbookLedgerDataTable(); PMProfit = 0; CMProfit = 0; TotalExpense = 0; //TotalAmt = 0; TotalIncomeAmt = 0; NetExpense = 0; NetIncome = 0; string SPName = string.Empty; string PMPStatus = string.Empty; string CMLStatus = string.Empty; if (rbDaily.Checked) { fromDate = dtpDaily.Value; toDate = fromDate; SPName = "EXEC sp_ProfitLossReportDaily "; PMPStatus = "Previous Profit"; CMLStatus = "Current Day profit"; } else if (rbMonth.Checked) { fromDate = new DateTime(dtpMonthly.Value.Year, dtpMonthly.Value.Month, 1); toDate = fromDate.AddMonths(1).AddDays(-1); SPName = "EXEC sp_ProfitLossReport "; PMPStatus = "Previous Profit"; CMLStatus = "Current Month profit"; } else { fromDate = new DateTime(dtYear.Value.Year, 1, 1); toDate = (new DateTime(dtYear.Value.AddYears(1).Year, 1, 1)).AddDays(-1); SPName = "EXEC sp_ProfitLossReportYearly "; PMPStatus = "Previous Profit"; CMLStatus = "Current Year profit"; } string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM"; string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM"; DateTime dFFDate = Convert.ToDateTime(sFFdate); DateTime dTTDate = Convert.ToDateTime(sTTdate); DataSet ds = new DataSet(); string embededResource = ""; dt.TableName = "TransactionalDataset_dtDailyCashBookLedger"; ds.Tables.Add(dt); embededResource = "INVENTORY.UI.RDLC.rptProfit.rdlc"; ReportParameter rParam = new ReportParameter(); List <ReportParameter> parameters = new List <ReportParameter>(); using (DEWSRMEntities db1 = new DEWSRMEntities()) { using (var connection = db1.Database.Connection) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = SPName + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'"; var reader = command.ExecuteReader(); var Data = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailyCashBookLedgerModel>(reader).ToList(); foreach (var item in Data) { if (item.Income == "Previous Profit") { PMProfit = (double)item.IncomeAmt; } if (item.Expense == "Total Expense") { TotalExpense = (double)item.ExpenseAmt; } if (item.Income == "Total Income") { TotalIncomeAmt = (double)item.IncomeAmt; } if (item.Income == "Total Profit") { TotalAmt = (double)item.IncomeAmt; } if (item.Income == "Current Profit") { CMProfit = (double)item.IncomeAmt; } if (item.Income == "Previous Profit" || item.Income == "Total Profit" || item.Expense == "Current Profit" || item.Expense == "Total Expense") { } else { dt.Rows.Add(item.TransDate, item.id, item.Expense, item.ExpenseAmt, item.Income, item.IncomeAmt); TotalIncomeAmt = TotalIncomeAmt + (double)item.IncomeAmt; } } } } rParam = new ReportParameter("DateRange", "From : " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy")); parameters.Add(rParam); rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName); parameters.Add(rParam); rParam = new ReportParameter("TotalExpense", TotalExpense.ToString()); parameters.Add(rParam); rParam = new ReportParameter("TotalIncomeAmt", TotalIncomeAmt.ToString()); parameters.Add(rParam); rParam = new ReportParameter("PMProfit", PMProfit.ToString()); parameters.Add(rParam); rParam = new ReportParameter("PMPStatus", PMPStatus); parameters.Add(rParam); rParam = new ReportParameter("CMLStatus", CMLStatus); parameters.Add(rParam); rParam = new ReportParameter("CMProfit", CMProfit.ToString()); parameters.Add(rParam); rParam = new ReportParameter("TotalAmt", TotalAmt.ToString()); parameters.Add(rParam); fReportViewer frm = new fReportViewer(); if (dt.Rows.Count > 0) { frm.CommonReportViewer(embededResource, ds, parameters, true); } else { MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnPreview_Click(object sender, EventArgs e) { try { DateTime fromDate = dtpFromDate.Value; DateTime toDate = dtpToDate.Value; DateTime dFFDate = DateTime.Now; DateTime dTTDate = DateTime.Now; string sFFdate = string.Empty; string sTTdate = string.Empty; TransactionalDataSet.dtDailyCashbookLedgerDataTable dt = new TransactionalDataSet.dtDailyCashbookLedgerDataTable(); double PCashInHandAmt = 0; double TotalIncomeAmt = 0; double CCashInHandAmt = 0; double TotalPayable = 0; double TotalRecivable = 0; double OpeningCashInhand = 0; double CurrentCashInhand = 0; double ClosingCashInhand = 0; if (rbDay.Checked) { sFFdate = dtpDay.Value.ToString("dd MMM yyyy") + " 12:00:00 AM"; sTTdate = dtpDay.Value.ToString("dd MMM yyyy") + " 11:59:59 PM"; dFFDate = Convert.ToDateTime(sFFdate); dTTDate = Convert.ToDateTime(sTTdate); } else if (rbMonth.Checked) { fromDate = new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.Month, 1); toDate = (new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.AddMonths(1).Month, 1)).AddDays(-1); sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM"; sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM"; dFFDate = Convert.ToDateTime(sFFdate); dTTDate = Convert.ToDateTime(sTTdate); } else { fromDate = new DateTime(dtYear.Value.Year, 1, 1); toDate = (new DateTime(dtYear.Value.AddYears(1).Year, 1, 1)).AddDays(-1); DateTime preDate = new DateTime(2000, 3, 9, 16, 5, 7, 123); sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM"; sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM"; dFFDate = Convert.ToDateTime(sFFdate); dTTDate = Convert.ToDateTime(sTTdate); } if (rbMonth.Checked || rbDay.Checked || rbYear.Checked) { try { PCashInHandAmt = 0; TotalIncomeAmt = 0; TotalPayable = 0; CCashInHandAmt = 0; TotalRecivable = 0; List <DailyCashBookLedgerModel> DataForTable = null; List <DailyCashBookLedgerModel> DataForTotal = null; List <DailyCashBookLedgerModel> DataForOpening = null; List <DailyCashBookLedgerModel> DataForCurrent = null; List <DailyCashBookLedgerModel> DataForClosing = null; using (DEWSRMEntities db1 = new DEWSRMEntities()) { using (var connection = db1.Database.Connection) { connection.Open(); var command = connection.CreateCommand(); if (rbDay.Checked) { command.CommandText = "EXEC sp_DailyCashInHand " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'"; } else if (rbMonth.Checked) { command.CommandText = "EXEC sp_DailyCashBookLedger4 " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'"; } else { command.CommandText = "EXEC sp_DailyCashBookLedger5 " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'"; } var reader = command.ExecuteReader(); var Data = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailyCashBookLedgerModel>(reader).ToList(); DataForTable = Data.Where(o => o.Expense != "Total Payable" && o.Income != "Total Receivable" && o.Expense != "Current Cash In Hand" && o.Income != "Closing Cash In Hand" && o.Income != "Opening Cash In Hand").ToList(); DataForTotal = Data.Where(o => o.Expense == "Total Payable" && o.Income == "Total Receivable").ToList(); DataForOpening = Data.Where(o => o.Income == "Opening Cash In Hand").ToList(); DataForCurrent = Data.Where(o => o.Expense == "Current Cash In Hand").ToList(); DataForClosing = Data.Where(o => o.Expense == "Closing Cash In Hand").ToList(); //var DataForCashOut2 = DataWithinDateForTable.Where(o => o.Category == "Expense" || o.Category == "Cash Delivery"); //double CashIn = (double)DataForCashIn.Sum(o => o.IncomeAmt); TotalPayable = (double)DataForTotal.Sum(o => o.ExpenseAmt); TotalRecivable = (double)DataForTotal.Sum(o => o.IncomeAmt); OpeningCashInhand = (double)DataForOpening.Sum(o => o.IncomeAmt); CurrentCashInhand = (double)DataForCurrent.Sum(o => o.ExpenseAmt); ClosingCashInhand = OpeningCashInhand + CurrentCashInhand; foreach (var item in DataForTable) { dt.Rows.Add(item.TransDate, item.id, item.Expense, item.ExpenseAmt, item.Income, item.IncomeAmt); } } } DataSet ds = new DataSet(); string embededResource = string.Empty; dt.TableName = "TransactionalDataset_dtDailyCashBookLedger"; ds.Tables.Add(dt); embededResource = "ESRP.UI.RDLC.rptDailyCashStatement.rdlc"; ReportParameter rParam = new ReportParameter(); List <ReportParameter> parameters = new List <ReportParameter>(); rParam = new ReportParameter("DateRange", "From : " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy")); parameters.Add(rParam); rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName); parameters.Add(rParam); rParam = new ReportParameter("TotalPayable", TotalPayable.ToString()); parameters.Add(rParam); rParam = new ReportParameter("TotalRecivable", TotalRecivable.ToString()); parameters.Add(rParam); rParam = new ReportParameter("OpeningCashInhand", OpeningCashInhand.ToString()); parameters.Add(rParam); rParam = new ReportParameter("CurrentCashInhand", CurrentCashInhand.ToString()); parameters.Add(rParam); rParam = new ReportParameter("ClosingCashInHand", ClosingCashInhand.ToString()); parameters.Add(rParam); fReportViewer frm = new fReportViewer(); //if (dt.Rows.Count > 0) //{ frm.CommonReportViewer(embededResource, ds, parameters, true); //} //else //{ // MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information); //} } catch (Exception ex) { MessageBox.Show(ex.Message); } } else if (rbYear.Checked) { PCashInHandAmt = 0; TotalIncomeAmt = 0; TotalPayable = 0; CCashInHandAmt = 0; TotalRecivable = 0; List <DailyCashBookLedgerModel> DataForTable = null; List <DailyCashBookLedgerModel> DataForTotal = null; List <DailyCashBookLedgerModel> DataForOpening = null; List <DailyCashBookLedgerModel> DataForCurrent = null; List <DailyCashBookLedgerModel> DataForClosing = null; fromDate = new DateTime(dtYear.Value.Year, 1, 1); toDate = (new DateTime(dtYear.Value.AddYears(1).Year, 1, 1)).AddDays(-1); DateTime Date = new DateTime(2000, 3, 9, 16, 5, 7, 123); String RDate = Date.ToString("dd MMM yyyy"); DateTime preDate = new DateTime(2000, 3, 9, 16, 5, 7, 123); sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM"; sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM"; dFFDate = Convert.ToDateTime(sFFdate); dTTDate = Convert.ToDateTime(sTTdate); using (DEWSRMEntities db1 = new DEWSRMEntities()) { using (var connection = db1.Database.Connection) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = "EXEC sp_DailyCashBookLedger5 " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'"; var reader = command.ExecuteReader(); var Data = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailyCashBookLedgerModel>(reader).ToList(); DataForTable = Data.Where(o => o.Expense != "Total Payable" && o.Income != "Total Receivable" && o.Expense != "Current Cash In Hand" && o.Income != "Closing Cash In Hand" && o.Income != "Opening Cash In Hand").ToList(); DataForTotal = Data.Where(o => o.Expense == "Total Payable" && o.Income == "Total Receivable").ToList(); DataForOpening = Data.Where(o => o.Income == "Opening Cash In Hand").ToList(); DataForCurrent = Data.Where(o => o.Expense == "Current Cash In Hand").ToList(); DataForClosing = Data.Where(o => o.Expense == "Closing Cash In Hand").ToList(); //var DataForCashOut2 = DataWithinDateForTable.Where(o => o.Category == "Expense" || o.Category == "Cash Delivery"); //double CashIn = (double)DataForCashIn.Sum(o => o.IncomeAmt); TotalPayable = (double)DataForTotal.Sum(o => o.ExpenseAmt); TotalRecivable = (double)DataForTotal.Sum(o => o.IncomeAmt); OpeningCashInhand = (double)DataForOpening.Sum(o => o.IncomeAmt); CurrentCashInhand = (double)DataForCurrent.Sum(o => o.ExpenseAmt); ClosingCashInhand = (double)DataForClosing.Sum(o => o.ExpenseAmt); foreach (var item in Data) { if (item.Income == "Opening Cash In Hand") { PCashInHandAmt = (double)item.IncomeAmt; } if (item.Expense == "Total Payable") { TotalPayable = (double)item.ExpenseAmt; } if (item.Income == "Total Receivable") { TotalRecivable = (double)item.IncomeAmt; } if (item.Income == "Total Receivable" || item.Income == "Opening Cash In Hand" || item.Income == "Total Amount" || item.Expense == "Current Cash In Hand") { } else { dt.Rows.Add(item.TransDate, item.id, item.Expense, item.ExpenseAmt, item.Income, item.IncomeAmt); TotalIncomeAmt = TotalIncomeAmt + (double)item.IncomeAmt; } } if (PCashInHandAmt < 0) { TotalPayable = TotalPayable - PCashInHandAmt; CCashInHandAmt = TotalIncomeAmt - TotalPayable; } else { TotalIncomeAmt = TotalIncomeAmt + PCashInHandAmt; CCashInHandAmt = TotalIncomeAmt - TotalPayable; } } } DataSet ds = new DataSet(); string embededResource = string.Empty; dt.TableName = "TransactionalDataset_dtDailyCashBookLedger"; ds.Tables.Add(dt); embededResource = "ESRP.UI.RDLC.rptDailyCashStatement.rdlc"; embededResource = "ESRP.UI.RDLC.rptDailyCashStatement.rdlc"; ReportParameter rParam = new ReportParameter(); List <ReportParameter> parameters = new List <ReportParameter>(); rParam = new ReportParameter("DateRange", "From : " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy")); parameters.Add(rParam); rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName); parameters.Add(rParam); rParam = new ReportParameter("TotalPayable", TotalPayable.ToString()); parameters.Add(rParam); rParam = new ReportParameter("TotalRecivable", TotalRecivable.ToString()); parameters.Add(rParam); rParam = new ReportParameter("OpeningCashInhand", OpeningCashInhand.ToString()); parameters.Add(rParam); rParam = new ReportParameter("CurrentCashInhand", CurrentCashInhand.ToString()); parameters.Add(rParam); rParam = new ReportParameter("ClosingCashInHand", ClosingCashInhand.ToString()); parameters.Add(rParam); fReportViewer frm = new fReportViewer(); if (dt.Rows.Count > 0) { frm.CommonReportViewer(embededResource, ds, parameters, true); } else { MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }