public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
RptBankBook rpt = new RptBankBook();
rpt.Site = this.Site;
return(rpt);
}
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);
//"select ISNULL(a.Receipt,0) as receipt,ISNULL(a.Payment ,0) as payment,a.balance from (SELECT SUM((case when a.Amount < 0 then -1*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=" + m.psessioncode + ") 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 ("+m.psessioncode+",'" + dtpfrom.Value + "' ,'Open. Bal.',0," + CashopBal + ","+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-------
c.getconnstr();
c.returnconn(c.myconn);
trn = c.myconn.BeginTransaction();
mysql = "insert into tbl_Ledger (yearno,accno,accname,vchno,vchdate,vchtype,remark,ChequeNo,ChequeDate,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.ChequeNo,a.ChequeDate as Date,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','BP','BR') 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.GRP_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-------
DateTime dt = dtpfrom.Value;
string str1 = "select'" + valcmbcash.Text + "' as bank,' For " + 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\BankBook.xsd");
RptBankBook cr = new RptBankBook();
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);
}
}