NEW_coffee_project/coffeeproject/app/test_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_ferment():
    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()

#插入一筆新資料(乾燥表)
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()
#修改發酵入料儲豆槽狀態 FI_Waiting改成FI_InputtingBean。
#f = 1,2(發酵入料儲豆槽編號)
def update_Ferment_Input_status(f):
    insert_data_ferment()
    update_status = conn.cursor()
    Ferment_name = 'Ferment_Input_'
    re = "UPDATE `ferment_container_status` SET " + Ferment_name + str(f) + " = 'FI_InputtingBean' ORDER BY `datetime` DESC LIMIT 1 "
    update_status.execute(re)
    conn.commit()

#修改發酵入料儲豆槽狀態，FI_InputtingBean改成FI_Stand_by。
#f = 1,2(發酵入料儲豆槽編號)
def update_Fer_Input_status(f):
    insert_data_ferment()
    update_status_fer = conn.cursor()
    Ferment_name = 'Ferment_Input_'
    re_fer = "UPDATE `ferment_container_status` SET "+Ferment_name+str(f)+"= 'FI_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_fer.execute(re_fer)
    conn.commit()

#修改脫皮出料儲豆槽狀態，PO_OutputtingBean改成PO_Stand_by。
#p = 1,2(脫皮出料儲豆槽編號)
def update_Peel_Output_status(p):
    insert = conn.cursor()
    insert_status = "INSERT INTO `clean_container_status`(`Clean_Input_1`,`Clean_Input_2`," \
                    "`Clean_Tank_1`,`Clean_Tank_2`, `Clean_Tank_3`, `Clean_Tank_4`, `Reclaimed_Tank_1`," \
                    "`ColorSelect_Input_1`, `ColorSelect_Input_2`,`ColorSelect_Tank_1`, `ColorSelect_Tank_2`, `ColorSelect_Output_g1`," \
                    "`ColorSelect_Output_b1`, `ColorSelect_Output_g2`, `ColorSelect_Output_b2`,`Peel_Tank_1`,`Peel_Tank_2`,`Peel_Output_1`," \
                    "`Peel_Output_2`,`Peel_Output_b1`)" \
                    "SELECT `Clean_Input_1`,`Clean_Input_2`," \
                    "`Clean_Tank_1`,`Clean_Tank_2`, `Clean_Tank_3`, `Clean_Tank_4`, `Reclaimed_Tank_1`," \
                    "`ColorSelect_Input_1`, `ColorSelect_Input_2`,`ColorSelect_Tank_1`, `ColorSelect_Tank_2`, `ColorSelect_Output_g1`," \
                    "`ColorSelect_Output_b1`, `ColorSelect_Output_g2`, `ColorSelect_Output_b2`,`Peel_Tank_1`,`Peel_Tank_2`,`Peel_Output_1`," \
                    "`Peel_Output_2`,`Peel_Output_b1`" \
                    "FROM clean_container_status ORDER BY `datetime` DESC LIMIT 1"
    insert.execute(insert_status)
    conn.commit()
    update_status_peel = conn.cursor()
    Peel_name = 'Peel_Output_'
    re_peel = "UPDATE `clean_container_status` SET " + Peel_name + str(p) + "= 'PO_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_peel.execute(re_peel)
    conn.commit()

#修改發酵桶槽狀態:F_InputtingBeans改成F_Stand_by
def update_Fer_Tank_status(f_tank):
    insert_data_ferment()
    update_status_fer_Tank = conn.cursor()
    Ferment_Tank_name = 'Ferment_Tank_'
    re_fer_Tank = "UPDATE `ferment_container_status` SET "+Ferment_Tank_name+str(f_tank)+"= 'F_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_fer_Tank.execute(re_fer_Tank)
    conn.commit()
#修改發酵桶槽狀態:入豆中 F_Waiting改成F_InputtingBean
def update_Fer_Tank_status1(f_tank):
    insert_data_ferment()
    update_status_fer_Tank = conn.cursor()
    Ferment_Tank_name = 'Ferment_Tank_'
    re_fer_Tank = "UPDATE `ferment_container_status` SET "+Ferment_Tank_name+str(f_tank)+"= 'F_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_fer_Tank.execute(re_fer_Tank)
    conn.commit()

#修改發酵出料儲豆槽狀態:入豆中  FO_Waiting改成FO_InputtingBean
def update_Fer_Output_status(f_Output):
    insert_data_ferment()
    update_status_fer = conn.cursor()
    Ferment_name = 'Ferment_Output_'
    re_fer = "UPDATE `ferment_container_status` SET "+Ferment_name+str(f_Output)+"= 'FO_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_fer.execute(re_fer)
    conn.commit()

#修改發酵出料儲豆槽狀態:FO_InputtingBean改成FO_Stand_by。
#FO_Stand_by
def update_Fer_Output_status_Stand_by(f_Output):
    insert_data_ferment()
    update_status_peel = conn.cursor()
    Fer_Output_name = 'Ferment_Output_'
    re_peel = "UPDATE `ferment_container_status` SET "+Fer_Output_name+str(f_Output)+"= 'FO_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_peel.execute(re_peel)
    conn.commit()

#修改乾燥入料儲豆槽狀態:入豆
def update_Dry_Input_status_input(p):
    insert_data_dry()
    update_status_dry = conn.cursor()
    Dry_name = 'Dry_Input_'
    re_dry = "UPDATE `dry_container_status` SET "+Dry_name+str(p)+"= 'DI_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_dry.execute(re_dry)
    conn.commit()

#修改乾燥入料儲豆槽狀態:待命
def update_Dry_Input_status(p):
    insert_data_dry()
    update_status_dry = conn.cursor()
    Dry_name = 'Dry_Input_'
    re_dry = "UPDATE `dry_container_status` SET "+Dry_name+str(p)+"= 'DI_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'
)
# conn = pymysql.connect(
#     host='60.250.156.230',
#     user='user',
#     password='Gold@53743001',
#     db='CoffeeManage',
#     charset='utf8'
# )

cur1 = conn.cursor()
sql1 = "SELECT `Ferment_Input_1`,`Ferment_Input_2` FROM `ferment_container_status` ORDER BY `sn` DESC LIMIT 1 "
cur1.execute(sql1)
get_Ferment_Input = cur1.fetchone()

# 發酵入料儲豆槽1
FI1 = get_Ferment_Input[0]
# 發酵入料儲豆槽2
FI2 = get_Ferment_Input[1]

ferment_input_status_N = [FI1, FI2]
f = 1
# 第一部分檢查脫皮出料與發酵入料儲豆槽狀態
for i in range(len(ferment_input_status_N)):
    f = i + 1
    cur = conn.cursor()
    sql = "SELECT `Peel_Output_1`,`Peel_Output_2` FROM `clean_container_status` ORDER BY `sn` DESC LIMIT 1"
    cur.execute(sql)
    get_Peel_Output_status = cur.fetchone()
    # 清洗脫皮出料儲豆槽1
    PO1 = get_Peel_Output_status[0]
    # 清洗脫皮出料儲豆槽2
    PO2 = get_Peel_Output_status[1]
    get_Peel_Output_status_N = [PO1, PO2]
    p = 1
    if  (ferment_input_status_N[i] == 'FI_Waiting'):
        N = 'FI' + str(f)
        print(N + ' ' + ferment_input_status_N[i] + ' 空桶等待')
        update_Ferment_Input_status(f)
        time.sleep(1)
    elif (ferment_input_status_N[i] == 'FI_InputtingBean'):
        print('FI' + str(f) + ' ' + ferment_input_status_N[i] + ' 入豆中')
        for b in range(len(get_Peel_Output_status_N)):
            p = b + 1
            if (get_Peel_Output_status_N[b] == 'PO_InputtingBean'):
                print('PO' + str(p) + ' ' + get_Peel_Output_status_N[b] + ' 入豆中')
            elif (get_Peel_Output_status_N[b] == 'PO_Waiting'):
                print('PO' + str(p) + ' ' + get_Peel_Output_status_N[b] + ' 空桶等待')
            elif (get_Peel_Output_status_N[b] == 'PO_Stand_by'):
                print('PO' + str(p) + '待命')
            elif (get_Peel_Output_status_N[b] == 'PO_OutputtingBean'):
                print('PO' + str(p) + ' ' + get_Peel_Output_status_N[b] + ' 可出豆')
                send_mqtt()
                print('發送MQTT訊號通知', 'PO' + str(p) + ' 可出豆')
                # 修改發酵入料槽狀態
                update_Fer_Input_status(f)
                # 修改脫皮出料狀態
                update_Peel_Output_status(p)
                break

    # 第二部分開始判斷發酵入料是否是可出豆狀態
    elif (ferment_input_status_N[i] == 'FI_OutputtingBean'):
        print('FI' + str(f) + ' ' + ferment_input_status_N[i] + ' 可出豆')
        cur1 = conn.cursor()
        sql1 = "SELECT `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` " \
               "FROM `ferment_container_status` ORDER BY `sn` DESC LIMIT 1 "
        cur1.execute(sql1)
        get_Ferment_Tank = cur1.fetchone()
        # print(get_Ferment_Tank)

        # 發酵槽1-12號
        F1 = get_Ferment_Tank[0]
        F2 = get_Ferment_Tank[1]
        F3 = get_Ferment_Tank[2]
        F4 = get_Ferment_Tank[3]
        F5 = get_Ferment_Tank[4]
        F6 = get_Ferment_Tank[5]
        F7 = get_Ferment_Tank[6]
        F8 = get_Ferment_Tank[7]
        F9 = get_Ferment_Tank[8]
        F10 = get_Ferment_Tank[9]
        F11 = get_Ferment_Tank[10]
        F12 = get_Ferment_Tank[11]
        ferment_Tank_status = [F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12]
        f_tank = 1
        for i_tank in range(len(ferment_Tank_status)):
            f_tank = i_tank + 1
            if (ferment_Tank_status[i_tank] == 'F_Waiting'):
                # 修改發酵桶槽狀態F_Waiting->F_InputtingBean
                update_Fer_Tank_status1(f_tank)
                print('F' + str(f_tank) + '修改發酵桶槽狀態F_Waiting->F_InputtingBean')

            elif (ferment_Tank_status[i_tank] == 'F_InputtingBean'):
                # 修改發酵桶槽狀態F_InputtingBean->F_Standby
                update_Fer_Tank_status(f_tank)
                print('F' + str(f_tank) + ' ' + ferment_Tank_status[i_tank] + ' 入豆中')
                send_mqtt()
                break
            elif (ferment_Tank_status[i_tank] == 'F_OutputtingBean'):
                cur1 = conn.cursor()
                sql1 = "SELECT `Ferment_Output_1`,`Ferment_Output_2` FROM `ferment_container_status` ORDER BY `sn` DESC LIMIT 1 "
                cur1.execute(sql1)
                get_Ferment_Output = cur1.fetchone()

                # 發酵出料儲豆槽1
                FO1 = get_Ferment_Output[0]
                # 發酵出料儲豆槽2
                FO2 = get_Ferment_Output[1]
                ferment_Output_status = [FO1, FO2]
                f_Output = 1
                for i_Output in range(len(ferment_Output_status)):
                    f_Output = i_Output + 1
                    if (ferment_Output_status[i_Output] == 'FO_Waiting'):
                        update_Fer_Output_status(f_Output)
                        print('FO' + str(f_Output) + ' 修改發酵桶槽狀態FO_Waiting->FO_InputtingBean')
                    elif (ferment_Output_status[i_Output] == 'FO_InputtingBean'):
                        update_Fer_Output_status_Stand_by(f_Output)
                        print('修改' + 'FO' + str(f_Output) + '出料槽狀態，並發送MQTT訊號。')
                        send_mqtt()
                    elif (ferment_Output_status[i_Output] == 'FO_OutputtingBean'):
                        cur = conn.cursor()
                        sql = "SELECT `Dry_Input_1`,`Dry_Input_2` FROM `dry_container_status` ORDER BY `sn` DESC LIMIT 1"
                        cur.execute(sql)
                        get_Dry_Input_status = cur.fetchone()
                        # 清洗脫皮出料儲豆槽1
                        DI1 = get_Dry_Input_status[0]
                        # 清洗脫皮出料儲豆槽2
                        DI2 = get_Dry_Input_status[1]
                        get_Dry_Input_status_N = [DI1, DI2]
                        p = 1
                        for b in range(len(get_Dry_Input_status_N)):
                            p = b + 1
                            if (get_Dry_Input_status_N[b] == 'DI_Waiting'):
                                update_Dry_Input_status_input(p)
                                print('DI' + str(p) + ' ' + get_Dry_Input_status_N[
                                    b] + ' 空桶等待,並修改狀態變成"DI_InputtingBean"')
                            elif (get_Dry_Input_status_N[b] == 'DI_InputtingBean'):
                                update_Dry_Input_status(p)
                                print('DI' + str(p) + ' ' + get_Dry_Input_status_N[b] + ' 入豆中,並修改狀態變成"DI_Standby"')
                                send_mqtt()
                                print('發送MQTT', 'DI' + str(p))
                            elif (get_Dry_Input_status_N[b] == 'DI_Stand_by'):
                                print('DI' + str(p) + ' ' + get_Dry_Input_status_N[b] + '待命中')
                    elif (ferment_Output_status[i_Output] == 'FO_Standby'):
                        print('FO' + str(f_Output) + ' 待命中')

            elif (ferment_Tank_status[i_tank] == 'F_Fermenting'):
                print('F_Fermenting,發酵中')
            elif (ferment_Tank_status[i_tank] == 'F_Standby'):
                print('F' + str(f_tank) + '待命')
            elif (ferment_Tank_status[i_tank] == 'F_InputtingBean_Pause'):
                print('F' + str(f_tank) + '入豆暫停')
            elif (ferment_Tank_status[i_tank] == 'F_InputtingBean_Finish'):
                print('F' + str(f_tank) + '入豆完成')
            elif (ferment_Tank_status[i_tank] == 'F_InputtingWater'):
                print('F' + str(f_tank) + '入水中')
            elif (ferment_Tank_status[i_tank] == 'F_Cleaning'):
                print('F' + str(f_tank) + '清洗中')
            elif (ferment_Tank_status[i_tank] == 'F_Warning'):
                print('F' + str(f_tank) + '發生錯誤')
    elif (ferment_input_status_N[i] == 'FI_Standby'):
        print('FI' + str(f) + ' ' + ferment_input_status_N[i] + '待命')
    elif (ferment_input_status_N[i] == 'FI_Error'):
        print('FI' + str(f) + ' ' + ferment_input_status_N[i] + '執行緊急停止流程')