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
#-------------------------------------------新建电压排序函数--------------------------------------------
def CellVolt_rang():
#     global df_Diag_Ram
#     end_time=datetime.datetime.now()
#     start_time=end_time-datetime.timedelta(seconds=720)
#     start_time=start_time.strftime('%Y-%m-%d %H:%M:%S')
#     end_time=end_time.strftime('%Y-%m-%d %H:%M:%S')
# print('---------------计算中-------------------------')
    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()
        # mode=1
        # # tablename4='cellstateestimation_intershort'
        # DBRead=DBDownload.DBDownload(host, port, db, user, password, mode)  #mode==1取数据库最后一行数据
        # with DBRead as DBRead:
        #     df_data=DBRead.getdata('date,device_id,position,current,soc,cellvolt_2,cellvolt_3,cellvolt_4,cellvolt_5,cellvolt_6,cellvolt_7,cellvolt_8,cellvolt_9', tablename=tablename, sn=sn, timename='date', st=start_time, sp=end_time)  
        # df_bms.to_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\origin_data\\'+'CBMS_diag_'+sn+'.csv',encoding='gbk')
        #电压排序................................................................................................................................................................
        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()
        sn_choose = 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)
            sn_choose.append(sn)

        
        #df_chrgr_cellvolt_sort.to_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+'CBMS_diag_'+sn+'.csv',encoding='gbk')
        #保存数据这个位置改为保存sn号
        end=time.time()
        # print('--------------计算时间：------------')
        # print(end-start)
        # print(df_soh)
    return sn_choose
        

#...............................................主函数.......................................................................................................................
if __name__ == "__main__":
    
    excelpath=r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\sn.xlsx'
    SNdata_didi_trw = pd.read_excel(excelpath, sheet_name='sn')
    SNnums_didi_trw = SNdata_didi_trw['device_id'].tolist()
    SNnums = SNnums_didi_trw
    
    mylog=log.Mylog('log_diag.txt','error')
    mylog.logcfg()
#..............................................................................................................................................................
    scheduler = BlockingScheduler()
    scheduler.add_job(CellVolt_rang, 'interval', seconds=720, id='diag_job')

    try:  
        scheduler.start()
    except Exception as e:
        scheduler.shutdown()
        print(repr(e))
        mylog.logopt(e)