protected void btn_go_Click(object sender, EventArgs e) { btnSave.Visible = false; gridStudList.Visible = false; ledgersave.Visible = false; Cancelhold.Visible = false; grid.Visible = false; GridView1.Visible = false; Grid1.Visible = false; string ledger = Convert.ToString(getCblSelectedText(chkl_studled)); if (string.IsNullOrEmpty(ledger)) { try { grid.Visible = true; lbl_errormsg.Visible = false; string selectquery; string branch = reUse.GetSelectedItemsValueAsString(cbl_branch); //ddl_branch.Items.Count > 0 ? ddl_branch.SelectedValue : ""; string degCode = reUse.GetSelectedItemsValueAsString(cbl_degree); //ddl_degree.Items.Count > 0 ? ddl_degree.SelectedValue : ""; string stream = ddl_strm.Enabled ? ddl_strm.Items.Count > 0 ? ddl_strm.SelectedItem.Text.Trim() : "" : ""; string section = reUse.GetSelectedItemsText(cbl_sec);//ddl_sec.Items.Count > 0 ? ddl_sec.SelectedItem.Text.Trim() : ""; string batch_year = ddl_batch.Items.Count > 0 ? ddl_batch.SelectedItem.Text : ""; string cusem = reUse.GetSelectedItemsText(cbl_sem);// ddl_sem.Items.Count > 0 ? ddl_sem.SelectedItem.Text : ""; DataTable dtStud = new DataTable(); if (batch_year != string.Empty && degCode != string.Empty && branch != string.Empty && cusem != string.Empty) { if (stream != string.Empty) { stream = " and c.type in ('" + stream + "')"; } string rptType = string.Empty; if (ddlType.SelectedItem.Text == "Part Payment") { rptType = ",isPartAmount"; } else { rptType = ",isPartAmount,isPartHold"; } selectquery = "select r.Roll_No,r.Roll_Admit,r.smart_serial_no,r.Stud_Name,d.Degree_Code,(C.Course_Name +' - '+ dt.Dept_Name) as Department,r.Reg_No,r.App_No,c.type,isnull(r.Sections,'') as Sections, case when a.sex=0 then 'Male' when a.sex=1 then 'Female' when a.sex=2 then 'Transgender' else 'N/A' end as Gender,a.IsFinPartPay" + rptType + " from Registration r,applyn a,Degree d,Department dt,Course c where r.app_no=a.app_no and r.degree_code =d.Degree_Code and dt.Dept_Code =d.Dept_Code and c.Course_Id =d.Course_Id and CC=0 and DelFlag =0 and Exam_Flag <>'DEBAR' and r.Batch_Year =" + batch_year + " and r.degree_code in ('" + branch + "') and isnull(r.Sections,'') in ('" + section + "') and r.current_semester in('" + cusem + "') and isnull(a.admission_status,'0')='1' " + stream + " order by d.Degree_Code,isnull(r.Sections,''),ltrim (r.Roll_No) asc ,ltrim (r.stud_name) asc "; dtStud = dirAcc.selectDataTable(selectquery); } if (dtStud.Rows.Count > 0) { //for (int row = 0; row < dtStud.Rows.Count; row++) //{ //} gridStudList.DataSource = dtStud; gridStudList.DataBind(); gridStudList.Visible = true; btnSave.Visible = true; } else { gridStudList.DataSource = null; gridStudList.DataBind(); gridStudList.Visible = false; btnSave.Visible = false; lbl_errormsg.Visible = true; lbl_errormsg.Text = "No Records Found"; } } catch { gridStudList.DataSource = null; gridStudList.DataBind(); gridStudList.Visible = false; btnSave.Visible = false; lbl_errormsg.Visible = true; lbl_errormsg.Text = "No Records Found"; } } else { bindsemledger(); dsload = dsvalue(); single.Visible = false; multiple.Visible = true; try { ledgersave.Visible = true; Cancelhold.Visible = true; grid.Visible = false; #region design // loaddesc1(); #endregion #region value int sno = 0; if (dsload.Tables[0].Rows.Count > 0) { //for (int row = 0; row < dtStud.Rows.Count; row++) //{ //} GridView1.DataSource = dsload; GridView1.DataBind(); GridView1.Visible = true; //btnSave.Visible = true; Grid1.Visible = true; DropDownList ddlledger = (DropDownList)GridView1.HeaderRow.FindControl("grid1ddl_ledgerhead"); for (int i = 0; i < dsload.Tables[0].Rows.Count; i++) { DropDownList ddlledger1 = GridView1.Rows[i].FindControl("grid1ddl_ledger") as DropDownList; ddlledger1.Items.Add("Select"); if (i == 0) { ddlledger.Items.Add("Select"); } if (chkl_studled.Items.Count > 0) { for (int fine = 0; fine < chkl_studled.Items.Count; fine++) { ddlledger1.Items.Add(Convert.ToString(chkl_studled.Items[fine].Text)); if (i == 0) { ddlledger.Items.Add(Convert.ToString(chkl_studled.Items[fine].Text)); } } } } } else { GridView1.DataSource = null; GridView1.DataBind(); GridView1.Visible = false; //btnSave.Visible = false; lbl_errormsg.Visible = true; Grid1.Visible = false; lbl_errormsg.Text = "No Records Found"; } #endregion //imgdiv2.Visible = false; //lbl_alert.Text = ""; //lblvalidation1.Text = ""; //for (int i = 1; i < FpSpread1.Sheets[0].Rows.Count; i++) //{ // string rollno = Convert.ToString(FpSpread1.Sheets[0].Cells[i, 2].Text); // string semester = Convert.ToString(FpSpread1.Sheets[0].Cells[i, 5].Text); // string feecat = DA.GetFunction(" select textcode from TextValTable where textval='" + semester + "'"); // if (rollno != "") // { // string confirmFine = " select distinct roll_no from FT_FineCancelSetting where roll_no='" + rollno.Trim() + "' and feecategory in('" + feecat + "')"; // DataSet dsFine = new DataSet(); // dsFine = DA.select_method_wo_parameter(confirmFine, "Text"); // if (dsFine.Tables.Count > 0 && dsFine.Tables[0].Rows.Count > 0) // { // for (int k = 0; k < FpSpread1.Columns.Count; k++) // { // FpSpread1.Sheets[0].Cells[i, k].BackColor = Color.LightGreen; // } // } // else // { // string DelChk = " select distinct roll_no from FT_FineCancelSetting where roll_no='" + rollno.Trim() + "' and feecategory in('" + feecat + "')"; // DataSet dsDel = new DataSet(); // dsDel = DA.select_method_wo_parameter(DelChk, "Text"); // if (dsDel.Tables.Count > 0 && dsDel.Tables[0].Rows.Count > 0) // { // for (int k = 0; k < FpSpread1.Columns.Count; k++) // { // FpSpread1.Sheets[0].Cells[i, k].BackColor = ColorTranslator.FromHtml("#FF3333"); // } // } // } // } //} } catch { } } }
protected void btnGo_Click(object sender, EventArgs e) { try { string College = string.Empty; string staffcategory = string.Empty; string stafftype = string.Empty; string Month = string.Empty; string year = string.Empty; College = rs.GetSelectedItemsValue(cbl_college); staffcategory = rs.GetSelectedItemsText(cbl_Category); stafftype = rs.GetSelectedItemsText(cbl_stafftype); Month = rs.GetSelectedItemsValue(cbl_Month); List <string> Lis = new List <string>(); Lis = rs.GetSelectedItemsValueList(cbl_Month); year = ddlyear.SelectedItem.Text; int StafType = 0; int Category = 0; int MonthNum = 0; DataTable DtMain = new DataTable(); DataTable DFSC = new DataTable(); DataTable DFST = new DataTable(); DataView Dv = new DataView(); Hashtable has = new Hashtable(); Hashtable RowIndex = new Hashtable(); bool Check = false; if (College.Trim() != "" && staffcategory.Trim() != "" && stafftype.Trim() != "" && Month.Trim() != "") { //string Query = "select count(s.staff_code) as Total,sc.category_Name,S.college_code,t.stftype,resign,settled,join_date,relieve_date from staffmaster s,stafftrans t,staffcategorizer sc where s.staff_code=t.staff_code and t.category_code=sc.category_code and s.college_code=sc.college_Code and Latestrec='1' and s.college_code in (" + College + ") and sc.category_name in ('" + staffcategory + "') and t.stftype in ('" + stafftype + "') group by sc.category_Name,S.college_code,t.stftype,resign,settled,join_date,relieve_date order by s.College_code"; string Query = "select count(s.staff_code) as Total,sc.category_Name,S.college_code,t.stftype,resign,settled,join_date,relieve_date from staff_appl_master sa,staffmaster s,stafftrans t,hrdept_master h,desig_master d,staffcategorizer sc where s.staff_code =t.staff_code and s.appl_no =sa.appl_no and h.dept_code =t.dept_code and t.desig_code =d.desig_code and sc.category_code =t.category_code and s.college_code=h.college_code and s.college_code=d.collegeCode and s.college_code=sc.college_code and Latestrec='1' and s.college_code in (" + College + ") and sc.category_name in ('" + staffcategory + "') and t.stftype in ('" + stafftype + "') group by sc.category_Name,S.college_code,t.stftype,resign,settled,join_date,relieve_date order by s.College_code"; Query += " select payMonthNum,PayYear,To_Date,College_code from Hrpaymonths"; Query += " select collname,coll_acronymn,acr,college_code from collinfo"; ds.Clear(); ds = d2.select_method_wo_parameter(Query, "Text"); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { DtMain = ds.Tables[0].DefaultView.ToTable(); DFSC = ds.Tables[0].DefaultView.ToTable(true, "category_Name"); DFST = ds.Tables[0].DefaultView.ToTable(true, "stftype"); Farpont1.Sheets[0].ColumnHeader.RowCount = 3; Farpont1.Sheets[0].ColumnCount = 2; Farpont1.Sheets[0].RowHeader.Visible = false; Farpont1.Sheets[0].AutoPostBack = true; Farpont1.Sheets[0].RowCount = 0; Farpont1.CommandBar.Visible = false; FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo(); darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA"); darkstyle.ForeColor = Color.White; Farpont1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle; Farpont1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No"; Farpont1.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center; Farpont1.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 3, 1); Farpont1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "College Name"; Farpont1.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center; Farpont1.Sheets[0].ColumnHeaderSpanModel.Add(0, 1, 3, 1); if (DFST.Rows.Count > 0) { for (int intST = 0; intST < DFST.Rows.Count; intST++) { ds.Tables[0].DefaultView.RowFilter = "stftype='" + Convert.ToString(DFST.Rows[intST]["stftype"]) + "'"; Dv = ds.Tables[0].DefaultView; if (Dv.Count > 0) { Farpont1.Sheets[0].ColumnCount++; Farpont1.Sheets[0].ColumnHeader.Cells[0, Farpont1.Sheets[0].ColumnCount - 1].Text = Convert.ToString(DFST.Rows[intST]["stftype"]); Farpont1.Sheets[0].ColumnHeader.Cells[0, Farpont1.Sheets[0].ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center; StafType = Farpont1.Sheets[0].ColumnCount - 1; DFSC = Dv.ToTable(true, "category_Name"); if (DFSC.Rows.Count > 0) { for (int intSC = 0; intSC < DFSC.Rows.Count; intSC++) { if (intSC != 0) { Farpont1.Sheets[0].ColumnCount++; } Category = Farpont1.Sheets[0].ColumnCount - 1; Farpont1.Sheets[0].ColumnHeader.Cells[1, Farpont1.Sheets[0].ColumnCount - 1].Text = Convert.ToString(DFSC.Rows[intSC]["category_Name"]); Farpont1.Sheets[0].ColumnHeader.Cells[1, Farpont1.Sheets[0].ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center; if (cbl_Month.Items.Count > 0) { Check = false; for (int intMont = 0; intMont < cbl_Month.Items.Count; intMont++) { if (cbl_Month.Items[intMont].Selected == true) { if (Check == true) { Farpont1.Sheets[0].ColumnCount++; } else { MonthNum = Farpont1.Sheets[0].ColumnCount - 1; } Farpont1.Sheets[0].ColumnHeader.Cells[2, Farpont1.Sheets[0].ColumnCount - 1].Text = Convert.ToString(cbl_Month.Items[intMont].Text + " " + year); Farpont1.Sheets[0].ColumnHeader.Cells[2, Farpont1.Sheets[0].ColumnCount - 1].Tag = Convert.ToString(cbl_Month.Items[intMont].Value + " $" + year); Farpont1.Sheets[0].ColumnHeader.Cells[2, Farpont1.Sheets[0].ColumnCount - 1].Note = Convert.ToString(DFSC.Rows[intSC]["category_Name"] + "$" + DFST.Rows[intST]["stftype"]); Farpont1.Sheets[0].ColumnHeader.Cells[2, Farpont1.Sheets[0].ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center; Check = true; } } } Farpont1.Sheets[0].ColumnHeaderSpanModel.Add(1, (Category), 1, ((Farpont1.Sheets[0].ColumnCount) - Category)); } } Farpont1.Sheets[0].ColumnHeaderSpanModel.Add(0, (StafType), 1, ((Farpont1.Sheets[0].ColumnCount) - StafType)); } } } int Colcount = Farpont1.Sheets[0].ColumnCount; Farpont1.Sheets[0].ColumnCount++; Farpont1.Sheets[0].ColumnHeader.Cells[0, Farpont1.Sheets[0].ColumnCount - 1].Text = "Grand Total"; Farpont1.Sheets[0].ColumnHeader.Cells[0, Farpont1.Sheets[0].ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center; int NumberCount = 0; if (cbl_Month.Items.Count > 0) { Check = false; for (int intMont = 0; intMont < cbl_Month.Items.Count; intMont++) { if (cbl_Month.Items[intMont].Selected == true) { NumberCount++; if (Check == true) { Farpont1.Sheets[0].ColumnCount++; } else { MonthNum = Farpont1.Sheets[0].ColumnCount - 1; } Farpont1.Sheets[0].ColumnHeader.Cells[2, Farpont1.Sheets[0].ColumnCount - 1].Text = Convert.ToString(cbl_Month.Items[intMont].Text + " " + year); Farpont1.Sheets[0].ColumnHeader.Cells[2, Farpont1.Sheets[0].ColumnCount - 1].Tag = Convert.ToString(cbl_Month.Items[intMont].Value + "$" + year); Farpont1.Sheets[0].ColumnHeader.Cells[2, Farpont1.Sheets[0].ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center; has.Add(Convert.ToString(cbl_Month.Items[intMont].Value + "$" + year), Farpont1.Sheets[0].ColumnCount - 1); Check = true; } } } Farpont1.Sheets[0].ColumnHeaderSpanModel.Add(0, Colcount, 2, (Farpont1.Sheets[0].ColumnCount - Colcount)); DFSC = DtMain.DefaultView.ToTable(true, "College_code"); if (DFSC.Rows.Count > 0) { for (int intD = 0; intD < DFSC.Rows.Count; intD++) { //has.Clear(); Farpont1.Sheets[0].Rows.Count++; Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString((intD + 1)); Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center; ds.Tables[2].DefaultView.RowFilter = "College_code='" + Convert.ToString(DFSC.Rows[intD]["College_code"]) + "'"; DataView Dvcol = ds.Tables[2].DefaultView; Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(Dvcol[0]["coll_acronymn"]); Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center; for (int intFc = 2; intFc < Farpont1.Sheets[0].ColumnCount - NumberCount; intFc++) { string Mon = Farpont1.Sheets[0].ColumnHeader.Cells[2, intFc].Tag.ToString().Split('$')[0]; string Ye = Farpont1.Sheets[0].ColumnHeader.Cells[2, intFc].Tag.ToString().Split('$')[1]; string Cat = Farpont1.Sheets[0].ColumnHeader.Cells[2, intFc].Note.ToString().Split('$')[0]; string Stf = Farpont1.Sheets[0].ColumnHeader.Cells[2, intFc].Note.ToString().Split('$')[1]; string PayMonthDate = string.Empty; ds.Tables[1].DefaultView.RowFilter = "payMonthNum='" + Mon + "' and PayYear ='" + Ye + "' and College_code='" + Convert.ToString(DFSC.Rows[intD]["College_code"]) + "'"; DataView dvdate = ds.Tables[1].DefaultView; if (dvdate.Count > 0) { PayMonthDate = Convert.ToString(dvdate[0]["To_Date"]); } if (PayMonthDate.Trim() != "") { ds.Tables[0].DefaultView.RowFilter = " join_date <='" + PayMonthDate + "' and category_Name='" + Cat + "' and stftype ='" + Stf + "' and college_code ='" + Convert.ToString(DFSC.Rows[intD]["College_code"]) + "'"; DataView dvstrength = ds.Tables[0].DefaultView; DtMain.DefaultView.RowFilter = " relieve_date <='" + PayMonthDate + "' and category_Name='" + Cat + "' and stftype ='" + Stf + "' and college_code ='" + Convert.ToString(DFSC.Rows[intD]["College_code"]) + "'"; DataView dvRelived = DtMain.DefaultView; if (dvstrength.Count > 0) { DataTable DtST = dvstrength.ToTable(); DataTable DtRt = dvRelived.ToTable(); if (DtST.Rows.Count > 0) { int Total = 0; Total = Convert.ToInt32(DtST.Compute("sum (Total)", "")); int SubTotal = 0; if (DtRt.Rows.Count > 0) { SubTotal = Convert.ToInt32(DtRt.Compute("sum (Total)", "")); } Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, intFc].Text = Convert.ToString(Total - SubTotal); Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, intFc].HorizontalAlign = HorizontalAlign.Center; if (has.ContainsKey(Convert.ToString(Mon.Trim() + "$" + Ye.Trim()))) { int Index = Convert.ToInt32(has[Convert.ToString(Mon.Trim() + "$" + Ye.Trim())]); int GrandTotal = 0; int.TryParse(Convert.ToString(Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, Index].Text), out GrandTotal); GrandTotal += Convert.ToInt32(Total - SubTotal); Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, Index].Text = Convert.ToString(GrandTotal); Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, Index].HorizontalAlign = HorizontalAlign.Center; if (!RowIndex.ContainsKey(Index)) { RowIndex.Add(Index, Convert.ToString(Total - SubTotal)); } else { int OverAllTotal = Convert.ToInt32(RowIndex[Index]); RowIndex.Remove(Index); RowIndex.Add(Index, ((Total - SubTotal) + OverAllTotal)); } } if (!RowIndex.ContainsKey(intFc)) { RowIndex.Add(intFc, Convert.ToString(Total - SubTotal)); } else { int OverAllTotal = Convert.ToInt32(RowIndex[intFc]); OverAllTotal += Convert.ToInt32(Total - SubTotal); RowIndex.Remove(intFc); RowIndex.Add(intFc, OverAllTotal); } } } else { Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, intFc].Text = Convert.ToString("-"); Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, intFc].HorizontalAlign = HorizontalAlign.Center; } } else { Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, intFc].Text = Convert.ToString("-"); Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, intFc].HorizontalAlign = HorizontalAlign.Center; } } } if (RowIndex.Count > 0) { Farpont1.Sheets[0].Rows.Count++; Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString("Total"); Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center; for (int intFc = 2; intFc < Farpont1.Sheets[0].ColumnCount; intFc++) { if (RowIndex.ContainsKey(intFc)) { Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, intFc].Text = Convert.ToString(RowIndex[intFc]); Farpont1.Sheets[0].Cells[Farpont1.Sheets[0].RowCount - 1, intFc].HorizontalAlign = HorizontalAlign.Center; } } } Farpont1.Visible = true; print.Visible = true; } else { Farpont1.Visible = false; print.Visible = false; ScriptManager.RegisterClientScriptBlock(this, typeof(Page), UniqueID, "alert('No Record Found!')", true); } } else { Farpont1.Visible = false; print.Visible = false; ScriptManager.RegisterClientScriptBlock(this, typeof(Page), UniqueID, "alert('No Record Found!')", true); } } else { Farpont1.Visible = false; print.Visible = false; ScriptManager.RegisterClientScriptBlock(this, typeof(Page), UniqueID, "alert('Please Select All Values!')", true); } } catch { } }