def get_as_skus(bookings): # Build a SKU dict as a filter tmp_dict = build_sku_dict() sku_dict = {} header_row = bookings[0] header_vals = [] for my_cell in header_row: header_vals.append(my_cell.value) # Strip out all but Service sku's for sku_key, sku_val in tmp_dict.items(): if sku_val[0] == 'Service': sku_dict[sku_key] = sku_val sku_col_header = 'Bundle Product ID' sku_col_num = 0 as_bookings = [header_row] # Get the col number that has the SKU's for idx, val in enumerate(header_vals): if val == sku_col_header: sku_col_num = idx break # Gather all the rows with AS skus for booking in bookings: if booking[sku_col_num].value in sku_dict: as_bookings.append(booking) print('All AS SKUs have been extracted from the current data!') return as_bookings
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 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
import time 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("AS Fixed SKUs Rows:", as_ws.nrows) print('Bookings Rows:', cust_ws.nrows) print('Subscription Rows:', sub_ws.nrows) # # Create a SKU Filter # # Options Are: Product / Software / Service / SaaS / * sku_filter_val = '*' 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 == '*': # Selects ALL Interesting SKUs sku_filter_dict[key] = val print('SKU Filter set to:', sku_filter_val) # # Build a xref dict of valid customer ids for lookup by SO and ERP Name # xref_cust_name = {}
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 pre_run_file_checks(run_dir=app_cfg['UPDATES_SUB_DIR']): home = os.path.join(app_cfg['HOME'], app_cfg['MOUNT_POINT'], app_cfg['MY_APP_DIR']) working_dir = app_cfg['WORKING_SUB_DIR'] update_dir = app_cfg['UPDATES_SUB_DIR'] archive_dir = app_cfg['ARCHIVES_SUB_DIR'] # Check that all key directories exist path_to_main_dir = (os.path.join(home)) if not os.path.exists(path_to_main_dir): print(path_to_main_dir, "MAIN_DIR does NOT Exist !") exit() path_to_run_dir = (os.path.join(home, run_dir)) if not os.path.exists(path_to_run_dir): print(path_to_run_dir, " WORKING_DIR does NOT Exist !") exit() path_to_updates = (os.path.join(home, update_dir)) if not os.path.exists(path_to_updates): print(path_to_updates, "UPDATES_SUB_DIR does NOT Exist !") exit() path_to_archives = (os.path.join(home, archive_dir)) if not os.path.exists(path_to_archives): print(path_to_archives, "ARCHIVE_SUB_DIR does NOT Exist !") exit() # OK directories are there any files ? if not os.listdir(path_to_run_dir): print('Directory', path_to_run_dir, 'contains NO files') exit() # Get the required Files to begin processing from app_cfg (settings.py) files_needed = {} # Do we have RAW files to process ? for var in app_cfg: if var.find('RAW') != -1: # Look for any config var containing the word 'RAW' and assume they are "Missing' files_needed[app_cfg[var]] = 'Missing' # See if we have the files_needed are there and they have consistent dates (date_list) run_files = os.listdir(path_to_run_dir) date_list = [] for file_needed, status in files_needed.items(): for run_file in run_files: date_tag = run_file[-13:-13 + 8] # Grab the date if any run_file = run_file[:len(run_file) - 14] # Grab the name without the date if run_file == file_needed: date_list.append(date_tag) # Grab the date files_needed[file_needed] = 'Found' break # All time stamps the same ? base_date = date_list[0] for date_stamp in date_list: if date_stamp != base_date: print('ERROR: Inconsistent date stamp(s) found') exit() # Do we have all the files we need ? for file_name, status in files_needed.items(): if status != 'Found': print("ERROR: Filename ", "'" + file_name, "MM-DD-YY' is missing from directory", "'" + run_dir + "'") exit() # Read the config_dict.json file try: with open(os.path.join(path_to_run_dir, app_cfg['META_DATA_FILE'])) as json_input: config_dict = json.load(json_input) print(config_dict) print(type(config_dict)) print(config_dict['last_run_dir']) except: print('No config_dict file found.') # Since we have a consistent date then Create the json file for config_data.json. # Put the time_stamp in it config_dict = { 'data_time_stamp': base_date, 'last_run_dir': path_to_run_dir, 'files_scrubbed': 'never' } with open(os.path.join(path_to_run_dir, app_cfg['META_DATA_FILE']), 'w') as json_output: json.dump(config_dict, json_output) # Delete all previous tmp_ files for file_name in run_files: if file_name[0:4] == 'tmp_': os.remove(os.path.join(path_to_run_dir, file_name)) # Here is what we have - All things should be in place print('Our directories:') print('\tPath to Main Dir:', path_to_main_dir) print('\tPath to Updates Dir:', path_to_updates) print('\tPath to Archives Dir:', path_to_archives) print('\tPath to Run Dir:', path_to_run_dir) # Process the RAW data (Renewals and Bookings) # Clean up rows, combine multiple Bookings files, add custom table names processing_date = date_list[0] file_paths = [] bookings = [] subscriptions = [] as_status = [] print() print('We are processing files:') # We need to make a sku_filter_dict here tmp_dict = build_sku_dict() sku_filter_dict = {} for key, val in tmp_dict.items(): if val[0] == 'Service': sku_filter_dict[key] = val # Main loop to process files for file_name in files_needed: file_path = file_name + ' ' + processing_date + '.xlsx' file_path = os.path.join(path_to_run_dir, file_path) file_paths.append(file_path) my_wb, my_ws = open_wb(file_name + ' ' + processing_date + '.xlsx', run_dir) print('\t\t', file_name + '', processing_date + '.xlsx', ' has ', my_ws.nrows, ' rows and ', my_ws.ncols, 'columns') if file_name.find('Bookings') != -1: # For the Bookings start_row is here start_row = 3 start_col = 1 for row in range(start_row, my_ws.nrows): bookings.append(my_ws.row_slice(row, start_col)) elif file_name.find('Subscriptions') != -1: # This raw sheet starts on row num 0 for row in range(0, my_ws.nrows): subscriptions.append(my_ws.row_slice(row)) elif file_name.find('AS Delivery Status') != -1: # This AS-F raw sheet starts on row num 0 # Grab the header row as_status.append(my_ws.row_slice(0)) for row in range(1, my_ws.nrows): # Check to see if this is a TA SKU if my_ws.cell_value(row, 14) in sku_filter_dict: as_status.append(my_ws.row_slice(row)) # For the Subscriptions sheet we need to convert # col 6 & 8 to DATE from STR # col 10 (monthly rev) to FLOAT from STR # subscriptions_scrubbed = [] for row_num, my_row in enumerate(subscriptions): my_new_row = [] for col_num, my_cell in enumerate(my_row): if row_num == 0: # Is this the header row ? my_new_row.append(my_cell.value) continue if col_num == 9 or col_num == 11: tmp_val = datetime.strptime(my_cell.value, '%d %b %Y') # tmp_val = datetime.strptime(my_cell.value, '%m/%d/%Y') elif col_num == 13: tmp_val = my_cell.value try: tmp_val = float(tmp_val) except ValueError: tmp_val = 0 else: tmp_val = my_cell.value my_new_row.append(tmp_val) subscriptions_scrubbed.append(my_new_row) # Now Scrub AS Delivery Info as_status_scrubbed = [] for row_num, my_row in enumerate(as_status): my_new_row = [] for col_num, my_cell in enumerate(my_row): if row_num == 0: # Is this the header row ? my_new_row.append(my_cell.value) continue if col_num == 0: # PID tmp_val = str(int(my_cell.value)) elif col_num == 19: # SO Number tmp_val = str(int(my_cell.value)) elif col_num == 26: # Project Start Date tmp_val = datetime( *xlrd.xldate_as_tuple(my_cell.value, my_wb.datemode)) elif col_num == 27: # Scheduled End Date tmp_val = datetime( *xlrd.xldate_as_tuple(my_cell.value, my_wb.datemode)) elif col_num == 28: # Project Creation Date tmp_val = datetime( *xlrd.xldate_as_tuple(my_cell.value, my_wb.datemode)) else: tmp_val = my_cell.value my_new_row.append(tmp_val) as_status_scrubbed.append(my_new_row) # Now Scrub Bookings Data bookings_scrubbed = [] for row_num, my_row in enumerate(bookings): my_new_row = [] for col_num, my_cell in enumerate(my_row): if row_num == 0: # Is this the header row ? my_new_row.append(my_cell.value) continue if col_num == 0 or col_num == 2 or \ col_num == 11: # Fiscal Year / Fiscal Period / SO tmp_val = str(int(my_cell.value)) elif col_num == 15: # Customer ID try: tmp_val = str(int(my_cell.value)) except ValueError: tmp_val = '-999' else: tmp_val = my_cell.value my_new_row.append(tmp_val) bookings_scrubbed.append(my_new_row) # # Push the lists out to an Excel File # push_list_to_xls(bookings_scrubbed, app_cfg['XLS_BOOKINGS'], run_dir, 'ta_bookings') push_list_to_xls(subscriptions_scrubbed, app_cfg['XLS_SUBSCRIPTIONS'], run_dir, 'ta_subscriptions') push_list_to_xls(as_status_scrubbed, app_cfg['XLS_AS_DELIVERY_STATUS'], run_dir, 'ta_delivery') # push_xlrd_to_xls(as_status, app_cfg['XLS_AS_DELIVERY_STATUS'], run_dir, 'ta_delivery') print('We have ', len(bookings), 'bookings line items') print('We have ', len(as_status), 'AS-Fixed SKU line items') print('We have ', len(subscriptions), 'subscription line items') with open(os.path.join(path_to_run_dir, app_cfg['META_DATA_FILE'])) as json_input: config_dict = json.load(json_input) config_dict['files_scrubbed'] = 'phase_1' with open(os.path.join(path_to_run_dir, app_cfg['META_DATA_FILE']), 'w') as json_output: json.dump(config_dict, json_output) print('pre run file checks DONE!') 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
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