import pandas as pd
from sqlalchemy import create_engine
import datetime

engine = create_engine('mysql+pymysql://root:admin123456@192.168.50.235:30306/appoint')
mesurepoint_conf = {
    0: '网侧L1相功率因数',
    1: '网侧L1相电流',
    2: '网侧L2相电流',
    3: '网侧L3相电流',
    4: '变频器水冷入口压力',
    5: '变频器水冷出口压力',
    6: '变频器水冷入口温度',
    7: '变频器水冷出口温度',
    8: '电网频率',
    9: '网侧L1相电压',
    10: '网侧L2相电压',
    11: '网侧L3相电压',
    12: '外测有功功率',
    13: '发电机进风口前部温度',
    14: '发电机压差传感器',
    15: '发电机进风口后部温度',
    16: '发电机出风口温度',
    17: '风机有功功率',
    18: '发电机水冷入口压力',
    19: '发电机水冷出口压力',
    20: '发电机无功功率',
    21: '发电机转速',
    22: '发电机水冷入口温度',
    23: '发电机水冷出口温度',
    24: '发电机驱动端轴承温度',
    25: '发电机非驱动端轴承温度',
    26: '发电机滑环室温度',
    27: '发电机定子U相线圈温度',
    28: '发电机定子V相线圈温度',
    29: '发电机定子W相线圈温度',
    30: '机舱水冷齿轮箱冷却液温度1min',
    31: '机舱水冷发电机冷却液温度1min',
    32: '机舱冷却系统水泵入口压力',
    33: '机舱水冷泵出口压力1min',
    34: '塔底水冷变频器入口水温',
    35: '塔底水冷变频器出口水温',
    36: '塔底湿度',
    37: '塔底水冷三通阀设置实际值',
    38: '塔底水冷三通阀反馈值',
    39: '塔外湿度',
    40: '塔底水冷泵入口压力',
    41: '塔底水冷泵出口压力',
    42: '舱内温度',
    43: '机舱控制柜温度',
    44: '舱外温度',
    45: '塔底温度',
    46: '塔底控制柜温度',
    47: '风向',
    48: '风速',
    49: '机舱中轴线与风向夹角',
    50: '变桨电池1电压',
    51: '变桨电池2电压',
    52: '变桨电池3电压',
    53: '变桨电机1电流',
    54: '变桨电机2电流',
    55: '变桨电机3电流',
    56: '1#桨电机温度',
    57: '2#桨电机温度',
    58: '3#桨电机温度',
    59: '变桨驱动器1温度',
    60: '变桨驱动器2温度',
    61: '变桨驱动器3温度',
    62: '轮毂内温度',
    63: '变桨电池柜1温度',
    64: '变桨电池柜2温度',
    65: '变桨电池柜3温度',
    66: '动力电缆温度监控1',
    67: '动力电缆温度监控2',
    68: '辅变绕组温度',
    69: '主变绕组温度',
    70: '齿轮箱入口油压',
    71: '齿轮箱油路滤网前油压',
    72: '液压站预充压力',
    73: '主轴转速',
    74: '齿轮箱油路入口温度',
    75: '齿轮箱散热器出口温度',
    76: '齿轮箱高速轴HSS_GSGS端轴承温度',
    77: '齿轮箱高速轴HSS_GSRS端轴承温度',
    78: '齿轮箱中速轴HSS_RS端轴承温度',
    79: '齿轮箱中速轴IMS_GSRS端轴承温度',
    80: '齿轮箱高速轴驱动端轴承温度',
    81: '齿轮箱高速轴非驱动端轴承温度',
    82: '齿轮箱油池温度',
    83: '齿轮箱中速轴驱动端轴承温度',
    84: '齿轮箱中速轴非驱动端轴承温度',
    85: '主轴承温度',
    86: '主轴承内圈温度',
    87: '主轴承外圈温度',
    88: '主控有功设置值',
    89: '主控无功设置值',
    90: '主控次要SC',
    91: '主控有功限值上限',
    92: '对外AI型状态1',
    93: '机舱侧向振动(已滤波)',
    94: '机舱轴向振动(已滤波)',
    95: '后方摩擦片距离',
    96: '前方摩擦片距离',
    97: '机舱角度',
    98: '机舱电流',
    99: '扭揽角度',
    100: '偏航电机转速1',
    101: '偏航电机转速2',
    102: '偏航角度'
}

common_address_conf = {
    1: '风场1',
    2: '风场2',
    3: '风场3',
    4: '风场4',
    5: '风场5',
    6: '风场6',
    7: '风场7',
    8: '风场8',
    9: '风场9',
    10: '风场10',
    11: '风场11',
    12: '风场12'
}


def pretty_print(message):
    print(datetime.datetime.now(), ":", message)


def exists_table(table_name):
    sql = f"SELECT * FROM information_schema.tables WHERE table_schema = 'appoint' AND table_name = '{table_name}'"
    pretty_print(sql)
    table_df = pd.read_sql_query(sql, engine)
    if table_df.empty:
        return False
    return True


if __name__ == '__main__':
    begin = datetime.datetime.now()
    pretty_print("开始执行")
    today_year_month = datetime.datetime.now().strftime('%Y%m')
    today = datetime.datetime.now().strftime('%d')
    yestoday = (datetime.datetime.now() - datetime.timedelta(days=1)).strftime('%Y%m%d')
    lastday = (datetime.datetime.now() - datetime.timedelta(days=2)).strftime('%Y%m%d')

    lastday_table = f'{lastday}_measurement'
    lastday_df = pd.DataFrame()
    if not exists_table(lastday_table):
        pretty_print(f"{lastday_table}表不存在")
    else:
        lastday_df_sql = f"SELECT * FROM `{lastday_table}` order by  id desc limit 10"
        pretty_print(lastday_df_sql)
        lastday_df = pd.read_sql_query(lastday_df_sql, engine)

    yestoday_table = f'{yestoday}_measurement'
    if not exists_table(yestoday_table):
        pretty_print(f"{yestoday_table}表不存在")
        raise Exception(f"{yestoday_table}表不存在")
    else:
        yestoday_df_sql = f"SELECT * FROM `{yestoday_table}`"
        pretty_print(yestoday_df_sql)
        yestoday_df = pd.read_sql_query(yestoday_df_sql, engine)