USE [SDS_DevSchoolDistrict]
GO
/****** Object:  StoredProcedure [dbo].[CreateUserDefinedAssessmentTestGroup]    Script Date: 04/16/2012 19:29:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CreateUserDefinedAssessmentTestGroup]
    @ApplicationUserId  int,
    @AssessmentTestGroupName    varchar(100),
    @AssessmentTestIdList       varchar(4000) = NULL,
    @AssessmentTestGroupId      int OUTPUT
AS
BEGIN

        /*---------------------------------------------------------
        -- Debugging
        ---------------------------------------------------------
        -- SELECT TOP 3 AssessmentId FROM Assessments ORDER BY AssessmentId
        -- SELECT * FROM AssessmentGRoups SG WHERE SG.ApplicationUserId IS NOT NULL
        DECLARE @ApplicationUserId int          SET @ApplicationUserId = 357
        DECLARE @AssessmentGroupId  int = 22588
        DECLARE @AssessmentIdList   varchar(4000) = '197, 198, 199'
        ---------------------------------------------------------*/



        ------------------------------------------------------
        DECLARE @MyAssessmentTestGroupId int
        ------------------------------------------------------
        SELECT  @MyAssessmentTestGroupId = ATG.AssessmentTestGroupId
        FROM    AssessmentTestGroups ATG
        WHERE   ATG.AssessmentTestGroupLabel = @AssessmentTestGroupName
          AND   ATG.ApplicationUserId = @ApplicationUserId
        ------------------------------------------------------

        ------------------------------------------------------
        DECLARE @TheirAssessmentTestGroupId int
        ------------------------------------------------------
        SELECT  @MyAssessmentTestGroupId = ATG.AssessmentTestGroupId
        FROM    AssessmentTestGroups ATG
        WHERE   ATG.AssessmentTestGroupLabel = @AssessmentTestGroupName
          AND   ATG.ApplicationUserId <> @ApplicationUserId
        ------------------------------------------------------


        IF(@MyAssessmentTestGroupId IS NOT NULL)
        BEGIN   ------------------------------------------------------
                SET @AssessmentTestGroupId = @MyAssessmentTestGroupId

        END     ------------------------------------------------------
        ELSE IF(@TheirAssessmentTestGroupId IS NOT NULL)
        BEGIN   ------------------------------------------------------
                SET @AssessmentTestGroupName = @AssessmentTestGroupName + ' (' + CAST(@ApplicationUserId as varchar(8)) + ')'
                ------------------------------------------------------
                INSERT  AssessmentTestGroups (AssessmentTestGroup, AssessmentTestGroupLabel, ApplicationUserId)
                VALUES (@AssessmentTestGroupName, @AssessmentTestGroupName, @ApplicationUserId)
                ------------------------------------------------------
                SET @AssessmentTestGroupId = SCOPE_IDENTITY()
        END     ------------------------------------------------------
        ELSE
        BEGIN   ------------------------------------------------------
                INSERT  AssessmentTestGroups (AssessmentTestGroup, AssessmentTestGroupLabel, ApplicationUserId)
                VALUES (@AssessmentTestGroupName, @AssessmentTestGroupName, @ApplicationUserId)
                ------------------------------------------------------
                SET @AssessmentTestGroupId = SCOPE_IDENTITY()
        END     ------------------------------------------------------





        -------------------------------------------
        --
        -------------------------------------------
        IF(ISNULL(@AssessmentTestIdList, '') <> '')
        BEGIN
                DECLARE @AssessmentTestIds TABLE ( AssessmentTestId int )
                INSERT  @AssessmentTestIds
                SELECT  CAST(Value as int)
                FROM    dbo.SplitList(ISNULL(@AssessmentTestIdList,''), ',')
                -------------------------------------------
                --SELECT * FROM @AssessmentTestIds
                -------------------------------------------


                -------------------------------------------
                --
                -------------------------------------------
                INSERT  AssessmentTests_Groups(AssessmentTestId, AssessmentTestGroupId)
                SELECT  AssessmentTestId, @AssessmentTestGroupId
                FROM    @AssessmentTestIds S
                WHERE   NOT EXISTS (
                                SELECT  1
                                FROM    AssessmentTests_Groups S_G
                                WHERE   S_G.AssessmentTestId = S.AssessmentTestId
                                  AND   S_G.AssessmentTestGroupId = @AssessmentTestGroupId
                        )
                -------------------------------------------
                --SELECT * FROM Assessments_Groups WHERE AssessmentGroupId = @AssessmentGroupId
                -------------------------------------------
        END
        -------------------------------------------

END