private void btnOk_Click(object sender, EventArgs e) { try { //-------For Select Opening Date ------- DateTime Cashopdate = dtpfrom.Value.Date; decimal CashopBal = 0; c.getconnstr(); c.returnconn(c.myconn); SqlDataReader dr = null; con = c.myconn; dr = c.fillreader(ref dr, "select isnull(OPE_BLD,0) OPE_BLD ,OpenDate from tbl_LedgerAcc where BHM_COD='" + valcmbcash.SelectedValue + "'", con); if (dr.HasRows == true) { dr.Read(); CashopBal = Convert.ToDecimal(dr["OPE_BLD"]); Cashopdate = Convert.ToDateTime(dr["OpenDate"]); } dr.Close(); //-------For Select Opening Balance ------- c.getconnstr(); c.returnconn(c.myconn); con = c.myconn; dr = c.fillreader(ref dr, "select ISNULL(a.Receipt,0) as receipt,ISNULL(a.Payment ,0) as payment,a.balance from (SELECT SUM((case when a.Amount > 0 then a.Amount else 0 end)) AS Receipt,SUM((case when a.Amount < 0 then a.Amount else 0 end)) AS Payment,0 as balance FROM tbl_VoucherDetail AS a Where a.CashBankNo=0 and a.VchDate>='" + Cashopdate.Date + "' and a.VchDate<'" + dtpfrom.Value.ToShortDateString() + "' and a.AccCode=" + valcmbcash.SelectedValue + " and a.YearNo=" + school.CurrentSessionCode + ") a ", con); decimal Cashrec = 0; decimal Cashpay = 0; if (dr.HasRows == true) { dr.Read(); Cashrec = Convert.ToDecimal(dr["receipt"]); Cashpay = Convert.ToDecimal(dr["payment"]); } CashopBal = CashopBal + Cashrec - Cashpay; dr.Close(); //--------------insert the opening balance------- c.getconnstr(); c.returnconn(c.myconn); SqlTransaction trn; trn = c.myconn.BeginTransaction(); string mysql; mysql = "delete from tbl_Ledger"; c.connectsql(mysql, c.myconn, trn); if (dtpfrom.Value.Date < Cashopdate.Date) { dtpfrom.Value = Cashopdate.Date; } mysql = " Insert into tbl_Ledger (YearNo,vchdate,accname,Reciept,Payment,balance) "; if (CashopBal > 0) { Cashrec = CashopBal; Cashpay = 0; } else { Cashrec = 0; Cashpay = CashopBal; } mysql = mysql + " values (" + school.CurrentSessionCode + ",'" + dtpfrom.Value + "' ,'Opening Balance'," + Cashrec + "," + Cashpay + "," + CashopBal + ")"; c.connectsql(mysql, c.myconn, trn); //trn.Commit(); //--------------Get all voucher of the day - from date------- //m.getconnstr(); //m.returnconn(m.myconn); //trn = m.myconn.BeginTransaction(); mysql = "insert into tbl_Ledger (yearno,accno,accname,vchno,vchdate,vchtype,remark,vchamt,Reciept,Payment) "; mysql = mysql + " SELECT 0 as yearno,a.AccCode as Accno,c.bhm_desh as accname, b.VouchNo, b.VchDate,b.VchType,remark ,a.Amount as vchamt,"; mysql = mysql + " (case when a.Amount < 0 then -1*a.Amount else 0 end) AS Reciept,(case when (a.Amount) >= 0 then a.Amount else 0 end) AS Payment "; mysql = mysql + " FROM tbl_VoucherDetail AS a, tbl_Voucher AS b,tbl_LedgerAcc c Where a.VchType in ('CV','CP','CR') and "; mysql = mysql + " a.VchDate>='" + dtpfrom.Value.Date + "' and a.VchDate<='" + dtpto.Value.Date + "' and a.YearNo='" + school.CurrentSessionCode + "' and "; mysql = mysql + " a.VouchNo = b.VouchNo and a.VchType = b.VchType and a.AccCode=c.BHM_COD and a.CashBankNo<>0 "; mysql = mysql + " order by b.VchDate,b.VouchNo,b.VchType "; c.connectsql(mysql, c.myconn, trn); trn.Commit(); //--------------Crystel Report------- //--------------Crystel Report------- string str1 = "select '',' From " + dtpfrom.Text + " to " + dtpto.Text + "' as period,* from tbl_Ledger order by VchDate,VchNo,VchType"; DataSet ds = new DataSet(); ds.Clear(); str1 = str1 + " SELECT Schoolname + ' - ( ' + Schoolcity + ' ) ' as Schoolname, Schoolcity FROM tbl_School"; ds = Connection.GetDataSet(str1); ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\CashBook.xsd"); RptCashBook cr = new RptCashBook(); cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait; cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter; cr.SetDataSource(ds); ShowAllReports s = new ShowAllReports(); s.crystalReportViewer1.ReportSource = cr; s.Show(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void btnsave_Click(object sender, EventArgs e) { try { int ricept; int Payment; ricept = Convert.ToInt32(txtReciept.Text); Payment = Convert.ToInt32(txtPayment.Text); if (ricept != Payment) { MessageBox.Show("Payment Amount and Reciept Amount Does not Match...Entry Cannot Saved.."); } else { c.getconnstr(); c.returnconn(c.myconn); txtvchtype.Text = "JV"; if (add_edit == true) { SqlConnection con = c.myconn; DateTime dt = dtp.Value; Int32 VchNo = c.getvchno(txtvchtype.Text, ref dt, con); vouchNo = c.getvouchernumber(txtvchtype.Text, ref dt, con); //txtvchtype.Text + dtp.Value.Date.ToString("ddMMyyyy") + VchNo; txtvoucherno.Text = vouchNo; SqlTransaction trn; trn = c.myconn.BeginTransaction(); try { string mysql; mysql = "insert into tbl_Voucher(yearno,VchNo,VouchNo,VchType,VchDate,Remark,status)values('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + dtp.Value.Date + "','" + txtRemark.Text + "','AE')"; c.connectsql(mysql, c.myconn, trn); for (int i = 0; i < dataGridView1.Rows.Count; i++) { decimal pay = Convert.ToInt32(dataGridView1.Rows[i].Cells["Payment"].Value); decimal rec = Convert.ToInt32(dataGridView1.Rows[i].Cells["Reciept"].Value); if (pay == 0) { mysql = "insert into tbl_VoucherDetail (yearno,vchno,VouchNo,VchType,AccCode,VchDate,Amount) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + dataGridView1.Rows[i].Cells["ACCOUNT"].Value.ToString() + "','" + dtp.Value.Date + "','" + rec + "')"; } else { mysql = "insert into tbl_VoucherDetail (yearno,vchno,VouchNo,VchType,AccCode,VchDate,Amount) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + dataGridView1.Rows[i].Cells["ACCOUNT"].Value.ToString() + "','" + dtp.Value.Date + "','" + -1 * pay + "')"; } c.connectsql(mysql, c.myconn, trn); } trn.Commit(); MessageBox.Show("Record Saved..."); } catch (Exception ex) { MessageBox.Show(ex.Message); trn.Rollback(); } } if (add_edit == false) { c.getconnstr(); c.returnconn(c.myconn); SqlDataReader dr = null; con = c.myconn; dr = c.fillreader(ref dr, "select VchNo from tbl_Voucher where vchtype='JV' and VouchNo='" + txtvoucherno.Text + "'", con); if (dr.HasRows == true) { dr.Read(); VchNo = Convert.ToInt16(dr[0]); if (dr[1] == "AE") { MessageBox.Show("Voucher Not Made from Account Entry..."); dr.Close(); goto mline; } } dr.Close(); SqlTransaction trn; trn = c.myconn.BeginTransaction(); try { string mysql; mysql = "delete from tbl_Voucher where vchtype='JV' and VouchNo='" + txtvoucherno.Text + "'"; c.connectsql(mysql, c.myconn, trn); mysql = "delete from tbl_VoucherDetail where vchtype='JV' and VouchNo='" + txtvoucherno.Text + "'"; c.connectsql(mysql, c.myconn, trn); for (int i = 0; i < dataGridView1.Rows.Count; i++) { decimal pay = Convert.ToInt32(dataGridView1.Rows[i].Cells["Payment"].Value); decimal rec = Convert.ToInt32(dataGridView1.Rows[i].Cells["Reciept"].Value); if (pay == 0) { mysql = "insert into tbl_VoucherDetail (yearno,vchno,VouchNo,VchType,AccCode,VchDate,Amount) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + dataGridView1.Rows[i].Cells["ACCOUNT"].Value.ToString() + "','" + dtp.Value.Date + "'," + rec + "')"; } else { mysql = "insert into tbl_VoucherDetail (yearno,vchno,VouchNo,VchType,AccCode,VchDate,Amount) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + dataGridView1.Rows[i].Cells["ACCOUNT"].Value.ToString() + "','" + dtp.Value.Date + "'," + -1 * pay + "')"; } c.connectsql(mysql, c.myconn, trn); } trn.Commit(); MessageBox.Show("Record Update..."); } catch (Exception ex) { MessageBox.Show(ex.Message); trn.Rollback(); } trn.Dispose(); } mline: c.GetMdiParent(this).EnableAllEditMenuButtons(); DesignForm.fromDesign1(this); } } catch { } }
public void btnsave_Click(object sender, EventArgs e) { if (valcmbbank.Text == "" && valcmbaccountgroup.Text == "" && txtPaidAmt.Text == "" && txtNarration.Text == "") { MessageBox.Show("Null Value Not Allowed "); } else { int Bank1; int Bank2; Bank1 = Convert.ToInt32(valcmbaccountgroup.SelectedValue); Bank2 = Convert.ToInt32(valcmbbank.SelectedValue); if (Bank1 == Bank2) { MessageBox.Show("Both Accounts are same...."); goto mline; } c.returnconn(c.myconn); txtvchtype.Text = "BP"; if (add_edit == true) { SqlConnection con = c.myconn; DateTime dt = dtp.Value; Int32 VchNo = c.getvchno(txtvchtype.Text, ref dt, con); vouchNo = c.getvouchernumber(txtvchtype.Text, ref dt, con); //txtvchtype.Text + dtp.Value.Date.ToString("ddMMyyyy") + VchNo; txtvoucherno.Text = vouchNo; SqlTransaction trn; trn = c.myconn.BeginTransaction(); try { string mysql; mysql = "insert into tbl_Voucher(YearNo,VchNo,VouchNo,VchType,VchDate,Remark,Status)values('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + dtp.Value.Date + "','" + txtNarration.Text + "','AE')"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_VoucherDetail (YearNo,vchno,VouchNo,VchType,AccCode,VchDate,Amount,CashBankNo,ChequeNo,ChequeDate) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + valcmbbank.SelectedValue + "','" + dtp.Value.Date + "'," + -1 * Convert.ToDecimal(txtPaidAmt.Text) + ",0,'" + txtChkNo.Text + "','" + txtChkDate.Value + "')"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_VoucherDetail (YearNo,vchno,VouchNo,VchType,AccCode,VchDate,Amount,CashBankNo,ChequeNo,ChequeDate) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + valcmbaccountgroup.SelectedValue + "','" + dtp.Value.Date + "','" + txtPaidAmt.Text + "','" + valcmbbank.SelectedValue + "','" + txtChkNo.Text + "','" + txtChkDate.Value + "')"; c.connectsql(mysql, c.myconn, trn); trn.Commit(); MessageBox.Show("Record Saved..."); } catch (Exception ex) { MessageBox.Show(ex.Message); trn.Rollback(); } trn.Dispose(); } if (add_edit == false) { c.getconnstr(); c.returnconn(c.myconn); SqlDataReader dr = null; con = c.myconn; dr = c.fillreader(ref dr, "select VchNo,Status from tbl_Voucher where vchtype='BP' and VouchNo='" + txtvoucherno.Text + "'", con); if (dr.HasRows == true) { dr.Read(); VchNo = Convert.ToInt16(dr[0]); if (dr[1] == "AE") { MessageBox.Show("Voucher Not Made from Account Entry..."); dr.Close(); goto mline; } } dr.Close(); c.returnconn(c.myconn); SqlTransaction trn; trn = c.myconn.BeginTransaction(); try { string mysql; mysql = "delete from tbl_Voucher where vchtype='BP' and VouchNo='" + txtvoucherno.Text + "'"; c.connectsql(mysql, c.myconn, trn); mysql = "delete from tbl_VoucherDetail where vchtype='BP' and VouchNo='" + txtvoucherno.Text + "'"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_Voucher(YearNo,VchNo,VouchNo,VchType,VchDate,Remark,Status)values('" + school.CurrentSessionCode + "','" + VchNo + "','" + txtvoucherno.Text + "','" + txtvchtype.Text + "','" + dtp.Value.Date + "','" + txtNarration.Text + "','AE')"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_VoucherDetail (YearNo,vchno,VouchNo,VchType,AccCode,VchDate,Amount,CashBankNo,ChequeNo,ChequeDate) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + valcmbbank.SelectedValue + "','" + dtp.Value.Date + "'," + -1 * Convert.ToDecimal(txtPaidAmt.Text) + ",0,'" + txtChkNo.Text + "','" + txtChkDate.Value + "')"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_VoucherDetail (YearNo,vchno,VouchNo,VchType,AccCode,VchDate,Amount,CashBankNo,ChequeNo,ChequeDate) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + valcmbaccountgroup.SelectedValue + "','" + dtp.Value.Date + "','" + txtPaidAmt.Text + "','" + valcmbbank.SelectedValue + "','" + txtChkNo.Text + "','" + txtChkDate.Value + "')"; c.connectsql(mysql, c.myconn, trn); trn.Commit(); MessageBox.Show("Record Update..."); } catch (Exception ex) { MessageBox.Show(ex.Message); trn.Rollback(); } trn.Dispose(); } mline: c.GetMdiParent(this).EnableAllEditMenuButtons(); DesignForm.fromDesign1(this); } }
public void btnsave_Click(object sender, EventArgs e) { int BHM_DES = 0; SqlCommand command1 = new SqlCommand("select BHM_COD from tbl_LedgerAcc where BHM_COD=1", c.myconn); SqlDataReader reader1 = command1.ExecuteReader(); int i = 0; if (reader1.HasRows) { reader1.Read(); BHM_DES = Convert.ToInt16(reader1["BHM_COD"]); if (BHM_DES != 1) { MessageBox.Show("Cannot Select Cash Account.."); reader1.Close(); goto mline; } } c.returnconn(c.myconn); txtvchtype.Text = "CR"; if (add_edit == true) { SqlConnection con = c.myconn; DateTime dt = dtp.Value; Int32 VchNo = c.getvchno(txtvchtype.Text, ref dt, con); vouchNo = c.getvouchernumber(txtvchtype.Text, ref dt, con); //txtvchtype.Text + dtp.Value.Date.ToString("ddMMyyyy") + VchNo; txtvoucherno.Text = vouchNo; SqlTransaction trn; trn = c.myconn.BeginTransaction(); try { string mysql; mysql = "insert into tbl_Voucher(YearNo,VchNo,VouchNo,VchType,VchDate,Remark)values('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + dtp.Value.Date + "','" + txtRemark.Text + "')"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_VoucherDetail (YearNo,vchno,VouchNo,VchType,AccCode,VchDate,Amount,cashbankno) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + BHM_DES + "','" + dtp.Value.Date + "'," + txtReceiptAmt.Text + ",0)"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_VoucherDetail (YearNo,vchno,VouchNo,VchType,AccCode,VchDate,Amount,cashbankno) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + valcmbaccountgroup.SelectedValue + "','" + dtp.Value.Date + "'," + -1 * Convert.ToDecimal(txtReceiptAmt.Text) + ",'" + BHM_DES + "')"; c.connectsql(mysql, c.myconn, trn); trn.Commit(); MessageBox.Show("Record Saved..."); } catch (Exception ex) { MessageBox.Show(ex.Message); trn.Rollback(); } trn.Dispose(); } if (add_edit == false) { c.getconnstr(); c.returnconn(c.myconn); SqlDataReader dr = null; con = c.myconn; dr = c.fillreader(ref dr, "select VchNo from tbl_Voucher where vchtype='CR' and VouchNo='" + txtvoucherno.Text + "'", con); if (dr.HasRows == true) { dr.Read(); VchNo = Convert.ToInt16(dr[0]); } dr.Close(); SqlTransaction trn; trn = c.myconn.BeginTransaction(); try { string mysql; mysql = "delete from tbl_Voucher where vchtype='CR' and VouchNo='" + txtvoucherno.Text + "'"; c.connectsql(mysql, c.myconn, trn); mysql = "delete from tbl_VoucherDetail where vchtype='CR' and VouchNo='" + txtvoucherno.Text + "'"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_Voucher(YearNo,VchNo,VouchNo,VchType,VchDate,Remark)values('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + dtp.Value.Date + "','" + txtRemark.Text + "')"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_VoucherDetail (YearNo,vchno,VouchNo,VchType,AccCode,VchDate,Amount,cashbankno) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + BHM_DES + "','" + dtp.Value.Date + "'," + txtReceiptAmt.Text + "," + BHM_DES + ")"; c.connectsql(mysql, c.myconn, trn); mysql = "insert into tbl_VoucherDetail (YearNo,vchno,VouchNo,VchType,AccCode,VchDate,Amount,cashbankno) values ('" + school.CurrentSessionCode + "','" + VchNo + "','" + vouchNo + "','" + txtvchtype.Text + "','" + valcmbaccountgroup.SelectedValue + "','" + dtp.Value.Date + "'," + -1 * Convert.ToDecimal(txtReceiptAmt.Text) + ",0)"; c.connectsql(mysql, c.myconn, trn); trn.Commit(); MessageBox.Show("Record Update..."); } catch (Exception ex) { MessageBox.Show(ex.Message); trn.Rollback(); } trn.Dispose(); } mline: c.GetMdiParent(this).EnableAllEditMenuButtons(); DesignForm.fromDesign1(this); }