Beispiel #1
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);
                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);
            }
        }
Beispiel #2
0
        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 { }
        }
Beispiel #3
0
        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);
            }
        }
Beispiel #4
0
        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);
        }