Example #1
0
        //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");
            }
        }
Example #2
0
        //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);
        }
Example #3
0
        //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);
        }
Example #4
0
        //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);
        }
Example #5
0
        //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();
            }
        }