From 007cda3cf342276a4b5838ab9c4fa9032682a253 Mon Sep 17 00:00:00 2001 From: Kilian Saffran Date: Sun, 24 Mar 2019 18:39:40 +0100 Subject: [PATCH] db changes --- db/cashbox.sqlite.dump.sql | 145 +++++++++++++++++++++++++++++++++++ db/cashbox.sqlite.schema.sql | Bin 5966 -> 2981 bytes 2 files changed, 145 insertions(+) create mode 100644 db/cashbox.sqlite.dump.sql diff --git a/db/cashbox.sqlite.dump.sql b/db/cashbox.sqlite.dump.sql new file mode 100644 index 0000000..ffc65a9 --- /dev/null +++ b/db/cashbox.sqlite.dump.sql @@ -0,0 +1,145 @@ +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 ( + 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','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 + 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; diff --git a/db/cashbox.sqlite.schema.sql b/db/cashbox.sqlite.schema.sql index 14c9d30f59aabed7aa1923a792efe262b195b807..f5bc007787a81a74c4fdae21aabb376521a22251 100644 GIT binary patch literal 2981 zcmd6p&2Hi_5Xa9%;vMGLN~?W=st2GJi$p_}rt031u?<+wS8WF^`}8{raj=uN3oDTJ z1Rj6H^Z(6^XA+T7OduYO=L8D67pcgM*%v~?O)!ugV)8To77Q|)3j06Jxm-)Z;Q_bC zvqu74tZA7T4U?BBB4NC&tk3c2@d>vwC8#-hb)jTR)gIo&9&RMR4Q}s(Nps0)Hl30P zjM^-{t1ioe!^=~39ax0E8v%^TbQT6+7kE42nZ#iIx$oT`UVo5?0LpL=A$k2S`CR}c zd^ld|pqt8h$N$2QvPPvYt;XC3WmUZhBco`hDXaAEb8=(C z(m;GHk(JO*3ZTKzO6lXcQNvoYE@ZKx@={nlZ`F|%sS z*To(TPGsz=+~Mp_^;o-;m&r-zL==hkQq!5Yk|N*^(7(pOKM{W~X=bF^gUpQB2=)K5 zs}%bjUp4y`!CLQ&BrQfAzD6P~5{&as+PdY`F5yDUOZW8N;y1s0r+>DYjYGH4M!nyz)GdCV9MsnLkpg>UZ)~m33ZVB zU~B6b4wKw{1<`O84_tpve}T(UF0a8De$ruB2|6&WdDw8kU)x#Osq+!wA^om`<=I+p z{KB=PN!bQ&*Av%~x-X%ORqe!AiPPo*ppf-*mz6awBYqhyOa%gUT0dPXcSt!`Lbq?y zZ%ZD}s_I+wP}Y~gmaZ8V+dON15Gj=jG#|tCWl9!qPvl3(nZa>hZ1h9J71ZfHt*o8M Pu;yNZ+KKbyq6v#XfYF?z^S$ft<)k>vx1VH?Z&^ckoGX$8w2L za-4lwyvC)n7r5&}>(k2fP-f7Mp!XEkQ&^v1W)XfD7}qVl@b|#R;n2?hP~O;IKd$E( z^Lu=Hn1{jmW-`W1>SKJ3`KLBQmzZB3B_n5ufeGkwT@2Q+2VW74sZz2CMP^Ak&R|7>llCJM_64+lDvhI63M;hD-Jda~&~$4J=WM z8C&MOdN#};AH$FG;n4Ed1a^{2D>24HUZo%Mu~{aKKX1)qZVUEdiH6lVLPbh|7o&X3HuZOlFc4ik%3 z=9(hExw_DdtJ}fK`KR(j>>;c4f(wW?26wPvZ52Gx( zKmYli3HCawS2>&!A$+vUhmeJTlMOoVBAd9?;?SPj*wq?4`&Hfn@hd1JtO4)9)>S<+ zJ()CBA4cjrP3ztXYdwxk&x@O7r}qBbyfMpepSw#Y@HIu1$&)3!T(Nw|cU}{6U0H$G zbq6zO&jRlwoV7>R2G6+eShzkN`GNRXoxpj**8R|5iFI_eqvyX*Q8V*AUbRl-s|c3K aJN``fY*J(sABkZG@ul0bhOhPfOz|5K+<+|r -- 2.39.5