USP_COMMUNICATIONLETTERACTIVITYEXCLUSIONS_CREATEORUPDATEIDSET

create procedure dbo.USP_COMMUNICATIONLETTERACTIVITYEXCLUSIONS_CREATEORUPDATEIDSET
(
    @IDSETREGISTERID uniqueidentifier output,
    @CHANGEAGENTID uniqueidentifier = null,
    @EXCLUDEBASEDONRECENTCOMMUNICATION bit,
    @NUMRECENTCOMMUNICATIONPERIODS int,    
    @RECENTCOMMUNICATIONPERIODTYPECODE tinyint,
    @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR bit,
    @NUMTOTALCOMMUNICATIONSINPASTYEAR int,
    @EXCLUDEBASEDONRECENTGIVING bit,
    @NUMRECENTGIVINGPERIODS int,    
    @RECENTGIVINGPERIODTYPECODE tinyint,
    @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR bit,
    @TOTALREVENUEAMOUNTINPASTYEAR money,
    @COMMUNICATIONTYPES xml
)
with execute as owner
as
begin    
    declare @VIEWNAME nvarchar(62);
    declare @VIEWSQL nvarchar(max) = '';
    declare @SELECTIONNAME nvarchar(300);
    declare @CONSTITUENTRECORDTYPEID uniqueidentifier;

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

    select 
        @CONSTITUENTRECORDTYPEID = [ID] 
    from dbo.[RECORDTYPE] 
    where upper([NAME]) = 'CONSTITUENT';

    if @EXCLUDEBASEDONRECENTCOMMUNICATION = 1
        set @VIEWSQL = @VIEWSQL + 'select CONSTITUENTID as ID from dbo.UFN_COMMUNICATIONS_GETCONSTITUENTSWITHRECENTCOMMUNICATIONS(' + cast(@RECENTCOMMUNICATIONPERIODTYPECODE as nvarchar(1)) + ', ' + cast(@NUMRECENTCOMMUNICATIONPERIODS as nvarchar(100)) + ', dbo.UFN_DATE_GETLATESTTIME(getDate()), ''' + cast(@COMMUNICATIONTYPES as nvarchar(max)) + ''')' + char(13);

    if @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR = 1
        begin
            if len(@VIEWSQL) > 0 
                set @VIEWSQL = @VIEWSQL + 'union ' + char(13);
            set @VIEWSQL = @VIEWSQL + 'select CONSTITUENTID as ID from dbo.UFN_COMMUNICATIONS_GETCONSTITUENTTOTALCOMMUNICATIONS(3, 1, dbo.UFN_DATE_GETLATESTTIME(getDate()), ''' + cast(@COMMUNICATIONTYPES as nvarchar(max)) + ''') where TOTALCOMMUNICATIONS >= ' + cast(@NUMTOTALCOMMUNICATIONSINPASTYEAR as nvarchar(100)) + char(13)
        end

    if @EXCLUDEBASEDONRECENTGIVING = 1
        begin
            if len(@VIEWSQL) > 0 
                set @VIEWSQL = @VIEWSQL + 'union ' + char(13);
            set @VIEWSQL = @VIEWSQL + 'select CONSTITUENTID as ID from dbo.UFN_COMMUNICATIONS_GETCONSTITUENTSWITHRECENTREVENUE(' + cast(@RECENTGIVINGPERIODTYPECODE as nvarchar(1)) + ', ' + cast(@NUMRECENTGIVINGPERIODS as nvarchar(100)) + ', dbo.UFN_DATE_GETLATESTTIME(getDate()))' + char(13);
        end

    if @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR = 1
        begin
            if len(@VIEWSQL) > 0 
                set @VIEWSQL = @VIEWSQL + 'union ' + char(13);
            set @VIEWSQL = @VIEWSQL + 'select CONSTITUENTID as ID from dbo.UFN_COMMUNICATIONS_GETCONSTITUENTTOTALREVENUE(3, 1, dbo.UFN_DATE_GETLATESTTIME(getDate())) where TOTALREVENUEAMOUNT > = ' + cast(@TOTALREVENUEAMOUNTINPASTYEAR as nvarchar(100)) + char(13);
        end        

    declare @VALIDVIEW bit = 0;
    if len(@VIEWSQL) > 0
        set @VALIDVIEW = 1;

    if @VALIDVIEW = 1
        begin
            if @IDSETREGISTERID is null
                set @IDSETREGISTERID = newid();

            set @VIEWNAME = dbo.UFN_COMMUNICATIONLETTERACTIVITYEXCLUSIONS_MAKEVIEWNAME(@IDSETREGISTERID);

            set @VIEWSQL = ' view dbo.' + @VIEWNAME + '' + char(13) +
                    'as' + char(13) + @VIEWSQL;

            -- Create or alter the view

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

            exec sp_sqlexec @VIEWSQL;
/*            
            exec sp_executesql @VIEWSQL, 
                    N'@COMMUNICATIONTYPES xml,
                    @RECENTCOMMUNICATIONPERIODTYPECODE tinyint, @NUMRECENTCOMMUNICATIONPERIODS integer, @NUMTOTALCOMMUNICATIONSINPASTYEAR integer,
                    @RECENTGIVINGPERIODTYPECODE tinyint, @NUMRECENTGIVINGPERIODS integer, @TOTALREVENUEAMOUNTINPASTYEAR money',
                    @COMMUNICATIONTYPES = @COMMUNICATIONTYPES,  
                    @RECENTCOMMUNICATIONPERIODTYPECODE = @RECENTCOMMUNICATIONPERIODTYPECODE, 
                    @NUMRECENTCOMMUNICATIONPERIODS = @NUMRECENTCOMMUNICATIONPERIODS,
                    @NUMTOTALCOMMUNICATIONSINPASTYEAR = @NUMTOTALCOMMUNICATIONSINPASTYEAR,
                    @RECENTGIVINGPERIODTYPECODE = @RECENTGIVINGPERIODTYPECODE,
                    @NUMRECENTGIVINGPERIODS = @NUMRECENTGIVINGPERIODS,
                    @TOTALREVENUEAMOUNTINPASTYEAR = @TOTALREVENUEAMOUNTINPASTYEAR;
*/

            -- Grant rights for new views

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

            -- Add to the ID Set Register

            -- Get the view row count

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

            -- Create the ID set...

            exec dbo.USP_IDSETREGISTER_CREATEORUPDATE 
                @IDSETREGISTERID,
                @VIEWNAME,
                @VIEWNAME,
                @VIEWNAME,
                0,
                @CONSTITUENTRECORDTYPEID,
                0,
                0,
                @NUMROWS,
                @CHANGEAGENTID;

            -- Mark IDSET as inactive to filter from searches

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

            update dbo.IDSETREGISTER set
                RECORDTYPEID = @CONSTITUENTRECORDTYPEID,
                ACTIVE = 0,
                ISSYSTEM = 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = getDate()
            where ID = @IDSETREGISTERID;
        end    

    else if @IDSETREGISTERID is not null
        begin
            -- Remove all references to the idset 

            update dbo.COMMUNICATIONLETTERACTIVITYEXCLUSIONS set
                IDSETREGISTERID = null
            where IDSETREGISTERID = @IDSETREGISTERID;

            declare @ID uniqueidentifier;

            select
                @ID = ID
            from dbo.MKTSEGMENTATIONFILTERSELECTION
            where SELECTIONID = @IDSETREGISTERID;

            exec dbo.[USP_MKTSEGMENTATIONFILTERSELECTION_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;

            -- Delete the IDSet

            exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @IDSETREGISTERID, @CHANGEAGENTID;

            set @IDSETREGISTERID = null;
        end

end