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

SELECT distinct ct.CoursesTimeID
	  ,ct.StartTimes
	  ,ct.Times
	  ,ct.TimeDesc
	  ,w.Weekday
	  ,w.WeekdayDesc
	  ,s.SchoolyearID
	  ,s.Code
	  ,es.EducationSchedulingClassID 
	  ,es.TaskGroupName 
	  ,es.StudentCount
	  ,es.EducationMissionClassID 
	  ,es.OrderNo
	  ,es.Name 
	  ,es.TeachingModeID
	  ,es.EducationMissionID 
	  ,c.BuildingsInfoID 
	  ,c.BuildingsName 
	  ,es.CourseCode
	  ,es.CourseName
	  ,es.StartWeeklyNum
	  ,es.EndWeeklyNum
	  ,c.ClassroomID
	  ,c.ClassroomName
	  ,es.DepartmentID
	  ,es.TeacherName 
	  ,es.WeekNums 
	  ,es.MinWeekNum 
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,b.BuildingsInfoID,c.ClassroomID,c.Name as ClassroomName,b.Name as BuildingsName 
	from CF_Classroom c 
	left join CF_BuildingsInfo b on c.BuildingsInfoID=b.BuildingsInfoID 
	inner join (
		select em.SchoolyearID,es.ClassroomID 
		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 
		group by em.SchoolyearID,es.ClassroomID
	) es on c.ClassroomID=es.ClassroomID 
) 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
		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 
		  LEFT JOIN EM_EducationMissionClass_CF_Classmajor 
			ON EM_EducationMissionClass.EducationMissionClassID = EM_EducationMissionClass_CF_Classmajor.EducationMissionClassID 
		  LEFT 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
		) 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 
	union all 
	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 
			ceus.Weekday,
			ceus.CoursesTimeID,
			ceu.ClassroomID,
			ceus.ClassroomExcessiveUseSchedulingID as EducationSchedulingClassID,
			'' as TaskGroupName,
			null as StudentCount,
			ceu.ClassroomExcessiveUseID as EducationMissionClassID,
			0 as OrderNo,
			ceu.Content as Name,
			null as TeachingModeID,
			null as EducationMissionID,
			ceu.CollegeID,
			ceu.SchoolyearID,
			'' as CourseCode,
			'' as CourseName,
			0 as StartWeeklyNum,
			0 as EndWeeklyNum,
			c.Name as ClassroomName,
			null as ClassmajorID,
			null as ClassMajorName,
			null as DepartmentID,
			null as TeacherName
		from ES_ClassroomExcessiveUse ceu 
		inner join CF_Classroom c on ceu.ClassroomID=c.ClassroomID 
		inner join ES_ClassroomExcessiveUseScheduling ceus on ceu.ClassroomExcessiveUseID=ceus.ClassroomExcessiveUseID 
	) x
	left join (
		select x.ClassroomExcessiveUseSchedulingID,MIN(x.WeekNum) as MinWeekNum,
			STUFF((select convert(varchar(10),ceusw.WeekNum)+','  
			from ES_ClassroomExcessiveUseSchedulingWeekNum ceusw 
			where ceusw.ClassroomExcessiveUseSchedulingID=x.ClassroomExcessiveUseSchedulingID
			order by ceusw.WeekNum
			for xml path('')
			), 1, 0, '') as WeekNums 
		from ES_ClassroomExcessiveUseSchedulingWeekNum x 
		group by x.ClassroomExcessiveUseSchedulingID
	) wn on x.EducationSchedulingClassID=wn.ClassroomExcessiveUseSchedulingID
	
) es 
on ct.CoursesTimeID=es.CoursesTimeID  
and w.Weekday=es.Weekday 
and s.SchoolyearID=es.SchoolyearID 
and c.ClassroomID=es.ClassroomID 
where s.SchoolyearID=@SchoolyearID 
and (c.BuildingsInfoID=@BuildingsInfoID or @BuildingsInfoID is null)
and (c.ClassroomID=@ClassroomID or @ClassroomID is null)