Beispiel #1
0
        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);
            }
        }
Beispiel #2
0
        /*   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();
            }
        }