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] + '警告')