public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
RptTrialBalance rpt = new RptTrialBalance();
rpt.Site = this.Site;
return(rpt);
}
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);
}
}