diff options
| -rw-r--r-- | python/innocap_file_transfer.py | 52 | ||||
| -rw-r--r-- | python/report_ops/utils.py | 4 |
2 files changed, 49 insertions, 7 deletions
diff --git a/python/innocap_file_transfer.py b/python/innocap_file_transfer.py index 1161a5c6..191901f1 100644 --- a/python/innocap_file_transfer.py +++ b/python/innocap_file_transfer.py @@ -3,8 +3,13 @@ from serenitas.utils.env import DAILY_DIR import datetime from serenitas.ops.citco import GIL, GTL from serenitas.utils.remote import Client -from serenitas.analytics.dates import prev_business_day +from serenitas.utils.db import dbconn +from serenitas.analytics.dates import prev_business_day, next_business_day from report_ops.sma import build_position_file +from report_ops.utils import SettlementMonitor + +settlement_sum_query = "SELECT currency, sum(payment_amount) as payment_amount FROM payment_settlements ps2 WHERE fund=%s AND asset_class in ('BOND', 'SPOT') AND settle_date BETWEEN %s AND %s group by currency;" +_account_fund = {"ISOS01": "ISOSEL"} def concat_csv(file_type, date): @@ -46,15 +51,49 @@ def upload_position_files(date, fund, upload): client.put(buf, dest.name) +def get_cash_balance(date, account, conn): + with conn.cursor() as c: + sql_str = "SELECT balance, currency_code from cash_balances WHERE account_number=%s AND date=%s" + c.execute(sql_str, (account, date)) + return {row.currency_code: row.balance for row in c} + + +def monitor_cash_balances(date, account, conn, upload): + cob = prev_business_day(date) + fund = _account_fund[account] + if cash_balances := get_cash_balance(cob, account, conn): + for projection_date in (date, next_business_day(date)): + with conn.cursor() as c: + c.execute(settlement_sum_query, (fund, date, projection_date)) + for row in c: + projection_balance = ( + cash_balances[row.currency] + row.payment_amount + ) + if projection_balance < 0: + SettlementMonitor.stage( + { + "date": projection_date, + "account": account, + "currency": row.currency, + "projected_balance": projection_balance, + } + ) + SettlementMonitor.email(fund) + SettlementMonitor._insert_queue.clear() + + else: + raise ValueError(f"No cash balances parsed for account {account}: {cob}") + + if __name__ == "__main__": import argparse parser = argparse.ArgumentParser() parser.add_argument( - "cob", + "workdate", nargs="?", type=datetime.date.fromisoformat, - default=prev_business_day(datetime.date.today()), + default=datetime.date.today(), help="file transfer date", ) parser.add_argument( @@ -65,6 +104,9 @@ if __name__ == "__main__": help="uploads to citco and innocap", ) args = parser.parse_args() + cob = prev_business_day(args.workdate) + conn = dbconn("dawndb") - upload_citco_files(args.cob, not args.no_upload) - upload_position_files(args.cob, "ISOSEL", not args.no_upload) + upload_citco_files(cob, not args.no_upload) + upload_position_files(cob, "ISOSEL", not args.no_upload) + monitor_cash_balances(args.workdate, "ISOS01", conn, not args.no_upload) diff --git a/python/report_ops/utils.py b/python/report_ops/utils.py index 2177a5ae..1360f102 100644 --- a/python/report_ops/utils.py +++ b/python/report_ops/utils.py @@ -299,9 +299,9 @@ class CDXQuoteMonitor( ) -class CBMonitor( +class SettlementMonitor( Monitor, - headers=("date", "account", "currency", "amount"), + headers=("date", "account", "currency", "projected_balance"), num_format=[("{0:,.2f}", 3)], ): @classmethod |