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