public static async Task <List <ResidentReportItem> > GetAssignments(string sortByFieldName,
bool asc)
{
List <ResidentReportItem> items = new List <ResidentReportItem>();
DateTime now = DateTime.Now;
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "SELECT resident.ResidentID, FirstName, LastName, PGY, PhoneNumber, SymptomsDate, " +
"SymptomsDescription, Covid19TestDate, IsQuarantined, QuarantinedUntil, " +
"test_result.TestResultDescription AS Covid19TestResult," +
"DutyDescription, DateStart, DateEnd " +
"FROM resident " +
"INNER JOIN test_result ON resident.Covid19TestResult = test_result.TestResultType " +
"INNER JOIN duty_assignment ON resident.ResidentID = duty_assignment.ResidentID " +
"INNER JOIN duty ON duty.DutyType = duty_assignment.DutyType ";
cmd.Parameters.AddWithValue("@FieldName", sortByFieldName);
cmd.CommandText = query;
DbDataReader reader = await cmd.ExecuteReaderAsync();
items = await readResidentReportItemsWithDuties(reader);
}
return(items);
}
public static async Task <List <TestResult> > GetTestResultTypes()
{
List <TestResult> testResultTypes = new List <TestResult>();
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "SELECT TestResultType, TestResultDescription FROM " +
"test_result ORDER BY TestResultType ASC;";
cmd.CommandText = query;
DbDataReader reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
testResultTypes.Add(new TestResult(
reader["TestResultType"] as int? ?? 0,
reader["TestResultDescription"] as string ?? string.Empty));
}
}
}
return(testResultTypes);
}
public static async Task <List <Duty> > GetDutyTypes()
{
List <Duty> dutyTypes = new List <Duty>();
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "SELECT DutyType, DutyDescription FROM duty;";
cmd.CommandText = query;
DbDataReader reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
dutyTypes.Add(new Duty(
reader["DutyType"] as int? ?? 0,
reader["DutyDescription"] as string ?? string.Empty));
}
}
}
return(dutyTypes);
}
/// <summary>
///
/// </summary>
/// <param name="sortByFieldName"></param>
/// <param name="asc"></param>
/// <returns></returns>
public static async Task <List <ResidentReportItem> > GetQuarantinedResidents(string sortByFieldName,
bool asc)
{
List <ResidentReportItem> items = new List <ResidentReportItem>();
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "SELECT ResidentID, FirstName, LastName, PGY, PhoneNumber, SymptomsDate, " +
"SymptomsDescription, Covid19TestDate, IsQuarantined, QuarantinedUntil, " +
"test_result.TestResultDescription AS Covid19TestResult " +
"FROM resident " +
"INNER JOIN test_result ON resident.Covid19TestResult = test_result.TestResultType " +
"WHERE IsQuarantined = 1 " +
$"ORDER BY @FieldName {(asc ? "ASC" : "DESC")} ";
cmd.Parameters.AddWithValue("@FieldName", sortByFieldName);
cmd.CommandText = query;
DbDataReader reader = await cmd.ExecuteReaderAsync();
items = await readResidentReportItems(reader);
}
return(items);
}
/// <summary>
///
/// </summary>
/// <param name="residentId"></param>
/// <param name="resident"></param>
/// <returns></returns>
public static async Task UpdateResidentEntry(int residentId, Resident resident)
{
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "UPDATE resident SET FirstName = @FirstName, LastName = @LastName, " +
"PGY = @PGY, PhoneNumber = @PhoneNumber, SymptomsDate = @SymptomsDate, " +
"Covid19TestDate = @Covid19TestDate, Covid19TestResult = @Covid19TestResult, " +
"IsQuarantined = @IsQuarantined, QuarantinedUntil = @QuarantinedUntil " +
"WHERE ResidentID = @ResidentID;";
cmd.Parameters.AddWithValue("@ResidentID", residentId);
cmd.Parameters.AddWithValue("@FirstName", resident.FirstName);
cmd.Parameters.AddWithValue("@LastName", resident.LastName);
cmd.Parameters.AddWithValue("@PGY", resident.PGY);
cmd.Parameters.AddWithValue("@PhoneNumber", resident.PhoneNumber);
cmd.Parameters.AddWithValue("@SymptomsDate", resident.SymptomsDate);
cmd.Parameters.AddWithValue("@SymptomsDescription", resident.SymptomsDescription);
cmd.Parameters.AddWithValue("@Covid19TestDate", resident.Covid19TestDate);
cmd.Parameters.AddWithValue("@Covid19TestResult", resident.Covid19TestResult.TestResultType);
cmd.Parameters.AddWithValue("@IsQuarantined", resident.IsQuarantined);
cmd.Parameters.AddWithValue("@QuarantinedUntil", resident.QuarantinedUntil);
cmd.CommandText = query;
await cmd.ExecuteNonQueryAsync();
}
}
/// <summary>
///
/// </summary>
/// <param name="resident"></param>
/// <returns></returns>
public static async Task <int> CreateResidentEntry(Resident resident)
{
int residentId = -1;
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "INSERT INTO resident (FirstName, LastName, PGY, PhoneNumber, " +
"SymptomsDate, SymptomsDescription, Covid19TestDate, Covid19TestResult, " +
"IsQuarantined, QuarantinedUntil) VALUES (@FirstName, @LastName, @PGY, " +
"@PhoneNumber, @SymptomsDate, @SymptomsDescription, @Covid19TestDate, " +
"@Covid19TestResult, @IsQuarantined, @QuarantinedUntil);";
cmd.Parameters.AddWithValue("@FirstName", resident.FirstName);
cmd.Parameters.AddWithValue("@LastName", resident.LastName);
cmd.Parameters.AddWithValue("@PGY", resident.PGY);
cmd.Parameters.AddWithValue("@PhoneNumber", resident.PhoneNumber);
cmd.Parameters.AddWithValue("@SymptomsDate", resident.SymptomsDate);
cmd.Parameters.AddWithValue("@SymptomsDescription", resident.SymptomsDescription);
cmd.Parameters.AddWithValue("@Covid19TestDate", resident.Covid19TestDate);
cmd.Parameters.AddWithValue("@Covid19TestResult", resident.Covid19TestResult.TestResultType);
cmd.Parameters.AddWithValue("@IsQuarantined", resident.IsQuarantined);
cmd.Parameters.AddWithValue("@QuarantinedUntil", resident.QuarantinedUntil);
cmd.CommandText = query;
await cmd.ExecuteNonQueryAsync();
residentId = Convert.ToInt32(cmd.LastInsertedId);
}
return(residentId);
}
/// <summary>
///
/// </summary>
/// <param name="residentId"></param>
/// <returns></returns>
public static async Task DeleteResidentEntry(int residentId)
{
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "DELETE FROM resident WHERE ResidentID = @ResidentID";
cmd.Parameters.AddWithValue("@ResidentID", residentId);
cmd.CommandText = query;
await cmd.ExecuteNonQueryAsync();
}
}
public static async Task <LocalIDSet <int> > PreviousAndNextResidentIds(int residentId)
{
LocalIDSet <int> localSet = new LocalIDSet <int>();
List <int> residentIds = new List <int>();
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "SELECT ResidentID FROM resident ORDER BY ResidentID ASC";
cmd.CommandText = query;
DbDataReader reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
residentIds.Add((int)reader["ResidentID"]);
}
}
int idx = residentIds.IndexOf(residentId);
int previousId = -1;
int nextId = -1;
int previousIdx = idx - 1;
int nextIdx = idx + 1;
if (previousIdx >= 0)
{
previousId = residentIds[previousIdx];
}
if (nextIdx < residentIds.Count)
{
nextId = residentIds[nextIdx];
}
localSet.PreviousID = previousId;
localSet.NextID = nextId;
return(localSet);
}
/// <summary>
///
/// </summary>
/// <param name="residentId"></param>
/// <returns></returns>
public static async Task <Resident> GetResidentEntry(int residentId)
{
Resident resident = null;
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "SELECT ResidentID, FirstName, LastName, PGY, PhoneNumber, " +
"SymptomsDate, SymptomsDescription, Covid19TestDate, Covid19TestResult, " +
"IsQuarantined, QuarantinedUntil, TestResultDescription " +
"FROM resident INNER JOIN test_result ON test_result.TestResultType = " +
"resident.Covid19TestResult WHERE ResidentID = @ResidentID;";
cmd.Parameters.AddWithValue("@ResidentID", residentId);
cmd.CommandText = query;
DbDataReader reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
await reader.ReadAsync();
resident = new Resident(
residentId,
reader["FirstName"] as string ?? string.Empty,
reader["LastName"] as string ?? string.Empty,
reader["PGY"] as string ?? string.Empty,
reader["PhoneNumber"] as string ?? string.Empty,
reader["SymptomsDate"] as DateTime?,
reader["SymptomsDescription"] as string ?? string.Empty,
reader["Covid19TestDate"] as DateTime?,
new TestResult(
reader["Covid19TestResult"] as int? ?? 0,
reader["TestResultDescription"] as string ?? string.Empty),
Convert.ToBoolean(reader["IsQuarantined"] as sbyte? ?? 0),
reader["QuarantinedUntil"] as DateTime?);
}
}
return(resident);
}
/// <summary>
///
/// </summary>
/// <param name="dutyAssignment"></param>
/// <returns></returns>
public static async Task AssignDuty(DutyAssignment dutyAssignment)
{
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "INSERT INTO duty_assignment (ResidentID, DutyType, DateStart, DateEnd, DateAssigned) " +
"VALUES (@ResidentID, @DutyType, @DateStart, @DateEnd, @DateAssigned)";
cmd.Parameters.AddWithValue("@ResidentID", dutyAssignment.ResidentID);
cmd.Parameters.AddWithValue("@DutyType", dutyAssignment.Duty.DutyType);
cmd.Parameters.AddWithValue("@DateStart", dutyAssignment.DateStart);
cmd.Parameters.AddWithValue("@DateEnd", dutyAssignment.DateEnd);
cmd.Parameters.AddWithValue("@DateAssigned", dutyAssignment.DateAssigned);
cmd.CommandText = query;
await cmd.ExecuteNonQueryAsync();
}
}
/// <summary>
///
/// </summary>
/// <param name="sortByFieldName"></param>
/// <param name="asc"></param>
/// <returns></returns>
public static async Task <List <ResidentReportItem> > GetAvailableResidents(string sortByFieldName,
bool asc)
{
List <ResidentReportItem> items = new List <ResidentReportItem>();
DateTime now = DateTime.Now;
DateTime todayStart = now.AddHours(-now.Hour);
DateTime todayEnd = now.AddHours(24 - now.Hour);
using (LocalDB db = new LocalDB())
{
await db.OpenConnectionAsync();
MySqlCommand cmd = db.CreateCommand();
string query = "SELECT resident.ResidentID, FirstName, LastName, PGY, PhoneNumber, SymptomsDate, " +
"SymptomsDescription, Covid19TestDate, IsQuarantined, QuarantinedUntil, " +
"test_result.TestResultDescription AS Covid19TestResult," +
"DutyDescription, DateStart, DateEnd " +
"FROM resident " +
"LEFT JOIN duty_assignment ON duty_assignment.ResidentID = resident.ResidentID " +
"LEFT JOIN duty ON duty.DutyType = duty_assignment.DutyType " +
"INNER JOIN test_result ON resident.Covid19TestResult = test_result.TestResultType " +
"WHERE DateStart IS NULL " +
"OR (@TodayStart < DateStart OR @TodayEnd > DateEnd) " +
$"ORDER BY @FieldName {(asc ? "ASC" : "DESC")} ";
cmd.Parameters.AddWithValue("@FieldName", sortByFieldName);
cmd.Parameters.AddWithValue("@TodayStart", todayStart);
cmd.Parameters.AddWithValue("@TodayEnd", todayEnd);
cmd.CommandText = query;
DbDataReader reader = await cmd.ExecuteReaderAsync();
items = await readResidentReportItemsWithDuties(reader);
}
return(items);
}
