USP_EXCHANGECALENDARSYNC_GETINTERACTIONLIST

            CREATE procedure [dbo].[USP_EXCHANGECALENDARSYNC_GETINTERACTIONLIST]
            (
                @PARAMETERSETID uniqueidentifier
            )
            as
                set nocount on;

                declare @APPUSERIDSETREGISTERID uniqueidentifier
                declare @LASTRUNON datetime
                declare @SPECIFICAPPUSERID uniqueidentifier
                declare @APPLYTOCODE tinyint
                declare @CREATEITEMOPTIONCODE tinyint
                declare @EXPECTEDDATEINTERVALCODE tinyint
                declare @INTERACTIONFILTERCODE tinyint
                declare @CURRENTDATE datetime
                declare @EXPECTEDDATE datetime
                declare @APPUSERLIST table (CONSTITUENTID uniqueidentifier, APPUSERID uniqueidentifier, USERSID varbinary(85));

                declare @PENDINGSTATUS tinyint
                set @PENDINGSTATUS = 1;

                set @CURRENTDATE = getdate();

                /* Get the current settings for the business process.*/
                select
                    @APPUSERIDSETREGISTERID = EXCHANGECALENDARSYNC.APPUSERIDSETREGISTERID,
                    @LASTRUNON = EXCHANGECALENDARSYNC.LASTRUNON,
                    @SPECIFICAPPUSERID = EXCHANGECALENDARSYNC.SPECIFICAPPUSERID,
                    @APPLYTOCODE = EXCHANGECALENDARSYNC.APPLYTOCODE,
                    @CREATEITEMOPTIONCODE = EXCHANGECALENDARSYNC.CREATEITEMOPTIONCODE,
                    @EXPECTEDDATEINTERVALCODE = EXCHANGECALENDARSYNC.EXPECTEDDATEINTERVALCODE,
                    @INTERACTIONFILTERCODE = case 
                                                when EXCHANGECALENDARSYNC.INCLUDEPENDINGINTERACTIONS = 1 and  EXCHANGECALENDARSYNC.INCLUDEPROSPECTPLANSTEPS = 1 then 2
                                                when EXCHANGECALENDARSYNC.INCLUDEPENDINGINTERACTIONS = 0 and  EXCHANGECALENDARSYNC.INCLUDEPROSPECTPLANSTEPS = 1 then 1
                                                else 0 end --adding case for backwards compat

                from
                    dbo.EXCHANGECALENDARSYNC
                where
                    EXCHANGECALENDARSYNC.ID = @PARAMETERSETID

                --build the daterange to test

                select @EXPECTEDDATE = case @CREATEITEMOPTIONCODE
                    when 0 then 
                        case @EXPECTEDDATEINTERVALCODE
                            when 0 then DATEADD(mm, 1, @CURRENTDATE)
                            when 1 then DATEADD(mm, 2, @CURRENTDATE)
                            when 2 then DATEADD(mm, 3, @CURRENTDATE)
                            when 3 then DATEADD(mm, 4, @CURRENTDATE)
                            when 4 then DATEADD(mm, 5, @CURRENTDATE)
                            when 5 then DATEADD(mm, 6, @CURRENTDATE)
                        end
                    when 1 then NULL
                end

                if @APPLYTOCODE = 0 -- All users

                    begin
                        insert into @APPUSERLIST(CONSTITUENTID, APPUSERID, USERSID) 
                            select 
                                CONSTITUENT.ID, 
                                APPUSER.ID,
                                APPUSER.USERSID
                            from
                                dbo.CONSTITUENT
                                inner join dbo.APPUSER on CONSTITUENT.ID = APPUSER.CONSTITUENTID
                            where
                                CONSTITUENT.ISORGANIZATION = 0
                            order by 
                                CONSTITUENT.ID;                                    
                    end
                else if @APPLYTOCODE = 1 --Selected users

                    begin
                        declare @APPUSERS TABLE (ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);
                        INSERT INTO @APPUSERS (ID) select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@APPUSERIDSETREGISTERID);

                        insert into @APPUSERLIST(CONSTITUENTID, APPUSERID, USERSID) 
                            select 
                                CONSTITUENT.ID, 
                                APPUSER.ID,
                                APPUSER.USERSID 
                            from
                                dbo.CONSTITUENT
                                inner join dbo.APPUSER on APPUSER.CONSTITUENTID = CONSTITUENT.ID
                                inner join @APPUSERS as APPUSERSELECTION on APPUSERSELECTION.ID = APPUSER.ID
                            where
                                CONSTITUENT.ISORGANIZATION = 0
                            order by 
                                CONSTITUENT.ID;
                    end
                else if @APPLYTOCODE = 2 -- Specific user

                    begin
                        insert into @APPUSERLIST(CONSTITUENTID, APPUSERID, USERSID) 
                            select 
                                CONSTITUENT.ID, 
                                @SPECIFICAPPUSERID,
                                (select APPUSER.USERSID from dbo.APPUSER where APPUSER.ID = @SPECIFICAPPUSERID)
                            from
                                dbo.CONSTITUENT
                                inner join dbo.APPUSER on CONSTITUENT.ID = APPUSER.CONSTITUENTID 
                            where
                                APPUSER.ID = @SPECIFICAPPUSERID and CONSTITUENT.ISORGANIZATION = 0 
                            order by 
                                CONSTITUENT.ID;
                    end                            

                /* Return interactions owned by our appusers that satisfy the criteria and have not been added to Exchange or where the information has been updated since the last run */
                select 
                    INTERACTION.ID, 
                    dbo.UFN_CONSTITUENT_BUILDNAME(INTERACTION.CONSTITUENTID) as CONSTITUENTNAME, 
                    INTERACTION.OBJECTIVE as SUBJECT, 
                    INTERACTION.CONSTITUENTID, 
                    APPUSERLIST.APPUSERID, 
                    APPUSERLIST.USERSID, 
                    INTERACTION.EXPECTEDDATE,                                   
                    coalesce(EXCHANGEUSER.WATERMARK,'') as WATERMARK,
                    EXCHANGESERVERAPPUSER.EMAILADDRESS,
                    coalesce(EXCHANGESERVERAPPUSER.IDENTIFIERTYPECODE,0) as IDENTIFIERTYPECODE,
                    coalesce(EXCHANGESERVERAPPUSER.EXCHANGESERVERID, (select EXCHANGESERVER.ID from dbo.EXCHANGESERVER where ISDEFAULTSERVER = 1)) as EXCHANGESERVERID    
                from
                    @APPUSERLIST AS APPUSERLIST
                    inner join dbo.INTERACTION on INTERACTION.FUNDRAISERID = APPUSERLIST.CONSTITUENTID
                    left join dbo.EXCHANGEUSER on APPUSERLIST.APPUSERID = EXCHANGEUSER.APPUSERID
                    left join dbo.EXCHANGESERVERAPPUSER on APPUSERLIST.APPUSERID = EXCHANGESERVERAPPUSER.APPUSERID
                where
                    (INTERACTION.DATECHANGED > @LASTRUNON or @LASTRUNON is null) and
                    (
                        (
                            (@INTERACTIONFILTERCODE in (0,2)) and INTERACTION.STATUSCODE = @PENDINGSTATUS
                        ) 
                        or
                        (
                            (@INTERACTIONFILTERCODE in (1,2)) and (PROSPECTPLANID is not NULL)
                        )
                    ) 
                    and 
                    (
                        (@EXPECTEDDATE is null) or (INTERACTION.EXPECTEDDATE <= @EXPECTEDDATE)
                    );