USP_EXCHANGECALENDARSYNC_GETINTERACTIONLIST_2

CREATE procedure [dbo].[USP_EXCHANGECALENDARSYNC_GETINTERACTIONLIST_2]
(
    @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 @INCLUDEPENDINGINTERACTIONS bit
  declare @INCLUDEPROSPECTPLANSTEPS bit
  declare @INCLUDEPENDINGSTEWARDSHIPSTEPS bit        
  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,
      @INCLUDEPENDINGINTERACTIONS = EXCHANGECALENDARSYNC.INCLUDEPENDINGINTERACTIONS,
  @INCLUDEPROSPECTPLANSTEPS = EXCHANGECALENDARSYNC.INCLUDEPROSPECTPLANSTEPS,
  @INCLUDEPENDINGSTEWARDSHIPSTEPS = EXCHANGECALENDARSYNC.INCLUDEPENDINGSTEWARDSHIPSTEPS
  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,
    case 
      when INTERACTION.PROSPECTPLANID is null then 0
      else 1 
    end as RECORDTYPE,
    null as CONTACTNAME,
    INTERACTION.EXPECTEDSTARTDATETIME,
    INTERACTION.EXPECTEDENDDATETIME,
    INTERACTION.ISALLDAYEVENT,
    (select name from TIMEZONEENTRY where TIMEZONEENTRY.ID = INTERACTION.TIMEZONEENTRYID) as TIMEZONE
  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.STATUSCODE in (0,1) and
    (INTERACTION.DATECHANGED > @LASTRUNON or @LASTRUNON is null) and
    (
      (@INCLUDEPENDINGINTERACTIONS = 1 and INTERACTION.STATUSCODE = @PENDINGSTATUS) 
      or
      (@INCLUDEPROSPECTPLANSTEPS = 1 and PROSPECTPLANID is not NULL)
    ) 
    and datediff(day, @CURRENTDATE, INTERACTION.EXPECTEDDATE) > = 0 -- Only get future interactions        

    and 
    (
      (@EXPECTEDDATE is null) or (INTERACTION.EXPECTEDDATE <= @EXPECTEDDATE)
    )

  union all

  select 
    STEP.ID, 
    CONSTITUENT.NAME as CONSTITUENTNAME, 
    STEP.OBJECTIVE as SUBJECT, 
    STEWARDSHIPPLAN.CONSTITUENTID, 
    APPUSERLIST.APPUSERID, 
    APPUSERLIST.USERSID, 
    STEP.TARGETDATE as 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,
    2 as RECORDTYPE,
    dbo.UFN_CONSTITUENT_BUILDNAME(STEP.CONTACTPERSONID) as CONTACTNAME,
    STEP.TARGETSTARTDATETIME,
    STEP.TARGETENDDATETIME,
    STEP.ISALLDAYEVENT,
    (select name from TIMEZONEENTRY where TIMEZONEENTRY.ID = STEP.TIMEZONEENTRYID) as TIMEZONE
  from @APPUSERLIST AS APPUSERLIST
  inner join dbo.STEWARDSHIPPLANSTEP STEP 
    on STEP.CONSTITUENTID = APPUSERLIST.CONSTITUENTID
  inner join dbo.STEWARDSHIPPLAN
    on STEWARDSHIPPLAN.ID = STEP.PLANID
  inner join dbo.CONSTITUENT
    on CONSTITUENT.ID = STEWARDSHIPPLAN.CONSTITUENTID
  left join dbo.EXCHANGEUSER 
    on APPUSERLIST.APPUSERID = EXCHANGEUSER.APPUSERID
  left join dbo.EXCHANGESERVERAPPUSER 
    on APPUSERLIST.APPUSERID = EXCHANGESERVERAPPUSER.APPUSERID
  where
    (STEP.DATECHANGED > @LASTRUNON or @LASTRUNON is null)
    and @INCLUDEPENDINGSTEWARDSHIPSTEPS = 1
    and STEP.STATUSCODE = 0            -- Pending Steps     

    and datediff(day, @CURRENTDATE, STEP.TARGETDATE) > = 0 -- Only get future steps            

    and (@EXPECTEDDATE is null or STEP.TARGETDATE <= @EXPECTEDDATE)