import CBMSBatChrgcopy
import log
#coding=utf-8
import datetime
import pandas as pd
from LIB.BACKEND import DBManager, Log
from LIB.MIDDLE.CellStateEstimation.Common.V1_0_1 import DBDownload
# from LIB.MIDDLE.CellStateEstimation.Common.V1_0_1 import log
from sqlalchemy import create_engine
import time, datetime
import os
import numpy as np
from apscheduler.schedulers.blocking import BlockingScheduler
import QX_BatteryParam
import pymysql
import matplotlib.pyplot as plt

#...............................................主函数.......................................................................................................................
if __name__ == "__main__":
    
    excelpath=r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\sn.csv'

    SNdata_didi_trw = pd.read_csv(excelpath, encoding='gbk')

    SNnums_didi_trw = SNdata_didi_trw['device_id'].tolist()

    SNnums = SNnums_didi_trw
    
    mylog=log.Mylog('log_diag.txt','error')
    mylog.logcfg()
    #print('---------------计算中-------------------------')
    start=time.time()
    for sn in SNnums:
        
        #读取结果数据库数据........................................................................................................................................................
        host='47.97.96.242'
        port=3306
        db='didi'
        user='root'
        password='qx123456'
        tablename='didi_data'
        param='date,device_id,bat_model,position,current,soc,celltemp,cellvolt_2,cellvolt_3,cellvolt_4,cellvolt_5,cellvolt_6,cellvolt_7,cellvolt_8,cellvolt_9'
        mysql = pymysql.connect (host=host, user=user, password=password, port=port, database=db)
        cursor = mysql.cursor()
        sql = "select %s from %s where device_id='%s'" %(param,tablename,sn)
        cursor.execute(sql)
        res = cursor.fetchall()
        df_bms= pd.DataFrame(res,columns=param.split(','))
        cursor.close()
        mysql.close()
        #电压排序................................................................................................................................................................
        df_temp_crnt = df_bms[df_bms['current']>1]#筛选充电数据
        df_temp = df_temp_crnt[df_temp_crnt['position']==2]#筛选充电数据
        df_chrgr = df_temp.reset_index(drop=True)
        df_chrgr_cellvolt = df_chrgr[['cellvolt_2','cellvolt_3','cellvolt_4','cellvolt_5','cellvolt_6','cellvolt_7','cellvolt_8','cellvolt_9']]
        df_chrgr_cellvolt_change = np.array(df_chrgr_cellvolt)#转数组
        df_chrgr_cellvolt_sort = np.argsort(df_chrgr_cellvolt_change)#取排序号
        df_cellvolt_sort_dif = np.diff(df_chrgr_cellvolt_sort,axis=0)#一次微分
        df_cellvolt_sort_dif_confir = np.nonzero(df_cellvolt_sort_dif)#取非0值
        Cell_num = set(df_cellvolt_sort_dif_confir[1])#寻找哪号电芯序号异常np.unique
        X_col=np.size(df_chrgr_cellvolt,0)  #计算 X 的列数
        #df_cellvolt_sort_difdif = np.diff(df_cellvolt_sort_dif)#二次微分
        problem_data = pd.DataFrame()
        temp_list = []

        for item in Cell_num:
            temp_list.append(np.sum(df_cellvolt_sort_dif_confir[1]==item) > X_col/20)
        
        if any(temp_list):#序号变化的电芯
            data_temp = pd.DataFrame(df_chrgr_cellvolt_sort)
            #problem_data = pd.concat([df_chrgr,data_temp], axis = 1)
            problem_data = data_temp
        sn=sn.replace('/','')
        
        if not problem_data.empty:
            problem_data.to_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+'CBMS_diag_'+sn+'.csv',encoding='gbk')
            #print(problem_data)
            # ax=problem_data.plot(marker='*',markersize=15, figsize=(16,9))
            # plt.xlabel('时间', fontsize=20)
            # plt.ylabel('排序变化', fontsize=20)
            # plt.xticks(fontsize=15)
            # plt.yticks(fontsize=15)
            # # plt.ylim(-30,30)
            # plt.title(str(sn),fontsize=25)
            # plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
            # plt.rcParams['axes.unicode_minus']=False #用来正常显示负号
            # plt.legend(bbox_to_anchor=(1, 0), loc=3, fontsize=14)
            # plt.show()
            # fig = ax.get_figure()
            # fig.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+str(sn)+'电压排序.png')

        end=time.time()
        print('--------------计算时间：------------')
        print(end-start)
        # print(df_soh)