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}';");
}
