private void button1_Click(object sender, EventArgs e)
{
try
{
DateTime opdate = dtpfrom.Value.Date;
c.returnconn(c.myconn);
string mysql;
mysql = "INSERT INTO TBL_LEDGER (ACCNO,ACCNAME,openbal,Reciept,PAYMENT) ";
mysql = mysql + "select L.accno,l.accname,L.opbal,l.receipt,L.payment FROM (";
mysql = mysql + "SELECT m.BHM_COD as accno,m.BHM_DES, m.BHM_DESH as accname,(case when m.opbal>0 then 'Cr' else 'Dr' end) as baltype, isnull(m.opbal,0) as opbal, isnull(n.receipt,0) as receipt,isnull(n.payment,0) as payment,(isnull(m.opbal,0)+isnull(n.receipt,0)- isnull(n.payment,0)) as clbal FROM (";
mysql = mysql + "SELECT p.GRP_COD,p.BHM_COD ,p.BHM_DESH,p.BHM_DES, isnull(p.ope_bld,0) +isnull(q.receipt,0)-isnull(q.payment,0) AS opbal, 0 AS receipt, 0 AS payment, 0 AS CLBAL FROM tbl_LedgerAcc AS p LEFT JOIN ( SELECT b.acccode,isnull(sum(b.receipt),0) AS receipt, isnull(sum(b.payment),0) AS payment FROM ( SELECT a.AccCode ,sum(a.Amount) AS Receipt, 0 AS payment FROM tbl_VoucherDetail AS a, tbl_voucher AS b WHERE a.VouchNo=b.VouchNo and a.vchdate>= (select OpenDate from tbl_LedgerAcc where bhm_cod=a.AccCode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.amount>0 GROUP BY a.AccCode UNION ALL SELECT a.AccCode, 0 AS Receipt, sum(a.Amount) AS payment FROM tbl_VoucherDetail AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.VchDate> (select OpenDate from tbl_LedgerAcc where BHM_COD=a.AccCode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.VchType=b.VchType and A.Amount < 0 GROUP BY a.AccCode ) b GROUP BY b.AccCode ) AS q ON p.BHM_COD =q.acccode ) m ";
mysql = mysql + "LEFT JOIN (SELECT b.AccCode, 0 AS opbal,sum(b.receipt) AS receipt, sum(b.payment) AS payment, 0 AS clbal FROM (";
mysql = mysql + "SELECT a.AccCode, 0 AS OPBAL, sum(a.Amount) AS Receipt, 0 AS payment, 0 AS CLBAL FROM tbl_VoucherDetail AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate= (select (case when OpenDate<='" + dtpfrom.Value.Date + "' then OpenDate else '" + dtpfrom.Value.Date + "' end) as mdate from tbl_LedgerAcc where BHM_COD=a.AccCode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.Amount >0 GROUP BY a.AccCode ";
mysql = mysql + "UNION ALL SELECT a.AccCode, 0 AS Receipt, 0 AS OPBAL, sum(a.Amount) AS payment, 0 AS CLBAL FROM tbl_VoucherDetail AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select (case when OpenDate<='" + dtpfrom.Value.Date + "' then OpenDate else '" + dtpfrom.Value.Date + "' end) as mdate from tbl_LedgerAcc where BHM_COD =a.AccCode ) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.Amount <0 group by a.AccCode ) AS b ";
mysql = mysql + "GROUP BY b.AccCode ) n ON m.BHM_COD =n.AccCode ) L order by L.BHM_DES ";
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
c.connectsql("delete from tbl_ledger", c.myconn, trn);
//insert the opening balance
c.connectsql(mysql, c.myconn, trn);
trn.Commit();
//--------------------------------- Crystal Report -------------------
string str1 = "select '','" + dtpfrom.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\TrialBalance.xsd");
RptTrialBalance cr = new RptTrialBalance();
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
s.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnOk_Click(object sender, EventArgs e)
{
try
{
//DateTime opdate = dtpfrom.Value.Date;
c.returnconn(c.myconn);
string mysql;
mysql = " INSERT INTO TBL_ACCLEDGER (ACCNO,ACCNAME,PAYMENT,RECEIPT) ";
mysql = mysql + " select L.accno,L.accname,(case when L.clbal<0 then -1*L.clbal else 0 end) payment,(case when L.clbal>=0 then L.clbal else 0 end) receipt FROM ";
mysql = mysql + " ( SELECT m.accode as accno, m.acname as accname,m.acoptype as baltype, isnull(m.opbal,0) as opbal, isnull(n.receipt,0) as receipt,isnull(n.payment,0) as payment,(isnull(m.opbal,0)+isnull(n.receipt,0)-isnull(n.payment,0)) as clbal FROM ";
mysql = mysql + " ( SELECT p.accode,p.acname,p.acoptype, (case when p.acoptype='Cr' then -1 else 1 end) *isnull(p.acopbal,0) +isnull(q.receipt,0)-isnull(q.payment,0) AS opbal, 0 AS receipt, 0 AS payment, 0 AS CLBAL FROM tbl_account AS p LEFT JOIN ( SELECT b.accode,isnull(sum(b.receipt),0) AS receipt, isnull(sum(b.payment),0) AS payment FROM ( SELECT a.accode, sum(a.vchamt) AS Receipt, 0 AS payment FROM tbl_voucherdet AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select opdate from tbl_account where accode=a.accode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.AMTtype='Dr' GROUP BY a.accode UNION ALL SELECT a.accode, 0 AS Receipt, sum(a.vchamt) AS payment FROM tbl_voucherdet AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select opdate from tbl_account where accode=a.accode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.AMTtype='Cr' GROUP BY a.accode ) b GROUP BY b.accode ) AS q ON p.accode=q.accode ) m LEFT JOIN ";
mysql = mysql + " (SELECT b.accode, 0 AS opbal,sum(b.receipt) AS receipt, sum(b.payment) AS payment, 0 AS clbal FROM (SELECT a.accode, 0 AS OPBAL, sum(a.vchamt) AS Receipt, 0 AS payment, 0 AS CLBAL FROM tbl_voucherdet AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select (case when opdate<'" + dtpfrom.Value.Date + "' then opdate else '" + dtpfrom.Value.Date + "' end) as mdate from tbl_account where accode=a.accode) and a.vchdate<='" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.AMTtype='Dr' GROUP BY a.accode UNION ALL SELECT a.accode, 0 AS Receipt, 0 AS OPBAL, sum(a.vchamt) AS payment, 0 AS CLBAL FROM tbl_voucherdet AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select (case when opdate<'" + dtpfrom.Value.Date + "' then opdate else '" + dtpfrom.Value.Date + " ' end) as mdate from tbl_account where accode=a.accode) and a.vchdate<='" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.AMTtype='Cr' group by a.accode) AS b GROUP BY b.accode ) n ";
mysql = mysql + " ON m.accode=n.accode ) L ";
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
c.connectsql("delete from tbl_accledger", c.myconn, trn);
//insert the opening balance
c.connectsql(mysql, c.myconn, trn);
trn.Commit();
//---------------------------------
mysql = "select ' Income/Expanse From " + dtpto.Text + " Upto " + dtpfrom.Text + "' as period,(select top 1 schoolname from tbl_school ) as SName,(select top 1 schooladdress from tbl_school ) as SAddress,accname,receipt,payment from(SELECT 'Fee' as accname,0 as receipt,SUM(isnull( acl.payment,0)) as payment FROM TBL_ACCLEDGER acl INNER JOIN tbl_account tac on acl.accno=tac.accode where tac.actype=3 union all SELECT acl.accname, SUM(isnull( acl.receipt,0)) as receipt,0 as payment FROM TBL_ACCLEDGER acl INNER JOIN tbl_account tac on acl.accno=tac.accode where tac.actype not in (3,1) group by acl.accname)tbl_accledger where (receipt>0 or payment > 0) order by accname ";
DataSet ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet(mysql);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TrialBook.xsd");
//ds.WriteXmlSchema(@"D:\XSDSchema1.xsd");
TrialBook cr = new TrialBook();
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);
}
}
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);
}
}
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)
{
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);
}
}
private void btnsave_Click(object sender, EventArgs e)
{
if (dtgbook.Rows.Count < 1)
{
return;
}
c.returnconn(c.myconn);
int i = 0;
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
try
{
//---------
string mysql = "";
mysql = "select * from tbl_booktitle where bookno='" + valcmbbook.SelectedValue + "'";
SqlCommand com;
com = new SqlCommand(mysql, c.myconn, trn);
SqlDataReader reader;
reader = com.ExecuteReader();
if (reader.HasRows)
{
i = 0;
while (reader.Read())
{
string filename = @"" + Connection.GetAccessPathId() + @"Barcodes\b\" + reader["BarcodeNo"] + ".bmp";
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
i++;
}
reader.Close();
}
else
{
reader.Close();
}
//---------
c.connectsql("delete from tbl_booktitle where bookno=" + valcmbbook.SelectedValue, c.myconn, trn);
i = 0;
do
{
if (dtgbook.Rows[i].Cells[1].Value != null)
{
txtbarnum = Guid.NewGuid().ToString().Replace("-", "").Trim().Substring(0, 10);
barcodectrl.BarCode = txtbarnum;
c.connectsql("insert into tbl_booktitle (bookno,booktitle,prize,Granttype,Edition,noofpages,publicationyr,barcodeno) values (" + dtgbook.Rows[i].Cells[0].Value + "," + dtgbook.Rows[i].Cells[1].Value + "," + dtgbook.Rows[i].Cells[2].Value + ",'" + dtgbook.Rows[i].Cells[3].Value + "','" + dtgbook.Rows[i].Cells[4].Value + "'," + dtgbook.Rows[i].Cells[5].Value + "," + dtgbook.Rows[i].Cells[6].Value + ",'" + txtbarnum + "')", c.myconn, trn);
//----------
string filename = @"" + Connection.GetAccessPathId() + @"\Barcodes\b\" + txtbarnum + ".bmp";
barcodectrl.SaveImage(filename);
FileStream fs;
fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
//a byte array to read the image
byte[] picbyte = new byte[fs.Length];
fs.Read(picbyte, 0, System.Convert.ToInt32(fs.Length));
System.Threading.Thread.Sleep(200);
fs.Flush();
fs.Close();
//----------
string str1 = "Update dbo.tbl_booktitle set BarcodeImage=@Bimage where booktitle=@booktitle ";
SqlCommand cmd = new SqlCommand(str1, c.myconn, trn);
cmd.Parameters.AddWithValue("@booktitle", dtgbook.Rows[i].Cells[1].Value);
cmd.Parameters.AddWithValue("@Bimage", picbyte);
cmd.ExecuteNonQuery();
//----------
//dtgbook.Rows[i].Cells[7].Value = barcodectrl.BarCode;
}
i++;
}while (i <= dtgbook.Rows.Count - 1);
trn.Commit();
MessageBox.Show("Data Saved..", "School");
}
catch (Exception EX)
{
MessageBox.Show(EX.Message);
trn.Rollback();
}
}
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 override void btnsave_Click(object sender, EventArgs e)
{
string strmessage = "Invalid Bed No.";
if (txtbedno.Text.Length == 0)
{
goto mline;
textBox1.Focus();
}
//check for existing for room-bed no
c.returnconn(c.myconn);
txtbedno.Text = txtbedno.Text.ToUpper();
string mysql;
mysql = "select * from tbl_roomdet where hostelcode=" + valcmbclass.SelectedValue + " and bedno='" + txtbedno.Text + "'";
SqlCommand com;
com = new SqlCommand(mysql, c.myconn);
reader = com.ExecuteReader();
int i = 0;
if (reader.HasRows)
{
reader.Read();
int studentno = 0;
string tstr = Convert.ToString(reader["studentno"]);
if (string.IsNullOrEmpty(tstr))
{
//do something
}
else
{
try
{
studentno = Convert.ToInt16(reader["studentno"]);
}
catch
{
}
}
reader.Close();
if (studentno != 0)
{
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
c.connectsql("update tbl_roomdet set studentno=null ,allotmentdate=null,studentname=null where studentno=" + lblstudentno.Text + " and hostelcode=" + valcmbclass.SelectedValue + " and bedno='" + txtbedno.Text + "'", c.myconn, trn);
trn.Commit();
strmessage = "Room Vacated..";
}
else
{
MessageBox.Show("Room/Bed Already Vacated...");
}
}
else
{
goto mline;
}
mline:
MessageBox.Show(strmessage);
shwoList();
}
public void GetStudentDetailsOld()
{
try
{
DateTime opdate = dtpfrom.Value.Date;
c.returnconn(c.myconn);
string mysql;
mysql = "select opdate from tbl_account where accode=" + valcmbbank.SelectedValue;
SqlCommand com;
com = new SqlCommand(mysql, c.myconn);
SqlDataReader reader = com.ExecuteReader();
int i = 0;
if (reader.HasRows)
{
reader.Read();
opdate = Convert.ToDateTime(reader["opdate"]);
}
reader.Close();
string str = " SELECT isnull(sum(case a.amttype when 'Dr' then a.vchamt else 0 end),0) AS Payment, isnull(sum(case a.amttype when 'Cr' then a.vchamt else 0 end),0) AS Receipt,";
str = str + " 0 as balance FROM tbl_voucherdet AS a ";
str = str + " Where a.vchdate>='" + opdate.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 mrec = 0;
decimal mpay = 0;
if (reader.HasRows)
{
reader.Read();
mrec = Convert.ToDecimal(reader["receipt"]);
mpay = Convert.ToDecimal(reader["payment"]);
}
reader.Close();
//--------------------
string actypet = string.Empty;
string actypep = string.Empty;
actypet = Convert.ToString(Connection.GetExecuteScalar("select actype from tbl_account where accode='" + valcmbbank.SelectedValue + "' "));
actypep = Convert.ToString(Connection.GetExecuteScalar("select dbo.GetAccountCode('F')"));
//--------------------
SqlTransaction trn;
if (actypet == actypep)
{
trn = c.myconn.BeginTransaction();
c.connectsql("delete from tbl_accledger", c.myconn, trn);
DateTime mopdate = opdate.Date;
if (dtpfrom.Value.Date < opdate.Date)
{
dtpfrom.Value = opdate.Date;
}
//insert the opening balance
str = " Insert into tbl_accledger (accno,vchtype,vchdate,narration,receipt,payment,balance,baltype) ";
str = str + " select accode as accno,'Open. Bal.' as vchtype,'" + dtpfrom.Value.ToShortDateString() + "' as vchdate,'' as narration,0,0,isnull(acopbal,0) as balance,(case acoptype when 'Cr' then 'Debit' else 'Credit' end) as baltype ";
str = str + " from tbl_account where accode=" + valcmbbank.SelectedValue;
c.connectsql(str, c.myconn, trn);
//update opening balance for gap period
if (mpay > 0)
{
c.connectsql("update tbl_accledger set balance = balance - " + mpay + " + " + mrec, c.myconn, trn);
c.connectsql("update tbl_accledger set balance = balance - dbo.GetAccountOpening('" + valcmbbank.SelectedValue + "')", c.myconn, trn);
}
else
{
c.connectsql("update tbl_accledger set balance = balance - " + mpay + " + " + mrec, c.myconn, trn);
}
//---------------------------------
//insert the voucher details for each entry date wise for the account
str = " Insert into tbl_accledger (accno,vchno,vchdate,vchtype,amttype,narration,payment,receipt,balance) ";
str = str + " SELECT a.accode as accno,b.vchno, b.vchdate,b.vchtype, (case a.amttype when 'Cr' then 'Receipt' else 'Payment' end ) AS amttype, isnull(b.remark,'') as narration, ";
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,0 as balance ";
str = str + " FROM tbl_voucherdet AS a, tbl_voucher AS b Where a.vchdate>='" + dtpfrom.Value.ToShortDateString() + "'";
str = str + " and a.vchdate<='" + dtpto.Value.ToShortDateString() + "' and a.accode=" + valcmbbank.SelectedValue + " and a.sessioncode in (select distinct sessioncode from tbl_voucherdet where accode=a.accode) and a.vchno = b.vchno and a.VCHTYPE = b.VCHTYPE and a.sessioncode=b.sessioncode ORDER BY b.vchdate ";
c.connectsql(str, c.myconn, trn);
trn.Commit();
//---------------------------------
mysql = "select " + "' Ledger of : " + valcmbbank.Text + " 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,accno,tbl_account.acname as accname,accdate,convert(varchar(10),vchdate,105) as cvchdate,vchdate,vchtype,vchno,narration,amt,receipt,payment,balance,baltype,recno,amttype from tbl_accledger ,tbl_account where tbl_accledger.accno=tbl_account.accode order by vchdate";
DataSet ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet(mysql);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\BankBook.xsd");
//ds.WriteXmlSchema(@"D:\XSDSchema1.xsd");
JournalBook cr = new JournalBook();
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();
}
else
{
trn = c.myconn.BeginTransaction();
c.connectsql("delete from tbl_accledger", c.myconn, trn);
DateTime mopdate = opdate.Date;
if (dtpfrom.Value.Date < opdate.Date)
{
dtpfrom.Value = opdate.Date;
}
//insert the opening balance
str = " Insert into tbl_accledger (accno,vchtype,vchdate,narration,receipt,payment,balance,baltype) ";
str = str + " select accode as accno,'Open. Bal.' as vchtype,'" + dtpfrom.Value.ToShortDateString() + "' as vchdate,'' as narration,0,0,isnull(acopbal,0) as balance,(case acoptype when 'Cr' then 'Credit' else 'Debit' end) as baltype ";
str = str + " from tbl_account where accode=" + valcmbbank.SelectedValue;
c.connectsql(str, c.myconn, trn);
//update opening balance for gap period
c.connectsql("update tbl_accledger set balance = balance - " + mpay + " + " + mrec, c.myconn, trn);
//---------------------------------
//insert the voucher details for each entry date wise for the account
str = " Insert into tbl_accledger (accno,vchno,vchdate,vchtype,amttype,narration,payment,receipt,balance) ";
str = str + " SELECT a.accode as accno,b.vchno, b.vchdate,b.vchtype, (case a.amttype when 'Cr' then 'Receipt' else 'Payment' end ) AS amttype, isnull(b.remark,'') as narration, ";
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,0 as balance ";
str = str + " FROM tbl_voucherdet AS a, tbl_voucher AS b Where a.vchdate>='" + dtpfrom.Value.ToShortDateString() + "'";
str = str + " and a.vchdate<='" + dtpto.Value.ToShortDateString() + "' and a.accode=" + valcmbbank.SelectedValue + " and a.sessioncode in (select distinct sessioncode from tbl_voucherdet where accode=a.accode) and a.vchno = b.vchno and a.VCHTYPE = b.VCHTYPE and a.sessioncode=b.sessioncode ORDER BY b.vchdate ";
c.connectsql(str, c.myconn, trn);
trn.Commit();
//---------------------------------
mysql = "select " + "' Ledger of : " + valcmbbank.Text + " 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,accno,tbl_account.acname as accname,accdate,convert(varchar(10),vchdate,105) as cvchdate,vchdate,vchtype,vchno,narration,amt,receipt,payment,balance,baltype,recno,amttype from tbl_accledger ,tbl_account where tbl_accledger.accno=tbl_account.accode order by vchdate";
DataSet ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet(mysql);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\OBankBook.xsd");
//ds.WriteXmlSchema(@"D:\XSDSchema1.xsd");
JournalBookO cr = new JournalBookO();
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 override void btnsave_Click(object sender, EventArgs e)
{
c.getconnstr();
i = 0;
c.returnconn(c.myconn);
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
try
{
Connection.AllPerform("delete tbl_hostelroom where hostelcode='" + valcmbclass.SelectedValue + "'");
Connection.AllPerform("delete tbl_roomdet where hostelcode='" + valcmbclass.SelectedValue + "'");
do
{
string mysql = "";
if (dtgbook.Rows[i].Cells[0].Value != null)
{
mysql = "insert into tbl_hostelroom (hostelcode,roomno,totalbeds) values (" + valcmbclass.SelectedValue + "," + dtgbook.Rows[i].Cells[0].Value + "," + dtgbook.Rows[i].Cells[1].Value + ")";
c.connectsql(mysql, c.myconn, trn);
}
if (dtgbook.Rows[i].Cells[1].Value != null)
{
int totbed = Convert.ToInt16(dtgbook.Rows[i].Cells[1].Value);
for (int j = 0; j < totbed; j++)
{
string bedno = "";
switch (j)
{
case 0:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-A";
break;
case 1:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-B";
break;
case 2:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-C";
break;
case 3:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-D";
break;
case 4:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-E";
break;
case 5:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-F";
break;
case 6:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-G";
break;
case 7:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-H";
break;
case 8:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-I";
break;
case 9:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-J";
break;
case 10:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-K";
break;
case 11:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-L";
break;
case 12:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-M";
break;
case 13:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-N";
break;
case 14:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-O";
break;
case 15:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-P";
break;
case 16:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-Q";
break;
case 17:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-R";
break;
case 18:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-S";
break;
case 19:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-T";
break;
case 20:
bedno = dtgbook.Rows[i].Cells[0].Value.ToString() + "-U";
break;
default: break;
}
mysql = "insert into tbl_roomdet(hostelcode,roomno,bedno) values (" + valcmbclass.SelectedValue + "," + dtgbook.Rows[i].Cells[0].Value + ",'" + bedno + "')";
c.connectsql(mysql, c.myconn, trn);
}
// }
// }
}
i++;
// }
// else { MessageBox.Show("Duplicate Data Not Allowed"); }
}while (i <= dtgbook.Rows.Count - 1);
trn.Commit();
MessageBox.Show("Data Saved..", "School");
//}
//else { MessageBox.Show("duplicate entry not allowed"); }
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
trn.Rollback();
}
}
public override void btnsave_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" || txtbedno.Text == "")
{
MessageBox.Show("field not blanked"); textBox1.Focus();
}
else
{
i = 0;
if (txtbedno.Text.Length < 1)
{
return;
}
txtbedno.Text = txtbedno.Text.ToUpper();
c.returnconn(c.myconn);
txtbedno.Text = txtbedno.Text.ToUpper();
DataSet ds = Connection.GetDataSet("select studentno from tbl_roomdet where bedno='" + txtbedno.Text + "'");
DataSet d1 = Connection.GetDataSet("select count(*) from tbl_roomdet where studentno='" + lblstudentno.Text + "'");
DataSet dsss = Connection.GetDataSet("select count(*) from tbl_roomdet where studentno='" + lblstudentno.Text + "' and bedno='" + txtbedno.Text + "'");
string studentno3 = Convert.ToString(ds.Tables[0].Rows[0][0]);
int studentno1 = Convert.ToInt32(d1.Tables[0].Rows[0][0]);
int studentno2 = Convert.ToInt32(dsss.Tables[0].Rows[0][0]);
if (studentno2 <= 0 && studentno3 == "" && studentno1 <= 0)
{
try
{
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
Connection.AllPerform("update tbl_roomdet set studentno='" + lblstudentno.Text + "',studentname='" + lblname.Text + "',allotmentdate='" + dateTimePicker1.Value + "' where hostelcode='" + valcmbclass.SelectedValue + "' and bedno='" + txtbedno.Text + "'");
DataSet dss = Connection.GetDataSet("select count(*) from tbl_hostelfee where studentno='" + lblstudentno.Text + "'");
int count = Convert.ToInt32(dss.Tables[0].Rows[0][0]);
if (count == 0)
{
DataSet d = Connection.GetDataSet("select roomno from tbl_roomdet where hostelcode=" + valcmbclass.SelectedValue + " and bedno='" + txtbedno.Text + "'");
int roomno = Convert.ToInt32(d.Tables[0].Rows[0][0]);
DataSet dd = Connection.GetDataSet("select name from tbl_student where scholarno='" + textBox1.Text + "'");
studentName = dd.Tables[0].Rows[0][0].ToString();
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",5," + c.pdate1.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",6," + c.pdate1.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",7," + c.pdate1.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",8," + c.pdate1.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",9," + c.pdate1.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",10," + c.pdate1.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",11," + c.pdate1.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",12," + c.pdate1.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",1," + c.pdate2.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",2," + c.pdate2.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",3," + c.pdate2.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
c.connectsql("insert into tbl_hostelfee (studentno,studentname,hostelcode,roomno,bedno,sessioncode,monthno,yearno,hostelfee,messfee,rechostelfee,recmessfee) values (" + lblstudentno.Text + ",'" + studentName + "'," + valcmbclass.SelectedValue + ",'" + roomno + "','" + txtbedno.Text + "'," + school.CurrentSessionCode + ",4," + c.pdate2.Year + "," + hostelfee + "," + messfee + ",'" + 0 + "','" + 0 + "')", c.myconn, trn);
trn.Commit();
}
else
{
string roomno = txtbedno.Text;
DataSet d11 = Connection.GetDataSet("select roomno from tbl_roomdet where hostelcode=" + valcmbclass.SelectedValue + " and bedno='" + txtbedno.Text + "'");
int rno = Convert.ToInt32(d11.Tables[0].Rows[0][0]);
Connection.AllPerform("update tbl_hostelfee set studentno='" + lblstudentno.Text + "',studentname='" + lblname.Text + "',hostelcode='" + valcmbclass.SelectedValue + "',roomno='" + rno + "',bedno='" + txtbedno.Text + "',sessioncode='" + school.CurrentSessionCode + "' where studentno='" + lblstudentno.Text + "'");
}
MessageBox.Show("room alloted");
showList();
}
catch
{
// MessageBox.Show(exx.Message.ToString());
}
}
else
{
MessageBox.Show("Room/Bed Already Alloted...");
}
}
}
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);
}
