diff options
Diffstat (limited to 'src')
-rw-r--r-- | src/db.vala | 101 | ||||
-rw-r--r-- | src/web.vala | 23 |
2 files changed, 119 insertions, 5 deletions
diff --git a/src/db.vala b/src/db.vala index 1e81aad..dc1c834 100644 --- a/src/db.vala +++ b/src/db.vala @@ -33,6 +33,18 @@ public struct RestockEntry { public int64 timestamp; public int amount; public string price; + public int supplier; + public int64 best_before_date; +} + +public struct Supplier { + public int64 id; + public string name; + public string postal_code; + public string city; + public string street; + public string phone; + public string website; } public struct UserInfo { @@ -125,6 +137,10 @@ public class Database { return stmt.step(); } + public int bind_null(int index) { + return stmt.bind_null(index); + } + public int bind_int(int index, int value) { return stmt.bind_int(index, value); } @@ -171,10 +187,10 @@ public class Database { queries["undo"] = "DELETE FROM sales WHERE user = ? ORDER BY timestamp DESC LIMIT 1"; queries["product_create"] = "INSERT INTO products ('id', 'name', 'amount') VALUES (?, ?, ?)"; queries["price_create"] = "INSERT INTO prices ('product', 'valid_from', 'memberprice', 'guestprice') VALUES (?, ?, ?, ?)"; - queries["stock"] = "INSERT INTO restock ('user', 'product', 'amount', 'price', 'timestamp') VALUES (?, ?, ?, ?, ?)"; + queries["stock"] = "INSERT INTO restock ('user', 'product', 'amount', 'price', 'timestamp', 'supplier', 'best_before_date') VALUES (?, ?, ?, ?, ?, ?, ?)"; queries["price"] = "SELECT memberprice, guestprice FROM prices WHERE product = ? AND valid_from <= ? ORDER BY valid_from DESC LIMIT 1"; queries["prices"] = "SELECT valid_from, memberprice, guestprice FROM prices WHERE product = ? ORDER BY valid_from ASC;"; - queries["restocks"] = "SELECT timestamp, amount, price FROM restock WHERE product = ? ORDER BY timestamp ASC;"; + queries["restocks"] = "SELECT timestamp, amount, price, supplier, best_before_date FROM restock WHERE product = ? ORDER BY timestamp ASC;"; queries["profit_complex"] = "SELECT SUM(memberprice - (SELECT price FROM purchaseprices WHERE product = purch.product)) FROM sales purch, prices WHERE 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);"; queries["sales_complex"] = "SELECT SUM(memberprice) FROM sales purch, prices WHERE 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);"; queries["stock_status"] = "SELECT id, name, amount, memberprice, guestprice FROM products, prices WHERE products.id = prices.product AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = products.id ORDER BY valid_from DESC LIMIT 1) ORDER BY name"; @@ -200,6 +216,9 @@ public class Database { queries["user_auth_create"] = "INSERT OR IGNORE INTO authentication (user) VALUES (?)"; queries["user_disable"] = "UPDATE authentication SET disabled = ? WHERE user = ?"; queries["last_timestamp"] = "SELECT timestamp FROM sales ORDER BY timestamp DESC LIMIT 1"; + queries["supplier_list"] = "SELECT id, name, postal_code, city, street, phone, website FROM supplier"; + 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 (?, ?, ?, ?, ?, ?)"; /* compile queries into statements */ foreach(var entry in queries.entries) { @@ -378,6 +397,8 @@ public class Database { Price p = statements["restocks"].column_int(2); entry.price = @"$p"; + entry.supplier = statements["restocks"].column_int(3); + entry.best_before_date = statements["restocks"].column_int64(4); result.add(entry); } @@ -491,7 +512,7 @@ public class Database { return true; } - public bool restock(int user, uint64 product, uint amount, uint price) { + public bool restock(int user, uint64 product, uint amount, uint price, int supplier, int64 best_before_date) { if(user > 0) { int rc = 0; int64 timestamp = (new DateTime.now_utc()).to_unix(); @@ -502,6 +523,14 @@ public class Database { statements["stock"].bind_text(3, @"$amount"); statements["stock"].bind_text(4, @"$price"); statements["stock"].bind_int64(5, timestamp); + if(supplier > 0) + statements["stock"].bind_int(6, supplier); + else + statements["stock"].bind_null(6); + if(best_before_date > 0) + statements["stock"].bind_int64(7, best_before_date); + else + statements["stock"].bind_null(7); rc = statements["stock"].step(); if(rc != Sqlite.DONE) @@ -821,4 +850,70 @@ public class Database { return 0; return statements["last_timestamp"].column_int64(0); } + + public Gee.List<Supplier?> get_supplier_list() { + var result = new Gee.ArrayList<Supplier?>(); + statements["supplier_list"].reset(); + + while(statements["supplier_list"].step() == Sqlite.ROW) { + Supplier entry = { + statements["supplier_list"].column_int64(0), + statements["supplier_list"].column_text(1), + statements["supplier_list"].column_text(2), + statements["supplier_list"].column_text(3), + statements["supplier_list"].column_text(4), + statements["supplier_list"].column_text(5), + statements["supplier_list"].column_text(6) + }; + + result.add(entry); + } + + return result; + } + + public Supplier get_supplier(int id) { + Supplier result = Supplier(); + + statements["supplier_get"].reset(); + statements["supplier_get"].bind_int(1, id); + + if(statements["supplier_get"].step() != Sqlite.ROW) { + result.id = 0; + result.name = "Unknown"; + result.postal_code = ""; + result.city = ""; + result.street = ""; + result.phone = ""; + result.website = ""; + } else { + result.id = statements["supplier_get"].column_int64(0); + result.name = statements["supplier_get"].column_text(1); + result.postal_code = statements["supplier_get"].column_text(2); + result.city = statements["supplier_get"].column_text(3); + result.street = statements["supplier_get"].column_text(4); + result.phone = statements["supplier_get"].column_text(5); + result.website = statements["supplier_get"].column_text(6); + } + + return result; + } + + public bool add_supplier(string name, string postal_code, string city, string street, string phone, string website) { + statements["supplier_add"].reset(); + statements["supplier_add"].bind_text(1, name); + statements["supplier_add"].bind_text(2, postal_code); + statements["supplier_add"].bind_text(3, city); + statements["supplier_add"].bind_text(4, street); + statements["supplier_add"].bind_text(5, phone); + statements["supplier_add"].bind_text(6, website); + int rc = statements["supplier_add"].step(); + + if(rc != Sqlite.DONE) { + warning("[internal error: %d]".printf(rc)); + return false; + } + + return true; + } } diff --git a/src/web.vala b/src/web.vala index 0820bc8..81b8e67 100644 --- a/src/web.vala +++ b/src/web.vala @@ -501,6 +501,13 @@ public class WebServer { } t.replace("RESTOCKS", restocks); + /* suppliers */ + string suppliers = "<option value=\"0\">Unknown</option>"; + foreach(var e in db.get_supplier_list()) { + suppliers += "<option value=\"%lld\">%s</option>".printf(e.id, e.name); + } + t.replace("SUPPLIERS", suppliers); + msg.set_response("text/html", Soup.MemoryUse.COPY, t.data); } catch(TemplateError e) { stderr.printf(e.message+"\n"); @@ -567,12 +574,24 @@ public class WebServer { if(query != null && query.contains("amount") && query.contains("price")) { int amount = int.parse(query["amount"]); - Price price = Price.parse(query["price"]); + int supplier = int.parse(query["supplier"]); + string best_before_date = query["best_before_date"]; + Price price = Price.parse(query["price"]); + DateTime bbd; + + var dateparts = best_before_date.split("-"); + if(dateparts.length == 3) { + bbd = new DateTime.local(int.parse(dateparts[0]), int.parse(dateparts[1]), int.parse(dateparts[2]), 0, 0, 0); + } else { + bbd = new DateTime.from_unix_local(0); + } if(amount >= 1 && price >= 1) { - if(db.restock(session.user, id, amount, price)) { + if(db.restock(session.user, id, amount, price, supplier, bbd.to_unix())) { template.replace("AMOUNT", @"$amount"); template.replace("PRICE", @"$price"); + template.replace("BESTBEFORE", bbd.format("%Y-%m-%d")); + template.replace("SUPPLIER", db.get_supplier(supplier).name); template.replace("RESTOCK.OK", "block"); template.replace("RESTOCK.FAIL", "none"); msg.set_response("text/html", Soup.MemoryUse.COPY, template.data); |