USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[StudentGroupCandidatesSearchResults]    Script Date: 04/13/2012 18:45:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--SELECT * FROM ProfileLookupSearchResults('Acierto')
-- GO

--SELECT * FROM DistrictAdministrators
--SELECT * FROM [StudentGroupCandidatesSearchResults]('smith', DEFAULT, DEFAULT, DEFAULT, 357, 1)
ALTER FUNCTION [dbo].[StudentGroupCandidatesSearchResults]
(
    @SearchText         varchar(100) = '',
--  @EntityTypeId       int = NULL,-- 1 (School District), 2 (School), 3 (Class), 4 (Family), 5 (Activity), or 6 (Person)
--  @PersonRoleId       int = NULL, -- 'Teacher', 'Student', 'Guardian', 'Specialist', 'School Administrator', or 'District Administrator'
    @RestrictToCurrent  bit = 1,
    @StudentGroupId     int = NULL,
    ---------------------------------------------------
    @DataViewMode       varchar(200) = 'Standard',
    @EncryptIds         bit = 0,
    @ApplicationUserId  int = NULL,
    @ApplicationUserPersonRoleId int = 1
)
--DECLARE  @OutputTable TABLE
RETURNS @OutputTable TABLE
(
    studentId                   ElementId,
    Ordinal             int,
    [Text]              varchar(500),
    displayName         varchar(500),
    Description         varchar(500),
    ImagePath           varchar(500),
    Gender              char(1)
)
AS
BEGIN   -- =============================================================================*/

        /*----------------------------------------------
        -- Debugging
        -----------------------------------------------
        DECLARE @SearchText         varchar(100) = 'John'
        DECLARE @RestrictToCurrent bit = 1
        DECLARE @DataViewMode       varchar(200) = 'Demonstration'
        DECLARE @EncryptIds         bit
        DECLARE @ApplicationUserId  int = 357
        DECLARE @ApplicationUserPersonRoleId int = 1
        -----------------------------------------------*/




        ----------------------------------------------
        --
        -----------------------------------------------
        DECLARE @EntityTypeId int = 6 -- Person
        DECLARE @PersonRoleId int = 8 -- Student
        -----------------------------------------------





        ----------------------------------------------
        --
        -----------------------------------------------
        INSERT  @OutputTable (
                studentId,
                Text,
                DisplayName,
                Gender,
                Description,
                Ordinal
        )
        SELECT  EntityId,
                EntityName,
                EntityName,
                Gender,
                PersonRoleDesc,
                ROW_NUMBER() OVER (ORDER BY R.EntityOrderingText) AS Ordinal
        FROM    ProfileLookupSearchResults(@SearchText, @EntityTypeId, @PersonRoleId, @RestrictToCurrent, @DataViewMode, @EncryptIds, @ApplicationUserId, @ApplicationUserPersonRoleId) R
        WHERE   NOT EXISTS (
                    SELECT  1
                    FROM    Students_Groups S_G
                    WHERE   S_G.StudentGroupId = @StudentGroupId
                      AND   S_G.StudentId = R.EntityId
                )
        ORDER
            BY  6
        -----------------------------------------------



        --SELECT * FROM @OutputTable
RETURN  -- =============================================================================
END