{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "### start to get data TJMCL120502305010 from 2021-12-09 10:00:00 to 2021-12-09 11:00:00\n",
      "# get data from 2021-12-09 10:00:00 to 2021-12-09 11:00:00......... \n",
      "all data-getting done, bms_count is 244, gps_count is 248, system_count is 0, accum_count is 0 \n",
      "\n",
      "### start to get data TJMCL120502305012 from 2021-12-09 10:00:00 to 2021-12-09 11:00:00\n",
      "# get data from 2021-12-09 10:00:00 to 2021-12-09 11:00:00......... \n",
      "all data-getting done, bms_count is 191, gps_count is 192, system_count is 0, accum_count is 0 \n",
      "\n",
      "### start to get data TJMCL120502305048 from 2021-12-09 10:00:00 to 2021-12-09 11:00:00\n",
      "# get data from 2021-12-09 10:00:00 to 2021-12-09 11:00:00......... \n",
      "all data-getting done, bms_count is 107, gps_count is 108, system_count is 0, accum_count is 0 \n",
      "\n",
      "### start to get data TJMCL120502305044 from 2021-12-09 10:00:00 to 2021-12-09 11:00:00\n",
      "# get data from 2021-12-09 10:00:00 to 2021-12-09 11:00:00......... \n",
      "all data-getting done, bms_count is 156, gps_count is 161, system_count is 0, accum_count is 0 \n",
      "\n",
      "### start to get data TJMCL120502305026 from 2021-12-09 10:00:00 to 2021-12-09 11:00:00\n",
      "# get data from 2021-12-09 10:00:00 to 2021-12-09 11:00:00......... \n",
      "all data-getting done, bms_count is 225, gps_count is 227, system_count is 0, accum_count is 0 \n",
      "\n",
      "### start to get data TJMCL120502305022 from 2021-12-09 10:00:00 to 2021-12-09 11:00:00\n",
      "# get data from 2021-12-09 10:00:00 to 2021-12-09 11:00:00......... \n",
      "all data-getting done, bms_count is 239, gps_count is 248, system_count is 0, accum_count is 0 \n",
      "\n",
      "### start to get data TJMCL120502305032 from 2021-12-09 10:00:00 to 2021-12-09 11:00:00\n",
      "# get data from 2021-12-09 10:00:00 to 2021-12-09 11:00:00......... \n",
      "all data-getting done, bms_count is 162, gps_count is 169, system_count is 0, accum_count is 0 \n",
      "\n",
      "### start to get data TJMCL120502305038 from 2021-12-09 10:00:00 to 2021-12-09 11:00:00\n",
      "# get data from 2021-12-09 10:00:00 to 2021-12-09 11:00:00......... \n",
      "all data-getting done, bms_count is 6, gps_count is 18, system_count is 0, accum_count is 0 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 获取数据\n",
    "import sys\n",
    "from LIB.BACKEND import DBManager\n",
    "import pymysql\n",
    "import time, datetime\n",
    "import dateutil.relativedelta\n",
    "import pandas as pd\n",
    "from LIB.BACKEND import DataPreProcess\n",
    "import V1_0_0.status_sta as status_sta\n",
    "dataPrePro = DataPreProcess.DataPreProcess()\n",
    "\n",
    "\n",
    "# 更新sn列表\n",
    "host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com'\n",
    "port=3306\n",
    "db='qixiang_oss'\n",
    "user='qx_algo_rw'\n",
    "password='qx@123456'\n",
    "conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(\"select sn, imei, add_time from app_device where status in (1,2,3)\")\n",
    "res = cursor.fetchall()\n",
    "df_sn = pd.DataFrame(res, columns=['sn', 'imei', 'add_time'])\n",
    "df_sn = df_sn.reset_index(drop=True)\n",
    "cursor.close()\n",
    "conn.close()\n",
    "df_sn['factory'] = '骑享'\n",
    "\n",
    "db = 'qx_cas'\n",
    "conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(\"select sn, factory from sn_list\")\n",
    "res = cursor.fetchall()\n",
    "df_sn2 = pd.DataFrame(res, columns=['sn', 'factory'])\n",
    "df_sn2['imei'] = \"\"\n",
    "df_sn = pd.concat([df_sn, df_sn2], axis=0)\n",
    "df_sn = df_sn.reset_index(drop=True)\n",
    "\n",
    "now_time = datetime.datetime.now()\n",
    "pre_time = now_time + dateutil.relativedelta.relativedelta(hours=-1)#上个月时间\n",
    "end_time=datetime.datetime.strftime(now_time,\"%Y-%m-%d %H:00:00\")\n",
    "start_time=datetime.datetime.strftime(pre_time,\"%Y-%m-%d %H:00:00\")\n",
    "\n",
    "dbManager = DBManager.DBManager()\n",
    "charge_count = 0\n",
    "drive_count = 0\n",
    "stand_count = 0\n",
    "charge_count_jinmao = 0\n",
    "drive_count_jinmao = 0\n",
    "stand_count_jinmao = 0\n",
    "for sn in df_sn['sn'].tolist():\n",
    "    if 'TJMCL'in sn: # 金茂\n",
    "        df_data = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time, data_groups=['bms', 'gps'])\n",
    "        # \n",
    "        df_bms = df_data['bms']\n",
    "        df_gps = df_data['gps']\n",
    "        factory = '金茂'\n",
    "        charge_count_jinmao, drive_count_jinmao, stand_count_jinmao = status_sta.status_sta(df_bms, df_gps, start_time, charge_count_jinmao, drive_count_jinmao, stand_count_jinmao, factory)\n",
    "# df_result = pd.DataFrame({'factory':['骑享'], 'time':[start_time], 'charge_count':[charge_count], 'drive_count':[drive_count], 'stand_count':stand_count})\n",
    "df_result = pd.DataFrame({'factory':['金茂'], 'time':[start_time], 'charge_count':[charge_count_jinmao], 'drive_count':[drive_count_jinmao], 'stand_count':stand_count_jinmao})\n",
    "\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>factory</th>\n",
       "      <th>time</th>\n",
       "      <th>charge_count</th>\n",
       "      <th>drive_count</th>\n",
       "      <th>stand_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>金茂</td>\n",
       "      <td>2021-12-09 10:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  factory                 time  charge_count  drive_count  stand_count\n",
       "0      金茂  2021-12-09 10:00:00             0            7            1"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0, 0, 0, 0, 0, 0]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bms['充电状态'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "4 in df_bms['充电状态'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0\n",
       "1    0\n",
       "2    0\n",
       "3    0\n",
       "4    0\n",
       "5    0\n",
       "Name: 充电状态, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bms['充电状态']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "b3ba2566441a7c06988d0923437866b63cedc61552a5af99d1f4fb67d367b25f"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 64-bit ('base': conda)",
   "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
}