4 files changed, 143 insertions, 0 deletions
diff --git a/res/sql/views/orcl_accounts.sql b/res/sql/views/orcl_accounts.sql
new file mode 100644
index 0000000..400d38f
--- /dev/null
+++ b/res/sql/views/orcl_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;
diff --git a/res/sql/views/orcl_sales.sql b/res/sql/views/orcl_sales.sql
new file mode 100644
index 0000000..851d17e
--- /dev/null
+++ b/res/sql/views/orcl_sales.sql
@@ -0,0 +1,49 @@
+CREATE OR REPLACE VIEW sales AS
+  SELECT
+    items.uuid                                                             AS item_uuid,
+    items.user_uuid                                                        AS seller_uuid,
+    items.start_date                                                       AS start_date,
+    items.end_date                                                         AS end_date,
+    best_bids.uuid                                                         AS best_bid_uuid,
+    best_bids.user_uuid                                                    AS best_bidder_uuid,
+    best_bids.offer                                                        AS best_offer,
+    COALESCE(charges.fee, 0) + COALESCE(charges.rate, 0) * best_bids.offer AS charges
+  FROM items
+    LEFT OUTER JOIN (
+                      SELECT
+                        DISTINCT
+                        item_uuid,
+                        first_value(offer)
+                        OVER (
+                          PARTITION BY item_uuid
+                          ORDER BY offer DESC
+                          ) offer,
+                        first_value(uuid)
+                        OVER (
+                          PARTITION BY item_uuid
+                          ORDER BY offer DESC
+                          ) uuid,
+                        first_value(user_uuid)
+                        OVER (
+                          PARTITION BY item_uuid
+                          ORDER BY offer DESC
+                          ) user_uuid
+                      FROM bids
+                    ) best_bids
+      ON best_bids.item_uuid = items.uuid
+    LEFT OUTER JOIN (
+                      SELECT
+                        start_date,
+                        end_date,
+                        SUM(fee)  AS fee,
+                        SUM(rate) AS rate
+                      FROM charges
+                      GROUP BY charges.start_date, charges.end_date
+                    ) charges
+      ON items.end_date BETWEEN charges.start_date AND charges.end_date;
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;
diff --git a/res/sql/views/pg_sales.sql b/res/sql/views/pg_sales.sql
new file mode 100644
index 0000000..f36d8e7
--- /dev/null
+++ b/res/sql/views/pg_sales.sql
@@ -0,0 +1,32 @@
+CREATE OR REPLACE VIEW sales AS
+  SELECT
+    items.uuid                                                             AS item_uuid,
+    items.user_uuid                                                        AS seller_uuid,
+    items.start_date                                                       AS start_date,
+    items.end_date                                                         AS end_date,
+    best_bids.uuid                                                         AS best_bid_uuid,
+    best_bids.user_uuid                                                    AS best_bidder_uuid,
+    best_bids.offer                                                        AS best_offer,
+    COALESCE(charges.fee, 0) + COALESCE(charges.rate, 0) * best_bids.offer AS charges
+  FROM items
+    LEFT OUTER JOIN (
+                      SELECT DISTINCT ON (item_uuid) *
+                      FROM bids
+                      ORDER BY item_uuid, offer DESC
+                    ) best_bids
+      ON best_bids.item_uuid = items.uuid
+    LEFT OUTER JOIN (
+                      SELECT
+                        start_date,
+                        end_date,
+                        SUM(fee)  AS fee,
+                        SUM(rate) AS rate
+                      FROM charges
+                      GROUP BY charges.start_date, charges.end_date
+                    ) charges
+      ON items.end_date BETWEEN charges.start_date AND charges.end_date;

diff --git a/res/sql/views/orcl_accounts.sql b/res/sql/views/orcl_accounts.sql new file mode 100644 index 0000000..400d38f --- /dev/null +++ b/res/sql/views/orcl_accounts.sql
@@ -0,0 +1,31 @@
	1	CREATE OR REPLACE VIEW accounts AS
	2
	3	SELECT
	4	users.uuid AS user_uuid,
	5	COALESCE(transactions.balance, 0) AS balance,
	6	COALESCE(sales.open_bids, 0) AS open_bids,
	7	COALESCE(transactions.balance, 0) - COALESCE(sales.open_bids, 0) AS equity
	8
	9	FROM users
	10
	11	LEFT OUTER JOIN (
	12	SELECT
	13	user_uuid,
	14	SUM(transactions.amount) AS balance
	15
	16	FROM transactions
	17	WHERE transaction_date < current_datetime()
	18	GROUP BY user_uuid
	19	) transactions
	20	ON transactions.user_uuid = users.uuid
	21
	22	LEFT OUTER JOIN (
	23	SELECT
	24	best_bidder_uuid,
	25	SUM(sales.best_offer + sales.charges) AS open_bids
	26
	27	FROM sales
	28	WHERE end_date > current_datetime()
	29	GROUP BY best_bidder_uuid
	30	) sales
	31	ON sales.best_bidder_uuid = users.uuid;


diff --git a/res/sql/views/orcl_sales.sql b/res/sql/views/orcl_sales.sql new file mode 100644 index 0000000..851d17e --- /dev/null +++ b/res/sql/views/orcl_sales.sql
@@ -0,0 +1,49 @@
	1	CREATE OR REPLACE VIEW sales AS
	2
	3	SELECT
	4	items.uuid AS item_uuid,
	5	items.user_uuid AS seller_uuid,
	6	items.start_date AS start_date,
	7	items.end_date AS end_date,
	8	best_bids.uuid AS best_bid_uuid,
	9	best_bids.user_uuid AS best_bidder_uuid,
	10	best_bids.offer AS best_offer,
	11	COALESCE(charges.fee, 0) + COALESCE(charges.rate, 0) * best_bids.offer AS charges
	12
	13	FROM items
	14
	15	LEFT OUTER JOIN (
	16	SELECT
	17	DISTINCT
	18	item_uuid,
	19	first_value(offer)
	20	OVER (
	21	PARTITION BY item_uuid
	22	ORDER BY offer DESC
	23	) offer,
	24	first_value(uuid)
	25	OVER (
	26	PARTITION BY item_uuid
	27	ORDER BY offer DESC
	28	) uuid,
	29	first_value(user_uuid)
	30	OVER (
	31	PARTITION BY item_uuid
	32	ORDER BY offer DESC
	33	) user_uuid
	34
	35	FROM bids
	36	) best_bids
	37	ON best_bids.item_uuid = items.uuid
	38
	39	LEFT OUTER JOIN (
	40	SELECT
	41	start_date,
	42	end_date,
	43	SUM(fee) AS fee,
	44	SUM(rate) AS rate
	45
	46	FROM charges
	47	GROUP BY charges.start_date, charges.end_date
	48	) charges
	49	ON items.end_date BETWEEN charges.start_date AND charges.end_date;


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 @@
	1	CREATE OR REPLACE VIEW accounts AS
	2
	3	SELECT
	4	users.uuid AS user_uuid,
	5	COALESCE(transactions.balance, 0) AS balance,
	6	COALESCE(sales.open_bids, 0) AS open_bids,
	7	COALESCE(transactions.balance, 0) - COALESCE(sales.open_bids, 0) AS equity
	8
	9	FROM users
	10
	11	LEFT OUTER JOIN (
	12	SELECT
	13	user_uuid,
	14	SUM(transactions.amount) AS balance
	15
	16	FROM transactions
	17	WHERE transaction_date < current_datetime()
	18	GROUP BY user_uuid
	19	) transactions
	20	ON transactions.user_uuid = users.uuid
	21
	22	LEFT OUTER JOIN (
	23	SELECT
	24	best_bidder_uuid,
	25	SUM(sales.best_offer + sales.charges) AS open_bids
	26
	27	FROM sales
	28	WHERE end_date > current_datetime()
	29	GROUP BY best_bidder_uuid
	30	) sales
	31	ON sales.best_bidder_uuid = users.uuid;


diff --git a/res/sql/views/pg_sales.sql b/res/sql/views/pg_sales.sql new file mode 100644 index 0000000..f36d8e7 --- /dev/null +++ b/res/sql/views/pg_sales.sql
@@ -0,0 +1,32 @@
	1	CREATE OR REPLACE VIEW sales AS
	2
	3	SELECT
	4	items.uuid AS item_uuid,
	5	items.user_uuid AS seller_uuid,
	6	items.start_date AS start_date,
	7	items.end_date AS end_date,
	8	best_bids.uuid AS best_bid_uuid,
	9	best_bids.user_uuid AS best_bidder_uuid,
	10	best_bids.offer AS best_offer,
	11	COALESCE(charges.fee, 0) + COALESCE(charges.rate, 0) * best_bids.offer AS charges
	12
	13	FROM items
	14
	15	LEFT OUTER JOIN (
	16	SELECT DISTINCT ON (item_uuid) *
	17	FROM bids
	18	ORDER BY item_uuid, offer DESC
	19	) best_bids
	20	ON best_bids.item_uuid = items.uuid
	21
	22	LEFT OUTER JOIN (
	23	SELECT
	24	start_date,
	25	end_date,
	26	SUM(fee) AS fee,
	27	SUM(rate) AS rate
	28
	29	FROM charges
	30	GROUP BY charges.start_date, charges.end_date
	31	) charges
	32	ON items.end_date BETWEEN charges.start_date AND charges.end_date;