1 files changed, 63 insertions, 0 deletions
diff --git a/python/citco_ops/cash.py b/python/citco_ops/cash.py
index 650d507f..8ed92db3 100644
--- a/python/citco_ops/cash.py
+++ b/python/citco_ops/cash.py
@@ -1,6 +1,12 @@
 from dataclasses import field, dataclass
 from serenitas.ops.trade_dataclasses import Deal, Fund
+from serenitas.analytics.dates import prev_business_day
 import datetime
+import gpg
+from serenitas.utils.exchange import ExchangeMessage
+from serenitas.utils.env import DAILY_DIR
+import pandas as pd
+from serenitas.utils.db import dbconn, dawn_engine
 
 
 @dataclass
@@ -32,3 +38,60 @@ class IAMDeal(Deal, deal_type=None, table_name="iam_tickets"):
         obj["MarginType"] = "Variation"
         obj["Basis"] = "ACT/360"
         return obj
+
+
+@dataclass
+class CashReport:
+    fund: str = "ISOSEL"
+    _conn: dbconn = dbconn("dawndb")
+
+    def __init__(self, date):
+        self.date = date
+        self.fund = "ISOSEL"
+
+    def download_report(self):
+        em = ExchangeMessage()
+        for msg in em.get_msgs(path=["SeleneOps", "Passport"]):
+            for attach in msg.attachments:
+                message_date = attach.last_modified_time.date()
+                if (
+                    attach.name == "Attachment1.pgp"
+                    and prev_business_day(message_date) == self.date
+                ):
+                    with attach.fp as fp:
+                        plaintext, result, verify_result = gpg.Context().decrypt(
+                            fp.read(), passphrase="Serenitas1"
+                        )
+                        dest = (
+                            DAILY_DIR
+                            / message_date.strftime("%Y-%m-%d")
+                            / f"{message_date.strftime('%Y%m%d')}_ISOSEL.csv"
+                        )
+                        with open(dest, "w") as csvFile:
+                            text = plaintext.decode("utf-8").replace("\t", ",")
+                            csvFile.write(text)
+                        return dest
+        raise ValueError(f"No reports found for fund: {self.fund} date: {self.date}")
+
+    def to_db(self):
+        df = pd.read_csv(self.download_report(), on_bad_lines="warn")
+        df = df[df["T-NARR-LONG"] == "CLOSING BALANCE"]
+        df = df[["Consolidation", "Currency code", "A-TRAN-AMT", "Account name"]]
+        df = df.reset_index(drop=True).rename(
+            {
+                "Consolidation": "account_number",
+                "Currency code": "currency_code",
+                "Account name": "account_name",
+                "A-TRAN-AMT": "balance",
+            },
+            axis=1,
+        )
+        df["date"] = self.date
+        df["fund"] = self.fund
+        with self._conn.cursor() as c:
+            c.execute(
+                "DELETE from cash_balances where fund=%s and date=%s",
+                (self.fund, self.date),
+            )
+        self._conn.commit()
+        df.to_sql("cash_balances", index=False, if_exists="append", con=dawn_engine)