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