import sys
import os
from datetime import datetime

from flask import Blueprint, render_template, redirect, request, Response, send_file, send_from_directory, jsonify, url_for
from flask_login import LoginManager, login_user, logout_user, current_user, login_required
from router.models import User, query_user

import pytz
import serial
import serial.tools.list_ports
import configparser

import zipfile
import re
import json
import time
import csv
import io
import random

import sqlite3
import openpyxl
from openpyxl import load_workbook
# from matplotlib.backends.backend_agg import FigureCanvasAgg as FigureCanvas
# from matplotlib.figure import Figure
from openpyxl import Workbook
from router.broker import Broker
from router.pager import Pagination

HIST_DATA_SAVE_TO_DB = True

# here define a route
module_bp = Blueprint(
    'module',
    '__name__',
)



def slugify(text, lower=1):
    if lower == 1:
        text = text.strip().lower()
    text = re.sub(r'[^\w _-]+', '', text)
    text = re.sub(r'[- ]+', '_', text)
    return text


def save_excel_data_to_sqlitedb(excel_file_path):
    sheet_excel = "hist_data"
    table_db    = 'hist_data'
    # excel_file  = './static/files/history.xlsx'
    excel_file = excel_file_path

    #Replace with a database name
    con = sqlite3.connect('./static/files/test.db')
    #replace with the complete path to youe excel workbook

    cursor = con.cursor()
    cursor.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='hist_data' ''')
    if cursor.fetchone()[0]==1 : 
        print("Table exists, drop it and then create a new table ... ")
        cursor.execute("DROP TABLE hist_data")
        con.commit()
    else :
        print('Table does not exist and create it ...')

    wb = load_workbook(excel_file)
    ws = wb[sheet_excel]

    con.execute('''
        CREATE TABLE hist_data (
            时间  TEXT,
            CH1温度    REAL,
            CH1湿度    REAL,
            CH2温度    REAL,
            CH2湿度    REAL,
            CH3温度    REAL,
            CH3湿度    REAL,
            CH4温度    REAL,
            CH4湿度    REAL                        
        );''')

    tup = []
    for i, rows in enumerate(ws):
        tuprow = []
        # if i == 0:
        #     continue
        for row in rows:
            tuprow.append(row.value.strip()) if row.value.strip() != 'None' else tuprow.append('')
        tup.append(tuple(tuprow))

    # print('--------------------------------------')
    # print(tup)
    con.executemany("INSERT INTO hist_data (时间, CH1温度, CH1湿度, CH2温度, CH2湿度, CH3温度, CH3湿度, CH4温度, CH4湿度) VALUES(?,?,?,?,?,?,?,?,?)",tup)
    con.commit()
    con.close()


##################################################
# SETTINGS
##################################################
@module_bp.route('/')
@module_bp.route('/dashboard')
@login_required
def dashboard():
    print(current_user.get_id())
    return render_template('dashboard.html', title='首页-仪表盘', curr_user=current_user.get_id())


@module_bp.route('/api/dashboard_get_info1', methods=['POST'])
def dashboard_get_info1():
    try:
        b = Broker()
        print("dashboard_get_info1 ...")
        dashboard_get_info1_dict = {}
            
        # 系统时间
        # t1,t2,t3,t4,t5,t6 表示当时间，按年，月，日，周，时，分依次
        data = b.dashboard_info
        data_list = data.split(" ")
        year = int('0x' + data_list[9], 16)
        month = int('0x' + data_list[10], 16)
        day = int('0x' + data_list[11], 16)
        week_str = ['日', '一', '二', '三', '四', '五', '六', '---' ]
        week = int('0x' + data_list[12], 16)

        hour = int('0x' + data_list[13], 16)
        mins = int('0x' + data_list[14], 16)
        dashboard_get_info1_dict.update({"system_time" : "20{:0>2d}年{:0>2d}月{:0>2d}日 星期{} {:0>2d}:{:0>2d}".format(year, month,day, week_str[week], hour, mins) })

        dashboard_get_info1_dict.update({"device_id" : str( int('0x' + data_list[3], 16) ) })

        if int('0x' + data_list[5], 16) == 1:
            run_status = "运行"
        else:
            run_status = "停止"
        dashboard_get_info1_dict.update({"run_status" : run_status })

        dashboard_get_info1_dict.update({"run_time" : str( int('0x' + data_list[6] + data_list[7], 16)) })

        dashboard_get_info1_dict.update({"record_interval" : str( round(int('0x' + data_list[8], 16))) })
        
        #dt_utc = time.localtime()
        dt_utc = datetime.now().astimezone(pytz.timezone('Asia/Shanghai'))
        dashboard_get_info1_dict.update({"update_time" : '更新于: {}-{}-{} {}:{}:{}'.format(dt_utc.year, dt_utc.month, dt_utc.day, dt_utc.hour, dt_utc.minute, dt_utc.second) })
        
        # 01  00 C8 03 26  00 C8  00 0A  03 C0  00 32  01 00 00 00 00 	  第一通道数据
        # 第1字节，表示传感器存在与否（1表示存在，0表示不存在），不存在时，温湿度上传数据为0，在界面上显示为“――――”
        # 第2，3字节为温度数据，高位在前，低位在后。实际显示时应缩小10位，取得一位小数。如本次收到00C8，实际显示为20.0.
        # 第4，5字节为湿度数据，高位在前，低位在后。实际显示时应缩小10位，取得一位小数。如本次收到0326，实际显示为80.6.

        
        # air_protect_status_ 1    ------> temp_status_info_1
        # control_output_status_ 1 ------> humi_status_info_1
        ch = 1
        for idx in (15,33,51,69): #各通道数据起始地址在整个响应数据中的位置
            temp_status_info = ''
            humi_status_info = ''
            if int('0x' + data_list[idx], 16) == 0:
                dashboard_get_info1_dict.update({"temp_" + str(ch) : "----" })
                dashboard_get_info1_dict.update({"hum_" + str(ch) : "----" })
                dashboard_get_info1_dict.update({"control_mode_" + str(ch) : "hide" })
                dashboard_get_info1_dict.update({"control_method_" + str(ch) : "hide" })
                # dashboard_get_info1_dict.update({"air_protect_status_" + str(ch) : "hide" })
                # dashboard_get_info1_dict.update({"control_output_status_" + str(ch) : "hide" })
                dashboard_get_info1_dict.update({"temp_status_info_" + str(ch) : temp_status_info })
                dashboard_get_info1_dict.update({"humi_status_info_" + str(ch) : humi_status_info })
            else:
                dashboard_get_info1_dict.update({"temp_" + str(ch) : str(int('0x' + data_list[idx+1] + data_list[idx+2], 16)/10.0) })
                dashboard_get_info1_dict.update({"hum_" + str(ch) : str(int('0x' + data_list[idx+3] + data_list[idx+4], 16)/10.0) })
                if int('0x' + data_list[idx+13], 16) == 1:
                    dashboard_get_info1_dict.update({"control_mode_" + str(ch) : "循环 <i class='glyphicon glyphicon-refresh'></i>" })
                else:
                    dashboard_get_info1_dict.update({"control_mode_" + str(ch) : "自动 <i class='glyphicon glyphicon-transfer'></i>" })
                
                if int('0x' + data_list[idx+14], 16) == 1:
                    dashboard_get_info1_dict.update({"control_method_" + str(ch) : "节能 <i class='glyphicon glyphicon-leaf'></i>" })
                else:
                    dashboard_get_info1_dict.update({"control_method_" + str(ch) : "hide" })

                if int('0x' + data_list[idx+15], 16) == 0: #00
                    temp_status_info = ''
                elif int('0x' + data_list[idx+15], 16) == 2: #10
                    temp_status_info = "制冷保护 <i class='glyphicon glyphicon-lock'></i>"
                elif int('0x' + data_list[idx+15], 16) == 1: #01
                    temp_status_info = "加热保护 <i class='glyphicon glyphicon-lock'></i>"
                else:
                    temp_status_info = ''

                # bin_str="{:>08}".format(bin(int('0x' + data_list[31], 16))[2:])
                bin_str="{:>08}".format(bin(int('0x' + data_list[idx+16], 16))[2:])
                print('---------------------------------------')
                print(bin_str)
                print('----------------------------------------')
                bin_str_list = list(bin_str)
                # cos_str = ''

                # ['0', '0', '0', '0', '0', '1', '0', '1']
                # 从低到高每个状态为：加热，制冷，化霜，加湿，抽湿.
                if bin_str_list[7] == '1':
                    # cos_str += "加热 "
                    temp_status_info += "加热 <i class='glyphicon glyphicon-fire'></i>"
                if bin_str_list[6] == '1':
                    # cos_str += "制冷 "
                    temp_status_info += "制冷 <i class='glyphicon glyphicon-asterisk'></i>"
                if bin_str_list[5] == '1':
                    # cos_str += "化霜 "
                    temp_status_info += "化霜 "
                if bin_str_list[4] == '1':
                    # cos_str += "加湿 "      
                    humi_status_info += "加湿 <i class='glyphicon glyphicon-tint'></i>"  
                if bin_str_list[3] == '1':
                    # cos_str += "抽湿 " 
                    humi_status_info += "抽湿 <i class='glyphicon glyphicon-log-out'></i>"
                # if cos_str == '':
                #     cos_str = "hide"
                # dashboard_get_info1_dict.update({"control_output_status_" + str(ch) : cos_str })


            dashboard_get_info1_dict.update({"temp_status_info_" + str(ch) : temp_status_info })
            dashboard_get_info1_dict.update({"humi_status_info_" + str(ch) : humi_status_info })

            dashboard_get_info1_dict.update({"target_temp_" + str(ch) : str(int('0x' + data_list[idx+5] + data_list[idx+6], 16)/10.0) })
            dashboard_get_info1_dict.update({"control_temp_" + str(ch) : str(int('0x' + data_list[idx+7] + data_list[idx+8], 16)/10.0) })
            dashboard_get_info1_dict.update({"target_hum_" + str(ch) : str(int('0x' + data_list[idx+9] + data_list[idx+10], 16)/10.0) })
            dashboard_get_info1_dict.update({"control_hum_" + str(ch) : str(int('0x' + data_list[idx+11] + data_list[idx+12], 16)/10.0) })

            ch = ch + 1


        print(dashboard_get_info1_dict)
        return json.dumps(dashboard_get_info1_dict)
    except:
        print("Something wrong, try again ...")
        time.sleep(3)
        dashboard_get_info1()


@module_bp.route('/record_data_setting')
def record_data_setting():
    return render_template('record_data_setting.html', title='数据记录设置', curr_user=current_user.get_id())


@module_bp.route('/api/get_record_data_setting', methods=['POST'])
def get_record_data_setting():
    b = Broker()
    print("get_record_data_setting ...")
    return b.record_data_setting


@module_bp.route('/api/set_record_data_setting', methods=['POST'])
def set_record_data_setting():
    record_interval = int(request.form["record_interval"])
    refresh_interval = int(request.form["refresh_interval"])
    light = int(request.form["light"])
    sleep_time = int(request.form["sleep_time"])
    auto_refresh = int(request.form["auto_refresh"])
    # 数据记录间隔（小时）,数据更新间隔（秒）,背光亮度（%）,休眠时间（分）,自动刷新（分）,01 ??
    b = Broker()
    b.record_data_setting=record_interval,refresh_interval,light,sleep_time,auto_refresh,1
    print("set_record_data_setting ...")
    return "ok"


# 高级设置->仪表控制设置
@module_bp.route('/advance_setting_param')
def advance_setting_param():
    return render_template('advance_setting_param.html', title='高级设置', curr_user=current_user.get_id())


@module_bp.route('/api/get_advance_setting_param', methods=['POST'])
def get_advance_setting_param():
    b = Broker()
    print("get_advance_setting_param ...")
    # 55 12 F1 00 63 03 1F 00 C8 00 13 00 00 00 00 00 00 00 01 AA 
    # print(b.advance_setting_param)
    return b.advance_setting_param


@module_bp.route('/api/set_advance_setting_param', methods=['POST'])
def set_advance_setting_param():
    device_id = int(request.form["device_id"])
    run_time = int(request.form["run_time"])
    water_temp = int( float(request.form["water_temp"])*10 )
    water_temp1 = int( float(request.form["water_temp1"])*10 )
    clock = int(request.form["clock"])
    save_id = int(request.form["save_id"])
    save_point = int(request.form["save_point"])
    b = Broker()
    print(device_id,run_time,water_temp,water_temp1,clock,save_id,save_point)
    b.advance_setting_param=device_id,run_time,water_temp,water_temp1,clock,save_id,save_point,1
    print("set_advance_setting_param ...")
    return "ok"


@module_bp.route('/channel_control_settings')
def channel_control_settings():
    return render_template('channel_control_settings.html', title='控制参数设置', curr_user=current_user.get_id())


@module_bp.route('/api/get_channel_control_settings', methods=['POST'])
def get_channel_control_settings():
    b = Broker()
    print("get_channel_control_settings ...")
    channel_control_settings_dict = {}
    for ch in (0,1,2,3):
        ch_data = b.channel_control_settings(ch)
        ch_data_list = ch_data.split(" ")
        channel_control_settings_dict.update({"target_temp_" + str(ch+1) : int('0x' + ch_data_list[4] + ch_data_list[5], 16)/10})
        channel_control_settings_dict.update({"target_hum_" + str(ch+1) : int('0x' + ch_data_list[6] + ch_data_list[7], 16)/10})
        channel_control_settings_dict.update({"control_temp_" + str(ch+1) : int('0x' + ch_data_list[8] + ch_data_list[9], 16)/10})
        channel_control_settings_dict.update({"control_hum_" + str(ch+1) : int('0x' + ch_data_list[10] + ch_data_list[11], 16)/10})
        channel_control_settings_dict.update({"temp_convert_time_" + str(ch+1) : int('0x' + ch_data_list[12] + ch_data_list[13], 16)})
        channel_control_settings_dict.update({"hum_convert_time_" + str(ch+1) : int('0x' + ch_data_list[14] + ch_data_list[15], 16)})
        channel_control_settings_dict.update({"temp_heat_time_" + str(ch+1) : int('0x' + ch_data_list[16] + ch_data_list[17], 16)})
        channel_control_settings_dict.update({"hum_add_start_time_" + str(ch+1) : int('0x' + ch_data_list[18] + ch_data_list[19], 16)})
        channel_control_settings_dict.update({"temp_frost_time_" + str(ch+1) : int('0x' + ch_data_list[20] + ch_data_list[21], 16)})
        channel_control_settings_dict.update({"hum_add_stop_time_" + str(ch+1) : int('0x' + ch_data_list[22] + ch_data_list[23], 16)})
    print(channel_control_settings_dict)
    return json.dumps(channel_control_settings_dict)


@module_bp.route('/api/get_channel_control_settings_by_ch', methods=['POST'])
def get_channel_control_settings_by_ch():
    b = Broker()
    print("get_channel_control_settings_by_ch ...")
    channel_control_settings_dict = {}
    ch = int(request.form["ch"])
    ch_data = b.channel_control_settings(ch)
    ch_data_list = ch_data.split(" ")
    channel_control_settings_dict.update({"target_temp_" + str(ch+1) : int('0x' + ch_data_list[4] + ch_data_list[5], 16)/10})
    channel_control_settings_dict.update({"target_hum_" + str(ch+1) : int('0x' + ch_data_list[6] + ch_data_list[7], 16)/10})
    channel_control_settings_dict.update({"control_temp_" + str(ch+1) : int('0x' + ch_data_list[8] + ch_data_list[9], 16)/10})
    channel_control_settings_dict.update({"control_hum_" + str(ch+1) : int('0x' + ch_data_list[10] + ch_data_list[11], 16)/10})
    channel_control_settings_dict.update({"temp_convert_time_" + str(ch+1) : int('0x' + ch_data_list[12] + ch_data_list[13], 16)})
    channel_control_settings_dict.update({"hum_convert_time_" + str(ch+1) : int('0x' + ch_data_list[14] + ch_data_list[15], 16)})
    channel_control_settings_dict.update({"temp_heat_time_" + str(ch+1) : int('0x' + ch_data_list[16] + ch_data_list[17], 16)})
    channel_control_settings_dict.update({"hum_add_start_time_" + str(ch+1) : int('0x' + ch_data_list[18] + ch_data_list[19], 16)})
    channel_control_settings_dict.update({"temp_frost_time_" + str(ch+1) : int('0x' + ch_data_list[20] + ch_data_list[21], 16)})
    channel_control_settings_dict.update({"hum_add_stop_time_" + str(ch+1) : int('0x' + ch_data_list[22] + ch_data_list[23], 16)})
    print(channel_control_settings_dict)
    return json.dumps(channel_control_settings_dict)


@module_bp.route('/api/set_channel_control_settings_by_ch', methods=['POST'])
def set_channel_control_settings_by_ch():
    print('set_channel_control_settings_by_ch .')
    channel = request.form["arg0"]
    target_temp = int( float(request.form["arg1"])*10 )
    target_hum = int( float(request.form["arg2"])*10 )
    control_temp = int( float(request.form["arg3"])*10 )
    control_hum = int( float(request.form["arg4"])*10 )
    temp_convert_time = int(request.form["arg5"])
    hum_convert_time = int(request.form["arg6"])
    temp_heat_time = int(request.form["arg7"])
    hum_add_start_time = int(request.form["arg8"])
    temp_frost_time = int(request.form["arg9"])
    hum_add_stop_time = int(request.form["arg10"])    
    b = Broker()
    print('set_channel_control_settings_by_ch params ....')
    print(channel)
    # 2 210 960 10 50 3 3 40 50 3 40
    print(channel,target_temp,target_hum,control_temp,control_hum,temp_convert_time,hum_convert_time,temp_heat_time,hum_add_start_time,temp_frost_time,hum_add_stop_time)
    b.channel_control_settings_by_ch=channel,target_temp,target_hum,control_temp,control_hum,temp_convert_time,hum_convert_time,temp_heat_time,hum_add_start_time,temp_frost_time,hum_add_stop_time,0,0
    print("set_channel_control_settings_by_ch ...")
    return "ok"


@module_bp.route('/current_channel_verify_data')
def current_channel_verify_data():
    return render_template('current_channel_verify_data.html', title='传感器校正', curr_user=current_user.get_id())


@module_bp.route('/api/get_current_channel_verify_data', methods=['POST'])
def get_current_channel_verify_data():
    try:
        b = Broker()
        print("get_current_channel_verify_data ...")
        current_channel_verify_data_dict = {}
        ch_data = b.current_channel_verify_data
        ch_data_list = str(ch_data).split(" ")
        print("ch_data_list...")
        print(ch_data_list)
        # for ch in (1,2,3,4):
        # current_channel_verify_data_dict.update({"temp_offset_mark_" + str(ch) : int('0x' + ch_data_list[3], 16)})
        # current_channel_verify_data_dict.update({"temp_offset_" + str(ch) : int('0x' + ch_data_list[4] + ch_data_list[5], 16)/10})
        # current_channel_verify_data_dict.update({"hum_offset_mark_" + str(ch) : int('0x' + ch_data_list[6], 16)})
        # current_channel_verify_data_dict.update({"hum_offset_" + str(ch) : int('0x' + ch_data_list[7] + ch_data_list[8], 16)/10})

        current_channel_verify_data_dict.update({"temp_offset_mark_1"  : int('0x' + ch_data_list[3], 16)})
        current_channel_verify_data_dict.update({"temp_offset_1" : int('0x' + ch_data_list[4] + ch_data_list[5], 16)/10})
        current_channel_verify_data_dict.update({"hum_offset_mark_1" : int('0x' + ch_data_list[6], 16)})
        current_channel_verify_data_dict.update({"hum_offset_1" : int('0x' + ch_data_list[7] + ch_data_list[8], 16)/10})

        current_channel_verify_data_dict.update({"temp_offset_mark_2" : int('0x' + ch_data_list[9], 16)})
        current_channel_verify_data_dict.update({"temp_offset_2" : int('0x' + ch_data_list[10] + ch_data_list[11], 16)/10})
        current_channel_verify_data_dict.update({"hum_offset_mark_2" : int('0x' + ch_data_list[12], 16)})
        current_channel_verify_data_dict.update({"hum_offset_2" : int('0x' + ch_data_list[13] + ch_data_list[14], 16)/10})

        current_channel_verify_data_dict.update({"temp_offset_mark_3" : int('0x' + ch_data_list[15], 16)})
        current_channel_verify_data_dict.update({"temp_offset_3" : int('0x' + ch_data_list[16] + ch_data_list[17], 16)/10})
        current_channel_verify_data_dict.update({"hum_offset_mark_3" : int('0x' + ch_data_list[18], 16)})
        current_channel_verify_data_dict.update({"hum_offset_3" : int('0x' + ch_data_list[19] + ch_data_list[20], 16)/10})

        current_channel_verify_data_dict.update({"temp_offset_mark_4" : int('0x' + ch_data_list[21], 16)})
        current_channel_verify_data_dict.update({"temp_offset_4" : int('0x' + ch_data_list[22] + ch_data_list[23], 16)/10})
        current_channel_verify_data_dict.update({"hum_offset_mark_4" : int('0x' + ch_data_list[24], 16)})
        current_channel_verify_data_dict.update({"hum_offset_4" : int('0x' + ch_data_list[25] + ch_data_list[26], 16)/10})

        # here get verified temp and humi
        data = b.dashboard_info
        data_list = data.split(" ")
        ch = 1
        for idx in (15,33,51,69): #各通道数据起始地址在整个响应数据中的位置
            if int('0x' + data_list[idx], 16) == 0:
                current_channel_verify_data_dict.update({"verified_temp_" + str(ch) : "----" })
                current_channel_verify_data_dict.update({"verified_humi_" + str(ch) : "----" })
            else:
                current_channel_verify_data_dict.update({"verified_temp_" + str(ch) : str(int('0x' + data_list[idx+1] + data_list[idx+2], 16)/10.0) })
                current_channel_verify_data_dict.update({"verified_humi_" + str(ch) : str(int('0x' + data_list[idx+3] + data_list[idx+4], 16)/10.0) })
            ch = ch + 1

        print(current_channel_verify_data_dict)
        return json.dumps(current_channel_verify_data_dict)
    except:
        print("Something wrong, try again ...")
        time.sleep(1.5)
        return get_current_channel_verify_data()



@module_bp.route('/api/set_current_channel_verify_data', methods=['POST'])
def set_current_channel_verify_data():
    print('set_current_channel_verify_data .')
    arg1 = int( request.form["arg1"] )
    arg2 = int( float(request.form["arg2"])*10 )
    arg3 = int( request.form["arg3"] )
    arg4 = int( float(request.form["arg4"])*10 )
    arg5 = int( request.form["arg5"] )
    arg6 = int( float(request.form["arg6"])*10 )
    arg7 = int( request.form["arg7"] )
    arg8 = int( float(request.form["arg8"])*10 )
    arg9 = int( request.form["arg9"] )
    arg10 = int( float(request.form["arg10"])*10 )
    arg11 = int( request.form["arg11"] )
    arg12 = int( float(request.form["arg12"])*10 )
    arg13 = int( request.form["arg13"] )
    arg14 = int( float(request.form["arg14"])*10 )
    arg15 = int( request.form["arg15"] )
    arg16 = int( float(request.form["arg16"])*10 )        
    b = Broker()
    print('set_current_channel_verify_data params ....')
    print(arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8,arg9,arg10,arg11,arg12,arg13,arg14,arg15,arg16)
    b.current_channel_verify_data=arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8,arg9,arg10,arg11,arg12,arg13,arg14,arg15,arg16
    print("set_current_channel_verify_data ...")
    return "ok"


@module_bp.route('/humidifier_energy_saving_settings_param')
def humidifier_energy_saving_settings_param():
    return render_template('humidifier_energy_saving_settings_param.html', title='加湿器控制设置', curr_user=current_user.get_id())


@module_bp.route('/api/get_humidifier_energy_saving_settings_param', methods=['POST'])
def get_humidifier_energy_saving_settings_param():
    b = Broker()
    print("get_humidifier_energy_saving_settings_param ...")
    humidifier_energy_saving_settings_param_dict = {}
    ch_data = b.humidifier_energy_saving_settings_param
    ch_data_list = ch_data.split(" ")
    # DATA: 55 0C F0 01 01 14 17 01 01 01 01 01 01 AA
    humidifier_energy_saving_settings_param_dict.update({"loop_mode" : int('0x' + ch_data_list[3], 16)})
    humidifier_energy_saving_settings_param_dict.update({"save_mode" : int('0x' + ch_data_list[4], 16)})
    humidifier_energy_saving_settings_param_dict.update({"save_start_time" : int('0x' + ch_data_list[5], 16)})
    humidifier_energy_saving_settings_param_dict.update({"save_end_time" : int('0x' + ch_data_list[6], 16)})
    humidifier_energy_saving_settings_param_dict.update({"ch1_save" : int('0x' + ch_data_list[7], 16)})
    humidifier_energy_saving_settings_param_dict.update({"ch2_save" : int('0x' + ch_data_list[8], 16)})
    humidifier_energy_saving_settings_param_dict.update({"ch3_save" : int('0x' + ch_data_list[9], 16)})
    humidifier_energy_saving_settings_param_dict.update({"ch4_save" : int('0x' + ch_data_list[10], 16)})
    print(humidifier_energy_saving_settings_param_dict)
    return json.dumps(humidifier_energy_saving_settings_param_dict)



@module_bp.route('/api/set_humidifier_energy_saving_settings_param', methods=['POST'])
def set_humidifier_energy_saving_settings_param():
    print('set_humidifier_energy_saving_settings_param .')
    arg1 = int( request.form["arg1"] )
    arg2 = int( request.form["arg2"] )
    arg3 = int( request.form["arg3"] )
    arg4 = int( request.form["arg4"] )
    arg5 = int( request.form["arg5"] )
    arg6 = int( request.form["arg6"] )
    arg7 = int( request.form["arg7"] )
    arg8 = int( request.form["arg8"] )
    b = Broker()
    print('set_humidifier_energy_saving_settings_param params ....')
    print(arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8)
    b.humidifier_energy_saving_settings_param=arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8
    print("set_humidifier_energy_saving_settings_param ...")
    return "ok"



@module_bp.route('/history_data')
def history_data():
    if HIST_DATA_SAVE_TO_DB:
        # use db data and pager 
        conn = sqlite3.connect('./static/files/test.db')
        cur = conn.cursor()
        print("Opened database successfully...")
        cur.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='hist_data' ''')
        if cur.fetchone()[0]==1 : 
            print("Table exists and get data ...")
            cur.execute("SELECT * from hist_data")
            all_data = cur.fetchall()
        else :
            print('Table does not exist, data is null ...')
            all_data = []

        conn.close()

        pager_obj = Pagination(request.args.get("page",1),len(all_data),request.path,request.args,per_page_count=15)
        # print(request.args)
        index_list = all_data[pager_obj.start:pager_obj.end]
        html = pager_obj.page_html()
        return render_template("send_all_area_data.html",data=index_list, html = html,condition=request.path, title='历史数据查询', curr_user=current_user.get_id())
    else:
        # 时间    CH1温度   CH1湿度   CH2温度   CH2湿度   CH3温度   CH3湿度   CH4温度   CH4湿度
        # 2021-9-3 21:00    31.7    67.7    30.2    70.8    29.7    74.2    30.9    73
        all_data = []
        try:
            f = open('./static/files/history.csv','r')
            csv_reader = csv.reader(f)
            all_data = list(csv_reader)
            # print(all_data)
            return render_template('send_all_area_data.html', data=all_data, html = '', title='历史数据查询', curr_user=current_user.get_id())   
        except:
            print('csv_reader bad ...')
            return render_template('send_all_area_data.html', data=all_data, html = '', title='历史数据查询', curr_user=current_user.get_id())   



@module_bp.route('/send_all_area_data')
def send_all_area_data():
    try:
        b = Broker()
        print('send_all_area_data .')
        ch_data = b.send_all_area_data

        ch_data_list = ch_data.split(" ")

        print("---------------------------------DATA_LIST BEGIN-------------------------------")
        print(ch_data_list)
        print("---------------------------------DATA_LIST END-------------------------------")

        # DATA: 55 0C F0 01 01 14 17 01 01 01 01 01 01 AA
        ch_data_list1 = ch_data_list[6:len(ch_data_list)-2]

        ch_data_list2 = []

        for i in range(0, len(ch_data_list1), 21):
            k = ch_data_list1[i:i+21]
            ch_data_list2.append(k)
        
        f = open('./static/files/history.csv','w',newline='')
        wb = Workbook()
        my_sheet = wb.create_sheet('hist_data')
        my_sheet.append( ["时间", "CH1温度", "CH1湿度", "CH2温度", "CH2湿度", "CH3温度", "CH3湿度", "CH4温度", "CH4湿度"] )

        csv_write = csv.writer(f,dialect='excel')
        csv_write.writerow(("时间", "CH1温度", "CH1湿度", "CH2温度", "CH2湿度", "CH3温度", "CH3湿度", "CH4温度", "CH4湿度"))

        all_data = []
        for v in ch_data_list2:
            one_data = []
            one_data.append( '20'+str(int('0x' + v[20], 16)) + "/" + str(int('0x' + v[19], 16)).rjust(2,'0') + "/" + str(int('0x' + v[18], 16)).rjust(2,'0') + " " + str(int('0x' + v[17], 16)).rjust(2,'0') + ":" + str(int('0x' + v[16], 16)).rjust(2,'0') )
            
            for idx in( 12, 14, 4, 6, 8, 10, 0, 2 ):
                if str(int('0x' + v[idx] + v[idx+1], 16)/10) == '0.0':
                    cd = '-'
                else:
                    cd = str(int('0x' + v[idx] + v[idx+1], 16)/10)
                one_data.append(cd)

            all_data.append(one_data)
            # write a row to csv file ---------------------------------
            csv_write.writerow(one_data)

            # write a row to excel file ---------------------------------
            my_sheet.append(one_data)

        f.close()
        wb.save('./static/files/history.xlsx')

        if HIST_DATA_SAVE_TO_DB:
            # here save data to sqlite db
            save_excel_data_to_sqlitedb(excel_file_path='./static/files/history.xlsx')

        print('--------------------------')
        print(all_data)
        print('--------------------------')

        # return render_template('send_all_area_data.html', all_data=[], title='历史数据查询', curr_user=current_user.get_id())   
        return redirect(url_for('module.history_data'))
         
    except Exception as e:
        print("try again ...")
        print(str(e))
        time.sleep(2)
        # return render_template('hint.html', title='温馨提示', curr_user=current_user.get_id())    
        return send_all_area_data()


@module_bp.route('/air_conditioning_settings_param')
def air_conditioning_settings_param():
    return render_template('air_conditioning_settings_param.html', title='空调保护设置', curr_user=current_user.get_id())


@module_bp.route('/api/get_air_conditioning_settings_param', methods=['POST'])
def get_air_conditioning_settings_param():
    b = Broker()
    print("get_air_conditioning_settings_param ...")
    air_conditioning_settings_param_dict = {}
    data = b.air_conditioning_settings_param
    data_list = data.split(" ")
    # DATA: 55 22 EF 
    # 00 00 00 01 00 00 00 00 03 E6 00 00 03 E6 03 E6 00 00 00 C8 03 E6 00 27 00 78 00 1E 00 78 00 1E AA
    # 参数1:制冷保护(取值0或1)
    # 参数2: 加热保护(取值0或1)
    # 参数3,制冷模式H1(取值0到999)
    # 参数4, 加热模式H1 (取值0到999)
    # 参数5: 制冷模式H2(取值0到999)
    # 参数6: 加热模式H2 (取值0到999)
    # 参数7: 制冷模式T1(取值0到999)
    # 参数8: 加热模式T1 (取值0到999)
    # 参数9: 制冷模式T2(取值0到999)
    # 参数10: 加热模式T2 (取值0到999)
    # 参数11: 抽湿时间(取值0到999)
    # 参数12: 抽湿停止(取值0到999)
    # 参数13: 保留(无实际意义)
    # 参数14: 保留(无实际意义)
    # 参数15: 保留(无实际意义)
    # 参数16: 保留(无实际意义)

    air_conditioning_settings_param_dict.update({"cold_protect" : int('0x' + data_list[3+0] + data_list[3+1], 16)})
    air_conditioning_settings_param_dict.update({"heat_protect" : int('0x' + data_list[3+2] + data_list[3+3], 16)})
    air_conditioning_settings_param_dict.update({"cold_mode_h1" : int('0x' + data_list[3+4] + data_list[3+5], 16)})
    air_conditioning_settings_param_dict.update({"heat_mode_h1" : int('0x' + data_list[3+6] + data_list[3+7], 16)})
    air_conditioning_settings_param_dict.update({"cold_mode_h2" : int('0x' + data_list[3+8] + data_list[3+9], 16)})
    air_conditioning_settings_param_dict.update({"heat_mode_h2" : int('0x' + data_list[3+10] + data_list[3+11], 16)})
    air_conditioning_settings_param_dict.update({"cold_mode_t1" : int('0x' + data_list[3+12] + data_list[3+13], 16)/10 })
    air_conditioning_settings_param_dict.update({"heat_mode_t1" : int('0x' + data_list[3+14] + data_list[3+15], 16)/10 })
    air_conditioning_settings_param_dict.update({"cold_mode_t2" : int('0x' + data_list[3+16] + data_list[3+17], 16)/10 })
    air_conditioning_settings_param_dict.update({"heat_mode_t2" : int('0x' + data_list[3+18] + data_list[3+19], 16)/10 })
    air_conditioning_settings_param_dict.update({"rid_hum_time" : int('0x' + data_list[3+20] + data_list[3+21], 16)})
    air_conditioning_settings_param_dict.update({"rid_hum_stop" : int('0x' + data_list[3+22] + data_list[3+23], 16)})
    print(air_conditioning_settings_param_dict)
    return json.dumps(air_conditioning_settings_param_dict)


@module_bp.route('/api/set_air_conditioning_settings_param', methods=['POST'])
def set_air_conditioning_settings_param():
    print('set_air_conditioning_settings_param .')
    arg1 = int( request.form["arg1"] )
    arg2 = int( request.form["arg2"] )
    arg3 = int( request.form["arg3"] )
    arg4 = int( request.form["arg4"] )
    arg5 = int( request.form["arg5"] )
    arg6 = int( request.form["arg6"] )
    arg7 = int( float(request.form["arg7"])*10 )
    arg8 = int( float(request.form["arg8"])*10 )
    arg9 = int( float(request.form["arg9"])*10 )
    arg10 = int( float(request.form["arg10"])*10 )
    arg11 = int( request.form["arg11"] )
    arg12 = int( request.form["arg12"] )
    b = Broker()
    print('set_air_conditioning_settings_param params ....')
    print(arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8,arg9,arg10,arg11,arg12)
    b.air_conditioning_settings_param=arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8,arg9,arg10,arg11,arg12
    print("set_air_conditioning_settings_param ...")
    return "ok"




@module_bp.route('/history_chart')
def history_chart():
    return render_template('histry_chart.html', title='历史数据图表', curr_user=current_user.get_id())

@module_bp.route('/plot1.png')
def plot1_png():
    fig = create_figure1()
    output = io.BytesIO()
    FigureCanvas(fig).print_png(output)
    return Response(output.getvalue(), mimetype='image/png')

def create_figure1():
    count = 1
    ch1_time_list = []
    ch1_temp_list = []
    ch1_hum_list = []
    with open('./static/files/history.csv', 'r') as f:
        reader = csv.reader(f)
        for item in reader:
            print(item)
            if count > 30:
                break
            if item[1] == '-' or 'CH' in item[1]:
                continue
            ch1_time_list.append(item[0])
            #CH1 TEMP
            ch1_temp_list.append(item[1])
            #CH1 HUM
            ch1_hum_list.append(item[2])
            count = count + 1
    f.close()

    fig = Figure(figsize=(15, 4))
    # fig = Figure()
    fig.subplots_adjust(wspace = 0.3, hspace = 8)
    #CH1 TEMP

    axis_ch1_temp = fig.add_subplot(1, 2, 1)
    axis_ch1_temp.set_xticklabels(ch1_time_list, rotation='vertical', ha='center')
    axis_ch1_temp.plot(ch1_time_list, ch1_temp_list, label='CH1 Temperature')
    axis_ch1_temp.legend()

    #CH1 HUM
    axis_ch1_hum = fig.add_subplot(1, 2, 2)
    axis_ch1_hum.set_xticklabels(ch1_time_list, rotation='vertical', ha='center')
    axis_ch1_hum.plot(ch1_time_list, ch1_hum_list, label='CH1 Humidity')
    axis_ch1_hum.legend()
    return fig


@module_bp.route('/setting')
def setting():
    return render_template('setting.html', title='系统设置', curr_user=current_user.get_id())


cf = configparser.ConfigParser()
cf.read("Config.ini")
SER_PORT = cf.get("dev", "port")
# SER_BAUDRATE = cf.getint("device", "baudrate")
# GUI_VER = cf.get("GUI Info","Version")

@module_bp.route('/api/get_device_settings', methods=['POST'])
def get_device_settings():
    print('get_device_settings ...')
    data = {}
    portname_str = ""
    for n, (portname, desc, hwid) in enumerate(sorted(serial.tools.list_ports.comports())):
        if portname == SER_PORT:
            portname_str = portname_str + "<option selected>" + portname + "</option>"
        else:
            portname_str = portname_str + "<option>" + portname + "</option>"

    data = {"port": portname_str}
    # "baudrate": str(SER_BAUDRATE)
    return json.dumps(data)


@module_bp.route('/api/set_device_settings', methods=['POST'])
def set_device_settings():
    print('set_device_settings ...')
    device_port_val = request.form["device_port_val"]
    cf.set("dev", "port", device_port_val)

    with open("Config.ini", "w+") as f:
	    cf.write(f)
	
    return "Set successfully."


@module_bp.route('/api/set_software_update', methods=['POST'])
def set_software_update():
    print('set_software_update ...')
    b = Broker()
    b.rp_box_software_update()
    return "software_update: Set successfully."


@module_bp.route('/api/set_software_reboot', methods=['POST'])
def set_software_reboot():
    print('set_software_reboot ...')
    b = Broker()
    b.rp_box_software_reboot()
    return "oftware_reboot: Set successfully."


@module_bp.route('/api/get_temp_hum_chart_data_by_ch', methods=['POST'])
def get_temp_hum_chart_data_by_ch():
    count = 1
    ch1_time_list = []
    ch1_temp_list = []
    ch1_hum_list = []
    ch2_temp_list = []
    ch2_hum_list = []
    ch3_temp_list = []
    ch3_hum_list = []
    ch4_temp_list = []
    ch4_hum_list = []
    with open('./static/files/history.csv', 'r') as f:
        reader = csv.reader(f)
        for item in reader:
            print(item)
            if count > 30:
                break
            # if item[1] == '-' or 'CH' in item[1] or 'ch' in item[1]:
            #     continue
            # skip invalid rows
            if '/' not in item[0]:
                continue
            if ':' not in item[0]:
                continue
            ch1_time_list.append(item[0])
            #CH1 TEMP and HUM
            ch1_temp_list.append(item[1])
            ch1_hum_list.append(item[2])
            #CH2 TEMP and HUM
            ch2_temp_list.append(item[3])
            ch2_hum_list.append(item[4])
            #CH3 TEMP and HUM
            ch3_temp_list.append(item[5])
            ch3_hum_list.append(item[6])
            #CH4 TEMP and HUM
            ch4_temp_list.append(item[7])
            ch4_hum_list.append(item[8])

            count = count + 1
    f.close()

    ch1_time_list.reverse()
    ch1_temp_list.reverse()
    ch1_hum_list.reverse()
    return json.dumps([ch1_time_list,ch1_temp_list,ch1_hum_list,ch2_temp_list,ch2_hum_list,ch3_temp_list,ch3_hum_list,ch4_temp_list,ch4_hum_list])


@module_bp.route('/api/set_sys_datetime', methods=['POST'])
def set_sys_datetime():
    b = Broker()
    print('set_sys_datetime ...')
    datetime_val = request.form["datetime_val"]
    # 2020-10-06 21:50
    d = datetime_val.split(" ")

    year = int(d[0].split("-")[0][2:4])
    month = int(d[0].split("-")[1])
    day = int(d[0].split("-")[2])
    hour = int(d[1].split(":")[0])
    min = int(d[1].split(":")[1])
    second = int(0)

    print(year,month,day,0,hour,min,second)
    b.datetime_setting=year,month,day,0,hour,min,second
    return "set_sys_datetime: Set successfully."


@module_bp.route('/debug_command')
def debug_command():
    return render_template('debug_command.html', title='工程调试命令', curr_user=current_user.get_id())


@module_bp.route('/api/my_sleep', methods=['POST'])
def my_sleep():
    sleep_t = int(request.form["sleep_t"])
    time.sleep(sleep_t)
    return "my_sleep ok." + str(sleep_t)


@module_bp.route('/api/inst_pause', methods=['POST'])
def inst_pause():
    b = Broker()
    print('inst_pause ...')
    b.inst_pause
    return "inst_pause: Set successfully."


@module_bp.route('/api/inst_reboot', methods=['POST'])
def inst_reboot():
    b = Broker()
    print('inst_reboot ...')
    b.inst_reboot
    return "inst_reboot: Set successfully."


@module_bp.route('/api/clean_all_args', methods=['POST'])
def clean_all_args():
    b = Broker()
    print('clean_all_args ...')
    b.remove_all_param
    return "clean_all_args: Set successfully."

@module_bp.route('/api/clean_all_recorders', methods=['POST'])
def clean_all_recorders():
    b = Broker()
    print('clean_all_recorders ...')
    b.remove_all_records
    return "clean_all_recorders: Set successfully."


@module_bp.route('/api/clean_runtime', methods=['POST'])
def clean_runtime():
    b = Broker()
    print('clean_runtime ...')
    b.remove_run_time
    return "clean_runtime: Set successfully."


@module_bp.route('/debug_console')
def debug_console():
    return render_template('debug_console.html', title='命令控制台', curr_user=current_user.get_id())


@module_bp.route('/api/set_debug_cmdstr', methods=['POST'])
def set_debug_cmdstr():
    b = Broker()
    print('set_debug_cmdstr ...')
    cmd_str = str(request.form["cmd_str"]).replace(" ", "")
    ret = b.send_debug_cmdstr(cmd_str)
    return str(ret)



@module_bp.route('/api/rp_box_reboot', methods=['POST'])
def rp_box_reboot():
    b = Broker()
    print('rp_box_reboot ...')
    b.rp_box_reboot()
    return "rp_box_reboot: Set successfully."


###################################################################
# 空调红外指令调试
###################################################################
@module_bp.route('/api/rd_group_set', methods=['POST'])
def rd_group_set():
    device_port_val = request.form["device_port_val"]
    rd_group = int(request.form["rd_group"]) - 1

    print('device_port_val: ', device_port_val)
    print('rd_group_set: ', rd_group+1)

    # 01 06 00 01 00 01 19 CA//发送第1组
    # 01 06 00 01 00 02 59 CB//发送第2组
    # 01 06 00 01 00 03 98 0B//发送第3组
    # 01 06 00 01 00 04 D9 C9//发送第4组
    cmds = [
                [0x01, 0x06, 0x00, 0x01, 0x00, 0x01, 0x19, 0xCA],
                [0x01, 0x06, 0x00, 0x01, 0x00, 0x02, 0x59, 0xCB],
                [0x01, 0x06, 0x00, 0x01, 0x00, 0x03, 0x98, 0x0B],
                [0x01, 0x06, 0x00, 0x01, 0x00, 0x04, 0xD9, 0xC9]
            ]

    ser=serial.Serial(port=device_port_val, baudrate='9600')

    print(ser.port)
    print(ser.baudrate)

    cmd_tmp = []
    for i in cmds[rd_group]:
        cmd_tmp.append(hex(i))
    print('SEND: {}'.format(cmd_tmp))
    time.sleep(1)

    ser.write(cmds[rd_group])

    time.sleep(1)
    buffer_string = b''
    while True:
        buffer_string = buffer_string + ser.read(ser.inWaiting())
        if ser.read(ser.inWaiting()) == b'':
            break 
    ser.close()

    data = ''.join(['%02X ' %x  for x in buffer_string])
    print("RECV: {}".format(data))

    return "rd_group_set: Set successfully."



@module_bp.route('/api/rd_group_test_loop', methods=['POST'])
def rd_group_test_loop():
    device_port_val = request.form["device_port_val"]
    rd_group = int(request.form["rd_group"]) - 1

    print('device_port_val: ', device_port_val)
    print('rd_group_set: ', rd_group+1)
    cmds = [
                [0x01, 0x06, 0x00, 0x01, 0x00, 0x01, 0x19, 0xCA],
                [0x01, 0x06, 0x00, 0x01, 0x00, 0x02, 0x59, 0xCB],
                [0x01, 0x06, 0x00, 0x01, 0x00, 0x03, 0x98, 0x0B],
                [0x01, 0x06, 0x00, 0x01, 0x00, 0x04, 0xD9, 0xC9]
            ]

    ser=serial.Serial(port=device_port_val, baudrate='9600')

    print(ser.port)
    print(ser.baudrate)

    cmd_tmp = []
    for i in cmds[rd_group]:
        cmd_tmp.append(hex(i))
    print('SEND: {}'.format(cmd_tmp))
    time.sleep(1)

    ser.write(cmds[rd_group])

    time.sleep(1)
    buffer_string = b''
    while True:
        buffer_string = buffer_string + ser.read(ser.inWaiting())
        if ser.read(ser.inWaiting()) == b'':
            break 
    ser.close()

    data = ''.join(['%02X ' %x  for x in buffer_string])
    print("RECV: {}".format(data))
    return "rd_group_set: Set successfully."



@module_bp.route('/m/demo')
def demo():
    return render_template('m/demo.html', title='demo')