public static bool CanBeNull(string columnName, string tableName)
{
string query = $"SELECT is_nullable FROM sys.columns WHERE object_id = object_id('{tableName}') AND name = '{columnName}';";
string res = DBAccess.GetStringsWithQuery(query)[0];
return(bool.Parse(res));
}
/// <summary>
/// Gets the number of appointments on each day of the week
/// </summary>
public static void GetAppsByDayOfWeek(ref double[][] data, ref string[] headers, DateTime minDate)
{
string query = "SET DATEFIRST 1; SELECT Count([Appointment ID]) FROM [Appointment] " +
$"WHERE [Appointment Date] BETWEEN '{minDate:yyyy-MM-dd}' AND '{DateTime.Now:yyyy-MM-dd}' AND [Cancelled] = 0 " +
"GROUP BY DatePart(WeekDay, [Appointment Date]) ORDER BY DatePart(WeekDay, [Appointment Date]);";
data[0] = DBAccess.GetStringsWithQuery(query).Select(x => Convert.ToDouble(x)).ToArray();
headers = new string[] { "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun" };
}
public static void GetBusinessOfStaff(ref double[][] data, ref string[] headers, DateTime minDate)
{
string dataQuery = "SELECT Count([Staff ID]) FROM [Appointment] " +
$"WHERE [Appointment Date] BETWEEN '{minDate:yyyy-MM-dd}' AND '{DateTime.Now:yyyy-MM-dd}' AND [Cancelled] = 0 " +
"GROUP BY [Staff ID] ORDER BY [Staff ID];";
data[0] = DBAccess.GetStringsWithQuery(dataQuery).Select(double.Parse).ToArray();
headers = DBAccess.GetStringsWithQuery("SELECT [Staff Name] FROM [Staff] ORDER BY [Staff ID];").ToArray();
}
/// <summary>
/// Gets the number of appointments in each month of the last year
/// </summary>
public static void GetBookingsInMonths(ref double[][] data, ref string[] headers, DateTime minDate)
{
string query = "SELECT Count([Appointment ID]) FROM [Appointment] " +
$"WHERE [Appointment Date] BETWEEN '{DateTime.Now.AddYears(-1):yyyy-MM-dd}' AND '{DateTime.Now:yyyy-MM-dd}' AND [Cancelled] = 0 " +
"GROUP BY DatePart(Month, [Appointment Date]) ORDER BY DatePart(Month, [Appointment Date]);";
data[0] = DBAccess.GetStringsWithQuery(query).Select(x => Convert.ToDouble(x)).ToArray();
headers = months;
}
public static bool DoesUse2FA(string username, ref string email)
{
bool uses2FA = DBAccess.GetStringsWithQuery("SELECT [Staff Uses 2FA] FROM [Staff] WHERE [Staff].[Staff Name] = '" + username + "';")[0] == "True";
if (uses2FA)
{
email = DBAccess.GetStringsWithQuery("SELECT [Staff Email] FROM [Staff] WHERE [Staff].[Staff Name] = '" + username + "';")[0];
}
return(uses2FA);
}
internal static string GetMinKeyNotUsed(string table, string col, List <BookingCreator> booking)
{
string res = DBAccess.GetStringsWithQuery($"SELECT Max([{col}]) FROM [{table}];")[0];
if (res == "")
{
return((booking.Select(b => b.GetData().Count).Sum() + 1).ToString());
}
int id = Convert.ToInt32(res);
id += booking.Select(b => b.GetData().Count).Sum() + 1;
return(id.ToString());
}
public static string GetMinKeyNotUsed(string table, string col)
{
List <string> res = DBAccess.GetStringsWithQuery($"SELECT TOP 1 t1.[{col}]+1 FROM [{table}] t1 WHERE NOT EXISTS(SELECT * FROM [{table}] t2 WHERE t2.[{col}] = t1.[{col}] + 1) ORDER BY t1.[{col}]");
if (res.Count == 0)
{
return("0");
}
else
{
return(res[0]);
}
}
public static List <List <string> > GetInvoiceData(string clientID)
{
double[] basePrices = DBAccess.GetStringsWithQuery("SELECT [Base Price] FROM [Appointment Type];").Select(double.Parse).ToArray();
string query = "SELECT [Appointment].[Booking ID], [Appointment].[Appointment ID], [Dog].[Dog Name], " +
"[Appointment Type].[Description], [Staff].[Staff Name], [Appointment].[Nails And Teeth], " +
"[Appointment].[Appointment Date], [Appointment].[Appointment Time], " +
"[Appointment].[Appointment Type ID] " +
"FROM [Appointment] INNER JOIN [Staff] ON [Staff].[Staff ID] = [Appointment].[Staff ID] " +
"INNER JOIN [Dog] ON [Dog].[Dog ID] = [Appointment].[Dog ID] " +
"INNER JOIN [Appointment Type] ON [Appointment Type].[Appointment Type ID] = [Appointment].[Appointment Type ID] " +
$"WHERE [Dog].[Client ID] = {clientID} AND " +
$"[Appointment].[Appointment Date] BETWEEN '{DateTime.Now.AddMonths(-12):yyyy-MM-dd}' AND '{DateTime.Now:yyyy-MM-dd}' " +
"AND [Appointment].[Cancelled] = 'False' ORDER BY [Appointment].[Appointment Date];";
List <List <string> > results = DBAccess.GetListStringsWithQuery(query);
foreach (List <string> ls in results)
{
int appTypeID = Convert.ToInt32(ls[^ 1]);
/// <summary>
/// Gets the number of clients over time
/// </summary>
public static void GetGrowthOverTime(ref double[][] data, ref string[] headers, DateTime minDate)
{
List <string> ls = DBAccess.GetStringsWithQuery("SELECT MIN([Client Join Date]) FROM [Client]");
if (ls[0] == "")
{
return;
}
DateTime startDate = MaxDate(Convert.ToDateTime(ls[0]), minDate);
DateTime endDate = DateTime.Now.Date;
int diff = (int)(endDate - startDate).TotalDays;
List <double> growth = new List <double>();
for (double i = 0; i < diff; i += diff / 75.0)
{
string query = $"SELECT COUNT([Client ID]) FROM [Client] WHERE [Client Join Date] < '{startDate.AddDays(i):yyyy-MM-dd}';";
growth.Add(Convert.ToInt32(DBAccess.GetStringsWithQuery(query)[0]));
}
data[0] = growth.ToArray();
headers = InterpolateDates(startDate, diff);
}
// No longer used, but kept around as it could be useful some day.
public static void GetCustReturns(ref double[][] data, ref string[] headers, DateTime minDate)
{
DateTime startDate = MaxDate(Convert.ToDateTime(DBAccess.GetStringsWithQuery("SELECT MIN([Client Join Date]) FROM [Client]")[0]), minDate);
DateTime endDate = DateTime.Now;
double diff = (endDate - startDate).TotalDays;
List <double> returns = new List <double>();
int count = 0;
for (double i = 0; i < diff; i += diff / 40.0)
{
string query = "SELECT b.[Appointment Date] FROM [Appointment] AS a " +
"CROSS APPLY (SELECT TOP 1 [Appointment Date] From [Appointment] WHERE [Dog ID] = a.[Dog ID] ORDER BY [Appointment Date] desc) as b " +
$"WHERE b.[Appointment Date] BETWEEN '{startDate.Add(TimeSpan.FromDays(i - diff / 40.0)):yyyy-MM-dd}' AND '{startDate.Add(TimeSpan.FromDays(i)):yyyy-MM-dd}';";
List <List <string> > result = DBAccess.GetListStringsWithQuery(query);
returns.Add(result.Count);
count++;
}
data[0] = returns.ToArray();
headers = InterpolateDates(startDate, (int)diff);
}
public static bool IsAppInShift(int dow, string staffID, TimeSpan appStart, TimeSpan appEnd, DateTime appDate)
{
bool isInShift = false;
string shiftQuery = $"SELECT [Shift Start Time], [Shift End Time] FROM [Shift] WHERE [Shift].[Staff ID] = {staffID} AND [Shift].[Shift Day] = {dow};";
List <List <string> > shiftData = DBAccess.GetListStringsWithQuery(shiftQuery);
foreach (List <string> shift in shiftData)
{
TimeSpan shiftStart = TimeSpan.Parse(shift[0]);
TimeSpan shiftEnd = TimeSpan.Parse(shift[1]);
isInShift = (appStart >= shiftStart && appEnd <= shiftEnd) || isInShift;
// Note: If a shift starts in shift A and ends in shift B, and shift A and B have no time gap between them,
// The result will still be marked as clashing. This is an unsupported use case.
}
string shiftExcQuery = $"SELECT [Shift Exception ID] FROM [Shift Exception] WHERE [Staff ID] = {staffID} AND [Start Date] <= '{appDate:yyyy-MM-dd}' AND [End Date] >= '{appDate:yyyy-MM-dd}';";
List <string> shiftExcData = DBAccess.GetStringsWithQuery(shiftExcQuery);
return(isInShift && shiftExcData.Count == 0);
}
/// <summary>
/// Gets a rolling average of what % of appointments have been cancelled over time
/// </summary>
public static void GetAppCancelRate(ref double[][] data, ref string[] headers, DateTime minDate)
{
string ls = DBAccess.GetStringsWithQuery("SELECT MIN([Appointment Date]) FROM Appointment")[0];
if (ls == "")
{
return;
}
DateTime startDate = MaxDate(Convert.ToDateTime(ls), minDate);
DateTime endDate = DateTime.Now;
int diff = (int)(endDate - startDate).TotalDays;
double increment = diff / 75.0;
List <double> cancelRate = new List <double>();
for (double i = 0; i < diff; i += increment)
{
DateTime currentDate = startDate.AddDays(i);
string totalInTimeQuery = "SELECT COUNT([Appointment ID]) FROM [Appointment] " +
$"WHERE [Appointment Date] BETWEEN '{currentDate.AddDays(-increment * 10):yyyy-MM-dd}' AND '{currentDate:yyyy-MM-dd}';";
double totalInTime = Convert.ToInt32(DBAccess.GetStringsWithQuery(totalInTimeQuery)[0]);
string cancelledInTimeQuery = "SELECT COUNT([Appointment ID]) FROM [Appointment] " +
$"WHERE [Cancelled] = 1 AND [Appointment Date] BETWEEN '{currentDate.AddDays(-increment * 10):yyyy-MM-dd}' AND '{currentDate:yyyy-MM-dd}';";
double cancelledInTime = Convert.ToInt32(DBAccess.GetStringsWithQuery(cancelledInTimeQuery)[0]);
if (cancelledInTime == 0 || totalInTime == 0)
{
cancelRate.Add(0);
}
else
{
cancelRate.Add(cancelledInTime * 100 / totalInTime);
}
}
data[0] = cancelRate.ToArray();
headers = InterpolateDates(startDate, diff);
}
public static string GetNewCusts(DateTime minDate)
{
return(DBAccess.GetStringsWithQuery($"SELECT Count([Client ID]) FROM [Client] WHERE [Client].[Client Join Date] BETWEEN '{minDate:yyyy-MM-dd}' AND '{DateTime.Now:yyyy-MM-dd}';")[0]);
}
public static double GetBookingDiscount(string bookingID)
{
return(Convert.ToInt32(DBAccess.GetStringsWithQuery($"SELECT CASE WHEN Count([Appointment ID]) > 2 THEN 5 ELSE 0 END FROM [Appointment] WHERE [Appointment].[Booking ID] = {bookingID};")[0]));
}
/// <summary>
/// Returns the names of all tables
/// </summary>
public static List <string> GetTableNames()
{
return(DBAccess.GetStringsWithQuery("SELECT [TABLE_NAME] FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_TYPE] = 'BASE TABLE';"));
}
public static List <string> GetAllTableNames()
{
return(DBAccess.GetStringsWithQuery("SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'"));
}
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 IsPKeyFree(string table, string column, string value)
{
return(DBAccess.GetStringsWithQuery($"SELECT COUNT([{column}]) FROM [{table}] WHERE [{column}] = '{value}';")[0] == "0");
}
public static bool IsNameTaken(string name)
{
return(DBAccess.GetStringsWithQuery("SELECT COUNT([Staff ID]) FROM [Staff] WHERE [Staff].[Staff Name] = '" + name + "';")[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");
}
