__author__ = 'lmstack' #coding=utf-8 import os import datetime import pandas as pd from LIB.BACKEND import DBManager, Log from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker import time, datetime import dateutil.relativedelta import traceback from LIB.MIDDLE.CellStateEstimation.Common import log from pandas.core.frame import DataFrame from LIB.MIDDLE.SaftyCenter.Common import QX_BatteryParam from LIB.MIDDLE.SaftyCenter.CellValueDiag.V1_0_0.SC_SamplingSafty import SamplingSafty from LIB.MIDDLE.CellStateEstimation.Common import DBDownload from urllib import parse import pymysql import pdb from apscheduler.schedulers.blocking import BlockingScheduler import datacompy def fun(): global df_sn global db_res_engine global logger global df_Diag_Ram # 读取结果数据库 host2='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port2=3306 db2='safety_platform' user2='qx_read' password2='Qx@123456' start=time.time() end_time=datetime.datetime.now() start_time=end_time-datetime.timedelta(seconds=120) start_time=start_time.strftime('%Y-%m-%d %H:%M:%S') end_time=end_time.strftime('%Y-%m-%d %H:%M:%S') logger.info("cycle start !!!!!!!!!!!!!!!!!!!!") for i in range(0, len(df_sn)): try: if df_sn.loc[i, 'imei'][5:9] == 'N640': celltype=1 #6040三元电芯 elif df_sn.loc[i, 'imei'][5:9] == 'N440': celltype=2 #4840三元电芯 elif df_sn.loc[i, 'imei'][5:9] == 'L660': celltype=99 # 6060锂电芯 elif df_sn.loc[i, 'imei'][3:5] == 'LX' and df_sn.loc[i, 'imei'][5:9] == 'N750': celltype=3 #力信 50ah三元电芯 elif df_sn.loc[i, 'imei'][3:5] == 'CL' and df_sn.loc[i, 'imei'][5:9] == 'N750': celltype=4 #CATL 50ah三元电芯 elif df_sn.loc[i, 'imei'][5:9] == 'L420': celltype=101 #20ah磷酸铁锂电芯 else: logger.info("pid-{} celltype-{} SN: {} SKIP!".format(os.getpid(), "未知", sn)) continue sn = df_sn.loc[i, 'sn'] logger.info("pid-{} celltype-{} SN: {} START!".format(os.getpid(), celltype, sn)) param=QX_BatteryParam.BatteryInfo(celltype) # sn='PK50201A000002039' # celltype=2 # start_time='2021-05-02 09:12:26' # end_time='2021-06-03 19:12:26' # # df_bms= pd.read_csv(r'D:\Platform\platform_python\data_analyze_platform\USER\01qixiang\98Download\\'+'BMS_'+sn+'.csv',encoding='GB18030') #读取原始数据库数据........................................................................................................................................................ dbManager = DBManager.DBManager() df_data = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time, data_groups=['bms']) df_bms = df_data['bms'] # df_bms.to_csv(r'D:\Platform\platform_python\data_analyze_platform\USER\01qixiang\99Result\\''BMS_'+sn+'.csv',encoding='GB18030') #读取结果数据库数据........................................................................................................................................................ host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port=3306 db='qx_cas' user='qx_read' password='Qx@123456' mode=1 # 读取结果数据库 host2='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port2=3306 db2='safety_platform' user2='qx_read' password2='Qx@123456' tablename1='cellstateestimation_soh' tablename2='cellstateestimation_uniform_socvoltdiff' tablename3='cellstateestimation_soc' DBRead=DBDownload.DBDownload(host, port, db, user, password,mode) with DBRead as DBRead: df_soh=DBRead.getdata('time_st','sn','soh', 'cellsoh', tablename=tablename1, sn=sn, timename='time_sp', st=start_time, sp=end_time) df_uniform=DBRead.getdata('time','sn','cellsoc_diff','cellmin_num','cellmax_num', tablename=tablename2, sn=sn, timename='time', st=start_time, sp=end_time) # df_soc=DBRead.getdata('time','sn','packsoc', tablename=tablename3, sn=sn) #电池诊断................................................................................................................................................................ #BatDiag=CBMSBatDiag.BatDiag(sn,celltype,df_bms, df_soh, df_uniform) #df_res=BatDiag.diag() #df_Diag_Ram_old=df_Diag_Ram df_Diag_Ram_Update=DataFrame(columns=['start_time', 'end_time', 'product_id', 'code', 'level', 'info','advice']) CellFltInfo=DataFrame(columns=['start_time', 'end_time', 'product_id', 'code', 'level', 'info','advice']) if not df_bms.empty: CellFltInfo=df_Diag_Ram[df_Diag_Ram['product_id']==sn] df_Diag_Ram_Update=SamplingSafty.main(sn,param,df_bms,CellFltInfo) if not df_Diag_Ram_Update.empty: sn_index=df_Diag_Ram[df_Diag_Ram['product_id']==sn].index df_Diag_Ram=df_Diag_Ram.drop(index=sn_index) df_Diag_Ram=df_Diag_Ram.append(df_Diag_Ram_Update) df_Diag_Ram.reset_index(inplace=True,drop=True) Diag_Ram_Dif=datacompy.Compare(df_Diag_Ram_Update,CellFltInfo,join_columns=['product_id','end_time', 'code']) Diag_Ram_Dif=Diag_Ram_Dif.df1_unq_rows if len(Diag_Ram_Dif)>0: Diag_Ram_Dif_New=Diag_Ram_Dif[Diag_Ram_Dif['end_time']=='0000-00-00 00:00:00'] Diag_Ram_Dif_Finish=df_Diag_Ram[df_Diag_Ram['end_time']!='0000-00-00 00:00:00'] if len(Diag_Ram_Dif_New)>0: # 新增 Diag_Ram_Dif_New.columns = ['start_time', 'end_time', 'product_id', 'code', 'level', 'info', 'advice'] Diag_Ram_Dif_New['factory'] = '骑享' Diag_Ram_Dif_New.to_sql("all_fault_info",con=db_res_engine, if_exists="append",index=False) if len(Diag_Ram_Dif_Finish)>0: # 修改 Diag_Ram_Dif_Finish=Diag_Ram_Dif_Finish.reset_index(drop=True) try: conn = pymysql.connect(host=host2, port=port2, user=user2, password=password2, database=db2) cursor = conn.cursor() for i in range(0,len(Diag_Ram_Dif_Finish)): cursor.execute(''' update all_fault_info set end_time={} where product_id='{}' and code={} '''.format(Diag_Ram_Dif_Finish.loc[i,'end_time'], Diag_Ram_Dif_Finish.loc[i,'product_id'], Diag_Ram_Dif_Finish.loc[i,'code'])) conn.commit() conn.close(); except: logger.error(traceback.format_exc) logger.error(u"{} ：{},{} 任务运行错误\n".format(sn,start_time,end_time), exc_info=True) conn.close(); end=time.time() logger.info("pid-{} SN: {} DONE!".format(os.getpid(), sn)) except: logger.error(traceback.format_exc) logger.error(u"{} ：{},{} 任务运行错误\n".format(sn,start_time,end_time), exc_info=True) if __name__ == "__main__": # 时间设置 # now_time = datetime.datetime.now() # pre_time = now_time + dateutil.relativedelta.relativedelta(days=-1)# 前一日 # end_time=datetime.datetime.strftime(now_time,"%Y-%m-%d 00:00:00") # start_time=datetime.datetime.strftime(pre_time,"%Y-%m-%d 00:00:00") history_run_flag = False # 历史数据运行标志 # 更新sn列表 host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com' port=3306 db='qixiang_oss' user='qixiang_oss' password='Qixiang2021' conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db) cursor = conn.cursor() cursor.execute("select sn, imei, add_time from app_device") res = cursor.fetchall() df_sn = pd.DataFrame(res, columns=['sn', 'imei', 'add_time']) df_sn = df_sn.reset_index(drop=True) conn.close(); # 数据库配置 host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port = 3306 user = 'qx_cas' password = parse.quote_plus('Qx@123456') database = 'qx_cas' db_engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( user, password, host, port, database )) DbSession = sessionmaker(bind=db_engine) # 运行历史数据配置 df_first_data_time = pd.read_sql("select * from bat_first_data_time", db_engine) # 日志配置 now_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()).replace(":","_") log_path = 'log/' + now_str if not os.path.exists(log_path): os.makedirs(log_path) log = Log.Mylog(log_name='saftyCenter_cellvaluediag', log_level = 'info') log.set_file_hl(file_name='{}/info.log'.format(log_path), log_level='info', size=1024* 1024 * 100) log.set_file_hl(file_name='{}/error.log'.format(log_path), log_level='error', size=1024* 1024 * 100) logger = log.get_logger() logger.info("pid is {}".format(os.getpid())) # 算法参数 host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port=3306 db='safety_platform' user='qx_read' password=parse.quote_plus('Qx@123456') tablename='all_fault_info' db_res_engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( user, password, host, port, db )) #............................模块运行前，先读取数据库中所有结束时间为0的数据，需要从数据库中读取................ # print("select start_time, end_time, product_id, code, level, info, advice, factory from {}".format(tablename)) result=pd.read_sql("select start_time, end_time, product_id, code, level, info, advice from all_fault_info where factory = '{}'".format('骑享'), db_res_engine) result = result[['start_time', 'end_time', 'product_id', 'code', 'level', 'info', 'advice']] df_Diag_Ram=result[result['end_time']=='0000-00-00 00:00:00'] #定时任务....................................................................................................................................................................... scheduler = BlockingScheduler() scheduler.add_job(fun, 'interval', seconds=120, id='diag_job') try: scheduler.start() except Exception as e: scheduler.shutdown() logger.error(str(e))