data_analyze_platform/LIB/FRONTEND/soh/NCMSoh 20210716.py

# 获取数据
from LIB.BACKEND import DBManager

import os
import pandas as pd
import numpy as np
# import matplotlib.pyplot as plt

#参数输入
Capacity = 41
PackFullChrgVolt=69.99
CellFullChrgVolt=3.5
CellVoltNums=17
CellTempNums=4
FullChrgSoc=98
PeakSoc=57
# #40Ah-OCV
LookTab_SOC = [0,	3.534883489,	8.358178409,	13.18141871,	18.00471528,	22.82796155,	27.65123833,	32.47444668,	37.29772717,	42.12099502,	46.94423182,	51.76744813,	56.59070685,	61.4139927,	66.23719857,	71.0604667,	75.88373853,	80.70702266,	85.5302705,	90.35352009,	95.17676458,	100]
LookTab_OCV = [3.3159,	3.4384,	3.4774,	3.5156,	3.5478,	3.5748,	3.6058,	3.6238,	3.638,	3.6535,	3.6715,	3.6951,	3.7279,	3.7757,	3.8126,	3.8529,	3.8969,	3.9446,	3.9946,	4.0491,	4.109,	4.183]
# #55Ah-OCV
# LookTab_SOC = [0.00, 	2.40, 	6.38, 	10.37, 	14.35, 	18.33, 	22.32, 	26.30, 	30.28, 	35.26, 	40.24, 	45.22, 	50.20, 	54.19, 	58.17, 	60.16, 	65.14, 	70.12, 	75.10, 	80.08, 	84.06, 	88.05, 	92.03, 	96.02, 	100.00]
# LookTab_OCV = [2.7151,	3.0298,	3.1935,	3.2009,	3.2167,	3.2393,	3.2561,	3.2703,	3.2843,	3.2871,	3.2874,	3.2868,	3.2896,	3.2917,	3.2967,	3.3128,	3.3283,	3.3286,	3.3287,	3.3288,	3.3289,	3.3296,	3.3302,	3.3314,	3.3429]

#参数初始化
Soh3=[]
Time3=[]
Bms_Soh3=[]
Soh_Err3=[]
sn_list=[]
#输入一个含有‘SN号’的xlsx
SNdata = pd.read_excel('骑享资产梳理-20210621.xlsx', sheet_name='6040骑享')
SNnums=SNdata['SN号']
for k in range(len(SNnums)):
    SNnum=str(SNnums[k])

    sn = SNnum
    st = '2021-07-06 00:00:00'
    et = '2021-07-07 20:00:00'

    dbManager = DBManager.DBManager()
    df_data = dbManager.get_data(sn=sn, start_time=st, end_time=et, data_groups=['bms'])
    # 
    data = df_data['bms']
    print(data)

    packcrnt=data['总电流[A]']
    packvolt=data['总电压[V]']
    SOC=data['SOC[%]']
    SOH=data['SOH[%]']
    bmsstat=data['充电状态']
    time= pd.to_datetime(data['时间戳'], format='%Y-%m-%d %H:%M:%S')

    #第一步：筛选充电数据
    if len(packcrnt)>100:
        ChgStart=[]
        ChgEnd=[]
        for i in range(3, len(time) - 3):
            if i==3 and bmsstat[i]==2 and bmsstat[i+1]==2 and bmsstat[i+2]==2:
                ChgStart.append(i)
            elif bmsstat[i-2]!=2 and bmsstat[i-1]!=2 and bmsstat[i]==2:
                ChgStart.append(i)
            elif bmsstat[i-1]==2 and bmsstat[i]!=2 and bmsstat[i+1]!=2:
                ChgEnd.append(i-1)
            elif i == (len(time) - 4) and bmsstat[len(bmsstat)-1] == 2 and bmsstat[len(bmsstat)-2] == 2:
                ChgEnd.append(len(time)-2)

        print(ChgStart)
        print(ChgEnd)
        #第二步：筛选充电起始Soc<45% & SOC>85%，电芯温度>5℃
        ChgStartValid1=[]
        ChgEndValid1=[]
        ChgStartValid2=[]
        ChgEndValid2=[]
        StandingNum=[]

        for i in range(min(len(ChgStart),len(ChgEnd))):

            #获取最小温度值
            celltemp = []
            for j in range(1, CellTempNums+1):
                s = str(j)
                temp = data['单体温度' + s]
                celltemp.append(temp[ChgEnd[i]])
            
            #去除电流0点   
            for k in range(ChgStart[i],ChgEnd[i]):
                if packcrnt[k]<-0.5 and packcrnt[k+1]>-0.5 and packcrnt[k+2]>-0.5 and packcrnt[k+3]>-0.5:
                    ChgEnd[i]=k
            
            #计算最大packvolt
            if len(packvolt[ChgStart[i]:ChgEnd[i]])>0:
                packvoltMAX=max(packvolt[ChgStart[i]:ChgEnd[i]])

                    
                #筛选满足2点法计算的数据
                StandingTime=0
                StandingTime1=0
                StandingTime2=0
                if SOC[ChgEnd[i]]>85 and SOC[ChgStart[i]]<45 and min(celltemp)>5:
                    for m in range(min(len(packcrnt)-ChgEnd[i]-2,ChgStart[i]-2)):
                        if abs(packcrnt[ChgStart[i] - m - 1]) < 0.1:
                            StandingTime = StandingTime + (time[ChgStart[i] - m] - time[ChgStart[i] - m - 1]).total_seconds()
                        if abs(packcrnt[ChgEnd[i] + m + 1]) < 0.1:
                            StandingTime1 = StandingTime1 + (time[ChgEnd[i] + m + 1] - time[ChgEnd[i] + m]).total_seconds()
                        if StandingTime > 900 and StandingTime1>900 and ((time[ChgEnd[i]]-time[ChgStart[i]]).total_seconds())/(ChgEnd[i]-ChgStart[i])<60:  #筛选静置时间>15min且慢充过程丢失数据少
                            ChgStartValid1.append(ChgStart[i])
                            ChgEndValid1.append(ChgEnd[i])
                            StandingNum.append(m)
                            break
                        if abs(packcrnt[ChgStart[i] - m - 2])>0.5 and abs(packcrnt[ChgEnd[i] + m + 2])>0.5:
                            break

                    #筛选满充的数据
                    # for m in range(min(len(packcrnt)-ChgEnd[i],ChgStart[i]-2)):
                    #     if abs(packcrnt[ChgStart[i] - m - 2]) < 0.1 and  abs(packcrnt[ChgEnd[i] + m + 2]) < 0.1:
                    #         StandingTime = StandingTime + (time[ChgStart[i] - m] - time[ChgStart[i] - m - 1]).total_seconds()
                    #         if StandingTime>1200 and  packvoltMAX>PackFullChrgVolt:
                    #             ChgStartValid2.append(ChgStart[i])
                    #             ChgEndValid2.append(ChgEnd[i])
                    #             break
                    #         elif abs(packcrnt[ChgStart[i] - m - 2])>0.5:
                    #             break
                    #     else:
                    #         break


        print(ChgStart)
        print(ChgEnd)
        print(ChgStartValid1)
        print(ChgEndValid1)
        print(StandingNum)

        # 计算soh
        Soh1=[]
        Soh2=[]
        Time1=[]
        Bms_Soh1=[]
        Soh_Err1=[]
        #两点法计算Soh
        if len(ChgStartValid1)>0:
            for i in range(len(ChgStartValid1)):
                #计算Ah
                Ah=0
                for j in range(ChgStartValid1[i],ChgEndValid1[i]):
                    Step=(time[j+1]-time[j]).total_seconds()
                    Ah=Ah-packcrnt[j+1]*Step/3600
                #计算每个电芯的Soh
                for j in range(1, CellVoltNums+1):
                    s = str(j)
                    cellvolt = data['单体电压' + s]/1000
                    OCVStart=cellvolt[ChgStartValid1[i]-2]
                    OCVEnd=cellvolt[ChgEndValid1[i]+StandingNum[i]]
                    #soh
                    Ocv_Soc1=np.interp(OCVStart,LookTab_OCV,LookTab_SOC)
                    Ocv_Soc2=np.interp(OCVEnd,LookTab_OCV,LookTab_SOC)
                    Soh2.append(Ah*100/((Ocv_Soc2-Ocv_Soc1)*0.01*Capacity))
                Soh1.append(np.mean(Soh2))
                Bms_Soh1.append(SOH[ChgStartValid1[i]])
                Soh_Err1.append(Bms_Soh1[-1]-Soh1[-1])
                Time1.append(time[ChgStartValid1[i]])

            
            Soh3.append(np.mean(Soh1))
            Bms_Soh3.append(np.mean(Bms_Soh1))
            Soh_Err3.append(np.mean(Soh_Err1))
            Time3.append(time[ChgStartValid1[-1]])
            sn_list.append(SNnum)

                # ax1 = plt.subplot(3, 1, 1)
                # plt.plot(xvolt[10:-10],DVDQ[10:-10],'r*-')
                # ax1 = plt.subplot(3, 1, 2)
                # plt.plot(soc2[10:-10],xvolt[10:-10],'y*-')
                # ax1 = plt.subplot(3, 1, 3)
                # plt.plot(soc2[10:-10], DVDQ[10:-10], 'r*-')
                # plt.show()

        #第四步：将数据存入Excel
            result_soh2={'时间': Time1,
                'BMS_SOH': Bms_Soh1,
                'SOH': Soh1,
                'SOH误差': Soh_Err1}

            Result_Soh2=pd.DataFrame(result_soh2)
            print(Result_Soh2)
            Result_Soh2.to_csv('BMS_SOH_'+SNnum+'.csv',encoding='GB18030')

#     result_soh1={'时间': Time3,
#         'SN号':sn_list,
#         'BMS_SOH': Bms_Soh3,
#         'SOH': Soh3,
#         'SOH误差': Soh_Err3}

# Result_Soh1=pd.DataFrame(result_soh1)
# print(Result_Soh1)
# Result_Soh1.to_csv('BMS_SOH_'+'6040'+'.csv',encoding='GB18030')