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'])
def check_rics_file(self, loc, dest, combined=False):
path = Common.change_working_directory(loc.value)
fl = FileService(path)
self.ric_files = fl.get_source_file()
if combined:
self.ric_files = [
f for f in self.ric_files if 'ETL_RICS_BAP_COMBINED' in f
]
print('-' * 100, '\nPROCESSING RIC FILES\n')
for fl in self.ric_files:
print(fl)
try:
Common.change_working_directory(loc.value)
wb = openpyxl.load_workbook(fl, data_only=True)
print('\tProgram data')
program_sheet = wb.get_sheet_by_name(WS.bap_program.value)
self.qa_program_sheet(program_sheet)
print('\tProgram Youth data')
program_youth_sheet = wb.get_sheet_by_name(
WS.bap_program_youth.value)
self.qa_program_youth_sheet(program_youth_sheet)
print('\tQuarterly Company data')
quarterly_company_sheet = wb.get_sheet_by_name(
WS.bap_company.value)
self.rics_sheet_header(quarterly_company_sheet)
self.qa_quarterly_company_data_sheet(quarterly_company_sheet)
print('\tAnnual Company data')
if self.quarter == 'Q3' and ('haltec' in fl.lower()
or 'communitech' in fl.lower()):
annual_company_sheet = wb.get_sheet_by_name(
WS.bap_company_annual.value)
self.qa_annual_company_data_sheet(annual_company_sheet)
Common.change_working_directory(dest.value)
wb.save('{}_Q1_QA.xlsx'.format(fl[:-5]))
print('\t\t[{}_Q1_QA] IS SAVED.'.format(fl[:-5]))
except BaseException as ex:
print(ex)
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.')
def _main_():
''' PELASE INSTALL CERTIFICATE AND REMOVE THIS, WHERE EVER THE CERTIFICATE IS '''
# urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
"""Menu-selected actions for SGizmo API
"""
menu_actions.write_survey_entries(API_TOKEN)
menu = menu_actions.construct_menu()
survey_id = 'w'
selection = 0
campaign_id = 'w'
table_dict = 'w'
surveys_df = None
session_variables = []
print("\nYou must enter a survey ID for this session.")
# sleep(1)
print("Downloading list of surveys now.")
# sleep(1)
surveys_df = menu_actions.get_surveys(API_TOKEN, with_stats=True)
if surveys_df is not None:
session_variables.append(1)
# while type(survey_id) != int:
# try:
survey_id = input("Enter a survey ID from above to use for this session: ")
survey_id = menu_actions.validate_survey_id(survey_id,
session_variables,
API_TOKEN,
surveys_df,
mandatory=True)
# except ValueError:
# continue
menu_title = "\nMenu\n Quit: 99\n Back to main menu: -1\n============================================"
# write all surveys from SG to DB, including BatchID
while selection == 0:
# main menu
print(menu_title)
# get survey title from survey_df
survey_title = surveys_df.loc[surveys_df["id"] == str(survey_id),
["title"]]
try:
survey_title = survey_title.values[0][0]
except IndexError:
survey_title = None
for key in menu:
if survey_id != 'w' and key == 17:
# specify current session survey
print(
str(key) + ".\t" + str(menu[key]) + " (Set as " +
str(survey_id) + " : " + str(survey_title) + ")")
elif key in session_variables:
# strike through if action has been completed this session
print('\u0336'.join(str(key) + ". " + menu[key]) + '\u0336' +
" DONE")
else:
print(str(key) + ".\t" + menu[key])
while type(selection) != int or selection not in range(
1,
len(menu) + 1):
try:
selection = int(input("\nEnter a valid option number: "))
if selection == 99:
print("You entered 99.\nI can't believe you've done this.")
print('Farewell...')
break
except ValueError:
continue
# get surveys
if selection == 1:
if 1 not in session_variables:
surveys_df = menu_actions.get_surveys(API_TOKEN)
if surveys_df is not None:
session_variables.append(1)
elif 1 in session_variables:
print(surveys_df)
print("Surveys already downloaded from SGizmo API.")
sleep(0.75)
print("Returning to main menu.")
sleep(1)
# get campaigns
elif selection == 3:
campaigns_df = menu_actions.get_campaigns(API_TOKEN, survey_id,
session_variables,
surveys_df)
session_variables.append(3)
# get email msgs
elif selection == 5:
if 3 not in session_variables:
print("You must download campaign data first.")
get_campaigns = input("Get campaign data now? (y/n): ")
if get_campaigns.lower() == "y":
campaigns_df = menu_actions.get_campaigns(
API_TOKEN, survey_id, session_variables, surveys_df)
if campaigns_df is not None:
session_variables.append(5)
else:
print("Returning to main menu.")
sleep(1)
elif 2 in session_variables:
emails_df = menu_actions.get_emails(survey_id, API_TOKEN,
session_variables,
surveys_df)
if emails_df is not None:
session_variables.append(5)
# get contact lists
elif selection == 9:
if 9 not in session_variables:
contact_list_df = menu_actions.get_contact_lists(
survey_id, API_TOKEN)
# contact_list_df = sg_contact_lists.sg_contactlists_df(API_TOKEN)
print(contact_list_df)
if contact_list_df is not None:
session_variables.append(9)
elif 9 in session_variables:
print(contact_list_df)
print(
"Contact lists already downloaded. Returning to main menu."
)
sleep(1.5)
# get contacts on list
elif selection == 10:
list_id = 'w'
while type(list_id) != int:
try:
list_id = int(
input(
"Enter ID of contact list that you would like to retrieve: "
))
except ValueError:
continue
if list_id != -1:
contacts_df = menu_actions.get_contacts(API_TOKEN, list_id)
# get respondents
elif selection == 11:
if 11 not in session_variables:
resps_df = menu_actions.get_resps(survey_id, API_TOKEN)
if resps_df is not None:
session_variables.append(11)
elif 11 in session_variables:
print("Already downloaded responses. Returning to main menu")
sleep(1.5)
# get questions or options
elif selection in [12, 13]:
if 12 not in session_variables and 13 not in session_variables:
qs_df, os_df = menu_actions.get_qsos(survey_id, API_TOKEN)
if qs_df is not None:
session_variables.append(12)
if os_df is not None:
session_variables.append(13)
if selection == 12 or (selection == 12
and 12 in session_variables):
print(qs_df)
elif selection == 13 or (selection == 13
and 13 in session_variables):
print(os_df)
# get answers
elif selection == 14:
if 14 not in session_variables:
answers_df, resps_df = menu_actions.get_ans(
survey_id, API_TOKEN)
if answers_df is not None:
session_variables.append(14)
elif 14 in session_variables:
print("Already downloaded answers. Returning to main menu.")
sleep(1.5)
# get response statuses
elif selection == 6:
try:
reports_df, status_df = menu_actions.get_resp_stats(
survey_id, API_TOKEN)
except TypeError:
selection = 0
if "JLAB" in survey_title:
path = "/Users/gcree/Box Sync/MaRS DataCatalyst 2017 CONFIDENTIAL/JLABS Toronto Annual Survey 2017/Response_Status_Reports/"
misc.write_to_xl(status_df.drop("invite_link", axis=1),
"ResponseStatuses",
out_path=path,
sheetname="response_statuses")
elif "annual" in survey_title.lower() and "2018" in survey_title:
path = CM.get_config("config.ini", "paths",
"survey2018_response_stats")
misc.write_to_xl(status_df,
'ResponseStatuses' + survey_title,
out_path=path,
sheetname="response_statuses")
# get resp stats for all campaigns
elif selection == 19:
campaigns_df = menu_actions.get_campaigns(API_TOKEN, survey_id,
session_variables,
surveys_df)
campaigns_df = campaigns_df[[
'id', 'campaign_name', 'link_type', 'campaign_status'
]]
reports_list = []
status_list = []
campaigns_df = campaigns_df[
campaigns_df['campaign_status'] != 'Deleted']
campaigns_df = campaigns_df[campaigns_df['link_type'] != 'link']
for cid in campaigns_df["id"]:
print('Downloading data for campaign id: {}'.format(cid))
reports_df, status_df = menu_actions.get_resp_stats(
survey_id, API_TOKEN, campaign_id=int(cid))
if len(reports_df) > 0:
reports_list.append(reports_df)
status_list.append(status_df)
# concat all reports dfs and concat all status dfs
if len(reports_list) == 0:
pass
elif len(reports_list) == 1:
reports_df = reports_list[0]
status_df = status_list[0]
else:
reports_df = pd.concat(reports_list)
status_df = pd.concat(status_list)
# left join campaigns <- reports <- statuses dfs
df1 = pd.merge(campaigns_df,
reports_df,
how='inner',
left_on=["id"],
right_on=["campaign_id"])
all_resp_stats = pd.merge(df1,
status_df,
how='left',
left_on='id_y',
right_on='report_id')
all_resp_stats = all_resp_stats.drop('id_y', axis=1).drop(
'campaign_id',
axis=1).drop('report_id',
axis=1).drop('primary_RIC',
axis=1).drop('venture_id', axis=1)
all_resp_stats = all_resp_stats.rename(
columns={'id_x': "campaign_id"})
path_ini = CM.get_config("config.ini", "paths", "sandbox")
path = CM.change_working_directory(path_ini)
print(path)
misc.write_to_xl(all_resp_stats,
'ResponseStatuses - {}'.format(survey_title),
out_path=path,
sheetname="response_statuses")
# stat_table = 'MDCReport.Fact_Response_Status'
# print('Truncating and writing to ' + stat_table)
# trunc_sql = CM.get_config('config.ini', 'sql_queries', 'trunc_stat_rep')
# DB.execute(trunc_sql)
# ins_sql = CM.get_config('config.ini', 'sql_queries', 'insert_stat_rep_f')
# ins_sql = ins_sql.replace('(WHAT_HEADERS) ', '')
# values = CM.df_list(all_resp_stats)
# val_num = len(values)
# for i in range(val_num):
# try:
# val = []
# for l, j in enumerate(values[i]):
# if isinstance(values[i][l], list):
# val.append(''.join(str(x) for x in values[i][l]))
# elif isinstance(values[i][l], str):
# val.append(CM.sql_compliant(values[i][l]))
# else:
# val.append(values[i][l])
# tup = tuple(val)
# ins_sql_final = ins_sql.format(tup)
# ins_sql_final = ins_sql_final.replace('"', '\'')
# DB.execute(ins_sql_final)
# print("Record {} of {} : SUCCESS".format(i, val_num))
# except Exception as e:
# print("Record number {} of {} : ERROR: {}".format(i, val_num, e))
# print("ERROR VALUES : {}".format(ins_sql))
# continue
# set survey ID
elif selection == 17:
# survey_id_choice = 1
r_u_sure = 0
# if type(survey_id) == int:
while str(r_u_sure).lower() not in ['n', 'y']:
try:
r_u_sure = input("""
Warning: changing the surveyID for this session will
clear the data downloaded for the previous survey during this session.
Do you still want to change the survey ID? (y/n): """)
if str(r_u_sure).lower() == 'y':
session_variables[:] = [
y for y in session_variables if y in [1, 15]
]
survey_id_choice = input(
"Survey ID has been reset. Enter new ID: ")
survey_id_choice = menu_actions.validate_survey_id(
survey_id_choice, session_variables, API_TOKEN,
surveys_df)
if survey_id_choice is not None:
survey_id = survey_id_choice
elif str(r_u_sure).lower() in ['n', str(-1)]:
print('Returning to main menu')
sleep(0.75)
selection = 0
break
except ValueError:
continue
# get all tables from schema into dfs
elif selection == 15:
# schema = "JLABS"
# schema = str(input("Enter name of schema for which you would like to load all tables into dataframes: "))
#
# table_dict = menu_actions.get_db_tables(schema, printout=True)
#
# session_variables.append(15)
#
# # ========= Dependency query and dict ==========
#
# dependency_dict = menu_actions.get_dependencies(schema, printout=True)
#
# load_ordered_tables = menu_actions.get_load_order(schema, printout=True)
pass
# test get dependencies
elif selection == 16:
schema = str(
input(
"Enter name of schema you would like to get dependencies for: "
))
skipped = False
if menu_actions.return_to_main(schema) == 1:
print("skipped")
skipped = True
sleep(0.5)
if not skipped:
dependencies = sg_get_tables.get_dependencies(schema)
print("\n", dependencies)
dependency_dict = {}
for i in range(0, len(dependencies)):
fkt = dependencies.iloc[:, 0][i]
reft = dependencies.iloc[:, 1][i]
if fkt not in dependency_dict.keys():
dependency_dict[fkt] = []
dependency_dict[fkt].append(reft)
print("\nDependency dict: \n")
for key in dependency_dict.keys():
print(key, ":", dependency_dict[key])
load_order = sg_get_tables.get_load_order(schema)
print("\nLOAD ORDER:\n", load_order)
# load survey into DB
elif selection == 2:
# if 12 not in session_variables:
# print("Pull in tables from DB before loading survey data into DB")
# print("Execute menu item 12.")
# sleep(1)
#
# elif 12 in session_variables:
# # check if surveyID selected is in survey DB table
# surveys_table = table_dict["Surveys"]
# survey_ids = []
# for id in surveys_table["id"]:
# survey_ids.append(id)
# if survey_id in survey_ids:
# print("Survey already exists in database")
# elif survey_id not in survey_ids:
# print("Survey does not exist in DB. Loading survey data now")
print("\nLoading survey entry into DB")
menu_actions.load_survey_entry(surveys_df, survey_id)
print("\nLoading survey questions & options into DB")
menu_actions.load_qsos(survey_id, API_TOKEN)
session_variables.append(2)
# load responses, answers, contacts, contact lists, and contacts__lists entries
elif selection == 4:
exist = menu_actions.check_qs_exist(survey_id)
if exist:
print(
"At least one question for this survey exists in DB. Proceeding to load answers into DB"
)
print("Loading Responses first...")
menu_actions.load_resps_ans_contacts__lists(
survey_id, API_TOKEN)
else:
print(
"No questions for this survey exist in DB. Load questions before loading answers."
)
# write all current survey to DB
elif selection == 7:
menu_actions.write_all_survey_components_to_db(
session_variables, surveys_df, survey_id, API_TOKEN)
session_variables.append(7)
# write all components of all surveys to DB
elif selection == 8:
menu_actions.do_everything_for_all_surveys(session_variables,
surveys_df, API_TOKEN)
session_variables.append(8)
elif selection == 18:
sure = input(
"\nAre you sure you wish to delete all components of current survey from the database? (y/n): "
)
if str(sure).lower() == "y":
menu_actions.del_survey_components(survey_id)
# quit program
elif selection == 99:
break
selection = 0