{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import lattice\n", "import logging\n", "import json\n", "\n", "logger = logging.getLogger('billing_reports')\n", "logger.setLevel(logging.INFO)\n", "\n", "fh = logging.FileHandler('/Users/nico/Documents/Github/hp-billing-report/billing.log')\n", "fh.setLevel(logging.INFO)\n", "\n", "ch = logging.StreamHandler()\n", "ch.setLevel(logging.ERROR)\n", "\n", "formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')\n", "fh.setFormatter(formatter)\n", "ch.setFormatter(formatter)\n", "\n", "logger.addHandler(fh)\n", "logger.addHandler(ch)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "#Mistaway data collection\n", "from datetime import datetime as dt\n", "\n", "json_file_output = False\n", "console_output = False" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "nodes = lattice.getNodes()" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "# Find a value for a given key in a given dictionary\n", "def _findItem(obj, key):\n", " if key in obj: return obj[key] \n", " for k, v in obj.items():\n", " if isinstance(v,dict):\n", " item = _findItem(v, key)\n", " if item is not None:\n", " return item" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "# recursively go through folders to build folder structure\n", "def putFolder(folder, fs):\n", " try:\n", " if not folder[\"id\"] == folder[\"parentFolderId\"]:\n", " parent = _findItem(fs, folder[\"parentFolderId\"])\n", " parent[folder[\"id\"]] = folder\n", " putFolder(parent,fs)\n", " else:\n", " fs[folder[\"id\"]] = folder\n", " return fs\n", " except Exception as e:\n", " logger.error(f\"Exception in putFolder: {e}\")\n", " " ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "2023-09-06 09:37:18,257 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,258 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,259 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,260 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,261 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,262 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,262 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,263 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,263 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,264 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,265 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,266 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,267 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,267 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,268 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,269 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,269 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,270 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,270 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,271 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,272 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,272 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,273 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,273 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,274 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,275 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,276 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,276 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,277 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,277 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,278 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,278 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,279 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,280 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,280 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,281 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,282 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,282 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,283 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,283 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,284 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,285 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,286 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,286 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,287 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,288 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,289 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,289 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,290 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,291 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,293 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,293 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,294 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,295 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,295 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,296 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,296 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,297 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,297 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,298 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,299 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,299 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,300 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,301 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,301 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,302 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,303 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,304 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,305 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,305 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,307 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,308 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,310 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,311 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,312 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,313 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,314 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,315 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,320 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,321 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,321 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,322 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,323 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,323 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,324 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,325 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,325 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,326 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,327 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,327 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,336 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n", "2023-09-06 09:37:18,336 - billing_reports - ERROR - Exception in putFolder: 'NoneType' object does not support item assignment\n" ] } ], "source": [ "# Go through every folder and build a proper folder structure\n", "# Output to JSON file\n", "folders = lattice.getFolders()\n", "foldermap = {}\n", "#print(json.dumps(folders, indent=4))\n", "for folder in folders:\n", " logger.debug(folder)\n", " putFolder(folder,foldermap)\n", "if console_output:\n", " print(json.dumps(foldermap, indent=4))\n", "if json_file_output:\n", " with open(\"./folderMap\" + str(dt.date(dt.now())) +\".json\", \"w\") as f:\n", " json.dump(foldermap, f, indent=4)" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "# Go through every node and collect unique device id (MAC)\n", "# Output to JSON file\n", "foldersTracker = []\n", "vanityMap = {}\n", "deviceTypes = {}\n", "for type in lattice.getNodeTypes():\n", " deviceTypes[type[\"id\"]] = type[\"name\"]\n", "for node in nodes:\n", " if not node[\"uniqueId\"][-6:] in [\":00:00\", \":00:30\"]:\n", " if not node[\"folderId\"] in foldersTracker:\n", " foldersTracker.append(node[\"folderId\"])\n", " folder = _findItem(foldermap, node[\"folderId\"])\n", " if folder:\n", " deviceName = folder[\"name\"]\n", " latitude = folder[\"location\"][\"lat\"]\n", " longitude = folder[\"location\"][\"lng\"]\n", " pfolder = _findItem(foldermap, folder[\"parentFolderId\"])\n", " ppfolder = _findItem(foldermap, pfolder[\"parentFolderId\"])\n", " customer = ppfolder[\"name\"]\n", " vanityMap[node[\"uniqueId\"]] = {\"deviceName\": deviceName, \"deviceType\": deviceTypes[node[\"nodeTypeId\"]], \"customer\": customer, \"latitude\": latitude, \"longitude\": longitude}\n", " else:\n", " logger.info(\"Folder does not exist: \" + str(node[\"folderId\"]))\n", " else:\n", " logger.info(\"Folder already in list: \" + str(node[\"folderId\"]))\n", "#print(vanityMap)\n", "if json_file_output:\n", " with open(\"./deviceMap\" + str(dt.date(dt.now())) +\".json\", \"w\") as f:\n", " json.dump(vanityMap, f, indent=4)\n" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'\\nData to be collected:\\n {\\n \"customer\":{\\n \"sales_order\": {\\n \"billable_item_1\": {\\n \"sales_price\": 75,\\n \"platform_cost\": 10,\\n \"platform\": \"thingsboard\", # \"thingsboard\", \"mistaway\"\\n \"cellular_cost\": 15,\\n \"billing_type\": \"stand-alone\" # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\\n },\\n \"billable_item_2:{...},\\n ...\\n },\\n \"sales_order_2\":{...}\\n },\\n \"customer_2\":{...}\\n }\\n'" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\"\"\"\n", "Data to be collected:\n", " {\n", " \"customer\":{\n", " \"sales_order\": {\n", " \"billable_item_1\": {\n", " \"sales_price\": 75,\n", " \"platform_cost\": 10,\n", " \"platform\": \"thingsboard\", # \"thingsboard\", \"mistaway\"\n", " \"cellular_cost\": 15,\n", " \"billing_type\": \"stand-alone\" # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " },\n", " \"billable_item_2:{...},\n", " ...\n", " },\n", " \"sales_order_2\":{...}\n", " },\n", " \"customer_2\":{...}\n", " }\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "mistaway_data = {}\n", "from shapely.geometry import Point, Polygon\n", "\n", "denali_east = Polygon([(31.441289, -102.175343),(31.467676, -101.936571), (31.321496, -101.943604),(31.304714, -102.139878) ])\n", "jitterbug = []\n", "for key, value in vanityMap.items():\n", " customer = value[\"customer\"]\n", " device = value[\"deviceName\"]\n", " type = value[\"deviceType\"]\n", " location = Point(value[\"latitude\"], value[\"longitude\"])\n", " if not device in [\"Melinda 252\"]:\n", " cellular_cost = 20\n", " so = \"HPSO-1\"\n", " price = 75\n", " billing_type = \"Stand-Alone\"\n", " if customer not in mistaway_data:\n", " mistaway_data[customer] = {}\n", " \n", " if customer == \"CrownQuest\":\n", " if device == \"LimeQuest 6 SR 1-1\":\n", " so = \"LimeQuest SO\"\n", " price = 75\n", " billing_type = \"Stand-Alone\"\n", " elif \"Wilkinson 39\" in device or device in [\"Wilkinson 37 WS 1-9B\", \"Wilkinson 37 WS 1-9B\", \"Free 40 WS 1-2\", \"Free 40 WS 1-4\", \"Free 40 WS 1-1\"]:\n", " so = \"Wilkinson 39 Field\"\n", " price = 0\n", " billing_type = \"AP-bundled\"\n", " cellular_cost = 0\n", " if so not in mistaway_data[customer]:\n", " mistaway_data[customer][so] = {}\n", " if \"Wilkinson 39 AP\" not in mistaway_data[customer][so]:\n", " mistaway_data[customer][so][\"Wilkinson 39 AP\"] = {\n", " \"Sales Price\": 250,\n", " \"Platform Cost\": 0,\n", " \"Platform\": \"Mistaway\", # \"thingsboard\", \"mistaway\"\n", " \"Cellular Cost\": 20,\n", " \"Billing Type\": \"AP\" # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " }\n", " elif \"Wilkinson 37\" in device or device in [\"Wilkinson 33 WS 3-1\", \"Wilkinson 33 WS 4-1\", \"Wilkinson 34 WS 2-10\", \"Wilkinson 34 WS 1-8\"]:\n", " so = \"Wilkinson 37 Field\"\n", " price = 0\n", " billing_type = \"AP-bundled\"\n", " cellular_cost = 0\n", " if so not in mistaway_data[customer]:\n", " mistaway_data[customer][so] = {}\n", " if \"Wilkinson 37 AP\" not in mistaway_data[customer][so]:\n", " mistaway_data[customer][so][\"Wilkinson 37 AP\"] = {\n", " \"Sales Price\": 250,\n", " \"Platform Cost\": 0,\n", " \"Platform\": \"Mistaway\", # \"thingsboard\", \"mistaway\"\n", " \"Cellular Cost\": 20,\n", " \"Billing Type\": \"AP\" # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " }\n", " elif \"Wilkinson 33\" in device or \"Wilkinson 34\" in device or \"Wilkinson 1\" in device or \"Wilkinson 4\" in device:\n", " so = \"Wilkinson 33-34 Field\"\n", " price = 0\n", " billing_type = \"AP-bundled\"\n", " cellular_cost = 0\n", " if so not in mistaway_data[customer]:\n", " mistaway_data[customer][so] = {}\n", " if \"Wilkinson 33-34 AP\" not in mistaway_data[customer][so]:\n", " mistaway_data[customer][so][\"Wilkinson 33-34 AP\"] = {\n", " \"Sales Price\": 250,\n", " \"Platform Cost\": 0,\n", " \"Platform\": \"Mistaway\", # \"thingsboard\", \"mistaway\"\n", " \"Cellular Cost\": 20,\n", " \"Billing Type\": \"AP\" # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " }\n", " elif \"Free 40\" in device or \"Free 32\" in device:\n", " so = \"Free Field\"\n", " price = 0\n", " billing_type = \"AP-bundled\"\n", " cellular_cost = 0\n", " if so not in mistaway_data[customer]:\n", " mistaway_data[customer][so] = {}\n", " if \"Free AP\" not in mistaway_data[customer][so]:\n", " mistaway_data[customer][so][\"Free AP\"] = {\n", " \"Sales Price\": 250,\n", " \"Platform Cost\": 0,\n", " \"Platform\": \"Mistaway\", # \"thingsboard\", \"mistaway\"\n", " \"Cellular Cost\": 20,\n", " \"Billing Type\": \"AP\" # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " }\n", " elif \"LimeQuest 5\" in device or \"LimeQuest 10\" in device:\n", " so = \"LimeQuest Field\"\n", " price = 0\n", " billing_type = \"AP-bundled\"\n", " cellular_cost = 0\n", " if so not in mistaway_data[customer]:\n", " mistaway_data[customer][so] = {}\n", " if \"LimeQuest AP\" not in mistaway_data[customer][so]:\n", " mistaway_data[customer][so][\"LimeQuest AP\"] = {\n", " \"Sales Price\": 250,\n", " \"Platform Cost\": 0,\n", " \"Platform\": \"Mistaway\", # \"thingsboard\", \"mistaway\"\n", " \"Cellular Cost\": 20,\n", " \"Billing Type\": \"AP\" # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " }\n", " elif \"LimeQuest 5\" in device or \"LimeQuest 10\" in device:\n", " so = \"LimeQuest Field\"\n", " price = 0\n", " billing_type = \"AP-bundled\"\n", " cellular_cost = 0\n", " if so not in mistaway_data[customer]:\n", " mistaway_data[customer][so] = {}\n", " if \"LimeQuest AP\" not in mistaway_data[customer][so]:\n", " mistaway_data[customer][so][\"LimeQuest AP\"] = {\n", " \"Sales Price\": 250,\n", " \"Platform Cost\": 0,\n", " \"Platform\": \"Mistaway\", # \"thingsboard\", \"mistaway\"\n", " \"Cellular Cost\": 20,\n", " \"Billing Type\": \"AP\" # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " }\n", " elif \"Horton 23\" in device or \"Horton 34\" in device:\n", " so = \"Horton Field\"\n", " price = 0\n", " billing_type = \"AP-bundled\"\n", " cellular_cost = 0\n", " if so not in mistaway_data[customer]:\n", " mistaway_data[customer][so] = {}\n", " if \"Horton AP\" not in mistaway_data[customer][so]:\n", " mistaway_data[customer][so][\"Horton AP\"] = {\n", " \"Sales Price\": 250,\n", " \"Platform Cost\": 0,\n", " \"Platform\": \"Mistaway\", # \"thingsboard\", \"mistaway\"\n", " \"Cellular Cost\": 20,\n", " \"Billing Type\": \"AP\" # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " } \n", " elif type == \"advvfdipp\" or type == \"ipp\":\n", " so = \"Santa Rosa\"\n", " elif type == \"rigpump\":\n", " so = \"Rig Pump\"\n", " elif type == \"transferlite\":\n", " so = \"Transfer\"\n", " elif customer == \"Patriot Resources\":\n", " so = \"Patriot Resources\"\n", " price = 100\n", " elif customer == \"Keagan Faudree Water\":\n", " so = \"Keagan Faudree Water\"\n", " elif customer == \"Summit Petroleum\":\n", " if location.x >= 31.654963:\n", " so = \"Banay\"\n", " price = 75\n", " elif location.within(denali_east):\n", " so = \"Denali East\"\n", " if type == \"dual_flowmeter\":\n", " price = 75\n", " elif type == \"plcfreshwater\":\n", " price = 55\n", " cellular_cost = 0\n", " billing_type = \"Networked\"\n", " else: #if location.within(jitterbug):\n", " so = \"Jitterbug\"\n", " if type == \"dual_flowmeter\":\n", " price = 75\n", " elif type == \"plcfreshwater\":\n", " price = 55\n", " cellular_cost = 0\n", " billing_type = \"Networked\"\n", " \n", " \n", " if so not in mistaway_data[customer]:\n", " mistaway_data[customer][so] = {}\n", "\n", " mistaway_data[customer][so][device] = {\n", " \"Sales Price\": price,\n", " \"Platform Cost\": 10,\n", " \"Platform\": \"Mistaway\", # \"thingsboard\", \"mistaway\"\n", " \"Cellular Cost\": cellular_cost,\n", " \"Billing Type\": billing_type # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " }\n", " \n", " \n", " " ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "#ThingsBoard data collection\n", "from tb_rest_client.rest_client_ce import *\n", "from tb_rest_client.rest import ApiException" ] }, { "cell_type": "code", "execution_count": 11, "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": 12, "metadata": {}, "outputs": [], "source": [ "def getDevices(rest_client, customers, page=0, pageSize=500):\n", " thingsboard_data = {}\n", " for c in customers.data:\n", " cname = c.name\n", " cid = c.id.id\n", " if cname not in [\"Test Company\", \"Amerus Safety Solutions\"]:\n", " #create new company if it doesn't exist\n", " if cname not in thingsboard_data:\n", " thingsboard_data[cname] = {}\n", " #get devices of a company\n", " devices = rest_client.get_customer_devices(customer_id=cid, page_size=pageSize, page=page)\n", " #go through each device and store its data in the dict\n", " for device in devices.data:\n", " cellular_cost = 20\n", " #fix naming to work with JSON/dict\n", " if '\"' in device.name:\n", " deviceName = device.name.replace('\"', 'in')\n", " else:\n", " deviceName = device.name\n", " # Sort Device details\n", " if cname == \"Chuda Resources\":\n", " so = \"Water Wells\"\n", " price = 75\n", " billing_type = \"Stand-Alone\"\n", " elif cname == \"Henry Petroleum\":\n", " so = \"Check Meters\"\n", " price = 50\n", " billing_type = \"Stand-Alone\"\n", " elif cname == \"Faskens\":\n", " if device.type == \"tankalarms\":\n", " so = \"Tanks\"\n", " billing_type = \"Stand-Alone\"\n", " price = 50\n", " elif device.type in [\"advvfdipp\", \"plcfreshwater\"]:\n", " so = \"Water Wells\"\n", " billing_type = \"Stand-Alone\"\n", " price = 50\n", " elif device.type == \"plcpond\":\n", " so = \"Ponds\"\n", " billing_type = \"Stand-Alone\"\n", " price = 50\n", " else:\n", " so = \"HPSO-1\"\n", " billing_type = \"Stand-Alone\"\n", " price = 50\n", " elif cname == \"Henry Resources\":\n", " if deviceName == \"Pearl Central\":\n", " so = \"Henry Resources\"\n", " price = 300\n", " billing_type = \"Stand-Alone-WiFi\"\n", " else:\n", " so = \"Henry Resources\"\n", " price = 275\n", " billing_type = \"Stand-Alone\"\n", " elif cname == \"ConocoPhillips\":\n", " if device.type == \"flowmeterskid\":\n", " so = \"Portable Meter\"\n", " price = 50\n", " billing_type = \"Stand-Alone\"\n", " elif device.type == \"plcfreshwater\":\n", " so = \"Water Well\"\n", " price = 50\n", " cellular_cost = 0\n", " billing_type = \"Networked\"\n", " elif device.type == \"advvfdipp\":\n", " so = \"Santa Rosa\"\n", " price = 50\n", " billing_type = \"Stand-Alone\"\n", " elif device.type == \"Gateway\":\n", " so = \"AP\"\n", " price = 0\n", " billing_type = \"AP-bundled\"\n", " elif device.type == \"cpdualflowmeter\":\n", " so = \"Pond/Flowmeter\"\n", " price = 50\n", " billing_type = \"Networked\"\n", " elif cname == \"Saulsbury Ventures\":\n", " so = \"Saulsbury Ventures\"\n", " price = 50\n", " billing_type = \"Stand-Alone\"\n", " else:\n", " so = \"HPSO-1\"\n", " price = 50\n", " billing_type = \"Stand-Alone\"\n", " \n", " #make a new Sales Order if it doesn't exist\n", " if so not in thingsboard_data[cname]:\n", " thingsboard_data[cname][so] = {}\n", " #add device to Sales Order under Company\n", " thingsboard_data[cname][so][deviceName] = {\n", " \"Sales Price\": price,\n", " \"Platform Cost\": 0,\n", " \"Platform\": \"ThingsBoard\", # \"thingsboard\", \"mistaway\"\n", " \"Cellular Cost\": cellular_cost,\n", " \"Billing Type\": billing_type # \"stand-alone\", \"AP\", \"AP-bundled\", \"networked\", \"stand-alone-wifi\"\n", " }\n", " return thingsboard_data" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "def getThingsBoardData(url, username, password):\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", " thingsboard_data = getDevices(rest_client=rest_client, customers=customers)\n", " return thingsboard_data\n", " except ApiException as e:\n", " logger.error(e)\n", " return False" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "thingsboard_data = getThingsBoardData(url, username,password)" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": [ "#Mixing data from Mistaway and ThingsBoard\n", "excel_data = {}\n", "excel_data.update(mistaway_data)\n", "for customer in thingsboard_data.keys():\n", " if customer in excel_data:\n", " for so in thingsboard_data[customer].keys():\n", " if so in excel_data[customer]:\n", " excel_data[customer][so].update(thingsboard_data[customer][so])\n", " else:\n", " excel_data[customer].update(thingsboard_data[customer])\n", " else:\n", " excel_data[customer] = thingsboard_data[customer]\n" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [], "source": [ "#Build report from data\n", "import xlsxwriter\n", "import pandas as pd\n", "\n", "# Create a new Excel writer object\n", "with pd.ExcelWriter(f\"Billing-Report-{dt.date(dt.now())}.xlsx\", engine=\"xlsxwriter\") as writer:\n", " # Formats\n", " bold_format = writer.book.add_format({'bold': True})\n", " header_format = writer.book.add_format({'bold': True, 'center_across': True, 'border': True, 'bottom': True})\n", " currency_format = writer.book.add_format({'num_format': \"[$$-409]#,##0.00\"})\n", " bold_currency_format = writer.book.add_format({'num_format': \"[$$-409]#,##0.00\", 'bold': True})\n", " highlight_format = writer.book.add_format({'bg_color': \"yellow\"})\n", " # Setting up overview sheet\n", " overview = writer.book.add_worksheet(\"Overview\")\n", " overview_row = 1\n", " overview.write(0,0, \"Customer\", header_format)\n", " overview.write(0,1, \"Revenue\", header_format)\n", " overview.write(0,2, \"Platform Cost\", header_format)\n", " overview.write(0,3, \"Cellular Cost\", header_format)\n", " overview.write(0,4, \"Profit\", header_format)\n", "\n", " \n", " # Loop through customers\n", " for customer, orders in excel_data.items():\n", " rows = []\n", " counts = {}\n", " # Loop through each sales order for the customer\n", " for order, items in orders.items():\n", " for item, details in items.items():\n", " row = {\n", " \"Sales Order\": order,\n", " \"Location\": item\n", " }\n", " row.update(details)\n", " rows.append(row)\n", " counts[order] = len(items)\n", " \n", " # Convert the data to a DataFrame\n", " df = pd.DataFrame(rows)\n", " \n", " # Sort by sales order\n", " df = df.sort_values(by=\"Sales Order\")\n", " \n", " # Write to a specific sheet in the Excel file\n", " df.to_excel(writer, sheet_name=customer, index=False)\n", " for column in df:\n", " column_length = max(df[column].astype(str).map(len).max(), len(column))\n", " col_idx = df.columns.get_loc(column)\n", " if col_idx in [2,3,5]:\n", " writer.sheets[customer].set_column(col_idx, col_idx, column_length, currency_format)\n", " else:\n", " writer.sheets[customer].set_column(col_idx, col_idx, column_length)\n", " \n", " # Get the dimensions of the dataframe.\n", " (max_row, max_col) = df.shape\n", " #Apply highlighting\n", " writer.sheets[customer].conditional_format(f\"G2:G{max_row+1}\", {\"type\": \"text\", \"criteria\": \"not containing\", \"value\": \"AP-bundled\", \"format\": highlight_format})\n", "\n", " #writer.sheets[customer].set_column(2,3,None,currency_format)\n", " #manually adding extra calculated values\n", " sales_formula = f\"C2:C{max_row+1}\"\n", " platform_formula = f\"D2:D{max_row+1}\"\n", " cellular_formula = f\"F2:F{max_row+1}\"\n", " profit_formula = f\"=B{max_row+3} - B{max_row+4} - B{max_row+5}\"\n", " writer.sheets[customer].write(f'A{max_row+3}', \"Revenue\", bold_format)\n", " writer.sheets[customer].write(f'A{max_row+4}', \"Platform Cost\", bold_format)\n", " writer.sheets[customer].write(f'A{max_row+5}', \"Cellular Cost\", bold_format)\n", " writer.sheets[customer].write(f'A{max_row+6}', \"Profit\", bold_format)\n", " writer.sheets[customer].write(f'B{max_row+3}', '=SUM(' + sales_formula + ')', bold_currency_format)\n", " writer.sheets[customer].write(f'B{max_row+4}', '=SUM(' + platform_formula + ')', bold_currency_format)\n", " writer.sheets[customer].write(f'B{max_row+5}', '=SUM(' + cellular_formula + ')', bold_currency_format)\n", " writer.sheets[customer].write_formula(f'B{max_row+6}', profit_formula, bold_currency_format)\n", " for ind,order in enumerate(counts):\n", " writer.sheets[customer].write(f'A{max_row+8+ind}', order)\n", " writer.sheets[customer].write(f'B{max_row+8+ind}', counts[order])\n", " overview.write(overview_row,0, customer)\n", " overview.write(overview_row,1, f\"='{customer}'!B{max_row+3}\")\n", " overview.write(overview_row,2, f\"='{customer}'!B{max_row+4}\")\n", " overview.write(overview_row,3, f\"='{customer}'!B{max_row+5}\")\n", " overview.write(overview_row,4, f\"='{customer}'!B{max_row+6}\")\n", " overview_row += 1\n", " overview.write(overview_row,0, \"Total\", bold_format)\n", " overview.write(overview_row,1, f\"=SUM(B2:B{overview_row})\", bold_currency_format)\n", " overview.write(overview_row,2, f\"=SUM(C2:C{overview_row})+399\", bold_currency_format)\n", " overview.write(overview_row,3, f\"=SUM(D2:D{overview_row})\", bold_currency_format)\n", " overview.write(overview_row,4, f\"=B{overview_row+1} - C{overview_row+1} - D{overview_row+1}\", bold_currency_format)\n", "\n", " overview.set_column(0,4, 18)" ] } ], "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 }