from LIB.BACKEND import DBManager
dbManager = DBManager.DBManager()
import pandas as pd
import numpy as np
import datetime
import seaborn as sns
import matplotlib.pyplot as plt

now_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')   #type: str
now_time=datetime.datetime.strptime(now_time,'%Y-%m-%d %H:%M:%S')     #type: datetime
start_time=now_time-datetime.timedelta(minutes=1)
end_time=str(now_time)
start_time=str(start_time)

def makedataset(cellname):
    dataset = pd.DataFrame()
    for k in range(len(cellname)):
        sn = cellname[k]
        datasn = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time, data_groups=['bms'])
        datasn = datasn['bms']
        datasn['SN号']=sn
        if len(datasn)>0:
            datasn=datasn.iloc[-1]
            dataset=dataset.append(datasn)
    return dataset

fileNamesPK504 = pd.read_excel('sn-20210903.xlsx',sheet_name='科易6060')
fileNamesPK500 = pd.read_excel('sn-20210903.xlsx',sheet_name='科易6040')
fileNamesPK502 = pd.read_excel('sn-20210903.xlsx',sheet_name='科易4840')
fileNamesMGML = pd.read_excel('sn-20210903.xlsx',sheet_name='格林美-力信7255')
fileNamesMGMC = pd.read_excel('sn-20210903.xlsx',sheet_name='格林美-CATL7255')
fileNamesUDO = pd.read_excel('sn-20210903.xlsx',sheet_name='优旦7255')

dataPK504=makedataset(list(fileNamesPK504['SN号']))
dataPK500=makedataset(list(fileNamesPK500['SN号']))
dataPK502=makedataset(list(fileNamesPK502['SN号']))
dataMGML=makedataset(list(fileNamesMGML['SN号']))
dataMGMC=makedataset(list(fileNamesMGMC['SN号']))
dataUDO=makedataset(list(fileNamesUDO['SN号']))

dataPK504=dataPK504[['SN号','单体温度1','单体温度2','单体温度3','单体温度4','其他温度2','其他温度4','其他温度5']]
dataPK500=dataPK500[['SN号','单体温度1','单体温度2','单体温度3','单体温度4','其他温度2','其他温度3','其他温度4','其他温度5']]
dataPK502=dataPK502[['SN号','单体温度1','单体温度2','单体温度3','单体温度4','其他温度2','其他温度3','其他温度4','其他温度5']]
dataMGML=dataMGML[['SN号','单体温度1','单体温度2','单体温度3','单体温度4','其他温度1','其他温度3','其他温度4','其他温度5','其他温度6']]
dataMGMC=dataMGMC[['SN号','单体温度1','单体温度2']]
dataUDO=dataUDO[['SN号','单体温度1','单体温度2']]

datatotal1=pd.concat([dataPK504,dataPK500,dataPK502])
datatotal2=pd.concat([dataMGMC,dataUDO])

def calculavg(datacell):
    avg_temp=[]
    max_temp=[]
    min_temp=[]
    for i in range(len(datacell)):
        avg_temp.append(np.mean(datacell.iloc[i,1:]))
        max_temp.append(max(datacell.iloc[i,1:]))
        min_temp.append(min(datacell.iloc[i,1:]))
    datacell['平均温度']=avg_temp
    datacell['最高温度']=max_temp
    datacell['最低温度']=min_temp
    return datacell

datatotal1=calculavg(datatotal1)
datatotal2=calculavg(datatotal2)
dataMGML=calculavg(dataMGML)
datatotal1=datatotal1[datatotal1['最低温度']>-40]
datatotal2=datatotal2[datatotal2['最低温度']>-40]
dataMGML=dataMGML[dataMGML['最低温度']>-40]
datatotal1=datatotal1.reset_index(drop=True)
datatotal2=datatotal2.reset_index(drop=True)
dataMGML=dataMGML.reset_index(drop=True)

def boxplot_fill(col,a):
    # 计算iqr：数据四分之三分位值与四分之一分位值的差
    iqr = col.quantile(0.75)-col.quantile(0.25)
    # 根据iqr计算异常值判断阈值
    u_th = col.quantile(0.75) + a*iqr # 上界
    l_th = col.quantile(0.25) - a*iqr # 下界
    # 定义转换函数：如果数字大于上界则用上界值填充，小于下界则用下界值填充。
    return l_th,u_th

uptemp_out1=list(boxplot_fill(datatotal1['最高温度'],2.5))[1]
uptemp_out2=list(boxplot_fill(datatotal2['最高温度'],5))[1]
uptemp_out3=list(boxplot_fill(dataMGML['最高温度'],3.5))[1]

anomalies1 = datatotal1[(datatotal1['最高温度']>uptemp_out1) & (datatotal1['最高温度']<120) & (datatotal1['最高温度']!=datatotal1['单体温度4']) & (datatotal1['单体温度4']<50) & (datatotal1['最高温度']!=datatotal1['其他温度2']) & (datatotal1['其他温度2']<50)]
anomalies2 = datatotal2[(datatotal2['最高温度']>uptemp_out2) & (datatotal2['最高温度']<120)]
anomalies3 = dataMGML[(dataMGML['最高温度']>uptemp_out3) & (dataMGML['最高温度']<120)]
anomalies=pd.concat([anomalies1,anomalies2,anomalies3])