import datetime from typing import ClassVar from dataclasses import dataclass import gpg from selenium.common.exceptions import ( StaleElementReferenceException, ElementNotInteractableException, ) from serenitas.utils.exchange import ExchangeMessage, FileAttachment from serenitas.utils.env import DAILY_DIR from serenitas.ops.trade_dataclasses import BondDeal from serenitas.ops.funds import Service from .misc import get_dir, _recipients, _cc_recipients from .scotia import download_scotia_report def upload_to_custodian(account, trade_date, upload): _service = {"BBH": "BRINKER", "UMB": "UMB"} custodian = Service[_service[account]] conn = BondDeal._conn with conn.cursor() as c, conn.cursor() as d: c.execute( "SELECT * FROM bond_trades WHERE trade_date=%s AND account=%s", ( trade_date, account, ), ) for row in c: d.execute( "SELECT identifier, principal_payment, accrued_payment FROM bond_csv_upload WHERE allocationid=%s FOR UPDATE", (row.id,), ) if old_row := d.fetchone(): if any( [ old_row.identifier != row.identifier, old_row.principal_payment != row.principal_payment, old_row.accrued_payment != row.accrued_payment, ] ): old_trade = BondDeal.from_dict( **(row._asdict() | old_row._asdict()), scaled=True ) custodian.push_trade(old_trade, "CANCEL") d.execute( "UPDATE bond_csv_upload SET identifier=%s, principal_payment=%s, accrued_payment=%s WHERE allocationid=%s", ( row.identifier, row.principal_payment, row.accrued_payment, row.id, ), ) else: continue else: d.execute( "INSERT INTO bond_csv_upload (allocationid, identifier, principal_payment, accrued_payment) VALUES (%s, %s, %s, %s)", ( row.id, row.identifier, row.principal_payment, row.accrued_payment, ), ) trade = BondDeal.from_dict(**row._asdict(), scaled=True) custodian.push_trade(trade, "NEW") if not custodian.staging_queue: return buf, dest = custodian.build_buffer("bond") custodian.staging_queue.clear() conn.commit() if upload: em = ExchangeMessage() custodian.upload(buf, dest.name, confirm=account != "UMB") cc = _cc_recipients.get(custodian.name, ("NYOPS@lmcg.com",)) em.send_email( f"{account}: Bond Positions Uploaded for {trade_date}", "Hi, \nWe've just uploaded the positions via SFTP. File receipt attached to this email", _recipients.get(account, cc), cc_recipients=cc, reply_to=cc, attach=(FileAttachment(name=dest.name, content=buf),), ) with conn.cursor() as c: c.execute( "UPDATE bond_csv_upload SET status = 'Submitted' FROM " "(SELECT trade_date, account, allocationid FROM bond_csv_upload " "LEFT JOIN bond_trades ON bond_csv_upload.allocationid = bond_trades.id " "WHERE account=%s AND trade_date=%s) subquery " "WHERE bond_csv_upload.allocationid = subquery.allocationid ", ( account, trade_date, ), ) conn.commit() @dataclass class Custodian: date: datetime.date account: ClassVar[str] em = ExchangeMessage() def __init_subclass__(cls, account): cls.account = account class NT(Custodian, account="NT"): @classmethod def download_reports(cls, date=datetime.date.today()): for msg in cls.em.get_msgs( path=["SeleneOps", "Passport"], start_date=date, end_date=date ): for attach in msg.attachments: message_time = attach.last_modified_time.replace( tzinfo=cls.em._account.default_timezone ) if attach.name == "Attachment1.pgp": dest = get_dir(message_time.date(), archived=False) dest.mkdir(exist_ok=True, parents=True) with attach.fp as fp: plaintext, result, verify_result = gpg.Context().decrypt( fp.read(), passphrase="Serenitas1" ) fname = ( "custodian_wires" if "custodian" in verify_result.file_name else "cash" ) dest = dest / f"{fname}_{message_time:%Y%m%d%H%M}.csv" with open(dest, "w") as csvFile: text = plaintext.decode("utf-8").replace("\t", ",") csvFile.write(text) class UMB(Custodian, account="UMB"): @classmethod def download_reports(cls, date=datetime.date.today()): for msg in cls.em.get_msgs( count=20, path=["NYops", "Powerstation"], start_date=date, end_date=date ): for attach in msg.attachments: ts = attach.last_modified_time.replace( tzinfo=cls.em._account.default_timezone ) dest = get_dir(date, archived=False) dest.mkdir(exist_ok=True, parents=True) if attach.name.startswith("cash_balances_umb"): p = dest / f"umb_{ts:%Y%m%d%H%M}.xlsx" elif attach.name.startswith("umb_serenitas_wires"): p = dest / f"umbwires_{ts:%Y%m%d%H%M}.xlsx" else: pass if not p.exists(): p.write_bytes(attach.content) class BNY(Custodian, account="BONY2"): @classmethod def download_reports(cls, date=datetime.date.today()): for msg in cls.em.get_msgs( 20, path=["BowdoinOps", "Reports"], subject__startswith="Document(s) from Reporting", start_date=date, end_date=date, ): if msg.sender == "notify@bnymellon.com": for attach in msg.attachments: fname = attach.name if fname.endswith("csv"): base_name = fname.removesuffix(".csv") file_type, date_part = base_name.split("_") match file_type: case ( "Asset Detail" | "Net Investment Earned Income by Security" | "Settled Cash Statement" ): date = datetime.datetime.strptime( date_part, "%d %b %Y" ).date() case "BowdstWires": try: date = datetime.datetime.strptime( date_part, "%Y%m%d%H%M%S" ).date() except ValueError: date = datetime.datetime.strptime( date_part, "%d %b %Y%H%M%S" ).date() case "Live-cash": date = datetime.datetime.strptime( date_part, "%Y%m%d%H%M%S" ).date() case _: continue p = DAILY_DIR / str(date) / "Reports" / fname if not p.parent.exists(): p.parent.mkdir(parents=True, exist_ok=True) if not p.exists(): p.write_bytes(attach.content) class SCOTIA(Custodian, account="SCOTIA"): @staticmethod def download_reports(date=datetime.date.today()): try: return download_scotia_report(date) except (StaleElementReferenceException, ElementNotInteractableException): raise ValueError(f"Scotia report not ready yet for {date}")