-
Notifications
You must be signed in to change notification settings - Fork 0
/
test.sql
77 lines (62 loc) · 2.69 KB
/
test.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
/*
Run this to test:
cat test.sql | sqlite3 instance/pypos.sqlite
*/
SELECT gt.id, gt.total, gt.date_time, pay.payment_method,
pay.discount, pay.pending, uat.operation_add AS uat_add,
ua.id AS uat_id, u.id AS user_id, u.username AS username
FROM generic_transaction gt
INNER JOIN user_account_transaction uat ON uat.generic_transaction_id=gt.id
INNER JOIN payment_info pay ON pay.generic_transaction_id=gt.id
INNER JOIN user_account ua ON ua.user_id=uat.user_account_id
INNER JOIN user u ON u.id=ua.user_id
WHERE uat.operation_add=-1;
SELECT * FROM generic_transaction gt;
-- SELECT uc.user_provider_id FROM user u INNER JOIN user_child uc
-- ON u.id=uc.user_id WHERE u.id=12;
-- SELECT u.id, u.username, "" AS user_provider_name, ua.id AS account_id
-- FROM user u INNER JOIN user_account ua ON u.id=ua.user_id
-- WHERE u.canteen_id=1 AND u.active=1 AND
-- u.role_name IN ('client')
-- UNION
-- SELECT u.id, u.username, (SELECT username FROM user WHERE id=uc.user_provider_id) AS user_provider_name,
-- ua.id AS account_id
-- FROM user u INNER JOIN user_child uc ON u.id=uc.user_id
-- INNER JOIN user_account ua ON ua.id=uc.user_provider_id
-- WHERE canteen_id=1;
/* PRODUCT CATEGORY IS WORKING
SELECT p.id, p.name, p.price, p.active, pc.name as category_name
FROM product p LEFT JOIN product_category pc ON p.category = pc.id
WHERE p.canteen_id=2;
/*
-- SELECT pm.name FROM generic_transaction tp
-- INNER JOIN transaction_product_item tpi ON tp.id=tpi.transaction_product_id
-- INNER JOIN product p ON p.id = tpi.product_id
-- INNER JOIN payment_method pm ON tp.payment_method = pm.id
-- GROUP BY tp.id;
/* Nested structure of Transactions
-- SELECT tp.date, tp.payment_method, tp.discount, tp.total_value,
-- group_concat('{"name":' || p.name || ',"quantity":' || tpi.quantity || '}') as products
-- FROM generic_transaction tp
-- INNER JOIN transaction_product_item tpi ON tp.id=tpi.transaction_product_id
-- INNER JOIN product p ON p.id = tpi.product_id
-- GROUP BY tp.id;
/* SELECT pc.id, pc.name as category_name, COUNT(*) as products_inside
-- FROM product p INNER JOIN product_category pc ON p.category = pc.id
-- GROUP BY pc.id;
/*
-- SELECT * FROM product;
-- INSERT INTO product(name, price, category) VALUES('bar', 2.0, 1);
-- SELECT * FROM product;
/*
-- DELETE FROM product WHERE id=7;
-- SELECT * FROM user;
-- SELECT * FROM product;
-- SELECT * FROM product_category;
/* select category id, name and count of products belonging to it
-- SELECT pc.id, pc.name, COUNT(*) as products_inside
-- FROM product_category pc INNER JOIN product p ON p.category = pc.id
-- GROUP BY pc.id UNION
-- SELECT pc.id, pc.name, '0' as products_inside
-- FROM product_category pc LEFT JOIN product p ON p.category = pc.id
-- WHERE p.id IS NULL;