protected void btnexcelsheet_Click(object sender, EventArgs e)
{
try
{
DataTable dt = DstumDAL.getExcelSheetData();
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "DSTUM");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=DSTUM Service Management.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
catch
{
}
}
protected void Addtools_Click(object sender, EventArgs e)
{
bool inset = false;
Tools tools = new Tools();
tools.toolname = txtToolname.Text.ToString();
tools.desc = txtdesc.Text.ToString();
try
{
inset = DstumDAL.insertTools(tools);
}
catch (Exception ex)
{
}
if (inset == true)
{
Response.Write(@"<script language='javascript'>window.alert('Tools Added successfully \n .');</script>");
// Response.Write(@"<script language='javascript'>alert('Artifact Added successfully \n .');</script>");
// Response.Redirect("~/Default.aspx");
DataBindGrid();
}
else
{
Response.Write(@"<script language='javascript'>alert('some error is occurs \n .');</script>");
}
}
protected void Addartifact_Click(object sender, EventArgs e)
{
bool inset = false;
Addartifact addArtifact = new Addartifact();
addArtifact.artNum = txtArtNum.Text.ToString();
addArtifact.artName = txtArtName.Text.ToString();
addArtifact.toolid = Convert.ToInt32(lbltoolid.Text);
addArtifact.createdby = Convert.ToInt32(lbluserid.Text);
addArtifact.desc = txtArtdesc.Text.ToString();
addArtifact.esthour = Convert.ToInt32(txtEsthour.Text);
user = DstumDAL.getUserdetails(Context.User.Identity.Name);
try
{
inset = DstumDAL.insertArtifact(addArtifact, user.userid);
}
catch (Exception ex)
{
}
if (inset == true)
{
Response.Write(@"<script language='javascript'>window.alert('Artifact Added successfully \n .');window.location='Default.aspx';</script>");
// Response.Write(@"<script language='javascript'>alert('Artifact Added successfully \n .');</script>");
// Response.Redirect("~/Default.aspx");
// UpdatePanel1.Update();
}
else
{
Response.Write(@"<script language='javascript'>alert('some error is occurs \n .');</script>");
}
}
protected void AddUser_Click(object sender, EventArgs e)
{
bool inset = false;
User user = new User();
user.name = txtUsername.Text.ToString();
user.username = txtUserid.Text.ToString();
user.role = ddlrole.SelectedValue.ToString();
user.toolid = Convert.ToInt32(ddltools.SelectedValue.ToString());
try
{
inset = DstumDAL.insertUser(user);
}
catch (Exception ex)
{
}
if (inset == true)
{
txtUserid.Text = " ";
txtUsername.Text = " ";
GridViewdataBind();
}
else
{
//Response.Write(@"<script language='javascript'>alert('some error is occurs \n ');</script>");
}
}
public void DataBindGrid()
{
DataSet ds = DstumDAL.toolList();
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if ((e.Row.RowState & DataControlRowState.Edit) > 0)
{
DropDownList ddList = (DropDownList)e.Row.FindControl("ddlartname");
//bind dropdown-list
// DropDownList ddlrole1 = (DropDownList)e.Row.FindControl("ddlrole1");
Label dr = (Label)e.Row.FindControl("lblaid12");
if (dr.Text.ToString().Equals(""))
{
String sCommand1_subbu1 = "select 'Meeting/KT/Other' as ArtifactName ,'-1' as aid";
using (SqlDataAdapter adapterSubbu1 = new SqlDataAdapter(sCommand1_subbu1, connString1))
{
DataSet ds = new DataSet();
adapterSubbu1.Fill(ds);
ddList.DataSource = ds;
ddList.DataTextField = "ArtifactName";
ddList.DataValueField = "AID";
ddList.DataBind();
conn.Close();
ddList.SelectedValue = dr.Text.ToString();
}
}
else
{
User user2 = DstumDAL.getUserdetails(Context.User.Identity.Name);
String sCommand1_subbu1 = "select * from artifact where createdfor=@userid";
// DataTable dataSubbu1 = new DataTable();
using (SqlDataAdapter adapterSubbu1 = new SqlDataAdapter(sCommand1_subbu1, connString1))
{
adapterSubbu1.SelectCommand.Parameters.AddWithValue("@userid", user2.userid);
DataSet ds = new DataSet();
adapterSubbu1.Fill(ds);
ddList.DataSource = ds;
ddList.DataTextField = "ArtifactName";
ddList.DataValueField = "AID";
ddList.DataBind();
conn.Close();
ddList.SelectedValue = dr.Text.ToString();
}
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataSet ds = DstumDAL.toolList();
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
user = DstumDAL.getUserdetails(Context.User.Identity.Name);
Tools tools = DstumDAL.getToolDetails(user.toolid);
lblusername.Text = user.username;
lbluserid.Text = user.userid.ToString();
lbltoolid.Text = user.toolid.ToString();
lblToolName.Text = tools.toolname;
DataBindGrid();
}
}
private void DataBindGrid()
{
user = DstumDAL.getUserdetails(Context.User.Identity.Name);
String sCommand1_subbu1 = " select ar.*,est_hour-lock_hour as remhour,tr.toolname from artifact ar inner join tools tr on ar.toolid=tr.toolID where createdfor= @userid and status='In Progress' or status= 'pending'";
// DataTable dataSubbu1 = new DataTable();
SqlDataAdapter adapterSubbu1 = new SqlDataAdapter(sCommand1_subbu1, connString1);
adapterSubbu1.SelectCommand.Parameters.AddWithValue("@userid", user.userid);
DataSet ds = new DataSet();
adapterSubbu1.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
private void DataBindGrid()
{
user = DstumDAL.getUserdetails(Context.User.Identity.Name);
String sCommand1_subbu1 = "select ds.DID, replace(convert(NVARCHAR, ds.todaydate, 106), ' ', '/') todaydate, ds.userid, ds.aid, ds.Todayhour, ds.yesterdayhour, ds.[desc], us.Name, CASE WHEN Cast(ar.[ArtifactName] AS varchar) is null THEN 'Meeting/KT/Other' ELSE Cast(ar.[ArtifactName] AS varchar) END AS ArtifactName, CASE WHEN Cast(ar.est_hour AS varchar) is null THEN 0 ELSE Cast(ar.est_hour AS varchar) END AS est_hour, CASE WHEN Cast(ar.est_hour AS varchar) is null THEN 0 ELSE Cast(ar.est_hour-ar.lock_hour AS varchar) END AS remhour, ar.status from dstum ds left outer join [user] us on ds.userid = us.userid left outer join artifact ar on ar.AID=ds.AID where us.userID=@userid";
// DataTable dataSubbu1 = new DataTable();
using (SqlDataAdapter adapterSubbu1 = new SqlDataAdapter(sCommand1_subbu1, connString1))
{
adapterSubbu1.SelectCommand.Parameters.AddWithValue("@userid", user.userid);
DataSet ds = new DataSet();
adapterSubbu1.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
conn.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
bool isuser = DstumDAL.isUserExist(Context.User.Identity.Name);
if (!isuser)
{
Response.Redirect("~/ContactToAdmin.aspx");
}
else
{
user = DstumDAL.getUserdetails(Context.User.Identity.Name);
if (user.role.ToString().Equals("User"))
{
divbutton.Visible = false;
}
}
if (!IsPostBack)
{
toolDropdownbind();
DataBindGrid();
}
}
public void toolDropdownbind()
{
User user1 = DstumDAL.getUserdetails(Context.User.Identity.Name);
String sCommand1_subbu1 = "select ar.AID,ar.ArtifactName from artifact ar where createdfor=@userid ";
sCommand1_subbu1 += " union select '-1' as aid, 'Meeting/KT/Other' as ArtifactName ";
// DataTable dataSubbu1 = new DataTable();
SqlDataAdapter adapterSubbu1 = new SqlDataAdapter(sCommand1_subbu1, connString1);
adapterSubbu1.SelectCommand.Parameters.AddWithValue("@userid", user1.userid);
DataSet ds = new DataSet();
adapterSubbu1.Fill(ds);
ddlairtifact.DataSource = ds;
ddlairtifact.DataTextField = "ArtifactName";
ddlairtifact.DataValueField = "AID";
ddlairtifact.DataBind();
conn.Close();
}
protected void btnLockHour_Click(object sender, EventArgs e)
{
bool inset = false;
DSTUM dstum = new DSTUM();
User user1 = DstumDAL.getUserdetails(Context.User.Identity.Name);
dstum.userid = user1.userid;
dstum.Todaydate = DateTime.Now.ToString("yyyy/MM/dd");;
dstum.aid = Convert.ToInt32(ddlairtifact.SelectedValue.ToString());
dstum.Todayhour = Convert.ToInt32(txttodayhour.Text.ToString());
dstum.yesterdayHour = Convert.ToInt32(txtYesterdayhour.Text.ToString());
dstum.desc = txtdesc.Text.ToString();
try
{
inset = DstumDAL.lockHour(dstum);
}
catch (Exception ex)
{
}
if (inset == true)
{
Response.Write(@"<script language='javascript'>window.alert('Lock Your hour successfully \n ');</script>");
txtdesc.Text = "";
txttodayhour.Text = "";
txtYesterdayhour.Text = "";
DataBindGrid();
}
else
{
Response.Write(@"<script language='javascript'>alert('some error is occurs \n ');</script>");
}
}
protected void load_excel_Click(object sender, EventArgs e)
{
List <ArtifactExcel> listArtifact = new List <ArtifactExcel>();
//if File is not selected then return
if (Request.Files["FileUpload1"].ContentLength <= 0)
{
return;
}
try
{
//Get the file extension
string fileExtension = Path.GetExtension(Request.Files["FileUpload1"].FileName);
//If file is not in excel format then return
if (fileExtension != ".xls" && fileExtension != ".xlsx")
{
return;
}
//Get the File name and create new path to save it on server
string fileLocation = Server.MapPath("\\") + Request.Files["FileUpload1"].FileName;
//if the File is exist on serevr then delete it
if (File.Exists(fileLocation))
{
File.Delete(fileLocation);
}
//save the file lon the server before loading
Request.Files["FileUpload1"].SaveAs(fileLocation);
//Create the QueryString for differnt version of fexcel file
switch (fileExtension)
{
case ".xls": //Excel 1997-2003
oledbConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties = 'Excel 8.0;HDR=Yes;IMEX=1'; ";
break;
case ".xlsx": //Excel 2007-2010
oledbConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileLocation + ";Extended Properties = 'Excel 12.0;HDR=YES;IMEX=1;'; ";
break;
}
using (OleDbConnection connection = new OleDbConnection(oledbConn))
{
OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ArtifactExcel artifact = new ArtifactExcel();
artifact.artNum = reader[0].ToString();;
artifact.artName = reader[2].ToString();;
artifact.esthour = Convert.ToInt32(reader[4].ToString());
artifact.desc = reader[3].ToString();;
artifact.createdby = reader[1].ToString();
listArtifact.Add(artifact);
}
}
reader.Close();
for (int i = 0; i < listArtifact.Count; i++)
{
int id = DstumDAL.getIdfromName(listArtifact[i].createdby);
int toolid = DstumDAL.gettoolIdfromName(listArtifact[i].createdby);
listArtifact[i].id = id;
listArtifact[i].toolid = toolid;
}
bool isArtifactAdded = DstumDAL.addArtifactFromExcel(listArtifact);
if (isArtifactAdded)
{
Response.Redirect("~/Default.aspx");
}
}
/// GridView1.DataSource = ds;
GridView1.DataBind();
//Bind the datatable to the Grid
File.Delete(fileLocation);
}
catch (Exception ex)
{
}
//Delete the excel file from the server
}
protected void btntodayreport_Click(object sender, EventArgs e)
{
Document document = new Document(PageSize.A4, 88f, 88f, 10f, 10f);
List <Report> listReport = DstumDAL.getReportDetails();
using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())
{
PdfWriter writer = PdfWriter.GetInstance(document, memoryStream);
Phrase phrase = null;
PdfPCell cell = null;
PdfPTable table = null;
Color color = null;
document.Open();
//Header Table
table = new PdfPTable(2);
table.TotalWidth = 500f;
table.LockedWidth = true;
table.SetWidths(new float[] { 0.3f, 0.7f });
//Company Logo
color = new Color(System.Drawing.ColorTranslator.FromHtml("#00468b"));
DrawLine(writer, 25f, document.Top - 29f, document.PageSize.Width - 25f, document.Top - 29f, color);
// DrawLine(writer, 25f, document.Top - 30f, document.PageSize.Width - 25f, document.Top - 30f, color);
document.Add(table);
//Company Name and Address
//Separater Line
table = new PdfPTable(2);
table.HorizontalAlignment = Element.ALIGN_LEFT;
table.SetWidths(new float[] { 0.3f, 1f });
table.SpacingBefore = 50f;
//Employee Details
cell = PhraseCell(new Phrase("DSTUM Report for " + DateTime.Now.ToString("dd/MM/yyyy"), FontFactory.GetFont("Arial", 18, Font.BOLD, new Color(0, 70, 139))), PdfPCell.ALIGN_CENTER);
cell.Colspan = 3;
table.AddCell(cell);
cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
cell.Colspan = 3;
cell.PaddingBottom = 30f;
table.AddCell(cell);
document.Add(table);
// data table
table = new PdfPTable(5);
table.AddCell(PhraseCell1(new Phrase(" Name ", FontFactory.GetFont("Arial", 10, Font.BOLD, new Color(0, 70, 139))), PdfPCell.ALIGN_CENTER));
table.AddCell(PhraseCell1(new Phrase(" Tools ", FontFactory.GetFont("Arial", 10, Font.BOLD, new Color(0, 70, 139))), PdfPCell.ALIGN_CENTER));
table.AddCell(PhraseCell1(new Phrase(" Attendance ", FontFactory.GetFont("Arial", 10, Font.BOLD, new Color(0, 70, 139))), PdfPCell.ALIGN_CENTER));
table.AddCell(PhraseCell1(new Phrase(" Availibility ", FontFactory.GetFont("Arial", 10, Font.BOLD, new Color(0, 70, 139))), PdfPCell.ALIGN_CENTER));
table.AddCell(PhraseCell1(new Phrase(" mood ", FontFactory.GetFont("Arial", 10, Font.BOLD, new Color(0, 70, 139))), PdfPCell.ALIGN_CENTER));
for (int i = 0; i < listReport.Count; i++)
{
table.AddCell(PhraseCell1(new Phrase(" " + listReport[i].name, FontFactory.GetFont("Arial", 10, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_CENTER));
table.AddCell(PhraseCell1(new Phrase(" " + listReport[i].tools, FontFactory.GetFont("Arial", 10, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_CENTER));
table.AddCell(PhraseCell1(new Phrase(" " + listReport[i].attendance, FontFactory.GetFont("Arial", 10, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_CENTER));
if (listReport[i].availability.ToString().Equals("Leave"))
{
table.AddCell(PhraseCell1(new Phrase(" " + listReport[i].availability, FontFactory.GetFont("Arial", 10, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_CENTER));
}
else if (Convert.ToInt32(listReport[i].availability) < 100)
{
table.AddCell(PhraseCell1(new Phrase(" " + (100 - Convert.ToInt32(listReport[i].availability)) + "%", FontFactory.GetFont("Arial", 10, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_CENTER));
}
else
{
table.AddCell(PhraseCell1(new Phrase("0%", FontFactory.GetFont("Arial", 10, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_CENTER));
}
if (listReport[i].mood.ToString().Equals("green"))
{
cell = ImageCell(System.Web.HttpContext.Current.Server.MapPath("~/img/green.jpg"), 30f, PdfPCell.ALIGN_CENTER);
table.AddCell(cell);
}
else if (listReport[i].mood.ToString().Equals("red"))
{
cell = ImageCell(System.Web.HttpContext.Current.Server.MapPath("~/img/red.jpg"), 30f, PdfPCell.ALIGN_CENTER);
table.AddCell(cell);
}
else if (listReport[i].mood.ToString().Equals("orange"))
{
cell = ImageCell(System.Web.HttpContext.Current.Server.MapPath("~/img/orange.jpg"), 30f, PdfPCell.ALIGN_CENTER);
table.AddCell(cell);
}
else
{
cell = ImageCell(System.Web.HttpContext.Current.Server.MapPath("~/img/red.jpg"), 30f, PdfPCell.ALIGN_CENTER);
table.AddCell(cell);
}
// table.AddCell(PhraseCell1(new Phrase(" " + listReport[i].mood, FontFactory.GetFont("Arial", 10, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_CENTER));
}
document.Add(table);
document.Close();
byte[] bytes = memoryStream.ToArray();
memoryStream.Close();
Response.Clear();
Response.ContentType = "application/pdf";
Response.AddHeader("Content-Disposition", "attachment; filename=DSTUM " + DateTime.Now.ToString("dd/MM/yyyy") + ".pdf");
Response.ContentType = "application/pdf";
Response.Buffer = true;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.BinaryWrite(bytes);
Response.End();
Response.Close();
}
}
