def favoriteDeal(self, deal, customer):
try:
# Check if the customer has already favorited this deal (select from Favorites).
query = """SELECT count(*) FROM Favorites
WHERE cid='{cid}' and did='{did}'""".format(
cid=customer.getCid(), did=deal.getDid())
result = DBUtils.getVar(self._conn, query)
# If so, return. You can only favorite once per deal
if (result > 0):
print("{name} has already favorited this deal".format(
name=customer.getFName()))
return
# Add favorite to favorite table, (linking customer and the deal)
query = """INSERT INTO Favorites VALUES (%s, %s)"""
DBUtils.executeUpdate(self._conn, query,
(customer.getCid(), deal.getDid()))
# Get the business associated with the deal
query = """
SELECT bid from Deals where dealId='{did}'
""".format(did=deal.getDid())
busID = DBUtils.getVar(self._conn, query)
# Update business's num favorites
query = """UPDATE Business SET numFavouritedDeals = numFavouritedDeals + 1
WHERE businessId='{bid}'
""".format(bid=busID)
DBUtils.executeUpdate(self._conn, query)
print("Deal favorited! Check your profile.")
except psycopg2.Error as e:
print(e)
def saveEmployeeRoles(self, fullname, managername, rolename, startdate,
enddate):
try:
if (enddate == None):
print("Must Specify an end date")
return False
roleid = DBUtils.getRow(
self._conn, "select roleid from roles where rolename like '" +
str(rolename) + "'")
employeeid = DBUtils.getRow(
self._conn,
"select employeeid from (SELECT E.EmployeeID, E.FirstName, E.LastName, CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME FROM Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID WHERE E.isEmployeed = 0) as data where fullname like '"
+ str(fullname) + "'")
managerid = DBUtils.getRow(
self._conn,
"select ManagerID from (SELECT E.ManagerID, (SELECT FullName FROM Managers where ManagerID = E.ManagerID) AS ManagerName FROM Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID WHERE E.isEmployeed = 0) as data where ManagerName like '"
+ str(managername) + "'")
query = "update employeeroles set roleid = " + str(
roleid[0]) + ", startdate = '" + str(
startdate) + "', enddate = '" + str(
enddate) + "' where employeeid = " + str(employeeid[0])
DBUtils.executeUpdate(self._conn, query)
query = "update employees set managerid = " + str(
managerid[0]) + " where employeeid = " + str(employeeid[0])
DBUtils.executeUpdate(self._conn, query)
return True
except psycopg2.Error as e:
print(e)
return False
def registerIndividual(self, newContactInfo, newClient):
try:
query = """
insert into Contact_Info (street, city, zip, state) values (%s,%s,%s,%s)
"""
DBUtils.executeUpdate(
self._conn, query,
(newContactInfo.getStreet(), newContactInfo.getCity(),
newContactInfo.getZip(), newContactInfo.getState()))
cid = DBUtils.getVar(self._conn,
"select max(cid) from Contact_Info")
i_issue = DBUtils.getVar(
self._conn, "select iid from Issues where name='%s'" %
newClient.getIssue())
worker_ssn = DBUtils.getVar(
self._conn, "select ssn from Social_Workers where name='%s'" %
newClient.getWorker())
query = """
insert into Individuals (ssn, name, date_joined, issue, social_worker, sw_since, contact_info) values (%s,%s,%s,%s,%s,%s,%s)
"""
DBUtils.executeUpdate(
self._conn, query,
(newClient.getSSN().strip(), newClient.getName(),
newClient.getDateJoined().strftime('%m-%d-%Y'), i_issue,
worker_ssn.strip(),
newClient.getWorkerSince().strftime('%m-%d-%Y'), cid))
except psycopg2.Error as e:
print(e)
return newClient
def updateRoles(self, EngineOilSpendingAmount, EngineOilBuyingAmount,
CrudeOilSpendingAmount, CrudeOilBuyingAmount,
MotorOilSpendingAmount, MotorOilBuyingAmount,
GasolineBuyingAmount, GasolineSpendingAmount,
PetroleumSpendingAmount, PetroleumBuyingAmount,
InternalSpendingBudget, RoleNameT):
query = "update roles set EngineOilSpendingAmount = %s, EngineOilBuyingAmount = %s,CrudeOilSpendingAmount = %s,CrudeOilBuyingAmount = %s,MotorOilSpendingAmount = %s,MotorOilBuyingAmount = %s,GasolineBuyingAmount = %s,GasolineSpendingAmount = %s,PetroleumSpendingAmount = %s,PetroleumBuyingAmount = %s,InternalSpendingBudget = %s where RoleName = %s"
DBUtils.executeUpdate(self._conn, query,
(EngineOilSpendingAmount, EngineOilBuyingAmount,
CrudeOilSpendingAmount, CrudeOilBuyingAmount,
MotorOilSpendingAmount, MotorOilBuyingAmount,
GasolineBuyingAmount, GasolineSpendingAmount,
PetroleumSpendingAmount, PetroleumBuyingAmount,
InternalSpendingBudget, RoleNameT))
def rateDeal(self, deal, customer, value):
try:
# Check if the customer has already rated this deal (select from Ratings)
query = """SELECT count(*) FROM Ratings
WHERE cid='{cid}' and did='{did}'
""".format(cid=customer.getCid(), did=deal.getDid())
result = DBUtils.getVar(self._conn, query)
# If so, the rating should be updated
if result > 0:
print("You have already rated this deal")
return
# User rating is new and has to be added
query = """INSERT INTO Ratings VALUES (%s, %s, %s)"""
DBUtils.executeUpdate(self._conn, query,
(customer.getCid(), deal.getDid(), value))
# Update deal's average rating
# 1) Count the number of ratings this now deal has
query = """SELECT count(*) FROM Ratings
WHERE did='{did}'""".format(did=deal.getDid())
num_ratings = DBUtils.getVar(
self._conn, query
) # (At least one at this point since new rating was added)
# 2) Get previous average rating
query = """SELECT avgRating FROM Deals
WHERE dealId='{did}'""".format(did=deal.getDid())
previous_avg = DBUtils.getVar(self._conn, query)
if previous_avg is not None:
# 3a) Calculate updated average.
new_avg = (previous_avg + value) / (num_ratings)
else:
# 3b) Set average.
new_avg = value
# 4) Update deal's average rating
query = """UPDATE Deals SET avgRating = {new_avg}
WHERE dealId='{did}'
""".format(did=deal.getDid(), new_avg=new_avg)
DBUtils.executeUpdate(self._conn, query)
print("Deal rated!")
except psycopg2.Error as e:
print(e)
def saveNewRoles2(self, RoleNameT, EngineOilSpendingAmount,
EngineOilBuyingAmount, CrudeOilSpendingAmount,
CrudeOilBuyingAmount, MotorOilSpendingAmount,
MotorOilBuyingAmount, GasolineBuyingAmount,
GasolineSpendingAmount, PetroleumSpendingAmount,
PetroleumBuyingAmount, InternalSpendingBudget):
cnt = DBUtils.getVar(
self._conn, "select count(*) from roles where rolename like '" +
str(RoleNameT) + "'")
if (RoleNameT is not None):
if (len(str(RoleNameT)) > 0):
if (cnt == 0):
query = "insert into roles (RoleName,EngineOilSpendingAmount,EngineOilBuyingAmount,CrudeOilSpendingAmount,CrudeOilBuyingAmount,MotorOilSpendingAmount,MotorOilBuyingAmount,GasolineBuyingAmount,GasolineSpendingAmount,PetroleumSpendingAmount,PetroleumBuyingAmount,InternalSpendingBudget) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
DBUtils.executeUpdate(
self._conn, query,
(RoleNameT, EngineOilSpendingAmount,
EngineOilBuyingAmount, CrudeOilSpendingAmount,
CrudeOilBuyingAmount, MotorOilSpendingAmount,
MotorOilBuyingAmount, GasolineBuyingAmount,
GasolineSpendingAmount, PetroleumSpendingAmount,
PetroleumBuyingAmount, InternalSpendingBudget))
