diff options
| -rw-r--r-- | python/Dawn/models.py | 4 | ||||
| -rw-r--r-- | python/Dawn/views.py | 3 | ||||
| -rw-r--r-- | sql/dawn.sql | 35 |
3 files changed, 37 insertions, 5 deletions
diff --git a/python/Dawn/models.py b/python/Dawn/models.py index 24dc8b37..70a3cce7 100644 --- a/python/Dawn/models.py +++ b/python/Dawn/models.py @@ -171,6 +171,8 @@ class CDSDeal(db.Model): upfront_settle_date = db.Column(db.Date, nullable=False) initial_margin_percentage = db.Column(db.Float) swap_type = db.Column(SWAP_TYPE, nullable=False) + orig_attach = db.Column(db.SmallInteger, info={'min': 0, 'max': 100}) + orig_detach = db.Column(db.SmallInteger, info={'min': 0, 'max': 100}) attach = db.Column(db.Float, info={'min': 0., 'max': 100.}) detach = db.Column(db.Float, info={'min': 0., 'max': 100.}) corr_attach = db.Column(db.Float, info={'min': 0., 'max': 1.}) @@ -188,7 +190,7 @@ class CDSDeal(db.Model): termination_counterparty = db.relationship(Counterparties, foreign_keys=[termination_cp]) fcm_account = db.relationship(Accounts, foreign_keys=[account_code]) __table_args__ = (db.CheckConstraint("swap_type!='CD_INDEX_TRANCHE' or " \ - "(attach is not NULL and detach is not NULL AND " \ + "(orig_attach is not NULL and orig_detach is not NULL AND " \ "clearing_facility is NULL)"),) class RepoDeal(db.Model): diff --git a/python/Dawn/views.py b/python/Dawn/views.py index eebb2671..086d7d99 100644 --- a/python/Dawn/views.py +++ b/python/Dawn/views.py @@ -102,7 +102,8 @@ class CDSForm(ModelForm): model = CDSDeal include_foreign_keys = True exclude = ['dealid', 'lastupdate', 'termination_amount', - 'termination_cp', 'termination_date', 'custodian', 'cashaccount'] + 'termination_cp', 'termination_date', 'custodian', + 'cashaccount', 'attach', 'detach'] class SwaptionForm(ModelForm): diff --git a/sql/dawn.sql b/sql/dawn.sql index e3bd7640..161c734a 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -133,6 +133,8 @@ CREATE TABLE cds(id serial primary key, upfront float NOT NULL, upfront_settle_date date NOT NULL, swap_type swap_type NOT NULL, + orig_attach smallint, + orig_detach smallint, attach float, detach float, clearing_facility varchar(12) DEFAULT NULL, @@ -145,9 +147,11 @@ CREATE TABLE cds(id serial primary key, corr_attach float DEFAULT NULL, corr_detach float DEFAULT NULL, account_code varchar(5) NOT NULL, - CONSTRAINT tranche_check CHECK (swap_type != 'CD_INDEX_TRANCHE' OR - (attach IS NOT NULL AND detach IS NOT NULL AND - clearing_facility IS NULL)) + CONSTRAINT tranche_check CHECK (( + swap_type IN ('CD_INDEX_TRANCHE', 'BESPOKE') AND + (orig_attach IS NOT NULL AND orig_detach IS NOT NULL AND clearing_facility IS NULL)) + OR (swap_type='CD_INDEX' AND orig_attach IS NULL AND orig_detach IS NULL AND clearing_facility='ICE-CREDIT') + OR (swap_type='ABS_CDS' AND orig_attach IS NULL AND orig_detach IS NULL AND clearing_faciliy IS NULL)) ); ALTER TABLE cds OWNER TO dawn_user; @@ -290,11 +294,35 @@ identifier = COALESCE(identifier, cusip, isin)', NEW.id); END; $$ language plpgsql; +CREATE OR REPLACE function update_attach() +RETURNS TRIGGER AS $$ +DECLARE + factor float; + cum_loss float; +BEGIN +IF NEW.orig_attach IS NULL AND NEW.orig_detach IS NULL THEN +RETURN NEW; +ELSE +SELECT indexfactor, cumulativeloss INTO factor, cum_loss + FROM index_version WHERE redindexcode=NEW.security_id; +UPDATE cds + SET attach=100*LEAST(GREATEST((NEW.orig_attach - cum_loss)/factor, 0), 1), + detach=100*LEAST(GREATEST((NEW.orig_detach - cum_loss)/factor, 0), 1) +WHERE id=NEW.id; +RETURN NEW; +END IF; +END +$$ LANGUAGE plpgsql; + + create trigger dealid after insert on capfloors for each row execute procedure auto_dealid() ; +CREATE TRIGGER cds_attach AFTER INSERT OR UPDATE OF orig_attach, orig_detach ON cds +FOR EACH ROW EXECUTE PROCEDURE update_attach(); + CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); @@ -903,6 +931,7 @@ RETURN (newdetach-newattach)/(detach-attach)*index_factor; END; $$ LANGUAGE plpgsql; + CREATE OR REPLACE function fx_rate(p_date date) RETURNS TABLE(currency currency, fxrate float) AS $$ BEGIN RETURN QUERY SELECT unnest(Array['USD', 'EUR', 'CAD'])::currency, |