sql: view: invoice: fix price calculation for special users

the price for special users is supposed to be the average of
the restock price. After this commit only prices, that happened
before the invoice entry are taken into account, so that its
possible to recalculate the same invoice at a later point.

1 files changed, 4 insertions, 3 deletions

diff --git a/sql/views.sql b/sql/views.sql
index d40ac32..4c0a56b 100644
--- a/sql/views.sql
+++ b/sql/views.sql
@@ -5,9 +5,10 @@ CREATE VIEW IF NOT EXISTS invoice AS
 	SELECT user, timestamp, id AS productid, name AS productname,
 		CASE
 			WHEN user < 0 THEN
-				(SELECT price
-					FROM purchaseprices
-					WHERE purchaseprices.product = id)
+				(SELECT SUM(price * amount) / SUM(amount)
+					FROM restock
+					WHERE restock.product = id AND restock.timestamp <= sales.timestamp
+				)
 			else
 				(SELECT
 					CASE