add historical data and migration script

2 files changed, 176 insertions, 0 deletions

diff --git a/historical_data.csv b/historical_data.csv
new file mode 100644
index 0000000..0c88d4a
--- /dev/null
+++ b/historical_data.csv
@@ -0,0 +1,48 @@
+date,alert_level,new_students,new_staff,total_students,total_staff,quarantine_on_campus,quarantine_off_campus,isolation_on_campus,isolation_off_campus,tests_administered,beds_available
+2020-08-27,green,2,1,3,1,3,4,3,4,0,95
+2020-08-28,green,2,1,3,1,3,7,0,3,0,96
+2020-08-31,green,1,0,3,1,4,3,0,1,0,95
+2020-09-01,green,0,0,3,1,7,2,2,0,0,92
+2020-09-02,green,0,0,3,1,6,2,3,0,0,93
+2020-09-03,green,0,0,3,1,11,3,3,0,0,92
+2020-09-04,green,3,1,4,1,1,5,0,1,0,96
+2020-09-06,green,2,0,5,1,3,5,0,1,0,97
+2020-09-07,green,3,0,6,1,5,17,1,6,0,95
+2020-09-08,green,3,0,6,1,5,17,1,6,0,95
+2020-09-10,green,3,0,6,1,1,17,0,3,0,98
+2020-09-11,green,3,0,6,1,1,17,2,4,0,96
+2020-09-14,green,4,0,6,1,4,16,2,1,0,95
+2020-09-15,green,4,0,7,1,9,19,3,2,0,92
+2020-09-16,green,5,0,8,1,12,19,3,2,0,90
+2020-09-17,green,4,1,8,2,16,18,2,1,0,88
+2020-09-18,green,4,1,9,2,14,16,2,1,0,89
+2020-09-21,green,4,1,10,2,8,14,2,1,0,94
+2020-09-22,green,4,1,10,2,10,9,4,1,0,91
+2020-09-24,green,4,1,10,2,15,8,5,1,0,88
+2020-09-25,green,4,1,10,2,10,12,2,0,3747,92
+2020-09-26,green,4,1,10,2,10,12,2,0,3747,92
+2020-09-27,green,4,1,10,2,10,12,2,0,3747,92
+2020-09-28,green,2,1,10,2,1,8,1,0,3751,98
+2020-09-29,green,2,1,10,2,0,9,2,0,3753,98
+2020-09-30,green,2,1,10,2,0,9,2,0,3753,98
+2020-10-01,green,0,0,10,2,0,8,1,2,4038,99
+2020-10-02,green,0,0,10,2,1,6,1,0,4135,98
+2020-10-03,green,0,0,10,2,1,6,1,0,4135,98
+2020-10-04,green,0,0,10,2,1,6,1,0,4135,98
+2020-10-05,green,1,0,10,2,3,1,1,0,4136,97
+2020-10-06,green,1,0,10,2,11,5,5,0,4139,90
+2020-10-08,yellow,3,0,12,2,7,10,4,1,4482,93
+2020-10-09,yellow,3,0,12,2,8,14,4,0,4625,92
+2020-10-12,yellow,4,0,13,2,8,20,3,4,5053,93
+2020-10-13,yellow,4,0,13,2,9,24,4,2,5055,92
+2020-10-15,yellow,5,0,14,2,8,27,4,4,5471,93
+2020-10-16,yellow,6,0,15,2,5,25,1,5,5590,96
+2020-10-19,yellow,6,0,16,2,6,23,3,4,5591,95
+2020-10-20,yellow,7,0,17,2,11,29,3,6,5598,92
+2020-10-21,yellow,6,0,17,2,11,32,3,5,5887,92
+2020-10-23,yellow,7,0,19,2,8,33,1,5,6141,95
+2020-10-26,yellow,8,0,21,2,16,35,2,4,6146,90
+2020-10-27,yellow,9,0,22,2,14,36,2,4,6150,91
+2020-10-28,yellow,10,1,23,3,12,34,3,4,6392,92
+2020-10-29,yellow,10,2,24,4,13,31,3,9,6490,91
+2020-10-30,yellow,17,2,32,4,18,27,6,9,6638,86
diff --git a/migrations.py b/migrations.py
new file mode 100644
index 0000000..c93fd0f
--- /dev/null
+++ b/migrations.py
@@ -0,0 +1,128 @@
+import os
+import csv
+import sqlite3
+
+LATEST_DATA={}
+
+def create_tables():
+    print('creating tables')
+    db_conn = sqlite3.connect('./poller/data/data.sqlite3')
+    c = db_conn.cursor()
+    sql = f'CREATE TABLE IF NOT EXISTS `alertlevel` (time DATETIME PRIMARY KEY NOT NULL, color CHAR(50) NOT NULL);'
+    c.execute(sql)
+    sql = f'CREATE TABLE IF NOT EXISTS `total` (time DATETIME PRIMARY KEY NOT NULL, total_students INT NOT NULL, total_staff INT NOT NULL);'
+    c.execute(sql)
+    sql = f'CREATE TABLE IF NOT EXISTS `new` (time DATETIME PRIMARY KEY NOT NULL, new_students INT NOT NULL, new_staff INT NOT NULL);'
+    c.execute(sql)
+    sql = f'CREATE TABLE IF NOT EXISTS `quarantine` (time DATETIME PRIMARY KEY NOT NULL, quarantine_on_campus INT NOT NULL, quarantine_off_campus INT NOT NULL);'
+    c.execute(sql)
+    sql = f'CREATE TABLE IF NOT EXISTS `isolation` (time DATETIME PRIMARY KEY NOT NULL, isolation_on_campus INT NOT NULL, isolation_off_campus INT NOT NULL);'
+    c.execute(sql)
+    sql = f'CREATE TABLE IF NOT EXISTS `beds` (time DATETIME PRIMARY KEY NOT NULL, beds_available INT NOT NULL);'
+    c.execute(sql)
+    sql = f'CREATE TABLE IF NOT EXISTS `tests` (time DATETIME PRIMARY KEY NOT NULL, tests_administered INT NOT NULL);'
+    c.execute(sql)
+    db_conn.commit()
+    db_conn.close()
+
+def update_db():
+    db_conn = sqlite3.connect('./poller/data/data.sqlite3')
+    c = db_conn.cursor()
+    sql = f'INSERT INTO `alertlevel` VALUES (\'{LATEST_DATA["last_updated"]}\', \'{LATEST_DATA["alert_level"]}\');'
+    c.execute(sql)
+    sql = f'INSERT INTO `total` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["total_students"]}, {LATEST_DATA["total_staff"]});'
+    c.execute(sql)
+    sql = f'INSERT INTO `new` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["new_students"]}, {LATEST_DATA["new_staff"]});'
+    c.execute(sql)
+    sql = f'INSERT INTO `quarantine` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["quarantine_on_campus"]}, {LATEST_DATA["quarantine_off_campus"]});'
+    c.execute(sql)
+    sql = f'INSERT INTO `isolation` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["isolation_on_campus"]}, {LATEST_DATA["isolation_off_campus"]});'
+    c.execute(sql)
+    sql = f'INSERT INTO `beds` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["beds_available"]});'
+    c.execute(sql)
+    sql = f'INSERT INTO `tests` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["tests_administered"]});'
+    c.execute(sql)
+    db_conn.commit()
+    db_conn.close()
+
+def get_latest_from_db():
+
+    db_conn = sqlite3.connect('./poller/data/data.sqlite3')
+    c = db_conn.cursor()
+    sql = 'SELECT max(alertlevel.time), alertlevel.color, total.total_students, total.total_staff, new.new_students, new.new_staff, ' + \
+        'quarantine.quarantine_on_campus, quarantine.quarantine_off_campus, isolation.isolation_on_campus, isolation.isolation_off_campus, ' + \
+        'beds.beds_available, tests.tests_administered ' + \
+        'FROM `alertlevel` ' + \
+        'INNER JOIN `total` ' + \
+        'ON alertlevel.time = total.time ' + \
+        'INNER JOIN `new` ' + \
+        'ON alertlevel.time = new.time ' + \
+        'INNER JOIN `quarantine` ' + \
+        'ON alertlevel.time = quarantine.time ' + \
+        'INNER JOIN `isolation` ' + \
+        'ON alertlevel.time = isolation.time ' + \
+        'INNER JOIN `beds` ' + \
+        'ON alertlevel.time = beds.time ' + \
+        'INNER JOIN `tests` ' + \
+        'ON alertlevel.time = tests.time'
+    c.execute(sql)
+    d = c.fetchone()
+    data = {
+        'alert_level': d[1],        
+        'total_students': d[2],
+        'total_staff': d[3],
+        'new_students': d[4],
+        'new_staff': d[5],
+        'quarantine_on_campus': d[6],
+        'quarantine_off_campus': d[7],
+        'isolation_on_campus': d[8],
+        'isolation_off_campus': d[9],
+        'beds_available': d[10],
+        'tests_administered': d[11],
+        'last_updated': d[0]
+    }
+    return data
+
+def db_is_same():
+    global LATEST_DATA
+    latest_data = get_latest_from_db()
+    if latest_data is None or LATEST_DATA is None:
+        return False
+    for key in list(latest_data.keys()):
+        if key != 'last_updated' and LATEST_DATA[key] != latest_data[key]:
+            return False
+    return True
+
+if not os.path.exists('./poller'):
+    os.mkdir('./poller')
+if not os.path.exists('./poller/data')
+    os.mkdir('./poller/data')
+
+create_tables()
+
+with open('historical_data.csv', 'r') as csvfile:
+    print('importing data...')
+    csvreader = csv.reader(csvfile)
+    firstRow = True
+    for row in csvreader:
+        if firstRow: 
+            firstRow = False
+            continue
+        LATEST_DATA = {
+            'alert_level': str(row[1]),        
+            'total_students': int(row[4]),
+            'total_staff': int(row[5]),
+            'new_students': int(row[2]),
+            'new_staff': int(row[3]),
+            'quarantine_on_campus': int(row[6]),
+            'quarantine_off_campus': int(row[7]),
+            'isolation_on_campus': int(row[8]),
+            'isolation_off_campus': int(row[9]),
+            'beds_available': int(row[11]),
+            'tests_administered': int(row[10]),
+            'last_updated': f'{row[0]} 16:00:00'
+        }
+        if not db_is_same():
+            update_db()
+    print('data imported!')
+