{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time, datetime\n",
    "\n",
    "from apscheduler.schedulers.blocking import BlockingScheduler\n",
    "import pandas as pd\n",
    "\n",
    "from ZlwlAlgosCommon.utils.ProUtils import *\n",
    "from ZlwlAlgosCommon.service.iotp.IotpAlgoService import IotpAlgoService\n",
    "cur_env = 'dev' # 设置运行环境\n",
    "app_path = \"../../..\" # 设置相对路径\n",
    "app_name = \"schedule\" # 应用名, 建议与topic的后缀相同\n",
    "\n",
    "sysUtils = SysUtils(cur_env, app_path)\n",
    "\n",
    "mysqlUtils = MysqlUtils()\n",
    "mysql_iotp_params = sysUtils.get_cf_param('mysql-iotp')\n",
    "mysql_iotp_engine, mysql_iotp_Session= mysqlUtils.get_mysql_engine(mysql_iotp_params)\n",
    "mysql_iotp_conn = mysql_iotp_engine.connect()\n",
    "mysql_kw_params = sysUtils.get_cf_param('mysql-kewei')\n",
    "mysql_kw_engine, mysql_kw_Session= mysqlUtils.get_mysql_engine(mysql_kw_params)\n",
    "mysql_kw_conn = mysql_kw_engine.connect()\n",
    "\n",
    "kafka_params = sysUtils.get_cf_param('kafka')\n",
    "kafkaUtils = KafkaUtils()\n",
    "kafka_producer = kafkaUtils.get_kafka_producer(kafka_params, client_id=\"test\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = f\"select devcode as sn from ff_device where last_msg_time >= '2023-03-10 13:45:00'\"\n",
    "print(sql)\n",
    "if mysql_iotp_conn.closed:\n",
    "    mysql_iotp_conn = mysql_iotp_engine.connect()\n",
    "df_devcode = pd.read_sql(sql, mysql_iotp_conn)\n",
    "\n",
    "sql = f\"select sn, imei, pack_model, device_cell_type from t_device\"\n",
    "if mysql_kw_conn.closed:\n",
    "    mysql_kw_conn = mysql_kw_engine.connect()\n",
    "df_t_device = pd.read_sql(sql, mysql_kw_conn)\n",
    "sql = f\"select algo_id, pack_code, param from algo_adjustable_param\"\n",
    "df_algo_adjustable_param = pd.read_sql(sql, mysql_kw_conn)\n",
    "sql = f\"select pack_code, param from algo_pack_param\"\n",
    "df_algo_pack_param = pd.read_sql(sql, mysql_kw_conn)\n",
    "df_merge = pd.merge(df_devcode, df_t_device, on='sn', how='inner')\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'snlist': [{'sn': 'UD02030118B4C0016', 'imei': 'MGMCLN750M214L043'}, {'sn': 'UD02030118B4C0029', 'imei': 'MGMCLN750M214L009'}, {'sn': 'UD02030118B4C0025', 'imei': 'MGMCLN750M214L003'}, {'sn': 'UD02030118B4C0009', 'imei': 'MGMCLN750M214L024'}, {'sn': 'UD02030118B4C0021', 'imei': 'MGMCLN750M214L004'}, {'sn': 'UD02030118B4C0014', 'imei': 'MGMCLN750M214L021'}, {'sn': 'UD02030118B4C0013', 'imei': 'MGMCLN750M214L016'}, {'sn': 'UD02030118B4C0010', 'imei': 'MGMCLN750M214L020'}, {'sn': 'UD02030118B4C0001', 'imei': 'MGMCLN750M214L028'}, {'sn': 'UD02030118B4C0011', 'imei': 'MGMCLN750M214L039'}], 'adjustable_param': [{'algo_id': 1, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\"}}'}, {'algo_id': 2, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_vol\":\"4.15\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_vol\":\"4.18\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_vol\":\"4.2\"}}'}, {'algo_id': 3, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"under_vol\":\"3.2\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"under_vol\":\"3.15\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"under_vol\":\"3.1\"}}'}, {'algo_id': 4, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"vol_uni\":\"0.2\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"vol_uni\":\"0.4\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"vol_uni\":\"3\"}}'}, {'algo_id': 5, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\"}}'}, {'algo_id': 6, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\"}}'}, {'algo_id': 8, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"under_vol\":\"43.4\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"under_vol\":\"43.2\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"under_vol\":\"0\"}}'}, {'algo_id': 9, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"temp_uplmt\":\"119\",\"temp_lwlmt\":\"-35\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"temp_uplmt\":\"999\",\"temp_lwlmt\":\"-999\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"temp_uplmt\":\"9999\",\"temp_lwlmt\":\"-9999\"}}'}, {'algo_id': 10, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_temp\":\"50\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_temp\":\"52\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_temp\":\"55\"}}'}, {'algo_id': 11, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"under_temp\":\"-15\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"under_temp\":\"-20\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"under_temp\":\"-30\"}}'}, {'algo_id': 12, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"temp_uni\":\"5\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"temp_uni\":\"10\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"temp_uni\":\"15\"}}'}, {'algo_id': 13, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\"}}'}, {'algo_id': 14, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\", \"temp_rate\":\"10\"}, \"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\", \"temp_rate\":\"15\"}, \"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\", \"temp_rate\":\"20\"}}'}, {'algo_id': 15, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_crnt\":\"-25\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_crnt\":\"-23\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_crnt\":\"-30\"}}'}, {'algo_id': 16, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_crnt\":\"50\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_crnt\":\"60\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_crnt\":\"70\"}}'}, {'algo_id': 17, 'param': '{\"lvl1\":{\"jump_value\":\"10\"}}'}, {'algo_id': 18, 'param': '{\"lvl1\":{\"stuck_value\":\"10\"}}'}, {'algo_id': 19, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"low_ins\":\"700\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"low_ins\":\"500\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"low_ins\":\"250\"}}'}, {'algo_id': 20, 'param': '{\"lvl1\":{\"soh_low\":\"70\"}}'}, {'algo_id': 21, 'param': '{\"lvl1\":{\"soh_uni\":\"5\"}}'}, {'algo_id': 22, 'param': '{\"lvl1\":{\"inr_uni\":\"10\"}}'}, {'algo_id': 23, 'param': '{\"lvl1\":{\"inr_sap\":\"4\"}}'}, {'algo_id': 24, 'param': '{\"lvl1\":{\"innershrt_crnt\":\"-6\",\"innershrt_volt\":\"-6\"}}'}, {'algo_id': 25, 'param': '{}'}, {'algo_id': 26, 'param': '{\"lipltd_time_total\":\"4000\",\"lipltd_time_single\":\"60\",\"lipltd_count\":\"6\"}'}, {'algo_id': 27, 'param': '{}'}, {'algo_id': 28, 'param': '{}'}, {'algo_id': 29, 'param': '{\"lvl1\":{\"low_soc\":\"5\"},\"lvl2\":{\"low_soc\":\"3\"},\"lvl3\":{\"low_soc\":\"0\"}}'}, {'algo_id': 101, 'param': '{}'}, {'algo_id': 102, 'param': '{}'}, {'algo_id': 103, 'param': '{\"lvl1\":{\"volt_min\":\"5000\"},\"lvl2\":{\"volt_min\":\"1000\"}}'}, {'algo_id': 104, 'param': '{\"lvl1\":{\"volt_min\":\"3200\"},\"lvl2\":{\"volt_min\":\"3100\"},\"lvl3\":{\"volt_min\":\"2800\"}}'}, {'algo_id': 7, 'param': '{\"lvl1\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_vol\":\"84.5\"},\"lvl2\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_vol\":\"85.3\"},\"lvl3\":{\"confirm_time\":\"60\",\"remove_time\":\"60\",\"over_vol\":\"88\"}}'}, {'algo_id': 105, 'param': '{\"lvl1\":{\"temp2_diff\":\"40\"},\"lvl2\":{\"temp2_diff\":\"200\"}}'}], 'pack_param': [{'param': '{\"PackFullChrgVolt\":\"80\",\"CellFullChrgVolt\":\"4.2\",\"charge_ocv_v\":[3.152,3.397,3.438,3.481,3.523,3.560,3.586,3.604,3.620,3.638,3.661,3.693,3.748,3.803,3.853,3.903,3.953,4.006,4.063,4.121,4.183,4.253],\"charge_ocv_soc\":[0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105],\"discharge_ocv_v\":[3.152,3.397,3.438,3.481,3.523,3.560,3.586,3.604,3.620,3.638,3.661,3.693,3.748,3.803,3.853,3.903,3.953,4.006,4.063,4.121,4.183,4.253],\"discharge_ocv_soc\":[0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105],\"capacity\":\"50\",\"CellVoltTotalCount\":\"20\",\"CellTempTotalCount\":\"2\",\"soc_inflexion1\":\"30\",\"soc_inflexion2\":\"55\",\"soc_inflexion3\":\"65\",\"ocv_inflexion1\":\"3.281\",\"ocv_inflexion2\":\"3.297\",\"ocv_inflexion3\":\"3.325\",\"peak_soc\":\"60\",\"fullchrg_soc\":\"98\"}'}], 'pack_code': 'GM02010', 'cell_tpe': 'N'}\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "import json\n",
    "for (pack_code, cell_type), df in df_merge.groupby([\"pack_model\", \"device_cell_type\"]):\n",
    "    adjustable_param = df_algo_adjustable_param[df_algo_adjustable_param['pack_code']==pack_code].drop(['pack_code'], axis=1)\n",
    "    adjustable_param = adjustable_param.to_dict(\"records\")\n",
    "    pack_param = df_algo_pack_param[df_algo_pack_param['pack_code']==pack_code].drop(['pack_code'], axis=1)\n",
    "    pack_param = pack_param.to_dict(\"records\")\n",
    "    count = 0\n",
    "    count_max = 10\n",
    "    sn_list = []\n",
    "    for d in df.index:\n",
    "        sn = df.loc[d, 'sn']\n",
    "        imei = df.loc[d, 'imei']\n",
    "        sn_list.append({'sn':sn, 'imei':imei})\n",
    "        count = count + 1\n",
    "        if count >= count_max:\n",
    "            send_data = {'snlist':sn_list, 'adjustable_param':adjustable_param, 'pack_param':pack_param, 'pack_code':pack_code, 'cell_tpe':cell_type}\n",
    "            print(send_data)\n",
    "            kafka_producer.send('topic_test', bytes(json.dumps(send_data),'utf-8'))\n",
    "            count = 0\n",
    "            sn_list = []\n",
    "            break\n",
    "    break\n",
    "    print(pack_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for d in df.index:\n",
    "    print(df.loc[d, 'sn'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame().drop()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py3916",
   "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.9.16"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}