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