{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime,time\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from urllib import parse\n",
    "import pymysql\n",
    "host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'\n",
    "port=3306\n",
    "db='qx_cas'\n",
    "user='qx_algo_rw'\n",
    "password='qx@123456'\n",
    "\n",
    "db_qxcas_engine = create_engine(\n",
    "    \"mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8\".format(\n",
    "        user, parse.quote_plus(password), host, port, db\n",
    "    ))\n",
    "\n",
    "db = 'safety_platform'\n",
    "conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)\n",
    "cursor = conn.cursor()\n",
    "db_sp_engine = create_engine(\n",
    "    \"mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8\".format(\n",
    "        user, parse.quote_plus(password), host, port, db\n",
    "    ))\n",
    "sql = \"select sn, DATE_FORMAT(time, '%%Y-%%m-%%d %%H:%%i:%%s') as time, level from lowsoc_info where add_time >DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%%Y-%%m-%%d 00:00:00')\"+ \\\n",
    "    \" and  add_time <DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%%Y-%%m-%%d 23:59:59')\"           \n",
    "df_lowsocalarm_lastday = pd.read_sql(sql, db_qxcas_engine)\n",
    "sn_lowsoc = df_lowsocalarm_lastday['sn'].tolist()\n",
    "\n",
    "sql = \"select sn, DATE_FORMAT(add_time, '%%Y-%%m-%%d %%H:%%i:%%s') as time, level, last_time from offline_info where add_time >= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%%Y-%%m-%%d 00:00:00')\"+ \\\n",
    "    \" and add_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%%Y-%%m-%%d 23:59:59')\"           \n",
    "df_offlinealarm_lastday = pd.read_sql(sql, db_qxcas_engine)\n",
    "sn_offline = df_offlinealarm_lastday['sn'].tolist()\n",
    "\n",
    "sql = \"select * from all_fault_info_copy1 where end_time='{}' and code={}\".format('0000-00-00 00:00:00', 58)\n",
    "df_all_alarm_info = pd.read_sql(sql, db_sp_engine)\n",
    "sn_allalarm = df_all_alarm_info['product_id'].tolist()\n",
    "\n",
    "# 遍历昨日报警和报警列表，如果 1：sn存在于昨日报警，但不存在与报警allinfo里，则添加一条新的报警记录到allinfo里；\n",
    "#                           2：如果sn存在于allinfo但不存在于昨日报警，也不存在于昨日离线，则记录故障为结束；\n",
    "#                           3： 如果sn在两个表中都存在，但级别不同，则更新级别（离线暂时不考虑级别）。\n",
    "sn_new = list(set(sn_lowsoc)-set(sn_allalarm))\n",
    "sn_close = list(set(sn_allalarm)-set(sn_lowsoc)-set(sn_offline))\n",
    "# sn_cross = list(set(sn_lowsoc) & set(sn_allalarm))\n",
    "# 新增\n",
    "df_new = pd.DataFrame(columns=['add_time', 'product_id', 'start_time', 'end_time', 'code'])\n",
    "now = datetime.datetime.now()\n",
    "for sn in sn_new:\n",
    "    df = df_lowsocalarm_lastday[df_lowsocalarm_lastday['sn']==sn]\n",
    "    df_new = df_new.append({'add_time':now, 'product_id':sn, 'start_time':df['time'].values[0], 'end_time':'0000-00-00 00:00:00', 'code':58}, ignore_index=True)\n",
    "if not df_new.empty:\n",
    "    df_new.to_sql('all_fault_info_copy1', db_sp_engine, if_exists='append', index=False)\n",
    "\n",
    "# 修改\n",
    "now = datetime.datetime.now()\n",
    "for sn in sn_close:\n",
    "    df = df_lowsocalarm_lastday[df_lowsocalarm_lastday['sn']==sn]\n",
    "    df1 = df_all_alarm_info[df_all_alarm_info['product_id']==sn]\n",
    "    sql = ''' update all_fault_info_copy1 set update_time='{}', end_time='{}' where product_id = '{}' and start_time = '{}' and code = {}'''.format \\\n",
    "                (now, datetime.datetime.strftime(now,\"%Y-%m-%d %H:%M:%S\"), sn, pd.to_datetime(df1['start_time'].values[0]), df1['code'].values[0])\n",
    "    cursor.execute(sql)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sn_close"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "b3ba2566441a7c06988d0923437866b63cedc61552a5af99d1f4fb67d367b25f"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 64-bit ('base': conda)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}