def initialiseValues(self):
self.ui.error_Barcode.clear()
self.ui.lineEdit_Price.clear()
self.ui.lineEdit_Manu.clear()
self.ui.lineEdit_Category.clear()
self.ui.lineEdit_Name.clear()
barcode = self.ui.lineEdit_Barcode.text()
if isNumber(barcode) & (len(str(barcode)) == 8):
conn, cur = connectDb.connectToDatabase()
checkForExistence = "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 ;" % int(
barcode)
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if count == 1:
query = "SELECT name, category, manufacturer, cost FROM product, batch WHERE product.barcode=batch.barcode AND product.active=1 AND NOT ISNULL(batch.expiry) AND product.barcode= %d" % int(
barcode)
cur.execute(query)
row = cur.fetchone()
self.ui.lineEdit_Name.setText(row[0])
if row[1] is not None:
self.ui.lineEdit_Category.setText(row[1])
if row[2] is not None:
self.ui.lineEdit_Manu.setText(row[2])
self.ui.lineEdit_Price.setText(str(row[3]))
else:
self.ui.error_Barcode.setText("*no such perishable product")
connectDb.closeDatabaseConnection(conn, cur)
else:
self.ui.error_Barcode.setText("*no such perishable product")
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
def removePromo(self):
self.ui.error_promoId.clear()
promoId = self.ui.lineEdit_Id.text()
if (len(str(promoId)) > 0):
if (isNumber(promoId)):
promoId = int(promoId)
conn, cur = connectDb.connectToDatabase()
checkForExistence = "SELECT count(*) FROM promotion WHERE promoid = %d AND active = 1;" % promoId
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if (count == 1):
query = "UPDATE promotion SET active = 0 WHERE promoid = %d;" % promoId
cur.execute(query)
conn.commit()
self.parent.viewPromo()
self.close()
else:
self.ui.error_promoId.setText("*no such promoId")
connectDb.closeDatabaseConnection(conn, cur)
else:
self.ui.error_promoId.setText("*incorrect input")
else:
self.ui.error_promoId.setText("*required")
def removePromo(self):
self.ui.error_promoId.clear()
promoId = self.ui.lineEdit_Id.text()
if (len(str(promoId)) > 0):
if (isNumber(promoId)):
promoId = int(promoId)
conn, cur = connectDb.connectToDatabase()
checkForExistence = "SELECT count(*) FROM promotion WHERE promoid = %d AND active = 1;" % promoId
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if (count == 1):
query = "UPDATE promotion SET active = 0 WHERE promoid = %d;" % promoId
cur.execute(query)
conn.commit()
self.parent.viewPromo()
self.close()
else:
self.ui.error_promoId.setText("*no such promoId")
connectDb.closeDatabaseConnection(conn, cur)
else:
self.ui.error_promoId.setText("*incorrect input")
else:
self.ui.error_promoId.setText("*required")
def deleteProduct(self):
self.ui.error_Barcode.clear()
barcode = self.ui.lineEdit_Barcode.text()
if len(str(barcode)) > 0:
if isNumber(barcode):
barcode = int(barcode)
conn, cur = connectDb.connectToDatabase()
checkForExistence = "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 ;" % barcode
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if count != 0:
cur.execute(
"UPDATE product SET active=0 WHERE barcode = %d" %
barcode)
else:
self.ui.error_Barcode.setText("*no such product exists")
conn.commit()
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewPerishables()
self.close()
else:
self.ui.lineEdit_Barcode.clear()
self.ui.error_Barcode.setText("*incorrect input")
else:
self.ui.error_Barcode.setText("*required")
def removeBatch(barcode, batchDate):
conn, cur = connectDb.connectToDatabase()
cur.execute(
"SELECT count(*) FROM batch WHERE barcode = %d AND batchdate = \'%s\';"
% (barcode, batchDate))
ifbatch = cur.fetchone()
ifbatch = ifbatch[0]
if ifbatch == 1:
cur.execute(
"SELECT stock FROM batch WHERE barcode = %d AND batchdate = \'%s\';"
% (barcode, batchDate))
stock = cur.fetchone()
stock = stock[0]
cur.execute(
"UPDATE product SET stocklevel = stocklevel - %d WHERE barcode = %d;"
% (stock, barcode))
cur.execute(
"DELETE FROM batch WHERE barcode = %d AND batchdate = \'%s\';" %
(barcode, batchDate))
tid = getNextTransactionId()
cur.execute("INSERT INTO transaction values(%d, 1, CURDATE(), 0);" %
tid)
cur.execute("SELECT cost FROM product WHERE barcode = %d;" % barcode)
cost = cur.fetchone()
cost = cost[0]
execute.cur(
"INSERT INTO transactiondetails VALUES(%d, %d, NULL, %d, 'write off');"
% (tid, barcode, stock))
message = "\nWrite-off successful"
else:
message = "\nWrite-off not successful"
conn.commit()
connectDb.closeDatabaseConnection(conn, cur)
return message
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
def deleteDeviceUnit(self):
self.ui.error_Device.clear()
device = self.ui.lineEdit_Device.text()
if (len(str(device)) > 0):
if (isNumber(device)):
device = int(device)
conn, cur = connectDb.connectToDatabase()
checkForExistence = "SELECT count(*) FROM cashier WHERE id = %d ;" % device
cur.execute(checkForExistence)
countCashier = cur.fetchone()
countCashier = countCashier[0]
checkForExistence = "SELECT count(*) FROM pdumap WHERE id = %d ;" % device
cur.execute(checkForExistence)
countPdu = cur.fetchone()
countPdu = countPdu[0]
if (countPdu == 1) | (countCashier == 1):
if countCashier == 1:
query = "UPDATE cashier SET active = 0 WHERE id = %d;" % device
if countPdu == 1:
query = "DELETE FROM pdumap WHERE id = %d;" % device
cur.execute(query)
cur.execute("UPDATE flag SET flag=1 WHERE 1")
conn.commit()
self.parent.viewUnits()
self.close()
else:
self.ui.error_Device.setText("*no such device")
connectDb.closeDatabaseConnection(conn, cur)
else:
self.ui.error_Device.setText("*incorrect input")
else:
self.ui.error_Device.setText("*required")
def deleteDeviceUnit(self):
self.ui.error_Device.clear()
device = self.ui.lineEdit_Device.text()
if (len(str(device)) > 0):
if (isNumber(device)):
device = int(device)
conn, cur = connectDb.connectToDatabase()
checkForExistence = "SELECT count(*) FROM cashier WHERE id = %d ;" % device
cur.execute(checkForExistence)
countCashier = cur.fetchone()
countCashier = countCashier[0]
checkForExistence = "SELECT count(*) FROM pdumap WHERE id = %d ;" % device
cur.execute(checkForExistence)
countPdu = cur.fetchone()
countPdu = countPdu[0]
if (countPdu == 1) | (countCashier == 1):
if countCashier == 1:
query = "UPDATE cashier SET active = 0 WHERE id = %d;" % device
if countPdu == 1:
query = "DELETE FROM pdumap WHERE id = %d;" % device
cur.execute(query)
cur.execute("UPDATE flag SET flag=1 WHERE 1")
conn.commit()
self.parent.viewUnits()
self.close()
else:
self.ui.error_Device.setText("*no such device")
connectDb.closeDatabaseConnection(conn, cur)
else:
self.ui.error_Device.setText("*incorrect input")
else:
self.ui.error_Device.setText("*required")
def dashboard(self):
conn, cur = connectDb.connectToDatabase()
noOfTrans = "SELECT count(distinct t.transactionid) FROM transaction t WHERE t.date = CURDATE()"
cur.execute(noOfTrans)
countTransDay = cur.fetchone()
countTransDay = countTransDay[0]
if countTransDay != 0:
moneyTrans = "SELECT sum(price) FROM transaction WHERE transaction.date = CURDATE()"
cur.execute(moneyTrans)
totalMoney = cur.fetchone()
totalMoney = totalMoney[0]
itemsSold = "SELECT sum(td.unitsold) FROM transaction t, transactiondetails td WHERE t.transactionid = td.transactionid AND t.date = CURDATE()"
cur.execute(itemsSold)
totalItemsSold = cur.fetchone()
totalItemsSold = totalItemsSold[0]
else:
totalMoney = 0
totalItemsSold = 0
self.ui.noTrans.setText("Number of Transactions Today : %d" %
countTransDay)
self.ui.moneyDay.setText("Total Money Received Today : $%.2f" %
totalMoney)
self.ui.noItems.setText("Number of Items sold Today: %d" %
totalItemsSold)
connectDb.closeDatabaseConnection(conn, cur)
def nextProduct(self):
self.ui.error_barcode.clear()
self.ui.error_qty.clear()
barcode = self.ui.lineEdit_barcode.text()
qty = self.ui.lineEdit_qty.text()
if (len(str(barcode)) == 8) & (len(str(qty)) > 0):
if (isNumber(barcode)) & (isNumber(qty)):
barcode = int(barcode)
qty = int(qty)
if barcode>0:
conn, cur = connectDb.connectToDatabase()
checkExistence = "SELECT count(*) FROM product WHERE active = 1 AND barcode = %d" % barcode
checkQty = "SELECT count(*) FROM product WHERE active = 1 AND barcode = %d AND stocklevel >= %d" % (barcode, qty)
cur.execute(checkQty)
count = cur.fetchone()
count = count[0]
if count != 0:
self.items[barcode] = qty
(costPerUnit, qtyCost, promoid) = getInfo.getPrice(barcode, qty)
self.totalBill = self.totalBill + qtyCost
if promoid is None:
promoid = 0
self.promos[barcode] = promoid
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
else:
cur.execute(checkExistence)
count = cur.fetchone()
count = count[0]
if count != 0:
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*not enough stock")
else:
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
self.ui.error_barcode.setText("*no such product")
connectDb.closeDatabaseConnection(conn, cur)
else:
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
self.ui.error_barcode.setText("*incorrect input")
else:
if not isNumber(barcode):
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
self.ui.error_barcode.setText("*incorrect input")
if not isNumber(qty):
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*incorrect input")
else:
if (len(str(qty)) == 0):
self.ui.error_qty.setText("*required")
if (len(str(barcode)) == 0):
self.ui.lineEdit_qty.clear()
self.ui.error_barcode.setText("*required")
elif (len(str(barcode)) != 8):
self.ui.lineEdit_qty.clear()
self.ui.error_barcode.setText("*incorrect input")
def updateProduct(self):
barcode = self.ui.lineEdit_Barcode.text()
name = str(self.ui.lineEdit_Name.text())
category = str(self.ui.lineEdit_Category.text())
manu = str(self.ui.lineEdit_Manu.text())
price = self.ui.lineEdit_Price.text()
self.ui.error_Barcode.clear()
self.ui.error_Price.clear()
self.ui.error_Name.clear()
flag = self.validateInput(barcode, name, category, manu, price)
if flag != 0 & (len(str(barcode)) == 8):
barcode = int(barcode)
price = float(price)
if (price > 0) & (barcode > 0):
conn, cur = connectDb.connectToDatabase()
checkForExistence = "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 ;" % barcode
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if count == 1:
if len(category) == 0:
if len(manu) == 0:
insertProduct = "UPDATE product SET name='%s', category=NULL, manufacturer=NULL, cost=%.2f WHERE barcode = %d" % (
name, price, barcode)
else:
insertProduct = "UPDATE product SET name='%s', category=NULL, manufacturer='%s', cost=%.2f WHERE barcode = %d" % (
name, manu, price, barcode)
else:
if len(manu) == 0:
insertProduct = "UPDATE product SET name='%s', category='%s', manufacturer=NULL, cost=%.2f WHERE barcode = %d" % (
name, category, price, barcode)
else:
insertProduct = "UPDATE product SET name='%s', category='%s', manufacturer='%s', cost=%.2f WHERE barcode = %d" % (
name, category, manu, price, barcode)
cur.execute(insertProduct)
conn.commit()
self.parent.viewPerishables()
self.close()
else:
self.ui.message.setText(
"No Perishable Product with the entered barcode found to update"
)
connectDb.closeDatabaseConnection(conn, cur)
else:
if int(barcode) <= 0:
self.ui.lineEdit_Barcode.clear()
self.ui.error_Barcode.setText("*incorrect input")
elif float(price) <= 0:
self.ui.lineEdit_Price.clear()
self.ui.error_Price.setText("*incorrect input")
else:
self.ui.lineEdit_Price.clear()
self.ui.lineEdit_Manu.clear()
self.ui.lineEdit_Category.clear()
self.ui.lineEdit_Name.clear()
def getPrice(barcode, qty):
#This function will return 3 values (barcode of unit price, price of the qty products, corresponding promo used)
#if both returned values are none: product doesn't exist
#if second value returned is none: insufficient stock
conn, cur = connectDb.connectToDatabase()
cur.execute("SELECT count(*) FROM product WHERE barcode = %d;" % barcode)
products = cur.fetchone()
products = products[0]
if products == 0: #if no such product exists
connectDb.closeDatabaseConnection(conn, cur)
return None, None, None
else: #product exists
cur.execute(
"SELECT cost, stocklevel FROM product WHERE barcode = %d;" %
barcode)
products = cur.fetchone()
costPerUnit = products[0]
stockAvailable = products[1]
#check if suffficient stock is available
if qty > stockAvailable:
connectDb.closeDatabaseConnection(conn, cur)
return costPerUnit, None, None
else:
#check for promo and return qty cost accordingly
cur.execute(
"SELECT promoid, type, value FROM promotion WHERE expiry>CURDATE();"
)
bestPromo = 0
qtyCost = qty * costPerUnit
for promos in cur.fetchall():
promos_type = promos[1]
#Enter all the different type of promotions here
if promos_type == 0: #bundle type promotion
promos_value = promos[2]
if qty >= promos_value: #if criteria for bundle is met
newqty = 9 * qty * costPerUnit / 10
if newqty < qtyCost: #if bundle promotion is the cheaper of the promotions, then store this as new qtyCost
qtyCost = newqty
bestPromo = promos[0]
elif promos_type == 1: #direct discount per unit promotion
promos_value = promos[2]
newqty = 9 * qty * costPerUnit / 10
if newqty < qtyCost: #if promotion is the cheaper of the promotions, then store this as new qtyCost
qtyCost = newqty
bestPromo = promos[0]
if bestPromo == 0:
bestPromo = None
connectDb.closeDatabaseConnection(conn, cur)
return costPerUnit, qtyCost, bestPromo
def getNextTransactionId():
conn, cur = connectDb.connectToDatabase()
cur.execute("SELECT max(transactionid) FROM transaction;")
maxTrans = cur.fetchone()
maxTrans = maxTrans[0]
if maxTrans is None:
transactionid = 1000000000
else:
transactionid = maxTrans+1
connectDb.closeDatabaseConnection(conn, cur)
return transactionid
def addCashier(self):
self.ui.error_Id.clear()
self.ui.error_Pwd.clear()
cid = self.ui.person_id.text()
pwd = self.ui.person_password.text()
if (len(str(cid))>0) & (len(str(pwd))>0):
if isNumber(cid) & isNumber(pwd) & (len(str(pwd)) <= 6):
cid = int(cid)
pwd = int(pwd)
if cid<65536:
conn, cur = connectDb.connectToDatabase()
checkForExistence = "SELECT count(*) FROM cashier WHERE id = %d ;" % cid
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
checkForExistence = "SELECT count(*) FROM pdumap WHERE id = %d ;" % cid
cur.execute(checkForExistence)
countOther = cur.fetchone()
countOther = countOther[0]
if (count == 0) & (countOther == 0):
insertQuery = "INSERT INTO cashier VALUES(%d, %d, 1)" % (cid, pwd)
cur.execute(insertQuery)
cur.execute("UPDATE flag SET flag=1 WHERE 1")
conn.commit()
self.parent.viewUnits()
self.close()
else:
self.ui.person_id.clear()
self.ui.person_password.clear()
self.ui.error_Id.setText("*device exists")
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewUnits()
else:
self.ui.person_id.clear()
self.ui.person_password.clear()
self.ui.error_Id.setText("*id must be <65536")
else:
if not isNumber(cid):
self.ui.person_id.clear()
self.ui.person_password.clear()
self.ui.error_Id.setText("*incorrect input")
if not isNumber(pwd) | (len(str(pwd)) > 6):
self.ui.person_id.clear()
self.ui.person_password.clear()
self.ui.error_Pwd.setText("*incorrect input")
else:
if len(str(cid)) == 0:
self.ui.person_id.clear()
self.ui.person_password.clear()
self.ui.error_Id.setText("*required")
if len(str(pwd)) == 0:
self.ui.person_password.clear()
self.ui.error_Pwd.setText("*required")
def getNextTransactionId():
conn, cur = connectDb.connectToDatabase()
cur.execute("SELECT max(transactionid) FROM transaction;")
maxTrans = cur.fetchone()
maxTrans = maxTrans[0]
if maxTrans is None:
transactionid = 1000000000
else:
transactionid = maxTrans + 1
connectDb.closeDatabaseConnection(conn, cur)
return transactionid
def nextProduct(self):
self.ui.error_barcode.clear()
self.ui.error_qty.clear()
barcode = self.ui.lineEdit_barcode.text()
qty = self.ui.lineEdit_qty.text()
if (len(str(barcode)) == 8) & (len(str(qty)) > 0):
if (isNumber(barcode)) & (isNumber(qty)):
barcode = int(barcode)
qty = int(qty)
if (qty>0):
conn, cur = connectDb.connectToDatabase()
checkExistence = "SELECT count(*) FROM product WHERE active = 1 AND barcode = %d" % barcode
checkQty = "SELECT count(*) FROM product WHERE active = 1 AND barcode = %d AND stocklevel >= %d" % (barcode, qty)
cur.execute(checkQty)
count = cur.fetchone()
count = count[0]
if count != 0:
self.items[barcode] = qty
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
else:
cur.execute(checkExistence)
count = cur.fetchone()
count = count[0]
if count != 0:
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*not enough stock")
else:
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
self.ui.error_barcode.setText("*no such product")
connectDb.closeDatabaseConnection(conn, cur)
else:
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*incorrect input")
else:
if not isNumber(barcode):
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
self.ui.error_barcode.setText("*incorrect input")
if not isNumber(qty):
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*incorrect input")
else:
if (len(str(qty)) == 0):
self.ui.error_qty.setText("*required")
if (len(str(barcode)) == 0):
self.ui.lineEdit_qty.clear()
self.ui.error_barcode.setText("*required")
elif (len(str(barcode)) != 8):
self.ui.lineEdit_qty.clear()
self.ui.error_barcode.setText("*incorrect input")
def getPrice(barcode, qty):
#This function will return 3 values (barcode of unit price, price of the qty products, corresponding promo used)
#if both returned values are none: product doesn't exist
#if second value returned is none: insufficient stock
conn, cur = connectDb.connectToDatabase()
cur.execute("SELECT count(*) FROM product WHERE barcode = %d;" % barcode)
products = cur.fetchone()
products = products[0]
if products == 0: #if no such product exists
connectDb.closeDatabaseConnection(conn, cur)
return None, None, None
else: #product exists
cur.execute("SELECT cost, stocklevel FROM product WHERE barcode = %d;" % barcode)
products = cur.fetchone()
costPerUnit = products[0]
stockAvailable = products[1]
#check if suffficient stock is available
if qty>stockAvailable:
connectDb.closeDatabaseConnection(conn, cur)
return costPerUnit, None, None
else:
#check for promo and return qty cost accordingly
cur.execute("SELECT promoid, type, value FROM promotion WHERE expiry>CURDATE();")
bestPromo = 0;
qtyCost = qty*costPerUnit
for promos in cur.fetchall():
promos_type = promos[1]
#Enter all the different type of promotions here
if promos_type == 0: #bundle type promotion
promos_value = promos[2]
if qty>=promos_value: #if criteria for bundle is met
newqty = 9*qty*costPerUnit/10
if newqty < qtyCost: #if bundle promotion is the cheaper of the promotions, then store this as new qtyCost
qtyCost = newqty
bestPromo = promos[0]
elif promos_type == 1: #direct discount per unit promotion
promos_value = promos[2]
newqty = 9*qty*costPerUnit/10
if newqty < qtyCost: #if promotion is the cheaper of the promotions, then store this as new qtyCost
qtyCost = newqty
bestPromo = promos[0]
if bestPromo == 0:
bestPromo = None
connectDb.closeDatabaseConnection(conn, cur)
return costPerUnit, qtyCost, bestPromo
def getManager(mid, pwd):
conn, cur = connectDb.connectToDatabase()
if isNumber(mid) == True:
mid = int(mid)
else:
connectDb.closeDatabaseConnection(conn, cur)
return None
cur.execute("SELECT count(*) FROM manager WHERE id=%d AND pwd=\'%s\'" % (mid,pwd))
managerExist = cur.fetchone()
if managerExist[0] == 1:
connectDb.closeDatabaseConnection(conn, cur)
return mid
else :
connectDb.closeDatabaseConnection(conn, cur)
return None
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 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 getManager(mid, pwd):
conn, cur = connectDb.connectToDatabase()
if isNumber(mid) == True:
mid = int(mid)
else:
connectDb.closeDatabaseConnection(conn, cur)
return None
cur.execute("SELECT count(*) FROM manager WHERE id=%d AND pwd=\'%s\'" %
(mid, pwd))
managerExist = cur.fetchone()
if managerExist[0] == 1:
connectDb.closeDatabaseConnection(conn, cur)
return mid
else:
connectDb.closeDatabaseConnection(conn, cur)
return None
def getCashier(cid, pwd):
conn, cur = connectDb.connectToDatabase()
if isNumber(cid) == True & isNumber(pwd) == True:
cid = int(cid)
pwd = int(pwd)
else:
connectDb.closeDatabaseConnection(conn, cur)
return None
cur.execute("SELECT count(*) FROM cashier WHERE id=%d AND pwd=%d" % (cid,pwd))
cashierExist = cur.fetchone()
if cashierExist[0] == 1:
connectDb.closeDatabaseConnection(conn, cur)
return cid
else :
connectDb.closeDatabaseConnection(conn, cur)
return None
def getCashier(cid, pwd):
conn, cur = connectDb.connectToDatabase()
if isNumber(cid) == True & isNumber(pwd) == True:
cid = int(cid)
pwd = int(pwd)
else:
connectDb.closeDatabaseConnection(conn, cur)
return None
cur.execute("SELECT count(*) FROM cashier WHERE id=%d AND pwd=%d" %
(cid, pwd))
cashierExist = cur.fetchone()
if cashierExist[0] == 1:
connectDb.closeDatabaseConnection(conn, cur)
return cid
else:
connectDb.closeDatabaseConnection(conn, cur)
return None
def getProductInfo(barcode):
conn, cur = connectDb.connectToDatabase()
cur.execute("SELECT count(*) FROM product WHERE barcode = %d;" % (barcode))
countProduct = cur.fetchone()
countProduct = countProduct[0]
if countProduct == 1:
cur.execute("SELECT * FROM product WHERE barcode = %d;" % barcode)
prodDetails = cur.fetchall()
prodDetails = prodDetails[0]
name = prodDetails[1]
category = prodDetails[2]
manufacturer = prodDetails[3]
costPerUnit = prodDetails[4]
stocklevel = prodDetails[5]
connectDb.closeDatabaseConnection(conn, cur)
return name, category, manufacturer, costPerUnit, stocklevel
else:
connectDb.closeDatabaseConnection(conn, cur)
return None, None, None, None, None
def getProductInfo(barcode):
conn, cur = connectDb.connectToDatabase()
cur.execute("SELECT count(*) FROM product WHERE barcode = %d;" % (barcode))
countProduct = cur.fetchone()
countProduct = countProduct[0]
if countProduct == 1:
cur.execute("SELECT * FROM product WHERE barcode = %d;" % barcode)
prodDetails = cur.fetchall()
prodDetails = prodDetails[0]
name = prodDetails[1]
category = prodDetails[2]
manufacturer = prodDetails[3]
costPerUnit = prodDetails[4]
stocklevel = prodDetails[5]
connectDb.closeDatabaseConnection(conn, cur)
return name, category, manufacturer, costPerUnit, stocklevel
else:
connectDb.closeDatabaseConnection(conn, cur)
return None, None, None, None, None
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 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 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 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 removeBatch(barcode, batchDate):
conn, cur = connectDb.connectToDatabase()
cur.execute("SELECT count(*) FROM batch WHERE barcode = %d AND batchdate = \'%s\';" % (barcode, batchDate))
ifbatch = cur.fetchone()
ifbatch = ifbatch[0]
if ifbatch == 1:
cur.execute("SELECT stock FROM batch WHERE barcode = %d AND batchdate = \'%s\';" % (barcode, batchDate))
stock = cur.fetchone()
stock = stock[0]
cur.execute("UPDATE product SET stocklevel = stocklevel - %d WHERE barcode = %d;" % (stock,barcode))
cur.execute("DELETE FROM batch WHERE barcode = %d AND batchdate = \'%s\';" % (barcode, batchDate))
tid = getNextTransactionId()
cur.execute("INSERT INTO transaction values(%d, 1, CURDATE(), 0);" % tid)
cur.execute("SELECT cost FROM product WHERE barcode = %d;" % barcode)
cost = cur.fetchone()
cost = cost[0]
execute.cur("INSERT INTO transactiondetails VALUES(%d, %d, NULL, %d, 'write off');" % (tid, barcode, stock));
message = "\nWrite-off successful"
else:
message = "\nWrite-off not successful"
conn.commit()
connectDb.closeDatabaseConnection(conn, cur)
return message
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 deleteProduct(self):
self.ui.error_Barcode.clear()
barcode = self.ui.lineEdit_Barcode.text()
if len(str(barcode)) > 0:
if isNumber(barcode):
barcode = int(barcode)
conn, cur = connectDb.connectToDatabase()
checkForExistence = "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 ;" % barcode
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if count != 0:
cur.execute("UPDATE product SET active=0 WHERE barcode = %d" % barcode)
else:
self.ui.error_Barcode.setText("*no such product exists")
conn.commit()
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewPerishables()
self.close()
else:
self.ui.lineEdit_Barcode.clear()
self.ui.error_Barcode.setText("*incorrect input")
else:
self.ui.error_Barcode.setText("*required")
def updatePdu(self):
self.ui.error_Dev.clear()
self.ui.error_Bar.clear()
self.ui.error_Port.clear()
port = self.ui.device_port.text()
did = self.ui.device_id.text()
barcode = self.ui.device_barcode.text()
if (len(str(did))>0) & (len(str(barcode))>0) & (len(str(port))>0):
if isNumber(did) & isNumber(barcode) & (len(str(barcode)) == 8) & isNumber(port):
did = int(did)
barcode = int(barcode)
port = int(port)
if (did<65536) & (did>=0) & (port<8) & (port>=0):
conn, cur = connectDb.connectToDatabase()
checkForExistence = "SELECT count(*) FROM pdumap WHERE pdumap.id = %d AND pdumap.port = %d;" % (did,port)
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
checkForExistence = "SELECT count(*) FROM pdumap WHERE pdumap.id = %d;" % (did)
cur.execute(checkForExistence)
countPort = cur.fetchone()
countPort = countPort[0]
checkForExistence = "SELECT count(*) FROM cashier WHERE cashier.id = %d ;" % did
cur.execute(checkForExistence)
countOther = cur.fetchone()
countOther = countOther[0]
if (count == 1) & (countOther == 0):
checkForExistence1 = "SELECT count(*) FROM product WHERE product.barcode = %d ;" % barcode
cur.execute(checkForExistence1)
countProd = cur.fetchone()
countProd = countProd[0]
if countProd == 1:
insertQuery = "UPDATE pdumap SET barcode = %d WHERE id = %d AND port = %d" % (barcode, did, port)
cur.execute(insertQuery)
cur.execute("UPDATE flag SET flag=1 WHERE 1")
conn.commit()
self.parent.viewUnits()
self.close()
else:
self.ui.device_barcode.clear()
self.ui.error_Bar.setText("*barcode does not exist")
else:
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
if countPort >= 1:
self.ui.error_Port.setText("*port doesn't exist")
else:
self.ui.error_Dev.setText("*pdu doesn't exist")
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewUnits()
else:
if not ((did<65536) & (did>0)):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Dev.setText("*id must be <65536")
if not ((port<8) & (port>=0)):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Port.setText("*port must be btw 0-7")
else:
if not isNumber(did):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Dev.setText("*incorrect input")
if not isNumber(barcode):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Bar.setText("*incorrect input")
if not isNumber(port):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Port.setText("*incorrect input")
if (len(str(barcode)) != 8):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Bar.setText("*incorrect input")
else:
if len(str(did)) == 0:
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Dev.setText("*required")
if len(str(barcode)) == 0:
self.ui.device_barcode.clear()
self.ui.error_Bar.setText("*required")
if len(str(port)) == 0:
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Dev.setText("*required")
def restockProduct(self):
self.ui.error_Barcode.clear()
self.ui.error_Stock.clear()
self.ui.error_Expiry.clear()
barcode = self.ui.lineEdit_Barcode.text()
stock = self.ui.lineEdit_Stock.text()
expiry = self.ui.lineEdit_Expiry.text()
if (len(str(barcode)) > 0) & (len(str(stock)) > 0) & (len(str(expiry)) > 0):
if isNumber(barcode) & isNumber(stock):
year = expiry[:4]
month = expiry[5:7]
day = expiry[8:]
if isNumber(year) & isNumber(month) & isNumber(day) & (int(stock)>0):
dateInput = QtCore.QDate(int(year), int(month), int(day))
if dateInput:
barcode = int(barcode)
stock = int(stock)
today = QtCore.QDate.currentDate()
if today<dateInput:
if stock>0:
conn, cur = connectDb.connectToDatabase()
checkForExistence = "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 ;" % barcode
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if count != 0:
cur.execute("UPDATE product SET stocklevel=stocklevel+%d WHERE barcode = %d" % (stock, barcode))
cur.execute("INSERT INTO batch VALUES(%d, CURDATE(), %d, '%s')" % (barcode, stock, expiry))
else:
self.ui.lineEdit_Barcode.clear()
self.ui.lineEdit_Stock.clear()
self.ui.lineEdit_Expiry.clear()
self.ui.error_Barcode.setText("*no such product exists")
conn.commit()
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewPerishables()
self.close()
else:
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*incorrect input")
else:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect date")
else:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect input")
else:
if int(stock) <= 0:
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*incorrect input")
else:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect input")
else:
if not isNumber(stock):
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*incorrect input")
if not isNumber(barcode):
self.ui.lineEdit_Barcode.clear()
self.ui.lineEdit_Stock.clear()
self.ui.lineEdit_Expiry.clear()
self.ui.error_Barcode.setText("*incorrect input")
else:
if len(str(barcode)) == 0:
self.ui.lineEdit_Barcode.clear()
self.ui.error_Barcode.setText("*required")
if len(str(stock)) == 0:
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*required")
if len(str(expiry)) == 0:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Stock.setText("*required")
def completeTrans(self):
self.ui.error_barcode.clear()
self.ui.error_qty.clear()
barcode = self.ui.lineEdit_barcode.text()
qty = self.ui.lineEdit_qty.text()
if (len(str(barcode)) == 8) & (len(str(qty)) > 0):
if (isNumber(barcode)) & (isNumber(qty)):
barcode = int(barcode)
qty = int(qty)
conn, cur = connectDb.connectToDatabase()
checkExistence = "SELECT count(*) FROM product WHERE active = 1 AND barcode = %d" % barcode
checkQty = "SELECT count(*) FROM product WHERE active = 1 AND barcode = %d AND stocklevel >= %d" % (barcode, qty)
cur.execute(checkQty)
count = cur.fetchone()
count = count[0]
if count != 0:
self.items[barcode] = qty
(costPerUnit, qtyCost, promoid) = getInfo.getPrice(barcode, qty)
self.totalBill = self.totalBill + qtyCost
if promoid is None:
promoid = 0
self.promos[barcode] = promoid
tid = getInfo.getNextTransactionId()
cid = 0 #Manager
cur.execute("INSERT INTO transaction VALUES (%d, %d, CURDATE(), %.2f);" % (tid, cid, self.totalBill))
keys = self.items.keys()
cur1 = conn.cursor()
for barcode in keys:
qty = self.items.get(barcode)
cur.execute("SELECT batchdate, stock FROM batch WHERE barcode = %d;" % barcode)
stockAccountedFor = 0
for allBatches in cur.fetchall():
batchStock = allBatches[1]
batchDate = allBatches[0]
#Even after adding this batch, we are unable to reach the required stock to be removed
if stockAccountedFor+batchStock <= qty:
cur1.execute("DELETE FROM batch WHERE barcode = %d AND batchdate = \'%s\';" % (barcode,batchDate))
stockAccountedFor = stockAccountedFor + batchStock
else:
cur1.execute("UPDATE batch SET stock = stock - %d WHERE barcode=%d AND batchdate=\'%s\';" % (qty-stockAccountedFor,barcode,batchDate))
stockAccountedFor = qty
if promoid == 0:
cur.execute("INSERT INTO transactiondetails VALUES(%d, %d, NULL, %d,'sale');" % (tid,barcode,qty))
else:
cur.execute("INSERT INTO transactiondetails VALUES(%d, %d, %d, %d,'sale');" % (tid,barcode,promoid,qty))
cur.execute("UPDATE product SET stocklevel = stocklevel - %d WHERE barcode=%d;" % (qty,barcode))
conn.commit()
self.parent.initialiseAllViews()
self.close()
else:
cur.execute(checkExistence)
count = cur.fetchone()
count = count[0]
if count != 0:
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*not enough stock")
else:
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
self.ui.error_barcode.setText("*no such product")
connectDb.closeDatabaseConnection(conn, cur)
else:
if not isNumber(barcode):
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
self.ui.error_barcode.setText("*incorrect input")
if not isNumber(qty):
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*incorrect input")
else:
if (len(str(qty)) == 0):
self.ui.error_qty.setText("*required")
if (len(str(barcode)) == 0):
self.ui.lineEdit_qty.clear()
self.ui.error_barcode.setText("*required")
elif (len(str(barcode)) != 8):
self.ui.lineEdit_qty.clear()
self.ui.error_barcode.setText("*incorrect input")
def addProduct(self):
barcode = self.ui.lineEdit_Barcode.text()
name = str(self.ui.lineEdit_Name.text())
category = str(self.ui.lineEdit_Category.text())
manu = str(self.ui.lineEdit_Manu.text())
price = self.ui.lineEdit_Price.text()
stock = self.ui.lineEdit_Stock.text()
expiry = self.ui.lineEdit_Expiry.text()
self.ui.error_Expiry.clear()
self.ui.error_Barcode.clear()
self.ui.error_Price.clear()
self.ui.error_Stock.clear()
self.ui.error_Name.clear()
flag = self.validateInput(barcode, name, category, manu, price, stock,
expiry)
if flag != 0 & (len(str(barcode)) == 8):
year = expiry[:4]
month = expiry[5:7]
day = expiry[8:]
if isNumber(year) & isNumber(month) & isNumber(day) & (
int(barcode) >= 10000000) & (float(price) > 0) & (
int(stock) > 0) & (int(barcode) <= 99999999):
dateInput = QtCore.QDate(int(year), int(month), int(day))
if not dateInput:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect input")
else:
today = QtCore.QDate.currentDate()
if today > dateInput:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect date")
else:
barcode = int(barcode)
price = float(price)
stock = int(stock)
conn, cur = connectDb.connectToDatabase()
checkForExistence = "SELECT count(*) FROM product WHERE product.barcode = %d ;" % barcode
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if count == 0:
if len(category) == 0:
if len(manu) == 0:
insertProduct = "INSERT INTO product VALUES(%d, '%s', NULL, NULL, %.2f, %d, 1)" % (
barcode, name, price, stock)
else:
insertProduct = "INSERT INTO product VALUES(%d, '%s', NULL, '%s', %.2f, %d, 1)" % (
barcode, name, manu, price, stock)
else:
if len(manu) == 0:
insertProduct = "INSERT INTO product VALUES(%d, '%s', '%s', NULL, %.2f, %d, 1)" % (
barcode, name, category, price, stock)
else:
insertProduct = "INSERT INTO product VALUES(%d, '%s', '%s', '%s', %.2f, %d, 1)" % (
barcode, name, category, manu, price,
stock)
cur.execute(insertProduct)
insertBatch = "INSERT INTO batch VALUES(%d, CURDATE(), %d, '%s')" % (
barcode, stock, expiry)
cur.execute(insertBatch)
else:
self.ui.message.setText(
"Cannot Add Product since the barcode already exists"
)
conn.commit()
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewPerishables()
self.close()
else:
if (int(barcode) > 99999999) | (int(barcode) < 10000000):
self.ui.lineEdit_Barcode.clear()
self.ui.error_Barcode.setText("*incorrect input")
elif float(price) <= 0:
self.ui.lineEdit_Price.clear()
self.ui.error_Price.setText("*incorrect input")
elif int(stock) <= 0:
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*incorrect input")
else:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect input")
else:
self.ui.lineEdit_Barcode.clear()
self.ui.lineEdit_Expiry.clear()
self.ui.lineEdit_Stock.clear()
self.ui.lineEdit_Price.clear()
self.ui.lineEdit_Manu.clear()
self.ui.lineEdit_Category.clear()
self.ui.lineEdit_Name.clear()
def restockProduct(self):
self.ui.error_Barcode.clear()
self.ui.error_Stock.clear()
self.ui.error_Expiry.clear()
barcode = self.ui.lineEdit_Barcode.text()
stock = self.ui.lineEdit_Stock.text()
expiry = self.ui.lineEdit_Expiry.text()
if (len(str(barcode)) > 0) & (len(str(stock)) > 0) & (len(str(expiry))
> 0):
if isNumber(barcode) & isNumber(stock):
year = expiry[:4]
month = expiry[5:7]
day = expiry[8:]
if isNumber(year) & isNumber(month) & isNumber(day) & (
int(stock) > 0):
dateInput = QtCore.QDate(int(year), int(month), int(day))
if dateInput:
barcode = int(barcode)
stock = int(stock)
today = QtCore.QDate.currentDate()
if today < dateInput:
if stock > 0:
conn, cur = connectDb.connectToDatabase()
checkForExistence = "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 ;" % barcode
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if count != 0:
cur.execute(
"UPDATE product SET stocklevel=stocklevel+%d WHERE barcode = %d"
% (stock, barcode))
cur.execute(
"INSERT INTO batch VALUES(%d, CURDATE(), %d, '%s')"
% (barcode, stock, expiry))
else:
self.ui.lineEdit_Barcode.clear()
self.ui.lineEdit_Stock.clear()
self.ui.lineEdit_Expiry.clear()
self.ui.error_Barcode.setText(
"*no such product exists")
conn.commit()
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewPerishables()
self.close()
else:
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*incorrect input")
else:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect date")
else:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect input")
else:
if int(stock) <= 0:
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*incorrect input")
else:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect input")
else:
if not isNumber(stock):
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*incorrect input")
if not isNumber(barcode):
self.ui.lineEdit_Barcode.clear()
self.ui.lineEdit_Stock.clear()
self.ui.lineEdit_Expiry.clear()
self.ui.error_Barcode.setText("*incorrect input")
else:
if len(str(barcode)) == 0:
self.ui.lineEdit_Barcode.clear()
self.ui.error_Barcode.setText("*required")
if len(str(stock)) == 0:
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*required")
if len(str(expiry)) == 0:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Stock.setText("*required")
def completeWriteoff(self):
self.ui.error_barcode.clear()
self.ui.error_qty.clear()
barcode = self.ui.lineEdit_barcode.text()
qty = self.ui.lineEdit_qty.text()
if (len(str(barcode)) == 8) & (len(str(qty)) > 0):
if (isNumber(barcode)) & (isNumber(qty)):
barcode = int(barcode)
qty = int(qty)
if (qty < 0):
conn, cur = connectDb.connectToDatabase()
checkExistence = "SELECT count(*) FROM product WHERE active = 1 AND barcode = %d" % barcode
checkQty = "SELECT count(*) FROM product WHERE active = 1 AND barcode = %d AND stocklevel >= %d" % (barcode, qty)
cur.execute(checkQty)
count = cur.fetchone()
count = count[0]
if count != 0:
self.items[barcode] = qty
tid = getInfo.getNextTransactionId()
cid = 0 #Manager
cur.execute("INSERT INTO transaction VALUES (%d, %d, CURDATE(), 0.00);" % (tid, cid))
keys = self.items.keys()
cur1 = conn.cursor()
for barcode in keys:
qty = self.items.get(barcode)
cur.execute("SELECT batchdate, stock FROM batch WHERE barcode = %d;" % barcode)
stockAccountedFor = 0
for allBatches in cur.fetchall():
batchStock = allBatches[1]
batchDate = allBatches[0]
#Even after adding this batch, we are unable to reach the required stock to be removed
if stockAccountedFor+batchStock <= qty:
cur1.execute("DELETE FROM batch WHERE barcode = %d AND batchdate = \'%s\';" % (barcode,batchDate))
stockAccountedFor = stockAccountedFor + batchStock
else:
cur1.execute("UPDATE batch SET stock = stock - %d WHERE barcode=%d AND batchdate=\'%s\';" % (qty-stockAccountedFor,barcode,batchDate))
stockAccountedFor = qty
cur.execute("INSERT INTO transactiondetails VALUES(%d, %d, NULL, %d,'writeoff');" % (tid,barcode,qty))
cur.execute("UPDATE product SET stocklevel = stocklevel - %d WHERE barcode=%d;" % (qty,barcode))
conn.commit()
self.close()
else:
cur.execute(checkExistence)
count = cur.fetchone()
count = count[0]
if count != 0:
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*not enough stock")
else:
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
self.ui.error_barcode.setText("*no such product")
connectDb.closeDatabaseConnection(conn, cur)
else:
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*incorrect input")
else:
if not isNumber(barcode):
self.ui.lineEdit_qty.clear()
self.ui.lineEdit_barcode.clear()
self.ui.error_barcode.setText("*incorrect input")
if not isNumber(qty):
self.ui.lineEdit_qty.clear()
self.ui.error_qty.setText("*incorrect input")
else:
if (len(str(qty)) == 0):
self.ui.error_qty.setText("*required")
if (len(str(barcode)) == 0):
self.ui.lineEdit_qty.clear()
self.ui.error_barcode.setText("*required")
elif (len(str(barcode)) != 8):
self.ui.lineEdit_qty.clear()
self.ui.error_barcode.setText("*incorrect input")
def createPromo(self):
self.ui.error_Barcode.clear()
self.ui.error_Value.clear()
self.ui.error_Expiry.clear()
barcode = self.ui.lineEdit_Barcode.text()
value = self.ui.lineEdit_Value.text()
expiry = self.ui.lineEdit_Expiry.text()
if (len(str(barcode)) > 0) & (len(str(value)) > 0):
if (isNumber(barcode)) & (isNumber(value)):
barcode = int(barcode)
value = int(value)
conn, cur = connectDb.connectToDatabase()
query = "SELECT count(*) FROM product WHERE barcode = %d" % barcode
cur.execute(query)
count = cur.fetchone()
count = count[0]
if count == 1:
cur.execute("SELECT max(promoid) FROM promotion;")
maxPromo = cur.fetchone()
maxPromo = maxPromo[0]
if maxPromo is None:
promoid = 1
else:
promoid = maxPromo + 1
if (len(str(expiry)) > 0):
year = expiry[:4]
month = expiry[5:7]
day = expiry[8:]
if isNumber(year) & isNumber(month) & isNumber(day):
dateInput = QtCore.QDate(int(year), int(month),
int(day))
if not dateInput:
self.ui.error_Expiry.setText(
"*incorrect input")
self.ui.lineEdit_Expiry.clear()
else:
today = QtCore.QDate.currentDate()
if today > dateInput:
self.ui.error_Expiry.setText(
"*incorrect date")
self.ui.lineEdit_Expiry.clear()
else:
promoQuery = "INSERT INTO promotion VALUES(%d, %d, 1, %d, '%s', 1)" % (
barcode, promoid, value, expiry)
cur.execute(promoQuery)
conn.commit()
connectDb.closeDatabaseConnection(
conn, cur)
self.parent.viewPromo()
self.close()
else:
self.ui.error_Expiry.setText("*incorrect input")
self.ui.lineEdit_Expiry.clear()
else:
promoQuery = "INSERT INTO promotion VALUES(%d, %d, 0, %d, NULL, 1)" % (
barcode, promoid, value)
cur.execute(promoQuery)
conn.commit()
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewPromo()
self.close()
else:
self.ui.error_Barcode.setText("*no such product")
self.ui.lineEdit_Expiry.clear()
self.ui.lineEdit_Value.clear()
else:
if not isNumber(barcode):
self.ui.error_Barcode.setText("*incorrect input")
self.ui.lineEdit_Expiry.clear()
self.ui.lineEdit_Value.clear()
if not isNumber(value):
self.ui.error_Value.setText("*incorrect input")
self.ui.lineEdit_Expiry.clear()
self.ui.lineEdit_Value.clear()
else:
if len(str(barcode)) == 0:
self.ui.error_Barcode.setText("*required")
if len(str(value)) == 0:
self.ui.error_Value.setText("*required")
def addProduct(self):
barcode = self.ui.lineEdit_Barcode.text()
name = str(self.ui.lineEdit_Name.text())
category = str(self.ui.lineEdit_Category.text())
manu = str(self.ui.lineEdit_Manu.text())
price = self.ui.lineEdit_Price.text()
stock = self.ui.lineEdit_Stock.text()
expiry = self.ui.lineEdit_Expiry.text()
self.ui.error_Expiry.clear()
self.ui.error_Barcode.clear()
self.ui.error_Price.clear()
self.ui.error_Stock.clear()
self.ui.error_Name.clear()
flag = self.validateInput(barcode, name, category, manu, price, stock, expiry)
if flag != 0 & (len(str(barcode)) == 8):
year = expiry[:4]
month = expiry[5:7]
day = expiry[8:]
if isNumber(year) & isNumber(month) & isNumber(day) & (int(barcode)>=10000000) & (float(price)>0) & (int(stock)>0) & (int(barcode)<=99999999):
dateInput = QtCore.QDate(int(year), int(month), int(day))
if not dateInput:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect input")
else:
today = QtCore.QDate.currentDate()
if today>dateInput:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect date")
else:
barcode = int(barcode)
price = float(price)
stock = int(stock)
conn, cur = connectDb.connectToDatabase()
checkForExistence = "SELECT count(*) FROM product WHERE product.barcode = %d ;" % barcode
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
if count == 0:
if len(category) == 0:
if len(manu) == 0:
insertProduct = "INSERT INTO product VALUES(%d, '%s', NULL, NULL, %.2f, %d, 1)" % (barcode, name, price, stock)
else:
insertProduct = "INSERT INTO product VALUES(%d, '%s', NULL, '%s', %.2f, %d, 1)" % (barcode, name, manu, price, stock)
else:
if len(manu) == 0:
insertProduct = "INSERT INTO product VALUES(%d, '%s', '%s', NULL, %.2f, %d, 1)" % (barcode, name, category, price, stock)
else:
insertProduct = "INSERT INTO product VALUES(%d, '%s', '%s', '%s', %.2f, %d, 1)" % (barcode, name, category, manu, price, stock)
cur.execute(insertProduct)
insertBatch = "INSERT INTO batch VALUES(%d, CURDATE(), %d, '%s')" % (barcode, stock, expiry)
cur.execute(insertBatch)
else:
self.ui.message.setText("Cannot Add Product since the barcode already exists")
conn.commit()
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewPerishables()
self.close()
else:
if (int(barcode) > 99999999) | (int(barcode)<10000000):
self.ui.lineEdit_Barcode.clear()
self.ui.error_Barcode.setText("*incorrect input")
elif float(price) <= 0:
self.ui.lineEdit_Price.clear()
self.ui.error_Price.setText("*incorrect input")
elif int(stock) <= 0:
self.ui.lineEdit_Stock.clear()
self.ui.error_Stock.setText("*incorrect input")
else:
self.ui.lineEdit_Expiry.clear()
self.ui.error_Expiry.setText("*incorrect input")
else:
self.ui.lineEdit_Barcode.clear()
self.ui.lineEdit_Expiry.clear()
self.ui.lineEdit_Stock.clear()
self.ui.lineEdit_Price.clear()
self.ui.lineEdit_Manu.clear()
self.ui.lineEdit_Category.clear()
self.ui.lineEdit_Name.clear()
def updatePdu(self):
self.ui.error_Dev.clear()
self.ui.error_Bar.clear()
self.ui.error_Port.clear()
port = self.ui.device_port.text()
did = self.ui.device_id.text()
barcode = self.ui.device_barcode.text()
if (len(str(did)) > 0) & (len(str(barcode)) > 0) & (len(str(port)) >
0):
if isNumber(did) & isNumber(barcode) & (len(str(barcode))
== 8) & isNumber(port):
did = int(did)
barcode = int(barcode)
port = int(port)
if (did < 65536) & (did >= 0) & (port < 8) & (port >= 0):
conn, cur = connectDb.connectToDatabase()
checkForExistence = "SELECT count(*) FROM pdumap WHERE pdumap.id = %d AND pdumap.port = %d;" % (
did, port)
cur.execute(checkForExistence)
count = cur.fetchone()
count = count[0]
checkForExistence = "SELECT count(*) FROM pdumap WHERE pdumap.id = %d;" % (
did)
cur.execute(checkForExistence)
countPort = cur.fetchone()
countPort = countPort[0]
checkForExistence = "SELECT count(*) FROM cashier WHERE cashier.id = %d ;" % did
cur.execute(checkForExistence)
countOther = cur.fetchone()
countOther = countOther[0]
if (count == 1) & (countOther == 0):
checkForExistence1 = "SELECT count(*) FROM product WHERE product.barcode = %d ;" % barcode
cur.execute(checkForExistence1)
countProd = cur.fetchone()
countProd = countProd[0]
if countProd == 1:
insertQuery = "UPDATE pdumap SET barcode = %d WHERE id = %d AND port = %d" % (
barcode, did, port)
cur.execute(insertQuery)
cur.execute("UPDATE flag SET flag=1 WHERE 1")
conn.commit()
self.parent.viewUnits()
self.close()
else:
self.ui.device_barcode.clear()
self.ui.error_Bar.setText(
"*barcode does not exist")
else:
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
if countPort >= 1:
self.ui.error_Port.setText("*port doesn't exist")
else:
self.ui.error_Dev.setText("*pdu doesn't exist")
connectDb.closeDatabaseConnection(conn, cur)
self.parent.viewUnits()
else:
if not ((did < 65536) & (did > 0)):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Dev.setText("*id must be <65536")
if not ((port < 8) & (port >= 0)):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Port.setText("*port must be btw 0-7")
else:
if not isNumber(did):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Dev.setText("*incorrect input")
if not isNumber(barcode):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Bar.setText("*incorrect input")
if not isNumber(port):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Port.setText("*incorrect input")
if (len(str(barcode)) != 8):
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Bar.setText("*incorrect input")
else:
if len(str(did)) == 0:
self.ui.device_id.clear()
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Dev.setText("*required")
if len(str(barcode)) == 0:
self.ui.device_barcode.clear()
self.ui.error_Bar.setText("*required")
if len(str(port)) == 0:
self.ui.device_port.clear()
self.ui.device_barcode.clear()
self.ui.error_Dev.setText("*required")
