USP_APPEALMAILINGSETUPLETTER_REMOVE

CREATE procedure dbo.USP_APPEALMAILINGSETUPLETTER_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 = APPEALMAILINGSETUPLETTER.APPEALMAILINGSETUPID,
        @APPEALID = APPEALMAILING.APPEALID,
        @ACTIVE = MKTSEGMENTATION.ACTIVE,
        @EMAILSEGMENTID = APPEALMAILINGSETUPLETTER.EMAILSEGMENTID,
        @MAILSEGMENTID = APPEALMAILINGSETUPLETTER.MAILSEGMENTID,
        @EMAILPACKAGEID = APPEALMAILINGSETUPLETTER.EMAILPACKAGEID,
        @MAILPACKAGEID = APPEALMAILINGSETUPLETTER.MAILPACKAGEID
    from dbo.APPEALMAILINGSETUPLETTER
    left join dbo.[APPEALMAILING] 
        on APPEALMAILINGSETUPLETTER.APPEALMAILINGSETUPID = APPEALMAILING.ID
    left join dbo.MKTSEGMENTATION
        on APPEALMAILINGSETUPLETTER.APPEALMAILINGSETUPID = MKTSEGMENTATION.ID
    where APPEALMAILINGSETUPLETTER.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.APPEALMAILINGSETUPLETTER set
        APPEALMAILINGSETUPID = null
    where ID = @ID;

    return 0;

end