from flask_wtf import Form from flask_wtf.file import FileField from sqlalchemy.dialects.postgresql import ENUM from wtforms import SelectField from wtforms.validators import Length from wtforms_alchemy import model_form_factory from sqlalchemy_utils import EmailType, PhoneNumberType from . import db class Counterparties(db.Model): __tablename__ = 'counterparties' code = db.Column(db.String(12), primary_key=True) name = db.Column(db.String) city = db.Column(db.String) state = db.Column(db.String(2)) dtc_number = db.Column(db.Integer) sales_contact = db.Column(db.String) sales_email = db.Column(EmailType) sales_phone = db.Column(PhoneNumberType()) valuation_contact1 = db.Column(db.String) valuation_email1 = db.Column(EmailType) valuation_contact2 = db.Column(db.String) valuation_email2 = db.Column(EmailType) valuation_contact3 = db.Column(db.String) valuation_email3 = db.Column(EmailType) valuation_contact4 = db.Column(db.String) valuation_email4 = db.Column(EmailType) notes = db.Column(db.String) instructions = db.Column(db.String, info={'form_field_class': FileField}) BOND_STRAT = ENUM('M_STR_MAV', 'M_STR_MEZZ', 'CSO_TRANCH', 'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU', 'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW', 'M_MTG_FP', 'M_MTG_LMG', 'M_MTG_SD', 'M_MTG_PR', name='bond_strat') CDS_STRAT = ENUM('HEDGE_CSO', 'HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS', 'SER_IGSNR', 'SER_IGMEZ', 'SER_IGEQY', 'SER_IGINX', 'SER_HYSNR', 'SER_HYMEZ', 'SER_HYEQY', 'SER_HYINX', 'SER_IGCURVE', 'MBSCDS', 'IGOPTDEL', 'HYOPTDEL', name='cds_strat') SWAPTION_STRAT = ENUM('IGPAYER', 'IGREC', 'HYPAYER', 'HYREC', name='swaption_strat') SWAPTION_TYPE = ENUM('PAYER', 'RECEIVER', name='swaption_type') REPO_TYPE = ENUM('REPO', 'REVERSE REPO', name='repo_type') CALL_NOTICE = ENUM('24H', '48H', '3D', '4D', '5D', '6D', '1W', '8D', '9D', '10D', '2W', '1M', '2M', name='call_notice') ASSET_CLASS = ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared', name='asset_class') ACTION = ENUM('NEW', 'UPDATE', 'CANCEL', name='action') CCY = ENUM('USD', 'CAD', 'EUR', 'YEN', name='currency') BBG_TYPE = ENUM('Mtge', 'Corp', name='bbg_type') SWAP_TYPE = ENUM('CD_INDEX', 'CD_INDEX_TRANCHE', 'CD_BASKET_TRANCHE', 'ABS_CDS', name='swap_type') ISDA = ENUM('ISDA2014', 'ISDA2003Cred', name='isda') DAY_COUNT = ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365', name='day_count') BUS_DAY_CONVENTION = ENUM('Modified Following', 'Following', 'Modified Preceding', 'Preceding', 'Second-Day-After', 'End-of-Month', name='bus_day_convention') PROTECTION = ENUM('Buyer', 'Seller', name='protection') class BondDeal(db.Model): __tablename__ = 'bonds' id = db.Column('id', db.Integer, primary_key=True) dealid = db.Column(db.String(28)) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(BOND_STRAT, nullable=False) custodian = db.Column(db.String(12), default='BAC', nullable=False) cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'counterparty'}, nullable = False) trade_date = db.Column(db.Date, nullable = False) settle_date = db.Column(db.Date, nullable = False) cusip = db.Column(db.String(9), info={'validators': Length(9,9), 'filters': [lambda x: x or None,], 'trim': True}) isin = db.Column(db.String(12), info={'validators': Length(12, 12), 'filters': [lambda x: x or None,], 'trim': True}) identifier = db.Column(db.String(12), info={'filters': [lambda x: x or None,], 'trim': True}) description = db.Column(db.String(32), nullable = False, info={'trim': True}) buysell = db.Column(db.Boolean, nullable = False, info={'choices':[(0, 'sell'), (1, 'buy')], 'coerce': lambda x: bool(int(x)) \ if x is not None else x}) faceamount = db.Column(db.Float, nullable=False) price = db.Column(db.Float, nullable=False) accrued = db.Column(db.Float, nullable = False) asset_class = db.Column(ASSET_CLASS) ticket = db.Column(db.String, info={'form_field_class': FileField}) principal_payment = db.Column(db.Float) accrued_payment = db.Column(db.Float) counterparty = db.relationship(Counterparties) __table_args__= (db.CheckConstraint('cusip is not Null or isin is not Null'),) class CDSDeal(db.Model): __tablename__ = 'cds' id = db.Column('id', db.Integer, primary_key=True) dealid = db.Column(db.String(28)) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(CDS_STRAT, nullable = False) custodian = db.Column(db.String(12), default='SGFCM', nullable=False) cashaccount = db.Column(db.String(10), default='SGNSCLMASW', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'counterparty'}, nullable = False) trade_date = db.Column(db.Date, nullable = False) effective_date = db.Column(db.Date, nullable = False) maturity = db.Column(db.Date, nullable = False) currency = db.Column(CCY, nullable = False) payment_rolldate = db.Column(BUS_DAY_CONVENTION, nullable = False) notional = db.Column(db.Float, nullable = False) fixed_rate = db.Column(db.Float, nullable = False) day_count = db.Column(DAY_COUNT, nullable = False) frequency = db.Column(db.SmallInteger, default=4, nullable = False) protection = db.Column(PROTECTION, nullable = False) security_id = db.Column(db.String(12), nullable = False) security_desc = db.Column(db.String(32), nullable = False) upfront = db.Column(db.Float, nullable = False) upfront_settle_date = db.Column(db.Date, nullable = False) swap_type = db.Column(SWAP_TYPE, nullable = False) attach = db.Column(db.SmallInteger, info={'min': 0, 'max':100}) detach = db.Column(db.SmallInteger, info={'min': 0, 'max':100}) clearing_facility = db.Column(db.String(12), default='ICE-CREDIT') isda_definition = db.Column(ISDA) counterparty = db.relationship(Counterparties) __table_args__ = (db.CheckConstraint("swap_type!='CD_INDEX_TRANCHE' or " \ "(attach is not NULL and detach is not NULL)"),) class RepoDeal(db.Model): __tablename__ = 'repo' id = db.Column('id', db.Integer, primary_key=True) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(CDS_STRAT, nullable = False) custodian = db.Column(db.String(12), default='SGFCM', nullable=False) cashaccount = db.Column(db.String(10), default='SGNSCLMASW', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'counterparty'}, nullable = False) trade_date = db.Column(db.Date, nullable = False) settle_date = db.Column(db.Date, nullable = False) cusip = db.Column(db.String(9), info={'validators': Length(9,9), 'filters': [lambda x: x or None,], 'trim': True}) isin = db.Column(db.String(12), info={'validators': Length(12, 12), 'filters': [lambda x: x or None,], 'trim': True}) identifier = db.Column(db.String(12), info={'filters': [lambda x: x or None,], 'trim': True}) description = db.Column(db.String(32), nullable = False, info={'trim': True}) transation_indicator = db.Column(REPO_TYPE) faceamount = db.Column(db.Float, nullable = False) price = db.Column(db.Float, nullable = False) currency = db.Column(CCY, nullable = False) expiration_date = db.Column(db.Date) weighted_amount = db.Column(db.Float) haircut = db.Column(db.Float) repo_rate = db.Column(db.Float, nullable = False) call_notice = db.Column(CALL_NOTICE) daycount = db.Column(DAY_COUNT) ticket = db.Column(db.String, info={'form_field_class': FileField}) __table__args = (db.CheckConstraint("haircut is not NULL and weighted_amount is NULL) or " \ "haircut is NULL and weighted_amount is NOT NULL)"), db.CheckConstraint("cusip is NOT NULL or isin is NOT NULL")) class SwaptionDeal(db.Model): __tablename__ = 'swaptions' id = db.Column('id', db.Integer, primary_key=True) dealid = db.Column(db.String(28)) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(SWAPTION_STRAT, nullable = False) custodian = db.Column(db.String(12), default='NONE', nullable=False) cashaccount = db.Column(db.String(10), default='SGNSCLMASW', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'counterparty'}, nullable = False) trade_date = db.Column(db.Date, nullable = False) settle_date = db.Column(db.Date, nullable = False) buysell = db.Column(db.Boolean, nullable = False, info={'choices':[(0, 'sell'), (1, 'buy')], 'coerce': lambda x: bool(int(x)) \ if x is not None else x}) notional = db.Column(db.Float, nullable = False) swaption_type = db.Column(SWAPTION_TYPE, nullable = False) strike = db.Column(db.Float, nullable = False) price = db.Column(db.Float, nullable = False) expiration_date = db.Column(db.Date, nullable = False) initial_margin_percentage = db.Column(db.Float) index_ref = db.Column(db.Float) security_id = db.Column(db.String(12), nullable = False) security_desc = db.Column(db.String(32), nullable = False) fixed_rate = db.Column(db.Float, nullable = False) maturity = db.Column(db.Date, nullable = False) currency = db.Column(CCY, nullable = False) counterparty = db.relationship(Counterparties) BaseModelForm = model_form_factory(Form) class ModelForm(BaseModelForm): @classmethod def get_session(self): return db.session