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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }
Пример #5
0
        /// <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();
            }
        }
Пример #6
0
        /// <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);
        }
Пример #7
0
        /// <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();
            }
        }
Пример #8
0
        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);
        }
Пример #9
0
        /// <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);
        }
Пример #10
0
        /// <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);
        }