def addProjectRating(self, proj_id, user_id, rating):
log = logging.getLogger('csdt')
log.info("Project.addProjectRating()")
# Checks if user has already submitted a rating to the given project. If not, it should return an empty set.
sql = "SELECT pr.project_id AS project_id, pr.user_id AS user_id, pr.rating AS rating from projects p, project_ratings pr, users u WHERE p.id = pr.project_id AND u.id = pr.user_id AND pr.project_id = %s AND pr.user_id = %s AND u.active = 1 AND p.active = 1;" % (str(proj_id), str(user_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
if int(rating) == 0:
log.warning("User has returned a 0 rating")
return
sql = "INSERT INTO project_ratings (project_id, user_id, rating) VALUES (%s, %s, %s);" % (str(proj_id), str(user_id), str(rating))
result = database.executeInsertQuery(sql)
return
sql = "DELETE FROM project_ratings where project_id = %s AND user_id = %s;" % (str(proj_id), str(user_id))
result = database.executeDeleteQuery(sql)
if int(rating) == 0:
log.warning("User has returned a 0 rating")
return
sql = "INSERT INTO project_ratings (project_id, user_id, rating) VALUES (%s, %s, %s);" % (str(proj_id), str(user_id), str(rating))
result = database.executeInsertQuery(sql)
return
def getSizeOfActiveProjectTable(self):
log = logging.getLogger('csdt')
log.info("Project.getSizeOfActiveProjectTable()")
sql = "SELECT COUNT(id) AS size FROM projects WHERE visible = 1 AND active = 1;"
result = database.executeSelectQuery(sql, "one")
return result["size"]
def getNumOfFlaggedCommentUsers(self, comment_id):
log = logging.getLogger('csdt')
log.info("AccountComment.getNumOfFlaggedCommentUsers()")
sql = "SELECT COUNT(pcr.user_id) AS num_users FROM project_comments_ratings pcr WHERE pcr.proj_comment_id = %s AND pcr.flag = 1;" % (str(comment_id))
result = database.executeSelectQuery(sql, "one")
return result["num_users"]
def getClassFlagCommentLevel(self, user_id, class_id):
log = logging.getLogger('csdt')
log.info("ClassComment.getClassFlagCommentLevel()")
sql = "SELECT flag_comment_level FROM classrooms WHERE id = %s AND owner = %s;" % (str(class_id), str(user_id))
result = database.executeSelectQuery(sql, "one")
return result["flag_comment_level"]
def getNumOfProjectsForClass(self, class_id):
log = logging.getLogger('csdt')
log.info("Classroom.getNumOfProjectsForClass()")
sql = "SELECT COUNT(id) AS num_projects FROM projects p, project_memberships pm WHERE pm.class_id = %s AND pm.project_id = p.id AND p.active = 1;" % (str(class_id))
result = database.executeSelectQuery(sql, "one")
return result["num_projects"]
def getNumOfAllActiveClasses(self):
log = logging.getLogger('csdt')
log.info("Gallery.getNumOfAllActiveClasses()")
sql = "SELECT COUNT(DISTINCT cm.class_id) AS num_classes FROM class_memberships cm, classnames cn, classrooms c, users u WHERE cm.permissions = \'t\' AND c.id = cn.class_id AND cn.class_id = cm.class_id AND cm.user_id = u.id AND c.active = 1 AND u.active = 1;"
result = database.executeSelectQuery(sql, "one")
return result["num_classes"]
def getIndividualClassSize(self, class_id):
log = logging.getLogger('csdt')
log.info("Classroom.getIndividualClassSize()")
sql = "SELECT COUNT(DISTINCT cm.user_id) AS num_students FROM class_memberships cm, classnames cn, classrooms c, users u WHERE cm.permissions = \'s\' AND c.id = cn.class_id AND cn.class_id = cm.class_id AND cm.user_id = u.id AND cm.class_id = %s AND c.active = 1 AND u.active = 1;" % (str(class_id))
result = database.executeSelectQuery(sql, "one")
return result["num_students"]
def getSizeOfActiveProjectTableForAUser(self, user_id):
log = logging.getLogger('csdt')
log.info("Account.getSizeOfActiveProjectTableForAUser()")
sql = "SELECT COUNT(id) AS size FROM projects WHERE user_id = %s AND active = 1;" % (str(user_id))
result = database.executeSelectQuery(sql, "one")
return result["size"]
def getNumOfFlaggedComments(self, user_id):
log = logging.getLogger('csdt')
log.info("AccountComment.getNumOfFlaggedComments()")
sql = "SELECT COUNT(table_A.flag) AS num_comments FROM (SELECT p.id AS project_id, pc.flag AS flag FROM projects p, project_comments pc WHERE p.id = pc.project_id AND p.active = 1 AND pc.flag = 1) table_A RIGHT JOIN (SELECT DISTINCT pm.project_id AS project_id FROM class_memberships cm, project_memberships pm WHERE cm.class_id = pm.class_id AND cm.user_id = %s AND cm.permissions = 't') table_B ON table_A.project_id = table_B.project_id;" % (str(user_id))
result = database.executeSelectQuery(sql, "one")
return result["num_comments"]
def getNumOfEnrolledClasses(self, user_id):
log = logging.getLogger('csdt')
log.info("AccountProject.getNumOfEnrolledClasses()")
sql = "SELECT COUNT(DISTINCT cm.class_id) AS num_classes FROM class_memberships cm, classnames cn, classrooms c, users u WHERE c.id = cn.class_id AND cn.class_id = cm.class_id AND cm.user_id = u.id AND cm.user_id = %s AND c.active = 1 AND u.active = 1;" % (str(user_id))
result = database.executeSelectQuery(sql, "one")
return result["num_classes"]
def verifyUserId(self, user_id):
log = logging.getLogger('csdt')
log.info("Classroom.verifyUserId()")
sql = "SELECT id FROM users WHERE id = %s AND active = 1;" % (str(user_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("user does not exist")
return
return result["id"]
def getProjectOwner(self, proj_id):
log = logging.getLogger('csdt')
log.info("Project.getProjectOwner()")
sql = "SELECT user_id FROM projects WHERE id = %s;" % (str(proj_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("project does not exist")
return
return result["user_id"]
def getProjRatingForAUser(self, proj_id, user_id):
log = logging.getLogger('csdt')
log.info("Project.getProjRatingForAUser()")
sql = "SELECT pr.rating AS rating from projects p, project_ratings pr, users u WHERE p.id = pr.project_id AND u.id = pr.user_id AND pr.project_id = %s AND pr.user_id = %s AND u.active = 1 AND p.active = 1;" % (str(proj_id), str(user_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("user has not submitted a rating for the particular project")
return
return result["rating"]
def verifyProjectId(self, project_id, user_id):
log = logging.getLogger('csdt')
log.info("AccountProject.verifyProjectId()")
sql = "SELECT p.id AS proj_id FROM projects p WHERE p.id = %s AND p.user_id = %s;" % (str(project_id), str(user_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("project is not under the jurisdication of the user")
return
return result["proj_id"]
def verifyCommentId(self, comment_id, user_id):
log = logging.getLogger('csdt')
log.info("AccountComment.verifyCommentId()")
sql = "SELECT pc.id AS proj_comment_id FROM class_memberships cm, projects p, project_comments pc, project_memberships pm WHERE cm.class_id = pm.class_id AND pm.project_id = pc.project_id AND pm.project_id = p.id AND pc.id = %s AND cm.user_id = %s AND cm.permissions = 't';" % (str(comment_id), str(user_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("comment is not under the jurisdication of the user")
return
return result["proj_comment_id"]
def doesClassnameExist(self, classname):
log = logging.getLogger('csdt')
log.info("RegisterClass.doesClassnameExist()")
sql = "SELECT c.id AS class_id FROM classnames cn, classrooms c WHERE c.id = cn.class_id AND cn.classname = '%s' AND c.active = 1;" % (str(classname))
result = database.executeSelectQuery(sql, "one")
if result is not None:
log.warning("class already exists")
return result["class_id"]
return
def verifyProjectOwner(self, proj_id, username):
log = logging.getLogger('csdt')
log.info("Project.verifyProjectOwner()")
sql = "SELECT p.proj_type AS proj_type FROM projects p, users u, usernames un WHERE p.user_id = u.id AND u.id = un.user_id AND p.id = %s AND un.username = '******' AND p.active = 1 AND u.active = 1;" % (str(proj_id), str(username))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("project does not exist")
return
return result["proj_type"]
def getClassOwner(self, class_id):
log = logging.getLogger('csdt')
log.info("ClassComment.getClassOwner()")
sql = "SELECT un.username AS username FROM classnames cn, class_memberships cm, classrooms c, usernames un, users u WHERE c.id = cn.class_id AND cn.class_id = cm.class_id AND cm.user_id = u.id AND un.user_id = u.id AND c.id = %s AND cm.permissions = 't' AND c.active = 1 AND u.active = 1;" % (str(class_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("class does not exist")
return
return result["username"]
def doesEmailExist(self, email):
log = logging.getLogger('csdt')
log.info("RegisterPublic.doesEmailExist()")
sql = "SELECT id AS user_id FROM users WHERE email = \'%s\' AND active = 1;" % (str(email))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("user does not exist")
return
return result["user_id"]
def authenticateUser(self, username, password):
log = logging.getLogger('csdt')
log.info("Login.authenticateUser()")
sql = "SELECT un.user_id AS user_id FROM usernames un, users u WHERE un.user_id = u.id AND un.username = '******' AND un.pass = '******' AND u.active = 1;" % (str(username), str(password))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("user does not exist")
return
return result["user_id"]
def doesUsernameExist(self, username):
log = logging.getLogger('csdt')
log.info("RegisterAccount.doesUsernameExist()")
sql = "SELECT un.user_id AS user_id FROM usernames un, users u WHERE un.user_id = u.id AND un.username = \'%s\' AND u.active = 1;" % (str(username))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("user does not exist")
return
return result["user_id"]
def verifyClassPassword(self, class_id, password):
log = logging.getLogger('csdt')
log.info("RegisterClass.verifyClassPassword()")
sql = "SELECT c.id AS class_id FROM classnames cn, classrooms c WHERE c.id = cn.class_id AND c.id = %s AND cn.pass = '******' AND c.active = 1;" % (str(class_id), str(password))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("class does not exist")
return
return result["class_id"]
def getNumOfRegistableClasses(self):
log = logging.getLogger('csdt')
log.info("RegisterClass.getNumOfRegistableClasses()")
sql = "SELECT COUNT(DISTINCT cm.class_id) AS num_classes FROM class_memberships cm, classnames cn, classrooms c, users u WHERE cm.permissions = 't' AND c.id = cn.class_id AND cn.class_id = cm.class_id AND cm.user_id = u.id AND c.active = 1 AND u.active = 1;"
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("no classes exist")
return
return result["num_classes"]
def verifyUserAsClassParticipant(self, class_id, user_id):
log = logging.getLogger('csdt')
log.info("AccountProject.verifyUserAsClassParticipant()")
sql = "SELECT c.id AS class_id FROM class_memberships cm, classrooms c, users u WHERE cm.class_id = c.id AND cm.user_id = u.id AND c.id = %s AND u.id = %s AND c.active = 1 AND u.active = 1;" % (str(class_id), str(user_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("user is not a participant in the class")
return
return result["class_id"]
def isUserApartOfClass(self, user_id, class_id):
log = logging.getLogger('csdt')
log.info("RegisterClass.isUserApartOfClass()")
# Creates an association as a student of a particular class
sql = "SELECT class_id FROM class_memberships WHERE user_id = %s AND class_id = %s;" % (str(user_id), str(class_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("user is not apart of the particular class")
return
return result["class_id"]
def getFlaggedCommentUsers(self, comment_id, sort_name, sort_order):
log = logging.getLogger('csdt')
log.info("AccountComment.getFlaggedCommentUsers()")
sql = "SELECT u.username AS username FROM project_comments_ratings pcr, usernames u WHERE pcr.user_id = u.user_id AND pcr.proj_comment_id = %s AND pcr.flag = 1 ORDER BY %s %s;" % (str(comment_id), sort_name, sort_order)
result = database.executeSelectQuery(sql, "many")
comment_list = []
for row in result:
comment_hash = {}
comment_hash['username'] = row["username"]
comment_list.append(comment_hash)
return comment_list
def getUserId(self, username):
log = logging.getLogger('csdt')
log.info("Account.getUserId()")
sql = "SELECT u.id AS user_id FROM users u, usernames un WHERE u.id = un.user_id AND un.username = \'%s\' AND u.active = 1;" % (username)
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("user does not exist")
return
user_id = result["user_id"]
log.debug("user_id = %s" % user_id)
return user_id
def determineClassPermissions(self, class_id, user_id):
log = logging.getLogger('csdt')
log.info("Classroom.getClassInformation()")
sql = "SELECT cn.classname AS classname, cm.permissions AS permissions FROM classnames cn, class_memberships cm, classrooms c, users u WHERE c.id = cn.class_id AND cn.class_id = cm.class_id AND cm.user_id = u.id AND c.id = %s AND u.id = %s AND c.active = 1 AND u.active = 1;" % (str(class_id), str(user_id))
result = database.executeSelectQuery(sql, "one")
if result is None:
log.warning("class does not exist")
return
class_info = {}
class_info["classname"] = result["classname"]
class_info["permissions"] = result["permissions"]
return class_info
def getAllClassesEnrolledAsTeacher(self, user_id, sort_name, sort_order):
log = logging.getLogger('csdt')
log.info("AccountClass.getAllClassesEnrolledAsTeacher()")
sql = "SELECT table_B.class_id AS cid, table_B.classname AS classname, COUNT(cm.user_id) AS size FROM (SELECT table_A.class_id AS class_id, table_A.classname AS classname, cm.user_id AS user_id, table_A.permissions AS permissions FROM (SELECT cn.class_id AS class_id, cn.classname AS classname, cm.permissions AS permissions FROM classnames cn, class_memberships cm, classrooms c, users u WHERE c.id = cn.class_id AND cn.class_id = cm.class_id AND cm.user_id = u.id AND cm.user_id = %s AND cm.permissions = 't' AND c.active = 1 AND u.active = 1) table_A, class_memberships cm, users u WHERE u.active = 1 AND table_A.class_id = cm.class_id AND cm.user_id = u.id) table_B LEFT JOIN class_memberships cm ON table_B.class_id = cm.class_id AND table_B.permissions <> cm.permissions AND table_B.user_id = cm.user_id GROUP BY table_B.class_id ORDER BY %s %s;" % (str(user_id), sort_name, sort_order)
result = database.executeSelectQuery(sql, "many")
class_list = []
for row in result:
class_hash = {}
class_hash['cid'] = row["cid"]
class_hash['classname'] = row["classname"]
class_hash['size'] = row["size"]
class_list.append(class_hash)
return class_list
def getAllEnrolledClassesWithUploadedProject(self, user_id, proj_id, sort_name, sort_order):
log = logging.getLogger('csdt')
log.info("AccountProject.getAllEnrolledClassesWithUploadedProject()")
sql = "SELECT table_A.cid AS cid, table_A.classname AS classname, COUNT(table_B.project_id) AS uploaded FROM (SELECT c.id AS cid, cn.classname AS classname FROM class_memberships cm, classnames cn, classrooms c, users u WHERE c.id = cn.class_id AND cn.class_id = cm.class_id AND cm.user_id = u.id AND cm.user_id = %s AND c.active = 1 AND u.active = 1) table_A LEFT JOIN (SELECT project_id, class_id FROM project_memberships WHERE project_id = %s) table_B ON table_B.class_id = table_A.cid GROUP BY table_A.cid ORDER BY %s %s;" % (str(user_id), str(proj_id), sort_name, sort_order)
result = database.executeSelectQuery(sql, "many")
class_list = []
for row in result:
class_hash = {}
class_hash['cid'] = row["cid"]
class_hash['classname'] = row["classname"]
class_hash['uploaded'] = row["uploaded"]
class_list.append(class_hash)
return class_list
