//Made up data used to test database functions
public static void EventLogData()
{
const string insert = "SELECT ID FROM DoorEventTable";
var dt = Datatable.DataTable(insert, "");
if (dt.Rows.Count < 1)
{
DataToLog("2020-10-23 10:07", "DÖIN", "LGH0201", "0201B");
DataToLog("2020-10-23 10:08", "DÖUT", "LGH0201", "0201B");
DataToLog("2020-10-23 10:19", "DÖIN", "LGH0302", "0302A");
DataToLog("2020-10-23 10:19", "DÖIN", "LGH0201", "0201A");
DataToLog("2020-10-23 10:20", "DÖUT", "SOPRUM", "0302A");
DataToLog("2020-10-23 10:20", "DÖIN", "UT", "0201A");
DataToLog("2020-10-23 10:21", "DÖIN", "SOPRUM", "0302A");
DataToLog("2020-10-23 10:22", "DÖIN", "LGH0302", "0302A");
DataToLog("2020-10-23 10:55", "DÖIN", "LGH0202", "0202A");
DataToLog("2020-10-23 10:56", "DÖUT", "LGH0202", "0202A");
DataToLog("2020-10-23 11:03", "DÖIN", "LGH0301", "0301D");
DataToLog("2020-10-23 11:04", "DÖUT", "LGH0301", "0301D");
DataToLog("2020-10-23 11:05", "DÖUT", "TVÄTT", "0301D");
DataToLog("2020-10-23 11:07", "DÖIN", "SOPRUM", "0102A");
DataToLog("2020-10-23 12:07", "DÖUT", "UT", "0201C");
DataToLog("2020-10-23 13:20", "DÖUT", "SOPRUM", "0102A");
}
}
//List all tenants in a specific room/apartment
public static DataTable ListTenantsAt(string apartmentNumber)
{
var find = @"SELECT(TenantTable.FirstName || ' ' || TenantTable.LastName) AS Name
FROM TenantTable
WHERE TenantTable.ApartmentNumber_ID = (SELECT ID FROM DoorLocationTable WHERE ApartmentNumber = @values)
LIMIT " + MaxEntries;
var dt = Datatable.DataTable(find, apartmentNumber.ToUpper());
return(dt);
}
//Find all events from a person
public static DataTable FindEntriesByTenant(string tenantName)
{
var find = @"SELECT OpeningDate,DoorName.DoorName,DoorEvent.EventCode,TenantTable.TenantTag,(TenantTable.FirstName || ' ' || TenantTable.LastName) AS Name
FROM DoorEventTable
JOIN DoorName ON DoorEventTable.DoorID = DoorName.ID
JOIN DoorEvent ON DoorEventTable.DoorEventType = DoorEvent.ID
JOIN TenantTable ON DoorEventTable.TenantID = TenantTable.ID
WHERE TenantTable.FirstName LIKE @values OR (TenantTable.FirstName || ' ' || TenantTable.LastName) = @values
ORDER BY OpeningDate DESC
LIMIT " + MaxEntries;
var dt = Datatable.DataTable(find, tenantName);
return(dt);
}
//Find all events from a room/apartment
public static DataTable FindEntriesByLocation(string doorLocation)
{
var find = @"SELECT OpeningDate,DoorName.DoorName,DoorEvent.EventCode,TenantTable.TenantTag,(TenantTable.FirstName || ' ' || TenantTable.LastName) AS Name
FROM DoorEventTable
JOIN DoorName ON DoorEventTable.DoorID = DoorName.ID
JOIN DoorEvent ON DoorEventTable.DoorEventType = DoorEvent.ID
JOIN TenantTable ON DoorEventTable.TenantID = TenantTable.ID
WHERE DoorName.DoorName = 'LGH' || @values OR DoorName.DoorName = 'BLK' || @values OR DoorName.DoorName = @values
ORDER BY OpeningDate DESC
LIMIT " + MaxEntries;
var dt = Datatable.DataTable(find, doorLocation.ToUpper());
return(dt);
}
//Pre-recorded inputs to database for testing
internal static void BuildingAndTenantData()
{
using var conn = new SQLiteConnection("data source=" + Settings.Database);
conn.Open();
string insert, table;
SQLiteCommand cmd;
table = "SELECT ID FROM TenantTable";
var dt = Datatable.DataTable(table, "");
if (dt.Rows.Count < 1)
{
insert = "INSERT INTO TenantTable(TenantTag,ApartmentNumber_ID,FirstName,LastName) " +
"VALUES('0101A','0','Liam','Jönsson'),('0102A','1','Elias','Petterson')," +
"('0102B','1','Wilma','Johansson'),('0103A','2','Alicia','Sanchez')," +
"('0103B','2','Aaron','Sanchez'),('0201A','3','Olivia','Erlander')," +
"('0201B','3','William','Erlander'),('0201C','3','Alexander','Erlander')," +
"('0201D','3','Astrid','Erlander'),('0202A','4','Lucas','Adolfsson')," +
"('0202B','4','Ebba','Adolfsson'),('0202C','4','Lilly','Adolfsson')," +
"('0301A','5','Ella','Ahlström'),('0301B','5','Alma','Alfredsson')," +
"('0301C','5','Elsa','Ahlström'),('0301D','5','Maja','Ahlström')," +
"('0302A','6','Noah','Almgren'),('0302B','6','Adam','Andersen')," +
"('0302C','6','Kattis','Backman'),('0302D','6','Oscar','Chen')," +
"('VAKT01','7','Vaktmästare','')";
using (cmd = new SQLiteCommand(insert, conn))
{
cmd.ExecuteNonQuery();
}
}
table = "SELECT ID FROM DoorName";
dt = Datatable.DataTable(table, "");
if (dt.Rows.Count < 1)
{
insert = "INSERT INTO DoorName(DoorName) " +
"VALUES ('LGH0101'),('LGH0102')," +
"('LGH0103'),('LGH0201')," +
"('LGH0202'),('LGH0301')," +
"('LGH0302'),('VAKT')," +
"('SOPRUM'),('TVÄTT')," +
"('UT')";
cmd = new SQLiteCommand(insert, conn);
cmd.ExecuteNonQuery();
}
table = "SELECT ID FROM DoorEvent";
dt = Datatable.DataTable(table, "");
if (dt.Rows.Count < 1)
{
insert = "INSERT INTO DoorEvent(EventCode) " +
"VALUES ('DÖIN'),('DÖUT')," +
"('FDIN'),('FDUT')";
cmd = new SQLiteCommand(insert, conn);
cmd.ExecuteNonQuery();
}
table = "SELECT ID FROM DoorLocationTable";
dt = Datatable.DataTable(table, "");
if (dt.Rows.Count < 1)
{
insert = "INSERT INTO DoorLocationTable(ApartmentNumber) " +
"VALUES('0101'),('0102')," +
"('0103'),('0201')," +
"('0202'),('0301')," +
"('0302'),('VAKT')," +
"('SOPRUM'),('TVÄTT')";
cmd = new SQLiteCommand(insert, conn);
cmd.ExecuteNonQuery();
}
}