# 将文件中的ocv数据更新到数据库中
import pandas as pd
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from sqlalchemy.pool import NullPool
from urllib import parse
import json

host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
port = '3306'
db = 'hz_dev'
user = 'hz_dev'
password = parse.quote_plus('Qx@123456')

db_engine = create_engine(
    "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
        user, password, host, port, db
    ), poolclass=NullPool)



df_cell = pd.read_sql("select * from sys_battery_cell_model", db_engine)
df_model_lk = pd.read_sql("select * from sys_battery_model_lk where model_type='M'", db_engine)
df_module_model = pd.read_sql("select * from sys_battery_module_model", db_engine)
# df_entity = pd.read_sql("select * from sys_battery_pack_entity_storage", db_engine)
df_pack_model = pd.read_sql("select * from sys_battery_pack_model", db_engine)
df_algo_pack_param = pd.read_sql("select * from algo_pack_param", db_engine)

# 读取ocv曲线
files = os.listdir('./OCV-SOC曲线')
for f in files:
    # 根据cellcode 查找packcode
    module_codes =  list(df_module_model[df_module_model['cell_model']==f[0:-5].replace('_','/')]['model_code'].values)
    for module_code in module_codes:
        pack_codes = df_model_lk[df_model_lk['child_code']==module_code]['code']
        for pack_code in pack_codes:
            # 读取ocv曲线， 并更新到pack参数中
            df = pd.read_excel('./OCV-SOC曲线/'+f)
            param = df_algo_pack_param[df_algo_pack_param['pack_code']==pack_code]['param']
            if len(param) == 0:
                param = {'charge_ocv_v':list(df['充电OCV']),'charge_ocv_soc':list(df['充电SOC']),'discharge_ocv_v':list(df['放电OCV']),'discharge_ocv_soc':list(df['放电SOC'])}
            else:
                param = json.loads(param.values[0])
                param.update({'charge_ocv_v':list(df['充电OCV']),'charge_ocv_soc':list(df['充电SOC']),'discharge_ocv_v':list(df['放电OCV']),'discharge_ocv_soc':list(df['放电SOC'])})
            param = json.dumps(param)
            Session = sessionmaker(bind=db_engine)
            session = Session()
            cursor = session.execute('insert into algo_pack_param (pack_code, param) values (:pack_code, :param) on duplicate key update param = :param',
                                        params={"pack_code":pack_code,"param": param})
            session.commit()
            session.close()
db_engine.dispose()
    

# 更新pack的容量到参数中
import pandas as pd
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool
from urllib import parse
import json

host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
port = '3306'
db = 'hz_dev'
user = 'hz_dev'
password = parse.quote_plus('Qx@123456')

db_engine = create_engine(
    "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
        user, password, host, port, db
    ), poolclass=NullPool)

Session = sessionmaker(bind=db_engine)
session = Session()

df_cell = pd.read_sql("select * from sys_battery_cell_model", db_engine)
df_model_lk = pd.read_sql("select * from sys_battery_model_lk where model_type='M'", db_engine)
df_module_model = pd.read_sql("select * from sys_battery_module_model", db_engine)
# df_entity = pd.read_sql("select * from sys_battery_pack_entity_storage", db_engine)
df_pack_model = pd.read_sql("select * from sys_battery_pack_model", db_engine)
df_algo_pack_param = pd.read_sql("select * from algo_pack_param", db_engine)

# 读取ocv曲线
files = os.listdir('./OCV-SOC曲线')
for f in files:
    module_codes =  list(df_module_model[df_module_model['cell_model']==f[0:-5].replace('_','/')]['model_code'].values)
    for module_code in module_codes:
        pack_codes = df_model_lk[df_model_lk['child_code']==module_code]['code']
        for pack_code in pack_codes:
            # 根据cellcode 查找packcode及容量
            capacity = df_pack_model[df_pack_model['model_code']==pack_code]['capacity'].values[0]
            # 更新参数json
            param = df_algo_pack_param[df_algo_pack_param['pack_code']==pack_code]['param']
            param = json.loads(param.values[0])
            param.update({'capacity':str(capacity)})
            param = json.dumps(param)
            Session = sessionmaker(bind=db_engine)
            session = Session()
            cursor = session.execute('insert into algo_pack_param (pack_code, param) values (:pack_code, :param) on duplicate key update param = :param',
                                        params={"pack_code":pack_code,"param": param})
            session.commit()
db_engine.dispose()