//14/08/2010 - LOB Report - Daily
public string AppendActualUserDataToExcelSummary(int sheet)
{
BA_Timesheet1 batimesheet = new BA_Timesheet1();
DataSet ds = new DataSet();
string company = Session["PSEName"].ToString();
string StartDt = txtStartDate.Text;
string EndDt = txtEndDate.Text;
string company1 = ddlCompUsers.SelectedValue;
StringBuilder myHtml = new StringBuilder();
try
{
int workSheet = sheet;
if (workSheet == 1)
{
ds = batimesheet.GetcompinGrid(StartDt, EndDt, ddlcompany.SelectedValue, ddlCompUsers.SelectedValue.ToString());
}
string[] dt = Convert.ToDateTime(txtStartDate.Text).ToLongDateString().ToString().Split(',');
//Report Title
myHtml.Append("<ss:Row ss:Index=\"2\">");//Row
myHtml.Append("<Cell ss:Index=\"2\" ss:MergeAcross=\"" + (ds.Tables[0].Columns.Count - 1) + "\" ss:StyleID=\"s100\"><Data ss:Type=\"String\">Time sheet Reports :" + dt[0] + " - " + dt[1] + ", " + dt[2] + " </Data></Cell>");
myHtml.Append("</ss:Row>");
myHtml.Append("<ss:Row ss:Index=\"3\">");
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
myHtml.Append("<Cell ss:Index=\"" + (i + 2) + "\" ss:StyleID=\"s101\"><Data ss:Type=\"String\">" + ds.Tables[0].Columns[i].ColumnName.ToString() + "</Data></Cell>");
}
myHtml.Append("</ss:Row>");
if (ds.Tables[0] != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myHtml.Append("<ss:Row ss:Index=\"" + (4 + i).ToString() + "\">");
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
myHtml.Append("<ss:Cell ss:Index=\"" + (j + 2) + "\" ss:StyleID=\"s41\"><ss:Data ss:Type=\"String\">" + ds.Tables[0].Rows[i][j].ToString() + "</ss:Data></ss:Cell>");
}
myHtml.Append("</ss:Row>");
}
}
}
}
catch (Exception Ex)
{
//lblMessage.Text = Ex.Message.ToString();
}
finally
{
}
return(myHtml.ToString());
}
protected void ddlcompany_SelectedIndexChanged(object sender, EventArgs e)
{
BA_Timesheet1 batine = new BA_Timesheet1();
DataSet ds = new DataSet();
string id = ddlcompany.SelectedItem.Text;
ds = batine.GetUsers(id);
ddlCompUsers.DataSource = ds;
ddlCompUsers.DataTextField = "Name";
ddlCompUsers.DataValueField = "ConsultantId";
ddlCompUsers.DataBind();
ddlCompUsers.Items.Insert(0, new ListItem("--Select--", "--Select--"));
}
protected void Page_Load(object sender, EventArgs e)
{
this.Master.ErrorMessage = this.Title;
if (!Page.IsPostBack)
{
BA_Timesheet1 batimesheet = new BA_Timesheet1();
DataSet ds1 = new DataSet();
ds1 = batimesheet.GetComList();
ddlcompany.DataSource = ds1.Tables[0];
ddlcompany.DataTextField = "CompanyName";
ddlcompany.DataValueField = "CompanyName";
ddlcompany.DataBind();
ddlcompany.Items.Insert(0, new ListItem("--------------Select----------------", "--Select--"));
}
}
protected void ddlCompUsers_SelectedIndexChanged1(object sender, EventArgs e)
{
pnlGrid.Visible = true;
gridComp.Visible = true;
BA_Timesheet1 batine = new BA_Timesheet1();
DataSet ds = new DataSet();
string id1 = ddlCompUsers.SelectedValue;
ds = batine.GetTimeSheetInfo(id1);
gridComp.DataSource = ds;
gridComp.DataBind();
ddlCompday.DataSource = ds;
ddlCompday.DataTextField = "day";
ddlCompday.DataValueField = "day";
ddlCompday.DataBind();
ddlCompday.Items.Insert(0, new ListItem("--Select--", "--Select--"));
}
protected void btnComp_Click(object sender, EventArgs e)
{
string company = ddlcompany.SelectedItem.ToString();
BA_Timesheet1 batine = new BA_Timesheet1();
string x = tsTimeIn.Hour.ToString() + ":" + tsTimeIn.Minute.ToString() + tsTimeIn.AmPm;
string y = tsTimeOut.Hour.ToString() + ":" + tsTimeOut.Minute.ToString() + tsTimeOut.AmPm;
TimeSpan timediff = Convert.ToDateTime(y) - Convert.ToDateTime(x);
string timedif = timediff.ToString();
string[] ar = timedif.Split(':');
string tt = string.Empty;
int cnt = 0;
foreach (string t in ar)
{
if (cnt < 2)
{
tt = tt + t.ToString() + '.';
cnt++;
}
}
tt = tt.Remove(tt.Length - 1, 1);
string totalwork = tt.ToString();
string d = ddlCompday.SelectedItem.ToString();
string sid = ddlCompUsers.SelectedValue;
string worktype = ddlCompDayType.SelectedItem.ToString();
if (worktype == "Working")
{
batine.InsertTimeSheet(x, y, d, sid, totalwork, worktype, company);
}
else if (worktype == "Holiday" || worktype == "Leave")
{
batine.InsertTimeSheet(x, y, d, sid, totalwork, worktype, company);
}
DataSet ds = new DataSet();
string id1 = ddlCompUsers.SelectedValue;
ds = batine.GetTimeSheetInfo(id1);
gridComp.DataSource = ds;
gridComp.DataBind();
}
protected void TimeSheet_ActiveTabChanged(object sender, EventArgs e)
{
gridComp.Visible = false;
if (TimeSheet.ActiveTabIndex == 0)
{
if (ddlCompUsers.SelectedIndex != 0 && ddlcompany.SelectedIndex != 0)
{
pnlGrid.Visible = true;
gridComp.Visible = true;
BA_Timesheet1 batine = new BA_Timesheet1();
DataSet ds = new DataSet();
string id1 = ddlCompUsers.SelectedValue;
ds = batine.GetTimeSheetInfo(id1);
gridComp.DataSource = ds;
gridComp.DataBind();
ddlCompday.DataSource = ds;
ddlCompday.DataTextField = "day";
ddlCompday.DataValueField = "day";
ddlCompday.DataBind();
ddlCompday.Items.Insert(0, new ListItem("--Select--", "--Select--"));
}
}
else
{
if (ddlusers.SelectedIndex != 0)
{
pnlGrid.Visible = true;
gridComp.Visible = true;
BA_Timesheet1 batine = new BA_Timesheet1();
DataSet ds = new DataSet();
string id1 = ddlusers.SelectedValue;
ds = batine.GetTimeSheetInfo(id1);
gridComp.DataSource = ds;
gridComp.DataBind();
ddlusersday.DataSource = ds;
ddlusersday.DataTextField = "day";
ddlusersday.DataValueField = "day";
ddlusersday.DataBind();
ddlusersday.Items.Insert(0, new ListItem("--Select--", "--Select--"));
}
}
}
protected void ddlusers_SelectedIndexChanged(object sender, EventArgs e)
{
pnlGrid.Visible = true;
gridComp.Visible = true;
BA_Timesheet1 batine = new BA_Timesheet1();
DataSet ds = new DataSet();
string id1 = ddlusers.SelectedValue;
ds = batine.GetTimeSheetInfo(id1);
ViewState["CompName"] = ds.Tables[0].Rows[0]["CompanyName"].ToString();
gridComp.DataSource = ds;
gridComp.DataBind();
ddlusersday.DataSource = ds;
ddlusersday.DataTextField = "day";
ddlusersday.DataValueField = "day";
ddlusersday.DataBind();
ddlusersday.Items.Insert(0, new ListItem("--Select--", "--Select--"));
}
protected void ddlcompany_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlcompany.SelectedIndex != 0)
{
BA_Timesheet1 batimesheet = new BA_Timesheet1();
DataSet ds1 = new DataSet();
id = ddlcompany.SelectedItem.Text;
ds1 = batimesheet.GetUsers(id);
ddlCompUsers.DataSource = ds1.Tables[0];
ddlCompUsers.DataTextField = "Name";
ddlCompUsers.DataValueField = "Consultantid";
ddlCompUsers.DataBind();
ddlCompUsers.Items.Insert(0, new ListItem("--------------Select--------------", "Select"));
ddlCompUsers.Items.Insert(1, new ListItem("All", "All"));
}
else
{
ddlCompUsers.Items.Clear();
}
}
protected void Page_Load(object sender, EventArgs e)
{
this.Master.ErrorMessage = this.Title;
if (!Page.IsPostBack)
{
BA_Timesheet1 batime = new BA_Timesheet1();
DataSet ds = new DataSet();
ds = batime.GetComList();
ddlcompany.DataSource = ds.Tables[0];
ddlcompany.DataTextField = "CompanyName";
ddlcompany.DataValueField = "CompanyName";
ddlcompany.DataBind();
ddlcompany.Items.Insert(0, new ListItem("--Select--", "--Select--"));
ddlusers.DataSource = ds.Tables[1];
ddlusers.DataTextField = "Name";
ddlusers.DataValueField = "ConsultantId";
ddlusers.DataBind();
ddlusers.Items.Insert(0, new ListItem("--Select--", "--Select--"));
pnlGrid.Visible = false;
}
}
//protected void btnImport_Click1(object sender, EventArgs e)
//{
// //try
// //{
// // BA_Timesheet1 batimesheet = new BA_Timesheet1();
// // DataSet ds = new DataSet();
// // string company = Session["PSEName"].ToString();
// // string StartDt = txtStartDate.Text;
// // string EndDt = txtEndDate.Text;
// // string company1 = ddlCompUsers.SelectedValue;
// // ds = batimesheet.GetcompinGrid(StartDt, EndDt, ddlcompany.SelectedValue, ddlCompUsers.SelectedValue.ToString());
// // string filename = "Time Sheet Report from '" + txtStartDate.Text + "' to '" + txtEndDate.Text + "'.xls";
// // string excelHeader = "Time Sheet Report from '" + txtStartDate.Text + "' to '" + txtEndDate.Text + "'";
// // HttpContext.Current.Response.Clear();
// // HttpContext.Current.Response.ClearContent();
// // HttpContext.Current.Response.ClearHeaders();
// // HttpContext.Current.Response.Buffer = true;
// // HttpContext.Current.Response.ContentType = "application/ms-excel";
// // HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
// // HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
// // HttpContext.Current.Response.Charset = "utf-8";
// // HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
// // //sets font
// // //HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
// // //HttpContext.Current.Response.Write("<BR><BR><BR>");
// // //sets the table border, cell spacing, border color, font of the text, background, foreground, font height
// // HttpContext.Current.Response.Write("<table style='height:200px;width:100px;'><tr><td style='width:200px;height:100px;'></td></tr><tr><td style='width:200px;'></td><td><Table border='1' borderColor='#000000' style='padding-left:300px'> <TR><td align='center' colspan='" + ds.Tables[0].Columns.Count + "' style='height:40px; font-weight: bold; font-size: 16px; color:black; background-color:Tan;vertical-align:middle;'>" + excelHeader.ToUpper() + "</td></tr><tr>");
// // //Getting Dataset's column headers
// // int columnscount = ds.Tables[0].Columns.Count;
// // for (int j = 0; j < columnscount; j++)
// // { //write in new column
// // HttpContext.Current.Response.Write("<Td style='height:25px; font-weight: bold; font-size: 14px; color:black; background-color:PaleGoldenrod;vertical-align:left;'>");
// // //Get column headers and make it as bold in excel columns
// // HttpContext.Current.Response.Write("<B>");
// // HttpContext.Current.Response.Write(ds.Tables[0].Columns[j].ColumnName.ToString());
// // HttpContext.Current.Response.Write("</B>");
// // HttpContext.Current.Response.Write("</Td>");
// // }
// // HttpContext.Current.Response.Write("</TR>");
// // foreach (DataRow row in ds.Tables[0].Rows)
// // {
// // //write in new row
// // HttpContext.Current.Response.Write("<TR >");
// // for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
// // {
// // HttpContext.Current.Response.Write("<Td style='height:22px; font-size: 14px; background-color:white; color:black; vertical-align:left;'>");
// // HttpContext.Current.Response.Write(row[i].ToString());
// // HttpContext.Current.Response.Write("</Td>");
// // }
// // HttpContext.Current.Response.Write("</TR>");
// // }
// // HttpContext.Current.Response.Write("</Table></td></tr></table>");
// // HttpContext.Current.Response.Write("</font>");
// // HttpContext.Current.Response.Flush();
// // HttpContext.Current.Response.End();
// //}
// //catch (Exception ex)
// //{
// //}
//}
protected void btnImport_Click1(object sender, EventArgs e)
{
try
{
BA_Timesheet1 batimesheet = new BA_Timesheet1();
DataSet ds = new DataSet();
string company = Session["PSEName"].ToString();
string StartDt = txtStartDate.Text;
string EndDt = txtEndDate.Text;
string company1 = ddlCompUsers.SelectedValue;
ds = batimesheet.GetcompinGrid(StartDt, EndDt, ddlcompany.SelectedValue, ddlCompUsers.SelectedValue.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
ViewState["ds"] = ds;
string fileName = "Time_Sheet_ReportOn" + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Year.ToString() + ".xls";
string fName = Server.MapPath(@"Backup\") + fileName;
string newFileName = fName;
string oldFileName = Server.MapPath(@"Template\TimeSheetReport.xls");
File.Copy(oldFileName, newFileName, true);
//openExcelDocument(newFileName);
//File creation
//lblMessage.Text = "Process Started...";
addData(newFileName);
Response.Redirect("dummy.aspx?fName=" + fileName, false);
//HttpContext.Current.ApplicationInstance.CompleteRequest();
Response.Flush();
}
else
{
// lblSubmitMesag.Text = "No Time Sheets are available";
}
}
catch (Exception exp)
{
//lblMessage.Text = exp.Message.ToString();
}
}
protected void btnComp_Click(object sender, EventArgs e)
{
try
{
BA_Timesheet1 batimesheet = new BA_Timesheet1();
DataSet ds = new DataSet();
string company = Session["PSEName"].ToString();
string StartDt = txtStartDate.Text;
string EndDt = txtEndDate.Text;
string company1 = ddlCompUsers.SelectedItem.Text;
ds = batimesheet.GetcompinGrid(StartDt, EndDt, ddlcompany.SelectedValue, ddlCompUsers.SelectedValue.ToString());
// lblPageInfo.Text=ds.Tables[0].Rows.Count.ToString();
gvReports.DataSource = ds.Tables[0];
gvReports.DataBind();
if (ds.Tables[0].Rows.Count > 0)
{
pnlSub.Visible = false;
btnImport.Visible = true;
pnlGrid.Visible = true;
int tot = 0;
tot = tot + Convert.ToInt32(ds.Tables[0].Rows[0][8]);
//lblSum.Text = tot.ToString();
lblTotalhr.Text = ds.Tables[0].Rows[0][4].ToString();
lblTotAmount.Text = tot.ToString();
}
else
{
pnlSub.Visible = true;
btnImport.Visible = false;
pnlGrid.Visible = false;
}
}
catch (Exception ex)
{
}
}