gistfile1.sql

select
				(select @offeringId) as offeringId,
				(select @userId) as userId,
				(select id from discussion where offeringId = @offeringId) as discussionId,
				(select role from offering_to_user where offeringId = @offeringId and userId = @userId) as roleId,
				(select studentId from offering_to_user where offeringId = @offeringId and userId = @userId) as studentId,
				(select c.title from course c, offering o where o.courseId = c.id and o.id = @offeringId) as title,
				(select c.subject from course c, offering o where o.courseId = c.id and o.id = @offeringId) as subject,
				(select courseId from offering where id = offeringId) as courseId,
        		(select price from offering where id = offeringId) as price,
        		(select spots from offering where id = offeringId) as spots,
        		(select startDate from offering where id = offeringId) as startDate,
        		(select endDate from offering where id = offeringId) as endDate,
        		(select liveClassDate from offering where id = offeringId) as liveClassDate,
				(select s.name from school s, course c, offering o where s.id = c.schoolId and c.id = o.courseId and o.id = @offeringId) as schoolName,

				(select group_concat(id separator ',') 
				    from announcement where offeringId = @offeringId
				)as announcementIds,
				(
				select group_concat(id separator ',') 
				    from assignment where offeringId = @offeringId and (studentVisible = 1 and toRelease = 0  or roleId <> 0 or (roleId = 0 and toRelease = 1 and releaseDate <= now()))
				)as assignmentIds,
                (
				select group_concat(id separator ',') 
				    from live_class where offeringId = @offeringId
				)as liveClassIds,
				(
				select group_concat(id separator ',') 
				    from resource where offeringId = @offeringId and (studentVisible = 1 and toRelease = 0  or roleId <> 0 or (roleId = 0 and toRelease = 1 and releaseDate <= now()))
				)as resourceIds,
				(
				select group_concat(id separator ',') 
				    from lab where offeringId = @offeringId and (studentVisible = 1 and toRelease = 0 or roleId <> 0 or (roleId = 0 and toRelease = 1 and releaseDate <= now()))
				)as labIds,
				(
				select group_concat(g.id separator ',') 
				    from grade g, gradeable ga where g.gradeableId = ga.id and g.studentId = @userId and ga.offeringId = @offeringId and ga.published = 1
				)as gradeIds,
				(
				select group_concat(id separator ',') 
				    from gradeable where offeringId = @offeringId and (roleId <> 0 or (roleId = 0 and published =1))
				)as gradeableIds,
				(
				select group_concat(id separator ',') 
				    from gradeable_type where offeringId = @offeringId
				)as gradeableTypeIds,
				(
				select group_concat(id separator ',') 
				    from offering_summary_item where offeringId = @offeringId
				)as offeringSummaryItemIds,
				(
				select group_concat(u.id separator ',') 
				    from user u, offering_to_user otu where otu.offeringId = @offeringId and u.id = otu.userId and otu.role = 0
				)as students,
				(
				select group_concat(otu.studentId separator ',') 
				    from user u, offering_to_user otu where otu.offeringId = @offeringId and u.id = otu.userId and otu.role = 0
				)as studentIds,
				(
				select group_concat(u.id separator ',') 
				    from user u, offering_to_user otu where otu.offeringId = @offeringId and u.id = otu.userId and otu.role = 1
				)as instructors,
				(
				select group_concat(u.id separator ',') 
				    from user u, offering_to_user otu where otu.offeringId = @offeringId and u.id = otu.userId and otu.role = 2
				)as assistants,
				(
				select group_concat(u.id separator ',') 
				    from user u, offering_to_user otu where otu.offeringId = @offeringId and u.id = otu.userId and otu.role = 3
				)as admins,
				( 
				  select count(*) from announcement where offeringId = @offeringId and id not in (select announcementId from announcement_read_by where userId=@userId) 
				) as announcementsUnread
				,(
				  select count(*) from announcement where offeringId = @offeringId and id in (select announcementId from announcement_flagged_by where userId=@userId) 
				) as announcementsFlagged
				,(
				  select count(*) from assignment where offeringId = @offeringId and (studentVisible = 1 and toRelease = 0 or roleId <> 0 or (roleId = 0 and toRelease = 1 and releaseDate <= now())) and id not in (select assignmentId from assignment_read_by where userId=@userId) 
				) as assignmentsUnread
				,(
				  select count(*) from assignment where offeringId = @offeringId and id in (select assignmentId from assignment_flagged_by where userId=@userId) 
				) as assignmentsFlagged
				,(
				  select count(*) from lab where offeringId = @offeringId and (studentVisible = 1 and toRelease = 0 or roleId <> 0 or (roleId = 0 and toRelease = 1 and releaseDate <= now())) and id not in (select labId from lab_read_by where userId=@userId) 
				) as labsUnread
				,(
				  select count(*) from lab where offeringId = @offeringId and id in (select labId from lab_flagged_by where userId=@userId) 
				) as labsFlagged
				,(
				  select count(*) from resource where offeringId = @offeringId and (studentVisible = 1 and toRelease = 0 or roleId <> 0 or (roleId = 0 and toRelease = 1 and releaseDate <= now())) and id not in (select resourceId from resource_read_by where userId=@userId) 
				) as resourcesUnread,
				(
				  select count(*) from resource where offeringId = @offeringId and id in (select resourceId from resource_flagged_by where userId=@userId) 
				) as resourcesFlagged,
				(
				  select count(p.id) from post p, discussion d where p.discussionId = d.id and d.offeringId = @offeringId and p.id not in (select postId from post_read_by where userId=@userId) 
				) as discussionUnread,
				(
				  select count(p.id) from post p, discussion d where p.discussionId = d.id and d.offeringId = @offeringId and p.id in (select postId from post_flagged_by where userId=@userId) 
				) as discussionFlagged,
				(
				  select (assignmentsUnread + announcementsUnread + labsUnread + resourcesUnread + discussionUnread)
				) as unread,
				(
				  select (assignmentsFlagged + announcementsFlagged + labsFlagged + resourcesFlagged + discussionFlagged)
				) as flagged