import numpy as np
import pandas as pd
import datetime,time
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from urllib import parse

def offline_alarm(db_iotp_engine, db_qxcas_engine, ignore_sns):

    sql = "select t2.sn, t2.status_time as last_time, t2.offline_time_hour from " + \
        "(SELECT t.*,t1.devcode,t1.update_time,t1.status_time,TIMESTAMPDIFF(hour, status_time, now()) as offline_time_hour from operation_sn t LEFT JOIN " + \
        "ff_battery_status t1 ON(t.sn = t1.devcode)) t2 where t2.offline_time_hour > 72 order by t2.offline_time_hour"
    df_alarm_lastday = pd.read_sql(sql, db_iotp_engine)
    df_alarm_lastday = df_alarm_lastday[['sn', 'last_time']]
    df_alarm_lastday = df_alarm_lastday[~df_alarm_lastday['sn'].isin(ignore_sns)]
    df_alarm_lastday['level'] = 1
    df_alarm_lastday['add_time'] = datetime.datetime.now()
    df_alarm_lastday.to_sql("offline_info", db_qxcas_engine,if_exists="append", index=False)