from flask import jsonify,render_template
from app.api import bp
from config import Config
from pyecharts.charts import Bar,Line
from pyecharts.options.global_options import ThemeType
import pyecharts.options as opts
import pymysql
import pymongo
import pandas as pd
import pytz
import time
import json
from datetime import datetime,timedelta
import urllib.request
import operator
import re


def iso2timestamp(datestamp, format='%Y-%m-%d %H:%M:%S',timespec='seconds'):
    """
    ISO8601时间转换为时间戳

    :param datestring:iso时间字符串 2019-03-25T16:00:00.000Z，2019-03-25T16:00:00.000111Z
    :param format:%Y-%m-%dT%H:%M:%S.%fZ；其中%f 表示毫秒或者微秒
    :param timespec:返回时间戳最小单位 seconds 秒，milliseconds 毫秒,microseconds 微秒
    :return:时间戳 默认单位秒
    """
    datestring = datestamp.strftime("%Y-%m-%d %H:%M:%S")
    tz = pytz.timezone('Asia/Shanghai')
    utc_time = datetime.strptime(datestring, format)  # 将字符串读取为 时间 class datetime.datetime
    t = utc_time.replace(tzinfo=pytz.utc).astimezone(tz)
    last_time=t.strftime("%Y-%m-%d %H:%M:%S")
    # print (last_time)
    return last_time

def local2utc(local_st):
    """本地时间转UTC时间（-8: 00）"""
    time_struct = time.mktime(local_st.timetuple())
    utc_st = datetime.utcfromtimestamp(time_struct)
    return utc_st

# def get_data():
#     # Get the top10 products
#     conn = pymysql.connect(host="127.0.0.1", user="root", password="", db="test", charset="utf8")
#     cur = conn.cursor()
#     sql = '''
#         SELECT
#             DATE(更新时间) AS date,
#             COUNT(*) AS num
#         FROM
#             erp_source1_v1
#         WHERE
#             `类型` = '入库'
#         AND DATE(更新时间) > '2020-08-21'
#         GROUP BY
#             date
#         ORDER BY
#             date;
#         '''
#     cur.execute(sql)
#     see = cur.fetchall()
#     date_list = []
#     count_list = []
#     jsonData = {}
#     for data in see:
#         # legname.append(data[0])
#         date_list.append(data[0])  # 获取日期数据
#         count_list.append(data[1])  # 获取count数据
#     jsonData['date'] = date_list
#     jsonData['count'] = count_list
#
#     return (date_list,count_list)

def conn_mongo_datalog(start,end):
    mongo_client = pymongo.MongoClient(host='localhost', port=27017)
    # current_dbname=Config.DB_NAME
    # db = mongo_client[current_dbname]
    db = mongo_client['qisda2']
    cach = db.dataLog
    start_date = local2utc(start)
    end_date = local2utc(end)
    # start_date = datetime(2020,8,25,16,00,00)
    # end_date = datetime(2020,8,26,16,00,00)
    data=list(cach.find({"updateDate": {"$gte": start_date, "$lt": end_date}}))
    mongo_client.close()
    df = pd.DataFrame(data)
    return (df)

def process_datalog_hour():
    now = datetime.now()
    start=datetime.now() - timedelta(hours=24)
    start = datetime(start.year, start.month, start.day, start.hour, start.minute, start.second)
    end = datetime(now.year, now.month, now.day, now.hour, now.minute, now.second)
    print("*********************datalog**24小时数据准备中*******************")
    # start = datetime(2020,9,7,16,00,00)
    # end = datetime(2020,9,8,16,00,00)
    df = conn_mongo_datalog(start,end)
    if not df.empty:
        df['updateDate'] = df['updateDate'].apply(iso2timestamp)
        temp_df_in = df[(df['type'] == 1) & (df['status'] != 'CANCEL') & (df['status'] != 'WAIT') & (df['posName'] !="")]
        df_in = temp_df_in[['updateDate', 'status']]
        datalog_hour_in = df_in['updateDate'].groupby(df_in['updateDate'].map(lambda x: x[5:13])).count()
        hour_list_in = datalog_hour_in.index.to_list()
        count_list_in = datalog_hour_in.values.tolist()
        hour_list_in,count_list_in = check_hour_zero(start,now,hour_list_in,count_list_in)
        # check_hour_zero(start, now, hour_list_in, count_list_in)
        temp_df_out = df[(df['type'] == 2) & (df['status'] != 'CANCEL') & (df['status'] != 'WAIT') & (df['posName'] !="")]
        df_out = temp_df_out[['updateDate', 'status']]
        datalog_hour_out = df_out['updateDate'].groupby(df_out['updateDate'].map(lambda x: x[5:13])).count()
        hour_list_out = datalog_hour_out.index.to_list()
        count_list_out = datalog_hour_out.values.tolist()
        hour_list_out, count_list_out = check_hour_zero(start, now, hour_list_out, count_list_out)
    else:
        hour_list_in = []
        count_list_in = []
        hour_list_out = []
        count_list_out = []
        hour_list_in,count_list_in = check_hour_zero(start,now,hour_list_in,count_list_in)
        hour_list_out,count_list_out = check_hour_zero(start,now,hour_list_out,count_list_out)
    data_dict = {
                'hour_list_in':hour_list_in,
                'count_list_in':count_list_in,
                'hour_list_out':hour_list_out,
                'count_list_out':count_list_out
    }
    return data_dict

def get_days_before_today(n=0):
    '''''
    date format = "YYYY-MM-DD HH:MM:SS"
    '''
    now = datetime.now()
    if(n<0):
        return datetime(now.year, now.month, now.day, now.hour, now.minute, now.second)
    else:
        n_days_before = now - timedelta(days=n)
        return datetime(n_days_before.year, n_days_before.month, n_days_before.day)

def process_datalog_day(n=5):#默认取前10天（含当天）
    now = datetime.now()
    start = get_days_before_today(n)
    end = now + timedelta(days=1)
    end = datetime(end.year, end.month, end.day, 23, 59, 59)
    # start = datetime(2020,9,1)
    # end = datetime(2020,9,8)
    print ("*********************datalog**7天数据准备中*******************")
    df = conn_mongo_datalog(start,end)
    if not df.empty:
        df['updateDate'] = df['updateDate'].apply(iso2timestamp)
        # df.to_csv('./7day.csv')
        # temp_df_in = df[(df['type'] == 1)&(df['status' !='CANCEL'])]
        temp_df_in=df[(df['type'] == 1) & (df['status'] != 'CANCEL') & (df['status'] != 'WAIT') & (df['posName'] !="")]
        df_in = temp_df_in[['updateDate', 'status']]
        datalog_day_in = df_in['updateDate'].groupby(df_in['updateDate'].map(lambda x: x[0:10])).count()
        day_list_in = datalog_day_in.index.to_list()
        day_count_list_in = datalog_day_in.values.tolist()
        day_list_in, day_count_list_in = check_zero(start, end, day_list_in, day_count_list_in)
        temp_df_out = df[(df['type'] == 2) & (df['status'] != 'CANCEL') & (df['status'] != 'WAIT') & (df['posName'] !="")]
        df_out = temp_df_out[['updateDate', 'status']]
        datalog_day_out = df_out['updateDate'].groupby(df_out['updateDate'].map(lambda x: x[0:10])).count()
        day_list_out = datalog_day_out.index.to_list()
        day_count_list_out = datalog_day_out.values.tolist()
        day_list_out, day_count_list_out = check_zero(start, end, day_list_out, day_count_list_out)
    else:
        day_list_in = []
        day_count_list_in = []
        day_list_out = []
        day_count_list_out = []
    data_dict = {
                'day_list_in':day_list_in,
                'day_count_list_in':day_count_list_in,
                'day_list_out':day_list_out,
                'day_count_list_out':day_count_list_out
    }
    return data_dict


def conn_mongo_outinfo(start,end):
    mongo_client = pymongo.MongoClient(host='localhost', port=27017)
    # current_dbname = Config.DB_NAME
    # db = mongo_client[current_dbname]
    db = mongo_client['qisda2']
    cach = db.outInfo
    start_date = local2utc(start)
    end_date = local2utc(end)
    # start_date = datetime(2020,8,25,16,00,00)
    # end_date = datetime(2020,8,26,16,00,00)
    data = list(cach.find({"taskNeedOutDate": {"$gte": start_date, "$lt": end_date}}))
    mongo_client.close()
    df = pd.DataFrame(data)
    return (df)

def process_outinfo_hour():
    now = datetime.now()
    start = datetime.now() - timedelta(hours=24)
    start = datetime(start.year, start.month, start.day, start.hour, start.minute, start.second)
    end = datetime(now.year, now.month, now.day, now.hour, now.minute, now.second)
    print("*******************outinfo**24小时数据准备中*********************")
    # start = datetime(2020, 9, 7, 16, 00, 00)
    # end = datetime(2020, 9, 8, 16, 00, 00)
    df = conn_mongo_outinfo(start,end)
    if not df.empty:
        df['taskNeedOutDate'] = df['taskNeedOutDate'].apply(iso2timestamp)
        df_need = df[['taskNeedOutDate', 'totalBindNum']]
        outinfo_hour = df_need['totalBindNum'].groupby(df_need['taskNeedOutDate'].map(lambda x: x[5:13]))
        sum_outinfo_hour = outinfo_hour.sum()
        outhour_list = sum_outinfo_hour.index.to_list()
        outcounthour_list = sum_outinfo_hour.values.tolist()
        outhour_list, outcounthour_list = check_hour_zero(start, now, outhour_list, outcounthour_list)
    else:
        outhour_list = []
        outcounthour_list = []
    return (outhour_list, outcounthour_list)

def process_outinfo_day(n=5):#默认取前6天（含当天）
    now = datetime.now()
    start = get_days_before_today(n)
    end = now + timedelta(days=1)
    end = datetime(end.year, end.month, end.day, 23,59,59)
    # start = datetime(2020,9,7)
    # end = datetime(2020,9,15)
    print ("*******************outinfo**7天数据准备中*********************")
    df = conn_mongo_outinfo(start,end)
    if not df.empty:
        df['taskNeedOutDate'] = df['taskNeedOutDate'].apply(iso2timestamp)
        # df.to_csv('./7dayoutinfo.csv')
        df_need = df[['taskNeedOutDate', 'totalBindNum']]
        outinfo_day = df_need['totalBindNum'].groupby(df_need['taskNeedOutDate'].map(lambda x: x[0:10]))
        sum_outinfo_day = outinfo_day.sum()
        outday_list = sum_outinfo_day.index.to_list()
        outcount_list = sum_outinfo_day.values.tolist()
        # print ("1111{}--{}".format(outday_list,outcount_list))
        outday_list,outcount_list=check_zero(start,end,outday_list,outcount_list)
    else:
        outday_list = []
        outcount_list = []
    return (outday_list,outcount_list)

def check_zero(start,end,day_list,count_list):
    print ("7天原始数据，日期{}，数量{}".format(day_list,count_list))
    date = pd.date_range(start, end)
    date_list = [datetime.strftime(x, '%F') for x in date]
    if len(day_list) == len(date_list):
        return (day_list,count_list)
    else:
        l = len(date_list)
        new_l = [0]*l
        for index,value in enumerate(date_list):
            for index1,value1 in enumerate(day_list):
                if value == value1:
                    new_l[index] = count_list[index1]
        print ("7天补全数据，日期{}，数量{}".format(date_list,new_l))
        return (date_list,new_l)
    # print (date_list)

def check_hour_zero(start,end,hour_list,count_list):
    print ("24小时原始数据，时间{}，数量{}".format(hour_list,count_list))
    date_hour_list = []
    for i in range(25):
        date_hour = (end - timedelta(hours=(i))).strftime('%m-%d %H')
        date_hour_list.append(date_hour)
    date_hour_list = list(reversed(date_hour_list))
    if len(hour_list) == len(date_hour_list):
        return (hour_list,count_list)
    else:
        l = len(date_hour_list)
        new_l = [0]*l
        for index,value in enumerate(date_hour_list):
            for index1,value1 in enumerate(hour_list):
                if value == value1:
                    new_l[index] = count_list[index1]
        # print (date_hour_list,new_l)
        print("24小时补全数据，时间{}，数量{}".format(date_hour_list, new_l))
        return (date_hour_list,new_l)



@bp.route('/')
@bp.route('/index')
def index():
    stop=0
    outday_list,outcount_list=process_outinfo_day()
    outhour_list, outcounthour_list = process_outinfo_hour()
    print ("24小时需求总数量{}".format(sum(outcounthour_list)))
    # outcount_list = [4335,4321,4360,4329,4132,4199,3180]
    log_data_day = process_datalog_day()
    log_data=process_datalog_hour()
    hour_list_in = log_data.get('hour_list_in')
    count_list_in = log_data.get('count_list_in')
    print ("24小时入库总数量{}".format(sum(count_list_in)))
    hour_list_out = log_data.get('hour_list_out')
    count_list_out = log_data.get('count_list_out')
    print("24小时出库总数量{}".format(sum(count_list_out)))
    day_list_in = log_data_day.get('day_list_in')
    day_count_list_in = log_data_day.get('day_count_list_in')
    day_list_out = log_data_day.get('day_list_out')
    day_count_list_out = log_data_day.get('day_count_list_out')
    # date_list,count_list = get_data()
    y_total = [i + j for i, j in zip(day_count_list_in, day_count_list_out)]
    bar = (
        Bar(init_opts=opts.InitOpts(bg_color='rgba(255,255,255,0.7)',
                                    theme=ThemeType.LIGHT
                                    )
            )
            .add_xaxis(hour_list_in)
            .add_yaxis("需求数量", outcounthour_list, stack="outinfo")
            .add_yaxis("实际出库数量", count_list_out,stack="datalog")
            .add_yaxis("实际入库数量", count_list_in, stack="datalog")
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False, font_size=14))
            .set_global_opts(xaxis_opts=opts.AxisOpts(name='时间/小时',axislabel_opts=opts.LabelOpts(rotate=-60)),
                             yaxis_opts=opts.AxisOpts(name='单位:盘',splitline_opts=False,splitarea_opts=True))
    )
    bar1 = (
        Bar(init_opts=opts.InitOpts(bg_color='rgba(255,255,255,0.7)',
                                    theme=ThemeType.LIGHT
                                )
        )
            .add_xaxis(day_list_in)
            .add_yaxis("需求数量", outcount_list, stack="outinfo_day")
            .add_yaxis("实际出库数量", day_count_list_out, stack="datalog_day",)
            .add_yaxis("实际入库数量", day_count_list_in, stack="datalog_day")
            .set_series_opts(label_opts=opts.LabelOpts(is_show=True,position='inside', font_size=12),
                             markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(y=12000, name="达标线=12000")],linestyle_opts=opts.LineStyleOpts(width = 3,color = 'red',)))
            # .set_global_opts(title_opts=opts.TitleOpts(title='7天实际出入库', subtitle='需求-实际'),
            #                  xaxis_opts=opts.AxisOpts(name='时间'),
            #                  yaxis_opts=opts.AxisOpts(name='单位:盘'))
            .set_global_opts(xaxis_opts=opts.AxisOpts(name='时间/天',axislabel_opts=opts.LabelOpts(rotate=-40)),
                             yaxis_opts=opts.AxisOpts(name='单位:盘',max_=13000,split_number=13),
                             )
    )
    line = (
        Line()
            .add_xaxis(day_list_in)
            .add_yaxis(
            "总数量",
            y_total,
            # color="#675bba",
            label_opts=opts.LabelOpts(is_show=True),
        )
    )
    bar1.overlap(line)
    # print(bar.render_embed())
    # print(bar.dump_options())
    return render_template(
        "index.html",
        bar_data=bar.dump_options(),
        bar_data1 = bar1.dump_options(),
        stop=stop
    )

def open_url(url):
    resp = urllib.request.urlopen(url)
    ele_json = json.loads(resp.read())
    return ele_json

def add_outinfo(equip_info,origin_equip,sign):
    print (equip_info)
    equips = []
    if sign == 'agv':
        for equip in equip_info:
            equips.append(equip['name'])
        c = [x for x in equips if x in origin_equip]
        add_equip = [y for y in (equips + origin_equip) if y not in c]
        for add in add_equip:
            number = int(re.findall(r"\d+", add)[0])
            add_dict = {
                'name':add,
                'des':'充电中/待机位',
                'state':'正常',
                'number':number
            }
            equip_info.append(add_dict)
    elif sign == 'line':
        for equip in equip_info:
            equips.append(equip['name'])
        c = [x for x in equips if x in origin_equip]
        add_equip = [y for y in (equips + origin_equip) if y not in c]
        for add in add_equip:
            number = int(re.findall(r"\d+", add)[1])
            add_dict = {
                'name':add,
                'des':'状态正常',
                'state':'正常',
                'number':number
            }
            equip_info.append(add_dict)
    elif sign == 'box':
        for equip in equip_info:
            equips.append(equip['name'])
        c = [x for x in equips if x in origin_equip]
        add_equip = [y for y in (equips + origin_equip) if y not in c]
        for add in add_equip:
            number = int(re.findall(r"\d+", add)[0])
            add_dict = {
                'name':add,
                'des':'未获取到状态',
                'state':'异常',
                'number':number
            }
            equip_info.append(add_dict)
    return equip_info






@bp.route('/getagvinfo',methods=['POST'])
def getagvinfo():
    url = Config.URL
    ele_json = open_url(url)
    current_agv = Config.CONFIG_AGV
    info_agv = []
    for info in ele_json:
        if (info['msgKey'])[9:11] == '号车':
            if info['type'] == 0:
                state = '异常'
            else:
                state = '正常'
            number = int(re.findall(r"\d+", info['name'])[0])
            # print (number)
            c_dict = {
                'name':info['name'],
                'des':info['msgValue'],
                'state':state,
                'number':number}
            info_agv.append(c_dict)
        else:
            pass
    sign = 'agv'
    info_agv = add_outinfo(info_agv,current_agv,sign)
    info_agv = sorted(info_agv, key=operator.itemgetter('number'))
    return json.dumps(info_agv)

@bp.route('/get4cinfo',methods=['POST'])
def get4cinfo():
    url = Config.URL
    ele_json = open_url(url)
    nodes = [{'name':'AGV001','des':'test','state':'正常'}]
    current_4c = Config.CONFIG_4C
    info_4c = []
    for info in ele_json:
        if info['msgKey'][8:9] == 'G':
            if info['type'] == 0:
                state = '异常'
            else:
                state = '正常'
            number = int(re.findall(r"\d+", info['name'])[1])
            c_dict = {
                'name': info['name'],
                'des': info['msgValue'],
                'state': state,
                'number':number}
            info_4c.append(c_dict)
        else:
            pass
    # info_4c = json.dumps(info_4c)
    sign = 'line'
    info_4c = add_outinfo(info_4c, current_4c,sign)
    info_4c = sorted(info_4c, key=operator.itemgetter('number'))
    return json.dumps(info_4c)

@bp.route('/get4dinfo',methods=['POST'])
def get4dinfo():
    url = Config.URL
    ele_json = open_url(url)
    current_4d = Config.CONFIG_4D
    info_4d = []
    for info in ele_json:
        if info['msgKey'][8:9] == 'E':
            if info['type'] == 0:
                state = '异常'
            else:
                state = '正常'
            number = int(re.findall(r"\d+", info['name'])[1])
            c_dict = {
                'name': info['name'],
                'des': info['msgValue'],
                'state': state,
                'number':number}
            info_4d.append(c_dict)
        else:
            pass
    # info_4d = json.dumps(info_4d)
    sign = 'line'
    info_4d = add_outinfo(info_4d, current_4d,sign)
    info_4d = sorted(info_4d, key=operator.itemgetter('number'))
    return json.dumps(info_4d)

@bp.route('/getboxinfo',methods=['POST'])
def getboxinfo():
    url = Config.BOX_URL
    ele_json = open_url(url)
    current_box = Config.CONFIG_BOX
    info_box = []
    for info in ele_json:
        if info['cid'][0:7] == 'line-ac':
            if info['status'] == 1:
                state = '正常'
            else:
                state = '异常'
            number = int(re.findall(r"\d+", info['cid'])[0])
            c_dict = {
                'name': info['cid'],
                'des': '状态正常',
                'state': state,
                'number':number}
            info_box.append(c_dict)
        else:
            pass
    # info_4d = json.dumps(info_4d)
    sign = 'box'
    info_box = add_outinfo(info_box, current_box,sign)
    info_box = sorted(info_box, key=operator.itemgetter('number'))
    return json.dumps(info_box)

@bp.route('/state')
def state():
    return render_template("state.html")