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();
}
}
//protected void reimburseAmount_TextChanged(object sender, EventArgs e)
//{
// hfReimburseAmount.Value = reimburseAmount.Text.Trim();
// if (!((hfReimburseAmount.Value) == null || (hfReimburseAmount.Value) == ""))
// {
// double availableBalance = 0.00;
// double reimbursedAmount = 0.00;
// double amountToBeReimbursed = 0.00;
// availableBalance = Convert.ToDouble(PCBAvailableBalance.Text.Trim());
// reimbursedAmount = Convert.ToDouble(hfReimburseAmount.Value);
// amountToBeReimbursed = Convert.ToDouble(AmountToBeReimbursed.Text.Trim());
// if (amountToBeReimbursed >= reimbursedAmount)
// bcf.Text = (availableBalance + reimbursedAmount).ToString();
// //else
// // Message.Text = "You can not Reimburse an amount which will result exceed the Float amount";
// //has to get the Msgbox.dll
// }
// else
// bcf.Text = "";
//}
protected void cmdSubmit_Click(object sender, EventArgs e)
{
hfReimburseAmount.Value = reimburseAmount.Text.Trim();
if (!((hfReimburseAmount.Value) == null || (hfReimburseAmount.Value) == ""))
{
double reimbursedAmount = 0.00;
double amountToBeReimbursed = 0.00;
double balanceCarriedForward = 0.00;
reimbursedAmount = Convert.ToDouble(hfReimburseAmount.Value);
amountToBeReimbursed = Convert.ToDouble(AmountToBeReimbursed.Text.Trim());
balanceCarriedForward = Convert.ToDouble(PCBAvailableBalance.Text.Trim()) + reimbursedAmount;
hfBCF.Value=balanceCarriedForward.ToString();
if (Page.IsPostBack)
{
//insertPCRDetails();
if (cmdSubmit.CommandName.ToLower() == "SAVE".ToLower())
{
core_petty_cash_reimbursement pcrObj = new core_petty_cash_reimbursement();
core_petty_cash_reimbursement_Objects objPCR = new core_petty_cash_reimbursement_Objects();
objPCR.Id_Petty_Cash_Book = Convert.ToInt32(ddlPCBName.SelectedValue);
objPCR.Amount_ThatShouldBe_Reimbursed = Convert.ToDouble(AmountToBeReimbursed.Text.Trim());
objPCR.Reimbursement_Amount = Convert.ToDouble(reimburseAmount.Text.Trim());
objPCR.Balance_Carried_Forward = Convert.ToDouble(hfBCF.Value);
objPCR.Petty_Cash_Reimbursement_Remark = PCR_Remark.Text.Trim();
objPCR.Created_Date = DateTime.Now;
pcrObj.Insert(objPCR);
ScriptManager.RegisterStartupScript(this, GetType(), "insert", "showAlert('success');", true);
}
/////// added code below
if (cmdSubmit.CommandName.ToLower() == "SAVE".ToLower())
{
core_ledger_entry leObj = new core_ledger_entry();
core_ledger_entry_Objects objLE = new core_ledger_entry_Objects();
objLE.Id_Petty_Cash_Book = Convert.ToInt32(ddlPCBName.SelectedValue);
objLE.Id_Petty_Cash_Category = 42;
objLE.Id_Ledger_Account = 6;
//objLE.Id_Petty_Cash_Voucher = 0;
objLE.Business_Purpose = "Reimbursement";
//double x = Convert.ToDouble(hfIDLedgerAccountBCF.Value);
//double y = Convert.ToDouble(cashOutAmount.Text);
objLE.Credit_Amount = Convert.ToDouble(reimburseAmount.Text.Trim());
objLE.Balance_Carried_Forward = Convert.ToDouble(hfBCF.Value);
//double z = (x + y);
objLE.Created_Date = DateTime.Now;
//objLE.Post_Payment = payment_Type;
leObj.Insert(objLE);
}
//////////
/*if (cmdSubmit.CommandName.ToLower() == "SAVE".ToLower())
{
core_petty_cash_payment_voucher pcvObj = new core_petty_cash_payment_voucher();
core_petty_cash_payment_voucher_Objects objPCV = new core_petty_cash_payment_voucher_Objects();
//objPCV.Id_Petty_Cash_Voucher = Convert.ToInt32(PCV_Id.Text);
objPCV.Id_Petty_Cash_Book = Convert.ToInt32(ddlPCBName.SelectedValue);
objPCV.Id_Petty_Cash_Category = 42;
double result;
if (double.TryParse(PCBAvailableBalance.Text, out result))
objPCV.Available_Balance_Amount = Convert.ToDouble(PCBAvailableBalance.Text);
objPCV.Business_Purpose = "Reimbursement";
//objPCV.Cash_Out_Amount = Convert.ToDouble(cashOutAmount.Text);
objPCV.Received_By = "Shan";
//objPCV.Petty_Cash_Voucher_Remark = PCV_Remark.Text;
objPCV.Created_Date = DateTime.Now;
objPCV.Edited_Date = DateTime.Now;
//objPCV.Post_Payment = payment_Type;
objPCV.Is_Active = 1;
pcvObj.Insert(objPCV);
}*/
//////////
/////// added code ends
//updatePCBTable();
core_petty_cash_book pcbObj = new core_petty_cash_book();
pcbObj.UpdateMethod(@"UPDATE `core_petty_cash_book` SET `Available_Balance_Amount` = '" + Convert.ToDouble(hfBCF.Value) + @"' WHERE `Id_Petty_Cash_Book` = '" + Convert.ToInt32(ddlPCBName.SelectedValue) + @"' AND `Is_Active` = '0';");
clearFields();
Response.Redirect("PettyCashReimbursement.aspx", false);
}
}
else
{
//Message.Text
}
clearFields();
loadGrid();
}
private void insertPCVDetails()
{
int payment_Type;
if (selectedPost.Selected == true)
{
payment_Type = 0;
}
else if (selectedPre.Selected == true)
{
payment_Type = 1;
}
else
{
payment_Type = 2;
MessageBox.Show("Error! Please Select a Payment Type");
}
if (cmdSubmit.CommandName.ToLower() == "SAVE".ToLower())
{
core_petty_cash_payment_voucher pcvObj = new core_petty_cash_payment_voucher();
core_petty_cash_payment_voucher_Objects objPCV = new core_petty_cash_payment_voucher_Objects();
objPCV.Id_Petty_Cash_Voucher = Convert.ToInt32(PCV_Id.Text);
objPCV.Id_Petty_Cash_Book=Convert.ToInt32(ddlPCBName.SelectedValue);
objPCV.Id_Petty_Cash_Category = Convert.ToInt32(ddlPCCName.SelectedValue);
double result;
if(double.TryParse(PCBAvailableBalance.Text,out result))
objPCV.Available_Balance_Amount = Convert.ToDouble(PCBAvailableBalance.Text);
objPCV.Business_Purpose=businessPurpose.Text;
objPCV.Cash_Out_Amount=Convert.ToDouble(cashOutAmount.Text);
objPCV.Received_By=receivedBy.Text;
objPCV.Petty_Cash_Voucher_Remark=PCV_Remark.Text;
objPCV.Created_Date=DateTime.Now;
objPCV.Edited_Date = DateTime.Now;
objPCV.Post_Payment= payment_Type;
pcvObj.Insert(objPCV);
}
if (cmdSubmit.CommandName.ToLower() == "SAVE".ToLower())
{
double availableBalance;
double cashoutamount;
availableBalance = 0.00;
cashoutamount = 0.00;
availableBalance =Convert.ToDouble(PCBAvailableBalance.Text);
cashoutamount = Convert.ToDouble(cashOutAmount.Text.Trim());
double newAmount = Convert.ToDouble(availableBalance - cashoutamount);
core_petty_cash_book pcbObj = new core_petty_cash_book();
pcbObj.UpdateMethod(@"UPDATE `core_petty_cash_book`
SET
`Available_Balance_Amount` = '" + newAmount + @"'
WHERE `Id_Petty_Cash_Book` = '" + Convert.ToInt32(ddlPCBName.SelectedValue) + @"';");
}
if (cmdSubmit.CommandName.ToLower() == "SAVE".ToLower())
{
core_ledger_entry leObj = new core_ledger_entry();
core_ledger_entry_Objects objLE = new core_ledger_entry_Objects();
objLE.Id_Petty_Cash_Book = Convert.ToInt32(ddlPCBName.SelectedValue);
objLE.Id_Petty_Cash_Category = Convert.ToInt32(ddlPCCName.SelectedValue);
objLE.Id_Ledger_Account = Convert.ToInt32(hfIDLedgerAccount.Value);
objLE.Id_Petty_Cash_Voucher = Convert.ToInt32(PCV_Id.Text);
objLE.Business_Purpose = businessPurpose.Text;
//double x = Convert.ToDouble(hfIDLedgerAccountBCF.Value);
//////
double x = Convert.ToDouble(PCBAvailableBalance.Text);
//////
double y = Convert.ToDouble(cashOutAmount.Text);
objLE.Debit_Amount = Convert.ToDouble(cashOutAmount.Text);
objLE.Balance_Carried_Forward = (x-y);
//double z = (x + y);
objLE.Created_Date = DateTime.Now;
objLE.Post_Payment = payment_Type;
leObj.Insert(objLE);
//core_ledger_account laObj = new core_ledger_account();
//laObj.UpdateMethod(@"UPDATE `core_ledger_account`
// SET
// `Balance_Carried_Forward` = '" + z + @"'
// WHERE `Id_Ledger_Account` = '" + Convert.ToInt32(hfIDLedgerAccount.Value) + @"';");
}
ScriptManager.RegisterStartupScript(this, GetType(), "insert", "showAlert('success');", true);
//this.clearFields();
//loadGrid();
//Response.Redirect("PettyCashVoucher.aspx", false);
}
private void insertUpdatedVoucherDetails()
{
if (cmdSubmit.CommandName.ToLower() == "SAVE".ToLower())
{
core_petty_cash_settlement pcvsObj = new core_petty_cash_settlement();
core_petty_cash_settlement_Objects objPCVS = new core_petty_cash_settlement_Objects();
objPCVS.Id_Petty_Cash_Voucher = Convert.ToInt32(idPrePV.Text);
//objPCVS.Id_Petty_Cash_Book = Convert.ToInt32(ddlPCBName.Text);
objPCVS.Id_Petty_Cash_Book = Convert.ToInt32(hfPCBID.Value);
//objPCVS.Id_Petty_Cash_Category = Convert.ToInt32(ddlPCCName.Text);
objPCVS.Id_Petty_Cash_Category = Convert.ToInt32(hfPCCID.Value);
objPCVS.Cash_Out_Amount = Convert.ToDouble(cashOutAmount.Text);
objPCVS.Cash_In_Amount = Convert.ToDouble(cashInAmount.Text);
objPCVS.Net_Cash_Out_Amount = Convert.ToDouble(hfnetcashOutAmount.Value);
objPCVS.Petty_Cash_Voucher_Remark = PCV_Remark.Text;
objPCVS.Created_Date = DateTime.Now;
pcvsObj.Insert(objPCVS);
//Inserting Ledger Entry & Updating Ledger Account BCF
core_ledger_entry leObj = new core_ledger_entry();
core_ledger_entry_Objects objLE = new core_ledger_entry_Objects();
objLE.Id_Petty_Cash_Book = Convert.ToInt32(hfPCBID.Value);
objLE.Id_Petty_Cash_Category = Convert.ToInt32(hfPCCID.Value);
objLE.Id_Ledger_Account = Convert.ToInt32(hfIDLedgerAccount.Value);
objLE.Id_Petty_Cash_Voucher = Convert.ToInt32(idPrePV.Text);
double x = Convert.ToDouble(hfIDLedgerAccountBCF.Value);
double y = Convert.ToDouble(cashInAmount.Text);
objLE.Credit_Amount = Convert.ToDouble(cashInAmount.Text);
objLE.Balance_Carried_Forward = (x - y);
double z = (x - y);
objLE.Created_Date = DateTime.Now;
objLE.Post_Payment = 1;
leObj.Insert(objLE);
core_ledger_account laObj = new core_ledger_account();
laObj.UpdateMethod(@"UPDATE `core_ledger_account`
SET
`Balance_Carried_Forward` = '" + z + @"'
WHERE `Id_Ledger_Account` = '" + Convert.ToInt32(hfIDLedgerAccount.Value) + @"';");
}
clearFields();
loadGrid();
}
