public static Task <Layout> GetLayoutAsync(string layoutAddress)
{
// Create a new database connection
SqliteConnection sqlite_conn = new SqliteConnection("Data Source=" + _dbLocation + ";");
sqlite_conn.Open();
SqliteDataReader sqlite_datareader = ReadFrom(sqlite_conn, "SELECT * FROM Layout WHERE BuildingAddress = \"" + Sanitize(layoutAddress) + "\";");
Layout l = new Layout();
// Initialize Layout Data
while (sqlite_datareader.Read())
{
l.Name = sqlite_datareader.GetString(0);
l.Address = sqlite_datareader.GetString(1);
// TODO PG -> use images instead of encoding strings to make this more memory efficient
if (!sqlite_datareader.IsDBNull(2))
{
string base64Enoding = sqlite_datareader.GetString(2);
l.LayoutImage = base64Enoding;
}
}
// Initialize Areas
sqlite_datareader = ReadFrom(sqlite_conn, "SELECT * FROM Area WHERE LayoutName = \"" + Sanitize(l.Name) + "\";");
List <Area> areas = new List <Area>();
while (sqlite_datareader.Read())
{
Area a = new Area();
a.AreaType = (Area.Type)sqlite_datareader.GetInt64(0);
float x = sqlite_datareader.GetFloat(1);
float y = sqlite_datareader.GetFloat(2);
a.AreaLocation = new PointF(x, y);
a.NumberOfSeats = sqlite_datareader.GetInt32(3);
a.Name = sqlite_datareader.GetString(4);
a.LayoutName = sqlite_datareader.GetString(5);
a.LayoutAddress = sqlite_datareader.GetString(6);
areas.Add(a);
}
l.Areas = areas;
// Add reservation data to Areas
foreach (Area a in areas)
{
sqlite_datareader = ReadFrom(sqlite_conn, "SELECT * FROM Reserves WHERE AreaX = " + a.AreaLocation.X + " AND AreaY = " + a.AreaLocation.Y + ";");
while (sqlite_datareader.Read())
{
Reservation r = new Reservation();
r.Id = sqlite_datareader.GetString(0);
r.Date = DateTime.Parse(sqlite_datareader.GetString(1));
r.Duration = sqlite_datareader.GetFloat(2);
// Customer fetching
string customerEmail = sqlite_datareader.GetString(3);
Customer c = new Customer();
SqliteDataReader customerReader = ReadFrom(sqlite_conn, "SELECT * FROM Customer WHERE Email = \"" + Sanitize(customerEmail) + "\";");
while (customerReader.Read())
{
c.Email = customerReader.GetString(0);
c.FirstName = customerReader.GetString(1);
c.LastName = customerReader.GetString(2);
}
r.Customer = c;
a.Reservations.Add(r);
}
}
if (!l.IsDefined())
{
l = null;
}
sqlite_conn.Close();
return(Task.FromResult(l));
}
public static Task <Layout[]> GetAdminLayoutsAsync(string adminEmail)
{
// Create a new database connection
SqliteConnection sqlite_conn = new SqliteConnection("Data Source=" + _dbLocation + ";");
sqlite_conn.Open();
List <Layout> layoutList = new List <Layout>();
SqliteDataReader layoutsDatareader = ReadFrom(sqlite_conn, $"SELECT * FROM Layout l, Manages m WHERE l.BuildingAddress = m.BuildingAddress AND m.Email = \'{Sanitize(adminEmail)}\';");
// Initialize Layout Data
while (layoutsDatareader.Read())
{
Layout l = new Layout();
l.Name = layoutsDatareader.GetString(0);
l.Address = layoutsDatareader.GetString(1);
// TODO PG -> use images instead of encoding strings to make this more memory efficient
if (!layoutsDatareader.IsDBNull(2))
{
string base64Enoding = layoutsDatareader.GetString(2);
l.LayoutImage = base64Enoding;
}
// Initialize Areas
SqliteDataReader areasDatareader = ReadFrom(sqlite_conn, "SELECT * FROM Area WHERE LayoutName = \"" + Sanitize(l.Name) + "\";");
List <Area> areas = new List <Area>();
while (areasDatareader.Read())
{
Area a = new Area();
a.AreaType = (Area.Type)areasDatareader.GetInt64(0);
float x = areasDatareader.GetFloat(1);
float y = areasDatareader.GetFloat(2);
a.AreaLocation = new PointF(x, y);
a.NumberOfSeats = areasDatareader.GetInt32(3);
a.Name = areasDatareader.GetString(4);
areas.Add(a);
}
l.Areas = areas;
// Add reservation data to Areas
foreach (Area a in areas)
{
// TODO PG -> These areas are not layout specific!
areasDatareader = ReadFrom(sqlite_conn, "SELECT * FROM Reserves WHERE AreaX = " + a.AreaLocation.X + " AND AreaY = " + a.AreaLocation.Y + ";");
while (areasDatareader.Read())
{
Reservation r = new Reservation();
r.Id = areasDatareader.GetString(0);
r.Date = DateTime.Parse(areasDatareader.GetString(1));
r.Duration = areasDatareader.GetFloat(2);
// Customer fetching
string customerEmail = areasDatareader.GetString(3);
Customer c = new Customer();
SqliteDataReader customerReader = ReadFrom(sqlite_conn, "SELECT * FROM Customer WHERE Email = \"" + Sanitize(customerEmail) + "\";");
while (customerReader.Read())
{
c.Email = customerReader.GetString(0);
c.FirstName = customerReader.GetString(1);
c.LastName = customerReader.GetString(2);
}
r.Customer = c;
a.Reservations.Add(r);
}
}
layoutList.Add(l);
}
sqlite_conn.Close();
return(Task.FromResult(layoutList.ToArray()));
}
