USE [VIGIL-VRM]
GO

-- DROP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_casefile_investigators_list]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_casefile_investigators_list]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_interview_attendees_list]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_interview_attendees_list]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_interview_subjects_list]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_interview_subjects_list]
GO


-- CREATE

CREATE FUNCTION [dbo].[fn_casefile_investigators_list] (@caseid int)  
RETURNS varchar(1000)
BEGIN
	declare @investigators as varchar(1000)
	declare @lastname as varchar(50)

	declare cur cursor for 
		select p.lastname 
		from casefiles c, casefile_investigators ci, personnel p, personnel_type pt
		where c.id = ci.casefile_id_fk
		and p.id = ci.personnel_id_fk
		and p.type_id_fk = pt.id 
		and pt.description = 'Investigator'
		and c.id = @caseID

	open cur

	set @investigators = ''

	fetch next from cur into @lastname

	while (@@fetch_status = 0)
	begin
		if @investigators <> ''
		begin
			set @investigators =  @investigators + ', '
		end			

		set @investigators =  @investigators + @lastname

		fetch next from cur into @lastname
	end

	CLOSE cur
	DEALLOCATE cur
RETURN @investigators
END
GO

CREATE FUNCTION [dbo].[fn_interview_attendees_list] (@interviewID int)  
RETURNS varchar(1000)
BEGIN
declare @investigators as varchar(1000)
	declare @lastname as varchar(50)

	declare cur cursor for 
		select p.lastname 
		from interview_attendees i, personnel p
		where i.personnel_id_fk = p.id
			and i.interview_id_fk = @interviewID

	open cur

	set @investigators = ''

	fetch next from cur into @lastname

	while (@@fetch_status = 0)
	begin
		if @investigators <> ''
		begin
			set @investigators =  @investigators + ', '
		end			

		set @investigators =  @investigators + @lastname

		fetch next from cur into @lastname
	end

	CLOSE cur
	DEALLOCATE cur
RETURN @investigators
END
GO

CREATE FUNCTION [dbo].[fn_interview_subjects_list] (@interviewID int)  
RETURNS varchar(1000)
BEGIN
	declare @subjects as varchar(1000)
	declare @lastname as varchar(50)

	declare cur cursor for 
		select s.lastname 
		from interview_subjects i, subjects s
		where i.subject_id_fk = s.id
			and i.interview_id_fk = @interviewID

	open cur

	set @subjects = ''

	fetch next from cur into @lastname

	while (@@fetch_status = 0)
	begin
		if @subjects <> ''
		begin
			set @subjects =  @subjects + ', '
		end			

		set @subjects =  @subjects + @lastname

		fetch next from cur into @lastname
	end

	CLOSE cur
	DEALLOCATE cur
RETURN @subjects
END
GO