UFN_SMARTQUERY_REVENUEDYNAMICS

create function dbo.UFN_SMARTQUERY_REVENUEDYNAMICS
(
  @INCLUDENEW bit = 0,
  @INCLUDERECAPTURE bit = 0,
  @INCLUDEUPGRADE bit = 0,
  @INCLUDESAME bit = 0,
  @INCLUDEDOWNGRADE bit = 0,
  @INCLUDELAPSEDNEW bit = 0,
  @INCLUDELAPSEDREPEAT bit = 0,
  @PREVIOUSFROMDATE datetime,
  @PREVIOUSTODATE datetime,
  @THISFROMDATE datetime,
  @THISTODATE datetime,
  @DESIGNATIONS xml,
  @CAMPAIGNS xml,
  @TYPECODES xml,
  @PREVIOUSDATERANGE tinyint,
  @THISDATERANGE tinyint,
  @EXCLUDEGIFTSOVER money,
  @CURRENCYCODE tinyint,
  @ENABLEEXCLUDEGIFTSOVER bit,
  @CURRENCYID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MAXROWS int = null
)
returns @T table 
(
  ID uniqueidentifier not null, 
  NAME nvarchar(154),
  PREVIOUSAMOUNT money,
  THISAMOUNT money,
  ALLOTHERAMOUNT money,
  BEHAVIORTYPECODE tinyint,
  CURRENCYID uniqueidentifier
)
as
begin
  declare @ISADMIN bit;
  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  set @CURRENCYID = case @CURRENCYCODE 
    when 0 then dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)
    else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
  end

  if @PREVIOUSDATERANGE <> 0
  begin
    if @PREVIOUSDATERANGE=6 begin
      -- Current month

      set @PREVIOUSFROMDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@PREVIOUSFROMDATE, 0);
      set @PREVIOUSTODATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@PREVIOUSTODATE, 1);
    end else if @PREVIOUSDATERANGE=12 begin
      -- Previous month

      set @PREVIOUSFROMDATE = dbo.UFN_DATE_LASTMONTH_FIRSTDAY(@PREVIOUSFROMDATE, 0);
      set @PREVIOUSTODATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@PREVIOUSTODATE, 1);
    end else if @PREVIOUSDATERANGE=39 begin
      -- Current fiscal year

      set @PREVIOUSFROMDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@PREVIOUSFROMDATE, 0);
      set @PREVIOUSTODATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@PREVIOUSTODATE, 1);
    end else if @PREVIOUSDATERANGE=38 begin
      -- Previous fiscal year

      set @PREVIOUSFROMDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@PREVIOUSFROMDATE), 0);
      set @PREVIOUSTODATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(dateadd(year,-1,@PREVIOUSTODATE), 1);
    end else if @PREVIOUSDATERANGE=7 begin
      -- This calendar year

      set @PREVIOUSFROMDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@PREVIOUSFROMDATE, 0);
      set @PREVIOUSTODATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@PREVIOUSTODATE, 1);
    end else if @PREVIOUSDATERANGE=13 begin
      -- Last calendar year

      set @PREVIOUSFROMDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@PREVIOUSFROMDATE, 0);
      set @PREVIOUSTODATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@PREVIOUSTODATE, 1);
    end else if @PREVIOUSDATERANGE=43 begin
      -- This quarter

      set @PREVIOUSFROMDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@PREVIOUSFROMDATE, 0);
      set @PREVIOUSTODATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@PREVIOUSTODATE, 1);
    end else if @PREVIOUSDATERANGE=45 begin
      -- Last quarter

      set @PREVIOUSFROMDATE = dbo.UFN_DATE_LASTQUARTER_FIRSTDAY(@PREVIOUSFROMDATE, 0);
      set @PREVIOUSTODATE = dbo.UFN_DATE_LASTQUARTER_LASTDAY(@PREVIOUSTODATE, 1);
        end else if @PREVIOUSDATERANGE=5 begin
      -- This week

            set @PREVIOUSFROMDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@PREVIOUSFROMDATE, 0);
        set @PREVIOUSTODATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@PREVIOUSTODATE, 1);
        end else if @PREVIOUSDATERANGE=11 begin
      -- Last week

      set @PREVIOUSFROMDATE = dbo.UFN_DATE_LASTWEEK_FIRSTDAY(@PREVIOUSFROMDATE, 0);
      set @PREVIOUSTODATE = dbo.UFN_DATE_LASTWEEK_LASTDAY(@PREVIOUSTODATE, 1);
    end
  end

  if @THISDATERANGE <> 0
  begin
    if @THISDATERANGE=6 begin
      -- Current month

      set @THISFROMDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@THISFROMDATE, 0);
      set @THISTODATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@THISTODATE, 1);
    end else if @THISDATERANGE=12 begin
      -- Previous month

      set @THISFROMDATE = dbo.UFN_DATE_LASTMONTH_FIRSTDAY(@THISFROMDATE, 0);
      set @THISTODATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@THISTODATE, 1);
    end else if @THISDATERANGE=39 begin
      -- Current fiscal year

      set @THISFROMDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@THISFROMDATE, 0);
      set @THISTODATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@THISTODATE, 1);
    end else if @THISDATERANGE=38 begin
      -- Previous fiscal year

      set @THISFROMDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@THISFROMDATE), 0);
      set @THISTODATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(dateadd(year,-1,@THISTODATE), 1);
    end else if @THISDATERANGE=7 begin
      -- This calendar year

      set @THISFROMDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@THISFROMDATE, 0);
      set @THISTODATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@THISTODATE, 1);
    end else if @THISDATERANGE=13 begin
      -- Last calendar year

      set @THISFROMDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@THISFROMDATE, 0);
      set @THISTODATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@THISTODATE, 1);
    end else if @THISDATERANGE=43 begin
      -- This quarter

      set @THISFROMDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@THISFROMDATE, 0);
      set @THISTODATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@THISTODATE, 1);
    end else if @THISDATERANGE=45 begin
      -- Last quarter

      set @THISFROMDATE = dbo.UFN_DATE_LASTQUARTER_FIRSTDAY(@THISFROMDATE, 0);
      set @THISTODATE = dbo.UFN_DATE_LASTQUARTER_LASTDAY(@THISTODATE, 1);
        end else if @THISDATERANGE=5 begin
      -- This week

            set @THISFROMDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@THISFROMDATE, 0);
        set @THISTODATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@THISTODATE, 1);
        end else if @THISDATERANGE=11 begin
      -- Last week

      set @THISFROMDATE = dbo.UFN_DATE_LASTWEEK_FIRSTDAY(@THISFROMDATE, 0);
      set @THISTODATE = dbo.UFN_DATE_LASTWEEK_LASTDAY(@THISTODATE, 1);
    end
  end

  set @PREVIOUSFROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSFROMDATE);
  set @PREVIOUSTODATE = dbo.UFN_DATE_GETLATESTTIME(@PREVIOUSTODATE);

  set @THISFROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@THISFROMDATE);
  set @THISTODATE = dbo.UFN_DATE_GETLATESTTIME(@THISTODATE);

  set @EXCLUDEGIFTSOVER = coalesce(@EXCLUDEGIFTSOVER, 0);

  declare @GIFTTYPESFILTER table (TYPECODE tinyint, APPLICATIONCODE tinyint primary key (TYPECODE, APPLICATIONCODE));    
  declare @DESIGNATIONSFILTER table (ID uniqueidentifier primary key);
  declare @CAMPAIGNSFILTER table(ID uniqueidentifier primary key);

  if @TYPECODES is null
  begin
    insert into @GIFTTYPESFILTER(TYPECODE,APPLICATIONCODE) values (0,0)
  end
  else
  begin

    insert into @GIFTTYPESFILTER(TYPECODE,APPLICATIONCODE) 
    select distinct
      case T.c.value('(TYPECODEID)[1]','tinyint') 
        when 1 then 1 --Pledge

        when 3 then 3 --Matching gift claim

        when 9 then 4 --Planned gift

                when 13 then 7 --Auction donation

                when 15 then 8 --Donor challenge claim

                when 17 then 6 --Grant award

        else 0
      end as TYPECODE,
      case T.c.value('(TYPECODEID)[1]','tinyint') 
        when 4 then 2 --Pledge payment

        when 5 then 3 --RG payment

        when 6 then 1 --Event registration fee

        when 8 then 7 --Matching gift payment

        when 10 then 6 --Planned gift payment

        when 11 then 5 --Membership

        when 12 then 4 --Other

                when 16 then 13 --Donor challenge

                when 18 then 8 --Grant award

        when 14 then 12 --Auction purchase

        else 0          --Gift, pledge, planned gift, and matching gift claim

      end as APPLICATIONCODE
      FROM   @TYPECODES.nodes('/TYPECODES/ITEM') T(c)

    if exists (select 1 from @GIFTTYPESFILTER where TYPECODE = 1)
      insert into @GIFTTYPESFILTER(TYPECODE,APPLICATIONCODE)
      select 1,5 union all select 1,4; --Add pledges applied to memberships and applied to other    


    -- If Gifts selected, include Sales order donations

    if exists(select 1 from @GIFTTYPESFILTER where TYPECODE = 0 and APPLICATIONCODE = 0)
      insert into @GIFTTYPESFILTER(TYPECODE, APPLICATIONCODE)
        values (5, 0);

    -- Bug 262313 If Memberships selected, include Sales order memberships

    if exists(select 1 from @GIFTTYPESFILTER where TYPECODE = 0 and APPLICATIONCODE = 5)
      insert into @GIFTTYPESFILTER(TYPECODE, APPLICATIONCODE)
        values (5, 5);
  end 

  if @DESIGNATIONS is not null
    insert into @DESIGNATIONSFILTER(ID) select distinct
      T.c.value('(ID)[1]','uniqueidentifier')
      FROM   @DESIGNATIONS.nodes('/DESIGNATIONS/ITEM') T(c);
  if not exists (select ID from @DESIGNATIONSFILTER)
    set @DESIGNATIONS = null;

  if @CAMPAIGNS is not null
    insert into @CAMPAIGNSFILTER(ID) select distinct
      T.c.value('(ID)[1]','uniqueidentifier')
      FROM  @CAMPAIGNS.nodes('/CAMPAIGNS/ITEM') T(c);
  if not exists (select ID from @CAMPAIGNSFILTER)
    set @CAMPAIGNS = null;

  -- todo account for writeoffs

  with REVENUE_CTE as
  (
    select
      REVENUE.ID,
      REVENUE.CONSTITUENTID,
      sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID)) [AMOUNT],
      REVENUE.DATE
    from
      dbo.FINANCIALTRANSACTION as REVENUE
    inner join
       dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
    inner join
       dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
    inner join 
      @GIFTTYPESFILTER as GIFTTYPESFILTER on GIFTTYPESFILTER.TYPECODE = REVENUE.TYPECODE and GIFTTYPESFILTER.APPLICATIONCODE = REVENUESPLIT_EXT.APPLICATIONCODE
    where
      REVENUESPLIT.DELETEDON IS NULL and
      REVENUESPLIT.TYPECODE <> 1 and
      REVENUE.DELETEDON IS NULL and
      REVENUE.TYPECODE IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) and
      (
        (
          @DESIGNATIONS is null
        )
        or 
        (
          (
            REVENUESPLIT.ID in 
            (
              select 
                REVENUESPLIT.ID 
              from 
                dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT 
              inner join 
                dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
              inner join 
                @DESIGNATIONSFILTER as DESIGNATIONSFILTER on DESIGNATIONSFILTER.ID = REVENUESPLIT_EXT.DESIGNATIONID
            )
          )
          or
          (
            REVENUE.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 1
          )
        )
      )
    and
      (
        (
          @CAMPAIGNS is null
        )
        or
        (
          (
            REVENUESPLIT.ID in 
            (    
              select 
                REVENUESPLIT.ID 
              from 
                dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT 
              inner join 
                dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
              inner join 
                dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID 
              inner join 
                @CAMPAIGNSFILTER as CAMPAIGNSFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNSFILTER.ID
            )
          )
          or
          (
            REVENUE.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 1
          )
        )
      )
    and
      (@EXCLUDEGIFTSOVER = 0 or dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID, @CURRENCYID) <= @EXCLUDEGIFTSOVER)
    and
            (
          @ISADMIN = 1 
                or 
              (
                (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '6e022da3-e987-4d91-bd99-4beabe7e791e', REVENUE.ID) = 1 )
                and
                (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '6e022da3-e987-4d91-bd99-4beabe7e791e', REVENUE.CONSTITUENTID) = 1)
                and
                (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '6e022da3-e987-4d91-bd99-4beabe7e791e', REVENUE.CONSTITUENTID) = 1)
              )
            )
    group by
      REVENUE.ID, REVENUE.CONSTITUENTID, REVENUE.TYPECODE, REVENUE.DATE
  ),
  CONSTITUENTREVENUE_CTE as
  (
    select
      REVENUE_CTE.CONSTITUENTID,
      SUM(
        case
          when (REVENUE_CTE.DATE < @PREVIOUSFROMDATE) then REVENUE_CTE.AMOUNT
          else 0
        end        
      ) [ALLOTHERAMOUNT],
      SUM(
        case
          when REVENUE_CTE.DATE between @PREVIOUSFROMDATE and @PREVIOUSTODATE then REVENUE_CTE.AMOUNT
          else 0
        end
      ) [PREVIOUSPERIODAMOUNT],
      SUM(
        case 
          when REVENUE_CTE.DATE between @THISFROMDATE and @THISTODATE then REVENUE_CTE.AMOUNT
          else 0
        end
      ) [THISPERIODAMOUNT]
    from
      REVENUE_CTE
    group by
      REVENUE_CTE.CONSTITUENTID
  )
  insert into @T
  select
    CONSTITUENTREVENUE_CTE.CONSTITUENTID,
    CONSTITUENT.NAME,
    CONSTITUENTREVENUE_CTE.PREVIOUSPERIODAMOUNT,
    CONSTITUENTREVENUE_CTE.THISPERIODAMOUNT,
    CONSTITUENTREVENUE_CTE.ALLOTHERAMOUNT,
    case
      when THISPERIODAMOUNT > 0 and PREVIOUSPERIODAMOUNT = 0 and ALLOTHERAMOUNT = 0 then 0
      when THISPERIODAMOUNT > 0 and PREVIOUSPERIODAMOUNT = 0 and ALLOTHERAMOUNT > 0 then 1
      when THISPERIODAMOUNT > PREVIOUSPERIODAMOUNT and PREVIOUSPERIODAMOUNT > 0 then 2
      when THISPERIODAMOUNT = PREVIOUSPERIODAMOUNT and PREVIOUSPERIODAMOUNT > 0 then 3
      when THISPERIODAMOUNT < PREVIOUSPERIODAMOUNT and THISPERIODAMOUNT > 0 then 4
      when THISPERIODAMOUNT = 0 and PREVIOUSPERIODAMOUNT > 0 and ALLOTHERAMOUNT = 0 then 5
      when THISPERIODAMOUNT = 0 and PREVIOUSPERIODAMOUNT > 0 and ALLOTHERAMOUNT > 0 then 6
      else -1
    end,
    @CURRENCYID 
  from
    CONSTITUENTREVENUE_CTE
  inner join
    dbo.CONSTITUENT on CONSTITUENT.ID = CONSTITUENTREVENUE_CTE.CONSTITUENTID
  where
    (@INCLUDENEW = 1 and THISPERIODAMOUNT > 0 and PREVIOUSPERIODAMOUNT = 0 and ALLOTHERAMOUNT = 0)
  or
    (@INCLUDERECAPTURE = 1 and THISPERIODAMOUNT > 0 and PREVIOUSPERIODAMOUNT = 0 and ALLOTHERAMOUNT > 0)
  or
    (@INCLUDEUPGRADE = 1 and THISPERIODAMOUNT > PREVIOUSPERIODAMOUNT and PREVIOUSPERIODAMOUNT > 0)
  or
    (@INCLUDESAME = 1 and THISPERIODAMOUNT = PREVIOUSPERIODAMOUNT and PREVIOUSPERIODAMOUNT > 0)
  or
    (@INCLUDEDOWNGRADE = 1 and THISPERIODAMOUNT < PREVIOUSPERIODAMOUNT and THISPERIODAMOUNT > 0)
  or
    (@INCLUDELAPSEDNEW = 1 and THISPERIODAMOUNT = 0 and PREVIOUSPERIODAMOUNT > 0 and ALLOTHERAMOUNT = 0)
  or
    (@INCLUDELAPSEDREPEAT = 1 and THISPERIODAMOUNT = 0 and PREVIOUSPERIODAMOUNT > 0 and ALLOTHERAMOUNT > 0)

  return;
end;