def updateEmployee(request):
Authorizer.authorizeManager(request)
employeeID = request.matchdict['id']
postVars = request.POST
validKeys = ['ssn', 'lastName', 'firstName', 'address', 'city', 'state', 'zipCode', 'telephone', 'hourlyRate', 'type']
acceptedValues = []
queryAppend = []
query = "UPDATE Employees SET "
for key in validKeys:
if key in postVars:
queryAppend.append(key + " = %s")
acceptedValues.append(postVars[key])
acceptedValues.append(request.matchdict['id'])
query = query + ', '.join(queryAppend) + " WHERE ID = %s"
try:
cnx = mysql.connector.connect(user='******', password='******', host='127.0.0.1', database='305')
cursor = cnx.cursor()
cursor.execute(query, tuple(acceptedValues))
cursor.close()
cnx.commit()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err))
raise exc.HTTPOk()
def managerDashboard(request):
Authorizer.authorizeManager(request)
values = {
'currentUser': None,
}
if('currentUser' in request.session):
values["currentUser"] = request.session['currentUser']
return values
def salesReport(request):
Authorizer.authorizeManager(request)
getVars = request.GET
validKeys = ["month", "year", "itemID", "customerID", "itemType"]
acceptedValues = []
queryAppend = []
report = []
query = "SELECT * FROM Sales_Report WHERE "
for key in validKeys:
if key in getVars:
if key == "month":
queryAppend.append("MONTH(time) = %s")
acceptedValues.append(getVars[key])
elif key == "year":
queryAppend.append("YEAR(time) = %s")
acceptedValues.append(getVars[key])
else:
queryAppend.append(key + " = %s")
acceptedValues.append(getVars[key])
query = query + " AND ".join(queryAppend)
try:
cnx = mysql.connector.connect(user="******", password="******", host="127.0.0.1", database="305")
cursor = cnx.cursor(dictionary=True)
cursor.execute(query, tuple(acceptedValues))
for row in cursor:
reportValues = {}
for key in row:
if isinstance(row[key], datetime):
reportValues[key] = row[key].isoformat()
elif isinstance(row[key], Decimal):
reportValues[key] = str(row[key])
else:
reportValues[key] = row[key]
report.append(reportValues)
cursor.close()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
return report
def addEmployee(request):
Authorizer.authorizeManager(request)
requiredKeys = ['ssn', 'lastName', 'firstName', 'address', 'city', 'state', 'zipCode', 'telephone', 'startDate', 'hourlyRate', 'type']
requiredUserKeys = ['username','password']
postVars = request.POST
acceptedKeys = []
accepteduserKeys = []
for key in requiredKeys:
if(key in postVars):
acceptedKeys.append(postVars[key])
else:
print(key)
raise exc.HTTPBadRequest()
for key in requiredUserKeys:
if(key not in postVars):
raise exc.HTTPBadRequest()
salt = 'qwerty'
postVars['password'] = crypt.crypt(postVars['password'], salt)
query = ("INSERT INTO Employees(ssn, lastName, firstName, address, city, state, zipCode, telephone, startDate, hourlyRate, type)\
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);")
try:
cnx = mysql.connector.connect(user='******', password='******', host='127.0.0.1', database='305')
cursor = cnx.cursor()
cursor.execute(query, tuple(acceptedKeys))
query = ("INSERT INTO Users(username, password, type, id) VALUES (%s, %s, 1, LAST_INSERT_ID())")
cursor.execute(query, tuple([postVars['username'], postVars['password']]))
cursor.close()
cnx.commit()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), 500)
raise exc.HTTPOk()
def getEmployee(request):
Authorizer.authorizeManager(request)
employeeID = request.matchdict['id']
try:
cnx = mysql.connector.connect(user='******', password='******', host='127.0.0.1', database='305')
cursor = cnx.cursor(dictionary=True)
query = ("SELECT * FROM Employees WHERE id = %s")
cursor.execute(query, tuple([str(employeeID)]))
employee = {}
for employee in cursor:
employee = {
'type': employee['type'],
'id': employee['id'],
'name': employee['firstName'] + " " + employee['lastName'],
'firstName' : employee['firstName'],
'lastName' : employee['lastName'],
'address': employee['address'],
'city': employee['city'],
'state': employee['state'],
'zipCode': employee['zipCode'],
'telephone': employee['telephone'],
'startDate': employee['startDate'].isoformat(),
'hourlyRate': str(employee['hourlyRate']),
}
cursor.close()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
if(len(employee) == 0):
raise exc.HTTPNoContent()
return employee
def deleteEmployee(request):
Authorizer.authorizeManager(request)
employeeID = request.matchdict['id']
query= "DELETE FROM Employees WHERE id= %s"
try:
cnx = mysql.connector.connect(user='******', password='******', host='127.0.0.1', database='305')
cursor = cnx.cursor()
cursor.execute(query, tuple(employeeID))
cursor.close()
cnx.commit()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err))
raise exc.HTTPOk()
def revenueReport(request):
Authorizer.authorizeManager(request)
getVars = request.GET
query = "SELECT ItemName, SUM(Amount) AS revenue, COUNT(Amount) AS copiesSold FROM Sales_Report WHERE "
secondQuery = "SELECT ItemName, SUM(Amount) AS revenue, COUNT(Amount) AS copiesSold FROM Sales_Report WHERE MONTH(time) = MONTH(NOW()) AND YEAR(time) = YEAR(NOW()) AND "
value = None
if "employeeID" in getVars and "customerID" not in getVars and "itemID" not in getVars:
query = query + "monitorID = %s GROUP BY ItemName"
secondQuery = secondQuery + "monitorID = %s GROUP BY ItemName"
value = getVars["employeeID"]
elif "employeeID" not in getVars and "customerID" in getVars and "itemID" not in getVars:
query = query + "customerID = %s GROUP BY ItemName"
secondQuery = secondQuery + "customerID = %s GROUP BY ItemName"
value = getVars["customerID"]
elif "employeeID" not in getVars and "customerID" not in getVars and "itemID" in getVars:
query = query + "itemID = %s"
secondQuery = secondQuery + "itemID = %s"
value = getVars["itemID"]
else:
raise exc.HTTPBadRequest()
report = {}
totalReport = []
monthReport = []
try:
cnx = mysql.connector.connect(user="******", password="******", host="127.0.0.1", database="305")
cursor = cnx.cursor(dictionary=True)
cursor.execute(query, tuple([str(value)]))
for row in cursor:
totalReportValues = {}
for key in row:
if isinstance(row[key], datetime):
totalReportValues[key] = row[key].isoformat()
elif isinstance(row[key], Decimal):
totalReportValues[key] = str(row[key])
else:
totalReportValues[key] = row[key]
totalReport.append(totalReportValues)
report["total"] = totalReport
cursor.execute(secondQuery, tuple([str(value)]))
for row in cursor:
monthReportValues = {}
for key in row:
if isinstance(row[key], datetime):
monthReportValues[key] = row[key].isoformat()
elif isinstance(row[key], Decimal):
monthReportValues[key] = str(row[key])
else:
monthReportValues[key] = row[key]
monthReport.append(monthReportValues)
report["month"] = monthReport
cursor.close()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
return report
def apiRevenueStats(request):
Authorizer.authorizeManager(request)
getVars = request.GET
query1 = "SELECT SUM(Amount) AS revenue, COUNT(Amount) AS copiesSold, Items.* FROM Sales_Report LEFT JOIN Items on Items.id = Sales_Report.itemID GROUP BY itemID ORDER BY revenue DESC LIMIT 1"
query2 = "SELECT SUM(Amount) AS revenue, COUNT(Amount) AS copiesSold, Customers.* FROM Sales_Report LEFT JOIN Customers on Customers.id = Sales_Report.sellerID GROUP BY customerID ORDER BY revenue DESC LIMIT 1;"
query3 = "SELECT SUM(Amount) AS revenue, COUNT(Amount) AS copiesSold, Employees.* FROM Sales_Report LEFT JOIN Employees on Employees.id = Sales_Report.monitorID GROUP BY monitorID ORDER BY revenue DESC LIMIT 1;"
stats = {}
try:
cnx = mysql.connector.connect(user="******", password="******", host="127.0.0.1", database="305")
cursor = cnx.cursor(dictionary=True)
cursor.execute(query1)
row = cursor.fetchone()
stat = {}
for key in row:
if isinstance(row[key], datetime):
stat[key] = row[key].isoformat()
elif isinstance(row[key], Decimal):
stat[key] = str(row[key])
else:
stat[key] = row[key]
stats["item"] = stat
query1 = "SELECT * FROM ItemsImages WHERE itemID = %s"
cursor.execute(query1, tuple([str(stat["id"])]))
images = []
for row in cursor:
images.append(row["url"])
stats["item"]["images"] = images
cursor.execute(query2)
row = cursor.fetchone()
stat = {}
for key in row:
if isinstance(row[key], datetime):
stat[key] = row[key].isoformat()
elif isinstance(row[key], Decimal):
stat[key] = str(row[key])
else:
stat[key] = row[key]
stats["customer"] = stat
cursor.execute(query3)
row = cursor.fetchone()
stat = {}
for key in row:
if isinstance(row[key], datetime):
stat[key] = row[key].isoformat()
if isinstance(row[key], date):
stat[key] = row[key].isoformat()
elif isinstance(row[key], Decimal):
stat[key] = str(row[key])
else:
stat[key] = row[key]
stats["employee"] = stat
cursor.close()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
return stats
