#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