-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathgetLists.py
147 lines (125 loc) · 7.88 KB
/
getLists.py
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
#!usr/bin/python
import connectDb
def getCashierList(option, valueSearch = None):
count = None
rowCashier = None
conn, cur = connectDb.connectToDatabase()
if option == 0:
countQuery = "SELECT count(*) FROM cashier WHERE active = 1"
resultQuery = "SELECT id FROM cashier WHERE active = 1"
elif option == 2:
countQuery = "SELECT count(*) FROM cashier WHERE active = 1 AND id = %d" % valueSearch
resultQuery = "SELECT id FROM cashier WHERE active = 1 AND id = %d" % valueSearch
cur.execute(countQuery)
count = cur.fetchone()
count = count[0]
cur.execute(resultQuery)
rowCashier = cur.fetchall()
connectDb.closeDatabaseConnection(conn, cur)
return count, rowCashier
def getPDUList(option, valueSearch = None):
count = None
rowPDUs = None
conn, cur = connectDb.connectToDatabase()
if option == 0:
countQuery = "SELECT count(*) FROM pdumap"
resultQuery = "SELECT id, port, barcode FROM pdumap ORDER BY id, port "
elif option == 1:
countQuery = "SELECT count(*) FROM pdumap WHERE barcode = %d" % valueSearch
resultQuery = "SELECT id, port, barcode FROM pdumap WHERE barcode = %d ORDER BY id, port" % valueSearch
elif option == 2:
countQuery = "SELECT count(*) FROM pdumap WHERE id = %d" % valueSearch
resultQuery = "SELECT id, port, barcode FROM pdumap WHERE id = %d ORDER BY id, port" % valueSearch
cur.execute(countQuery)
count = cur.fetchone()
count = count[0]
cur.execute(resultQuery)
rowPDUs = cur.fetchall()
connectDb.closeDatabaseConnection(conn, cur)
return count, rowPDUs
def getTranList(option, valueSearch = None):
count = None
rowTran = None
conn, cur = connectDb.connectToDatabase()
if option == 0:
countQuery = "SELECT count(*) FROM transaction"
resultQuery = "SELECT * FROM transaction t ORDER BY t.date DESC"
elif option == 1:
countQuery = "SELECT count(*) FROM transactiondetails WHERE transactionid = %d" % valueSearch
resultQuery = "SELECT * FROM transactiondetails WHERE transactionid = %d" % valueSearch
elif option == 2:
countQuery = "SELECT count(*) FROM transaction t WHERE t.date = '%s'" % valueSearch
resultQuery = "SELECT * FROM transaction t WHERE t.date = '%s' ORDER BY t.transactionid DESC" % valueSearch
cur.execute(countQuery)
count = cur.fetchone()
count = count[0]
cur.execute(resultQuery)
rowTran = cur.fetchall()
connectDb.closeDatabaseConnection(conn, cur)
return count, rowTran
def getProductList(option, valueSearch = None):
count = None
rowProducts = None
conn, cur = connectDb.connectToDatabase()
if option == 0:
countQuery = "SELECT count(distinct product.barcode) FROM product, batch WHERE product.barcode=batch.barcode AND product.active=1 AND ISNULL(batch.expiry)"
resultQuery = "SELECT distinct p.barcode, p.name, p.category, p.manufacturer, p.cost, p.stocklevel FROM product p, batch b WHERE p.barcode=b.barcode AND p.active=1 AND ISNULL(b.expiry)"
elif option == 1:
countQuery = "SELECT count(distinct product.barcode) FROM product, batch WHERE product.barcode=batch.barcode AND product.active=1 AND ISNULL(batch.expiry) AND product.barcode= %d" % valueSearch
resultQuery = "SELECT distinct p.barcode, p.name, p.category, p.manufacturer, p.cost, p.stocklevel FROM product p, batch b WHERE p.barcode=b.barcode AND p.active=1 AND ISNULL(b.expiry) AND p.barcode= %d" % valueSearch
elif option == 2:
valueSearch = '%' + valueSearch + '%'
countQuery = "SELECT count(distinct product.barcode) FROM product, batch WHERE product.barcode=batch.barcode AND product.active=1 AND ISNULL(batch.expiry) AND product.name LIKE '%s'" % valueSearch
resultQuery = "SELECT distinct p.barcode, p.name, p.category, p.manufacturer, p.cost, p.stocklevel FROM product p, batch b WHERE p.barcode=b.barcode AND p.active=1 AND ISNULL(b.expiry) AND p.name LIKE '%s'" % valueSearch
cur.execute(countQuery)
count = cur.fetchone()
count = count[0]
cur.execute(resultQuery)
rowProducts = cur.fetchall()
connectDb.closeDatabaseConnection(conn, cur)
return count, rowProducts
def getPerishableList(option, valueSearch = None):
count = None
rowProducts = None
conn, cur = connectDb.connectToDatabase()
if option == 0:
countQuery = "SELECT count(distinct product.barcode) FROM product, batch WHERE product.barcode=batch.barcode AND product.active=1 AND NOT ISNULL(batch.expiry)"
resultQuery = "SELECT distinct p.barcode, p.name, p.category, p.manufacturer, p.cost, p.stocklevel FROM product p, batch b WHERE p.barcode=b.barcode AND p.active=1 AND NOT ISNULL(b.expiry)"
elif option == 1:
countQuery = "SELECT count(distinct product.barcode) FROM product, batch WHERE product.barcode=batch.barcode AND product.active=1 AND NOT ISNULL(batch.expiry) AND product.barcode= %d" % valueSearch
resultQuery = "SELECT distinct p.barcode, p.name, p.category, p.manufacturer, p.cost, p.stocklevel FROM product p, batch b WHERE p.barcode=b.barcode AND p.active=1 AND NOT ISNULL(b.expiry) AND p.barcode= %d" % valueSearch
elif option == 2:
valueSearch = '%' + valueSearch + '%'
countQuery = "SELECT count(distinct product.barcode) FROM product, batch WHERE product.barcode=batch.barcode AND product.active=1 AND NOT ISNULL(batch.expiry) AND product.name LIKE '%s'" % valueSearch
resultQuery = "SELECT distinct p.barcode, p.name, p.category, p.manufacturer, p.cost, p.stocklevel FROM product p, batch b WHERE p.barcode=b.barcode AND p.active=1 AND NOT ISNULL(b.expiry) AND p.name LIKE '%s'" % valueSearch
cur.execute(countQuery)
count = cur.fetchone()
count = count[0]
cur.execute(resultQuery)
rowProducts = cur.fetchall()
connectDb.closeDatabaseConnection(conn, cur)
return count, rowProducts
def getPromoList(option, valueSearch = None):
count = None
rowPromo = None
conn, cur = connectDb.connectToDatabase()
if option == 0:
countQuery = "SELECT count(distinct promotion.promoid) FROM product, promotion WHERE product.barcode=promotion.barcode AND product.active=1 AND promotion.active=1 AND (ISNULL(promotion.expiry) OR promotion.expiry>CURDATE())"
resultQuery = "SELECT distinct pr.promoid, pr.barcode, p.name, p.cost, p.stocklevel, pr.value FROM product p, promotion pr WHERE p.barcode=pr.barcode AND p.active=1 AND pr.active=1 AND (ISNULL(pr.expiry) OR pr.expiry>CURDATE())"
elif option == 1:
countQuery = "SELECT count(distinct promotion.promoid) FROM product, promotion WHERE product.barcode=promotion.barcode AND product.active=1 AND promotion.active=1 AND (ISNULL(promotion.expiry) OR promotion.expiry>CURDATE()) AND product.barcode= %d" % valueSearch
resultQuery = "SELECT distinct pr.promoid, pr.barcode, p.name, p.cost, p.stocklevel, pr.value FROM product p, promotion pr WHERE p.barcode=pr.barcode AND p.active=1 AND pr.active=1 AND (ISNULL(pr.expiry) OR pr.expiry>CURDATE()) AND p.barcode= %d" % valueSearch
elif option == 2:
countQuery = "SELECT count(distinct promotion.promoid) FROM product, promotion WHERE product.barcode=promotion.barcode AND product.active=1 AND (ISNULL(promotion.expiry) OR promotion.expiry>CURDATE()) AND promotion.promoid= %d" % valueSearch
resultQuery = "SELECT distinct pr.promoid, pr.barcode, p.name, p.cost, p.stocklevel, pr.value FROM product p, promotion pr WHERE p.barcode=pr.barcode AND p.active=1 AND (ISNULL(pr.expiry) OR pr.expiry>CURDATE()) AND pr.promoid= %d" % valueSearch
elif option == 3:
valueSearch = '%' + valueSearch + '%'
countQuery = "SELECT count(distinct promotion.promoid) FROM product, promotion WHERE product.barcode=promotion.barcode AND product.active=1 AND promotion.active=1 AND (ISNULL(promotion.expiry) OR promotion.expiry>CURDATE()) AND product.name LIKE '%s'" % valueSearch
resultQuery = "SELECT distinct pr.promoid, pr.barcode, p.name, p.cost, p.stocklevel, pr.value FROM product p, promotion pr WHERE p.barcode=pr.barcode AND p.active=1 AND pr.active=1 AND (ISNULL(pr.expiry) OR pr.expiry>CURDATE()) AND p.name LIKE '%s'" % valueSearch
cur.execute(countQuery)
count = cur.fetchone()
count = count[0]
cur.execute(resultQuery)
rowPromo = cur.fetchall()
connectDb.closeDatabaseConnection(conn, cur)
return count, rowPromo