protected void btngo_Click(object sender, EventArgs e)
{
try
{
string valCollege = string.Empty;
int exMonthName = 0;
int colspanbranch = 0;
int colspansubj = 0;
int coldeg = 0;
int coldegr = 0;
int dsrow = 0;
int inrow = 0;
string sess = string.Empty;
string collegeCode1 = string.Empty;
int total = 0;
bool colspanBool = false;
string BranchCodeReplication = string.Empty;
string degree = string.Empty;
string subjcode = string.Empty;
DataView colSpanDV = new DataView();
DataView colSpansub = new DataView();
DataSet dsCollege = new DataSet();
Fpspread.Sheets[0].Visible = true;
Fpspread.Sheets[0].AutoPostBack = true;
Fpspread.Sheets[0].RowHeader.Visible = false;
Fpspread.Sheets[0].ColumnHeader.Visible = true;
MyStyle.Font.Size = FontUnit.Medium;
MyStyle.Font.Name = "Book Antiqua";
MyStyle.Font.Bold = true;
MyStyle.HorizontalAlign = HorizontalAlign.Center;
MyStyle.ForeColor = Color.White;
MyStyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
Fpspread.Sheets[0].ColumnHeader.DefaultStyle = MyStyle;
Fpspread.CommandBar.Visible = false;
Fpspread.Sheets[0].ColumnCount = 8;
Fpspread.Sheets[0].RowCount = 2;
Fpspread.BorderWidth = 2;
valCollege = rs.GetSelectedItemsValueAsString(cblCollege);
btn_directprint.Visible = true;
if (valCollege.ToString() != "")
{
if (ddlDate.SelectedValue != "" && ddlSession.SelectedValue != "")
{
lblerror.Visible = false;
Fpspread.Visible = true;
int.TryParse(Convert.ToString(ddlMonth.SelectedValue).Trim(), out exMonthName);
string exdate = Convert.ToString(ddlDate.SelectedValue).Trim();
string[] spl1 = exdate.Split('-');
DateTime dtl1 = Convert.ToDateTime(spl1[1] + '-' + spl1[0] + '-' + spl1[2]);
string exmdate = dtl1.ToString("dd");
string exmmonth = dtl1.ToString("MM");
string exmmonthful = dtl1.ToString("MMMM");
string exmyear = dtl1.ToString("yyyy");
string examdate = exmyear + '-' + exmmonth + '-' + exmdate;
string prmonthyea = exmmonthful + '-' + exmyear;
// string sql ="select distinct s.subject_code, de.Dept_Name,c.Course_Name,d.Degree_Code,s.subject_name,d.Acronym,et.exam_date,et.exam_session from exmtt e,exmtt_det et,course c,Degree d,Department de,subject s where e.degree_code=d.Degree_Code and c.Course_Id=d.Course_Id and d.Dept_Code=de.Dept_Code and et.exam_date='" + examdate + "' and e.exam_Month='" + exMonthName + "' and e.Exam_Year='" + ddlYear.SelectedItem.Text + "' and et.exam_session='" + ddlSession.SelectedItem.Text + "' and et.coll_code in('" + valCollege + "')and e.exam_code=et.exam_code and et.subject_no=s.subject_no group by c.Course_Name, de.Dept_Name,d.Degree_Code,s.subject_name,d.Acronym,et.exam_date,et.exam_session,s.subject_code order by de.Dept_Name";
//string sql = "select distinct s.subject_code,de.Dept_Name,c.Course_Name,s.subject_name,et.exam_date,et.exam_session,d.Acronym,COUNT(ea.roll_no) as stucount from Exam_Details e,exam_application ea,exam_appl_details ed,subject s,exmtt_det et, Registration r,Degree d,course c,Department de where e.exam_code=ea.exam_code and ea.appl_no=ed.appl_no and ed.subject_no=s.subject_no and s.subject_no=et.subject_no and ea.roll_no =r.Roll_No and r.degree_code=d.Degree_Code and d.Dept_Code=de.Dept_Code and d.Course_Id=c.Course_Id and e.Exam_Month='" + exMonthName + "'and et.coll_code in('" + valCollege + "') and e.Exam_year='" + ddlYear.SelectedItem.Text + "' and et.exam_date='" + examdate + "' and et.exam_session='" + ddlSession.SelectedItem.Text + "' group by s.subject_code,de.Dept_Name,c.Course_Name,s.subject_name,et.exam_date,et.exam_session,d.Acronym,c.type order by de.Dept_Name";
string sql = "select s.subject_code,(c.Course_Name+'-'+de.Dept_Name) as course,c.Edu_Level,s.subject_name,et.exam_date,et.exam_session,d.Acronym,COUNT(ea.roll_no) as stucount from Exam_Details e,exam_application ea,exam_appl_details ed,subject s,exmtt_det et, Registration r,Degree d,course c,Department de where e.exam_code=ea.exam_code and ea.appl_no=ed.appl_no and ed.subject_no=s.subject_no and s.subject_no=et.subject_no and ea.roll_no =r.Roll_No and r.degree_code=d.Degree_Code and d.Dept_Code=de.Dept_Code and d.Course_Id=c.Course_Id and e.Exam_Month='" + exMonthName + "' and et.coll_code in('" + valCollege + "') and e.Exam_year='" + ddlYear.SelectedItem.Text + "' and et.exam_date='" + examdate + "' and et.exam_session='" + ddlSession.SelectedItem.Text + "' group by s.subject_code,de.Dept_Name,c.Course_Name,s.subject_name,et.exam_date,et.exam_session,d.Acronym,c.Edu_Level,c.type order by s.subject_code";
ds = dt.select_method_wo_parameter(sql, "text");
int tabrow = ds.Tables[0].Rows.Count;
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
Fpspread.Sheets[0].RowCount = 0;
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
Fpspread.Sheets[0].RowCount++;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Text = "Date of Exam";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Columns[0].Width = 50;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Session";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Columns[1].Width = 50;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Subject Code";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Columns[1].Width = 50;
string subj = Convert.ToString(ds.Tables[0].Rows[j]["subject_code"]).Trim();
string subjname = Convert.ToString(ds.Tables[0].Rows[j]["subject_name"]).Trim();
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Branch";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Columns[1].Width = 50;
string edulevel = Convert.ToString(ds.Tables[0].Rows[j]["Edu_Level"]).Trim();
ds.Tables[0].DefaultView.RowFilter = " Edu_Level='" + Convert.ToString(ds.Tables[0].Rows[j]["Edu_Level"]) + "' ";
if (!hat.ContainsKey(edulevel))
{
hat.Add(edulevel, subjname);
Spdegree.InnerHtml = "DEGREE: " + edulevel + "";
}
string bran = Convert.ToString(ds.Tables[0].Rows[j]["course"]).Trim();
ds.Tables[0].DefaultView.RowFilter = " course='" + Convert.ToString(ds.Tables[0].Rows[j]["course"]) + "' ";
colSpanDV = ds.Tables[0].DefaultView;
if (BranchCodeReplication != Convert.ToString(ds.Tables[0].Rows[j]["course"]))
{
Fpspread.Sheets[0].Cells[j, 3].Text = Convert.ToString(bran).Trim();
Fpspread.Sheets[0].Cells[j, 3].VerticalAlign = VerticalAlign.Middle;
Fpspread.Sheets[0].Cells[j, 3].HorizontalAlign = HorizontalAlign.Left;
if (BranchCodeReplication != "")
{
if (colSpanDV.Count > 0)
{
Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - colspanbranch - 1, 3, colspanbranch, 1);
}
}
colspanbranch = 0;
}
colspanbranch++;
BranchCodeReplication = Convert.ToString(ds.Tables[0].Rows[j]["course"]).Trim();
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Q.P Code/Booklet code";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Columns[1].Width = 50;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Answer Paper Packet Number Alloted by College";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Columns[1].Width = 50;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].Text = "Subject";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Columns[1].Width = 50;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].Text = "Total Answer scripts";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Columns[1].Width = 50;
string strquery = "select *,district+' - '+pincode as districtpin from collinfo where college_code='" + Convert.ToString(Session["collegecode"]).Trim() + "'";
DataSet ds1 = new DataSet();
ds1.Dispose();
ds1.Reset();
ds1 = dt.select_method_wo_parameter(strquery, "Text");
spF1College.InnerText = ds1.Tables[0].Rows[0]["Collname"].ToString();
string catagor = ds1.Tables[0].Rows[0]["category"].ToString();
// string[] strpa = Convert.ToString(ds1.Tables[0].Rows[0]["affliatedby"]).Trim().Split(',');
spcategory.InnerHtml = "(" + Convert.ToString(catagor).Trim() + " & " + Convert.ToString(ds1.Tables[0].Rows[0]["university"]).Trim() + ")";
spF1Date.InnerText = "Month & year of Exam: " + Convert.ToString(prmonthyea).Trim() + "";
spHead.InnerText = "DESPATCH OF ANSWER PACKETS ";
dateoedel.InnerHtml = "Date of Delvery:" + Convert.ToString(ddlDate.SelectedValue).Trim() + "";
spsign.InnerHtml = "Signature of the Anna University Representative";
spsignchif.InnerHtml = "Signature of the Chief Superintendent";
spsig.InnerHtml = "Authorized Signatory office of COE";
spnbun.InnerHtml = "Received " + ds.Tables[0].Rows.Count + " bundles from exam cell";
ds.Tables[0].DefaultView.RowFilter = " subject_code='" + Convert.ToString(ds.Tables[0].Rows[j]["subject_code"]) + "' ";
colSpansub = ds.Tables[0].DefaultView;
if (subjcode != Convert.ToString(ds.Tables[0].Rows[j]["subject_code"]))
{
if (subjcode != "")
{
if (colSpansub.Count > 0)
{
dsrow = j;
inrow = j;
dsrow = ds.Tables[0].Rows.Count - 1;
Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - colspansubj - 1, 2, colspansubj, 1);
Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - colspansubj - 1, 6, colspansubj, 1);
}
}
colspansubj = 0;
}
colspansubj++;
if (dsrow == 0 && j == ds.Tables[0].Rows.Count - 1)
{
Fpspread.Sheets[0].SpanModel.Add(0, 2, ds.Tables[0].Rows.Count, 1);
Fpspread.Sheets[0].SpanModel.Add(0, 6, ds.Tables[0].Rows.Count, 1);
}
if (dsrow == j)
{
Fpspread.Sheets[0].SpanModel.Add(inrow, 2, colspansubj, 1);
Fpspread.Sheets[0].SpanModel.Add(inrow, 6, colspansubj, 1);
}
subjcode = Convert.ToString(ds.Tables[0].Rows[j]["subject_code"]).Trim();
if (!hat.ContainsKey(subj))
{
hat.Add(subj, bran);
Fpspread.Sheets[0].Cells[j, 2].Text = Convert.ToString(subj).Trim();
Fpspread.Sheets[0].Cells[j, 2].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[j, 2].VerticalAlign = VerticalAlign.Middle;
Fpspread.Sheets[0].Cells[j, 2].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].Cells[j, 6].Text = Convert.ToString(subjname).Trim();
Fpspread.Sheets[0].Cells[j, 6].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[j, 6].VerticalAlign = VerticalAlign.Middle;
Fpspread.Sheets[0].Cells[j, 6].HorizontalAlign = HorizontalAlign.Left;
}
Fpspread.Sheets[0].Cells[j, 7].Text = Convert.ToString(ds.Tables[0].Rows[j]["stucount"]).Trim();
Fpspread.Sheets[0].Cells[j, 7].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].Cells[j, 7].Font.Size = FontUnit.Medium;
total += Convert.ToInt32(ds.Tables[0].Rows[j]["stucount"].ToString());
}
Fpspread.Sheets[0].SpanModel.Add(0, 0, Fpspread.Sheets[0].RowCount, 1);
Fpspread.Sheets[0].Cells[0, 0].Text = Convert.ToString(exdate).Trim();
Fpspread.Sheets[0].Cells[0, 0].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[0, 0].VerticalAlign = VerticalAlign.Middle;
Fpspread.Sheets[0].Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].SpanModel.Add(0, 1, Fpspread.Sheets[0].RowCount, 1);
Fpspread.Sheets[0].Cells[0, 1].Text = Convert.ToString(ddlSession.SelectedValue).Trim();
Fpspread.Sheets[0].Cells[0, 1].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[0, 1].VerticalAlign = VerticalAlign.Middle;
Fpspread.Sheets[0].Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].RowCount++;
Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - 1, 0, 1, 5);
Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].Text = "Total";
Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 7].Text = Convert.ToString(total).Trim();
Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 7].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Right;
Fpspread.Sheets[0].RowCount++;
Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - 1, 0, 1, Fpspread.Sheets[0].Columns.Count);
if (ddlSession.SelectedValue.Trim() == "A.N")
{
sess = "Afternoon";
}
else
{
sess = "Forenoon";
}
Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].Text = "1.Certificate of opening of sessionwise package of Q.P for " + sess + "";
Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Left;
Fpspread.Sheets[0].RowCount++;
Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - 1, 0, 1, Fpspread.Sheets[0].Columns.Count);
Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].Text = "2.Student Attendance Sheet original copies for " + sess + "";
Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Left;
Fpspread.Sheets[0].PageSize = Fpspread.Sheets[0].RowCount;
Fpspread.Width = 900;
Fpspread.Height = 900;
Fpspread.SaveChanges();
}
else
{
lblerror.Text = "No Records Found";
lblerror.Visible = true;
}
}
else
{
lblerror.Text = "Please select the date and session";
Fpspread.Visible = false;
lblerror.Visible = true;
btn_directprint.Visible = false;
}
}
else
{
lblerror.Text = "Please select all field";
Fpspread.Visible = false;
lblerror.Visible = true;
btn_directprint.Visible = false;
}
}
catch (Exception ex)
{ da.sendErrorMail(ex, collegeCode, "DespatchOfAnswerPackets"); }
}
protected void btngo_Click(object sender, EventArgs e)
{
try
{
//divfpspread.Visible = true;
btnxl.Visible = true;
lblrptname.Visible = true;
txtexcelname.Visible = true;
Printcontrol.Visible = false;
btnprintmaster.Visible = true;
pnlContent1.Visible = true;
Fpspread.Sheets[0].Visible = true;
string valCollege = string.Empty;
lblnorec.Visible = false;
string sumpart = string.Empty;
int value = 0;
int sno = 0;
valCollege = rs.GetSelectedItemsValueAsString(cblCollege);
if (valCollege != "")
{
if (txtfromDate.Text != "" && txttoDate.Text != "")
{
frdate = txtfromDate.Text;
todate = txttoDate.Text;
string dt = frdate;
string[] dsplit = dt.Split(new Char[] { '-' });
frdate = dsplit[2].ToString() + "-" + dsplit[1].ToString() + "-" + dsplit[0].ToString();
demfcal = int.Parse(dsplit[2].ToString());
demfcal = demfcal * 12;
cal_from_date = demfcal + int.Parse(dsplit[1].ToString());
cal_from_attdate_tmp = demfcal + int.Parse(dsplit[1].ToString());
monthcal = cal_from_date.ToString();
dt = todate;
dsplit = dt.Split(new Char[] { '-' });
spltodate = dsplit[1].ToString() + "/" + dsplit[0].ToString() + "/" + dsplit[2].ToString();
todate = dsplit[2].ToString() + "-" + dsplit[1].ToString() + "-" + dsplit[0].ToString();
demtcal = int.Parse(dsplit[2].ToString());
demtcal = demtcal * 12;
cal_to_date = demtcal + int.Parse(dsplit[1].ToString());
cal_to_attdate_tmp = demtcal + int.Parse(dsplit[1].ToString());
per_from_attdate = Convert.ToDateTime(frdate);
per_to_attdate = Convert.ToDateTime(todate);
if (per_from_attdate <= per_to_attdate)
{
#region colbinding
Fpspread.Sheets[0].AutoPostBack = true;
Fpspread.Sheets[0].RowHeader.Visible = false;
Fpspread.Sheets[0].ColumnHeader.Visible = true;
MyStyle.Font.Size = FontUnit.Medium;
MyStyle.Font.Name = "Book Antiqua";
MyStyle.Font.Bold = true;
MyStyle.HorizontalAlign = HorizontalAlign.Center;
MyStyle.ForeColor = Color.White;
MyStyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
Fpspread.Sheets[0].ColumnHeader.DefaultStyle = MyStyle;
Fpspread.CommandBar.Visible = false;
Fpspread.Sheets[0].ColumnCount = 5;
Fpspread.Sheets[0].RowCount = 0;
Fpspread.BorderWidth = 2;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Text = "College Name";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Total Strenth";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Present %";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Absent %";
Fpspread.Columns[4].Width = 25;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold = true;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
#endregion
// // string sql = "select * from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "' and '" + cal_to_attdate_tmp + "' and r.college_code in('" + valCollege + "')";
string sql = "select * from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "' and '" + cal_to_attdate_tmp + "' and r.college_code in('" + valCollege + "') order by r.degree_code,r.Roll_No";
ds = da.select_method_wo_parameter(sql, "text");
int fprow = 0;
for (int clg = 0; clg < cblCollege.Items.Count; clg++)
{
if (cblCollege.Items[clg].Selected == true)
{
double present = 0;
double absent = 0;
int hour = 0;
double tolhour_perclg = 0;
int cunstu = 0;
//int tolhour_perclg = 0;
Fpspread.Sheets[0].RowCount++;
sno++;
string stucon = da.GetFunction("select count(distinct r.app_no) from Registration r where r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and r.college_code='" + cblCollege.Items[clg].Value + "' and Adm_Date<='" + spltodate + "'");
//string stucon = da.GetFunction("select count(distinct r.roll_no) from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "' and '" + cal_to_attdate_tmp + "' and r.college_code in('" + cblCollege.Items[clg].Value + "')");
//ds = da.select_method("bind_branch", has, "sp");
//string gendegree = "select distinct r.degree_code from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "' and '" + cal_to_attdate_tmp + "' and r.college_code='" + cblCollege.Items[clg].Value +"' order by r.degree_code";
string valcolle_code = Convert.ToString(cblCollege.Items[clg].Value).Trim();
int valcoll_code;
int.TryParse(valcolle_code, out valcoll_code);
has.Clear();
has.Add("valcoll_code", valcoll_code);
has.Add("cal_from_attdate_tmp", cal_from_attdate_tmp);
has.Add("cal_to_attdate_tmp", cal_to_attdate_tmp);
DataSet degreegener = da.select_method("attoverallclg_coll", has, "sp");
//DataSet degreegener = da.select_method_wo_parameter(gendegree, "text");
for (int degrow = 0; degrow < degreegener.Tables[0].Rows.Count; degrow++)
{
int tolhour_perdeg = 0;
string hrs = da.GetFunction("select No_of_hrs_per_day from PeriodAttndSchedule where degree_code='" + degreegener.Tables[0].Rows[degrow]["degree_code"] + "'");
int.TryParse(hrs, out hour);
//string stud =da.GetFunction(" select count(distinct r.roll_no) from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "' and '" + cal_to_attdate_tmp + "'and r.degree_code='" + degreegener.Tables[0].Rows[degrow]["degree_code"] + "' and r.college_code='" + cblCollege.Items[clg].Value + "'");
ds.Tables[0].DefaultView.RowFilter = "college_code='" + cblCollege.Items[clg].Value + "' and degree_code='" + degreegener.Tables[0].Rows[degrow]["degree_code"] + "'";
DataView clgfilter = ds.Tables[0].DefaultView;
for (DateTime dtt = per_from_attdate; dtt <= per_to_attdate; dtt = dtt.AddDays(1))
{
//for (int degrow = 0; degrow < clgfilter.Count; degrow++)
// {
//ds.Tables[0].DefaultView.RowFilter = "month_year='" + cal_from_date + "'";
for (int i = 1; i <= hour; i++)
{
for (int sturow = 0; sturow < clgfilter.Count; sturow++)
{
date = "d" + dtt.Day.ToString("") + "d" + i.ToString();
string maxval = Convert.ToString(clgfilter[sturow][date]).Trim();
if (maxval != "" && maxval != "0" && maxval != "NULL" && maxval != "Null" && maxval != "null" && maxval != "H" && maxval != "NJ" && maxval != "Null")
{
int.TryParse(maxval, out value);
string valqu = da.GetFunction("select CalcFlag from AttMasterSetting where collegecode=13 and LeaveCode='" + value + "' group by CalcFlag");
int.TryParse(valqu, out value);
if (value == 0)
{
present = present + 1;
}
else
{
absent = absent + 1;
}
}
else
{
}
}
}
tolhour_perdeg += hour;
}
//int.TryParse(stud, out cunstu);
//tolhour_perdeg *= cunstu;
//tolhour_perclg += tolhour_perdeg;
tolhour_perclg = present + absent;
}
if (present != 0)
{
present = (present / tolhour_perclg);
present = Math.Round(present, 3);
present = present * 100;
sumpart = String.Format("{0:0.00}", present);
}
if (absent != 0)
{
absent = (absent / tolhour_perclg);
absent = Math.Round(absent, 3);
absent = absent * 100;
sumpart = String.Format("{0:0.00}", absent);
}
Fpspread.Sheets[0].Cells[fprow, 0].Text = sno.ToString();
Fpspread.Columns[0].Width = 10;
Fpspread.Sheets[0].Cells[fprow, 0].Font.Bold = true;
Fpspread.Sheets[0].Cells[fprow, 0].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].Cells[fprow, 0].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[fprow, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].Cells[fprow, 1].Text = cblCollege.Items[clg].ToString();
Fpspread.Columns[1].Width = 100;
Fpspread.Sheets[0].Cells[fprow, 1].Font.Bold = true;
Fpspread.Sheets[0].Cells[fprow, 1].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].Cells[fprow, 1].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[fprow, 1].HorizontalAlign = HorizontalAlign.Left;
Fpspread.Sheets[0].Cells[fprow, 2].Text = stucon.ToString();
Fpspread.Columns[2].Width = 25;
Fpspread.Sheets[0].Cells[fprow, 2].Font.Bold = true;
Fpspread.Sheets[0].Cells[fprow, 2].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].Cells[fprow, 2].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[fprow, 2].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].Cells[fprow, 3].Text = present.ToString();
Fpspread.Columns[3].Width = 25;
Fpspread.Sheets[0].Cells[fprow, 3].Font.Bold = true;
Fpspread.Sheets[0].Cells[fprow, 3].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].Cells[fprow, 3].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[fprow, 3].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].Cells[fprow, 4].Text = absent.ToString();
Fpspread.Sheets[0].Cells[fprow, 4].Font.Bold = true;
Fpspread.Sheets[0].Cells[fprow, 4].Font.Name = "Book Antiqua";
Fpspread.Sheets[0].Cells[fprow, 4].Font.Size = FontUnit.Medium;
Fpspread.Sheets[0].Cells[fprow, 4].HorizontalAlign = HorizontalAlign.Center;
Fpspread.Sheets[0].PageSize = Fpspread.Sheets[0].RowCount;
Fpspread.Width = 900;
Fpspread.Height = 700;
Fpspread.SaveChanges();
fprow++;
}
}
}
else
{
Fpspread.Visible = false;
btnxl.Visible = false;
lblrptname.Visible = false;
txtexcelname.Visible = false;
Printcontrol.Visible = false;
btnprintmaster.Visible = false;
lblnorec.Visible = true;
lblnorec.Text = "Please select the To date is greater then From date";
}
}
else
{
Fpspread.Visible = false;
btnxl.Visible = false;
lblrptname.Visible = false;
txtexcelname.Visible = false;
Printcontrol.Visible = false;
btnprintmaster.Visible = false;
lblnorec.Visible = true;
lblnorec.Text = "Please select the From date and To date";
}
}
else
{
btnxl.Visible = false;
lblrptname.Visible = false;
txtexcelname.Visible = false;
Printcontrol.Visible = false;
btnprintmaster.Visible = false;
Printcontrol.Visible = false;
Fpspread.Visible = false;
lblnorec.Visible = true;
lblnorec.Text = "Please select the College";
}
}
catch (Exception ex)
{
da.sendErrorMail(ex, collegeCode, "OverallcollegeAttendancepercentage");
}
}
