def import_activities_for_job(job_id): ''' Imports the new activities for a given job ID since the last time the process was run :param job_id: Workable ID of the job to import activities for :return: ''' w = Workable(subdomain=rp.WORKABLE_SUBDOMAIN, private_key=rp.WORKABLE_PRIVATE_KEY) most_recent_activity_id = get_last_activity_id(job_id=job_id) job_activities = w.get_jobs(id=job_id, activities=True, since_id=most_recent_activity_id) session = mysql_db_sessionmaker() #if len(job_activities)>1: cs.util_output("Importing {} activities for job ID {}...".format( max(len(job_activities) - 1, 0), job_id)) for activity in job_activities: # Disqualified flag isn't captured in the "Stage Name" stage_id = None if activity['stage_name']: stage_id = r.WORKABLE_STAGE_NAMES[activity['stage_name']] elif activity['action'] == 'disqualified': stage_id = 99 stage_desc = None if activity['stage_name']: stage_desc = activity['stage_name'] elif activity['action'] == 'disqualified': stage_desc = activity['action'] row = customobjects.database_objects.TableActivities( ActivityID=activity['id'], CandidateID=activity['candidate']['id'] if 'candidate' in activity.keys() else None, JobID=job_id, StageID=stage_id, StageDesc=stage_desc, MemberID=activity['member']['id'] if 'member' in activity.keys() else None, Body=convert_string_to_unicode(string_to_convert=activity['body']) if activity['body'] else None, DisqualifiedFlag=(activity['action'] == 'disqualified'), WorkableDateTime=convert_string_to_datetime( time_as_string=activity['created_at'])) # Workable since_id method seems to include that id in call if row.ActivityID != most_recent_activity_id: session.add(row) session.commit() session.close()
def budget_create_consol_table(label): ''' Creates a single, consolidated reporting table that includes allocation data and standardised company master data mappings :param kwargs: :return: ''' try: util_output( "Creating consolidated Budget Financial Statements for {}...". format(label)) budget_import.create_consolidated_budget_data(label=label) util_output( "Creation of consolidated Budget Financial Statements complete") except (error_objects.PeriodIsLockedError, error_objects.PeriodNotFoundError, error_objects.TableEmptyForPeriodError, error_objects.MasterDataIncompleteError, error_objects.BalanceSheetImbalanceError, error_objects.CashFlowCalculationError), e: util_output("ERROR: {}".format(e.message)) util_output( "ERROR: Creation of consolidated Budget Financial Statements aborted" )
def budget_run_allocations(label, max_year=9999, max_month=13): ''' Runs the allocation process on extracted Xero data following its conversion to standardised company master data :param year: Year of the period to run allocations on (Integer) :param month: Month of the period to run allocations on (Integer) :return: ''' try: # ToDo: add check that the input dates are valid util_output( "Starting budget allocation process for {} up to period {}.{}...". format(label, max_year, max_month)) allocate_budget_data(label=label, max_year=max_year, max_month=max_month) util_output( "Budget allocation process for dataset {} is complete".format( label)) except (error_objects.PeriodIsLockedError, error_objects.PeriodNotFoundError, error_objects.TableEmptyForPeriodError, error_objects.MasterDataIncompleteError, error_objects.BalanceSheetImbalanceError, error_objects.CashFlowCalculationError), e: util_output("ERROR: {}".format(e.message)) util_output("ERROR: Creation of cost allocations aborted")
def actuals_lock_period(year, month, locked): ''' Locks/unlocks a given period in the reporting database to prevent data being overwritten :param year: Year of the actuals dataset to lock :param month: Month of the actuals dataset to lock :param locked: True/False whether the period should be locked (True) or unlocked (False) :return: ''' try: utils.data_integrity.check_period_exists(year=year, month=month) except error_objects.PeriodNotFoundError, e: util_output("ERROR: {}".format(e.message)) util_output("ERROR: Setting of period lock aborted")
def actuals_publish_period(year, month, publish): ''' Publishes/unpublishes (makes available to external reports) a given period in the reporting database to prevent data being exposed before it is considered ready by the user :param year: Year of the actuals dataset to publish :param month: Month of the actuals dataset to publish :param publish: True/False whether the period should be published (True) or unpublished (False) :return: ''' try: utils.data_integrity.check_period_exists(year=year, month=month) except error_objects.PeriodNotFoundError, e: util_output("ERROR: {}".format(e.message)) util_output("ERROR: Setting of period publish status aborted")
def budget_get_data(overwrite): ''' Imports budget data in *.csv flatfile format :return: ''' util_output("Select file for import:") file_to_import = utils.misc_functions.get_filename_from_gui() if file_to_import: try: util_output("Importing file {}...".format(file_to_import)) budget_import.import_budget_data_to_database( filepath=file_to_import, overwrite_data=overwrite) except error_objects.BudgetDataExistsError, e: util_output("ERROR: {}".format(e.message)) util_output("ERROR: Import of Budget data is aborted") else: util_output("File import successful.")
def get_workable_data(): # Get all data from the Workable database cs.util_output("Retrieving data from the Workable API...") import_data.import_all_jobs() import_data.import_all_jobs_activities() import_data.import_all_candidates() import_data.import_all_account_members() cs.util_output("Data retrival process complete.") cs.util_output("Creating consolidated table...") import_data.create_consol_table() cs.util_output("Creation of consolidated table complete.")
def import_all_jobs(): ''' Imports jobs data from Workable into the database :return: ''' cs.util_output("Getting JOBS information from Workable API...") w = Workable(subdomain=rp.WORKABLE_SUBDOMAIN, private_key=rp.WORKABLE_PRIVATE_KEY) jobs = w.get_jobs() jobs_to_import = [] # Populate database row objects for each job cs.util_output("Importing {} jobs to the database...".format(len(jobs))) for job in jobs: row = customobjects.database_objects.TableJobs( ImportDateTime=convert_string_to_datetime( time_as_string=job['created_at']), JobID=job['shortcode'], JobDescription=job['title'], JobStatus=job['state']) jobs_to_import.append(row) # Add to the database session = mysql_db_sessionmaker() session.query(TableJobs).delete() for row in jobs_to_import: session.add(row) session.commit() session.close() cs.util_output("Jobs import process complete.")
def user_confirm_action_on_period(action, dataset): ''' Prompts the user to confirm an action on a dataset (e.g. locking, publishing) :param action: Text description of the action (for use in console output) :param dataset: Text description of the dataset the action is performed on :return: True/False on whether the action should be performed ''' check_status = False # Perform additional check only if the user wants to publish a period (i.e. ok to unpublish without checking) check_input = raw_input("Please confirm you want to {} {} (Y/N):".format( action, dataset)) if check_input in ['y', 'Y']: check_status = True elif check_input in ['n', 'N']: pass else: # Inform the user that the input is incorrect and then abort the publishing process util_output( "Input '{}' not recognised. Valid inputs are 'Y' or 'N'.".format( check_input)) return check_status
def actuals_convert_data(year, month): ''' Converts imported Xero data into the standardised internal format :param year: Year to convert (Integer) :param month: Month of the year to convert (Integer) :return: ''' try: util_output("Converting Xero data for period {}.{}".format( year, month)) create_internal_financial_statements(year=year, month=month) util_output("Conversion of Xero data complete") except (error_objects.PeriodIsLockedError, error_objects.PeriodNotFoundError, error_objects.TableEmptyForPeriodError, error_objects.MasterDataIncompleteError, error_objects.BalanceSheetImbalanceError, error_objects.CashFlowCalculationError), e: util_output("ERROR: {}".format(e.message)) util_output("ERROR: Conversion of Xero data is aborted")
def actuals_create_consol_table(year, month): ''' Creates a single, consolidated reporting table that includes allocation data and standardised company master data mappings :param kwargs: :return: ''' try: util_output( "Creating consolidated Financial Statements for period {}.{}...". format(year, month)) create_consolidated_financial_statements(year=year, month=month) util_output("Creation of consolidated Financial Statements complete") except (error_objects.PeriodIsLockedError, error_objects.PeriodNotFoundError, error_objects.TableEmptyForPeriodError, error_objects.MasterDataIncompleteError, error_objects.BalanceSheetImbalanceError, error_objects.CashFlowCalculationError), e: util_output("ERROR: {}".format(e.message)) util_output( "ERROR: Creation of consolidated Financial Statements aborted")
def actuals_get_data(year, month): ''' Pulls data from the company Xero instance and imports it into the reporting database using standardised master data :param year: :param month: :return: ''' try: util_output("Retrieving Xero data for period {}.{}...".format( year, month)) pull_xero_data_to_database(year=year, month=month) util_output("Pull of Xero data for period {}.{} is complete".format( year, month)) except (error_objects.PeriodIsLockedError, error_objects.PeriodNotFoundError, error_objects.MasterDataIncompleteError, error_objects.BalanceSheetImbalanceError, error_objects.UnallocatedCostsNotNilError), e: util_output("ERROR: {}".format(e.message)) util_output("ERROR: Import of Xero data aborted")
def actuals_run_allocations(year, month): ''' Runs the allocation process on extracted Xero data following its conversion to standardised company master data :param year: Year of the period to run allocations on (Integer) :param month: Month of the period to run allocations on (Integer) :return: ''' try: util_output("Starting allocations process for period {}.{}...".format( year, month)) allocate_actuals_data(year=year, month=month) util_output("Allocation process for period {}.{} is complete".format( year, month)) except (error_objects.PeriodIsLockedError, error_objects.PeriodNotFoundError, error_objects.TableEmptyForPeriodError, error_objects.MasterDataIncompleteError, error_objects.BalanceSheetImbalanceError, error_objects.CashFlowCalculationError), e: util_output("ERROR: {}".format(e.message)) util_output("ERROR: Creation of cost allocations aborted")
def import_all_candidates(): ''' Imports all candidates from the Workable database into the local database :return: ''' cs.util_output("Getting CANDIDATE information from Workable API...") w = Workable(subdomain=rp.WORKABLE_SUBDOMAIN, private_key=rp.WORKABLE_PRIVATE_KEY) most_recent_candidate_id = get_last_candidate_id() candidates = w.get_candidates(since_id=most_recent_candidate_id) candidates_to_import = [] # Populate database row objects for each job cs.util_output("Importing {} new candidates to the database...".format( len(candidates))) for candidate in candidates: row = customobjects.database_objects.TableCandidate( CandidateID=candidate['id'], CandidateName=convert_string_to_unicode( string_to_convert=candidate['name']), WorkableDateTime=convert_string_to_datetime( time_as_string=candidate['created_at']), SourceID=candidate['domain'] if candidate['domain'] else "Sourced", WorkableUrl=candidate['profile_url']) candidates_to_import.append(row) # Add to the database session = mysql_db_sessionmaker() for row in candidates_to_import: if row.CandidateID != most_recent_candidate_id: session.add(row) session.commit() session.close() cs.util_output("Candidate import process complete.")
def output_to_csv(): ''' Outputs the consolidated income statement to a csv file to the file position specified by the user :param folder: :return: ''' # Prompt the user to select the folder to output the data to: util_output("Select the folder to output files to:") folder = utils.misc_functions.get_directoryname_from_gui() if folder: folder = utils.misc_functions.convert_dir_path_to_standard_format( folder_path=folder) if utils.data_integrity.check_directory_exists(folder): util_output("Outputting table {}...".format( database_objects.TableConsolidatedFinStatements.__tablename__)) utils.misc_functions.output_table_to_csv( table=database_objects.TableConsolidatedFinStatements, output_directory=folder) util_output("Output of table {} complete.".format( database_objects.TableConsolidatedFinStatements.__tablename__)) util_output("Outputting table {}...".format( database_objects.TableConsolidatedBudget.__tablename__)) utils.misc_functions.output_table_to_csv( table=database_objects.TableConsolidatedBudget, output_directory=folder) util_output("Output of table {} complete.".format( database_objects.TableConsolidatedBudget.__tablename__)) util_output("Table output complete.") else: util_output("ERROR: Directory {} does not exist".format(folder)) util_output("ERROR: Output of tables {} and {} aborted".format( database_objects.TableConsolidatedFinStatements.__tablename__, database_objects.TableConsolidatedBudget.__tablename__)) else: util_output("No folder selected by user. Output process aborted.")
:return: ''' try: utils.data_integrity.check_period_exists(year=year, month=month) except error_objects.PeriodNotFoundError, e: util_output("ERROR: {}".format(e.message)) util_output("ERROR: Setting of period lock aborted") else: # Execute the lock/unlocking process only if the period is valid and exists in the database if not locked in [True, False]: util_output( "ERROR: User input '{}' not recognised: options are 'True' or 'False'" .format(locked)) else: check_status = user_confirm_action_on_period( action=('LOCK' if locked else 'UNLOCK'), dataset=str(year) + "." + str(month)) if check_status: utils.misc_functions.set_period_lock_status(year=year, month=month, status=locked) util_output("Reporting period {}.{} is {}".format( year, month, ("LOCKED" if locked else "UNLOCKED"))) else: util_output("Locking process aborted for period {}.{}".format( year, month))