1 files changed, 47 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index cadd077d..23847c51 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -861,6 +861,53 @@ END IF;
 END;
 $$ LANGUAGE plpgsql;
 
+CREATE OR REPLACE function list_cds_marks_pre(p_date date, strat cds_strat DEFAULT NULL::cds_strat, fund fund DEFAULT 'SERCGMAST'::fund)
+RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type,
+              p_series smallint,
+              p_version smallint, tenor tenor, maturity date, notional float,
+              factor float, coupon float, duration float, theta float, price float,
+              closespread float, clean_nav float, accrued float) AS $$
+DECLARE
+  days integer;
+  eur_fx float;
+  params text;
+  sqlquery text;
+  and_clause text;
+BEGIN
+days:=days_accrued(p_date);
+SELECT DISTINCT ON (date) eurusd INTO eur_fx FROM fx WHERE date BETWEEN p_date - INTERVAL '3 DAYS' AND p_date;
+IF strat IS NOT NULL THEN
+   params := '$1, $5, $2';
+   and_clause := 'AND strat = ltrim($5::text, ''SER_'')::strategy';
+ELSE
+   params := '$1, NULL, $2';
+   and_clause := '';
+END IF;
+
+sqlquery := format(
+'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact
+              FROM list_cds_positions(%s) a
+              LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)),
+index_price AS (SELECT DISTINCT ON (index, series, version, d.tenor) index, series, version, d.tenor, closeprice, d.duration2, d.closespread, d.theta2
+                FROM index_quotes d WHERE date BETWEEN $1 - INTERVAL ''3 days'' AND $1 ORDER BY index, series, version, d.tenor, date desc)
+SELECT
+    temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor,
+    temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100,
+    index_price.duration2, index_price.theta2, index_price.closeprice, index_price.closespread,
+    (1.-index_price.closeprice/100.) * temp.notional * temp.fact *
+    (CASE WHEN temp.currency = ''EUR'' THEN $3 ELSE 1 END),
+    -temp.notional * temp.fixed_rate/100. * temp.fact * $4 / 360 *
+    (CASE WHEN temp.currency = ''EUR'' THEN $3 ELSE 1 END)
+FROM temp
+LEFT JOIN index_price USING (index, series, version, tenor)', params);
+IF strat IS NOT NULL THEN
+   RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days, strat;
+ELSE
+    RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days;
+END IF;
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE OR REPLACE function list_cds_marks(p_date date, strat cds_strat DEFAULT NULL::cds_strat)
 RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type,
               p_series smallint,