def prep_sql():
cnx = mysql.connect(user=database['USER'],
passwd=database['PASSWORD'],
host=database['HOST'],
db=database['DATABASE'])
mycursor = cnx.cursor()
download_file = app['DOWNLOAD_FILE']
download_dir = app['DOWNLOAD_DIR']
working_dir = app['WORKING_DIR']
working_file = app['WORKING_FILE']
working_csv_file = app['WORKING_CSV_FILE']
working_data_dir = app['WORKING_DATA_DIR']
as_of_date = app['AS_OF_DATE']
# Got Coverage ?
if table_exists(mycursor, 'coverage'):
sql = "DROP TABLE coverage"
mycursor.execute(sql)
sql = ("CREATE TABLE coverage ("
"`PSS` text,"
"`TSA` text,"
"`Sales Level 1` text,"
"`Sales Level 2` text,"
"`Sales Level 3` text,"
"`Sales Level 4` text,"
"`Sales Level 5` text,"
"`Fiscal Year` text ) ")
mycursor.execute(sql)
cnx.commit()
sql = ("load data local infile '" + working_dir + "coverage.csv" +
"' into table coverage "
"fields terminated by ',' "
"enclosed by '\"' "
"escaped by '' "
"lines terminated by '\r\n' "
"ignore 1 lines")
mycursor.execute(sql)
cnx.commit()
print("Updated Coverage Table !")
#Got PIDS ?
if table_exists(mycursor, 'pids'):
sql = "DROP TABLE pids"
mycursor.execute(sql)
sql = ("CREATE TABLE `pids` ("
"`Product Family` text,"
"`Product ID` text,"
"`Description` text)")
mycursor.execute(sql)
cnx.commit()
sql = ("load data local infile '" + working_dir + "pids.csv" +
"' into table pids "
"fields terminated by ',' "
"enclosed by '\"' "
"escaped by '' "
"lines terminated by '\r\n' "
"ignore 1 lines")
mycursor.execute(sql)
cnx.commit()
print("Updated Product IDs Table !")
#Clean up old Bookings data
if table_exists(mycursor, 'master_bookings_data'):
sql = "DROP TABLE master_bookings_data"
mycursor.execute(sql)
print("Deleted OLD Master Bookings Data...")
cnx.commit()
if table_exists(mycursor, 'todays_bookings_data'):
sql = "DROP TABLE todays_bookings_data"
mycursor.execute(sql)
print("Deleted Daily Bookings Data...")
cnx.commit()
#Create master_bookings table
#
sql = ('CREATE TABLE master_bookings_data '
'(`Fiscal Year` TEXT,'
'`Fiscal Quarter ID` TEXT,'
'`Fiscal Period ID` TEXT,'
'`Fiscal Week ID` TEXT,'
'`Date Booked` DATE,'
'`Sales Level 1` TEXT,'
'`Sales Level 2` TEXT,'
'`Sales Level 3` TEXT,'
'`Sales Level 4` TEXT,'
'`Sales Level 5` TEXT,'
'`Sales Level 6` TEXT,'
'`Sales Agent Name` TEXT,'
'`Internal Business Entity Name` TEXT,'
'`Internal Sub Business Entity Name` TEXT,'
'`Product Family` TEXT,'
'`Product ID` TEXT,'
'`End Customer Global Ultimate Name` TEXT,'
'`End Customer Global Ultimate Company Target ID` TEXT,'
'`Ship to ERP Customer Name` TEXT,'
'`Sales Order Number Detail` TEXT,'
'`ERP Deal ID` TEXT,'
'`Corporate Bookings Flag` TEXT,'
'`Partner Name` TEXT,'
'`SCMS` TEXT,'
'`Product Bookings Net` TEXT,'
'`Service Bookings Net` TEXT,'
'`Bookings Adjustments Description` TEXT ,'
'`Hash Value` VARCHAR (32)) ')
mycursor.execute(sql)
cnx.commit()
#Add an index to speed customer lookups
sql = ("ALTER TABLE `master_bookings_data` "
"ADD INDEX `cust` (`End Customer Global Ultimate Name`(75) ASC)")
mycursor.execute(sql)
cnx.commit()
#Make a duplicate for todays data
#
sql = "CREATE TABLE todays_bookings_data LIKE master_bookings_data;"
mycursor.execute(sql)
print("Created NEW Master Bookings Data...")
cnx.commit()
print("Importing NEW Bookings Data...")
sql = ("load data local infile '" + working_dir + working_data_dir +
working_csv_file + "' into table todays_bookings_data "
"fields terminated by ',' "
"enclosed by '\"' "
"escaped by '' "
"lines terminated by '\r\n' "
"ignore 1 lines")
mycursor.execute(sql)
cnx.commit()
sql = "INSERT INTO master_bookings_data SELECT archive_bookings_data_fy15.* FROM archive_bookings_data_fy15"
mycursor.execute(sql)
print("Gathered FY15 Archived Bookings Data...")
cnx.commit()
sql = "INSERT INTO master_bookings_data SELECT archive_bookings_data_fy16.* FROM archive_bookings_data_fy16"
mycursor.execute(sql)
print("Gathered FY16 Archived Bookings Data...")
cnx.commit()
sql = "INSERT INTO master_bookings_data SELECT archive_bookings_data_fy17.* FROM archive_bookings_data_fy17"
mycursor.execute(sql)
print("Gathered FY17 Archived Bookings Data...")
cnx.commit()
sql = "INSERT INTO master_bookings_data SELECT todays_bookings_data.* FROM todays_bookings_data"
mycursor.execute(sql)
print("Gathered FY18 Current Bookings Data...")
cnx.commit()
cnx.close()
def create_cust_data():
start_time = datetime.now()
print('\tcreate_cust_data started: ', datetime.now())
#Create 2 mySQL connections
cnx = mysql.connector.connect(user=database['USER'],
password=database['PASSWORD'],
host=database['HOST'],
database=database['DATABASE'])
mycursor = cnx.cursor()
cnx1 = mysql.connector.connect(user=database['USER'],
password=database['PASSWORD'],
host=database['HOST'],
database=database['DATABASE'])
mycursor1 = cnx1.cursor()
#Delete and re-create master customer data as required
if table_exists(mycursor, 'master_customer_data'):
mycursor.execute("SELECT COUNT(*) FROM master_customer_data")
current_customers = mycursor.fetchone()
print("\t\tMaster Customer Data: ", current_customers[0])
sql = "DROP TABLE master_customer_data"
mycursor.execute(sql)
print("\t\tDeleted OLD Master Customer Data...")
cnx.commit()
sql = ('CREATE TABLE master_customer_data ('
'`End Customer Global Ultimate Name` TEXT,'
'`End Customer Global Ultimate Company Target ID` TEXT,'
'`Qty of N9300 PIDs ordered` TEXT,'
'`Qty of N9500 PIDs ordered` TEXT,'
'`Qty of APIC PIDs ordered` TEXT,'
'`Qty of C3 PIDs ordered` TEXT,'
'`Qty of NFM PIDs ordered` TEXT,'
'`Qty of Tetration PIDs ordered` TEXT,'
'`Sales Agent Name` TEXT,'
'`Assigned PSS` TEXT,'
'`Assigned TSA` TEXT,'
'`Reference Customer ?` TEXT,'
'`Target as ACI to Production by end of Q1 ?` TEXT,'
'`Equipment Status ?` TEXT,'
'`Next Steps` TEXT,'
'`Customer Vertical` TEXT,'
'`Target for Competitive Pursuit ?` TEXT,'
'`Other PSS` TEXT,'
'`Sales Level 1` TEXT,'
'`Sales Level 2` TEXT,'
'`Sales Level 3` TEXT,'
'`Sales Level 4` TEXT,'
'`Sales Level 5` TEXT,'
'`MultipleBookings` TEXT,'
'`FirstDateBooked` DATE,'
'`LastDateBooked` DATE,'
'`Last Refresh` TEXT)')
mycursor.execute(sql)
cnx.commit()
# Load the interesting Product ID list and create two dicts (one by ProdID and one by ProdFam)
mycursor.execute("SELECT * FROM pids")
products = mycursor.fetchall()
prod_id_dict = {}
prod_fam_dict = {}
for product in products:
prod_id_dict[product[1]] = (product[0], product[2])
prod_fam_dict[product[0]] = (product[1], product[2])
#Get each unique Customer Name
sql = (
'SELECT DISTINCT `End Customer Global Ultimate Name` FROM `master_bookings_data`'
'ORDER BY `End Customer Global Ultimate Name` ASC')
mycursor.execute(sql)
customers = mycursor.fetchall()
print("\t\tCustomers Found: ", len(customers))
# Create a the Team Coverage Object
my_coverage = Coverage()
progress = 0
for customer in customers:
#Gather this customers orders
sql = (
"SELECT * FROM `master_bookings_data` WHERE `End Customer Global Ultimate Name` = %s "
"ORDER BY `Date Booked` ASC")
query_values = (customer)
mycursor.execute(sql, query_values)
orders = mycursor.fetchall()
# Init per customer counters and variables
n9300_PID = 0
n9500_PID = 0
APIC_PID = 0
C3_PID = 0
EMBRANE_PID = 0
TETR_PID = 0
pss = ''
tsa = ''
first_date_booked = date(2030, 3, 1) #Random Date in the future
last_date_booked = date(1900, 3, 1) #Default earliest date for Excel
for order in orders:
# Init per customer counters and variables
current_customer = order[16]
current_customer_id = order[17]
account_mgr = order[11]
# Set the first and last date booked
if first_date_booked > order[4]:
first_date_booked = order[4]
if last_date_booked < order[4]:
last_date_booked = order[4]
# Do we count this PID ? If not skip it
# order[14] has ProdFam, order[15] has ProdID
if order[14] == "TETR":
TETR_PID += 1
elif order[14] == "C3":
C3_PID += 1
elif prod_id_dict.get(order[15]) is not None:
if prod_id_dict[order[15]][0] == 'N9300':
n9300_PID += 1
elif prod_id_dict[order[15]][0] == 'N9500':
n9500_PID += 1
elif prod_id_dict[order[15]][0] == 'APIC':
APIC_PID += 1
elif prod_id_dict[order[15]][0] == 'EMBRANE':
EMBRANE_PID += 1
else:
continue # Get the next order since this is not interesting
# Get the territory this booked in
sales_lv1 = order[5]
sales_lv2 = order[6]
sales_lv3 = order[7]
sales_lv4 = order[8]
sales_lv5 = order[9]
sales_str = order[5] + order[6] + order[7] + order[8] + order[9]
#Find the team(s) that cover this customer in this territory
teams = my_coverage.find_team(sales_str)
pss = ' '.join(teams[0])
tsa = ' '.join(teams[1])
# We are done with this customer
# Create a summary customer record
sql = (
'INSERT INTO master_customer_data '
' (`End Customer Global Ultimate Name`,'
' `End Customer Global Ultimate Company Target ID`,'
' `Qty of N9300 PIDs ordered`,'
' `Qty of N9500 PIDs ordered`,'
' `Qty of APIC PIDs ordered`,'
' `Qty of C3 PIDs ordered`,'
' `Qty of NFM PIDs ordered`,'
' `Qty of Tetration PIDs ordered`,'
' `Sales Agent Name`,'
' `Assigned PSS`,'
' `Assigned TSA`,'
' `Reference Customer ?`,'
' `Target as ACI to Production by end of Q1 ?`,'
' `Equipment Status ?`,'
' `Next Steps`,'
' `Customer Vertical`,'
' `Target for Competitive Pursuit ?`,'
' `Other PSS`,'
' `Sales Level 1`,'
' `Sales Level 2`,'
' `Sales Level 3`,'
' `Sales Level 4`,'
' `Sales Level 5`,'
' `MultipleBookings`,'
' `FirstDateBooked`,'
' `LastDateBooked`,'
' `Last Refresh`)'
' VALUES'
' ("' + current_customer + '",'
'"' + current_customer_id + '",'
'"' + str(n9300_PID) + '",'
'"' + str(n9500_PID) + '",'
'"' + str(APIC_PID) + '",'
'"' + str(C3_PID) + '",'
'"' + str(EMBRANE_PID) + '",'
'"' + str(TETR_PID) + '",'
'"' + account_mgr + '",'
'"' + pss + '",' # Assigned PSS name
'"' + tsa + '",' # Assigned TSA name
'"Reference Customer ?",'
'"Target as ACI to Production by end of Q1",'
'"Equipment Status ?",'
'"Next Steps",'
'"Customer Vertical",'
'"Target for Competitive Pursuit ?",'
'"Other PSS",'
'"' + sales_lv1 + '",' # Sales Level 1
'"' + sales_lv2 + '",' # Sales Level 2
'"' + sales_lv3 + '",' # Sales Level 3
'"' + sales_lv4 + '",' # Sales Level 4
'"' + sales_lv5 + '",' # Sales Level 5
'"' + str(first_date_booked != last_date_booked) +
'",' # Multiple Bookings
'"' + str(first_date_booked) + '",' # First Date Booked
'"' + str(last_date_booked) + '",' # Last Date Booked
'"Last Refresh")')
mycursor1.execute(sql)
cnx1.commit()
#Show progress for every 1000 customers processed
progress = progress + 1
if progress % 1000 == 0:
print("\t\t Customers Processed: ", progress)
#Clean up and Close out
cnx.close()
cnx1.close()
end_time = datetime.now()
print('\t\trun time: ', end_time - start_time)
print('\tcreate_cust_data module complete: ', datetime.now())
def import_archive():
# This is to import an entire Fiscal Year of Data
cnx = mysql.connect(user=database['USER'],
passwd=database['PASSWORD'],
host=database['HOST'],
db=database['DATABASE'])
mycursor = cnx.cursor()
working_dir = app['WORKING_DIR']
working_file = app['ARCHIVE_FILE']
working_csv_file = app['ARCHIVE_CSV_FILE']
working_data_dir = app['WORKING_DATA_DIR']
as_of_date = app['AS_OF_DATE']
#Convert xls bookings file to CVS
print("\tProcessing FY17 Archive Bookings Data...")
csv_from_excel(working_dir + working_data_dir + working_file, 'Data')
#Stamp it and save it
stamp_it(working_dir + working_data_dir + working_file, as_of_date)
#CSV file now prepped.
#Clean up existing archive if it exists
if table_exists(mycursor, 'archive_bookings_data_fy17'):
sql = "DROP TABLE archive_bookings_data_fy17"
mycursor.execute(sql)
print("Deleted FY17 Archive Bookings Data...")
cnx.commit()
#Create new archive bookings table
#
sql = ('CREATE TABLE archive_bookings_data_fy17 '
'(`Fiscal Year` TEXT,'
'`Fiscal Quarter ID` TEXT,'
'`Fiscal Period ID` TEXT,'
'`Fiscal Week ID` TEXT,'
'`Date Booked` DATE,'
'`Sales Level 1` TEXT,'
'`Sales Level 2` TEXT,'
'`Sales Level 3` TEXT,'
'`Sales Level 4` TEXT,'
'`Sales Level 5` TEXT,'
'`Sales Level 6` TEXT,'
'`Sales Agent Name` TEXT,'
'`Internal Business Entity Name` TEXT,'
'`Internal Sub Business Entity Name` TEXT,'
'`Product Family` TEXT,'
'`Product ID` TEXT,'
'`End Customer Global Ultimate Name` TEXT,'
'`End Customer Global Ultimate Company Target ID` TEXT,'
'`Ship to ERP Customer Name` TEXT,'
'`Sales Order Number Detail` TEXT,'
'`ERP Deal ID` TEXT,'
'`Corporate Bookings Flag` TEXT,'
'`Partner Name` TEXT,'
'`SCMS` TEXT,'
'`Product Bookings Net` TEXT,'
'`Service Bookings Net` TEXT,'
'`Bookings Adjustments Description` TEXT ,'
'`Hash Value` VARCHAR (32)) ')
mycursor.execute(sql)
cnx.commit()
#Add an index to speed customer lookups
sql = ("ALTER TABLE `archive_bookings_data_fy17` "
"ADD INDEX `cust` (`End Customer Global Ultimate Name`(75) ASC)")
mycursor.execute(sql)
cnx.commit()
print("Importing FY17 Archive Bookings Data...")
sql = ("load data local infile '" + working_dir + working_data_dir +
working_csv_file + "' into table archive_bookings_data_fy17 "
"fields terminated by ',' "
"enclosed by '\"' "
"escaped by '' "
"lines terminated by '\r\n' "
"ignore 1 lines")
mycursor.execute(sql)
cnx.commit()
cnx.close()