public static double GetIncomeSince(DateTime minDate, DateTime maxDate)
{
// Note: Does not take into account is appointment initial, discount based on booking count
string query = "SELECT " +
"CASE " +
"WHEN [Appointment Type ID] = 0 THEN 35 " +
"WHEN [Appointment Type ID] = 1 THEN 40 " +
"WHEN [Appointment Type ID] = 2 THEN 50 " +
"ELSE 0 " +
"END, " +
"CASE " +
"WHEN [Nails And Teeth] = 'True' THEN 10 " +
"ELSE 0 " +
"END " +
$"FROM [Appointment] WHERE [Paid] = 1 AND [Cancelled] = 0 AND [Appointment Date] BETWEEN '{minDate:yyyy-MM-dd}' AND '{maxDate:yyyy-MM-dd}';";
List <List <string> > allPriceData = DBAccess.GetListStringsWithQuery(query);
double income = 0;
foreach (List <string> appData in allPriceData)
{
double appIncome = Convert.ToDouble(appData[0]);
appIncome += Convert.ToDouble(appData[1]);
//appIncome = appIncome * (100.0 - GetBookingDiscount(appData[2]));
income += appIncome;
//income += CalculateAppointmentPrice(app.ToArray());
}
return(Math.Round(income, 2));
}
public static bool IsColumnPrimaryKey(string columnName, string tableName)
{
string query = "SELECT K.CONSTRAINT_NAME " +
"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K " +
"ON C.TABLE_NAME = K.TABLE_NAME AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA " +
$"AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY' AND K.COLUMN_NAME = '{columnName}' AND K.TABLE_NAME = '{tableName}';";
return(DBAccess.GetListStringsWithQuery(query).Count > 0);
}
public static bool IsLoginDataCorrect(string name, string password)
{
List <List <string> > userData = DBAccess.GetListStringsWithQuery($"SELECT * FROM [Staff] WHERE [Staff].[Staff Name] = '{name}';");
if (userData.Count == 0)
{
return(false);
}
else
{
return(GetSecureHash(password, userData[0][3]) == userData[0][2]);
}
}
public static ForeignKey[] GetFKeyToTable(string tableName)
{
string query = "SELECT OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) ColName " +
"FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id " +
$"INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id WHERE OBJECT_NAME(f.referenced_object_id) = '{tableName}';";
List <List <string> > results = DBAccess.GetListStringsWithQuery(query);
ForeignKey[] toReturn = new ForeignKey[results.Count];
for (int i = 0; i < results.Count; i++)
{
toReturn[i] = new ForeignKey(results[i][0], results[i][1]);
}
return(toReturn);
}
public static ForeignKey[] GetFKeyOfTable(string tableName)
{
string query = $"SELECT tab2.name, col2.name " +
"FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id " +
"INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id " +
"INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id " +
"INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON " +
$"col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE tab1.name = '{tableName}';";
List <List <string> > results = DBAccess.GetListStringsWithQuery(query);
ForeignKey[] toReturn = new ForeignKey[results.Count];
for (int i = 0; i < results.Count; i++)
{
toReturn[i] = new ForeignKey(results[i][0], results[i][1]);
}
return(toReturn);
}
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]);
public static void GetGrossProfitLastYear(ref double[][] data, ref string[] headers, DateTime minDate)
{
headers = new string[12];
data[0] = new double[12];
DateTime endDate = DateTime.Now.Date;
DateTime startDate = endDate.AddMonths(-12);
for (int i = 0; i < 12; i++)
{
DateTime curDate = startDate.AddMonths(i);
headers[i] = months[curDate.Month - 1];
// Note: Does not consider if is first booking and booking discount
string query = "SELECT " +
"CASE " +
"WHEN [Appointment Type ID] = 0 THEN 35 " +
"WHEN [Appointment Type ID] = 1 THEN 40 " +
"WHEN [Appointment Type ID] = 2 THEN 50 " +
"ELSE 0 " +
"END, " +
"CASE " +
"WHEN [Nails And Teeth] = 'True' THEN 10 " +
"ELSE 0 " +
"END " +
$"FROM [Appointment] WHERE [Paid] = 1 AND [Cancelled] = 0 AND DatePart(month, [Appointment Date]) = {curDate.Month} AND DatePart(year, [Appointment Date]) = {curDate.Year};";
List <List <string> > dataFromMonth = DBAccess.GetListStringsWithQuery(query);
double incomeFromMonth = 0;
foreach (List <string> appData in dataFromMonth)
{
double appIncome = Convert.ToDouble(appData[0]);
appIncome += Convert.ToDouble(appData[1]);
//appIncome = appIncome * (100.0 - GetBookingDiscount(appData[2]));
//income += CalculateAppointmentPrice(app.ToArray());
incomeFromMonth += appIncome - 43.4;
}
incomeFromMonth = (incomeFromMonth * 15 + 4000) * 1.3;
data[0][i] = incomeFromMonth;
}
}
// 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);
}
public static List <List <string> > GetDataTypesFromTable(string tableName)
{
string query = $"SELECT Column_Name, Data_Type, Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = '{tableName}';";
return(DBAccess.GetListStringsWithQuery(query));
}
/// <summary>
/// Gets all data from the specified table
/// </summary>
public static List <List <string> > GetAllFromTable(string tableName, string[] headers = null)
{
return(DBAccess.GetListStringsWithQuery("SELECT * FROM [" + tableName + "];", headers));
}
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 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);
}
