NEW_coffee_project/coffeeproject/test_auto.py

import pymysql
import time
import datetime
import random
import json
import paramiko


# 測試自動流程
#
# 第一步份
# 發酵入料先改成FI_waiting 會自動改成FI_Inputtingbean
# 脫皮出料改成PO_Outputtingbean
#
# 這樣如果兩個槽都是FI_inputtingbean跟PO_Outputtingbean會再改成standby並傳mqtt
#
# 第二部分
# 發酵入料改成FI_Outputtingbean，發酵槽改成F_Waiting會自動改成F_InputtingBean，然後會改成standby並傳mqtt
#
# 第三部分
# 發酵槽改成F_outputting，發酵出料改成Fo_Waiting會自動改成FO_InputtingBean，然後會改成standby並傳mqtt
#
# 第四部份
# 發酵出料改成FO_Outputtingbean，乾燥入料改成DI_Waiting會自動改成DI_InputtingBean，然後會改成standby並傳mqtt


def first_parts():
    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 = conn.cursor()
    Ferment_name = 'Ferment_Input_'
    re = "UPDATE `ferment_container_status` SET " + Ferment_name + str(
        1) + " = 'FI_Waiting' ORDER BY `datetime` DESC LIMIT 1 "
    update_status.execute(re)
    conn.commit()

    insert1 = conn.cursor()
    insert_status1 = "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"
    insert1.execute(insert_status1)
    conn.commit()
    update_status_peel = conn.cursor()
    Peel_name = 'Peel_Output_'
    re_peel = "UPDATE `clean_container_status` SET " + Peel_name + str(1) + "= 'PO_OutputtingBean' " \
                                                                            "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_peel.execute(re_peel)
    conn.commit()

    print('模擬：修改脫皮機狀態成-->PO_Outputtingbean')
    print('模擬：修改發酵入料儲豆槽狀態成-->FI_Waiting')

def second_parts():
    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 = conn.cursor()
    Ferment_name = 'Ferment_Input_'
    re = "UPDATE `ferment_container_status` SET " + Ferment_name + str(
        1) + " = 'FI_OutputtingBean' ORDER BY `datetime` DESC LIMIT 1 "
    update_status.execute(re)
    conn.commit()

    update_status_fer_Tank = conn.cursor()
    Ferment_Tank_name = 'Ferment_Tank_'
    re_fer_Tank = "UPDATE `ferment_container_status` SET " + Ferment_Tank_name + str(1) + "= 'F_Waiting' " \
                                                                                               "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_fer_Tank.execute(re_fer_Tank)
    conn.commit()

    print('模擬：修改發酵入料儲豆槽狀態成-->FI_OutputtingBean')
    print('模擬：修改發酵槽狀態成-->F_Waiting')

def third_parts():
    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_fer_Tank = conn.cursor()
    Ferment_Tank_name = 'Ferment_Tank_'
    re_fer_Tank = "UPDATE `ferment_container_status` SET " + Ferment_Tank_name + str(1) + "= 'F_OutputtingBean' " \
                                                                                          "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_fer_Tank.execute(re_fer_Tank)


    update_status_fer = conn.cursor()
    Ferment_name = 'Ferment_Output_'
    re_fer = "UPDATE `ferment_container_status` SET " + Ferment_name + str(1) + "= 'FO_Waiting' " \
                                                                                       "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_fer.execute(re_fer)
    conn.commit()

    print('模擬：修改發酵槽狀態成-->F_OutputtingBean')
    print('模擬：修改發酵出料儲豆槽狀態成-->FO_Waiting')

def four_parts():
    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_fer = conn.cursor()
    Ferment_name = 'Ferment_Output_'
    re_fer = "UPDATE `ferment_container_status` SET " + Ferment_name + str(1) + "= 'FO_OutputtingBean' " \
                                                                                "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_fer.execute(re_fer)
    conn.commit()

    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()

    update_status_dry = conn.cursor()
    Dry_name = 'Dry_Input_'
    re_dry = "UPDATE `dry_container_status` SET "+Dry_name+str(1)+"= 'DI_Waiting' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_dry.execute(re_dry)
    conn.commit()

    print('模擬：修改發酵出料槽狀態成-->FO_OutputtingBean')
    print('模擬：修改乾燥入料儲豆槽狀態成-->DI_Waiting')




if __name__ == "__main__":
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='g53743001',
        db='coffee',
        charset='utf8')
    #first_parts()
    #time.sleep(1)
    #second_parts()
    #time.sleep(1)
    #third_parts()
    #time.sleep(1)
    four_parts()
    #time.sleep(1)