//Returns DataTable showing all tenants in a location
public DataTable ListTenantsAt(string location)
{
//string sql = "SELECT Tenants.tenant, Tenants.Location, Tenants.Tag FROM Tenants WHERE Tenants.Location = '@Loction'", "@Location", location;
string sql = @"SELECT Tenants.tenant, Tenants.Location, Tenants.Tag FROM Tenants WHERE Tenants.Location = @Location";
DataTable data = DatabaseCommunication.GetDataTable(sql, "@Location", location);
return(data);
}
public static void CreateMainDatabase()
{
//Create database
SQLiteConnection.CreateFile(database);
//Create tables
DatabaseCommunication.ExecuteSQL("CREATE TABLE Tenants (ID INTEGER NOT NULL UNIQUE, Tenant TEXT NOT NULL, Location TEXT, Tag TEXT UNIQUE, PRIMARY KEY(ID AUTOINCREMENT))");
DatabaseCommunication.ExecuteSQL("CREATE TABLE Event (ID INTEGER NOT NULL UNIQUE, Event TEXT, PRIMARY KEY(ID AUTOINCREMENT))");
DatabaseCommunication.ExecuteSQL("CREATE TABLE Door (ID INTEGER NOT NULL UNIQUE, Door TEXT NOT NULL, Location TEXT, PRIMARY KEY(ID AUTOINCREMENT))");
DatabaseCommunication.ExecuteSQL("CREATE TABLE LogEntry (ID INTEGER NOT NULL UNIQUE, Date TEXT, Door TEXT, Tag TEXT, Event TEXT, PRIMARY KEY(ID AUTOINCREMENT))");
}
//Returns DataTable showing all entries by event in LogEntry
public DataTable FindEntriesByEvent(string eventInput)
{
string sql = @"SELECT LogEntry.Date, LogEntry.Door, LogEntry.Tag, LogEntry.Event,
Tenants.Location, Tenants.Tenant
FROM LogEntry
JOIN Tenants ON LogEntry.Tag=Tenants.Tag
WHERE LogEntry.Event LIKE @EventInput
ORDER BY LogEntry.Date DESC
LIMIT " + maxEntries + "";
DataTable data = DatabaseCommunication.GetDataTable(sql, "@EventInput", "%" + eventInput + "%");
return(data);
}
//Returns DataTable showing all entries by tag in LogEntry
public DataTable FindEntriesByTag(string tag)
{
string sql = @"SELECT LogEntry.Date, LogEntry.Door, LogEntry.Tag, LogEntry.Event,
Tenants.Location, Tenants.Tenant
FROM LogEntry
JOIN Tenants ON LogEntry.Tag=Tenants.Tag
WHERE Tenants.Tag LIKE @Tag
ORDER BY LogEntry.Date DESC
LIMIT " + maxEntries + "";
DataTable data = DatabaseCommunication.GetDataTable(sql, "@Tag", "%" + tag + "%");
return(data);
}
public static void InsertIntoTables()
{
//Creates a new object of the class StaticData
StaticData obj = new StaticData();
//Adds tenants to table
for (int i = 0; i < obj.TenantsArray.Length; i++)
{
DatabaseCommunication.ExecuteSQL("Insert Into Tenants (Tenant, Tag) Values (@Tenant, @Tag)", "@Tenant", obj.TenantsArray[i], "@Tag", obj.TagArray[i]);
}
//Updates Tenants.Location with values
for (int i = 0; i < obj.LocationArray.Length; i++)
{
//DatabaseCommunication.ExecuteSQL($@"UPDATE Tenants SET ""Location"" = '{obj.LocationArray[i]}' WHERE Tag LIKE '{obj.LocationArray[i]}%';");
DatabaseCommunication.ExecuteSQL("UPDATE Tenants SET Location = @Location WHERE Tenants.Tag LIKE @Location2;",
"@Location", obj.LocationArray[i],
"@Location2", obj.LocationArray[i] + "%"
);
}
//Adds values to Event.Event
for (int i = 0; i < obj.EventArray.Length; i++)
{
DatabaseCommunication.ExecuteSQL("INSERT INTO Event (Event) Values (@Event)", "@Event", obj.EventArray[i]);
}
//Adds values to Door.Door
for (int i = 0; i < obj.DoorArray.Length; i++)
{
DatabaseCommunication.ExecuteSQL("INSERT INTO Door (Door) Values (@Door)", "@Door", obj.DoorArray[i]);
}
//Updates Door.Location with values
for (int i = 0; i < obj.LocationArray.Length; i++)
{
//DatabaseCommunication.ExecuteSQL($@"UPDATE Door SET ""Location"" = '{obj.LocationArray[i]}' WHERE Door.Door LIKE '%{obj.LocationArray[i]}';");
DatabaseCommunication.ExecuteSQL("UPDATE Door SET Location = @Location WHERE Door.Door LIKE @Location2",
"@Location", obj.LocationArray[i],
"@Location2", "%" + obj.LocationArray[i] + "%"
);
}
}
//Methods adds logs (from test data) to database
public void LogEntry(string date, string door, string eventCode, string tag)
{
DatabaseCommunication.ExecuteSQL("INSERT INTO LogEntry (Date, Door, Event, Tag) VALUES (@date, @door, @eventCode, @tag) ", "@date", date, "@door", door, "@eventCode", eventCode, "@tag", tag);
}