data_analyze_platform/USER/LZX/01算法开发/01电压排序/01算法/maincopycopy.py

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 SNnums
#     global df_Diag_Ram
#     start=time.time()
#     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:
#     if 'PK500' in sn:
#         celltype=1 #6040三元电芯
#     elif 'PK502' in sn:
#         celltype=2 #4840三元电芯
#     elif 'K504B' in sn:
#         celltype=99    #60ah林磷酸铁锂电芯
#     elif 'MGMLXN750' in sn:
#         celltype=3 #力信50ah三元电芯
#     elif 'MGMCLN750' or 'UD' in sn: 
#         celltype=4 #CATL 50ah三元电芯
#     else:
#         print('SN：{}，未找到对应电池类型！！！'.format(sn))
#         continue
#         # sys.exit()
#     param=QX_BatteryParam.BatteryInfo(celltype) 
#     #读取结果数据库数据........................................................................................................................................................
#     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 = df_bms[df_bms['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)#一次微分
#     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.to_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+'CBMS_diag_'+sn+'.csv',encoding='gbk')
#     # volt_column = ['0','1','2','3','4','5','6','7']
#     # fig = plt.figure(figsize=(20,10))
#     # length = len(problem_data)
#     # for column in volt_column:
#     #     plt.scatter([x for x in range(0, length)], problem_data[column][0:length], label=column)
#     # plt.legend()
#     # title = problem_data['device_id'][0]
#     # plt.title(title)
#     # plt.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+title+'.png', dpi=300)
#     # plt.show()
#     # fig = plt.figure()
#     # #ax = plt.subplot()
#     # plt.scatter(problem_data['date'], problem_data['0'], c='black',marker='o',alpha=0.5, label = "2#电芯")
#     # plt.scatter(problem_data['date'], problem_data['1'], c='green', marker='o',alpha=0.5, label = "3#电芯")
#     # plt.scatter(problem_data['date'], problem_data['2'], c='red',marker='o', alpha=0.5, label = "4#电芯") 
#     # plt.scatter(problem_data['date'], problem_data['3'], c='black',marker='o',alpha=0.5, label = "5#电芯")
#     # plt.scatter(problem_data['date'], problem_data['4'], c='green', marker='o',alpha=0.5, label = "6#电芯")
#     # plt.scatter(problem_data['date'], problem_data['5'], c='red',marker='o', alpha=0.5, label = "7#电芯")  
#     # plt.scatter(problem_data['date'], problem_data['6'], c='black',marker='o',alpha=0.5, label = "8#电芯")
#     # plt.scatter(problem_data['date'], problem_data['7'], c='green', marker='o',alpha=0.5, label = "9#电芯") 
#     # plt.xlabel("时间", fontsize=15)
#     # plt.ylabel("电压排序", fontsize=15)
#     # title = problem_data['device_id'][0]
#     # plt.title(title, fontsize=20)
#     # plt.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+title+'.png', dpi=300)
#     # plt.show()
#     #print(problem_data)

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

#...............................................主函数.......................................................................................................................
if __name__ == "__main__":
    
    excelpath=r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\sn.csv'
    # SNdata_6060 = pd.read_excel(excelpath, sheet_name='科易6060')
    # SNdata_6040 = pd.read_excel(excelpath, sheet_name='科易6040')
    # SNdata_4840 = pd.read_excel(excelpath, sheet_name='科易4840')
    # SNdata_L7255 = pd.read_excel(excelpath, sheet_name='格林美-力信7255')
    # SNdata_C7255 = pd.read_excel(excelpath, sheet_name='格林美-CATL7255')
    # SNdata_U7255 = pd.read_excel(excelpath, sheet_name='优旦7255')
    # SNdata_didi = pd.read_csv(excelpath, encoding='gbk')
    SNdata_didi_trw = pd.read_csv(excelpath, encoding='gbk')
    # SNnums_6060=SNdata_6060['SN号'].tolist()
    # SNnums_6040=SNdata_6040['SN号'].tolist()
    # SNnums_4840=SNdata_4840['SN号'].tolist()
    # SNnums_L7255=SNdata_L7255['SN号'].tolist()
    # SNnums_C7255=SNdata_C7255['SN号'].tolist()
    # SNnums_U7255=SNdata_U7255['SN号'].tolist()
    # SNnums_didi = SNdata_didi['device_id'].tolist()
    SNnums_didi_trw = SNdata_didi_trw['device_id'].tolist()
    # SNnums=SNnums_L7255 + SNnums_C7255 + SNnums_6040 + SNnums_4840 + SNnums_U7255+ SNnums_6060
    # SNnums=['BAA3219081550897']
    SNnums = SNnums_didi_trw
    
    mylog=log.Mylog('log_diag.txt','error')
    mylog.logcfg()
    #............................模块运行前，先读取数据库中所有结束时间为0的数据，需要从数据库中读取................
    #result=pd.read_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\result.csv',encoding='gbk')
    
    #df_Diag_Ram=result[result['end_time']=='0000-00-00 00:00:00']
    print('----------------输入--------')
    #定时任务.......................................................................................................................................................................
    # 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)
    print('---------------计算中-------------------------')
    start=time.time()
    for sn in SNnums:
        if 'PK500' in sn:
            celltype=1 #6040三元电芯
        elif 'PK502' in sn:
            celltype=2 #4840三元电芯
        elif 'K504B' in sn:
            celltype=99    #60ah林磷酸铁锂电芯
        elif 'MGMLXN750' in sn:
            celltype=3 #力信50ah三元电芯
        elif 'MGMCLN750' or 'UD' in sn: 
            celltype=4 #CATL 50ah三元电芯
        else:
            print('SN：{}，未找到对应电池类型！！！'.format(sn))
            continue
            # sys.exit()
        param=QX_BatteryParam.BatteryInfo(celltype) 
        #读取结果数据库数据........................................................................................................................................................
        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()
        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('/','')
        
        # volt_column = [0,1,2,3,4,5,6,7]
        # fig = plt.figure(figsize=(20,10))
        # length = len(problem_data)
        # num_length = np.arange(length)
        # for column in volt_column:
        #     plt.scatter(num_length, problem_data[column], label=column)
        # plt.legend()
        # # title = problem_data['device_id'][0]
        # plt.title(sn)
        # plt.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+sn+'.png', dpi=300)
        # plt.show()
        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')
        # fig = plt.figure()
        # #ax = plt.subplot()
        # plt.scatter(problem_data['date'], problem_data['0'], c='black',marker='o',alpha=0.5, label = "2#电芯")
        # plt.scatter(problem_data['date'], problem_data['1'], c='green', marker='o',alpha=0.5, label = "3#电芯")
        # plt.scatter(problem_data['date'], problem_data['2'], c='red',marker='o', alpha=0.5, label = "4#电芯") 
        # plt.scatter(problem_data['date'], problem_data['3'], c='black',marker='o',alpha=0.5, label = "5#电芯")
        # plt.scatter(problem_data['date'], problem_data['4'], c='green', marker='o',alpha=0.5, label = "6#电芯")
        # plt.scatter(problem_data['date'], problem_data['5'], c='red',marker='o', alpha=0.5, label = "7#电芯")  
        # plt.scatter(problem_data['date'], problem_data['6'], c='black',marker='o',alpha=0.5, label = "8#电芯")
        # plt.scatter(problem_data['date'], problem_data['7'], c='green', marker='o',alpha=0.5, label = "9#电芯") 
        # plt.xlabel("时间", fontsize=15)
        # plt.ylabel("电压排序", fontsize=15)
        # title = problem_data['device_id'][0]
        # plt.title(title, fontsize=20)
        # plt.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+title+'.png', dpi=300)
        # plt.show()
        #print(problem_data)

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