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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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")