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