diff options
| -rw-r--r-- | python/strat_cash_realloc.py | 30 | ||||
| -rw-r--r-- | sql/dawn.sql | 19 |
2 files changed, 25 insertions, 24 deletions
diff --git a/python/strat_cash_realloc.py b/python/strat_cash_realloc.py index 748b8de2..29baf7dc 100644 --- a/python/strat_cash_realloc.py +++ b/python/strat_cash_realloc.py @@ -1,24 +1,21 @@ -from serenitas.utils.db import dbconn +from dataclasses import replace from serenitas.ops.funds import Bowdst from serenitas.ops.trade_dataclasses import WireDeal import datetime from serenitas.analytics.dates import prev_business_day from collateral.common import CASH_STRATEGY_MAPPING, STRATEGY_CASH_MAPPING -from serenitas.utils.misc import rename_keys -def generate_csv(date, conn, fund="BOWDST"): - wire_deals = [] - with conn.cursor() as c: +def generate_csv(date, fund="BOWDST"): + with WireDeal._conn.cursor() as c: c.execute( - "SELECT count(*) FROM wires WHERE trade_date=%s AND fund=%s AND author='auto';", + "SELECT 1 FROM wires WHERE trade_date=%s AND fund=%s AND author='auto'", ( date, fund, ), ) - (count,) = c.fetchone() - if count > 0: + if c.fetchone() is None: return c.execute( "SELECT * FROM list_orphaned_cash(%s, %s) ", @@ -28,17 +25,13 @@ def generate_csv(date, conn, fund="BOWDST"): ), ) for row in c: - if row.strat not in CASH_STRATEGY_MAPPING: - obj = WireDeal(**row._asdict(), action="NEW") - offset = obj.offset(STRATEGY_CASH_MAPPING[obj.folder]) + if row.folder not in CASH_STRATEGY_MAPPING: + obj = WireDeal(**row._asdict()) + offset = replace(obj, folder=STRATEGY_CASH_MAPPING[obj.folder]) obj.stage() offset.stage() - wire_deals.extend([obj, offset]) - if wire_deals: - WireDeal.commit() - for wire in wire_deals: - wire.set_id() - Bowdst.staging_queue.append(wire.to_globeop("NEW")) + for wire in WireDeal.commit(returning=True): + Bowdst.staging_queue.append(wire.to_globeop("NEW")) buf, dest = Bowdst.build_buffer("wire") Bowdst.upload(buf, dest.name) Bowdst().clear() @@ -57,5 +50,4 @@ if __name__ == "__main__": ) args = parser.parse_args() - conn = dbconn("dawndb") - generate_csv(args.workdate, conn) + generate_csv(args.workdate) diff --git a/sql/dawn.sql b/sql/dawn.sql index 6993c389..d5ad3ea2 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3921,10 +3921,19 @@ UNION spots.spot_rate FROM spots) a LEFT JOIN counterparties c ON cp_code=code ; - - CREATE OR REPLACE function list_orphaned_cash(p_date date, p_fund fund) -RETURNS TABLE(fund fund, trade_date date,amount float, portfolio portfolio, clean_strat strategy, cash_account text, currency currency, folder TEXT) AS $$ + +CREATE OR REPLACE function list_orphaned_cash(p_date date, p_fund fund) +RETURNS TABLE(fund fund, trade_date date,amount float, portfolio portfolio, cash_account text, currency currency, folder TEXT, custodian text, cp_code varchar(10)) AS $$ BEGIN - RETURN Query SELECT a.*, coalesce(pfm.folder::text, a.strat::text) FROM (SELECT vr.fund, vr.periodenddate, -sum(vr.endqty) AS amount, vr.port, vr.strat, vr.custacctname AS account, vr.invid::currency AS currency FROM valuation_reports vr - WHERE vr.periodenddate =p_date AND vr.fund=p_fund AND vr.invid IN ('USD', 'EUR') AND vr.port NOT IN ('GFS_HELPER_BUSINESS_UNIT', 'CASH') GROUP BY (vr.port, vr.strat, vr.custacctname, vr.invid, vr.fund, vr.periodenddate) HAVING sum(vr.endqty) !=0) a LEFT JOIN portfolio_folder_mapping pfm on a.strat::text = pfm.clean_folder::text WHERE active; END; + RETURN QUERY + SELECT a.*, coalesce(pfm.folder::text, a.strat::text), accounts2.custodian, accounts2.cp_code FROM ( + SELECT vr.fund, vr.periodenddate, -sum(vr.endqty) AS amount, vr.port, vr.custacctname AS cash_account, vr.invid::currency AS currency + FROM valuation_reports vr + GROUP BY (vr.periodenddate, vr.fund, vr.port, vr.strat, vr.custacctname, vr.invid) + HAVING periodenddate=p_date AND vr.fund=p_fund AND vr.invid in ('USD', 'EUR') and port NOT IN ('GFS_HELPER_BUSINESS_UNIT', 'CASH') AND sum(vr.endqty) !=0 + ) a + LEFT JOIN portfolio_folder_mapping pfm ON a.strat::text = pfm.clean_folder::text + LEFT JOIN accounts2 USING (cash_account) + WHERE pfm.active; +END $$ LANGUAGE plpgsql; |