Database

/****** Object:  StoredProcedure [dbo].[sp_Timetable_View]    Script Date: 01/15/2010 10:51:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Timetable_View] @selectionType varchar(30),		-- either Venues, Staff, Subjects, Curricula etc...
@selectedItems ntext, @selectedTerms ntext

AS

/*This Part of the code was Representing the Sp Get_Staff_Cycles_For_TimeTable
 due to temp table scope , i had to put the code here so that the temp tables will be available 

/*This code of the code will Add the Cycles to be Displayed ina string 
*/
 */
 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#temp]'))
drop table [#temp]
--Creation of Temp1
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
into #temp
FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID


--STEP 3 HERE WE ARE CREATING A TEMP TABLE 
--CHECK IF THE TABLE EXISTS FIRST IF IT DOES DROP IT 
--HERE WE ARE CREATING A FIELD CYCLEIDlIST THAT IS EMPTY THAT WILL BE POPULATED LATER
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#TempSummary]'))
drop table [#TempSummary]


SELECT DESCR, dbo.GetSortedString(Cast(NULL AS varchar(8000))) AS CycleIdList,ACTV
INTO #TempSummary
FROM #temp (NOLOCK)
GROUP BY DESCR,ACTV
ORDER BY DESCR,ACTV

--CHECK THE TABLE CONTENTETS
--SELECT * FROM TempSummary
--WE ONLY SAW NAMES THAT ARE NOT DUPLICATED AND NULLS IN THE OTHER FIELD

--HERE WE UPDATE THE TEMPSUMMARY TABLE AND DO SOME CONCATINATING AND THIS IS VERY FAST,
--STEP 4
UPDATE #TempSummary
SET CycleIdList = dbo.Concat(#TempSummary.Descr,actv)

--SELECT * FROM TempSummary
--LETS CHECK THE TEMP SUMMARY TABLE
--select  * from TempSummary

--IT CONTAINS ALL THE DATA THAT WE WANT :)
--Select DISTINCT Descr AS [Staff],dbo.DistinctList(Cycles,Descr) As [Cycles] 
--into Temp2 From temp


/*============================================================================================================

Nornmal sp_Timetable_View sp was Starting here 
===========================================================================================================
*/

Declare @xmldoc int
EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedItems		--Create an internal representation of the XML document

-- turn the xml into a table with characteristics of the given table 
SELECT * 
INTO #selectedItems
FROM OPENXML ( @xmldoc , '/Root/Tags' , 2) 
WITH 	(
		[TagID] int
	)

EXEC sp_xml_removedocument @xmldoc

create table #selectedActvs (
	id int
)

-- From the list of selected items of whichever type, find the list of activities these relate to; i.e. which activities are envolved in the selection
if (@SelectionType = 'Venues') begin
-- show the timetable for the selected activities
	Insert into #selectedActvs
		(id)
		select distinct Actv [Id] 
		from sol_actv_venu
		where venu in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Staff') begin
	Insert into #selectedActvs
		(id)
		select distinct Actv [Id] 
		from sol_actv_staff
		where staff in ( select TagID from #selectedItems )		
end
if (@SelectionType = 'Subjects') begin
	Insert into #selectedActvs
		(id)
		select distinct Actv [Id] 
		from sol_actv_time
		inner join tbl_actv a on a.id = sol_actv_time.Actv
		inner join tbl_cntc ct on ct.id = a.cntcID
		where modlID in ( select TagID from #selectedItems )		
end
if (@SelectionType = 'SubjectContainer') begin
	Insert into #selectedActvs
		(id)
		select distinct Actv [Id] 
		from sol_actv_time
		inner join tbl_actv a on a.id = sol_actv_time.Actv
		inner join tbl_cntc ct on ct.id = a.cntcID
		inner join tbl_modl m on m.id=ct.modlid
		inner join mtm_modl_container mc on mc.modl=m.id
		where mc.container in ( select TagID from #selectedItems )		
end
if (@SelectionType = 'Curricula') begin
	Insert into #selectedActvs
		(id)
		select distinct sa.Actv [Id] 
		from sol_actv_time sa
		inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
		inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
		inner join tbl_curr_strm c on c.id = cs.currstrmid
		where c.curr in ( select TagID from #selectedItems )		
end			
if (@SelectionType = 'Curriculum Streams') begin
	Insert into #selectedActvs
		(id)
		select distinct sa.Actv [Id] 
		from sol_actv_time sa
		inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
		inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
		inner join tbl_curr_strm c on c.id = cs.currstrmid
		where c.id in ( select TagID from #selectedItems )		
end			

EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedTerms		--Create an internal representation of the XML document

-- turn the xml into a table with characteristics of the given table 
SELECT * 
INTO #selectedTerms
FROM OPENXML ( @xmldoc , '/Root/Tags' , 2) 
WITH 	(
		[TagID] int
	)

EXEC sp_xml_removedocument @xmldoc

select distinct tt.Dy, tt.Sess, m.descr as  [Code], m.LongName as[Description], ctyp.Abrev, ctyp.Descr as [Type], ct.Number,
a.GrpName, a.GrpNumber, a.Duration, 
CASE WHEN MAV.stud IS NULL THEN a.Students
ELSE MAV.STUD END as [Students], 
v.descr as [Venue], v.Capacity,
s.descr as [Staff], m.id as [ModlID], t.Descr as [Term]
, ISNULL(temp.CYCLEIDLIST,t.Descr) as [StaffTerm]
, dates.descr [Date],a.Length as [Length]
from sol_actv_time tt
inner join tbl_clmn dates on dates.id=tt.dy
inner join tbl_actv a on a.id = tt.actv
inner join #selectedActvs SelectedActvs on SelectedActvs.ID = a.id	-- filter the list of activies to be shown by the activities which fall in the selection
inner join tbl_cntc ct on ct.id = a.cntcid
inner join tbl_cntc_typ ctyp on ctyp.id = ct.cntctyp
inner join tbl_modl m on m.id = ct.modlid
inner join tbl_term t on t.id = a.term		--This will become t.id = actv.term when terms move to activities
inner join #selectedTerms selTerms on selTerms.TagID = a.term
left outer join sol_actv_venu ttVenu on ttVenu.actv = a.id
left outer join SOL_ACTV_VENU mav on mav.venu=ttVenu.Venu and mav.actv=a.id
left outer join tbl_venue v on v.id = ttVenu.Venu
left outer join sol_actv_staff ttStaff on ttStaff.Actv = a.id
left outer join mtm_actv_staff mas on mas.actv=ttStaff.actv and mas.staff=ttStaff.Staff
left outer join tbl_term staffTerm on staffTerm.id=mas.cycletemplate
left outer join tbl_staff s on s.id = ttStaff.Staff
left outer join [#TempSummary] temp on temp.descr = s.descr
order by tt.dy, tt.sess

ALTER FUNCTION [dbo].[Concat] (@Name varchar(50),@Actv int)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
Declare @s varchar(max)
SET @s = ''
SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '')
FROM #Temp (NOLOCK)
WHERE #Temp.Descr = @Name And Temp.Actv = @Actv
GROUP BY Cycletemplate
ORDER BY Cycletemplate
IF (@S IS NOT NULL) AND (@S <> '') SET @S = SubString(@s, 2, Len(@S)-1)
RETURN @S
END