{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import logging, json, xlsxwriter\n",
    "import pandas as pd\n",
    "from datetime import datetime as dt\n",
    "from datetime import timedelta as td\n",
    "from tb_rest_client.rest_client_ce import *\n",
    "from tb_rest_client.rest import ApiException"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "logging.basicConfig(level=logging.DEBUG,\n",
    "                    format='%(asctime)s - %(levelname)s - %(module)s - %(lineno)d - %(message)s',\n",
    "                    datefmt='%Y-%m-%d %H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ThingsBoard REST API URL\n",
    "url = \"https://hp.henrypump.cloud\"\n",
    "# Default Tenant Administrator credentials\n",
    "username = \"henry.pump.automation@gmail.com\"\n",
    "password = \"Henry Pump @ 2022\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getDevices(rest_client, customers,target_customer, page=0, pageSize=500):\n",
    "    for c in customers.data:\n",
    "        if c.name == target_customer:\n",
    "            cid = c.id.id\n",
    "    devices = rest_client.get_customer_devices(customer_id=cid, page_size=pageSize, page=page)\n",
    "    return devices.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getDeviceKeys(rest_client, devices,target_device):\n",
    "        try:\n",
    "            for d in devices['data']:\n",
    "                if d[\"name\"] == target_device:\n",
    "                    did = d['id']['id']\n",
    "                    eType = d['id']['entity_type']\n",
    "                    keys = rest_client.get_timeseries_keys_v1(entity_type=eType, entity_id=did)\n",
    "                    return eType, did, keys, None\n",
    "            return None,None,None,\"Device Not Found\"\n",
    "        except Exception as e:\n",
    "            print(e)\n",
    "            return (None, None, None, e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getTelemetry(rest_client, devices, keys, start_ts, end_ts, entity_type, entity_id, limit):\n",
    "    return rest_client.get_timeseries(entity_type=entity_type, entity_id=entity_id, keys=keys, start_ts=start_ts, end_ts=end_ts, limit=limit)\n",
    "     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getTime(timeRequest):\n",
    "    start_ts, end_ts = 0,0\n",
    "    if timeRequest[\"type\"] == \"last\":\n",
    "        now = dt.now()\n",
    "        delta = td(days=timeRequest[\"days\"], seconds=timeRequest[\"seconds\"], microseconds=timeRequest[\"microseconds\"], milliseconds=timeRequest[\"milliseconds\"], minutes=timeRequest[\"minutes\"], hours=timeRequest[\"hours\"], weeks=timeRequest[\"weeks\"])\n",
    "        start_ts = str(int(dt.timestamp(now - delta) * 1000))\n",
    "        end_ts = str(int(dt.timestamp(now) * 1000))\n",
    "    \n",
    "    return (start_ts, end_ts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getThingsBoardData(url, username, password, targetCustomer, timeRequest):\n",
    "    # Creating the REST client object with context manager to get auto token refresh\n",
    "    with RestClientCE(base_url=url) as rest_client:\n",
    "        try:\n",
    "            # Auth with credentials\n",
    "            rest_client.login(username=username, password=password)\n",
    "            # Get customers > get devices under a target customer > get keys for devices > get data for devices\n",
    "            customers = rest_client.get_customers(page_size=\"100\", page=\"0\")\n",
    "            devices = getDevices(rest_client=rest_client, customers=customers, target_customer=targetCustomer)\n",
    "            telemetry = {}\n",
    "            for d in devices['data']:\n",
    "                entity_type, entity_id, keys, err = getDeviceKeys(rest_client=rest_client, devices=devices, target_device=d['name'])\n",
    "                start_ts, end_ts = getTime(timeRequest)\n",
    "                telemetry[d['name']] = getTelemetry(rest_client=rest_client, devices=devices, keys=','.join(keys), start_ts=start_ts, end_ts=end_ts, entity_id=entity_id, entity_type=entity_type, limit=25000)\n",
    "            return telemetry\n",
    "        except ApiException as e:\n",
    "            logging.error(e)\n",
    "            return False\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def formatColumnName(telemetryName):\n",
    "   return \" \".join([x.capitalize() for x in telemetryName.split(\"_\")])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getDataFrame(telemetry, ignore_keys):    \n",
    "    df = pd.DataFrame()\n",
    "    #for location in telemetry.keys():\n",
    "    # Iterate through each datapoint within each location\n",
    "    for datapoint in telemetry.keys():\n",
    "        # Convert the datapoint list of dictionaries to a DataFrame\n",
    "        if datapoint not in ignore_keys:\n",
    "            temp_df = pd.DataFrame(telemetry[datapoint])\n",
    "            temp_df['ts'] = pd.to_datetime(temp_df['ts'], unit='ms').dt.tz_localize('UTC').dt.tz_convert('US/Central').dt.tz_localize(None)\n",
    "            # Set 'ts' as the index\n",
    "            temp_df.set_index('ts', inplace=True)\n",
    "            # Rename 'value' column to the name of the datapoint\n",
    "            temp_df.rename(columns={'value': formatColumnName(datapoint)}, inplace=True)\n",
    "            \n",
    "            # Join the temp_df to the main DataFrame\n",
    "            df = df.join(temp_df, how='outer')\n",
    "\n",
    "            df = df.fillna(method='ffill', limit=2)\n",
    "     # Rename index to 'Date'\n",
    "    df.rename_axis('Date', inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "time = {\n",
    "    \"type\": \"last\",\n",
    "    \"days\":3,\n",
    "    \"seconds\":0,\n",
    "    \"microseconds\":0,\n",
    "    \"milliseconds\":0,\n",
    "    \"minutes\":0,\n",
    "    \"hours\":0,\n",
    "    \"weeks\":0\n",
    "    }\n",
    "telemetry = getThingsBoardData(url, username, password, \"Faskens\", time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/nico/miniforge3/envs/thingsboard/lib/python3.10/site-packages/xlsxwriter/worksheet.py:3261: UserWarning: Must have at least one data row in in add_table()\n",
      "  warn(\"Must have at least one data row in in add_table()\")\n",
      "/Users/nico/miniforge3/envs/thingsboard/lib/python3.10/site-packages/xlsxwriter/worksheet.py:3261: UserWarning: Must have at least one data row in in add_table()\n",
      "  warn(\"Must have at least one data row in in add_table()\")\n",
      "/Users/nico/miniforge3/envs/thingsboard/lib/python3.10/site-packages/xlsxwriter/worksheet.py:3261: UserWarning: Must have at least one data row in in add_table()\n",
      "  warn(\"Must have at least one data row in in add_table()\")\n",
      "/Users/nico/miniforge3/envs/thingsboard/lib/python3.10/site-packages/xlsxwriter/worksheet.py:3261: UserWarning: Must have at least one data row in in add_table()\n",
      "  warn(\"Must have at least one data row in in add_table()\")\n",
      "/Users/nico/miniforge3/envs/thingsboard/lib/python3.10/site-packages/xlsxwriter/worksheet.py:3261: UserWarning: Must have at least one data row in in add_table()\n",
      "  warn(\"Must have at least one data row in in add_table()\")\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Create a Pandas Excel writer using XlsxWriter as the engine.\n",
    "writer = pd.ExcelWriter(\"/Users/nico/Documents/test/pandas_table.xlsx\", engine=\"xlsxwriter\",\n",
    "    datetime_format=\"yyyy-mm-dd hh:mm:ss\",\n",
    "    date_format=\"yyyy-mm-dd\",engine_kwargs={'options': {'strings_to_numbers': True}})\n",
    "chartsheet = writer.book.add_worksheet(\"Charts\")\n",
    "\n",
    "for device in telemetry.keys():\n",
    "    df = getDataFrame(telemetry[device], [\"yesterday_volume\"])\n",
    "    # Write the dataframe data to XlsxWriter. Turn off the default header and\n",
    "    # index and skip one row to allow us to insert a user defined header.\n",
    "    df.to_excel(writer, sheet_name=device, startrow=1, header=False, index=True)\n",
    "\n",
    "    # Get the xlsxwriter workbook and worksheet objects.\n",
    "    workbook = writer.book\n",
    "    worksheet = writer.sheets[device]\n",
    "\n",
    "    # Get the dimensions of the dataframe.\n",
    "    (max_row, max_col) = df.shape\n",
    "    \n",
    "    # Create a list of column headers, to use in add_table().\n",
    "    column_settings = [{\"header\": column} for column in df.columns]\n",
    "    # Add the Excel table structure. Pandas will add the data.\n",
    "    worksheet.add_table(0, 0, max_row, max_col, {\"columns\": [{'header': 'Date'}] + column_settings})\n",
    "\n",
    "    # Make the columns wider for clarity.\n",
    "    worksheet.set_column(0, max_col , 18)\n",
    "charts = []#[{\"chartType\": \"line\",\"columnName\": \"temperature\"}, {\"chartType\": \"line\", \"columnName\": \"volume_flow\"},{\"chartType\": \"bar\", \"columnName\": \"volume_flow\"},{\"chartType\": \"pie\", \"columnName\": \"today_volume\"},{\"chartType\": \"pie\", \"columnName\": \"month_volume\"}]\n",
    "position = 1\n",
    "pie_chart_count = 1\n",
    "for c in charts:\n",
    "    # Configure the chart. In simplest case we add one or more data series.\n",
    "    chart = writer.book.add_chart({'type': c[\"chartType\"]})\n",
    "    if c[\"chartType\"] == \"pie\":\n",
    "        pieWorksheet = writer.book.add_worksheet(\"Pie Chart \" + str(pie_chart_count))\n",
    "        pieData = []\n",
    "        \n",
    "    for device in telemetry.keys():\n",
    "        #print(device)\n",
    "        dataColumn = df.columns.get_loc(formatColumnName(c[\"columnName\"])) + 1\n",
    "        if c[\"chartType\"] in [ \"line\", \"bar\"]:\n",
    "            chart.add_series({\n",
    "                'values': [device, 1, dataColumn ,max_row,dataColumn],\n",
    "                'categories': [device, 1,0,max_row,0],\n",
    "                'name': device,\n",
    "\n",
    "            })\n",
    "            chart.set_x_axis({'text_axis': True})\n",
    "        elif c[\"chartType\"] == \"pie\":\n",
    "            #pie charts don't allow data from multiple worksheets need to make a new worksheet with all the desired data so add the data to the worksheet here\n",
    "            pieData.append([device, \"='\" + device + \"'!$\" + chr(65+dataColumn) + \"$\" + str(max_row)])\n",
    "    if c[\"chartType\"] == \"pie\":\n",
    "        for row in range(len(pieData)):\n",
    "            pieWorksheet.write_row(row, 0, pieData[row])\n",
    "        chart.add_series({\n",
    "                'values': [\"Pie Chart \" + str(pie_chart_count), 0, 1 ,len(pieData)-1,1],\n",
    "                'categories': [\"Pie Chart \" + str(pie_chart_count), 0, 0 ,len(pieData)-1,0],\n",
    "                'name': device,\n",
    "                'data_labels': {'value': True, 'category': True, 'position': 'best_fit'}\n",
    "            })\n",
    "        pie_chart_count += 1\n",
    "    chart.set_title({\"name\": formatColumnName(c[\"columnName\"])})\n",
    "        \n",
    "    chartsheet.insert_chart('A' + str(position), chart, {'x_scale': 3, 'y_scale': 2})\n",
    "    position += 30\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "thingsboard",
   "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.10.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
