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