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

select demc.*,s.Code as SchoolyearCode,d.Name as DepartmentName,c.No AS CollegeNo,
	w.Weekday,w.WeekdayDesc,case when right(es.WeekNums,1)=',' then substring(es.WeekNums,0,LEN(es.WeekNums)) else es.WeekNums end as WeekNums,es.ClassroomID,
	es.Name as ClassroomName,es.StartTimes,es.Times,es.StopWeekNumStr 
from (
	select distinct x.* 
	from (
		select cm.CourseName,emc.EducationMissionClassID,emc.Name as EducationMissionClassName,
					ISNULL(emcts.TheoryCourse,0)+
					ISNULL(emcts.Practicehours,0) as TotalHours,
					esccs.StudentCount,u.Name as TeacherName,dit.Name as TitleDesc,em.SchoolyearID,
					em.DepartmentID,emc.HandleModeID,
					g.GradeID as GradeYearID,f.StandardID,emc.OrderNo 
		from ES_EducationScheduling es 
		inner join EM_CoursesTime ct on es.CoursesTimeID=ct.CoursesTimeID 
		inner join ES_EducationSchedulingTeacher est on es.EducationSchedulingID=est.EducationSchedulingID 
		inner join CF_Staff s on est.UserID=s.UserID 
		inner join Sys_User u on s.UserID=u.UserID
		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_EducationMissionClassTeachingSetting emcts on emc.EducationMissionClassID=emcts.EducationMissionClassID 
		left join EM_Coursematerial cm on emc.CoursematerialID=cm.CoursematerialID 
		left join CF_Classmajor c on emc.MainScheduleClassID=c.ClassmajorID
		left join CF_Grademajor g on c.GrademajorID = g.GrademajorID 
		left join CF_Facultymajor f on g.FacultymajorID = f.FacultymajorID 
		left join Sys_DictionaryItem dit on dit.DictionaryCode='CF_Title' and dit.Value=s.TitleID
		left join (
			select EducationSchedulingClassID,COUNT(1) as StudentCount 
			from EM_EducationSchedulingClass_CF_Student
			group by EducationSchedulingClassID
		) esccs on esc.EducationSchedulingClassID=esccs.EducationSchedulingClassID 
		where ct.RecordStatus>0
	) x 
) demc 
inner join CF_Schoolyear s on demc.SchoolyearID=s.SchoolyearID
inner join CF_Department d on demc.DepartmentID=d.DepartmentID 
inner join CF_College c on d.CollegeID=c.CollegeID 
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 
left join (
	select x.EducationMissionClassID,x.Weekday,x.ClassroomID,x.Name,x.StartTimes,x.Times,STUFF((select convert(varchar(10),esw.WeekNum)+','  
			from ES_EducationScheduling es 
			inner join ES_EducationSchedulingWeekNum esw on es.EducationSchedulingID=esw.EducationSchedulingID 
			inner join EM_EducationSchedulingClass esc on es.EducationSchedulingClassID=esc.EducationSchedulingClassID 
			group by esc.EducationMissionClassID,es.Weekday,es.ClassroomID,esw.WeekNum 
			having esc.EducationMissionClassID=x.EducationMissionClassID and es.Weekday=x.Weekday 
				and es.ClassroomID=x.ClassroomID 
				and STUFF((select ','+CONVERT(varchar(10),ctt.StartTimes)+','+CONVERT(varchar(10),ctt.EndTimes) 
					from ES_EducationScheduling est 
					inner join EM_CoursesTime ctt on est.CoursesTimeID=ctt.CoursesTimeID 
					inner join EM_EducationSchedulingClass esct on est.EducationSchedulingClassID=esct.EducationSchedulingClassID
					where esct.EducationMissionClassID=esc.EducationMissionClassID 
						and est.ClassroomID=es.ClassroomID 
						and est.Weekday=es.Weekday and ctt.RecordStatus>0
					order by ctt.StartTimes
					for xml path('')), 1, 1, '')=x.Times 
			order by esw.WeekNum
			for xml path('')
			), 1, 0, '') as WeekNums,
			x.StopWeekNumStr  
	from (
		select esc.EducationMissionClassID,es.Weekday,c.ClassroomID,c.Name,Min(ct.StartTimes) as StartTimes,STUFF((select ','+CONVERT(varchar(10),ctt.StartTimes)+','+CONVERT(varchar(10),ctt.EndTimes) 
			from ES_EducationScheduling est 
			inner join EM_CoursesTime ctt on est.CoursesTimeID=ctt.CoursesTimeID 
			inner join EM_EducationSchedulingClass esct on est.EducationSchedulingClassID=esct.EducationSchedulingClassID
			inner join CF_Classroom ct on est.ClassroomID=ct.ClassroomID 
			where esct.EducationMissionClassID=esc.EducationMissionClassID 
				and ct.ClassroomID=c.ClassroomID 
				and est.Weekday=es.Weekday and ctt.RecordStatus>0
			order by ctt.StartTimes
			for xml path('')), 1, 1, '') as Times,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 
			where tess.EducationMissionClassID=esc.EducationMissionClassID 
				and tess.ClassroomID=c.ClassroomID 
				and tess.Weekday=es.Weekday and tess.RecordStatus=3  
			order by ISNULL(tess.WeekNum,0)
			for xml path('')), 1, 1, '') as StopWeekNumStr 
		from ES_EducationScheduling es 
		inner join EM_CoursesTime ct on es.CoursesTimeID=ct.CoursesTimeID 
		inner join EM_EducationSchedulingClass esc on es.EducationSchedulingClassID=esc.EducationSchedulingClassID
		inner join CF_Classroom c on es.ClassroomID=c.ClassroomID 
		where ct.RecordStatus>0
		group by esc.EducationMissionClassID,es.Weekday,c.ClassroomID,c.Name
	) x 
	group by x.EducationMissionClassID,x.Weekday,x.ClassroomID,x.Name,x.StartTimes,x.Times,x.StopWeekNumStr
) es on demc.EducationMissionClassID=es.EducationMissionClassID and w.Weekday=es.Weekday
where demc.SchoolyearID=@SchoolyearID 
and d.CollegeID=@CollegeID 
and d.DepartmentID=@DepartmentID 
and (demc.HandleModeID=@HandleModeID or @HandleModeID is null)
order by demc.CourseName,demc.EducationMissionClassName,demc.TeacherName;