private void новыйФайлToolStripMenuItem_Click(object sender, EventArgs e)
{
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
string msg = "Сохранить данный файл?";
string caption = "Подтверждение действия";
if (MessageBox.Show(msg, caption, buttons) == DialogResult.Yes)
{
Save_File();
}
JournalBook.Clear();
Data.Rows.Clear();
New_File();
}
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);
}
}
private void удалитьЖурналToolStripMenuItem_Click(object sender, EventArgs e)
{
JournalBook.Clear();
journal.Clear();
MessageBox.Show("Журнал очищен.");
}
////////******** РАБОТА С ЖУРНАЛОМ ********\\\\\\\\
#region Journal
private void очиститьToolStripMenuItem_Click(object sender, EventArgs e)
{
JournalBook.Clear();
}