USP_COMMUNICATIONLETTER_REMOVE

CREATE procedure dbo.USP_COMMUNICATIONLETTER_REMOVE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as begin    

    declare @SEGMENTATIONID uniqueidentifier;
    declare @APPEALID uniqueidentifier;
    declare @ACTIVE bit;
    declare @EMAILSEGMENTID uniqueidentifier;
    declare @MAILSEGMENTID uniqueidentifier;
    declare @MKTSEGMENTATIONSEGMENTID uniqueidentifier;
    declare @EMAILPACKAGEID uniqueidentifier;
    declare @MAILPACKAGEID uniqueidentifier;
    declare @MKTSEGMENTATIONPACKAGEID uniqueidentifier;

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

    select
        @SEGMENTATIONID = COMMUNICATIONLETTER.SEGMENTATIONID,
        @APPEALID = APPEALMAILING.APPEALID,
        @ACTIVE = MKTSEGMENTATION.ACTIVE,
        @EMAILSEGMENTID = COMMUNICATIONLETTER.EMAILSEGMENTID,
        @MAILSEGMENTID = COMMUNICATIONLETTER.MAILSEGMENTID,
        @EMAILPACKAGEID = COMMUNICATIONLETTER.EMAILPACKAGEID,
        @MAILPACKAGEID = COMMUNICATIONLETTER.MAILPACKAGEID
    from dbo.COMMUNICATIONLETTER
    left join dbo.[APPEALMAILING] 
        on COMMUNICATIONLETTER.SEGMENTATIONID = APPEALMAILING.ID
    left join dbo.MKTSEGMENTATION
        on COMMUNICATIONLETTER.SEGMENTATIONID = MKTSEGMENTATION.ID
    where COMMUNICATIONLETTER.ID = @ID;

    -- Rollback active mailings to be re-activated

    if @ACTIVE = 1 or dbo.UFN_MKTSEGMENTATION_HASACTIVESEGMENTS(@SEGMENTATIONID) = 1
        begin
            declare @DELETEAPPEALINFO bit = case when @APPEALID is null then 0 else 1 end;

            exec dbo.USP_MKTSEGMENTATIONACTIVATE_ROLLBACK @SEGMENTATIONID, @CHANGEAGENTID, @DELETEAPPEALINFO, 1;
        end

    if @EMAILSEGMENTID is not null
        begin
            declare EMAILSEGMENTATIONSEGMENTCURSOR cursor local fast_forward for
                select 
                    ID 
                from dbo.MKTSEGMENTATIONSEGMENT
                where SEGMENTATIONID = @SEGMENTATIONID
                    and SEGMENTID = @EMAILSEGMENTID;

            open EMAILSEGMENTATIONSEGMENTCURSOR;
            fetch next from EMAILSEGMENTATIONSEGMENTCURSOR into @MKTSEGMENTATIONSEGMENTID;

             while (@@FETCH_STATUS = 0)
                begin                
                    -- Drop the Segmentation segment record linking the mailing to the segment

                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_DELETEBYID_WITHCHANGEAGENTID @MKTSEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                    fetch next from EMAILSEGMENTATIONSEGMENTCURSOR into @MKTSEGMENTATIONSEGMENTID;
                end

            close EMAILSEGMENTATIONSEGMENTCURSOR;
            deallocate EMAILSEGMENTATIONSEGMENTCURSOR;                
        end

    if @EMAILPACKAGEID is not null
        begin
            declare EMAILSEGMENTATIONPACKAGECURSOR cursor local fast_forward for
                select 
                    ID 
                from dbo.MKTSEGMENTATIONPACKAGE
                where SEGMENTATIONID = @SEGMENTATIONID
                    and PACKAGEID = @EMAILPACKAGEID;

            open EMAILSEGMENTATIONPACKAGECURSOR;
            fetch next from EMAILSEGMENTATIONPACKAGECURSOR into @MKTSEGMENTATIONPACKAGEID;

             while (@@FETCH_STATUS = 0)
                begin                
                    -- Drop the Segmentation segment record linking the mailing to the segment

                    exec dbo.USP_MKTSEGMENTATIONPACKAGE_DELETEBYID_WITHCHANGEAGENTID @MKTSEGMENTATIONPACKAGEID, @CHANGEAGENTID;

                    fetch next from EMAILSEGMENTATIONPACKAGECURSOR into @MKTSEGMENTATIONPACKAGEID;
                end

            close EMAILSEGMENTATIONPACKAGECURSOR;
            deallocate EMAILSEGMENTATIONPACKAGECURSOR;                
        end

    if @MAILSEGMENTID is not null
        begin
            declare MAILSEGMENTATIONSEGMENTCURSOR cursor local fast_forward for
                select 
                    ID 
                from dbo.MKTSEGMENTATIONSEGMENT
                where SEGMENTATIONID = @SEGMENTATIONID
                    and SEGMENTID = @MAILSEGMENTID;

            open MAILSEGMENTATIONSEGMENTCURSOR;
            fetch next from MAILSEGMENTATIONSEGMENTCURSOR into @MKTSEGMENTATIONSEGMENTID;

             while (@@FETCH_STATUS = 0)
                begin                
                    -- Drop the Segmentation package record linking the mailing to the segment

                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_DELETEBYID_WITHCHANGEAGENTID @MKTSEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                    fetch next from MAILSEGMENTATIONSEGMENTCURSOR into @MKTSEGMENTATIONSEGMENTID;
                end

            close MAILSEGMENTATIONSEGMENTCURSOR;
            deallocate MAILSEGMENTATIONSEGMENTCURSOR;                
        end

    if @MAILPACKAGEID is not null
        begin
            declare MAILSEGMENTATIONPACKAGECURSOR cursor local fast_forward for
                select 
                    ID 
                from dbo.MKTSEGMENTATIONPACKAGE
                where SEGMENTATIONID = @SEGMENTATIONID
                    and PACKAGEID = @MAILPACKAGEID;

            open MAILSEGMENTATIONPACKAGECURSOR;
            fetch next from MAILSEGMENTATIONPACKAGECURSOR into @MKTSEGMENTATIONPACKAGEID;

             while (@@FETCH_STATUS = 0)
                begin                
                    -- Drop the Segmentation package record linking the mailing to the segment

                    exec dbo.USP_MKTSEGMENTATIONPACKAGE_DELETEBYID_WITHCHANGEAGENTID @MKTSEGMENTATIONPACKAGEID, @CHANGEAGENTID;

                    fetch next from MAILSEGMENTATIONPACKAGECURSOR into @MKTSEGMENTATIONPACKAGEID;
                end

            close MAILSEGMENTATIONPACKAGECURSOR;
            deallocate MAILSEGMENTATIONPACKAGECURSOR;                
        end

    update dbo.COMMUNICATIONLETTER set
        SEGMENTATIONID = null
    where ID = @ID;

    -- Reorder the remaining letters

    declare @SEQUENCE int;
    select @SEQUENCE = SEQUENCE from dbo.COMMUNICATIONLETTER where ID = @ID;

    update dbo.COMMUNICATIONLETTER
    set
        SEQUENCE = (SEQUENCE - 1)
    where
        ID in (select ID from dbo.COMMUNICATIONLETTER where SEGMENTATIONID = @SEGMENTATIONID and SEQUENCE > @SEQUENCE)
    return 0;

end