#coding=utf-8
import os
import datetime
import pandas as pd
from LIB.BACKEND import DBManager, Log
from LIB.MIDDLE import SignalMonitor
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import time, datetime
import traceback
from LIB.MIDDLE.DrivingRange import UpdtFct
from urllib import parse
import pymysql
import dateutil.relativedelta
from create_table2 import DrivingRangeResult

dbManager = DBManager.DBManager()
if __name__ == "__main__":
    
    # 时间设置
    now_time = datetime.datetime.now()
    pre_time = now_time + dateutil.relativedelta.relativedelta(minutes=-30)
    end_time=datetime.datetime.strftime(now_time,"%Y-%m-%d %H:%M:%S")
    start_time=datetime.datetime.strftime(pre_time,"%Y-%m-%d %H:%M:%S")
    print(pre_time)


    # # 更新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 from app_device")
    # res = cursor.fetchall()
    # df_sn = pd.DataFrame(res, columns=['sn', 'imei'])
    # 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)
    db_qx = pymysql.connect(
        host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com',
        user='qx_read',
        password='Qx@123456',#Qx@123456
        database='qx_cas',
        charset='utf8'
    )
    
    # 日志配置
    now_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()).replace(":","_")
    log_path = 'log/driving_range/{}/'.format(now_str[0:10]) + now_str
    if not os.path.exists(log_path):
        os.makedirs(log_path)
    log = Log.Mylog(log_name='driving_range', 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()))
    
    try:
        #调度周期：每天运行一次。
        #更新所有sn，连读多日的factor，如果start_date和end_date相隔一天，代表更新start_date的factor。
        logger.info(u"{},{} 任务运行开始\n".format(start_time,end_time), exc_info=True)
        df_res = UpdtFct.updtVehElecRng(db_qx, db_qx, sn_newest_table_name='driving_range_sn_factor_newest', input_time=start_time)
        df_res = df_res.reset_index(drop=True)
        if not df_res.empty:
            df_res.columns = ['sn', 'time', 'soc', 'a0', 'a1', 'a2', 'a3', 'a4', 'vehelecrng']                
            df_res.to_sql("driving_range_result",con=db_engine, if_exists="append",index=False)
        # session = DbSession()
        # for i in range(0, len(df_res)-1):
        #     obj = session.query(DrivingRangeResult).filter_by(sn=df_res.loc[i,'sn']).first()
        #     if not obj:
        #         obj = DrivingRangeResult(df_res.loc[i,'sn'],df_res.loc[i,'time'],df_res.loc[i,'soc'],df_res.loc[i,'a0'],df_res.loc[i,'a1'],df_res.loc[i,'a2'],df_res.loc[i,'a3'],df_res.loc[i,'a4'],
        #                                  df_res.loc[i,'vehelecrng'])               
        #     else:
        #         obj.time = df_res.loc[i,'time']
        #         obj.soc = df_res.loc[i,'soc']
        #         obj.a0 = df_res.loc[i,'a0']
        #         obj.a1 = df_res.loc[i,'a1']
        #         obj.a2 = df_res.loc[i,'a2']
        #         obj.a3 = df_res.loc[i,'a3']
        #         obj.a4 = df_res.loc[i,'a4']
        #         obj.vehelecrng = df_res.loc[i,'vehelecrng']
        #     session.add(obj)
        # session.commit()
        # session.close()
        logger.info(u"{},{} 续驶里程计算完成\n".format(start_time,end_time), exc_info=True)
    except:
        logger.error(traceback.format_exc)
        logger.error(u"{},{} 任务运行错误\n".format(start_time,end_time), exc_info=True)
    db_engine.dispose()
    db_qx.close()