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