def increaseCount(conn):
id = request.args.get('itemID')
execute(
conn,
"UPDATE Items SET stockNum = (Items.stockNum + 1) WHERE Items.itemID = "
+ id + "")
return True
def addItemToInventory(conn):
type = request.args.get('itemType')
price = request.args.get('itemPrice')
stockNum = request.args.get('itemStockNum')
manu = request.args.get('itemManufacturer')
execute(
conn,
"INSERT INTO Items (stockNum, borrowedNum, price, manufacturer, type) VALUES (?, 0, ?, ?, ?)",
[stockNum, price, manu, type])
return True
def decreaseCount(conn):
id = request.args.get('itemID')
stockNum = request.args.get('stockNum')
borrowedNum = request.args.get('borrowedNum')
if borrowedNum >= stockNum:
return False
else:
execute(
conn,
"UPDATE Items SET stockNum = (Items.stockNum - 1) WHERE Items.itemID = "
+ id + "")
return True
def returnItem(conn):
itemID = request.args.get('itemID')
staffID = request.args.get('staffID')
dateBorrowed = request.args.get('dateBorrowed')
execute(
conn,
"UPDATE Uses SET dateReturned = DATE('now') WHERE Uses.itemID = " +
itemID + " AND Uses.staffID = " + staffID +
" AND DATE(Uses.dateBorrowed) = ?", [dateBorrowed])
execute(
conn,
"UPDATE Items SET borrowedNum = (Items.borrowedNum - 1) WHERE Items.itemID = "
+ itemID + "")
def borrowItemToday(conn):
staffID = request.args.get('staffID')
itemID = request.args.get('itemID')
foundItem = execute(
conn, "SELECT * FROM Items WHERE Items.itemID = " + itemID + "")
foundStaff = execute(
conn, "SELECT * FROM Staff WHERE Staff.staffID = " + staffID + "")
if foundItem == [] or foundStaff == []:
return False
else:
if foundItem[0]['borrowedNum'] >= foundItem[0]['stockNum']:
return False
else:
execute(
conn,
"INSERT INTO Uses (itemID, staffID, dateBorrowed, dateReturned) VALUES (?, ?, DATE('now'), NULL)",
[itemID, staffID])
execute(
conn,
"UPDATE Items SET borrowedNum = (Items.borrowedNum + 1) WHERE Items.itemID = "
+ itemID + "")
return True
def items(conn):
return execute(
conn,
"SELECT i.itemID as ID, i.type as TYPE, i.manufacturer as MANUFACTURER FROM Items AS i"
)
def itemsReport(conn):
id = request.args.get('itemID')
return execute(
conn,
"SELECT s.staffID as 'Staff ID', s.name as 'Staff Name', u.dateBorrowed as 'Date borrowed', u.dateReturned as 'Date returned' FROM ITEMS as i INNER JOIN Uses as u ON i.itemID = u.itemID INNER JOIN Staff as s ON u.staffID = s.staffID WHERE i.itemID = "
+ id + " ORDER BY u.dateReturned IS NULL DESC, u.dateBorrowed DESC")
def getItemInformation(conn):
id = request.args.get('itemID')
return execute(
conn,
"SELECT i.itemID, i.type, i.manufacturer, i.stockNum, i.borrowedNum, i.price, i.price * i.stockNum as totalPrice FROM Items as i WHERE i.itemID = "
+ id + "")
def getOldestUses(conn):
amount = request.args.get('amount-to-show')
return execute(
conn,
"SELECT i.itemID as 'Item ID', i.type as 'Item Type', MAX(u.dateBorrowed) as 'Most Recent Date Used' FROM Items as i INNER JOIN Uses as u ON i.itemID = u.itemID WHERE u.dateReturned IS NOT NULL GROUP BY i.itemID ORDER BY MAX(u.dateBorrowed) ASC LIMIT "
+ amount + "")
def getOldestCount(conn):
amount = request.args.get('amount-to-show')
return execute(
conn,
"SELECT i.itemID as 'Item ID', i.type as 'Item Type', COUNT(i.itemID) as 'Total Uses' FROM Items as i INNER JOIN Uses as u ON i.itemID = u.itemID WHERE u.dateReturned IS NOT NULL GROUP BY i.itemID ORDER BY COUNT(i.itemID) ASC LIMIT "
+ amount + "")
def uses(conn):
return execute(
conn,
"SELECT u.itemID as 'Item ID', i.type as 'Item Type', u.staffID as 'Staff ID', s.name as 'Staff Name', u.dateBorrowed as 'Date borrowed', u.dateReturned as 'Date returned' FROM Items as i INNER JOIN Uses AS u ON i.itemID = u.itemID INNER JOIN Staff as s ON u.staffID = s.staffID ORDER BY u.dateReturned IS NULL DESC, u.dateBorrowed DESC"
)
def getHistory(conn):
staffID = request.args.get('staffID')
return execute(conn, "SELECT i.itemID as 'Item ID', i.type as 'Item Type', u.dateBorrowed as 'Date borrowed', u.dateReturned as 'Date returned' FROM ITEMS as i INNER JOIN Uses as u ON i.itemID = u.itemID INNER JOIN Staff as s ON u.staffID = s.staffID WHERE s.staffID = "+staffID+" ORDER BY u.dateReturned IS NULL DESC, u.dateBorrowed DESC")
def getStaffInfo(conn):
staffID = request.args.get('staffID')
return execute(conn, "SELECT s.staffID, s.name, s.department FROM Staff as s INNER JOIN Uses as u ON s.staffID = u.staffID INNER JOIN Items as i ON u.itemID = i.itemID WHERE s.staffID = "+staffID+" GROUP BY s.staffID")
def getTotalPrice(conn):
staffID = request.args.get('staffID')
return execute(conn, "SELECT SUM(i.price) as totalPrice FROM Staff as s INNER JOIN Uses as u ON s.staffID = u.staffID INNER JOIN Items as i ON u.itemID = i.itemID WHERE s.staffID = "+staffID+" AND u.dateReturned IS NULL GROUP BY s.staffID")
def staff(conn):
return execute(conn, "SELECT s.staffID as 'Staff ID', s.name as 'Name', s.age as 'Age', s.department as 'Department', s.address as 'Address', s.phoneNum as 'Phone Number' FROM Staff AS s")