public void gomethod()
{
string sqlcmdgraderstotal = "";
string GPA_Val = "";
string CGPA_Val = "";
string RollNo1 = "";
string semval = "";
string strtempseme = "";
string latmode1 = "";
FpSpread1.Sheets[0].RowCount = 0;
FpSpread1.Sheets[0].ColumnCount = 0;
FpSpread1.Sheets[0].ColumnHeader.Visible = true;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].AutoPostBack = true;
FpSpread1.Sheets[0].ColumnHeader.RowCount = 1;
FpSpread1.Sheets[0].ColumnCount = 8;
FpSpread1.Sheets[0].RowCount = 0;
string sqlcmdall = "select distinct ROW_NUMBER() OVER (ORDER BY Roll_no) As SrNo,Convert(Varchar,batch_year) + '-' + acronym as Degree,roll_no,reg_no,stud_name,stud_type,Convert(Varchar,r.batch_year) + '-' + Convert(Varchar,r.degree_code) as DegreeCode,mode as Mode from registration r,degree d where r.degree_code=d.degree_code";
if (txtbatch.Text != "---Select---" || chklstbatch.Items.Count != null)
{
int itemcount = 0;
for (itemcount = 0; itemcount < chklstbatch.Items.Count; itemcount++)
{
if (chklstbatch.Items[itemcount].Selected == true)
{
if (strbatch == "")
{
strbatch = chklstbatch.Items[itemcount].Value.ToString();
}
else
{
strbatch = strbatch + "," + chklstbatch.Items[itemcount].Value.ToString();
}
}
}
if (strbatch != "")
{
strbatch = " in(" + strbatch + ")";
}
sqlcmdall = sqlcmdall + " and r.batch_year " + strbatch + "";
sqlcmdbatch = " batch_year " + strbatch + "";
}
else
{
// errmsg.Visible = true;
errmsg.Text = "Plaese Choose Batch";
}
if (txtbranch.Text != "---Select---" || chklstbranch.Items.Count != null)
{
int itemcount1 = 0;
for (itemcount1 = 0; itemcount1 < chklstbranch.Items.Count; itemcount1++)
{
if (chklstbranch.Items[itemcount1].Selected == true)
{
if (strbranch == "")
{
strbranch = chklstbranch.Items[itemcount1].Value.ToString();
}
else
{
strbranch = strbranch + "," + chklstbranch.Items[itemcount1].Value.ToString();
}
}
}
if (strbranch != "")
{
strbranch = " in (" + strbranch + ")";
}
sqlcmdall = sqlcmdall + " and r.degree_code" + strbranch + "";
sqlcmdbranch = " degree_code" + strbranch + " and";
}
else
{
// errmsg.Visible = true;
errmsg.Text = "Plaese Choose Degree";
}
if (chklstsection.Items.Count > 0)
{
if (txtsection.Text != "---Select---" || chklstsection.Items.Count != null)
{
int itemcount = 0;
if (chklstsection.Items[chklstsection.Items.Count - 1].Selected == true)
{
sqlcmdall1 = "or sections is null or sections=''";
}
for (itemcount = 0; itemcount < chklstsection.Items.Count - 1; itemcount++)
{
if (chklstsection.Items[itemcount].Selected == true)
{
if (strsecti == "")
{
strsecti = "'" + chklstsection.Items[itemcount].Value.ToString() + "'";
}
else
{
strsecti = strsecti + "," + "'" + chklstsection.Items[itemcount].Value.ToString() + "'";
}
}
}
if (strsecti != "")
{
strsecti = " in(" + strsecti + ")";
sqlcmdall = sqlcmdall + " and (sections " + strsecti + sqlcmdall1 + ")";
}
}
}
if (txtseme.Text != "---Select---" || chklstseme.Items.Count != null)
{
int itemcount3 = 0;
for (itemcount3 = 0; itemcount3 < chklstseme.Items.Count; itemcount3++)
{
if (chklstseme.Items[itemcount3].Selected == true)
{
if (strseme == "")
{
strseme = chklstseme.Items[itemcount3].Value.ToString();
}
else
{
strseme = strseme + "," + chklstseme.Items[itemcount3].Value.ToString();
}
if (strseme == "")
{
strtempseme = chklstseme.Items[itemcount3].Value.ToString();
}
else
{
strtempseme = strtempseme + "," + chklstseme.Items[itemcount3].Value.ToString();
string[] semecount = strtempseme.Split(new Char[] { ',' });
if (semecount.GetUpperBound(0) >= 0)
{
int semcount = semecount.GetUpperBound(0);
semval = Convert.ToString(semecount[semcount]);
}
}
}
}
if (strseme != "")
{
strseme = " in(" + strseme + ")";
sqlcmdseme = " and current_semester " + strseme + "";
}
}
else
{
// errmsg.Visible = true;
errmsg.Text = "Plaese Choose Semester";
}
sqlcmdall = sqlcmdall + "and cc=0 and delflag=0 and exam_flag<>'debar' order by r.reg_no";
dgo = d2.select_method(sqlcmdall, hat, "Text");
FarPoint.Web.Spread.TextCellType textcell = new FarPoint.Web.Spread.TextCellType();
if (dgo != null && dgo.Tables[0] != null && dgo.Tables[0].Rows.Count > 0)
{
FpSpread1.Visible = true;
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 1, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Degree";
FpSpread1.Sheets[0].SetColumnMerge(1, FarPoint.Web.Spread.Model.MergePolicy.Always);
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 2, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Roll No";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 3, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Reg No";
FpSpread1.Sheets[0].Columns[3].CellType = textcell;
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 4, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Student Name";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 5, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Student Type";
FpSpread1.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Columns[1].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Columns[6].Visible = false;
FpSpread1.Sheets[0].Columns[7].Visible = false;
//FpSpread1.DataSource = dgo;
//FpSpread1.DataBind();
int slno = 0;
for (int cnt = 0; cnt < dgo.Tables[0].Rows.Count; cnt++)
{
slno++;
FpSpread1.Sheets[0].RowCount = FpSpread1.Sheets[0].RowCount + 1;
int rc = FpSpread1.Sheets[0].RowCount - 1;
//magesh 27/2/18
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
FpSpread1.Sheets[0].Cells[rc, 2].CellType = txt;//magesh 27/1/18
FpSpread1.Sheets[0].Cells[rc, 0].Text = slno.ToString();
FpSpread1.Sheets[0].Cells[rc, 1].Text = dgo.Tables[0].Rows[cnt]["Degree"].ToString();
FpSpread1.Sheets[0].Cells[rc, 2].Text = dgo.Tables[0].Rows[cnt]["roll_no"].ToString();
FpSpread1.Sheets[0].Cells[rc, 3].Text = dgo.Tables[0].Rows[cnt]["reg_no"].ToString();
FpSpread1.Sheets[0].Cells[rc, 4].Text = dgo.Tables[0].Rows[cnt]["stud_name"].ToString();
FpSpread1.Sheets[0].Cells[rc, 5].Text = dgo.Tables[0].Rows[cnt]["stud_type"].ToString();
FpSpread1.Sheets[0].Cells[rc, 6].Text = dgo.Tables[0].Rows[cnt]["DegreeCode"].ToString();
FpSpread1.Sheets[0].Cells[rc, 7].Text = dgo.Tables[0].Rows[cnt]["Mode"].ToString();
}
sqlcmdretriveunialldet = "select distinct exam_month,exam_year,DATENAME(MONTH,'1990/' + CAST(exam_month AS VARCHAR(3)) + '/23')as exam_month1 from exam_details where " + sqlcmdbranch + " " + sqlcmdbatch + " " + sqlcmdseme + " order by exam_year,exam_month";
dgo2 = d2.select_method(sqlcmdretriveunialldet, hat, "Text");
if (dgo2 != null && dgo2.Tables[0] != null && dgo2.Tables[0].Rows.Count > 0)
{
for (int o = 0; o < dgo2.Tables[0].Rows.Count; o++)
{
strexammonth = Convert.ToString(dgo2.Tables[0].Rows[o]["exam_month1"]);
strexamyear = dgo2.Tables[0].Rows[o]["exam_year"].ToString();
FpSpread1.Sheets[0].ColumnCount = FpSpread1.Sheets[0].ColumnCount + 1;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = Convert.ToString(strexammonth + "-" + strexamyear);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Note = Convert.ToString(dgo2.Tables[0].Rows[o]["exam_month"]);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Tag = Convert.ToString(strexamyear);
}
FpSpread1.Sheets[0].ColumnCount = FpSpread1.Sheets[0].ColumnCount + 1;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "CGPA";
}
FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
for (int q = 0; q < FpSpread1.Sheets[0].RowCount; q++)
{
int col_count = FpSpread1.Sheets[0].ColumnCount;
string tempt1 = "", tempt2 = "";
Boolean checkfailstatus = false;//added By srinath
for (int r = 8; r < col_count; r++)
{
RollNo1 = Convert.ToString(FpSpread1.Sheets[0].Cells[q, 2].Text);
string[] degcodebatchyr = Convert.ToString(FpSpread1.Sheets[0].Cells[q, 6].Text).Split(new Char[] { '-' });
if (degcodebatchyr.GetUpperBound(0) >= 1)
{
batch_year1 = Convert.ToString(degcodebatchyr[0]);
degree_code1 = Convert.ToString(degcodebatchyr[1]);
}
exam_month1 = Convert.ToString(FpSpread1.Sheets[0].ColumnHeader.Cells[0, r].Note);
exam_year1 = Convert.ToString(FpSpread1.Sheets[0].ColumnHeader.Cells[0, r].Tag);
latmode1 = Convert.ToString(dgo.Tables[0].Rows[q]["mode"]);
if (RollNo1 != null && RollNo1 != "" && degree_code1 != null && degree_code1 != "" && batch_year1 != null && batch_year1 != "" && exam_month1 != null && exam_month1 != "" && exam_year1 != null && exam_year1 != "" && semval != null && semval != "" && latmode1 != null && latmode1 != "")
{
sqlcmdgraderstotal = " select distinct frange,trange,credit_points,mark_grade from grade_master where degree_code=" + degree_code1 + " and batch_year=" + batch_year1 + " and college_code=" + Session["collegecode"] + "";
dggradetot = d2.select_method(sqlcmdgraderstotal, hat, "Text");
//Added By Srinath 12/2/2013 ==Start
string syll_code = "";
syll_code = GetFunction("select distinct syll_code from exam_details e,syllabus_master s where e.degree_code=s.degree_code and e.batch_year=s.batch_year and e.current_semester=s.semester and e.degree_code='" + degree_code1 + "' and e.batch_year=" + batch_year1 + " and exam_month=" + exam_month1 + " and exam_year=" + exam_year1 + "");
//string checkresult = "Select Mark_Entry.result,Subject.credit_points,Mark_Entry.total,Mark_Entry.grade from Mark_Entry,Subject,exam_details e where Mark_Entry.Subject_No = Subject.Subject_No and e.exam_code=mark_entry.exam_code and e.degree_code=" + degree_code1 + " and e.batch_year=" + batch_year1 + " and subject.syll_code="+ syll_code +" and roll_no='" + RollNo1 + "' and result<>'Pass'";
string checkresult = "Select isnull(Subject_Code,'') as scode , isnull(subjecT_name,'') as sname , semester from subject,syllabus_master as smas where smas.syll_code = subject.syll_code and subject_no in (select distinct subject_no from mark_entry where subject_no not in (select distinct subject_no from mark_entry where passorfail=1 and result='Pass' and ltrim(rtrim(roll_no))='" + RollNo1 + "') and roll_no ='" + RollNo1 + "' and subject.syll_code=" + syll_code.ToString() + " )";
DataSet dscheckresult = d2.select_method(checkresult, hat, "Text");
//Rajkumar for fail CGPA on 29-5-2018 ===========
bool ArrerCheckFlag = false;
string val1 = d2.GetFunctionv("select value from Master_Settings where settings = 'include gpa for fail student'");//Rajkumar on 28-5-2018
if (val1.Trim() == "true" || val1.Trim() == "1")
{
ArrerCheckFlag = true;
}
//=====================
if (dscheckresult.Tables[0].Rows.Count > 0 && !ArrerCheckFlag)
{
GPA_Val = "-";
checkfailstatus = true;
}
else
{
// GPA_Val = Calulat_GPA(RollNo1,degree_code1,batch_year1,exam_month1 ,exam_year1 );//modified By Srinath 12/2/2013
GPA_Val = d2.Calulat_GPA_Semwise(RollNo1, degree_code1, batch_year1, exam_month1, exam_year1, collegecode);
}
//------------------rajkumar
FpSpread1.Sheets[0].Cells[q, r].HorizontalAlign = HorizontalAlign.Center;
if (Convert.ToString(GPA_Val) == "0")
{
FpSpread1.Sheets[0].Cells[q, r].Text = "-";
}
else
{
FpSpread1.Sheets[0].Cells[q, r].Text = Convert.ToString(GPA_Val);
}
//===End
if (r >= col_count - 2)
{ //Added By Srinath 12/2/2013 ==Start
if (checkfailstatus == false)
{
CGPA_Val = d2.Calculete_CGPA(RollNo1, semval, degree_code1, batch_year1, latmode1, collegecode);
}
else
{
CGPA_Val = "-";
}
// CGPA_Val = Calculete_CGPA(RollNo1, semval, degree_code1, batch_year1, exam_month1, exam_year1, latmode1);//Hiden By Srinath 12/2/2013
//===End
FpSpread1.Sheets[0].Cells[q, FpSpread1.Sheets[0].ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center;
if (Convert.ToString(CGPA_Val) == "0")
{
FpSpread1.Sheets[0].Cells[q, FpSpread1.Sheets[0].ColumnCount - 1].Text = "-";
}
else
{
FpSpread1.Sheets[0].Cells[q, FpSpread1.Sheets[0].ColumnCount - 1].Text = Convert.ToString(CGPA_Val);
}
}
}
}
}
btnxl.Visible = true;
Button1.Visible = true;
}
}
protected void btn_go(object sender, EventArgs e)
{
try
{
clear();
FpSpread1.Sheets[0].SheetCorner.ColumnCount = 0;
FpSpread1.Sheets[0].ColumnHeader.RowCount = 2;
FpSpread1.Sheets[0].ColumnCount = 0;
FpSpread1.Sheets[0].RowCount = 0;
FpSpread1.CommandBar.Visible = false;
FpSpread1.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Name = "Book Antiqua";
FpSpread1.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread1.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Bold = true;
FpSpread1.Sheets[0].DefaultStyle.Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
FarPoint.Web.Spread.StyleInfo style2 = new FarPoint.Web.Spread.StyleInfo();
style2.Font.Size = 13;
style2.Font.Name = "Book Antiqua";
style2.Font.Bold = true;
style2.HorizontalAlign = HorizontalAlign.Center;
style2.ForeColor = Color.White;
style2.BackColor = Color.Teal;
FpSpread1.Sheets[0].SheetCornerStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread1.Sheets[0].ColumnHeader.DefaultStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
DataSet dsrank = new DataSet();
Hashtable hatstutotal = new Hashtable();
DataSet dsexam = new DataSet();
DataView dvcount = new DataView();
string batchcount = "";
string sqlbatch = "";
string branchcount = "";
string sqlbranch = "";
string exam_year = "";
string exam_month = "";
collegecode = ddlclg.SelectedItem.Value;
for (int itemcount = 0; itemcount < chklsbatch.Items.Count; itemcount++)
{
if (chklsbatch.Items[itemcount].Selected == true)
{
if (batchcount == "")
{
batchcount = "'" + chklsbatch.Items[itemcount].Value.ToString() + "'";
}
else
{
batchcount = batchcount + ",'" + chklsbatch.Items[itemcount].Value.ToString() + "'";
}
}
}
if (batchcount != "")
{
sqlbatch = " and r.batch_year in(" + batchcount + ")";
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Batch And Then Proceed";
return;
}
for (int itemcount = 0; itemcount < chklstbranch.Items.Count; itemcount++)
{
if (chklstbranch.Items[itemcount].Selected == true)
{
if (branchcount == "")
{
branchcount = "'" + chklstbranch.Items[itemcount].Value.ToString() + "'";
}
else
{
branchcount = branchcount + ",'" + chklstbranch.Items[itemcount].Value.ToString() + "'";
}
}
}
if (branchcount != "")
{
sqlbranch = " and r.degree_code in(" + branchcount + ")";
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Degree and Branch And Then Proceed";
return;
}
if (ddlyear.Items.Count > 0)
{
if (ddlyear.SelectedItem.ToString().Trim() != "")
{
exam_year = ddlyear.SelectedValue.ToString();
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Exam Year And Then Proceed";
return;
}
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Exam Year And Then Proceed";
return;
}
if (ddlmonth.Items.Count > 0)
{
if (ddlmonth.SelectedItem.ToString().Trim() != "")
{
exam_month = ddlmonth.SelectedValue.ToString();
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Exam Month And Then Proceed";
return;
}
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Exam Month And Then Proceed";
return;
}
Double cgpafrom = 0;
Double cgpato = 0;
string strfrange = txt_rangefrom.Text.ToString();
if (strfrange.Trim() != "")
{
cgpafrom = Convert.ToDouble(strfrange);
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The CGPA From Range And Then Proceed";
return;
}
string strtrange = txt_to.Text.ToString();
if (strtrange.Trim() != "")
{
cgpato = Convert.ToDouble(strtrange);
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The CGPA To Range And Then Proceed";
return;
}
if (cgpato < cgpafrom)
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Enter The CGPA To Range Must Be Greater Then From Range";
return;
}
string query = "select distinct r.stud_name,r.Reg_No,r.batch_year,dg.Acronym,course.Course_Name,ed.current_semester,r.sections,r.degree_code,d.dept_name,course.course_name,dg.course_id,r.mode ,r.roll_no,r.degree_code,r.mode from registration r,mark_entry me ,department d,course,degree dg,Exam_Details ed where me.roll_no=r.roll_no and r.degree_code=dg.degree_code and dg.course_id = course.course_id and dg.dept_code = d.dept_code and ed.degree_code=r.degree_code and ed.batch_year=r.Batch_Year and me.result='pass' and ed.Exam_Month='" + exam_month + "' and ed.Exam_year='" + exam_year + "' " + sqlbatch + " " + sqlbranch + " order by r.degree_code,r.Batch_Year";
string getbranchvalue = "select distinct d.dept_acronym,c.Course_Name,dg.Degree_Code from registration r,mark_entry me ,department d,course c,degree dg,Exam_Details ed where me.roll_no=r.roll_no and r.degree_code=dg.degree_code and dg.course_id = c.course_id and dg.dept_code = d.dept_code and ed.degree_code=r.degree_code and ed.batch_year=r.Batch_Year and me.result='pass' and ed.Exam_Month='" + exam_month + "' and ed.Exam_year='" + exam_year + "' " + sqlbatch + " " + sqlbranch + " order by c.Course_Name,d.dept_acronym";
ds.Dispose();
ds.Reset();
ds = d2.select_method(query, hat, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
DataSet dstable = d2.select_method("select * from sysobjects where name='tbl_Topperrank' and Type='U'", hat, "text ");
if (dstable.Tables[0].Rows.Count == 0)
{
int p = d2.insert_method("create table tbl_Topperrank (roll_no nvarchar(50),cgpa float (8),stud_name nvarchar(200),degree nvarchar(500),user_code nvarchar(25))", hat, "text");
}
else
{
int p = d2.insert_method("IF not EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_Topperrank' AND COLUMN_NAME = 'user_code') alter table tbl_Topperrank add user_code nvarchar(15)", hat, "text");
}
ds.Dispose();
dsfind = d2.select_method("select name from sysobjects where xtype='p' and name='sp_ins_upd_topperrank' ", hat, "text");
if (dsfind.Tables[0].Rows.Count == 0)
{
string spcreation = " CREATE procedure sp_ins_upd_topperrank (@RollNumber varchar(50), @cgpa varchar(20), @stud_name varchar(20), @degree varchar(200) ,@user_code nvarchar(25)) as declare @cou_nt int set @cou_nt=(select count(Roll_no)from tbl_Topperrank where Roll_no=@RollNumber) if @cou_nt=0 BEGIN insert into tbl_Topperrank(Roll_no,cgpa,stud_name,degree,user_code) values (@RollNumber,@cgpa,@stud_name,@degree,@user_code) End Else BEGIN update tbl_Topperrank set cgpa=@cgpa where Roll_no=@RollNumber and user_code=@user_code end";
int s = d2.insert_method(spcreation, hat, "Text");
}
else
{
string spalter = " alter procedure sp_ins_upd_topperrank (@RollNumber varchar(50), @cgpa varchar(20), @stud_name varchar(20), @degree varchar(200) ,@user_code nvarchar(25)) as declare @cou_nt int set @cou_nt=(select count(Roll_no)from tbl_Topperrank where Roll_no=@RollNumber) if @cou_nt=0 BEGIN insert into tbl_Topperrank(Roll_no, cgpa,stud_name,degree,user_code)values(@RollNumber,@cgpa,@stud_name,@degree, @user_code) End Else BEGIN update tbl_Topperrank set cgpa=@cgpa where Roll_no=@RollNumber and user_code=@user_code End";
int gf = d2.insert_method(spalter, hat, "Text");
}
Boolean rowflag = false;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "CGPA";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 2, 1);
string tempdegree = "";
DataSet dsdegree = d2.select_method_wo_parameter(getbranchvalue, "text");
int spancolumn = 0;
Boolean valkflag = false;
for (int d = 0; d < dsdegree.Tables[0].Rows.Count; d++)
{
string strdegree = dsdegree.Tables[0].Rows[d]["dept_acronym"].ToString();
string Course = dsdegree.Tables[0].Rows[d]["Course_Name"].ToString();
if (tempdegree != Course || d == dsdegree.Tables[0].Rows.Count - 1)
{
if (tempdegree != "")
{
if (tempdegree == Course && d == dsdegree.Tables[0].Rows.Count - 1)
{
spancolumn++;
valkflag = true;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = Course;
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 1].Text = strdegree;
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 1].Note = dsdegree.Tables[0].Rows[d]["Degree_Code"].ToString();
}
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, FpSpread1.Sheets[0].ColumnCount - spancolumn, 1, spancolumn);
}
spancolumn = 0;
tempdegree = Course;
}
if (valkflag == false)
{
spancolumn++;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = Course;
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 1].Text = strdegree;
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 1].Note = dsdegree.Tables[0].Rows[d]["Degree_Code"].ToString();
}
}
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "Total";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, FpSpread1.Sheets[0].ColumnCount - 1, 2, 1);
for (int y = 0; y < chklsbatch.Items.Count; y++)
{
if (chklsbatch.Items[y].Selected == true)
{
string batch = chklsbatch.Items[y].Text.ToString();
FpSpread1.Sheets[0].RowCount++;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].CellType = txt;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = "Batch " + batch + "";
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].BackColor = Color.AliceBlue;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].SpanModel.Add(FpSpread1.Sheets[0].RowCount - 1, 0, 1, FpSpread1.Sheets[0].ColumnCount);
int startow = FpSpread1.Sheets[0].RowCount;
for (Double dc = cgpato; dc >= cgpafrom; dc = dc - 0.5)
{
FpSpread1.Sheets[0].RowCount++;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].CellType = txt;
if (dc == cgpato)
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = ">" + dc.ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Note = " and cgpa >" + dc + "";
}
else
{
Double doc1 = dc + 0.5;
Double doc2 = dc + 0.001;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = dc + " - " + doc1;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Note = " and cgpa between " + doc2 + " and " + doc1 + "";
}
}
for (int c = 1; c < FpSpread1.Sheets[0].ColumnCount - 1; c++)
{
string degreecode = FpSpread1.Sheets[0].ColumnHeader.Cells[1, c].Note.ToString();
ds.Tables[0].DefaultView.RowFilter = "Batch_year='" + batch + "' and degree_code='" + degreecode + "'";
DataView dvstud = ds.Tables[0].DefaultView;
for (int s = 0; s < dvstud.Count; s++)
{
string rollno = dvstud[s]["roll_no"].ToString();
string sem = dvstud[s]["current_semester"].ToString();
string mode = dvstud[s]["mode"].ToString();
string name = dvstud[s]["stud_name"].ToString();
string degreevalue = dvstud[s]["Acronym"].ToString();
int failcount = Convert.ToInt32(d2.GetFunction(" Select COUNT(*) from Mark_Entry,Subject where Mark_Entry.Subject_No = Subject.Subject_No and roll_no='" + rollno + "' and result='fail' and result='Fail' "));
if (failcount == 0)
{
string cgpav = d2.Calculete_CGPA(rollno, sem, degreecode, batch, mode, collegecode);
if (cgpav != "0" && cgpav != "" && cgpav != "-" && cgpav != "NaN")
{
Double num = 0;
if (Double.TryParse(cgpav, out num))
{
hat.Clear();
hat.Add("RollNumber", rollno);
hat.Add("cgpa", cgpav.ToString());
hat.Add("stud_name", name.ToString());
hat.Add("degree", degreevalue.ToString());
hat.Add("user_code", usercode.ToString());
int o = d2.insert_method("sp_ins_upd_topperrank", hat, "sp");
}
}
}
}
for (int r = startow; r < FpSpread1.Sheets[0].RowCount; r++)
{
string valran = FpSpread1.Sheets[0].Cells[r, 0].Note;
string getstucount = d2.GetFunction("select count(roll_no) from tbl_Topperrank where user_code='" + usercode + "' " + valran + "");
if (getstucount.Trim() != "0" && getstucount.Trim() != "")
{
rowflag = true;
}
FpSpread1.Sheets[0].Cells[r, c].CellType = txt;
FpSpread1.Sheets[0].Cells[r, c].Text = getstucount;
FpSpread1.Sheets[0].Cells[r, c].HorizontalAlign = HorizontalAlign.Center;
}
int p = d2.insert_method("Delete from tbl_Topperrank where user_code='" + usercode + "' ", hat, "text");
}
}
}
for (int r = 1; r < FpSpread1.Sheets[0].RowCount; r++)
{
int totalstudent = 0;
for (int c = 1; c < FpSpread1.Sheets[0].ColumnCount - 1; c++)
{
string rowsvalue = FpSpread1.Sheets[0].Cells[r, c].Text.ToString();
if (rowsvalue.Trim() != "")
{
totalstudent = totalstudent + Convert.ToInt32(rowsvalue);
}
FpSpread1.Sheets[0].Cells[r, FpSpread1.Sheets[0].ColumnCount - 1].Text = totalstudent.ToString();
FpSpread1.Sheets[0].Cells[r, FpSpread1.Sheets[0].ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center;
}
}
if (rowflag == true)
{
FpSpread1.Visible = true;
lbl_rptname.Visible = true;
btn_excel.Visible = true;
btn_print.Visible = true;
txt_rpt.Visible = true;
}
else
{
lbl_errmsg.Text = "No Records Found";
lbl_errmsg.Visible = true;
}
}
else
{
lbl_errmsg.Text = "No Records Found";
lbl_errmsg.Visible = true;
}
FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
}
catch (Exception ex)
{
lbl_errmsg.Text = ex.ToString();
lbl_errmsg.Visible = true;
}
}
