public static bool addNewTimeEntry(TimeEntry te, OleDbCommand comm, OleDbConnection conn) { conn.Open(); comm.Connection = conn; comm.CommandType = CommandType.Text; comm.CommandText = "insert into TimeEntry (MonthNumber, DayNumber, YearNumber, GrantID, GrantHours, EmpID) values ("; comm.CommandText += te.monthNumber.ToString() + "," + te.dayNumber.ToString() + "," + te.yearNumber.ToString() + ","; comm.CommandText += te.grantID.ToString() + "," + te.grantHours.ToString() + "," + te.empID.ToString() + ");"; comm.Connection = conn; try { comm.ExecuteNonQuery(); } catch (System.Exception ex) { return false; } 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 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 bool updateTimeEntry(TimeEntry te, OleDbCommand comm, OleDbConnection conn) { conn.Open(); comm.Connection = conn; comm.CommandType = CommandType.Text; comm.CommandText = "update TimeEntry set GrantHours=" + te.grantHours.ToString() + " where ID=" + te.ID.ToString(); comm.Connection = conn; try { comm.ExecuteNonQuery(); } catch (System.Exception ex) { return false; } return true; }