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