1 files changed, 118 insertions, 1 deletions
diff --git a/python/report_ops/sma.py b/python/report_ops/sma.py
index 9a3bf77a..bd3ce547 100644
--- a/python/report_ops/sma.py
+++ b/python/report_ops/sma.py
@@ -1,12 +1,15 @@
 import datetime
-from dataclasses import dataclass
+from dataclasses import dataclass, field
 from serenitas.utils.db import dbconn
 from serenitas.utils.exchange import ExchangeMessage
+from serenitas.utils.misc import rename_keys
+from serenitas.ops.trade_dataclasses import Deal
 from .misc import _sma_recipients, _cc_recipients
 from exchangelib import FileAttachment
 import pandas as pd
 from io import StringIO
 from typing import ClassVar
+from .headers import POSITION_HEADERS
 
 
 @dataclass
@@ -71,3 +74,117 @@ class IsoselSMA(SMA, fund="ISOSEL"):
 
 class BowdstSMA(SMA, fund="BOWDST"):
     pass
+
+
+_sql_query = {
+    "bond": "SELECT * FROM risk_positions(%s, null, %s) ",
+    "future": (
+        "WITH tmp AS (SELECT bbg_ticker, fund, security_desc, currency, maturity, sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional FROM futures "
+        "WHERE trade_date <= %s AND fund=%s) "
+        "SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity FROM tmp LEFT JOIN accounts USING (fund) WHERE tmp.notional != 0 AND account_type='Future';"
+    ),
+    "tranche": "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, trb.notional * trb.tranche_factor as active_notional, cds.* FROM tranche_risk_isosel trb left join cds on trade_id=id WHERE date=%s",
+    "cdx_swaption": "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, %s) spr LEFT JOIN swaptions  ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;",
+    "ir_swaption": "SELECT abs(spr.notional) AS active_notional, spr.nav as serenitas_nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, %s) spr LEFT JOIN swaptions  ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;",
+    "cdx": "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, %s) cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;",
+}
+
+_fund_custodian = {"BOWDST": "BONY2", "ISOSEL": "NT"}
+_fund_client = {"BOWDST": "Hedgemark", "ISOSEL": "Innocap"}
+
+
+@dataclass
+class PositionReport(Deal, deal_type=None, table_name=None):
+    client_name: str = field(metadata={"position": "Client Name"})
+    fund: str = field(metadata={"position": "Fund Name"})
+    cp_code: str = field(metadata={"position": "Counterparty"})
+    dealid: str = field(metadata={"position": "Unique Deal ID"})
+    buysell: bool
+    currency: str = field(metadata={"position": "DealCurrencyA"})
+    notional: float = field(metadata={"position": "NotionalA"})
+    cob: datetime.date = field(metadata={"position": "COB Date"})
+    identifier: str = field(
+        metadata={"position": "Underlying (ISIN / CUSP / RED CODES)"}
+    )
+    start_date: datetime.date = field(default=None, metadata={"position": "Start Date"})
+    effective_date: datetime.date = field(
+        default=None, metadata={"position": "Effective Date"}
+    )
+    maturity_date: datetime.date = field(
+        default=None, metadata={"position": "Maturity Date"}
+    )
+    description: str = field(default=None, metadata={"position": "Underlying Desc"})
+    mtm_currency: str = field(default=None, metadata={"position": "MTM Currency"})
+    mtm_valuation: float = field(default=None, metadata={"position": "MTM Valuation"})
+    fixed_rate: float = field(default=None, metadata={"position": "FixedRate"})
+    putcall: bool = None
+    strike: float = field(default=None, metadata={"position": "Strike"})
+    underlying_maturity: datetime.date = field(
+        default=None, metadata={"position": "Underlying Maturity"}
+    )
+    exercise_type: str = field(default=None, metadata={"position": "Exercise Type"})
+    clearing_house: str = field(
+        default=None, metadata={"position": "Clearing House Name"}
+    )
+    account: str = field(default=None, metadata={"position": "AccountNumber"})
+    primebroker: str = field(default=None, metadata={"position": "Prime Broker"})
+    price: float = field(default=None, metadata={"position": "MarketPrice"})
+    asset_class: ClassVar[str] = field(metadata={"position": "Product Type"})
+    _query: ClassVar[str]
+
+    def __init_subclass__(cls, asset_class, **kwargs):
+        cls.asset_class = asset_class
+        cls._query = _sql_query[asset_class]
+
+    @classmethod
+    def stage_positions(cls, cob, fund):
+        with cls._conn.cursor() as c:
+            c.execute(cls._query, (cob, fund))
+            for row in c:
+                position = cls.from_query(row._asdict(), cob, fund)
+                position.serialize("position")
+
+    def from_query(d, cob, fund):
+        d["client_name"] = _fund_client[fund]
+        d["fund"] = fund
+        d["cob"] = cob
+        return d
+
+
+class BondPosition(PositionReport, asset_class="bond"):
+    @classmethod
+    def from_query(cls, d: dict, cob, fund):
+        d = super().from_query(d, cob, fund)
+        rename_keys(
+            d,
+            {
+                "local_market_value": "Local Market Value",
+                "usd_market_value": "MTM Valuation",
+            },
+        )
+        for key in ("account", "primebroker", "cp_code"):
+            d[key] = _fund_custodian[fund]
+        d["dealid"] = "Aggregated"
+        d["buysell"] = "B"
+        d["currency"] = "USD"
+        return cls.from_dict(**d)
+
+
+class FuturePosition(PositionReport, asset_class="future"):
+    pass
+
+
+class TranchePosition(PositionReport, asset_class="tranche"):
+    pass
+
+
+class IRSwaptionPosition(PositionReport, asset_class="ir_swaption"):
+    pass
+
+
+class CDXSwaptionPosition(PositionReport, asset_class="cdx_swaption"):
+    pass
+
+
+class CDXPosition(PositionReport, asset_class="cdx"):
+    pass