1 files changed, 110 insertions, 0 deletions
diff --git a/python/citco_ops/wires.py b/python/citco_ops/wires.py
new file mode 100644
index 00000000..9f1965fd
--- /dev/null
+++ b/python/citco_ops/wires.py
@@ -0,0 +1,110 @@
+from dataclasses import dataclass
+import datetime
+from serenitas.ops.trade_dataclasses import Deal, Ccy
+from typing import ClassVar
+from .custodians import NT, BNY
+from .misc import get_dir
+from dataclasses import field
+from csv import DictReader
+
+_nt_to_currency = {"EURO - EUR": "EUR", "U.S. DOLLARS - USD": "USD"}
+
+
+@dataclass
+class Wire(Deal, table_name="custodian_wires", deal_type="custodian_wires"):
+    date: datetime.date
+    fund: ClassVar[str]
+    entry_date: datetime.date
+    value_date: datetime.date
+    pay_date: datetime.date
+    currency: Ccy
+    amount: float
+    wire_details: str
+    unique_ref: str
+    dtkey: ClassVar = field(metadata={"insert": False, "select": False})
+
+    def __init_subclass__(cls, fund, dtkey, **kwargs):
+        cls._sql_insert = (
+            cls._sql_insert.removesuffix("RETURNING *")
+            + "ON CONFLICT (unique_ref) DO NOTHING RETURNING *"
+        )
+        cls.fund = fund
+        cls.dtkey = dtkey
+
+    def __post_init__(self):
+        self.amount = self.amount.replace(",", "")
+        if "(" in self.amount:
+            self.amount = -float(self.amount[1:-1])
+        else:
+            self.amount = float(self.amount)
+
+    @classmethod
+    def to_db(cls, fname, date):
+        cls.download_reports(date)
+        p = max(
+            [f for f in get_dir(date).iterdir() if fname in f.name],
+            key=cls.dtkey_fun(),
+            default=None,
+        )
+        return p
+
+    @classmethod
+    def dtkey_fun(cls):
+        def dtkey_fun(f):
+            return datetime.datetime.strptime(
+                f.name.removesuffix(".csv").removesuffix(".xlsx").rsplit("_")[-1],
+                cls.dtkey,
+            )
+
+        return dtkey_fun
+
+
+class BowdstWire(Wire, BNY, fund="BOWDST", dtkey="%Y%m%d%H%M%S"):
+    @classmethod
+    def from_report_line(cls, line: dict):
+        return cls(
+            date=line["Report Run Date"],
+            entry_date=line["Cash Entry Date"],
+            value_date=line["Cash Value Date"],
+            pay_date=line["Settle / Pay Date"],
+            currency=line["Local Currency Code"],
+            amount=line["Local Amount"],
+            wire_details=line["Transaction Description 1"]
+            if line["Transaction Type Code"] == "CW"
+            else line["Transaction Description 2"],
+            unique_ref=line["Reference Number"],
+        )
+
+    @classmethod
+    def to_db(cls, date):
+        p = super().to_db("BowdstWires", date)
+        with open(p) as fh:
+            reader = DictReader(fh)
+            for line in reader:
+                cls.from_report_line(line).stage()
+        cls.commit()
+
+
+class NTWire(Wire, NT, fund="ISOSEL", dtkey="%Y%m%d%H%M"):
+    @classmethod
+    def from_passport_line(cls, line: dict):
+        return cls(
+            date=line["Through date"],
+            entry_date=line["D-GL-POST"],
+            value_date=line["D-TRAN-EFF"],
+            pay_date=line["D-TRAN-EFF"],
+            currency=_nt_to_currency[line["N-GL-AC30"]],
+            amount=line["Net amount - local"],
+            wire_details=line["narrative"],
+            unique_ref=line["C-EXTL-SYS-TRN-DSC-3"],
+        )
+
+    @classmethod
+    def to_db(cls, date):
+        p = super().to_db("custodian_wires", date)
+        with open(p) as fh:
+            reader = DictReader(fh)
+            for line in reader:
+                if "sponsor" in line["narrative"].lower():
+                    cls.from_preport_line(line).stage()
+        cls.commit()