1 files changed, 80 insertions, 0 deletions
diff --git a/python/citco_ops/remote.py b/python/citco_ops/remote.py
new file mode 100644
index 00000000..06fa69f7
--- /dev/null
+++ b/python/citco_ops/remote.py
@@ -0,0 +1,80 @@
+from serenitas.utils.remote import SftpClient
+from dataclasses import field, dataclass
+from typing import Callable
+import pandas as pd
+from serenitas.utils.db import dawn_engine, dbconn
+import datetime
+import re
+from serenitas.analytics.dates import prev_business_day
+
+
+def citco_accrued(s):
+    if m := re.search("100502500_INNOCAP_ISOSEL.([\d]+)\.", s):
+        dt = datetime.datetime.strptime(m.group(1), "%Y%m%d%H%M%S")
+    return dt
+
+
+def load_citco_report(df, kd):
+    df["row"] = df.index
+    df.columns = df.columns.str.lower()
+    df.columns = df.columns.str.replace(" ", "_")
+    df["period_end_date"] = prev_business_day(kd.date())
+    df["knowledge_date"] = kd
+    for column in [
+        "init_date",
+        "init_settle_date",
+        "liqd_date",
+        "liqd_settle_date",
+        "maturity_date",
+        "orig_date",
+        "start_date",
+        "end_date",
+    ]:
+        if column in df.columns:
+            df[column] = pd.to_datetime(df[column], infer_datetime_format=True).dt.date
+    df["tid"] = df["tid"].astype("str")
+    return df
+
+
+@dataclass
+class Report:
+    table: str
+    ped_func: Callable[[str], datetime.datetime]
+    fname: str
+    _sftp: SftpClient
+    date: datetime.date
+    _conn: dbconn = dbconn("dawndb")
+
+    @classmethod
+    def set_report(cls, report, date):
+        fund, report = report.split("_")
+        if (fund, report) == ("isosel", "accrued"):
+            return cls(
+                "isosel_accrued",
+                citco_accrued,
+                "100502500_INNOCAP_ISOSEL",
+                SftpClient.from_creds("citco", folder="outgoing"),
+                date,
+            )
+
+    @property
+    def most_recent_report(self):
+        report_files = [
+            filename
+            for filename in self._sftp.client.listdir()
+            if self.fname in filename
+            if self.ped_func(filename).date() == self.date
+        ]
+        return max(report_files, key=self.ped_func)
+
+    def to_df(self):
+        with self._sftp.client.open(self.most_recent_report) as fh:
+            df = pd.read_csv(fh)
+        return load_citco_report(df, self.ped_func(self.most_recent_report))
+
+    def to_db(self, date):
+        # with self._conn.cursor() as c:
+        #     c.execute(f"DELETE FROM {self.table} WHERE period_end_date= %s", (prev_business_day(self.date),))
+        self._conn.commit()
+        df = self.to_df()
+        df.to_sql(self.table, dawn_engine, if_exists="append", index=False)