data_analyze_platform/USER/LZX/01算法开发/01电压排序/01算法/maincopy.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.xlsx'
    # 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_excel(excelpath, sheet_name='sn')
    SNdata_didi_trw = pd.read_excel(excelpath, sheet_name='sn')
    # 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)