Example #1
0
 def __init__(self, path):
     self.path = os.path.join(os.path.expanduser("~"), path)
     self.source_file = None
     self._set_folder()
     self.data_list = []
     self.year, self.quarter = COM.fiscal_year_quarter(
         datetime.datetime.utcnow())
Example #2
0
	def main():
		while True:
			fy, fq = COM.fiscal_year_quarter()
			print('_'*100)
			print('| WELCOME TO BAP QUARTERLY ETL\n| FISCAL YEAR:     {}\n| FISCAL QUARTER:     {}'.format(fy, fq - 1))
			print('_' * 100)
			menu = '''
			1: Show Source File for BAP quarterly FY18-Q3
			1a: CHECK Columns Completeness
			2: QA spreadsheet by RIC
			3: Combine RICs BQ spreadsheet
			4: QA RICs BQ combined spreadsheet
			5: Push RICs data ro the database
			6: Generate Batch for RICs FY18 -Q3
			7: Match Company name
			8: Push Company data to DIM COMPANY and DIM COMPANY SOURCE
			9: Push quarterly company data to FACT RIC COMPANY DATA
			10: Push Annual company data to FACT RIC COMPANY DATA
			11: push Program and Program youth data to FACT RIC Aggregation
			'''
			print(menu)

			option = input('\nChoose your option:\t')
			if str(option) == '1':
				BapQuarterly.show_bap_quarterly_template()
			if str(option) == '1a':
				BapQuarterly.qa.check_columns_completeness()
			if str(option) == '2':
				BapQuarterly.qa.check_rics_file()
			if str(option) == '3':
				pass
			if str(option) == '4':
				pass
			if str(option) == '5':
				pass
			if str(option) == '6':
				pass
			if str(option) == '7':
				pass
			if str(option) == '8':
				pass
			if str(option) == '9':
				pass
			if str(option) == '10':
				pass
			if str(option) == '11':
				pass
			if str(option) == '12':
				pass
Example #3
0
    def write_survey_entries(self, api_token):

        year, quarter = CM.fiscal_year_quarter()

        api_surveys_df = self.get_surveys(api_token, prin=False)
        api_surveys_df = api_surveys_df.apply(pd.to_numeric, errors='ignore')

        db_surveys_sql = CM.get_config("config.ini", "sql_queries", "surveys")
        db_surveys_df = DB.pandas_read(db_surveys_sql)
        db_surveys_df = db_surveys_df.apply(pd.to_numeric, errors='ignore')

        surveys_not_in_db = pd.merge(api_surveys_df,
                                     db_surveys_df[['id']],
                                     how='outer',
                                     indicator=True,
                                     on="id")
        surveys_not_in_db2 = surveys_not_in_db[surveys_not_in_db['_merge'] ==
                                               'left_only'].drop("_merge",
                                                                 axis=1)

        # write surveys_not_in_db2 to db, one at a time so BatchService can be executed for each one
        for index in range(len(surveys_not_in_db2)):
            row = surveys_not_in_db2.iloc[index][:]
            df = pd.DataFrame([list(row.values)],
                              columns=list(surveys_not_in_db2))

            batch = BatchService()
            x = batch.create_new_batch(datasource=-1,
                                       systemsource=50,
                                       year=year,
                                       quarter=quarter)
            batch_id = x.iloc[-1][0]

            # add batchID to end of df
            df['BatchID'] = int(batch_id)

            self.df_to_db(df, "insert_survey_entry")

        pass
Example #4
0
class BapQuarterly:
	desired_width = 420
	pd.set_option('display.width', desired_width)

	year, quarter = COM.fiscal_year_quarter(datetime.datetime.utcnow())
	quarter = quarter - 1
	batch = BatchService()
	bap_path_source = COM.get_config('config.ini', 'box_file_path', 'path_bap_source')
	bap_path_etl = COM.get_config('config.ini', 'box_file_path', 'path_bap_etl')
	file = FileService(bap_path_source)
	qa = BapQA()
	season = '19_Q1'
	company = CompanyService()

	@staticmethod
	def show_bap_quarterly_template():
		BapQuarterly.file.show_source_file()

	'''
	Checks if all the RICs send the right template with all the columns exists.
	'''
	@staticmethod
	def qa_bap_spread_sheet_by_ric():
		BapQuarterly.qa.check_rics_file(fp.path_bap_etl, fp.path_bap_qa)

	@staticmethod
	def combine_rics_bap_quarterly(combine_for):
		program, program_youth, company_quarterly, company_annually = BapQuarterly.file.read_source_file(
			FileType.SPREAD_SHEET.value, DS.BAP, combine_for, current_path=fp.path_bap_qa.value)
		file_name = '{}'.format(FN.bap_combined.value.format('19','1'))#(str(BapQuarterly.year - 1)[-2:], BapQuarterly.quarter))
		if combine_for == Combine.FOR_QA:
			file_name = 'QA_' + file_name
		else:
			file_name = 'ETL_' + file_name

		print('\nSave spreadsheet file named: {}'.format(file_name))

		save_location = COM.change_working_directory(fp.path_bap_combined.value)
		print(str(save_location))

		writer = pd.ExcelWriter(file_name)
		
		program.to_excel(writer, WS.bap_program.value, index=False)
		program_youth.to_excel(writer, WS.bap_program_youth.value, index=False)
		company_quarterly.to_excel(writer, WS.bap_company.value, index=False)
		if BapQuarterly.quarter == 3:
			company_annually.to_excel(writer, WS.bap_company_annual.value, index=False)
		writer.save()
		
		print('rics_spreasheet_combined.')

	@staticmethod
	def qa_bap_ric_combined(combined=False):
		BapQuarterly.qa.check_rics_file(fp.path_bap_combined, fp.path_bap_combined_dest, combined)

	@staticmethod
	def transfer_csv_program(dataframe):
		val = COM.df_list(dataframe)
		db.bulk_insert(sql.sql_bap_ric_program_insert.value, val)
	
	@staticmethod
	def transfer_csv_program_youth(dataframe):
		val = COM.df_list(dataframe)
		db.bulk_insert(sql.sql_bap_ric_program_youth_insert.value, val)
	
	@staticmethod
	def bulk_insert_quarterly_data(dataframe):
		val = COM.df_list(dataframe)
		db.bulk_insert(sql.sql_bap_ric_venture_quarterly_insert.value, val)

	@staticmethod
	def bulk_insert_annual_data(dataframe):
		val = COM.df_list(dataframe)
		db.bulk_insert(sql.sql_bap_ric_venture_annual_insert.value, val)

	@staticmethod
	def push_bap_quarterly_to_database():
		COM.change_working_directory(fp.path_bap_combined.value)

		bap = pd.read_excel('ETL_RICS_BAP_COMBINED_FY19Q1.xlsx', sheet_name=None)

		# BapQuarterly.transfer_csv_program(bap['csv_program16'])
		# BapQuarterly.transfer_csv_program_youth(bap['csv_program16_youth'])
		BapQuarterly.bulk_insert_quarterly_data(bap['Quarterly Company Data'])
		if BapQuarterly.quarter == 3:
			BapQuarterly.bulk_insert_annual_data(bap['Annual Company data'])

	@staticmethod
	def create_bap_batch():
		batch = BatchService()
		program = db.pandas_read(sql.sql_bap_distinct_batch.value.format(tbl.ric_program.value,BapQuarterly.year,BapQuarterly.quarter))
		program_youth = db.pandas_read(sql.sql_bap_distinct_batch.value.format(tbl.ric_program_youth.value, BapQuarterly.year,BapQuarterly.quarter))
		company = db.pandas_read(sql.sql_bap_distinct_batch.value.format(tbl.venture_data.value, BapQuarterly.year, BapQuarterly.quarter))
		comapny_annual = db.pandas_read(sql.sql_annual_bap_distinct_batch.value.format(tbl.venture_annual.value,BapQuarterly.year))

		# batch.create_bap_batch(program, BapQuarterly.year, BapQuarterly.quarter, tbl.ric_program.value, WS.bap_program.value, ss.RICPD_bap.value)
		# batch.create_bap_batch(program_youth, BapQuarterly.year, BapQuarterly.quarter, tbl.ric_program_youth.value, WS.bap_program_youth.value, ss.RICPDY_bap.value)
		batch.create_bap_batch(company, BapQuarterly.year, BapQuarterly.quarter, tbl.venture_data.value, WS.bap_company.value, ss.RICCD_bap.value)
		if BapQuarterly.quarter == 3:
			batch.create_bap_batch(comapny_annual, BapQuarterly.year, BapQuarterly.quarter, tbl.venture_annual.value, WS.bap_company_annual.value, ss.RICACD_bap.value)
	
	@staticmethod
	def transfer_bap_company():
		cs = CompanyService()
		cs.move_company_data()

	@staticmethod
	def get_proper_values(df):
		df['StageLevelID'] = df.apply(lambda dfs: COM.get_stage_level(dfs.Stage), axis=1)
		df['High Potential y/n'] = df.apply(lambda dfs: COM.get_yes_no(dfs['High Potential y/n']), axis=1)
		df['Social Enterprise y/n'] = df.apply(lambda dfs: COM.get_yes_no(dfs['Social Enterprise y/n']), axis=1)
		df['Youth'] = df.apply(lambda dfs: COM.get_yes_no(dfs['Youth']), axis=1)
		# df['Funding Raised to Date $CAN'] = df.apply(lambda dfs: BapQuarterly.split_funding_range(dfs['Funding Raised to Date $CAN']), axis=1)
		return df

	@staticmethod
	def transfer_fact_ric_company_data():
		df = db.pandas_read(sql.sql_bap_fact_ric_data_fyq4.value)
		df_frc = BapQuarterly.get_proper_values(df)
		# BapQuarterly.update_month_year(df_frc)
		# df_frc['IntakeDate'] = pd.to_datetime(df_frc['IntakeDate'])
		df_frc['Age'] = None
		# df_frc['Date of Incorporation'] = pd.to_datetime(df_frc['Date of Incorporation'])
		# df_ric = df_frc.drop(columns=['ID', 'Incorporate year (YYYY)', 'Incorporation month (MM)'])
		# BapQuarterly.file.save_as_csv(df_frc, '00 FactRICCompany.xlsx', os.getcwd(), 'FactRICCompany')
		values_list = COM.df_list(df_frc)

		db.bulk_insert(sql.sql_bap_fact_ric_company_insert.value, values_list)

	@staticmethod
	def split_funding_range(funding):
		funding_value = 0
		if funding == '$100-149k':
			funding_value = '100000'
		elif funding == '$10-24k':
			funding_value = '10000'
		elif funding == '$150-249k':
			funding_value = '150000'
		elif funding == '$1M-1.9M':
			funding_value = '1000000'
		elif funding == '$250-499k':
			funding_value = '250000'
		elif funding == '$25-49k':
			funding_value = '25000'
		elif funding == '$2-5M':
			funding_value = '2000000'
		elif funding == '$2M-5M':
			funding_value = '2000000'
		elif funding == '$500-999k':
			funding_value = '500000'
		elif funding == '$50-99k':
			funding_value = '50000'
		elif funding == '<$10k':
			funding_value = '1000'
		elif funding == '>$5M':
			funding_value = '5000000'

		return funding_value

	@staticmethod
	def update_month_year(df):
		i = 0
		for index, row in df.iterrows():
			if row['Incorporate year (YYYY)'] is not None and row['Incorporation month (MM)'] is not None:
				row['Date of Incorporation'] = parser.parse('{}-{}-15'.format(row['Incorporate year (YYYY)'], row['Incorporation month (MM)']))
				i+=1
				print('{}. {}'.format(i, row['Date of Incorporation']))
		# for index, row in df.iterrows():
		# 	if row['Incorporate year (YYYY)'] is not None and len(row['Incorporate year (YYYY)']) > 4:
		# 		update = 'UPDATE BAP.QuarterlyCompanyData SET [Incorporate year (YYYY)] = {} WHERE ID = {} -- {}'.format(parser.parse(row['Incorporate year (YYYY)']).year, row['ID'], parser.parse(row['Incorporate year (YYYY)']))
		# 		print(update)
		# dfs = df[df['Incorporate year (YYYY)'].isnull()]
		# for index, row in dfs.iterrows():
		# 	if row['Incorporation month (MM)'] is not None and len(row['Incorporation month (MM)']) > 2:
		# 		update = 'UPDATE BAP.QuarterlyCompanyData SET [Incorporate year (YYYY)] = {} WHERE ID = {}'.format(parser.parse(row['Incorporation month (MM)']).year, row['ID'])
		# 		print(update)
		# i= 0
		# for index, row in df.iterrows():
		# 	if row['Incorporation month (MM)'] is not None and len(row['Incorporation month (MM)']) > 2:
		# 		i += 1
		# 		update = 'UPDATE BAP.QuarterlyCompanyData SET [Incorporation month (MM)] = {} WHERE ID = {}'.format(parser.parse(row['Incorporation month (MM)']).month, row['ID'])
		# 		print(update)
		print('')

	@staticmethod
	def transfer_fact_ric_aggregation():
		date_id = COM.get_dateid(datevalue=None)
		metric_prg = [130, 132, 133, 129, 134, 63, 77, 60, 68, 67, 135, 136, 137]
		metric_prg_youth = [134, 138]
		
		df_program = db.pandas_read(sql.sql_company_aggregate_program.value.format(2018, 4))#(BapQuarterly.year, BapQuarterly.quarter))
		df_program_youth = db.pandas_read(sql.sql_company_aggregate_program_youth.value.format(2018, 4))#(BapQuarterly.year, BapQuarterly.quarter))
		
		values = []
		
		for _, row in df_program.iterrows():
			i = 7
			while i < 20:
				m = i - 7
				val = []
				val.append(int(row['DataSource']))  # DataSource
				val.append(int(date_id))  # RICDateID
				val.append(int(metric_prg[m]))  # MetricID
				val.append(int(row['BatchID']))  # BatchID
				
				if str(row[i]) in ['no data', 'n\\a', '-', 'n/a', 'nan']:
					val.append(-1.0)
					print(row[i])
				else:
					val.append(round(float(row[i]), 2))  # AggregateNumber
				val.append(str(datetime.datetime.today())[:23])  # ModifiedDate
				val.append(str(datetime.datetime.today())[:23])  # CreatedDate
				val.append(row['Youth'])  # Youth
				values.append(val)
				i = i + 1
				# db.execute(sql.sql_bap_fra_insert.value.format(tuple(val)))
		
		for _, row in df_program_youth.iterrows():
			
			j = 7
			while j < 9:
				m = j - 7
				val = []
				val.append(int(row['DataSource']))  # DataSource
				val.append(int(date_id))  # RICDateID
				val.append(int(metric_prg_youth[m]))  # MetricID
				val.append(int(row['BatchID']))  # BatchID
				if str(row[j]) in ['no data', 'n\\a', '-', 'n/a', 'nan']:
					val.append(-1.0)
					print(row[j])
				else:
					val.append(round(float(row[j]), 2))  # AggregateNumber
				val.append(str(datetime.datetime.today())[:23])  # ModifiedDate
				val.append(str(datetime.datetime.today())[:23])  # CreatedDate
				val.append(row['Youth'])  # Youth
				
				values.append(val)
				j = j + 1
				# db.execute(sql.sql_bap_fra_insert.value.format(tuple(val)))
		for val in range(len(values)):
			print('{}. {}'.format(val,values[val]))
			# print('{}. {}'.format(val,values[val][1]))
		db.bulk_insert(sql.sql_bap_fact_ric_aggregation_insert.value, values)
	
	@staticmethod
	def generate_bap_rolled_up():
		company = []
		i = 0
		df_frcd = db.pandas_read(sql.sql_bap_fact_ric_company.value.format(BapQuarterly.year))
		print('Number of record to process {} '.format(len(df_frcd)))
		df_fact_ds_quarter = db.pandas_read(sql.sql_bap_report_company_ds_quarter.value.format(BapQuarterly.year))
		df_FactRICRolledUp = pd.DataFrame(columns=clm.clmn_fact_ric_rolled_up.value)
		df_industry = db.pandas_read(sql.sql_industry_list_table.value)
		cq = BapQuarterly.quarter
		total = 0
		if not df_frcd.empty:
			for _, row in df_fact_ds_quarter.iterrows():
				company_id = row['CompanyID']
				data_source_id = row['DataSourceID']
				
				i = i + 1
				print('{}. {}'.format(i, company_id))
				# ['Q1', 'Q2']
				ls_q = []
				ls_quarters = \
				df_fact_ds_quarter.query('CompanyID == {} & DataSourceID == {}'.format(company_id, data_source_id))[
					'MinFQ'].tolist()
				ls = df_frcd.query('CompanyID == {}'.format(company_id))['FiscalQuarter'].tolist()
				
				for itm in ls_quarters:
					ls_q.append(itm[-1:])
				# if str(cq) not in ls_q:
				# 	ls_q.append(cq)
				df_agg = df_frcd.query('CompanyID == {} & DataSourceID == {}'.format(company_id, data_source_id))
				print(ls_q)
				for quarter in ls_q:
					if int(quarter) == cq and len(df_agg) > 1:
						current_quarter = 'FiscalQuarter == \'Q{}\''.format(quarter)
						previous_quarter = 'FiscalQuarter == \'Q{}\''.format(int(quarter) - 1)
					elif int(quarter) == cq and len(df_agg) == 1:
						current_quarter = 'FiscalQuarter == \'Q{}\''.format(quarter)
					elif int(quarter) < cq:
						current_quarter = 'FiscalQuarter == \'Q{}\''.format(quarter)
					
					try:
						batch_id = df_agg.query(current_quarter)['BatchID'].values[0] if not df_agg.query(
							current_quarter).empty else None
						min_date = -1
						current_date = -1
						vhs = df_agg.query(current_quarter)['VolunteerMentorHours'].values[0] if not df_agg.query(
							current_quarter).empty else None
						adv = df_agg.query(current_quarter)['AdvisoryServicesHours'].values[0] if not df_agg.query(
							current_quarter).empty else None
						
						if int(quarter) == cq:
							vhs_agg = df_agg['VolunteerMentorHours'].sum()
							adv_agg = df_agg['AdvisoryServicesHours'].sum()
							funding_agg = df_agg['FundingCurrentQuarter'].sum()
						else:
							vhs_agg = float(df_agg['VolunteerMentorHours'].sum()) - float(
								df_agg.query('FiscalQuarter == \'Q{}\''.format(cq))['VolunteerMentorHours'].values[
									0]) if not df_agg.query('FiscalQuarter == \'Q{}\''.format(cq)).empty else float(
								df_agg.query('FiscalQuarter == \'Q{}\''.format(cq - 1))['VolunteerMentorHours'].values[
									0])
							adv_agg = float(df_agg['AdvisoryServicesHours'].sum()) - float(
								df_agg.query('FiscalQuarter == \'Q{}\''.format(cq))['AdvisoryServicesHours'].values[
									0]) if not df_agg.query('FiscalQuarter == \'Q{}\''.format(cq)).empty else float(
								df_agg.query('FiscalQuarter == \'Q{}\''.format(cq - 1))['AdvisoryServicesHours'].values[
									0])
							funding_agg = float(df_agg['FundingCurrentQuarter'].sum()) - float(
								df_agg.query('FiscalQuarter == \'Q{}\''.format(cq))['FundingCurrentQuarter'].values[
									0]) if not df_agg.query('FiscalQuarter == \'Q{}\''.format(cq)).empty else float(
								df_agg.query('FiscalQuarter == \'Q{}\''.format(cq - 1))['FundingCurrentQuarter'].values[
									0])
						
						modified_date = datetime.datetime.utcnow().__str__()[:23]
						
						stage = df_agg.query(current_quarter)['Stage'].values[0] if not df_agg.query(
							current_quarter).empty else df_agg.query(previous_quarter)['Stage'].values[0]
						industry_sector = df_agg.query(current_quarter)['IndustrySector'].values[0] if not df_agg.query(
							current_quarter).empty else df_agg.query(previous_quarter)['IndustrySector'].values[0]
						socialEnterprise = df_agg.query(current_quarter)['SocialEnterprise'].values[
							0] if not df_agg.query(current_quarter).empty else \
						df_agg.query(previous_quarter)['SocialEnterprise'].values[0]
						highPotential = df_agg.query(current_quarter)['HighPotential'].values[0] if not df_agg.query(
							current_quarter).empty else df_agg.query(previous_quarter)['HighPotential'].values[0]
						youth = df_agg.query(current_quarter)['Youth'].values[0] if not df_agg.query(
							current_quarter).empty else df_agg.query(previous_quarter)['Youth'].values[0]
						dateOfIncorporation = df_agg.query(current_quarter)['DateOfIncorporation'].values[
							0] if not df_agg.query(current_quarter).empty else \
						df_agg.query(previous_quarter)['DateOfIncorporation'].values[0]
						
						annual_revenue = df_agg.query(current_quarter)['AnnualRevenue'].values[0] if not df_agg.query(
							current_quarter).empty else None
						funding_current_quarter = df_agg.query(current_quarter)['FundingCurrentQuarter'].values[
							0] if not df_agg.query(current_quarter).empty else None
						
						number_of_employees = df_agg.query(current_quarter)['NumberEmployees'].values[
							0] if not df_agg.query(current_quarter).empty else None
						intake_date = df_agg.query(current_quarter)['IntakeDate'].values[0] if not df_agg.query(
							current_quarter).empty else None
						lvl2_industry_name = df_industry.query('Industry_Sector == \'{}\''.format(industry_sector))[
							'Lvl2IndustryName'].values[0] if not df_industry.query(
							'Industry_Sector == \'{}\''.format(industry_sector)).empty else None
						dd = {'DataSourceID': data_source_id,
							  'CompanyID': company_id,
							  'MinDate': min_date,
							  'CurrentDate': current_date,
							  'VolunteerYTD': vhs_agg,
							  'AdvisoryHoursYTD': adv_agg,
							  'VolunteerThisQuarter': vhs,
							  'AdvisoryThisQuarter': adv,
							  'FiscalQuarter': quarter,
							  'BatchID': batch_id,
							  'ModifiedDate': modified_date,
							  'SocialEnterprise': socialEnterprise,
							  'Stage': stage,
							  'HighPotential': highPotential,
							  'Lvl2IndustryName': lvl2_industry_name,
							  'FiscalYear': BapQuarterly.year,
							  'Youth': youth,
							  'DateOfIncorporation': dateOfIncorporation,
							  'AnnualRevenue': annual_revenue,
							  'NumberEmployees': number_of_employees,
							  'FundingToDate': funding_current_quarter,
							  'IndustrySector': industry_sector,
							  'IntakeDate': intake_date,
							  'FundingCurrentQuarter': funding_agg
							  }
						print(dd.values())
						df = pd.DataFrame([dd], columns=clm.clmn_fact_ric_rolled_up.value)
						df_FactRICRolledUp = pd.concat([df_FactRICRolledUp, df])
					except Exception as ex:
						total = total + 1
						company.append(company_id)
						print(ex)
			df_FactRICRolledUp = df_FactRICRolledUp[clm.clmn_fact_ric_rolled_up.value]
			BapQuarterly.file.save_as_csv(df_FactRICRolledUp,
										  'BAP_Rolled_UP_{}.xlsx'.format(str(datetime.datetime.today())),
										  '/Users/mnadew/Box Sync/mnadew/IE/data/ETL/BAP')
			print(company)
			print('{} + {} = {}/ 6236 '.format(len(df_FactRICRolledUp), total, len(df_FactRICRolledUp) + total))
	
	# @staticmethod
	# def generate_bap_report():
	# 	pass
	
	@staticmethod
	def create_postal_code_list():
		pcdb = PostalCodeDatabase()
		results = pcdb.get_postalcodes_around_radius('T3Z', 2500)
		print(type(results))
		cl = ['postalcode', 'city', 'province', 'longitude', 'latitude', 'timezone', 'dst']
		dfs = pd.DataFrame(columns=cl)
		for r in results:
			df = pd.DataFrame([r.__dict__], columns=cl)
			dfs = pd.concat([dfs, df])
		dfs
		
	@staticmethod
	def read_postal_code():
		path = '/Users/mnadew/Box Sync/mnadew/PRD_DB_REVIEW'
		print(os.getcwd())
		os.chdir(path)
		print(os.getcwd())
		columns = ['FSALDU', 'LATITUDE', 'LONGITUDE', 'COMMNAME', 'CSDNAMEE', 'CSDNAMEF', 'CSDTYPENE', 'PRABB']
		df = pd.read_csv('postal_code_utf8.csv')
		df = df[columns]
		print(len(df))
		i = 846000
		j = 847000
		while j < 847001:
			print('From {} to {}'.format(i, j))
			df_ins = df.iloc[i:j]
			BapQuarterly.insert(df_ins)
			print(len(df_ins))
			i, j = i + 1000, j + 1000
			print('From {} to {}'.format(i, j))

	@staticmethod
	def bap_insert(df):
		values_list = COM.df_list(df)
		db.bulk_insert(sql.sql_postal_code_insert.value, values_list)

	@staticmethod
	def main():
		while True:
			fy, fq = COM.fiscal_year_quarter()
			print('_'*100)
			print('| WELCOME TO BAP QUARTERLY ETL\n| FISCAL YEAR:     {}\n| FISCAL QUARTER:     {}'.format(fy, fq - 1))
			print('_' * 100)
			menu = '''
			1: Show Source File for BAP quarterly FY18-Q3
			1a: CHECK Columns Completeness
			2: QA spreadsheet by RIC
			3: Combine RICs BQ spreadsheet
			4: QA RICs BQ combined spreadsheet
			5: Push RICs data ro the database
			6: Generate Batch for RICs FY18 -Q3
			7: Match Company name
			8: Push Company data to DIM COMPANY and DIM COMPANY SOURCE
			9: Push quarterly company data to FACT RIC COMPANY DATA
			10: Push Annual company data to FACT RIC COMPANY DATA
			11: push Program and Program youth data to FACT RIC Aggregation
			'''
			print(menu)

			option = input('\nChoose your option:\t')
			if str(option) == '1':
				BapQuarterly.show_bap_quarterly_template()
			if str(option) == '1a':
				BapQuarterly.qa.check_columns_completeness()
			if str(option) == '2':
				BapQuarterly.qa.check_rics_file()
			if str(option) == '3':
				pass
			if str(option) == '4':
				pass
			if str(option) == '5':
				pass
			if str(option) == '6':
				pass
			if str(option) == '7':
				pass
			if str(option) == '8':
				pass
			if str(option) == '9':
				pass
			if str(option) == '10':
				pass
			if str(option) == '11':
				pass
			if str(option) == '12':
				pass

	@staticmethod
	def tech_alliance_intake_date_TEMP():
		# update = 'UPDATE BAP.QuarterlyCompanyData SET [Date of Intake] = \'{}\' WHERE [Company Name] = \'{}\' AND DataSource = 6'
		update = ' SELECT * FROM BAP.QuarterlyCompanyData WHERE [Company Name] = \'{}\' AND DataSource = 6 UNION'
		current_path = os.path.join(os.path.expanduser("~"), '/Users/mnadew/Box Sync/Workbench/BAP/BAP_FY18/FY18_Q3/for ETL/Missing data Reports')
		os.chdir(current_path)
		df = pd.read_excel('01 TechAlliance_BAP_qtrly_perCompany_MISSING DATA(2).xlsx', 'Quarterly Company data')
		# df['BasicName'] = df.apply(lambda dfs: COM.update_cb_basic_name(dfs['Company Name']), axis=1)
		i = 0
		for i, r in df.iterrows():
			if r[2] is not None or r[2]== 'nan':
				# print(r[2])
				year = r[2][-4:]
				month = r[2][3:5]
				date = r[2][:2]
				i = i + 1
				# print('{}. {} ---> {}-{}-{}'.format(i, r[2], year, month, date))
				d = '{}-{}-{}'.format(year, month, date)
				# print(update.format(d, r[0]))
				print(update.format(r[0]))

	@staticmethod
	def combine_missing_data():
		quarterly_missing = BapQuarterly.file.combine_bap_missing_source_file(
			current_path=fp.path_missing_bap_etl.value)
		quarterly_missing = quarterly_missing.where(pd.notnull(quarterly_missing), None)
		quarterly_missing['BasicName'] = quarterly_missing.apply(lambda dfs: COM.get_basic_name(dfs.CompanyName),
																 axis=1)
		df = quarterly_missing.where(pd.notnull(quarterly_missing), None)
		print(df.columns)
		dfs = df[['CompanyName', 'BasicName', 'Website', 'AnnualRevenue', 'NumberOfEmployees', 'FundingToDate',
				  'DataSource']]
		BapQuarterly.file.save_as_csv(dfs, '00 BAP Missing data Combined.xlsx', os.getcwd(), 'BAP Missing data')
		print(dfs.head())

	@staticmethod
	def push_bap_missing_data_to_temp_table():
		 current_path = os.path.join(os.path.expanduser("~"), '/Users/mnadew/Box Sync/Workbench/BAP/BAP_FY18/FY18_Q3/for ETL/Missing data Reports')
		 os.chdir(current_path)
		 df = pd.read_excel('00 BAP Missing data Combined.xlsx', 'BAP Missing data')
		 df['CompanyID'] = 0
		 new_col = ['CompanyID','CompanyName','BasicName','Website','AnnualRevenue','NumberOfEmployees','FundingToDate','DataSource']
		 dfs = df[new_col]
		 sql = 'INSERT INTO BAP.BAP_FY18Q3_Missing_Data VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
		 values = COM.df_list(dfs)
		 db.bulk_insert(sql, values)

	@staticmethod
	def bap_company_basic_name():
		db.update_basic_name(sql.sql_bap_basic_name.value,
							 'ID',
							 'CompanyName',
							 sql.sql_bap_basic_name_update.value)