Example #1
0
	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'])
Example #2
0
    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)
Example #3
0
	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.')
Example #4
0
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