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)