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