765 lines
44 KiB
Plaintext
765 lines
44 KiB
Plaintext
{
|
|
"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
|
|
}
|