From afe377a9b10b5c1b2badc74d4b9b636e7c4d0cbf Mon Sep 17 00:00:00 2001
From: Pacien TRAN-GIRARD
Date: Tue, 27 Jan 2015 18:01:45 +0100
Subject: Add E-R model and SQL bootstrap scripts

---
 res/sql/views/pg_accounts.sql | 31 +++++++++++++++++++++++++++++++
 1 file changed, 31 insertions(+)
 create mode 100644 res/sql/views/pg_accounts.sql

(limited to 'res/sql/views/pg_accounts.sql')

diff --git a/res/sql/views/pg_accounts.sql b/res/sql/views/pg_accounts.sql
new file mode 100644
index 0000000..400d38f
--- /dev/null
+++ b/res/sql/views/pg_accounts.sql
@@ -0,0 +1,31 @@
+CREATE OR REPLACE VIEW accounts AS
+
+  SELECT
+    users.uuid                                                       AS user_uuid,
+    COALESCE(transactions.balance, 0)                                AS balance,
+    COALESCE(sales.open_bids, 0)                                     AS open_bids,
+    COALESCE(transactions.balance, 0) - COALESCE(sales.open_bids, 0) AS equity
+
+  FROM users
+
+    LEFT OUTER JOIN (
+                      SELECT
+                        user_uuid,
+                        SUM(transactions.amount) AS balance
+
+                      FROM transactions
+                      WHERE transaction_date < current_datetime()
+                      GROUP BY user_uuid
+                    ) transactions
+      ON transactions.user_uuid = users.uuid
+
+    LEFT OUTER JOIN (
+                      SELECT
+                        best_bidder_uuid,
+                        SUM(sales.best_offer + sales.charges) AS open_bids
+
+                      FROM sales
+                      WHERE end_date > current_datetime()
+                      GROUP BY best_bidder_uuid
+                    ) sales
+      ON sales.best_bidder_uuid = users.uuid;
-- 
cgit v1.2.3