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()