def import_updates_to_sql(): create_tables() now = datetime.datetime.now() # # Import Delivery # wb, ws = open_wb(app_cfg['XLS_AS_DELIVERY_STATUS']) my_csv = xlrd_wb_to_csv(wb, ws) my_new_list = [] for my_row in my_csv: my_row.insert(0, '') my_new_list.append(my_row) push_list_to_csv(my_new_list, 'csv_services.csv') load_infile('services', 'csv_services.csv') # # Import Subscriptions # wb, ws = open_wb(app_cfg['XLS_SUBSCRIPTIONS']) my_csv = xlrd_wb_to_csv(wb, ws) my_new_list = [] for my_row in my_csv: my_row.insert(0, '') my_new_list.append(my_row) push_list_to_csv(my_new_list, 'csv_subscriptions.csv') load_infile('subscriptions', 'csv_subscriptions.csv') # # Import Bookings # wb, ws = open_wb(app_cfg['XLS_BOOKINGS']) my_csv = xlrd_wb_to_csv(wb, ws) my_new_list = [] last_col = len(my_csv[0]) for my_row in my_csv: # Add some useful columns my_row.insert(0, '') my_row.insert(last_col+1, 'hash') my_row.insert(last_col+2, now) my_new_list.append(my_row) push_list_to_csv(my_new_list, 'csv_bookings.csv') load_infile('bookings', 'csv_bookings.csv') return
def build_customer_list(): my_map = build_sheet_map(app_cfg['XLS_BOOKINGS'], sheet_map, 'XLS_BOOKINGS') wb_bookings, sheet_bookings = open_wb(app_cfg['XLS_BOOKINGS'], 'updates') customer_list = [] col_num_end_customer = -1 col_num_erp_customer = -1 # # First find the column numbers for these column names in the book # for val in my_map: if val[0] == 'ERP End Customer Name': col_num_erp_customer = val[2] elif val[0] == 'End Customer Global Ultimate Name': col_num_end_customer = val[2] # # Main loop of bookings excel data # top_row = [] for i in range(sheet_bookings.nrows): if i == 0: # Grab these values to make column headings top_row_erp_val = sheet_bookings.cell_value( i, col_num_erp_customer) top_row_end_val = sheet_bookings.cell_value( i, col_num_end_customer) top_row = [top_row_erp_val, top_row_end_val] continue # Capture both of the Customer names customer_name_erp = sheet_bookings.cell_value(i, col_num_erp_customer) customer_name_end = sheet_bookings.cell_value(i, col_num_end_customer) customer_list.append((customer_name_erp, customer_name_end)) # Create a simple customer_list list of tuples # Contains a full set of unique sorted customer names # customer_list = [(erp_customer_name,end_customer_ultimate), (CustA,CustA)] customer_list = set(customer_list) # Convert the SET to a LIST so we can sort it customer_list = list(customer_list) # Sort the LIST customer_list.sort(key=lambda tup: tup[0]) # Convert the customer name tuples to a list tmp_list = [] for customer in customer_list: tmp_list.append(list(customer)) customer_list = tmp_list # Place column headings at the top customer_list.insert(0, top_row) return customer_list
def get_customer(): print('Hello from main.py') # Try creating a Test_Table in the mySQL DB from Models.py create_tables("Test_Table") # Try retrieving a SmartSheet ss_test_list = get_list_from_ss('Tetration SKUs') print('Rows in my SmartSheet', len(ss_test_list)) # Try writing an xlsx file to local storage push_list_to_xls(ss_test_list, 'my_test_sheet.xlsx') # Try opening an xlsx file from local storage xws, wb = open_wb('my_test_sheet.xlsx') return 'Hello from Main.py: I read: '+ str(len(ss_test_list)) + ' rows from SmartSheets'
def get_customer(): print('Hello from main.py') # print('EXECUTING pre run checks') # pre_run_file_checks() # print('DONE EXECUTING pre run checks') # Try creating a Test_Table in the mySQL DB from Models.py create_tables("Test_Table") create_tables("Bookings") # Try retrieving a SmartSheet ss_test_list = get_list_from_ss('Tetration SKUs') print('Rows in my SmartSheet', len(ss_test_list)) # Try writing an xlsx file to local storage push_list_to_xls(ss_test_list, 'my_test_sheet.xlsx') # Try opening an xlsx file from local storage wb, ws = open_wb('my_test_sheet.xlsx') print("Opened my_test_sheet and it has:", ws.nrows, " rows") return 'Hello from Main.py: I read: ' + str( len(ss_test_list)) + ' rows from SmartSheets'
def build_sheet_map(file_name, my_map, tag, run_dir=app_cfg["UPDATES_DIR"]): print('MAPPING>>>>>>>>>> ', run_dir + '\\' + file_name) # First look and the tag and decide if we looking at # A local excel sheet or Smart Sheet if tag[:2] == 'SS': # Get the list of columns my_sheet = Ssheet(file_name, True) my_columns = my_sheet.columns # Loop across the Smart Sheet columns for ss_col in range(len(my_columns)): ss_col_num = my_sheet.columns[ss_col]['index'] ss_col_name = my_sheet.columns[ss_col]['title'] # Loop across the sheet map and look for a match for idx, val in enumerate(my_map): col_name = val[0] if col_name == ss_col_name and val[1] == tag: # We have a match on the source col name and file tag val[2] = ss_col_num elif tag[:3] == 'XLS': workbook, sheet = open_wb(file_name, run_dir) # Loop across all column headings in the bookings file and # Find the column number that matches the col_name in my_dict for wb_col_num in range(sheet.ncols): for idx, val in enumerate(my_map): col_name = val[0] if col_name == sheet.cell_value(0, wb_col_num) and val[1] == tag: val[2] = wb_col_num else: print('Missing Map TAG') exit() return my_map
def process_delivery(run_dir=app_cfg["UPDATES_DIR"]): print('MAPPING>>>>>>>>>> ', run_dir + '\\' + app_cfg['XLS_AS_DELIVERY_STATUS']) # Open up the subscription excel workbooks wb, sheet = open_wb(app_cfg['XLS_AS_DELIVERY_STATUS'], run_dir) # Get the renewal columns we are looking for my_map = build_sheet_map(app_cfg['XLS_AS_DELIVERY_STATUS'], sheet_map, 'XLS_AS_DELIVERY_STATUS', run_dir) # List comprehension replacement for above # Strip out the columns from the sheet map that we don't need my_map = [x for x in my_map if x[1] == 'XLS_AS_DELIVERY_STATUS'] # Create a simple column name dict col_nums = { sheet.cell_value(0, col_num): col_num for col_num in range(0, sheet.ncols) } # Loop over all of the delivery records # Build a dict of {customer:[delivery stuff blah blah ]} my_dict = {} for row_num in range(1, sheet.nrows): customer = sheet.cell_value(row_num, col_nums['End Customer']) if customer in my_dict: tmp_record = [] tmp_records = my_dict[customer] else: tmp_record = [] tmp_records = [] # Loop over the my map gather the columns we need for col_map in my_map: my_cell = sheet.cell_value(row_num, col_map[2]) # Is this cell a Date type (3) ? # If so format as a M/D/Y if sheet.cell_type(row_num, col_map[2]) == 3: my_cell = datetime.datetime( *xlrd.xldate_as_tuple(my_cell, wb.datemode)) my_cell = my_cell.strftime('%m-%d-%Y') tmp_record.append(my_cell) tmp_records.append(tmp_record) my_dict[customer] = tmp_records # # # Sort each customers delivery status # # # delivery_dict = {} # # print('diag1',my_dict['BLUE CROSS & BLUE SHIELD OF ALABAMA']) # # exit() # # ['08-20-2018', '12', '08-20-2019', 72.0, 1500.0, 'Sub170034', 'ACTIVE'] # # for customer, renewals in my_dict.items(): # # Sort this customers renewal records by date order # renewals.sort(key=lambda x: datetime.datetime.strptime(x[0], '%m-%d-%Y')) # # sorted_dict[customer] = renewals # # # # print('\t', customer, ' has', len(renewals), ' records') # # print('\t\t', renewals) # # print ('---------------------') # # time.sleep(1) # # next_renewal_date = renewals[0][0] # next_renewal_rev = 0 # next_renewal_qtr = renewals[0][2] # for renewal_rec in renewals: # if renewal_rec[0] == next_renewal_date: # # Tally this renewal record and get the next # # print (type(renewal_rec[4]), renewal_rec[4]) # # time.sleep(1) # next_renewal_rev = renewal_rec[4] + next_renewal_rev # elif renewal_rec[0] != next_renewal_date: # # Record these summarized values # summarized_rec.append([next_renewal_date, next_renewal_rev, next_renewal_qtr]) # # Reset these values and get the next renewal date for this customer # next_renewal_date = renewal_rec[0] # next_renewal_rev = renewal_rec[1] # next_renewal_qtr = renewal_rec[2] # # # Check to see if this is the last renewal record # # If so exit the loop # if renewals.index(renewal_rec) == len(renewals)-1: # break # # summarized_rec.append([next_renewal_date, next_renewal_rev, next_renewal_qtr]) # summarized_dict[customer] = summarized_rec # summarized_rec = [] return my_dict
def process_bookings(): # Go to Smartsheets and build these two dicts to use reference lookups # team_dict: {'sales_levels 1-6':[('PSS','TSA')]} # sku_dict: {sku : [sku_type, sku_description]} team_dict = build_coverage_dict() sku_dict = build_sku_dict() # # Open up the bookings excel workbooks # wb_bookings, sheet_bookings = open_wb(app_cfg['XLS_BOOKINGS'], 'updates') # From the current up to date bookings file build a simple list # that describes the format of the output file we are creating # and the columns we need to add (ie PSS, TSA, Renewal Dates) my_sheet_map = build_sheet_map(app_cfg['XLS_BOOKINGS'], sheet_map, 'XLS_BOOKINGS') print('sheet_map ', id(sheet_map)) print('my_sheet_map ', id(my_sheet_map)) # # init a bunch a variable we need for the main loop # order_header_row = [] order_rows = [] order_row = [] trash_rows = [] dest_col_nums = {} src_col_nums = {} # Build a dict of source sheet {'col_name' : src_col_num} # Build a dict of destination sheet {'col_name' : dest_col_num} # Build the header row for the output file for idx, val in enumerate(my_sheet_map): # Add to the col_num dict of col_names dest_col_nums[val[0]] = idx src_col_nums[val[0]] = val[2] order_header_row.append(val[0]) # Initialize the order_row and trash_row lists order_rows.append(order_header_row) trash_rows.append(sheet_bookings.row_values(0)) print('There are ', sheet_bookings.nrows, ' rows in Raw Bookings') # # Main loop of over raw bookings excel data # # This loop will build two lists: # 1. Interesting orders based on SKUs (order_rows) # 2. Trash orders SKUs we don't care about (trash_rows) # As determined by the sku_dict # We have also will assign team coverage to both rows # for i in range(1, sheet_bookings.nrows): # Is this SKU of interest ? sku = sheet_bookings.cell_value(i, src_col_nums['Bundle Product ID']) if sku in sku_dict: # Let's make a row for this order # Since it has an "interesting" sku customer = sheet_bookings.cell_value( i, src_col_nums['ERP End Customer Name']) order_row = [] sales_level = '' sales_level_cntr = 0 # Grab SKU data from the SKU dict sku_type = sku_dict[sku][0] sku_desc = sku_dict[sku][1] sku_sensor_cnt = sku_dict[sku][2] # Walk across the sheet_map columns # to build this output row cell by cell for val in my_sheet_map: col_name = val[0] # Source Sheet Column Name col_idx = val[2] # Source Sheet Column Number # If this is a 'Sales Level X' column then # Capture it's value until we get to level 6 # then do a team lookup if col_name[:-2] == 'Sales Level': sales_level = sales_level + sheet_bookings.cell_value( i, col_idx) + ',' sales_level_cntr += 1 if sales_level_cntr == 6: # We have collected all 6 sales levels # Now go to find_team to do the lookup sales_level = sales_level[:-1] sales_team = find_team(team_dict, sales_level) pss = sales_team[0] tsa = sales_team[1] order_row[dest_col_nums['pss']] = pss order_row[dest_col_nums['tsa']] = tsa if col_idx != -1: # OK we have a cell that we need from the raw bookings # sheet we need so grab it order_row.append(sheet_bookings.cell_value(i, col_idx)) elif col_name == 'Product Description': # Add in the Product Description order_row.append(sku_desc) elif col_name == 'Product Type': # Add in the Product Type order_row.append(sku_type) elif col_name == 'Sensor Count': # Add in the Sensor Count order_row.append(sku_sensor_cnt) else: # this cell is assigned a -1 in the sheet_map # so assign a blank as a placeholder for now order_row.append('') # Done with all the columns in this row # Log this row for BOTH customer names and orders # Go to next row of the raw bookings data order_rows.append(order_row) else: # The SKU was not interesting so let's trash it trash_rows.append(sheet_bookings.row_values(i)) print('Extracted ', len(order_rows), " rows of interesting SKU's' from Raw Bookings") print('Trashed ', len(trash_rows), " rows of trash SKU's' from Raw Bookings") # # End of main loop # # # Renewal Analysis # renewal_dict = process_renewals() for order_row in order_rows[1:]: customer = order_row[dest_col_nums['ERP End Customer Name']] if customer in renewal_dict: next_renewal_date = datetime.datetime.strptime( renewal_dict[customer][0][0], '%m-%d-%Y') next_renewal_rev = renewal_dict[customer][0][1] next_renewal_qtr = renewal_dict[customer][0][2] order_row[dest_col_nums['Renewal Date']] = next_renewal_date order_row[dest_col_nums['Product Bookings']] = next_renewal_rev order_row[dest_col_nums['Fiscal Quarter ID']] = next_renewal_qtr if len(renewal_dict[customer]) > 1: renewal_comments = '+' + str(len(renewal_dict[customer]) - 1) + ' more renewal(s)' order_row[dest_col_nums['Renewal Comments']] = renewal_comments # Now we build a an order dict # Let's organize as this # order_dict: {cust_name:[[order1],[order2],[orderN]]} order_dict = {} orders = [] order = [] for idx, order_row in enumerate(order_rows): if idx == 0: continue customer = order_row[0] orders = [] # Is this customer in the order dict ? if customer in order_dict: orders = order_dict[customer] orders.append(order_row) order_dict[customer] = orders else: orders.append(order_row) order_dict[customer] = orders # Create a simple customer_list # Contains a full set of unique sorted customer names # Example: customer_list = [[erp_customer_name,end_customer_ultimate], [CustA,CustA]] customer_list = build_customer_list() print('There are ', len(customer_list), ' unique Customer Names') # Clean up order_dict to remove: # 1. +/- zero sum orders # 2. zero revenue orders order_dict, customer_platforms = cleanup_orders(customer_list, order_dict, my_sheet_map) # # Create a summary order file out of the order_dict # summary_order_rows = [order_header_row] for key, val in order_dict.items(): for my_row in val: summary_order_rows.append(my_row) print(len(summary_order_rows), ' of scrubbed rows after removing "noise"') # # Push our lists to an excel file # # push_list_to_xls(customer_platforms, 'jim ') print('order summary name ', app_cfg['XLS_ORDER_SUMMARY']) push_list_to_xls(summary_order_rows, app_cfg['XLS_ORDER_SUMMARY'], 'updates') push_list_to_xls(order_rows, app_cfg['XLS_ORDER_DETAIL'], 'updates') push_list_to_xls(customer_list, app_cfg['XLS_CUSTOMER'], 'updates') push_list_to_xls(trash_rows, app_cfg['XLS_BOOKINGS_TRASH'], 'updates') # exit() # # Push our lists to a smart sheet # # push_xls_to_ss(wb_file, app_cfg['XLS_ORDER_SUMMARY']) # push_xls_to_ss(wb_file, app_cfg['XLS_ORDER_DETAIL']) # push_xls_to_ss(wb_file, app_cfg['XLS_CUSTOMER']) # exit() return
from my_app.func_lib.open_wb import open_wb from my_app.func_lib.push_list_to_xls import push_list_to_xls from my_app.func_lib.push_xlrd_to_xls import push_xlrd_to_xls from my_app.func_lib.build_sku_dict import build_sku_dict from my_app.settings import app_cfg from my_app.Customer import Customer import xlrd from datetime import datetime 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
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
# Add the new cell to the new row my_new_row[col_num] = new_cell_value # This will make list_to_hash type to all one big string called my_string str_to_hash = ''.join(list_to_hash) # Create a 32 character hash value and place it in the Hash Column hash_result = (hashlib.md5(str_to_hash.encode('utf-8')).hexdigest()) tie_breaker = 1 if hash_result in hash_dict: tie_breaker = hash_dict[hash_result] + 1 hash_dict[hash_result] = tie_breaker else: hash_dict[hash_result] = tie_breaker my_new_row[date_added_col] = date_added my_new_row[hash_col] = hash_result + '-' + str(tie_breaker) return my_new_sheet if __name__ == "__main__" and __package__ is None: xlrd_wb, xlrd_ws = open_wb('tmp_TA Master Bookings.xlsx') # wb, ws = open_wb('tmp_Master Renewals.xlsx') # xlrd_wb, xlrd_ws = open_wb('tmp_TA Customer List.xlsx') a_sheet = add_hash_to_xls(xlrd_wb, xlrd_ws) push_list_to_xls(a_sheet, 'tmp_TA Master Bookings_hashed.xlsx')
print() print('CREATING IN DIRECTORY >>>>>>>>>> ', path_to_run_dir) print('CREATING SHEET >>>>>>>>>> ', csv_output) print() # for x, my_row in enumerate(my_list): with open(path_to_file, 'w', newline='') as writeFile: writer = csv.writer(writeFile) writer.writerows(my_list) writeFile.close() return if __name__ == "__main__" and __package__ is None: wb, ws = open_wb(app_cfg['XLS_BOOKINGS']) # wb, ws = open_wb(app_cfg['XLS_AS_DELIVERY_STATUS']) # wb, ws = open_wb(app_cfg['XLS_SUBSCRIPTIONS']) create_tables('Bookings') my_csv_list = xlrd_wb_to_csv(wb, ws) my_list = xlrd_wb_to_list(wb, ws) print(len(my_csv_list)) print(len(my_list)) push_list_to_xls(my_list, 'my_xls.xlsx') push_list_to_csv(my_csv_list, 'my_csv.csv') exit()
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 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 phase_2(run_dir=app_cfg['UPDATES_DIR']): home = app_cfg['HOME'] working_dir = app_cfg['WORKING_DIR'] path_to_run_dir = (os.path.join(home, working_dir, run_dir)) bookings_path = os.path.join(path_to_run_dir, app_cfg['XLS_BOOKINGS']) # Read the config_dict.json file with open(os.path.join(path_to_run_dir, app_cfg['META_DATA_FILE'])) as json_input: config_dict = json.load(json_input) data_time_stamp = datetime.datetime.strptime(config_dict['data_time_stamp'], '%m-%d-%y') last_run_dir = config_dict['last_run_dir'] print("Run Date: ", data_time_stamp, type(data_time_stamp)) print('Run Directory:', last_run_dir) print(bookings_path) # Go to Smartsheets and build these two dicts to use reference lookups # team_dict: {'sales_levels 1-6':[('PSS','TSA')]} # sku_dict: {sku : [sku_type, sku_description]} team_dict = build_coverage_dict() sku_dict = build_sku_dict() # # Open up the bookings excel workbooks # wb_bookings, sheet_bookings = open_wb(app_cfg['XLS_BOOKINGS'], run_dir) # From the current up to date bookings file build a simple list # that describes the format of the output file we are creating # and the columns we need to add (ie PSS, TSA, Renewal Dates) my_sheet_map = build_sheet_map(app_cfg['XLS_BOOKINGS'], sheet_map, 'XLS_BOOKINGS', run_dir) # # init a bunch a variables we need for the main loop # order_header_row = [] order_rows = [] order_row = [] trash_rows = [] dest_col_nums = {} src_col_nums = {} # Build a dict of source sheet {'col_name' : src_col_num} # Build a dict of destination sheet {'col_name' : dest_col_num} # Build the header row for the output file for idx, val in enumerate(my_sheet_map): # Add to the col_num dict of col_names dest_col_nums[val[0]] = idx src_col_nums[val[0]] = val[2] order_header_row.append(val[0]) # Initialize the order_row and trash_row lists order_rows.append(order_header_row) trash_rows.append(sheet_bookings.row_values(0)) print('There are ', sheet_bookings.nrows, ' rows in Raw Bookings') # # Main loop of over raw bookings excel data # # This loop will build two lists: # 1. Interesting orders based on SKUs (order_rows) # 2. Trash orders SKUs we don't care about (trash_rows) # As determined by the sku_dict # We have also will assign team coverage to both rows # for i in range(1, sheet_bookings.nrows): # Is this SKU of interest ? sku = sheet_bookings.cell_value(i, src_col_nums['Bundle Product ID']) if sku in sku_dict: # Let's make a row for this order # Since it has an "interesting" sku customer = sheet_bookings.cell_value(i, src_col_nums['ERP End Customer Name']) order_row = [] sales_level = '' sales_level_cntr = 0 # Grab SKU data from the SKU dict sku_type = sku_dict[sku][0] sku_desc = sku_dict[sku][1] sku_sensor_cnt = sku_dict[sku][2] # Walk across the sheet_map columns # to build this output row cell by cell for val in my_sheet_map: col_name = val[0] # Source Sheet Column Name col_idx = val[2] # Source Sheet Column Number # If this is a 'Sales Level X' column then # Capture it's value until we get to level 6 # then do a team lookup if col_name[:-2] == 'Sales Level': sales_level = sales_level + sheet_bookings.cell_value(i, col_idx) + ',' sales_level_cntr += 1 if sales_level_cntr == 6: # We have collected all 6 sales levels # Now go to find_team to do the lookup sales_level = sales_level[:-1] sales_team = find_team(team_dict, sales_level) pss = sales_team[0] tsa = sales_team[1] order_row[dest_col_nums['pss']] = pss order_row[dest_col_nums['tsa']] = tsa if col_idx != -1: # OK we have a cell that we need from the raw bookings # sheet we need so grab it order_row.append(sheet_bookings.cell_value(i, col_idx)) elif col_name == 'Product Description': # Add in the Product Description order_row.append(sku_desc) elif col_name == 'Product Type': # Add in the Product Type order_row.append(sku_type) elif col_name == 'Sensor Count': # Add in the Sensor Count order_row.append(sku_sensor_cnt) else: # this cell is assigned a -1 in the sheet_map # so assign a blank as a placeholder for now order_row.append('') # Done with all the columns in this row # Log this row for BOTH customer names and orders # Go to next row of the raw bookings data order_rows.append(order_row) else: # The SKU was not interesting so let's trash it trash_rows.append(sheet_bookings.row_values(i)) print('Extracted ', len(order_rows), " rows of interesting SKU's' from Raw Bookings") print('Trashed ', len(trash_rows), " rows of trash SKU's' from Raw Bookings") # # End of main loop # push_list_to_xls(order_rows,'jim.xlsx') # # Subscription Analysis # no_match = [['No Match Found in Subscription update']] no_match_cntr = 0 match_cntr = 0 subs_sorted_dict, subs__summary_dict = process_subs(run_dir) for order_row in order_rows[1:]: customer = order_row[dest_col_nums['ERP End Customer Name']] if customer in subs_sorted_dict: match_cntr += 1 sub_start_date = datetime.datetime.strptime(subs_sorted_dict[customer][0][0], '%m-%d-%Y') sub_initial_term = subs_sorted_dict[customer][0][1] sub_renewal_date = datetime.datetime.strptime(subs_sorted_dict[customer][0][2], '%m-%d-%Y') sub_days_to_renew = subs_sorted_dict[customer][0][3] sub_monthly_charge = subs_sorted_dict[customer][0][4] sub_id = subs_sorted_dict[customer][0][5] sub_status = subs_sorted_dict[customer][0][6] order_row[dest_col_nums['Start Date']] = sub_start_date order_row[dest_col_nums['Initial Term']] = sub_initial_term order_row[dest_col_nums['Renewal Date']] = sub_renewal_date order_row[dest_col_nums['Days Until Renewal']] = sub_days_to_renew order_row[dest_col_nums['Monthly Charge']] = sub_monthly_charge order_row[dest_col_nums['Subscription ID']] = sub_id order_row[dest_col_nums['Status']] = sub_status if len(subs_sorted_dict[customer]) > 1: renewal_comments = '+' + str(len(subs_sorted_dict[customer])-1) + ' more subscriptions(s)' order_row[dest_col_nums['Subscription Comments']] = renewal_comments else: got_one = False for x in no_match: if x[0].lower() in customer.lower(): got_one = True break if got_one is False: no_match_cntr += 1 no_match.append([customer]) push_list_to_xls(order_rows, 'jim1.xlsx') push_list_to_xls(no_match, 'subcription misses.xlsx') # # AS Delivery Analysis # as_dict = process_delivery(run_dir) print(as_dict) print(len(as_dict)) for order_row in order_rows[1:]: customer = order_row[dest_col_nums['ERP End Customer Name']] if customer in as_dict: match_cntr += 1 # as_customer = as_dict[customer][0][0] # as_pid = as_dict[customer][0][1] # as_dm = as_dict[customer][0][2] # as_start_date = datetime.datetime.strptime(as_dict[customer][0][3], '%m-%d-%Y') # # order_row[dest_col_nums['End Customer']] = as_customer # order_row[dest_col_nums['PID']] = as_pid # order_row[dest_col_nums['Delivery Manager']] = as_dm # order_row[dest_col_nums['Project Scheduled Start Date']] = as_start_date order_row[dest_col_nums['PID']] = as_dict[customer][0][0] order_row[dest_col_nums['Delivery Manager']] = as_dict[customer][0][1] order_row[dest_col_nums['Delivery PM']] = as_dict[customer][0][2] order_row[dest_col_nums['Tracking status']] = as_dict[customer][0][3] order_row[dest_col_nums['Tracking Sub-status']] = as_dict[customer][0][4] order_row[dest_col_nums['Comments']] = as_dict[customer][0][5] order_row[dest_col_nums['Project Scheduled Start Date']] = datetime.datetime.strptime(as_dict[customer][0][6], '%m-%d-%Y') order_row[dest_col_nums['Scheduled End Date']] = datetime.datetime.strptime(as_dict[customer][0][7], '%m-%d-%Y') order_row[dest_col_nums['Project Creation Date']] = datetime.datetime.strptime(as_dict[customer][0][8], '%m-%d-%Y') # order_row[dest_col_nums['Project Closed Date']] = datetime.datetime.strptime(as_dict[customer][0][9], '%m-%d-%Y') order_row[dest_col_nums['Traffic lights (account team)']] = as_dict[customer][0][10] order_row[dest_col_nums['Tracking Responsible']] = as_dict[customer][0][11] order_row[dest_col_nums['ExecutiveSummary']] = as_dict[customer][0][12] order_row[dest_col_nums['Critalpath']] = as_dict[customer][0][13] order_row[dest_col_nums['IsssuesRisks']] = as_dict[customer][0][14] order_row[dest_col_nums['ActivitiesCurrent']] = as_dict[customer][0][15] order_row[dest_col_nums['ActivitiesNext']] = as_dict[customer][0][16] order_row[dest_col_nums['LastUpdate']] = as_dict[customer][0][17] order_row[dest_col_nums['SO']] = as_dict[customer][0][18] else: got_one = False for x in no_match: if x[0].lower() in customer.lower(): got_one = True break if got_one is False: no_match_cntr += 1 no_match.append([customer]) # # End of Construction Zone # # Now we build a an order dict # Let's organize as this # order_dict: {cust_name:[[order1],[order2],[orderN]]} order_dict = {} orders = [] order = [] for idx, order_row in enumerate(order_rows): if idx == 0: continue customer = order_row[0] orders = [] # Is this customer in the order dict ? if customer in order_dict: orders = order_dict[customer] orders.append(order_row) order_dict[customer] = orders else: orders.append(order_row) order_dict[customer] = orders # Create a simple customer_list # Contains a full set of unique sorted customer names # Example: customer_list = [[erp_customer_name,end_customer_ultimate], [CustA,CustA]] customer_list = build_customer_list(run_dir) print('There are ', len(customer_list), ' unique Customer Names') # Clean up order_dict to remove: # 1. +/- zero sum orders # 2. zero revenue orders order_dict, customer_platforms = cleanup_orders(customer_list, order_dict, my_sheet_map) # # Create a summary order file out of the order_dict # summary_order_rows = [order_header_row] for key, val in order_dict.items(): for my_row in val: summary_order_rows.append(my_row) print(len(summary_order_rows), ' of scrubbed rows after removing "noise"') # # Push our lists to an excel file # # push_list_to_xls(customer_platforms, 'jim ') print('order summary name ', app_cfg['XLS_ORDER_SUMMARY']) push_list_to_xls(summary_order_rows, app_cfg['XLS_ORDER_SUMMARY'], run_dir, 'ta_summary_orders') push_list_to_xls(order_rows, app_cfg['XLS_ORDER_DETAIL'], run_dir, 'ta_order_detail') push_list_to_xls(customer_list, app_cfg['XLS_CUSTOMER'], run_dir, 'ta_customers') push_list_to_xls(trash_rows, app_cfg['XLS_BOOKINGS_TRASH'], run_dir, 'ta_trash_rows') # exit() # # Push our lists to a smart sheet # # push_xls_to_ss(wb_file, app_cfg['XLS_ORDER_SUMMARY']) # push_xls_to_ss(wb_file, app_cfg['XLS_ORDER_DETAIL']) # push_xls_to_ss(wb_file, app_cfg['XLS_CUSTOMER']) # exit() return
def 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
from my_app.func_lib.open_wb import open_wb from my_app.func_lib.add_hash_to_xls import add_hash_to_xls from my_app.func_lib.push_list_to_xls import push_list_to_xls from datetime import datetime # # db.create_all() # Customers.__table__.drop(db.session.bind) Customers.__table__.create(db.session.bind) now = datetime.now() # Add a hash to this sheet xlrd_wb, xlrd_ws = open_wb('tmp_TA Customer List.xlsx') a_sheet = add_hash_to_xls(xlrd_wb, xlrd_ws) push_list_to_xls(a_sheet, 'tmp_TA Customer List_hashed.xlsx') # Now open the sheet that includes a unique hash value xlrd_wb, xlrd_ws = open_wb('tmp_TA Customer List_hashed.xlsx') # Loop over the sheet starting row 1 to exclude headers for row_num in range(1, xlrd_ws.nrows): a_cust = Customers() a_cust.customer_ultimate_name = xlrd_ws.cell_value(row_num, 0) a_cust.customer_erp_name = xlrd_ws.cell_value(row_num, 1) a_cust.date_added = now a_cust.hash_value = xlrd_ws.cell_value(row_num, 2) db.session.add(a_cust)
def refresh_data(): # This function retrieves data from the update_dir # It looks for a file(s) in the format of: # 'FY17 TA Master Bookings as of 02-25-19.xlsx' # 'TA Renewal Dates as of 02-25-19.xlsx' # It will prep them and create all supporting files in the update dir # ALL workbooks created will begin with tmp_XXXXX.xlsx # We then create a dated archive directory and move a set of all files to the archive & working dirs home = app_cfg['HOME'] working_dir = app_cfg['WORKING_DIR'] update_dir = app_cfg['UPDATES_DIR'] archive_dir = app_cfg['ARCHIVES_DIR'] path_to_main_dir = (os.path.join(home, working_dir)) path_to_updates = (os.path.join(home, working_dir, update_dir)) path_to_archives = (os.path.join(home, working_dir, archive_dir)) update_files = os.listdir(path_to_updates) bookings = [] start_row = 0 as_of_date = '' print(path_to_main_dir) print(path_to_updates) print(path_to_archives) print(app_cfg['PROD_DATE']) print(app_cfg['UPDATE_DATE']) # Look in the "ta_data_updates" dir # this is where we place newly updated sheets to be put into production if len(update_files) == 0: # NO update files exist so log an error and return print('ERROR: No Update files exist in:', path_to_updates) return else: for file in update_files: # When we find a "Master Bookings" file # Add the rows to the "bookings" list if file.find('Master Bookings') != -1: wb, ws = open_wb(file, 'updates') as_of_date = file[-13:-13 + 8] if start_row == 0: # For the first workbook include the header row start_row = 2 elif start_row == 2: # For subsequent workbooks skip the header start_row = 3 for row in range(start_row, ws.nrows): bookings.append(ws.row_values(row)) print('as of', as_of_date) # We have now created the bookings list lets write it # and rename it to the current as_of_date print('New Master Bookings has ', len(bookings), ' line items') push_list_to_xls(bookings, 'tmp_working_bookings', 'updates') os.rename( os.path.join(path_to_updates, 'tmp_working_bookings'), os.path.join(path_to_updates, 'tmp_Master Bookings as of ' + as_of_date + '.xlsx')) # Create a workbook of filtered AS SKUs only as_bookings = get_as_skus(bookings) push_list_to_xls(as_bookings, 'tmp_working_as_bookings', 'updates') os.rename( os.path.join(path_to_updates, 'tmp_working_as_bookings'), os.path.join(path_to_updates, 'tmp_TA AS SKUs as of ' + as_of_date + '.xlsx')) # process_bookings print('Before init', app_cfg['XLS_BOOKINGS']) init_settings() print('after init', app_cfg['XLS_BOOKINGS']) process_bookings() # build_dashboard build_dashboard() # Make an archive directory where we need to place these update files os.mkdir(os.path.join(path_to_archives, as_of_date + " Updates")) archive_folder_path = os.path.join(path_to_archives, as_of_date + " Updates") print(archive_folder_path) # Delete all current working files from the working directory stamped with del_date files = os.listdir(path_to_main_dir) del_date = '' for file in files: if file.find('Master Bookings') != -1: del_date = file[-13:-13 + 8] break for file in files: if file[-13:-13 + 8] == del_date: print('Deleting file', file) os.remove(os.path.join(path_to_main_dir, file)) # Move a copy of all new files to the working directory also main_files = os.listdir(path_to_updates) for file in main_files: copyfile(os.path.join(path_to_updates, file), os.path.join(path_to_main_dir, file)) # Move all updates to the archive directory update_files = os.listdir(path_to_updates) for file in update_files: print(file) os.rename(os.path.join(path_to_updates, file), os.path.join(archive_folder_path, file)) print('All data files have been refreshed and archived !') print('Before init', app_cfg['XLS_BOOKINGS']) init_settings() print('after init', app_cfg['XLS_BOOKINGS']) return
def process_renewals(): # Open up the renewals excel workbooks wb, sheet = open_wb(app_cfg['XLS_RENEWALS'], 'updates') # Get the renewal columns we are looking for my_map = build_sheet_map(app_cfg['XLS_RENEWALS'], sheet_map, 'XLS_RENEWALS') print('sheet_map ', id(sheet_map)) print('my map ', id(my_map)) # List comprehension replacement for above # Strip out the columns from the sheet map that we don't need my_map = [x for x in my_map if x[1] == 'XLS_RENEWALS'] # Create a simple column name dict col_nums = { sheet.cell_value(0, col_num): col_num for col_num in range(0, sheet.ncols) } # Loop over all of the renewal records # Build a dict of {customer:[next renewal date, next renewal revenue, upcoming renewals]} my_dict = {} for row_num in range(1, sheet.nrows): customer = sheet.cell_value(row_num, col_nums['End Customer']) if customer in my_dict: tmp_record = [] tmp_records = my_dict[customer] else: tmp_record = [] tmp_records = [] # Loop over the my map gather the columns we need for col_map in my_map: my_cell = sheet.cell_value(row_num, col_map[2]) # Is this cell a Date type (3) ? # If so format as a M/D/Y if sheet.cell_type(row_num, col_map[2]) == 3: my_cell = datetime.datetime( *xlrd.xldate_as_tuple(my_cell, wb.datemode)) my_cell = my_cell.strftime('%m-%d-%Y') tmp_record.append(my_cell) tmp_records.append(tmp_record) my_dict[customer] = tmp_records # # Sort each customers renewal dates # sorted_dict = {} summarized_dict = {} summarized_rec = [] for customer, renewals in my_dict.items(): # Sort this customers renewal records by date order renewals.sort(key=lambda x: x[0]) sorted_dict[customer] = renewals next_renewal_date = renewals[0][0] next_renewal_rev = 0 next_renewal_qtr = renewals[0][2] for renewal_rec in renewals: if renewal_rec[0] == next_renewal_date: # Tally this renewal record and get the next next_renewal_rev = float(renewal_rec[1] + next_renewal_rev) elif renewal_rec[0] != next_renewal_date: # Record these summarized values summarized_rec.append( [next_renewal_date, next_renewal_rev, next_renewal_qtr]) # Reset these values and get the next renewal date for this customer next_renewal_date = renewal_rec[0] next_renewal_rev = renewal_rec[1] next_renewal_qtr = renewal_rec[2] # Check to see if this is the last renewal record # If so exit the loop if renewals.index(renewal_rec) == len(renewals) - 1: break summarized_rec.append( [next_renewal_date, next_renewal_rev, next_renewal_qtr]) summarized_dict[customer] = summarized_rec summarized_rec = [] # print(sorted_dict['FIRST NATIONAL BANK OF SOUTHERN AFRICA LTD']) # print(summarized_dict['SPECTRUM HEALTH SYSTEM']) # print (len(summarized_dict['SPECTRUM HEALTH SYSTEM'])) return summarized_dict
match = False ratios = fuzz.ratio(name1, name2) if ratios >= 85 and ratios < 100: print(name1, '/ \t', name2, ratios) match = True #time.sleep(1) return match if __name__ == "__main__": list_a = ['jim'] list_b = ['jim', 'gym', 'ang', 'jime'] my_file = 'C:/Users/jpisano/ta_adoption_data/ta_data_updates/tmp_TA Customer List.xlsx' my_wb, my_ws = open_wb(my_file) sheet1 = my_wb.sheet_by_name('Sheet1') sheet2 = my_wb.sheet_by_name('Sheet2') unique_names = [] jims_list = [] duplicate_names = [] aka_list = [] aka = {} for my_row in range(1, sheet1.nrows): duplicate_names.append(sheet1.cell_value(my_row, 0)) for my_row in range(0, sheet2.nrows): unique_names.append(sheet1.cell_value(my_row, 0))
def file_checks(run_dir=app_cfg['UPDATES_DIR']): home = app_cfg['HOME'] working_dir = app_cfg['WORKING_DIR'] update_dir = app_cfg['UPDATES_DIR'] archive_dir = app_cfg['ARCHIVES_DIR'] # Check that all key directories exist path_to_main_dir = (os.path.join(home, working_dir)) if not os.path.exists(path_to_main_dir): print(path_to_main_dir, " does NOT Exist !") exit() path_to_run_dir = (os.path.join(home, working_dir, run_dir)) if not os.path.exists(path_to_run_dir): print(path_to_run_dir, " does NOT Exist !") exit() path_to_updates = (os.path.join(home, working_dir, update_dir)) if not os.path.exists(path_to_updates): print(path_to_updates, " does NOT Exist !") exit() path_to_archives = (os.path.join(home, working_dir, archive_dir)) if not os.path.exists(path_to_archives): print(path_to_archives, " 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 found') exit() # Do we have all the files we need ? for file_name, status in files_needed.items(): if status != 'Found': print('ERROR: File ', file_name, 'is missing') exit() # 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) # print(config_dict) # 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 } 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 = [] renewals = [] start_row = 0 print() print('We are processing 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) # my_wb = xlrd.open_workbook(file_path) # my_ws = my_wb.sheet_by_index(0) print('\t\t', file_name + '', processing_date + '.xlsx', ' has ', my_ws.nrows, ' rows and ', my_ws.ncols, 'columns') if file_name.find('Bookings') != -1: if start_row == 0: # For the first workbook include the header row start_row = 2 elif start_row == 2: # For subsequent workbooks skip the header start_row = 3 for row in range(start_row, my_ws.nrows): bookings.append(my_ws.row_slice(row)) elif file_name.find('Renewals') != -1: for row in range(2, my_ws.nrows): renewals.append(my_ws.row_slice(row)) # Push the lists out to an Excel File push_xlrd_to_xls(bookings, app_cfg['XLS_BOOKINGS'], run_dir, 'ta_bookings') as_bookings = get_as_skus(bookings) push_xlrd_to_xls(as_bookings, app_cfg['XLS_AS_SKUS'], run_dir, 'as_bookings') push_xlrd_to_xls(renewals, app_cfg['XLS_RENEWALS'], run_dir, 'ta_renewals') print('We have ', len(bookings), 'bookings line items') print('We have ', len(as_bookings), 'Services line items') print('We have ', len(renewals), 'renewal line items') return
from my_app.settings import app_cfg from my_app.func_lib.open_wb import open_wb from my_app.func_lib.push_list_to_xls import push_list_to_xls from my_app.func_lib.get_list_from_ss import get_list_from_ss from my_app.func_lib.push_xls_to_ss import push_xls_to_ss # push_xls_to_ss('mailer names.xlsx', 'CTUG mailer') # jim = get_list_from_ss('Tetration Coverage Map') # print (jim) # # exit() # Feed this a list like this # Chris Mchenry (chmchenr) <*****@*****.**>; Gordon Hirst (ghirst) <*****@*****.**>; # wb, ws = open_wb('mailer scrub.xlsx') raw = 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:] names.append(name) name = '' word = ''
def process_subs(run_dir=app_cfg["UPDATES_DIR"]): print('MAPPING>>>>>>>>>> ', run_dir + '\\' + app_cfg['XLS_SUBSCRIPTIONS']) # Open up the subscription excel workbooks wb, sheet = open_wb(app_cfg['XLS_SUBSCRIPTIONS'], run_dir) # Get the renewal columns we are looking for my_map = build_sheet_map(app_cfg['XLS_SUBSCRIPTIONS'], sheet_map, 'XLS_SUBSCRIPTIONS', run_dir) print('sheet_map ', id(sheet_map)) print('my map ', id(my_map)) # List comprehension replacement for above # Strip out the columns from the sheet map that we don't need my_map = [x for x in my_map if x[1] == 'XLS_SUBSCRIPTIONS'] # Create a simple column name dict col_nums = { sheet.cell_value(0, col_num): col_num for col_num in range(0, sheet.ncols) } # Loop over all of the subscription records # Build a dict of {customer:[next renewal date, next renewal revenue, upcoming renewals]} my_dict = {} for row_num in range(1, sheet.nrows): customer = sheet.cell_value(row_num, col_nums['End Customer']) if customer in my_dict: tmp_record = [] tmp_records = my_dict[customer] else: tmp_record = [] tmp_records = [] # Loop over the my map gather the columns we need for col_map in my_map: my_cell = sheet.cell_value(row_num, col_map[2]) # Is this cell a Date type (3) ? # If so format as a M/D/Y if sheet.cell_type(row_num, col_map[2]) == 3: my_cell = datetime.datetime( *xlrd.xldate_as_tuple(my_cell, wb.datemode)) my_cell = my_cell.strftime('%m-%d-%Y') tmp_record.append(my_cell) tmp_records.append(tmp_record) my_dict[customer] = tmp_records # # Sort each customers renewal dates # sorted_dict = {} summarized_dict = {} summarized_rec = [] # print('diag1',my_dict['BLUE CROSS & BLUE SHIELD OF ALABAMA']) # exit() # ['08-20-2018', '12', '08-20-2019', 72.0, 1500.0, 'Sub170034', 'ACTIVE'] for customer, renewals in my_dict.items(): # Sort this customers renewal records by date order renewals.sort( key=lambda x: datetime.datetime.strptime(x[0], '%m-%d-%Y')) sorted_dict[customer] = renewals # # print('\t', customer, ' has', len(renewals), ' records') # print('\t\t', renewals) # print ('---------------------') # time.sleep(1) next_renewal_date = renewals[0][0] next_renewal_rev = 0 next_renewal_qtr = renewals[0][2] for renewal_rec in renewals: if renewal_rec[0] == next_renewal_date: # Tally this renewal record and get the next # print (type(renewal_rec[4]), renewal_rec[4]) # time.sleep(1) next_renewal_rev = renewal_rec[4] + next_renewal_rev elif renewal_rec[0] != next_renewal_date: # Record these summarized values summarized_rec.append( [next_renewal_date, next_renewal_rev, next_renewal_qtr]) # Reset these values and get the next renewal date for this customer next_renewal_date = renewal_rec[0] next_renewal_rev = renewal_rec[1] next_renewal_qtr = renewal_rec[2] # Check to see if this is the last renewal record # If so exit the loop if renewals.index(renewal_rec) == len(renewals) - 1: break summarized_rec.append( [next_renewal_date, next_renewal_rev, next_renewal_qtr]) summarized_dict[customer] = summarized_rec summarized_rec = [] # print(sorted_dict['FIRST NATIONAL BANK OF SOUTHERN AFRICA LTD']) # print(summarized_dict['SPECTRUM HEALTH SYSTEM']) # print (len(summarized_dict['SPECTRUM HEALTH SYSTEM'])) return sorted_dict, summarized_dict
elif src_cell_type == xlrd.XL_CELL_BLANK: print("\t\tBlank", my_ws.cell_value(my_row, my_col[2]), ' needs to be a ' + dest_cell_type) elif src_cell_type == xlrd.XL_CELL_BOOLEAN: print("\t\tBoolean", my_ws.cell_value(my_row, my_col[2]), ' needs to be a ' + dest_cell_type) elif src_cell_type == xlrd.XL_CELL_EMPTY: print("\t\tEmpty", my_ws.cell_value(my_row, my_col[2]), ' needs to be a ' + dest_cell_type) elif src_cell_type == xlrd.XL_CELL_ERROR: print("\t\tError", my_ws.cell_value(my_row, my_col[2]), ' needs to be' + dest_cell_type) time.sleep(0.5) print(type(dest_cell_val), dest_cell_val) list_of_row.append(dest_cell_val) print(list_of_row) list_of_rows.append(list_of_row) print(list_of_rows) time.sleep(2) return list_of_rows if __name__ == "__main__": path_to_scrub = 'C:/Users\jpisano/ta_adoption_data/ta_data_updates/TA Master Subscriptions as of 06-12-19.xlsx' my_path, my_file = ntpath.split(path_to_scrub) wb, ws = open_wb(my_file) data_scrubber(ws, path_to_scrub)