1 files changed, 58 insertions, 1 deletions

diff --git a/sql/dawn.sql b/sql/dawn.sql
index 5b79b887..2a35bfc8 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -4717,4 +4717,61 @@ CREATE TABLE irs_tickets (
 	cclear_usiuti text NULL,
 	account text NULL,
 	client_fcm text NULL
-);
\ No newline at end of file
+);
+
+
+CREATE OR REPLACE VIEW admin_forward_valuations AS
+SELECT invccy, invid, periodenddate, fund, port, strat, sum(endlocalmv) as endlocalmv
+FROM valuation_reports vr
+GROUP BY invccy, invid, periodenddate, fund, port, strat
+HAVING invid IN ('EURF', 'USDF');
+
+CREATE OR REPLACE VIEW admin_otc_valuations AS SELECT invccy, custacctname, port, strat, periodenddate, fund, 
+       sum(
+          CASE
+            WHEN endbookmv = 0 OR endlocalmv = 0 THEN 0
+            ELSE endbooknav / (endbookmv / endlocalmv)
+          END
+       ) as endlocalmv
+FROM valuation_reports vr 
+WHERE invid NOT IN ('EUR', 'EURF') AND endbooknav IS NOT NULL
+GROUP BY invccy, custacctname, port, strat, periodenddate, fund
+HAVING invccy IN ('EUR', 'USD') 
+AND custacctname IN (SELECT cash_account FROM accounts2 WHERE account_type = 'Cash' AND active);
+
+CREATE OR REPLACE VIEW public.admin_cash_valuations
+AS SELECT valuation_reports.invid,
+    valuation_reports.invccy,
+    valuation_reports.fund,
+    valuation_reports.port,
+    valuation_reports.strat,
+    valuation_reports.periodenddate,
+    valuation_reports.custacctname,
+    sum(valuation_reports.endqty) AS endlocalmv
+   FROM valuation_reports
+  GROUP BY valuation_reports.invccy, valuation_reports.invid, valuation_reports.fund, valuation_reports.port, valuation_reports.strat, valuation_reports.periodenddate, valuation_reports.custacctname
+ HAVING valuation_reports.invid = ANY (ARRAY['EUR'::text, 'USD'::text]);
+
+
+CREATE VIEW eur_fx_valuations AS
+SELECT periodenddate, fund, port, forward_valuations, otc_valuations, cash_valuations
+FROM (
+  SELECT periodenddate, fund, port, sum(endlocalmv) AS forward_valuations
+  FROM admin_forward_valuations
+  WHERE invid = 'EURF'
+  GROUP BY periodenddate, fund, port
+) f
+FULL OUTER JOIN (
+  SELECT periodenddate, fund, port, sum(endlocalmv) AS otc_valuations
+  FROM admin_otc_valuations
+  WHERE invccy = 'EUR'
+  GROUP BY periodenddate, fund, port
+) otc
+USING (periodenddate, fund, port)
+FULL OUTER JOIN (
+  SELECT periodenddate, fund, port, sum(endlocalmv) AS cash_valuations
+  FROM admin_cash_valuations
+  WHERE invid = 'EUR'
+  GROUP BY periodenddate, fund, port
+) c
+USING (periodenddate, fund, port);