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] + '執行緊急停止流程')