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 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 master_data_uniquesness_check():
''' Performs integrity checks on the master data in the table and raises an MasterDataIncompleteError if any
duplicate values are detected in fields where each record should be unique.
:return:
'''
consolidated_error_message = ""
is_error = False
if not confirm_table_column_is_unique(TableChartOfAccounts,
r.COL_CHARTACC_GLCODE):
is_error = True
consolidated_error_message += "\n Table {} has duplicate values in column {}"\
.format(TableChartOfAccounts.__tablename__, r.COL_CHARTACC_GLCODE)
if not confirm_table_column_is_unique(TableAllocationAccounts,
r.COL_ALLOCACC_CODE):
is_error = True
consolidated_error_message += "\n Table {} has duplicate values in column {}"\
.format(TableAllocationAccounts.__tablename__, r.COL_ALLOCACC_CODE)
if not confirm_table_column_is_unique(TableCostCentres, r.COL_CC_CODE):
is_error = True
consolidated_error_message += "\n Table {} has duplicate values in column {}"\
.format(TableCostCentres.__tablename__, r.COL_CC_CODE)
if not confirm_table_column_is_unique(TableCompanies,
r.COL_COMPANIES_COMPCODE):
is_error = True
consolidated_error_message += "\n Table {} has duplicate values in column {}"\
.format(TableCompanies.__tablename__, r.COL_COMPANIES_COMPCODE)
if not confirm_table_column_is_unique(TableNodeHierarchy,
r.COL_NODE_L3CODE):
is_error = True
consolidated_error_message += "\n Table {} has duplicate values in column {}"\
.format(TableNodeHierarchy.__tablename__, r.COL_NODE_L3CODE)
if not confirm_table_column_is_unique(TablePeriods, r.COL_PERIOD_PERIOD):
is_error = True
consolidated_error_message += "\n Table {} has duplicate values in column {}"\
.format(TablePeriods.__tablename__, r.COL_PERIOD_PERIOD)
if is_error:
raise error_objects.MasterDataIncompleteError(
"The Master Data contains the following errors:\n{}".format(
consolidated_error_message))
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 create_internal_cashflow_statement(year, month):
''' Populates TableFinancialStatements with cash flow statement lines calculated indirectly using the
indirect method.
:param year:
:param month:
:return:
'''
current_period = datetime.datetime(year=year, month=month, day=1)
prior_period = current_period - relativedelta(months=1)
# Calculating cash flows based on Balance Sheet movements requires both the current period and the prior period
# to be populated in the database (throws error if the prior period doesn't exist)
utils.data_integrity.check_period_exists(year=prior_period.year,
month=prior_period.month)
# Get all Income Statement and Balance Sheet rows split by L0 and L1 node
session = db_sessionmaker()
data = session.query(TableFinancialStatements, TableChartOfAccounts, TableNodeHierarchy)\
.filter(TableFinancialStatements.AccountCode == TableChartOfAccounts.GLCode)\
.filter(TableChartOfAccounts.L3Code==TableNodeHierarchy.L3Code)\
.filter(TableFinancialStatements.Period<=current_period)\
.filter(TableFinancialStatements.Period>=prior_period)\
.all()
session.close()
# Populate the data into standardised Consol Fin Statement rows
calc_rows = []
for fs, coa, node in data:
new_row = TableConsolidatedFinStatements(
ID=None,
Period=fs.Period,
CompanyCode=fs.CompanyCode,
CompanyName=None,
PartnerCompanyCode=None,
PartnerCompanyName=None,
CostCentreCode=None,
CostCentreName=None,
PartnerCostCentreCode=None,
PartnerCostCentreName=None,
FinancialStatement=node.L0Name,
GLAccountCode=coa.GLCode,
GLAccountName=coa.GLName,
L1Code=node.L1Code,
L1Name=node.L1Name,
L2Code=node.L2Code,
L2Name=node.L2Name,
L3Code=node.L3Code,
L3Name=node.L3Name,
CostHierarchyNumber=None,
Value=fs.Value,
TimeStamp=fs.TimeStamp)
calc_rows.append(new_row)
# Check that all nodes in the standardised data are captured in the static master data
unmapped_nodes = utils.data_integrity.get_all_bs_nodes_unmapped_for_cashflow(
)
if unmapped_nodes != []:
raise error_objects.MasterDataIncompleteError(
"Balance sheet nodes not found in master lists, cannot calculate cashflow:\n{}"
.format(unmapped_nodes))
# Calculate the periodic movements of each cash flow statement category and create database row objects
list_of_companies = list(set([
row.CompanyCode for row in calc_rows
])) # Create for list of companies to future-proof
cash_flow_rows = []
for company in list_of_companies:
cash_flow_rows += calculate_company_cashflow(year=year,
month=month,
data_rows=calc_rows,
company_code=company)
# Check that the change in cash between two periods is the same as the calculated cashflow
for company in list_of_companies:
periodic_cash_change = calculate_change_in_balancesheet_value(
year=year,
month=month,
company=company,
bs_L2_node=r.CM_BS_CASH,
data_rows=calc_rows)
calculated_cash_change = sum([
row.Value for row in cash_flow_rows if row.CompanyCode == company
])
if abs(calculated_cash_change -
periodic_cash_change) > r.DEFAULT_MAX_CALC_ERROR:
raise error_objects.CashFlowCalculationError(
"Calculated indirect cash flow of {} in company {} is different for period change"
" in values {} for period {}.{} ".format(
calculated_cash_change, company, periodic_cash_change,
year, month))
return cash_flow_rows