public static bool sendMarieApprovalEmail(int month, int year, Employee emp, int grantID) { return true; }
public static double updateDailyNonGrantHours(string cellID, Employee emp, string hours) { int month = System.Convert.ToInt32(cellID.Substring(0, 2)); month--; int day = System.Convert.ToInt32(cellID.Substring(2, 2)); List<TimeEntry> timeEntries = (List<TimeEntry>)HttpContext.Current.Session["TimeEntries"]; if (emp != null) { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString); conn.Open(); OleDbCommand comm = new OleDbCommand(); comm.Connection = conn; comm.CommandType = CommandType.Text; comm.CommandText = "select * from TimeEntry where EmpID=" + emp.ID.ToString() + " and MonthNumber=" + month.ToString() + " and DayNumber=" + day.ToString() + " and GrantID=52"; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; DataSet set = new DataSet(); try { adapter.Fill(set); } catch (System.Exception ex) { return 0; } if (set.Tables != null && set.Tables[0].Rows.Count > 0) { var te = (from tim in timeEntries where tim.empID == emp.ID && tim.grantID == 52 && tim.dayNumber == day select tim).SingleOrDefault(); if (te != null) { conn.Close(); te.grantHours = System.Convert.ToDouble(hours); bool b = updateTimeEntry(te, comm, conn); conn.Close(); return timeEntries.Sum(t=>t.grantHours); } } else { TimeEntry te = new TimeEntry(); te.grantHours = System.Convert.ToDouble(hours); te.grantID = 52; //ID for non grant te.empID = emp.ID; te.monthNumber = month; te.dayNumber = day; te.yearNumber = System.Convert.ToInt32(cellID.Substring(4, 4)); conn.Close(); addNewTimeEntry(te, comm, conn); conn.Close(); timeEntries.Add(te); HttpContext.Current.Session["TimeEntries"] = timeEntries; } } return timeEntries.Sum(t=>t.grantHours); }
public static List<TimeEntry> GetEmployeeTimeEntriesForApproval(Employee emp, int month, int year, int grantID) { if (emp != null) { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString); OleDbCommand comm = new OleDbCommand(); comm.Connection = conn; comm.CommandType = CommandType.Text; // comm.CommandText = "select * from TimeEntry where EmpID=" + emp.ID.ToString() + " and MonthNumber=" + month.ToString() + // " and (GrantID=" + grantID.ToString() + " or 52 or 53)"; comm.CommandText = "SELECT * FROM TimeEntry WHERE (((TimeEntry.GrantID) In (" + grantID.ToString() + ",52,53)) AND ((TimeEntry.EmpID)=" + emp.ID.ToString(); comm.CommandText += ") AND ((TimeEntry.MonthNumber)=" + month.ToString() + ")"; comm.CommandText += " AND ((TimeEntry.YearNumber)=" + year.ToString() + "));"; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; DataSet set = new DataSet(); try { adapter.Fill(set); } catch (System.Exception ex) { return null; } List<TimeEntry> timeEntries = new List<TimeEntry>(); List<Grant> selGrants = new List<Grant>(); if (set.Tables != null && set.Tables[0].Rows.Count > 0) { foreach (DataRow dr in set.Tables[0].Rows) { TimeEntry te = new TimeEntry(); te.ID = (int)dr[0]; te.monthNumber = (int)dr[1]; te.dayNumber = (int)dr[2]; te.yearNumber = (int)dr[3]; te.grantID = (int)dr[4]; te.grantHours = (double)dr[5]; te.empID = (int)dr[6]; if (dr[7] != DBNull.Value) { te.supervisorID = (int)dr[7]; } timeEntries.Add(te); } } HttpContext.Current.Session["TimeEntries"] = timeEntries; GrantMonth.status stat = checkStatus(emp, month, year, conn); HttpContext.Current.Session["GrantStatus"] = stat; conn.Close(); return timeEntries; } return null; }
public static Employee getSupervisor(int supID, OleDbConnection conn) { OleDbCommand comm = new OleDbCommand(); comm.Connection = conn; if (conn.State != ConnectionState.Open) { conn.Open(); } string str = "select * from EmployeeList where ID=" + supID.ToString(); comm.CommandText = str; DataSet set = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; try { adapter.Fill(set); } catch (System.Exception ex) { return null; } DataRow dr = set.Tables[0].Rows[0]; Employee sup = new Employee(); sup.ID = (int)dr[0]; sup.lastName = dr[2].ToString(); sup.firstName = dr[3].ToString(); sup.emailAddress = dr[5].ToString(); conn.Close(); return sup; }
protected bool updateDatabase(Employee emp) { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString); conn.Open(); string scomm = "update EmployeeList set EmployeeNum=" + emp.EmpNum + ", LastName='" + emp.lastName + "', "; scomm += " FirstName='" + emp.firstName + "', JobTitle='" + emp.jobTitle + "', EmailAddress='" + emp.emailAddress + "', "; scomm += " registered=" + emp.registered.ToString() + ", manager=" + emp.manager.ToString(); scomm += ", DefaultSupervisor=" + emp.defaultSupervisor + " where ID=" + emp.ID.ToString(); OleDbCommand comm = new OleDbCommand(scomm, conn); try { comm.ExecuteNonQuery(); } catch (System.Exception e) { return false; } return true; }
private static Employee populateEmployee(DataRow dr) { Employee e = new Employee(); e.ID = (int)dr[0]; e.EmpNum = dr[1].ToString(); e.lastName = dr[2].ToString(); e.firstName = dr[3].ToString(); e.jobTitle = dr[4].ToString(); e.emailAddress = dr[5].ToString(); e.registered = (bool)dr[7]; e.manager = (bool)dr[8]; return e; }
public static string formulateApprovalForGM(int month, int year, Employee emp, Grant g, Employee manager) { StringBuilder sb = new StringBuilder(); sb.Append("<html><body><strong>Hi " + manager.firstName + "</strong><p />"); sb.Append(emp.firstName + " " + emp.lastName + " has been approved for the " + g.grantTitle + " grant.<br />"); sb.Append("To see the grant entries for " + emp.firstName + " please click on the following link:<br /><a href="); sb.Append("http://www.mid-state.net/GrantApplication/Default.aspx?Review=true&ID=" + manager.ID.ToString() + "&Employee=" + emp.ID.ToString()); sb.Append( "&month=" + month.ToString() + "&Year=" + year.ToString() + "&GrantID=" +g.ID.ToString()); sb.Append(">Review Time Enties</a><p />Thank you,<br />"); sb.Append("<strong>The grant allocations team.</strong></body></html>"); return sb.ToString(); }
protected Employee GatherUpTheFields() { if (String.IsNullOrEmpty(txtEmpNum.Text) || String.IsNullOrEmpty(txtFirst.Text) || String.IsNullOrEmpty(txtLast.Text)) { return null; } Employee emp = new Employee(); emp.EmpNum = txtEmpNum.Text; emp.lastName = txtLast.Text; emp.firstName = txtFirst.Text; emp.jobTitle = txtTitle.Text; emp.emailAddress = txtEmail.Text; emp.manager = cbManager.Checked; emp.defaultSupervisor = ddlDefSup.SelectedValue; return emp; }
private void setTheSupervisorInCBs(Employee sup) { ListItem li = new ListItem(sup.firstName + " " + sup.lastName); ddlSup1.Items.Add(li); ddlSup2.Items.Add(li); ddlSup3.Items.Add(li); ddlSup4.Items.Add(li); ddlSup1.Enabled = false; ddlSup2.Enabled = false; ddlSup3.Enabled = false; ddlSup4.Enabled = false; }
private bool updateEmails() { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString); conn.Open(); string str = "select * from EmployeeList"; OleDbCommand comm = new OleDbCommand(); comm.Connection = conn; comm.CommandText = str; comm.CommandType = CommandType.Text; DataSet set = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; try { adapter.Fill(set); } catch (System.Exception ex) { return false; } List<Employee> emps = new List<Employee>(); foreach (DataRow dr in set.Tables[0].Rows) { Employee emp = new Employee(); emp.ID = (int)dr[0]; emp.emailAddress = dr[5].ToString(); emp.lastName = dr[2].ToString(); emp.firstName = dr[3].ToString(); emps.Add(emp); } OleDbCommand comm2 = new OleDbCommand(); comm2.Connection = conn; foreach (Employee e in emps) { if (e.emailAddress == string.Empty) { str = "update EmployeeList set EmailAddress='" + e.firstName + "." + e.lastName + "@mstc.edu' where ID=" + e.ID.ToString(); comm2.CommandText = str; try { comm2.ExecuteNonQuery(); } catch (System.Exception egg) { continue; } } } conn.Close(); return true; }
private Employee getTheLoggedInSupervisor(int id) { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString); conn.Open(); string str = "select * from employeelist where ID=" + id.ToString(); OleDbCommand comm = new OleDbCommand(str, conn); DataSet set = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; try { adapter.Fill(set); } catch (System.Exception ex) { return null; } DataRow dr = set.Tables[0].Rows[0]; Employee sup = new Employee(); sup.ID = (int)dr[0]; sup.EmpNum = dr[1].ToString(); sup.lastName = dr[2].ToString(); sup.firstName = dr[3].ToString(); sup.jobTitle = dr[4].ToString(); sup.emailAddress = dr[5].ToString(); sup.registered = (bool)dr[7]; sup.manager = true; conn.Close(); return sup; }
/************************************************************************************************** * First we check to see if any of the grants for this employee, for this month are pending * because those have to be completed yet. * Then we check for any disapprovals becuase those have to e dealt with. * Finally we see if there are any approvals. If so, we send back approved. * **********************************************************************************************/ public static GrantMonth.status checkStatus(Employee emp, int month, int year, OleDbConnection conn) { if (conn.State != ConnectionState.Open) { conn.Open(); } string select = "select * from WorkMonth where EmpID=" + emp.ID.ToString() + " and WorkingMonth=" + month.ToString() + " and WorkYear=" + year.ToString(); OleDbCommand comm = new OleDbCommand(select, conn); DataSet set = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; try { adapter.Fill(set); } catch (System.Exception ex) { return GrantMonth.status.New; } if (set.Tables == null || set.Tables.Count < 1 || set.Tables[0].Rows.Count < 1) { conn.Close(); return GrantMonth.status.New; } try { var bag = (from row in set.Tables[0].AsEnumerable() where row.Field<int>("Status") == 1 select row).ToList(); //Pending if (bag != null && bag.Count > 0) { return GrantMonth.status.pending; } bag = (from row in set.Tables[0].AsEnumerable() where row.Field<int>("Status") == 2 select row).ToList(); //Disapproved if (bag != null && bag.Count > 0) { return GrantMonth.status.disapproved; } bag = (from row in set.Tables[0].AsEnumerable() where row.Field<int>("Status") == 3 select row).ToList(); //Approved if (bag != null && bag.Count > 0) { return GrantMonth.status.approved; } } catch (System.Exception ex) { int ii = 0; } return GrantMonth.status.New; }
private List<Employee> getEmployees(OleDbConnection conn, bool forApproval = false) { if (conn.State != ConnectionState.Open) { conn.Open(); } OleDbCommand comm = new OleDbCommand(); comm.Connection = conn; comm.CommandType = CommandType.Text; if (forApproval) { int empID = System.Convert.ToInt32(Request.Params["Employee"]); comm.CommandText = "select * from EmployeeList where ID=" + empID.ToString(); } else { comm.CommandText = "select * from EmployeeList"; } OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; DataSet set = new DataSet(); try { adapter.Fill(set); } catch (System.Exception eb) { return null; } List<Employee> emps = new List<Employee>(); foreach (DataRow dr in set.Tables[0].Rows) { Employee e = new Employee(); e.ID = (int)dr[0]; e.EmpNum = dr[1].ToString(); e.lastName = dr[2].ToString(); e.firstName = dr[3].ToString(); e.jobTitle = dr[4].ToString(); e.emailAddress = dr[5].ToString(); e.password = dr[6].ToString(); e.registered = (bool)dr[7]; emps.Add(e); } return emps; }
protected bool AddNewEmployee(Employee newEmp) { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString); conn.Open(); string scomm = "insert into EmployeeList (EmployeeNUm, LastName, FirstName, JobTitle, EmailAddress, registered, manager, DefaultSupervisor) "; scomm += "values (" + newEmp.EmpNum + ",'" + newEmp.lastName + "','" + newEmp.firstName + "','" + newEmp.jobTitle + "','"; scomm += newEmp.emailAddress + "'," + newEmp.registered + "," + newEmp.manager + "," + newEmp.defaultSupervisor + ")"; OleDbCommand comm = new OleDbCommand(scomm, conn); try { comm.ExecuteNonQuery(); } catch (System.Exception ex) { return false; } return true; }
public static string formulateEmailBody(Employee sup, Employee emp, DateTime dt, int grantID) { StringBuilder sb = new StringBuilder(); string email = "<html><body style='font-family:Arial'><h4>Hi " + sup.firstName + ".</h4><br />"; sb.Append(email); email = emp.firstName + " " + emp.lastName + " has submitted a grant time entry for your approval. <br />"; sb.Append(email); email = "To see the grant time entries, please browse to: <p />"; sb.Append(email); email = "http://www.mid-state.net/GrantApplication/Default.aspx?approval=true&ID=" + sup.ID.ToString() + "&Employee=" + emp.ID.ToString() + "&month=" + (dt.Month - 1).ToString() + "&Year=" + dt.Year.ToString() + "&GrantID=" + grantID.ToString(); sb.Append(email); sb.Append("<p /><h5>Thanks, the Grant Administrator</h5></body></html>"); return sb.ToString(); }
protected bool areEqual(Employee oldEmp, Employee newEmp) { if (oldEmp.defaultSupervisor != newEmp.defaultSupervisor || oldEmp.emailAddress != newEmp.emailAddress || oldEmp.EmpNum != newEmp.EmpNum || oldEmp.firstName != newEmp.firstName || oldEmp.jobTitle != newEmp.jobTitle || oldEmp.lastName != newEmp.lastName || oldEmp.manager != newEmp.manager) { return false; } return true; }
public static string formulateResultsEmail(string reasonTxt, Employee emp, Employee sup, bool approved) { int month = System.Convert.ToInt32(HttpContext.Current.Session["month"]); int year = System.Convert.ToInt32(HttpContext.Current.Session["Year"]); DateTime dater = new DateTime(year, month + 1, 1); //Again, have to convert from Javascript date to .Net date. string bodyTxt = "<html><body><strong>Hi " + emp.firstName + ".</strong><p />"; bodyTxt += "This email is to inform you " + sup.firstName + " " + sup.lastName + " has " + ((approved) ? "approved " : "disapproved "); bodyTxt += "<br />your grant entries for the month of " + dater.ToString("MMMM") + " for the following reason: <br />"; bodyTxt += reasonTxt + "<p />"; if (!approved) { bodyTxt += "Please take appropriate action to corrent this matter.<br />"; } bodyTxt += "Thanks for using the Grant Allocation form! </body></html>"; return bodyTxt; }
protected List<Employee> getAllManagers() { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString); conn.Open(); string scomm = string.Empty; scomm = "select * from EmployeeList where manager=true"; OleDbCommand comm = new OleDbCommand(scomm, conn); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; DataSet set = new DataSet(); try { adapter.Fill(set); } catch (Exception ex) { return null; } if (set.Tables != null && set.Tables[0].Rows.Count > 0) { List<Employee> mans = new List<Employee>(); foreach (DataRow dr in set.Tables[0].Rows) { Employee emp = new Employee(); emp.ID = (int)dr[0]; emp.EmpNum = dr[1].ToString(); emp.lastName = dr[2].ToString(); emp.firstName = dr[3].ToString(); if (dr[4] != DBNull.Value) { emp.jobTitle = dr[4].ToString(); } if (dr[5] != DBNull.Value) { emp.emailAddress = dr[5].ToString(); } if (dr[6] != DBNull.Value) { emp.password = dr[6].ToString(); } if (dr[7] != DBNull.Value) { emp.registered = (bool)dr[7]; } if (dr[8] != DBNull.Value) { emp.manager = (bool)dr[8]; } if (dr[9] != DBNull.Value) { emp.defaultSupervisor = dr[9].ToString(); } mans.Add(emp); } return mans; } return null; }
public static TimeEntry[] GetEmployeeTimeEntries(Employee emp, int month, int year) { if (emp != null) { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString); OleDbCommand comm = new OleDbCommand(); comm.Connection = conn; comm.CommandType = CommandType.Text; comm.CommandText = "select * from TimeEntry where EmpID=" + emp.ID.ToString() + " and MonthNumber=" + month.ToString() + " and YearNumber=" + year.ToString() + " order by GrantID"; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; DataSet set = new DataSet(); try { adapter.Fill(set); } catch (System.Exception ex) { return null; } List<TimeEntry> timeEntries = new List<TimeEntry>(); List<Grant> grants = (List<Grant>)HttpContext.Current.Session["Grants"]; List<Grant> selGrants = resetGrants(); if (set.Tables != null && set.Tables[0].Rows.Count > 0) { int selID = -1; foreach (DataRow dr in set.Tables[0].Rows) { TimeEntry te = new TimeEntry(); te.ID = (int)dr[0]; te.monthNumber = (int)dr[1]; te.dayNumber = (int)dr[2]; te.yearNumber = (int)dr[3]; te.grantID = (int)dr[4]; te.grantHours = (double)dr[5]; te.empID = (int)dr[6]; if (dr[7] != DBNull.Value) { te.supervisorID = (int)dr[7]; } timeEntries.Add(te); if (selID != te.grantID) { var g = (from grant in grants where grant.ID == te.grantID select grant).ToList().First(); if (!selGrants.Contains(g)) { var yy = selGrants.FindIndex(grt => grt.category == "PlaceHolder"); selGrants[yy] = g; } selID = te.grantID; } } } HttpContext.Current.Session["SelectedGrants"] = selGrants; HttpContext.Current.Session["TimeEntries"] = timeEntries; GrantMonth.status stat = checkStatus(emp, month, year, conn); HttpContext.Current.Session["GrantStatus"] = stat; conn.Close(); return timeEntries.ToArray(); } return null; }
public static Employee getSelectedEmp(string empNum, string ID = "0") { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString); conn.Open(); string scomm = string.Empty; scomm = (ID == "0") ? "select * from EmployeeList where EmployeeNUm = " + empNum : "select * from EmployeeList where ID = " + ID; OleDbCommand comm = new OleDbCommand(scomm, conn); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; DataSet set = new DataSet(); try { adapter.Fill(set); } catch (Exception ex) { return null; } if (set.Tables != null && set.Tables[0].Rows.Count > 0) { Employee emp = new Employee(); DataRow dr = set.Tables[0].Rows[0]; emp.ID = (int)dr[0]; emp.EmpNum = dr[1].ToString(); emp.lastName = dr[2].ToString(); emp.firstName = dr[3].ToString(); if (dr[4] != DBNull.Value) { emp.jobTitle = dr[4].ToString(); } if (dr[5] != DBNull.Value) { emp.emailAddress = dr[5].ToString(); } if (dr[6] != DBNull.Value) { emp.password = dr[6].ToString(); } if (dr[7] != DBNull.Value) { emp.registered = (bool)dr[7]; } if (dr[8] != DBNull.Value) { emp.manager = (bool)dr[8]; } if (dr[9] != DBNull.Value) { emp.defaultSupervisor = dr[9].ToString(); } if (empNum != "0") //It's calling for the default supervisor, not the employee. { HttpContext.Current.Session["CurrentEmployee"] = emp; } return emp; } return null; }
public static bool updateGrantStatus(Employee sup, OleDbConnection conn, Employee emp, DateTime selDate, int grantID, GrantMonth.status stat) { if (conn.State != ConnectionState.Open) { conn.Open(); } int istat = System.Convert.ToInt32(stat); string select = "select * from WorkMonth where EmpID=" + emp.ID.ToString() + " and WorkingMonth=" + (selDate.Month - 1).ToString() + " and WorkYear=" + selDate.Year.ToString() + " and GrantID=" + grantID.ToString(); string insert = "insert into WorkMonth (EmpID, WorkingMonth, WorkYear, GrantID, SupervisorID, Status) "; insert += "values(" + emp.ID.ToString() + "," + (selDate.Month - 1).ToString() + "," + selDate.Year.ToString() + "," + grantID.ToString() + "," + sup.ID.ToString() + ",1);"; string update = "update WorkMonth set status=" + istat.ToString() + " where EmpID=" + emp.ID.ToString() + " and WorkingMonth=" + (selDate.Month - 1).ToString() + " and WorkYear=" + selDate.Year.ToString() + " and GrantID=" + grantID.ToString(); OleDbCommand comm = new OleDbCommand(select, conn); OleDbCommand up = new OleDbCommand(update, conn); OleDbCommand ins = new OleDbCommand(insert, conn); DataSet set = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; try { adapter.Fill(set); } catch (System.Exception ex) { return false; } if (set.Tables.Count > 0 && set.Tables[0].Rows.Count > 0) { set.Reset(); up.ExecuteNonQuery(); } else { set.Reset(); ins.ExecuteNonQuery(); } return true; }