Untitled

USE [DaymapBranch]
GO
/****** Object:  StoredProcedure [dbo].[pr_FindStudents]    Script Date: 6/21/2023 1:26:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/* 
Added: "OR s.Firstname + ' ' + s.Surname LIKE '%' + @Name + '%')" in the WHERE clause.
This adds the firstname and surname together with a space and compares it to @Name returning matching results. Donnie 20/11/13
*/

ALTER PROCEDURE [dbo].[pr_FindStudents] 
	@ID nvarchar(50)=''
	, @Name nvarchar(100)
	, @IncludeInactiveStudents bit=0
	, @Photos bit=0
	, @GroupMask int=0
AS


DECLARE @d datetime
SET @d=[dbo].[GetDaymapDate]()

DECLARE @StudentList TABLE
(
	[ID] int,
	StudentCode nvarchar(20),
	FullName nvarchar(1500),
	ClassID int,
	RoomCode nvarchar(255),
	SubjectCode nvarchar(255),
	Email nvarchar(255),
	Form nvarchar(100),
	EventID int,
	[Group] nvarchar(100),
	[Year] nvarchar(100),
	House nvarchar(100),
	PhotoID int,
	Photo image null,
	[Location] nvarchar(100)
)
DECLARE @StudentListTemp TABLE
(
	[ID] int,
	StudentCode nvarchar(20),
	FullName nvarchar(1500),
	ClassID text,
	RoomCode nvarchar(255),
	SubjectCode nvarchar(255),
	Email nvarchar(255),
	Form nvarchar(100),
	EventID text,
	[Group] nvarchar(100),
	[Year] nvarchar(100),
	House nvarchar(100),
	PhotoID int,
	Photo image null,
	[Location] nvarchar(100)
)

IF @Photos=0 
	BEGIN
		INSERT INTO @StudentList
		SELECT s.[ID], s.StudentCode, s.Firstname + ' ' + s.Surname, c.ClassID,
		dbo.EventRooms(cl.EventID) AS RoomCode, c.SubjectCode, s.Email, s.Form, cl.EventID, s.[Group], s.[Year], s.House, NULL As PhotoID, NULL As Photo, CONCAT(c.SubjectCode, ' ', dbo.EventRooms(cl.EventID)) 
		FROM (SELECT PersonID AS StudentID,  ClassID, ce.EventID
				FROM dbo.CalendarEvents ce INNER JOIN
				dbo.CalendarEventPeople cep ON cep.EventID=ce.EventID
				WHERE ce.StartTime <= @d AND ce.EndTime > @d AND cep.PersonType=2) cl 
		INNER JOIN dbo.Class c ON cl.ClassID=c.ClassID 
		RIGHT OUTER JOIN dbo.Students s ON cl.StudentID = s.[ID]
		WHERE (
			((@ID='') AND (s.Surname LIKE '%' + @Name + '%' OR s.Firstname LIKE '%' + @Name + '%' OR s.Firstname + ' ' + s.Surname LIKE '%' + @Name + '%' ))
			OR
			((@ID<>'') AND (s.StudentCode=@ID))
		)
		AND (@IncludeInactiveStudents=1 OR  ((s.Flags & 1)=1))
		ORDER BY s.Surname 
	END
ELSE
	BEGIN
		INSERT INTO @StudentList
		SELECT s.[ID], s.StudentCode, s.Firstname + ' ' + s.Surname, c.ClassID,
		dbo.EventRooms(cl.EventID) AS RoomCode, c.SubjectCode, s.Email, s.Form, cl.EventID, s.[Group], s.[Year], s.House, p.StudentID AS PhotoID, p.Photo, CONCAT(c.SubjectCode, ' ', dbo.EventRooms(cl.EventID)) 
		FROM (SELECT PersonID AS StudentID,  ClassID, ce.EventID
				FROM dbo.CalendarEvents ce INNER JOIN
				dbo.CalendarEventPeople cep ON cep.EventID=ce.EventID
				WHERE ce.StartTime <= @d AND ce.EndTime > @d AND cep.PersonType=2) cl 
		INNER JOIN dbo.Class c ON cl.ClassID=c.ClassID 
		RIGHT OUTER JOIN dbo.Students s ON cl.StudentID = s.[ID]
		LEFT OUTER JOIN StudentPhotos p ON p.StudentID=s.[ID]
		WHERE (
			((@ID='') AND (s.Surname LIKE '%' + @Name + '%' OR s.Firstname LIKE '%' + @Name + '%' OR s.Firstname + ' ' + s.Surname LIKE '%' + @Name + '%' ))
			OR
			((@ID<>'') AND (s.StudentCode=@ID))
		)
		AND (@IncludeInactiveStudents=1 OR  ((s.Flags & 1)=1))
		ORDER BY s.Surname 
	END
	
--Student notes
SELECT    s.[ID],snc.CategoryID,snc.Category,snc.Colour, sn.NoteID,sn.Updated 
 FROM         dbo.StudentNotes sn 
INNER JOIN @StudentList s ON sn.StudentID=s.[ID]
INNER JOIN dbo.StudentNoteCategories snc ON snc.CategoryID=sn.CategoryID
WHERE snc.Colour IS NOT NULL 
	  AND sn.Expiry>=dbo.DayFromDate(@d)
	  AND ((@GroupMask & snc.[Read]) > 0 OR (@GroupMask & snc.[Edit] > 0))
	  AND snc.[Attendance] =0
ORDER BY s.[ID], snc.CategoryID, sn.Updated desc

--Student list
Insert into @StudentListTemp
select ID, StudentCode, FullName, String_agg(ClassID,', ') as ClassID, 
String_agg(RoomCode,', ') as RoomCode, String_agg(SubjectCode,', ') as SubjectCode, Email,
Form, String_agg(EventID,', ') as EventID, [Group],
[Year], House, cast(PhotoID  as varbinary(max)) as PhotoID, cast(Photo  as varbinary(max)) as Photo, String_agg([Location], ', ') as [Location]
from @StudentList
Group by ID, StudentCode, FullName, [Year], Form, Email, [Group], House, cast(PhotoID  as varbinary(max)), cast(Photo  as varbinary(max))
Select * from @StudentListTemp