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; } }