USE [SDS_DevSchoolDistrict]
GO
/****** Object:  StoredProcedure [dbo].[AddUserDefinedStudentGroup]    Script Date: 09/07/2012 18:15:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================================================
-- Copyright:   School Data Solutions
-- =================================================================================
ALTER PROCEDURE [dbo].[AddUserDefinedStudentGroup]
    @studentGroupId     int OUTPUT,
    @isDuplicate     bit OUTPUT,
    @ReplaceExisting    bit = 0,


    @ApplicationUserId  int,
    @StudentGroupLabel  varchar(100),
    @StudentIdList      varchar(4000) = NULL
AS
BEGIN
        --DECLARE @StudentIdList        varchar(4000) = '1, 22, 333, 4444, 55555, 666666'

      -------------------------------------------------
      --
      -------------------------------------------------
      DECLARE @UpdateMembership bit = 0
      -------------------------------------------------

        -----------------------------------------------
        -- Check for existence of Students group with same name for user
        -----------------------------------------------
        SELECT  @StudentGroupId = SG.StudentGroupId
        FROM    StudentGroups SG
        WHERE   SG.ApplicationUserId = @ApplicationUserId
          AND   SG.StudentGroupName = @StudentGroupLabel
        -----------------------------------------------
        IF(@StudentGroupId IS NOT NULL)
        BEGIN -----------------------------------------
            SET @IsDuplicate = 1
            IF(@ReplaceExisting = 1)
            BEGIN ---------------------------------
                    UPDATE SG
                    SET     StudentGroupIsStatic = 1,
                            SmartGroupAttributesJson = NULL,
                            MembershipCriteriaFilter = NULL
                    FROM    StudentGroups SG
                    WHERE   SG.StudentGroupId = @StudentGroupId
                    ---------------------------------
                    SET @UpdateMembership = 1
            END  ---------------------------------
        END  -----------------------------------------
        ELSE
        BEGIN   -----------------------------------------
                SET @IsDuplicate = 0
                -----------------------------------------------
                --
                -----------------------------------------------
                IF(@StudentGroupId IS NULL)
                BEGIN
                        INSERT  StudentGroups (
                                StudentGroupLabel,
                                StudentGroupName,
                                ApplicationUserId,
                                StudentGroupIsStatic,
                                StudentGroupIsActive,
                                StudentGroupIsAutomatic,
                                StudentGroupIsShared,
                                SaveSnapshotAtCurrentSchoolYearTransition
                        )
                        VALUES  (
                                @StudentGroupLabel,
                                @StudentGroupLabel,
                                @ApplicationUserId,
                                1,1,0,0,0
                        )
                        -----------------------------------------------
                        SET @StudentGroupId = SCOPE_IDENTITY()
                        -----------------------------------------------
                        SET @UpdateMembership = 1
                        -----------------------------------------------
                END
                -----------------------------------------------
        END
        -----------------------------------------------


        -------------------------------------------
        -- if Student-Id's were passed in
        -------------------------------------------
        IF(@UpdateMembership = 1 AND RTRIM(LTRIM(ISNULL(@StudentIdList, ''))) <> '')
        BEGIN   -------------------------------------------
                --
                -------------------------------------------
                DECLARE @StudentIds TABLE ( StudentId   int, Ordinal int IDENTITY )
                INSERT  @StudentIds
                SELECT  CAST(Value as int)
                FROM    dbo.SplitList(@StudentIdList, ',')
                -------------------------------------------
                --SELECT * FROM @StudentIds
                -------------------------------------------
                INSERT  Students_Groups (StudentId, StudentGroupId)
                SELECT  StudentId, @StudentGroupId
                FROM    @StudentIds
                -------------------------------------------
        END




END