示例#1
0
        public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
        {
            RptBankBook rpt = new RptBankBook();

            rpt.Site = this.Site;
            return(rpt);
        }
示例#2
0
        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);
            }
        }