def coa_L3_nodes_in_hierarchy():
''' Checks that all L3 nodes used in the CoA are found in the node hierarchy table
:return:
'''
# Get all L3 nodes used in the CoA and in the hierarchy mapping table
session = db_sessionmaker()
L3_nodes = session.query(TableChartOfAccounts.L3Code).all()
hierarchy_nodes = session.query(TableNodeHierarchy.L3Code).all()
session.close()
# Check that each L3 node used in the CoA is also included in the hierarchy
L3_nodes = list(set(L3_nodes))
missing_nodes = []
for node in L3_nodes:
if node not in hierarchy_nodes:
missing_nodes.append(node)
if missing_nodes:
node_error_message = ""
for node in missing_nodes:
node_error_message += str(node) + "\n"
raise error_objects.MasterDataIncompleteError(
"L3 hierarchy nodes included in {} are missing from the mapping in {}:\n{}"
.format(r.TBL_MASTER_CHARTOFACCOUNTS, r.TBL_MASTER_NODEHIERARCHY,
node_error_message))
def get_all_bs_nodes_unmapped_for_cashflow():
''' Checks that the L2 nodes in the Balance Sheet are all captured by one of the three categories of nodes
(operating, investment, financing) used to calculate the cash flows of the business
:return:
'''
session = db_sessionmaker()
qry = session.query(TableFinancialStatements,
TableChartOfAccounts,
TableNodeHierarchy)\
.filter(TableFinancialStatements.AccountCode == TableChartOfAccounts.GLCode)\
.filter(TableChartOfAccounts.L3Code==TableNodeHierarchy.L3Code)\
.all()
session.close()
b2_L2_nodes = list(
set([
node.L2Code for fs, coa, node in qry
if node.L0Name == r.CM_DATA_BALANCESHEET
]))
unmapped_nodes = []
for node in b2_L2_nodes:
if node not in r.CM_BS_L2_OPERATING:
if node not in r.CM_BS_L2_INVESTMENT:
if node not in r.CM_BS_L2_FINANCING:
if node not in r.CM_BS_L2_EXCLUDED:
unmapped_nodes.append(node)
return unmapped_nodes
def upload_allocated_costs_budget(costcentres, label):
'''
:param costcentres: Cost centre objects populated with direct costs and indirect cost allocations
:return:
'''
upload_time = datetime.datetime.now() # Create timestamp
session = db_sessionmaker()
for cc in costcentres:
for cost in cc.allocated_costs:
row = TableBudgetAllocationsData(
DateAllocationsRun = upload_time,
SendingCostCentre = cost.counterparty_costcentre,
ReceivingCostCentre = cc.master_code,
SendingCompany = r.COMPANY_CODE_MAINCO, # ToDo: Refactor to make this dynamic
ReceivingCompany = r.COMPANY_CODE_MAINCO, # ToDo: Refactor to make this dynamic
Period = cost.period,
GLAccount = cost.ledger_account_code,
CostHierarchy = cost.cost_hierarchy,
Value = round(cost.amount,3), # Rounded as database field is configured as decimal
Label = label
)
session.add(row)
session.commit()
session.close()
def balance_sheet_balances_check():
''' Checks whether the Balance Sheet nets to zero in the re-mapped financial data
:return:
'''
session = db_sessionmaker()
qry = session.query(TableFinancialStatements, TableChartOfAccounts, TableNodeHierarchy)\
.filter(TableFinancialStatements.AccountCode == TableChartOfAccounts.GLCode)\
.filter(TableChartOfAccounts.L3Code==TableNodeHierarchy.L3Code)\
.filter(TableNodeHierarchy.L0Name==r.CM_DATA_BALANCESHEET).all()
session.close()
consolidated_error_message = ""
is_error = False
time_periods = list(set([fs.Period for fs, coa, node in qry]))
for time_period in time_periods:
imbalance_check = sum(
[fs.Value for fs, coa, node in qry if fs.Period == time_period])
if imbalance_check != 0:
is_error = True
consolidated_error_message += " Balance Sheet has imbalance of {} for period {}."\
.format(imbalance_check, time_period.date())
if is_error:
raise error_objects.BalanceSheetImbalanceError(
"The Balance Sheet contains the following errors:\n{}".format(
consolidated_error_message))
def output_table_to_csv(table, output_directory):
''' Outputs a database table to a *.csv file, saved to a location specified by the user
:param table: The table object to be outputed
:param output_directory: Where the user would like the *.csv file saved
:return:
'''
timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
# Create the default output directory if it doesn't already exist
output_directory = convert_dir_path_to_standard_format(
folder_path=output_directory)
file_name = output_directory + table.__name__ + "_" + timestamp + ".csv"
# Get all records from the related table
session = db_sessionmaker()
records = session.query(table).all()
session.close()
# Output the records to a csv file
output_file = open(file_name, 'wb')
writer = csv.writer(output_file)
writer.writerow(table.__table__.columns.keys())
[
writer.writerow([
getattr(curr, column.name) for column in table.__mapper__.columns
]) for curr in records
]
output_file.close()
def allocate_budget_data(label, max_year=9999, max_month=13):
''' Creates cost allocation data for budget data for a certain budget dataset
:param label: The tag given to the budget dataset
:return:
'''
utils.data_integrity.master_data_integrity_check_budget()
# Get a list of all periods in the Budget data in the format (year, month)
all_periods = get_all_budget_periods(label=label)
all_processed_costcentres = []
for year, month in all_periods:
# To prevent large volumes of unnecessary data being generated, the period over which allocations
# are run can be limited by the user
if allocation_date_check(max_year=max_year,max_month=max_month,test_year=year, test_month=month):
unprocessed_costcentres = get_populated_costcentres_budget(year=year, month=month, label=label)
processed_costcentres = allocate_indirect_cost_for_period(unprocessed_costcentres=unprocessed_costcentres)
all_processed_costcentres += processed_costcentres
else:
break
# Delete previously allocated data (for the relevant period only)
session = db_sessionmaker()
session.query(TableBudgetAllocationsData) \
.filter(TableBudgetAllocationsData.Label == label) \
.delete()
session.commit()
session.close()
# Upload new data
upload_allocated_costs_budget(costcentres=all_processed_costcentres, label=label)
def check_budget_accounts_in_coa():
''' Checks that the GL accounts used in the Budget data are found in the main chart of accounts
:return:
'''
# Get all GLs used in the Budget and in the Chart of Accounts
session = db_sessionmaker()
budget_accounts = session.query(TableFinModelExtract.GLCode).all()
coa_accounts = session.query(TableChartOfAccounts.GLCode).all()
session.close()
# Unique values only
budget_accounts = list(set(budget_accounts))
missing_accounts = []
for budget_account in budget_accounts:
if budget_account not in coa_accounts:
missing_accounts.append(budget_account)
if missing_accounts:
account_error_message = ""
for missing_account in missing_accounts:
account_error_message += str(missing_account) + "\n"
raise error_objects.MasterDataIncompleteError(
"GL Accounts included in {} are missing from the Chart of Account in {}:\n{}"
.format(r.TBL_DATA_EXTRACT_FINMODEL,
r.TBL_MASTER_CHARTOFACCOUNTS, account_error_message))
def create_headcount_rows_actuals(year, month, time_stamp=None):
''' Creates rows for the Consolidated Financial Statement table that reflects headcount for the period
:param year: Year for the period to get headcount for
:param month: Month for the period to get headcout for
:return: TableConsolidatedFinStatements row objects
'''
# ToDo: Takes the headcount on the first day of the reporting month - should be as of the last day
# Only need headcount for employees in the business during the month
start_date = datetime.datetime(year=year, month=month, day=1)
end_date = datetime.datetime(year=year, month=month, day=1) + relativedelta(months=1) + relativedelta(days=-1)
# Get all headcount at the end of the period that have started but haven't left
session = db_sessionmaker()
headcount_qry = session.query(TableHeadcount, TableCompanies, TableCostCentres)\
.filter(TableHeadcount.StartDate<=end_date)\
.filter(or_(TableHeadcount.EndDate>end_date,TableHeadcount.EndDate==None))\
.filter(TableHeadcount.CompanyCode==TableCompanies.CompanyCode)\
.filter(TableHeadcount.CostCentreCode==TableCostCentres.CostCentreCode)\
.all()
session.close()
# Want the headcount grouped by cost centre, company code,
populated_rows = []
for headcount, comp, cc in headcount_qry:
row = TableConsolidatedFinStatements(
Period=start_date,
CompanyCode=comp.CompanyCode,
CompanyName=comp.CompanyName,
PartnerCompanyCode=None,
PartnerCompanyName=None,
CostCentreCode=cc.CostCentreCode,
CostCentreName=cc.CostCentreName,
PartnerCostCentreCode=None,
PartnerCostCentreName=None,
FinancialStatement=r.CM_DATA_HEADCOUNT,
GLAccountCode=r.CM_HC_GL_CONTRACT if headcount.IsContractor else r.CM_HC_GL_PERMANENT,
GLAccountName=headcount.FirstName + " " + headcount.LastName + " (" + headcount.JobTitle + ")",
L1Code=r.CM_HC_L1,
L1Name=r.CM_HC_L1,
L2Code=r.CM_HC_L2,
L2Name=r.CM_HC_L2,
L3Code=r.HEADCOUNT_CODE_CONTRACTOR if headcount.IsContractor else r.HEADCOUNT_CODE_PERMANENT,
L3Name=r.HEADCOUNT_NAME_CONTRACTOR if headcount.IsContractor else r.HEADCOUNT_NAME_PERMANENT,
CostHierarchyNumber=None,
Value=headcount.FTE,
TimeStamp = time_stamp if time_stamp else start_date,
Label=r.OUTPUT_LABEL_ACTUALS
)
populated_rows.append(row)
return populated_rows
def delete_existing_budget_data_by_label(table, label):
''' Deletes data in the tbl_DATA_finmodelextract by tag
:param table: Database table data is to be deleted from
:param label: Tag attached to the data by the user
:return:
'''
session = db_sessionmaker()
session.query(table).filter_by(Label=label).delete()
session.commit()
session.close()
def get_unmapped_xero_account_codes():
''' Returns a summary of all Xero account codes not mapped to an internal, master Chart of Accounts code
:return:
'''
session = db_sessionmaker()
xero_data = session.query(TableXeroExtract).all()
coa_data = session.query(TableChartOfAccounts).all()
mapped_xero_codes = list(set([row.XeroCode for row in coa_data]))
unmapped_rows = [(row.AccountName, row.AccountCode) for row in xero_data
if row.AccountCode not in mapped_xero_codes]
session.close()
return unmapped_rows
def check_label_exists_in_database(label):
''' Confirms whether a tag already exists in the database
:param label:
:return:
'''
session = db_sessionmaker()
results = session.query(TableFinModelExtract).filter_by(Label=label).all()
session.close()
if results:
return True
else:
return False
def get_all_budget_periods(label):
''' Returns a list of (year, month) tuples representing all periods available in the budget data for a given label
:param label: Tag used for specfic set of Budget data
:return:
'''
session = db_sessionmaker()
all_dates = session.query(TableFinModelExtract.Period).filter(TableFinModelExtract.Label == label).all()
session.close()
# Remove all duplicates from the list of periods
output = list(set([(d[0].year, d[0].month) for d in all_dates]))
output.sort()
return output
def delete_table_data_for_period(table, year, month):
''' Deletes all data in a given table object for a specific year and month
:param table: Sqlalchemy ORM table object of table where data should be deleted from
:param year: Year of the period to delete
:param month: Month of the period to delete
:return:
'''
date_to_delete = datetime.datetime(year=year, month=month, day=1)
check_period_is_locked(year=year, month=month)
try:
session = db_sessionmaker()
session.query(table).filter(table.Period == date_to_delete).delete()
except AttributeError, e:
raise error_objects.MasterDataIncompleteError(
e.message + "\n(relevant table must have column named 'Period' "
"for the function to work)")
def check_table_has_records_for_period(year, month, table):
''' Checks whether a table contains a non-zero number of records for a given period
:param year:
:param month:
:param table:
:return:
'''
check_period_exists(year=year, month=month)
period_to_check = datetime.datetime(year=year, month=month, day=1)
session = db_sessionmaker()
result = session.query(table).filter(table.Period == period_to_check).all()
session.close()
if result == []:
raise error_objects.TableEmptyForPeriodError(
"Table {} contains no records for period {}.{}".format(
table.__tablename__, year, month))
def create_internal_profit_and_loss(year, month):
''' Creates the rows for the master-data version of the profit and loss from the imported xero data
:param year:
:param month:
:return: A list of TableFinancialStatements table row objects
'''
# Check whether the cost centre field has been populated for all line items in the xero data
utils.data_integrity.check_unassigned_costcentres_is_nil(year=year,
month=month)
rows_to_upload = []
session = db_sessionmaker()
period_to_extract = datetime.datetime(year=year, month=month, day=1)
query = session.query(TableXeroExtract, TableCompanies, TableCostCentres, TableChartOfAccounts)\
.filter(TableXeroExtract.CompanyName==TableCompanies.XeroName)\
.filter(TableXeroExtract.CostCentreCode==TableCostCentres.XeroCode)\
.filter(TableXeroExtract.AccountCode==TableChartOfAccounts.XeroCode)\
.filter(TableXeroExtract.Period==period_to_extract)\
.all()
session.close()
if len(query) == 0:
raise error_objects.TableEmptyForPeriodError(
"No data returned from table {} for period {}.{}".format(
r.TBL_DATA_EXTRACT_XERO, year, month))
else:
for xero_data, company, costcentre, account in query:
if xero_data.Value != 0:
new_row = TableFinancialStatements(
TimeStamp=None,
CompanyCode=company.CompanyCode,
CostCentreCode=costcentre.CostCentreCode,
Period=period_to_extract,
AccountCode=account.GLCode,
Value=(xero_data.Value * account.XeroMultiplier
) # Xero outputs all balances as positive
)
rows_to_upload.append(new_row)
return rows_to_upload
def confirm_table_column_is_unique(table_object, column_name):
''' Confirms whether all the entries in a table column are unique (relevant for master data mappings)
:param table_object: The sqlalchemy ORM object of the table
:param column_name: Text description of the column name
:return: True if all entries in the column name are unique
'''
session = db_sessionmaker()
qry = session.query(table_object).all()
session.close()
table_keys = table_object.__table__.columns.keys()
test_field = []
for row in qry:
a = {name: getattr(row, name) for name in table_keys}
test_field.append(a[column_name])
return len(test_field) == len(list(set(test_field)))
def check_period_is_locked(year, month):
''' Checks whether a period in the reporting database is locked for changes
:param year:
:param month:
:return: True/False depending on whether the period is locked or not
'''
check_period_exists(year=year, month=month)
session = db_sessionmaker()
date_to_check = datetime.datetime(year=year, month=month, day=1)
lock_check = session.query(TablePeriods).filter(
TablePeriods.Period == date_to_check).one()
session.close()
if lock_check.IsLocked:
raise error_objects.PeriodIsLockedError(
"Period {}.{} is LOCKED in table {}".format(
year, month, TablePeriods.__tablename__))
else:
return True
def get_direct_costs_budget_by_cc_by_node(year, month, label):
'''
:param year:
:param month:
:param label:
:return:
'''
period = datetime.datetime(year=year, month=month, day=1)
session = db_sessionmaker()
qry_costs = session.query(TableFinModelExtract, TableChartOfAccounts, TableNodeHierarchy, TableAllocationAccounts)\
.filter(TableFinModelExtract.GLCode == TableChartOfAccounts.GLCode)\
.filter(TableChartOfAccounts.L3Code == TableNodeHierarchy.L3Code)\
.filter(TableNodeHierarchy.L2Code == TableAllocationAccounts.L2Hierarchy)\
.filter(TableFinModelExtract.Period == period)\
.filter(TableFinModelExtract.Label == label)\
.all()
session.close()
assert qry_costs != [], "Query in get_direct_costs_budget_by_cc_by_node for {} returned no results for period {}.{}"\
.format(label,year, month)
list_of_costcentres = list(set([fin.CostCentreCode for fin, coa, node, alloc in qry_costs]))
list_of_costcategories = list(set([(node.L2Code, alloc.GLCode) for fin, coa, node, alloc in qry_costs]))
# Create cost objects for each cost centre for each hierarchy node
output_dict = {}
for cc in list_of_costcentres: # e.g. C000001, C000002, etc
list_of_costs = []
for costcategory in list_of_costcategories: # e.g. L2-FIN, L2-STAFF, etc
cost = Cost()
cost.period = period
cost.master_code = costcategory[0]
cost.allocation_account_code = costcategory[1]
cost.amount = sum([pnl.Value for pnl, coa, node, alloc in qry_costs if pnl.CostCentreCode == cc and node.L2Code == costcategory[0]])
if abs(cost.amount) > r.DEFAULT_MAX_CALC_ERROR: # Filter out near-zero costs to reduce number of records up-stream
list_of_costs.append(cost)
output_dict[cc]=list_of_costs
return output_dict
def set_period_published_status(year, month, status):
''' Sets whether a period is locked or unlocked
:param year:
:param month:
:param status:
:return:
'''
assert status in [
True, False
], "User input is invalid - only TRUE or FALSE are permitted inputs"
period_to_update = datetime.datetime(year=year, month=month, day=1)
check_period_exists(year=year, month=month)
session = db_sessionmaker()
# If it exists, set the lock status
set_lock = session.query(TablePeriods).filter(TablePeriods.Period == period_to_update)\
.update({r.COL_PERIOD_ISPUBLISHED:status})
session.commit()
session.close()
def get_direct_costs_actuals_by_cc_by_node(year, month):
''' Get the direct costs (actuals) split by cost centre and L2 hierarchy level for a given period
:param year:
:param month:
:return:
'''
session = db_sessionmaker()
period = datetime.datetime(year=year, month=month, day=1)
qry_costs = session.query(TableFinancialStatements, TableChartOfAccounts, TableNodeHierarchy, TableAllocationAccounts)\
.filter(TableFinancialStatements.AccountCode == TableChartOfAccounts.GLCode)\
.filter(TableChartOfAccounts.L3Code == TableNodeHierarchy.L3Code)\
.filter(TableNodeHierarchy.L2Code == TableAllocationAccounts.L2Hierarchy)\
.filter(TableFinancialStatements.Period == period)\
.all()
session.close()
assert qry_costs != [], "Query in get_direct_costs_actuals_by_cc_by_node returned no results for period {}.{}".format(year, month)
output_dict = {}
# Get all cost centres in the extracted data
list_of_costcentres = list(set([pnl.CostCentreCode for pnl, coa, node, alloc in qry_costs]))
# Get all cost categories in the extracted data
list_of_costcategories = list(set([(node.L2Code, alloc.GLCode) for pnl, coa, node, alloc in qry_costs]))
# Create cost objects for each cost centre for each hierarchy node
output_dict = {}
for cc in list_of_costcentres: # e.g. C000001, C000002, etc
list_of_costs = []
for costcategory in list_of_costcategories: # e.g. L2-FIN, L2-STAFF, etc
cost = Cost()
cost.period = period
cost.master_code = costcategory[0]
cost.allocation_account_code = costcategory[1]
cost.amount = sum([pnl.Value for pnl, coa, node, alloc in qry_costs if pnl.CostCentreCode == cc and node.L2Code == costcategory[0]])
if abs(cost.amount) > r.DEFAULT_MAX_CALC_ERROR: # Filter out near-zero costs to reduce number of records up-stream
list_of_costs.append(cost)
output_dict[cc]=list_of_costs
return output_dict
def create_internal_balance_sheet(year, month):
''' Creates the master-data version of the balance sheet from the imported xero data
:param year:
:param month:
:return:
'''
rows_to_upload = []
session = db_sessionmaker()
period_to_extract = datetime.datetime(year=year, month=month, day=1)
query = session.query(TableXeroExtract, TableCompanies, TableChartOfAccounts)\
.filter(TableXeroExtract.ReportName==r.XERO_DATA_BALANCESHEET)\
.filter(TableXeroExtract.CompanyName==TableCompanies.XeroName)\
.filter(TableXeroExtract.AccountCode==TableChartOfAccounts.XeroCode)\
.filter(TableXeroExtract.Period==period_to_extract)\
.all()
session.close()
if len(query) == 0:
raise error_objects.TableEmptyForPeriodError(
"No data returned from table {} for period {}.{}".format(
r.TBL_DATA_EXTRACT_XERO, year, month))
else:
for xero_data, company, account in query:
if xero_data.Value != 0:
new_row = TableFinancialStatements(
TimeStamp=None,
CompanyCode=company.CompanyCode,
CostCentreCode=None,
Period=period_to_extract,
AccountCode=account.GLCode,
Value=(xero_data.Value * account.XeroMultiplier
) # Xero outputs all balances as positive
)
rows_to_upload.append(new_row)
return rows_to_upload
def import_budget_data_to_database(filepath, overwrite_data=False):
''' Imports a *.csv flatfile of Budget data into the database
:param filepath: Absolute filepath of the *.csv file to be updated
:param overwrite_data: User-specified permission to overwrite old data
:return:
'''
# ToDo: Check if period is locked
# Get import flatfile
file_as_lists = utils.misc_functions.open_csv_file_as_list(filepath)
# Confirm whether old data exists and if so, if it should be overwritten
import_tag = file_as_lists[0][7]
if check_label_exists_in_database(label=import_tag):
if overwrite_data:
delete_existing_budget_data_by_label(table=TableFinModelExtract,
label=import_tag)
else:
raise error_objects.BudgetDataExistsError(
"Data already exists for tag '{}', import process aborted.\n "
"Re-run process selecting --overwrite=True to overwrite data.".
format(import_tag))
# Import the data to the database
session = db_sessionmaker()
for row in file_as_lists:
new_row = TableFinModelExtract(
TimeStamp=convert_string_to_datetime(row[1]),
Period=convert_string_to_datetime(row[2]),
CompanyCode=row[3],
CostCentreCode=row[4],
GLCode=row[5],
Value=row[6],
Label=row[7],
Comments=row[8])
session.add(new_row)
session.commit()
session.close()
def check_unassigned_costcentres_is_nil(year, month):
''' Checks that any L1 nodes that must reallocate its costs has no costs that have no cost centre allocated
:param year: Year of the period to check
:param month: Month of the period to check
:return:
'''
session = db_sessionmaker()
date_to_check = datetime.datetime(year=year, month=month, day=1)
total_unassigned = session.query(TableXeroExtract, TableChartOfAccounts, TableNodeHierarchy, TableAllocationAccounts)\
.filter(TableXeroExtract.AccountCode==TableChartOfAccounts.XeroCode)\
.filter(TableChartOfAccounts.L3Code==TableNodeHierarchy.L3Code)\
.filter(TableNodeHierarchy.L2Code == TableAllocationAccounts.L2Hierarchy)\
.filter(TableXeroExtract.CostCentreName == rp.XERO_UNASSIGNED_CC) \
.filter(TableXeroExtract.Period==date_to_check)\
.all()
session.close()
L1_nodes = list(
set([node.L1Code for xero, coa, node, alloc_ac in total_unassigned]))
is_error = False
consolidated_error_message = ""
# Each L1 node should net to zero so that no unassigned costs are allocated to receiver cost centres
for L1_node in L1_nodes:
total_unallocated = sum([
xero.Value for xero, coa, node, alloc_ac in total_unassigned
if node.L1Code == L1_node
])
if abs(total_unallocated) > r.DEFAULT_MAX_CALC_ERROR:
is_error = True
consolidated_error_message += "Costs in cost centre '{}' for L1 node {} are not net flat for period {}.{} (total = {})\n"\
.format(rp.XERO_UNASSIGNED_CC, L1_node, year, month, total_unallocated)
if is_error:
raise error_objects.UnallocatedCostsNotNilError(
"The Xero data contains the following unassigned balances:\n{}".
format(consolidated_error_message))
def get_all_budget_employees_from_database(year, month, label):
''' Returns a list of Employee objects for a given period and a given Budget dataset
:param year:
:param month:
:param label:
:return:
'''
period_to_retrieve = datetime.datetime(year=year, month=month, day=1)
# Get the data from the database
session = db_sessionmaker()
# ToDo: Refactor to capture GLs for perm and contract
qry_headcount = session.query(TableFinModelExtract)\
.filter(TableFinModelExtract.Label == label)\
.filter(TableFinModelExtract.Period == period_to_retrieve)\
.filter(or_(TableFinModelExtract.GLCode == r.CM_HC_GL_CONTRACT,TableFinModelExtract.GLCode == r.CM_HC_GL_PERMANENT))\
.all()
session.close()
list_of_employees = []
# Create a list of Employee objects that will be used for allocations
for row in qry_headcount:
emp = Employee()
emp.id = None
emp.first_name = None
emp.last_name = None
emp.fte = row.Value
emp.job_title = None
emp.start_date = None
emp.end_date = None
emp.cost_centre = row.CostCentreCode
emp.company_code = row.CompanyCode
list_of_employees.append(emp)
return list_of_employees
def create_internal_financial_statements(year, month):
''' Creates an Income Statement, Balance Sheet and Cash Flow Statement, mapped to the Clearmatics internal
master data mapping.
:param year:
:param month:
:return:
'''
# Perform validations on the data before proceeding
utils.data_integrity.master_data_integrity_check_actuals(year=year,
month=month)
utils.data_integrity.check_table_has_records_for_period(
year=year, month=month, table=TableXeroExtract)
# Create the P&L
pnl_rows = create_internal_profit_and_loss(year=year, month=month)
# Create the Balance Sheet
bs_rows = create_internal_balance_sheet(year=year, month=month)
# Create the Cash Flow Statement
cf_rows = create_internal_cashflow_statement(year=year, month=month)
# Create the Cash Flow Statement using P&L and Balance Sheet
rows_to_upload = pnl_rows + bs_rows + cf_rows
utils.misc_functions.delete_table_data_for_period(
table=TableFinancialStatements, year=year, month=month)
try:
session = db_sessionmaker()
timestamp = datetime.datetime.now()
for row in rows_to_upload:
row.TimeStamp = timestamp
session.add(row)
except Exception, e:
raise
def get_all_cost_centres_from_database():
''' Returns a list of CostCentre objects populated with master data information
:param year:
:param month:
:return:
'''
session = db_sessionmaker()
qry_costcentres = session.query(TableCostCentres).all()
session.close()
list_of_costcentres = []
for row in qry_costcentres:
cc = CostCentre()
cc.master_name = row.CostCentreName
cc.master_code = row.CostCentreCode
cc.hierarchy_tier = row.AllocationTier
cc.employees = []
list_of_costcentres.append(cc)
return list_of_costcentres
def get_all_actuals_employees_from_database(year, month):
''' Returns a list of Employee objects based on the Actuals headcount for a given period
:param year:
:param month:
:return:
'''
session = db_sessionmaker()
# Period takes the headcount as of the last day of the month
period = utils.misc_functions.get_datetime_of_last_day_of_month(year=year, month=month)
qry_headcount = session.query(TableHeadcount)\
.filter(TableHeadcount.StartDate <= period)\
.filter(or_(TableHeadcount.EndDate >= period, TableHeadcount.EndDate == None))\
.all()
session.close()
list_of_employees = []
for row in qry_headcount:
emp = Employee()
emp.id = row.EmployeeID
emp.first_name = row.FirstName
emp.last_name = row.LastName
emp.job_title = row.JobTitle
emp.start_date = row.StartDate
emp.end_date = row.EndDate
emp.cost_centre = row.CostCentreCode
emp.company_code = row.CompanyCode
emp.fte = row.FTE
list_of_employees.append(emp)
return list_of_employees
def check_period_exists(year, month):
''' Checks that the period is included in the database
:param year:
:param month:
:return:
'''
# Check that the inputs are basically correct (i.e. year in sensible range, month between 1 and 12)
if (year in r.AVAILABLE_PERIODS_YEARS):
if (month in r.AVAILABLE_PERIODS_MONTHS):
pass
else:
raise error_objects.PeriodNotFoundError(
"Month {} is not included in range of valid inputs: {}".format(
month, r.AVAILABLE_PERIODS_MONTHS))
else:
raise error_objects.PeriodNotFoundError(
"Year {} is not included in range of valid inputs: {}".format(
year, r.AVAILABLE_PERIODS_YEARS))
# Check that the period exists in the database Periods table
try:
session = db_sessionmaker()
period_to_update = datetime.datetime(year=year, month=month, day=1)
period_check = session.query(TablePeriods) \
.filter(TablePeriods.Period == period_to_update) \
.one()
except sqlalchemy.orm.exc.NoResultFound:
raise error_objects.PeriodNotFoundError(
"Period {}.{} does not exist in table {}".format(
year, month, TablePeriods.__tablename__))
else:
return True
finally:
session.close()
def get_actuals_status_table():
''' Creates a console window table showing the import status of the various periods of actuals available
:return:
'''
# ToDo: Add column with time-stamp of last operation
# ToDo: Include "recalculate" requirement for instance where the TimeStampCheck shows an error
# ToDo: Add col with NPAT total for the period (sense-check)
session = db_sessionmaker()
# Get all periods in the database up to today's date (all data is historic)
period_qry = session.query(TablePeriods)\
.order_by(asc(TablePeriods.Period))\
.filter(TablePeriods.Period >= r.MODEL_START_DATE)\
.filter(TablePeriods.Period <= datetime.datetime.now())\
.all()
# Extract the count of each row to determine whether the data exists and get the timestamp data to assert that
# the data in the latter stages is up-to-date and that more recent data hasn't been created up-stream
# Count the number of xero rows for each date
xero_qry = session.query(TableXeroExtract.Period,
func.count(TableXeroExtract.ID).label('count'),
func.max(TableXeroExtract.DateExtracted).label('timestamp'))\
.group_by(TableXeroExtract.Period)\
.all()
# Count the number of rows of data converted to internal mappings
pnl_qry = session.query(TableFinancialStatements.Period,
func.count(TableFinancialStatements.ID).label('count'),
func.max(TableFinancialStatements.TimeStamp).label('timestamp'))\
.group_by(TableFinancialStatements.Period)\
.all()
# Count the number of rows of data of allocated indirect costs
alloc_qry = session.query(TableAllocationsData.Period,
func.count(TableAllocationsData.ID).label('count'),
func.max(TableAllocationsData.DateAllocationsRun).label('timestamp'))\
.group_by(TableAllocationsData.Period)\
.all()
# Count the number of rows of data transformed into the consolidated income statement
consol_qry = session.query(TableConsolidatedFinStatements.Period,
func.count(TableConsolidatedFinStatements.ID).label('count'),
func.max(TableConsolidatedFinStatements.TimeStamp).label('timestamp'))\
.group_by(TableConsolidatedFinStatements.Period)\
.all()
session.close()
# Convert to .date() as the data is extracted from the period_qry as datetime.date() objects
xero_dates = [row.Period.date() for row in xero_qry if row.count != 0]
pnl_dates = [row.Period.date() for row in pnl_qry if row.count != 0]
alloc_dates = [row.Period.date() for row in alloc_qry if row.count != 0]
consol_dates = [row.Period.date() for row in consol_qry if row.count != 0]
table_headers = [
"Period", "IsLocked", "IsPublished", "1) ActualsData", "2) Converted",
"3) Allocations", "4) Consol", "TimestampCheck"
]
# table_rows are lists of values that correspond to the headers in the list above
table_rows = []
for qry_row in period_qry:
# Get the timestamps of each process to check the order has been run correctly
# Results are returned as lists to capture the instance where no results are returned (returns [])
xero_timestamp = list(
set([
row.timestamp for row in xero_qry
if row.Period.date() == qry_row.Period
]))
pnl_timestamp = list(
set([
row.timestamp for row in pnl_qry
if row.Period.date() == qry_row.Period
]))
alloc_timestamp = list(
set([
row.timestamp for row in alloc_qry
if row.Period.date() == qry_row.Period
]))
consol_timestamp = list(
set([
row.timestamp for row in consol_qry
if row.Period.date() == qry_row.Period
]))
# Check that the time-stamps of each step are in the correct order
step_number_complete = run_actuals_from_step_number(
xero_date=xero_timestamp,
pnl_date=pnl_timestamp,
alloc_date=alloc_timestamp,
consol_date=consol_timestamp)
table_row = [
qry_row.Period, ('True' if qry_row.IsLocked else 'False'),
('True' if qry_row.IsPublished else 'False'),
('Imported' if qry_row.Period in xero_dates else 'Not Imported'),
('Imported' if qry_row.Period in pnl_dates else 'Not Imported'),
('Calculated'
if qry_row.Period in alloc_dates else 'Not Calculated'),
('Calculated'
if qry_row.Period in consol_dates else 'Not Calculated'),
('Pass' if step_number_complete == 5 else
'Run From Step {}'.format(step_number_complete))
]
table_rows.append(table_row)
# Output the table to the console
return tabulate(tabular_data=table_rows,
headers=table_headers,
numalign="right") + "\n"
def get_budget_status_table():
''' Returns a console table showing the status of imported Budget data
:return:
'''
# 1) Create a list of all labels available in the import table
session = db_sessionmaker()
labels_qry = session.query(TableFinModelExtract.Label, TableFinModelExtract.TimeStamp, TableFinModelExtract.Comments)\
.order_by(asc(TableFinModelExtract.TimeStamp))\
.all()
alloc_qry = session.query(TableBudgetAllocationsData.Label, TableBudgetAllocationsData.DateAllocationsRun)\
.all()
consol_qry = session.query(TableConsolidatedBudget.Label,
TableConsolidatedBudget.TimeStamp).all()
session.close()
# 2) Remove duplicates
labels_qry = list(set(labels_qry))
alloc_qry = list(set(alloc_qry))
consol_qry = list(set(consol_qry))
labels_allocated = [label[0] for label in alloc_qry]
labels_consol = [label[0] for label in consol_qry]
# ToDo: Include "IsPublished flag"
table_headers = [
"Label", "IsLocked", "1) IsAllocated", "2) Consol", "TimestampCheck",
"Comment"
]
table_rows = []
# 3) Create a table row for each label imported
for label in labels_qry:
time_stamp_check = 0
time_allocated = [
alloc[1] for alloc in alloc_qry if alloc[0] == label[0]
]
time_consol = [
consol[1] for consol in consol_qry if consol[0] == label[0]
]
# Determine the whether the allocations and consolidation have been performed in the correct order
if time_allocated:
if time_consol:
if time_allocated <= time_consol:
time_stamp_check = 2
else:
time_stamp_check = 1
else:
time_stamp_check = 1
else:
time_stamp_check = 0
# Create the table row
table_row = [
label[0], "N/A",
('Calculated'
if label[0] in labels_allocated else 'Not Calculated'),
('Calculated' if label[0] in labels_consol else 'Not Calculated'),
'Pass' if time_stamp_check == 2 else
'Run From Step {}'.format(time_stamp_check + 1), label[2]
]
table_rows.append(table_row)
return tabulate(tabular_data=table_rows,
headers=table_headers,
numalign="right") + "\n"
