From 198c088c5aed99449ffe406e54a67067b7752b63 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Wed, 28 Oct 2015 00:19:23 +0100 Subject: 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. --- sql/views.sql | 7 ++++--- 1 file 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 -- cgit v1.2.3