public static bool IsAppointmentInitial(string[] data, List <BookingCreator> booking) { string dogID = data[1]; if (dogID == "") { return(false); } List <List <string> > results = DBAccess.GetListStringsWithQuery($"SELECT TOP 1 [Appointment].[Appointment ID], [Appointment].[Appointment Date], [Appointment].[Appointment Time] FROM [Appointment] INNER JOIN [Dog] ON [Dog].[Dog ID] = [Appointment].[Dog ID] WHERE [Dog].[Dog ID] = {dogID} AND [Appointment].[Cancelled] = 'False' ORDER BY [Appointment].[Appointment Date], [Appointment].[Appointment Time];"); if (data[9] == "" || data[10] == "") { return(false); } bool isValidDate = DateTime.TryParse(data[9], out DateTime date); bool isValidTime = TimeSpan.TryParse(data[10], out TimeSpan time); if (!isValidDate || !isValidTime) { return(false); } DateTime compDateTime = date.Add(time); if (booking is not null) { foreach (BookingCreator b in booking) { if (!b.IsAdded) { continue; } List <string[]> bkData = b.GetData(); foreach (string[] bk in bkData) { if (bk[9] == "" || bk[10] == "") { continue; } if (bk[1] == data[1]) { DateTime bkDateTime = DateTime.Parse(bk[9]).Add(TimeSpan.Parse(bk[10])); if (compDateTime > bkDateTime) { return(false); } } } } } if (results.Count == 0) { return(true); } if (data[0] == results[0][0]) { return(true); } DateTime initDateTime = DateTime.Parse(results[0][1]).Add(TimeSpan.Parse(results[0][2])); return(compDateTime <= initDateTime); }
public static bool IsPKeyFree(string table, string column, string value) { return(DBAccess.GetStringsWithQuery($"SELECT COUNT([{column}]) FROM [{table}] WHERE [{column}] = '{value}';")[0] == "0"); }
public static bool IsFKeyRefUsed(string table, string col, ForeignKey fKey, string dataCondition) { return(Convert.ToInt32(DBAccess.GetStringsWithQuery($"SELECT Count([{table}].[{col}]) FROM [{table}] INNER JOIN [{fKey.ReferencedTable}] ON [{fKey.ReferencedTable}].[{col}] = [{table}].[{col}] WHERE [{table}].[{col}] = '{dataCondition}';")[0]) > 0); }
public static bool DoesMeetForeignKeyReq(ForeignKey fKey, string data) { return(DBAccess.GetStringsWithQuery($"SELECT COUNT([{fKey.ReferencedColumn}]) FROM [{fKey.ReferencedTable}] WHERE [{fKey.ReferencedColumn}] = '{data}';")[0] != "0"); }
public static List <List <string> > GetByColumnData(string table, string column, string toMatch, string[] headers = null) { return(DBAccess.GetListStringsWithQuery($"SELECT * FROM [{table}] WHERE [{column}] = '{toMatch}';", headers)); }
public static List <List <string> > GetAllAppointmentsOnDay(DateTime day, string[] headers) { return(DBAccess.GetListStringsWithQuery("SELECT * FROM [Appointment] WHERE [Appointment Date] = '" + day.ToString("yyyy-MM-dd") + "' AND [Cancelled] = 'False';", headers)); }
public static bool DoesAppointmentClash(string[] oldData, int roomID, DateTime date, TimeSpan time, List <BookingCreator> bookings, out string errMessage) { int thisAppLength = GetAppLength(oldData, bookings); TimeSpan appEnd = time.Add(new TimeSpan(0, thisAppLength, 0)); foreach (BookingCreator booking in bookings) { if (!booking.IsAdded) { continue; } List <string[]> bkData = booking.GetData(); foreach (string[] bk in bkData) { if (bk[0] == oldData[0]) // Cannot clash with itself { if (date < DateTime.Now.Date) { errMessage = "An appointment cannot be booked in the past!"; return(true); } continue; } if (bk is null) { continue; } if (bk[9] == "" || bk[10] == "") { continue; // Booking has not yet been made } if ( (bk[5] == roomID.ToString() || // Same room bk[1] == oldData[1] || // Same dog bk[3] == oldData[3]) && // Same staff DateTime.Parse(bk[9]).Date == date) { TimeSpan bkStart = TimeSpan.Parse(bk[10]); int bkLength = GetAppLength(bk, bookings); TimeSpan bkEnd = bkStart.Add(new TimeSpan(0, bkLength, 0)); if ((bkEnd > time && bkStart < time) || (bkStart < appEnd && bkStart >= time)) { errMessage = "Clashes with a new appointment!"; return(true); } } } } string query = $"SELECT * FROM [Appointment] WHERE [Appointment].[Appointment Date] = '{date:yyyy-MM-dd}' " + $"AND [Appointment].[Appointment Time] < '{appEnd}' AND [Appointment].[Cancelled] = 'False';"; List <List <string> > allOnDay = DBAccess.GetListStringsWithQuery(query); // An appointment cannot clash with itself, so remove the appointment with the same unique ID (If it exists) allOnDay.Remove(allOnDay.Where(a => a[0] == oldData[0]).FirstOrDefault()); List <List <string> > potentialCollisions = new List <List <string> >(); foreach (List <string> ls in allOnDay) { int appLength = GetAppLength(ls.ToArray(), bookings); TimeSpan localAppEnd = TimeSpan.Parse(ls[10]).Add(new TimeSpan(0, appLength, 0)); if (localAppEnd > time) { potentialCollisions.Add(ls); } } foreach (List <string> ls in potentialCollisions) { if (ls[1] == oldData[1]) { errMessage = "That dog is at another appointment at the same time!"; return(true); // A dog cannot be in 2 appointments at once } if (ls[3] == oldData[3]) { errMessage = "A staff member cannot be at 2 simultanious appointments!"; return(true); // A staff member cannot be at 2 appointments at once } } List <List <string> > inRoom = potentialCollisions.Where(a => a[5] == roomID.ToString()).ToList(); if (inRoom.Count > 0) { errMessage = "There is another appointment in that room at that time!"; return(true); } // Check if the staff member is available string staffID = oldData[3]; int dow = (int)(date.DayOfWeek + 6) % 7; if (!IsAppInShift(dow, staffID, time, appEnd, date.Date)) { errMessage = "That staff member's shift does not cover that time!"; return(true); } errMessage = ""; return(false); }
public static void UpdateColumn(string table, string newData, string columnToUpdate, string idColumn, string id) { DBAccess.ExecuteNonQuery($"UPDATE [{table}] SET [{columnToUpdate}] = '{newData}' WHERE [{idColumn}] = '{id}';"); }