From b299e6a7c94defeb93332d83defa9c2ce29f480c Mon Sep 17 00:00:00 2001 From: Kilian Saffran Date: Thu, 11 Apr 2019 20:50:29 +0200 Subject: [PATCH] paywindow transactions and commands --- db/cashbox.sqlite.dump.sql | 289 ++++++++++++++++++----------------- db/cashbox.sqlite.schema.sql | 216 ++++++++++++-------------- db/dumpsqlite.bat | 4 + modules/paywindow/index.html | 78 ++++++---- modules/paywindow/index.js | 180 +++++++++++++++++----- 5 files changed, 444 insertions(+), 323 deletions(-) create mode 100644 db/dumpsqlite.bat diff --git a/db/cashbox.sqlite.dump.sql b/db/cashbox.sqlite.dump.sql index ffc65a9..61f01de 100644 --- a/db/cashbox.sqlite.dump.sql +++ b/db/cashbox.sqlite.dump.sql @@ -1,145 +1,154 @@ -PRAGMA foreign_keys=OFF; -BEGIN TRANSACTION; -CREATE TABLE payementtypes ( - id TEXT, - name TEXT, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TABLE products ( - id TEXT, - id_group TEXT, - name TEXT, - specification TEXT, - netamount REAL, - grossamount REAL, - vatpercent REAL, - visible BOOLEAN, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, productgroup TEXT, - primary key (id) -); -INSERT INTO products VALUES('523FB3C9-562A-7644-9E0C-664A94984B60',NULL,'Softdrink (Cola, Fanta, Sprite)','0,3',1.3999999999999999111,1.3999999999999999111,NULL,1,'2019-03-18 20:31:18',NULL,'Boissons'); -INSERT INTO products VALUES('BE438932-ECDB-DF46-B0D8-5F1A4E2A8E77',NULL,'Rosport Bleu','0,5',1.3999999999999999111,1.3999999999999999111,NULL,1,'2019-03-18 20:31:18',NULL,'Boissons'); -INSERT INTO products VALUES('272B688C-FC90-D344-BC93-F4C4C0E78183',NULL,'Vittel','0,3',1.1999999999999999555,1.1999999999999999555,NULL,NULL,'2019-03-18 20:31:18',NULL,'Boissons'); -INSERT INTO products VALUES('9DF50B2D-8F58-CA4A-9F58-00E71138C80C',NULL,'Bière (Bouteille) Bofferding','0,33',2.0,2.0,NULL,1,'2019-03-18 20:31:18',NULL,'Alcohol'); -INSERT INTO products VALUES('E6AAD31B-B6E9-BE47-B221-4315ABFEF81D',NULL,'Bière (Mini) Bofferding','0,33',2.2000000000000001776,2.2000000000000001776,NULL,1,'2019-03-18 20:31:18',NULL,'Alcohol'); -INSERT INTO products VALUES('77D2A8A5-A1FE-704B-AF52-99817B25B7C7',NULL,'Pol Fabire brut','0,25',4.2000000000000001776,4.2000000000000001776,NULL,1,'2019-03-18 20:31:18',NULL,'Alcohol'); -INSERT INTO products VALUES('8D03C7EF-A773-6247-A0E3-057FA355B263',NULL,'Cellier rouge','0,25',2.2999999999999998223,2.2999999999999998223,NULL,1,'2019-03-18 20:31:18',NULL,'Alcohol'); -INSERT INTO products VALUES('EA1AFD8A-F839-5C47-9050-5EFF8229D12E',NULL,'Cellier rosé','0,25',2.2999999999999998223,2.2999999999999998223,NULL,1,'2019-03-18 20:31:18',NULL,'Alcohol'); -INSERT INTO products VALUES('242C42D6-3CC6-5147-8C68-C5F954B140B7',NULL,'Vin blanc (Rivaner / Elbling)','0,25',4.0,4.0,NULL,1,'2019-03-18 20:31:18',NULL,'Alcohol'); -INSERT INTO products VALUES('20683124-2DF2-FF4A-BC76-3844D26174A2',NULL,'Lëtzebuerger Grillwurscht',NULL,3.0,3.0,NULL,1,'2019-03-05 08:50:42',NULL,'Food'); -INSERT INTO products VALUES('6C487085-4221-F546-97BC-D3850E7742E6',NULL,'Lëtzebuerger Mettwurscht',NULL,3.0,3.0,NULL,1,'2019-03-05 08:50:42',NULL,'Food'); -INSERT INTO products VALUES('7B3B40EB-CC33-3140-9752-5FCE492796A0',NULL,'Edamer',NULL,3.0,3.0,NULL,1,'2019-03-05 08:50:42',NULL,'Food'); -INSERT INTO products VALUES('C507FA4B-31C1-144B-8F50-6BCF0B4E62DF',NULL,'Gauffre (sucre)',NULL,2.5,2.5,NULL,1,'2019-03-05 08:50:42',NULL,'Dessert'); -INSERT INTO products VALUES('D6C1C2A0-9343-C348-8786-1547F37ADB0E',NULL,'Gaufre (chantilly)',NULL,3.0,3.0,NULL,1,'2019-03-05 08:50:42',NULL,'Dessert'); -INSERT INTO products VALUES('0367CCA3-1FBB-DF41-AB10-ECDDFB3D0AC0',NULL,'Gaufre (chantily + fraises)',NULL,3.0,3.0,NULL,1,'2019-03-05 08:50:42',NULL,'Dessert'); -CREATE TABLE transactions ( - id TEXT, - id_payementtype TEXT, - id_client TEXT, - id_table TEXT, - id_product TEXT, - netamount REAL, - vatamount REAL, - grossamount REAL, - payedamount REAL, - cashbackamount REAL, - status TEXT, - statusdate DATE, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TABLE transactiondata ( - id TEXT, - id_transaction TEXT, - id_product TEXT, - id_client TEXT, - id_table TEXT, - netamount REAL, - vatamount REAL, - grossamount REAL, - quantity integer, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TABLE printtemplates ( - id TEXT, - name TEXT, - templatedesign TEXT, - templatesql TEXT, - templatetype TEXT, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TABLE stations ( +PRAGMA foreign_keys=OFF; +BEGIN TRANSACTION; +CREATE TABLE printtemplates ( id TEXT, - hostname TEXT, - type TEXT, - currentip TEXT, - staffmember TEXT, - status TEXT, - location TEXT, + name TEXT, + templatedesign TEXT, + templatesql TEXT, + templatetype TEXT, modified DATETIME defaults CURRENT_TIMESTAMP, created DATETIME defaults CURRENT_TIMESTAMP, primary key (id) -); -CREATE TABLE config ( - id TEXT, - value TEXT, - primary key (id) -); -INSERT INTO config VALUES('cardpayement_enabled','1'); -INSERT INTO config VALUES('checkout_grouping','1'); -CREATE TABLE accounts ( - id TEXT, - name TEXT, - type TEXT, - clientnumber text, - prename TEXT, - surname TEXT, - address TEXT, - zip TEXT, - city TEXT, - country TEXT, - phone TEXT, - email text, - description TEXT, - location TEXT, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TRIGGER trg_payementtypes_upd UPDATE ON payementtypes - BEGIN - UPDATE payementtypes SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TRIGGER trg_products_upd UPDATE ON products - BEGIN - UPDATE products SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TRIGGER trg_transactions_upd UPDATE ON transactions - BEGIN - UPDATE transactions SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TRIGGER trg_transactiondata_upd UPDATE ON transactiondata - BEGIN - UPDATE transactiondata SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TRIGGER trg_printtemplates_upd UPDATE ON printtemplates - BEGIN - UPDATE printtemplates SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TRIGGER trg_stations_upd UPDATE ON stations +); +CREATE TABLE stations ( + id TEXT, + hostname TEXT, + type TEXT, + currentip TEXT, + staffmember TEXT, + status TEXT, + location TEXT, + modified DATETIME defaults CURRENT_TIMESTAMP, + created DATETIME defaults CURRENT_TIMESTAMP, + primary key (id) +); +CREATE TABLE config ( + id TEXT, + value TEXT, + primary key (id) +); +INSERT INTO "config" VALUES('cardpayement_enabled','1'); +INSERT INTO "config" VALUES('checkout_grouping','0'); +CREATE TABLE transactiondata ( + id TEXT, + id_transaction TEXT, + id_product TEXT, + id_account TEXT, + netamount REAL, + vatamount REAL, + grossamount REAL, + quantity integer, + modified DATETIME default CURRENT_TIMESTAMP, + created DATETIME default CURRENT_TIMESTAMP, + primary key (id) +); +INSERT INTO "transactiondata" VALUES('0764ee4c-3f09-3ac4-8be3-a6e12bd33f60','6cd6ecbf-d4de-bebc-c8e6-d30ffcd6b72e','20683124-2DF2-FF4A-BC76-3844D26174A2','c418ae7e-c5e9-45f5-93ca-30dcb3e5e90a',3.0,NULL,3.0,1,'2019-04-11 17:36:25','2019-04-11 17:36:25'); +INSERT INTO "transactiondata" VALUES('c8e39d22-f33e-3a98-8d22-517e9efe789c','6cd6ecbf-d4de-bebc-c8e6-d30ffcd6b72e','6C487085-4221-F546-97BC-D3850E7742E6','c418ae7e-c5e9-45f5-93ca-30dcb3e5e90a',3.0,NULL,3.0,1,'2019-04-11 17:36:25','2019-04-11 17:36:25'); +INSERT INTO "transactiondata" VALUES('cc6d65d4-c9c3-2276-6bc1-e27380699ab3','6cd6ecbf-d4de-bebc-c8e6-d30ffcd6b72e','D6C1C2A0-9343-C348-8786-1547F37ADB0E','c418ae7e-c5e9-45f5-93ca-30dcb3e5e90a',3.0,NULL,3.0,1,'2019-04-11 17:36:25','2019-04-11 17:36:25'); +INSERT INTO "transactiondata" VALUES('ca1baff0-8911-2e2f-f8f0-22fe09798021','db53cb86-d04a-997c-bb93-e3f45bcf45e7','20683124-2DF2-FF4A-BC76-3844D26174A2','53a4fdac-613a-4188-9695-2edeef90a1f3',3.0,NULL,3.0,1,'2019-04-11 18:40:14','2019-04-11 18:40:14'); +INSERT INTO "transactiondata" VALUES('5bfc9065-55d7-4e74-f8ac-5c52ee27e8a8','b142be13-db66-a16a-4bc4-7f7a727f46ca','6C487085-4221-F546-97BC-D3850E7742E6','0cecba8c-febc-47d1-820c-6c948be9b9c6',3.0,NULL,3.0,1,'2019-04-11 18:44:19','2019-04-11 18:44:19'); +INSERT INTO "transactiondata" VALUES('89e59470-8d6b-9b8b-64fe-0c5bde8f8234','b142be13-db66-a16a-4bc4-7f7a727f46ca','20683124-2DF2-FF4A-BC76-3844D26174A2','0cecba8c-febc-47d1-820c-6c948be9b9c6',3.0,NULL,3.0,1,'2019-04-11 18:44:19','2019-04-11 18:44:19'); +INSERT INTO "transactiondata" VALUES('bc120dc7-03dc-be25-e014-6ad473476031','b142be13-db66-a16a-4bc4-7f7a727f46ca','7B3B40EB-CC33-3140-9752-5FCE492796A0','0cecba8c-febc-47d1-820c-6c948be9b9c6',3.0,NULL,3.0,1,'2019-04-11 18:44:19','2019-04-11 18:44:19'); +INSERT INTO "transactiondata" VALUES('62ebd480-192c-2519-414a-9315386ce4d6','2f5d0393-d6b7-e455-89b6-f7e111b8aa41','20683124-2DF2-FF4A-BC76-3844D26174A2','ef69bc4b-3828-4bb2-8e9b-3931eb2e187e',3.0,NULL,3.0,1,'2019-04-11 18:48:13','2019-04-11 18:48:13'); +INSERT INTO "transactiondata" VALUES('4ceaa3d9-c940-8824-d9ea-d9fefa83ab1d','2f5d0393-d6b7-e455-89b6-f7e111b8aa41','C507FA4B-31C1-144B-8F50-6BCF0B4E62DF','ef69bc4b-3828-4bb2-8e9b-3931eb2e187e',2.5,NULL,2.5,1,'2019-04-11 18:48:13','2019-04-11 18:48:13'); +INSERT INTO "transactiondata" VALUES('ca98e648-7615-0d0c-1e79-555a95c27285','2f5d0393-d6b7-e455-89b6-f7e111b8aa41','BE438932-ECDB-DF46-B0D8-5F1A4E2A8E77','ef69bc4b-3828-4bb2-8e9b-3931eb2e187e',1.4,NULL,1.4,1,'2019-04-11 18:48:14','2019-04-11 18:48:14'); +INSERT INTO "transactiondata" VALUES('fc42101c-1ee6-15e3-83d1-6c7b3ab4e11a','2f5d0393-d6b7-e455-89b6-f7e111b8aa41','272B688C-FC90-D344-BC93-F4C4C0E78183','ef69bc4b-3828-4bb2-8e9b-3931eb2e187e',1.2,NULL,1.2,1,'2019-04-11 18:48:14','2019-04-11 18:48:14'); +INSERT INTO "transactiondata" VALUES('031ba6d5-b7cc-fff8-280a-6175866a3a63','2f5d0393-d6b7-e455-89b6-f7e111b8aa41','9DF50B2D-8F58-CA4A-9F58-00E71138C80C','ef69bc4b-3828-4bb2-8e9b-3931eb2e187e',2.0,NULL,2.0,1,'2019-04-11 18:48:14','2019-04-11 18:48:14'); +CREATE TABLE transactions ( + id TEXT, + payementtype TEXT, + id_account TEXT, + payedamount REAL, + cashbackamount REAL, + cashamount REAL, + status TEXT, + statusdate DATE, + modified DATETIME default CURRENT_TIMESTAMP, + created DATETIME default CURRENT_TIMESTAMP, + primary key (id) +); +INSERT INTO "transactions" VALUES('2f5d0393-d6b7-e455-89b6-f7e111b8aa41','cash','ef69bc4b-3828-4bb2-8e9b-3931eb2e187e',10.1,NULL,10.1,'payed','2019-04-11','2019-04-11 18:48:13','2019-04-11 17:37:58'); +INSERT INTO "transactions" VALUES('313768a4-41d6-129e-7c56-ce1ddcdb8af9',NULL,NULL,NULL,NULL,NULL,'command','2019-04-11','2019-04-11 18:36:18','2019-04-11 18:36:18'); +INSERT INTO "transactions" VALUES('1561e1d4-f4b3-b710-d52c-c6c9d6bb2301',NULL,NULL,NULL,NULL,NULL,'command','2019-04-11','2019-04-11 18:37:26','2019-04-11 18:37:26'); +INSERT INTO "transactions" VALUES('b7c1f095-97ae-ae43-5034-7286260f8d96',NULL,NULL,NULL,NULL,NULL,'command','2019-04-11','2019-04-11 18:38:05','2019-04-11 18:38:05'); +INSERT INTO "transactions" VALUES('ddf95f30-d3d9-4bc8-792a-0aa10bd74732',NULL,NULL,NULL,NULL,NULL,'command','2019-04-11','2019-04-11 18:38:40','2019-04-11 18:38:40'); +INSERT INTO "transactions" VALUES('db53cb86-d04a-997c-bb93-e3f45bcf45e7',NULL,NULL,NULL,NULL,NULL,'command','2019-04-11','2019-04-11 18:39:50','2019-04-11 18:39:50'); +INSERT INTO "transactions" VALUES('b142be13-db66-a16a-4bc4-7f7a727f46ca',NULL,NULL,NULL,NULL,NULL,'command','2019-04-11','2019-04-11 18:43:33','2019-04-11 18:43:33'); +CREATE TABLE accounts ( + id TEXT, + name TEXT, + type TEXT, + visible boolean, + info TEXT, + modified DATETIME default CURRENT_TIMESTAMP, + created DATETIME default CURRENT_TIMESTAMP, + primary key (id) +); +INSERT INTO "accounts" VALUES('f3203f46-ac89-4ce3-bdd0-16387bc30cc3','Table 1','table',1,NULL,'2019-04-11 14:08:49',NULL); +INSERT INTO "accounts" VALUES('d8d7c17a-7a2d-41b6-b2be-f5a92df33c3f','Table 2','table',1,NULL,'2019-04-11 14:08:50',NULL); +INSERT INTO "accounts" VALUES('6b504c20-d88c-4576-9c47-9074f0573cef','Table 4','table',1,NULL,'2019-04-11 14:08:51',NULL); +INSERT INTO "accounts" VALUES('a2b97fd0-faa5-4dcb-b63e-f62f68d36d57','Table 5','table',1,NULL,'2019-04-11 14:08:52',NULL); +INSERT INTO "accounts" VALUES('c418ae7e-c5e9-45f5-93ca-30dcb3e5e90a','Table 6','table',1,NULL,'2019-04-11 14:08:54',NULL); +INSERT INTO "accounts" VALUES('ef69bc4b-3828-4bb2-8e9b-3931eb2e187e','Table 7','table',1,NULL,'2019-04-11 14:08:55',NULL); +INSERT INTO "accounts" VALUES('53a4fdac-613a-4188-9695-2edeef90a1f3','Table 8','table',1,NULL,'2019-04-11 14:08:56',NULL); +INSERT INTO "accounts" VALUES('7139973e-b86d-4474-b9e5-c28501fd9f30','Table 9','table',1,NULL,'2019-04-11 14:08:56',NULL); +INSERT INTO "accounts" VALUES('e8bec5da-a6cf-4425-910e-638e3fa16e4a','Table 10','table',1,NULL,'2019-04-11 14:09:01',NULL); +INSERT INTO "accounts" VALUES('96e24eb7-cae5-416d-ae23-79522a832507','Table 11','table',1,NULL,'2019-04-11 14:09:02',NULL); +INSERT INTO "accounts" VALUES('f9403f7a-0430-4ab4-a6d1-7f347d422eff','Table 12','table',1,NULL,'2019-04-11 14:09:07',NULL); +INSERT INTO "accounts" VALUES('6f7e649d-f07d-4ba8-bd73-226343dd111c','Table 14','table',1,NULL,'2019-04-11 14:09:08',NULL); +INSERT INTO "accounts" VALUES('0cecba8c-febc-47d1-820c-6c948be9b9c6','Table 15','table',1,NULL,'2019-04-11 14:12:40',NULL); +INSERT INTO "accounts" VALUES('9bcc8841-4bf3-41f7-894a-416264ef403c','Table 16','table',1,NULL,'2019-04-11 14:09:12',NULL); +CREATE TABLE products ( + id TEXT, + id_group TEXT, + name TEXT, + specification TEXT, + netamount REAL, + grossamount REAL, + vatpercent REAL, + visible BOOLEAN, + productgroup TEXT, + modified DATETIME default CURRENT_TIMESTAMP, + created DATETIME default CURRENT_TIMESTAMP, + primary key (id) +); +INSERT INTO "products" VALUES('523FB3C9-562A-7644-9E0C-664A94984B60',NULL,'Softdrink (Cola, Fanta, Sprite)',NULL,1.4,1.4,NULL,1,'Boissons','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('BE438932-ECDB-DF46-B0D8-5F1A4E2A8E77',NULL,'Rosport Bleu',NULL,1.4,1.4,NULL,1,'Boissons','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('272B688C-FC90-D344-BC93-F4C4C0E78183',NULL,'Vittel',NULL,1.2,1.2,NULL,NULL,'Boissons','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('9DF50B2D-8F58-CA4A-9F58-00E71138C80C',NULL,'Bière (Bouteille) Bofferding',NULL,2.0,2.0,NULL,1,'Boissons','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('E6AAD31B-B6E9-BE47-B221-4315ABFEF81D',NULL,'Bière (Mini) Bofferding',NULL,2.2,2.2,NULL,1,'Boissons','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('77D2A8A5-A1FE-704B-AF52-99817B25B7C7',NULL,'Pol Fabire brut',NULL,4.2,4.2,NULL,1,'Boissons','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('8D03C7EF-A773-6247-A0E3-057FA355B263',NULL,'Cellier rouge',NULL,2.3,2.3,NULL,1,'Boissons','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('EA1AFD8A-F839-5C47-9050-5EFF8229D12E',NULL,'Cellier rosé',NULL,2.3,2.3,NULL,1,'Boissons','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('242C42D6-3CC6-5147-8C68-C5F954B140B7',NULL,'Vin blanc (Rivaner / Elbling)',NULL,4.0,4.0,NULL,1,'Boissons','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('20683124-2DF2-FF4A-BC76-3844D26174A2',NULL,'Lëtzebuerger Grillwurscht',NULL,3.0,3.0,NULL,1,'Food','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('6C487085-4221-F546-97BC-D3850E7742E6',NULL,'Lëtzebuerger Mettwurscht',NULL,3.0,3.0,NULL,1,'Food','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('7B3B40EB-CC33-3140-9752-5FCE492796A0',NULL,'Edamer',NULL,3.0,3.0,NULL,1,'Food','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('C507FA4B-31C1-144B-8F50-6BCF0B4E62DF',NULL,'Gauffre (sucre)',NULL,2.5,2.5,NULL,1,'Dessert','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('D6C1C2A0-9343-C348-8786-1547F37ADB0E',NULL,'Gaufre (chantilly)',NULL,3.0,3.0,NULL,1,'Dessert','2019-04-11 17:34:23','2019-04-11 17:34:23'); +INSERT INTO "products" VALUES('0367CCA3-1FBB-DF41-AB10-ECDDFB3D0AC0',NULL,'Gaufre (chantily + fraises)',NULL,3.0,3.0,NULL,1,'Dessert','2019-04-11 17:34:23','2019-04-11 17:34:23'); +CREATE TRIGGER trg_printtemplates_upd UPDATE ON printtemplates BEGIN - UPDATE stations SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TRIGGER trg_accounts_upd UPDATE ON accounts - BEGIN - UPDATE accounts SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -COMMIT; + UPDATE printtemplates SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TRIGGER trg_stations_upd UPDATE ON stations + BEGIN + UPDATE stations SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TRIGGER trg_transactiondata_upd UPDATE ON transactiondata + BEGIN + UPDATE transactiondata SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TRIGGER trg_transactions_upd UPDATE ON transactions + BEGIN + UPDATE transactions SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TRIGGER trg_accounts_upd UPDATE ON accounts + BEGIN + UPDATE accounts SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TRIGGER trg_products_upd UPDATE ON products + BEGIN + UPDATE products SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +COMMIT; diff --git a/db/cashbox.sqlite.schema.sql b/db/cashbox.sqlite.schema.sql index f5bc007..b50c9fe 100644 --- a/db/cashbox.sqlite.schema.sql +++ b/db/cashbox.sqlite.schema.sql @@ -1,125 +1,101 @@ -CREATE TABLE payementtypes ( - id TEXT, - name TEXT, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TRIGGER trg_payementtypes_upd UPDATE ON payementtypes - BEGIN - UPDATE payementtypes SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TABLE products ( - id TEXT, - id_group TEXT, - name TEXT, - specification TEXT, - netamount REAL, - grossamount REAL, - vatpercent REAL, - visible BOOLEAN, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, productgroup TEXT, - primary key (id) -); -CREATE TRIGGER trg_products_upd UPDATE ON products - BEGIN - UPDATE products SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TABLE transactions ( - id TEXT, - id_payementtype TEXT, - id_client TEXT, - id_table TEXT, - id_product TEXT, - netamount REAL, - vatamount REAL, - grossamount REAL, - payedamount REAL, - cashbackamount REAL, - status TEXT, - statusdate DATE, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TRIGGER trg_transactions_upd UPDATE ON transactions - BEGIN - UPDATE transactions SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TABLE transactiondata ( - id TEXT, - id_transaction TEXT, - id_product TEXT, - id_client TEXT, - id_table TEXT, - netamount REAL, - vatamount REAL, - grossamount REAL, - quantity integer, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TRIGGER trg_transactiondata_upd UPDATE ON transactiondata - BEGIN - UPDATE transactiondata SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TABLE printtemplates ( - id TEXT, - name TEXT, - templatedesign TEXT, - templatesql TEXT, - templatetype TEXT, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TRIGGER trg_printtemplates_upd UPDATE ON printtemplates - BEGIN - UPDATE printtemplates SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TABLE stations ( +CREATE TABLE printtemplates ( id TEXT, - hostname TEXT, - type TEXT, - currentip TEXT, - staffmember TEXT, - status TEXT, - location TEXT, + name TEXT, + templatedesign TEXT, + templatesql TEXT, + templatetype TEXT, modified DATETIME defaults CURRENT_TIMESTAMP, created DATETIME defaults CURRENT_TIMESTAMP, primary key (id) -); -CREATE TRIGGER trg_stations_upd UPDATE ON stations +); +CREATE TRIGGER trg_printtemplates_upd UPDATE ON printtemplates BEGIN - UPDATE stations SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; -CREATE TABLE config ( - id TEXT, - value TEXT, - primary key (id) -); -CREATE TABLE accounts ( - id TEXT, - name TEXT, - type TEXT, - clientnumber text, - prename TEXT, - surname TEXT, - address TEXT, - zip TEXT, - city TEXT, - country TEXT, - phone TEXT, - email text, - description TEXT, - location TEXT, - modified DATETIME defaults CURRENT_TIMESTAMP, - created DATETIME defaults CURRENT_TIMESTAMP, - primary key (id) -); -CREATE TRIGGER trg_accounts_upd UPDATE ON accounts - BEGIN - UPDATE accounts SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; - END; + UPDATE printtemplates SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TABLE stations ( + id TEXT, + hostname TEXT, + type TEXT, + currentip TEXT, + staffmember TEXT, + status TEXT, + location TEXT, + modified DATETIME defaults CURRENT_TIMESTAMP, + created DATETIME defaults CURRENT_TIMESTAMP, + primary key (id) +); +CREATE TRIGGER trg_stations_upd UPDATE ON stations + BEGIN + UPDATE stations SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TABLE config ( + id TEXT, + value TEXT, + primary key (id) +); +CREATE TABLE transactiondata ( + id TEXT, + id_transaction TEXT, + id_product TEXT, + id_account TEXT, + netamount REAL, + vatamount REAL, + grossamount REAL, + quantity integer, + modified DATETIME default CURRENT_TIMESTAMP, + created DATETIME default CURRENT_TIMESTAMP, + primary key (id) +); +CREATE TRIGGER trg_transactiondata_upd UPDATE ON transactiondata + BEGIN + UPDATE transactiondata SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TABLE transactions ( + id TEXT, + payementtype TEXT, + id_account TEXT, + payedamount REAL, + cashbackamount REAL, + cashamount REAL, + status TEXT, + statusdate DATE, + modified DATETIME default CURRENT_TIMESTAMP, + created DATETIME default CURRENT_TIMESTAMP, + primary key (id) +); +CREATE TRIGGER trg_transactions_upd UPDATE ON transactions + BEGIN + UPDATE transactions SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TABLE accounts ( + id TEXT, + name TEXT, + type TEXT, + visible boolean, + info TEXT, + modified DATETIME default CURRENT_TIMESTAMP, + created DATETIME default CURRENT_TIMESTAMP, + primary key (id) +); +CREATE TRIGGER trg_accounts_upd UPDATE ON accounts + BEGIN + UPDATE accounts SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; +CREATE TABLE products ( + id TEXT, + id_group TEXT, + name TEXT, + specification TEXT, + netamount REAL, + grossamount REAL, + vatpercent REAL, + visible BOOLEAN, + productgroup TEXT, + modified DATETIME default CURRENT_TIMESTAMP, + created DATETIME default CURRENT_TIMESTAMP, + primary key (id) +); +CREATE TRIGGER trg_products_upd UPDATE ON products + BEGIN + UPDATE products SET modified=CURRENT_TIMESTAMP WHERE id = NEW.id; + END; diff --git a/db/dumpsqlite.bat b/db/dumpsqlite.bat new file mode 100644 index 0000000..9978282 --- /dev/null +++ b/db/dumpsqlite.bat @@ -0,0 +1,4 @@ +@echo off + +C:\Users\ksaff\Tools\sqlite3.exe "C:\Users\ksaff\AppData\Roaming\dks\db\cashboxdev.sqlite" ".dump" > "C:\Users\ksaff\Workspace\Apps\cashbox\db\cashbox.sqlite.dump.sql"; +C:\Users\ksaff\Tools\sqlite3.exe "C:\Users\ksaff\AppData\Roaming\dks\db\cashboxdev.sqlite" ".schema" > "C:\Users\ksaff\Workspace\Apps\cashbox\db\cashbox.sqlite.schema.sql"; \ No newline at end of file diff --git a/modules/paywindow/index.html b/modules/paywindow/index.html index 5cbe7e6..566741b 100644 --- a/modules/paywindow/index.html +++ b/modules/paywindow/index.html @@ -9,6 +9,12 @@ CashBox +