USP_APPEALMAILINGSETUP_CREATEORUPDATESEGMENT

CREATE procedure dbo.USP_APPEALMAILINGSETUP_CREATEORUPDATESEGMENT
(
    @ID uniqueidentifier output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @APPEALMAILINGID uniqueidentifier,
    @NAME nvarchar(100),
    @SELECTIONS xml
)
with execute as owner
as
begin
    --This procedure creates a segment and a view that unions the selections together. This is required specifically for the appeal mailing setup form.


    set nocount on;

    declare @CURRENTDATE datetime = getDate();

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @UPDATE bit = 1;
    if @ID is null
    begin
        set @ID = newId();
        set @UPDATE = 0;
    end

    declare @BBECRECORDSOURCEID uniqueidentifier;
    set @BBECRECORDSOURCEID = dbo.UFN_MKTRECORDSOURCE_GETFIRSTBBECRECORDSOURCEID();

    declare @NEWNAME nvarchar(100);
    select @NEWNAME = MKTSEGMENTATION.NAME from dbo.MKTSEGMENTATION inner join dbo.MKTSEGMENTATIONSEGMENT on MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = MKTSEGMENTATION.ID where MKTSEGMENTATIONSEGMENT.SEGMENTID = @ID;
    declare @MKTSEGMENTNAME nvarchar(100) = dbo.UFN_MKTSEGMENT_GETUNIQUENAME(@ID, @NAME, null);
    declare @MKTSEGMENTDESCRIPTION nvarchar(255) = 'Auto generated segment for mailing "' + @NAME + '".';

    if @UPDATE = 0 or @NAME <> @NEWNAME
        set @MKTSEGMENTNAME = dbo.[UFN_MKTSEGMENT_GETUNIQUENAME](@ID, @NAME, null);
    else
        set @MKTSEGMENTNAME = dbo.[UFN_MKTSEGMENT_GETNAME](@ID);

    if @UPDATE = 0
        -- Save the segment

        insert into dbo.MKTSEGMENT
        (
            ID,
            NAME,
            DESCRIPTION,
            SEGMENTTYPECODE,
            SEGMENTCATEGORYCODEID,
            CODE,
            QUERYVIEWCATALOGID,
            PARTDEFINITIONVALUESID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        ) 
        values 
        (
            @ID,
            @MKTSEGMENTNAME,
            @MKTSEGMENTDESCRIPTION,
            1,
            null,
            '',
            @BBECRECORDSOURCEID,
            null,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );
    else
        update dbo.MKTSEGMENT
        set
            NAME = @MKTSEGMENTNAME,
            DESCRIPTION = @MKTSEGMENTDESCRIPTION,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @ID;

    declare @SEGMENTFAMILYTYPECODE tinyint;
    select @SEGMENTFAMILYTYPECODE = SEGMENTFAMILYTYPECODE from dbo.MKTSEGMENT where ID = @ID;

    if @UPDATE = 0
        -- Insert a parameter set for the refresh process for this segment

        insert into dbo.MKTSEGMENTREFRESHPROCESS
        (
            ID,
            SEGMENTID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            newid(),
            @ID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

    -- Save all the selections

    if @UPDATE = 0
        exec dbo.USP_MKTSEGMENT_GETSELECTIONS_ADDFROMXML @ID, null, @SELECTIONS, @CHANGEAGENTID, @CURRENTDATE;
    else
        exec dbo.USP_MKTSEGMENT_GETSELECTIONS_UPDATEFROMXML @ID, null, @SELECTIONS, @CHANGEAGENTID, @CURRENTDATE;

    -- Create the VIEW and add it to the IDSETREGISTER

    declare @SEGSQL nvarchar(max);
    declare @VIEWSQL nvarchar(max);
    declare @SEGMENTVIEW nvarchar(60);
    declare @TABLECOUNT int;
    declare @BASETABLE nvarchar(255);
    declare @UNIONTABLE nvarchar(255);
    declare @SELECTIONNAME nvarchar(300);

    declare @SEGMENTNAME nvarchar(100);
    declare @SEGMENTDESCRIPTION nvarchar(255);
    declare @DATATYPE nvarchar(128);
    declare @SELECTIONID uniqueidentifier;
    declare @IDSETREGISTERID uniqueidentifier;
    declare @RECORDTYPEID uniqueidentifier;

    declare @RETVAL int;

    begin try
        set @TABLECOUNT = 1;
        set @BASETABLE = null;

        -- Get the segment information

        select
            @SEGMENTVIEW = dbo.UFN_MKTSEGMENT_MAKEVIEWNAME(MKTSEGMENT.ID),
            @SEGMENTNAME = MKTSEGMENT.NAME + ' (Segment)',
            @SEGMENTDESCRIPTION = MKTSEGMENT.DESCRIPTION,
            @DATATYPE = QUERYVIEWCATALOG.PRIMARYKEYTYPENAME,
            @RECORDTYPEID = QUERYVIEWCATALOG.RECORDTYPEID
        from 
            dbo.MKTSEGMENT
        inner join
            dbo.QUERYVIEWCATALOG on QUERYVIEWCATALOG.ID = MKTSEGMENT.QUERYVIEWCATALOGID
        where
            MKTSEGMENT.ID = @ID;

        -- Segment selections

        declare SEGMENTCURSOR cursor local fast_forward for
            select
                MKTSEGMENTSELECTION.SELECTIONID
            from
                dbo.MKTSEGMENTSELECTION
            inner join
                dbo.IDSETREGISTER on MKTSEGMENTSELECTION.SELECTIONID = IDSETREGISTER.ID
            where
                MKTSEGMENTSELECTION.SEGMENTID = @ID;

        open SEGMENTCURSOR;
        fetch next from SEGMENTCURSOR into @SELECTIONID;

        while (@@FETCH_STATUS = 0)
        begin
            --Remove the CR and LF characters since we are inserting this name into a dash-dash (--) comment in the SQL we are building...

            select
                @SELECTIONNAME = replace(replace(NAME, char(13), ''), char(10), '')
            from
                dbo.IDSETREGISTER
            where
                ID = @SELECTIONID;

            if @BASETABLE is null
            begin
                set @BASETABLE = 'T' + convert(nvarchar(10), @TABLECOUNT) + '';
                set @SEGSQL = 'select '+ @BASETABLE +'.ID from dbo.' + dbo.UFN_MKTSELECTION_GETFUNCTIONNAME(@SELECTIONID) + ' as ' + @BASETABLE + '  --' + @SELECTIONNAME + char(13);

            end 
            else
            begin
                set @UNIONTABLE = 'T' + convert(nvarchar(10), @TABLECOUNT) + '';
                set @SEGSQL = isnull(@SEGSQL,'') + 'union select '+ @UNIONTABLE +'.ID from dbo.' + dbo.UFN_MKTSELECTION_GETFUNCTIONNAME(@SELECTIONID) + ' as ' + @UNIONTABLE + ' --' + @SELECTIONNAME + char(13);

            end

            set @TABLECOUNT = @TABLECOUNT + 1;
            fetch next from SEGMENTCURSOR into @SELECTIONID;
        end;

        close SEGMENTCURSOR;
        deallocate SEGMENTCURSOR;

        -- Create or alter the segment view

        declare @OBJID int;
        select @OBJID = object_id(@SEGMENTVIEW, N'V');
        if @OBJID is null
            set @VIEWSQL = 'create';
        else
            set @VIEWSQL = 'alter';

        set @VIEWSQL = @VIEWSQL + ' view dbo.' + @SEGMENTVIEW + '' + char(13) +
            'as' + char(13) +
            (case when @BASETABLE is null
                then 'select top(0) cast(null as ' + @DATATYPE + ') as ID'
                else @SEGSQL
            end);

        exec (@VIEWSQL);

        -- Grant rights for new views

        if @OBJID is null
            exec ('grant select on dbo.' + @SEGMENTVIEW + ' to BBAPPFXSERVICEROLE');

        -- Add to the ID Set Register

        -- Get the view row count

        declare @COUNTSQL nvarchar(max);
        declare @PARAMETERS nvarchar(max);
        declare @NUMROWS int;
        set @COUNTSQL = 'select @NUMROWS = count(*) from dbo.' + @SEGMENTVIEW + '';
        set @PARAMETERS = N'@NUMROWS int OUTPUT' 
        exec sp_executeSQL @COUNTSQL, @PARAMETERS, @NUMROWS OUTPUT;

        -- Get the ID set ID if this is an edit operation, else it will be null.  Also get the segment record type.

        select distinct
            @IDSETREGISTERID = MKTSEGMENT.IDSETREGISTERID,
            @RECORDTYPEID = isnull(IDSETREGISTER.RECORDTYPEID, @RECORDTYPEID)
        from 
            dbo.MKTSEGMENT
        left join
            dbo.MKTSEGMENTSELECTION on MKTSEGMENTSELECTION.SEGMENTID = MKTSEGMENT.ID
        left join
            dbo.IDSETREGISTER on MKTSEGMENTSELECTION.SELECTIONID = IDSETREGISTER.ID
        where
            MKTSEGMENT.ID = @ID;

        -- Create the ID set...

        exec dbo.USP_IDSETREGISTER_CREATEORUPDATE 
            @IDSETREGISTERID output,
            @SEGMENTNAME,
            @SEGMENTDESCRIPTION,
            @SEGMENTVIEW,
            0,
            @RECORDTYPEID,
            0,
            1,
            @NUMROWS,
            @CHANGEAGENTID;

        -- Mark as inactive so it is hidden from searches

        -- Make sure the RECORDTYPEID gets set.  The SP doesn't update it on an UPDATE.

        update dbo.IDSETREGISTER 
        set
            RECORDTYPEID = @RECORDTYPEID,
            ACTIVE = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @IDSETREGISTERID;

        -- Save the ID set ID on the segment table...

        update dbo.MKTSEGMENT
        set
            IDSETREGISTERID = @IDSETREGISTERID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE 
        where
            ID = @ID;
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        set @RETVAL = 1;
    end catch

    return isnull(@RETVAL, 0);
end