protected void btnSelect_Click(object sender, EventArgs e)
{
Fpspread1.SaveChanges();
bool check = false;
if (Fpspread1.Sheets[0].RowCount > 0)
{
for (int row = 0; row < Fpspread1.Sheets[0].RowCount; row++)
{
int val = Convert.ToInt32(Fpspread1.Sheets[0].Cells[row, 4].Value);
if (val == 1)
{
string actid = Convert.ToString(Fpspread1.Sheets[0].Cells[row, 1].Tag);
string insertquery = "if exists (select Linkvalue from InsSettings where LinkName = 'Current Financial Year' and college_code ='" + collegestat0 + "' and FinuserCode='" + usercode + "') update InsSettings set LinkValue ='" + actid.ToString() + "' where LinkName ='Current Financial Year' and college_code ='" + collegestat0 + "' and FinuserCode='" + usercode + "' else insert into InsSettings (LinkName,LinkValue,college_code, FinuserCode) values('Current Financial Year','" + actid.ToString() + "','" + collegestat0 + "','" + usercode + "')";
int inscount = d2.update_method_wo_parameter(insertquery, "Text");
if (inscount > 0)
{
check = true;
}
}
}
if (check == true)
{
lbl_alerterr.Text = "Financial Year Is Selected Successfully";
imgdiv2.Visible = true;
bttngo_Click(sender, e);
}
else
{
imgdiv2.Visible = true;
lbl_alerterr.Text = "Please Select Any One Year";
}
}
}
public void btnsave_Click(object sender, EventArgs e)
{
string collcode = Convert.ToString(ddlclg.SelectedValue);
try
{
string sqldeptcode = "";
int totalpersons = 0;
int save = 0;
Fpspread1.SaveChanges();
for (int i = 0; i < Fpspread1.Sheets[0].Rows.Count; i++)
{
if (Fpspread1.Sheets[0].Cells[i, 0].Text.Trim() != "")
{
string ReqStaff = Convert.ToString(Fpspread1.Sheets[0].Cells[i, 5].Text);
if (ReqStaff.Trim() != "")
{
string sqldesigncode = Convert.ToString(Fpspread1.Sheets[0].Cells[i, 1].Tag);
sqldeptcode = Convert.ToString(Fpspread1.Sheets[0].Cells[i, 1].Note);
totalpersons = Convert.ToInt32(ReqStaff);
string noofpersons = d2.GetFunction("select No_ofPersons from VacancyMaster where DeptCode='" + sqldeptcode + "' and DesigCode='" + sqldesigncode + "' and Collegecode='" + collcode + "'");
int persons = 0;
if (Int32.TryParse(noofpersons, out persons))
{
persons = Convert.ToInt32(noofpersons);
}
else
{
persons = 0;
}
totalpersons = totalpersons + persons;
string insertempprsql = "if exists (select * from VacancyMaster where DeptCode='" + sqldeptcode + "' and DesigCode='" + sqldesigncode + "' and Collegecode='" + collcode + "') update VacancyMaster set No_ofPersons='" + totalpersons + "' where DeptCode='" + sqldeptcode + "' and DesigCode='" + sqldesigncode + "' and Collegecode='" + collcode + "' else INSERT INTO VacancyMaster (DeptCode,DesigCode,No_ofPersons,Collegecode) VALUES('" + sqldeptcode + "','" + sqldesigncode + "','" + totalpersons + "','" + collcode + "')";
d2.insert_method(insertempprsql, ht, "Text");
save++;
}
else
{
Fpspread1.SaveChanges();
lbl_alert.Text = "Please Add Required Staff";
lbl_alert.Visible = true;
imgdiv2.Visible = true;
}
}
}
if (save > 0)
{
lbl_alert.Text = "Saved Successfully";
lbl_alert.Visible = true;
imgdiv2.Visible = true;
}
btn_go_Click(sender, e);
}
catch (Exception ex)
{
d2.sendErrorMail(ex, collcode, "manpower_Alter.aspx");
}
}
protected void btnmod_Click(object sender, EventArgs e)
{
try
{
Fpspread1.SaveChanges();
bool modcheck = false;
if (Fpspread1.Sheets[0].RowCount > 0)
{
for (int row = 0; row < Fpspread1.Sheets[0].RowCount; row++)
{
int val = Convert.ToInt32(Fpspread1.Sheets[0].Cells[row, 4].Value);
if (val == 1)
{
bindcollege();
// bindloadcol();
popper1.Visible = true;
btnsave.Visible = false;
btnupdate.Visible = true;
btndelete.Visible = true;
lbldateerr.Visible = false;
string curractid = Convert.ToString(Fpspread1.Sheets[0].Cells[row, 1].Tag);
string colcode = ddlcolload.SelectedValue;
string selectload = "select FinYearAcr,FinYearName,(Convert(Varchar(10),FinYearStart,103)) as finyearstart,(Convert(varchar(10),FinYearEnd,103)) as finyearend from FM_FinYearMaster where FinYearPK='" + curractid + "' and CollegeCode='" + collegestat0 + "'";
ds.Clear();
ds = d2.select_method_wo_parameter(selectload, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
modcheck = true;
if (ddlcol.Items.Count > 0)
{
ddlcol.SelectedValue = colcode;
txtacr.Text = ds.Tables[0].Rows[0]["FinYearAcr"].ToString();
txtacc.Text = ds.Tables[0].Rows[0]["FinYearName"].ToString();
txtdatestart.Text = ds.Tables[0].Rows[0]["finyearstart"].ToString();
txtdatestart.Enabled = false;
txtdateend.Text = ds.Tables[0].Rows[0]["finyearend"].ToString();
txtdateend.Enabled = false;
}
}
}
}
if (modcheck == true)
{
}
else
{
imgdiv2.Visible = true;
lbl_alerterr.Text = "Please Select Any One Year";
}
}
}
catch
{
}
}
private bool CheckSprVal(ref string ErrText)
{
bool CheckFlag = true;
try
{
string ClgCode = Convert.ToString(ddlcollege.SelectedValue);
string Dept_Code = string.Empty;
string Desig_Code = string.Empty;
string DesigName = string.Empty;
string DeptName = string.Empty;
double tothrs = 0;
double Amnthrs = 0;
Fpspread1.SaveChanges();
for (int mySpr = 1; mySpr < Fpspread1.Sheets[0].RowCount; mySpr++)
{
DesigName = string.Empty;
DeptName = string.Empty;
Dept_Code = string.Empty;
Desig_Code = string.Empty;
tothrs = 0;
Amnthrs = 0;
byte Check = Convert.ToByte(Fpspread1.Sheets[0].Cells[mySpr, 1].Value);
if (Check == 1)
{
DesigName = Convert.ToString(Fpspread1.Sheets[0].Cells[mySpr, 3].Text);
Dept_Code = Convert.ToString(Fpspread1.Sheets[0].Cells[mySpr, 3].Note);
DeptName = d2.GetFunction("select Dept_Name from hrdept_master where Dept_Code='" + Dept_Code + "' and college_code='" + ClgCode + "'");
Desig_Code = Convert.ToString(Fpspread1.Sheets[0].Cells[mySpr, 3].Tag);
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[mySpr, 4].Text), out tothrs);
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[mySpr, 5].Text), out Amnthrs);
if (!String.IsNullOrEmpty(Dept_Code.Trim()) && !String.IsNullOrEmpty(Desig_Code.Trim()))
{
if (tothrs == 0)
{
ErrText = "Please Enter No.of Hours for '" + DesigName + "' Department of '" + DeptName + "'!";
CheckFlag = false;
return(CheckFlag);
}
if (Amnthrs == 0)
{
ErrText = "Please Enter Amount/Hours for '" + DesigName + "' Department of '" + DeptName + "'!";
CheckFlag = false;
return(CheckFlag);
}
}
}
}
}
catch { }
return(CheckFlag);
}
protected void btnyes_Click(object sender, EventArgs e)
{
try
{
Fpspread1.SaveChanges();
if (Fpspread1.Sheets[0].RowCount > 0)
{
for (int row = 0; row < Fpspread1.Sheets[0].RowCount; row++)
{
int val = Convert.ToInt32(Fpspread1.Sheets[0].Cells[row, 4].Value);
if (val == 1)
{
string genaccid = Convert.ToString(Fpspread1.Sheets[0].Cells[Convert.ToInt32(row), 1].Tag);
string collcode = ddlcol.SelectedValue;
ddlcol.SelectedValue = collcode;
string select = d2.GetFunction("select distinct FinYearFK from FT_FeeAllot where FinYearFK ='" + genaccid + "'");
if (select.Trim() == "0")
{
string delquery = "DELETE from FM_FinYearMaster WHERE FinYearPK = '" + genaccid + "' AND CollegeCode = '" + collegestat + "'";
int count = d2.update_method_wo_parameter(delquery, "Text");
if (count != 0)
{
bindaccname();
bttngo_Click(sender, e);
popper1.Visible = false;
imgdiv2.Visible = true;
imgdiv1.Visible = false;
lblalert.Visible = false;
lbl_alerterr.Text = "Deleted Successfully";
}
else
{
imgdiv2.Visible = true;
imgdiv1.Visible = false;
lblalert.Visible = false;
lbl_alerterr.Text = "Financial year is in Use,Can not Delete!";
}
}
else
{
imgdiv2.Visible = true;
imgdiv1.Visible = false;
lblalert.Visible = false;
lbl_alerterr.Text = "Financial year is in Use,Can not Delete!";
}
}
}
}
}
catch
{
}
}
private bool CheckSpr()
{
bool EntryFlag = false;
try
{
Fpspread1.SaveChanges();
for (int mySpr = 1; mySpr < Fpspread1.Sheets[0].RowCount; mySpr++)
{
byte Check = Convert.ToByte(Fpspread1.Sheets[0].Cells[mySpr, 1].Value);
if (Check == 1)
{
EntryFlag = true;
}
}
}
catch { }
return(EntryFlag);
}
private bool CheckSpr(ref string Myerr)
{
bool ErrFlag = true;
double TotHrs = 0;
double WrkHrs = 0;
try
{
Fpspread1.SaveChanges();
for (int sp = 1; sp < Fpspread1.Sheets[0].Rows.Count; sp++)
{
TotHrs = 0;
byte Check = Convert.ToByte(Fpspread1.Sheets[0].Cells[sp, 1].Value);
if (Check == 1)
{
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[sp, 8].Text), out TotHrs);
for (int mycol = 9; mycol <= Fpspread1.Sheets[0].ColumnCount - 1; mycol++)
{
WrkHrs = 0;
if (String.IsNullOrEmpty(Convert.ToString(Fpspread1.Sheets[0].Cells[sp, mycol].Text)))
{
Myerr = "Please Enter the Working Hours for '" + Convert.ToString(Fpspread1.Sheets[0].Cells[sp, 3].Text) + "' on (" + Convert.ToString(Fpspread1.Sheets[0].ColumnHeader.Cells[1, mycol].Text) + ")!";
ErrFlag = false;
return(ErrFlag);
}
else
{
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[sp, mycol].Text), out WrkHrs);
if (WrkHrs > TotHrs)
{
Myerr = "Working Hours should be less than or Equal to Total Hours for '" + Convert.ToString(Fpspread1.Sheets[0].Cells[sp, 3].Text) + "' on (" + Convert.ToString(Fpspread1.Sheets[0].ColumnHeader.Cells[1, mycol].Text) + ")!";
ErrFlag = false;
return(ErrFlag);
}
}
}
}
}
}
catch { }
return(ErrFlag);
}
private bool CheckSelSpr()
{
bool EntryFlag = false;
try
{
Fpspread1.SaveChanges();
for (int ch = 1; ch < Fpspread1.Sheets[0].Rows.Count; ch++)
{
byte Check = Convert.ToByte(Fpspread1.Sheets[0].Cells[ch, 1].Value);
if (Check == 1)
{
EntryFlag = true;
return(EntryFlag);
}
}
}
catch { }
return(EntryFlag);
}
protected void Fpspread1_Command(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
{
try
{
Fpspread1.SaveChanges();
string activerow = "";
string activecol = "";
activerow = Fpspread1.ActiveSheetView.ActiveRow.ToString();
activecol = Fpspread1.ActiveSheetView.ActiveColumn.ToString();
countnew = 0;
if (activecol == "4")
{
for (int i = 0; i < Fpspread1.Sheets[0].Rows.Count; i++)
{
if (Convert.ToInt32(Fpspread1.Sheets[0].Cells[Convert.ToInt32(i), Convert.ToInt32(activecol)].Value) == 1)
{
countnew++;
}
else
{
//countnew--;
for (int k = 0; k < Fpspread1.Columns.Count; k++)
{
Fpspread1.Sheets[0].Cells[Convert.ToInt32(i), k].BackColor = ColorTranslator.FromHtml("White");
}
}
}
}
if (countnew > 1)
{
countnew--;
Fpspread1.Sheets[0].Cells[Convert.ToInt32(activerow), Convert.ToInt32(activecol)].Value = 0;
imgdiv2.Visible = true;
lbl_alerterr.Text = "Only One Finance Year Is Allowed";
}
}
catch
{
}
}
protected void Fpspread1_Command(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
{
try
{
Fpspread1.SaveChanges();
string activerow = "";
string activecol = "";
activerow = Fpspread1.ActiveSheetView.ActiveRow.ToString();
activecol = Fpspread1.ActiveSheetView.ActiveColumn.ToString();
int countnew = 0;
if (activecol == "7")
{
for (int i = 0; i < Fpspread1.Sheets[0].Rows.Count; i++)
{
if (Convert.ToInt32(Fpspread1.Sheets[0].Cells[Convert.ToInt32(i), Convert.ToInt32(activecol)].Value) == 1)
{
countnew++;
}
else
{
for (int k = 0; k < Fpspread1.Columns.Count; k++)
{
Fpspread1.Sheets[0].Cells[Convert.ToInt32(i), k].BackColor = ColorTranslator.FromHtml("White");
}
}
}
}
if (countnew > 1)
{
countnew--;
Fpspread1.Sheets[0].Cells[Convert.ToInt32(activerow), Convert.ToInt32(activecol)].Value = 0;
ScriptManager.RegisterStartupScript(this, typeof(Page), UniqueID, "alert('Only One User Should Be Selected')", true);
}
}
catch
{
}
}
protected void Fpspread1_command(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
{
try
{
Fpspread1.SaveChanges();
byte Check = Convert.ToByte(Fpspread1.Sheets[0].Cells[0, 1].Value);
if (Check == 1)
{
for (int s = 0; s < Fpspread1.Sheets[0].Rows.Count; s++)
{
Fpspread1.Sheets[0].Cells[s, 1].Value = 1;
}
}
else
{
for (int s = 0; s < Fpspread1.Sheets[0].Rows.Count; s++)
{
Fpspread1.Sheets[0].Cells[s, 1].Value = 0;
}
}
}
catch { }
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["collegecode"] == null)
{
Response.Redirect("~/Default.aspx");
}
usercode = Session["usercode"].ToString();
collegecode1 = Session["collegecode"].ToString();
clgcode = collegecode1;
singleuser = Session["single_user"].ToString();
group_user = Session["group_code"].ToString();
if (!IsPostBack)
{
bindcollege();
//fairpoint();
binddepartment();
binddesignation();
Fpspread1.Visible = false;
rptprint.Visible = false;
btn_go_Click(sender, e);
}
lbl_norec.Visible = false;
Fpspread1.SaveChanges();
}
private void loadSpreadValues(DataSet dsVal, string dateEnding)
{
try
{
#region Design
Fpspread1.Sheets[0].RowCount = 0;
Fpspread1.Sheets[0].ColumnCount = 0;
Fpspread1.CommandBar.Visible = false;
Fpspread1.Sheets[0].AutoPostBack = true;
Fpspread1.Sheets[0].ColumnHeader.RowCount = 1;
Fpspread1.Sheets[0].RowHeader.Visible = false;
Fpspread1.Sheets[0].ColumnCount = 0;
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.Black;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
ArrayList arColumn = getColumn();
foreach (string columN in arColumn)
{
Fpspread1.Sheets[0].ColumnCount++;
int colCnt = Fpspread1.Sheets[0].ColumnCount - 1;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, colCnt].Text = columN;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, colCnt].ForeColor = ColorTranslator.FromHtml("#000000");
Fpspread1.Sheets[0].ColumnHeader.Cells[0, colCnt].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, colCnt].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, colCnt].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, colCnt].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Columns[colCnt].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Columns[colCnt].Width = 40;
switch (columN)
{
case "Year Ending(₹)":
Fpspread1.Sheets[0].ColumnHeader.Cells[0, colCnt].Text = columN + "-" + dateEnding;
Fpspread1.Sheets[0].Columns[colCnt].Width = 150;
Fpspread1.Sheets[0].Columns[colCnt].HorizontalAlign = HorizontalAlign.Right;
break;
case "Particulars":
Fpspread1.Sheets[0].Columns[colCnt].Width = 200;
Fpspread1.Sheets[0].Columns[colCnt].HorizontalAlign = HorizontalAlign.Left;
break;
case "Debit(₹)":
Fpspread1.Sheets[0].Columns[colCnt].Width = 80;
Fpspread1.Sheets[0].Columns[colCnt].HorizontalAlign = HorizontalAlign.Right;
break;
case "Credit(₹)":
Fpspread1.Sheets[0].Columns[colCnt].HorizontalAlign = HorizontalAlign.Right;
Fpspread1.Sheets[0].Columns[colCnt].Width = 80;
break;
}
}
#endregion
#region Value
Hashtable htSubTot = new Hashtable();
int rowNum = 0;
Dictionary <string, string> fnalYear = getFinancialYear();
Dictionary <string, string> hdName = getHeadername();
Dictionary <string, string> feesName = getFeeName();
int rowCnt = 0;
//foreach (KeyValuePair<string, string> financialYear in fnalYear)
//{
// int rowCnt = 0;
// Fpspread1.Sheets[0].RowCount++;
// rowCnt = Fpspread1.Sheets[0].RowCount - 1;
// Fpspread1.Sheets[0].Cells[rowCnt, 1].Text = Convert.ToString(financialYear.Value);
// Fpspread1.Sheets[0].SpanModel.Add(rowCnt, 0, 1, Fpspread1.Sheets[0].ColumnCount - 1);
// Fpspread1.Sheets[0].Rows[rowCnt].BackColor = Color.Green;
bool boolCheck = false;
if (rblType.SelectedIndex == 0)
{
#region header
foreach (KeyValuePair <string, string> headName in feesName)
{
//for (int hd = 0; hd < chkl_studhed.Items.Count; hd++)
//{
// if (!chkl_studhed.Items[hd].Selected)
// continue;
Fpspread1.Sheets[0].RowCount++;
rowCnt = Fpspread1.Sheets[0].RowCount - 1;
Fpspread1.Sheets[0].Cells[rowCnt, 0].Text = Convert.ToString(++rowNum);
double ob = 0;
double openBalhead = 0;
if (dsVal.Tables[3].Rows.Count > 0 || dsVal.Tables[6].Rows.Count > 0)
{
dsVal.Tables[3].DefaultView.RowFilter = " headerfk='" + headName.Key + "' ";//and finyearfk='" + financialYear.Key + "'
dsVal.Tables[6].DefaultView.RowFilter = "headerpk='" + headName.Key + "' ";
DataTable dtOpen = dsVal.Tables[3].DefaultView.ToTable();
DataTable dtopenhead = dsVal.Tables[6].DefaultView.ToTable();
if (dtOpen.Rows.Count > 0 || dtopenhead.Rows.Count > 0)
{
double.TryParse(Convert.ToString(dtOpen.Compute("sum(credit)", "")), out ob);
}
double.TryParse(Convert.ToString(dtopenhead.Compute("sum(openbal)", "")), out openBalhead);
}
double openBal = 0;
openBal = ob + openBalhead;
Fpspread1.Sheets[0].Cells[rowCnt, 1].Text = Convert.ToString(openBal);
if (openBal == 0)
{
Fpspread1.Sheets[0].Cells[rowCnt, 1].Text = "-";
}
if (!htSubTot.ContainsKey("OpenBal"))
{
htSubTot.Add("OpenBal", openBal);
}
else
{
double amount = 0;
double.TryParse(Convert.ToString(htSubTot["OpenBal"]), out amount);
amount += openBal;
htSubTot.Remove("OpenBal");
htSubTot.Add("OpenBal", Convert.ToString(amount));
}
Fpspread1.Sheets[0].Cells[rowCnt, 2].Text = Convert.ToString(headName.Value);
double debitAmt = 0;
if (dsVal.Tables[0].Rows.Count > 0)
{
dsVal.Tables[0].DefaultView.RowFilter = " headerfk='" + headName.Key + "' ";//and finyearfk='" + financialYear.Key + "'
DataTable dtDebit = dsVal.Tables[0].DefaultView.ToTable();
if (dtDebit.Rows.Count > 0)
{
double.TryParse(Convert.ToString(dtDebit.Compute("sum(debit)", "")), out debitAmt);
}
}
Fpspread1.Sheets[0].Cells[rowCnt, 3].Text = Convert.ToString(debitAmt);
if (debitAmt == 0)
{
Fpspread1.Sheets[0].Cells[rowCnt, 3].Text = "-";
}
if (!htSubTot.ContainsKey("Debit"))
{
htSubTot.Add("Debit", debitAmt);
}
else
{
double amount = 0;
double.TryParse(Convert.ToString(htSubTot["Debit"]), out amount);
amount += debitAmt;
htSubTot.Remove("Debit");
htSubTot.Add("Debit", Convert.ToString(amount));
}
double creditAmt = 0;
if (dsVal.Tables[0].Rows.Count > 0)
{
dsVal.Tables[0].DefaultView.RowFilter = " headerfk='" + headName.Key + "' ";//and finyearfk='" + financialYear.Key + "'
DataTable dtCredit = dsVal.Tables[0].DefaultView.ToTable();
if (dtCredit.Rows.Count > 0)
{
double.TryParse(Convert.ToString(dtCredit.Compute("sum(credit)", "")), out creditAmt);
}
}
Fpspread1.Sheets[0].Cells[rowCnt, 4].Text = Convert.ToString(creditAmt);
if (creditAmt == 0)
{
Fpspread1.Sheets[0].Cells[rowCnt, 4].Text = "-";
}
if (!htSubTot.ContainsKey("Credit"))
{
htSubTot.Add("Credit", creditAmt);
}
else
{
double amount = 0;
double.TryParse(Convert.ToString(htSubTot["Credit"]), out amount);
amount += creditAmt;
htSubTot.Remove("Credit");
htSubTot.Add("Credit", Convert.ToString(amount));
}
boolCheck = true;
}
#endregion
}
else
{
#region ledger
string lastName = string.Empty;
foreach (KeyValuePair <string, string> ldName in feesName)
{
// }
//for (int hd = 0; hd < chkl_studled.Items.Count; hd++)
//{
// if (!chkl_studled.Items[hd].Selected)
// continue;
if (hdName.ContainsKey(ldName.Key))
{
string NewName = Convert.ToString(hdName[ldName.Key]);
if (lastName != NewName)
{
Fpspread1.Sheets[0].RowCount++;
rowCnt = Fpspread1.Sheets[0].RowCount - 1;
lastName = NewName;
Fpspread1.Sheets[0].Cells[rowCnt, 0].Text = lastName;
Fpspread1.Sheets[0].SpanModel.Add(rowCnt, 0, 1, Fpspread1.Sheets[0].ColumnCount - 1);
Fpspread1.Sheets[0].Rows[rowCnt].BackColor = Color.YellowGreen;
}
}
Fpspread1.Sheets[0].RowCount++;
rowCnt = Fpspread1.Sheets[0].RowCount - 1;
Fpspread1.Sheets[0].Cells[rowCnt, 0].Text = Convert.ToString(++rowNum);
double ob = 0;
double openBalhead = 0;
if (dsVal.Tables[3].Rows.Count > 0)
{
dsVal.Tables[3].DefaultView.RowFilter = " ledgerfk='" + ldName.Key + "' ";
//and finyearfk='" + financialYear.Key + "'
// dsVal.Tables[6].DefaultView.RowFilter = "headerpk='" + headName.Key + "' ";
DataTable dtOpen = dsVal.Tables[3].DefaultView.ToTable();
DataTable dtopenhead = dsVal.Tables[6].DefaultView.ToTable();
if (dtOpen.Rows.Count > 0 || dtopenhead.Rows.Count > 0)
{
double.TryParse(Convert.ToString(dtOpen.Compute("sum(credit)", "")), out ob);
}
double.TryParse(Convert.ToString(dtopenhead.Compute("sum(openbal)", "")), out openBalhead);
}
double openBal = 0;
openBal = ob + openBalhead;
Fpspread1.Sheets[0].Cells[rowCnt, 1].Text = Convert.ToString(openBal);
if (openBal == 0)
{
Fpspread1.Sheets[0].Cells[rowCnt, 1].Text = "-";
}
if (!htSubTot.ContainsKey("OpenBal"))
{
htSubTot.Add("OpenBal", openBal);
}
else
{
double amount = 0;
double.TryParse(Convert.ToString(htSubTot["OpenBal"]), out amount);
amount += openBal;
htSubTot.Remove("OpenBal");
htSubTot.Add("OpenBal", Convert.ToString(amount));
}
Fpspread1.Sheets[0].Cells[rowCnt, 2].Text = Convert.ToString(ldName.Value);
double debitAmt = 0;
if (dsVal.Tables[0].Rows.Count > 0)
{
dsVal.Tables[0].DefaultView.RowFilter = " ledgerfk='" + ldName.Key + "' ";//and finyearfk='" + financialYear.Key + "'
DataTable dtDebit = dsVal.Tables[0].DefaultView.ToTable();
if (dtDebit.Rows.Count > 0)
{
double.TryParse(Convert.ToString(dtDebit.Compute("sum(debit)", "")), out debitAmt);
}
}
Fpspread1.Sheets[0].Cells[rowCnt, 3].Text = Convert.ToString(debitAmt);
if (debitAmt == 0)
{
Fpspread1.Sheets[0].Cells[rowCnt, 3].Text = "-";
}
if (!htSubTot.ContainsKey("Debit"))
{
htSubTot.Add("Debit", debitAmt);
}
else
{
double amount = 0;
double.TryParse(Convert.ToString(htSubTot["Debit"]), out amount);
amount += debitAmt;
htSubTot.Remove("Debit");
htSubTot.Add("Debit", Convert.ToString(amount));
}
double creditAmt = 0;
if (dsVal.Tables[0].Rows.Count > 0)
{
dsVal.Tables[0].DefaultView.RowFilter = " ledgerfk='" + ldName.Key + "' ";//and finyearfk='" + financialYear.Key + "'
DataTable dtCredit = dsVal.Tables[0].DefaultView.ToTable();
if (dtCredit.Rows.Count > 0)
{
double.TryParse(Convert.ToString(dtCredit.Compute("sum(credit)", "")), out creditAmt);
}
}
Fpspread1.Sheets[0].Cells[rowCnt, 4].Text = Convert.ToString(creditAmt);
if (creditAmt == 0)
{
Fpspread1.Sheets[0].Cells[rowCnt, 4].Text = "-";
}
if (!htSubTot.ContainsKey("Credit"))
{
htSubTot.Add("Credit", creditAmt);
}
else
{
double amount = 0;
double.TryParse(Convert.ToString(htSubTot["Credit"]), out amount);
amount += creditAmt;
htSubTot.Remove("Credit");
htSubTot.Add("Credit", Convert.ToString(amount));
}
boolCheck = true;
}
#endregion
}
if (boolCheck)
{
bool boolBank = false;
for (int bnK = 0; bnK < ds.Tables[2].Rows.Count; bnK++)
{
ds.Tables[1].DefaultView.RowFilter = "bankfk='" + Convert.ToString(ds.Tables[2].Rows[bnK]["bankpk"]) + "'";
DataTable dtBank = ds.Tables[1].DefaultView.ToTable();
if (dtBank.Rows.Count > 0)
{
if (!boolBank)
{
Fpspread1.Sheets[0].RowCount++;
rowCnt = Fpspread1.Sheets[0].RowCount - 1;
Fpspread1.Sheets[0].Cells[rowCnt, 0].Text = "Bank Balances";
Fpspread1.Sheets[0].SpanModel.Add(rowCnt, 0, 1, Fpspread1.Sheets[0].ColumnCount - 1);
Fpspread1.Sheets[0].Rows[rowCnt].BackColor = Color.Gray;
boolBank = true;
}
Fpspread1.Sheets[0].RowCount++;
rowCnt = Fpspread1.Sheets[0].RowCount - 1;
Fpspread1.Sheets[0].Cells[rowCnt, 0].Text = Convert.ToString(++rowNum);
// double openBal = 0;
double ob = 0;
double openBalhead = 0;
if (dsVal.Tables[4].Rows.Count > 0)
{
dsVal.Tables[4].DefaultView.RowFilter = " bankfk='" + Convert.ToString(ds.Tables[2].Rows[bnK]["bankpk"]) + "' ";//and finyearfk='" + financialYear.Key + "'
DataTable dtOpen = dsVal.Tables[3].DefaultView.ToTable();
DataTable dtopenhead = dsVal.Tables[6].DefaultView.ToTable();
if (dtOpen.Rows.Count > 0 || dtopenhead.Rows.Count > 0)
{
double.TryParse(Convert.ToString(dtOpen.Compute("sum(credit)", "")), out ob);
}
double.TryParse(Convert.ToString(dtopenhead.Compute("sum(openbal)", "")), out openBalhead);
}
double openBal = 0;
openBal = ob + openBalhead;
Fpspread1.Sheets[0].Cells[rowCnt, 1].Text = Convert.ToString(openBal);
if (!htSubTot.ContainsKey("OpenBal"))
{
htSubTot.Add("OpenBal", openBal);
}
else
{
double amount = 0;
double.TryParse(Convert.ToString(htSubTot["OpenBal"]), out amount);
amount += openBal;
htSubTot.Remove("OpenBal");
htSubTot.Add("OpenBal", Convert.ToString(amount));
}
Fpspread1.Sheets[0].Cells[rowCnt, 2].Text = Convert.ToString(ds.Tables[2].Rows[bnK]["bankname"]);
double debitAmt = 0;
double.TryParse(Convert.ToString(dtBank.Rows[0]["credit"]), out debitAmt);
Fpspread1.Sheets[0].Cells[rowCnt, 3].Text = Convert.ToString(debitAmt);
if (debitAmt == 0)
{
Fpspread1.Sheets[0].Cells[rowCnt, 3].Text = "-";
}
if (!htSubTot.ContainsKey("Debit"))
{
htSubTot.Add("Debit", debitAmt);
}
else
{
double amount = 0;
double.TryParse(Convert.ToString(htSubTot["Debit"]), out amount);
amount += debitAmt;
htSubTot.Remove("Debit");
htSubTot.Add("Debit", Convert.ToString(amount));
}
double creditAmt = 0;
double.TryParse(Convert.ToString(dtBank.Rows[0]["debit"]), out creditAmt);
Fpspread1.Sheets[0].Cells[rowCnt, 4].Text = Convert.ToString(creditAmt);
if (creditAmt == 0)
{
Fpspread1.Sheets[0].Cells[rowCnt, 4].Text = "-";
}
if (!htSubTot.ContainsKey("Credit"))
{
htSubTot.Add("Credit", creditAmt);
}
else
{
double amount = 0;
double.TryParse(Convert.ToString(htSubTot["Credit"]), out amount);
amount += creditAmt;
htSubTot.Remove("Credit");
htSubTot.Add("Credit", Convert.ToString(amount));
}
}
}
}
// Fpspread1.Sheets[0].PageSize = ds.Tables[0].Rows.Count + 1;
if (boolCheck)
{
Fpspread1.Sheets[0].Rows.Count++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].Rows.Count - 1, 2].Text = "Deficit";
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].Rows.Count - 1].Font.Bold = true;
// Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].Rows.Count - 1].BackColor = Color.Green;
double debit = 0;
double credit = 0;
double.TryParse(Convert.ToString(htSubTot["Debit"]), out debit);
double.TryParse(Convert.ToString(htSubTot["Credit"]), out credit);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].Rows.Count - 1, 3].Text = "-";
if (debit > credit)
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].Rows.Count - 1, 3].Text = Convert.ToString(debit - credit);
}
Fpspread1.Sheets[0].Rows.Count++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].Rows.Count - 1, 0].Text = "Total";
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].Rows.Count - 1].Font.Bold = true;
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].Rows.Count - 1].BackColor = Color.Green;
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].Rows.Count - 1].ForeColor = Color.White;
double grandvalue = 0;
double.TryParse(Convert.ToString(htSubTot["OpenBal"]), out grandvalue);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].Rows.Count - 1, 1].Text = Convert.ToString(grandvalue);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].Rows.Count - 1, 3].Text = Convert.ToString(debit);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].Rows.Count - 1, 4].Text = Convert.ToString(credit);
}
//for (int j = 2; j < FpSpread1.Sheets[0].ColumnCount; j++)
//{
// double.TryParse(Convert.ToString(grandtotal[j]), out grandvalue);
// FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].Rows.Count - 1, j].Text = Convert.ToString(grandvalue);
//}
// }
Fpspread1.Sheets[0].PageSize = Fpspread1.Sheets[0].RowCount;
Fpspread1.Visible = true;
print.Attributes.Add("Style", "display:block;");
// print.Visible = true;
Fpspread1.SaveChanges();
#endregion
}
catch { }
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
string InsQ = "";
int MyUpdCount = 0;
string ClgCode = Convert.ToString(ddlcollege.SelectedValue);
string Dept_Code = string.Empty;
string Desig_Code = string.Empty;
string staffcode = "";
double Tot_Hrs = 0;
double AmntPerHrs = 0;
string myErrTxt = string.Empty;
if (CheckSpr())
{
if (CheckSprVal(ref myErrTxt))
{
Fpspread1.SaveChanges();
for (int myVal = 1; myVal < Fpspread1.Sheets[0].RowCount; myVal++)
{
InsQ = "";
Dept_Code = string.Empty;
Desig_Code = string.Empty;
Tot_Hrs = 0;
AmntPerHrs = 0;
byte paytype = 0;
byte Check = Convert.ToByte(Fpspread1.Sheets[0].Cells[myVal, 1].Value);
if (Check == 1)
{
/* poomalar 16.10.17*/
if (chkstaff.Checked == false)
{
Dept_Code = Convert.ToString(Fpspread1.Sheets[0].Cells[myVal, 3].Note);
Desig_Code = Convert.ToString(Fpspread1.Sheets[0].Cells[myVal, 3].Tag);
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[myVal, 4].Text), out Tot_Hrs);
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[myVal, 5].Text), out AmntPerHrs);
paytype = 0;
}
if (chkstaff.Checked == true)
{
staffcode = Convert.ToString(Fpspread1.Sheets[0].Cells[myVal, 3].Text);
Dept_Code = Convert.ToString(Fpspread1.Sheets[0].Cells[myVal, 4].Note);
Desig_Code = Convert.ToString(Fpspread1.Sheets[0].Cells[myVal, 4].Tag);
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[myVal, 5].Text), out Tot_Hrs);
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[myVal, 6].Text), out AmntPerHrs);
paytype = 1;
}
if (!String.IsNullOrEmpty(Dept_Code) && !String.IsNullOrEmpty(Desig_Code) && Tot_Hrs != 0 && AmntPerHrs != 0)
{ /* poomalar 23.10.17*/
if (chkstaff.Checked == true)
{
InsQ = "if exists (select * from HourWise_PaySettings where college_code='" + ClgCode + "' and dept_code='" + Dept_Code + "' and desig_code='" + Desig_Code + "' and Staffcode='" + staffcode + "') update HourWise_PaySettings set Tot_Hrs='" + Tot_Hrs + "',Amnt_Per_Hrs='" + AmntPerHrs + "'where college_code='" + ClgCode + "' and dept_code='" + Dept_Code + "' and desig_code='" + Desig_Code + "' and Staffcode='" + staffcode + "' and PayType='" + paytype + "' else insert into HourWise_PaySettings (dept_code,desig_code,Tot_Hrs,Amnt_Per_Hrs,college_code,Staffcode,PayType) values ('" + Dept_Code + "','" + Desig_Code + "','" + Tot_Hrs + "','" + AmntPerHrs + "','" + ClgCode + "','" + staffcode + "','" + paytype + "')";
}
else
{
InsQ = "if exists (select * from HourWise_PaySettings where college_code='" + ClgCode + "' and dept_code='" + Dept_Code + "' and desig_code='" + Desig_Code + "') update HourWise_PaySettings set Tot_Hrs='" + Tot_Hrs + "',Amnt_Per_Hrs='" + AmntPerHrs + "'where college_code='" + ClgCode + "' and dept_code='" + Dept_Code + "' and desig_code='" + Desig_Code + "' and PayType='" + paytype + "' else insert into HourWise_PaySettings (dept_code,desig_code,Tot_Hrs,Amnt_Per_Hrs,college_code,PayType) values ('" + Dept_Code + "','" + Desig_Code + "','" + Tot_Hrs + "','" + AmntPerHrs + "','" + ClgCode + "','" + paytype + "')";
}
int insCount = d2.update_method_wo_parameter(InsQ, "Text");
if (insCount > 0)
{
MyUpdCount += 1;
}
}
}
}
if (MyUpdCount > 0)
{
alertpopwindow.Visible = true;
lblalerterr.Text = "Saved Successfully!";
btnGo_Click(sender, e);
}
}
else
{
lblMainErr.Visible = true;
lblMainErr.Text = myErrTxt;
}
}
else /* poomalar 23.10.17*/
{
if (chkstaff.Checked == true)
{
lblMainErr.Visible = true;
lblMainErr.Text = "Please Select Any Staff!";
}
else
{
lblMainErr.Visible = true;
lblMainErr.Text = "Please Select Any Designation!";
}
}
}
catch { }
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
string MyErrTxt = string.Empty;
string StaffCode = string.Empty;
string Appl_ID = string.Empty;
string WorkDate = string.Empty;
double WorkHrs = 0;
string InsQ = string.Empty;
int InsCount = 0;
if (CheckSelSpr())
{
if (CheckSpr(ref MyErrTxt))
{
Fpspread1.SaveChanges();
for (int ro = 1; ro < Fpspread1.Sheets[0].RowCount; ro++)
{
StaffCode = string.Empty;
Appl_ID = string.Empty;
byte Check = Convert.ToByte(Fpspread1.Sheets[0].Cells[ro, 1].Value);
if (Check == 1)
{
StaffCode = Convert.ToString(Fpspread1.Sheets[0].Cells[ro, 2].Text);
Appl_ID = Convert.ToString(Fpspread1.Sheets[0].Cells[ro, 2].Tag);
for (int col = 9; col <= Fpspread1.Sheets[0].ColumnCount - 1; col++)
{
WorkDate = string.Empty;
WorkHrs = 0;
WorkDate = Convert.ToString(Fpspread1.Sheets[0].ColumnHeader.Cells[1, col].Text);
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[ro, col].Text), out WorkHrs);
InsQ = "if exists (select * from Hour_Staff_Attnd where Appl_ID='" + Appl_ID + "' and Staff_Code='" + StaffCode + "' and WorkingDate='" + GetDayFrstDate(WorkDate).ToString("MM/dd/yyyy") + "') update Hour_Staff_Attnd set WorkingHour='" + WorkHrs + "' where Appl_ID='" + Appl_ID + "' and Staff_Code='" + StaffCode + "' and WorkingDate='" + GetDayFrstDate(WorkDate).ToString("MM/dd/yyyy") + "' else insert into Hour_Staff_Attnd (Appl_ID,Staff_Code,WorkingDate,WorkingHour) values ('" + Appl_ID + "','" + StaffCode + "','" + GetDayFrstDate(WorkDate).ToString("MM/dd/yyyy") + "','" + WorkHrs + "')";
int UpdCount = d2.update_method_wo_parameter(InsQ, "Text");
if (UpdCount > 0)
{
InsCount++;
}
}
}
}
if (InsCount > 0)
{
alertpopwindow.Visible = true;
lblMainErr.Visible = false;
lblalerterr.Text = "Saved Successfully!";
btnGo_Click(sender, e);
}
}
else
{
lblMainErr.Visible = true;
lblMainErr.Text = MyErrTxt;
}
}
else
{
lblMainErr.Visible = true;
lblMainErr.Text = "Please Select Any Staff!";
}
}
catch { }
}
private void LoadHeader()
{
lblNote.Visible = false;
Fpspread1.Visible = false;
rprint.Visible = false;
lblMainErr.Visible = false;
btnSave.Visible = false;
string collCode = Convert.ToString(ddlcollege.SelectedItem.Value);
string DeptCode = string.Empty;
string DesigCode = string.Empty;
string StfType = string.Empty;
string StfCat = string.Empty;
string MyDeptCode = string.Empty;
string MyDesigCode = string.Empty;
string MyStfType = string.Empty;
string MyStfCat = string.Empty;
DataView dvnew = new DataView();
DataView dvnew1 = new DataView();
DeptCode = GetSelectedItemsValueAsString(cblDept);
DesigCode = GetSelectedItemsValueAsString(cblDesig);
StfType = GetSelectedItemsText(cblStfType);
StfCat = GetSelectedItemsValueAsString(cblStfCat);
MyDeptCode = "'" + DeptCode + "'";
MyDesigCode = "'" + DesigCode + "'";
MyStfType = "'" + StfType + "'";
MyStfCat = "'" + StfCat + "'";
DateTime dtFrm = GetDayFrstDate(txtFrmDt.Text.Trim());
DateTime dtTo = GetDayFrstDate(txtToDt.Text.Trim());
DateTime dtTempFrm = dtFrm;
DateTime dtTempTo = dtTo;// delsi0606 added or isnull(st.stfnature,'')='part')
string SelQ = "select Tot_Hrs,Amnt_Per_Hrs,dept_code,desig_Code,college_code from HourWise_PaySettings where dept_code in(" + MyDeptCode + ") and desig_code in(" + MyDesigCode + ") and college_code='" + collCode + "'";
SelQ = SelQ + " select sm.staff_code,sm.staff_name,h.dept_name,desig.desig_name,st.stftype,sc.category_name,h.dept_code,desig.desig_code,sm.college_Code,sm.appl_no from staffmaster sm,stafftrans st,hrdept_master h,desig_master desig,staffcategorizer sc where sm.staff_code=st.staff_code and sm.college_code=h.college_code and sm.college_code=desig.collegeCode and sm.college_code=sc.college_code and (isnull(st.stfnature,0)='1' or isnull(st.stfnature,'')='part') and st.dept_code=h.dept_code and st.desig_code=desig.desig_code and st.category_code=sc.category_code and st.latestrec='1' and sm.resign='0' and sm.settled='0' and ISNULL(Discontinue,'0')='0' and sm.college_code='" + collCode + "'";
if (!String.IsNullOrEmpty(DeptCode))
{
SelQ = SelQ + " and h.dept_code in(" + MyDeptCode + ")";
}
if (!String.IsNullOrEmpty(DesigCode))
{
SelQ = SelQ + " and desig.desig_code in(" + MyDesigCode + ")";
}
if (!String.IsNullOrEmpty(StfType))
{
SelQ = SelQ + " and st.stftype in(" + MyStfType + ")";
}
if (!String.IsNullOrEmpty(StfCat))
{
SelQ = SelQ + " and sc.category_code in(" + MyStfCat + ")";
}
SelQ = SelQ + " select Appl_ID,Staff_Code,WorkingDate,WorkingHour from Hour_Staff_Attnd where MONTH(WorkingDate) in('" + dtFrm.Month + "','" + dtTo.Month + "') and YEAR(WorkingDate) in('" + dtFrm.Year + "','" + dtTo.Year + "')";
ds.Clear();
ds = d2.select_method_wo_parameter(SelQ, "Text");
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0 && ds.Tables[1].Rows.Count > 0)
{
Fpspread1.Sheets[0].AutoPostBack = false;
Fpspread1.Sheets[0].ColumnHeader.RowCount = 2;
Fpspread1.Sheets[0].RowCount = 0;
Fpspread1.Sheets[0].ColumnCount = 9;
Fpspread1.Sheets[0].RowHeader.Visible = false;
Fpspread1.CommandBar.Visible = false;
darkstyle.Font.Name = "Book Antiqua";
darkstyle.Font.Bold = true;
darkstyle.Font.Size = FontUnit.Medium;
darkstyle.HorizontalAlign = HorizontalAlign.Center;
darkstyle.ForeColor = Color.Black;
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
Fpspread1.Sheets[0].ColumnHeader.DefaultStyle = darkstyle;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No.";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Select";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Staff Code";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Staff Name";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Department";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Designation";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 6].Text = "Staff Type";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 7].Text = "Staff Category";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 8].Text = "Total Hours";
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 2, 1);
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 1, 2, 1);
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 2, 2, 1);
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 3, 2, 1);
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 4, 2, 1);
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 5, 2, 1);
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 6, 2, 1);
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 7, 2, 1);
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 8, 2, 1);
Fpspread1.Columns[0].Width = 75;
Fpspread1.Columns[1].Width = 75;
Fpspread1.Columns[2].Width = 150;
Fpspread1.Columns[3].Width = 200;
Fpspread1.Columns[4].Width = 180;
Fpspread1.Columns[5].Width = 180;
Fpspread1.Columns[6].Width = 150;
Fpspread1.Columns[7].Width = 150;
Fpspread1.Columns[8].Width = 75;
Fpspread1.Columns[0].Locked = true;
Fpspread1.Columns[2].Locked = true;
Fpspread1.Columns[3].Locked = true;
Fpspread1.Columns[4].Locked = true;
Fpspread1.Columns[5].Locked = true;
Fpspread1.Columns[6].Locked = true;
Fpspread1.Columns[7].Locked = true;
Fpspread1.Columns[8].Locked = true;
while (dtTempFrm <= dtTempTo)
{
Fpspread1.Sheets[0].ColumnCount++;
Fpspread1.Sheets[0].ColumnHeader.Cells[1, Fpspread1.Sheets[0].ColumnCount - 1].Text = Convert.ToString(dtTempFrm.ToString("dd/MM/yyyy"));
dtTempFrm = dtTempFrm.AddDays(1);
}
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 9].Text = "Working Hours";
Fpspread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 9, 1, Fpspread1.Sheets[0].ColumnCount - 9);
dtTempFrm = dtFrm;
dtTempTo = dtTo;
CheckAll.AutoPostBack = true;
CheckInd.AutoPostBack = false;
DoubleHrs.MaximumValue = 10;
DoubleHrs.ErrorMessage = "Allow only Numerics & Max Hours is less than or Equal to Total Hours!";
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].CellType = CheckAll;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Value = 0;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Font.Name = "Book Antiqua";
int slno = 0;
for (int ro = 0; ro < ds.Tables[0].Rows.Count; ro++)
{
ds.Tables[1].DefaultView.RowFilter = " dept_code='" + Convert.ToString(ds.Tables[0].Rows[ro]["dept_code"]) + "' and desig_code='" + Convert.ToString(ds.Tables[0].Rows[ro]["desig_code"]) + "' and college_code='" + Convert.ToString(ds.Tables[0].Rows[ro]["college_code"]) + "'";
dvnew = ds.Tables[1].DefaultView;
if (dvnew.Count > 0)
{
for (int dv = 0; dv < dvnew.Count; dv++)
{
Fpspread1.Sheets[0].RowCount++;
slno += 1;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(slno);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].CellType = CheckInd;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Value = 0;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Font.Name = "Book Antiqua";
string Appl_ID = "";
Appl_ID = d2.GetFunction("select appl_id from staff_appl_master where appl_no='" + Convert.ToString(dvnew[dv]["appl_no"]) + "' and college_code='" + collCode + "'");
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = Convert.ToString(dvnew[dv]["staff_code"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Tag = Convert.ToString(Appl_ID).Trim();
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(dvnew[dv]["staff_name"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = Convert.ToString(dvnew[dv]["dept_name"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].Text = Convert.ToString(dvnew[dv]["desig_name"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 6].Text = Convert.ToString(dvnew[dv]["stftype"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 6].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 6].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 6].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 7].Text = Convert.ToString(dvnew[dv]["category_name"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 7].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 7].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 8].Text = Convert.ToString(ds.Tables[0].Rows[ro]["Tot_Hrs"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 8].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 8].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 8].Font.Name = "Book Antiqua";
int col = 9;
double DblHRs = 0;
while (dtTempFrm <= dtTempTo)
{
col += 1;
ds.Tables[2].DefaultView.RowFilter = " Appl_ID='" + Appl_ID + "' and Staff_Code='" + Convert.ToString(dvnew[dv]["staff_code"]) + "' and WorkingDate='" + dtTempFrm.ToString("MM/dd/yyyy") + "'";
dvnew1 = ds.Tables[2].DefaultView;
if (dvnew1.Count > 0)
{
double.TryParse(Convert.ToString(dvnew1[0]["WorkingHour"]), out DblHRs);
DblHRs = Math.Round(DblHRs, 0, MidpointRounding.AwayFromZero);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, col - 1].Text = Convert.ToString(DblHRs);
}
else
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, col - 1].Text = "";
}
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, col - 1].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, col - 1].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, col - 1].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, col - 1].CellType = DoubleHrs;
dtTempFrm = dtTempFrm.AddDays(1);
}
dtTempFrm = dtFrm;
dtTempTo = dtTo;
}
}
}
Fpspread1.SaveChanges();
Fpspread1.Sheets[0].PageSize = Fpspread1.Sheets[0].RowCount;
Fpspread1.Sheets[0].FrozenRowCount = 1;
Fpspread1.Sheets[0].FrozenColumnCount = 4;
Fpspread1.Visible = true;
lblNote.Visible = true;
btnSave.Visible = true;
rprint.Visible = true;
lblMainErr.Visible = false;
}
else
{
lblMainErr.Visible = true;
lblMainErr.Text = "No Record(s) Found!";
}
}
private void loadspread(DataSet ds)
{
try
{
Fpspread1.Visible = true;
rptprint.Visible = true;
#region design
Fpspread1.Sheets[0].RowCount = 0;
Fpspread1.Sheets[0].ColumnCount = 0;
Fpspread1.CommandBar.Visible = false;
Fpspread1.Sheets[0].AutoPostBack = true;
Fpspread1.Sheets[0].ColumnHeader.RowCount = 1;
Fpspread1.Sheets[0].RowHeader.Visible = false;
Fpspread1.Sheets[0].ColumnCount = 5;
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.Black;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Border.BorderColor = System.Drawing.Color.Black;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Columns[0].Width = 40;
Fpspread1.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Receipts";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Border.BorderColor = System.Drawing.Color.Black;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Columns[1].Width = 300;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Amounts";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Border.BorderColor = System.Drawing.Color.Black;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Columns[2].Width = 150;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Payments";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Border.BorderColor = System.Drawing.Color.Black;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Columns[3].Width = 300;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Amounts";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Border.BorderColor = System.Drawing.Color.Black;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Columns[4].Width = 150;
string format = "Receipts and Payment for the Date of-" + Convert.ToString(txt_fromdate.Text) + "-" + Convert.ToString(txt_todate.Text);
#endregion
DataRow drow;
int rowcount = 0;
DataTable dtnew = new DataTable();
getHeadername();
Dictionary <string, string> hthdName = getHeadername();
//for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
//{
// Fpspread1.Sheets[0].RowCount++;
// Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text =
// //ds.Tables[1].DefaultView.RowFilter = "subType_no ='" + subno + "'";
// dtnew = ds.Tables[1].DefaultView.ToTable();
//}
Hashtable htRowCount = new Hashtable();
int totRcptCount = 0;
int RcptCountStart = 0;
double grandtotal = 0;//abarna
double grandtotalp = 0;
foreach (KeyValuePair <string, string> hdVal in hthdName)
{
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(hdVal.Value);
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].RowCount - 1].BackColor = Color.Green;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].ColumnSpan = 5;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = format;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].ColumnSpan = 5;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
double openbal = 0;
double openbalancehead = 0;
double openbalance = 0;
htRowCount = new Hashtable();
totRcptCount = 0;
RcptCountStart = 0;
if (ds.Tables.Count > 0 && ds.Tables[1].Rows.Count > 0)
{
int sno = 0;
double total = 0;
string headerfk = "select headerpk from fm_headermaster where headername in('" + hdVal.Value + "') and collegecode in('" + collegecode + "')";
//string[] str = headerfk.Split(',');
DataSet s = d2.select_method_wo_parameter(headerfk, "Text");
string str = "";
for (int k = 0; k < s.Tables[0].Rows.Count; k++)
{
if (str == "")
{
str = Convert.ToString(s.Tables[0].Rows[k]["headerpk"]);
}
else
{
str = str + "','" + Convert.ToString(s.Tables[0].Rows[k]["headerpk"]);
}
}
ds.Tables[0].DefaultView.RowFilter = "headerfk in('" + str + "')";
ds.Tables[4].DefaultView.RowFilter = "headerpk in('" + str + "')";
DataTable dtOpenBal = ds.Tables[0].DefaultView.ToTable();
DataTable dtopenhead = ds.Tables[4].DefaultView.ToTable();
object sum = dtOpenBal.Compute("Sum(debit)", "");
object sumhead = dtopenhead.Compute("sum(openbal)", "");
// object sum = ds.Tables[0].Compute("Sum(debit)", "");
//sno++;
double.TryParse(Convert.ToString(sum), out openbal);
double.TryParse(Convert.ToString(sumhead), out openbalancehead);
openbalance = openbal + openbalancehead;
Fpspread1.Sheets[0].RowCount++;
// Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Text = "OPENING BALANCE";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = d2.numberformat(openbalance.ToString());
total = openbalance;
//initial count set
if (RcptCountStart == 0)
{
int.TryParse(Convert.ToString(Fpspread1.Sheets[0].RowCount - 1), out RcptCountStart);
}
totRcptCount++;
if (ds.Tables.Count > 1 && ds.Tables[1].Rows.Count > 0)
{
DataTable dtDistinctLedger = new DataTable();
ds.Tables[1].DefaultView.RowFilter = "headerfk in('" + str + "')";
DataTable dtTablOne = ds.Tables[1].DefaultView.ToTable();
// dtDistinctLedger = ds.Tables[1].DefaultView.ToTable(true, "ledgerfk");
dtDistinctLedger = dtTablOne.DefaultView.ToTable(true, "ledgerfk");
double debit = 0;
foreach (DataRow drrow in dtDistinctLedger.Rows)
{
string ledgerId = Convert.ToString(drrow["ledgerfk"]).Trim();
//ds.Tables[1].DefaultView.RowFilter = "ledgerfk='" + ledgerId + "'";
double.TryParse(Convert.ToString(ds.Tables[1].Compute("Sum(debit)", "ledgerfk='" + ledgerId + "'")), out debit);
DataView ledger = new DataView();
if (ds.Tables.Count > 3 && ds.Tables[3].Rows.Count > 0)
{
ds.Tables[3].DefaultView.RowFilter = "ledgerfk='" + ledgerId + "'";
ledger = ds.Tables[3].DefaultView;
}
if (ledger.Count > 0)
{
sno++;
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(ledger[0]["LedgerName"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = d2.numberformat(Convert.ToString(debit));
totRcptCount++;
}
total += debit;
}
grandtotal += total;
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Text = "Total";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Note = "Total=Opening Balance + Sum Of Receipts";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].BackColor = Color.YellowGreen;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].BackColor = Color.YellowGreen;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = d2.numberformat(Convert.ToString(total));
//foreach (DataRow drrow1 in ds.Tables[2].Rows)
//{
// Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(ledger[0]["LedgerName"]);
// Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = Convert.ToString(drrow1["credit"]);
//}
}
double creditAmt = 0;
if (ds.Tables.Count > 2 && ds.Tables[2].Rows.Count > 0)
{
#region Voucher
sno = 0;
double closingbalance = 0;
double credittotal = 0;
int startingRow = RcptCountStart;
int maxRowCount = totRcptCount;
if (ds.Tables.Count > 2 && ds.Tables[2].Rows.Count > 0)
{
DataTable dtDistinctLedgerCredit = new DataTable();
ds.Tables[2].DefaultView.RowFilter = "headerfk in('" + str + "')";
DataTable dtTablTwo = ds.Tables[2].DefaultView.ToTable();
dtDistinctLedgerCredit = dtTablTwo.DefaultView.ToTable(true, "ledgerfk");
// dtDistinctLedgerCredit = ds.Tables[2].DefaultView.ToTable(true, "ledgerfk");
foreach (DataRow drrow in dtDistinctLedgerCredit.Rows)
{
string ledgerId = Convert.ToString(drrow["ledgerfk"]).Trim();
double credit = 0;
double.TryParse(Convert.ToString(ds.Tables[2].Compute("Sum(credit)", "ledgerfk='" + ledgerId + "'")), out credit);
DataView ledger = new DataView();
if (ds.Tables.Count > 3 && ds.Tables[3].Rows.Count > 0)
{
ds.Tables[3].DefaultView.RowFilter = "ledgerfk='" + ledgerId + "'";
ledger = ds.Tables[3].DefaultView;
}
if (ledger.Count > 0)
{
sno++;
if (maxRowCount == 0)
{
Fpspread1.Sheets[0].RowCount++;
int.TryParse(Convert.ToString(Fpspread1.Sheets[0].RowCount - 1), out startingRow);
}
else
{
maxRowCount--;
}
// Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
Fpspread1.Sheets[0].Cells[startingRow, 3].Text = Convert.ToString(ledger[0]["LedgerName"]);
Fpspread1.Sheets[0].Cells[startingRow, 4].Text = d2.numberformat(Convert.ToString(credit));
startingRow++;
creditAmt += credit;
}
}
if (total != 0)
{
if (maxRowCount == 0)
{
Fpspread1.Sheets[0].RowCount++;
int.TryParse(Convert.ToString(Fpspread1.Sheets[0].RowCount - 1), out startingRow);
}
Fpspread1.Sheets[0].Cells[startingRow, 3].Text = "Closing Balance";
// Fpspread1.Sheets[0].Cells[startingRow, 3].Tag = "total-debitamount";
Fpspread1.Sheets[0].Cells[startingRow, 3].Note = "Closing Balance=Sum OF Receipt Amount-Sum Of Payment Amount";
Fpspread1.Sheets[0].Cells[startingRow, 3].BackColor = Color.YellowGreen;
closingbalance = total - creditAmt;
Fpspread1.Sheets[0].Cells[startingRow, 4].Text = d2.numberformat(Convert.ToString(closingbalance));
}
Fpspread1.Sheets[0].RowCount++;
//startingRow++;
credittotal = creditAmt + closingbalance;
// Fpspread1.Sheets[0].RowCount++;
//
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = "Total";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Note = "Total=Closing Balance+Sum Of Payments";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = d2.numberformat(Convert.ToString(credittotal));
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].BackColor = Color.YellowGreen;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].BackColor = Color.YellowGreen;
//Fpspread1.Sheets[0].Cells[startingRow, 3].Text = "Total";
//Fpspread1.Sheets[0].Cells[startingRow, 4].Text = Convert.ToString(credittotal);
//Fpspread1.Sheets[0].Cells[startingRow, 3].BackColor = Color.Yellow;
//Fpspread1.Sheets[0].Cells[startingRow, 4].BackColor = Color.Yellow;
grandtotalp += credittotal;
}
#endregion
}
}
}
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Text = "Grand Total";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Note = "Total=Opening Balance + Sum Of Total Receipts";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].BackColor = Color.YellowGreen;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].BackColor = Color.YellowGreen;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = d2.numberformat(Convert.ToString(grandtotal));
Fpspread1.Sheets[0].PageSize = Fpspread1.Sheets[0].RowCount;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = "Total";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Note = "Total=Closing Balance+ Sum Of Total Payments";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = d2.numberformat(Convert.ToString(grandtotalp));
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].BackColor = Color.YellowGreen;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].BackColor = Color.YellowGreen;
Fpspread1.SaveChanges();
}
catch { }
}
public void btn_go_Click(object sender, EventArgs e)
{
string collcode = Convert.ToString(ddl_clg.SelectedItem.Value);
try
{
hide();
string ddlslbval = ddl_slbval.SelectedItem.Value.ToString();
Fpspread1.Sheets[0].RowHeader.Visible = false;
Fpspread1.CommandBar.Visible = false;
Fpspread1.Sheets[0].AutoPostBack = true;
Fpspread1.ColumnHeader.RowCount = 1;
Fpspread1.Sheets[0].Rows.Count = 0;
Fpspread1.Sheets[0].Columns.Count = 8;
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.White;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
string selqry = "select distinct salfrom,salto,category_code,ESI_EmpSlabType,ESI_EmpSlabValue,SlabFor,slabtype,slabvalue from pfslabs where SlabFor='" + ddlslbval + "' and college_code='" + collcode + "' order by category_code";
ds = d2.select_method_wo_parameter(selqry, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
Fpspread1.Sheets[0].Rows.Count = ds.Tables[0].Rows.Count;
Fpspread1.Sheets[0].Columns.Count = 8;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string catcode = ds.Tables[0].Rows[i]["category_code"].ToString();
string catname = d2.GetFunction("SELECT category_name FROM staffcategorizer where college_code ='" + collcode + "' and category_code='" + catcode + "'");
Fpspread1.Sheets[0].Cells[i, 0].Text = Convert.ToString(i + 1);
Fpspread1.Sheets[0].Cells[i, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[i, 1].Text = Convert.ToString(catname);
Fpspread1.Sheets[0].Cells[i, 1].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[i, 1].Tag = Convert.ToString(catcode);
Fpspread1.Sheets[0].Cells[i, 2].Text = Convert.ToString(ds.Tables[0].Rows[i]["salfrom"].ToString());
Fpspread1.Sheets[0].Cells[i, 2].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[i, 2].Tag = Convert.ToString(ds.Tables[0].Rows[i]["SlabFor"].ToString());
Fpspread1.Sheets[0].Cells[i, 3].Text = Convert.ToString(ds.Tables[0].Rows[i]["salto"].ToString());
Fpspread1.Sheets[0].Cells[i, 3].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[i, 4].Text = Convert.ToString(ds.Tables[0].Rows[i]["slabtype"].ToString());
Fpspread1.Sheets[0].Cells[i, 4].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[i, 5].Text = Convert.ToString(ds.Tables[0].Rows[i]["slabvalue"].ToString());
Fpspread1.Sheets[0].Cells[i, 5].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[i, 6].Text = Convert.ToString(ds.Tables[0].Rows[i]["ESI_EmpSlabType"].ToString());
Fpspread1.Sheets[0].Cells[i, 6].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[i, 7].Text = Convert.ToString(ds.Tables[0].Rows[i]["ESI_EmpSlabValue"].ToString());
Fpspread1.Sheets[0].Cells[i, 7].Font.Name = "Book Antiqua";
}
for (int ii = 0; ii < 8; ii++)
{
Fpspread1.Sheets[0].ColumnHeader.Columns[ii].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].ColumnHeader.Columns[ii].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Columns[ii].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Columns[ii].Font.Size = FontUnit.Medium;
}
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Category";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Salary From";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Salary To";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Slab Type";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Slab Value";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 6].Text = "Emp Slab Type";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 6].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 6].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 7].Text = "Emp Slab Value";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 7].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 7].Font.Name = "Book Antiqua";
for (int j = 0; j < Fpspread1.Sheets[0].Columns.Count; j++)
{
Fpspread1.Sheets[0].Columns[j].HorizontalAlign = HorizontalAlign.Center;
}
Fpspread1.Sheets[0].Columns[1].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Columns[4].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Columns[6].HorizontalAlign = HorizontalAlign.Left;
for (int i = 0; i < Fpspread1.Sheets[0].Columns.Count; i++)
{
Fpspread1.Sheets[0].Columns[i].Locked = true;
}
Fpspread1.Sheets[0].Columns[2].Locked = false;
Fpspread1.Sheets[0].Columns[3].Locked = false;
Fpspread1.Visible = true;
rptprint.Visible = true;
div1.Visible = true;
lbl_error.Visible = false;
Fpspread1.Sheets[0].PageSize = Fpspread1.Sheets[0].RowCount;
Fpspread1.SaveChanges();
addnew.Visible = false;
show();
}
else
{
lbl_error.Visible = true;
lbl_error.Text = "No Records Found";
hide();
}
}
catch (Exception er)
{
d2.sendErrorMail(er, collcode, "SlabsMaster_Alter.aspx");
}
}
protected void btndummynoprint_Click(object sender, EventArgs e)
{
try
{
ArrayList adddummyarray = new ArrayList();
Gios.Pdf.PdfDocument mydoc;
Font Fontbold = new Font("Book Antiqua", 18, FontStyle.Regular);
Font fbold = new Font("Book Antiqua", 18, FontStyle.Bold);
Font Fontsmall = new Font("Book Antiqua", 12, FontStyle.Regular);
Font fontname = new Font("Book Antiqua", 11, FontStyle.Bold);
Font fontmedium = new Font("Book Antiqua", 10, FontStyle.Regular);
Font fontmediumb = new Font("Book Antiqua", 8, FontStyle.Bold);
mydoc = new Gios.Pdf.PdfDocument(PdfDocumentFormat.InCentimeters(24, 30));
Gios.Pdf.PdfPage mypdfpage;
// Gios.Pdf.PdfTable table1 = myprovdoc.NewTable(Fontsmall, prov_cnt + 8, 6, 1);
Gios.Pdf.PdfTable table;
Gios.Pdf.PdfTable table1;
Gios.Pdf.PdfTablePage myprov_pdfpage1;
string deptvalue = "";
Fpspread1.SaveChanges();
int prinsheetno = 0;
string getmaxsheetno = d2.GetFunction("select isnull(max(sheet_no),'10000') from dummynumber where exam_year='" + ddlYear.SelectedValue.ToString() + "' and exam_month='" + ddlMonth.SelectedValue.ToString() + "' ");
int strshetno = Convert.ToInt32(getmaxsheetno);
if (Fpspread1.Sheets[0].RowCount > 0)
{
for (int row = 0; row < Fpspread1.Sheets[0].RowCount; row++)
{
string getval = Fpspread1.Sheets[0].Cells[row, 2].Tag.ToString();
if (getval == "1")
{
adddummyarray.Add(Convert.ToString(Fpspread1.Sheets[0].Cells[row, 2].Text));
}
else
{
adddummyarray.Add(Convert.ToString(Fpspread1.Sheets[0].Cells[row, 3].Text));
}
string dummyno = Fpspread1.Sheets[0].Cells[row, 3].Tag.ToString();
if (dummyno == "")
{
if ((row % 25) == 0)
{
strshetno++;
}
dummyno = strshetno.ToString();
string insertval = "update dummynumber set sheet_no='" + dummyno + "' where dummy_no='" + Fpspread1.Sheets[0].Cells[row, 3].Text.ToString() + "'";
int val = d2.update_method_wo_parameter(insertval, "Text");
}
if (prinsheetno == 0)
{
prinsheetno = Convert.ToInt32(dummyno);
}
Fpspread1.Sheets[0].Cells[row, 3].Tag = dummyno;
}
}
Fpspread1.SaveChanges();
string month = Convert.ToString(ddlMonth.SelectedItem.Text);
string year = Convert.ToString(ddlYear.SelectedItem.Text);
string dept = "";
if (chklstbranch.Items.Count > 0)
{
for (int ros = 0; ros < chklstbranch.Items.Count; ros++)
{
if (chklstbranch.Items[ros].Selected == true)
{
dept = Convert.ToString(chklstbranch.Items[ros].Text);
}
}
}
string course = Convert.ToString(Fpspread1.Sheets[0].Cells[0, 4].Text);
deptvalue = Convert.ToString(Fpspread1.Sheets[0].Cells[0, 5].Text);
string semval = Convert.ToString(Fpspread1.Sheets[0].Cells[0, 4].Tag);
string subjectname = ddlsubject.SelectedItem.Text;
string subjectcode = ddlsubject.SelectedItem.Value;
PdfTextArea ptc4 = new PdfTextArea(Fontsmall, System.Drawing.Color.Black,
new PdfArea(mydoc, 515, 0, 113, 30), System.Drawing.ContentAlignment.MiddleCenter, prinsheetno.ToString());
PdfTextArea ptc5 = new PdfTextArea(Fontsmall, System.Drawing.Color.Black,
new PdfArea(mydoc, 515, 30, 113, 20), System.Drawing.ContentAlignment.MiddleCenter, month + " " + year);
int y = 48;
PdfTextArea ptde = new PdfTextArea(fontmedium, System.Drawing.Color.Black,
new PdfArea(mydoc, 120, y, 100, 30), System.Drawing.ContentAlignment.MiddleLeft, "" + course + "");
PdfTextArea ptc = new PdfTextArea(fontmedium, System.Drawing.Color.Black,
new PdfArea(mydoc, 303, y, 350, 30), System.Drawing.ContentAlignment.MiddleLeft, "" + deptvalue + "");
PdfTextArea psem = new PdfTextArea(fontmedium, System.Drawing.Color.Black,
new PdfArea(mydoc, 544, y, 200, 30), System.Drawing.ContentAlignment.MiddleLeft, "" + semval + "");
y = y + 20;
PdfTextArea ptc1 = new PdfTextArea(fontmedium, System.Drawing.Color.Black,
new PdfArea(mydoc, 120, y, 350, 30), System.Drawing.ContentAlignment.MiddleLeft, "" + subjectname + "");
PdfTextArea ptc2 = new PdfTextArea(fontmedium, System.Drawing.Color.Black,
new PdfArea(mydoc, 544, y, 200, 30), System.Drawing.ContentAlignment.MiddleLeft, "" + subjectcode + "");
if (adddummyarray.Count > 0)
{
int rowcount = 0;
int totalcount = adddummyarray.Count;
while (totalcount > 25)
{
if (rowcount > 24)
{
prinsheetno++;
ptc4 = new PdfTextArea(Fontsmall, System.Drawing.Color.Black,
new PdfArea(mydoc, 515, 2, 113, 30), System.Drawing.ContentAlignment.MiddleCenter, prinsheetno.ToString());
}
totalcount = totalcount - 25;
table = mydoc.NewTable(Fontsmall, 13, 1, 10);
for (int row = 0; row < 13; row++)
{
string getvalue = Convert.ToString(adddummyarray[rowcount]);
table.Cell(row, 0).SetContent(getvalue);
table.Cell(row, 0).SetContentAlignment(ContentAlignment.MiddleLeft);
table.Cell(row, 0).SetCellPadding(11);
rowcount++;
}
table1 = mydoc.NewTable(Fontsmall, 12, 1, 10);
for (int row = 0; row < 12; row++)
{
string getvalue = Convert.ToString(adddummyarray[rowcount]);
table1.Cell(row, 0).SetContent(getvalue);
table1.Cell(row, 0).SetCellPadding(11);
rowcount++;
}
mypdfpage = mydoc.NewPage();
myprov_pdfpage1 = table.CreateTablePage(new Gios.Pdf.PdfArea(mydoc, 50, 150, 100, 500));
mypdfpage.Add(myprov_pdfpage1);
myprov_pdfpage1 = table1.CreateTablePage(new Gios.Pdf.PdfArea(mydoc, 340, 150, 100, 500));
mypdfpage.Add(myprov_pdfpage1);
mypdfpage.Add(ptc);
mypdfpage.Add(ptc1);
mypdfpage.Add(ptc2);
mypdfpage.Add(ptc4);
mypdfpage.Add(ptc5);
mypdfpage.Add(ptde);
mypdfpage.Add(psem);
mypdfpage.SaveToDocument();
}
bool check = false;
int subtotalcount = totalcount;
int value = 0;
mypdfpage = mydoc.NewPage();
if (subtotalcount >= 13)
{
value = subtotalcount - 13;
check = true;
}
if (check == true)
{
prinsheetno++;
ptc4 = new PdfTextArea(Fontsmall, System.Drawing.Color.Black,
new PdfArea(mydoc, 515, 2, 113, 30), System.Drawing.ContentAlignment.MiddleCenter, prinsheetno.ToString());
table = mydoc.NewTable(Fontsmall, 13, 1, 10);
for (int row = 0; row < 13; row++)
{
string getvalue = Convert.ToString(adddummyarray[rowcount]);
table.Cell(row, 0).SetContent(getvalue);
rowcount++;
}
myprov_pdfpage1 = table.CreateTablePage(new Gios.Pdf.PdfArea(mydoc, 50, 150, 100, 500));
mypdfpage.Add(myprov_pdfpage1);
}
else
{
prinsheetno++;
ptc4 = new PdfTextArea(Fontsmall, System.Drawing.Color.Black,
new PdfArea(mydoc, 515, 2, 113, 30), System.Drawing.ContentAlignment.MiddleCenter, prinsheetno.ToString());
table = mydoc.NewTable(Fontsmall, subtotalcount, 1, 10);
for (int row = 0; row < subtotalcount; row++)
{
string getvalue = Convert.ToString(adddummyarray[rowcount]);
table.Cell(row, 0).SetContent(getvalue);
rowcount++;
}
myprov_pdfpage1 = table.CreateTablePage(new Gios.Pdf.PdfArea(mydoc, 50, 150, 100, 500));
mypdfpage.Add(myprov_pdfpage1);
}
if (value != 0)
{
table1 = mydoc.NewTable(Fontsmall, value, 1, 10);
for (int row = 0; row < value; row++)
{
string getvalue = Convert.ToString(adddummyarray[rowcount]);
table1.Cell(row, 0).SetContent(getvalue);
rowcount++;
}
myprov_pdfpage1 = table1.CreateTablePage(new Gios.Pdf.PdfArea(mydoc, 340, 170, 100, 500));
mypdfpage.Add(myprov_pdfpage1);
}
mypdfpage.Add(ptc);
mypdfpage.Add(ptc1);
mypdfpage.Add(ptc2);
mypdfpage.Add(ptc4);
mypdfpage.Add(ptc5);
mypdfpage.Add(ptde);
mypdfpage.Add(psem);
mypdfpage.SaveToDocument();
string appPath = HttpContext.Current.Server.MapPath("~");
if (appPath != "")
{
string szPath = appPath + "/Report/";
string szFile = "Dummy.pdf";
mydoc.SaveToFile(szPath + szFile);
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment; filename=" + szFile);
Response.ContentType = "application/pdf";
Response.WriteFile(szPath + szFile);
}
}
}
catch
{
}
}
protected void btnupdate_Click(object sender, EventArgs e)
{
try
{
Fpspread1.SaveChanges();
bool modcheck = false;
if (Fpspread1.Sheets[0].RowCount > 0)
{
for (int row = 0; row < Fpspread1.Sheets[0].RowCount; row++)
{
int val = Convert.ToInt32(Fpspread1.Sheets[0].Cells[row, 4].Value);
if (val == 1)
{
lbldateerr.Visible = false;
string genaccid = Convert.ToString(Fpspread1.Sheets[0].Cells[Convert.ToInt32(row), 1].Tag);
string acr = txtacr.Text.ToString();
// string collcode = Convert.ToString(Fpspread1.Sheets[0].Cells[Convert.ToInt32(row), 2].Tag);
string collcode = ddlcol.SelectedItem.Value;
ddlcol.SelectedValue = collcode;
string accname = txtacc.Text.ToString();
string yearstart = txtdatestart.Text;
string yearend = txtdateend.Text;
string[] split = yearstart.Split('/');
DateTime dt = Convert.ToDateTime(split[1] + "/" + split[0] + "/" + split[2]);
split = yearend.Split('/');
DateTime dt1 = Convert.ToDateTime(split[1] + "/" + split[0] + "/" + split[2]);
accname = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(accname);
string selquery = "select * from FM_FinYearMaster where FinYearStart='" + dt.ToString("MM/dd/yyyy") + "' and FinYearPK not in('" + genaccid + "') and CollegeCode='" + collegestat + "'";
ds.Clear();
ds = d2.select_method_wo_parameter(selquery, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
lbldateerr.Visible = true;
lbldateerr.Text = "Financial Year Already Exist";
}
else if (dt == dt1)
{
lbldateerr.Visible = true;
lbldateerr.Text = "Year must be exactly one year";
}
else
{
string insquery = "update FM_FinYearMaster set FinYearAcr='" + acr + "',FinYearName='" + accname + "',FinYearStart='" + dt.ToString("MM/dd/yyyy") + "',FinYearEnd='" + dt1.ToString("MM/dd/yyyy") + "',CollegeCode='" + collegestat + "' where FinYearPK='" + genaccid + "' ";
int ins = d2.update_method_wo_parameter(insquery, "Text");
bindaccname();
bttngo_Click(sender, e);
popper1.Visible = false;
imgdiv2.Visible = true;
lbl_alerterr.Text = "Updated Sucessfully";
}
}
}
}
}
catch
{
}
}
protected void btn_go_Click(object sender, EventArgs e)
{
string collcode = Convert.ToString(ddlclg.SelectedItem.Value);
try
{
int count = 0;
for (int i = 0; i < cbl_dptname.Items.Count; i++)
{
if (cbl_dptname.Items[i].Selected == true)
{
count++;
}
}
if (count == 0)
{
lbl_alert.Text = "Please Select Atleast One Department";
lbl_alert.Visible = true;
imgdiv2.Visible = true;
hide();
return;
}
string deptcod = "";
if (cbl_dptname.Items.Count > 0)
{
for (int i = 0; i < cbl_dptname.Items.Count; i++)
{
if (cbl_dptname.Items[i].Selected == true)
{
if (deptcod == "")
{
deptcod = Convert.ToString(cbl_dptname.Items[i].Value);
}
else
{
deptcod = deptcod + "','" + Convert.ToString(cbl_dptname.Items[i].Value);
}
}
}
}
count = 0;
string sql = "";
DataView dvfil = new DataView();
Fpspread1.Sheets[0].Rows.Count = 0;
Fpspread1.Sheets[0].Columns.Count = 6;
Fpspread1.Height = 340;
Fpspread1.Width = 800;
Fpspread1.CommandBar.Visible = false;
Fpspread1.Sheets[0].RowHeader.Visible = false;
Fpspread1.Sheets[0].AutoPostBack = false;
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.White;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
Fpspread1.Sheets[0].ColumnHeader.Columns[0].Width = 75;
Fpspread1.Sheets[0].ColumnHeader.Columns[1].Width = 310;
Fpspread1.Sheets[0].ColumnHeader.Columns[2].Width = 100;
Fpspread1.Sheets[0].ColumnHeader.Columns[3].Width = 100;
Fpspread1.Sheets[0].ColumnHeader.Columns[4].Width = 100;
Fpspread1.Sheets[0].ColumnHeader.Columns[5].Width = 100;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Designation";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Available Staff";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "No.of Req Staff";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "No.of Vacancy";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].Text = "No.of Additional Req Staff";
FarPoint.Web.Spread.DoubleCellType intgrcel = new FarPoint.Web.Spread.DoubleCellType();
intgrcel.FormatString = System.Windows.Forms.MaskFormat.ExcludePromptAndLiterals.ToString();
intgrcel.MinimumValue = 0;
intgrcel.ErrorMessage = "Enter valid Required Staff";
string desigcod = d2.GetFunction(" Select desig_code from desig_master where desig_name='" + txt_search.Text.ToString() + "' and collegeCode='" + collcode + "'");
string selquery = "";
selquery = "select t.desig_code,t.dept_code,count(s.staff_code)as count from staffmaster s,stafftrans t where s.staff_code =t.staff_code and t.latestrec ='1' and resign=0 and settled =0 and isnull(Discontinue,'0') ='0' and s.college_code ='" + collcode + "' group by t.desig_code,t.dept_code";
selquery = selquery + " select DeptCode,DesigCode,No_ofPersons from VacancyMaster where Collegecode ='" + collcode + "'";
DataSet dsnew = new DataSet();
dsnew.Clear();
dsnew = d2.select_method_wo_parameter(selquery, "Text");
int sno = 1;
int rowcount = 0;
if (cbl_dptname.Items.Count > 0 && txt_dptname.Text.Trim() != "--Select--")
{
for (int st = 0; st < cbl_dptname.Items.Count; st++)
{
if (cbl_dptname.Items[st].Selected == true)
{
rowcount = 0;
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(cbl_dptname.Items[st].Text);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Font.Bold = true;
Fpspread1.Sheets[0].SpanModel.Add(Fpspread1.Sheets[0].RowCount - 1, 0, 1, 6);
string selq = "select desig_code,desig_name from desig_master where ((dept_code like '" + Convert.ToString(cbl_dptname.Items[st].Value) + ";%') or (dept_code like '%;" + Convert.ToString(cbl_dptname.Items[st].Value) + "%') or (dept_code like '%" + Convert.ToString(cbl_dptname.Items[st].Value) + "') or (dept_code='" + Convert.ToString(cbl_dptname.Items[st].Value) + "'))";
ds.Clear();
ds = d2.select_method_wo_parameter(selq, "Text");
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int ik = 0; ik < ds.Tables[0].Rows.Count; ik++)
{
if (txt_search.Text.Trim() != "")
{
if (Convert.ToString(ds.Tables[0].Rows[ik]["desig_code"]) == desigcod)
{
rowcount++;
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno++);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(txt_search.Text.Trim());
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Tag = Convert.ToString(desigcod);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Note = Convert.ToString(cbl_dptname.Items[st].Value);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Font.Name = "Book Antiqua";
if (dsnew.Tables.Count > 0 && dsnew.Tables[0].Rows.Count > 0)
{
DataView dvnew = new DataView();
dsnew.Tables[0].DefaultView.RowFilter = " dept_code='" + Convert.ToString(cbl_dptname.Items[st].Value) + "' and desig_code='" + desigcod + "'";
dvnew = dsnew.Tables[0].DefaultView;
if (dvnew.Count > 0)
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = Convert.ToString(dvnew[0]["count"]);
}
else
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = "-";
}
}
else
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = "-";
}
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Font.Name = "Book Antiqua";
if (dsnew.Tables.Count > 0 && dsnew.Tables[1].Rows.Count > 0)
{
DataView dvmynew = new DataView();
dsnew.Tables[1].DefaultView.RowFilter = " DeptCode='" + Convert.ToString(cbl_dptname.Items[st].Value) + "' and DesigCode='" + desigcod + "'";
dvmynew = dsnew.Tables[1].DefaultView;
if (dvmynew.Count > 0)
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(dvmynew[0]["No_ofPersons"]);
}
else
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = "-";
}
}
else
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = "-";
}
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Font.Name = "Book Antiqua";
Double availcount = 0;
Double noofreq = 0;
Double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text), out availcount);
Double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text), out noofreq);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = Convert.ToString(noofreq - availcount);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].CellType = intgrcel;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].Font.Name = "Book Antiqua";
}
}
else
{
for (int jk = 0; jk < cbl_desname.Items.Count; jk++)
{
if (cbl_desname.Items[jk].Selected == true)
{
if (Convert.ToString(ds.Tables[0].Rows[ik]["desig_code"]) == Convert.ToString(cbl_desname.Items[jk].Value))
{
rowcount++;
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno++);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(cbl_desname.Items[jk].Text);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Tag = Convert.ToString(cbl_desname.Items[jk].Value);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Note = Convert.ToString(cbl_dptname.Items[st].Value);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Font.Name = "Book Antiqua";
if (dsnew.Tables.Count > 0 && dsnew.Tables[0].Rows.Count > 0)
{
DataView dvnew = new DataView();
dsnew.Tables[0].DefaultView.RowFilter = " dept_code='" + Convert.ToString(cbl_dptname.Items[st].Value) + "' and desig_code='" + Convert.ToString(cbl_desname.Items[jk].Value) + "'";
dvnew = dsnew.Tables[0].DefaultView;
if (dvnew.Count > 0)
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = Convert.ToString(dvnew[0]["count"]);
}
else
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = "-";
}
}
else
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = "-";
}
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Font.Name = "Book Antiqua";
if (dsnew.Tables.Count > 0 && dsnew.Tables[1].Rows.Count > 0)
{
DataView dvmynew = new DataView();
dsnew.Tables[1].DefaultView.RowFilter = " DeptCode='" + Convert.ToString(cbl_dptname.Items[st].Value) + "' and DesigCode='" + Convert.ToString(cbl_desname.Items[jk].Value) + "'";
dvmynew = dsnew.Tables[1].DefaultView;
if (dvmynew.Count > 0)
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(dvmynew[0]["No_ofPersons"]);
}
else
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = "-";
}
}
else
{
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = "-";
}
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Font.Name = "Book Antiqua";
Double availcount = 0;
Double noofreq = 0;
Double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text), out availcount);
Double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text), out noofreq);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = Convert.ToString(noofreq - availcount);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].CellType = intgrcel;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].Font.Name = "Book Antiqua";
}
}
}
}
}
if (rowcount == 0)
{
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].RowCount - 1].Remove();
}
}
else
{
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].RowCount - 1].Remove();
}
}
}
}
for (int ii = 0; ii < Fpspread1.Sheets[0].Columns.Count; ii++)
{
Fpspread1.Sheets[0].ColumnHeader.Columns[ii].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].ColumnHeader.Columns[ii].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Columns[ii].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Columns[ii].Font.Size = FontUnit.Medium;
}
for (int kl = 0; kl < Fpspread1.Sheets[0].ColumnCount - 1; kl++)
{
Fpspread1.Sheets[0].Columns[kl].Locked = true;
}
if (Fpspread1.Sheets[0].RowCount > 1)
{
Fpspread1.Visible = true;
rptprint.Visible = true;
div1.Visible = true;
Fpspread1.Sheets[0].PageSize = Fpspread1.Sheets[0].RowCount;
Fpspread1.SaveChanges();
txt_search.Text = "";
}
else
{
txt_search.Text = "";
lbl_alert.Text = "No Records Found";
lbl_alert.Visible = true;
imgdiv2.Visible = true;
hide();
}
//}
//else
//{
// txt_search.Text = "";
// lbl_alert.Text = "No Records Found";
// lbl_alert.Visible = true;
// imgdiv2.Visible = true;
// hide();
//}
}
catch (Exception ex)
{
d2.sendErrorMail(ex, collcode, "manpower_Alter.aspx");
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
//****************************************************//
//string PageLogOut = "";
//string sess = Convert.ToString(Session["IsLogin"]);
//PageLogOut = Convert.ToString(Session["PageLogout"]);
//if (sess == "")
//{
//}
//else
//{
// if (!Request.FilePath.Contains("HRM"))
// {
// string strPreviousPage = "";
// if (Request.UrlReferrer != null)
// {
// strPreviousPage = Request.UrlReferrer.Segments[Request.UrlReferrer.Segments.Length - 1];
// }
// if (strPreviousPage == "")
// {
// string redrURI = ConfigurationManager.AppSettings["HR"].Trim();
// Response.Redirect(redrURI, false);
// return;
// }
// }
//}
//if (Session["collegecode"] == null)
//{
// string redrURI = ConfigurationManager.AppSettings["Logout"].Trim();
// Response.Redirect(redrURI, false);
// return;
//}
if (Session["collegecode"] == null)
{
Response.Redirect("~/Default.aspx");
}
if (!Request.FilePath.Contains("HRMenuIndex"))
{
string strPreviousPage = "";
if (Request.UrlReferrer != null)
{
strPreviousPage = Request.UrlReferrer.Segments[Request.UrlReferrer.Segments.Length - 1];
}
if (strPreviousPage == "")
{
Response.Redirect("~/HRMOD/HRMenuIndex.aspx");
return;
}
}
//****************************************************//
usercode = Session["usercode"].ToString();
collegecode1 = Session["collegecode"].ToString();
clgcode = collegecode1;
singleuser = Session["single_user"].ToString();
group_user = Session["group_code"].ToString();
if (!IsPostBack)
{
bindcollege();
//fairpoint();
binddepartment();
binddesignation();
Fpspread1.Visible = false;
rptprint.Visible = false;
btn_go_Click(sender, e);
}
lbl_norec.Visible = false;
Fpspread1.SaveChanges();
}
catch (Exception ex) { }
}
