def increaseQty(id):
updateStmt = "UPDATE " + db_constants[
'STOCK_TABLE'] + " SET " + stock_table[
'QUANTITY'] + " = " + stock_table[
'QUANTITY'] + " + 1 WHERE " + stock_table[
'PRODUCT_ID'] + " =?;"
updateFields = [id]
id = executeQuery(updateStmt, updateFields)
return id
def updateEstimation(id, month, rate):
monthID = getMonthID(month)
stmt = "UPDATE " + db_constants[
'ESTIMATIONS_TABLE'] + "SET " + estimation_table[
'ESTIMATED_RATE'] + " =? WHERE " + estimation_table[
'MONTH_ID'] + "=? AND " + estimation_table['PRODUCT_ID'] + "=?;"
fields = [rate, monthID, id]
lastrowid = executeQuery(stmt, fields)
return lastrowid
def addToStock(id, qty, created):
stmt = "INSERT INTO " + db_constants['STOCK_TABLE'] + " ( " + stock_table[
'PRODUCT_ID'] + ", " + stock_table['SIZE'] + ", " + stock_table[
'QUANTITY'] + ", " + stock_table['ADDED'] + ", " + stock_table[
'MANUFACTURED'] + ") VALUES(?,?,?,?,?);"
dateAdded = datetime.today()
fields = [id, 0, qty, dateAdded, created]
id = executeQuery(stmt, fields)
return id
def removeFromStock(id):
stmt = "DELETE FROM " + db_constants[
'STOCK_TABLE'] + " WHERE " + stock_table['PRODUCT_ID'] + " =?;"
fields = [id]
id = executeQuery(stmt, fields)
#removes item from stock
#only callable through decreaseQty
#otherwise flag would be neccessary to see if it has been added to history yet
return id
def insertEstimation(id, month, rate):
stmt = "INSERT INTO " + db_constants[
'ESTIMATIONS_TABLE'] + " ( " + estimation_table[
'MONTH_ID'] + ", " + estimation_table[
'PRODUCT_ID'] + ", " + estimation_table[
'ESTIMATED_RATE'] + ") VALUES(?,?,?);"
monthID = getMonthID(month)
fields = [monthID, id, rate]
id = executeQuery(stmt, fields)
return id
def addNewProduct(name, created, expires, qty):
shelflife = (expires - created).days
stmt = "INSERT INTO " + db_constants[
'PRODUCT_TABLE'] + " ( " + product_table[
'PRODUCT_NAME'] + ", " + product_table[
'SHELFLIFE'] + ") VALUES(?,?);"
fields = [name, shelflife]
id = executeQuery(stmt, fields)
addToStock(id, qty, created)
return id
def updateHistory(id, added, removed, rate):
stmt = "INSERT INTO " + db_constants[
'PRODUCT_HISTORY_TABLE'] + " ( " + history_table[
'PRODUCT_ID'] + ", " + history_table[
'ADDED'] + ", " + history_table[
'REMOVED'] + ", " + history_table[
'ACTUAL_RATE'] + ") VALUES(?,?,?,?);"
actualRate = calculateRate(added, removed)
fields = [id, added, removed, actualRate]
id = executeQuery(stmt, fields)
return id
def decreaseQty(id):
#add to history
stockInfo = moveToHistory(id)
predictRate(id)
#Check if there still is products left in stock
if stockInfo[0][stock_table['QUANTITY']] > 1:
updateStmt = "UPDATE " + db_constants[
'STOCK_TABLE'] + " SET " + stock_table[
'QUANTITY'] + " = " + stock_table[
'QUANTITY'] + " - 1 WHERE " + stock_table[
'PRODUCT_ID'] + "=?;"
updateFields = [id]
id = executeQuery(updateStmt, updateFields)
else:
id = removeFromStock(id)
return redirect('/')