1 files changed, 87 insertions, 0 deletions
diff --git a/python/notify_tranche_cashflows.py b/python/notify_tranche_cashflows.py
new file mode 100644
index 00000000..bac41fcc
--- /dev/null
+++ b/python/notify_tranche_cashflows.py
@@ -0,0 +1,87 @@
+import datetime
+from io import StringIO
+from exchangelib import HTMLBody, FileAttachment
+import pandas as pd
+
+from serenitas.utils.db import dawn_engine
+
+from report_ops.utils import Monitor
+from report_ops.misc import _settlement_recipients, _cc_recipients
+
+
+class TrancheCashflowsEmails(
+    Monitor,
+    headers=(
+        "settle_date",
+        "dealid",
+        "counterparty_id",
+        "counterparty_name",
+        "principal",
+        "accrued",
+        "total_cashflow",
+        "currency",
+    ),
+    num_format=[
+        ("{0:,.2f}", 4),
+        ("{0:,.2f}", 5),
+        ("{0:,.2f}", 6),
+    ],
+):
+    @classmethod
+    def email(cls, date, fund, attachment):
+        if not cls._staging_queue:
+            return
+        cls._em.send_email(
+            f"Projected Tranche Cashflows {fund}: {date}",
+            HTMLBody(
+                f"""
+<html>
+  <head>
+    <style>
+      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
+      th, td {{ padding: 5px; }}
+    </style>
+  </head>
+  <body>
+  Hello,<br><br>Could you please confirm you see the same tranche cashflows as us for {date}? Excel file attached for your convenience<br><br>{cls.to_tabulate()}
+  </body>
+</html>"""
+            ),
+            to_recipients=_settlement_recipients[fund],
+            cc_recipients=_cc_recipients[fund],
+            attach=[attachment],
+        )
+
+
+def email_cashflow_amounts(date, fund):
+    sql_query = "SELECT dealid, cpty_id as counterparty_id, name as counterparty_name, coalesce(principal,0) as principal, coalesce(accrued,0) as accrued, coalesce(total_cashflow,0) as total_cashflow, currency FROM tranche_cashflow_settlements WHERE settle_date=%s AND fund=%s"
+    df = pd.read_sql_query(sql_query, params=(date, fund), con=dawn_engine)
+    buf = StringIO()
+    for row in df.itertuples():
+        d = row._asdict() | {"settle_date": date, "fund": fund}
+        TrancheCashflowsEmails.stage(d)
+    df.to_csv(buf, index=False)
+    attachment = FileAttachment(
+        name=f"tranche_cashflows_{fund}_{date}.csv", content=buf.getvalue().encode()
+    )
+    TrancheCashflowsEmails.email(date, fund, attachment)
+    TrancheCashflowsEmails.clear()
+
+
+if __name__ == "__main__":
+    import argparse
+
+    """Notify counterparties about tranche payment settlements. Must add in the argument for the settlement date as it is in the future"""
+    parser = argparse.ArgumentParser()
+    parser.add_argument(
+        "date",
+        type=datetime.date.fromisoformat,
+        help="settlement date",
+    )
+    args = parser.parse_args()
+    for fund in (
+        "SERCGMAST",
+        "BOWDST",
+        "ISOSEL",
+    ):
+        email_cashflow_amounts(args.date, fund)