private void btnOk_Click(object sender, EventArgs e) { try { DateTime cashopdate = dtpfrom.Value.Date; DateTime bankopdate = dtpfrom.Value.Date; decimal cashopbal = 0; decimal bankopbal = 0; c.returnconn(c.myconn); string mysql; mysql = "select isnull(acopbal,0) acopbal ,opdate from tbl_account where accode=" + valcmbcash.SelectedValue; SqlCommand com; com = new SqlCommand(mysql, c.myconn); SqlDataReader reader = com.ExecuteReader(); int i = 0; if (reader.HasRows) { reader.Read(); cashopbal = Convert.ToDecimal(reader["acopbal"]); cashopdate = Convert.ToDateTime(reader["opdate"]); } reader.Close(); //--------------------------- mysql = "select isnull(acopbal,0) acopbal ,opdate from tbl_account where accode=" + valcmbbank.SelectedValue; //SqlCommand com; com = new SqlCommand(mysql, c.myconn); reader = com.ExecuteReader(); i = 0; if (reader.HasRows) { reader.Read(); bankopbal = Convert.ToDecimal(reader["acopbal"]); bankopdate = Convert.ToDateTime(reader["opdate"]); } reader.Close(); //--------------------------- string str = " SELECT isnull(sum(case a.amttype when 'Dr' then a.vchamt else 0 end),0) AS Receipt, isnull(sum(case a.amttype when 'Cr' then a.vchamt else 0 end),0) AS Payment,"; str = str + " 0 as balance FROM tbl_voucherdet AS a "; str = str + " Where a.vchdate>='" + cashopdate.Date + "'"; str = str + " and a.vchdate<'" + dtpfrom.Value.ToShortDateString() + "' and a.accode=" + valcmbcash.SelectedValue + " and a.sessioncode=" + school.CurrentSessionCode + " "; com = new SqlCommand(str, c.myconn); reader = com.ExecuteReader(); i = 0; decimal mcashrec = 0; decimal mcashpay = 0; if (reader.HasRows) { reader.Read(); mcashrec = Convert.ToDecimal(reader["receipt"]); mcashpay = Convert.ToDecimal(reader["payment"]); } cashopbal = cashopbal + mcashrec - mcashpay; reader.Close(); //------------------------ str = " SELECT isnull(sum(case a.amttype when 'Dr' then a.vchamt else 0 end),0) AS Receipt, isnull(sum(case a.amttype when 'Cr' then a.vchamt else 0 end),0) AS Payment,"; str = str + " 0 as balance FROM tbl_voucherdet AS a "; str = str + " Where a.vchdate>='" + bankopdate.Date + "'"; str = str + " and a.vchdate<'" + dtpfrom.Value.ToShortDateString() + "' and a.accode=" + valcmbbank.SelectedValue + " and a.sessioncode=" + school.CurrentSessionCode + " "; com = new SqlCommand(str, c.myconn); reader = com.ExecuteReader(); i = 0; decimal mbankrec = 0; decimal mbankpay = 0; if (reader.HasRows) { reader.Read(); mbankrec = Convert.ToDecimal(reader["receipt"]); mbankpay = Convert.ToDecimal(reader["payment"]); } bankopbal = bankopbal + mbankrec - mbankpay; reader.Close(); //---------------- SqlTransaction trn; trn = c.myconn.BeginTransaction(); c.connectsql("delete from tbl_cashbook", c.myconn, trn); DateTime mopdate = cashopdate.Date; if (dtpfrom.Value.Date < cashopdate.Date) { dtpfrom.Value = cashopdate.Date; } //insert the opening balance str = " Insert into tbl_cashbook (recno,r_date,r_particulars,r_cash,r_bank,isbold) "; str = str + " values (0,'" + dtpfrom.Text + "' ,'Open. Bal.'," + cashopbal + "," + bankopbal + ",'Y')"; c.connectsql(str, c.myconn, trn); trn.Commit(); //--------------------------------- //get all voucher of the day - fromdate //str = " SELECT a.accode as accno,b.vchno, b.vchdate,b.vchtype, isnull(b.remarks,'') as narration,a.amttype , "; //str = str + " (case a.amttype when 'Cr' then a.vchamt else 0 end) AS Payment, (case a.amttype when 'Dr' then a.vchamt else 0 end) AS Receipt,c.acname,isnull(c.lf,'') as lf,c.actype,a.basicsal,isnull(a.vchnarr,'') as vchnarr,isnull(b.entrytype,'') as entrytype,isnull(a.salarytype,'') as salarytype "; //str = str + " FROM tbl_voucherdet AS a, tbl_voucher AS b,tbl_account c Where a.vchdate='"+ dtpfrom.Value.ToShortDateString() +"'"; //str = str + " and a.sessioncode=" + school.CurrentSessionCode + " and a.vchno = b.vchno and a.VCHTYPE = b.VCHTYPE and a.accode=c.accode order by b.entrytype desc,a.vchtype,vchno,a.entryno"; str = " SELECT a.accode as accno,b.vchno, b.vchdate,b.vchtype, isnull(b.remark,'') as narration,a.amttype , "; str = str + " (case a.amttype when 'Dr' then a.vchamt else 0 end) AS Payment, (case a.amttype when 'Cr' then a.vchamt else 0 end) AS Receipt,( select top 1 (select top 1 acname from tbl_account where accode=ia.accode ) from tbl_voucherdet ia where ia.accode<>dbo.GetAccountCode('CS') and ia.vchno=b.vchno and ia.vchtype=a.vchtype ) as acname ,isnull(c.lf,'') as lf,c.actype,isnull(a.basicsal,0) as basicsal,isnull(a.vchnarr,'') as vchnarr,isnull(null,'') as entrytype,isnull(a.salarytype,'') as salarytype "; str = str + " FROM tbl_voucherdet AS a, tbl_voucher AS b,tbl_account c Where (a.vchtype='JV' "; str = str + " or (a.vchtype='CP' and a.amttype='Dr') or (a.vchtype='CR' and a.amttype='Cr')) and a.vchdate='" + dtpfrom.Value.ToShortDateString() + "'"; str = str + " and a.sessioncode=" + school.CurrentSessionCode + " and a.vchno = b.vchno and a.VCHTYPE = b.VCHTYPE and a.accode=c.accode and a.accode<>dbo.GetAccountCode('CS') order by a.vchtype,vchno,a.entryno"; DataSet ds = new DataSet(); c.filldataset(false, ref ds, str, c.myconn); if (ds.Tables[0].Rows.Count > 0) { int rwcnt = 0; int linecnt = 1; decimal deduct = 0; decimal pcash = 0; decimal pbank = 0; decimal rcash = 0; decimal rbank = 0; bool issubfound = false; string isline = "N"; string basicparticulars; string basicpay; string vchtype = ""; bool rnarr = false; bool pnarr = false; int cplineno = 0; int crlineno = 0; int bplineno = 0; int brlineno = 0; int subcnt = 0; int pline = 1; int rline = 1; basicparticulars = ""; basicpay = ""; while (rwcnt < ds.Tables[0].Rows.Count) { issubfound = false; isline = "N"; vchtype = Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]); if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "JV") { if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Cr") { if (rnarr == false) { if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["Entrytype"]) == "S") //salary addition/deduction voucher { trn = c.myconn.BeginTransaction(); //str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_details) values (" + linecnt + ",'"+ds.Tables[0].Rows[rwcnt]["vchno"]+"','" + ds.Tables[0].Rows[rwcnt]["narration"] + " - Deductions " + "',' ' )"; str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_details) values (" + linecnt + ",'','" + ds.Tables[0].Rows[rwcnt]["narration"] + " - Deductions " + "',' ' )"; c.connectsql(str, c.myconn, trn); trn.Commit(); linecnt++; rnarr = true; //means only only one time print } } str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where b.amttype='Cr' and a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'"; DataSet ds1 = new DataSet(); c.filldataset(false, ref ds1, str, c.myconn); subcnt = 0; if (ds1.Tables[0].Rows.Count > 0) { while (subcnt < ds1.Tables[0].Rows.Count) { trn = c.myconn.BeginTransaction(); str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details) values (" + (linecnt) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + " - " + ds1.Tables[0].Rows[subcnt]["narration"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")"; c.connectsql(str, c.myconn, trn); trn.Commit(); issubfound = true; linecnt++; subcnt++; } } if (issubfound == true) { isline = "Y"; } if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["basicsal"]) == "Y") { basicparticulars = Convert.ToString(ds.Tables[0].Rows[rwcnt]["acname"]); basicpay = Convert.ToString(ds.Tables[0].Rows[rwcnt]["receipt"]); } else { trn = c.myconn.BeginTransaction(); //str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details,isline) values (" + (linecnt + 1) + ",'" + ds.Tables[0].Rows[rwcnt]["lf"] + "','" + ds.Tables[0].Rows[rwcnt]["vchno"] + "','" + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["vchnarr"] + "'," + ds.Tables[0].Rows[rwcnt]["receipt"] + ",'" + isline + "')"; str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details,isline) values (" + (linecnt + 1) + ",'" + ds.Tables[0].Rows[rwcnt]["lf"] + "','','" + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["vchnarr"] + "'," + ds.Tables[0].Rows[rwcnt]["receipt"] + ",'" + isline + "')"; c.connectsql(str, c.myconn, trn); trn.Commit(); linecnt++; } if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["Salarytype"]) == "DE") { deduct = deduct + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]); } } if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Dr") { //seach debit account in subledger //trn = c.myconn.BeginTransaction(); //str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details) values (" + (rwcnt + 1) + ",'" + ds.Tables[0].Rows[rwcnt]["vchno"] + "','" + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["payment"] + ")"; //c.connectsql(str, c.myconn, trn); //trn.Commit(); if (deduct > 0) { trn = c.myconn.BeginTransaction(); str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_details,isline) values (" + (linecnt) + ",'','Less - Deductions ','" + deduct + "','Y')"; c.connectsql(str, c.myconn, trn); trn.Commit(); linecnt++; } if (pnarr == false) { if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["Entrytype"]) == "S") //salary addition/deduction voucher { trn = c.myconn.BeginTransaction(); str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details) values (" + linecnt + ",'','" + ds.Tables[0].Rows[rwcnt]["narration"] + "',' ' )"; c.connectsql(str, c.myconn, trn); trn.Commit(); linecnt++; pnarr = true; //means only only one time print } } str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where b.amttype='Dr' and a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'"; DataSet ds1 = new DataSet(); c.filldataset(false, ref ds1, str, c.myconn); subcnt = 0; if (ds1.Tables[0].Rows.Count > 0) { while (subcnt < ds1.Tables[0].Rows.Count) { trn = c.myconn.BeginTransaction(); str = "insert into tbl_cashbook (recno,p_lf,p_date,p_particulars,p_details) values (" + (linecnt) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")"; c.connectsql(str, c.myconn, trn); trn.Commit(); linecnt++; subcnt++; } } trn = c.myconn.BeginTransaction(); str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details,isline) values (" + (linecnt) + ",'" + ds.Tables[0].Rows[rwcnt]["vchno"] + "','" + ds.Tables[0].Rows[rwcnt]["acname"] + "'," + ds.Tables[0].Rows[rwcnt]["payment"] + ",'Y')"; c.connectsql(str, c.myconn, trn); linecnt++; trn.Commit(); if (deduct > 0) { trn = c.myconn.BeginTransaction(); str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details,isline) values (" + (linecnt) + ",'','Less - Deductions ','" + deduct + "','N')"; c.connectsql(str, c.myconn, trn); trn.Commit(); linecnt++; } if (basicpay != "") { trn = c.myconn.BeginTransaction(); str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details,isline) values (" + (linecnt) + ",'','" + basicparticulars + "'," + basicpay + ",'Y')"; c.connectsql(str, c.myconn, trn); trn.Commit(); linecnt++; } } pline = linecnt; rline = linecnt; } //////////////////////// // Journal Voucher Part Finished /// if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "CR") { if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Cr") { crlineno = 0; trn = c.myconn.BeginTransaction(); if (rline >= pline) { str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_cash) values (" + (rline) + ",'" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "',' From : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["receipt"] + ")"; } else { str = " Update tbl_cashbook set r_date='" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "' , r_particulars = ' From : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "',r_cash=" + ds.Tables[0].Rows[rwcnt]["receipt"] + " where recno = " + rline; } c.connectsql(str, c.myconn, trn); trn.Commit(); rcash = rcash + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]); crlineno = linecnt; rline++; linecnt++; //cashopbal = cashopbal + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]); str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'"; DataSet ds1 = new DataSet(); c.filldataset(false, ref ds1, str, c.myconn); subcnt = 0; if (ds1.Tables[0].Rows.Count > 0) { while (subcnt < ds1.Tables[0].Rows.Count) { trn = c.myconn.BeginTransaction(); if (rline >= pline) { str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details) values (" + (rline) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','To : " + ds1.Tables[0].Rows[subcnt]["acname"] + " -- " + ds1.Tables[0].Rows[subcnt]["narration"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")"; } else { str = "update tbl_cashbook set r_lf='" + ds1.Tables[0].Rows[subcnt]["lf"] + "',r_date='' ,r_particulars = ' To : " + ds1.Tables[0].Rows[subcnt]["acname"] + " -- " + ds1.Tables[0].Rows[subcnt]["narration"] + "',r_details=" + ds1.Tables[0].Rows[subcnt]["subledgamt"] + " where recno=" + rline; } c.connectsql(str, c.myconn, trn); trn.Commit(); rline++; linecnt++; subcnt++; } } if (subcnt > 0) { trn = c.myconn.BeginTransaction(); if (rline >= pline) { str = "insert into tbl_cashbook (recno,r_details,isline) values (" + (linecnt) + ",'" + ds.Tables[0].Rows[rwcnt]["receipt"] + "','Y')"; } else { str = "update tbl_cashbook set r_details='" + ds.Tables[0].Rows[rwcnt]["receipt"] + "',isline='Y' where recno=" + rline; } c.connectsql(str, c.myconn, trn); trn.Commit(); rline++; linecnt++; if (pline > rline) { rline = pline; } else { pline = rline; } } } } //cashopbal = cashopbal - Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]); //cplineno if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "CP") { if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Dr") { cplineno = 0; trn = c.myconn.BeginTransaction(); if (pline >= rline) { str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_cash) values (" + (pline) + ",'" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "',' To : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["payment"] + ")"; } else { str = "update tbl_cashbook set p_date='" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "' ,p_particulars=' To : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "',p_cash=" + ds.Tables[0].Rows[rwcnt]["payment"] + " where recno=" + pline; } c.connectsql(str, c.myconn, trn); trn.Commit(); bplineno = linecnt; pcash = pcash + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]); pline++; linecnt++; //cashopbal = cashopbal - Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]); str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'"; DataSet ds1 = new DataSet(); c.filldataset(false, ref ds1, str, c.myconn); subcnt = 0; if (ds1.Tables[0].Rows.Count > 0) { while (subcnt < ds1.Tables[0].Rows.Count) { trn = c.myconn.BeginTransaction(); if (pline >= rline) { str = "insert into tbl_cashbook (recno,p_lf,p_date,p_particulars,p_details) values (" + (pline) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")"; } else { str = "update tbl_cashbook set recno=" + pline + ",p_lf='" + ds1.Tables[0].Rows[subcnt]["lf"] + "',p_date='' , p_particulars='" + ds1.Tables[0].Rows[subcnt]["acname"] + "',p_details='" + ds1.Tables[0].Rows[subcnt]["subledgamt"] + " where recno=" + pline; } c.connectsql(str, c.myconn, trn); trn.Commit(); pline++; linecnt++; subcnt++; } } if (subcnt > 0) { trn = c.myconn.BeginTransaction(); if (pline > rline) { str = "insert into tbl_cashbook (recno,p_details,isline) values (" + (pline) + ",'" + ds.Tables[0].Rows[rwcnt]["Payment"] + "','Y')"; } else { str = "update tbl_cashbook set p_details=" + ds.Tables[0].Rows[rwcnt]["Payment"] + ",isline='Y' where recno=" + pline; } c.connectsql(str, c.myconn, trn); trn.Commit(); pline++; linecnt++; if (pline > rline) { rline = pline; } else { pline = rline; } } } } /// //for bank receipt /// if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "BR") { if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Cr") { brlineno = 0; trn = c.myconn.BeginTransaction(); if (rline >= pline) { str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_bank) values (" + (rline) + ",'" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "',' From : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["receipt"] + ")"; } else { str = " Update tbl_cashbook set r_date='" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "' , r_particulars = ' From : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "',r_bank=" + ds.Tables[0].Rows[rwcnt]["receipt"] + " where recno = " + rline; } c.connectsql(str, c.myconn, trn); trn.Commit(); brlineno = linecnt; rbank = rbank + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]); rline++; linecnt++; bankopbal = bankopbal + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]); str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'"; DataSet ds1 = new DataSet(); c.filldataset(false, ref ds1, str, c.myconn); subcnt = 0; if (ds1.Tables[0].Rows.Count > 0) { while (subcnt < ds1.Tables[0].Rows.Count) { trn = c.myconn.BeginTransaction(); //str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details) values (" + (linecnt) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + " -- "+ds1.Tables[0].Rows[subcnt]["narration"] +"'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")"; if (rline >= pline) { str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details) values (" + (rline) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','To : " + ds1.Tables[0].Rows[subcnt]["acname"] + " -- " + ds1.Tables[0].Rows[subcnt]["narration"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")"; } else { str = "update tbl_cashbook set r_lf='" + ds1.Tables[0].Rows[subcnt]["lf"] + "',r_date='' ,r_particulars = ' To : " + ds1.Tables[0].Rows[subcnt]["acname"] + " -- " + ds1.Tables[0].Rows[subcnt]["narration"] + "',r_details=" + ds1.Tables[0].Rows[subcnt]["subledgamt"] + " where recno=" + rline; } c.connectsql(str, c.myconn, trn); trn.Commit(); rline++; linecnt++; subcnt++; } } if (subcnt > 0) { trn = c.myconn.BeginTransaction(); if (rline >= pline) { str = "insert into tbl_cashbook (recno,r_details,isline) values (" + (linecnt) + ",'" + ds.Tables[0].Rows[rwcnt]["receipt"] + "','Y')"; } else { str = "update tbl_cashbook set r_details=" + ds.Tables[0].Rows[rwcnt]["receipt"] + ",isline='Y' where recno=" + rline; } c.connectsql(str, c.myconn, trn); trn.Commit(); rline++; linecnt++; if (pline > rline) { rline = pline; } else { pline = rline; } } } } /// /// for bank payment /// if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "BP") { if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Dr") { bplineno = 0; trn = c.myconn.BeginTransaction(); if (pline >= rline) { str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_bank) values (" + (pline) + ",'" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "',' To : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["payment"] + ")"; } else { str = "update tbl_cashbook set p_date='" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "' ,p_particulars=' To : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "',p_bank=" + ds.Tables[0].Rows[rwcnt]["payment"] + " where recno=" + pline; } c.connectsql(str, c.myconn, trn); trn.Commit(); bplineno = linecnt; pbank = pbank + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]); pline++; linecnt++; bankopbal = bankopbal - Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]); str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'"; DataSet ds1 = new DataSet(); c.filldataset(false, ref ds1, str, c.myconn); subcnt = 0; if (ds1.Tables[0].Rows.Count > 0) { while (subcnt < ds1.Tables[0].Rows.Count) { trn = c.myconn.BeginTransaction(); if (pline >= rline) { str = "insert into tbl_cashbook (recno,p_lf,p_date,p_particulars,p_details) values (" + (pline) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")"; } else { str = "update tbl_cashbook set p_lf='" + ds1.Tables[0].Rows[subcnt]["lf"] + "',p_date='' , p_particulars='" + ds1.Tables[0].Rows[subcnt]["acname"] + "',p_details='" + ds1.Tables[0].Rows[subcnt]["subledgamt"] + " where recno=" + pline; } //str = "insert into tbl_cashbook (recno,r_lf,p_date,p_particulars,p_details) values (" + (linecnt) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")"; c.connectsql(str, c.myconn, trn); trn.Commit(); pline++; linecnt++; subcnt++; } } if (subcnt > 0) { trn = c.myconn.BeginTransaction(); if (pline >= rline) { str = "insert into tbl_cashbook (recno,p_details,isline) values (" + (linecnt) + ",'" + ds.Tables[0].Rows[rwcnt]["Payment"] + "','Y')"; } else { str = "update tbl_cashbook set p_details='" + ds.Tables[0].Rows[rwcnt]["Payment"] + "',isline='Y' where recno=" + pline; } c.connectsql(str, c.myconn, trn); trn.Commit(); pline++; linecnt++; if (pline > rline) { rline = pline; } else { pline = rline; } } } } rwcnt++; } //----------- str = " SELECT sum( (case a.amttype when 'Dr' then a.vchamt else 0 end)) AS Payment, sum((case a.amttype when 'Cr' then a.vchamt else 0 end)) AS Receipt "; str = str + " FROM tbl_voucherdet AS a, tbl_voucher AS b,tbl_account c Where (a.vchtype='JV' "; str = str + " or (a.vchtype='CP' and a.amttype='Dr') or (a.vchtype='CR' and a.amttype='Cr')) and a.vchdate='" + dtpfrom.Value.ToShortDateString() + "'"; str = str + " and a.sessioncode=" + school.CurrentSessionCode + " and a.vchno = b.vchno and a.VCHTYPE = b.VCHTYPE and a.accode=c.accode and a.accode<>dbo.GetAccountCode('CS')"; ds = new DataSet(); c.filldataset(false, ref ds, str, c.myconn); //----------- decimal pamt = 0, ramt = 0, rsamt = 0; if (ds.Tables[0].Rows.Count > 0) { pamt = Convert.ToDecimal(ds.Tables[0].Rows[0]["Payment"].ToString()); ramt = Convert.ToDecimal(ds.Tables[0].Rows[0]["Receipt"].ToString()); } rsamt = (ramt + cashopbal) - pamt; trn = c.myconn.BeginTransaction(); str = "insert into tbl_cashbook (recno) values (" + (linecnt) + " )"; c.connectsql(str, c.myconn, trn); linecnt++; str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_cash,p_bank,isbold) values (" + (linecnt) + ",'" + "" + "','Closing Balance'," + rsamt + "," + bankopbal + ",'Y')"; c.connectsql(str, c.myconn, trn); linecnt++; str = "insert into tbl_cashbook (recno,r_particulars,r_details,r_cash,r_bank,p_particulars,p_details,p_cash,p_bank,isbold) values (" + (linecnt) + ",'Total '," + (ramt + cashopbal) + ",0," + rbank + ",'Total '," + (pamt + rsamt) + ",0," + pbank + ",'Y')"; c.connectsql(str, c.myconn, trn); trn.Commit(); linecnt++; } //--------------------------------- //mysql = "select '',' For " + dtpfrom.Text + " to " + dtpto.Text + "' as period,accno,accname,accdate,convert(varchar(10),vchdate,105) as vchdate,vchtype,vchno,narration,amt,receipt,payment,balance,baltype,recno,amttype from tbl_accledger order by vchdate "; string str1 = "select '',' For " + dtpfrom.Text + " to " + dtpto.Text + "' as period,(select top 1 schoolname from tbl_school ) as SName,(select top 1 schooladdress from tbl_school ) as SAddress,* from tbl_cashbook order by recno"; ds = new DataSet(); ds.Clear(); ds = Connection.GetDataSet(str1); ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\CashBook.xsd"); //ds.WriteXmlSchema(@"D:\XSDSchema1.xsd"); //Account.ReportDesign.CashBook Account.ReportDesign.CashBook cr = new Account.ReportDesign.CashBook(); cr.PrintOptions.PaperOrientation = PaperOrientation.Landscape; cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4; cr.SetDataSource(ds); ShowAllReports s = new ShowAllReports(); s.crystalReportViewer1.ReportSource = cr; s.Show(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }