def apiGetMailingList(request):
Authorizer.authorizeEmployee(request)
mailingListID = request.matchdict["id"]
mailingLists = {}
try:
cnx = mysql.connector.connect(user="******", password="******", host="127.0.0.1", database="305")
cursor = cnx.cursor(dictionary=True)
query = "SELECT * FROM MailingLists where id = %s"
cursor.execute(query, tuple([str(mailingListID)]))
for row in cursor:
mailingList = {"id": row["id"], "createdBy": row["createdBy"], "name": row["name"]}
query = "SELECT C.* from MailingListsMappings LEFT JOIN (SELECT id, firstName, lastName, email FROM Customers) as C ON C.id = MailingListsMappings.customerID and MailingListsMappings.listID = %s"
cursor.execute(query, tuple([str(mailingList["id"])]))
customers = []
for row in cursor:
customers.append({"id": row["id"], "name": row["firstName"] + " " + row["lastName"], "email": row["email"]})
mailingList["customers"] = customers
cursor.close()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
return mailingLists
def employeeDashboard(request):
Authorizer.authorizeEmployee(request)
values = {
'currentUser': None,
}
if('currentUser' in request.session):
values["currentUser"] = request.session['currentUser']
return values
def sold(request):
Authorizer.authorizeEmployee(request)
postVars = request.POST
requiredKeys = ['bidID', 'customerID', 'auctionID', 'itemID']
for key in requiredKeys:
if(key not in postVars):
raise exc.HTTPBadRequest()
try:
cnx = mysql.connector.connect(user='******', password='******', host='127.0.0.1', database='305')
cursor = cnx.cursor(dictionary=True)
query = """
INSERT INTO Wins (BidID, Time, CustomerID, AuctionID)
VALUES (%s,NOW(),%s, %s);
"""
cursor.execute(query, tuple([str(postVars['bidID']), str(postVars['customerID']), str(postVars['auctionID'])]))
query = """
UPDATE Items
SET CopiesSold = CopiesSold + 1, Stock=Stock-1
WHERE ID= %s
"""
cursor.execute(query, tuple([str(postVars['itemID'])]))
query = """
UPDATE Customers
SET ItemsSold=ItemsSold+1
WHERE ID = (SELECT SellerID
FROM Auctions
WHERE ID = %s);
"""
cursor.execute(query, tuple([str(postVars['auctionID'])]))
query = """
UPDATE Customers
SET ItemsPurchased=ItemsPurchased+1
WHERE ID = %s
"""
cursor.execute(query, tuple([str(postVars['customerID'])]))
cursor.close()
cnx.commit()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
raise exc.HTTPOk()
def deleteCustomer(request):
Authorizer.authorizeEmployee(request)
query = "DELETE FROM Customers WHERE id= %s"
try:
cnx = mysql.connector.connect(user='******', password='******', host='127.0.0.1', database='305')
cursor = cnx.cursor()
cursor.execute(query, tuple([request.matchdict['id']]))
cursor.close()
cnx.commit()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err))
raise exc.HTTPOk()
def allCustomers(request):
Authorizer.authorizeEmployee(request)
try:
cnx = mysql.connector.connect(user='******', password='******', host='127.0.0.1', database='305')
cursor = cnx.cursor(dictionary=True)
query = ("SELECT * FROM Customers")
cursor.execute(query)
items = []
for customer in cursor:
items.append({
'id': customer['id'],
'name': customer['firstName'] + customer['lastName'],
'firstName': customer['firstName'],
'lastName': customer['lastName'],
'address': customer['address'],
'city': customer['city'],
'state': customer['state'],
'zipCode': customer['zipCode'],
'telephone': customer['telephone'],
'email': customer['email'],
'creditCardNumber': customer['creditCardNumber'],
'itemsSold': customer['itemsSold'],
'itemsPurchased': customer['itemsPurchased'],
'rating': str(customer['rating'])
})
cursor.close()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
if(len(items) == 0):
raise exc.HTTPNoContent()
return items
def apiAddMailingList(request):
Authorizer.authorizeEmployee(request)
postVars = request.POST
print(postVars)
if "name" not in postVars:
raise exc.HTTPBadRequest()
try:
cnx = mysql.connector.connect(user="******", password="******", host="127.0.0.1", database="305")
cursor = cnx.cursor(dictionary=True)
query = "INSERT INTO MailingLists(name, createdBy) VALUES(%s, %s)"
cursor.execute(query, tuple([postVars["name"], Authorizer.getCurrentUser(request)["id"]]))
if "customers[]" in postVars:
# postVars.getall('customers[]')
query = "SELECT LAST_INSERT_ID() as id"
cursor.execute(query)
mailingListID = cursor.fetchone()["id"]
print(mailingListID)
for customerID in postVars.getall("customers[]"):
print(customerID)
query = "SELECT COUNT(*) as count FROM Customers where id = %s"
cursor.execute(query, tuple([str(customerID)]))
count = cursor.fetchone()["count"]
if count != 0:
query = "INSERT INTO MailingListsMappings(listID, customerID) VALUES (%s, %s)"
cursor.execute(query, tuple([mailingListID, customerID]))
else:
raise exc.HTTPBadRequest()
cursor.close()
cnx.commit()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
raise exc.HTTPOk()
def mailingList(request):
Authorizer.authorizeEmployee(request)
query = """SELECT email, concat(lastName, ' ', firstName) AS name FROM Customers"""
mailingList = []
try:
cnx = mysql.connector.connect(user="******", password="******", host="127.0.0.1", database="305")
cursor = cnx.cursor(dictionary=True)
cursor.execute(query)
for row in cursor:
customer = {}
for key in row:
customer[key] = row[key]
mailingList.append(customer)
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
return mailingList
def allEmployees(request):
Authorizer.authorizeEmployee(request)
try:
cnx = mysql.connector.connect(user='******', password='******', host='127.0.0.1', database='305')
cursor = cnx.cursor(dictionary=True)
query = ("SELECT * FROM Employees")
cursor.execute(query)
employees = []
for employee in cursor:
employees.append({
'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(employees) == 0):
raise exc.HTTPNoContent()
return employees
def apiAuctionWin(request):
Authorizer.authorizeEmployee(request)
auctionID = 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 Auctions where id = %s"
cursor.execute(query, tuple([str(auctionID)]))
auction = cursor.fetchone()
if auction["finished"] == 1:
cursor.close()
cnx.close()
raise exc.HTTPBadRequest()
elif auction["closingTime"] > datetime.now():
cursor.close()
cnx.close()
raise exc.HTTPBadRequest()
query = "UPDATE Auctions SET finished=1 WHERE id = %s"
cursor.execute(query, tuple([str(auctionID)]))
# query = "SELECT COUNT(*) as count, amount, id, customerID FROM Bids where auctionID = 1 ORDER BY amount DESC LIMIT 1"
query = "SELECT COUNT(*) as count FROM Bids where auctionID = %s"
cursor.execute(query, tuple([str(auctionID)]))
bid = cursor.fetchone()
if bid["count"] == 0:
cursor.close()
cnx.commit()
cnx.close()
raise exc.HTTPOk()
query = "SELECT amount, id, customerID FROM Bids WHERE auctionID = %s ORDER BY amount DESC LIMIT 1"
cursor.execute(query, tuple([str(auctionID)]))
bid = cursor.fetchone()
if bid["amount"] < auction["reserve"]:
cursor.close()
cnx.commit()
cnx.close()
raise exc.HTTPOk()
query = "INSERT INTO Wins (bidID, time, customerID, auctionID)\
VALUES (%s, NOW(), %s, %s);"
cursor.execute(query, tuple([bid["id"], bid["customerID"], auction["id"]]))
query = "UPDATE Items\
SET CopiesSold = CopiesSold + 1, Stock=Stock-1\
WHERE ID=%s"
cursor.execute(query, tuple([str(auction["itemID"])]))
print("itemUpdated")
query = "UPDATE Customers\
SET ItemsSold=ItemsSold+1\
WHERE ID = %s"
cursor.execute(query, tuple([str(auction["sellerID"])]))
print("seller")
query = "UPDATE Customers\
SET ItemsPurchased=ItemsPurchased+1\
WHERE ID = %s"
cursor.execute(query, tuple([str(bid["customerID"])]))
print("customer")
cursor.close()
cnx.commit()
cnx.close()
except mysql.connector.Error as err:
cursor.close()
cnx.close()
return Response("Something went wrong: {}".format(err), status=500)
raise exc.HTTPOk()
def apiAuctionsUnapproved(request):
Authorizer.authorizeEmployee(request)
auctions = []
try:
cnx = mysql.connector.connect(user="******", password="******", host="127.0.0.1", database="305")
cursor = cnx.cursor(dictionary=True)
query = "SELECT * FROM Auctions where closingTime<NOW() AND finished=0"
cursor.execute(query)
for row in cursor:
auctionInfo = {}
for key in row:
if isinstance(row[key], datetime):
auctionInfo[key] = row[key].isoformat()
elif isinstance(row[key], Decimal):
auctionInfo[key] = str(row[key])
else:
auctionInfo[key] = row[key]
auctions.append(auctionInfo)
for auction in auctions:
print(auction)
query = "SELECT * FROM Customers where id = %s"
cursor.execute(query, tuple([str(auction["sellerID"])]))
customer = cursor.fetchone()
print("hii")
auction["sellerName"] = customer["firstName"] + " " + customer["lastName"]
query = "SELECT name FROM Items where id=%s"
cursor.execute(query, tuple([str(auction["itemID"])]))
item = cursor.fetchone()
print("hiii")
auction["itemName"] = item["name"]
query = "SELECT url from ItemsImages where itemID=%s"
cursor.execute(query, tuple([str(auction["itemID"])]))
# print('hi')
imageArray = []
for row in cursor:
imageArray.append(row["url"])
auction["itemImage"] = imageArray
query = "SELECT COUNT(*) as count FROM Bids where auctionID = %s"
# print(tuple(str(auction["id"])))
print(auction["id"])
cursor.execute(query, tuple([str(auction["id"])]))
# print('loop started')
bid = cursor.fetchone()
if bid["count"] == 0:
print("no bids")
auction["winner"] = None
else:
query = "SELECT amount, id, customerID FROM Bids WHERE auctionID = %s ORDER BY amount DESC LIMIT 1"
cursor.execute(query, tuple([str(auction["id"])]))
bid = cursor.fetchone()
auction["amount"] = str(bid["amount"])
query = "SELECT * FROM Customers where id = %s"
print("h")
cursor.execute(query, tuple([str(bid["customerID"])]))
print("hi")
customer = cursor.fetchone()
auction["winnerName"] = customer["firstName"] + " " + customer["lastName"]
cursor.close()
cnx.close()
except mysql.connector.Error as err:
return Response("Something went wrong: {}".format(err), status=500)
return auctions