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
