private void btnVacatRoom_Click(object sender, EventArgs e)
{
DataSet dss = Connection.GetDataSet("select hostelcode from tbl_hostel where hostelname='" + cmbHostelName.Text + "'");
int hostelcode = Convert.ToInt32(dss.Tables[0].Rows[0][0]);
string mysql = "SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school SELECT tbl_hostel.HostelName,convert(nvarchar(10),tbl_roomdet.allotmentdate,103) as allotmentdate, tbl_roomdet.studentname, tbl_roomdet.bedno, tbl_roomdet.roomno FROM tbl_roomdet INNER JOIN tbl_hostel ON tbl_roomdet.hostelcode = tbl_hostel.Hostelcode WHERE tbl_roomdet.studentname IS NULL AND tbl_roomdet.hostelcode='" + hostelcode + "'";
DataSet ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet(mysql);
if (ds.Tables[1].Rows.Count > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"\Barcodes\a\hostelVaccent.xsd");
Hostal.ReportDesign.rptHostelVacat rh = new Hostal.ReportDesign.rptHostelVacat();
rh.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
rh.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
rh.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.Text = "Room Vacated Report";
s.crystalReportViewer1.ReportSource = rh;
s.Show();
}
else
{
//===
MessageBox.Show("Data not found.");
}
}
public void GetEmployeeDetails()
{
SqlTransaction trn = null;
try
{
// trn = c.myconn.BeginTransaction();
DataSet ds = new DataSet();
ds.Clear();
ds = GetDatasetByTrn("GetEmployeeLadger ", valcmbbank.SelectedValue.ToString(), dtpfrom.Value, dtpto.Value, c.myconn, trn);
// trn.Commit();
//---------------------------------
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\EJBook.xsd");
if (ds.Tables[0].Rows.Count > 0)
{
//ds.WriteXmlSchema(@"D:\XSDSchema1.xsd");
LDEmployee cr = new LDEmployee();
cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
s.Show();
}
else
{
MessageBox.Show("Racord not exist.", "Warnning:");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnAllottRoom_Click(object sender, EventArgs e)
{
if (cmbHostelName.Text.Trim() == string.Empty)
{
MessageBox.Show("Please Select A Hostel");
}
else
{
DataSet dss = Connection.GetDataSet("select hostelcode from tbl_hostel where hostelname='" + cmbHostelName.Text + "' ");
int hostelcode = Convert.ToInt32(dss.Tables[0].Rows[0][0]);
string mysql = "SELECT tbl_hostel.HostelName, tbl_school.schoolname, tbl_school.schooladdress, tbl_school.affiliate_by, tbl_school.logoimage FROM tbl_hostel CROSS JOIN tbl_school WHERE tbl_hostel.Hostelcode = '" + hostelcode + "' SELECT convert(nvarchar(10),allotmentdate,103) as allotmentdate,studentname,bedno,roomno FROM tbl_roomdet where studentname IS NOT NULL AND hostelcode='" + hostelcode + "' ";
DataSet ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet(mysql);
if (ds.Tables[1].Rows.Count > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"\Barcodes\a\hostelallot.xsd");
Hostal.ReportDesign.rptHostelAllotPosition rh = new Hostal.ReportDesign.rptHostelAllotPosition();
rh.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
rh.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
rh.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.Text = "Room Alloted Report";
s.crystalReportViewer1.ReportSource = rh;
s.Show();
}
else
{
//========
MessageBox.Show("Data not found.");
}
}
}
private void btnEveryDayFee_Click(object sender, EventArgs e)
{
if (cmbHostelName.Text.Trim() == string.Empty)
{
MessageBox.Show("Please Select A Hostel");
}
else
{
DataSet dss = Connection.GetDataSet("select hostelcode from tbl_hostel where HostelName='" + cmbHostelName.Text + "'");
DataSet ds = Connection.GetDataSet("SELECT tbl_hostel.HostelName, tbl_school.schoolname, tbl_school.schooladdress, tbl_school.affiliate_by, tbl_school.logoimage FROM tbl_hostel CROSS JOIN tbl_school WHERE tbl_hostel.Hostelcode = '" + dss.Tables[0].Rows[0][0]
+ "' SELECT tbl_hostel.HostelName, tbl_hostelfee.roomno, tbl_hostelfee.bedno, tbl_hostelfee.studentname, DateName( month , DateAdd( month , tbl_hostelfee.monthno , 0 ) - 1 ) as monthno, tbl_hostelfee.yearno, convert(nvarchar(10),tbl_hostelfee.feedate,103) as feedate, tbl_hostelfee.rechostelfee, tbl_hostelfee.recmessfee FROM tbl_hostelfee INNER JOIN tbl_hostel ON tbl_hostelfee.hostelcode = tbl_hostel.Hostelcode WHERE tbl_hostelfee.feedate ='" + cmbDate.Value.Date + "' and tbl_Hostelfee.hostelCode='" + dss.Tables[0].Rows[0][0] + "'");
if (ds.Tables[1].Rows.Count > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"\Barcodes\a\DailyHostelfee.xsd");
Hostal.ReportDesign.rptDailyHostelFee r = new Hostal.ReportDesign.rptDailyHostelFee();
r.SetDataSource(ds);
ShowAllReports f = new ShowAllReports();
r.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
r.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
f.Text = "Daily Hostel Fee Report";
f.crystalReportViewer1.ReportSource = r;
f.Show();
}
else
{
//------------------
MessageBox.Show("Data not found.");
}
}
}
private void btnViewReport_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds = Connection.GetDataSet("SELECT tbl_session.sessioncode, tbl_session.sessionname, tbl_student.studentno, tbl_student.scholarno,'Mast/Miss '+ tbl_student.name as name , 'Mr. '+ tbl_student.father as father, " +
" 'Mrs. '+ tbl_student.mother as mother, tbl_student.dob, tbl_student.C_address, tbl_student.m_tongue, tbl_student.casttype, tbl_student.bloodgroup,tbl_student.Height,tbl_student.Width,tbl_student.VisionL,tbl_student.VisionR,tbl_student.Teeth ,tbl_student.OHygiene,tbl_student.House,CASE WHEN tbl_student.CGPA IS NULL or tbl_student.CGPA='' THEN '..........' ELSE tbl_student.CGPA END as CGPA,CASE WHEN tbl_student.OGrade IS NULL or tbl_student.OGrade='' THEN '..........' ELSE tbl_student.OGrade END as OGrade, tbl_classstudent.stdtype,tbl_student.studentimage, " +
" tbl_student.marr_status AS Gender, tbl_student.studentphoto, tbl_student.phone, tbl_student.bldgroup AS Medium, tbl_tehsil.tehsil, tbl_district.district, " +
" tbl_district.statename, tbl_classmaster.classname, tbl_section.sectionname, tbl_classmaster.classname +' '+tbl_section.sectionname AS Class, " +
" tbl_sankay.sankayname AS Stream, ISNULL(tbl_StudentAttendance.Lectures, 0) AS [Total Lecture], ISNULL(tbl_StudentAttendance.PresentDays, 0) AS [Total Present], ISNULL(tbl_StudentAttendance.Per , 0) AS [Per],tbl_StudentAttendance.RollNo ,tbl_student.SSMId,tbl_student.Wdob " +
" FROM tbl_section INNER JOIN " +
" tbl_sankay INNER JOIN " +
" tbl_classstudent ON tbl_sankay.sankaycode = tbl_classstudent.Stream INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode INNER JOIN " +
" tbl_student ON tbl_classstudent.studentno = tbl_student.studentno INNER JOIN " +
" tbl_session ON tbl_classstudent.sessioncode = tbl_session.sessioncode ON tbl_section.sectioncode = tbl_classstudent.Section INNER JOIN " +
" tbl_tehsil ON tbl_student.tehcode = tbl_tehsil.tehcode INNER JOIN " +
" tbl_district ON tbl_student.distcode = tbl_district.distcode Left join tbl_StudentAttendance on tbl_student .studentno = tbl_StudentAttendance .StudentNo " +
" WHERE (tbl_section.sectioncode = '" + cmbScetion.SelectedValue + "') AND (tbl_classstudent.ClassNo ='" + cmbClass.SelectedValue + "') " +
" AND (tbl_session.sessioncode = '" + cmbSession.SelectedValue + "') and tbl_classstudent.stdtype<>'Ex-Student' Order By tbl_student.name ;" +
" SELECT schoolname,schooladdress,schoolcity,schoolphone,affiliate_by,principal,registrationno,logoimage FROM tbl_school ");
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptCBSEMarksFrontDetail.xsd");
CrystalDecisions.CrystalReports.Engine.ReportDocument s = null;
ShowAllReports cr = new ShowAllReports();
s = new MpBoardMarksheet.ReportDesign.rptMSCCEReportFormatFront();
s.SetDataSource(ds);
s.SetParameterValue("Class", Connection.GetNextClass(cmbClass.Text.Trim()));
cr.crystalReportViewer1.ReportSource = s;
cr.crystalReportViewer1.Zoom(54);
cr.Show();
}
private void btnDueFee_Click(object sender, EventArgs e)
{
if (cmbMonth.Text.Trim() == string.Empty || cmbHostelName.Text.Trim() == string.Empty)
{
MessageBox.Show("Please Select A Month");
}
else
{
DataSet dss = Connection.GetDataSet("select Hostelcode from tbl_hostel where HostelName='" + cmbHostelName.Text + "'");
int i = cmbMonth.SelectedIndex;
i = i + 1;
string mysql = "SELECT tbl_school.schoolname, tbl_school.schooladdress, tbl_school.affiliate_by, tbl_school.logoimage, tbl_hostel.HostelName FROM tbl_hostel CROSS JOIN tbl_school where tbl_hostel.hostelcode='" + dss.Tables[0].Rows[0][0] + "' select hostelcode,roomno,bedno,sessioncode,studentno,studentname,DateName( month , DateAdd( month , tbl_hostelfee.monthno , 0 ) - 1 ) as monthno,yearno,hostelfee,messfee,feedate,feeno,rechostelfee,recmessfee,messdays from tbl_hostelfee where monthno='" + i + "' and rechostelfee!='" + 0.00 + "' and recmessfee!='" + 0.00 + "' and Hostelcode='" + dss.Tables[0].Rows[0][0] + "' ";
DataSet ds = new DataSet();
ds = Connection.GetDataSet(mysql);
if (ds.Tables[1].Rows.Count > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"\Barcodes\a\HostelfeeMothwise.xsd");
Hostal.ReportDesign.rptMonthviseHostelFee r = new Hostal.ReportDesign.rptMonthviseHostelFee();
r.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
r.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
r.SetDataSource(ds);
ShowAllReports f = new ShowAllReports();
f.Text = "Paid Hostel Fee By Month";
f.crystalReportViewer1.ReportSource = r;
f.Show();
}
else
{
//====
MessageBox.Show("Data not found.");
}
}
}
private void BtnPrint_Click(object sender, EventArgs e)
{
SMS.Library.ReportDesign.BookDetail cr = new SMS.Library.ReportDesign.BookDetail();
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();
}
public override void btnprint_Click(object sender, EventArgs e)
{
DataSet ds = Connection.GetDataSet("SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage From tbl_school");
ds.Tables.Add(Connection.GetDataTableFromDataGridView(dataGridView1));
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\AbsentReport.xsd");
Report.Student.ReportDesign.rptStudentAbsent sa = new Report.Student.ReportDesign.rptStudentAbsent();
sa.SetDataSource(ds);
ShowAllReports s1 = new ShowAllReports();
s1.crystalReportViewer1.ReportSource = sa;
s1.Show();
}
private void tbnlblPerticularStudentFeeReport_Click(object sender, EventArgs e)
{
if (txtScholarNo.Text.Trim() == string.Empty)
{
MessageBox.Show("Field Required");
}
else
{
string mysql = "select studentno from tbl_student where scholarno='" + txtScholarNo.Text + "'";
DataSet ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet(mysql);
if (ds.Tables[0].Rows.Count > 0)
{
int studentno = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
DataSet ds1 = Connection.GetDataSet("select StudentNo from tbl_roomdet where studentno='" + studentno + "'");
if (ds1 != null && ds1.Tables.Count != 0 && ds1.Tables[0].Rows.Count != 0)
{
string mysql1 = "SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school SELECT tbl_hostel.HostelName, tbl_hostelfee.roomno, tbl_hostelfee.bedno, tbl_hostelfee.studentname, DateName( month , DateAdd( month , tbl_hostelfee.monthno , 0 ) - 1 ) as monthno, tbl_hostelfee.yearno, convert(nvarchar(10),tbl_hostelfee.feedate,103) as feedate, tbl_hostelfee.rechostelfee, tbl_hostelfee.recmessfee FROM tbl_hostelfee INNER JOIN tbl_hostel ON tbl_hostelfee.hostelcode = tbl_hostel.Hostelcode WHERE tbl_hostelfee.studentno ='" + studentno + "' ";
DataSet dss = new DataSet();
dss.Clear();
dss = Connection.GetDataSet(mysql1);
if (dss.Tables[1].Rows.Count > 0)
{
dss.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"\Barcodes\a\Hostelfee.xsd");
Hostal.ReportDesign.rptHostelFee r = new Hostal.ReportDesign.rptHostelFee();
r.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
r.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
r.SetDataSource(dss);
ShowAllReports f = new ShowAllReports();
f.Text = "Hostel Fee By Student";
f.crystalReportViewer1.ReportSource = r;
f.Show();
}
else
{
//--------
MessageBox.Show("Data not found.");
}
}
else
{
MessageBox.Show("Room Not Attoted To This Student");
}
}
else
{
MessageBox.Show("Scholar No. NOt Available");
}
}
}
private void BtnPrint_Click(object sender, EventArgs e)
{
DataSet ds = Connection.GetDataSet(" SELECT tbl_booktitle.booktitle, tbl_booktitle.BarcodeImage, tbl_booktitle.BarcodeNo, tbl_school.schoolname, tbl_school.schooladdress, tbl_school.affiliate_by, tbl_school.logoimage FROM tbl_booktitle CROSS JOIN tbl_school ");
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\PrinBookBarcode.xsd");
Library.ReportDesign.printcard fr = new Library.ReportDesign.printcard();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
fr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
private void BtnAll_Click(object sender, EventArgs e)
{
DataSet ds = Connection.GetDataSet("SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school Select BusId,BusNo,RTONO,SeatsCapacity,BusMilej,ModelNo,ChechisNo,InitialReading,convert(nvarchar(20), PurchaseDate,103) as PurchaseDate,Company,convert(nvarchar(20), RegistrationIssueDate,103) as RegistrationIssueDate,convert(nvarchar(20), RegistrationExpiryDate,103) as RegistrationExpiryDate,Status from tbl_BusDetails ");
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\BusRegistrationInfo.xsd");
Bus.ReportDesign.rptBusRegistrationInfo r = new Bus.ReportDesign.rptBusRegistrationInfo();
r.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
r.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
r.SetDataSource(ds);
ShowAllReports f = new ShowAllReports();
f.Text = "All Buses Registration Information";
f.crystalReportViewer1.ReportSource = r;
f.Show();
}
private void BtnAll_Click(object sender, EventArgs e)
{
DataSet ds = Connection.GetDataSet("SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school select ROW_NUMBER() OVER (ORDER BY BusEntry) AS Serial,BusEntry,BusNo,DieselAvailable,convert(nvarchar(20), PuredDate,103) as PuredDate,PuredDiesel,BusReading,DieselRate,Amount,CurrentDate,Status from tbl_dieselDetails where PuredDate between '" + dtpStartDate.Value.Date + "' and '" + dtpLastDate.Value.Date + "' select (max(busreading)-min(busreading)) from tbl_DieselDetails where PuredDate between '" + dtpStartDate.Value.Date + "' and '" + dtpLastDate.Value.Date + "'");
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\DieselConsumption.xsd");
Bus.ReportDesign.rptDieselReportOfBus r = new Bus.ReportDesign.rptDieselReportOfBus();
r.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
r.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
r.SetDataSource(ds);
ShowAllReports f = new ShowAllReports();
f.Text = "Diesel Report Of All Buses";
f.crystalReportViewer1.ReportSource = r;
f.Show();
}
private void BtnAll_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds = Connection.GetDataSet("SELECT ROW_NUMBER() OVER (ORDER BY tbl_BusDetails.BusNo) AS Serial,tbl_BusDetails.BusNo, tbl_DailyBusEntry.DriverName, tbl_DailyBusEntry.ConductorName, convert(nvarchar(20), tbl_DailyBusEntry.CurrentDate,103) as CurrentDate, tbl_DailyBusEntry.TeacherName,tbl_DailyBusEntry.RootName FROM tbl_BusDetails INNER JOIN tbl_DailyBusEntry ON tbl_BusDetails.BusNo = tbl_DailyBusEntry.BusNo order by tbl_DailyBusEntry.BusNo SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school");
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\DailyBusInfo.xsd");
Bus.ReportDesign.rptDailyBusEntryInfoAll r = new Bus.ReportDesign.rptDailyBusEntryInfoAll();
r.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
r.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
r.SetDataSource(ds);
ShowAllReports f = new ShowAllReports();
f.Text = "Bus Detail Report";
f.crystalReportViewer1.ReportSource = r;
f.Show();
}
public override void btnprint_Click(object sender, EventArgs e)
{
try
{
DataSet ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet("select (select top 1 logoimage from tbl_school ) as SLogo,(select top 1 schoolname from tbl_school ) as SName,(select top 1 schooladdress from tbl_school ) as SAddress,convert(nvarchar(20), v.vchdate,103) as vchdate,v.vchno,v.Remark,vd.vchamt,dbo.GetAccountName(vd.accode) as AcName,dbo.GetAccountName((select top 1 ivd.accode from tbl_voucherdet ivd where ivd.vchno=vd.vchno and ivd.amttype='Dr' and ivd.vchtype='BR')) as BName from tbl_voucher v inner join tbl_voucherdet vd on v.vchtype=vd.vchtype and v.vchno=vd.vchno where amttype='Cr' and vd.vchtype='BR' and vd.vchno='" + txtvoucherno.Text.Trim() + "'");
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\BankRec.xsd");
//ds.WriteXmlSchema(@"D:\XSDSchema1.xsd");
Account.ReportDesign.BankReceipt cr = new Account.ReportDesign.BankReceipt();
cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
s.Show();
}
catch (Exception ex)
{
throw ex;
}
}
private void btnOk_Click(object sender, EventArgs e)
{
try
{
//DateTime opdate = dtpfrom.Value.Date;
c.returnconn(c.myconn);
string mysql;
mysql = " INSERT INTO TBL_ACCLEDGER (ACCNO,ACCNAME,PAYMENT,RECEIPT) ";
mysql = mysql + " select L.accno,L.accname,(case when L.clbal<0 then -1*L.clbal else 0 end) payment,(case when L.clbal>=0 then L.clbal else 0 end) receipt FROM ";
mysql = mysql + " ( SELECT m.accode as accno, m.acname as accname,m.acoptype 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.accode,p.acname,p.acoptype, (case when p.acoptype='Cr' then -1 else 1 end) *isnull(p.acopbal,0) +isnull(q.receipt,0)-isnull(q.payment,0) AS opbal, 0 AS receipt, 0 AS payment, 0 AS CLBAL FROM tbl_account AS p LEFT JOIN ( SELECT b.accode,isnull(sum(b.receipt),0) AS receipt, isnull(sum(b.payment),0) AS payment FROM ( SELECT a.accode, sum(a.vchamt) AS Receipt, 0 AS payment FROM tbl_voucherdet AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select opdate from tbl_account where accode=a.accode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.AMTtype='Dr' GROUP BY a.accode UNION ALL SELECT a.accode, 0 AS Receipt, sum(a.vchamt) AS payment FROM tbl_voucherdet AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select opdate from tbl_account where accode=a.accode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.AMTtype='Cr' GROUP BY a.accode ) b GROUP BY b.accode ) AS q ON p.accode=q.accode ) m LEFT JOIN ";
mysql = mysql + " (SELECT b.accode, 0 AS opbal,sum(b.receipt) AS receipt, sum(b.payment) AS payment, 0 AS clbal FROM (SELECT a.accode, 0 AS OPBAL, sum(a.vchamt) AS Receipt, 0 AS payment, 0 AS CLBAL FROM tbl_voucherdet AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select (case when opdate<'" + dtpfrom.Value.Date + "' then opdate else '" + dtpfrom.Value.Date + "' end) as mdate from tbl_account where accode=a.accode) and a.vchdate<='" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.AMTtype='Dr' GROUP BY a.accode UNION ALL SELECT a.accode, 0 AS Receipt, 0 AS OPBAL, sum(a.vchamt) AS payment, 0 AS CLBAL FROM tbl_voucherdet AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select (case when opdate<'" + dtpfrom.Value.Date + "' then opdate else '" + dtpfrom.Value.Date + " ' end) as mdate from tbl_account where accode=a.accode) and a.vchdate<='" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.AMTtype='Cr' group by a.accode) AS b GROUP BY b.accode ) n ";
mysql = mysql + " ON m.accode=n.accode ) L ";
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
c.connectsql("delete from tbl_accledger", c.myconn, trn);
//insert the opening balance
c.connectsql(mysql, c.myconn, trn);
trn.Commit();
//---------------------------------
mysql = "select ' Income/Expanse From " + dtpto.Text + " Upto " + dtpfrom.Text + "' as period,(select top 1 schoolname from tbl_school ) as SName,(select top 1 schooladdress from tbl_school ) as SAddress,accname,receipt,payment from(SELECT 'Fee' as accname,0 as receipt,SUM(isnull( acl.payment,0)) as payment FROM TBL_ACCLEDGER acl INNER JOIN tbl_account tac on acl.accno=tac.accode where tac.actype=3 union all SELECT acl.accname, SUM(isnull( acl.receipt,0)) as receipt,0 as payment FROM TBL_ACCLEDGER acl INNER JOIN tbl_account tac on acl.accno=tac.accode where tac.actype not in (3,1) group by acl.accname)tbl_accledger where (receipt>0 or payment > 0) order by accname ";
DataSet ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet(mysql);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TrialBook.xsd");
//ds.WriteXmlSchema(@"D:\XSDSchema1.xsd");
TrialBook cr = new TrialBook();
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);
}
}
public override void btnprint_Click(object sender, EventArgs e)
{
try
{
DataSet ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet("GetCurrentJVoucher '" + txtvoucherno.Text.Trim() + "'");
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\CJournal.xsd");
//ds.WriteXmlSchema(@"D:\XSDSchema1.xsd");
Account.ReportDesign.IJournalBook cr = new Account.ReportDesign.IJournalBook();
cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
s.Show();
}
catch (Exception ex)
{
throw ex;
}
}
private void btnShow_Click(object sender, EventArgs e)
{
try
{
if (dgvScholarDetails.RowCount > 0)
{
DataSet ds = Connection.GetDataSet("SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage From tbl_school");
ds.Tables.Add(Connection.GetDataTableFromDataGridView(dgvScholarDetails));
if (dgvScholarDetails.RowCount > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\StudentScholarStatus.xsd");
Report.Student.ReportDesign.rptScholarshipStatus cr = new Report.Student.ReportDesign.rptScholarshipStatus();
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
s.Show();
}
}
else
{
}
}
catch { }
}
private void Btn_Format1_Click(object sender, EventArgs e)
{
int Indexer = 0;
DataTable dtTable = new DataTable("dtTable");
//this.NonPrimaryFlage = Convert.ToBoolean(Connection.GetExecuteScalar("Select NonPrimary From tbl_ClassMaster Where ClassCode = '" + cmbClass.SelectedValue + "'"));
DataTable dtStudentMarksDetail = Connection.GetDataTable("[dbo].[GetViewMPCCEMarksDetail_Format1] " + cmbClass.SelectedValue + "," + cmbScetion.SelectedValue + "," + school.CurrentSessionCode + "");
DataTable dtStudentSkillsDetail = Connection.GetDataTable("Select (Case When t1.ExamCode=0 Then 'TERMI_' When t1.ExamCode=1 Then 'TERMII_' End) AS ExamCode , " +
" t2.studentno StudentNo,t2.scholarno ScholarNo,t2.Name ,t1.SkillId ,t1.SkillName ,t1.Description ,t1.Grade From ( " +
" Select s2.ExamCode,s1.SkillId ,s1.SkillName ,s2.StudentNo ,s2.Description ,s2.Grade ,s2.ClassNo ,s2.SectionCode From tbl_CCESkills s1,tbl_MPCCEStudentSkills s2 Where s1.SkillId =s2.SkillId And " +
" s2.SectionCode ='" + cmbScetion.SelectedValue + "' And s2.ClassNo='" + cmbClass.SelectedValue + "' And s2.SessionCode ='" + school.CurrentSessionCode + "') t1 ,( " +
" Select s.studentno ,s.scholarno ,s.name,cs.classno ,cs.Section ,cs.Stream From tbl_student s ,tbl_classstudent cs Where cs.studentno =s.studentno And cs.sessioncode ='" + school.CurrentSessionCode + "'" +
" And cs.classno ='" + cmbClass.SelectedValue + "' And cs.Section ='" + cmbScetion.SelectedValue + "') t2 " +
" Where t1.StudentNo =t2.studentno And t1.SectionCode =t2.Section And t1.ClassNo =t2.classno Order By t1.SkillId ,t1.SkillName");
DataTable dt = dtStudentMarksDetail.DefaultView.ToTable(true, "SessionCode", "StudentNo", "ScholarNo");
dtTable.Columns.Add("SessionCode", typeof(int)); Indexer++;
dtTable.Columns.Add("sessionname", typeof(string)); Indexer++;
dtTable.Columns.Add("scholarno", typeof(string)); Indexer++;
dtTable.Columns.Add("name", typeof(string)); Indexer++;
dtTable.Columns.Add("RollNo", typeof(string)); Indexer++;
dtTable.Columns.Add("father", typeof(string)); Indexer++;
dtTable.Columns.Add("mother", typeof(string)); Indexer++;
dtTable.Columns.Add("dob", typeof(string)); Indexer++;
dtTable.Columns.Add("SSMId", typeof(string)); Indexer++;
dtTable.Columns.Add("classname", typeof(string)); Indexer++;
dtTable.Columns.Add("studentimage", typeof(byte[])); Indexer++;
dtTable.Columns.Add("Wdob", typeof(string)); Indexer++;
for (int i = 1; i <= 10; i++)
{
dtTable.Columns.Add("SubCode_" + i, typeof(string)); Indexer++;
dtTable.Columns["SubCode_" + i].DefaultValue = "00";
dtTable.Columns.Add("Sub_" + i, typeof(string)); Indexer++;
}
for (int j = 18; j < dtStudentMarksDetail.Columns.Count; j++)
{
string Sub = dtStudentMarksDetail.Columns[j].ColumnName;
for (int i = 1; i <= 10; i++)
{
dtTable.Columns.Add("Sub_" + i + "_" + Sub, typeof(string)); Indexer++;
}
}
//Add Skills Details
for (int r = 0; r < 2; r++)
{
//string Exam = (r["ExamCode"].Equals("0"))?"FA1_":(r["ExamCode"].Equals("1"))?"FA2_":(r["ExamCode"].Equals("2"))?"SA1_":
// (r["ExamCode"].Equals("3"))?"FA3_":(r["ExamCode"].Equals("4"))?"FA4_":(r["ExamCode"].Equals("5"))?"SA2_":"";
dataReader = Connection.GetDataReader("SELECT SkillId, SkillName, Status FROM tbl_CCESkills Order By SkillId");
if (dataReader.HasRows)
{
string Exam = (r.Equals(0)) ? "TERMI_" : "TERMII_";
while (dataReader.Read())
{
dtTable.Columns.Add(Exam + dataReader["SkillName"].ToString(), typeof(string));
dtTable.Columns.Add(Exam + dataReader["SkillName"].ToString() + "_G", typeof(string));
}
}
dataReader.Close();
}
//dataReader = Connection.GetDataReader("SELECT SkillId, SkillName, Status FROM tbl_CCESkills Order By SkillId");
//if (dataReader.HasRows)
//{
// string Exam = "TERMI_" ;
// while (dataReader.Read())
// {
// dtTable.Columns.Add(Exam + dataReader["SkillName"].ToString(), typeof(string));
// dtTable.Columns.Add(Exam + dataReader["SkillName"].ToString() + "_G", typeof(string));
// }
//}
//dataReader.Close();
//End Skills Details
for (int i = 0; i < dt.Rows.Count; i++)
{
int tmp1 = 0; int C = 0; int E = 0;
DataRow[] Student_C = dtStudentMarksDetail.Select("StudentNo='" + dt.Rows[i]["StudentNo"] + "' And ScholarNo='" + dt.Rows[i]["ScholarNo"] + "' And subjecttype='Core Subject'");
DataRow[] Student_E = dtStudentMarksDetail.Select("StudentNo='" + dt.Rows[i]["StudentNo"] + "' And ScholarNo='" + dt.Rows[i]["ScholarNo"] + "' And subjecttype='Elective Subject'");
DataRow NewRow = dtTable.NewRow();
NewRow["SessionCode"] = Student_C[0]["SessionCode"];
NewRow["sessionname"] = Student_C[0]["sessionname"];
NewRow["scholarno"] = Student_C[0]["scholarno"];
NewRow["name"] = Student_C[0]["name"];
NewRow["RollNo"] = Student_C[0]["RollNo"];
NewRow["father"] = Student_C[0]["father"];
NewRow["mother"] = Student_C[0]["mother"];
NewRow["dob"] = Student_C[0]["dob"];
NewRow["SSMId"] = Student_C[0]["SSMId"];
NewRow["classname"] = Student_C[0]["classname"];
if (string.IsNullOrEmpty(Convert.ToString(Student_C[0]["studentimage"])))
{
NewRow["studentimage"] = null;
}
else
{
byte[] ByteImage = (byte[])Student_C[0]["studentimage"];
Bitmap bmp = Connection.GetImageFromByteArray(ByteImage);
//bmp = Connection.AdjustContrastOfImage(bmp, 80f);
NewRow["studentimage"] = Connection.GetByteArrayFromImage(bmp);
}
NewRow["Wdob"] = Student_C[0]["Wdob"];
for (int k = 1; k <= 10; k++)
{
if (k <= 6)
{
tmp1 = Student_C.Length;
if (tmp1 == 6)
{
NewRow["SubCode_" + k] = Student_C[C]["SubjectCode"];
NewRow["Sub_" + k] = Student_C[C]["SubjectName"];
for (int GetGrade = 18; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = Student_C[C][Sub];
}
}
else if (C < 6 && tmp1 != 0)
{
if (C < tmp1)
{
NewRow["SubCode_" + k] = Student_C[C]["SubjectCode"];
NewRow["Sub_" + k] = Student_C[C]["SubjectName"];
for (int GetGrade = 18; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = Student_C[C][Sub];
}
}
else
{
for (int GetGrade = 18; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
}
else if (tmp1 == 0)
{
for (int GetGrade = 18; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
C++;
}
else if (k > 6 && k <= 10)
{
tmp1 = Student_E.Length;
if (tmp1 == 4)
{
NewRow["SubCode_" + k] = Student_E[E]["SubjectCode"];
NewRow["Sub_" + k] = Student_E[E]["SubjectName"];
for (int GetGrade = 18; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = Student_E[E][Sub];
}
}
else if (E < 4 && tmp1 != 0)
{
if (E < tmp1)
{
NewRow["SubCode_" + k] = Student_E[E]["SubjectCode"];
NewRow["Sub_" + k] = Student_E[E]["SubjectName"];
for (int GetGrade = 18; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = Student_E[E][Sub];
}
}
else
{
for (int GetGrade = 18; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
}
else if (tmp1 == 0)
{
for (int GetGrade = 18; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
E++;
}
}
if (dtStudentSkillsDetail != null)
{
DataTable dtExam = dtStudentSkillsDetail.DefaultView.ToTable(true, "ExamCode");
DataTable dtSkill = dtStudentSkillsDetail.DefaultView.ToTable(true, "SkillId", "SkillName");
int N = Indexer;
DataView dv = dtExam.DefaultView;
dv.Sort = "ExamCode asc";
DataTable sortedDT = dv.ToTable();
//dtExam.DefaultView.Sort = "ExamCode asc";
for (int Exam = 0; Exam < sortedDT.Rows.Count; Exam++)
{
for (int skill = 0; skill < dtSkill.Rows.Count; skill++)
{
DataRow[] desc = dtStudentSkillsDetail.Select("ExamCode='" + sortedDT.Rows[Exam]["ExamCode"] + "' And SkillName='" + dtSkill.Rows[skill][1] + "' And StudentNo='" + dt.Rows[i]["StudentNo"] + "' And ScholarNo='" + dt.Rows[i]["ScholarNo"] + "'");
if (desc.Count() > 0)
{
NewRow[Indexer] = desc[0][5]; Indexer++;
NewRow[Indexer] = desc[0][7]; Indexer++;
}
}
}
Indexer = N;
}
dtTable.Rows.Add(NewRow);
}
DataTable dtschool = Connection.GetDataTable("SELECT schoolname,schooladdress,schoolcity,schoolphone,affiliate_by,principal,registrationno,logoimage,HOESIGN,PRINSIGN FROM tbl_school");
DataSet ds = new DataSet();
ds.Tables.Add(dtTable);
ds.Tables.Add(dtschool);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptMPCBSEMarksBackDetailFormat_S.xsd");
int a = dtTable.Columns.Count;
CrystalDecisions.CrystalReports.Engine.ReportDocument s = null;
if (cmbClass.Text.Trim().Equals("IX") || cmbClass.Text.Trim().Equals("X"))
{
s = new MpBoardMarksheet.ReportDesign.rptMPMarksheetFormat_D();
}
else if (cmbClass.Text.Trim().Equals("I") || cmbClass.Text.Trim().Equals("II") || cmbClass.Text.Trim().Equals("III") || cmbClass.Text.Trim().Equals("IV") ||
cmbClass.Text.Trim().Equals("V"))
{
s = new MpBoardMarksheet.ReportDesign.rptMPMarksheetFormat_B();
}
else if (cmbClass.Text.Trim().Equals("VI") || cmbClass.Text.Trim().Equals("VII") || cmbClass.Text.Trim().Equals("VIII") || cmbClass.Text.Trim().Equals("IX"))
{
s = new MpBoardMarksheet.ReportDesign.rptMPMarksheetFormat_C();
}
else
{
s = new MpBoardMarksheet.ReportDesign.rptMPMarksheetFormat_A();
}
s.SetDataSource(ds);
ShowAllReports cr = new ShowAllReports();
cr.crystalReportViewer1.ReportSource = s;
s.SetParameterValue("NCLASS", Connection.GetNextClass(cmbClass.Text.Trim()));
cr.Show();
}
private void btnOk_Click(object sender, EventArgs e)
{
try
{
DateTime cashopdate = dtpfrom.Value.Date;
DateTime bankopdate = dtpfrom.Value.Date;
decimal cashopbal = 0;
decimal bankopbal = 0;
c.returnconn(c.myconn);
string mysql;
mysql = "select isnull(acopbal,0) acopbal ,opdate from tbl_account where accode=" + valcmbcash.SelectedValue;
SqlCommand com;
com = new SqlCommand(mysql, c.myconn);
SqlDataReader reader = com.ExecuteReader();
int i = 0;
if (reader.HasRows)
{
reader.Read();
cashopbal = Convert.ToDecimal(reader["acopbal"]);
cashopdate = Convert.ToDateTime(reader["opdate"]);
}
reader.Close();
//---------------------------
mysql = "select isnull(acopbal,0) acopbal ,opdate from tbl_account where accode=" + valcmbbank.SelectedValue;
//SqlCommand com;
com = new SqlCommand(mysql, c.myconn);
reader = com.ExecuteReader();
i = 0;
if (reader.HasRows)
{
reader.Read();
bankopbal = Convert.ToDecimal(reader["acopbal"]);
bankopdate = Convert.ToDateTime(reader["opdate"]);
}
reader.Close();
//---------------------------
string str = " SELECT isnull(sum(case a.amttype when 'Dr' then a.vchamt else 0 end),0) AS Receipt, isnull(sum(case a.amttype when 'Cr' then a.vchamt else 0 end),0) AS Payment,";
str = str + " 0 as balance FROM tbl_voucherdet AS a ";
str = str + " Where a.vchdate>='" + cashopdate.Date + "'";
str = str + " and a.vchdate<'" + dtpfrom.Value.ToShortDateString() + "' and a.accode=" + valcmbcash.SelectedValue + " and a.sessioncode=" + school.CurrentSessionCode + " ";
com = new SqlCommand(str, c.myconn);
reader = com.ExecuteReader();
i = 0;
decimal mcashrec = 0;
decimal mcashpay = 0;
if (reader.HasRows)
{
reader.Read();
mcashrec = Convert.ToDecimal(reader["receipt"]);
mcashpay = Convert.ToDecimal(reader["payment"]);
}
cashopbal = cashopbal + mcashrec - mcashpay;
reader.Close();
//------------------------
str = " SELECT isnull(sum(case a.amttype when 'Dr' then a.vchamt else 0 end),0) AS Receipt, isnull(sum(case a.amttype when 'Cr' then a.vchamt else 0 end),0) AS Payment,";
str = str + " 0 as balance FROM tbl_voucherdet AS a ";
str = str + " Where a.vchdate>='" + bankopdate.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 mbankrec = 0;
decimal mbankpay = 0;
if (reader.HasRows)
{
reader.Read();
mbankrec = Convert.ToDecimal(reader["receipt"]);
mbankpay = Convert.ToDecimal(reader["payment"]);
}
bankopbal = bankopbal + mbankrec - mbankpay;
reader.Close();
//----------------
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
c.connectsql("delete from tbl_cashbook", c.myconn, trn);
DateTime mopdate = cashopdate.Date;
if (dtpfrom.Value.Date < cashopdate.Date)
{
dtpfrom.Value = cashopdate.Date;
}
//insert the opening balance
str = " Insert into tbl_cashbook (recno,r_date,r_particulars,r_cash,r_bank,isbold) ";
str = str + " values (0,'" + dtpfrom.Text + "' ,'Open. Bal.'," + cashopbal + "," + bankopbal + ",'Y')";
c.connectsql(str, c.myconn, trn);
trn.Commit();
//---------------------------------
//get all voucher of the day - fromdate
//str = " SELECT a.accode as accno,b.vchno, b.vchdate,b.vchtype, isnull(b.remarks,'') as narration,a.amttype , ";
//str = str + " (case a.amttype when 'Cr' then a.vchamt else 0 end) AS Payment, (case a.amttype when 'Dr' then a.vchamt else 0 end) AS Receipt,c.acname,isnull(c.lf,'') as lf,c.actype,a.basicsal,isnull(a.vchnarr,'') as vchnarr,isnull(b.entrytype,'') as entrytype,isnull(a.salarytype,'') as salarytype ";
//str = str + " FROM tbl_voucherdet AS a, tbl_voucher AS b,tbl_account c Where a.vchdate='"+ dtpfrom.Value.ToShortDateString() +"'";
//str = str + " and a.sessioncode=" + school.CurrentSessionCode + " and a.vchno = b.vchno and a.VCHTYPE = b.VCHTYPE and a.accode=c.accode order by b.entrytype desc,a.vchtype,vchno,a.entryno";
str = " SELECT a.accode as accno,b.vchno, b.vchdate,b.vchtype, isnull(b.remark,'') as narration,a.amttype , ";
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,( select top 1 (select top 1 acname from tbl_account where accode=ia.accode ) from tbl_voucherdet ia where ia.accode<>dbo.GetAccountCode('CS') and ia.vchno=b.vchno and ia.vchtype=a.vchtype ) as acname ,isnull(c.lf,'') as lf,c.actype,isnull(a.basicsal,0) as basicsal,isnull(a.vchnarr,'') as vchnarr,isnull(null,'') as entrytype,isnull(a.salarytype,'') as salarytype ";
str = str + " FROM tbl_voucherdet AS a, tbl_voucher AS b,tbl_account c Where (a.vchtype='JV' ";
str = str + " or (a.vchtype='CP' and a.amttype='Dr') or (a.vchtype='CR' and a.amttype='Cr')) and a.vchdate='" + dtpfrom.Value.ToShortDateString() + "'";
str = str + " and a.sessioncode=" + school.CurrentSessionCode + " and a.vchno = b.vchno and a.VCHTYPE = b.VCHTYPE and a.accode=c.accode and a.accode<>dbo.GetAccountCode('CS') order by a.vchtype,vchno,a.entryno";
DataSet ds = new DataSet();
c.filldataset(false, ref ds, str, c.myconn);
if (ds.Tables[0].Rows.Count > 0)
{
int rwcnt = 0;
int linecnt = 1;
decimal deduct = 0;
decimal pcash = 0;
decimal pbank = 0;
decimal rcash = 0;
decimal rbank = 0;
bool issubfound = false;
string isline = "N";
string basicparticulars;
string basicpay;
string vchtype = "";
bool rnarr = false;
bool pnarr = false;
int cplineno = 0;
int crlineno = 0;
int bplineno = 0;
int brlineno = 0;
int subcnt = 0;
int pline = 1;
int rline = 1;
basicparticulars = "";
basicpay = "";
while (rwcnt < ds.Tables[0].Rows.Count)
{
issubfound = false;
isline = "N";
vchtype = Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]);
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "JV")
{
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Cr")
{
if (rnarr == false)
{
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["Entrytype"]) == "S") //salary addition/deduction voucher
{
trn = c.myconn.BeginTransaction();
//str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_details) values (" + linecnt + ",'"+ds.Tables[0].Rows[rwcnt]["vchno"]+"','" + ds.Tables[0].Rows[rwcnt]["narration"] + " - Deductions " + "',' ' )";
str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_details) values (" + linecnt + ",'','" + ds.Tables[0].Rows[rwcnt]["narration"] + " - Deductions " + "',' ' )";
c.connectsql(str, c.myconn, trn);
trn.Commit();
linecnt++;
rnarr = true; //means only only one time print
}
}
str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where b.amttype='Cr' and a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'";
DataSet ds1 = new DataSet();
c.filldataset(false, ref ds1, str, c.myconn);
subcnt = 0;
if (ds1.Tables[0].Rows.Count > 0)
{
while (subcnt < ds1.Tables[0].Rows.Count)
{
trn = c.myconn.BeginTransaction();
str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details) values (" + (linecnt) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + " - " + ds1.Tables[0].Rows[subcnt]["narration"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")";
c.connectsql(str, c.myconn, trn);
trn.Commit();
issubfound = true;
linecnt++;
subcnt++;
}
}
if (issubfound == true)
{
isline = "Y";
}
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["basicsal"]) == "Y")
{
basicparticulars = Convert.ToString(ds.Tables[0].Rows[rwcnt]["acname"]);
basicpay = Convert.ToString(ds.Tables[0].Rows[rwcnt]["receipt"]);
}
else
{
trn = c.myconn.BeginTransaction();
//str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details,isline) values (" + (linecnt + 1) + ",'" + ds.Tables[0].Rows[rwcnt]["lf"] + "','" + ds.Tables[0].Rows[rwcnt]["vchno"] + "','" + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["vchnarr"] + "'," + ds.Tables[0].Rows[rwcnt]["receipt"] + ",'" + isline + "')";
str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details,isline) values (" + (linecnt + 1) + ",'" + ds.Tables[0].Rows[rwcnt]["lf"] + "','','" + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["vchnarr"] + "'," + ds.Tables[0].Rows[rwcnt]["receipt"] + ",'" + isline + "')";
c.connectsql(str, c.myconn, trn);
trn.Commit();
linecnt++;
}
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["Salarytype"]) == "DE")
{
deduct = deduct + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]);
}
}
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Dr")
{
//seach debit account in subledger
//trn = c.myconn.BeginTransaction();
//str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details) values (" + (rwcnt + 1) + ",'" + ds.Tables[0].Rows[rwcnt]["vchno"] + "','" + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["payment"] + ")";
//c.connectsql(str, c.myconn, trn);
//trn.Commit();
if (deduct > 0)
{
trn = c.myconn.BeginTransaction();
str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_details,isline) values (" + (linecnt) + ",'','Less - Deductions ','" + deduct + "','Y')";
c.connectsql(str, c.myconn, trn);
trn.Commit();
linecnt++;
}
if (pnarr == false)
{
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["Entrytype"]) == "S") //salary addition/deduction voucher
{
trn = c.myconn.BeginTransaction();
str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details) values (" + linecnt + ",'','" + ds.Tables[0].Rows[rwcnt]["narration"] + "',' ' )";
c.connectsql(str, c.myconn, trn);
trn.Commit();
linecnt++;
pnarr = true; //means only only one time print
}
}
str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where b.amttype='Dr' and a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'";
DataSet ds1 = new DataSet();
c.filldataset(false, ref ds1, str, c.myconn);
subcnt = 0;
if (ds1.Tables[0].Rows.Count > 0)
{
while (subcnt < ds1.Tables[0].Rows.Count)
{
trn = c.myconn.BeginTransaction();
str = "insert into tbl_cashbook (recno,p_lf,p_date,p_particulars,p_details) values (" + (linecnt) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")";
c.connectsql(str, c.myconn, trn);
trn.Commit();
linecnt++;
subcnt++;
}
}
trn = c.myconn.BeginTransaction();
str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details,isline) values (" + (linecnt) + ",'" + ds.Tables[0].Rows[rwcnt]["vchno"] + "','" + ds.Tables[0].Rows[rwcnt]["acname"] + "'," + ds.Tables[0].Rows[rwcnt]["payment"] + ",'Y')";
c.connectsql(str, c.myconn, trn);
linecnt++;
trn.Commit();
if (deduct > 0)
{
trn = c.myconn.BeginTransaction();
str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details,isline) values (" + (linecnt) + ",'','Less - Deductions ','" + deduct + "','N')";
c.connectsql(str, c.myconn, trn);
trn.Commit();
linecnt++;
}
if (basicpay != "")
{
trn = c.myconn.BeginTransaction();
str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_details,isline) values (" + (linecnt) + ",'','" + basicparticulars + "'," + basicpay + ",'Y')";
c.connectsql(str, c.myconn, trn);
trn.Commit();
linecnt++;
}
}
pline = linecnt;
rline = linecnt;
}
////////////////////////
// Journal Voucher Part Finished
///
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "CR")
{
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Cr")
{
crlineno = 0;
trn = c.myconn.BeginTransaction();
if (rline >= pline)
{
str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_cash) values (" + (rline) + ",'" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "',' From : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["receipt"] + ")";
}
else
{
str = " Update tbl_cashbook set r_date='" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "' , r_particulars = ' From : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "',r_cash=" + ds.Tables[0].Rows[rwcnt]["receipt"] + " where recno = " + rline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
rcash = rcash + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]);
crlineno = linecnt;
rline++;
linecnt++;
//cashopbal = cashopbal + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]);
str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'";
DataSet ds1 = new DataSet();
c.filldataset(false, ref ds1, str, c.myconn);
subcnt = 0;
if (ds1.Tables[0].Rows.Count > 0)
{
while (subcnt < ds1.Tables[0].Rows.Count)
{
trn = c.myconn.BeginTransaction();
if (rline >= pline)
{
str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details) values (" + (rline) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','To : " + ds1.Tables[0].Rows[subcnt]["acname"] + " -- " + ds1.Tables[0].Rows[subcnt]["narration"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")";
}
else
{
str = "update tbl_cashbook set r_lf='" + ds1.Tables[0].Rows[subcnt]["lf"] + "',r_date='' ,r_particulars = ' To : " + ds1.Tables[0].Rows[subcnt]["acname"] + " -- " + ds1.Tables[0].Rows[subcnt]["narration"] + "',r_details=" + ds1.Tables[0].Rows[subcnt]["subledgamt"] + " where recno=" + rline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
rline++;
linecnt++;
subcnt++;
}
}
if (subcnt > 0)
{
trn = c.myconn.BeginTransaction();
if (rline >= pline)
{
str = "insert into tbl_cashbook (recno,r_details,isline) values (" + (linecnt) + ",'" + ds.Tables[0].Rows[rwcnt]["receipt"] + "','Y')";
}
else
{
str = "update tbl_cashbook set r_details='" + ds.Tables[0].Rows[rwcnt]["receipt"] + "',isline='Y' where recno=" + rline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
rline++;
linecnt++;
if (pline > rline)
{
rline = pline;
}
else
{
pline = rline;
}
}
}
}
//cashopbal = cashopbal - Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]);
//cplineno
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "CP")
{
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Dr")
{
cplineno = 0;
trn = c.myconn.BeginTransaction();
if (pline >= rline)
{
str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_cash) values (" + (pline) + ",'" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "',' To : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["payment"] + ")";
}
else
{
str = "update tbl_cashbook set p_date='" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "' ,p_particulars=' To : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "',p_cash=" + ds.Tables[0].Rows[rwcnt]["payment"] + " where recno=" + pline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
bplineno = linecnt;
pcash = pcash + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]);
pline++;
linecnt++;
//cashopbal = cashopbal - Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]);
str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'";
DataSet ds1 = new DataSet();
c.filldataset(false, ref ds1, str, c.myconn);
subcnt = 0;
if (ds1.Tables[0].Rows.Count > 0)
{
while (subcnt < ds1.Tables[0].Rows.Count)
{
trn = c.myconn.BeginTransaction();
if (pline >= rline)
{
str = "insert into tbl_cashbook (recno,p_lf,p_date,p_particulars,p_details) values (" + (pline) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")";
}
else
{
str = "update tbl_cashbook set recno=" + pline + ",p_lf='" + ds1.Tables[0].Rows[subcnt]["lf"] + "',p_date='' , p_particulars='" + ds1.Tables[0].Rows[subcnt]["acname"] + "',p_details='" + ds1.Tables[0].Rows[subcnt]["subledgamt"] + " where recno=" + pline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
pline++;
linecnt++;
subcnt++;
}
}
if (subcnt > 0)
{
trn = c.myconn.BeginTransaction();
if (pline > rline)
{
str = "insert into tbl_cashbook (recno,p_details,isline) values (" + (pline) + ",'" + ds.Tables[0].Rows[rwcnt]["Payment"] + "','Y')";
}
else
{
str = "update tbl_cashbook set p_details=" + ds.Tables[0].Rows[rwcnt]["Payment"] + ",isline='Y' where recno=" + pline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
pline++;
linecnt++;
if (pline > rline)
{
rline = pline;
}
else
{
pline = rline;
}
}
}
}
///
//for bank receipt
///
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "BR")
{
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Cr")
{
brlineno = 0;
trn = c.myconn.BeginTransaction();
if (rline >= pline)
{
str = "insert into tbl_cashbook (recno,r_date,r_particulars,r_bank) values (" + (rline) + ",'" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "',' From : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["receipt"] + ")";
}
else
{
str = " Update tbl_cashbook set r_date='" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "' , r_particulars = ' From : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "',r_bank=" + ds.Tables[0].Rows[rwcnt]["receipt"] + " where recno = " + rline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
brlineno = linecnt;
rbank = rbank + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]);
rline++;
linecnt++;
bankopbal = bankopbal + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["receipt"]);
str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'";
DataSet ds1 = new DataSet();
c.filldataset(false, ref ds1, str, c.myconn);
subcnt = 0;
if (ds1.Tables[0].Rows.Count > 0)
{
while (subcnt < ds1.Tables[0].Rows.Count)
{
trn = c.myconn.BeginTransaction();
//str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details) values (" + (linecnt) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + " -- "+ds1.Tables[0].Rows[subcnt]["narration"] +"'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")";
if (rline >= pline)
{
str = "insert into tbl_cashbook (recno,r_lf,r_date,r_particulars,r_details) values (" + (rline) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','To : " + ds1.Tables[0].Rows[subcnt]["acname"] + " -- " + ds1.Tables[0].Rows[subcnt]["narration"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")";
}
else
{
str = "update tbl_cashbook set r_lf='" + ds1.Tables[0].Rows[subcnt]["lf"] + "',r_date='' ,r_particulars = ' To : " + ds1.Tables[0].Rows[subcnt]["acname"] + " -- " + ds1.Tables[0].Rows[subcnt]["narration"] + "',r_details=" + ds1.Tables[0].Rows[subcnt]["subledgamt"] + " where recno=" + rline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
rline++;
linecnt++;
subcnt++;
}
}
if (subcnt > 0)
{
trn = c.myconn.BeginTransaction();
if (rline >= pline)
{
str = "insert into tbl_cashbook (recno,r_details,isline) values (" + (linecnt) + ",'" + ds.Tables[0].Rows[rwcnt]["receipt"] + "','Y')";
}
else
{
str = "update tbl_cashbook set r_details=" + ds.Tables[0].Rows[rwcnt]["receipt"] + ",isline='Y' where recno=" + rline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
rline++;
linecnt++;
if (pline > rline)
{
rline = pline;
}
else
{
pline = rline;
}
}
}
}
///
/// for bank payment
///
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["vchtype"]) == "BP")
{
if (Convert.ToString(ds.Tables[0].Rows[rwcnt]["amttype"]) == "Dr")
{
bplineno = 0;
trn = c.myconn.BeginTransaction();
if (pline >= rline)
{
str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_bank) values (" + (pline) + ",'" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "',' To : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "'," + ds.Tables[0].Rows[rwcnt]["payment"] + ")";
}
else
{
str = "update tbl_cashbook set p_date='" + vchtype + " - " + ds.Tables[0].Rows[rwcnt]["vchno"] + "' ,p_particulars=' To : " + ds.Tables[0].Rows[rwcnt]["acname"] + " - " + ds.Tables[0].Rows[rwcnt]["narration"] + "',p_bank=" + ds.Tables[0].Rows[rwcnt]["payment"] + " where recno=" + pline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
bplineno = linecnt;
pbank = pbank + Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]);
pline++;
linecnt++;
bankopbal = bankopbal - Convert.ToDecimal(ds.Tables[0].Rows[rwcnt]["payment"]);
str = "select a.acname,b.subledgamt,isnull(a.lf,'') as lf from tbl_account a,tbl_subledger b where a.accode=b.subledgercode and b.sessioncode=" + school.CurrentSessionCode + " and b.vchno=" + ds.Tables[0].Rows[rwcnt]["vchno"] + " and vchtype='" + ds.Tables[0].Rows[rwcnt]["vchtype"] + "' and vchdate='" + dtpfrom.Value.Date + "'";
DataSet ds1 = new DataSet();
c.filldataset(false, ref ds1, str, c.myconn);
subcnt = 0;
if (ds1.Tables[0].Rows.Count > 0)
{
while (subcnt < ds1.Tables[0].Rows.Count)
{
trn = c.myconn.BeginTransaction();
if (pline >= rline)
{
str = "insert into tbl_cashbook (recno,p_lf,p_date,p_particulars,p_details) values (" + (pline) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")";
}
else
{
str = "update tbl_cashbook set p_lf='" + ds1.Tables[0].Rows[subcnt]["lf"] + "',p_date='' , p_particulars='" + ds1.Tables[0].Rows[subcnt]["acname"] + "',p_details='" + ds1.Tables[0].Rows[subcnt]["subledgamt"] + " where recno=" + pline;
}
//str = "insert into tbl_cashbook (recno,r_lf,p_date,p_particulars,p_details) values (" + (linecnt) + ",'" + ds1.Tables[0].Rows[subcnt]["lf"] + "','','" + ds1.Tables[0].Rows[subcnt]["acname"] + "'," + ds1.Tables[0].Rows[subcnt]["subledgamt"] + ")";
c.connectsql(str, c.myconn, trn);
trn.Commit();
pline++;
linecnt++;
subcnt++;
}
}
if (subcnt > 0)
{
trn = c.myconn.BeginTransaction();
if (pline >= rline)
{
str = "insert into tbl_cashbook (recno,p_details,isline) values (" + (linecnt) + ",'" + ds.Tables[0].Rows[rwcnt]["Payment"] + "','Y')";
}
else
{
str = "update tbl_cashbook set p_details='" + ds.Tables[0].Rows[rwcnt]["Payment"] + "',isline='Y' where recno=" + pline;
}
c.connectsql(str, c.myconn, trn);
trn.Commit();
pline++;
linecnt++;
if (pline > rline)
{
rline = pline;
}
else
{
pline = rline;
}
}
}
}
rwcnt++;
}
//-----------
str = " SELECT sum( (case a.amttype when 'Dr' then a.vchamt else 0 end)) AS Payment, sum((case a.amttype when 'Cr' then a.vchamt else 0 end)) AS Receipt ";
str = str + " FROM tbl_voucherdet AS a, tbl_voucher AS b,tbl_account c Where (a.vchtype='JV' ";
str = str + " or (a.vchtype='CP' and a.amttype='Dr') or (a.vchtype='CR' and a.amttype='Cr')) and a.vchdate='" + dtpfrom.Value.ToShortDateString() + "'";
str = str + " and a.sessioncode=" + school.CurrentSessionCode + " and a.vchno = b.vchno and a.VCHTYPE = b.VCHTYPE and a.accode=c.accode and a.accode<>dbo.GetAccountCode('CS')";
ds = new DataSet();
c.filldataset(false, ref ds, str, c.myconn);
//-----------
decimal pamt = 0, ramt = 0, rsamt = 0;
if (ds.Tables[0].Rows.Count > 0)
{
pamt = Convert.ToDecimal(ds.Tables[0].Rows[0]["Payment"].ToString());
ramt = Convert.ToDecimal(ds.Tables[0].Rows[0]["Receipt"].ToString());
}
rsamt = (ramt + cashopbal) - pamt;
trn = c.myconn.BeginTransaction();
str = "insert into tbl_cashbook (recno) values (" + (linecnt) + " )";
c.connectsql(str, c.myconn, trn);
linecnt++;
str = "insert into tbl_cashbook (recno,p_date,p_particulars,p_cash,p_bank,isbold) values (" + (linecnt) + ",'" + "" + "','Closing Balance'," + rsamt + "," + bankopbal + ",'Y')";
c.connectsql(str, c.myconn, trn);
linecnt++;
str = "insert into tbl_cashbook (recno,r_particulars,r_details,r_cash,r_bank,p_particulars,p_details,p_cash,p_bank,isbold) values (" + (linecnt) + ",'Total '," + (ramt + cashopbal) + ",0," + rbank + ",'Total '," + (pamt + rsamt) + ",0," + pbank + ",'Y')";
c.connectsql(str, c.myconn, trn);
trn.Commit();
linecnt++;
}
//---------------------------------
//mysql = "select '',' For " + dtpfrom.Text + " to " + dtpto.Text + "' as period,accno,accname,accdate,convert(varchar(10),vchdate,105) as vchdate,vchtype,vchno,narration,amt,receipt,payment,balance,baltype,recno,amttype from tbl_accledger order by vchdate ";
string str1 = "select '',' 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,* from tbl_cashbook order by recno";
ds = new DataSet();
ds.Clear();
ds = Connection.GetDataSet(str1);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\CashBook.xsd");
//ds.WriteXmlSchema(@"D:\XSDSchema1.xsd");
//Account.ReportDesign.CashBook
Account.ReportDesign.CashBook cr = new Account.ReportDesign.CashBook();
cr.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
s.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnBackView_Click(object sender, EventArgs e)
{
int Indexer = 0;
DataTable dtTable = new DataTable("dtTable");
this.NonPrimaryFlage = Convert.ToBoolean(Connection.GetExecuteScalar("Select NonPrimary From tbl_ClassMaster Where ClassCode = '" + cmbClass.SelectedValue + "'"));
DataTable dtStudentMarksDetail = Connection.GetDataTable("[dbo].[GetViewMCCEMarksDetail] " + cmbClass.SelectedValue + "," + cmbScetion.SelectedValue + "," + school.CurrentSessionCode + "");
DataTable dtStudentSkillsDetail = Connection.GetDataTable("Select (Case When t1.ExamCode=0 Then 'SA1_' When t1.ExamCode=1 Then 'SA2_' When t1.ExamCode=2 Then 'SA3_' End) AS ExamCode , " +
" t2.studentno StudentNo,t2.scholarno ScholarNo,t2.Name ,t1.SkillId ,t1.SkillName ,t1.Description ,t1.Grade From ( " +
" Select s2.ExamCode,s1.SkillId ,s1.SkillName ,s2.StudentNo ,s2.Description ,s2.Grade ,s2.ClassNo ,s2.SectionCode From tbl_CCESkills s1,tbl_MCCEStudentSkills s2 Where s1.SkillId =s2.SkillId And " +
" s2.SectionCode ='" + cmbScetion.SelectedValue + "' And s2.ClassNo='" + cmbClass.SelectedValue + "' And s2.SessionCode ='" + school.CurrentSessionCode + "') t1 ,( " +
" Select s.studentno ,s.scholarno ,s.name,cs.classno ,cs.Section ,cs.Stream From tbl_student s ,tbl_classstudent cs Where cs.studentno =s.studentno And cs.sessioncode ='" + school.CurrentSessionCode + "'" +
" And cs.classno ='" + cmbClass.SelectedValue + "' And cs.Section ='" + cmbScetion.SelectedValue + "') t2 " +
" Where t1.StudentNo =t2.studentno And t1.SectionCode =t2.Section And t1.ClassNo =t2.classno Order By t1.SkillId ,t1.SkillName");
DataTable dt = dtStudentMarksDetail.DefaultView.ToTable(true, "SessionCode", "StudentNo", "ScholarNo");
dtTable.Columns.Add("SessionCode", typeof(int)); Indexer++;
dtTable.Columns.Add("ScholarNo", typeof(string)); Indexer++;
dtTable.Columns.Add("Name", typeof(string)); Indexer++;
dtTable.Columns.Add("Height", typeof(string)); Indexer++;
dtTable.Columns.Add("Width", typeof(string)); Indexer++;
for (int i = 1; i <= 9; i++)
{
dtTable.Columns.Add("SubCode_" + i, typeof(string)); Indexer++;
dtTable.Columns["SubCode_" + i].DefaultValue = "00";
dtTable.Columns.Add("Sub_" + i, typeof(string)); Indexer++;
}
for (int j = 9; j < dtStudentMarksDetail.Columns.Count; j++)
{
string Sub = dtStudentMarksDetail.Columns[j].ColumnName;
for (int i = 1; i <= 9; i++)
{
dtTable.Columns.Add("Sub_" + i + "_" + Sub, typeof(string)); Indexer++;
}
}
for (int r = 0; r < 1; r++)
{
//string Exam = (r["ExamCode"].Equals("0"))?"FA1_":(r["ExamCode"].Equals("1"))?"FA2_":(r["ExamCode"].Equals("2"))?"SA1_":
// (r["ExamCode"].Equals("3"))?"FA3_":(r["ExamCode"].Equals("4"))?"FA4_":(r["ExamCode"].Equals("5"))?"SA2_":"";
dataReader = Connection.GetDataReader("SELECT SkillId, SkillName, Status FROM tbl_CCESkills Order By SkillId");
if (dataReader.HasRows)
{
string Exam = (r.Equals(0)) ? "SA1_" : "SA2_";
while (dataReader.Read())
{
dtTable.Columns.Add(Exam + dataReader["SkillName"].ToString(), typeof(string));
dtTable.Columns.Add(Exam + dataReader["SkillName"].ToString() + "_G", typeof(string));
}
}
dataReader.Close();
}
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow[] Student = dtStudentMarksDetail.Select("StudentNo='" + dt.Rows[i]["StudentNo"] + "' And ScholarNo='" + dt.Rows[i]["ScholarNo"] + "'");
DataRow NewRow = dtTable.NewRow();
NewRow["SessionCode"] = Student[0]["SessionCode"]; NewRow["ScholarNo"] = Student[0]["ScholarNo"]; NewRow["Name"] = Student[0]["Name"];
NewRow["Height"] = Student[0]["Height"]; NewRow["Width"] = Student[0]["Width"];
for (int subject = 1; subject <= Student.Length; subject++)
{
NewRow["SubCode_" + subject] = Student[subject - 1]["SubjectCode"];
NewRow["Sub_" + subject] = Student[subject - 1]["SubjectName"];
}
for (int subject = 1; subject <= Student.Length; subject++)
{
for (int GetGrade = 11; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string tstr = string.Empty;
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
if (Student[subject - 1][Sub].ToString().Trim().Equals("-1"))
{
NewRow["Sub_" + subject + "_" + Sub] = "AB";
}
else
{
NewRow["Sub_" + subject + "_" + Sub] = Student[subject - 1][Sub];
}
}
}
if (dtStudentSkillsDetail != null)
{
DataTable dtExam = dtStudentSkillsDetail.DefaultView.ToTable(true, "ExamCode");
DataTable dtSkill = dtStudentSkillsDetail.DefaultView.ToTable(true, "SkillId", "SkillName");
if (this.NonPrimaryFlage)
{
for (int Exam = 0; Exam < dtExam.Rows.Count; Exam++)
{
for (int skill = 0; skill < dtSkill.Rows.Count; skill++)
{
string DI = string.Empty;
string Grade = string.Empty;
DataRow[] desc = dtStudentSkillsDetail.Select("ExamCode='" + dtExam.Rows[Exam]["ExamCode"] + "' And SkillName='" + dtSkill.Rows[skill][1] + "' And StudentNo='" + dt.Rows[i]["StudentNo"] + "' And ScholarNo='" + dt.Rows[i]["ScholarNo"] + "'");
for (int des = 0; des < desc.Length; des++)
{
DI = DI + desc[des]["SkillName"].ToString();
Grade = desc[0]["Grade"].ToString();
}
NewRow[dtExam.Rows[Exam]["ExamCode"].ToString() + dtSkill.Rows[skill][1].ToString()] = DI.TrimEnd(',');
NewRow[dtExam.Rows[Exam]["ExamCode"].ToString() + dtSkill.Rows[skill][1].ToString() + "_G"] = Grade;
}
}
}
else
{
int N = Indexer;
DataView dv = dtExam.DefaultView;
dv.Sort = "ExamCode asc";
DataTable sortedDT = dv.ToTable();
//dtExam.DefaultView.Sort = "ExamCode asc";
for (int Exam = 0; Exam < sortedDT.Rows.Count; Exam++)
{
for (int skill = 0; skill < dtSkill.Rows.Count; skill++)
{
DataRow[] desc = dtStudentSkillsDetail.Select("ExamCode='" + sortedDT.Rows[Exam]["ExamCode"] + "' And SkillName='" + dtSkill.Rows[skill][1] + "' And StudentNo='" + dt.Rows[i]["StudentNo"] + "' And ScholarNo='" + dt.Rows[i]["ScholarNo"] + "'");
NewRow[Indexer] = desc[0][5]; Indexer++;
NewRow[Indexer] = desc[0][7]; Indexer++;
}
}
Indexer = N;
}
}
dtTable.Rows.Add(NewRow);
}
DataTable dtschool = Connection.GetDataTable("SELECT schoolname,schooladdress,schoolcity,schoolphone,affiliate_by,principal,registrationno,logoimage,HOESIGN,PRINSIGN FROM tbl_school");
DataSet ds = new DataSet();
ds.Tables.Add(dtTable);
ds.Tables.Add(dtschool);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptCBSEMarksBackDetail.xsd");
int a = dtTable.Columns.Count;
CrystalDecisions.CrystalReports.Engine.ReportDocument s = null;
if (cmbClass.Text.Trim().Equals("VI") || cmbClass.Text.Trim().Equals("VII") || cmbClass.Text.Trim().Equals("VIII") || cmbClass.Text.Trim().Equals("IX") || cmbClass.Text.Trim().Equals("X"))
{
s = new MpBoardMarksheet.ReportDesign.rptMCCEReportFormatBack();
}
else
{
s = new MpBoardMarksheet.ReportDesign.rptMCCEReportFormatBack_A();
}
s.SetDataSource(ds);
s.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
s.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape;
ShowAllReports cr = new ShowAllReports();
cr.crystalReportViewer1.ReportSource = s;
cr.crystalReportViewer1.Zoom(100);
s.SetParameterValue("Class", cmbClass.Text.Trim());
cr.Show();
}
private void btnBackView_Click(object sender, EventArgs e)
{
int Indexer = 0;
DataTable dtTable = new DataTable("dtTable");
this.NonPrimaryFlage = Convert.ToBoolean(Connection.GetExecuteScalar("Select NonPrimary From tbl_ClassMaster Where ClassCode = '" + cmbClass.SelectedValue + "'"));
DataTable dtStudentMarksDetail = Connection.GetDataTable("[dbo].[GetViewMPCCEMarksDetail] " + cmbClass.SelectedValue + "," + cmbScetion.SelectedValue + "," + school.CurrentSessionCode + "");
DataTable dt = dtStudentMarksDetail.DefaultView.ToTable(true, "SessionCode", "StudentNo", "ScholarNo");
dtTable.Columns.Add("SessionCode", typeof(int)); Indexer++;
dtTable.Columns.Add("ScholarNo", typeof(string)); Indexer++;
dtTable.Columns.Add("Name", typeof(string)); Indexer++;
for (int i = 1; i <= 14; i++)
{
dtTable.Columns.Add("SubCode_" + i, typeof(string)); Indexer++;
dtTable.Columns["SubCode_" + i].DefaultValue = "00";
dtTable.Columns.Add("Sub_" + i, typeof(string)); Indexer++;
}
for (int j = 9; j < dtStudentMarksDetail.Columns.Count; j++)
{
string Sub = dtStudentMarksDetail.Columns[j].ColumnName;
for (int i = 1; i <= 14; i++)
{
dtTable.Columns.Add("Sub_" + i + "_" + Sub, typeof(string)); Indexer++;
}
}
int tk = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
PerT1 = 0; PerT2 = 0; PerT3 = 0;
T1 = T2 = T3 = 0;
DataRow[] Student = dtStudentMarksDetail.Select("StudentNo='" + dt.Rows[i]["StudentNo"] + "' And ScholarNo='" + dt.Rows[i]["ScholarNo"] + "'");
DataRow NewRow = dtTable.NewRow();
NewRow["SessionCode"] = Student[0]["SessionCode"];
NewRow["ScholarNo"] = Student[0]["ScholarNo"];
NewRow["Name"] = Student[0]["Name"];
for (int k = 1; k <= 14; k++)
{
if (k <= Student.Length - 4)
{
NewRow["SubCode_" + k] = Student[k - 1]["SubjectCode"];
NewRow["Sub_" + k] = Student[k - 1]["SubjectName"];
for (int GetGrade = 9; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = Student[k - 1][Sub];
if (Sub == "TERMI")
{
if (Student[k - 1][Sub].Equals(DBNull.Value) || Student[k - 1][Sub].Equals(string.Empty))
{
//-----------do something
}
else
{
PerT1 = PerT1 + Convert.ToDecimal(Student[k - 1][Sub]);
if (Convert.ToDecimal(Student[k - 1][Sub]) < 34)
{
T1 = 1;
}
}
}
if (Sub == "TERMII")
{
if (Student[k - 1][Sub].Equals(DBNull.Value) || Student[k - 1][Sub].Equals(string.Empty))
{
//-----------do something
}
else
{
PerT2 = PerT2 + Convert.ToDecimal(Student[k - 1][Sub]);
if (Convert.ToDecimal(Student[k - 1][Sub]) < 34)
{
T2 = 1;
}
}
}
if (Sub == "TERMIII")
{
if (Student[k - 1][Sub].Equals(DBNull.Value) || Student[k - 1][Sub].Equals(string.Empty))
{
//-----------do something
}
else
{
PerT3 = PerT3 + Convert.ToDecimal(Student[k - 1][Sub]);
if (Convert.ToDecimal(Student[k - 1][Sub]) < 34)
{
T3 = 1;
}
}
}
}
}
else if (k > Student.Length - 4 && k <= 6)
{
for (int GetGrade = 9; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
else if (k > 6 && k <= 10)
{
if (tk == 0)
{
tk = Student.Length - 4;
tk = tk + 1;
}
else
{
tk++;
}
NewRow["SubCode_" + k] = Student[tk - 1]["SubjectCode"];
NewRow["Sub_" + k] = Student[tk - 1]["SubjectName"];
for (int GetGrade = 9; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
NewRow["Sub_" + k + "_" + Sub] = Student[tk - 1][Sub];
}
}
else if (k == 11)
{
tk = Student.Length - 4;
for (int GetGrade = 9; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
if (Sub == "TERMI")
{
if (PerT1 > 0)
{
NewRow["Sub_" + k + "_" + Sub] = Math.Round(((PerT1 * 100) / (tk * 100)), 2);
}
else
{
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
if (Sub == "TERMII")
{
if (PerT2 > 0)
{
NewRow["Sub_" + k + "_" + Sub] = Math.Round(((PerT2 * 100) / (tk * 100)), 2);
}
else
{
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
if (Sub == "TERMIII")
{
if (PerT3 > 0)
{
NewRow["Sub_" + k + "_" + Sub] = Math.Round(((PerT3 * 100) / (tk * 100)), 2);
}
else
{
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
}
}
else if (k == 12)
{
for (int GetGrade = 9; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
if (Sub == "TERMI")
{
NewRow["Sub_" + k + "_" + Sub] = Sub + " TEACHER REMARK";
}
if (Sub == "TERMII")
{
NewRow["Sub_" + k + "_" + Sub] = Sub + " TEACHER REMARK";
}
if (Sub == "TERMIII")
{
NewRow["Sub_" + k + "_" + Sub] = Sub + " TEACHER REMARK";
}
}
}
else if (k == 13)
{
for (int GetGrade = 9; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
if (Sub == "TERMI")
{
if (PerT1 > 0)
{
NewRow["Sub_" + k + "_" + Sub] = PerT1;
}
else
{
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
if (Sub == "TERMII")
{
if (PerT2 > 0)
{
NewRow["Sub_" + k + "_" + Sub] = PerT2;
}
else
{
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
if (Sub == "TERMIII")
{
if (PerT3 > 0)
{
NewRow["Sub_" + k + "_" + Sub] = PerT3;
}
else
{
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
}
}
else if (k == 14)
{
for (int GetGrade = 9; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
if (Sub == "TERMI")
{
if (PerT1 > 0)
{
NewRow["Sub_" + k + "_" + Sub] = (PerT1 > 0 && T1 == 0) ? "Pass" : "Fail";
}
else
{
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
if (Sub == "TERMII")
{
if (PerT2 > 0)
{
NewRow["Sub_" + k + "_" + Sub] = (PerT2 > 0 && T2 == 0) ? "Pass" : "Fail";
}
else
{
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
if (Sub == "TERMIII")
{
if (PerT3 > 0)
{
NewRow["Sub_" + k + "_" + Sub] = (PerT3 > 0 && T3 == 0) ? "Pass" : "Fail";
}
else
{
NewRow["Sub_" + k + "_" + Sub] = "";
}
}
}
}
}
dtTable.Rows.Add(NewRow);
}
dtTable.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptMPCBSEMarksBackDetail.xsd");
int a = dtTable.Columns.Count;
CrystalDecisions.CrystalReports.Engine.ReportDocument s = null;
if (cmbClass.Text.Trim().Equals("IX") || cmbClass.Text.Trim().Equals("X"))
{
s = new rptCCEReportBackFormat1();
s.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
s.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLegal;
}
else if (cmbClass.Text.Trim().Equals("I") || cmbClass.Text.Trim().Equals("II") || cmbClass.Text.Trim().Equals("III") || cmbClass.Text.Trim().Equals("IV") ||
cmbClass.Text.Trim().Equals("V") || cmbClass.Text.Trim().Equals("VI") || cmbClass.Text.Trim().Equals("VII") || cmbClass.Text.Trim().Equals("VIII"))
{
s = new MpBoardMarksheet.ReportDesign.rptMPCCEReportBackFormat2();
s.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
s.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
}
else
{
s = new rptCCEReportBackFormat3();
s.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
s.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA3;
}
s.SetDataSource(dtTable);
ShowAllReports cr = new ShowAllReports();
s.SetParameterValue("class", cmbClass.Text.Trim());
s.SetParameterValue("nclass", Connection.GetNextClass(cmbClass.Text.Trim()));
s.SetParameterValue("nsession", Connection.GetNextSeesion());
cr.crystalReportViewer1.ReportSource = s;
cr.Show();
}
private void btnAYS_Click(object sender, EventArgs e)
{
int Indexer = 0;
DataTable dtTable = new DataTable("dtTable");
DataTable dt = clsClassStudent.GetDataOfStudent();
DataTable dtschstatus = clsClassStudent.GetDataOfClassStudent();
dtTable.Columns.Add("studentno", typeof(int)); Indexer++;
dtTable.Columns.Add("name", typeof(string)); Indexer++;
dtTable.Columns.Add("scholarno", typeof(string)); Indexer++;
dtTable.Columns.Add("father", typeof(string)); Indexer++;
dtTable.Columns.Add("mother", typeof(string)); Indexer++;
dtTable.Columns.Add("phone", typeof(string)); Indexer++;
dtTable.Columns.Add("schappno_I", typeof(string)); Indexer++;
dtTable.Columns.Add("Status_I", typeof(string)); Indexer++;
dtTable.Columns.Add("DistriStatus_I", typeof(string)); Indexer++;
dtTable.Columns.Add("schappno_II", typeof(string)); Indexer++;
dtTable.Columns.Add("Status_II", typeof(string)); Indexer++;
dtTable.Columns.Add("DistriStatus_II", typeof(string)); Indexer++;
dtTable.Columns.Add("schappno_III", typeof(string)); Indexer++;
dtTable.Columns.Add("Status_III", typeof(string)); Indexer++;
dtTable.Columns.Add("DistriStatus_III", typeof(string)); Indexer++;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow[] Student101 = dtschstatus.Select("studentno='" + dt.Rows[i]["studentno"] + "' And classno='101'");
DataRow[] Student102 = dtschstatus.Select("studentno='" + dt.Rows[i]["studentno"] + "' And classno='102'");
DataRow[] Student103 = dtschstatus.Select("studentno='" + dt.Rows[i]["studentno"] + "' And classno='103'");
DataRow NewRow = dtTable.NewRow();
NewRow["studentno"] = dt.Rows[i]["studentno"]; NewRow["name"] = dt.Rows[i]["name"];
NewRow["scholarno"] = dt.Rows[i]["scholarno"]; NewRow["father"] = dt.Rows[i]["father"];
NewRow["mother"] = dt.Rows[i]["mother"]; NewRow["phone"] = dt.Rows[i]["phone"];
if (Student101.Length > 0)
{
NewRow["schappno_I"] = Student101[0]["schappno"]; NewRow["Status_I"] = Student101[0]["Status"]; NewRow["DistriStatus_I"] = Student101[0]["DistriStatus"];
}
else
{
NewRow["schappno_I"] = ""; NewRow["Status_I"] = ""; NewRow["DistriStatus_I"] = "";
}
if (Student102.Length > 0)
{
NewRow["schappno_II"] = Student102[0]["schappno"]; NewRow["Status_II"] = Student102[0]["Status"]; NewRow["DistriStatus_II"] = Student102[0]["DistriStatus"];
}
else
{
NewRow["schappno_II"] = ""; NewRow["Status_II"] = ""; NewRow["DistriStatus_II"] = "";
}
if (Student103.Length > 0)
{
NewRow["schappno_III"] = Student103[0]["schappno"]; NewRow["Status_III"] = Student103[0]["Status"]; NewRow["DistriStatus_III"] = Student103[0]["DistriStatus"];
}
else
{
NewRow["schappno_III"] = ""; NewRow["Status_III"] = ""; NewRow["DistriStatus_III"] = "";
}
dtTable.Rows.Add(NewRow);
}
DataSet ds = Connection.GetDataSet("SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage From tbl_school");
ds.Tables.Add(dtTable);
if (dgvScholarDetails.RowCount > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\StudentSchStsThreeYear.xsd");
Report.Student.ReportDesign.rptScholarshipStatusComplete cr = new Report.Student.ReportDesign.rptScholarshipStatusComplete();
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
s.Show();
}
}
public override void btnprint_Click(object sender, EventArgs e)
{
if (dgvMStudent.Visible == true)
{
if (dgvMStudent.RowCount > 0)
{
if (ChkIdDueGreater.Checked)
{
if (chkClassWise.Checked == true && chkSection.Checked == false)
{
DataView dv = dtTotalFee.DefaultView;
dv.RowFilter = "ClassNo='" + cmbClass.SelectedValue + "' And SessionCode='" + cmbSession.SelectedValue + "' and [Due Fee]>0";
dv.Sort = "Name";
dgvMStudent.DataSource = dv;
}
else if (chkClassWise.Checked == true && chkSection.Checked == true)
{
DataView dv = dtTotalFee.DefaultView;
dv.RowFilter = "ClassNo='" + cmbClass.SelectedValue + "' And SessionCode='" + cmbSession.SelectedValue + "' And SectionNo='" + cmbSection.SelectedValue + "' and [Due Fee]>0";
dv.Sort = "Name";
dgvMStudent.DataSource = dv;
}
else
{
DataView dv = dtTotalFee.DefaultView;
dv.RowFilter = "[Due Fee]>0";
dv.Sort = "Name";
dgvMStudent.DataSource = dv;
}
}
DataSet ds = Connection.GetDataSet("SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage From tbl_school");
ds.Tables.Add(Connection.GetDataTableFromDataGridView(dgvMStudent));
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\HDueFee.xsd");
rptHDueFee fr1 = new rptHDueFee();
fr1.SetDataSource(ds);
ShowAllReports s1 = new ShowAllReports();
s1.crystalReportViewer1.ReportSource = fr1;
fr1.SetParameterValue("Session", cmbSession.Text);
if (chkClassWise.Checked && !chkClassWise.Checked)
{
fr1.SetParameterValue("ReportTitle", cmbStudentStatus.Text + " Due Fee Detail For Class - " + cmbClass.Text.Trim());
}
else if (chkClassWise.Checked && chkSection.Checked)
{
fr1.SetParameterValue("ReportTitle", cmbStudentStatus.Text + " Due Fee Detail For Class - " + cmbClass.Text.Trim() + " " + cmbSection.Text.Trim());
}
else
{
fr1.SetParameterValue("ReportTitle", cmbStudentStatus.Text + " Due Fee Detail");
}
s1.Show();
}
}
if (dgvSStudent.Visible == true)
{
if (dgvSStudent.RowCount > 0)
{
DataSet ds = Connection.GetDataSet(" SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage From tbl_school SELECT tbl_student.StudentNo,tbl_student.scholarno, tbl_student.name, tbl_student.father,tbl_student.phone,tbl_classmaster.ClassCode, tbl_classmaster.classname + ' ' + tbl_section.sectionname + ' ' + tbl_sankay.sankayname AS Classname,(Case When busfacility=1 Then ISNULL(tbl_StopDetails.BusStopNo, '-') Else '-' End) AS BusStopNo,(Case When busfacility=1 Then ISNULL(tbl_StopDetails.StopName, '-') Else '-' End) AS StopName,(Case When busfacility=1 Then ISNULL(tbl_StopDetails.StopFee, 0) Else 0 End) AS StopFee FROM tbl_classmaster INNER JOIN " +
" tbl_classstudent ON tbl_classmaster.classcode = tbl_classstudent.classno INNER JOIN tbl_section ON tbl_classstudent.Section = tbl_section.sectioncode INNER JOIN tbl_sankay ON tbl_classstudent.Stream = tbl_sankay.sankaycode INNER JOIN tbl_student ON tbl_classstudent.studentno = tbl_student.studentno LEFT OUTER JOIN tbl_StopDetails ON tbl_student.BusStopNo = tbl_StopDetails.BusStopNo " +
" WHERE (tbl_student.StudentNo = '" + tstudentno + "') AND (tbl_classstudent.sessioncode = '" + school.CurrentSessionCode + "')");
//----
dtPaidFee = Connection.GetDataTable("SELECT SUM(fr.consession) AS [ConsesFee],SUM(fr.latefee) AS [LateFee] FROM Tbl_Received fr where fr.date<='" + Convert.ToDateTime(dtpDate.Value).ToString("MM/dd/yyyy") + "' and fr.studentno='" + tstudentno + "' ");
//-------
ds.Tables.Add((DataTable)dgvSStudent.DataSource);
ds.Tables.Add(dtPaidFee);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\HSDueFee.xsd");
rptHSDueFee fr1 = new rptHSDueFee();
fr1.SetDataSource(ds);
ShowAllReports s1 = new ShowAllReports();
s1.crystalReportViewer1.ReportSource = fr1;
fr1.SetParameterValue("Session", cmbSession.Text);
fr1.SetParameterValue("ReportTitle", " Due Fee Detail");
s1.Show();
}
}
}
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);
}
}
