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