def query_accounts_by_company_id(insurer_ids, bank_name, account_type):
"""
get all bank accounts for each insurer in a list of insurer ids
"""
session, connection = connect_bank(bank_name)
accounts_query = session.query(
Insurer.insurer_id,
Insurer.customer_id,
Account.account_id
).outerjoin(
Account,
Insurer.customer_id == Account.customer_id
).filter(
Account.account_type == account_type
).statement
accounts = pd.read_sql(
accounts_query,
connection
)
accounts = accounts[accounts['insurer_id'].isin(insurer_ids)]
connection.close()
return accounts
def query_accounts_by_person_id(person_ids, bank_name, account_type):
"""
get all the accounts for each person id in a list of person ids
"""
session, connection = connect_bank(bank_name)
accounts_query = session.query(
Person.person_id,
Person.customer_id,
Account.account_id
).outerjoin(
Account,
Person.customer_id == Account.customer_id
).filter(
Account.account_type == account_type
).statement
accounts = pd.read_sql(
accounts_query,
connection
)
accounts = accounts[accounts['person_id'].isin(person_ids)]
connection.close()
return accounts
def query_customers_by_person_id(person_ids, bank_name):
"""
get corresponding customer ids for each person id in a list of person ids
"""
session, connection = connect_bank(bank_name)
customer_query = session.query(Person).statement
customers = pd.read_sql(customer_query, connection)
customers = customers[customers['person_id'].isin(person_ids)]
customers = customers['customer_id']
connection.close()
return customers
def query_population_wealth():
"""
get wealth for each person in the population
"""
population = query_population()
person_ids = population['person_id']
banks = query_banks()
pop_wealth = pd.DataFrame()
for index, row in banks.iterrows():
bank_name = row['bank_name']
session, connection = connect_bank(bank_name)
debit_query = session.query(
Transaction.debit_account,
func.sum(Transaction.transaction_amount)).group_by(
Transaction.debit_account).statement
debit_amounts = pd.read_sql(debit_query, connection)
debit_amounts.columns = ['account_id', 'debits']
credit_query = session.query(
Transaction.credit_account,
func.sum(Transaction.transaction_amount)).group_by(
Transaction.credit_account).statement
credit_amounts = pd.read_sql(credit_query, connection)
credit_amounts.columns = ['account_id', 'credits']
person_accounts = query_accounts_by_person_id(person_ids, bank_name,
'cash')
wealth = person_accounts.merge(debit_amounts,
on='account_id',
how='left')
wealth = wealth.merge(credit_amounts, on='account_id', how='left')
wealth['debits'] = wealth['debits'].fillna(0)
wealth['credits'] = wealth['credits'].fillna(0)
wealth = wealth[['person_id', 'account_id', 'debits', 'credits']]
wealth = wealth.groupby(['person_id',
'account_id'])[['debits', 'credits'
]].agg('sum').reset_index()
wealth['wealth'] = wealth['debits'] - wealth['credits']
wealth = wealth[['person_id', 'wealth']]
wealth = wealth.groupby(['person_id'
])['wealth'].agg('sum').reset_index()
pop_wealth = pop_wealth.append(wealth)
connection.close()
pop_wealth = pop_wealth.groupby(['person_id'
])['wealth'].agg('sum').reset_index()
return pop_wealth
def query_person_wealth(person_id):
"""
returns the wealth for one person
"""
banks = query_banks()
person_wealth = pd.DataFrame()
for index, row in banks.iterrows():
bank_name = row['bank_name']
session, connection = connect_bank(bank_name)
person_accounts = query_accounts_by_person_id(person_id, bank_name,
'cash')
account_list = list(person_accounts['account_id'])
debit_query = session.query(
Transaction.debit_account.label('account_id'),
func.sum(Transaction.transaction_amount).label('debits')).group_by(
Transaction.debit_account).filter(
Transaction.debit_account.in_(account_list)).statement
debit_amounts = pd.read_sql(debit_query, connection)
credit_query = session.query(
Transaction.credit_account.label('account_id'),
func.sum(
Transaction.transaction_amount).label('credits')).group_by(
Transaction.credit_account).filter(
Transaction.debit_account.in_(account_list)).statement
credit_amounts = pd.read_sql(credit_query, connection)
wealth = person_accounts.merge(debit_amounts,
on='account_id',
how='left')
wealth = wealth.merge(credit_amounts, on='account_id', how='left')
wealth['debits'] = wealth['debits'].fillna(0)
wealth['credits'] = wealth['credits'].fillna(0)
wealth = wealth[['person_id', 'account_id', 'debits', 'credits']]
wealth = wealth.groupby(['person_id',
'account_id'])[['debits', 'credits'
]].agg('sum').reset_index()
wealth['wealth'] = wealth['debits'] - wealth['credits']
wealth = wealth[['person_id', 'wealth']]
wealth = wealth.groupby(['person_id'
])['wealth'].agg('sum').reset_index()
person_wealth = person_wealth.append(wealth)
connection.close()
person_wealth = person_wealth.groupby(
['person_id'])['wealth'].agg('sum').reset_index()
return person_wealth