JW/EMIS.Web/Config/Report/EducationScheduling/ClassmajorWithSchedule.sql

SELECT distinct ct.CoursesTimeID
	  ,ct.StartTimes
	  ,ct.Times
	  ,ct.TimeDesc
	  ,w.Weekday
	  ,w.WeekdayDesc
	  ,s.SchoolyearID
	  ,s.Code  
	  ,es.ClassroomID 
	  ,es.EducationSchedulingClassID 
	  ,es.TaskGroupName 
	  ,es.StudentCount
	  ,es.EducationMissionClassID 
	  ,es.OrderNo
	  ,es.Name 
	  ,es.TeachingModeID
	  ,es.EducationMissionID 
	  ,c.CollegeID 
	  ,c.CollegeName
	  ,es.CourseCode
	  ,es.CourseName
	  ,es.StartWeeklyNum
	  ,es.EndWeeklyNum
	  ,es.ClassroomName
	  ,c.ClassmajorID
	  ,c.ClassMajorName
	  ,c.GrademajorID
	  ,c.GrademajorName
	  ,c.Years
	  ,es.DepartmentID
	  ,es.TeacherName 
	  ,es.WeekNums
	  ,es.MinWeekNum
	  ,es.StopWeekNumStr 
FROM (select ct.CoursesTimeID,ct.StartTimes,convert(varchar(10),ct.StartTimes)+'-'+CONVERT(varchar(10),ct.EndTimes) as Times,
	REPLICATE('0',2-len(convert(varchar(10),ct.StartHour)))+convert(varchar(10),ct.StartHour)
	  +':'+REPLICATE('0',2-len(convert(varchar(10),ct.StartMinutes)))+convert(varchar(10),ct.StartMinutes)
	  +'-'+REPLICATE('0',2-len(convert(varchar(10),ct.EndHour)))+convert(varchar(10),ct.EndHour)
	  +':'+REPLICATE('0',2-len(convert(varchar(10),ct.EndMinutes)))+convert(varchar(10),ct.EndMinutes) as TimeDesc 
	from EM_CoursesTime ct 
	where ct.RecordStatus>0
	) ct 
full join (
	select 1 as Weekday,'星期一' as WeekdayDesc
	union all
	select 2 as Weekday,'星期二' as WeekdayDesc
	union all
	select 3 as Weekday,'星期三' as WeekdayDesc
	union all
	select 4 as Weekday,'星期四' as WeekdayDesc
	union all
	select 5 as Weekday,'星期五' as WeekdayDesc
	union all
	select 6 as Weekday,'星期六' as WeekdayDesc
	union all
	select 0 as Weekday,'星期日' as WeekdayDesc
) w on 1 =1 
inner join CF_Schoolyear s on 1=1 
inner join (
	select es.SchoolyearID,cg.CollegeID,cg.Name as CollegeName,c.ClassmajorID,c.Name as ClassMajorName,g.GrademajorID,g.Name as GrademajorName,g.GradeID AS Years
	from CF_Classmajor c 
	left join CF_Grademajor g on c.GrademajorID=g.GrademajorID 
	left join CF_Facultymajor f on g.FacultymajorID=f.FacultymajorID 
	left join CF_College cg on f.CollegeID=cg.CollegeID 
	inner join (
		select em.SchoolyearID,emcc.ClassmajorID 
		from ES_EducationScheduling es 
		inner join EM_CoursesTime ct on es.CoursesTimeID=ct.CoursesTimeID and ct.RecordStatus>0 
		inner join EM_EducationSchedulingClass esc on es.EducationSchedulingClassID=esc.EducationSchedulingClassID 
		inner join EM_EducationMissionClass emc on esc.EducationMissionClassID=emc.EducationMissionClassID 
		inner join EM_EducationMission em on emc.EducationMissionID=em.EducationMissionID 
		inner join EM_EducationMissionClass_CF_Classmajor emcc on emc.EducationMissionClassID=emcc.EducationMissionClassID 
		group by em.SchoolyearID,emcc.ClassmajorID
	) es on c.ClassmajorID=es.ClassmajorID 
) c on s.SchoolyearID=c.SchoolyearID 
left join (
	select distinct x.*,wn.MinWeekNum,case when right(wn.WeekNums,1)=',' then substring(wn.WeekNums,0,LEN(wn.WeekNums)) else wn.WeekNums end as WeekNums 
	from (
		SELECT
		  ES_EducationScheduling.Weekday
		  ,ES_EducationScheduling.CoursesTimeID
		  ,ES_EducationScheduling.ClassroomID 
		  ,EM_EducationSchedulingClass.EducationSchedulingClassID 
		  ,EM_EducationSchedulingClass.TaskGroupName 
		  ,ISNULL(esccs.StudentCount,0) AS StudentCount
		  ,EM_EducationMissionClass.EducationMissionClassID 
		  ,EM_EducationMissionClass.OrderNo
		  ,EM_EducationMissionClass.Name 
		  ,EM_EducationMissionClass.TeachingModeID
		  ,EM_EducationMission.EducationMissionID 
		  ,EM_EducationMission.CollegeID 
		  ,EM_EducationMission.SchoolyearID 
		  ,EM_Coursematerial.CourseCode
		  ,EM_Coursematerial.CourseName
		  ,EM_EducationMissionClassTeachingSetting.StartWeeklyNum
		  ,EM_EducationMissionClassTeachingSetting.EndWeeklyNum
		  ,CF_Classroom.Name AS ClassroomName
		  ,CF_Classmajor.ClassmajorID
		  ,CF_Classmajor.Name AS ClassMajorName
		  ,EM_EducationMission.DepartmentID
		  ,mct.TeacherName
		  ,ess.StopWeekNumStr
		FROM
		  ES_EducationScheduling
		  INNER JOIN EM_EducationSchedulingClass
			ON EM_EducationSchedulingClass.EducationSchedulingClassID = ES_EducationScheduling.EducationSchedulingClassID 
		  LEFT JOIN (
			SELECT EducationSchedulingClassID,COUNT(1) AS StudentCount 
			FROM EM_EducationSchedulingClass_CF_Student 
			GROUP BY EducationSchedulingClassID
		  ) esccs ON EM_EducationSchedulingClass.EducationSchedulingClassID=esccs.EducationSchedulingClassID
		  INNER JOIN EM_EducationMissionClass
			ON EM_EducationSchedulingClass.EducationMissionClassID = EM_EducationMissionClass.EducationMissionClassID
		  INNER JOIN EM_EducationMission
			ON EM_EducationMissionClass.EducationMissionID = EM_EducationMission.EducationMissionID
		  INNER JOIN EM_EducationMissionClassTeachingSetting 
			ON EM_EducationMissionClass.EducationMissionClassID = EM_EducationMissionClassTeachingSetting.EducationMissionClassID
		  INNER JOIN EM_Coursematerial
			ON EM_EducationMissionClass.CoursematerialID = EM_Coursematerial.CoursematerialID 
		  INNER JOIN CF_Classroom 
			ON ES_EducationScheduling.ClassroomID = CF_Classroom.ClassroomID 
		  INNER JOIN EM_EducationMissionClass_CF_Classmajor 
			ON EM_EducationMissionClass.EducationMissionClassID = EM_EducationMissionClass_CF_Classmajor.EducationMissionClassID 
		  INNER JOIN CF_Classmajor
			ON EM_EducationMissionClass_CF_Classmajor.ClassmajorID = CF_Classmajor.ClassmajorID 
		  LEFT JOIN (select es.EducationSchedulingID, STUFF((select ','+tu.Name 
					from ES_EducationSchedulingTeacher tescs 
					inner join ES_EducationScheduling tes on tescs.EducationSchedulingID=tes.EducationSchedulingID 
					inner join EM_EducationSchedulingClass tesc on tes.EducationSchedulingClassID=tesc.EducationSchedulingClassID 
					inner join Sys_User tu on tescs.UserID=tu.UserID 
					where tescs.EducationSchedulingID=es.EducationSchedulingID 
					order by ISNULL(tescs.TeachingMethod,0),tu.Name
					for xml path('')), 1, 1, '') as TeacherName 
				from ES_EducationSchedulingTeacher escs 
				inner join ES_EducationScheduling es on escs.EducationSchedulingID=es.EducationSchedulingID 
				inner join EM_EducationSchedulingClass esc on es.EducationSchedulingClassID=esc.EducationSchedulingClassID 
				inner join Sys_User u on escs.UserID=u.UserID 
				group by es.EducationSchedulingID 
			) mct ON ES_EducationScheduling.EducationSchedulingID = mct.EducationSchedulingID 
		  LEFT JOIN (
			select es.EducationSchedulingID,STUFF((select ','+Convert(varchar(10),ISNULL(tess.WeekNum,0)) 
					from ES_EducationSchedulingStop tess 
					inner join ES_EducationSchedulingStopTeacher tesst on tess.EducationSchedulingStopID=tesst.EducationSchedulingStopID 
					inner join Sys_DictionaryItem ttm on ttm.DictionaryCode='EM_TeachingMethod' and ttm.Code='Lecturer' and tesst.TeachingMethod=ttm.Value 
					inner join EM_EducationMissionClass temc on tess.EducationMissionClassID=temc.EducationMissionClassID 
					inner join EM_EducationSchedulingClass tesc on temc.EducationMissionClassID=tesc.EducationMissionClassID 
					inner join ES_EducationScheduling tes on tesc.EducationSchedulingClassID=tes.EducationSchedulingClassID 
						and tes.Weekday=tess.Weekday and tes.CoursesTimeID=tess.CoursesTimeID and tes.ClassroomID=tess.ClassroomID 
					where tes.EducationSchedulingID=es.EducationSchedulingID 
					and tess.RecordStatus=3
					order by ISNULL(tess.WeekNum,0)
					for xml path('')), 1, 1, '') as StopWeekNumStr 
			from ES_EducationSchedulingStop ess 
			inner join ES_EducationSchedulingStopTeacher esst on ess.EducationSchedulingStopID=esst.EducationSchedulingStopID 
			inner join Sys_DictionaryItem tm on tm.DictionaryCode='EM_TeachingMethod' and tm.Code='Lecturer' and esst.TeachingMethod=tm.Value 
			inner join EM_EducationMissionClass emc on ess.EducationMissionClassID=emc.EducationMissionClassID 
			inner join EM_EducationSchedulingClass esc on emc.EducationMissionClassID=esc.EducationMissionClassID 
			inner join ES_EducationScheduling es on esc.EducationSchedulingClassID=es.EducationSchedulingClassID 
				and es.Weekday=ess.Weekday and es.CoursesTimeID=ess.CoursesTimeID and es.ClassroomID=ess.ClassroomID 
			inner join ES_EducationSchedulingWeekNum esw on es.EducationSchedulingID=esw.EducationSchedulingID and ess.WeekNum=esw.WeekNum 
			where ess.RecordStatus=3
			group by es.EducationSchedulingID
		  ) ess on ES_EducationScheduling.EducationSchedulingID=ess.EducationSchedulingID 
	) x
	left join (
		select x.EducationSchedulingClassID,x.Weekday,x.CoursesTimeID,x.ClassroomID,x.TeacherName,
			MIN(x.WeekNum) as MinWeekNum,
			STUFF((select convert(varchar(10),esw.WeekNum)+','  
			from ES_EducationScheduling es 
			inner join ES_EducationSchedulingWeekNum esw on es.EducationSchedulingID=esw.EducationSchedulingID 
			group by es.EducationSchedulingID,es.EducationSchedulingClassID,es.Weekday,es.CoursesTimeID,es.ClassroomID,esw.WeekNum 
			having es.EducationSchedulingClassID=x.EducationSchedulingClassID and es.Weekday=x.Weekday 
				and es.CoursesTimeID=x.CoursesTimeID and es.ClassroomID=x.ClassroomID 
				and STUFF((select ','+tu.Name 
					from ES_EducationSchedulingTeacher tescs 
					inner join ES_EducationScheduling tes on tescs.EducationSchedulingID=tes.EducationSchedulingID 
					inner join Sys_User tu on tescs.UserID=tu.UserID 
					where tescs.EducationSchedulingID=es.EducationSchedulingID 
					order by ISNULL(tescs.TeachingMethod,0),tu.Name
					for xml path('')), 1, 1, '')=x.TeacherName 
			order by esw.WeekNum
			for xml path('')
			), 1, 0, '') as WeekNums 
		from (
			select es.EducationSchedulingID,es.EducationSchedulingClassID,es.Weekday,es.CoursesTimeID,es.ClassroomID,STUFF((select ','+tu.Name 
				from ES_EducationSchedulingTeacher tescs 
				inner join ES_EducationScheduling tes on tescs.EducationSchedulingID=tes.EducationSchedulingID 
				inner join Sys_User tu on tescs.UserID=tu.UserID 
				where tescs.EducationSchedulingID=es.EducationSchedulingID 
				order by ISNULL(tescs.TeachingMethod,0),tu.Name
				for xml path('')), 1, 1, '') as TeacherName,esw.WeekNum  
			from ES_EducationScheduling es 
			inner join ES_EducationSchedulingWeekNum esw on es.EducationSchedulingID=esw.EducationSchedulingID 
			group by es.EducationSchedulingID,es.EducationSchedulingClassID,es.Weekday,es.CoursesTimeID,es.ClassroomID,esw.WeekNum
		) x 
		group by x.EducationSchedulingClassID,x.Weekday,x.CoursesTimeID,x.ClassroomID,x.TeacherName
	) wn on x.EducationSchedulingClassID=wn.EducationSchedulingClassID and x.Weekday=wn.Weekday 
		and x.CoursesTimeID=wn.CoursesTimeID and x.ClassroomID=wn.ClassroomID and x.TeacherName=wn.TeacherName
) es 
on ct.CoursesTimeID=es.CoursesTimeID 
and w.Weekday=es.Weekday 
and s.SchoolyearID=es.SchoolyearID 
and c.ClassmajorID=es.ClassmajorID 
where s.SchoolyearID=@SchoolyearID 
and (@CollegeID is null or c.CollegeID=@CollegeID)
and (@ClassmajorID is null or c.ClassmajorID=@ClassmajorID)
AND (@Years IS NULL OR c.Years=@Years)