def get_customer(): print('Hello from main.py') # Try creating a Test_Table in the mySQL DB from Models.py tools.create_tables("Test_Table") # Try retrieving a SmartSheet ss_test_list = tools.get_list_from_ss('Tetration SKUs') print('Rows in my SmartSheet', len(ss_test_list)) # Try writing an xlsx file to local storage tools.push_list_to_xls(ss_test_list, 'my_test_sheet.xlsx') # Try opening an xlsx file from local storage xws, wb = tools.open_wb('my_test_sheet.xlsx') return ('Hello from Main.py: I read: ' + str(len(ss_test_list)) + ' rows from SmartSheets', ss_test_list)
cx_comments = cx_tracking_rows[my_row][21] telemetry_data = [] if cx_cust_name in adopt_dict: telemetry_data = adopt_dict[cx_cust_name] else: telemetry_data.append('No Telemetry Data Found') telemetry_data.append('') telemetry_data.append('') telemetry_data.append('') telemetry_data.append('') # If no telemetry then do a subscription look up if cx_cust_name in sub_by_name_dict: print(cx_cust_name, sub_by_name_dict[cx_cust_name]) exit() # sub_by_name_dict[sub_cust_name] = [sub_web_order, sub_term, sub_renewal_date, sub_status] else: pass # print(cx_cust_name,cx_cust_id, cx_pid, cx_qtr, cx_comments, telemetry_data) # print(cx_cust_name, cx_cust_id, cx_pid, cx_qtr, cx_comments) cx_tracking_list.append([cx_cust_name, cx_cust_id, cx_pid, cx_qtr, cx_comments, telemetry_data[0], telemetry_data[1], telemetry_data[2], telemetry_data[3], telemetry_data[4]]) print(cx_tracking_list) tool.push_list_to_xls(cx_tracking_list, 'jim.xlsx') exit()
def pre_run_file_checks(run_dir=app_cfg['UPDATES_SUB_DIR']): pp = pprint.PrettyPrinter(indent=4, depth=2) 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 = [] bookings_header_flag = False subscriptions_header_flag = False subscriptions = [] as_status = [] telemetry_spock = [] telemetry_strom = [] saas_lookup = {} print() print('We are processing files:') # We need to make a sku_filter_dict here tmp_dict = tool.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 = tool.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 # This header flag tells us we have been here once already # So we need to skip the header row if bookings_header_flag is False: start_row = 3 bookings_header_flag = True else: start_row = 4 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: # # For the Subscriptions start_row is here # # We are merging the EA's and Regular Subscriptions # # This header flag tells us we have been here once already # # So we need to skip the header row if subscriptions_header_flag is False: start_row = 0 subscriptions_header_flag = True else: start_row = 1 start_col = 0 for row in range(start_row, my_ws.nrows): subscriptions.append(my_ws.row_slice(row, start_col)) elif file_name.find('SPOCK_sensor_sum') != -1: # This raw sheet starts on row num 0 for row in range(0, my_ws.nrows): # Look for these rows and strip out if (my_ws.cell_value(row, 0).find('spock') != -1)or \ (my_ws.cell_value(row, 0) == 'Default'): continue else: telemetry_spock.append(my_ws.row_slice(row)) elif file_name.find('STROM_sensor_sum') != -1: # This raw sheet starts on row num 0 for row in range(0, my_ws.nrows): # Look for these rows and strip out if (my_ws.cell_value(row, 0).find('strom') != -1) or (my_ws.cell_value(row, 0) == 'Default'): continue else: telemetry_strom.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)) elif file_name.find('SaaS Customer Tracking') != -1: # Build this index for matching the SaaS Tracker with telemetry data for row in range(1, my_ws.nrows): tmp_cust_name = my_ws.cell_value(row, 2) tmp_cust_id = my_ws.cell_value(row, 3) tmp_saas_name = my_ws.cell_value(row, 4) tmp_saas_vrf = my_ws.cell_value(row, 5) tmp_saas_so = my_ws.cell_value(row, 6) tmp_saas_start_date = my_ws.cell_value(row, 8) if tmp_saas_name == '': tmp_saas_name = 'Not Yet Provisioned' saas_lookup[tmp_saas_name] = [ tmp_cust_name, tmp_saas_vrf, tmp_cust_id, tmp_saas_so, tmp_saas_start_date ] # # All raw data now read in # # Start scrubbing the raw data # Scrub Subscriptions # For the Subscriptions sheet we need to convert # col 9 & 11 to DATE from STR # col 13 (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: # print(row_num, col_num, my_cell, type(my_cell.value)) # 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 and my_cell.ctype == xlrd.XL_CELL_DATE: # Project Start Date tmp_val = datetime( *xlrd.xldate_as_tuple(my_cell.value, my_wb.datemode)) elif col_num == 27 and my_cell.ctype == xlrd.XL_CELL_DATE: # Scheduled End Date tmp_val = datetime( *xlrd.xldate_as_tuple(my_cell.value, my_wb.datemode)) elif col_num == 28 and my_cell.ctype == xlrd.XL_CELL_DATE: # 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 Num 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' elif col_num == 12: # Web Order Num try: tmp_val = str(int(my_cell.value)) except ValueError: tmp_val = 'UNKNOWN' else: tmp_val = my_cell.value my_new_row.append(tmp_val) bookings_scrubbed.append(my_new_row) # # Scrub the Telemetry sheets # Merge the telemetry sheets and ADD a column # First the DR SPOCK List # telemetry_scrubbed = [] time_stamp = datetime.strptime(processing_date, '%m-%d-%y') for row_num, my_row in enumerate(telemetry_spock): my_new_row = [] tmp_cust_name = '' tmp_cust_id = '' tmp_saas_start_date = '' tmp_saas_so = '' tmp_sub_id = '' for col_num, my_cell in enumerate(my_row): if row_num == 0: tmp_val = my_cell.value else: if col_num >= 1: tmp_val = int(my_cell.value) else: # This is column 0 which has the SaaS platform customer short name tmp_val = my_cell.value if tmp_val in saas_lookup: tmp_cust_name = saas_lookup[tmp_val][0] try: tmp_saas_so = str(int(saas_lookup[tmp_val][3])) except: tmp_saas_so = str(saas_lookup[tmp_val][3]) # Format the Customer ID and Start Date if saas_lookup[tmp_val][2] != '': tmp_cust_id = int(saas_lookup[tmp_val][2]) else: tmp_cust_id = saas_lookup[tmp_val][2] if saas_lookup[tmp_val][4] != '': tmp_saas_start_date = saas_lookup[tmp_val][4] tmp_saas_start_date = datetime( *xlrd.xldate_as_tuple(tmp_saas_start_date, my_wb.datemode)) else: tmp_saas_start_date = saas_lookup[tmp_val][4] my_new_row.append(tmp_val) # saas_lookup[tmp_saas_name] = [tmp_cust_name, tmp_saas_vrf, tmp_cust_id, # tmp_saas_so, tmp_saas_start_date] # Put in a header row if row_num == 0: # my_new_row.insert(0, 'As_of') my_new_row.insert(0, 'Type') my_new_row.insert(1, 'Customer Name') my_new_row.insert(2, 'Customer ID') my_new_row.insert(3, 'Sales Order') my_new_row.insert(4, 'Subscription ID') my_new_row.insert(5, 'Start Date Requested') else: # my_new_row.insert(0, time_stamp) my_new_row.insert(0, 'DR') my_new_row.insert(1, tmp_cust_name) my_new_row.insert(2, tmp_cust_id) my_new_row.insert(3, tmp_saas_so) my_new_row.insert(4, tmp_sub_id) my_new_row.insert(5, tmp_saas_start_date) telemetry_scrubbed.append(my_new_row) # Now do the Non-DR STROM list for row_num, my_row in enumerate(telemetry_strom): if row_num == 0: continue my_new_row = [] tmp_cust_name = '' tmp_cust_id = '' tmp_saas_start_date = '' tmp_sub_id = '' tmp_saas_so = '' for col_num, my_cell in enumerate(my_row): if col_num >= 1: tmp_val = int(my_cell.value) else: # This is column 0 which has the SaaS platform customer short name tmp_val = my_cell.value if tmp_val in saas_lookup: tmp_cust_name = saas_lookup[tmp_val][0] try: tmp_saas_so = str(int(saas_lookup[tmp_val][3])) except: tmp_saas_so = str(saas_lookup[tmp_val][3]) # Format the Customer ID and Start Date if saas_lookup[tmp_val][2] != '': tmp_cust_id = int(saas_lookup[tmp_val][2]) else: tmp_cust_id = saas_lookup[tmp_val][2] if saas_lookup[tmp_val][4] != '': tmp_saas_start_date = saas_lookup[tmp_val][4] tmp_saas_start_date = datetime(*xlrd.xldate_as_tuple( tmp_saas_start_date, my_wb.datemode)) else: tmp_saas_start_date = saas_lookup[tmp_val][4] my_new_row.append(tmp_val) # my_new_row.insert(0, time_stamp) my_new_row.insert(0, 'Non-DR') my_new_row.insert(1, tmp_cust_name) my_new_row.insert(2, tmp_cust_id) my_new_row.insert(3, tmp_saas_so) my_new_row.insert(4, tmp_sub_id) my_new_row.insert(5, tmp_saas_start_date) telemetry_scrubbed.append(my_new_row) # # Push the lists out to an Excel File # tool.push_list_to_xls(telemetry_scrubbed, app_cfg['XLS_TELEMETRY'], run_dir, 'ta_telemetry') tool.push_list_to_xls(bookings_scrubbed, app_cfg['XLS_BOOKINGS'], run_dir, 'ta_bookings') tool.push_list_to_xls(subscriptions_scrubbed, app_cfg['XLS_SUBSCRIPTIONS'], run_dir, 'ta_subscriptions') tool.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(telemetry_scrubbed), 'telemetry line items') print('We have ', len(as_status), 'AS-Fixed SKU Delivery line items') print('We have ', len(subscriptions), 'subscription line items') msg = 'We have ' + str(len(bookings)) + ' bookings 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 msg
def main(): as_wb, as_ws = tool.open_wb(app_cfg['XLS_AS_DELIVERY_STATUS']) cust_wb, cust_ws = tool.open_wb(app_cfg['XLS_BOOKINGS']) sub_wb, sub_ws = tool.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 = tool.build_coverage_dict() # # Create a SKU Filter # # Options Are: Product / Software / Service / SaaS / All SKUs sku_filter_val = 'All SKUs' tmp_dict = tool.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 = tool.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 tmp_list = [['erp customer name','customer id']] for key, val in cust_alias_db.items(): tmp_list.append([key, val]) tool.push_list_to_xls(tmp_list, 'tmp_unique_customer_names.xlsx') 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]) tool.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_sku = as_ws.cell_value(row_num, 14) 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_sku)) 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_sku)) 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 tool.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) tool.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, 9) sub_renew_date = sub_ws.cell_value(row_num, 11) sub_renew_status = sub_ws.cell_value(row_num, 8) sub_monthly_rev = sub_ws.cell_value(row_num, 13) 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) if isinstance(as_project_start,float): year, month, day, hour, minute, second = xlrd.xldate_as_tuple(as_project_start, as_wb.datemode) as_project_start = datetime(year, month, day) if isinstance(as_scheduled_end, float): year, month, day, hour, minute, second = xlrd.xldate_as_tuple(as_scheduled_end, as_wb.datemode) as_scheduled_end = datetime(year, month, day) if isinstance(as_project_created, float): 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).days 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() tool.push_list_to_xls(magic_list, app_cfg['XLS_DASHBOARD']) # # Create a simple contact list for CX usage # # Create a dict of Service SKUs tmp_svc_dict = {} for k, v in sku_filter_dict.items(): if v[0] == 'Service': tmp_svc_dict[k] = v new_cust_list = [['Fiscal Year', 'Booking Qtr', 'Booking Period', 'Customer ID', 'Customer Name', 'CX PID', 'CX SKU', 'SO Num', 'PSS', 'PSS email', 'TSA', 'TSA email', 'AM']] alias_str = '' for cust_id, cust_obj in cust_db.items(): # for cust_alias in cust_obj.aliases: # cust_name = cust_alias # pss = cust_obj.pss # tsa = cust_obj.tsa # am = cust_obj.am alias_str = '' for x in cust_obj.aliases: alias_str = x + " : " + alias_str cust_name = alias_str pss = cust_obj.pss tsa = cust_obj.tsa am = cust_obj.am add_it = True for so_num, order_list in cust_obj.orders.items(): for sku in order_list: if sku in tmp_svc_dict: add_it = False new_cust_list.append(['', '', '', cust_id, cust_name, 'No PID Found', sku, so_num, pss, '', tsa, '', am]) if add_it: new_cust_list.append(['', '', '', cust_id, cust_name, 'No PID Found', '', so_num, pss, '', tsa, '', am]) # # See if we can find any CX PID Data # if len(cust_obj.as_pids) != 0: # for as_so, as_pid_list in cust_obj.as_pids.items(): # for as_pid in as_pid_list: # new_cust_list.append(['', '', '', cust_id, cust_name, as_pid[0], as_pid[2], pss, '', tsa, '', am]) # else: # new_cust_list.append(['', '', '', cust_id, cust_name, 'No PID Found', '', pss, '', tsa, '', am]) tool.push_list_to_xls(new_cust_list, 'tmp_CX Contact_list.xlsx') exit() # # Make a NEW customer list # cust_as_of = 2020 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') # push_list_to_xls(cust_db, 'tmp_All_Customers_list.xlsx') return
def build_rosetta_stone(): # # Build a Team Dict to figure out PSS/TSA # team_dict = tool.build_coverage_dict() # # Define Header Row # rosetta_list = [] rosetta_list.append(['Customer Name', 'Num Of Licenses ', '% of Sensors Installed', '% of Active Sensors', 'Adoption Factor' + '\n' + '(% Active Sensors : % of Subscription Consumed)' + '\n' + ' as of ', 'Subscription Term', 'Subscription Status', 'Days to Renew', 'PSS', 'TSA', 'AM', 'Sales Lv 1', 'Sales Lv 2', 'Telemetry Name', 'Telemetry VRF', 'Sensors Installed', 'Active Agents', 'Sub Type', 'Sub Order Num','Sub ID', 'Req Start Date', 'Renewal Date', 'CX PID', 'CX Delivery Manager', 'Customer ID']) # # Main Loop over all Customer IDs # customer_ids = Customer_Ids.query.all() print('There are', len(customer_ids), 'unique Customer IDs') print() for my_id_info in customer_ids: customer_id = my_id_info.customer_id if customer_id == 'INVALID': continue # Loop over each Alias we found for alias_num, my_alias in enumerate(my_id_info.customer_aliases): customer_name = my_alias.customer_alias rosetta_row = [] # # Perform All Queries for this Customer Alias # my_services = Services.query.filter_by(end_customer=customer_name).all() my_telemetry = Telemetry.query.filter_by(erp_cust_name=customer_name).all() my_bookings = Bookings.query.filter_by(erp_end_customer_name=customer_name).all() my_subs = sub_analysis(customer_name) if len(my_telemetry) > 1: print("\tsvc count", len(my_services)) print("\ttelemetry count", len(my_telemetry)) print("\tbookings count", len(my_bookings)) print("\tsubs count", len(my_subs)) print() time.sleep(.5) # # Get where this was sold and by who # cust_sales_lev_1 = my_bookings[0].sales_level_1 cust_sales_lev_2 = my_bookings[0].sales_level_2 cust_sales_lev_3 = my_bookings[0].sales_level_3 cust_sales_lev_4 = my_bookings[0].sales_level_4 cust_sales_lev_5 = my_bookings[0].sales_level_5 cust_sales_lev_6 = my_bookings[0].sales_level_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 = tool.find_team(team_dict, sales_level) pss = sales_team[0] tsa = sales_team[1] cust_acct_mgr = my_bookings[0].sales_agent_name # # Subscription Analysis # Loop over each Subscription for this Customer_Name / Customer_ID # sub_id = my_subs[0] sub_offer_name = my_subs[1] sub_start_date = my_subs[2] sub_term = my_subs[3] sub_status = my_subs[4] sub_so_num = my_subs[5] sub_renewal_date = my_subs[6] # # Check Telemetry Table and SaaS data # telemetry_name = '' telemetry_vrf_number = '' telemetry_num_of_licenses = 0 telemetry_actual_sensors_installed = 0 telemetry_inactive_agents = 0 telemetry_so = '' telemetry_start_date = '' # # Calculated Fields for Telemetry # saas_flag = False telemetry_active_agents = 0 pct_installed = 0 pct_active = 0 if len(my_telemetry) == 1: # print("\tNumber of Telemetry sessions", len(my_telemetry), my_telemetry[0].name) saas_flag = True # Telemetry Data telemetry_name = my_telemetry[0].name telemetry_vrf_number = my_telemetry[0].vrf telemetry_num_of_licenses = my_telemetry[0].licensed telemetry_actual_sensors_installed = my_telemetry[0].installed telemetry_inactive_agents = my_telemetry[0].inactive telemetry_so = my_telemetry[0].so_number telemetry_start_date = my_telemetry[0].start_date elif len(my_telemetry) > 1: print("\tERROR: More than one Telemetry session found !", len(my_telemetry)) exit() # # Make the Calculations for Telemetry # if int(telemetry_num_of_licenses) != 0: telemetry_active_agents = telemetry_actual_sensors_installed - telemetry_inactive_agents pct_installed = telemetry_actual_sensors_installed / telemetry_num_of_licenses pct_active = telemetry_active_agents / telemetry_num_of_licenses # Fields to grab from other places as_pid = '1234' as_dm = 'jim' # Calc Adoption Factor and Days to Renewal days_to_renew = '' now = datetime.datetime.now() adoption_factor = 0 if isinstance(telemetry_start_date, datetime.datetime) and \ isinstance(sub_renewal_date, datetime.datetime): days_to_renew = (sub_renewal_date - now).days sub_days_total = int(sub_term) * 30 sub_days_active = sub_days_total - days_to_renew pct_sub_expired = sub_days_active / sub_days_total adoption_factor = (pct_active / pct_sub_expired) adoption_factor = str(round(adoption_factor, 2)) + '_non$_' # # All the math is done so now # Format these all to push_list_to_xls.py # telemetry_num_of_licenses = int(telemetry_num_of_licenses) telemetry_actual_sensors_installed = int(telemetry_actual_sensors_installed) telemetry_active_agents = int(telemetry_active_agents) pct_installed = str(round(pct_installed, 1)) + '_%_' pct_active = str(round(pct_active, 1)) + '_%_' # Push out a row rosetta_row = [customer_name, telemetry_num_of_licenses, pct_installed, pct_active, adoption_factor, sub_term, sub_status, days_to_renew, pss, tsa, cust_acct_mgr, cust_sales_lev_1, cust_sales_lev_2, telemetry_name, telemetry_vrf_number, telemetry_actual_sensors_installed, telemetry_active_agents, sub_offer_name, sub_so_num, sub_id,telemetry_start_date, sub_renewal_date, as_pid, as_dm, customer_id] rosetta_list.append(rosetta_row) tool.push_list_to_xls(rosetta_list, 'stan.xlsx') print('done') my_telemetry = Telemetry.query.all() a_list = [['cust','vrf','order']] for r in my_telemetry: telemetry_cust = r.erp_cust_name vrf = r.vrf order = r.so_number found_it = False for my_row in rosetta_list: if my_row[0] == telemetry_cust: found_it = True # print('found',telemetry_cust) break if found_it == False: a_list.append([telemetry_cust, vrf, order]) print('MISSING', telemetry_cust, vrf, order) tool.push_list_to_xls(a_list, 'blanche.xlsx') return
cust_id, aliases, cust_key, fiscal_year, fiscal_qtr, sls_lv1, sls_lv2, sls_lv3, 'product', '', prod_sku, prod_rev, 0 ]) total_svc = 0 for booking in cust_obj.svc_booking: fiscal_year = booking[0] fiscal_qtr = booking[1] svc_sku = booking[2] svc_rev = booking[3] svc_type = booking[4] total_svc = total_svc + svc_rev output_list.append([ cust_id, aliases, cust_key, fiscal_year, fiscal_qtr, sls_lv1, sls_lv2, sls_lv3, 'service', svc_type, svc_sku, 0, svc_rev ]) my_summary = create_summary(total_prod, total_svc) summary_list.append([ cust_key, fiscal_year, fiscal_qtr, sls_lv1, sls_lv2, sls_lv3, my_summary[0], my_summary[1], total_prod, total_svc ]) # print(summary_list) # time.sleep(.4) push_list_to_xls(output_list, 'detail.xlsx') push_list_to_xls(summary_list, 'summary.xlsx') exit()
# We found a new AS Booking Lets capture it if r.hash_value not in base_dict: # Go get a PSS/TSA and Sales Levels sales_levels = r.sales_level_1 + ',' + r.sales_level_2 + ',' + r.sales_level_3 + ',' + r.sales_level_4 + ',' + \ r.sales_level_5 + ',' + r.sales_level_6 sales_team = tool.find_team(team_dict, sales_levels) pss = sales_team[0] tsa = sales_team[1] print('Found New', r.erp_end_customer_name, r.product_id, pss, tsa, r.sales_agent_name, r.erp_sales_order_number, r.web_order_id) new_as_list.append([r.erp_end_customer_name, r.product_id, pss, tsa, r.sales_agent_name, r.erp_sales_order_number, r.web_order_id, r.date_added]) # Push the list to excel tool.push_list_to_xls(new_as_list, 'new_AS.xlsx',) exit() # Go to the Repo and get all Customer Aliases/Customer Global IDs # Find the most recent snapshot in the repo most_recent_update = table_stats({'bookings': 'archive_bookings_repo'}) date_added = most_recent_update[0][0].strftime("%Y-%m-%d") print("Reporting on most recent snapshot", most_recent_update[0][0]) sql = "SELECT distinct erp_end_customer_name, end_customer_global_ultimate_id " + \ "FROM archive_bookings_repo " +\ "WHERE date_added = '" + date_added + "';" customer_aliases = db.engine.execute(sql)
def report_new_as_bookings(): # # Build a Team Dict of PSS/TSA # team_dict = tool.build_coverage_dict() # Create the Start and End Dates / Tables start_date = "2020-03-30" start_tbl = "tmp_booking_" + start_date.replace('-', '_') end_date = "2020-05-27" end_tbl = "tmp_booking_" + end_date.replace('-', '_') # # Re-Hash the Bookings Repo # # print("Hashing Bookings Repo") # tool.create_row_hash('Archive_Bookings_Repo') # Start Table # sql = "DROP TABLE " + start_tbl + ";" # db.engine.execute(sql) sql = "CREATE TABLE " + start_tbl + " LIKE archive_bookings_repo;" db.engine.execute(sql) sql = "INSERT INTO " + start_tbl + " SELECT * " +\ "FROM archive_bookings_repo " +\ "WHERE date_added = '" + start_date + "' ;" db.engine.execute(sql) sql = "DELETE FROM " + start_tbl + " WHERE product_id NOT LIKE 'AS%';" db.engine.execute(sql) # End Table # sql = "DROP TABLE " + end_tbl + ";" # db.engine.execute(sql) sql = "CREATE TABLE " + end_tbl + " LIKE archive_bookings_repo;" db.engine.execute(sql) sql = "INSERT INTO " + end_tbl + " SELECT * " +\ "FROM archive_bookings_repo " +\ "WHERE date_added = '" + end_date + "' ;" db.engine.execute(sql) sql = "DELETE FROM " + end_tbl + " WHERE product_id NOT LIKE 'AS%';" db.engine.execute(sql) # Compare the two sql = "SELECT * FROM " + start_tbl + ";" starting_rows = db.engine.execute(sql) base_dict = {} for r in starting_rows: base_dict[r.hash_value] = r.id sql = "SELECT * FROM " + end_tbl + ";" ending_rows = db.engine.execute(sql) # # Define Header Row # new_as_list = [['Customer Name', 'Product ID', 'PSS', 'TSA', 'Account Mgr', 'SO Number', 'Web Order ID', 'Date Added', 'Svc Revenue', 'Prod Revenue']] # Loop over the most recent Bookings records with AS orders for r in ending_rows: # We found a new AS Booking Lets capture it if r.hash_value not in base_dict: # Go get a PSS/TSA and Sales Levels sales_levels = r.sales_level_1 + ',' + r.sales_level_2 + ',' + r.sales_level_3 + ',' + \ r.sales_level_4 + ',' + r.sales_level_5 + ',' + r.sales_level_6 sales_team = tool.find_team(team_dict, sales_levels) pss = sales_team[0] tsa = sales_team[1] print('Found New', r.erp_end_customer_name, r.product_id, pss, tsa, r.sales_agent_name, r.erp_sales_order_number, r.web_order_id) new_as_list.append([r.erp_end_customer_name, r.product_id, pss, tsa, r.sales_agent_name, r.erp_sales_order_number, r.web_order_id, r.date_added, r.tms_sales_allocated_service_bookings_net, r.tms_sales_allocated_product_bookings_net]) # Push the list to excel tool.push_list_to_xls(new_as_list, 'tmp_new_AS_Bookings as of ' + end_date.replace('-', '_') + '.xlsx') return
def report_customer_contacts(): # # Build a Team Dict of PSS/TSA # team_dict = tool.build_coverage_dict() # # customer_aliases = Customer_Aliases.query.all() # print(customer_aliases) # customer_names = Bookings.query.all() # Go to the Repo and get all Customer Aliases/Customer Global IDs # Find the most recent snapshot in the repo most_recent_update = table_stats({'bookings': 'archive_bookings_repo'}) date_added = most_recent_update[0][0].strftime("%Y-%m-%d") print("Reporting on most recent snapshot", most_recent_update[0][0]) sql = "SELECT distinct erp_end_customer_name, end_customer_global_ultimate_name, end_customer_global_ultimate_id " + \ "FROM archive_bookings_repo " +\ "WHERE date_added = '" + date_added + "';" customer_aliases = db.engine.execute(sql) print("Processing ", customer_aliases.rowcount, " customers") # # Define Header Row # customer_contact_list = [[ 'Customer Name', 'Ultimate Customer Name', 'Customer ID', 'PSS', 'TSA', 'Account Mgr', 'Sales_Lev_1', 'Sales_Lev_2', 'Sales_Lev_3', 'Sales_Lev_4', 'Sales_Lev_5', 'Sales_Lev_6' ]] sales_levels = '' for r in customer_aliases: # Go get all sales levels and agents this_cust_info = Bookings.query.filter_by( erp_end_customer_name=r.erp_end_customer_name).all() x = '' # Search for a valid set of sales levels for x in this_cust_info: sales_levels = x.sales_level_1 + ',' + x.sales_level_2 + ',' + x.sales_level_3 + ',' + \ x.sales_level_4 + ',' + x.sales_level_5 + ',' + x.sales_level_6 # Skip any levels that has MISC in it if sales_levels.find('MISC') != -1: continue else: break # Go get a PSS/TSA sales_team = tool.find_team(team_dict, sales_levels) pss = sales_team[0] tsa = sales_team[1] print() print('got this', sales_team) print('got team', pss, tsa) # exit() customer_contact_list.append([ r.erp_end_customer_name, r.end_customer_global_ultimate_name, r.end_customer_global_ultimate_id, pss, tsa, x.sales_agent_name, x.sales_level_1, x.sales_level_2, x.sales_level_3, x.sales_level_4, x.sales_level_5, x.sales_level_6 ]) tool.push_list_to_xls( customer_contact_list, 'tmp_simple_customer_' + date_added.replace('-', '_') + '.xlsx') return
my_list = [[ 'emp id', 'username', 'email', 'full name', 'manager', 'dept', 'role', 'job title', 'fname', 'nickname', 'lname' ]] for k, v in ta_mailer.items(): # print(k, v['username'], v['dept'], v['job_title']) my_list.append([ k, v['username'], v['email'], v['lname'] + ', ' + v['nickname'], v['manager_id'], v['dept'], v['job_title_short'], v['job_title'], v['fname'], v['nickname'], v['lname'] ]) tool.push_list_to_xls( my_list, 'tmp_vwalters_mailer.xlsx', ) # tool.push_list_to_xls(my_list, 'tmp_global_ta_team.xlsx',) exit() # mai = ws.cell_value(0, 0) # raw_len = len(raw) # names = [] # name = '' # for c in raw: # if c != ';': # name = name + c # else: # if name[0] == ' ': # name = name[1:]
def scrub_fy20_coverage(): # Get the Tetration Specialists wb, ws = tool.open_wb('ldap_users.xlsx') tet_tsa_list = [] for x in range(1, ws.nrows): ws.cell_value(x, 10) if ws.cell_value(x, 10) == 'Tetration': tet_tsa_list.append([ws.cell_value(x, 4), ws.cell_value(x, 3)]) # file_name = "TA Services Bookings with SO as of 11-23-20" file_name = 'global_coverage' home = (app_cfg['UPDATES_SUB_DIR']) my_dir = "FY20_Coverage" my_path = os.path.join(home, my_dir) print('my path >>> ', os.path.join(home, my_dir)) my_wb, my_ws = tool.open_wb(file_name + '.xlsx', my_path) ws_americas = my_wb.sheet_by_name('Americas') ws_emear = my_wb.sheet_by_name('EMEAR') print(ws_americas.nrows) print(ws_emear.nrows) coverage_dict = {} # Process Americas Coverage for row in range(1, ws_americas.nrows): if ws_americas.cell_value(row, 27) != '': node_id = str(int(ws_americas.cell_value(row, 0))) sls_lev_1 = ws_americas.cell_value(row, 9) sls_lev_2 = ws_americas.cell_value(row, 10) sls_lev_3 = ws_americas.cell_value(row, 11) sls_lev_4 = ws_americas.cell_value(row, 12) sls_lev_5 = ws_americas.cell_value(row, 13) sls_lev_6 = ws_americas.cell_value(row, 14) css = ws_americas.cell_value(row, 20) tsa = ws_americas.cell_value(row, 21) swan_css = ws_americas.cell_value(row, 27) swan_tsa = ws_americas.cell_value(row, 28) tet_tsa = '' for tet_tsa in tet_tsa_list: if swan_tsa.find(tet_tsa[0]) != -1: tet_tsa = tet_tsa[0] + ', ' + tet_tsa[1] break else: tet_tsa = '' coverage_dict[node_id] = [ sls_lev_1, sls_lev_2, sls_lev_3, sls_lev_4, sls_lev_5, sls_lev_6, css, tsa, swan_css, swan_tsa, tet_tsa ] # Process EMEAR Coverage for row in range(1, ws_emear.nrows): if ws_emear.cell_value(row, 26) != '': node_id = str(int(ws_emear.cell_value(row, 1))) sls_lev_1 = ws_emear.cell_value(row, 4) sls_lev_2 = ws_emear.cell_value(row, 5) sls_lev_3 = ws_emear.cell_value(row, 6) sls_lev_4 = ws_emear.cell_value(row, 7) sls_lev_5 = ws_emear.cell_value(row, 8) sls_lev_6 = ws_emear.cell_value(row, 9) css = ws_emear.cell_value(row, 19) tsa = ws_emear.cell_value(row, 20) swan_css = ws_emear.cell_value(row, 26) swan_tsa = ws_emear.cell_value(row, 27) tet_tsa = '' for tet_tsa in tet_tsa_list: if swan_tsa.find(tet_tsa[0]) != -1: tet_tsa = tet_tsa[0] + ', ' + tet_tsa[1] break else: tet_tsa = '' coverage_dict[node_id] = [ sls_lev_1, sls_lev_2, sls_lev_3, sls_lev_4, sls_lev_5, sls_lev_6, css, tsa, swan_css, swan_tsa, tet_tsa ] coverage_list = [[ 'node_id', 'sales_level_1', 'sales_level_2', 'sales_level_3', 'sales_level_4', 'sales_level_5', 'sales_level_6', 'css', 'tsa', 'swan_css', 'swan_tsa', 'tetration_tsa' ]] for k, v in coverage_dict.items(): tmp_list = v tmp_list.insert(0, k) coverage_list.append(tmp_list) tool.push_list_to_xls(coverage_list, 'swan_coverage.xlsx') print(len(coverage_dict)) print(my_path) return