diff options
| -rw-r--r-- | python/Dawn/views.py | 75 | ||||
| -rw-r--r-- | sql/dawn.sql | 86 |
2 files changed, 87 insertions, 74 deletions
diff --git a/python/Dawn/views.py b/python/Dawn/views.py index 983e7ca9..cc85f39e 100644 --- a/python/Dawn/views.py +++ b/python/Dawn/views.py @@ -481,83 +481,10 @@ def list_trades(kind): app.logger.error(e) abort(404) else: - Orig_cp = aliased(Counterparties) - Term_cp = aliased(Counterparties) if kind not in ["cds", "swaption"]: trade_list = Deal.query.order_by(Deal.trade_date.desc(), Deal.id.desc()) else: - trade_list = ( - Deal.query.join(Orig_cp, Deal.counterparty, isouter=True) - .join(Term_cp, Deal.termination_counterparty, isouter=True) - .with_entities( - Deal.id, - Deal.dealid, - func.coalesce(Deal.termination_date, Deal.trade_date).label( - "trade_date" - ), - Deal.notional, - Deal.security_desc, - Deal.security_id, - func.coalesce(Term_cp.code, Orig_cp.code).label("cp_code"), - func.coalesce(Term_cp.name, Orig_cp.name).label("name"), - case( - [ - (Deal.termination_cp.is_(None), "New"), - (Deal.termination_cp == Deal.cp_code, "Termination"), - ], - else_="Assignment", - ).label("trade_type"), - Deal.folder, - ) - .order_by( - func.coalesce(Deal.termination_date, Deal.trade_date).desc(), - Deal.dealid, - ) - ) - if kind == "swaption": - trade_list = trade_list.add_columns( - case( - [(Deal.termination_cp.is_(None), Deal.settle_date)], else_=None - ).label("settle_date"), - Deal.option_type, - Deal.expiration_date, - Deal.strike, - case( - [(Deal.termination_cp.is_(None), Deal.buysell)], else_=~Deal.buysell - ).label("buysell"), - ( - case( - [ - ( - Deal.termination_cp.is_(None), - Deal.notional - * Deal.price - / 100 - * (2 * Deal.buysell.cast(db.Integer) - 1.0), - ) - ], - else_=-Deal.termination_fee, - ) - ).label("fee"), - ) - elif kind == "cds": - trade_list = trade_list.add_columns( - case( - [(Deal.termination_cp.is_(None), Deal.protection.cast(db.String))], - else_=case([(Deal.protection == "Buyer", "Seller")], else_="Buyer"), - ).label("protection"), - Deal.orig_attach, - Deal.orig_detach, - Deal.attach, - Deal.detach, - ( - case( - [(Deal.termination_cp.is_(None), Deal.upfront)], - else_=-Deal.termination_fee, - ) - ).label("upfront"), - Deal.ref, - ) + trade_list = db.session.execute(f"SELECT * FROM {kind}_trades") return render_template(f"{kind}_blotter.html", trades=trade_list) diff --git a/sql/dawn.sql b/sql/dawn.sql index 58369822..9e64720b 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1698,3 +1698,89 @@ CREATE TABLE strategy_im( ) CREATE TYPE cash_rate AS ENUM('FED_FUND', '1M_LIBOR', '3M_LIBOR'); + +CREATE OR REPLACE VIEW swaption_trades AS +SELECT id, + dealid, + termination_date as trade_date, + notional, + security_desc, + security_id, + CASE WHEN termination_cp=cp_code THEN + 'Termination' + ELSE + 'Assignment' + END AS trade_type, + folder, + termination_cp, + name, + option_type, + expiration_date, + strike, + NOT buysell AS buysell, + -termination_fee AS fee +FROM swaptions JOIN counterparties ON termination_cp = code WHERE termination_date IS NOT NULL +UNION ALL ( +SELECT id, + dealid, + trade_date, + notional, + security_desc, + security_id, + 'New' AS trade_type, + folder, + cp_code, + name, + option_type, + expiration_date, + strike, + buysell, + notional * price / 100 * (2* buysell::integer -1.) +FROM swaptions JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC; + +CREATE OR REPLACE VIEW cds_trades AS +SELECT id, + dealid, + termination_date as trade_date, + notional, + security_desc, + security_id, + CASE WHEN termination_cp=cp_code THEN + 'Termination' + ELSE + 'Assignment' + END, + folder, + termination_cp, + name, + orig_attach, + orig_detach, + attach, + detach, + CASE WHEN protection = 'Buyer' THEN + 'Seller'::protection + ELSE + 'Buyer'::protection + END AS protection, + -termination_fee AS upfront, + ref +FROM cds JOIN counterparties ON termination_cp = code WHERE termination_date IS NOT NULL +UNION ALL ( +SELECT id, + dealid, + trade_date, + notional, + security_desc, + security_id, + 'New', + folder, + cp_code, + name, + orig_attach, + orig_detach, + attach, + detach, + protection, + upfront, + ref +FROM cds JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC; |