UFN_SMARTQUERY_REVENUE_LYBUNT_DONORS

create function dbo.UFN_SMARTQUERY_REVENUE_LYBUNT_DONORS (
  @THISYEAR_DATERANGE tinyint = 0 --0=This calendar year , 1=NMonths, 2=Specific range

  ,@THISYEAR_NMONTHS tinyint = 12
  ,@THISYEAR_STARTDATE datetime = null
  ,@THISYEAR_ENDDATE datetime = null
  ,@SELECTIONID uniqueidentifier = null
  ,@LASTYEAR_DATERANGE tinyint = 0 --0=Last calendar year , 1=NMonths prior to this year, 2=Specific range

  ,@LASTYEAR_NMONTHS tinyint = 12
  ,@LASTYEAR_STARTDATE datetime = null
  ,@LASTYEAR_ENDDATE datetime = null
  ,@CURRENTAPPUSERID uniqueidentifier = null
  ,@MAXROWS int = 500
  )
returns @T table (
  ID uniqueidentifier not null
  ,LASTGIFTDATE datetime
  ,LASTYEARGIFTDATE datetime
  ,[NAME] nvarchar(154)
  ,ADDRESSBLOCK nvarchar(150)
  ,CITY nvarchar(50)
  ,[STATE] nvarchar(50)
  ,POSTCODE nvarchar(12)
  ,LOOKUPID nvarchar(36)
  )
as
begin
  declare @ISADMIN bit;

  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  declare @ids table (ID uniqueidentifier primary key);

  if @SELECTIONID is not null
  begin
    insert into @ids
    select ID
    from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID);
  end

  if (@THISYEAR_DATERANGE is null)
    or (
      @THISYEAR_DATERANGE not in (
        0
        ,1
        ,2
        )
      )
    set @THISYEAR_DATERANGE = 0;

  declare @NOW datetime;

  set @NOW = GETDATE();

  --Calculate this year date range

  if @THISYEAR_DATERANGE = 0
  begin
    set @THISYEAR_STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@NOW, 0);
    set @THISYEAR_ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@NOW, 1);
  end
  else
    if @THISYEAR_DATERANGE = 1
    begin
      set @THISYEAR_ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@NOW);
      set @THISYEAR_STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(DATEADD(month, - @THISYEAR_NMONTHS, @NOW));
    end
    else
      if @THISYEAR_DATERANGE = 2
      begin
        if @THISYEAR_STARTDATE is null
          and @THISYEAR_ENDDATE is null
          return;

        if @THISYEAR_STARTDATE is null
          set @THISYEAR_STARTDATE = '1900-01-01';

        if @THISYEAR_ENDDATE is null
          set @THISYEAR_ENDDATE = '2900-01-01';
        set @THISYEAR_STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@THISYEAR_STARTDATE);
        set @THISYEAR_ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@THISYEAR_ENDDATE);
      end

  --Calculate last year date range

  if @LASTYEAR_DATERANGE = 0
  begin
    set @LASTYEAR_STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@NOW, 0);
    set @LASTYEAR_ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@NOW, 1);
  end
  else
    if @LASTYEAR_DATERANGE = 1
    begin
      set @LASTYEAR_ENDDATE = dbo.UFN_DATE_GETLATESTTIME(DATEADD(day, - 1, @THISYEAR_STARTDATE));
      set @LASTYEAR_STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(DATEADD(month, - @LASTYEAR_NMONTHS, @LASTYEAR_ENDDATE));
    end
    else
      if @LASTYEAR_DATERANGE = 2
      begin
        if @LASTYEAR_STARTDATE is null
          and @LASTYEAR_ENDDATE is null
          return;

        if @LASTYEAR_STARTDATE is null
          set @LASTYEAR_STARTDATE = '1900-01-01';

        if @LASTYEAR_ENDDATE is null
          set @LASTYEAR_ENDDATE = '2900-01-01';
        set @LASTYEAR_STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@LASTYEAR_STARTDATE);
        set @LASTYEAR_ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@LASTYEAR_ENDDATE);
      end;

  with REVENUE_CTE
  as (
    select FTRANS.ID
      ,cast(FTRANS.[DATE] as datetime) as [DATE]
      ,FTRANS.CONSTITUENTID
    from dbo.FINANCIALTRANSACTION FTRANS with (
        nolock
        ,index (IX_FINANCIALTRANSACTION_CONSTITUENTID_TYPECODE_DATE)
        )
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTRANS.ID = FTLI.FINANCIALTRANSACTIONID
    inner join dbo.REVENUESPLIT_EXT RS on FTLI.ID = RS.ID
    where RS.TYPECODE in (
        0
        ,1
        ,2
        ,3
        ,4
        ,8
        ,9
        ,12
        ,15
      ,17
        )
      and FTRANS.DELETEDON is null
      and FTLI.DELETEDON is null
      and cast(FTRANS.[DATE] as datetime) >= @LASTYEAR_STARTDATE --this smart query never cares about transaction before the start of last year, so eliminate them

      and (
        @SELECTIONID is null
        or FTRANS.CONSTITUENTID in (
          select ID
          from @ids
          )
        )
      and (
        @ISADMIN = 1
        or dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '0DAC425D-A4C8-4dcd-891F-F3F3F6C07A75', FTRANS.ID) = 1
        )
    )
  insert into @T
  select top (@MAXROWS) C.ID
    ,(
      select MAX([DATE]) as LGD
      from REVENUE_CTE as R
      where R.CONSTITUENTID = C.ID
      ) as LASTGIFTDATE
    ,(
      select MAX([DATE]) as TYGD
      from REVENUE_CTE as R
      where R.CONSTITUENTID = C.ID
        and R.[DATE] between @LASTYEAR_STARTDATE and @LASTYEAR_ENDDATE
      ) as LASTYEARGIFTDATE
    ,C.[NAME]
    ,A.ADDRESSBLOCK
    ,A.CITY
    ,[STATE].DESCRIPTION
    ,A.POSTCODE
    ,C.LOOKUPID
  from dbo.CONSTITUENT as C with (nolock)
  left join dbo.ADDRESS as A with (nolock) on A.CONSTITUENTID = C.ID
    and A.ISPRIMARY = 1
  left join dbo.[STATE] on [STATE].ID = A.STATEID
  where exists (
      --Donors with a gift last year

      select top 1 1
      from REVENUE_CTE as R
      where R.[DATE] between @LASTYEAR_STARTDATE and @LASTYEAR_ENDDATE
        and R.CONSTITUENTID = C.ID
      )
    and not exists (
      --Donors with a gift this year

      select top 1 1
      from REVENUE_CTE as R
      where R.[DATE] between @THISYEAR_STARTDATE and @THISYEAR_ENDDATE
        and R.CONSTITUENTID = C.ID
      )
    and (
      @ISADMIN = 1
      or (
        dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '0DAC425D-A4C8-4dcd-891F-F3F3F6C07A75', C.ID) = 1
        and dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '0DAC425D-A4C8-4dcd-891F-F3F3F6C07A75', C.ID) = 1
        )
      )
  order by LASTGIFTDATE desc;

  return;
end;