535 lines
22 KiB
Python
535 lines
22 KiB
Python
# %%
|
|
import lattice
|
|
import logging
|
|
import json
|
|
|
|
logger = logging.getLogger('billing_reports')
|
|
logger.setLevel(logging.INFO)
|
|
|
|
fh = logging.FileHandler('/Users/nico/Documents/Github/hp-billing-report/billing.log')
|
|
fh.setLevel(logging.INFO)
|
|
|
|
ch = logging.StreamHandler()
|
|
ch.setLevel(logging.ERROR)
|
|
|
|
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
|
|
fh.setFormatter(formatter)
|
|
ch.setFormatter(formatter)
|
|
|
|
logger.addHandler(fh)
|
|
logger.addHandler(ch)
|
|
|
|
# %%
|
|
#Mistaway data collection
|
|
from datetime import datetime as dt
|
|
|
|
json_file_output = False
|
|
console_output = False
|
|
|
|
# %%
|
|
nodes = lattice.getNodes()
|
|
|
|
# %%
|
|
# Find a value for a given key in a given dictionary
|
|
def _findItem(obj, key):
|
|
if key in obj: return obj[key]
|
|
for k, v in obj.items():
|
|
if isinstance(v,dict):
|
|
item = _findItem(v, key)
|
|
if item is not None:
|
|
return item
|
|
|
|
# %%
|
|
# recursively go through folders to build folder structure
|
|
def putFolder(folder, fs):
|
|
try:
|
|
if not folder["id"] == folder["parentFolderId"]:
|
|
parent = _findItem(fs, folder["parentFolderId"])
|
|
parent[folder["id"]] = folder
|
|
putFolder(parent,fs)
|
|
else:
|
|
fs[folder["id"]] = folder
|
|
return fs
|
|
except Exception as e:
|
|
logger.error(f"Exception in putFolder: {e}")
|
|
|
|
|
|
# %%
|
|
# Go through every folder and build a proper folder structure
|
|
# Output to JSON file
|
|
folders = lattice.getFolders()
|
|
foldermap = {}
|
|
#print(json.dumps(folders, indent=4))
|
|
for folder in folders:
|
|
logger.debug(folder)
|
|
putFolder(folder,foldermap)
|
|
if console_output:
|
|
print(json.dumps(foldermap, indent=4))
|
|
if json_file_output:
|
|
with open("./folderMap" + str(dt.date(dt.now())) +".json", "w") as f:
|
|
json.dump(foldermap, f, indent=4)
|
|
|
|
# %%
|
|
# Go through every node and collect unique device id (MAC)
|
|
# Output to JSON file
|
|
foldersTracker = []
|
|
vanityMap = {}
|
|
deviceTypes = {}
|
|
for type in lattice.getNodeTypes():
|
|
deviceTypes[type["id"]] = type["name"]
|
|
for node in nodes:
|
|
if not node["uniqueId"][-6:] in [":00:00", ":00:30"]:
|
|
if not node["folderId"] in foldersTracker:
|
|
foldersTracker.append(node["folderId"])
|
|
folder = _findItem(foldermap, node["folderId"])
|
|
if folder:
|
|
deviceName = folder["name"]
|
|
latitude = folder["location"]["lat"]
|
|
longitude = folder["location"]["lng"]
|
|
pfolder = _findItem(foldermap, folder["parentFolderId"])
|
|
ppfolder = _findItem(foldermap, pfolder["parentFolderId"])
|
|
customer = ppfolder["name"]
|
|
vanityMap[node["uniqueId"]] = {"deviceName": deviceName, "deviceType": deviceTypes[node["nodeTypeId"]], "customer": customer, "latitude": latitude, "longitude": longitude}
|
|
else:
|
|
logger.info("Folder does not exist: " + str(node["folderId"]))
|
|
else:
|
|
logger.info("Folder already in list: " + str(node["folderId"]))
|
|
#print(vanityMap)
|
|
if json_file_output:
|
|
with open("./deviceMap" + str(dt.date(dt.now())) +".json", "w") as f:
|
|
json.dump(vanityMap, f, indent=4)
|
|
|
|
|
|
# %%
|
|
"""
|
|
Data to be collected:
|
|
{
|
|
"customer":{
|
|
"sales_order": {
|
|
"billable_item_1": {
|
|
"sales_price": 75,
|
|
"platform_cost": 10,
|
|
"platform": "thingsboard", # "thingsboard", "mistaway"
|
|
"cellular_cost": 15,
|
|
"billing_type": "stand-alone" # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
},
|
|
"billable_item_2:{...},
|
|
...
|
|
},
|
|
"sales_order_2":{...}
|
|
},
|
|
"customer_2":{...}
|
|
}
|
|
"""
|
|
|
|
# %%
|
|
mistaway_data = {}
|
|
from shapely.geometry import Point, Polygon
|
|
|
|
denali_east = Polygon([(31.441289, -102.175343),(31.467676, -101.936571), (31.321496, -101.943604),(31.304714, -102.139878) ])
|
|
jitterbug = []
|
|
for key, value in vanityMap.items():
|
|
customer = value["customer"]
|
|
device = value["deviceName"]
|
|
type = value["deviceType"]
|
|
location = Point(value["latitude"], value["longitude"])
|
|
if not device in ["Melinda 252"]:
|
|
cellular_cost = 15
|
|
so = "HPSO-1"
|
|
price = 75
|
|
billing_type = "Stand-Alone"
|
|
if customer not in mistaway_data:
|
|
mistaway_data[customer] = {}
|
|
|
|
if customer == "CrownQuest":
|
|
if device == "LimeQuest 6 SR 1-1":
|
|
so = "LimeQuest SO"
|
|
price = 75
|
|
billing_type = "Stand-Alone"
|
|
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"]:
|
|
so = "Wilkinson 39 Field"
|
|
price = 0
|
|
billing_type = "AP-bundled"
|
|
cellular_cost = 0
|
|
if so not in mistaway_data[customer]:
|
|
mistaway_data[customer][so] = {}
|
|
if "Wilkinson 39 AP" not in mistaway_data[customer][so]:
|
|
mistaway_data[customer][so]["Wilkinson 39 AP"] = {
|
|
"Sales Price": 250,
|
|
"Platform Cost": 0,
|
|
"Platform": "Mistaway", # "thingsboard", "mistaway"
|
|
"Cellular Cost": 15,
|
|
"Billing Type": "AP" # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
}
|
|
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"]:
|
|
so = "Wilkinson 37 Field"
|
|
price = 0
|
|
billing_type = "AP-bundled"
|
|
cellular_cost = 0
|
|
if so not in mistaway_data[customer]:
|
|
mistaway_data[customer][so] = {}
|
|
if "Wilkinson 37 AP" not in mistaway_data[customer][so]:
|
|
mistaway_data[customer][so]["Wilkinson 37 AP"] = {
|
|
"Sales Price": 250,
|
|
"Platform Cost": 0,
|
|
"Platform": "Mistaway", # "thingsboard", "mistaway"
|
|
"Cellular Cost": 15,
|
|
"Billing Type": "AP" # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
}
|
|
elif "Wilkinson 33" in device or "Wilkinson 34" in device or "Wilkinson 1" in device or "Wilkinson 4" in device:
|
|
so = "Wilkinson 33-34 Field"
|
|
price = 0
|
|
billing_type = "AP-bundled"
|
|
cellular_cost = 0
|
|
if so not in mistaway_data[customer]:
|
|
mistaway_data[customer][so] = {}
|
|
if "Wilkinson 33-34 AP" not in mistaway_data[customer][so]:
|
|
mistaway_data[customer][so]["Wilkinson 33-34 AP"] = {
|
|
"Sales Price": 250,
|
|
"Platform Cost": 0,
|
|
"Platform": "Mistaway", # "thingsboard", "mistaway"
|
|
"Cellular Cost": 15,
|
|
"Billing Type": "AP" # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
}
|
|
elif "Free 40" in device or "Free 32" in device:
|
|
so = "Free Field"
|
|
price = 0
|
|
billing_type = "AP-bundled"
|
|
cellular_cost = 0
|
|
if so not in mistaway_data[customer]:
|
|
mistaway_data[customer][so] = {}
|
|
if "Free AP" not in mistaway_data[customer][so]:
|
|
mistaway_data[customer][so]["Free AP"] = {
|
|
"Sales Price": 250,
|
|
"Platform Cost": 0,
|
|
"Platform": "Mistaway", # "thingsboard", "mistaway"
|
|
"Cellular Cost": 15,
|
|
"Billing Type": "AP" # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
}
|
|
elif "LimeQuest 5" in device or "LimeQuest 10" in device:
|
|
so = "LimeQuest Field"
|
|
price = 0
|
|
billing_type = "AP-bundled"
|
|
cellular_cost = 0
|
|
if so not in mistaway_data[customer]:
|
|
mistaway_data[customer][so] = {}
|
|
if "LimeQuest AP" not in mistaway_data[customer][so]:
|
|
mistaway_data[customer][so]["LimeQuest AP"] = {
|
|
"Sales Price": 250,
|
|
"Platform Cost": 0,
|
|
"Platform": "Mistaway", # "thingsboard", "mistaway"
|
|
"Cellular Cost": 15,
|
|
"Billing Type": "AP" # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
}
|
|
elif "LimeQuest 5" in device or "LimeQuest 10" in device:
|
|
so = "LimeQuest Field"
|
|
price = 0
|
|
billing_type = "AP-bundled"
|
|
cellular_cost = 0
|
|
if so not in mistaway_data[customer]:
|
|
mistaway_data[customer][so] = {}
|
|
if "LimeQuest AP" not in mistaway_data[customer][so]:
|
|
mistaway_data[customer][so]["LimeQuest AP"] = {
|
|
"Sales Price": 250,
|
|
"Platform Cost": 0,
|
|
"Platform": "Mistaway", # "thingsboard", "mistaway"
|
|
"Cellular Cost": 15,
|
|
"Billing Type": "AP" # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
}
|
|
elif "Horton 23" in device or "Horton 34" in device:
|
|
so = "Horton Field"
|
|
price = 0
|
|
billing_type = "AP-bundled"
|
|
cellular_cost = 0
|
|
if so not in mistaway_data[customer]:
|
|
mistaway_data[customer][so] = {}
|
|
if "Horton AP" not in mistaway_data[customer][so]:
|
|
mistaway_data[customer][so]["Horton AP"] = {
|
|
"Sales Price": 250,
|
|
"Platform Cost": 0,
|
|
"Platform": "Mistaway", # "thingsboard", "mistaway"
|
|
"Cellular Cost": 15,
|
|
"Billing Type": "AP" # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
}
|
|
elif type == "advvfdipp" or type == "ipp":
|
|
so = "Santa Rosa"
|
|
elif type == "rigpump":
|
|
so = "Rig Pump"
|
|
elif type == "transferlite":
|
|
so = "Transfer"
|
|
elif customer == "Patriot Resources":
|
|
so = "Patriot Resources"
|
|
price = 100
|
|
elif customer == "Keagan Faudree Water":
|
|
so = "Keagan Faudree Water"
|
|
elif customer == "Summit Petroleum":
|
|
if location.x >= 31.654963:
|
|
so = "Banay"
|
|
price = 75
|
|
elif location.within(denali_east):
|
|
so = "Denali East"
|
|
if type == "dual_flowmeter":
|
|
price = 75
|
|
elif type == "plcfreshwater":
|
|
price = 55
|
|
cellular_cost = 0
|
|
billing_type = "Networked"
|
|
else: #if location.within(jitterbug):
|
|
so = "Jitterbug"
|
|
if type == "dual_flowmeter":
|
|
price = 75
|
|
elif type == "plcfreshwater":
|
|
price = 55
|
|
cellular_cost = 0
|
|
billing_type = "Networked"
|
|
|
|
|
|
if so not in mistaway_data[customer]:
|
|
mistaway_data[customer][so] = {}
|
|
|
|
mistaway_data[customer][so][device] = {
|
|
"Sales Price": price,
|
|
"Platform Cost": 10,
|
|
"Platform": "Mistaway", # "thingsboard", "mistaway"
|
|
"Cellular Cost": cellular_cost,
|
|
"Billing Type": billing_type # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
}
|
|
|
|
|
|
|
|
|
|
# %%
|
|
#ThingsBoard data collection
|
|
from tb_rest_client.rest_client_ce import *
|
|
from tb_rest_client.rest import ApiException
|
|
|
|
# %%
|
|
# ThingsBoard REST API URL
|
|
url = "https://hp.henrypump.cloud"
|
|
# Default Tenant Administrator credentials
|
|
username = "henry.pump.automation@gmail.com"
|
|
password = "Henry Pump @ 2022"
|
|
|
|
# %%
|
|
def getDevices(rest_client, customers, page=0, pageSize=500):
|
|
thingsboard_data = {}
|
|
for c in customers.data:
|
|
cname = c.name
|
|
cid = c.id.id
|
|
if cname not in ["Test Company", "Amerus Safety Solutions"]:
|
|
#create new company if it doesn't exist
|
|
if cname not in thingsboard_data:
|
|
thingsboard_data[cname] = {}
|
|
#get devices of a company
|
|
devices = rest_client.get_customer_devices(customer_id=cid, page_size=pageSize, page=page)
|
|
#go through each device and store its data in the dict
|
|
for device in devices.data:
|
|
cellular_cost = 15
|
|
#fix naming to work with JSON/dict
|
|
if '"' in device.name:
|
|
deviceName = device.name.replace('"', 'in')
|
|
else:
|
|
deviceName = device.name
|
|
# Sort Device details
|
|
if cname == "Chuda Resources":
|
|
so = "Water Wells"
|
|
price = 75
|
|
billing_type = "Stand-Alone"
|
|
elif cname == "Henry Petroleum":
|
|
so = "Check Meters"
|
|
price = 50
|
|
billing_type = "Stand-Alone"
|
|
elif cname == "Faskens":
|
|
if device.type == "tankalarms":
|
|
so = "Tanks"
|
|
billing_type = "Stand-Alone"
|
|
price = 50
|
|
elif device.type in ["advvfdipp", "plcfreshwater"]:
|
|
so = "Water Wells"
|
|
billing_type = "Stand-Alone"
|
|
price = 50
|
|
elif device.type == "plcpond":
|
|
so = "Ponds"
|
|
billing_type = "Stand-Alone"
|
|
price = 50
|
|
else:
|
|
so = "HPSO-1"
|
|
billing_type = "Stand-Alone"
|
|
price = 50
|
|
elif cname == "Henry Resources":
|
|
if deviceName == "Pearl Central":
|
|
so = "Henry Resources"
|
|
price = 300
|
|
billing_type = "Stand-Alone-WiFi"
|
|
else:
|
|
so = "Henry Resources"
|
|
price = 275
|
|
billing_type = "Stand-Alone"
|
|
elif cname == "ConocoPhillips":
|
|
if device.type == "flowmeterskid":
|
|
so = "Portable Meter"
|
|
price = 50
|
|
billing_type = "Stand-Alone"
|
|
elif device.type == "plcfreshwater":
|
|
so = "Water Well"
|
|
price = 50
|
|
cellular_cost = 0
|
|
billing_type = "Networked"
|
|
elif device.type == "advvfdipp":
|
|
so = "Santa Rosa"
|
|
price = 50
|
|
billing_type = "Stand-Alone"
|
|
elif device.type == "Gateway":
|
|
so = "AP"
|
|
price = 0
|
|
billing_type = "AP-bundled"
|
|
elif cname == "Saulsbury Ventures":
|
|
so = "Saulsbury Ventures"
|
|
price = 50
|
|
billing_type = "Stand-Alone"
|
|
else:
|
|
so = "HPSO-1"
|
|
price = 50
|
|
billing_type = "Stand-Alone"
|
|
|
|
#make a new Sales Order if it doesn't exist
|
|
if so not in thingsboard_data[cname]:
|
|
thingsboard_data[cname][so] = {}
|
|
#add device to Sales Order under Company
|
|
thingsboard_data[cname][so][deviceName] = {
|
|
"Sales Price": price,
|
|
"Platform Cost": 0,
|
|
"Platform": "ThingsBoard", # "thingsboard", "mistaway"
|
|
"Cellular Cost": cellular_cost,
|
|
"Billing Type": billing_type # "stand-alone", "AP", "AP-bundled", "networked", "stand-alone-wifi"
|
|
}
|
|
return thingsboard_data
|
|
|
|
# %%
|
|
def getThingsBoardData(url, username, password):
|
|
# Creating the REST client object with context manager to get auto token refresh
|
|
with RestClientCE(base_url=url) as rest_client:
|
|
try:
|
|
# Auth with credentials
|
|
rest_client.login(username=username, password=password)
|
|
# Get customers > get devices under a target customer > get keys for devices > get data for devices
|
|
customers = rest_client.get_customers(page_size="100", page="0")
|
|
thingsboard_data = getDevices(rest_client=rest_client, customers=customers)
|
|
return thingsboard_data
|
|
except ApiException as e:
|
|
logger.error(e)
|
|
return False
|
|
|
|
# %%
|
|
thingsboard_data = getThingsBoardData(url, username,password)
|
|
|
|
# %%
|
|
#Mixing data from Mistaway and ThingsBoard
|
|
excel_data = {}
|
|
excel_data.update(mistaway_data)
|
|
for customer in thingsboard_data.keys():
|
|
if customer in excel_data:
|
|
for so in thingsboard_data[customer].keys():
|
|
if so in excel_data[customer]:
|
|
excel_data[customer][so].update(thingsboard_data[customer][so])
|
|
else:
|
|
excel_data[customer].update(thingsboard_data[customer])
|
|
else:
|
|
excel_data[customer] = thingsboard_data[customer]
|
|
|
|
|
|
# %%
|
|
#Build report from data
|
|
import xlsxwriter
|
|
import pandas as pd
|
|
|
|
def highlight_rows(row):
|
|
"""Function to apply row highlighting for non 'AP-bundled' billing types."""
|
|
return 'background-color: yellow' if row['Billing Type'] != 'AP-bundled' else ''
|
|
|
|
|
|
# Create a new Excel writer object
|
|
with pd.ExcelWriter(f"Billing-Report-{dt.date(dt.now())}.xlsx", engine="xlsxwriter") as writer:
|
|
overview = writer.book.add_worksheet("Overview")
|
|
overview_row = 1
|
|
overview.write(0,1, "Revenue")
|
|
overview.write(0,2, "Platform Cost")
|
|
overview.write(0,3, "Cellular Cost")
|
|
overview.write(0,4, "Profit")
|
|
# Loop through customers
|
|
for customer, orders in excel_data.items():
|
|
|
|
rows = []
|
|
counts = {}
|
|
# Loop through each sales order for the customer
|
|
for order, items in orders.items():
|
|
for item, details in items.items():
|
|
"""
|
|
details_copy = details.copy()
|
|
details_copy["item"] = item
|
|
details_copy["order"] = order if item.endswith("1") else "" # Display order only once per customer
|
|
rows.append(details_copy)
|
|
"""
|
|
row = {
|
|
"Sales Order": order,
|
|
"Location": item
|
|
}
|
|
row.update(details)
|
|
rows.append(row)
|
|
counts[order] = len(items)
|
|
|
|
# Convert the data to a DataFrame
|
|
df = pd.DataFrame(rows)
|
|
|
|
# Sort by sales order
|
|
df = df.sort_values(by="Sales Order")
|
|
#df = df.sort_values(by=["order", "item"])
|
|
|
|
|
|
# Apply the highlighting
|
|
df_style = df.style.applymap(lambda x: highlight_rows({'Billing Type': x}) if isinstance(x, str) else "", subset=['Billing Type'])
|
|
|
|
# Write to a specific sheet in the Excel file
|
|
#df.to_excel(writer, sheet_name=customer, index=False)
|
|
df_style.to_excel(writer, sheet_name=customer, index=False, header=True)
|
|
for column in df:
|
|
column_length = max(df[column].astype(str).map(len).max(), len(column))
|
|
col_idx = df.columns.get_loc(column)
|
|
writer.sheets[customer].set_column(col_idx, col_idx, column_length)
|
|
# Get the dimensions of the dataframe.
|
|
(max_row, max_col) = df.shape
|
|
#manually adding extra calculated values
|
|
sales_formula = f"C2:C{max_row+1}"
|
|
platform_formula = f"D2:D{max_row+1}"
|
|
cellular_formula = f"F2:F{max_row+1}"
|
|
profit_formula = f"=B{max_row+3} - B{max_row+4} - B{max_row+5}"
|
|
writer.sheets[customer].write(f'A{max_row+3}', "Revenue")
|
|
writer.sheets[customer].write(f'A{max_row+4}', "Platform Cost")
|
|
writer.sheets[customer].write(f'A{max_row+5}', "Cellular Cost")
|
|
writer.sheets[customer].write(f'A{max_row+6}', "Profit")
|
|
writer.sheets[customer].write(f'B{max_row+3}', '=SUM(' + sales_formula + ')', )
|
|
writer.sheets[customer].write(f'B{max_row+4}', '=SUM(' + platform_formula + ')', )
|
|
writer.sheets[customer].write(f'B{max_row+5}', '=SUM(' + cellular_formula + ')', )
|
|
writer.sheets[customer].write_formula(f'B{max_row+6}', profit_formula, )
|
|
for ind,order in enumerate(counts):
|
|
writer.sheets[customer].write(f'A{max_row+8+ind}', order)
|
|
writer.sheets[customer].write(f'B{max_row+8+ind}', counts[order])
|
|
overview.write(overview_row,0, customer)
|
|
overview.write(overview_row,1, f"='{customer}'!B{max_row+3}")
|
|
overview.write(overview_row,2, f"='{customer}'!B{max_row+4}")
|
|
overview.write(overview_row,3, f"='{customer}'!B{max_row+5}")
|
|
overview.write(overview_row,4, f"='{customer}'!B{max_row+6}")
|
|
overview_row += 1
|
|
overview.write(overview_row,0, "Total")
|
|
overview.write(overview_row,1, f"=SUM(B2:B{overview_row})")
|
|
overview.write(overview_row,2, f"=SUM(C2:C{overview_row})+399")
|
|
overview.write(overview_row,3, f"=SUM(D2:D{overview_row})")
|
|
overview.write(overview_row,4, f"=B{overview_row+1} - C{overview_row+1} - D{overview_row+1}")
|
|
|
|
overview.set_column(0,4, 18)
|
|
|
|
# %%
|
|
|
|
|
|
|