#主業務邏輯中的視圖和路由的定義
import os
import datetime
from flask import render_template, request, session, Response, jsonify
#導入藍圖程序，用於構建路由
from werkzeug.utils import redirect
from . import main
from coffee_manage import mqtt
#導入db，用於操作數據庫
from coffee_manage import db
#導入實體類，用於操作數據庫
from ..models import *
import json
from datetime import datetime as dt
from sqlalchemy import text
import pymysql
import pandas as pd
from concurrent.futures import ThreadPoolExecutor
import cv2
import pickle
import socket
from time import sleep as sl
import threading
import numpy as np
import math

from .mqtt import MQTT


pool = ThreadPoolExecutor(25)
s_sock = 0
lock = threading.Lock()



mydb = pymysql.connect(host='52.69.200.169', port=3306, user='coffee', password='skyeye', database='Coffee',
                          charset='utf8')
mycursor = mydb.cursor()


#主頁的訪問路徑
@main.route('/')
def main_index():
    #獲取登入信息
    if 'id' in session and 'uname' in session:
        username = session['uname']


        return render_template('index.html',params=locals())
    else:
        return render_template('sign_in.html')

#登入頁面的訪問路徑
@main.route('/login', methods=['GET','POST'])
def login_views():
    if request.method == 'GET':
        if 'id' in session and 'uname' in session:
            return redirect('/')
        else:
            return render_template('sign_in.html')
    else:
        #接收前端傳過來的資料
        username = request.form['username']
        password = request.form['password']
        #使用接收的用戶和密碼到資料庫中查詢
        user = User.query.filter_by(username=username, password=password).first()
        #如果用戶存在，將信息保存置session並重定向回首頁，否則重定向回登入頁
        if user:
            resp = redirect('/')
            #判斷是否有記住密碼
            if 'rem' in request.form:
                userID = str(user.userID)
                max_age = 60*60*24*365
                resp.set_cookie("username", username, max_age=max_age)
                resp.set_cookie("userID", userID, max_age=max_age)

            session['uname'] = user.username
            session['id'] = user.userID
            return resp
        else:
            errMsg = "Wrong login or password"
            return render_template('sign_in.html',errMsg=errMsg)

#登入重設密碼的頁面
@main.route('/reset_password', methods=['POST', 'GET'])
def reset_password_views():
    if request.method == 'GET':
        if 'mail' in session:
            del session['mail']
        return render_template('reset_pwd1.html')
    else:
        #如果有id在session裡，代表從reset_pwd2過來的
        if "mail" in session:
            new_pwd = request.form['new_pwd']
            confirm_pwd = request.form['confirm_pwd']
            #判斷密碼是否一致
            if new_pwd == confirm_pwd:
                mail = session['mail']
                user = User.query.filter_by(mail=mail).first()
                user.password = new_pwd
                db.session.add(user)
                del session['mail']
                #修改完後回登入頁
                return redirect('/login')
            else:
                errMsg = "Passwords does not match"
                return render_template('reset_pwd2.html', errMsg=errMsg)

        email = request.form['email']
        user = User.query.filter_by(mail=email).first()
        if user:
            session['mail'] = user.mail
            return render_template('reset_pwd2.html')
        else:
            errMsg = "Wrong email.Please try again"
            return render_template('reset_pwd1.html', errMsg=errMsg)

#註冊頁面的訪問路徑
@main.route('/register',methods=['POST',"GET"])
def register_views():
    if request.method == 'GET':
        return render_template('registration.html')
    else:
        #獲取文本框的值並賦值給user實體對象
        user = User()
        user.firstname = request.form['firstname']
        user.lastname = request.form['lastname']
        user.mail = request.form['email']
        user.phone = request.form['phone']
        user.username = request.form['username']
        user.password = request.form['password']
        user.status = 1
        #將數據保存進資料庫  - 註冊
        db.session.add(user)
        #手動提交，目的是為了獲取提交後的user的id
        db.session.commit()
        #當user成功插入進資料庫之後，程序會自動將所有信息取出來在賦值給user
        #完成登入的操作
        user = User.query.filter_by(username=user.username).first()
        session['id'] = user.userID
        session['uname'] = user.username
        return redirect('/')

#驗證email訪問路徑
@main.route('/check_email')
def check_email_views():
    email = request.args['email']
    user = User.query.filter_by(mail=email).first()
    if user:
        result = {"errMsg":" "}
    else:
        result = {"pass":" "}
    return json.dumps(result)

#驗證username訪問路徑
@main.route('/check_username')
def check_username_views():
    username = request.args['username']
    user = User.query.filter_by(username=username).first()
    if user:
        result = {"errMsg":" "}
    else:
        result = {"pass":" "}
    return json.dumps(result)


#咖啡貨櫃的訪問路徑
@main.route('/cargo_list', methods=['GET', 'POST'])
def cargo_list_views():
    username = session['uname']
    if request.method == 'GET':
        return render_template('cargo_list.html', params=locals())
    else:
        pass


#貨櫃1的訪問路徑
@main.route('/cargo1', methods=['GET', 'POST'])
def cargo1_views():
    username = session['uname']
    if request.method == 'GET':
        return render_template('cargo1.html', params=locals())
    else:
        pass

#貨櫃1排程的訪問路徑
@main.route('/cargo1_schedule', methods=['GET', 'POST'])
def cargo1_schedule_views():
    username = session['uname']
    if request.method == 'GET':
        #將已儲存的排程資料傳給前端
        #進豆閥
        try:
            bean = BeanValve.query.order_by(text('datetime desc')).first()
            datetime = bean.datetime
            beans = BeanValve.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass

        #清洗機
        try:
            wash = WashMachine.query.order_by(text('datetime desc')).first()
            datetime = wash.datetime
            washes = WashMachine.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #輸送帶1
        try:
            belt1 = ConveyorBelt1.query.order_by(text('datetime desc')).first()
            datetime = belt1.datetime
            belts1 = ConveyorBelt1.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass



        #消毒機
        try:
            disinfect = Cargo1Disinfect.query.order_by(text('datetime desc')).first()
            datetime = disinfect.datetime
            disinfects = Cargo1Disinfect.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #色選機
        try:
            color = ColorMachine.query.order_by(text('datetime desc')).first()
            datetime = color.datetime
            colors = ColorMachine.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #輸送帶2
        try:
            belt2 = ConveyorBelt2.query.order_by(text('datetime desc')).first()
            datetime = belt2.datetime
            belts2 = ConveyorBelt2.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #去皮機
        try:
            peeling = PeelingMachine.query.order_by(text('datetime desc')).first()
            datetime = peeling.datetime
            peelings = PeelingMachine.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #輸送帶3
        try:
            belt3 = ConveyorBelt3.query.order_by(text('datetime desc')).first()
            datetime = belt3.datetime
            belts3 = ConveyorBelt3.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        return render_template('cargo1_schedule.html', params=locals())
    else:
        current_time = dt.now()


        # 循環從前端提交過來的資料
        for i in request.form:
            # 進豆閥
            if i[:10] == 'inletValve':
                if i[:19] == 'inletValve_duration':
                    bean = BeanValve()
                    bean.duration = request.form[i]

                elif i[:18] == 'inletValve_from_hr':
                    iv_from_hr = request.form[i]

                elif i[:19] == 'inletValve_from_min':
                    iv_from_min = request.form[i]
                    bean.start = iv_from_hr + ":" + iv_from_min

                elif i[:16] == 'inletValve_to_hr':
                    iv_to_hr = request.form[i]

                elif i[:17] == 'inletValve_to_min':
                    iv_to_min = request.form[i]
                    bean.end = iv_to_hr + ":" + iv_to_min

                    bean.datetime = current_time
                    db.session.add(bean)
                    db.session.commit()


            #清洗機
            elif i[:4] == 'wash':
                if i[:13] == 'wash_duration':
                    wash = WashMachine()
                    wash.duration = request.form[i]

                elif i[:12] == 'wash_from_hr':
                    wash_from_hr = request.form[i]

                elif i[:13] == 'wash_from_min':
                    wash_from_min = request.form[i]
                    wash.start = wash_from_hr + ":" + wash_from_min


                elif i[:10] == 'wash_to_hr':
                    wash_to_hr = request.form[i]

                elif i[:11] == 'wash_to_min':
                    wash_to_min = request.form[i]
                    wash.end = wash_to_hr + ":" + wash_to_min

                    wash.datetime = current_time
                    db.session.add(wash)
                    db.session.commit()

            #輸送帶1
            elif i[:5] == 'belt1':
                if i[:14] == 'belt1_duration':
                    belt1 = ConveyorBelt1()
                    belt1.duration = request.form[i]

                elif i[:13] == 'belt1_from_hr':
                    belt1_from_hr = request.form[i]

                elif i[:14] == 'belt1_from_min':
                    belt1_from_min = request.form[i]
                    belt1.start = belt1_from_hr + ":" + belt1_from_min


                elif i[:11] == 'belt1_to_hr':
                    belt1_to_hr = request.form[i]

                elif i[:12] == 'belt1_to_min':
                    belt1_to_min = request.form[i]
                    belt1.end = belt1_to_hr + ":" + belt1_to_min

                    belt1.datetime = current_time
                    db.session.add(belt1)
                    db.session.commit()

            #消毒
            elif i[:9] == 'disinfect':
                if i[:18] == 'disinfect_duration':
                    disinfect = Cargo1Disinfect()
                    disinfect.duration = request.form[i]

                elif i[:17] == 'disinfect_from_hr':
                    di_from_hr = request.form[i]

                elif i[:18] == 'disinfect_from_min':
                    di_from_min = request.form[i]
                    disinfect.start = di_from_hr + ":" + di_from_min


                elif i[:15] == 'disinfect_to_hr':
                    di_to_hr = request.form[i]

                elif i[:16] == 'disinfect_to_min':
                    di_to_min = request.form[i]
                    disinfect.end = di_to_hr + ":" + di_to_min

                    disinfect.datetime = current_time
                    db.session.add(disinfect)
                    db.session.commit()

            #色選機
            elif i[:5] == 'color':
                if i[:14] == 'color_duration':
                    color = ColorMachine()
                    color.duration = request.form[i]

                elif i[:13] == 'color_from_hr':
                    color_from_hr = request.form[i]

                elif i[:14] == 'color_from_min':
                    color_from_min = request.form[i]
                    color.start = color_from_hr + ":" + color_from_min

                elif i[:11] == 'color_to_hr':
                    color_to_hr = request.form[i]

                elif i[:12] == 'color_to_min':
                    color_to_min = request.form[i]
                    color.end = color_to_hr + ":" + color_to_min

                    color.datetime = current_time
                    db.session.add(color)
                    db.session.commit()



            #輸送帶2
            elif i[:5] == 'belt2':
                if i[:14] == 'belt2_duration':
                    belt2 = ConveyorBelt2()
                    belt2.duration = request.form[i]

                elif i[:13] == 'belt2_from_hr':
                    belt2_from_hr = request.form[i]

                elif i[:14] == 'belt2_from_min':
                    belt2_from_min = request.form[i]
                    belt2.start = belt2_from_hr + ":" + belt2_from_min

                elif i[:11] == 'belt2_to_hr':
                    belt2_to_hr = request.form[i]

                elif i[:12] == 'belt2_to_min':
                    belt2_to_min = request.form[i]
                    belt2.end = belt2_to_hr + ":" + belt2_to_min

                    belt2.datetime = current_time
                    db.session.add(belt2)
                    db.session.commit()


            #去皮機
            elif i[:6] == 'peeled':
                if i[:15] == 'peeled_duration':
                    peeling = PeelingMachine()
                    peeling.duration = request.form[i]

                elif i[:14] == 'peeled_from_hr':
                    peeled_from_hr = request.form[i]

                elif i[:15] == 'peeled_from_min':
                    peeled_from_min = request.form[i]
                    peeling.start = peeled_from_hr + ":" + peeled_from_min


                elif i[:12] == 'peeled_to_hr':
                    peeled_to_hr = request.form[i]

                elif i[:13] == 'peeled_to_min':
                    peeled_to_min = request.form[i]
                    peeling.end = peeled_to_hr + ":" + peeled_to_min

                    peeling.datetime = current_time
                    db.session.add(peeling)
                    db.session.commit()


            #輸送帶3
            elif i[:5] == 'belt3':
                if i[:14] == 'belt3_duration':
                    belt3 = ConveyorBelt3()
                    belt3.duration = request.form[i]

                elif i[:13] == 'belt3_from_hr':
                    belt3_from_hr = request.form[i]

                elif i[:14] == 'belt3_from_min':
                    belt3_from_min = request.form[i]
                    belt3.start = belt3_from_hr + ":" + belt3_from_min

                elif i[:11] == 'belt3_to_hr':
                    belt3_to_hr = request.form[i]

                elif i[:12] == 'belt3_to_min':
                    belt3_to_min = request.form[i]
                    belt3.end = belt3_to_hr + ":" + belt3_to_min

                    belt3.datetime = current_time
                    db.session.add(belt3)
                    db.session.commit()


        return render_template('cargo1.html', params=locals())




#貨櫃2的訪問路徑
@main.route('/cargo2', methods=['GET', 'POST'])
def cargo2_views():
    username = session['uname']
    if request.method == 'GET':
        return render_template('cargo2.html', params=locals())
    else:
        pass

#貨櫃2排程的發酵槽訪問路徑
@main.route('/cargo2_schedule_tanks', methods=['GET', 'POST'])
def cargo2_schedule_tanks_views():
    username = session['uname']
    if request.method == 'GET':
        return render_template('cargo2_schedule_tanks.html', params=locals())
    else:
        pass

#貨櫃2排程發酵槽的清單訪問路徑
@main.route('/cargo2_schedule/<tid>', methods=['GET', 'POST'])
def cargo2_schedule_views(tid):
    username = session['uname']
    if request.method == 'GET':
        #將已儲存的排程資料傳給前端
        # 打菌
        try:
            bacteria = Bacteria.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
            datetime = bacteria.datetime
            bacterias = Bacteria.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass

        #消毒
        try:
            disinfect = Cargo2Disinfect.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
            datetime = disinfect.datetime
            disinfects = Cargo2Disinfect.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #加熱
        try:
            heating = Heating.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
            datetime = heating.datetime
            heatings = Heating.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass



        #攪拌
        try:
            stir = Stir.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
            datetime = stir.datetime
            stirs = Stir.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #注水
        try:
            water = WaterInjection.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
            datetime = water.datetime
            waters = WaterInjection.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #開上閥
        try:
            top = TopValve.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
            datetime = top.datetime
            tops = TopValve.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #開下閥
        try:
            bottom = BottomValve.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
            datetime = bottom.datetime
            bottoms = BottomValve.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass

        #溫度
        try:
            tem = Temperature.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
            datetime = tem.datetime
            tems = Temperature.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass

        return render_template('cargo2_schedule.html', params=locals())
    else:
        current_time = dt.now()

        # 循環從前端提交過來的資料
        for i in request.form:
            # 打菌
            if i[:8] == 'bacteria':
                if i[:17] == 'bacteria_duration':
                    bacteria = Bacteria()
                    bacteria.duration = request.form[i]

                elif i[:16] == 'bacteria_from_hr':
                    bac_from_hr = request.form[i]

                elif i[:17] == 'bacteria_from_min':
                    bac_from_min = request.form[i]
                    bacteria.start = bac_from_hr + ":" + bac_from_min

                elif i[:14] == 'bacteria_to_hr':
                    bac_to_hr = request.form[i]

                elif i[:15] == 'bacteria_to_min':
                    bac_to_min = request.form[i]
                    bacteria.end = bac_to_hr + ":" + bac_to_min

                    bacteria.datetime = current_time
                    bacteria.tank_num = int(tid)
                    db.session.add(bacteria)
                    db.session.commit()


            #消毒
            elif i[:9] == 'disinfect':
                if i[:18] == 'disinfect_duration':
                    disinfect = Cargo2Disinfect()
                    disinfect.duration = request.form[i]

                elif i[:17] == 'disinfect_from_hr':
                    di_from_hr = request.form[i]

                elif i[:18] == 'disinfect_from_min':
                    di_from_min = request.form[i]
                    disinfect.start = di_from_hr + ":" + di_from_min


                elif i[:15] == 'disinfect_to_hr':
                    di_to_hr = request.form[i]

                elif i[:16] == 'disinfect_to_min':
                    di_to_min = request.form[i]
                    disinfect.end = di_to_hr + ":" + di_to_min

                    disinfect.datetime = current_time
                    disinfect.tank_num = int(tid)
                    db.session.add(disinfect)
                    db.session.commit()


            #加熱
            elif i[:7] == 'heating':
                if i[:16] == 'heating_duration':
                    heating = Heating()
                    heating.duration = request.form[i]

                elif i[:15] == 'heating_from_hr':
                    heat_from_hr = request.form[i]

                elif i[:16] == 'heating_from_min':
                    heat_from_min = request.form[i]
                    heating.start = heat_from_hr + ":" + heat_from_min


                elif i[:13] == 'heating_to_hr':
                    heat_to_hr = request.form[i]

                elif i[:14] == 'heating_to_min':
                    heat_to_min = request.form[i]
                    heating.end = heat_to_hr + ":" + heat_to_min

                    heating.datetime = current_time
                    heating.tank_num = int(tid)
                    db.session.add(heating)
                    db.session.commit()

            #攪拌
            elif i[:4] == 'stir':
                if i[:13] == 'stir_duration':
                    stir = Stir()
                    stir.duration = request.form[i]

                elif i[:12] == 'stir_from_hr':
                    stir_from_hr = request.form[i]

                elif i[:13] == 'stir_from_min':
                    stir_from_min = request.form[i]
                    stir.start = stir_from_hr + ":" + stir_from_min


                elif i[:10] == 'stir_to_hr':
                    stir_to_hr = request.form[i]

                elif i[:11] == 'stir_to_min':
                    stir_to_min = request.form[i]
                    stir.end = stir_to_hr + ":" + stir_to_min

                    stir.datetime = current_time
                    stir.tank_num = int(tid)
                    db.session.add(stir)
                    db.session.commit()


            #注水
            elif i[:5] == 'water':
                if i[:14] == 'water_duration':
                    water = WaterInjection()
                    water.duration = request.form[i]

                elif i[:13] == 'water_from_hr':
                    water_from_hr = request.form[i]

                elif i[:14] == 'water_from_min':
                    water_from_min = request.form[i]
                    water.start = water_from_hr + ":" + water_from_min


                elif i[:11] == 'water_to_hr':
                    water_to_hr = request.form[i]

                elif i[:12] == 'water_to_min':
                    water_to_min = request.form[i]
                    water.end = water_to_hr + ":" + water_to_min

                    water.datetime = current_time
                    water.tank_num = int(tid)
                    db.session.add(water)
                    db.session.commit()


            #開上閥
            elif i[:9] == 'highValve':
                if i[:18] == 'highValve_duration':
                    top = TopValve()
                    top.duration = request.form[i]

                elif i[:17] == 'highValve_from_hr':
                    h_valve_from_hr = request.form[i]

                elif i[:18] == 'highValve_from_min':
                    h_valve_from_min = request.form[i]
                    top.start = h_valve_from_hr + ":" + h_valve_from_min

                elif i[:15] == 'highValve_to_hr':
                    h_valve_to_hr = request.form[i]

                elif i[:16] == 'highValve_to_min':
                    h_valve_to_min = request.form[i]
                    top.end = h_valve_to_hr + ":" + h_valve_to_min

                    top.datetime = current_time
                    top.tank_num = int(tid)
                    db.session.add(top)
                    db.session.commit()

            #開下閥
            elif i[:8] == 'lowValve':
                if i[:17] == 'lowValve_duration':
                    bottom = BottomValve()
                    bottom.duration = request.form[i]

                elif i[:16] == 'lowValve_from_hr':
                    l_valve_from_hr = request.form[i]

                elif i[:17] == 'lowValve_from_min':
                    l_valve_from_min = request.form[i]
                    bottom.start = l_valve_from_hr + ":" + l_valve_from_min

                elif i[:14] == 'lowValve_to_hr':
                    l_valve_to_hr = request.form[i]

                elif i[:15] == 'lowValve_to_min':
                    l_valve_to_min = request.form[i]
                    bottom.end = l_valve_to_hr + ":" + l_valve_to_min

                    bottom.datetime = current_time
                    bottom.tank_num = int(tid)
                    db.session.add(bottom)
                    db.session.commit()

            #溫度
            elif i[:3] == 'tem':
                if i[:12] == 'tem_duration':
                    tem = Temperature()
                    tem.duration = request.form[i]

                elif i[:11] == 'tem_from_hr':
                    tem_from_hr = request.form[i]

                elif i[:12] == 'tem_from_min':
                    tem_from_min = request.form[i]
                    tem.start = tem_from_hr + ":" + tem_from_min


                elif i[:9] == 'tem_to_hr':
                    tem_to_hr = request.form[i]

                elif i[:10] == 'tem_to_min':
                    tem_to_min = request.form[i]
                    tem.end = tem_to_hr + ":" + tem_to_min

                    tem.datetime = current_time
                    tem.tank_num = int(tid)
                    db.session.add(tem)
                    db.session.commit()


        return render_template('cargo2.html', params=locals())




#貨櫃2感測器的發酵槽訪問路徑
@main.route('/cargo2_sensor_tanks', methods=['GET', 'POST'])
def cargo2_sensor_tanks_views():
    username = session['uname']
    if request.method == 'GET':
        return render_template('cargo2_sensor_tanks.html', params=locals())
    else:
        pass

#貨櫃2感測器發酵槽的清單訪問路徑
@main.route('/cargo2_sensor/<tid>', methods=['GET', 'POST'])
def s_tank_views(tid):
    #判斷用戶是否已關閉瀏覽器或登出後，在訪問這個route，如果沒有session就回登入頁
    try:
        username = session['uname']
    except KeyError:
        return redirect('/')

    if request.method == 'GET':
        tank_tem = TankTemSensor.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
        tank_ph = TankPHSensor.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
        tank_ec = TankECSensor.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
        tank_sonic = TankSonicSensor.query.filter_by(tank_num=int(tid)).order_by(text('datetime desc')).first()
        return render_template('cargo2_sensor.html', params=locals())
    else:
        pass


#貨櫃2感測器發酵槽歷史數據的訪問路徑
@main.route('/history_data', methods=['GET', 'POST'])
def history_data_views():
    if request.method == 'GET':
        info = request.args.to_dict()
        evt = info['evt']
        avg = int(info['avg'])
        max = int(info['max'])
        min = int(info['min'])
        time_interval = info['time-interval']
        # print(info)
        date_start = info['date-start']
        date_end_list = info['date-end'].split('-')
        date_end_list[2] = str(int(date_end_list[2]) + 1)
        date_end = '-'.join(date_end_list)
        tank_num = evt.split('-')[0]
        sensor = evt.split('-')[1]
        L = []
        D = {}
        maxData = []
        minData = []
        avgData = []


        def sensorData(data_name):

            def dataResample(time_interval, how, data_name):
                interval = 0
                #設置resample的第一個參數，要按照逐月、逐日或逐時
                if time_interval == 'month':
                    interval = "M"
                elif time_interval == 'day':
                    interval = "D"
                elif time_interval == 'hour':
                    interval = "H"

                if how == "max":
                    # data = df.resample(interval, how={data_name:how})
                    #最新版的pandas
                    data = df.resample(interval).max()
                    # 刪除具有NaN的值
                    data = data.dropna(axis=0, how='any')

                    if time_interval == ('hour' or 'day'):
                        data.index = pd.to_datetime(data.index, format='%Y-%b-%d_%H:%M:%S')  # 将数据类型转换为日期类型

                    for a in range(0, len(data.index)):
                        l = []
                        # 如果是逐時，才顯示小時分秒
                        if time_interval == 'hour':
                            l.append(str(data.index[a]))
                        else:
                            l.append(str(data.index[a]).split(' ')[0])

                        l.append(str(data[data_name][a]))
                        maxData.append(l)
                    D['max'] = maxData

                elif how == "mean":
                    # data = df.resample(interval, how={data_name:how})
                    #最新版的pandas
                    data = df.resample(interval).mean()
                    #刪除具有NaN的值
                    data = data.dropna(axis=0, how='any')
                    #將平均數取自小數1位
                    data = data.round({data_name:1})

                    for a in range(0, len(data.index)):
                        l = []
                        # 如果是逐時，才顯示小時分秒
                        if time_interval == 'hour':
                            l.append(str(data.index[a]))
                        else:
                            l.append(str(data.index[a]).split(' ')[0])
                        l.append(str(data[data_name][a]))
                        avgData.append(l)
                    D['avg'] = avgData

                elif how == "min":
                    # data = df.resample(interval, how={data_name:how})
                    #最新版的pandas
                    data = df.resample(interval).min()
                    # 刪除具有NaN的值
                    data = data.dropna(axis=0, how='any')

                    for a in range(0, len(data.index)):
                        # print(data.index[a])
                        l = []
                        #如果是逐時，才顯示小時分秒
                        if time_interval == 'hour':
                            l.append(str(data.index[a]))
                        else:
                            l.append(str(data.index[a]).split(' ')[0])
                        l.append(str(data[data_name][a]))
                        minData.append(l)
                    D['min'] = minData


            df = pd.DataFrame(L, columns=["datetime", data_name])  # 指定欄標籤名稱
            df['datetime'] = pd.to_datetime(df['datetime'], format='%Y-%b-%d_%H:%M:%S')  # 将数据类型转换为日期类型
            # print(df['datetime'])
            df[data_name] = df[data_name].astype(float)
            df = df.set_index('datetime')  # 将datetime设置为index
            # print(df)


            if time_interval == 'month':
                if max:
                    dataResample(time_interval, "max", data_name)
                if avg:
                    dataResample(time_interval, "mean", data_name)
                if min:
                    dataResample(time_interval, "min", data_name)

            elif time_interval == 'day':
                if max:
                    dataResample(time_interval, "max", data_name)
                if avg:
                    dataResample(time_interval, "mean", data_name)
                if min:
                    dataResample(time_interval, "min", data_name)

            elif time_interval == 'hour':
                if max:
                    dataResample(time_interval, "max", data_name)
                if avg:
                    dataResample(time_interval, "mean", data_name)
                if min:
                    dataResample(time_interval, "min", data_name)





        if sensor == '溫濕度':
            # tank_tem = TankTemSensor.query.filter_by(tank_num=int(tank_num)).order_by(text('datetime desc')).all()
            tank_tem = TankTemSensor.query.filter_by(tank_num=int(tank_num)).filter(TankTemSensor.datetime.between(date_start,date_end)).all()
            for tem in tank_tem:
                l = []
                time = dt.strftime(tem.datetime, '%Y-%b-%d_%H:%M:%S')
                # time = dt.strftime(tem.datetime, '%Y-%m-%d')
                l.append(time)
                l.append(tem.tem)
                L.append(l)

            # print(L)

            D['all'] = L
            sensorData("tem")

        elif sensor == '酸鹼值':
            tank_ph = TankPHSensor.query.filter_by(tank_num=int(tank_num)).filter(TankPHSensor.datetime.between(date_start,date_end)).all()
            for ph in tank_ph:
                l = []
                time = dt.strftime(ph.datetime, '%Y-%b-%d_%H:%M:%S')
                # time = dt.strftime(tem.datetime, '%Y-%m-%d')
                l.append(time)
                l.append(ph.ph)
                L.append(l)

            # print(L)

            D['all'] = L
            sensorData("ph")

        elif sensor == 'EC值':
            tank_ec = TankECSensor.query.filter_by(tank_num=int(tank_num)).filter(TankECSensor.datetime.between(date_start,date_end)).all()
            for ec in tank_ec:
                l = []
                time = dt.strftime(ec.datetime, '%Y-%b-%d_%H:%M:%S')
                # time = dt.strftime(tem.datetime, '%Y-%m-%d')
                l.append(time)
                l.append(ec.ec)
                L.append(l)

            # print(L)

            D['all'] = L
            sensorData("ec")

        elif sensor == '超音波':
            tank_sonic = TankSonicSensor.query.filter_by(tank_num=int(tank_num)).filter(TankSonicSensor.datetime.between(date_start,date_end)).all()
            for sonic in tank_sonic:
                l = []
                time = dt.strftime(sonic.datetime, '%Y-%b-%d_%H:%M:%S')
                # time = dt.strftime(tem.datetime, '%Y-%m-%d')
                l.append(time)
                l.append(sonic.sonic)
                L.append(l)

            # print(L)

            D['all'] = L
            sensorData("sonic")





        print(D)
        return json.dumps(D)
    else:
        pass


#貨櫃2制動器的發酵槽訪問路徑
@main.route('/cargo2_actuator_tanks', methods=['GET', 'POST'])
def cargo2_actuator_tanks_views():
    username = session['uname']
    if request.method == 'GET':
        return render_template('cargo2_actuator_tanks.html', params=locals())
    else:
        pass

#貨櫃2制動器發酵槽的清單訪問路徑
@main.route('/cargo2_actuator/<tid>', methods=['GET', 'POST'])
def cargo2_actuator_views(tid):
    username = session['uname']
    if request.method == 'GET':
        return render_template('cargo2_actuator.html', params=locals())
    else:
        pass


#貨櫃3的訪問路徑
@main.route('/cargo3', methods=['GET', 'POST'])
def cargo3_views():
    username = session['uname']
    if request.method == 'GET':
        return render_template('cargo3.html', params=locals())
    else:
        pass

#貨櫃3排程的訪問路徑
@main.route('/cargo3_schedule', methods=['GET', 'POST'])
def cargo3_schedule_views():
    username = session['uname']
    if request.method == 'GET':
        # 將已儲存的排程資料傳給前端
        # 提升機
        try:
            hoist = Hoist.query.order_by(text('datetime desc')).first()
            datetime = hoist.datetime
            hoists = Hoist.query.filter_by(datetime=datetime).all()
        except Exception as e:
            pass


        #烘乾機
        try:
            dryer = Dryer.query.order_by(text('datetime desc')).first()
            datetime = dryer.datetime
            dryers = Dryer.query.filter_by(datetime=datetime).all()

        except Exception as e:
            pass


        return render_template('cargo3_schedule.html', params=locals())
    else:

        current_time = dt.now()

        #循環從前端提交過來的資料
        for i in request.form:
            #堤升機
            if i[:5] == 'hoist':
                if i[:14] == 'hoist_duration':
                    hoist = Hoist()
                    hoist.duration = request.form[i]

                elif i[:13] == 'hoist_from_hr':
                    ho_from_hr = request.form[i]

                elif i[:14] == 'hoist_from_min':
                    ho_from_min = request.form[i]
                    hoist.start = ho_from_hr + ":" + ho_from_min

                elif i[:11] == 'hoist_to_hr':
                    ho_to_hr = request.form[i]

                elif i[:12] == 'hoist_to_min':
                    ho_to_min = request.form[i]
                    hoist.end = ho_to_hr + ":" + ho_to_min
                    hoist.datetime = current_time

                    db.session.add(hoist)
                    db.session.commit()


            # 烘乾機
            elif i[:5] == 'dryer':
                if i[:14] == 'dryer_duration':
                    dryer = Dryer()
                    dryer.duration = request.form[i]

                elif i[:13] == 'dryer_from_hr':
                    dry_from_hr = request.form[i]

                elif i[:14] == 'dryer_from_min':
                    dry_from_min = request.form[i]
                    dryer.start = dry_from_hr + ":" + dry_from_min

                elif i[:11] == 'dryer_to_hr':
                    dry_to_hr = request.form[i]

                elif i[:12] == 'dryer_to_min':
                    dry_to_min = request.form[i]
                    dryer.end = dry_to_hr + ":" + dry_to_min

                    dryer.datetime = current_time

                    db.session.add(dryer)
                    db.session.commit()


        return render_template('cargo3.html', params=locals())



#影像串流的訪問路徑
@main.route('/video', methods=['GET', 'POST'])
def video_views():
    username = session['uname']
    if request.method == 'GET':
        return render_template('video.html', params=locals())
    else:
        pass

#影像的訪問路徑
@main.route('/learn', methods=['GET', 'POST'])
def learn_views():
    username = session['uname']
    if request.method == 'GET':
        return render_template('learn.html', params=locals())
    else:
        pass

#獲取relay狀態路徑
@main.route('/relay', methods=['GET', 'POST'])
def relay_views():
    if request.method == 'GET':
        relay = Relay.query.order_by(text('datetime desc')).first()
        relay_status = relay.status
        return jsonify({"relay":relay_status})
    else:
        pass

#獲取脫皮機狀態路徑
@main.route('/peeling', methods=['GET', 'POST'])
def peeling_views():
    if request.method == 'GET':
        peeling = PeelingMachineRPM.query.order_by(text('datetime desc')).first()
        peeling_rpm = peeling.rpm
        return jsonify({"peeling":peeling_rpm})
    else:
        pass


#退出的訪問路徑
@main.route('/logout')
def logout_views():
    if 'id' in session and 'uname' in session:
        del session['id']
        del session['uname']
    return redirect('/')




@main.route("/udp_client", methods=['POST', 'GET'])
def udp_views():
    # sl(0.5)
    global c_sock
    if request.method == 'GET':
        # def close():
        #     global c_sock
        #     sl(600)
        #     c_sock.close()
        #     c_sock = 0
        #     print("c_sock is closing")

        max_length = 65000
        # max_length = 95000
        # lab1的IP
        host = "192.168.50.65"
        # lab2的IP
        # host = "192.168.51.160"
        port = 8000
        c_sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)


        # t = threading.Thread(target=close)
        # t.daemon = True
        # t.start()

        print("test")
        # dict = request.args.to_dict()
        # nr = int(dict['nr'])
        # if nr == 1:
        #     camara = 0
        # elif nr == 2:
        #     camara = 'rtsp://admin:admin@192.168.50.182/av2_0'
        # elif nr == 3:
        #     camara = 'rtsp://admin:abcd1234@192.168.51.101/av2_0'
        # 地下室小兵
        # cap = cv2.VideoCapture('rtsp://admin:abcd1234@192.168.51.48/av2_0')
        # 主機攝像頭
        # cap = cv2.VideoCapture(0)
        # 外面小兵
        # cap = cv2.VideoCapture('rtsp://admin:admin@192.168.50.182/av2_0')

        cap = cv2.VideoCapture('rtsp://192.168.50.182/av0_0')
        # cap = cv2.VideoCapture(0)
        # cap = cv2.VideoCapture(camara)
        ret, frame = cap.read()

        while ret:
            # compress frame
            frame = cv2.resize(frame, (550, 400), interpolation=cv2.INTER_AREA)
            retval, buffer = cv2.imencode(".jpg", frame)


            if retval:
                # convert to byte array
                buffer = buffer.tobytes()
                # get size of the frame
                buffer_size = len(buffer)

                num_of_packs = 1
                if buffer_size > max_length:
                    num_of_packs = math.ceil(buffer_size / max_length)

                frame_info = {"packs": num_of_packs}

                # send the number of packs to be expected
                # print("Number of packs:", num_of_packs)
                c_sock.sendto(pickle.dumps(frame_info), (host, port))

                left = 0
                right = max_length

                for i in range(num_of_packs):
                    # print("left:", left)
                    # print("right:", right)

                    # truncate data to send
                    data = buffer[left:right]
                    left = right
                    right += max_length

                    # send the frames accordingly
                    c_sock.sendto(data, (host, port))

            ret, frame = cap.read()

        print("done")
        return "done"
    else:
        c_sock.shutdown(2)
        c_sock.close()
        c_sock = 0
        print("c_sock is closing")



# 影像串流的路徑
@main.route("/video_feed", methods=['POST', 'GET'])
def video_feed_views():
    print("test2")
    global s_sock
    if request.method == 'GET':
        #於10分鐘之後，自動關閉socket server
        # if s_sock == 0:
        #     def socket_server_views():
        #         global s_sock
        #         print("test")
        #         print(s_sock)
        #         sl(600)
        #         if s_sock != 0:
        #             s_sock.close()
        #             s_sock = 0
        #             print('s_sock is closed')
        #
        #     pool.submit(socket_server_views)

        host = "192.168.50.65"
        port = 8000
        max_length = 65540
        # max_length = 95540

        s_sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        s_sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
        s_sock.bind((host, port))


        frame_info = None
        buffer = None
        frame = None
        encodedImage = None


        print("-> waiting for connection")


        # 影像生成器函數，將影像以jpg格式傳給前端
        def generate():
            while True:
                with lock:
                    global s_sock, frame_info, buffer, frame, encodedImage
                    if s_sock == 0:
                        break
                    data, address = s_sock.recvfrom(max_length)


                    if len(data) < 100:
                        frame_info = pickle.loads(data)


                        if frame_info:
                            nums_of_packs = frame_info["packs"]


                            for i in range(nums_of_packs):
                                if s_sock == 0:
                                    break
                                data, address = s_sock.recvfrom(max_length)

                                if i == 0:
                                    buffer = data
                                else:
                                    buffer += data


                            frame = np.frombuffer(buffer, dtype=np.uint8)
                            frame = frame.reshape(frame.shape[0], 1)

                            frame = cv2.imdecode(frame, cv2.IMREAD_UNCHANGED)

                            # print(frame)

                            #如果frame為None就跳過
                            if frame is None:
                                continue

                            frame = cv2.resize(frame, (640, 360), interpolation=cv2.INTER_AREA)

                            # encode the frame in JPEG format
                            (flag, encodedImage) = cv2.imencode(".jpg", frame)
                            # ensure the frame was successfully encoded
                            if not flag:
                                continue
                # yield the output frame in the byte format
                yield (b'--frame\r\n' b'Content-Type: image/jpeg\r\n\r\n' +
                    bytearray(encodedImage) + b'\r\n')

        # return the response generated along with the specific media
        # type (mime type)
        return Response(generate(), mimetype="multipart/x-mixed-replace; boundary=frame")
    else:
        if s_sock != 0:
            s_sock.close()
            s_sock = 0
            print('s_sock is closed')
            return "s_sock is closed"
        print("test")
        return "s_sock is closed"

D = {"butterfly_valve_status":0, "stir_status":0, "temp_status":0, "pump_status":0,
     "suction_machine_status":0, "peeling_machine_status":0, "suction_machine_valve_status":0,
     "valve1_status":0, "valve2_status":0, "pump1_status":0, "pump2_status":0, "pump3_status":0,
     "blower_status":0, "heater1_status":0, "heater2_status":0}


pub_topic = 'AISKY/Coffee/MK-G/b8:27:eb:b4:59:3e'
# sub_topic = 'AISKY/Coffee/MK-G/b8:27:eb:b4:59:3e/Log'

# mqttObj = MQTT('aisky-client', 'aiskyc', '60.250.156.234', 1883, 60, sub_topic)
mqttObj = MQTT()


#mqtt發布
@main.route('/mqtt/<tid>', methods=['POST'])
def mqtt_views(tid):
    import json
    dict = request.form.to_dict()
    json = json.dumps(dict)

    # print(json)

    topic = 'AISKY/Coffee/MK-G/b8:27:eb:b4:59:3e'


    # res = mqttObj.mqttPublish(pub_topic, json)
    # print(res)
    # sl(2)
    # print(mqttObj.res)
    #
    # print('test')

    mqtt.publish(topic, json)

    #蝴蝶閥
    if dict['command'] == 'butterfly-valve':
        sl(1)
        # print(D['butterfly_valve_status'])
        if D['butterfly_valve_status'] == 'on':
            return 'on'
        elif D['butterfly_valve_status'] == 'off':
            return 'off'
        else:
            return "Butterfly valve signal was not received"

    #抽水馬達
    elif dict['command'] == 'pump':
        sl(1)
        if D['pump_status'] == 'on':
            return 'on'
        elif D['pump_status'] == 'off':
            return 'off'
        else:
            return "pump signal was not received"

    #真空吸料機
    elif dict['command'] == 'suction-machine':
        sl(1)
        if D['suction_machine_status'] == 'on':
            return 'on'
        elif D['suction_machine_status'] == 'off':
            return 'off'
        else:
            return "suction machine signal was not received"
    
    #真空吸料機閥門
    elif dict['command'] == 'suction-machine-valve':
        sl(1)
        if D['suction_machine_valve_status'] == 'on':
            return 'on'
        elif D['suction_machine_valve_status'] == 'off':
            return 'off'
        else:
            return "suction machine valve signal was not received"
    
    #脫皮機馬達
    elif dict['command'] == 'peeling-machine':
        sl(1)
        if D['peeling_machine_status'] == 'on':
            return 'on'
        elif D['peeling_machine_status'] == 'off':
            return 'off'
        else:
            return "peeling machine signal was not received"
    
    #攪拌機馬達
    elif dict['command'] == 'stir':
        sl(1)
        if D['stir_status'] == 'on':
            return 'on'
        elif D['stir_status'] == 'off':
            return 'off'
        else:
            return "stir signal was not received"
    
    #控制恆溫Relay
    elif dict['command'] == 'temp':
        sl(1)
        # print(D['butterfly_valve_status'])
        if D['temp_status'] == 'on':
            return 'on'
        else:
            return "temp signal was not received"

    #Valve1
    elif dict['command'] == 'valve1':
        sl(1)
        if D['valve1_status'] == 'on':
            return 'on'
        elif D['valve1_status'] == 'off':
            return 'off'
        else:
            return "valve1 signal was not received"

    #Valve2
    elif dict['command'] == 'valve2':
        sl(1)
        if D['valve2_status'] == 'on':
            return 'on'
        elif D['valve2_status'] == 'off':
            return 'off'
        else:
            return "valve2 signal was not received"

    #Pump1
    elif dict['command'] == 'pump1':
        sl(1)
        if D['pump1_status'] == 'on':
            return 'on'
        elif D['pump1_status'] == 'off':
            return 'off'
        else:
            return "Pump1 signal was not received"

    #Pump2
    elif dict['command'] == 'Pump2':
        sl(1)
        if D['pump2_status'] == 'on':
            return 'on'
        elif D['pump2_status'] == 'off':
            return 'off'
        else:
            return "Pump2 signal was not received"

    #Pump3
    elif dict['command'] == 'pump3':
        sl(1)
        if D['pump3_status'] == 'on':
            return 'on'
        elif D['pump3_status'] == 'off':
            return 'off'
        else:
            return "Pump3 signal was not received"

    #Blower
    elif dict['command'] == 'blower':
        sl(1)
        if D['blower_status'] == 'on':
            return 'on'
        elif D['blower_status'] == 'off':
            return 'off'
        else:
            return "Blower signal was not received"

    #Heater1
    elif dict['command'] == 'heater1':
        sl(1)
        if D['heater1_status'] == 'on':
            return 'on'
        elif D['heater1_status'] == 'off':
            return 'off'
        else:
            return "Heater1 signal was not received"

    #Heater2
    elif dict['command'] == 'heater2':
        sl(1)
        if D['heater2_status'] == 'on':
            return 'on'
        elif D['heater2_status'] == 'off':
            return 'off'
        else:
            return "Heater2 signal was not received"

    return "publish done"


#處理mqtt訂閱的信息
@mqtt.on_message()
def handle_mqtt_message(client, userdata, message):
    payload = message.payload.decode()
    payload = json.loads(payload)
    # print("-------msg-------")
    # print('name  :', p['name'])
    # print('email :', p['email'])
    print(payload)
    
    #蝴蝶閥
    if payload['command'] == 'butterfly-valve':
        if payload['response'] == 'on':
            # print('test')
            D['butterfly_valve_status'] = 'on'
        else:
            D['butterfly_valve_status'] = 'off'
    
    #抽水馬達
    elif payload['command'] == 'pump':
        if payload['response'] == 'on':
            # print('test')
            D['pump_status'] = 'on'
        else:
            D['pump_status'] = 'off'
    
    #真空吸料機
    elif payload['command'] == 'suction-machine':
        if payload['response'] == 'on':
            # print('test')
            D['suction_machine_status'] = 'on'
        else:
            D['suction_machine_status'] = 'off'
    
    #真空吸料機閥門
    elif payload['command'] == 'suction-machine-valve':
        if payload['response'] == 'on':
            # print('test')
            D['suction_machine_valve_status'] = 'on'
        else:
            D['suction_machine_valve_status'] = 'off'
    
    #脫皮機馬達
    elif payload['command'] == 'peeling-machine':
        if payload['response'] == 0:
            # print('test')
            D['peeling_machine_status'] = 'off'
        else:
            D['peeling_machine_status'] = 'on'

    #攪拌機馬達
    elif payload['command'] == 'stir':
        if payload['response'] == 0:
            # print('test')
            D['stir_status'] = 'off'
        else:
            D['stir_status'] = 'on'

    #恆溫Relay
    elif payload['command'] == 'air_temp':
        D['temp_status'] = 'on'

    #Valve1
    elif payload['command'] == 'valve1':
        if payload['response'] == 'on':
            # print('test')
            D['valve1_status'] = 'on'
        else:
            D['valve1_status'] = 'off'

    #Valve2
    elif payload['command'] == 'valve2':
        if payload['response'] == 'on':
            # print('test')
            D['valve2_status'] = 'on'
        else:
            D['valve2_status'] = 'off'

    #Pump1
    elif payload['command'] == 'pump1':
        if payload['response'] == 'on':
            # print('test')
            D['pump1_status'] = 'on'
        else:
            D['pump1_status'] = 'off'

    #Pump2
    elif payload['command'] == 'pump2':
        if payload['response'] == 'on':
            # print('test')
            D['pump2_status'] = 'on'
        else:
            D['pump2_status'] = 'off'

    #Pump3
    elif payload['command'] == 'pump3':
        if payload['response'] == 'on':
            # print('test')
            D['pump3_status'] = 'on'
        else:
            D['pump3_status'] = 'off'

    #Blower
    elif payload['command'] == 'blower':
        if payload['response'] == 'on':
            # print('test')
            D['blower_status'] = 'on'
        else:
            D['blower_status'] = 'off'

    #Heater1
    elif payload['command'] == 'heater1':
        if payload['response'] == 'on':
            # print('test')
            D['heater1_status'] = 'on'
        else:
            D['heater1_status'] = 'off'

    #Heater2
    elif payload['command'] == 'heater2':
        if payload['response'] == 'on':
            # print('test')
            D['heater2_status'] = 'on'
        else:
            D['heater2_status'] = 'off'
