NEW_coffee_project/coffeeproject/app/dry_logic.py

import pymysql
import time
import paho.mqtt.publish as publish
import paho.mqtt.client as mqtt
import datetime
import random
import json
import paramiko
from flask_mqtt import Mqtt



#插入一筆新資料(乾燥表)
def insert_data_dry():
    insert = conn.cursor()
    insert_status = "INSERT INTO `dry_container_status`(`Dry_Input_1`,`Dry_Input_2`," \
                    "`Dry_Tank_1`,`Dry_Tank_2`, `Dry_Tank_3`, `Dry_Tank_4`, `Dry_Tank_5`," \
                    "`Dry_Tank_6`, `Dry_Tank_7`,`Dry_Tank_8`, `Dry_Tank_9`, `Dry_Tank_10`," \
                    "`Dry_Tank_11`, `Dry_Tank_12`, `Dry_Output_1`,`Dry_Output_2`)" \
                    "SELECT `Dry_Input_1`,`Dry_Input_2`," \
                    "`Dry_Tank_1`,`Dry_Tank_2`, `Dry_Tank_3`, `Dry_Tank_4`, `Dry_Tank_5`," \
                    "`Dry_Tank_6`, `Dry_Tank_7`,`Dry_Tank_8`, `Dry_Tank_9`, `Dry_Tank_10`," \
                    "`Dry_Tank_11`, `Dry_Tank_12`, `Dry_Output_1`,`Dry_Output_2`" \
                    "FROM dry_container_status ORDER BY `datetime` DESC LIMIT 1"
    insert.execute(insert_status)
    conn.commit()


def update_Dry_Input_status(f):
    insert_data_dry()
    update_status = conn.cursor()
    Dry_name = 'dry_Input_'
    re = "UPDATE `dry_container_status` SET " + Dry_name + str(f) + " = 'DI_InputtingBean' ORDER BY `datetime` DESC LIMIT 1 "
    update_status.execute(re)
    conn.commit()

def update_dry_Input_status(f):
    insert_data_dry()
    update_status_dry = conn.cursor()
    dry_name = 'Dry_Input_'
    re_fer = "UPDATE `dry_container_status` SET "+dry_name+str(f)+"= 'DI_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_dry.execute(re_fer)
    conn.commit()

def update_Ferment_Output_status(p):
    insert = conn.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)
    conn.commit()
    update_status_Ferment = conn.cursor()
    Ferment_name = 'Ferment_Output_'
    re_ferment = "UPDATE `ferment_container_status` SET " + Ferment_name + str(p) + "= 'FO_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_Ferment.execute(re_ferment)
    conn.commit()

#修改乾燥桶槽狀態:入豆中 D_Waiting改成D_InputtingBean
def update_Dry_Tank_status1(d_tank):
    insert_data_dry()
    update_status_dry_Tank = conn.cursor()
    Dry_Tank_name = 'Dry_Tank_'
    re_dry_Tank = "UPDATE `dry_container_status` SET "+Dry_Tank_name+str(d_tank)+"= 'D_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_dry_Tank.execute(re_dry_Tank)
    conn.commit()


#修改乾燥桶槽狀態:D_InputtingBean改成D_Stand_by
def update_Dry_Tank_status(d_tank):
    insert_data_dry()
    update_status_dry_Tank = conn.cursor()
    Dry_Tank_name = 'Dry_Tank_'
    re_fer_Tank = "UPDATE `dry_container_status` SET "+Dry_Tank_name+str(d_tank)+"= 'D_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_dry_Tank.execute(re_fer_Tank)
    conn.commit()

#修改發酵出料儲豆槽狀態:入豆中  FO_Waiting改成FO_InputtingBean
def update_Dry_Output_status(d_Output):
    insert_data_dry()
    update_status_dry = conn.cursor()
    Dry_name = 'Dry_Output_'
    re_dry = "UPDATE `dry_container_status` SET "+Dry_name+str(d_Output)+"= 'DO_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_dry.execute(re_dry)
    conn.commit()

def update_Dry_Output_status_Stand_by(d_Output):
    insert_data_dry()
    update_status_dry = conn.cursor()
    Dry_Output_name = 'Dry_Output_'
    re_dry = "UPDATE `dry_container_status` SET "+Dry_Output_name+str(d_Output)+"= 'DO_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_dry.execute(re_dry)
    conn.commit()
#-----------------------------------------------------------------------


#傳送MQTT訊號
def send_mqtt():
    # 連線設定
    # 初始化地端程式
    client = mqtt.Client()
    # 設置日期時間的格式
    ISOTIMEFORMAT = '%m/%d %H:%M:%S'
    t = datetime.datetime.now().strftime(ISOTIMEFORMAT)

    # 設定登入帳號密碼
    client.username_pw_set("aisky-server","aisky")

    # 設定連線資訊(IP, Port, 連線時間)
    client.connect("60.250.156.234", 1883, 60)

    payload = {"command": "F_InputtingBean"}
    print(json.dumps(payload),t)
    #要發布的主題和內容
    #自行測試用
    client.publish("AISKY/Coffee/MK-G/b8:27:eb:7e:24:78", json.dumps(payload))
    #正式測試用
    #client.publish("AISKY/Coffee/MK-G/b8:27:eb:b4:59:3e", json.dumps(payload))
    #time.sleep(5)


#while True:
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='g53743001',
    db='coffee',
    charset='utf8'
)

cur1 = conn.cursor()
sql1 = "SELECT `Dry_Input_1`,`Dry_Input_2` FROM `dry_container_status` ORDER BY `sn` DESC LIMIT 1 "
cur1.execute(sql1)
get_Dry_Input = cur1.fetchone()

# 乾燥入料儲豆槽1
DI1 = get_Dry_Input[0]
# 乾燥入料儲豆槽2
DI2 = get_Dry_Input[1]

get_Dry_Input_status_N = [DI1, DI2]
f = 1
for i in range(len(get_Dry_Input_status_N)):
    f = i + 1
    cur = conn.cursor()
    sql = "SELECT `Ferment_Output_1`,`Ferment_Output_2` FROM `ferment_container_status` ORDER BY `sn` DESC LIMIT 1"
    cur.execute(sql)
    get_Ferment_Output_status = cur.fetchone()
    # 發酵出料儲豆槽1
    FO1 = get_Ferment_Output_status[0]
    # 發酵出料儲豆槽2
    FO2 = get_Ferment_Output_status[1]
    get_Ferment_Output_status_N = [FO1, FO2]
    p = 1
    if(get_Dry_Input_status_N[i] == 'DI_Waiting'):
        N = 'DI' + str(f)
        print(N + ' ' + get_Dry_Input_status_N[i] + ' 空桶等待')
        update_Dry_Input_status(f)
        time.sleep(1)
    elif(get_Dry_Input_status_N[i] == 'DI_InputtingBean'):
        print('DI' + str(f) + ' ' + get_Dry_Input_status_N[i] + ' 入豆中')
        for b in range(len(get_Ferment_Output_status_N)):
            p = b + 1
            if (get_Ferment_Output_status_N[b] == 'FO_InputtingBean'):
                print('FO' + str(p) + ' ' + get_Ferment_Output_status_N[b] + ' 入豆中')
            elif (get_Ferment_Output_status_N[b] == 'FO_Waiting'):
                print('FO' + str(p) + ' ' + get_Ferment_Output_status_N[b] + ' 空桶等待')
            elif (get_Ferment_Output_status_N[b] == 'FO_Stand_by'):
                print('FO' + str(p) + '待命')
            elif (get_Ferment_Output_status_N[b] == 'FO_OutputtingBean'):
                print('FO' + str(p) + ' ' + get_Ferment_Output_status_N[b] + ' 可出豆')
                send_mqtt()
                print('發送MQTT訊號通知', 'FO' + str(p) + ' 可出豆')
                # 修改乾燥入料槽狀態
                update_dry_Input_status(f)
                # 修改發酵出料狀態
                update_Ferment_Output_status(p)
                break

    elif(get_Dry_Input_status_N[i] == 'DI_OutputtingBean'):
        print('DI' + str(f) + ' ' + get_Dry_Input_status_N[i] + ' 可出豆')
        cur1 = conn.cursor()
        sql1 = "SELECT `Dry_Tank_1`, `Dry_Tank_2`, `Dry_Tank_3`, `Dry_Tank_4`, `Dry_Tank_5`, `Dry_Tank_6`, " \
               "`Dry_Tank_7`, `Dry_Tank_8`, `Dry_Tank_9`, `Dry_Tank_10`, `Dry_Tank_11`, `Dry_Tank_12` " \
               "FROM `dry_container_status` ORDER BY `sn` DESC LIMIT 1 "
        cur1.execute(sql1)
        get_dry_Tank = cur1.fetchone()
        # print(get_Ferment_Tank)

        # 乾燥槽1-12號
        D1 = get_dry_Tank[0]
        D2 = get_dry_Tank[1]
        D3 = get_dry_Tank[2]
        D4 = get_dry_Tank[3]
        D5 = get_dry_Tank[4]
        D6 = get_dry_Tank[5]
        D7 = get_dry_Tank[6]
        D8 = get_dry_Tank[7]
        D9 = get_dry_Tank[8]
        D10 = get_dry_Tank[9]
        D11 = get_dry_Tank[10]
        D12 = get_dry_Tank[11]
        dry_Tank_status = [F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12]
        d_tank = 1
        for i_tank in range(len(dry_Tank_status)):
            d_tank = i_tank + 1
            if (dry_Tank_status[i_tank] == 'D_Waiting'):
                update_Dry_Tank_status1(d_tank)
                print('D' + str(d_tank) + '修改發酵桶槽狀態D_Waiting->D_InputtingBean')
            elif (dry_Tank_status[i_tank] == 'D_InputtingBean'):
                # 修改發酵桶槽狀態F_InputtingBean->F_Standby
                update_Dry_Tank_status(d_tank)
                print('D' + str(d_tank) + ' ' + dry_Tank_status[i_tank] + ' 入豆中')
                send_mqtt()
                break
            elif (dry_Tank_status[i_tank] == 'D_OutputtingBean'):
                cur1 = conn.cursor()
                sql1 = "SELECT `Dry_Output_1`, `Dry_Output_2` FROM `dry_container_status` ORDER BY `sn` DESC LIMIT 1 "
                cur1.execute(sql1)
                get_dry_Output = cur1.fetchone()

                # 乾燥出料儲豆槽1
                DO1 = get_dry_Output[0]
                # 乾燥出料儲豆槽2
                DO2 = get_dry_Output[1]
                get_dry_Output_status = [DO1, DO2]
                d_Output = 1
                for i_Output in range(len(get_dry_Output_status)):
                    d_Output = i_Output + 1
                    if (get_dry_Output_status[i_Output] == 'DO_Waiting'):
                        update_Dry_Output_status(d_Output)
                        print('DO' + str(d_Output) + ' 修改發酵桶槽狀態DO_Waiting->DO_InputtingBean')
                    elif (get_dry_Output_status[i_Output] == 'DO_InputtingBean'):
                        update_Dry_Output_status_Stand_by(d_Output)
                        print('修改' + 'DO' + str(d_Output) + '出料槽狀態，並發送MQTT訊號。')
                        send_mqtt()
                    elif (get_dry_Output_status[i_Output] == 'DO_OutputtingBean'):
                        print('修改' + 'DO' + str(d_Output) + '可出豆')
                    elif (get_dry_Output_status[i_Output] == 'DO_Standby'):
                        print('DO' + str(d_Output) + ' 待命中')
                    elif (get_dry_Output_status[i_Output] == 'DO_Warning'):
                        print('DO' + str(d_Output) + ' 待命中')
            elif (dry_Tank_status[i_tank] == 'D_Standby'):
                print('D' + str(d_tank) + ' ' + dry_Tank_status[i_tank] + ' 待命')
            elif (dry_Tank_status[i_tank] == 'D_Warning'):
                print('D' + str(d_tank) + ' ' + dry_Tank_status[i_tank] + ' 警告')
            elif (dry_Tank_status[i_tank] == 'D_Drying'):
                print('D' + str(d_tank) + ' ' + dry_Tank_status[i_tank] + ' 乾燥中')
            elif (dry_Tank_status[i_tank] == 'D_Cleaning'):
                print('D' + str(d_tank) + ' ' + dry_Tank_status[i_tank] + ' 清洗中')
            elif (dry_Tank_status[i_tank] == 'D_InputtingBean_Pause'):
                print('D' + str(d_tank) + ' ' + dry_Tank_status[i_tank] + ' 入豆暫停')
            elif (dry_Tank_status[i_tank] == 'D_InputtingBean_Finish'):
                print('D' + str(d_tank) + ' ' + dry_Tank_status[i_tank] + ' 入豆完成')

    elif(get_Dry_Input_status_N[i] == 'DI_Standby'):
        print('DI' + str(f) + ' ' + get_Ferment_Output_status_N[i] + '待命')
    elif(get_Dry_Input_status_N[i] == 'DI_Warning'):
        print('DI' + str(f) + ' ' + get_Ferment_Output_status_N[i] + '警告')