def getIssues(self):
query = "select iss.name, iss.iid from issues iss "
rows = DBUtils.getAllRows(self._conn, query)
issues = []
for val in rows:
issues.append(val[0])
return issues
def getWorkers(self):
query = "select w.name, w.ssn from social_workers w "
rows = DBUtils.getAllRows(self._conn, query)
workers = []
for val in rows:
workers.append(val[0])
return workers
def getWorkers_And_Clients(self, orderByClause):
query = (
"select w.name as \"Social Worker\", w.title as Title, count(i.ssn) as \"Num of clients\", min(i.sw_since) as \"Oldest Current Client\" "
"from social_workers w left join individuals i on w.ssn=i.social_worker "
"group by w.name, w.specialization, w.title "
"order by %s" % orderByClause)
return DBUtils.getAllRows(self._conn, query)
def getIndividuals(self):
query = (
"select i.ssn, i.name, iss.name, s.name, s.title, i.date_joined, ci.street || ' ' || ci.city || ' ' || ci.state || ', ' || ci.zip as Address "
"from individuals i, social_workers s, issues iss, contact_info ci "
"where i.social_worker=s.ssn and i.issue=iss.iid and i.contact_info = ci.cid"
)
return DBUtils.getAllRows(self._conn, query)
def getEmRo(self, fullname):
query1 = "select employeeid from employeewithrole where lower(fullname) like lower('" + str(
fullname) + "')"
employeeid = DBUtils.getRow(self._conn, query1)
#query = "select * from EmployeeRolesView where employeeid = " + str(employeeid[0])
query = "SELECT E.EmployeeID, E.FirstName, E.LastName, CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME, E.isEmployeed, E.ManagerID, (SELECT FullName FROM Managers where ManagerID = E.ManagerID) AS ManagerName, R.RoleName, ER.StartDate StartDate, ER.EndDate EndDate FROM Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID where employeeid = " + str(
employeeid[0])
return DBUtils.getAllRows(self._conn, query)
def display_statistics(self):
query = """
select iss.name, c.state, count(iss.iid)
from individuals i, issues iss, contact_info c
where i.issue=iss.iid and i.contact_info=c.cid
group by iss.name, c.state
order by iss.name, count(iss.iid) desc, c.state"""
return DBUtils.getAllRows(self._conn, query)
def getAudits(self, num):
query = ""
if (num == 1):
query = "select * from AuditEmployees"
elif (num == 2):
query = "select * from AuditEmployeeRoles"
else:
query = "select * from AuditRoles"
return DBUtils.getAllRows(self._conn, query)
def getFilteredEmployees(self, fullname, managername, rolename):
if (rolename == "All"):
rolename = '%'
if (managername == "All"):
managername = '%'
query = "SELECT * FROM (SELECT E.FirstName,E.LastName,CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME,E.EmailAddress,R.RoleName,(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 and lower(rolename) like lower('" + rolename + "%')) AS DATA WHERE lower(managername) like lower('" + str(
managername) + "%') AND lower(FULLNAME) like lower('" + str(
fullname) + "%')"
return DBUtils.getAllRows(self._conn, query)
def display_worker_no_clients(self):
query = """
SELECT w.ssn, w.name, w.title, iss.name as Specialization, ci.state as "State Of Operation"
FROM social_workers w, issues iss, contact_info ci
WHERE w.ssn NOT IN (SELECT i.social_worker
FROM individuals i,
social_workers w
WHERE w.ssn = i.social_worker)
and w.specialization=iss.iid and w.contact_info = ci.cid"""
return DBUtils.getAllRows(self._conn, query)
def getFirstUser(self):
try:
query = """
SELECT *
FROM Customers
LIMIT 1;
"""
except psycopg2.Error as e:
print(e)
return DBUtils.getAllRows(self._conn, query)
def getAllDeals(self):
try:
query = """SELECT Deals.title, Deals.description, Deals.avgRating, Deals.imageURL,
Deals.startDate, Deals.endDate, Business.name, Business.phoneNum, Business.businessId, Deals.dealId
FROM Deals
INNER JOIN Business ON Deals.bid=Business.businessId
ORDER BY Business.name
"""
except psycopg2.Error as e:
print(e)
return DBUtils.getAllRows(self._conn, query)
def search(self, name):
query = """
select s.ssn, s.name, 'Social Worker' as type, ci.State
from social_workers s, contact_info ci
where lower(s.name) like '%%%s%%' and s.contact_info=ci.cid
UNION
select i.ssn, i.name, 'Individual' as type, ci.State
from Individuals i, contact_info ci
where lower(i.name) like '%%%s%%' and i.contact_info=ci.cid
order by 1""" % (name.lower(), name.lower())
return DBUtils.getAllRows(self._conn, query)
def getFavoritedDeals(self, customer):
try:
query = """
SELECT Deals.title, Deals.description, Deals.avgRating, Deals.imageURL,
Deals.startDate, Deals.endDate, Business.name, Business.phoneNum,
Business.businessId, Deals.dealId
FROM Deals
INNER JOIN Favorites ON Favorites.did=Deals.dealId
INNER JOIN Business ON Deals.bid=Business.businessId
WHERE Favorites.cid='{cid}'
""".format(cid=customer.getCid())
except psycopg2.Error as e:
print(e)
return DBUtils.getAllRows(self._conn, query)
def getAllRetailers(self):
try:
# Businesses sorted by "reputation", which is num_visits + num_favorites * 2
query = """
Select name, imageURL, homePageURL, categoryName
FROM Business
INNER JOIN (SELECT * from Belongs_To group by cid, bid) belongTo
on belongTo.bid=Business.businessId
INNER JOIN Category
on belongTo.cid=Category.categoryId
order by (numvisited + numFavouritedDeals*2) desc
"""
except psycopg2.Error as e:
print(e)
return DBUtils.getAllRows(self._conn, query)
def getTopNDeals(self, n):
# Return the top n best deals (best as in highest average rating)
try:
query = """
SELECT Deals.title, Deals.description, Deals.avgRating, Deals.imageURL,
Deals.startDate, Deals.endDate, Business.name, Business.phoneNum,
Business.businessId, Deals.dealId
FROM Deals
INNER JOIN Business ON Deals.bid=Business.businessId
WHERE Deals.avgRating IS NOT NULL
ORDER BY Deals.avgRating desc
LIMIT {n}
""".format(n=n)
except psycopg2.Error as e:
print(e)
return DBUtils.getAllRows(self._conn, query)
def searchForDeal(self, deal_name):
try:
# Get deal info where name or description contains a substring of the passed
# in deal_name search string
query = """
SELECT Deals.title, Deals.description, Deals.avgRating, Deals.imageURL,
Deals.startDate, Deals.endDate, Business.name,
Business.phoneNum, Business.businessId, Deals.dealId
FROM Deals
INNER JOIN Business ON Deals.bid=Business.businessId
where title LIKE \'%{deal_name}%\' or description LIKE \'%{deal_name}%\'
ORDER BY Deals.avgrating DESC
""".format(deal_name=deal_name)
except psycopg2.Error as e:
print(e)
return DBUtils.getAllRows(self._conn, query)
def getAllEmployees(self):
#query = "select firstname,lastname,fullname,emailaddress,rolename,managername from employeewithrole order by firstname asc"
query = " SELECT E.FirstName,E.LastName,CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME,E.EmailAddress,R.RoleName,(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 order by firstname asc"
return DBUtils.getAllRows(self._conn, query)
def getAllRoles(self):
query = "select RoleID,RoleName,EngineOilSpendingAmount,EngineOilBuyingAmount,CrudeOilSpendingAmount,CrudeOilBuyingAmount,MotorOilSpendingAmount,MotorOilBuyingAmount,GasolineBuyingAmount,GasolineSpendingAmount,PetroleumSpendingAmount,PetroleumBuyingAmount,InternalSpendingBudget from roles"
return DBUtils.getAllRows(self._conn, query)
