import datetime from typing import ClassVar from dataclasses import dataclass import gpg import re import dateutil.parser as dp from selenium.common.exceptions import ( StaleElementReferenceException, ElementNotInteractableException, TimeoutException, ) from serenitas.utils.exchange import ExchangeMessage, FileAttachment from serenitas.utils.env import DAILY_DIR from serenitas.utils.remote import Client 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() _fund_folder_mapping = { "SERCGMAST": "NYops", "BOWDST": "BowdoinOps", "ISOSEL": "SeleneOps", } def download_nt_reports(date, fund, em): for msg in em.get_msgs( path=[_fund_folder_mapping[fund], "Passport"], start_date=date, end_date=date ): for attach in msg.attachments: message_time = attach.last_modified_time.replace( tzinfo=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" ) if "custodian" in verify_result.file_name: fname = f"NT_WIRE_{fund}" elif "Cash" in verify_result.file_name: fname = f"NT_CASH_{fund}" else: print(f"NT file not recognized:{verify_result.file_name}") p = dest / f"{fname}_{message_time:%Y%m%d%H%M}.csv" if not p.exists(): with open(p, "w") as csvFile: text = plaintext.decode("utf-8").replace("\t", ",") csvFile.write(text) def download_umb_reports(date, fund, em): for msg in em.get_msgs( count=20, path=[_fund_folder_mapping[fund], "Powerstation"], start_date=date, end_date=date, ): for attach in msg.attachments: if attach.name.endswith("xlsx"): ts = attach.last_modified_time.replace( tzinfo=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_CASH_{fund}_{ts:%Y%m%d%H%M}.xlsx" elif attach.name.startswith("umb_serenitas_wires"): p = dest / f"UMB_WIRE_{fund}_{ts:%Y%m%d%H%M}.xlsx" else: pass if not p.exists(): p.write_bytes(attach.content) def download_bny_reports(date, fund, em): for msg in em.get_msgs( 20, path=[_fund_folder_mapping[fund], "Reports"], subject__startswith="Document(s) from Reporting", sender="notify@bnymellon.com", start_date=date, end_date=date, ): for attach in msg.attachments: fname = attach.name if fname.endswith("csv"): file_type, timestamp = fname.rsplit("_", 1) date = dp.parse(timestamp.removesuffix(".csv")).date() 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) def download_scotia_reports(date, fund, **kwargs): download_scotia_report(date) def download_gstx_reports(date, fund, **kwargs): sftp = Client.from_creds("gstx") pattern = r"(\d{4}-\d{2}-\d{2}T\d{2}_\d{2}_\d{2}\.\d{6})" for f in sftp.client.listdir(): match = re.search(pattern, f).group(1) ts = datetime.datetime.strptime(match, "%Y-%m-%dT%H_%M_%S.%f") if ts.date() == date: with sftp.client.open(f) as fh: plaintext, result, verify_result = gpg.Context().decrypt( fh.read(), passphrase="Serenitas;1" ) dest = DAILY_DIR / "Serenitas" / "GSTX_reports" / f"bai_{date}.csv" with open(dest, "w") as csvFile: text = plaintext.decode("utf-8") csvFile.write(text) return raise ValueError(f"No Gstx report {fund}:{date}") def get_custodian_download_fun(custodian): match custodian: case "SCOTIA": return download_scotia_reports case "GSTX": return download_gstx_reports case "BNY": return download_bny_reports case "UMB": return download_umb_reports case "NT": return download_nt_reports case _: raise ValueError(f"Custodian Value {custodian} not valid")