private void loadGrid() { Common cm = new Common(); core_ledger_entry objLE = new core_ledger_entry(); DataSet ds = objLE.SelectMethod(@"SELECT le.Id_Ledger_Entry, le.Id_Petty_Cash_Voucher, le.Id_Petty_Cash_Book, pcb.Petty_Cash_Book_Name, pcb.Petty_Cash_Book_Code, le.Id_Petty_Cash_Category, pcc.Petty_Cash_Category_Name, pcc.Petty_Cash_Category_Code, le.Id_Ledger_Account, le.Balance_Carried_Forward, le.Debit_Amount, le.Credit_Amount, CASE WHEN le.Post_Payment='0' THEN 'Post Payment' ELSE 'Pre Payment' END AS Post_Payment, le.Created_Date, le.Created_User, le.Edited_Date, le.Edited_User FROM core_ledger_entry AS le INNER JOIN core_petty_cash_book AS pcb ON le.Id_Petty_Cash_Book=pcb.Id_Petty_Cash_Book INNER JOIN core_petty_cash_category AS pcc ON le.Id_Petty_Cash_Category=pcc.Id_Petty_Cash_Category WHERE le.Id_Ledger_Account='" + Convert.ToInt32(hfLAID.Value) + @"' AND DATE(le.Created_Date) BETWEEN DATE('" + cm.convertToMySqlDate(fromDate.Text.Trim()) + "') and DATE('" + cm.convertToMySqlDate(toDate.Text.Trim()) + "');", "core_ledger_entry"); if (ds.Tables[0].Rows.Count > 0) { leGrid.DataSource = ds.Tables["core_ledger_entry"]; leGrid.DataBind(); } else { leGrid.DataSource = new int[] { }; leGrid.DataBind(); ScriptManager.RegisterStartupScript(this, GetType(), "AlertSweet", "alertSweet();", true); //ScriptManager.RegisterStartupScript(up1, GetType(), "AlertSweet", "showAlert('error');", true); } }
/* private void getReport() * { * Common cm = new Common(); * core_ledger_entry dbAccess = new core_ledger_entry(); * DataSet ds = dbAccess.SelectMethod(@"SELECT * le.Id_Ledger_Entry, * le.Id_Petty_Cash_Voucher, * pcv.Business_Purpose, * le.Id_Petty_Cash_Book, * pcb.Petty_Cash_Book_Name, * pcb.Petty_Cash_Book_Code, * le.Id_Petty_Cash_Category, * pcc.Petty_Cash_Category_Name, * pcc.Petty_Cash_Category_Code, * le.Id_Ledger_Account, * le.Balance_Carried_Forward, * le.Debit_Amount, * le.Credit_Amount, * le.Post_Payment, * le.Created_Date, * le.Created_User, * le.Edited_Date, * le.Edited_User * FROM core_ledger_entry AS le * INNER JOIN core_petty_cash_payment_voucher AS pcv ON le.Id_Petty_Cash_Voucher=pcv.Id_Petty_Cash_Voucher * INNER JOIN core_petty_cash_book AS pcb ON le.Id_Petty_Cash_Book=pcb.Id_Petty_Cash_Book * INNER JOIN core_petty_cash_category AS pcc ON le.Id_Petty_Cash_Category=pcc.Id_Petty_Cash_Category * WHERE DATE(le.Created_Date) BETWEEN DATE('" + cm.convertToMySqlDate(fromDate.Text.Trim()) + "') and DATE('" + cm.convertToMySqlDate(toDate.Text.Trim()) + "');", "core_ledger_entry"); * * CrystalReport1 irpt = new CrystalReport1(); * * if (ds.Tables[0].Rows.Count == 0) * { * if (IsPostBack) * { * ScriptManager.RegisterStartupScript(this, GetType(), "AlertSweet", "alertSweet();", true); * crvVoucherReport.ReportSource = null; * //ScriptManager.RegisterStartupScript(this, GetType(), "AlertSweet", "showAlert('error');", true); * } * } * else * { * irpt.SetDataSource(ds.Tables["core_ledger_entry"]); * crvVoucherReport.EnableDatabaseLogonPrompt = false; * Session["ReportDocument"] = irpt; * ReportDocument doc = (ReportDocument)Session["ReportDocument"]; * if (doc != null) * crvVoucherReport.ReportSource = doc; * crvVoucherReport.DataBind(); * * } * * } */ private void getReport() { Common cm = new Common(); core_ledger_entry obj = new core_ledger_entry(); string sql = @"SELECT le.Id_Ledger_Entry, le.Id_Petty_Cash_Voucher, le.Business_Purpose, le.Id_Petty_Cash_Book, le.Id_Petty_Cash_Category, pcc.Petty_Cash_Category_Name, pcc.Petty_Cash_Category_Code, le.Id_Ledger_Account, le.Balance_Carried_Forward, le.Debit_Amount, le.Credit_Amount, le.Post_Payment, le.Created_Date FROM core_ledger_entry AS le INNER JOIN core_petty_cash_book AS pcb ON le.Id_Petty_Cash_Book=pcb.Id_Petty_Cash_Book INNER JOIN core_petty_cash_category AS pcc ON le.Id_Petty_Cash_Category=pcc.Id_Petty_Cash_Category WHERE le.Id_Petty_Cash_Book='" + Convert.ToInt32(ddlPCBName.SelectedValue) + @"'"; if (cm.convertToMySqlDate(fromDate.Text.Trim()) != string.Empty) { sql = @"SELECT Id_Ledger_Entry, '' AS Id_Petty_Cash_Voucher, 'Balance Carried Forward' AS Business_Purpose, Id_Petty_Cash_Book, '42' AS Id_Petty_Cash_Category, 'Movements between PCB and Bank account' AS Petty_Cash_Category_Name, Petty_Cash_Category_Code, '6' AS Id_Ledger_Account, Balance_Carried_Forward, Debit_Amount, Credit_Amount, Post_Payment, '' AS Created_Date FROM ( SELECT le.Id_Ledger_Entry, le.Id_Petty_Cash_Voucher, le.Business_Purpose, le.Id_Petty_Cash_Book, le.Id_Petty_Cash_Category, pcc.Petty_Cash_Category_Name, pcc.Petty_Cash_Category_Code, le.Id_Ledger_Account, (CASE WHEN (SUM(le.Credit_Amount)-1*SUM(le.Debit_Amount))>0 THEN (SUM(le.Credit_Amount)-1*SUM(le.Debit_Amount)) ELSE 0 END) AS Debit_Amount, (CASE WHEN (SUM(le.Credit_Amount)-1*SUM(le.Debit_Amount))<0 THEN (SUM(le.Credit_Amount)-1*SUM(le.Debit_Amount)) ELSE 0 END) AS Credit_Amount, (SUM(le.Credit_Amount)-1*SUM(le.Debit_Amount)) AS Balance_Carried_Forward, le.Post_Payment, le.Created_Date FROM core_ledger_entry AS le INNER JOIN core_petty_cash_book AS pcb ON le.Id_Petty_Cash_Book=pcb.Id_Petty_Cash_Book INNER JOIN core_petty_cash_category AS pcc ON le.Id_Petty_Cash_Category=pcc.Id_Petty_Cash_Category WHERE le.Id_Petty_Cash_Book='" + Convert.ToInt32(ddlPCBName.SelectedValue) + @"' AND DATE(le.Created_Date) < '" + cm.convertToMySqlDate(fromDate.Text.Trim()) + @"') AS ZZZ UNION ALL " + sql + @" AND DATE(le.Created_Date) >= '" + cm.convertToMySqlDate(fromDate.Text.Trim()) + @"' "; } if (cm.convertToMySqlDate(toDate.Text.Trim()) != string.Empty) { sql += @"AND DATE(le.Created_Date) <= '" + cm.convertToMySqlDate(toDate.Text.Trim()) + @"' ORDER BY Id_Ledger_Entry;"; } DataSet ds = obj.SelectMethod(sql, "table"); PettyCashBookReport irpt = new PettyCashBookReport(); if (ds.Tables[0].Rows.Count == 0) { if (IsPostBack) { ///// irpt.SetDataSource(ds.Tables["table"]); crvVoucherReport.EnableDatabaseLogonPrompt = false; Session["ReportDocument"] = irpt; ReportDocument doc = (ReportDocument)Session["ReportDocument"]; ///// /* ScriptManager.RegisterStartupScript(this, GetType(), "AlertSweet", "alertSweet();", true); * crvVoucherReport.ReportSource = null; */ /////// crvVoucherReport.ReportSource = doc; crvVoucherReport.DataBind(); /////// } } else { irpt.SetDataSource(ds.Tables["table"]); crvVoucherReport.EnableDatabaseLogonPrompt = false; ////////////newly added if (IsPostBack) { TextObject obj1 = (TextObject)irpt.ReportDefinition.Sections["Section1"].ReportObjects["Text5"]; TextObject obj2 = (TextObject)irpt.ReportDefinition.Sections["Section1"].ReportObjects["Text7"]; obj1.Text = fromDate.Text.Trim(); obj2.Text = toDate.Text.Trim(); } //////////// Session["ReportDocument"] = irpt; ReportDocument doc = (ReportDocument)Session["ReportDocument"]; if (doc != null) { crvVoucherReport.ReportSource = doc; } crvVoucherReport.DataBind(); } }