USP_DATALIST_CONSTITUENT_INTERACTIONS

                CREATE procedure dbo.USP_DATALIST_CONSTITUENT_INTERACTIONS(
                    @CONSTITUENTID uniqueidentifier,
                    @DATEFILTER tinyint = 0,
                    @STARTDATE datetime = null,
                    @ENDDATE datetime = null,
                    @TYPEFILTER uniqueidentifier = null
                )
                as begin
                    set nocount on;

                    declare @DATE datetime;
                    set @DATE = getdate();

                    if @DATEFILTER = 0        -- all

                        begin
                            set @STARTDATE = null;
                            set @ENDDATE = null;
                        end

                    if @DATEFILTER = 1        -- this year

                        begin
                            set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@DATE, 0);
                            set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATE, 0);
                        end

                    if @DATEFILTER = 2        -- this quarter

                        begin
                            set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@DATE, 0);
                            set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0);
                        end

                    if @DATEFILTER = 3        -- this month

                    begin
                        set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0);
                        set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0);
                    end

                    if @DATEFILTER = 4        -- this week

                    begin
                        set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0);
                        set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0);
                    end

                    if @DATEFILTER = 5        -- next week

                    begin
                        set @STARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@DATE, 0);
                        set @ENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@DATE, 0);
                    end

                    if @DATEFILTER = 6        -- next month

                    begin
                        set @STARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@DATE, 0);
                        set @ENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@DATE, 0);
                    end

                    if @DATEFILTER = 7        -- next quarter

                    begin
                        set @STARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@DATE, 0);
                        set @ENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@DATE, 0);
                    end

                    if @DATEFILTER = 8        -- next year

                    begin
                        set @STARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@DATE, 0);
                        set @ENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@DATE, 0);
                    end;

          with INTERACTION_CTE as
          (
            select 
              I.ID,
              I.DATE,
              case I.COMPLETED when 1 then 'Completed' else I.STATUS end STATUS,
              I.OBJECTIVE,
                          dbo.UFN_INTERACTIONTYPECODE_GETDESCRIPTION(I.INTERACTIONTYPECODEID) TYPE,
              case when I.PROSPECTPLANID is null then 0 else 1 end ISSTEP,
              I.FUNDRAISERID,
              I.PROSPECTPLANID,
              I.ISINTERACTION,
              I.STATUSCODE,
              I.INTERACTIONTYPECODEID,
              I.EVENTID,
              I.INTERACTIONSUBCATEGORYID
            from
              dbo.INTERACTION I
            where
              I.CONSTITUENTID = @CONSTITUENTID

            union

            select
              I.ID,
              I.DATE,
              case I.COMPLETED when 1 then 'Completed' else I.STATUS end,
              I.OBJECTIVE,
                          dbo.UFN_INTERACTIONTYPECODE_GETDESCRIPTION(I.INTERACTIONTYPECODEID),
              case when I.PROSPECTPLANID is null then 0 else 1 end,
              I.FUNDRAISERID,
              I.PROSPECTPLANID,
              I.ISINTERACTION,
              I.STATUSCODE,
              I.INTERACTIONTYPECODEID,
              I.EVENTID,
              I.INTERACTIONSUBCATEGORYID
            from
              dbo.INTERACTION I
            inner join
              dbo.INTERACTIONPARTICIPANT IP on IP.INTERACTIONID = I.ID
            where
              IP.CONSTITUENTID = @CONSTITUENTID
          )
          select distinct
                        I.ID,
                        I.DATE,
                        I.STATUS,
                        FC.NAME,
                        FC.ID,
                        I.OBJECTIVE,
                        I.TYPE,
                        case when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID=I.ID)
                               or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID=I.ID)
                               or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID=I.ID)
                        then convert(bit,1) else convert(bit,0) end,
                        I.ISSTEP,
                        dbo.UFN_EVENT_GETNAME(I.EVENTID),
                        case when exists(select 1 from dbo.INTERACTIONRESPONSE where INTERACTIONRESPONSE.INTERACTIONID=I.ID)
                        then convert(bit,1) else convert(bit,0) end,
                        IC.NAME,
                        ISC.NAME,
                        case when I.ISSTEP = 1 then '6ce4b0e8-cfcf-4f57-94c3-00eea94fb499'
                             else 'b525985b-be02-4f02-a9b8-c110411cf936'
                        end VIEWFORMID
                    from
                        INTERACTION_CTE I
                    left outer join 
                        dbo.CONSTITUENT FC on FC.ID=I.FUNDRAISERID
                    left outer join 
                        dbo.INTERACTIONSUBCATEGORY ISC on ISC.ID = I.INTERACTIONSUBCATEGORYID
                    left outer join 
                        dbo.INTERACTIONCATEGORY IC on IC.ID = ISC.INTERACTIONCATEGORYID
                    where
                        I.ISINTERACTION = 1
                              and 
                        I.STATUSCODE != 0
                                and 
                      (
                        (I.DATE between @STARTDATE and @ENDDATE)
                                  or
                                    (@STARTDATE is null and @ENDDATE is null)
                                    or
                                    (I.DATE >= @STARTDATE and @ENDDATE is null)
                                    or
                                    (@STARTDATE is null and I.DATE <= @ENDDATE)
                                )
                                and (
                                  (I.INTERACTIONTYPECODEID = @TYPEFILTER)
                                    or
                                    (@TYPEFILTER is null)
                                )
                                order by
                                    I.DATE;
                            end