156 lines
7.0 KiB
Python
156 lines
7.0 KiB
Python
#Build report from data
|
|
import pandas as pd
|
|
from datetime import datetime as dt
|
|
import os, boto3
|
|
from thingsBoardData import getThingsBoardData
|
|
from mistawayData import getMistAwayData
|
|
import logging
|
|
|
|
from email.mime.multipart import MIMEMultipart
|
|
from email import encoders
|
|
from email.mime.base import MIMEBase
|
|
|
|
logger = logging.getLogger('billing_reports')
|
|
logger.setLevel(logging.INFO)
|
|
ch = logging.StreamHandler()
|
|
ch.setLevel(logging.INFO)
|
|
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
|
|
ch.setFormatter(formatter)
|
|
logger.addHandler(ch)
|
|
|
|
ses = boto3.client('ses')
|
|
EMAIL_FROM_ADDRESS = "alerts@henry-pump.com"
|
|
email_to = [
|
|
"nmelone@henry-pump.com"
|
|
]
|
|
|
|
#Get Mistaway Data
|
|
mistaway_data = getMistAwayData()
|
|
#Get ThingsBoard Data
|
|
thingsboard_data = getThingsBoardData(os.environ["url"], os.environ["username"],os.environ["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]
|
|
|
|
def reportBuilder(event, context):
|
|
# Create a new Excel writer object
|
|
with pd.ExcelWriter(f"/tmp/Remote-Monitoring-Report-{dt.date(dt.now())}.xlsx", engine="xlsxwriter") as writer:
|
|
# Formats
|
|
bold_format = writer.book.add_format({'bold': True})
|
|
header_format = writer.book.add_format({'bold': True, 'center_across': True, 'border': True, 'bottom': True})
|
|
currency_format = writer.book.add_format({'num_format': "[$$-409]#,##0.00"})
|
|
bold_currency_format = writer.book.add_format({'num_format': "[$$-409]#,##0.00", 'bold': True})
|
|
highlight_format = writer.book.add_format({'bg_color': "yellow"})
|
|
# Setting up overview sheet
|
|
overview = writer.book.add_worksheet("Overview")
|
|
overview_row = 1
|
|
overview.write(0,0, "Customer", header_format)
|
|
overview.write(0,1, "Revenue", header_format)
|
|
overview.write(0,2, "Platform Cost", header_format)
|
|
overview.write(0,3, "Cellular Cost", header_format)
|
|
overview.write(0,4, "Profit", header_format)
|
|
|
|
|
|
# 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():
|
|
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")
|
|
|
|
# Write to a specific sheet in the Excel file
|
|
df.to_excel(writer, sheet_name=customer, index=False)
|
|
for column in df:
|
|
column_length = max(df[column].astype(str).map(len).max(), len(column))
|
|
col_idx = df.columns.get_loc(column)
|
|
if col_idx in [2,3,5]:
|
|
writer.sheets[customer].set_column(col_idx, col_idx, column_length, currency_format)
|
|
else:
|
|
writer.sheets[customer].set_column(col_idx, col_idx, column_length)
|
|
|
|
# Get the dimensions of the dataframe.
|
|
(max_row, max_col) = df.shape
|
|
#Apply highlighting
|
|
writer.sheets[customer].conditional_format(f"G2:G{max_row+1}", {"type": "text", "criteria": "not containing", "value": "AP-bundled", "format": highlight_format})
|
|
|
|
#writer.sheets[customer].set_column(2,3,None,currency_format)
|
|
#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", bold_format)
|
|
writer.sheets[customer].write(f'A{max_row+4}', "Platform Cost", bold_format)
|
|
writer.sheets[customer].write(f'A{max_row+5}', "Cellular Cost", bold_format)
|
|
writer.sheets[customer].write(f'A{max_row+6}', "Profit", bold_format)
|
|
writer.sheets[customer].write(f'B{max_row+3}', '=SUM(' + sales_formula + ')', bold_currency_format)
|
|
writer.sheets[customer].write(f'B{max_row+4}', '=SUM(' + platform_formula + ')', bold_currency_format)
|
|
writer.sheets[customer].write(f'B{max_row+5}', '=SUM(' + cellular_formula + ')', bold_currency_format)
|
|
writer.sheets[customer].write_formula(f'B{max_row+6}', profit_formula, bold_currency_format)
|
|
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", bold_format)
|
|
overview.write(overview_row,1, f"=SUM(B2:B{overview_row})", bold_currency_format)
|
|
overview.write(overview_row,2, f"=SUM(C2:C{overview_row})+399", bold_currency_format)
|
|
overview.write(overview_row,3, f"=SUM(D2:D{overview_row})", bold_currency_format)
|
|
overview.write(overview_row,4, f"=B{overview_row+1} - C{overview_row+1} - D{overview_row+1}", bold_currency_format)
|
|
|
|
overview.set_column(0,4, 18)
|
|
|
|
send_email()
|
|
|
|
def send_email():
|
|
attachment = MIMEBase('application', 'octet-stream')
|
|
attachment.set_payload(open(f"/tmp/Remote-Monitoring-Report-{dt.date(dt.now())}.xlsx", "rb").read())
|
|
encoders.encode_base64(attachment)
|
|
msg = MIMEMultipart('alternative')
|
|
msg['Subject'] = f"Remote Monitoring Report - {dt.date(dt.now())}"
|
|
msg['From'] = "alerts@henry-pump.com"
|
|
msg['To'] = ", ".join(email_to)
|
|
|
|
filename = f"Remote-Monitoring-{dt.date(dt.now())} .xlsx"
|
|
attachment.add_header('Content-Disposition', 'attachment', filename=filename)
|
|
|
|
msg.attach(attachment)
|
|
response = ses.send_raw_email(
|
|
Source=EMAIL_FROM_ADDRESS,
|
|
Destinations=email_to,
|
|
RawMessage={
|
|
'Data': msg.as_string()
|
|
},
|
|
FromArn='',
|
|
SourceArn='',
|
|
ReturnPathArn=''
|
|
)
|
|
return response
|