/// <summary>
/// Insert a WorkItemDueDate into the database, returning the WorkItemDueDateID.
/// </summary>
/// <param name="workItemDueDate"></param>
/// <returns></returns>
public int InsertWorkItemDueDate(WorkItemDueDate workItemDueDate)
{
int workItemDueDateID = -1;
using (var connection = new SQLiteConnection(dbConnectionString))
{
using (var cmd = new SQLiteCommand(connection))
{
connection.Open();
cmd.CommandText = "INSERT INTO WorkItemDueDate (WorkItem_ID, DueDateTime, ChangeReason, CreationDateTime) " +
"VALUES (@workItemID, @dueDate, @changeReason, @creation)";
cmd.Parameters.AddWithValue("@workItemID", workItemDueDate.WorkItemID);
cmd.Parameters.AddWithValue("@dueDate", workItemDueDate.DueDateTime);
cmd.Parameters.AddWithValue("@changeReason", workItemDueDate.ChangeReason);
cmd.Parameters.AddWithValue("@creation", workItemDueDate.CreationDateTime ?? DateTime.Now);
cmd.ExecuteNonQuery();
// Get the identity value (to return)
cmd.CommandText = "SELECT last_insert_rowid()";
workItemDueDateID = (int)(Int64)cmd.ExecuteScalar();
workItemDueDate.WorkItemDueDateID = workItemDueDateID;
}
connection.Close();
}
return(workItemDueDateID);
}
public DueDateDialog(WorkItemDueDate widd)
{
InitializeComponent();
((DueDateViewModel)DataContext).OriginalData = widd;
foreach (CalendarDateRange range in ((DueDateViewModel)DataContext).GetNonSelectableDatesAsCalendarDateRange())
{
CalendarSelection.BlackoutDates.Add(range);
}
DateTime originalDueDate = ((DueDateViewModel)DataContext).OriginalData.DueDateTime;
CalendarSelection.DisplayDateStart = originalDueDate;
CalendarSelection.SelectedDate = originalDueDate;
if (((DueDateViewModel)DataContext).CloseAction == null)
{
((DueDateViewModel)DataContext).CloseAction = new Action(() => this.Close());
}
}
/// <summary>
/// Update a WorkItemDueDate into the database, returning the WorkItemDueDateID.
/// </summary>
/// <param name="workItemDueDate"></param>
public void UpdateWorkItemDueDate(WorkItemDueDate workItemDueDate)
{
using (var connection = new SQLiteConnection(dbConnectionString))
{
using (var cmd = new SQLiteCommand(connection))
{
connection.Open();
cmd.CommandText = "UPDATE WorkItemDueDate " +
"SET DueDateTime = @dueDate, " +
"ChangeReason = @changeReason, " +
"CreationDateTime = @creation " +
"WHERE WorkItemDueDate_ID = @workItemDueDateID";
cmd.Parameters.AddWithValue("@workItemDueDateID", workItemDueDate.WorkItemID);
cmd.Parameters.AddWithValue("@dueDate", workItemDueDate.DueDateTime);
cmd.Parameters.AddWithValue("@changeReason", workItemDueDate.ChangeReason);
cmd.Parameters.AddWithValue("@creation", workItemDueDate.CreationDateTime);
cmd.ExecuteNonQuery();
}
connection.Close();
}
}
/// <summary>
/// Get a collection of Work Items from the database.
/// </summary>
/// <param name="loadAgedDays">Used to selectively load Work Items based on the number of days since they've been put in a Closed state.
/// Use -1 to load only active Work Items
/// Use 0 to load only active Work Items + those Completed today.
/// Use 10 would give you active + any Work Items completed in the last 10 calendar days.</param>
public List <WorkItem> GetWorkItems(int loadAgedDays)
{
// When this is called, first clear any content of the WorkItem collections.
List <WorkItem> rValue = new List <WorkItem>();
using (var connection = new SQLiteConnection(BaseRepository.dbConnectionString))
{
using (var cmd = new SQLiteCommand(connection))
{
connection.Open();
cmd.CommandText = "SELECT * FROM vwWorkItem ORDER BY DaysSinceCompletion ASC, DueDateTime ASC";
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
/* int daysStale = Convert.ToInt32(reader["DaysSinceCompletion"]);
*
* // Only create a WorkItem and add it to the model if DaysSinceCompletion <= loadAgedDays
* if (daysStale <= loadAgedDays)
* {*/
WorkItem workItem = new WorkItem();
int workItemID = Convert.ToInt32(reader["WorkItem_ID"]);
workItem.WorkItemID = workItemID;
workItem.Title = (string)reader["TaskTitle"];
if (reader["TaskDescription"].GetType() != typeof(DBNull))
{
workItem.Description = (string)reader["TaskDescription"];
}
workItem.CreationDateTime = DateTime.Parse(reader["CreationDateTime"].ToString());
if (reader["ModificationDateTime"].GetType() != typeof(DBNull))
{
workItem.ModificationDateTime = DateTime.Parse(reader["ModificationDateTime"].ToString());
}
if (reader["DeletionDateTime"].GetType() != typeof(DBNull))
{
workItem.DeletionDateTime = DateTime.Parse(reader["DeletionDateTime"].ToString());
}
// --- WorkItemStatusEntry ----
WorkItemStatusEntry wise = new WorkItemStatusEntry();
wise.WorkItemStatusEntryID = Convert.ToInt32(reader["WorkItemStatusEntry_ID"]);
wise.WorkItemID = workItemID;
wise.WorkItemStatusID = Convert.ToInt32(reader["WorkItemStatus_ID"]);
wise.StatusLabel = (string)reader["wisStatusLabel"];
wise.CompletionAmount = Convert.ToInt32(reader["CompletionAmount"]);
wise.CreationDateTime = DateTime.Parse(reader["wisCreationDateTime"].ToString());
DateTime?wiseModificationDateTime = null;
if (reader["wisModificationDateTime"] != DBNull.Value)
{
wiseModificationDateTime = DateTime.Parse(reader["wisModificationDateTime"].ToString());
}
wise.ModificationDateTime = wiseModificationDateTime;
workItem.CurrentWorkItemStatusEntry = wise;
// -- Due Date
WorkItemDueDate dueDate = new WorkItemDueDate(Convert.ToInt32(reader["WorkItemDueDate_ID"]), workItemID);
dueDate.DueDateTime = DateTime.Parse(reader["DueDateTime"].ToString());
dueDate.CreationDateTime = DateTime.Parse(reader["DueDateCreationDateTime"].ToString());
if (reader["DueDateModificationDateTime"] != DBNull.Value)
{
dueDate.ModificationDateTime = DateTime.Parse(reader["DueDateModificationDateTime"].ToString());
}
workItem.CurrentWorkItemDueDate = dueDate;
rValue.Add(workItem);
/* }
* else
* {
* Console.WriteLine($"Not loading {(string)reader["TaskTitle"]} because it's stale: {daysStale}.");
* }*/
}
}
connection.Close();
}
}
return(rValue);
}
