public void Update(clsDatabase db, string userID)
{
using (clsQueryResults results = new clsQueryResults())
{
string sql1 = "SELECT * FROM TimeLogEntries WHERE EntryID = " + db.ToSql(mEntryID);
results.Open(db, sql1);
DateTime lastMaint = (DateTime)results.GetColValue("DateTimeLastMaint");
if (lastMaint != DateTimeLastMaint)
{
throw new Exception("Entry has been modified since retrieval from the database.");
}
}
DateTimeLastMaint = DateTime.Now;
string sql2 = "UPDATE TimeLogEntries SET "
+ "EmployeeID = " + db.ToSql(EmployeeID)
+ ", DateWorked = " + db.ToSql(DateWorked)
+ ", HoursWorked = " + db.ToSql(HoursWorked)
+ ", Billable = " + db.ToSql(Billable)
+ ", Description = " + db.ToSql(Description)
+ ", DateTimeLastMaint = " + db.ToSql(DateTimeLastMaint)
+ " WHERE EntryID = " + db.ToSql(mEntryID);
db.ExecuteSQL(sql2);
addTimeLogHistory(sql2, userID, db);
}
public void RestoreStateFromQuery(clsQueryResults results)
{
mEntryID = (int)results.GetColValue("EntryID");
EmployeeID = (string)results.GetColValue("EmployeeID");
HoursWorked = (double)results.GetColValue("HoursWorked");
DateTime date = (DateTime)results.GetColValue("DateWorked");
DateWorked = new TimeLogDate(date.Year, date.Month, date.Day);
Description = (string)results.GetColValue("Description");
Billable = (bool)results.GetColValue("Billable");
DateTimeLastMaint = (DateTime)results.GetColValue("DateTimeLastMaint");
}
private int getEntryID(clsDatabase db)
{
using (clsQueryResults results = new clsQueryResults())
{
string sqlGetEntryID = "SELECT MAX(EntryID) AS entryID FROM TimeLogEntries WHERE "
+ "EmployeeID=" + db.ToSql(EmployeeID) + " AND "
+ "DateWorked=" + db.ToSql(DateWorked) + " AND "
+ "HoursWorked=" + db.ToSql(HoursWorked) + " AND "
+ "Billable=" + db.ToSql(Billable) + " AND "
+ "Description=" + db.ToSql(Description) + " AND "
+ "DateTimeLastMaint=" + db.ToSql(DateTimeLastMaint) + ";";
results.Open(db, sqlGetEntryID);
int entryID = (int)results.GetColValue("entryID");
return(entryID);
}
}
public List <clsTimeLogEntry> GetAllEntries(TimeLogDate beginDate, TimeLogDate endDate)
{
List <clsTimeLogEntry> listBetweenDates = new List <clsTimeLogEntry>();
string sql;
using (clsDatabase db = new clsDatabase(mConnectionString))
using (clsQueryResults results = new clsQueryResults())
{
db.Open();
sql = "SELECT COUNT(*) AS NumRecords FROM TimeLogEntries WHERE DateWorked >= "
+ db.ToSql(beginDate)
+ " AND DateWorked <= " + db.ToSql(endDate);
results.Open(db, sql);
int count = (int)results.GetColValue("NumRecords");
if (count > 500)
{
throw new clsTooManyRecordsException("More than 500 matching records found.");
}
results.Close();
sql = "SELECT * FROM TimeLogEntries WHERE DateWorked >= "
+ db.ToSql(beginDate)
+ " AND DateWorked <=" + db.ToSql(endDate)
+ " ORDER BY DateWorked DESC, EmployeeID, HoursWorked";
results.Open(db, sql);
while (results.EOF == false)
{
clsTimeLogEntry entry = new clsTimeLogEntry();
entry.RestoreStateFromQuery(results);
listBetweenDates.Add(entry);
results.MoveNext();
}
}
return(listBetweenDates);
}
