def main():
as_wb, as_ws = open_wb(app_cfg['XLS_AS_DELIVERY_STATUS'])
cust_wb, cust_ws = open_wb(app_cfg['XLS_BOOKINGS'])
sub_wb, sub_ws = open_wb(app_cfg['XLS_SUBSCRIPTIONS'])
print()
print('RAW Input Data')
print("\tAS Fixed SKUs Rows:", as_ws.nrows)
print('\tBookings Rows:', cust_ws.nrows)
print('\tSubscription Rows:', sub_ws.nrows)
#
# Build a Team Dict
#
team_dict = build_coverage_dict()
#
# Create a SKU Filter
#
# Options Are: Product / Software / Service / SaaS / All SKUs
sku_filter_val = 'All SKUs'
tmp_dict = build_sku_dict()
sku_filter_dict = {}
for key, val in tmp_dict.items():
if val[0] == sku_filter_val:
sku_filter_dict[key] = val
elif sku_filter_val == 'All SKUs':
# Selects ALL Interesting SKUs
sku_filter_dict[key] = val
print()
print('SKU Filter set to:', sku_filter_val)
print()
#
# Build a xref dict of valid customer ids for lookup by SO and ERP Name
#
xref_cust_name = {}
xref_so = {}
for row_num in range(1, cust_ws.nrows):
cust_id = cust_ws.cell_value(row_num, 15)
cust_erp_name = cust_ws.cell_value(row_num, 13)
cust_so = cust_ws.cell_value(row_num, 11)
# Only add valid ID/Name Pairs to the reference
if cust_id == '-999' or cust_id == '':
continue
if cust_erp_name not in xref_cust_name:
xref_cust_name[cust_erp_name] = cust_id
if (cust_so, cust_erp_name) not in xref_so:
xref_so[(cust_so, cust_erp_name)] = cust_id
#
# Process Main Bookings File
#
cntr = 0
cust_db = {}
cust_alias_db = {}
so_dict = {}
#
# Main loop over the bookings data starts here
#
for row_num in range(1, cust_ws.nrows):
# Gather the fields we want
cust_id = cust_ws.cell_value(row_num, 15)
cust_erp_name = cust_ws.cell_value(row_num, 13)
cust_ultimate_name = cust_ws.cell_value(row_num, 14)
cust_so = cust_ws.cell_value(row_num, 11)
cust_sku = cust_ws.cell_value(row_num, 19)
cust_sales_lev_1 = cust_ws.cell_value(row_num, 3)
cust_sales_lev_2 = cust_ws.cell_value(row_num, 4)
cust_sales_lev_3 = cust_ws.cell_value(row_num, 5)
cust_sales_lev_4 = cust_ws.cell_value(row_num, 6)
cust_sales_lev_5 = cust_ws.cell_value(row_num, 7)
cust_sales_lev_6 = cust_ws.cell_value(row_num, 8)
cust_acct_mgr = cust_ws.cell_value(row_num, 9)
# Grab this SO number in a simple dict {so:(cust_id, cust_id)
if cust_so not in so_dict:
# so_dict[cust_so] = ((cust_id, cust_erp_name),)
so_dict[cust_so] = ((cust_id, cust_erp_name, cust_sku), )
else:
# so_dict[cust_so] = so_dict[cust_so] + ((cust_id, cust_erp_name),)
so_dict[cust_so] = so_dict[cust_so] + (
(cust_id, cust_erp_name, cust_sku), )
# Do we have a missing or bad cust_id try to look one up
if cust_id == '' or cust_id == '-999':
if cust_erp_name in xref_cust_name:
cust_id = xref_cust_name[cust_erp_name]
if (cust_so, cust_erp_name) in xref_so:
cust_id = xref_so[(cust_so, cust_erp_name)]
# If id is still bad flag cust_id as UNKNOWN
if cust_id == '' or cust_id == '-999':
cust_id = 'UNKNOWN'
#
# Check cust_db
# {cust_id: Customer_obj}
#
# Is this a new cust_id ?
if cust_id not in cust_db:
# Create a new cust_id object and basic record
cust_db[cust_id] = Customer(cust_id)
cust_db[cust_id].sales_lev_1 = cust_sales_lev_1
cust_db[cust_id].sales_lev_2 = cust_sales_lev_2
cust_db[cust_id].sales_lev_3 = cust_sales_lev_3
cust_db[cust_id].sales_lev_4 = cust_sales_lev_4
cust_db[cust_id].sales_lev_5 = cust_sales_lev_5
cust_db[cust_id].sales_lev_6 = cust_sales_lev_6
sales_level = cust_sales_lev_1 + ',' + cust_sales_lev_2 + ',' + cust_sales_lev_3 + ',' + \
cust_sales_lev_4 + ',' + cust_sales_lev_5 + ',' + cust_sales_lev_6
sales_team = find_team(team_dict, sales_level)
pss = sales_team[0]
tsa = sales_team[1]
cust_db[cust_id].pss = pss
cust_db[cust_id].tsa = tsa
cust_db[cust_id].am = cust_acct_mgr
# Is this a SKU we want if so add_order
if cust_sku in sku_filter_dict:
cust_db[cust_id].add_order(cust_so, cust_sku)
# Add this customer_erp_name as an alias to the customer object
cust_db[cust_id].add_alias(cust_erp_name)
# Add this name to an easy alias lookup dict
if cust_erp_name not in cust_alias_db:
cust_alias_db[cust_erp_name] = cust_id
print('Unique Customer IDs with filter of', " '" + sku_filter_val + "' :",
len(cust_db))
print("Customer Unique Customer Names: ", len(cust_alias_db))
print("Unique Sales Order Numbers: ", len(so_dict))
# A quick check on customer ids -
id_list = [['Customer ID', 'Customer Aliases']]
for cust_id, cust_obj in cust_db.items():
alias_list = []
alias_str = ''
cust_aliases = cust_obj.aliases
for cust_alias in cust_aliases:
alias_list.append(cust_alias)
alias_str = alias_str + cust_alias + ' : '
alias_str = alias_str[:-3]
id_list.append([cust_id, alias_str])
push_list_to_xls(id_list, 'log_Unique_Cust_IDs.xlsx')
#
# Get SAAS Data from the BU Sheet
#
# saas_rows = get_list_from_ss(app_cfg['SS_SAAS'])
# test_list = []
#
# for x in saas_rows:
# for y in x:
# print(type(y), y)
# time.sleep(.4)
# exit()
#
# for row_num in range(1, len(saas_rows)):
# try:
# tmp_val = [saas_rows[row_num][1], str(int(saas_rows[row_num][2]))]
# except ValueError:
# tmp_val = ['Bad Data in row ' + str(row_num), saas_rows[row_num][1]]
#
# test_list.append(tmp_val)
# push_list_to_xls(test_list, 'saas_status.xlsx')
#
# saas_status_list = [['status', 'cust name', 'saas so', 'cust id']]
# for row in test_list:
# saas_name = row[0]
# saas_so = row[1]
# saas_status = ''
# saas_cust_id = ''
#
# if saas_name.find('Bad Data') != -1:
# saas_status = 'Bad Data in SaaS Sheet', saas_name, saas_so, saas_cust_id
# else:
# if saas_name in cust_alias_db:
# saas_cust_id = cust_alias_db[saas_name]
# saas_status = 'Matched Data with SaaS Sheet', saas_name, saas_so, saas_cust_id
# else:
# saas_status = 'No Matching Data from SaaS Sheet', saas_name, saas_so, saas_cust_id
#
# saas_status_list.append(saas_status)
# push_list_to_xls(saas_status_list, 'log_saas_data_matches.xlsx')
# # Display Customer IDs and Aliases
# for cust_id, cust_obj in cust_db.items():
# if len(cust_obj.aliases) > 1:
# print()
# print('Customer ID', cust_id, ' has the following aliases')
# for name in cust_obj.aliases:
# print('\t\t', name)
# time.sleep(1)
# # Display Sales Order info
# for cust_id, cust_obj in cust_db.items():
# if len(cust_obj.orders) > 1:
# print()
# print('Customer ID', cust_id, cust_obj.aliases, ' has the following orders')
# for my_order, my_skus in cust_obj.orders.items():
# print('\t', 'SO Num:', my_order, 'SKUs', my_skus)
# time.sleep(1)
#
# Process AS AS-F SKU File - match bookings SO and (AS SO / PID) numbers
# and make a list of tuples for each cust_id
#
as_db = {}
so_status_list = [[
'AS SO Number', 'AS Customer Name', "AS PID", 'Duplicate ?',
'Match in Booking ?'
]]
as_zombie_so = []
as_so_found_cntr = 0
as_so_not_found_cntr = 0
as_so_duplicate_cntr = 0
as_so_unique_cntr = 0
for row_num in range(1, as_ws.nrows):
my_as_info_list = []
# Gather the fields we want
as_pid = as_ws.cell_value(row_num, 0)
as_cust_name = as_ws.cell_value(row_num, 2)
as_so = as_ws.cell_value(row_num, 19)
# Just a check
if as_so in as_db:
dupe = 'Duplicate SO'
as_so_duplicate_cntr += 1
else:
dupe = 'Unique SO'
as_so_unique_cntr += 1
if as_so not in as_db:
my_as_info_list.append((as_pid, as_cust_name))
as_db[as_so] = my_as_info_list
else:
my_as_info_list = as_db[as_so]
add_it = True
for info in my_as_info_list:
if info == (as_pid, as_cust_name):
add_it = False
break
if add_it:
my_as_info_list.append((as_pid, as_cust_name))
as_db[as_so] = my_as_info_list
# Checks
if as_so not in so_dict:
so_status_list.append(
[as_so, as_cust_name, as_pid, dupe, 'NOT in Bookings'])
as_zombie_so.append([as_so, as_cust_name, as_pid])
as_so_not_found_cntr += 1
else:
so_status_list.append(
[as_so, as_cust_name, as_pid, dupe, 'FOUND in Bookings'])
as_so_found_cntr += 1
push_list_to_xls(so_status_list, 'log_AS SO_Status_List.xlsx')
print('AS SO NOT Found (Zombies):', as_so_not_found_cntr)
print('AS SO Found:', as_so_found_cntr)
print('\t AS SO Totals:', as_so_found_cntr + as_so_not_found_cntr)
print()
print('AS SO Duplicate:', as_so_duplicate_cntr)
print('AS SO Unique:', as_so_unique_cntr)
print('len of as_db', len(as_db))
#
# Update the cust_db objects with the AS data from as_db
#
found_list = 0
as_zombies = [[
'AS SO', 'AS PID', 'AS Customer Name', 'Possible Match', 'Ratio'
]]
for as_so, as_info in as_db.items():
# as_info is [so #:[(as_pid, as_cust_name),()]]
as_cust_name = as_info[0][1]
if as_so in so_dict:
cust_id = so_dict[as_so][0][0]
cust_obj = cust_db[cust_id]
found_list = found_list + len(as_info)
cust_obj.add_as_pid(as_so, as_info)
else:
# OK this AS_SO is NOT in the Main so_dict
# We need to attempt to match on as_cust_name in the customer alias dict
# We need to find the customer_id
if as_cust_name in cust_alias_db:
cust_id = cust_alias_db[as_cust_name]
cust_obj = cust_db[cust_id]
found_list = found_list + len(as_info)
cust_obj.add_as_pid(as_so, as_info)
else:
# do a fuzzy match search against all customer aliases
best_match = 0
for k, v in cust_alias_db.items():
match_ratio = fuzz.ratio(as_cust_name, k)
if match_ratio > best_match:
possible_cust = k
best_match = match_ratio
cust_id = cust_alias_db[possible_cust]
cust_obj = cust_db[cust_id]
found_list = found_list + len(as_info)
cust_obj.add_as_pid(as_so, as_info)
cust_obj.add_as_pid(as_so, as_info)
as_zombies.append([
as_so, as_info[0][0], as_info[0][1], possible_cust,
best_match
])
print('\tNOT FOUND Customer ID for: ', as_cust_name)
push_list_to_xls(as_zombies, 'tmp_zombies.xlsx')
print('Updated cust_db with: ', found_list, ' AS SOs')
#
# Process Subscriptions and add to Customer Objects
#
for row_num in range(1, sub_ws.nrows):
# Gather the fields we want
sub_cust_name = sub_ws.cell_value(row_num, 2)
sub_id = sub_ws.cell_value(row_num, 4)
sub_start_date = sub_ws.cell_value(row_num, 6)
sub_renew_date = sub_ws.cell_value(row_num, 8)
sub_renew_status = sub_ws.cell_value(row_num, 5)
sub_monthly_rev = sub_ws.cell_value(row_num, 10)
year, month, day, hour, minute, second = xlrd.xldate_as_tuple(
sub_start_date, sub_wb.datemode)
sub_start_date = datetime(year, month, day)
year, month, day, hour, minute, second = xlrd.xldate_as_tuple(
sub_renew_date, sub_wb.datemode)
sub_renew_date = datetime(year, month, day)
if sub_cust_name in cust_alias_db:
cust_id = cust_alias_db[sub_cust_name]
cust_obj = cust_db[cust_id]
sub_info = [
sub_id, sub_cust_name, sub_start_date, sub_renew_date,
sub_renew_status, sub_monthly_rev
]
cust_obj.add_sub_id(sub_info)
#
# Make the Magic List
#
magic_list = []
header_row = [
'Customer ID', 'AS SO', 'AS PID', 'AS Customer Name', 'Sales Level 1',
'Sales Level 2', 'PSS', 'TSA', 'AM', 'Subscription History' + ' \n' +
'Sub ID - Start Date - Renewal Date - Days to Renew - Annual Rev',
'Sub 1st Billing Date', 'Next Renewal Date', 'Days to Renew',
'Next Renewal Monthly Rev', 'Sub Current Status', 'AS Delivery Mgr',
'AS Tracking Status', 'AS Tracking Sub Status', 'AS Tracking Comments',
'AS SKU', 'AS Project Creation Date', 'AS Project Start Date',
'AS Scheduled End Date',
'Days from 1st Sub Billing to AS Project Start'
]
magic_list.append(header_row)
print(magic_list)
x = 0
today = datetime.today()
for cust_id, cust_obj in cust_db.items():
cust_aliases = cust_obj.aliases
as_pids = cust_obj.as_pids
sub_ids = cust_obj.subs
pss = cust_obj.pss
tsa = cust_obj.tsa
am = cust_obj.am
sales_lev1 = cust_obj.sales_lev_1
sales_lev2 = cust_obj.sales_lev_2
if len(as_pids) == 0:
# No AS PID info available
sub_summary, billing_start_date, next_renewal_date, days_to_renew, renewal_rev, sub_renew_status = process_sub_info(
cust_obj.subs)
magic_row = [
cust_id, '', 'AS Info Unavailable', cust_aliases[0],
sales_lev1, sales_lev2, pss, tsa, am, sub_summary,
billing_start_date, next_renewal_date, days_to_renew,
renewal_rev, sub_renew_status, '', '', '', '', '', '', '', '',
''
]
magic_list.append(magic_row)
else:
# Let's look at the AS PIDs in cust_obj
for so, as_pid_info in as_pids.items():
# We will make a row for each AS SO
for as_detail in as_pid_info:
magic_row = []
as_so = so
as_pid = as_detail[0]
as_cust_name = as_detail[1]
sub_summary, billing_start_date, next_renewal_date, days_to_renew, renewal_rev, sub_renew_status = process_sub_info(
cust_obj.subs)
# Go get additional AS Info
as_tracking_status = ''
as_tracking_sub_status = ''
as_tracking_comments = ''
as_dm = ''
as_project_start = ''
as_scheduled_end = ''
as_project_created = ''
as_sku = ''
for row_num in range(1, as_ws.nrows):
if as_pid == as_ws.cell_value(row_num, 0):
as_dm = as_ws.cell_value(row_num, 1)
as_tracking_status = as_ws.cell_value(row_num, 7)
as_tracking_sub_status = as_ws.cell_value(
row_num, 8)
as_tracking_comments = as_ws.cell_value(row_num, 9)
as_sku = as_ws.cell_value(row_num, 14)
as_project_start = as_ws.cell_value(row_num, 26)
as_scheduled_end = as_ws.cell_value(row_num, 27)
as_project_created = as_ws.cell_value(row_num, 28)
year, month, day, hour, minute, second = xlrd.xldate_as_tuple(
as_project_start, as_wb.datemode)
as_project_start = datetime(year, month, day)
year, month, day, hour, minute, second = xlrd.xldate_as_tuple(
as_scheduled_end, as_wb.datemode)
as_scheduled_end = datetime(year, month, day)
year, month, day, hour, minute, second = xlrd.xldate_as_tuple(
as_project_created, as_wb.datemode)
as_project_created = datetime(year, month, day)
break
if isinstance(billing_start_date, datetime) and isinstance(
as_project_start, datetime):
time_to_service = billing_start_date - as_project_start
else:
time_to_service = ''
magic_row = [
cust_id, so, as_pid, as_cust_name, sales_lev1,
sales_lev2, pss, tsa, am, sub_summary,
billing_start_date, next_renewal_date, days_to_renew,
renewal_rev, sub_renew_status, as_dm,
as_tracking_status, as_tracking_sub_status,
as_tracking_comments, as_sku, as_project_created,
as_project_start, as_scheduled_end, time_to_service
]
magic_list.append(magic_row)
# print(len(magic_list))
# print(x)
# for my_row in magic_list:
# for my_col in my_row:
# print (my_col, type(my_col))
# time.sleep(.1)
# exit()
push_list_to_xls(magic_list, 'magic.xlsx')
#
# Make a NEW customer list
#
cust_as_of = 201910
new_cust_dict = {}
new_cust_list = [[
'Booking Period', 'Customer ID', 'Customer Name', 'PSS', 'PSS email',
'TSA', 'TSA email', 'AM'
]]
for row_num in range(1, cust_ws.nrows):
cust_id = cust_ws.cell_value(row_num, 15)
if cust_id in cust_db:
booking_period = cust_ws.cell_value(row_num, 2)
cust_name = cust_ws.cell_value(row_num, 13)
pss = cust_db[cust_id].pss
tsa = cust_db[cust_id].tsa
am = cust_db[cust_id].am
if int(cust_ws.cell_value(row_num, 2)) >= cust_as_of:
new_cust_list.append(
[booking_period, cust_id, cust_name, pss, tsa, am])
push_list_to_xls(new_cust_list, 'tmp_New_Customer_list.xlsx')
return
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 main():
as_wb, as_ws = open_wb(app_cfg['TESTING_TA_AS_FIXED_SKU_RAW'])
cust_wb, cust_ws = open_wb(app_cfg['TESTING_BOOKINGS_RAW_WITH_SO'])
sub_wb, sub_ws = open_wb(app_cfg['TESTING_RAW_SUBSCRIPTIONS'])
print()
print('RAW Input Data')
print("\tAS Fixed SKUs Rows:", as_ws.nrows)
print('\tBookings Rows:', cust_ws.nrows)
print('\tSubscription Rows:', sub_ws.nrows)
#
# Build a Team Dict
#
team_dict = build_coverage_dict()
#
# Create a SKU Filter
#
# Options Are: Product / Software / Service / SaaS / All SKUs
sku_filter_val = 'All SKUs'
tmp_dict = build_sku_dict()
sku_filter_dict = {}
for key, val in tmp_dict.items():
if val[0] == sku_filter_val:
sku_filter_dict[key] = val
elif sku_filter_val == 'All SKUs':
# Selects ALL Interesting SKUs
sku_filter_dict[key] = val
print()
print('SKU Filter set to:', sku_filter_val)
print()
#
# Build a xref dict of valid customer ids for lookup by SO and ERP Name
#
xref_cust_name = {}
xref_so = {}
for row_num in range(1, cust_ws.nrows):
cust_id = cust_ws.cell_value(row_num, 15)
cust_erp_name = cust_ws.cell_value(row_num, 13)
cust_so = cust_ws.cell_value(row_num, 11)
# Only add valid ID/Name Pairs to the reference
if cust_id == '-999' or cust_id == '':
continue
if cust_erp_name not in xref_cust_name:
xref_cust_name[cust_erp_name] = cust_id
if (cust_so, cust_erp_name) not in xref_so:
xref_so[(cust_so, cust_erp_name)] = cust_id
#
# Process Main Bookings File
#
cntr = 0
cust_db = {}
cust_alias_db = {}
so_dict = {}
#
# Main loop over the bookings data starts here
#
for row_num in range(1, cust_ws.nrows):
# Gather the fields we want
cust_id = cust_ws.cell_value(row_num, 15)
cust_erp_name = cust_ws.cell_value(row_num, 13)
cust_ultimate_name = cust_ws.cell_value(row_num, 14)
cust_so = cust_ws.cell_value(row_num, 11)
cust_sku = cust_ws.cell_value(row_num, 19)
cust_sales_lev_1 = cust_ws.cell_value(row_num, 3)
cust_sales_lev_2 = cust_ws.cell_value(row_num, 4)
cust_sales_lev_3 = cust_ws.cell_value(row_num, 5)
cust_sales_lev_4 = cust_ws.cell_value(row_num, 6)
cust_sales_lev_5 = cust_ws.cell_value(row_num, 7)
cust_sales_lev_6 = cust_ws.cell_value(row_num, 8)
cust_acct_mgr = cust_ws.cell_value(row_num, 9)
# Grab this SO number in a simple dict {so:(cust_id, cust_id)
if cust_so not in so_dict:
so_dict[cust_so] = ((cust_id, cust_erp_name), )
else:
so_dict[cust_so] = so_dict[cust_so] + ((cust_id, cust_erp_name), )
# Do we have a missing or bad cust_id try to look one up
if cust_id == '' or cust_id == '-999':
if cust_erp_name in xref_cust_name:
cust_id = xref_cust_name[cust_erp_name]
if (cust_so, cust_erp_name) in xref_so:
cust_id = xref_so[(cust_so, cust_erp_name)]
# If id is still bad flag cust_id as UNKNOWN
if cust_id == '' or cust_id == '-999':
cust_id = 'UNKNOWN'
#
# Check cust_db
# {cust_id: Customer_obj}
#
# Is this a new cust_id ?
if cust_id not in cust_db:
# Create a new cust_id object and basic record
cust_db[cust_id] = Customer(cust_id)
cust_db[cust_id].sales_lev_1 = cust_sales_lev_1
cust_db[cust_id].sales_lev_2 = cust_sales_lev_2
cust_db[cust_id].sales_lev_3 = cust_sales_lev_3
cust_db[cust_id].sales_lev_4 = cust_sales_lev_4
cust_db[cust_id].sales_lev_5 = cust_sales_lev_5
cust_db[cust_id].sales_lev_6 = cust_sales_lev_6
sales_level = cust_sales_lev_1 + ',' + cust_sales_lev_2 + ',' + cust_sales_lev_3 + ',' + \
cust_sales_lev_4 + ',' + cust_sales_lev_5 + ',' + cust_sales_lev_6
sales_team = find_team(team_dict, sales_level)
pss = sales_team[0]
tsa = sales_team[1]
cust_db[cust_id].pss = pss
cust_db[cust_id].tsa = tsa
cust_db[cust_id].am = cust_acct_mgr
# Is this a SKU we want if so add_order
if cust_sku in sku_filter_dict:
cust_db[cust_id].add_order(cust_so, cust_sku)
# Add this customer_erp_name as an alias to the customer object
cust_db[cust_id].add_alias(cust_erp_name)
# Add this name to an easy alias lookup dict
if cust_erp_name not in cust_alias_db:
cust_alias_db[cust_erp_name] = cust_id
print('Unique Customer IDs with filter of', " '" + sku_filter_val + "' :",
len(cust_db))
print("Customer Unique Customer Names: ", len(cust_alias_db))
print("Unique Sales Order Numbers: ", len(so_dict))
# A quick check on customer ids -
id_list = [['Customer ID', 'Customer Aliases']]
for cust_id, cust_obj in cust_db.items():
alias_list = []
alias_str = ''
cust_aliases = cust_obj.aliases
for cust_alias in cust_aliases:
alias_list.append(cust_alias)
alias_str = alias_str + cust_alias + ' : '
alias_str = alias_str[:-3]
id_list.append([cust_id, alias_str])
push_list_to_xls(id_list, 'log_Unique_Cust_IDs.xlsx')
# # Display Customer IDs and Aliases
# for cust_id, cust_obj in cust_db.items():
# if len(cust_obj.aliases) > 1:
# print()
# print('Customer ID', cust_id, ' has the following aliases')
# for name in cust_obj.aliases:
# print('\t\t', name)
# time.sleep(1)
# # Display Sales Order info
# for cust_id, cust_obj in cust_db.items():
# if len(cust_obj.orders) > 1:
# print()
# print('Customer ID', cust_id, cust_obj.aliases, ' has the following orders')
# for my_order, my_skus in cust_obj.orders.items():
# print('\t', 'SO Num:', my_order, 'SKUs', my_skus)
# time.sleep(1)
#
# Process AS AS-F SKU File - match bookings SO and (AS SO / PID) numbers
# and make a list of tuples for each cust_id
#
as_db = {}
so_status_list = [[
'AS SO Number', 'AS Customer Name', "AS PID", 'Duplicate ?',
'Match in Booking ?'
]]
as_zombie_so = []
as_so_found_cntr = 0
as_so_not_found_cntr = 0
as_so_duplicate_cntr = 0
as_so_unique_cntr = 0
for row_num in range(1, as_ws.nrows):
my_as_info_list = []
# Gather the fields we want
as_pid = as_ws.cell_value(row_num, 0)
as_cust_name = as_ws.cell_value(row_num, 2)
as_so = as_ws.cell_value(row_num, 19)
# Just a check
if as_so in as_db:
dupe = 'Duplicate SO'
as_so_duplicate_cntr += 1
else:
dupe = 'Unique SO'
as_so_unique_cntr += 1
if as_so not in as_db:
my_as_info_list.append((as_pid, as_cust_name))
as_db[as_so] = my_as_info_list
else:
my_as_info_list = as_db[as_so]
add_it = True
for info in my_as_info_list:
if info == (as_pid, as_cust_name):
add_it = False
break
if add_it:
my_as_info_list.append((as_pid, as_cust_name))
as_db[as_so] = my_as_info_list
# Checks
if as_so not in so_dict:
so_status_list.append(
[as_so, as_cust_name, as_pid, dupe, 'NOT in Bookings'])
as_zombie_so.append([as_so, as_cust_name, as_pid])
as_so_not_found_cntr += 1
else:
so_status_list.append(
[as_so, as_cust_name, as_pid, dupe, 'FOUND in Bookings'])
as_so_found_cntr += 1
push_list_to_xls(so_status_list, 'log_AS SO_Status_List.xlsx')
print('AS SO NOT Found (Zombies):', as_so_not_found_cntr)
print('AS SO Found:', as_so_found_cntr)
print('\t AS SO Totals:', as_so_found_cntr + as_so_not_found_cntr)
print()
print('AS SO Duplicate:', as_so_duplicate_cntr)
print('AS SO Unique:', as_so_unique_cntr)
#
# Update the cust_db objects with the AS data from as_db
#
found_list = []
for cust_id, cust_obj in cust_db.items():
for so, skus in cust_obj.orders.items():
if so in as_db:
found_list.append(so)
cust_obj.add_as_pid(so, as_db[so])
print('Updated cust_db with: ', len(found_list), ' AS SOs')
#
# Process Subscriptions and add to Customer Objects
#
for row_num in range(1, sub_ws.nrows):
# Gather the fields we want
sub_cust_name = sub_ws.cell_value(row_num, 2)
sub_id = sub_ws.cell_value(row_num, 4)
sub_start_date = sub_ws.cell_value(row_num, 6)
sub_renew_date = sub_ws.cell_value(row_num, 8)
sub_renew_status = sub_ws.cell_value(row_num, 5)
sub_monthly_rev = sub_ws.cell_value(row_num, 10)
year, month, day, hour, minute, second = xlrd.xldate_as_tuple(
sub_start_date, sub_wb.datemode)
sub_start_date = datetime(year, month, day)
year, month, day, hour, minute, second = xlrd.xldate_as_tuple(
sub_renew_date, sub_wb.datemode)
sub_renew_date = datetime(year, month, day)
if sub_cust_name in cust_alias_db:
cust_id = cust_alias_db[sub_cust_name]
cust_obj = cust_db[cust_id]
sub_info = [
sub_id, sub_cust_name, sub_start_date, sub_renew_date,
sub_renew_status, sub_monthly_rev
]
cust_obj.add_sub_id(sub_info)
#
# Make the Magic List
#
magic_list = []
header_row = [
'Customer ID', 'AS SO', 'AS PID', 'AS Customer Name', 'PSS', 'TSA',
'AM', 'Upcoming Renewal Info' + ' \n' +
'Sub ID - Start Date - Renewal Date - Days to Renew - Annual Rev',
' Next Renewal Date', 'Days to Renew', 'Subscription Status',
'AS Delivery Mgr', 'AS Tracking Status', 'AS Tracking Sub Status',
'AS Tracking Comments'
]
magic_list.append(header_row)
print(magic_list)
x = 0
today = datetime.today()
for cust_id, cust_obj in cust_db.items():
cust_aliases = cust_obj.aliases
as_pids = cust_obj.as_pids
sub_ids = cust_obj.subs
pss = cust_obj.pss
tsa = cust_obj.tsa
am = cust_obj.am
if len(as_pids) == 0:
# No AS PID info available
sub_summary, next_renewal_date, days_to_renew, sub_renew_status = process_sub_info(
cust_obj.subs)
magic_row = [
cust_id, '', 'AS Info Unavailable', cust_aliases[0], pss, tsa,
am, sub_summary, next_renewal_date, days_to_renew,
sub_renew_status, '', '', '', ''
]
magic_list.append(magic_row)
else:
# Let's look at the AS PIDs in cust_obj
for so, as_pid_info in as_pids.items():
# We will make a row for each AS SO
for as_detail in as_pid_info:
magic_row = []
as_so = so
as_pid = as_detail[0]
as_cust_name = as_detail[1]
sub_summary, next_renewal_date, days_to_renew, sub_renew_status = process_sub_info(
cust_obj.subs)
# Go get additional AS Info
as_tracking_status = ''
as_tracking_sub_status = ''
as_tracking_comments = ''
as_dm = ''
for row_num in range(1, as_ws.nrows):
if as_pid == as_ws.cell_value(row_num, 0):
as_dm = as_ws.cell_value(row_num, 1)
as_tracking_status = as_ws.cell_value(row_num, 7)
as_tracking_sub_status = as_ws.cell_value(
row_num, 8)
as_tracking_comments = as_ws.cell_value(row_num, 9)
break
magic_row = [
cust_id, so, as_pid, as_cust_name, pss, tsa, am,
sub_summary, next_renewal_date, days_to_renew,
sub_renew_status, as_dm, as_tracking_status,
as_tracking_sub_status, as_tracking_comments
]
magic_list.append(magic_row)
print(len(magic_list))
print(x)
push_list_to_xls(magic_list, 'magic.xlsx')
return
# Check cust_db
# {cust_id: Customer_obj}
#
# Is this a new cust_id ?
if cust_id not in cust_db:
# Create a new cust_id object and basic record
cust_db[cust_id] = Customer(cust_id)
cust_db[cust_id].sales_lev_1 = cust_sales_lev_1
cust_db[cust_id].sales_lev_2 = cust_sales_lev_2
cust_db[cust_id].sales_lev_3 = cust_sales_lev_3
cust_db[cust_id].sales_lev_4 = cust_sales_lev_4
cust_db[cust_id].sales_lev_5 = cust_sales_lev_5
cust_db[cust_id].sales_lev_6 = cust_sales_lev_6
sales_level = cust_sales_lev_1 + ',' + cust_sales_lev_2 + ',' + cust_sales_lev_3 + ',' + \
cust_sales_lev_4 + ',' + cust_sales_lev_5 + ',' + cust_sales_lev_6
sales_team = find_team(team_dict, sales_level)
pss = sales_team[0]
tsa = sales_team[1]
cust_db[cust_id].pss = pss
cust_db[cust_id].tsa = tsa
# Is this a SKU we want if so add_order
if cust_sku in sku_filter_dict:
cust_db[cust_id].add_order(cust_so, cust_sku)
# Add this customer_erp_name as an alias to the customer object
cust_db[cust_id].add_alias(cust_erp_name)
# Add this name to an easy alias lookup dict
if cust_erp_name not in cust_alias_db:
cust_alias_db[cust_erp_name] = cust_id
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