private void btnNormarl_Click(object sender, EventArgs e)
{
DataTable dtStudent = Connection.GetDataTableFromDataGridViewDefaultDataType(dataGridView1);
if (dtStudent != null)
{
DataRow[] r = dtStudent.Copy().Select("Select='True'", "Name");
if (r != null && r.Length > 0)
{
if (string.IsNullOrEmpty(dtStudent.Rows[0]["logoimage"].ToString()))
{
DataSet dsi = new DataSet();
dsi = Connection.JGetDefaultImage();
if (dsi.Tables[0].Rows.Count > 0)
{
TData = (byte[])(dsi.Tables[0].Rows[0]["Image"]);
//byte[] ByteImage = (byte[])dtStudent.Rows[0]["logoimage"];
Bitmap bmp = Connection.GetImageFromByteArray(TData);
bmp = Connection.AdjustContrastOfImage(bmp, 80f);
byte[] ByteImage1 = Connection.GetByteArrayFromImage(bmp);
dtStudent = r.CopyToDataTable();
dtStudent.TableName = "Student Details";
dtStudent.Rows[0]["Watermark"] = Connection.GetByteArrayFromImage(bmp);
}
}
else
{
byte[] ByteImage = (byte[])dtStudent.Rows[0]["logoimage"];
Bitmap bmp = Connection.GetImageFromByteArray(ByteImage);
bmp = Connection.AdjustContrastOfImage(bmp, 80f);
byte[] ByteImage1 = Connection.GetByteArrayFromImage(bmp);
dtStudent = r.CopyToDataTable();
dtStudent.TableName = "Student Details";
dtStudent.Rows[0]["Watermark"] = Connection.GetByteArrayFromImage(bmp);
}
dtStudent.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\SmartCard.xsd");
rptStudentSmartCardWithoutBarcode fr = new rptStudentSmartCardWithoutBarcode();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
fr.SetDataSource(dtStudent);
ShowAllReports s = new ShowAllReports();
s.Text = "Smart/Identity Card";
fr.SetParameterValue("Title", "IDENTITY CARD");
fr.SetParameterValue("ICStatus", true);
fr.DataDefinition.FormulaFields["RollNo"].Text = "'0'";
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
else
{
MessageBox.Show("Please Select Student...");
}
}
else
{
MessageBox.Show("Please Select Student...");
}
}
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,'Mr/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] " +
" 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;
rptCCEReportFormat3 s3 = new rptCCEReportFormat3();
s3.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
s3.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA3;
ShowAllReports cr = new ShowAllReports();
if (cmbClass.Text.Trim().Equals("IX") || cmbClass.Text.Trim().Equals("X"))
{
s = new rptCCEMarkFrontEnd();
s.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
s.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLegal;
s.SetDataSource(ds);
cr.crystalReportViewer1.ReportSource = s;
}
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 rptCCEReportFormat2();
s.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
s.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA3;
s.SetDataSource(ds);
s.SetParameterValue("Class", Connection.GetNextClass(cmbClass.Text.Trim()));
cr.crystalReportViewer1.ReportSource = s;
}
else
{
s3.SetDataSource(ds);
s3.SetParameterValue("Class", Connection.GetNextClass(cmbClass.Text.Trim()));
cr.crystalReportViewer1.ReportSource = s3;
}
cr.crystalReportViewer1.Zoom(54);
cr.Show();
}
private void btnAttendanceBetweenReport_Click(object sender, EventArgs e)
{
if (dataGridView1.RowCount > 0)
{
DataSet ds = Connection.GetDataSet(" SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school");
DataTable dt = Connection.GetDataTableFromDataGridView(dataGridView1);
DataView dv = Connection.GetDataSet("Select EmpNo,EmpName,AccountNo,BankName From tbl_EmployeeInfo").Tables[0].DefaultView;
dt.Columns.Add("AccountNo"); dt.Columns.Add("BankName");
for (int i = 0; i < dt.Rows.Count; i++)
{
dv.RowFilter = "EmpNo =" + dt.Rows[i][0];
dt.Rows[i]["AccountNo"] = dv[0][2];
dt.Rows[i]["BankName"] = dv[0][3];
}
ds.Tables.Add(dt);
rptSalaryStatementForBank cr1 = new rptSalaryStatementForBank();
cr1.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr1.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\SalaryStatementForBank.xsd");
cr1.SetDataSource(ds);
cr1.SetParameterValue("Month", new DateTime(Convert.ToInt16(cmbYear.Text), cmbMonth.SelectedIndex, 1));
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr1;
s.Show();
}
else
{
MessageBox.Show("Record Not Shown.\n\tPlease Select Month And Press Tab Key.");
}
}
public override void btnsave_Click(object sender, EventArgs e)
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells[2].Value == null)
{
MessageBox.Show("Please Fill All Field Properly!");
return;
}
}
// this changes i have done for bareli school date 10/02/2016. i put !=true and i comment all previous work//
if (btnShowAttendance.Visible != true)
{
if (DialogResult.Yes.Equals(MessageBox.Show("Are You Sure To Save Attendance Date Of \" " + txtAttandanceDate.Text + " \" ?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question)))
{
System.Data.SqlClient.SqlTransaction trn = Connection.GetMyConnection().BeginTransaction();
Connection.SqlTransection("Delete From tbl_Attendance Where AttendanceDate = '" + txtAttandanceDate.Value.Date.ToString("MM/dd/yyyy") + "'",
Connection.MyConnection, trn);
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
//foreach (DataGridViewRow r in dataGridView1.Rows)
//{
Connection.AllPerform("insert into tbl_Attendance (EmpNo, AttendanceDate, Attendance)values('" + dataGridView1.Rows[i].Cells[0].Value + "','" + txtAttandanceDate.Text + "','" + dataGridView1.Rows[i].Cells[2].Value + "')");
//Connection.SqlTransection("insert into tbl_Attendance (EmpNo, AttendanceDate, Attendance, TimeIn, TimeOut) "+
// " Values('" + r.Cells[0].Value + "','" + txtAttandanceDate.Text + "','" +
// r.Cells[2].Value + "','" + r.Cells[5].Value + "','" +
// r.Cells[6].Value + "')",
// Connection .MyConnection , trn );
//}
}
trn.Commit();
}
}
else
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
DataSet ds11 = Connection.GetDataSet("select count(*) from tbl_Attendance where AttendanceDate='" + txtAttandanceDate.Value.Date + "' and empNo='" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "'");
if (ds11.Tables[0].Rows[0][0].ToString() == "0")
{
Connection.AllPerform("insert into tbl_Attendance (EmpNo, AttendanceDate, Attendance)values('" + dataGridView1.Rows[i].Cells[0].Value + "','" + txtAttandanceDate.Text + "','" + dataGridView1.Rows[i].Cells[2].Value + "')");
}
}
}
str1 = "SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school";
str1 = str1 + " SELECT tbl_Attendance.EmpNo, tbl_EmployeeInfo.EmpName, tbl_Attendance.Attendance,tbl_Attendance.AttendanceDate FROM tbl_Attendance INNER JOIN tbl_EmployeeInfo ON tbl_Attendance.EmpNo = tbl_EmployeeInfo.EmpNo WHERE (tbl_Attendance.AttendanceDate = '" + txtAttandanceDate.Value.Date + "') order by tbl_Attendance.EmpNo";
DataSet ds = Connection.GetDataSet(str1);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TodaysAttendance.xsd");
AttendanceReport cr1 = new AttendanceReport();
cr1.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr1.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
cr1.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr1;
s.Show();
}
public override void btnprint_Click(object sender, EventArgs e)
{
if (dataGridView1.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";
dataGridView1.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";
dataGridView1.DataSource = dv;
}
else
{
DataView dv = dtTotalFee.DefaultView;
dv.RowFilter = "[Due Fee]>0";
dv.Sort = "Name";
dataGridView1.DataSource = dv;
}
}
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\DueFee.xsd");
rptDueFee fr1 = new rptDueFee();
fr1.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr1.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
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();
}
}
public override void btnprint_Click(object sender, EventArgs e)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\BirthList.xsd");
BirthDayReports fr = new BirthDayReports();
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();
}
public override void btnprint_Click(object sender, EventArgs e)
{
DataSet ds = Connection.GetDataSet("SELECT tbl_subwiseclass.board, tbl_subwiseclass.subjecttype, tbl_subject.subjectname, tbl_subwiseclass.sankayname, tbl_school.schoolname, tbl_school.schooladdress, tbl_school.logoimage,tbl_school.affiliate_by, tbl_classmaster.classname FROM tbl_subject INNER JOIN tbl_subwiseclass ON tbl_subject.subjectno = tbl_subwiseclass.subjectno INNER JOIN tbl_school ON tbl_subwiseclass.board = tbl_school.affiliate_by INNER JOIN tbl_classmaster ON tbl_subwiseclass.classno = tbl_classmaster.classcode WHERE (tbl_subwiseclass.classno = '" + classno + "') order by tbl_subwiseclass.subjecttype ");
if (ds != null)
{
// ds.WriteXmlSchema(@"D:\Barcodes\a\classwisesubject.xsd");
classwisesubject fr = new classwisesubject();
fr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
}
public override void btnprint_Click(object sender, EventArgs e)
{
if (dataGridView1.RowCount > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\AllFeeTransaction.xsd");
rptAllTransactionReport fr = new rptAllTransactionReport();
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();
}
}
public void GetFeeDetailsH()
{
try
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\DailyFeeRcpt.xsd");
rptDailyFeeReport fr = new rptDailyFeeReport();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
fr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
catch (Exception ex)
{
}
}
private void btnReport_Click(object sender, EventArgs e)
{
if (dataGridView1.RowCount > 0)
{
DataSet ds = Connection.GetDataSet("SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage From tbl_school");
ds.Tables.Add(Connection.GetDataTableFromDataGridView(dataGridView1));
if (rdoBusStopDetail.Checked)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\StopDetail.xsd");
rptBusstopInfo r = new rptBusstopInfo();
r.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
r.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
r.SetDataSource(ds);
ShowAllReports f = new ShowAllReports();
f.Text = "Report";
f.crystalReportViewer1.ReportSource = r;
r.SetParameterValue("ReportTitle", "Bus Stop Details");
f.Show();
}
else if (rdoClassWise.Checked)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\ListOfStudentByBus.xsd");
rptBusstopClasswiseInfo r = new rptBusstopClasswiseInfo();
r.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
r.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
r.SetDataSource(ds);
txtSearch.Text = "Search By Stop";
ShowAllReports f = new ShowAllReports();
f.Text = "Report";
f.crystalReportViewer1.ReportSource = r;
if (chkClassWise.Checked && !chkSection.Checked)
{
r.SetParameterValue("ReportTitle", "Bus Stop Details For Class-" + cmbClass.Text);
}
else if (chkClassWise.Checked && chkSection.Checked)
{
r.SetParameterValue("ReportTitle", "Bus Stop Details For Class-" + cmbClass.Text + "-" + cmbSection.Text);
}
else
{
r.SetParameterValue("ReportTitle", "Student Stop Details ");
}
f.Show();
}
}
}
public void GetDuplicateFessSP()
{
DataSet ds = Connection.GetDataSet("SELECT tbl_student.studentno, tbl_student.scholarno, tbl_student.name, tbl_student.dob, tbl_classmaster.classname + ' ' + tbl_section.sectionname AS Class " +
" , tbl_session.sessionname, tbl_tehsil.tehsil, tbl_district.district, tbl_district.statename, tbl_student.P_address, tbl_student.C_address, tbl_student.father,tbl_student.mother " +
" , tbl_student.casttype, tbl_student.bloodgroup, tbl_student.marr_status AS gender, tbl_student.phone, (CASE WHEN tbl_student.busfacility = 1 THEN tbl_StopDetails.StopName ELSE ISNULL(tbl_student.P_address,tbl_tehsil.tehsil) END) AS StopName " +
" , tbl_StopDetails.StopFee, tbl_feemaster.rcptno " +
" , tbl_feemaster.rcptdate, tbl_feemaster.rcvdamt, tbl_feemaster.totalpaidfee, tbl_feemaster.dueamount, tbl_feemaster.latefee, tbl_feemaster.totfeeamt, tbl_feemaster.monthname " +
" , tbl_feemaster.consession FROM tbl_section INNER JOIN tbl_session INNER JOIN tbl_feemaster ON tbl_session.sessioncode = tbl_feemaster.sessioncode INNER JOIN tbl_student ON " +
" tbl_feemaster.studentno = tbl_student.studentno INNER JOIN tbl_classstudent ON tbl_student.studentno = tbl_classstudent.studentno AND tbl_session.sessioncode = tbl_classstudent.sessioncode " +
" INNER JOIN tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode ON tbl_section.sectioncode = tbl_classstudent.Section INNER JOIN tbl_sankay ON tbl_classstudent.Stream = tbl_sankay.sankaycode " +
" INNER JOIN tbl_district ON tbl_student.distcode = tbl_district.distcode INNER JOIN tbl_tehsil ON tbl_student.tehcode = tbl_tehsil.tehcode AND tbl_district.distcode = tbl_tehsil.distcode LEFT OUTER JOIN " +
" tbl_StopDetails ON tbl_student.BusStopNo = tbl_StopDetails.BusStopNo WHERE (tbl_classstudent.sessioncode = '" + school.CurrentSessionCode + "') AND (tbl_feemaster.rcptno = '" + txtfeeno.Text.Trim() + "'); " +
" SELECT schoolname, schooladdress, affiliate_by, logoimage, schoolcity, schoolphone, principal, registrationno FROM tbl_school ");
try
{
bool b = Convert.ToBoolean(Connection.GetExecuteScalar("Select DualSlipType From tbl_FeeHeads"));
if (b)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptFeeReceipt.xsd");
rptFeeSlip cr = new rptFeeSlip();
cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
cr.SetParameterValue("Title", "DUPLICATE FEE RECEIPT");
s.Show();
}
else
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptFeeReceiptSingle.xsd");
rptFeeSlipSingle cr = new rptFeeSlipSingle();
cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
//System.Drawing.Printing.PaperSize pr = new System.Drawing.Printing.PaperSize("Atul", 4, 5.5);
//Size sz = new Size(4, 6);
s.crystalReportViewer1.ReportSource = cr;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperEnvelopePersonal;
s.Show();
}
}
catch { }
}
private void btnSalaryPaymentReport_Click(object sender, EventArgs e)
{
if (dataGridView1.RowCount > 0)
{
DataSet ds = Connection.GetDataSet(" SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school");
DataTable dt = Connection.GetDataTableFromDataGridView(dataGridView1);
ds.Tables.Add(dt);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\SalaryPaymentRecord.xsd");
rptSalaryPaymentRecord cr1 = new rptSalaryPaymentRecord();
cr1.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
cr1.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
cr1.SetDataSource(ds);
cr1.SetParameterValue("Month", new DateTime(Convert.ToInt16(cmbYear.Text), cmbMonth.SelectedIndex, 1));
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr1;
s.Show();
}
}
private void btnok_Click(object sender, EventArgs e)
{
if (cmbempcategory.Text != "")
{
string select = cmbempcategory.SelectedItem.ToString();
string str;
if (select == "All Employee")
{
//str = "select convert(varchar, EmpNo),EmpName,FathersName,Gender,Address,convert(varchar,doj,103)DOJ,convert(varchar,dob,103)DOB,IsActive,tbl_EmployeeType .Detail Employee_Type, designation Designation,salaryrate Salary,rsa Spl_All,pf PF,lic Lic,loan Loan,splinctv Spl_Inc,pfnumber PF_No,aleave A_Leave,balleave Bal_Leave,el El,elbal ElBal,enclleave EnCashLeave,contactno Contact_No,da DA,daamt,pfamt,hra HRA,ESIC,ESICAmt from tbl_employeeinfo,tbl_EmployeeType where tbl_EmployeeInfo .EmpTypeId =tbl_EmployeeType .EmpTypeId ";
//str = str + " SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school ";
//ds = Connection.GetDataSet(str);
str = "SELECT tbl_EmployeeInfo.EmpNo, tbl_EmployeeInfo.EmpName, tbl_EmployeeInfo.FathersName, tbl_EmployeeInfo.Address, tbl_EmployeeInfo.DOJ, tbl_EmployeeInfo.DOB, tbl_EmployeeInfo.Gender, tbl_EmployeeInfo.salaryrate, tbl_EmployeeInfo.pf, tbl_EmployeeInfo.lic, tbl_EmployeeInfo.loan, tbl_EmployeeInfo.designation, tbl_EmployeeInfo.photo, tbl_EmployeeInfo.rsa, tbl_EmployeeInfo.pfnumber, tbl_EmployeeInfo.splinctv, tbl_EmployeeInfo.HRA, tbl_EmployeeInfo.contactno, tbl_EmployeeInfo.DAAmt, tbl_EmployeeInfo.PFAmt, tbl_EmployeeInfo.ESICAmt, tbl_EmployeeInfo.AccountNo, tbl_EmployeeInfo.BankName, tbl_EmployeeInfo.DA, tbl_EmployeeInfo.ESIC, tbl_EmployeeType.Detail FROM tbl_EmployeeInfo INNER JOIN tbl_EmployeeType ON tbl_EmployeeInfo.EmpTypeId = tbl_EmployeeType.EmpTypeId ";
str = str + " SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school ";
ds = Connection.GetDataSet(str);
}
else
{
//str = "select convert(varchar, EmpNo),EmpName,FathersName,Gender,Address,convert(varchar,doj,103)DOJ,convert(varchar,dob,103)DOB,IsActive,tbl_EmployeeType .Detail Employee_Type, designation Designation,salaryrate Salary,rsa Spl_All,pf PF,lic Lic,loan Loan,splinctv Spl_Inc,pfnumber PF_No,aleave A_Leave,balleave Bal_Leave,el El,elbal ElBal,enclleave EnCashLeave,contactno Contact_No,da DA,daamt,pfamt,hra HRA,ESIC,ESICAmt from tbl_employeeinfo,tbl_EmployeeType where tbl_EmployeeInfo .EmpTypeId =tbl_EmployeeType .EmpTypeId and tbl_EmployeeType.Detail='" + select + "'";
//str = str + " SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school ";
//ds = Connection.GetDataSet(str);
str = "SELECT tbl_EmployeeInfo.EmpNo, tbl_EmployeeInfo.EmpName, tbl_EmployeeInfo.FathersName, tbl_EmployeeInfo.Address, tbl_EmployeeInfo.DOJ, tbl_EmployeeInfo.DOB, tbl_EmployeeInfo.Gender, tbl_EmployeeInfo.salaryrate, tbl_EmployeeInfo.pf, tbl_EmployeeInfo.lic, tbl_EmployeeInfo.loan, tbl_EmployeeInfo.designation, tbl_EmployeeInfo.photo, tbl_EmployeeInfo.rsa, tbl_EmployeeInfo.pfnumber, tbl_EmployeeInfo.splinctv, tbl_EmployeeInfo.HRA, tbl_EmployeeInfo.contactno, tbl_EmployeeInfo.DAAmt, tbl_EmployeeInfo.PFAmt, tbl_EmployeeInfo.ESICAmt, tbl_EmployeeInfo.AccountNo, tbl_EmployeeInfo.BankName, tbl_EmployeeInfo.DA, tbl_EmployeeInfo.ESIC, tbl_EmployeeType.Detail FROM tbl_EmployeeInfo INNER JOIN tbl_EmployeeType ON tbl_EmployeeInfo.EmpTypeId = tbl_EmployeeType.EmpTypeId Where tbl_EmployeeType.Detail=N'" + select + "'";
str = str + " SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school ";
ds = Connection.GetDataSet(str);
}
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("There is no Record.");
return;
}
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\EmployeeListReport.xsd");
rptEmployeeList rl = new rptEmployeeList();
rl.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
rl.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
rl.SetDataSource(ds);
ShowAllReports f = new ShowAllReports();
f.crystalReportViewer1.ReportSource = rl;
f.Show();
}
else
{
MessageBox.Show("Please Select Any One Category.");
}
}
public override void btnprint_Click(object sender, EventArgs e)
{
string str = "";
str = "SELECT dbo.tbl_student_enquiry.name, dbo.tbl_student_enquiry.dob, dbo.tbl_student_enquiry.P_address, dbo.tbl_student_enquiry.father, dbo.tbl_student_enquiry.mother, dbo.tbl_student_enquiry.m_tongue, dbo.tbl_student_enquiry.casttype, dbo.tbl_student_enquiry.RegDate, dbo.tbl_student_enquiry.phone,dbo.tbl_student_enquiry.marr_status , dbo.tbl_classmaster.classname, dbo.tbl_student_enquiry.studentno FROM dbo.tbl_classmaster INNER JOIN dbo.tbl_student_enquiry ON dbo.tbl_classmaster.classcode = dbo.tbl_student_enquiry.admclass where tbl_student_enquiry.studentno='" + StuId + "'";
str = str + "SELECT schoolname, schooladdress, affiliate_by, logoimage, schoolphone FROM tbl_school ";
str = str + "SELECT CONVERT(varchar(10), GETDATE(), 103) AS Expr1";
DataSet ds = Connection.GetDataSet(str);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\Enquiry.xsd");
Enquiryform fr = new Enquiryform();
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 button1_Click(object sender, EventArgs e)
{
try
{
DateTime opdate = dtpfrom.Value.Date;
c.returnconn(c.myconn);
string mysql;
mysql = "INSERT INTO TBL_LEDGER (ACCNO,ACCNAME,openbal,Reciept,PAYMENT) ";
mysql = mysql + "select L.accno,l.accname,L.opbal,l.receipt,L.payment FROM (";
mysql = mysql + "SELECT m.BHM_COD as accno,m.BHM_DES, m.BHM_DESH as accname,(case when m.opbal>0 then 'Cr' else 'Dr' end) as baltype, isnull(m.opbal,0) as opbal, isnull(n.receipt,0) as receipt,isnull(n.payment,0) as payment,(isnull(m.opbal,0)+isnull(n.receipt,0)- isnull(n.payment,0)) as clbal FROM (";
mysql = mysql + "SELECT p.GRP_COD,p.BHM_COD ,p.BHM_DESH,p.BHM_DES, isnull(p.ope_bld,0) +isnull(q.receipt,0)-isnull(q.payment,0) AS opbal, 0 AS receipt, 0 AS payment, 0 AS CLBAL FROM tbl_LedgerAcc AS p LEFT JOIN ( SELECT b.acccode,isnull(sum(b.receipt),0) AS receipt, isnull(sum(b.payment),0) AS payment FROM ( SELECT a.AccCode ,sum(a.Amount) AS Receipt, 0 AS payment FROM tbl_VoucherDetail AS a, tbl_voucher AS b WHERE a.VouchNo=b.VouchNo and a.vchdate>= (select OpenDate from tbl_LedgerAcc where bhm_cod=a.AccCode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.amount>0 GROUP BY a.AccCode UNION ALL SELECT a.AccCode, 0 AS Receipt, sum(a.Amount) AS payment FROM tbl_VoucherDetail AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.VchDate> (select OpenDate from tbl_LedgerAcc where BHM_COD=a.AccCode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.VchType=b.VchType and A.Amount < 0 GROUP BY a.AccCode ) b GROUP BY b.AccCode ) AS q ON p.BHM_COD =q.acccode ) m ";
mysql = mysql + "LEFT JOIN (SELECT b.AccCode, 0 AS opbal,sum(b.receipt) AS receipt, sum(b.payment) AS payment, 0 AS clbal FROM (";
mysql = mysql + "SELECT a.AccCode, 0 AS OPBAL, sum(a.Amount) AS Receipt, 0 AS payment, 0 AS CLBAL FROM tbl_VoucherDetail AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate= (select (case when OpenDate<='" + dtpfrom.Value.Date + "' then OpenDate else '" + dtpfrom.Value.Date + "' end) as mdate from tbl_LedgerAcc where BHM_COD=a.AccCode) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.Amount >0 GROUP BY a.AccCode ";
mysql = mysql + "UNION ALL SELECT a.AccCode, 0 AS Receipt, 0 AS OPBAL, sum(a.Amount) AS payment, 0 AS CLBAL FROM tbl_VoucherDetail AS a, tbl_voucher AS b WHERE a.vchno=b.vchno and a.vchdate>= (select (case when OpenDate<='" + dtpfrom.Value.Date + "' then OpenDate else '" + dtpfrom.Value.Date + "' end) as mdate from tbl_LedgerAcc where BHM_COD =a.AccCode ) and a.vchdate<'" + dtpfrom.Value.Date + "' and a.vchtype=b.vchtype and A.Amount <0 group by a.AccCode ) AS b ";
mysql = mysql + "GROUP BY b.AccCode ) n ON m.BHM_COD =n.AccCode ) L order by L.BHM_DES ";
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
c.connectsql("delete from tbl_ledger", c.myconn, trn);
//insert the opening balance
c.connectsql(mysql, c.myconn, trn);
trn.Commit();
//--------------------------------- Crystal Report -------------------
string str1 = "select '','" + dtpfrom.Text + " ' as period,* from tbl_Ledger order by VchDate,VchNo,VchType";
DataSet ds = new DataSet();
ds.Clear();
str1 = str1 + " SELECT Schoolname + ' - ( ' + Schoolcity + ' ) ' as Schoolname, Schoolcity FROM tbl_School";
ds = Connection.GetDataSet(str1);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TrialBalance.xsd");
RptTrialBalance cr = new RptTrialBalance();
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
s.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnSalaryStatement_Click(object sender, EventArgs e)
{
if (dataGridView1.RowCount > 0)
{
DataSet ds = Connection.GetDataSet(" SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school");
DataTable dt = Connection.GetDataTableFromDataGridView(dataGridView1);
ds.Tables.Add(dt);
SalaryStatement cr1 = new SalaryStatement();
cr1.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
cr1.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\SalaryStatement.xsd");
cr1.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr1;
s.Show();
}
else
{
MessageBox.Show("Record Not Shown.\n\tPlease Select Month And Press Tab Key.");
}
}
private void btnAttendanceBetweenReport_Click(object sender, EventArgs e)
{
DataSet dsAttendance = Connection.GetDataSet("GetAttendanceBetWeenDate '" + txtDateFrom.Value.Date + "','" + txtDateTo.Value.Date + "' SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school");
if (dsAttendance.Tables[0].Rows.Count > 0)
{
AttendanceDateBetween cr1 = new AttendanceDateBetween();
cr1.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr1.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
dsAttendance.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\AttendanceDateBetween.xsd");
cr1.SetDataSource(dsAttendance);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr1;
s.Show();
}
else
{
MessageBox.Show("Attendance Not Registered.");
return;
}
}
private void btnReport_Click(object sender, EventArgs e)
{
if (datagridview1.RowCount > 0)
{
string Session = Convert.ToString(Connection.GetExecuteScalar("Select sessionname from tbl_session where sessioncode='" + school.CurrentSessionCode + "'"));
DataSet ds = Connection.GetDataSet("SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage ,'" + Session + "' as Session FROM tbl_school ");
ds.Tables.Add(Connection.GetDataTableFromDataGridView(datagridview1));
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\RollNoGen.xsd");
rptRollNoGen cr = new rptRollNoGen();
cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
cr.SetParameterValue("ReportTitle", "Roll No. List For Class : " + valcmbclass.Text);
s.Show();
}
else
{
MessageBox.Show("Please Check Student Record.", "", MessageBoxButtons.OK);
}
}
public override void btnprint_Click(object sender, EventArgs e)
{
try
{
if (dataGridView1.RowCount > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TerminateStudentList.xsd");
TerminateStudentReport fr = new TerminateStudentReport();
fr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
fr.SetParameterValue("ReportTitle", "Terminated Student Details");
s.Show();
}
else
{
MessageBox.Show("Plese Select Session Name Properly..");
}
}
catch
{ }
}
private void button1_Click(object sender, EventArgs e)
{
if (dataGridView1.RowCount > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptFeeByMonthCollection.xsd");
rptmonthfeecombination fr1 = new rptmonthfeecombination();
fr1.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
fr1.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
fr1.SetDataSource(ds);
ShowAllReports s1 = new ShowAllReports();
s1.crystalReportViewer1.ReportSource = fr1;
fr1.SetParameterValue("Session", "Session: " + cmbSession.Text);
if (chkClass.Checked)
{
fr1.SetParameterValue("ReportTitle", "Fee Collection For Class- " + cmbclassname.Text);
}
else
{
fr1.SetParameterValue("ReportTitle", "Fee Collection");
}
s1.Show();
}
}
public override void btnprint_Click(object sender, EventArgs e)
{
DataSet ds3 = Connection.GetDataSet("select classcode from tbl_classmaster where classname='" + cmbclass.Text + "'");
classno = Convert.ToInt32(ds3.Tables[0].Rows[0].ItemArray[0].ToString());
string str = "";
str = " SELECT tbl_feeheads.feeheads, tbl_classfeeregular.feeamt, '" + cmbclass.Text + "' as ClassName FROM tbl_classfeeregular INNER JOIN tbl_feeheads ON tbl_classfeeregular.feecode = tbl_feeheads.feecode inner join tbl_sankay ts on ts.sankaycode=tbl_classfeeregular.Stream WHERE (tbl_classfeeregular.classno = '" + classno.ToString() + "' and tbl_classfeeregular.Stream='" + strcmbfaculty.SelectedValue.ToString() + "' )";
str = str + "select sum(feeamt) from tbl_classfeeregular where classno='" + classno.ToString() + "'";
str = str + "SELECT schoolname, schooladdress, logoimage FROM tbl_school";
DataSet ds4 = Connection.GetDataSet(str);
if (ds4 != null)
{
//ds4.WriteXmlSchema(@"D:\Barcodes\a\regularFeeInfo.xsd");
regularFeeInfo fr = new regularFeeInfo();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
fr.SetDataSource(ds4);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
}
public void GetDuplicateFessHP()
{
DataSet ds = Connection.GetDataSet("GetRecPrint " + school.CurrentSessionCode + "," + txtfeeno.Text.Trim());
try
{
bool b = Convert.ToBoolean(Connection.GetExecuteScalar("Select DualSlipType From tbl_FeeHeads"));
if (b)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptFeeReceiptNew.xsd");
rptFeeSlipNew cr = new rptFeeSlipNew();
cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
//cr.SetParameterValue("Title", "FEE RECEIPT");
s.Show();
}
else
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptFeeReceiptSingleNew.xsd");
rptFeeSlipSingleNew cr = new rptFeeSlipSingleNew();
cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
//cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
// System.Drawing.Printing.PaperSize pr = new System.Drawing.Printing.PaperSize("Atul", 4, 6);
Size sz = new Size(4, 6);
s.crystalReportViewer1.ReportSource = cr;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperEnvelopePersonal;
s.Show();
}
}
catch { }
}
private void btnAdminCard_Click(object sender, EventArgs e)
{
DataTable dtStudent = Connection.GetDataTableFromDataGridViewDefaultDataType(dataGridView1);
DataRow[] r = dtStudent.Copy().Select("Select='True'", "Name");
if (r.Length > 0)
{
if (string.IsNullOrEmpty(dtStudent.Rows[0]["logoimage"].ToString()))
{
DataSet dsi = new DataSet();
dsi = Connection.JGetDefaultImage();
if (dsi.Tables[0].Rows.Count > 0)
{
TData = (byte[])(dsi.Tables[0].Rows[0]["Image"]);
//byte[] ByteImage = (byte[])dtStudent.Rows[0]["logoimage"];
Bitmap bmp = Connection.GetImageFromByteArray(TData);
bmp = Connection.AdjustContrastOfImage(bmp, 80f);
byte[] ByteImage1 = Connection.GetByteArrayFromImage(bmp);
dtStudent = r.CopyToDataTable();
dtStudent.TableName = "Student Details";
dtStudent.Rows[0]["Watermark"] = Connection.GetByteArrayFromImage(bmp);
}
}
else
{
byte[] ByteImage = (byte[])dtStudent.Rows[0]["logoimage"];
Bitmap bmp = Connection.GetImageFromByteArray(ByteImage);
bmp = Connection.AdjustContrastOfImage(bmp, 80f);
byte[] ByteImage1 = Connection.GetByteArrayFromImage(bmp);
dtStudent = r.CopyToDataTable();
dtStudent.TableName = "Student Details";
dtStudent.Rows[0]["Watermark"] = null;
}
dtStudent.Columns.Add("RollNo", typeof(string));
foreach (DataRow Row in dtStudent.Rows)
{
object obj = Connection.GetExecuteScalar("Select RollNo From tbl_StudentAttendance Where " +
" SessionCode='" + school.CurrentSessionCode + "' And ClassNo='" + cmbClass.SelectedValue + "' " +
" And StudentNo='" + Row["StudentNo"] + "'");
Row["RollNo"] = (obj != null) ? obj : "0";
}
dtStudent.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\SmartCard.xsd");
rptStudentSmartCardWithoutBarcode fr = new rptStudentSmartCardWithoutBarcode();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
fr.SetDataSource(dtStudent);
ShowAllReports s = new ShowAllReports();
s.Text = "Smart/Identity Card";
s.crystalReportViewer1.ReportSource = fr;
string ex = string.Empty;
ex = "ADMIT CARD [ " + cmbExam.Text.ToString() + " ]";
fr.SetParameterValue("Title", ex);
fr.SetParameterValue("ICStatus", false);
//dtStudent.Dispose();
s.Show();
}
else
{
MessageBox.Show("Please Select Student...");
}
}
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].[GetViewCCEMarksDetail] " + cmbClass.SelectedValue + "," + cmbScetion.SelectedValue + "," + school.CurrentSessionCode + "");
DataTable dtStudentSkillsDetail = Connection.GetDataTable("Select (Case When t1.ExamCode=0 Then 'FA1_' When t1.ExamCode=1 Then 'FA2_' When t1.ExamCode=2 Then 'SA1_' When t1.ExamCode=3 Then 'FA3_' When t1.ExamCode=4 Then 'FA4_' When t1.ExamCode=5 Then 'SA2_' 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_CCEStudentSkills 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 <= 8; 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 <= 8; i++)
{
dtTable.Columns.Add("Sub_" + i + "_" + Sub, typeof(string)); Indexer++;
}
}
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)) ? "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 = 9; GetGrade < dtStudentMarksDetail.Columns.Count; GetGrade++)
{
string Sub = dtStudentMarksDetail.Columns[GetGrade].ColumnName;
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]["Description"].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);
}
dtTable.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\rptCBSEMarksBackDetail.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 rptCCEReportBackFormat2N();
s.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
s.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA3;
}
else
{
s = new rptCCEReportBackFormat3();
s.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
s.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA3;
}
s.SetDataSource(dtTable);
ShowAllReports cr = new ShowAllReports();
cr.crystalReportViewer1.ReportSource = s;
s.SetParameterValue("Class", cmbClass.Text.Trim());
cr.Show();
}
private void btnOk_Click(object sender, EventArgs e)
{
try
{
//-------For Select Opening Date -------
DateTime Cashopdate = dtpfrom.Value.Date;
decimal CashopBal = 0;
c.getconnstr();
c.returnconn(c.myconn);
SqlDataReader dr = null;
con = c.myconn;
dr = c.fillreader(ref dr, "select isnull(OPE_BLD,0) OPE_BLD ,OpenDate from tbl_LedgerAcc where BHM_COD='" + valcmbcash.SelectedValue + "'", con);
if (dr.HasRows == true)
{
dr.Read();
CashopBal = Convert.ToDecimal(dr["OPE_BLD"]);
Cashopdate = Convert.ToDateTime(dr["OpenDate"]);
}
dr.Close();
//-------For Select Opening Balance -------
c.getconnstr();
c.returnconn(c.myconn);
con = c.myconn;
dr = c.fillreader(ref dr, "select ISNULL(a.Receipt,0) as receipt,ISNULL(a.Payment ,0) as payment,a.balance from (SELECT SUM((case when a.Amount > 0 then a.Amount else 0 end)) AS Receipt,SUM((case when a.Amount < 0 then a.Amount else 0 end)) AS Payment,0 as balance FROM tbl_VoucherDetail AS a Where a.CashBankNo=0 and a.VchDate>='" + Cashopdate.Date + "' and a.VchDate<'" + dtpfrom.Value.ToShortDateString() + "' and a.AccCode=" + valcmbcash.SelectedValue + " and a.YearNo=" + school.CurrentSessionCode + ") a ", con);
decimal Cashrec = 0;
decimal Cashpay = 0;
if (dr.HasRows == true)
{
dr.Read();
Cashrec = Convert.ToDecimal(dr["receipt"]);
Cashpay = Convert.ToDecimal(dr["payment"]);
}
CashopBal = CashopBal + Cashrec - Cashpay;
dr.Close();
//--------------insert the opening balance-------
c.getconnstr();
c.returnconn(c.myconn);
SqlTransaction trn;
trn = c.myconn.BeginTransaction();
string mysql;
mysql = "delete from tbl_Ledger";
c.connectsql(mysql, c.myconn, trn);
if (dtpfrom.Value.Date < Cashopdate.Date)
{
dtpfrom.Value = Cashopdate.Date;
}
mysql = " Insert into tbl_Ledger (YearNo,vchdate,accname,Reciept,Payment,balance) ";
if (CashopBal > 0)
{
Cashrec = CashopBal;
Cashpay = 0;
}
else
{
Cashrec = 0;
Cashpay = CashopBal;
}
mysql = mysql + " values (" + school.CurrentSessionCode + ",'" + dtpfrom.Value + "' ,'Opening Balance'," + Cashrec + "," + Cashpay + "," + CashopBal + ")";
c.connectsql(mysql, c.myconn, trn);
//trn.Commit();
//--------------Get all voucher of the day - from date-------
//m.getconnstr();
//m.returnconn(m.myconn);
//trn = m.myconn.BeginTransaction();
mysql = "insert into tbl_Ledger (yearno,accno,accname,vchno,vchdate,vchtype,remark,vchamt,Reciept,Payment) ";
mysql = mysql + " SELECT 0 as yearno,a.AccCode as Accno,c.bhm_desh as accname, b.VouchNo, b.VchDate,b.VchType,remark ,a.Amount as vchamt,";
mysql = mysql + " (case when a.Amount < 0 then -1*a.Amount else 0 end) AS Reciept,(case when (a.Amount) >= 0 then a.Amount else 0 end) AS Payment ";
mysql = mysql + " FROM tbl_VoucherDetail AS a, tbl_Voucher AS b,tbl_LedgerAcc c Where a.VchType in ('CV','CP','CR') and ";
mysql = mysql + " a.VchDate>='" + dtpfrom.Value.Date + "' and a.VchDate<='" + dtpto.Value.Date + "' and a.YearNo='" + school.CurrentSessionCode + "' and ";
mysql = mysql + " a.VouchNo = b.VouchNo and a.VchType = b.VchType and a.AccCode=c.BHM_COD and a.CashBankNo<>0 ";
mysql = mysql + " order by b.VchDate,b.VouchNo,b.VchType ";
c.connectsql(mysql, c.myconn, trn);
trn.Commit();
//--------------Crystel Report-------
//--------------Crystel Report-------
string str1 = "select '',' From " + dtpfrom.Text + " to " + dtpto.Text + "' as period,* from tbl_Ledger order by VchDate,VchNo,VchType";
DataSet ds = new DataSet();
ds.Clear();
str1 = str1 + " SELECT Schoolname + ' - ( ' + Schoolcity + ' ) ' as Schoolname, Schoolcity FROM tbl_School";
ds = Connection.GetDataSet(str1);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\CashBook.xsd");
RptCashBook cr = new RptCashBook();
cr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
s.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void button8_Click(object sender, EventArgs e)
{
dtStudent.Columns.Clear(); dtStudent.Rows.Clear();
dtStudent.Columns.Add("ScholarNo", typeof(string));
dtStudent.Columns.Add("Name", typeof(string));
dtStudent.Columns.Add("Class", typeof(string));
dtStudent.Columns.Add("Subject", typeof(string));
dtStudent.Columns.Add("July", typeof(string));
dtStudent.Columns.Add("August", typeof(string));
dtStudent.Columns.Add("September", typeof(string));
dtStudent.Columns.Add("October", typeof(string));
dtStudent.Columns.Add("HYExam", typeof(string));
dtStudent.Columns.Add("Decmber", typeof(string));
dtStudent.Columns.Add("January", typeof(string));
dtStudent.Columns.Add("Febuary", typeof(string));
dtStudent.Columns.Add("AnnExam", typeof(string));
dtStudent.Columns.Add("Result", typeof(string));
dtStudentField.Columns.Clear(); dtStudentField.Rows.Clear();
dtStudentField.Columns.Add("ScholarNo", typeof(string));
dtStudentField.Columns.Add("Name", typeof(string));
dtStudentField.Columns.Add("Father", typeof(string));
dtStudentField.Columns.Add("Mother", typeof(string));
dtStudentField.Columns.Add("DBO", typeof(DateTime));
dtStudentField.Columns.Add("Cast", typeof(string));
dtStudentField.Columns.Add("Distic", typeof(string));
dtStudentField.Columns.Add("Tehsil", typeof(string));
dtStudentField.Columns.Add("BloodGroup", typeof(string));
dtStudentField.Columns.Add("Literal", typeof(string));
dtStudentField.Columns.Add("Cultural", typeof(string));
dtStudentField.Columns.Add("Scientific", typeof(string));
dtStudentField.Columns.Add("Creative", typeof(string));
dtStudentField.Columns.Add("Games", typeof(string));
dtStudentField.Columns.Add("FinalGrade1", typeof(string));
dtStudentField.Columns.Add("Regularity", typeof(string));
dtStudentField.Columns.Add("Pun", typeof(string));
dtStudentField.Columns.Add("Clean", typeof(string));
dtStudentField.Columns.Add("Discip", typeof(string));
dtStudentField.Columns.Add("Spriti", typeof(string));
dtStudentField.Columns.Add("Sensitivity", typeof(string));
dtStudentField.Columns.Add("leadership", typeof(string));
dtStudentField.Columns.Add("Truth", typeof(string));
dtStudentField.Columns.Add("Honest", typeof(string));
dtStudentField.Columns.Add("Attitude", typeof(string));
dtStudentField.Columns.Add("FinalGrade2", typeof(string));
dtStudentField.Columns.Add("Attendance", typeof(int));
DataTable dt = Connection.GetDataTable("SELECT [ScholarNo] ,[Name] ,[Subject] ,[Jul] ,[Aug] ,[Sept] ,[Oct] " +
" ,[HY] ,[Dec] ,[Jan] ,[Feb] ,[Annual] ,[Attendance] ,[sssmid] ,[id] ,[FinalGrade] ,[Class] " +
" FROM [dbTemprary].[dbo].[tblStudent] Where Class = '" + comboBox1.Text.Trim() + "'");
if (dt.Rows.Count > 0)
{
DataTable dtsubject = dt.DefaultView.ToTable(true, "ScholarNo");
foreach (DataRow st in dtsubject.Rows)
{
DataRow[] r = dt.Select("ScholarNo='" + st["ScholarNo"] + "'");
if (r.Length > 0)
{
for (int i = 0; i < r.Length; i++)
{
DataRow newrow = dtStudent.NewRow();
newrow["ScholarNo"] = r[i]["ScholarNo"];
newrow["Name"] = r[i]["Name"];
newrow["Class"] = r[i]["Class"];
newrow["Subject"] = r[i]["Subject"];
newrow["July"] = r[i]["Jul"];
newrow["August"] = r[i]["Aug"];
newrow["September"] = r[i]["Sept"];
newrow["October"] = r[i]["Oct"];
newrow["HYExam"] = r[i]["HY"];
newrow["Decmber"] = r[i]["Dec"];
newrow["January"] = r[i]["Jan"];
newrow["Febuary"] = r[i]["Feb"];
newrow["AnnExam"] = r[i]["Annual"];
newrow["Result"] = r[i]["FinalGrade"];
dtStudent.Rows.Add(newrow);
}
}
}
}
DataTable d = dtStudent.DefaultView.ToTable(true, "ScholarNo", "Name");
foreach (DataRow st in d.Rows)
{
DataTable dtl = Connection.GetDataTable("SELECT [dbTemprary].[dbo].tblF1.ScholarNo,[dbTemprary].[dbo].tblF1.Name,[dbTemprary].[dbo].tblF1.Literary, [dbTemprary].[dbo].tblF1.Cultural, [dbTemprary].[dbo].tblF1.Science, " +
" [dbTemprary].[dbo].tblF1.Creativity, [dbTemprary].[dbo].tblF1.Sprot, [dbTemprary].[dbo].tblF1.Annual Annual1, [dbTemprary].[dbo].tblF1.FinalGrade FinalGrade1, [dbTemprary].[dbo].tblF2.Regularity, [dbTemprary].[dbo].tblF2.Punctuality, " +
" [dbTemprary].[dbo].tblF2.Cleanliness, [dbTemprary].[dbo].tblF2.Discipline, [dbTemprary].[dbo].tblF2.CoOperation, [dbTemprary].[dbo].tblF2.Environment, [dbTemprary].[dbo].tblF2.Leadership, " +
" [dbTemprary].[dbo].tblF2.Truthfulness,[dbTemprary].[dbo]. tblF2.Honesty,[dbTemprary].[dbo]. tblF2.Nature,[dbTemprary].[dbo]. tblF2.Annual AS Annual2, [dbTemprary].[dbo].tblF2.FinalGrade AS FinalGrade2 " +
" FROM [dbTemprary].[dbo].tblF1 INNER JOIN [dbTemprary].[dbo].tblF2 ON [dbTemprary].[dbo].tblF1.ScholarNo =[dbTemprary].[dbo]. tblF2.ScholarNo AND [dbTemprary].[dbo].tblF1.Name = [dbTemprary].[dbo].tblF2.Name AND " +
" [dbTemprary].[dbo].tblF1.Class = [dbTemprary].[dbo].tblF2.Class Where [dbTemprary].[dbo].tblF1.ScholarNo = '" + st["ScholarNo"] + "' AND [dbTemprary].[dbo].tblF1.Name ='" + st["Name"] + "'");
DataTable std = Connection.GetDataTable("SELECT tbl_student.name, tbl_student.dob, tbl_student.P_address, tbl_student.father, " +
" tbl_student.mother, tbl_student.m_tongue, tbl_student.casttype, tbl_student.bloodgroup, tbl_classstudent.stdtype, " +
" tbl_student.marr_status, tbl_tehsil.tehsil, tbl_district.district FROM tbl_student INNER JOIN " +
" tbl_tehsil ON tbl_student.tehcode = tbl_tehsil.tehcode INNER JOIN tbl_district ON tbl_tehsil.distcode = tbl_district.distcode " +
" Where tbl_student.name='" + st["Name"] + "'");
if (dtl.Rows.Count > 0)
{
DataRow r = dtStudentField.NewRow();
r["ScholarNo"] = dtl.Rows[0]["ScholarNo"];
r["Name"] = dtl.Rows[0]["Name"];
if (std.Rows.Count > 0)
{
r["Father"] = (std.Rows[0]["father"] != DBNull.Value) ? std.Rows[0]["father"] : null;
r["Mother"] = (std.Rows[0]["mother"] != DBNull.Value) ? std.Rows[0]["mother"] : null;
r["DBO"] = (std.Rows[0]["dob"] != DBNull.Value) ? std.Rows[0]["dob"] : null;
r["Cast"] = (std.Rows[0]["casttype"] != DBNull.Value) ? std.Rows[0]["casttype"] : null;
r["Distic"] = (std.Rows[0]["district"] != DBNull.Value) ? std.Rows[0]["district"] : null;
r["Tehsil"] = (std.Rows[0]["tehsil"] != DBNull.Value) ? std.Rows[0]["tehsil"] : null;
r["BloodGroup"] = (std.Rows[0]["bloodgroup"] != DBNull.Value) ? std.Rows[0]["bloodgroup"] : null;
}
r["Literal"] = dtl.Rows[0]["Literary"];
r["Cultural"] = dtl.Rows[0]["Cultural"];
r["Scientific"] = dtl.Rows[0]["Science"];
r["Creative"] = dtl.Rows[0]["Creativity"];
r["Games"] = dtl.Rows[0]["Sprot"];
r["FinalGrade1"] = dtl.Rows[0]["FinalGrade1"];
r["Regularity"] = dtl.Rows[0]["Regularity"];
r["Pun"] = dtl.Rows[0]["Punctuality"];
r["Clean"] = dtl.Rows[0]["Cleanliness"];
r["Discip"] = dtl.Rows[0]["Discipline"];
r["Spriti"] = dtl.Rows[0]["CoOperation"];
r["Sensitivity"] = dtl.Rows[0]["Environment"];
r["leadership"] = dtl.Rows[0]["Leadership"];
r["Truth"] = dtl.Rows[0]["Truthfulness"];
r["Honest"] = dtl.Rows[0]["Honesty"];
r["Attitude"] = dtl.Rows[0]["Nature"];
r["FinalGrade2"] = dtl.Rows[0]["FinalGrade2"];
r["Attendance"] = Connection.GetExecuteScalar("Select ISNULL(Attendance,0) From [dbTemprary].[dbo].tblStudent Where ScholarNo='" + r["ScholarNo"] + "' And Name='" + r["Name"] + "' AND Attendance IS NOT NULL");
dtStudentField.Rows.Add(r);
}
}
DataSet ds = Connection.GetDataSet("SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage, Website, logoimage as Watermark, SchoolCode, TCSrNo , TCBookNo FROM tbl_school ");
ds.Tables.Add(dtStudent);
ds.Tables.Add(dtStudentField);
if (ds.Tables[0].Rows.Count > 0)
{
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\marksheet.xsd");
marksheet2 cr = new marksheet2();
cr.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr;
cr.SetParameterValue("CLASS", comboBox1.Text);
s.Show();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (this.Format.Equals("TC-Format-1"))
{
string str = " SELECT tbl_student .scholarno AS [Scholar No], tbl_student .name AS Name, tbl_student .father AS [Father Name], tbl_student .mother AS [Mother Name], " +
" tbl_student .RegDate AS [Admission Date], tbl_student .dob AS [Birth Date], tbl_student .phone AS [Contact No], " +
" tbl_student .m_tongue AS [Mother Tongue], tbl_student .casttype AS Category, tbl_student .bloodgroup AS [Blood Group], " +
" CASE WHEN tbl_student .sp_challange = 'False' THEN 'No' ELSE 'Yes' END AS [Physically Challanged], " +
" tbl_student .SubCast AS [Sub-Cast], tbl_student .Cast, tbl_student .Religion, tbl_student .bldgroup AS Medium, " +
" tbl_student .marr_status AS Gender, tbl_classmaster.classname + ' ' + tbl_section.sectionname AS Class, tbl_sankay.sankayname AS Stream, " +
" tbl_classstudent.stdtype AS Status, tbl_session .sessionname AS Session, tbl_StopDetails.StopName AS [Bus Stop], tbl_tehsil.tehsil AS Tehsil, " +
" tbl_district.district AS District, tbl_district.statename AS State, tbl_classmaster.classname AS ClassName, tbl_section.sectionname AS SectionName, " +
" tbl_StudentAttendance.Lectures, tbl_StudentAttendance.PresentDays, tbl_StudentAttendance.Per,tbl_student.SSMId as [SSSM Id] " +
" FROM tbl_student AS tbl_student INNER JOIN " +
" tbl_classstudent ON tbl_student .studentno = tbl_classstudent.studentno INNER JOIN " +
" tbl_session AS tbl_session ON tbl_classstudent.sessioncode = tbl_session .sessioncode INNER JOIN " +
" tbl_sankay ON tbl_classstudent.Stream = tbl_sankay.sankaycode INNER JOIN " +
" tbl_section ON tbl_classstudent.Section = tbl_section.sectioncode INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode INNER JOIN " +
" tbl_tehsil ON tbl_student .tehcode = tbl_tehsil.tehcode INNER JOIN " +
" tbl_district ON tbl_tehsil.distcode = tbl_district.distcode LEFT JOIN " +
" tbl_StudentAttendance ON tbl_student .studentno = tbl_StudentAttendance.StudentNo LEFT OUTER JOIN " +
" tbl_StopDetails ON tbl_student .BusStopNo = tbl_StopDetails.BusStopNo " +
" WHERE (tbl_classstudent.sessioncode = '" + cmbsession.SelectedValue + "') AND (tbl_student .scholarno = '" + txtscholarno.Text.Trim() + "') ; " +
" SELECT tbl_session.sessionname, tbl_classmaster.classname " +
" FROM tbl_student INNER JOIN " +
" tbl_session ON tbl_student.admsession = tbl_session.sessioncode INNER JOIN " +
" tbl_classstudent ON tbl_student.admsession = tbl_classstudent.sessioncode AND tbl_student.studentno = tbl_classstudent.studentno INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode AND tbl_student.scholarno = '" + txtscholarno.Text.Trim() + "'; " +
" SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage, Website, logoimage as Watermark, SchoolCode, isnull(TCSrNo,0) as TCSrNo,isnull(TCBookNo,0) as TCBookNo FROM tbl_school ;";
DataSet ds = Connection.GetDataSet(str);
byte[] ByteImage = (byte[])ds.Tables[2].Rows[0]["logoimage"];
Bitmap bmp = Connection.GetImageFromByteArray(ByteImage);
bmp = Connection.AdjustContrastOfImage(bmp, 80f);
byte[] ByteImage1 = Connection.GetByteArrayFromImage(bmp);
ds.Tables[2].Rows[0]["Watermark"] = Connection.GetByteArrayFromImage(bmp);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TC.xsd");
dataGridView1.DataSource = ds.Tables[0];
if (ds.Tables[0].Rows.Count > 0)
{
if (DialogResult.Yes.Equals(MessageBox.Show("Are you sure io issue Trancefer Certificate for this Student?", "Book No.!!!", MessageBoxButtons.YesNo, MessageBoxIcon.Question)))
{
if (Convert.ToInt32(ds.Tables[2].Rows[0]["TCSrNo"]) > 150)
{
Connection.AllPerform("Update tbl_School Set TCSrNo = 101, TCBookNo = '" + (Convert.ToInt32(ds.Tables[2].Rows[0]["TCBookNo"]) + 1) + "' ");
}
else
{
Connection.AllPerform("Update tbl_School Set TCSrNo = '" + (Convert.ToInt32(ds.Tables[2].Rows[0]["TCSrNo"]) + 1) + "' ");
}
//Connection.AllPerform("Update tbl_Student Set stdtype='Ex-Student' Where ScholarNo='" + txtscholarno.Text.Trim() + "' ");
//Connection.AllPerform("Update tbl_classstudent Set stdtype='Ex-Student' Where studentno=dbo.GetStudentNo('" + txtscholarno.Text.Trim() + "') and sessioncode='" + school.CurrentSessionCode + "' ");
}
rptTCFormat1 fr = new rptTCFormat1();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
fr.SetDataSource(ds);
fr.SetParameterValue("Reason", txtReason.Text.Trim());
fr.SetParameterValue("Charecter", txtCharecter.Text.Trim());
fr.SetParameterValue("Class", cmbPromotClass.Text.Trim());
fr.SetParameterValue("NewSession", cmbsession.Text);
fr.SetParameterValue("Date", dateTimePicker1.Value.Date.ToString("dd/MM/yyyy"));
//fr.SetParameterValue("AdmissionClass", cmbAdmissionClass.Text.Trim());
//fr.SetParameterValue("NewClass", cmbPromotClass.Text.Trim());
//fr.SetParameterValue("NewSession", cmbsession.Text);
// fr.SetParameterValue("DateOfApplication", dateTimePicker2.Value.Date.ToString("dd/MM/yyyy"));
//fr.SetParameterValue("Remarks", txtRemarks.Text.Trim());
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
}
else if (this.Format.Equals("TC-Format-2"))
{
string str = " SELECT tbl_student.scholarno AS [Scholar No], tbl_student.name AS Name, tbl_student.SSMId AS SSMId,tbl_student.father AS [Father Name], tbl_student.mother AS [Mother Name], " +
" Convert(varchar,tbl_student.RegDate,105) AS [Admission Date], Convert(varchar,tbl_student.dob,105) AS [Birth Date], " +
" tbl_student.phone AS [Contact No], tbl_student.m_tongue AS [Mother Tongue], tbl_student.casttype AS Category, tbl_student.bloodgroup as [Blood Group]," +
" CASE WHEN tbl_student.sp_challange = 'False' THEN 'No' ELSE 'Yes' END AS [Physically Challanged], tbl_student.SubCast AS [Sub-Cast], " +
" tbl_student.Cast, tbl_student.Religion, tbl_student.bldgroup AS Medium, tbl_student.marr_status AS Gender, " +
" tbl_classmaster.classname + ' ' + tbl_section.sectionname AS Class, tbl_sankay.sankayname AS Stream,tbl_classstudent.stdtype as Status ,tbl_session.sessionname AS Session, " +
" tbl_StopDetails.StopName AS [Bus Stop], tbl_tehsil.tehsil AS Tehsil, tbl_district.district AS District, tbl_district.statename AS State,tbl_classmaster.classname as ClassName,tbl_section.sectionname as SectionName " +
" FROM tbl_student INNER JOIN " +
" tbl_classstudent ON tbl_student.studentno = tbl_classstudent.studentno INNER JOIN " +
" tbl_session ON tbl_classstudent.sessioncode = tbl_session.sessioncode INNER JOIN " +
" tbl_sankay ON tbl_classstudent.Stream = tbl_sankay.sankaycode INNER JOIN " +
" tbl_section ON tbl_classstudent.Section = tbl_section.sectioncode INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode INNER JOIN " +
" tbl_tehsil ON tbl_student.tehcode = tbl_tehsil.tehcode INNER JOIN " +
" tbl_district ON tbl_tehsil.distcode = tbl_district.distcode LEFT OUTER JOIN " +
" tbl_StopDetails ON tbl_student.BusStopNo = tbl_StopDetails.BusStopNo " +
" WHERE (tbl_classstudent.sessioncode = '" + cmbsession.SelectedValue + "') AND (tbl_student.scholarno = '" + txtscholarno.Text.Trim() + "') ";
str = str + " SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage, Website, logoimage as Watermark, SchoolCode, isnull(TCSrNo,0) as TCSrNo,isnull(TCBookNo,0) as TCBookNo FROM tbl_school ";
DataSet ds = Connection.GetDataSet(str);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TC.xsd");
dataGridView1.DataSource = ds.Tables[0];
if (ds.Tables[0].Rows.Count > 0)
{
rptTCFormat4 fr = new rptTCFormat4();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4;
fr.SetDataSource(ds);
fr.SetParameterValue("Result", txtresult.Text.Trim());
fr.SetParameterValue("Attadance", txtpercent.Text.Trim());
fr.SetParameterValue("Date", dateTimePicker1.Value.Date.ToString("dd/MM/yyyy"));
fr.SetParameterValue("Charecter", txtCharecter.Text.Trim());
fr.SetParameterValue("IssueOfApplication", dateTimePicker2.Value.Date.ToString("dd/MM/yyyy"));
fr.SetParameterValue("PassedClass", cmbPromotClass.Text.Trim());
fr.SetParameterValue("AdmissionClass", cmbAdmissionClass.Text.Trim());
fr.SetParameterValue("NewClass", cmbPromotClass.Text.Trim());
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
}
else if (this.Format.Equals("TC-Format-3"))
{
string str = " SELECT tbl_student .scholarno AS [Scholar No], tbl_student .name AS Name, tbl_student .father AS [Father Name], tbl_student .mother AS [Mother Name], " +
" CONVERT(varchar, tbl_student .RegDate, 105) AS [Admission Date], tbl_student .dob AS [Birth Date], tbl_student .phone AS [Contact No], " +
" tbl_student .m_tongue AS [Mother Tongue], tbl_student .casttype AS Category, tbl_student .bloodgroup AS [Blood Group], " +
" CASE WHEN tbl_student .sp_challange = 'False' THEN 'No' ELSE 'Yes' END AS [Physically Challanged], " +
" tbl_student .SubCast AS [Sub-Cast], tbl_student .Cast, tbl_student .Religion, tbl_student .bldgroup AS Medium, " +
" tbl_student .marr_status AS Gender, tbl_classmaster.classname + ' ' + tbl_section.sectionname AS Class, tbl_sankay.sankayname AS Stream, " +
" tbl_classstudent.stdtype AS Status, tbl_session .sessionname AS Session, tbl_StopDetails.StopName AS [Bus Stop], tbl_tehsil.tehsil AS Tehsil, " +
" tbl_district.district AS District, tbl_district.statename AS State, tbl_classmaster.classname AS ClassName, tbl_section.sectionname AS SectionName, " +
" tbl_StudentAttendance.Lectures, tbl_StudentAttendance.PresentDays " +
" FROM tbl_student AS tbl_student INNER JOIN " +
" tbl_classstudent ON tbl_student .studentno = tbl_classstudent.studentno INNER JOIN " +
" tbl_session AS tbl_session ON tbl_classstudent.sessioncode = tbl_session .sessioncode INNER JOIN " +
" tbl_sankay ON tbl_classstudent.Stream = tbl_sankay.sankaycode INNER JOIN " +
" tbl_section ON tbl_classstudent.Section = tbl_section.sectioncode INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode INNER JOIN " +
" tbl_tehsil ON tbl_student .tehcode = tbl_tehsil.tehcode INNER JOIN " +
" tbl_district ON tbl_tehsil.distcode = tbl_district.distcode LEFT JOIN " +
" tbl_StudentAttendance ON tbl_student .studentno = tbl_StudentAttendance.StudentNo LEFT OUTER JOIN " +
" tbl_StopDetails ON tbl_student .BusStopNo = tbl_StopDetails.BusStopNo " +
" WHERE (tbl_classstudent.sessioncode = '" + cmbsession.SelectedValue + "') AND (tbl_student .scholarno = '" + txtscholarno.Text.Trim() + "') ; " +
" SELECT tbl_session.sessionname, tbl_classmaster.classname " +
" FROM tbl_student INNER JOIN " +
" tbl_session ON tbl_student.admsession = tbl_session.sessioncode INNER JOIN " +
" tbl_classstudent ON tbl_student.admsession = tbl_classstudent.sessioncode AND tbl_student.studentno = tbl_classstudent.studentno INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode AND tbl_student.scholarno = '" + txtscholarno.Text.Trim() + "'; " +
" SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage, Website, logoimage as Watermark, SchoolCode, isnull(TCSrNo,0) as TCSrNo,isnull(TCBookNo,0) as TCBookNo FROM tbl_school ";
DataSet ds = Connection.GetDataSet(str);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TC.xsd");
dataGridView1.DataSource = ds.Tables[0];
if (ds.Tables[0].Rows.Count > 0)
{
rptTCFormat4 fr = new rptTCFormat4();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
fr.SetDataSource(ds);
DataTable dt = Connection.GetDataTable("");
fr.SetParameterValue("Reason", txtReason.Text.Trim());
//fr.SetParameterValue("Charecter", txtCharecter.Text.Trim());
fr.SetParameterValue("Date", dateTimePicker1.Value.Date.ToString("dd/MM/yyyy"));
fr.SetParameterValue("PassedClass", cmbPromotClass.Text.Trim());
fr.SetParameterValue("NewSession", cmbsession.Text);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
}
else if (this.Format.Equals("TC-Format-4"))
{
string str = " SELECT tbl_student .scholarno AS [Scholar No], tbl_student .name AS Name, tbl_student .father AS [Father Name], tbl_student .mother AS [Mother Name], " +
" tbl_student .RegDate AS [Admission Date], tbl_student .dob AS [Birth Date], tbl_student .phone AS [Contact No], " +
" tbl_student .SSMId AS [SSSMID], tbl_student .ChielId AS [Chiel Id], " +
" tbl_student .m_tongue AS [Mother Tongue], tbl_student .casttype AS Category, tbl_student .bloodgroup AS [Blood Group], " +
" CASE WHEN tbl_student .sp_challange = 'False' THEN 'No' ELSE 'Yes' END AS [Physically Challanged], " +
" tbl_student .SubCast AS [Sub-Cast], tbl_student .Cast, tbl_student .Religion, tbl_student .bldgroup AS Medium, " +
" tbl_student .marr_status AS Gender, tbl_classmaster.classname + ' ' + tbl_section.sectionname AS Class, tbl_sankay.sankayname AS Stream, " +
" tbl_classstudent.stdtype AS Status, tbl_session .sessionname AS Session,(select top 1 s_to from tbl_session where sessioncode<104 order by sessioncode desc) as s_to, tbl_StopDetails.StopName AS [Bus Stop], tbl_tehsil.tehsil AS Tehsil, " +
" tbl_district.district AS District, tbl_district.statename AS State, tbl_classmaster.classname AS ClassName, tbl_section.sectionname AS SectionName, " +
" tbl_StudentAttendance.Lectures, tbl_StudentAttendance.PresentDays, tbl_StudentAttendance.Per " +
" FROM tbl_student AS tbl_student INNER JOIN " +
" tbl_classstudent ON tbl_student .studentno = tbl_classstudent.studentno INNER JOIN " +
" tbl_session AS tbl_session ON tbl_classstudent.sessioncode = tbl_session .sessioncode INNER JOIN " +
" tbl_sankay ON tbl_classstudent.Stream = tbl_sankay.sankaycode INNER JOIN " +
" tbl_section ON tbl_classstudent.Section = tbl_section.sectioncode INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode INNER JOIN " +
" tbl_tehsil ON tbl_student .tehcode = tbl_tehsil.tehcode INNER JOIN " +
" tbl_district ON tbl_tehsil.distcode = tbl_district.distcode LEFT JOIN " +
" tbl_StudentAttendance ON tbl_student .studentno = tbl_StudentAttendance.StudentNo LEFT OUTER JOIN " +
" tbl_StopDetails ON tbl_student .BusStopNo = tbl_StopDetails.BusStopNo " +
" WHERE (tbl_classstudent.sessioncode = '" + cmbsession.SelectedValue + "') AND (tbl_student .scholarno = '" + txtscholarno.Text.Trim() + "') ; " +
" SELECT tbl_session.sessionname, tbl_session.s_to , tbl_classmaster.classname " +
" FROM tbl_student INNER JOIN " +
" tbl_session ON tbl_student.admsession = tbl_session.sessioncode INNER JOIN " +
" tbl_classstudent ON tbl_student.admsession = tbl_classstudent.sessioncode AND tbl_student.studentno = tbl_classstudent.studentno INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode AND tbl_student.scholarno = '" + txtscholarno.Text.Trim() + "'; " +
" SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage, Website, logoimage as Watermark, SchoolCode, isnull(TCSrNo,0) as TCSrNo,isnull(TCBookNo,0) as TCBookNo FROM tbl_school ;";
DataTable dtSubject = new DataTable("dtSubject");
int i = 1;
while (i <= 8)
{
dtSubject.Columns.Add("Subject-" + i); i++;
}
SqlDataReader DataReader = Connection.GetDataReader("SELECT s1.subjectno, s1.subjectname, s1.subjecttype, s1.SubjectCode, s1.SubjectOrder FROM tbl_subject s1, tbl_subwiseclass s2 " +
" Where s1.subjectno = s2.subjectno AND s2.classno = " +
" (Select c.classno From tbl_student s, tbl_classstudent c Where s.studentno = c.studentno AND c.sessioncode = '" + cmbsession.SelectedValue + "' AND s.scholarno = '" + txtscholarno.Text.Trim() + "') Order By SubjectOrder ");
if (DataReader.HasRows)
{
DataRow r = dtSubject.NewRow(); i = 0;
while (DataReader.Read())
{
if (i < 8)
{
r[i] = DataReader["subjectname"];
}
i++;
}
dtSubject.Rows.Add(r);
}
DataSet ds = Connection.GetDataSet(str);
ds.Tables.Add(dtSubject);
byte[] ByteImage = (byte[])ds.Tables[2].Rows[0]["logoimage"];
Bitmap bmp = Connection.GetImageFromByteArray(ByteImage);
bmp = Connection.AdjustContrastOfImage(bmp, 70f);
byte[] ByteImage1 = Connection.GetByteArrayFromImage(bmp);
ds.Tables[2].Rows[0]["Watermark"] = Connection.GetByteArrayFromImage(bmp);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TC.xsd");
dataGridView1.DataSource = ds.Tables[0];
if (ds.Tables[0].Rows.Count > 0)
{
if (DialogResult.Yes.Equals(MessageBox.Show("Are you sure io issue Trancefer Certificate for this Student?", "Book No.!!!", MessageBoxButtons.YesNo, MessageBoxIcon.Question)))
{
if (Convert.ToInt32(ds.Tables[2].Rows[0]["TCSrNo"]) > 0)
{
Connection.AllPerform("Update tbl_School Set TCSrNo = '" + (Convert.ToInt32(ds.Tables[2].Rows[0]["TCSrNo"]) + 1) + "', TCBookNo = '" + (Convert.ToInt32(ds.Tables[2].Rows[0]["TCBookNo"]) + 1) + "' ");
}
else
{
Connection.AllPerform("Update tbl_School Set TCSrNo = '" + (Convert.ToInt32(ds.Tables[2].Rows[0]["TCSrNo"]) + 1) + "' ");
}
Connection.AllPerform("Update tbl_Student Set stdtype='Ex-Student' Where ScholarNo='" + txtscholarno.Text.Trim() + "' ");
Connection.AllPerform("Update tbl_classstudent Set stdtype='Ex-Student' Where studentno=dbo.GetStudentNo('" + txtscholarno.Text.Trim() + "') and sessioncode='" + school.CurrentSessionCode + "' ");
}
rptTCFormat4 fr = new rptTCFormat4();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
fr.SetDataSource(ds);
fr.SetParameterValue("Reason", txtReason.Text.Trim());
fr.SetParameterValue("Charecter", txtCharecter.Text.Trim());
fr.SetParameterValue("DateOfAdmission", dateTimePicker1.Value.Date.ToString("dd/MM/yyyy"));
fr.SetParameterValue("AdmissionClass", cmbAdmissionClass.Text.Trim());
fr.SetParameterValue("NewClass", cmbPromotClass.Text.Trim());
fr.SetParameterValue("NewSession", cmbsession.Text);
fr.SetParameterValue("DateOfIssue", dateTimePicker2.Value.Date.ToString("dd/MM/yyyy"));
fr.SetParameterValue("DateOfApplication", dateTimePicker3.Value.Date.ToString("dd/MM/yyyy"));
fr.SetParameterValue("Result", txtresult.Text.Trim());
fr.SetParameterValue("Remarks", txtRemarks.Text.Trim());
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
}
else if (this.Format.Equals("TC-Format-5"))
{
string str = " SELECT tbl_student .scholarno AS [Scholar No],upper( tbl_student .SSMId) AS SSSMID,upper( tbl_student .name) AS Name,upper( tbl_student .father) AS [Father Name], upper(tbl_student .mother) AS [Mother Name], " +
" convert(nvarchar(10), tbl_student .RegDate,103) AS [Admission Date],upper(tbl_student .Wdob) as Wdob, tbl_student .dob AS [Birth Date], tbl_student .phone AS [Contact No], " +
" upper(tbl_student .m_tongue) AS [Mother Tongue],upper(tbl_student.C_address) as C_address, upper(tbl_student .casttype) AS Category, tbl_student .bloodgroup AS [Blood Group], " +
" CASE WHEN tbl_student .sp_challange = 'False' THEN 'No' ELSE 'Yes' END AS [Physically Challanged], " +
" upper( tbl_student .SubCast) AS [Sub-Cast], upper(tbl_student .Cast) as Cast, upper(tbl_student .Religion) as Religion, upper(tbl_student .bldgroup) AS Medium, " +
" upper( tbl_student .marr_status) AS Gender,upper( tbl_classmaster.classname + ' ' + tbl_section.sectionname) AS Class, upper(tbl_sankay.sankayname) AS Stream, " +
" upper(tbl_classstudent.stdtype) AS Status, tbl_session .sessionname AS Session, upper(tbl_StopDetails.StopName) AS [Bus Stop], upper(tbl_tehsil.tehsil) AS Tehsil, " +
" upper(tbl_district.district) AS District, upper(tbl_district.statename) AS State, upper(tbl_classmaster.classname) AS ClassName,upper( tbl_section.sectionname) AS SectionName, " +
" convert(nvarchar(10), convert(numeric(10,0), tbl_StudentAttendance.Lectures)) as Lectures,convert(nvarchar(10), convert(numeric(10,0), tbl_StudentAttendance.PresentDays)) as PresentDays, tbl_StudentAttendance.Per " +
" FROM tbl_student AS tbl_student INNER JOIN " +
" tbl_classstudent ON tbl_student .studentno = tbl_classstudent.studentno INNER JOIN " +
" tbl_session AS tbl_session ON tbl_classstudent.sessioncode = tbl_session .sessioncode INNER JOIN " +
" tbl_sankay ON tbl_classstudent.Stream = tbl_sankay.sankaycode INNER JOIN " +
" tbl_section ON tbl_classstudent.Section = tbl_section.sectioncode INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode INNER JOIN " +
" tbl_tehsil ON tbl_student .tehcode = tbl_tehsil.tehcode INNER JOIN " +
" tbl_district ON tbl_tehsil.distcode = tbl_district.distcode LEFT JOIN " +
" tbl_StudentAttendance ON tbl_student .studentno = tbl_StudentAttendance.StudentNo LEFT OUTER JOIN " +
" tbl_StopDetails ON tbl_student .BusStopNo = tbl_StopDetails.BusStopNo " +
" WHERE (tbl_classstudent.sessioncode = '" + cmbsession.SelectedValue + "') AND (tbl_student .scholarno = '" + txtscholarno.Text.Trim() + "') ; " +
" SELECT tbl_session.sessionname,tbl_session.s_to, tbl_classmaster.classname " +
" FROM tbl_student INNER JOIN " +
" tbl_session ON tbl_student.admsession = tbl_session.sessioncode INNER JOIN " +
" tbl_classstudent ON tbl_student.admsession = tbl_classstudent.sessioncode AND tbl_student.studentno = tbl_classstudent.studentno INNER JOIN " +
" tbl_classmaster ON tbl_classstudent.classno = tbl_classmaster.classcode AND tbl_student.scholarno = '" + txtscholarno.Text.Trim() + "'; " +
" SELECT schoolname, schooladdress, schoolcity, schoolphone, affiliate_by, principal, registrationno, logoimage, Website, logoimage as Watermark, SchoolCode, isnull(TCSrNo,0) as TCSrNo,isnull(TCBookNo,0) as TCBookNo FROM tbl_school ;";
DataSet ds = Connection.GetDataSet(str);
byte[] ByteImage = (byte[])ds.Tables[2].Rows[0]["logoimage"];
Bitmap bmp = Connection.GetImageFromByteArray(ByteImage);
bmp = Connection.AdjustContrastOfImage(bmp, 80f);
byte[] ByteImage1 = Connection.GetByteArrayFromImage(bmp);
ds.Tables[2].Rows[0]["Watermark"] = Connection.GetByteArrayFromImage(bmp);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TC.xsd");
dataGridView1.DataSource = ds.Tables[0];
if (ds.Tables[0].Rows.Count > 0)
{
if (DialogResult.Yes.Equals(MessageBox.Show("Are you sure io issue Trancefer Certificate for this Student?", "Book No.!!!", MessageBoxButtons.YesNo, MessageBoxIcon.Question)))
{
if (Convert.ToInt32(ds.Tables[2].Rows[0]["TCSrNo"]) > 150)
{
Connection.AllPerform("Update tbl_School Set TCSrNo = 101, TCBookNo = '" + (Convert.ToInt32(ds.Tables[2].Rows[0]["TCBookNo"]) + 1) + "' ");
}
else
{
Connection.AllPerform("Update tbl_School Set TCSrNo = '" + (Convert.ToInt32(ds.Tables[2].Rows[0]["TCSrNo"]) + 1) + "' ");
}
Connection.AllPerform("Update tbl_Student Set stdtype='Ex-Student' Where ScholarNo='" + txtscholarno.Text.Trim() + "' ");
Connection.AllPerform("Update tbl_classstudent Set stdtype='Ex-Student' Where studentno=dbo.GetStudentNo('" + txtscholarno.Text.Trim() + "') and sessioncode='" + school.CurrentSessionCode + "' ");
}
rptTCFormat5 fr = new rptTCFormat5();
fr.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
fr.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
fr.SetDataSource(ds);
//fr.SetParameterValue("Reason", txtReason.Text.Trim());
//fr.SetParameterValue("Charecter", txtCharecter.Text.Trim().ToUpper());
//fr.SetParameterValue("Date", dateTimePicker1.Text.Trim());
//fr.SetParameterValue("TDate", dateTimePicker2.Text.Trim());
//fr.SetParameterValue("Class", cmbAdmissionClass.Text.Trim() + " ( " +Connection.GetClassDeails(cmbAdmissionClass.Text.Trim()) + " )");
//fr.SetParameterValue("NewClass", cmbPromotClass.Text.Trim() + " ( " + Connection.GetClassDeails(cmbPromotClass.Text.Trim()) + " )");
//fr.SetParameterValue("NewSession", cmbsession.Text);
//fr.SetParameterValue("Result", txtresult.Text.Trim());
//fr.SetParameterValue("Percent", txtpercent.Text.Trim());
fr.SetParameterValue("Result", txtresult.Text.Trim());
fr.SetParameterValue("Attadance", txtpercent.Text.Trim());
fr.SetParameterValue("Date", dateTimePicker1.Value.Date.ToString("dd/MM/yyyy"));
fr.SetParameterValue("Charecter", txtCharecter.Text.Trim());
fr.SetParameterValue("IssueOfApplication", dateTimePicker2.Value.Date.ToString("dd/MM/yyyy"));
fr.SetParameterValue("PassedClass", cmbPromotClass.Text.Trim());
fr.SetParameterValue("AdmissionClass", cmbAdmissionClass.Text.Trim());
fr.SetParameterValue("NewClass", cmbPromotClass.Text.Trim());
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = fr;
s.Show();
}
}
}
