import socket, threading
import pymysql
import time

s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.bind(('192.168.51.102', 1))
s.listen(5)
c, a = s.accept()
print('server start at: %s:%s' % a)
print('wait for connection...')

def update_Ferment_Input_status(f,r):
    con = pymysql.connect(host='60.250.156.230', user='user', password='Gold@53743001', db='CoffeeManage',
                          charset='utf8')
    datetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    insert = con.cursor()
    insert_status = "INSERT INTO `ferment_container_status`(`Ferment_Input_1`,`Ferment_Input_2`," \
                   "`Ferment_Tank_1`,`Ferment_Tank_2`, `Ferment_Tank_3`, `Ferment_Tank_4`, `Ferment_Tank_5`," \
                   "`Ferment_Tank_6`, `Ferment_Tank_7`,`Ferment_Tank_8`, `Ferment_Tank_9`, `Ferment_Tank_10`," \
                   "`Ferment_Tank_11`, `Ferment_Tank_12`, `Ferment_Output_1`,`Ferment_Output_2`)" \
                   "SELECT `Ferment_Input_1`, `Ferment_Input_2`," \
                   "`Ferment_Tank_1`,`Ferment_Tank_2`, `Ferment_Tank_3`, `Ferment_Tank_4`, `Ferment_Tank_5`," \
                   "`Ferment_Tank_6`, `Ferment_Tank_7`,`Ferment_Tank_8`, `Ferment_Tank_9`, `Ferment_Tank_10`," \
                  "`Ferment_Tank_11`, `Ferment_Tank_12`, `Ferment_Output_1`,`Ferment_Output_2`" \
                   "FROM ferment_container_status ORDER BY `datetime` DESC LIMIT 1"
    insert.execute(insert_status)
    con.commit()
    time.sleep(1)
    update_status = con.cursor()
    Ferment_name = 'Ferment_Tank_'
    re = "UPDATE `ferment_container_status` SET " + Ferment_name + str(f) + "='"+r+"' ORDER BY `datetime` DESC LIMIT 1 "
    update_status.execute(re)
    con.commit()


class A(threading.Thread):
    def __init__(self):
        threading.Thread.__init__(self)

    def run(self):
        while True:
            b = c.recv(1024)
            msg = b.decode(encoding='utf8')
            print('send: ' + msg)
            m = b.decode(encoding='utf8').split('\n')
            for j in range(0, (len(b.decode().split('\n')) - 1)):
                if m[j].split('= ')[0] == "M1_SonicESMUS07":
                    con = pymysql.connect(host='60.250.156.230', user='user', password='Gold@53743001',
                                          db='CoffeeManage',
                                          charset='utf8')
                    datetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
                    insert = con.cursor()
                    insert_status = "INSERT INTO `ferment_tank_UltraSonic`(`datetime`,`tank_num`,`UltraSonic`) VALUES ('%s', '%s', '%s')" % (
                        datetime, 'F1', m[j].split('= ')[1])
                    insert.execute(insert_status)
                    con.commit()
                if m[j].split('= ')[0] == "M4_PH":
                    con = pymysql.connect(host='60.250.156.230', user='user', password='Gold@53743001',
                                          db='CoffeeManage',
                                          charset='utf8')
                    datetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
                    insert = con.cursor()
                    insert_status = "INSERT INTO `ferment_tank_PH`(`datetime`,`tank_num`,`PH`) VALUES ('%s', '%s', '%s')" % (
                        datetime, 'F1', m[j].split('= ')[1])
                    insert.execute(insert_status)
                    con.commit()
                if m[j].split('= ')[0] == "M17_SHT11":
                    con = pymysql.connect(host='60.250.156.230', user='user', password='Gold@53743001',
                                          db='CoffeeManage',
                                          charset='utf8')
                    datetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
                    insert = con.cursor()
                    insert_status = "INSERT INTO `ferment_tank_SHT11`(`datetime`,`tank_num`,`SHT11_Temp`,`SHT11_Humidity`) VALUES ('%s', '%s', '%s', '%s')" % (
                        datetime, 'F1', m[j].split('= ')[1], 60)
                    insert.execute(insert_status)
                    con.commit()
                if m[j].split('= ')[0] == "M19_WATERLEVEL":
                    con = pymysql.connect(host='60.250.156.230', user='user', password='Gold@53743001',
                                          db='CoffeeManage',
                                          charset='utf8')
                    datetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
                    insert = con.cursor()
                    insert_status = "INSERT INTO `ferment_tank_WaterLevel`(`datetime`,`tank_num`,`WaterLevel`) VALUES ('%s', '%s', '%s')" % (
                        datetime, 'F1', m[j].split('= ')[1])
                    insert.execute(insert_status)
                    con.commit()
                if b.decode().split('\n')[j] == "F_InputtingBean":
                    update_Ferment_Input_status(1, 'F_InputtingBean')
                if b.decode().split('\n')[j] == "F_Fermenting":
                    update_Ferment_Input_status(1, 'F_Fermenting')
                if b.decode().split('\n')[j] == "F_OutputtingBean":
                    update_Ferment_Input_status(1, 'F_OutputtingBean')
                if b.decode().split('\n')[j] == "F_InputtingBean_Finish":
                    update_Ferment_Input_status(1, 'F_InputtingBean_Finish')
                if b.decode().split('\n')[j] == "F_Fermenting_Finish":
                    update_Ferment_Input_status(1, 'F_Fermenting_Finish')
                if b.decode().split('\n')[j] == "F_Waiting":
                    update_Ferment_Input_status(1, 'F_Waiting')


e = A()
e.start()

while True:
    d = input('STATUS:：')
    c.send(d.encode(encoding='utf8'))