from flask import (request, render_template, redirect, url_for, send_from_directory, send_file, g, jsonify) from .models import ModelForm, BondDeal, CDSDeal, SwaptionDeal, Counterparties from sqlalchemy.exc import IntegrityError from wtforms.fields import BooleanField import pandas as pd from pandas.tseries.offsets import CustomBusinessDay from pandas.tseries.holiday import get_calendar, HolidayCalendarFactory, GoodFriday import os import datetime import redis from .utils import load_counterparties, bump_rev, simple_serialize from PyPDF2 import PdfFileMerger from io import BytesIO from . import app import socket import psycopg2 fed_cal = get_calendar('USFederalHolidayCalendar') bond_cal = HolidayCalendarFactory('BondCalendar', fed_cal, GoodFriday) bus_day = CustomBusinessDay(calendar=bond_cal()) def cp_choices(): return Counterparties.query.order_by('name').\ with_entities(Counterparties.code, Counterparties.name) def get_queue(): q = getattr(g, 'queue', None) if q is None: hostname = socket.gethostname() if hostname == 'debian': q = g.queue = redis.Redis(unix_socket_path='/var/run/redis/redis.sock') elif hostname =='gomez': q = g.queue = redis.Redis(host='debian') return q def get_db(): db = getattr(g, '_database', None) if db is None: db = g._database = psycopg2.connect(database="serenitasdb", user="serenitas_user", host="debian") return db @app.teardown_appcontext def close_connection(exception): db = getattr(g, '_database', None) if db is not None: db.close() class CounterpartyForm(ModelForm): class Meta: model = Counterparties include_primary_keys = True class BondForm(ModelForm): upload_globeop = BooleanField(label="Upload to globeop?") class Meta: model = BondDeal include_foreign_keys = True exclude = ['dealid', 'lastupdate', #we generate it with a trigger at the server level 'principal_payment', 'accrued_payment'] class CDSForm(ModelForm): upload_globeop = BooleanField(label="Upload to globeop?") class Meta: model = CDSDeal include_foreign_keys = True exclude = ['dealid', 'lastupdate'] class SwaptionForm(ModelForm): upload_globeop = BooleanField(label="Upload to globeop?") class Meta: model = SwaptionDeal include_foreign_keys = True exclude = ['dealid', 'lastupdate'] def get_deal(kind): if kind == 'cds': return CDSDeal elif kind == 'bond': return BondDeal elif kind == 'swaption': return SwaptionDeal else: raise RuntimeError('Unknown Deal type') def get_form(trade, kind): if kind == 'cds': Form = CDSForm elif kind == 'bond': Form = BondForm elif kind == 'swaption': Form = SwaptionForm else: raise RuntimeError('Unknown Deal type') if trade.id: form = Form(obj = trade) else: today = pd.datetime.today() if kind == 'cds': tomorrow = today + pd.DateOffset(1) form = Form(trade_date = today.date(), effective_date = tomorrow.date(), upfront_settle_date = today.date() + 3 * bus_day) else: form = Form(trade_date = today.date(), settle_date = today.date() + 3 * bus_day) #add extra empty fields empty_choice = (None, '') for attr in ['folder', 'buysell', 'asset_class', 'swaption_type']: try: dropdown = getattr(form, attr) if dropdown.choices[0] != empty_choice: dropdown.choices.insert(0, empty_choice) except AttributeError: continue return form def get_trade(tradeid, kind): Deal = get_deal(kind) return Deal.query.get(tradeid) if tradeid else Deal() def save_ticket(trade, old_ticket_name): if trade.ticket.filename: if old_ticket_name: new_name = bump_rev(old_ticket_name) else: new_name = "{0} {1}.pdf".format(str(trade.trade_date), trade.description) trade.ticket.save(os.path.join(app.config['TICKETS_FOLDER'], new_name)) trade.ticket = new_name else: trade.ticket = old_ticket_name @app.route('/trades//', methods = ['GET', 'POST']) @app.route('/trades//', defaults = {'tradeid': None}, methods = ['GET', 'POST']) @app.route('/trades/', defaults = {'tradeid': None, 'kind': 'bond'}, methods = ['GET', 'POST']) def trade_manage(tradeid, kind): trade = get_trade(tradeid, kind) form = get_form(trade, kind) if kind == 'bond': old_ticket_name = trade.ticket form.cp_code.choices = form.cp_code.choices + list(cp_choices()) if form.validate_on_submit(): form.populate_obj(trade) session = form.get_session() if not tradeid: session.add(trade) if kind == 'bond': save_ticket(trade, old_ticket_name) try: session.commit() except IntegrityError as e: app.logger.error(e) session.rollback() return render_template('trade_entry.html', form = form, action_url = url_for('trade_manage', tradeid = tradeid, kind = kind)) else: if form.upload_globeop.data: q = get_queue() q.rpush('{0}_trades'.format(kind), simple_serialize(trade)) return redirect(url_for('list_trades', kind=kind)) return render_template('trade_entry.html', form=form, action_url = url_for('trade_manage', tradeid = tradeid, kind = kind)) @app.route('/', defaults = {'kind': 'bond'}) @app.route('/blotter/') @app.route('/blotter/', defaults = {'kind': 'bond'}) def list_trades(kind): Deal = get_deal(kind) trade_list = Deal.query.order_by(Deal.trade_date.desc(), Deal.id.desc()) return render_template('{}_blotter.html'.format(kind), trades=trade_list.all()) @app.route('/tickets/') def download_ticket(tradeid): trade = BondDeal.query.get(tradeid) pdf = PdfFileMerger() pdf.append(os.path.join(app.config['TICKETS_FOLDER'], trade.ticket)) pdf.append(os.path.join(app.config['CP_FOLDER'], trade.counterparty.instructions)) fh = BytesIO() pdf.write(fh) pdf.close() fh.seek(0) return send_file(fh, mimetype='application/pdf') @app.route('/counterparties/', methods = ['GET']) @app.route('/counterparties/', defaults = {'instr': None}, methods = ['GET']) def list_counterparties(instr): if instr: return send_from_directory(filename = instr, directory = app.config['CP_FOLDER'], mimetype='application/pdf') else: cp_list = Counterparties.query.order_by(Counterparties.name) return render_template('counterparties.html', counterparties = cp_list.all()) @app.route('/edit_cp/', methods = ['GET', 'POST']) @app.route('/edit_cp/', defaults = {'cpcode': None}, methods = ['GET', 'POST']) def edit_counterparty(cpcode): if cpcode: cp = Counterparties.query.get(cpcode) cp_form = CounterpartyForm(obj = cp) else: cp = Counterparties() cp_form = CounterpartyForm() old_instructions = cp.instructions or None if cp_form.validate_on_submit(): cp_form.populate_obj(cp) session = cp_form.get_session() if not cpcode: session.add(cp) instructions = cp.instructions if instructions.filename == '': cp.instructions = old_instructions else: cp.instructions = cp.name + '.pdf' instructions.save(os.path.join(app.config['CP_FOLDER'], cp.instructions)) session.commit() return redirect(url_for('list_counterparties')) else: return render_template('edit_cp.html', form=cp_form, code=cpcode) @app.route('/_ajax', methods = ['GET']) def get_bbg_id(): bbg_id = request.args.get('bbg_id') try: _, indextype, _, series, tenor = bbg_id.split() except ValueError: return "not a valid bloomberg description", 400 indextype = indextype[:2] tenor = tenor[:-1] + 'yr' series = int(series[1:]) sqlstr1 = "SELECT * FROM index_redcode(%s::index_type, %s::smallint, %s)" sqlstr2 = "SELECT maturity, coupon FROM index_maturity WHERE index=%s and series=%s and tenor=%s" db = get_db() with db.cursor() as c: print(indextype, series) c.execute(sqlstr1, (indextype, series, pd.datetime.today().date())) (redcode,) = c.fetchone() c.execute(sqlstr2, (indextype, series, tenor)) maturity, coupon = c.fetchone() return jsonify({'maturity': maturity.strftime('%Y-%m-%d'), 'redcode': redcode, 'coupon': coupon})