From 9a1fe41c15403064b50589258efd2bad397916b2 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Sun, 2 Feb 2014 20:06:43 +0100 Subject: database: do not use invoice view using the view results in sqlite not making use of the sales index. --- src/database/database.vala | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'src/database/database.vala') diff --git a/src/database/database.vala b/src/database/database.vala index 8c48588..ddc71d4 100644 --- a/src/database/database.vala +++ b/src/database/database.vala @@ -105,7 +105,7 @@ public class DataBase : Object { queries["userinfo"] = "SELECT firstname, lastname, email, gender, street, plz, city, pgp FROM users WHERE id = ?"; queries["userauth"] = "SELECT disabled, superuser FROM authentication WHERE user = ?"; queries["profit_by_product"] = "SELECT name, SUM(memberprice - (SELECT price FROM purchaseprices WHERE product = purch.product)) AS price FROM sales purch, prices, products WHERE purch.product = products.id AND purch.product = prices.product AND purch.user > 0 AND purch.timestamp > ? AND purch.timestamp < ? AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = purch.product AND valid_from < purch.timestamp ORDER BY valid_from DESC LIMIT 1) GROUP BY name ORDER BY price;"; - queries["invoice"] = "SELECT timestamp, productid, productname, price FROM invoice WHERE user = ? AND timestamp >= ? AND timestamp < ?;"; + queries["invoice"] = "SELECT timestamp, id AS productid, name AS productname, CASE WHEN user < 0 THEN (SELECT price FROM purchaseprices WHERE purchaseprices.product = id) else (SELECT CASE WHEN user=0 THEN guestprice else memberprice END FROM prices WHERE product = id AND valid_from <= timestamp ORDER BY valid_from DESC LIMIT 1) END AS price FROM sales INNER JOIN products ON sales.product = products.id WHERE user = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp"; queries["purchase_first"] = "SELECT timestamp FROM sales WHERE user = ? ORDER BY timestamp ASC LIMIT 1"; queries["purchase_last"] = "SELECT timestamp FROM sales WHERE user = ? ORDER BY timestamp DESC LIMIT 1"; queries["count_articles"] = "SELECT COUNT(*) FROM products"; @@ -122,7 +122,7 @@ public class DataBase : Object { queries["supplier_get"] = "SELECT id, name, postal_code, city, street, phone, website FROM supplier WHERE id = ?"; queries["supplier_add"] = "INSERT INTO supplier('name', 'postal_code', 'city', 'street', 'phone', 'website') VALUES (?, ?, ?, ?, ?, ?)"; queries["users_with_sales"] = "SELECT user FROM sales WHERE timestamp > ? AND timestamp < ? GROUP BY user"; - queries["user_invoice_sum"] = "SELECT SUM(price) FROM invoice WHERE user = ? AND timestamp > ? AND timestamp < ?"; + queries["user_invoice_sum"] = "SELECT SUM(CASE WHEN user < 0 THEN (SELECT price FROM purchaseprices WHERE purchaseprices.product = id) else (SELECT CASE WHEN user=0 THEN guestprice else memberprice END FROM prices WHERE product = id AND valid_from <= timestamp ORDER BY valid_from DESC LIMIT 1) END) FROM sales INNER JOIN products ON sales.product = products.id WHERE user = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp"; queries["cashbox_status"] = "SELECT amount FROM current_cashbox_status"; queries["cashbox_add"] = "INSERT INTO cashbox_diff ('user', 'amount', 'timestamp') VALUES (?, ?, ?)"; -- cgit v1.2.3