USP_MKTSEGMENTATIONTESTSEGMENT_GETCOMMONUSERDEFINEDCODES

CREATE procedure [dbo].[USP_MKTSEGMENTATIONTESTSEGMENT_GETCOMMONUSERDEFINEDCODES]
(
    @TESTSEGMENTIDSXML xml,
    @SOURCECODEID uniqueidentifier,
    @USERDEFINEDLOADEDSOURCECODESXML xml = null output
)
as
begin
    /* Convert IDs from xml to table */
    declare @TESTSEGMENTSTABLE table([ID] uniqueidentifier primary key);
    insert into @TESTSEGMENTSTABLE
    select T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
    from @TESTSEGMENTIDSXML.nodes('/ITEMLIST/ITEM') T(c);

    declare @USERDEFINEDLOADEDSOURCECODES table([PARTDEFINITIONVALUESID] uniqueidentifier, [CODE] nvarchar(10));
    declare @USERDEFINEDPARTDEFINITIONID uniqueidentifier;

    -- AdamBu - 2/16/11 - This table will hold all the source code parts that are defaults or

    --    associated with a test segment we are concerned with.

    declare @SOURCECODEPART table(
        [SEGMENTATIONTESTSEGMENTID] uniqueidentifier,
        [PARTDEFINITIONVALUESID] uniqueidentifier,
        [MKTSOURCECODEPARTDEFINITIONID] uniqueidentifier,
        [CODE] nvarchar(10)
    )
    insert into @SOURCECODEPART
    select 
        [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID],
        [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID],
        [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID], 
        [MKTSOURCECODEPART].[CODE]
    from dbo.[MKTSOURCECODEPART]
        inner join [MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
    where [MKTSOURCECODEPART].[SEGMENTATIONSEGMENTID] is null
        and( [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] is null 
            or [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] in (
                select ID
                from @TESTSEGMENTSTABLE
            )
        )

    -- AdamBu - 2/16/11 - This table will be filled in the cursor with the parts that

    --    are associated with the definition being cursored over at the time.

    declare @DEFINITIONSOURCECODEPART table(
        [SEGMENTATIONTESTSEGMENTID] uniqueidentifier,
        [PARTDEFINITIONVALUESID] uniqueidentifier,
        [MKTSOURCECODEPARTDEFINITIONID] uniqueidentifier,
        [CODE] nvarchar(10)
    )

    declare USERDEFINEDPARTS_CURSOR cursor local fast_forward
    for
        select [MKTSOURCECODEPARTDEFINITION].[ID]
        from dbo.[MKTSOURCECODEITEM]
            inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
        where 
            [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID and
            [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 5;

    open USERDEFINEDPARTS_CURSOR
    fetch next from USERDEFINEDPARTS_CURSOR into @USERDEFINEDPARTDEFINITIONID;

    while (@@fetch_status = 0)
    begin

        -- AdamBu - 2/16/11 - Clear and fill the table with source code parts that are associated with the

        --    current definition.

        delete @DEFINITIONSOURCECODEPART
        insert into @DEFINITIONSOURCECODEPART
        select 
            SOURCECODEPART.[SEGMENTATIONTESTSEGMENTID],
            SOURCECODEPART.[PARTDEFINITIONVALUESID],
            SOURCECODEPART.[MKTSOURCECODEPARTDEFINITIONID], 
            SOURCECODEPART.[CODE]
        from @SOURCECODEPART SOURCECODEPART
        where SOURCECODEPART.[MKTSOURCECODEPARTDEFINITIONID] = @USERDEFINEDPARTDEFINITIONID

        -- AdamBu - 2/16/11 - If there is only 1 unique test segment/part definition pair (the default), we know

        --    that all the test segments in question are using the marketing effort's source code value.

        --    Add the source code part to the output table.

        if (
            select count([PARTDEFINITIONVALUESID])
            from (
                select distinct DEFINITIONSOURCECODEPART.[SEGMENTATIONTESTSEGMENTID],DEFINITIONSOURCECODEPART.[PARTDEFINITIONVALUESID] 
                from @DEFINITIONSOURCECODEPART DEFINITIONSOURCECODEPART
            ) UNIQUEDEFAULTCODES
        ) = 1
        begin
            insert into @USERDEFINEDLOADEDSOURCECODES
            select distinct 
                DEFINITIONSOURCECODEPART.[PARTDEFINITIONVALUESID],
                DEFINITIONSOURCECODEPART.[CODE] 
            from @DEFINITIONSOURCECODEPART DEFINITIONSOURCECODEPART
        end
        -- AdamBu - 2/16/11 - If any test segments can't be joined to a definition source code part, we know that that

        --    segment is using the effort's value.  The check above ensures that not all segments are using the effort's

        --    value, so if there are any here, we are dealing with a mix of effort values and specific values. We don't 

        --    need to check further and don't add the source code part to the output table.

        else if not exists(
            select distinct PARTDEFINITIONVALUESID
            from @TESTSEGMENTSTABLE TESTSEGMENTSTABLE
                left join @DEFINITIONSOURCECODEPART DEFINITIONSOURCECODEPART on DEFINITIONSOURCECODEPART.SEGMENTATIONTESTSEGMENTID = TESTSEGMENTSTABLE.ID
            where DEFINITIONSOURCECODEPART.PARTDEFINITIONVALUESID is null
        )
        begin
            -- AdamBu - 2/16/11 - Because we ensure there were not effort/default values above, if only 1 unique 

            --    part definition exists, the test segments in question are using the same specific values, so 

            --    add the source code part to the output table.

            if (select count([PARTDEFINITIONVALUESID])
            from (
                select distinct PARTDEFINITIONVALUESID
                from @TESTSEGMENTSTABLE TESTSEGMENTSTABLE
                    left join @DEFINITIONSOURCECODEPART DEFINITIONSOURCECODEPART on DEFINITIONSOURCECODEPART.SEGMENTATIONTESTSEGMENTID = TESTSEGMENTSTABLE.ID
            ) test) = 1
            begin
                insert into @USERDEFINEDLOADEDSOURCECODES
                select distinct
                    DEFINITIONSOURCECODEPART.[PARTDEFINITIONVALUESID],
                    DEFINITIONSOURCECODEPART.[CODE]
                from @TESTSEGMENTSTABLE TESTSEGMENTSTABLE
                    left join @DEFINITIONSOURCECODEPART DEFINITIONSOURCECODEPART on DEFINITIONSOURCECODEPART.SEGMENTATIONTESTSEGMENTID = TESTSEGMENTSTABLE.ID
            end
        end

        fetch next from USERDEFINEDPARTS_CURSOR into @USERDEFINEDPARTDEFINITIONID;
    end

    close USERDEFINEDPARTS_CURSOR;
    deallocate USERDEFINEDPARTS_CURSOR;

    --AdamBu - 2/16/11 - Convert output table to XML and fill output parameter.

    set @USERDEFINEDLOADEDSOURCECODESXML = (
        select [PARTDEFINITIONVALUESID], [CODE] 
        from @USERDEFINEDLOADEDSOURCECODES 
        for xml raw('ITEM'),type,elements,root('ITEMLIST'),BINARY BASE64
    );

    return 0;
end