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