def getClassDescription(self, class_id): log = logging.getLogger('csdt') log.info("Classroom.getClassDescription()") sql = "SELECT cn.classname AS classname, c.description AS description, c.time AS time FROM classnames cn, classrooms c WHERE c.id = cn.class_id AND c.id = %s AND c.active = 1;" % (str(class_id)) result = database.executeSelectQuery(sql, "one") if result is None: log.warning("class does not exist") return class_info = {} date_obj = date.date() class_info["classname"] = result["classname"] class_info["description"] = result["description"] class_info["time"] = date_obj.modifyDate(result["time"]) return class_info
def getAllFlaggedCommentsForATeacher(self, user_id, sort_name, sort_order): log = logging.getLogger('csdt') log.info("AccountComment.getAllFlaggedCommentsForATeacher()") sql = "SELECT table_A.proj_comment_id AS proj_comment_id, table_A.comment_owner AS comment_owner, table_A.proj_name AS proj_name, table_A.text AS text, table_A.time AS time FROM (SELECT p.id AS project_id, pc.id AS proj_comment_id, un.username AS comment_owner, p.proj_name AS proj_name, pc.text AS text, pc.time AS time FROM projects p, project_comments pc, usernames un WHERE p.id = pc.project_id AND p.user_id = un.user_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 ORDER BY %s %s;" % (str(user_id), sort_name, sort_order) result = database.executeSelectQuery(sql, "many") comment_list = [] for row in result: comment_hash = {} date_obj = date.date() comment_hash['proj_comment_id'] = row["proj_comment_id"] comment_hash['comment_owner'] = row["comment_owner"] comment_hash['proj_name'] = row["proj_name"] comment_hash['text'] = row["text"] comment_hash['time'] = date_obj.modifyDate(row["time"]) comment_list.append(comment_hash) return comment_list
def getAllProjectsForClass(self, class_id, sort_name, sort_order): log = logging.getLogger('csdt') log.info("Classroom.getAllProjectsForClass()") sql = "SELECT table_A.proj_id AS id, table_A.username AS username, table_A.proj_name AS proj_name, table_A.proj_type AS proj_type, table_A.description AS description, table_A.num_views AS num_views, COUNT(table_B.rating) AS ratings, table_A.downloads AS downloads, table_A.stored_proj_name AS stored_proj_name, table_A.time AS time FROM (SELECT p.id AS proj_id, u.id AS user_id, un.username AS username, p.proj_name AS proj_name, p.proj_type AS proj_type, p.description AS description, p.num_views AS num_views, p.downloads AS downloads, p.stored_proj_name AS stored_proj_name, p.time AS time FROM projects p, project_memberships pm, users u, usernames un WHERE p.user_id = u.id AND u.id = un.user_id AND pm.project_id = p.id AND pm.class_id = %s AND p.active = 1) table_A LEFT JOIN (SELECT pr.project_id, pr.rating FROM project_ratings pr, users u WHERE u.id = pr.user_id AND u.active = 1) table_B ON table_B.project_id = table_A.proj_id GROUP BY table_A.proj_id ORDER BY %s %s;" % (str(class_id), sort_name, sort_order) result = database.executeSelectQuery(sql, "many") proj_list = [] for row in result: proj_hash = {} date_obj = date.date() proj_hash['id'] = row["id"] proj_hash['username'] = row["username"] proj_hash['proj_name'] = row["proj_name"] proj_hash['proj_type'] = row["proj_type"] proj_hash['description'] = row["description"] proj_hash['num_views'] = row["num_views"] proj_hash['ratings'] = row["ratings"] proj_hash['downloads'] = row["downloads"] proj_hash['stored_proj_name'] = row["stored_proj_name"] proj_hash['time'] = date_obj.modifyDate(row["time"]) proj_list.append(proj_hash) return proj_list