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