{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlwt\n",
    "from influxdb import InfluxDBClient\n",
    "import calendar\n",
    "import datetime\n",
    "import pytz\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 參數\n",
    "name=\"Allen\"\n",
    "year=2019\n",
    "month=5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 時區設定\n",
    "tw = pytz.timezone('Asia/Taipei')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 獲取當月第一天的星期與當月的總天數\n",
    "firstDayWeekDay, monthRange = calendar.monthrange(year, month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 獲取當月第一天跟最後一天 localtime\n",
    "firstDay = datetime.datetime(year=year, month=month, day=1,hour=0,minute=0,second=0)\n",
    "lastDay = datetime.datetime(year=year, month=month, day=monthRange,hour=23,minute=59,second=59)\n",
    "# 時區本地化\n",
    "tw_firstDay=tw.localize(firstDay)\n",
    "tw_lastDay=tw.localize(lastDay)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 轉換為utc時間\n",
    "utc_firstDay=tw_firstDay.astimezone(pytz.utc)\n",
    "utc_lastDay=tw_lastDay.astimezone(pytz.utc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 字串格式化轉為influxdb時間格式\n",
    "format_firstDay=utc_firstDay.strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "format_lastDay=utc_lastDay.strftime(\"%Y-%m-%d %H:%M:%S\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 建立influxdb連線\n",
    "client=InfluxDBClient(host='192.168.0.6',port=8086,username='root',password='xMd2k5aK',database='rd')\n",
    "# client=InfluxDBClient(host='192.168.5.17',port=8086,username='root',password='root',database='RD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "login_response=client.query(\"SELECT * FROM login_ WHERE time > '{0}' AND time < '{1}' AND \\\"name\\\" = '{2}'\".format(format_firstDay,format_lastDay,name))\n",
    "logout_response=client.query(\"SELECT * FROM logout_ WHERE time > '{0}' AND time < '{1}' AND \\\"name\\\" = '{2}'\".format(format_firstDay,format_lastDay,name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'series'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-60-4d6e74a66a90>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mlogin_history\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mlogin_response\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraw\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'series'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'values'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0mlogout_history\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mlogout_response\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraw\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'series'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'values'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'series'"
     ]
    }
   ],
   "source": [
    "login_history=login_response.raw['series'][0]['values']\n",
    "logout_history=logout_response.raw['series'][0]['values']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "def utc2local(strtime):\n",
    "    strtime=strtime.split('Z')[0]\n",
    "    strtime=strtime.split('.')[0]\n",
    "    dt=datetime.datetime.strptime(strtime,\"%Y-%m-%dT%H:%M:%S\")\n",
    "    loacl_time=dt.astimezone(pytz.timezone('Asia/Taipei'))\n",
    "    return loacl_time.strftime(\"%Y-%m-%d %H:%M:%S\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "log_json={}\n",
    "for login,logout in zip(login_history,logout_history):\n",
    "    local_login=utc2local(login[0])\n",
    "    local_logout=utc2local(logout[0])\n",
    "    \n",
    "    login_date,login_time=local_login.split(' ')\n",
    "    logout_date,logout_time=local_logout.split(' ')\n",
    "    \n",
    "    d1 = datetime.datetime.strptime(local_logout,'%Y-%m-%d %H:%M:%S')\n",
    "    \n",
    "    overtime=0\n",
    "    if(d1.hour ==17 and d1.minute >= 45):\n",
    "        overtime=0.5\n",
    "    elif(d1.hour >17 and d1.minute >=15):\n",
    "        overtime=d1.hour-17\n",
    "        if(d1.minute>=45):\n",
    "            overtime=overtime+0.5\n",
    "    \n",
    "    date_json={'login':login_time,'logout':logout_time,'overtime':overtime}\n",
    "    log_json[login_date]=date_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 新增Excel檔案\n",
    "workbook = xlwt.Workbook(encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 新建Sheet\n",
    "sheet=workbook.add_sheet(\"{0}月出勤考核表\".format(month))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 基本欄位\n",
    "sheet.write(0,0,\"姓名\")\n",
    "sheet.write(0,1,name)\n",
    "sheet.write(1,0,\"日期\")\n",
    "sheet.write(1,1,\"上班打卡時間\")\n",
    "sheet.write(1,2,\"下班打卡時間\")\n",
    "sheet.write(1,3,\"加班時數\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'login': '08:21:52', 'logout': '17:53:55', 'overtime': 0.5}\n"
     ]
    }
   ],
   "source": [
    "x,y=2,0\n",
    "for k in log_json:\n",
    "    sheet.write(x,0,k)\n",
    "    sheet.write(x,1,log_json[k]['login'])\n",
    "    sheet.write(x,2,log_json[k]['logout'])\n",
    "    sheet.write(x,3,log_json[k]['overtime'])\n",
    "    print(log_json[k])\n",
    "    x=x+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 存儲\n",
    "workbook.save(\"docs/{0}{1}月出勤紀錄.xlsx\".format(name,month))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(login_response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"2019-07-01\": {\"login\": \"08:21:52\", \"logout\": \"17:53:55\", \"overtime\": 0.5}}\n"
     ]
    }
   ],
   "source": [
    "print(json.dumps(log_json))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2019-07-01T00:21:52'"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'2019-07-01T00:21:52Z'.split('Z')[0].split('.')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 上下班時間編碼為字典\n",
    "def encode_logtime(log_history):\n",
    "    log_json={}\n",
    "    for log in log_history:\n",
    "        local_log=utc2local(log[0].split('.')[0])\n",
    "        log_date,log_time=local_log.split(' ')\n",
    "\n",
    "        if(log_date in log_json):\n",
    "            tmp=log_json[log_date]\n",
    "            if(type(tmp) == list):\n",
    "                tmp.append(log_time)\n",
    "                log_json[log_date]=tmp\n",
    "            else:\n",
    "                log_json[log_date]=[tmp,log_time]\n",
    "        else:\n",
    "            log_json[log_date]=log_time\n",
    "    return log_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "login_json=encode_logtime(login_history)\n",
    "logout_json=encode_logtime(logout_history)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
