diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/Dawn/dawn.py | 61 | ||||
| -rw-r--r-- | python/Dawn/load_cp.py | 36 | ||||
| -rw-r--r-- | python/Dawn/models.py | 9 |
3 files changed, 62 insertions, 44 deletions
diff --git a/python/Dawn/dawn.py b/python/Dawn/dawn.py index abbb1407..98ff330f 100644 --- a/python/Dawn/dawn.py +++ b/python/Dawn/dawn.py @@ -1,5 +1,8 @@ from flask import Flask, request, render_template from models import db, ModelForm, BondDeal, Counterparties +from sqlalchemy.sql import text +from sqlalchemy import create_engine +import pandas as pd app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://dawn_user@debian/dawndb' @@ -10,13 +13,46 @@ def list_counterparties(): return Counterparties.query.order_by('name').\ with_entities(Counterparties.code, Counterparties.name) -# Base.metadata.create_all(engine) +def load_counterparties(): + counterparties = pd.read_csv("/home/share/CorpCDOs/data/counterparties.csv") + counterparties[['city', 'state']] = counterparties.Location.str.split(", ", expand=True) + counterparties.drop(['Location', 'Valuation Contact4', 'Valuation Contact4 Email'], axis=1, inplace=True) + counterparties.rename(columns ={'CODE': 'code', + 'DTC Number': 'dtc_number', + 'Email1': 'sales_email', + 'FIRM': 'name', + 'Phone': 'sales_phone', + 'Sales Contact': 'sales_contact', + 'Valuation Contact1': 'valuation_contact1', + 'Valuation Contact1 Email': 'valuation_email1', + 'Valuation Contact2': 'valuation_contact2', + 'Valuation Contact2 Email': 'valuation_email2', + 'Valuation Contact3': 'valuation_contact3', + 'Valuation Contact3 Email': 'valuation_email3', + 'Valuation Note': 'notes'}, inplace=True) + engine = create_engine('postgresql://dawn_user@debian/dawndb') + counterparties.to_sql('counterparties', engine, if_exists='append', index=False) + +def add_triggers(): + engine = create_engine('postgresql://dawn_user@debian/dawndb') + engine.execute(text(""" + CREATE OR REPLACE FUNCTION mydealid() + RETURNS TRIGGER AS $$ + BEGIN + UPDATE bonds SET dealid = 'SC_' ||left(asset_class::text,3)||id WHERE bonds.id = NEW.id; + RETURN NEW; + END; + $$ language plpgsql""")) + engine.execute(text("""CREATE TRIGGER dealid + AFTER INSERT ON bonds + FOR EACH ROW + EXECUTE PROCEDURE mydealid()""")) class BondForm(ModelForm): class Meta: model = BondDeal include_foreign_keys = True - exclude = ['dealid'] #we generate it with a trigger at the server level + exclude = ['dealid', 'lastupdate'] #we generate it with a trigger at the server level @app.route('/', methods=['GET', 'POST']) def trade_entry(): @@ -37,12 +73,27 @@ def trade_entry(): @app.route('/trade/<int:tradeid>', methods=['GET', 'POST']) def trade_update(tradeid): - bond_form = BondForm(obj = BondDeal().query.get(tradeid)) + trade = BondDeal() + bond_form = BondForm(obj = trade.query.get(tradeid)) bond_form.counterparty.choices = list_counterparties() + if bond_form.is_submitted(): + if bond_form.validate(): + bond_form.populate_obj(trade) + return redirect(url_for('list_trades')) + else: + return "something wrong!" return render_template("trade_entry.html", form=bond_form) +@app.route('/blotter/') +def list_trades(): + trade = BondDeal() + trade_list = trade.query.order_by(trade.trade_date) + return render_template('blotter.html', trades=trade_list.all()) + if __name__=="__main__": - #db.drop_all(app=app) - #db.create_all(app=app) db.init_app(app) + # db.drop_all(app=app) + # db.create_all(app=app) + # add_triggers() + # load_counterparties() app.run(debug=True) diff --git a/python/Dawn/load_cp.py b/python/Dawn/load_cp.py deleted file mode 100644 index 07f49060..00000000 --- a/python/Dawn/load_cp.py +++ /dev/null @@ -1,36 +0,0 @@ -import csv -from sqlalchemy import bindparam, create_engine, MetaData -from sqlalchemy.orm import sessionmaker - -engine = create_engine('postgresql://dawn_user@debian/dawndb') -meta = MetaData() -meta.reflect(bind=engine) -counterparties = meta.tables['counterparties'] -ins = counterparties.insert().values(code = bindparam('CODE'), - name = bindparam('FIRM'), - dtc_number = bindparam('DTC Number'), - sales_contact = bindparam('Sales Contact'), - sales_email = bindparam('Email1'), - sales_phone = bindparam('Phone'), - valuation_contact1 = bindparam('Valuation Contact1'), - valuation_email1 = bindparam('Valuation Contact1 Email'), - valuation_contact2 = bindparam('Valuation Contact2'), - valuation_email2 = bindparam('Valuation Contact2 Email'), - valuation_contact3 = bindparam('Valuation Contact3'), - valuation_email3 = bindparam('Valuation Contact3 Email'), - notes = bindparam('Valuation Note')) - - -with open("/home/share/CorpCDOs/data/counterparties.csv") as fh: - csvreader = csv.DictReader(fh) - to_insert = [] - for line in csvreader: - for k, v in line.items(): - if v=="": - line[k] = None - if any(line.values()): - line['city'] = line['Location'].split(", ")[0] - line['state'] = line['Location'].split(", ")[1] - to_insert.append(line) - conn = engine.connect() -conn.execute(ins, to_insert) diff --git a/python/Dawn/models.py b/python/Dawn/models.py index b9b6323c..dc8ff7d4 100644 --- a/python/Dawn/models.py +++ b/python/Dawn/models.py @@ -40,6 +40,7 @@ BOND_STRAT = ENUM('M_STR_MAV', 'M_STR_SMEZZ', 'CSO_TRANCH', 'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW', name='bond_strat') ASSET_CLASS = ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared', name='asset_class') +ACTION = ENUM('NEW', 'UPDATE', 'CANCEL', name='action') def myticket(ctx): return "{0}.{1}".format(ctx.current_parameters['dealid'], @@ -49,14 +50,16 @@ 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), nullable=False) - cashaccount = db.Column(db.String(10), nullable=False) + custodian = db.Column(db.String(12), default='BAC', nullable=False) + cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False) counterparty = db.Column(db.String, db.ForeignKey("counterparties.code"), info={'choices': [(None, None)]}) trade_date = db.Column(db.Date, nullable = False, default = datetime.date.today) settle_date = db.Column(db.Date, nullable = False, default = - lambda : datetime.date.today()+datetime.timedelta(3)) + lambda : datetime.date.today() + datetime.timedelta(3)) cusip = db.Column(db.String(9), info={'validators': Length(9,9)}) isin = db.Column(db.String(12), info={'validator': Length(12, 12)}) description = db.Column(db.String(32)) |