private void UpdateDatabase(TimeCardEntry_DO item)
{
try
{
DBResult result;
if (item.ID == 0)
{
if ((result = DataSource.ItemInsert(item)).ResultCode != DBResult.Result.Success)
{
throw new CommonException("Could not insert");
}
}
else
{
if ((result = DataSource.ItemUpdate(item)).ResultCode != DBResult.Result.Success)
{
throw new CommonException("Could not update");
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
public virtual DBResult ItemUpdate(TimeCardEntry_DO entry)
{
QueryString sql = DB.Format(
$"UPDATE timekeep.entries SET \n" +
$" project_id = {entry.Project.ID}, start_time = '{entry.Start.ToMySqlString()}', end_time = '{entry.End.ToMySqlString()}', memo = '{entry.Memo}' \n" +
$"WHERE id = {entry.ID}");
return(DB.Update(sql));
}
private void AddRowToListBox(TimeCardEntry_DO timeItem)
{
ListViewItem item = listItems.AddRow(COL_ID, timeItem.ID, timeItem);
listItems.SetListViewColumn(item, COL_PROJECT, timeItem.Project.Name);
listItems.SetListViewColumn(item, COL_START, timeItem.Start.TimeOfDay.ToTimeOfDayString());
listItems.SetListViewColumn(item, COL_END, timeItem.End.TimeOfDay.ToTimeOfDayString());
listItems.SetListViewColumn(item, COL_MEMO, timeItem.Memo);
item.BackColor = timeItem.IsValid ? Color.White : Color.LightSalmon;
}
private void OnNewClicked(object sender, EventArgs e)
{
TimeCardEntry_DO item = new TimeCardEntry_DO()
{
ID = 0,
Start = CurrentDate.Date,
End = CurrentDate.Date + TimeSpan.FromHours(8),
Memo = "",
};
DisplayItems.Add(item);
AddRowToListBox(item);
}
public virtual DBResult ItemInsert(TimeCardEntry_DO entry)
{
QueryString sql = DB.Format(
$"INSERT INTO timekeep.entries \n" +
$" SET project_id = {entry.Project.ID}, start_time = '{entry.Start.ToMySqlString()}', end_time = '{entry.End.ToMySqlString()}', memo = '{entry.Memo}'");
DBResult result = DB.Insert(sql);
if (result.ResultCode == DBResult.Result.Success)
{
entry.ID = result.ItemID;
}
return(result);
}
private void LoadLineItemFromRow(ListViewItem row)
{
TimeCardEntry_DO lineItem = row.Tag as TimeCardEntry_DO;
if (int.Parse(row.SubItems[COL_ID].Text) != lineItem.ID)
{
throw new ArgumentException("Row mismatch");
}
TimeSpan start, end;
if (TimeSpanExtensions.TryParseTimeOfDay(row.SubItems[COL_START].Text, out start) == false)
{
throw new ArgumentException("Bad begin");
}
if (TimeSpanExtensions.TryParseTimeOfDay(row.SubItems[COL_END].Text, out end) == false)
{
throw new ArgumentException("Bad end");
}
lineItem.Start = lineItem.Start.Date + start;
lineItem.End = lineItem.End.Date + end;
lineItem.Memo = row.SubItems[COL_MEMO].Text;
}
public virtual DBResult EntriesForDateGet(DateTime day, out TimeCardEntryList entries)
{
entries = new TimeCardEntryList();
DBResult result;
QueryString sql = DB.Format(
$"SELECT \n" +
$"E.id as entry_id, \n" +
$"E.start_time as start_time, \n" +
$"E.end_time as end_time, \n" +
$"E.memo as memo, \n" +
$"P.id as project_id, \n" +
$"P.name as project_name \n" +
$"FROM timekeep.entries E \n" +
$"JOIN timekeep.projects P ON E.project_id = P.id \n" +
$"WHERE \n" +
$" (start_time >= '{day.Date.ToMySqlString()}' AND start_time <= '{(day.Date + TimeSpan.FromDays(1)).ToMySqlString()}') OR \n" +
$" (end_time >= '{day.Date.ToMySqlString()}' AND end_time <= '{(day.Date + TimeSpan.FromDays(1)).ToMySqlString()}') \n" +
$"ORDER BY start_time ASC");
DatabaseDataReader reader;
result = DB.Query(sql, out reader);
using (reader)
{
if (result.ResultCode == DBResult.Result.Success)
{
while (reader.Read())
{
TimeCardEntry_DO entry = DataReaderConverter.CreateClassFromDataReader <TimeCardEntry_DO>(reader);
entry.Project = DataReaderConverter.CreateClassFromDataReader <Project_DO>(reader);
entries.Add(entry);
}
}
}
return(result);
}
