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