def build_customer_list():
my_map = build_sheet_map(app_cfg['XLS_BOOKINGS'], sheet_map,
'XLS_BOOKINGS')
wb_bookings, sheet_bookings = open_wb(app_cfg['XLS_BOOKINGS'], 'updates')
customer_list = []
col_num_end_customer = -1
col_num_erp_customer = -1
#
# First find the column numbers for these column names in the book
#
for val in my_map:
if val[0] == 'ERP End Customer Name':
col_num_erp_customer = val[2]
elif val[0] == 'End Customer Global Ultimate Name':
col_num_end_customer = val[2]
#
# Main loop of bookings excel data
#
top_row = []
for i in range(sheet_bookings.nrows):
if i == 0:
# Grab these values to make column headings
top_row_erp_val = sheet_bookings.cell_value(
i, col_num_erp_customer)
top_row_end_val = sheet_bookings.cell_value(
i, col_num_end_customer)
top_row = [top_row_erp_val, top_row_end_val]
continue
# Capture both of the Customer names
customer_name_erp = sheet_bookings.cell_value(i, col_num_erp_customer)
customer_name_end = sheet_bookings.cell_value(i, col_num_end_customer)
customer_list.append((customer_name_erp, customer_name_end))
# Create a simple customer_list list of tuples
# Contains a full set of unique sorted customer names
# customer_list = [(erp_customer_name,end_customer_ultimate), (CustA,CustA)]
customer_list = set(customer_list)
# Convert the SET to a LIST so we can sort it
customer_list = list(customer_list)
# Sort the LIST
customer_list.sort(key=lambda tup: tup[0])
# Convert the customer name tuples to a list
tmp_list = []
for customer in customer_list:
tmp_list.append(list(customer))
customer_list = tmp_list
# Place column headings at the top
customer_list.insert(0, top_row)
return customer_list
def process_subs(run_dir=app_cfg["UPDATES_DIR"]):
print('MAPPING>>>>>>>>>> ', run_dir + '\\' + app_cfg['XLS_SUBSCRIPTIONS'])
# Open up the subscription excel workbooks
wb, sheet = open_wb(app_cfg['XLS_SUBSCRIPTIONS'], run_dir)
# Get the renewal columns we are looking for
my_map = build_sheet_map(app_cfg['XLS_SUBSCRIPTIONS'], sheet_map,
'XLS_SUBSCRIPTIONS', run_dir)
print('sheet_map ', id(sheet_map))
print('my map ', id(my_map))
# List comprehension replacement for above
# Strip out the columns from the sheet map that we don't need
my_map = [x for x in my_map if x[1] == 'XLS_SUBSCRIPTIONS']
# Create a simple column name dict
col_nums = {
sheet.cell_value(0, col_num): col_num
for col_num in range(0, sheet.ncols)
}
# Loop over all of the subscription records
# Build a dict of {customer:[next renewal date, next renewal revenue, upcoming renewals]}
my_dict = {}
for row_num in range(1, sheet.nrows):
customer = sheet.cell_value(row_num, col_nums['End Customer'])
if customer in my_dict:
tmp_record = []
tmp_records = my_dict[customer]
else:
tmp_record = []
tmp_records = []
# Loop over the my map gather the columns we need
for col_map in my_map:
my_cell = sheet.cell_value(row_num, col_map[2])
# Is this cell a Date type (3) ?
# If so format as a M/D/Y
if sheet.cell_type(row_num, col_map[2]) == 3:
my_cell = datetime.datetime(
*xlrd.xldate_as_tuple(my_cell, wb.datemode))
my_cell = my_cell.strftime('%m-%d-%Y')
tmp_record.append(my_cell)
tmp_records.append(tmp_record)
my_dict[customer] = tmp_records
#
# Sort each customers renewal dates
#
sorted_dict = {}
summarized_dict = {}
summarized_rec = []
# print('diag1',my_dict['BLUE CROSS & BLUE SHIELD OF ALABAMA'])
# exit()
# ['08-20-2018', '12', '08-20-2019', 72.0, 1500.0, 'Sub170034', 'ACTIVE']
for customer, renewals in my_dict.items():
# Sort this customers renewal records by date order
renewals.sort(
key=lambda x: datetime.datetime.strptime(x[0], '%m-%d-%Y'))
sorted_dict[customer] = renewals
#
# print('\t', customer, ' has', len(renewals), ' records')
# print('\t\t', renewals)
# print ('---------------------')
# time.sleep(1)
next_renewal_date = renewals[0][0]
next_renewal_rev = 0
next_renewal_qtr = renewals[0][2]
for renewal_rec in renewals:
if renewal_rec[0] == next_renewal_date:
# Tally this renewal record and get the next
# print (type(renewal_rec[4]), renewal_rec[4])
# time.sleep(1)
next_renewal_rev = renewal_rec[4] + next_renewal_rev
elif renewal_rec[0] != next_renewal_date:
# Record these summarized values
summarized_rec.append(
[next_renewal_date, next_renewal_rev, next_renewal_qtr])
# Reset these values and get the next renewal date for this customer
next_renewal_date = renewal_rec[0]
next_renewal_rev = renewal_rec[1]
next_renewal_qtr = renewal_rec[2]
# Check to see if this is the last renewal record
# If so exit the loop
if renewals.index(renewal_rec) == len(renewals) - 1:
break
summarized_rec.append(
[next_renewal_date, next_renewal_rev, next_renewal_qtr])
summarized_dict[customer] = summarized_rec
summarized_rec = []
# print(sorted_dict['FIRST NATIONAL BANK OF SOUTHERN AFRICA LTD'])
# print(summarized_dict['SPECTRUM HEALTH SYSTEM'])
# print (len(summarized_dict['SPECTRUM HEALTH SYSTEM']))
return sorted_dict, summarized_dict
def build_dashboard():
exit()
from my_app.func_lib.sheet_desc import sheet_map
#
# Open the order summary
#
wb_orders, sheet_orders = open_wb(app_cfg['XLS_ORDER_SUMMARY'], 'updates')
# wb_orders, sheet_orders = open_wb('tmp_TA Scrubbed Orders_as_of ' + app_cfg['PROD_DATE'])
# Loop over the orders XLS worksheet
# Create a simple list of orders with NO headers
order_list = []
for row_num in range(1, sheet_orders.nrows): # Skip the header row start at 1
tmp_record = []
for col_num in range(sheet_orders.ncols):
my_cell = sheet_orders.cell_value(row_num, col_num)
# If we just read a date save it as a datetime
if sheet_orders.cell_type(row_num, col_num) == 3:
my_cell = datetime.datetime(*xlrd.xldate_as_tuple(my_cell, wb_orders.datemode))
tmp_record.append(my_cell)
order_list.append(tmp_record)
# Create a dict of customer orders
customer_order_dict = create_customer_order_dict(order_list)
print()
print('We have summarized ', len(order_list), ' of interesting line items into')
print(len(customer_order_dict), ' unique customers')
print()
# Build Sheet Maps
sheet_map = build_sheet_map(app_cfg['SS_CX'], sheet_map, 'SS_CX')
sheet_map = build_sheet_map(app_cfg['SS_AS'], sheet_map, 'SS_AS')
sheet_map = build_sheet_map(app_cfg['SS_SAAS'], sheet_map, 'SS_SAAS')
#
# Get dict updates from linked sheets CX/AS/SAAS
#
cx_dict = get_linked_sheet_update(sheet_map, 'SS_CX', sheet_keys)
as_dict = get_linked_sheet_update(sheet_map, 'SS_AS', sheet_keys)
saas_dict = get_linked_sheet_update(sheet_map, 'SS_SAAS', sheet_keys)
# print(cx_dict)
# exit()
print()
print('We have CX Updates: ', len(cx_dict))
print('We have AS Updates: ', len(as_dict))
print('We have SAAS Updates: ', len(saas_dict))
print()
# Create Platform dict for platform lookup
tmp_dict = build_sku_dict()
platform_dict = {}
for key, val in tmp_dict.items():
if val[0] == 'Product' or val[0] == 'SaaS':
platform_dict[key] = val[1]
#
# Init Main Loop Variables
#
new_rows = []
new_row = []
bookings_col_num = -1
sensor_col_num = -1
svc_bookings_col_num = -1
platform_type_col_num = -1
sku_col_num = -1
my_col_idx = {}
# Create top row for the dashboard
# also make a dict (my_col_idx) of {column names : column number}
for col_idx, col in enumerate(sheet_map):
new_row.append(col[0])
my_col_idx[col[0]] = col_idx
new_rows.append(new_row)
#
# Main loop
#
for customer, orders in customer_order_dict.items():
new_row = []
order = []
orders_found = len(orders)
# Default Values
bookings_total = 0
sensor_count = 0
service_bookings = 0
platform_type = 'Not Identified'
saas_status = 'No Status'
cx_contact = 'None assigned'
cx_status = 'No Update'
as_pm = ''
as_cse1 = ''
as_cse2 = ''
as_complete = '' # 'Project Status/PM Completion'
as_comments = '' # 'Delivery Comments'
#
# Get update from linked sheets (if any)
#
if customer in saas_dict:
saas_status = saas_dict[customer][0]
if saas_status is True:
saas_status = 'Provision Complete'
else:
saas_status = 'Provision NOT Complete'
else:
saas_status = 'No Status'
if customer in cx_dict:
cx_contact = cx_dict[customer][0]
cx_status = cx_dict[customer][1]
else:
cx_contact = 'None assigned'
cx_status = 'No Update'
if customer in as_dict:
if as_dict[customer][0] == '':
as_pm = 'None Assigned'
else:
as_pm = as_dict[customer][0]
if as_dict[customer][1] == '':
as_cse1 = 'None Assigned'
else:
as_cse1 = as_dict[customer][1]
if as_dict[customer][2] == '':
as_cse2 = 'None Assigned'
else:
as_cse2 = as_dict[customer][2]
if as_dict[customer][3] == '':
as_complete = 'No Update'
else:
# 'Project Status/PM Completion'
as_complete = as_dict[customer][3]
if as_dict[customer][4] == '':
as_comments = 'No Comments'
else:
as_comments = as_dict[customer][4]
#
# Loop over this customers orders
# Create one summary row for this customer
# Total things
# Build a list of things that may change order to order (ie Renewal Dates, Customer Names)
#
platform_count = 0
for order_idx, order in enumerate(orders):
# calculate totals in this loop (ie total_books, sensor count etc)
bookings_total = bookings_total + order[my_col_idx['Total Bookings']]
sensor_count = sensor_count + order[my_col_idx['Sensor Count']]
if order[my_col_idx['Product Type']] == 'Service':
service_bookings = service_bookings + order[my_col_idx['Total Bookings']]
if order[my_col_idx['Bundle Product ID']] in platform_dict:
platform_count += 1
platform_type = platform_dict[order[my_col_idx['Bundle Product ID']]]
if platform_count > 1:
platform_type = platform_type + ' plus ' + str(platform_count-1)
#
# Modify/Update this record as needed and then add to the new_rows
#
order[my_col_idx['Total Bookings']] = bookings_total
order[my_col_idx['Sensor Count']] = sensor_count
order[my_col_idx['Service Bookings']] = service_bookings
order[my_col_idx['CSM']] = cx_contact
order[my_col_idx['Comments']] = cx_status
order[my_col_idx['Project Manager']] = as_pm
order[my_col_idx['AS Engineer 1']] = as_cse1
order[my_col_idx['AS Engineer 2']] = as_cse2
order[my_col_idx['Project Status/PM Completion']] = as_complete # 'Project Status/PM Completion'
order[my_col_idx['Delivery Comments']] = as_comments
order[my_col_idx['Provisioning completed']] = saas_status
order[my_col_idx['Product Description']] = platform_type
order[my_col_idx['Orders Found']] = orders_found
new_rows.append(order)
#
# End of main loop
#
# Do some clean up and ready for output
#
# Rename the columns as per the sheet map
cols_to_delete = []
for idx, map_info in enumerate(sheet_map):
if map_info[3] != '':
if map_info[3] == '*DELETE*':
# Put the columns to delete in a list
cols_to_delete.append(idx)
else:
# Rename to the new column name
new_rows[0][idx] = map_info[3]
# Loop over the new_rows and
# delete columns we don't need as per the sheet_map
for col_idx in sorted(cols_to_delete, reverse=True):
for row_idx, my_row in enumerate(new_rows):
del new_rows[row_idx][col_idx]
#
# Write the Dashboard to an Excel File
#
push_list_to_xls(new_rows, app_cfg['XLS_DASHBOARD'], 'updates')
# push_xls_to_ss(app_cfg['XLS_DASHBOARD']+'_as_of_01_31_2019.xlsx', 'jims dash')
return
def process_delivery(run_dir=app_cfg["UPDATES_DIR"]):
print('MAPPING>>>>>>>>>> ',
run_dir + '\\' + app_cfg['XLS_AS_DELIVERY_STATUS'])
# Open up the subscription excel workbooks
wb, sheet = open_wb(app_cfg['XLS_AS_DELIVERY_STATUS'], run_dir)
# Get the renewal columns we are looking for
my_map = build_sheet_map(app_cfg['XLS_AS_DELIVERY_STATUS'], sheet_map,
'XLS_AS_DELIVERY_STATUS', run_dir)
# List comprehension replacement for above
# Strip out the columns from the sheet map that we don't need
my_map = [x for x in my_map if x[1] == 'XLS_AS_DELIVERY_STATUS']
# Create a simple column name dict
col_nums = {
sheet.cell_value(0, col_num): col_num
for col_num in range(0, sheet.ncols)
}
# Loop over all of the delivery records
# Build a dict of {customer:[delivery stuff blah blah ]}
my_dict = {}
for row_num in range(1, sheet.nrows):
customer = sheet.cell_value(row_num, col_nums['End Customer'])
if customer in my_dict:
tmp_record = []
tmp_records = my_dict[customer]
else:
tmp_record = []
tmp_records = []
# Loop over the my map gather the columns we need
for col_map in my_map:
my_cell = sheet.cell_value(row_num, col_map[2])
# Is this cell a Date type (3) ?
# If so format as a M/D/Y
if sheet.cell_type(row_num, col_map[2]) == 3:
my_cell = datetime.datetime(
*xlrd.xldate_as_tuple(my_cell, wb.datemode))
my_cell = my_cell.strftime('%m-%d-%Y')
tmp_record.append(my_cell)
tmp_records.append(tmp_record)
my_dict[customer] = tmp_records
#
# # Sort each customers delivery status
# #
# delivery_dict = {}
# # print('diag1',my_dict['BLUE CROSS & BLUE SHIELD OF ALABAMA'])
# # exit()
# # ['08-20-2018', '12', '08-20-2019', 72.0, 1500.0, 'Sub170034', 'ACTIVE']
#
# for customer, renewals in my_dict.items():
# # Sort this customers renewal records by date order
# renewals.sort(key=lambda x: datetime.datetime.strptime(x[0], '%m-%d-%Y'))
# # sorted_dict[customer] = renewals
# #
# # print('\t', customer, ' has', len(renewals), ' records')
# # print('\t\t', renewals)
# # print ('---------------------')
# # time.sleep(1)
#
# next_renewal_date = renewals[0][0]
# next_renewal_rev = 0
# next_renewal_qtr = renewals[0][2]
# for renewal_rec in renewals:
# if renewal_rec[0] == next_renewal_date:
# # Tally this renewal record and get the next
# # print (type(renewal_rec[4]), renewal_rec[4])
# # time.sleep(1)
# next_renewal_rev = renewal_rec[4] + next_renewal_rev
# elif renewal_rec[0] != next_renewal_date:
# # Record these summarized values
# summarized_rec.append([next_renewal_date, next_renewal_rev, next_renewal_qtr])
# # Reset these values and get the next renewal date for this customer
# next_renewal_date = renewal_rec[0]
# next_renewal_rev = renewal_rec[1]
# next_renewal_qtr = renewal_rec[2]
#
# # Check to see if this is the last renewal record
# # If so exit the loop
# if renewals.index(renewal_rec) == len(renewals)-1:
# break
#
# summarized_rec.append([next_renewal_date, next_renewal_rev, next_renewal_qtr])
# summarized_dict[customer] = summarized_rec
# summarized_rec = []
return my_dict
def process_bookings():
# Go to Smartsheets and build these two dicts to use reference lookups
# team_dict: {'sales_levels 1-6':[('PSS','TSA')]}
# sku_dict: {sku : [sku_type, sku_description]}
team_dict = build_coverage_dict()
sku_dict = build_sku_dict()
#
# Open up the bookings excel workbooks
#
wb_bookings, sheet_bookings = open_wb(app_cfg['XLS_BOOKINGS'], 'updates')
# From the current up to date bookings file build a simple list
# that describes the format of the output file we are creating
# and the columns we need to add (ie PSS, TSA, Renewal Dates)
my_sheet_map = build_sheet_map(app_cfg['XLS_BOOKINGS'], sheet_map,
'XLS_BOOKINGS')
print('sheet_map ', id(sheet_map))
print('my_sheet_map ', id(my_sheet_map))
#
# init a bunch a variable we need for the main loop
#
order_header_row = []
order_rows = []
order_row = []
trash_rows = []
dest_col_nums = {}
src_col_nums = {}
# Build a dict of source sheet {'col_name' : src_col_num}
# Build a dict of destination sheet {'col_name' : dest_col_num}
# Build the header row for the output file
for idx, val in enumerate(my_sheet_map):
# Add to the col_num dict of col_names
dest_col_nums[val[0]] = idx
src_col_nums[val[0]] = val[2]
order_header_row.append(val[0])
# Initialize the order_row and trash_row lists
order_rows.append(order_header_row)
trash_rows.append(sheet_bookings.row_values(0))
print('There are ', sheet_bookings.nrows, ' rows in Raw Bookings')
#
# Main loop of over raw bookings excel data
#
# This loop will build two lists:
# 1. Interesting orders based on SKUs (order_rows)
# 2. Trash orders SKUs we don't care about (trash_rows)
# As determined by the sku_dict
# We have also will assign team coverage to both rows
#
for i in range(1, sheet_bookings.nrows):
# Is this SKU of interest ?
sku = sheet_bookings.cell_value(i, src_col_nums['Bundle Product ID'])
if sku in sku_dict:
# Let's make a row for this order
# Since it has an "interesting" sku
customer = sheet_bookings.cell_value(
i, src_col_nums['ERP End Customer Name'])
order_row = []
sales_level = ''
sales_level_cntr = 0
# Grab SKU data from the SKU dict
sku_type = sku_dict[sku][0]
sku_desc = sku_dict[sku][1]
sku_sensor_cnt = sku_dict[sku][2]
# Walk across the sheet_map columns
# to build this output row cell by cell
for val in my_sheet_map:
col_name = val[0] # Source Sheet Column Name
col_idx = val[2] # Source Sheet Column Number
# If this is a 'Sales Level X' column then
# Capture it's value until we get to level 6
# then do a team lookup
if col_name[:-2] == 'Sales Level':
sales_level = sales_level + sheet_bookings.cell_value(
i, col_idx) + ','
sales_level_cntr += 1
if sales_level_cntr == 6:
# We have collected all 6 sales levels
# Now go to find_team to do the lookup
sales_level = sales_level[:-1]
sales_team = find_team(team_dict, sales_level)
pss = sales_team[0]
tsa = sales_team[1]
order_row[dest_col_nums['pss']] = pss
order_row[dest_col_nums['tsa']] = tsa
if col_idx != -1:
# OK we have a cell that we need from the raw bookings
# sheet we need so grab it
order_row.append(sheet_bookings.cell_value(i, col_idx))
elif col_name == 'Product Description':
# Add in the Product Description
order_row.append(sku_desc)
elif col_name == 'Product Type':
# Add in the Product Type
order_row.append(sku_type)
elif col_name == 'Sensor Count':
# Add in the Sensor Count
order_row.append(sku_sensor_cnt)
else:
# this cell is assigned a -1 in the sheet_map
# so assign a blank as a placeholder for now
order_row.append('')
# Done with all the columns in this row
# Log this row for BOTH customer names and orders
# Go to next row of the raw bookings data
order_rows.append(order_row)
else:
# The SKU was not interesting so let's trash it
trash_rows.append(sheet_bookings.row_values(i))
print('Extracted ', len(order_rows),
" rows of interesting SKU's' from Raw Bookings")
print('Trashed ', len(trash_rows),
" rows of trash SKU's' from Raw Bookings")
#
# End of main loop
#
#
# Renewal Analysis
#
renewal_dict = process_renewals()
for order_row in order_rows[1:]:
customer = order_row[dest_col_nums['ERP End Customer Name']]
if customer in renewal_dict:
next_renewal_date = datetime.datetime.strptime(
renewal_dict[customer][0][0], '%m-%d-%Y')
next_renewal_rev = renewal_dict[customer][0][1]
next_renewal_qtr = renewal_dict[customer][0][2]
order_row[dest_col_nums['Renewal Date']] = next_renewal_date
order_row[dest_col_nums['Product Bookings']] = next_renewal_rev
order_row[dest_col_nums['Fiscal Quarter ID']] = next_renewal_qtr
if len(renewal_dict[customer]) > 1:
renewal_comments = '+' + str(len(renewal_dict[customer]) -
1) + ' more renewal(s)'
order_row[dest_col_nums['Renewal Comments']] = renewal_comments
# Now we build a an order dict
# Let's organize as this
# order_dict: {cust_name:[[order1],[order2],[orderN]]}
order_dict = {}
orders = []
order = []
for idx, order_row in enumerate(order_rows):
if idx == 0:
continue
customer = order_row[0]
orders = []
# Is this customer in the order dict ?
if customer in order_dict:
orders = order_dict[customer]
orders.append(order_row)
order_dict[customer] = orders
else:
orders.append(order_row)
order_dict[customer] = orders
# Create a simple customer_list
# Contains a full set of unique sorted customer names
# Example: customer_list = [[erp_customer_name,end_customer_ultimate], [CustA,CustA]]
customer_list = build_customer_list()
print('There are ', len(customer_list), ' unique Customer Names')
# Clean up order_dict to remove:
# 1. +/- zero sum orders
# 2. zero revenue orders
order_dict, customer_platforms = cleanup_orders(customer_list, order_dict,
my_sheet_map)
#
# Create a summary order file out of the order_dict
#
summary_order_rows = [order_header_row]
for key, val in order_dict.items():
for my_row in val:
summary_order_rows.append(my_row)
print(len(summary_order_rows), ' of scrubbed rows after removing "noise"')
#
# Push our lists to an excel file
#
# push_list_to_xls(customer_platforms, 'jim ')
print('order summary name ', app_cfg['XLS_ORDER_SUMMARY'])
push_list_to_xls(summary_order_rows, app_cfg['XLS_ORDER_SUMMARY'],
'updates')
push_list_to_xls(order_rows, app_cfg['XLS_ORDER_DETAIL'], 'updates')
push_list_to_xls(customer_list, app_cfg['XLS_CUSTOMER'], 'updates')
push_list_to_xls(trash_rows, app_cfg['XLS_BOOKINGS_TRASH'], 'updates')
# exit()
#
# Push our lists to a smart sheet
#
# push_xls_to_ss(wb_file, app_cfg['XLS_ORDER_SUMMARY'])
# push_xls_to_ss(wb_file, app_cfg['XLS_ORDER_DETAIL'])
# push_xls_to_ss(wb_file, app_cfg['XLS_CUSTOMER'])
# exit()
return
def process_renewals():
# Open up the renewals excel workbooks
wb, sheet = open_wb(app_cfg['XLS_RENEWALS'], 'updates')
# Get the renewal columns we are looking for
my_map = build_sheet_map(app_cfg['XLS_RENEWALS'], sheet_map,
'XLS_RENEWALS')
print('sheet_map ', id(sheet_map))
print('my map ', id(my_map))
# List comprehension replacement for above
# Strip out the columns from the sheet map that we don't need
my_map = [x for x in my_map if x[1] == 'XLS_RENEWALS']
# Create a simple column name dict
col_nums = {
sheet.cell_value(0, col_num): col_num
for col_num in range(0, sheet.ncols)
}
# Loop over all of the renewal records
# Build a dict of {customer:[next renewal date, next renewal revenue, upcoming renewals]}
my_dict = {}
for row_num in range(1, sheet.nrows):
customer = sheet.cell_value(row_num, col_nums['End Customer'])
if customer in my_dict:
tmp_record = []
tmp_records = my_dict[customer]
else:
tmp_record = []
tmp_records = []
# Loop over the my map gather the columns we need
for col_map in my_map:
my_cell = sheet.cell_value(row_num, col_map[2])
# Is this cell a Date type (3) ?
# If so format as a M/D/Y
if sheet.cell_type(row_num, col_map[2]) == 3:
my_cell = datetime.datetime(
*xlrd.xldate_as_tuple(my_cell, wb.datemode))
my_cell = my_cell.strftime('%m-%d-%Y')
tmp_record.append(my_cell)
tmp_records.append(tmp_record)
my_dict[customer] = tmp_records
#
# Sort each customers renewal dates
#
sorted_dict = {}
summarized_dict = {}
summarized_rec = []
for customer, renewals in my_dict.items():
# Sort this customers renewal records by date order
renewals.sort(key=lambda x: x[0])
sorted_dict[customer] = renewals
next_renewal_date = renewals[0][0]
next_renewal_rev = 0
next_renewal_qtr = renewals[0][2]
for renewal_rec in renewals:
if renewal_rec[0] == next_renewal_date:
# Tally this renewal record and get the next
next_renewal_rev = float(renewal_rec[1] + next_renewal_rev)
elif renewal_rec[0] != next_renewal_date:
# Record these summarized values
summarized_rec.append(
[next_renewal_date, next_renewal_rev, next_renewal_qtr])
# Reset these values and get the next renewal date for this customer
next_renewal_date = renewal_rec[0]
next_renewal_rev = renewal_rec[1]
next_renewal_qtr = renewal_rec[2]
# Check to see if this is the last renewal record
# If so exit the loop
if renewals.index(renewal_rec) == len(renewals) - 1:
break
summarized_rec.append(
[next_renewal_date, next_renewal_rev, next_renewal_qtr])
summarized_dict[customer] = summarized_rec
summarized_rec = []
# print(sorted_dict['FIRST NATIONAL BANK OF SOUTHERN AFRICA LTD'])
# print(summarized_dict['SPECTRUM HEALTH SYSTEM'])
# print (len(summarized_dict['SPECTRUM HEALTH SYSTEM']))
return summarized_dict
def phase_2(run_dir=app_cfg['UPDATES_DIR']):
home = app_cfg['HOME']
working_dir = app_cfg['WORKING_DIR']
path_to_run_dir = (os.path.join(home, working_dir, run_dir))
bookings_path = os.path.join(path_to_run_dir, app_cfg['XLS_BOOKINGS'])
# Read the config_dict.json file
with open(os.path.join(path_to_run_dir, app_cfg['META_DATA_FILE'])) as json_input:
config_dict = json.load(json_input)
data_time_stamp = datetime.datetime.strptime(config_dict['data_time_stamp'], '%m-%d-%y')
last_run_dir = config_dict['last_run_dir']
print("Run Date: ", data_time_stamp, type(data_time_stamp))
print('Run Directory:', last_run_dir)
print(bookings_path)
# Go to Smartsheets and build these two dicts to use reference lookups
# team_dict: {'sales_levels 1-6':[('PSS','TSA')]}
# sku_dict: {sku : [sku_type, sku_description]}
team_dict = build_coverage_dict()
sku_dict = build_sku_dict()
#
# Open up the bookings excel workbooks
#
wb_bookings, sheet_bookings = open_wb(app_cfg['XLS_BOOKINGS'], run_dir)
# From the current up to date bookings file build a simple list
# that describes the format of the output file we are creating
# and the columns we need to add (ie PSS, TSA, Renewal Dates)
my_sheet_map = build_sheet_map(app_cfg['XLS_BOOKINGS'], sheet_map,
'XLS_BOOKINGS', run_dir)
#
# init a bunch a variables we need for the main loop
#
order_header_row = []
order_rows = []
order_row = []
trash_rows = []
dest_col_nums = {}
src_col_nums = {}
# Build a dict of source sheet {'col_name' : src_col_num}
# Build a dict of destination sheet {'col_name' : dest_col_num}
# Build the header row for the output file
for idx, val in enumerate(my_sheet_map):
# Add to the col_num dict of col_names
dest_col_nums[val[0]] = idx
src_col_nums[val[0]] = val[2]
order_header_row.append(val[0])
# Initialize the order_row and trash_row lists
order_rows.append(order_header_row)
trash_rows.append(sheet_bookings.row_values(0))
print('There are ', sheet_bookings.nrows, ' rows in Raw Bookings')
#
# Main loop of over raw bookings excel data
#
# This loop will build two lists:
# 1. Interesting orders based on SKUs (order_rows)
# 2. Trash orders SKUs we don't care about (trash_rows)
# As determined by the sku_dict
# We have also will assign team coverage to both rows
#
for i in range(1, sheet_bookings.nrows):
# Is this SKU of interest ?
sku = sheet_bookings.cell_value(i, src_col_nums['Bundle Product ID'])
if sku in sku_dict:
# Let's make a row for this order
# Since it has an "interesting" sku
customer = sheet_bookings.cell_value(i, src_col_nums['ERP End Customer Name'])
order_row = []
sales_level = ''
sales_level_cntr = 0
# Grab SKU data from the SKU dict
sku_type = sku_dict[sku][0]
sku_desc = sku_dict[sku][1]
sku_sensor_cnt = sku_dict[sku][2]
# Walk across the sheet_map columns
# to build this output row cell by cell
for val in my_sheet_map:
col_name = val[0] # Source Sheet Column Name
col_idx = val[2] # Source Sheet Column Number
# If this is a 'Sales Level X' column then
# Capture it's value until we get to level 6
# then do a team lookup
if col_name[:-2] == 'Sales Level':
sales_level = sales_level + sheet_bookings.cell_value(i, col_idx) + ','
sales_level_cntr += 1
if sales_level_cntr == 6:
# We have collected all 6 sales levels
# Now go to find_team to do the lookup
sales_level = sales_level[:-1]
sales_team = find_team(team_dict, sales_level)
pss = sales_team[0]
tsa = sales_team[1]
order_row[dest_col_nums['pss']] = pss
order_row[dest_col_nums['tsa']] = tsa
if col_idx != -1:
# OK we have a cell that we need from the raw bookings
# sheet we need so grab it
order_row.append(sheet_bookings.cell_value(i, col_idx))
elif col_name == 'Product Description':
# Add in the Product Description
order_row.append(sku_desc)
elif col_name == 'Product Type':
# Add in the Product Type
order_row.append(sku_type)
elif col_name == 'Sensor Count':
# Add in the Sensor Count
order_row.append(sku_sensor_cnt)
else:
# this cell is assigned a -1 in the sheet_map
# so assign a blank as a placeholder for now
order_row.append('')
# Done with all the columns in this row
# Log this row for BOTH customer names and orders
# Go to next row of the raw bookings data
order_rows.append(order_row)
else:
# The SKU was not interesting so let's trash it
trash_rows.append(sheet_bookings.row_values(i))
print('Extracted ', len(order_rows), " rows of interesting SKU's' from Raw Bookings")
print('Trashed ', len(trash_rows), " rows of trash SKU's' from Raw Bookings")
#
# End of main loop
#
push_list_to_xls(order_rows,'jim.xlsx')
#
# Subscription Analysis
#
no_match = [['No Match Found in Subscription update']]
no_match_cntr = 0
match_cntr = 0
subs_sorted_dict, subs__summary_dict = process_subs(run_dir)
for order_row in order_rows[1:]:
customer = order_row[dest_col_nums['ERP End Customer Name']]
if customer in subs_sorted_dict:
match_cntr += 1
sub_start_date = datetime.datetime.strptime(subs_sorted_dict[customer][0][0], '%m-%d-%Y')
sub_initial_term = subs_sorted_dict[customer][0][1]
sub_renewal_date = datetime.datetime.strptime(subs_sorted_dict[customer][0][2], '%m-%d-%Y')
sub_days_to_renew = subs_sorted_dict[customer][0][3]
sub_monthly_charge = subs_sorted_dict[customer][0][4]
sub_id = subs_sorted_dict[customer][0][5]
sub_status = subs_sorted_dict[customer][0][6]
order_row[dest_col_nums['Start Date']] = sub_start_date
order_row[dest_col_nums['Initial Term']] = sub_initial_term
order_row[dest_col_nums['Renewal Date']] = sub_renewal_date
order_row[dest_col_nums['Days Until Renewal']] = sub_days_to_renew
order_row[dest_col_nums['Monthly Charge']] = sub_monthly_charge
order_row[dest_col_nums['Subscription ID']] = sub_id
order_row[dest_col_nums['Status']] = sub_status
if len(subs_sorted_dict[customer]) > 1:
renewal_comments = '+' + str(len(subs_sorted_dict[customer])-1) + ' more subscriptions(s)'
order_row[dest_col_nums['Subscription Comments']] = renewal_comments
else:
got_one = False
for x in no_match:
if x[0].lower() in customer.lower():
got_one = True
break
if got_one is False:
no_match_cntr += 1
no_match.append([customer])
push_list_to_xls(order_rows, 'jim1.xlsx')
push_list_to_xls(no_match, 'subcription misses.xlsx')
#
# AS Delivery Analysis
#
as_dict = process_delivery(run_dir)
print(as_dict)
print(len(as_dict))
for order_row in order_rows[1:]:
customer = order_row[dest_col_nums['ERP End Customer Name']]
if customer in as_dict:
match_cntr += 1
# as_customer = as_dict[customer][0][0]
# as_pid = as_dict[customer][0][1]
# as_dm = as_dict[customer][0][2]
# as_start_date = datetime.datetime.strptime(as_dict[customer][0][3], '%m-%d-%Y')
#
# order_row[dest_col_nums['End Customer']] = as_customer
# order_row[dest_col_nums['PID']] = as_pid
# order_row[dest_col_nums['Delivery Manager']] = as_dm
# order_row[dest_col_nums['Project Scheduled Start Date']] = as_start_date
order_row[dest_col_nums['PID']] = as_dict[customer][0][0]
order_row[dest_col_nums['Delivery Manager']] = as_dict[customer][0][1]
order_row[dest_col_nums['Delivery PM']] = as_dict[customer][0][2]
order_row[dest_col_nums['Tracking status']] = as_dict[customer][0][3]
order_row[dest_col_nums['Tracking Sub-status']] = as_dict[customer][0][4]
order_row[dest_col_nums['Comments']] = as_dict[customer][0][5]
order_row[dest_col_nums['Project Scheduled Start Date']] = datetime.datetime.strptime(as_dict[customer][0][6], '%m-%d-%Y')
order_row[dest_col_nums['Scheduled End Date']] = datetime.datetime.strptime(as_dict[customer][0][7], '%m-%d-%Y')
order_row[dest_col_nums['Project Creation Date']] = datetime.datetime.strptime(as_dict[customer][0][8], '%m-%d-%Y')
# order_row[dest_col_nums['Project Closed Date']] = datetime.datetime.strptime(as_dict[customer][0][9], '%m-%d-%Y')
order_row[dest_col_nums['Traffic lights (account team)']] = as_dict[customer][0][10]
order_row[dest_col_nums['Tracking Responsible']] = as_dict[customer][0][11]
order_row[dest_col_nums['ExecutiveSummary']] = as_dict[customer][0][12]
order_row[dest_col_nums['Critalpath']] = as_dict[customer][0][13]
order_row[dest_col_nums['IsssuesRisks']] = as_dict[customer][0][14]
order_row[dest_col_nums['ActivitiesCurrent']] = as_dict[customer][0][15]
order_row[dest_col_nums['ActivitiesNext']] = as_dict[customer][0][16]
order_row[dest_col_nums['LastUpdate']] = as_dict[customer][0][17]
order_row[dest_col_nums['SO']] = as_dict[customer][0][18]
else:
got_one = False
for x in no_match:
if x[0].lower() in customer.lower():
got_one = True
break
if got_one is False:
no_match_cntr += 1
no_match.append([customer])
#
# End of Construction Zone
#
# Now we build a an order dict
# Let's organize as this
# order_dict: {cust_name:[[order1],[order2],[orderN]]}
order_dict = {}
orders = []
order = []
for idx, order_row in enumerate(order_rows):
if idx == 0:
continue
customer = order_row[0]
orders = []
# Is this customer in the order dict ?
if customer in order_dict:
orders = order_dict[customer]
orders.append(order_row)
order_dict[customer] = orders
else:
orders.append(order_row)
order_dict[customer] = orders
# Create a simple customer_list
# Contains a full set of unique sorted customer names
# Example: customer_list = [[erp_customer_name,end_customer_ultimate], [CustA,CustA]]
customer_list = build_customer_list(run_dir)
print('There are ', len(customer_list), ' unique Customer Names')
# Clean up order_dict to remove:
# 1. +/- zero sum orders
# 2. zero revenue orders
order_dict, customer_platforms = cleanup_orders(customer_list, order_dict, my_sheet_map)
#
# Create a summary order file out of the order_dict
#
summary_order_rows = [order_header_row]
for key, val in order_dict.items():
for my_row in val:
summary_order_rows.append(my_row)
print(len(summary_order_rows), ' of scrubbed rows after removing "noise"')
#
# Push our lists to an excel file
#
# push_list_to_xls(customer_platforms, 'jim ')
print('order summary name ', app_cfg['XLS_ORDER_SUMMARY'])
push_list_to_xls(summary_order_rows, app_cfg['XLS_ORDER_SUMMARY'],
run_dir, 'ta_summary_orders')
push_list_to_xls(order_rows, app_cfg['XLS_ORDER_DETAIL'], run_dir, 'ta_order_detail')
push_list_to_xls(customer_list, app_cfg['XLS_CUSTOMER'], run_dir, 'ta_customers')
push_list_to_xls(trash_rows, app_cfg['XLS_BOOKINGS_TRASH'], run_dir, 'ta_trash_rows')
# exit()
#
# Push our lists to a smart sheet
#
# push_xls_to_ss(wb_file, app_cfg['XLS_ORDER_SUMMARY'])
# push_xls_to_ss(wb_file, app_cfg['XLS_ORDER_DETAIL'])
# push_xls_to_ss(wb_file, app_cfg['XLS_CUSTOMER'])
# exit()
return
def data_scrubber(my_ws, path_to_file):
#
# Data Scrubber
#
# Initialize and Get Header Row
list_of_rows = []
list_of_row = []
list_headers = []
list_of_rows.append(my_ws.row(0))
list_headers.append(my_ws.row_values(0, 0))
print()
print(list_of_rows)
print("Headers\t", list_headers)
print()
# Open and Map the XLS file we are scrubbing
run_dir, my_file = ntpath.split(path_to_file)
my_map = build_sheet_map(my_file, sheet_map, 'XLS_SUBSCRIPTIONS', run_dir)
# List comprehension replacement for above
# Strip out the columns from the sheet map that we don't need
my_col_map = [x for x in my_map if x[1] == 'XLS_SUBSCRIPTIONS']
for my_row in range(1, my_ws.nrows):
print()
print('Row #:', my_row)
for my_col in my_col_map:
my_col_name = list_headers[0][my_col[2]]
dest_cell_type = my_col[4]
dest_cell_val = None
src_cell = my_ws.cell(my_row, my_col[2])
src_cell_type = my_ws.cell_type(my_row, my_col[2])
src_cell_val = my_ws.cell_value(my_row, my_col[2])
print()
print('\t\t' + my_col_name + ' source type/value is', src_cell)
print('\t\tDest type needs to be', dest_cell_type)
if src_cell_type == xlrd.XL_CELL_DATE:
print("\t\tDate", my_ws.cell_value(my_row, my_col[2]),
' needs to be a ' + dest_cell_type)
# datetime_object = datetime.strptime('Jun 1 2005 1:33PM', '%b %d %Y %I:%M%p')
elif src_cell_type == xlrd.XL_CELL_NUMBER:
# All numbers from xlrd are python floats so we need to check if we need an int
if dest_cell_type == 'int':
dest_cell_val = int(src_cell_val)
print('\t\tConverted', src_cell_val, ' to ', dest_cell_val)
elif src_cell_type == xlrd.XL_CELL_TEXT:
if dest_cell_type == 'currency' or dest_cell_type == 'int':
try:
int(src_cell_val)
dest_cell_val = int(src_cell_val)
print('\t\tConverted', src_cell_val, ' to ',
dest_cell_val)
except ValueError:
print("\t\tERROR: Not an currency, int or float")
dest_cell_val = 0
print('\t\tConverted', src_cell_val, ' to ',
dest_cell_val)
elif dest_cell_type == 'date':
try:
datetime.strptime(src_cell_val, '%d %b %Y')
dest_cell_val = datetime.strptime(
src_cell_val, '%d %b %Y')
print('\t\tConverted', src_cell_val, ' to ',
dest_cell_val)
except ValueError:
print('\t\t', type(src_cell_val), src_cell_val)
print('\t\tNot a date')
dest_cell_val = datetime.strptime(
'01 Jan 2000', '%d %b %Y')
print('\t\tConverted', src_cell_val, ' to ',
dest_cell_val)
elif src_cell_type == xlrd.XL_CELL_BLANK:
print("\t\tBlank", my_ws.cell_value(my_row, my_col[2]),
' needs to be a ' + dest_cell_type)
elif src_cell_type == xlrd.XL_CELL_BOOLEAN:
print("\t\tBoolean", my_ws.cell_value(my_row, my_col[2]),
' needs to be a ' + dest_cell_type)
elif src_cell_type == xlrd.XL_CELL_EMPTY:
print("\t\tEmpty", my_ws.cell_value(my_row, my_col[2]),
' needs to be a ' + dest_cell_type)
elif src_cell_type == xlrd.XL_CELL_ERROR:
print("\t\tError", my_ws.cell_value(my_row, my_col[2]),
' needs to be' + dest_cell_type)
time.sleep(0.5)
print(type(dest_cell_val), dest_cell_val)
list_of_row.append(dest_cell_val)
print(list_of_row)
list_of_rows.append(list_of_row)
print(list_of_rows)
time.sleep(2)
return list_of_rows
