/// <summary>
/// Assigns the chore to the user, updating the assignment if the chore was already scheduled
/// </summary>
public void AssignChore(ScheduledChore chore)
{
try
{
_conn.Open();
string sql = string.Empty;
if (chore.ID > 0)
{
sql = $"UPDATE [{SCHEDULE_TABLE}] " +
$"SET {ASSIGNED_COL} = {chore.UserID} " +
$"WHERE {ID_COL} = {chore.ID}";
}
else
{
sql = $"INSERT INTO [{SCHEDULE_TABLE}] " +
$"({ASSIGNED_COL}, {CHORE_COL}) " +
$"VALUES " +
$"({chore.UserID}, {chore.ChoreID})";
}
using (OleDbCommand command = new OleDbCommand(sql, _conn))
{
command.ExecuteNonQuery();
}
}
finally
{
_conn.Close();
}
}
/// <summary>
/// Removes the chore from the directory
/// </summary>
public void DeleteChore(ScheduledChore chore)
{
try
{
_conn.Open();
string sql = $"DELETE FROM [{SCHEDULE_TABLE}] " +
$"WHERE {CHORE_COL} = {chore.ChoreID}";
using (OleDbCommand command = new OleDbCommand(sql, _conn))
{
command.ExecuteNonQuery();
}
sql = $"DELETE FROM [{CHORE_TABLE}] " +
$"WHERE {ID_COL} = {chore.ChoreID}";
using (OleDbCommand command = new OleDbCommand(sql, _conn))
{
command.ExecuteNonQuery();
}
}
finally
{
_conn.Close();
}
}
private void AddChore_OnClick(object sender, RoutedEventArgs e)
{
ScheduledChore temp = _main.SelectedChore;
try
{
_main.AddingChore = true;
_main.SelectedChore = null;
EditChoreDialog dlg = new EditChoreDialog {
DataContext = _main
};
dlg.ShowDialog();
if (dlg.DialogResult == true)
{
_main.AddChore();
}
}
finally
{
_main.AddingChore = false;
_main.SelectedChore = temp;
}
}
/// <summary>
/// Gets a list of chores that have already been scheduled for the household
/// </summary>
public ObservableCollection <ScheduledChore> GetScheduledChores(List <Chore> allChores, List <User> allUsers, User currentUser)
{
ObservableCollection <ScheduledChore> tasks = new ObservableCollection <ScheduledChore>();
try
{
_conn.Open();
string sql = $"SELECT * FROM [{SCHEDULE_TABLE}]";
using (OleDbCommand command = new OleDbCommand(sql, _conn))
{
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int choreId = Convert.ToInt32(reader[CHORE_COL]);
int userId = Convert.ToInt32(reader[ASSIGNED_COL]);
Chore chore = allChores.FirstOrDefault(c => c.ID == choreId);
User user = allUsers.First(u => u.ID == userId);
ScheduledChore scheduledChore = new ScheduledChore(chore)
{
ID = Convert.ToInt32(reader[ID_COL]),
ChoreID = choreId,
UserID = userId,
AssignedTo = user
};
tasks.Add(scheduledChore);
}
}
}
List <string> userOrder = new List <string> {
currentUser.Username
};
tasks = new ObservableCollection <ScheduledChore>(tasks.OrderBy(c =>
{
int index = userOrder.IndexOf(c.AssignedTo.Username);
return(index == -1 ? int.MaxValue : index);
}));
}
finally
{
_conn.Close();
}
return(tasks);
}
/// <summary>
/// Modifies an existing chore in the database with new, user given values
/// </summary>
public void ModifyChore(ScheduledChore modifiedChore)
{
try
{
_conn.Open();
string sql = $"UPDATE [{CHORE_TABLE}] " +
$"SET {FREQUENCY_COL} = {modifiedChore.Frequency}, " +
$"{DURATION_COL} = {modifiedChore.Duration}, " +
$"{PERFORMED_COL} = '{modifiedChore.LastPerform}' " +
$"WHERE {ID_COL} = {modifiedChore.ChoreID}";
using (OleDbCommand command = new OleDbCommand(sql, _conn))
{
command.ExecuteNonQuery();
}
}
finally
{
_conn.Close();
}
}
