server1

#server
#frontend
#server.py



import cgi
import sqlite3
from sqlite3 import Error
from datetime import datetime

def create_connection(db_file):
    """ create a database connection to a SQLite database """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(sqlite3.version)
    except Error as e:
        print(e)
    finally:
        if conn:
            conn.close()


def InitDB(DatabaseName):
    DeviceInformation_Table = """ CREATE TABLE IF NOT EXISTS DeviceInformation (
                                        id integer PRIMARY KEY,
                                        DeviceID text,
                                        FirmwareVersion text,
                                        SampleTime text,
                                        Sensor1Min text,
                                        Sensor1Max text,
                                        Sensor2Min text,
                                        Sensor2Max text,
                                        Sensor3Min text,
                                        Sensor3Max text,
                                        Sensor4Min text,
                                        Sensor4Max text
                                    ); """

    DeviceData_Table = """ CREATE TABLE IF NOT EXISTS DeviceData (
                                        id integer PRIMARY KEY,
                                        DeviceID text,
                                        TimeStamp text,
                                        SensorData1 text,
                                        SensorData2 text,
                                        SensorData3 text,
                                        SensorData4 text,
                                    ); """


    if conn is not None:
        # create Table to store configuration information for each device
        create_table(conn, DeviceInformation_Table)

        # Create Table to store the Sensor data from reporting devices
        create_table(conn, DeviceData_Table)
    else:
        print('SQLite3 Connection Error')

def InitDeviceConfig(DatabaseName, NumDevices):
    for x in range(NumDevices):
        sql = ''' INSERT INTO DeviceData(DeviceID,FirmwareVersion,SampleTime,Sensor1Min,Sensor1Max,Sensor2Min,Sensor2Max,Sensor3Min,Sensor3Max,Sensor4Min,Sensor4Max)
                  VALUES(?,?,?,?,?,?) '''

        cur = conn.cursor()

        DeviceInfo = (str(x), '1.0', '5000s', '0', '1', '0', '1', '0', '1', '0', '1')
        cur.execute(sql, DeviceInfo)
        conn.commit()


def AddSensorData(SensorData):
    sql = ''' INSERT INTO DeviceData(DeviceID,TimeStamp,SensorData1,SensorData2,SensorData3,SensorData4)
              VALUES(?,?,?,?,?,?) '''

    cur = conn.cursor()
    cur.execute(sql, SensorData)
    conn.commit()
    return cur.lastrowid

def UpdateSensorData(Data):
    SensorData = Data.split(',')

    DeviceID = SensorData[0]
    TimeStamp = str(datetime.now())
    SensorValue1 = SensorData[1]
    SensorValue2 = SensorData[2]
    SensorValue3 = SensorData[3]
    SensorValue4 = SensorData[4]

    SensorBlob = (DeviceID, TimeStamp, SensorValue1, SensorValue2, SensorValue3, SensorValue4)
    AddSensorData(conn, SensorBlob)

def UpdateDeviceConfig(DeviceInfo):
    Params = DeviceInfo.split(',')

    DeviceID = params[0]
    FirmwareVersion = params[1]
    SampleTime = params[2]
    Sensor1Min = params[3]
    Sensor1Max = params[4]
    Sensor2Min = params[5]
    Sensor2Max = params[6]
    Sensor3Min = params[7]
    Sensor3Max = params[8]
    Sensor4Min = params[9]
    Sensor4Max = params[10]

    sql = ''' UPDATE DeviceInformation
                  SET FirmwareVersion = ? ,
                      SampleTime = ? ,
                      Sensor1Min = ?,
                      Sensor1Max = ?,
                      Sensor2Min = ?,
                      Sensor2Max = ?,
                      Sensor3Min = ?,
                      Sensor3Max = ?,
                      Sensor4Min = ?,
                      Sensor4Max = ?
                  WHERE DeviceID = ?'''
     cur = conn.cursor()
     cur.execute(sql, (FirmwareVersion, SampleTime, Sensor1Min, Sensor1Max, Sensor2Min, Sensor2Max, Sensor3Min, Sensor3Max, Sensor4Min, Sensor4Max, DeviceID))
     conn.commit()

def GetDeviceConfig(DeviceID):
    cur = conn.cursor()
    cur.execute("SELECT * FROM DeviceInformation WHERE DeviceID=?", (DeviceID,))

    rows = cur.fetchall()

    return rows

def Export2CSV(DatabaseName):
    pass


DatabaseName = 'SensorData.db'
conn = create_connection(DatabaseName)

form      = cgi.FieldStorage()
CMD       = form.getvalue('CMD')
params    = form.getvalue('params')

if(CMD == 'InitDatabase'):
  InitDB(DatabaseName)

if(CMD == 'InitDeviceConfig'):
  InitDeviceConfig(DatabaseName)

if(CMD == 'UpdateDeviceConfig'):
  print(UpdateDeviceConfig(params))

if(CMD == 'GetDeviceConfig'):
  print(GetDeviceConfig(DeviceID))

if(CMD == 'UpdateSensors')
  UpdateSensorData(params)