5zXqmJ4z

1#server
2#frontend
3#server.py
4
5
6
7import cgi
8import sqlite3
9from sqlite3 import Error
10from datetime import datetime
11
12def create_connection(db_file):
13    """ create a database connection to a SQLite database """
14    conn = None
15    try:
16        conn = sqlite3.connect(db_file)
17        print(sqlite3.version)
18    except Error as e:
19        print(e)
20    finally:
21        if conn:
22            conn.close()
23
24
25def InitDB(DatabaseName):
26    DeviceInformation_Table = """ CREATE TABLE IF NOT EXISTS DeviceInformation (
27                                        id integer PRIMARY KEY,
28                                        DeviceID text,
29                                        FirmwareVersion text,
30                                        SampleTime text,
31                                        Sensor1Min text,
32                                        Sensor1Max text,
33                                        Sensor2Min text,
34                                        Sensor2Max text,
35                                        Sensor3Min text,
36                                        Sensor3Max text,
37                                        Sensor4Min text,
38                                        Sensor4Max text
39                                    ); """
40
41    DeviceData_Table = """ CREATE TABLE IF NOT EXISTS DeviceData (
42                                        id integer PRIMARY KEY,
43                                        DeviceID text,
44                                        TimeStamp text,
45                                        SensorData1 text,
46                                        SensorData2 text,
47                                        SensorData3 text,
48                                        SensorData4 text,
49                                    ); """
50
51
52    if conn is not None:
53        # create Table to store configuration information for each device
54        create_table(conn, DeviceInformation_Table)
55
56        # Create Table to store the Sensor data from reporting devices
57        create_table(conn, DeviceData_Table)
58    else:
59        print('SQLite3 Connection Error')
60
61def InitDeviceConfig(DatabaseName, NumDevices):
62    for x in range(NumDevices):
63        sql = ''' INSERT INTO DeviceData(DeviceID,FirmwareVersion,SampleTime,Sensor1Min,Sensor1Max,Sensor2Min,Sensor2Max,Sensor3Min,Sensor3Max,Sensor4Min,Sensor4Max)
64                  VALUES(?,?,?,?,?,?) '''
65
66        cur = conn.cursor()
67
68        DeviceInfo = (str(x), '1.0', '5000s', '0', '1', '0', '1', '0', '1', '0', '1') 
69        cur.execute(sql, DeviceInfo)
70        conn.commit()
71
72
73def AddSensorData(SensorData):
74    sql = ''' INSERT INTO DeviceData(DeviceID,TimeStamp,SensorData1,SensorData2,SensorData3,SensorData4)
75              VALUES(?,?,?,?,?,?) '''
76
77    cur = conn.cursor()
78    cur.execute(sql, SensorData)
79    conn.commit()
80    return cur.lastrowid
81
82def UpdateSensorData(Data):
83    SensorData = Data.split(',')
84
85    DeviceID = SensorData[0]
86    TimeStamp = str(datetime.now())
87    SensorValue1 = SensorData[1]
88    SensorValue2 = SensorData[2]
89    SensorValue3 = SensorData[3]
90    SensorValue4 = SensorData[4]
91
92    SensorBlob = (DeviceID, TimeStamp, SensorValue1, SensorValue2, SensorValue3, SensorValue4)
93    AddSensorData(conn, SensorBlob)
94
95def UpdateDeviceConfig(DeviceInfo):
96    Params = DeviceInfo.split(',')
97
98    DeviceID = params[0]
99    FirmwareVersion = params[1]
100    SampleTime = params[2]
101    Sensor1Min = params[3]
102    Sensor1Max = params[4]
103    Sensor2Min = params[5]
104    Sensor2Max = params[6]
105    Sensor3Min = params[7]
106    Sensor3Max = params[8]
107    Sensor4Min = params[9]
108    Sensor4Max = params[10]
109
110    sql = ''' UPDATE DeviceInformation
111                  SET FirmwareVersion = ? ,
112                      SampleTime = ? ,
113                      Sensor1Min = ?,
114                      Sensor1Max = ?,
115                      Sensor2Min = ?,
116                      Sensor2Max = ?,
117                      Sensor3Min = ?,
118                      Sensor3Max = ?,
119                      Sensor4Min = ?,
120                      Sensor4Max = ?
121                  WHERE DeviceID = ?'''
122     cur = conn.cursor()
123     cur.execute(sql, (FirmwareVersion, SampleTime, Sensor1Min, Sensor1Max, Sensor2Min, Sensor2Max, Sensor3Min, Sensor3Max, Sensor4Min, Sensor4Max, DeviceID))
124     conn.commit()
125
126def GetDeviceConfig(DeviceID):
127    cur = conn.cursor()
128    cur.execute("SELECT * FROM DeviceInformation WHERE DeviceID=?", (DeviceID,))
129
130    rows = cur.fetchall()
131
132    return rows
133
134def Export2CSV(DatabaseName):
135    pass
136
137
138DatabaseName = 'SensorData.db'
139conn = create_connection(DatabaseName)
140
141form      = cgi.FieldStorage()
142CMD       = form.getvalue('CMD')
143params    = form.getvalue('params')
144
145if(CMD == 'InitDatabase'):
146  InitDB(DatabaseName)
147
148if(CMD == 'InitDeviceConfig'):
149  InitDeviceConfig(DatabaseName)
150
151if(CMD == 'UpdateDeviceConfig'):
152  print(UpdateDeviceConfig(params))
153
154if(CMD == 'GetDeviceConfig'):
155  print(GetDeviceConfig(DeviceID))
156
157if(CMD == 'UpdateSensors')
158  UpdateSensorData(params)
159
160