USP_CONSTITUENTSMARTFIELD_APPEALVALUES

CREATE procedure dbo.USP_CONSTITUENTSMARTFIELD_APPEALVALUES
(
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @BUSINESSUNITS xml,
  @CATEGORIES xml,
  @REPORTCODES xml,
  @SITES xml,
  @CALCULATIONTYPE tinyint = 0,
  @ASOF datetime,
  @SMARTFIELDID uniqueidentifier = null
)
with execute as owner
as
  /*
  This smart field will return an aggregation of appeal values for ALL constituents in the database
  */

  set nocount on;

  /*
  Find all valid constituents based on the date that the smart field was last updated.
  */
  declare @CONSTITUENTFILTERVIEW as nvarchar(60) = '#TMP_CONSTITUENTS_CONSTITUENTSMARTFIELD_APPEALVALUES'

  if @ASOF is null
    set @CONSTITUENTFILTERVIEW = 'dbo.CONSTITUENT'
  else
    begin
      if object_id('tempdb..#TMP_CONSTITUENTS_CONSTITUENTSMARTFIELD_APPEALVALUES') is not null
        drop table #TMP_CONSTITUENTS_CONSTITUENTSMARTFIELD_APPEALVALUES;

      create table #TMP_CONSTITUENTS_CONSTITUENTSMARTFIELD_APPEALVALUES (ID uniqueidentifier primary key);

      insert into #TMP_CONSTITUENTS_CONSTITUENTSMARTFIELD_APPEALVALUES (ID)
      select CONSTITUENTID from dbo.CONSTITUENTAPPEAL where CONSTITUENTAPPEAL.DATECHANGED > @ASOF
      union
      select CONSTITUENTID from dbo.CONSTITUENTAPPEALAUDIT inner join dbo.CONSTITUENT on CONSTITUENT.ID = CONSTITUENTAPPEALAUDIT.CONSTITUENTID where CONSTITUENTAPPEALAUDIT.AUDITDATE > @ASOF
      union
      select ID from dbo.CONSTITUENT where DATEADDED > @ASOF;
    end

  declare @SQL nvarchar(max);

  set @SQL = '
    declare @BUSINESSUNITSFILTER table(ID uniqueidentifier primary key);
    declare @CATEGORIESFILTER table(ID uniqueidentifier primary key);
    declare @REPORTCODESFILTER table(ID uniqueidentifier primary key);
    declare @SITESFILTER table(ID uniqueidentifier primary key);

    if @BUSINESSUNITS is not null
      insert into @BUSINESSUNITSFILTER (ID) select distinct
        T.c.value(''(BUSINESSUNITID)[1]'', ''uniqueidentifier'')
        FROM   @BUSINESSUNITS.nodes(''/BUSINESSUNITS/ITEM'') T(c)

    if @CATEGORIES is not null
      insert into @CATEGORIESFILTER (ID) select distinct
        T.c.value(''(CATEGORYID)[1]'', ''uniqueidentifier'')
        FROM   @CATEGORIES.nodes(''/CATEGORIES/ITEM'') T(c)

    if @SITES is not null
      insert into @SITESFILTER (ID) select distinct
        T.c.value(''(SITEID)[1]'', ''uniqueidentifier'')
        FROM   @SITES.nodes(''/SITES/ITEM'') T(c)

    if @REPORTCODES is not null
      insert into @REPORTCODESFILTER (ID) select distinct
        T.c.value(''(REPORTCODEID)[1]'', ''uniqueidentifier'')
        FROM @REPORTCODES.nodes(''/REPORTCODES/ITEM'') T(c);'

  set @SQL = @SQL + '
  with APPEAL_CTE (ID, CONSTITUENTID, DATESENT, NAME, ROW) as
    (select
      CONSTITUENTAPPEAL.ID,
      CONSTITUENTAPPEAL.CONSTITUENTID,
      CONSTITUENTAPPEAL.DATESENT,
      APPEAL.NAME,'

  -- Include a row number if needed for calculating an extreme.

  if @CALCULATIONTYPE = 2
    set @SQL = @SQL + char(10) + 'row_number() over (partition by CONSTITUENTAPPEAL.CONSTITUENTID order by CONSTITUENTAPPEAL.DATESENT desc, CONSTITUENTAPPEAL.DATEADDED desc) as ROW'
  else
    set @SQL = @SQL + char(10) + '0 as ROW'

  set @SQL = @SQL + char(10) + '
    from 
      dbo.CONSTITUENTAPPEAL
      inner join dbo.APPEAL on APPEAL.ID = CONSTITUENTAPPEAL.APPEALID'

  -- If the constituent filter is all constituents, then simply filtering out null is faster than joining

  if @ASOF is not null
      set @SQL = @SQL + char(10) + 'inner join ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER on CONSTITUENTFILTER.ID = CONSTITUENTAPPEAL.CONSTITUENTID'

  set @SQL = @SQL + char(10) + 'where'

  if @ASOF is null
      set @SQL = @SQL + char(10) + 'CONSTITUENTAPPEAL.CONSTITUENTID is not null and'

  set @SQL = @SQL + char(10) + '
      ((@STARTDATE is null) or (coalesce(CONSTITUENTAPPEAL.DATESENT, CONSTITUENTAPPEAL.DATEADDED) >= @STARTDATE))
      and ((@ENDDATE is null) or (coalesce(CONSTITUENTAPPEAL.DATESENT, CONSTITUENTAPPEAL.DATEADDED) <= @ENDDATE))
      and ((@BUSINESSUNITS is null) or exists (
        select top 1 1
        from dbo.APPEALBUSINESSUNIT
        inner join @BUSINESSUNITSFILTER as FILTER on FILTER.ID = APPEALBUSINESSUNIT.BUSINESSUNITCODEID
        where APPEALBUSINESSUNIT.APPEALID = CONSTITUENTAPPEAL.APPEALID
      ))
      and ((@CATEGORIES is null) or (APPEAL.APPEALCATEGORYCODEID in (select ID from @CATEGORIESFILTER)))
      and ((@SITES is null) or (APPEAL.SITEID in (select ID from @SITESFILTER)))
      and ((@REPORTCODES is null) or (APPEAL.APPEALREPORT1CODEID in (select ID from @REPORTCODESFILTER))))';

  -- backward compatibility

  if @SMARTFIELDID is not null
      set @SQL = @SQL + char(10) + ', SF_CTE (ID, VALUE) as ('

  /*
  Calculate totals based on calculation type.
  */
  if @CALCULATIONTYPE = 0 -- count(ID)

    set @SQL = @SQL + char(10) + '
      select
        CONSTITUENTFILTER.ID,
        isnull(count(CONSTITUENTAPPEALRECORDS.ID), 0) as VALUE
      from
        ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
        left outer join APPEAL_CTE as CONSTITUENTAPPEALRECORDS on CONSTITUENTFILTER.ID = CONSTITUENTAPPEALRECORDS.CONSTITUENTID
      group by
        CONSTITUENTFILTER.ID';

  else if @CALCULATIONTYPE = 1 -- datediff(year, x)

    set @SQL = @SQL + char(10) + '
      select
        CONSTITUENTFILTER.ID,
        isnull(datediff(year, min(CONSTITUENTAPPEALRECORDS.DATESENT), max(CONSTITUENTAPPEALRECORDS.DATESENT)), 0) as VALUE
      from
        ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
        left outer join APPEAL_CTE as CONSTITUENTAPPEALRECORDS on CONSTITUENTFILTER.ID = CONSTITUENTAPPEALRECORDS.CONSTITUENTID
      group by
        CONSTITUENTFILTER.ID';

  else if @CALCULATIONTYPE = 2 -- name

    set @SQL = @SQL + char(10) + '
      select
        CONSTITUENTFILTER.ID,
        isnull(CONSTITUENTAPPEALRECORDS.NAME, '''') as VALUE
      from
        ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
        left outer join APPEAL_CTE as CONSTITUENTAPPEALRECORDS on CONSTITUENTFILTER.ID = CONSTITUENTAPPEALRECORDS.CONSTITUENTID
      where
        CONSTITUENTAPPEALRECORDS.ID is null or CONSTITUENTAPPEALRECORDS.ROW = 1';

  -- Backwards compatibility

  if @SMARTFIELDID is null
    exec sp_executesql @SQL, N'@ASOF datetime,
          @STARTDATE datetime,
          @ENDDATE datetime,
          @BUSINESSUNITS xml,
          @CATEGORIES xml,
          @REPORTCODES xml,
          @SITES xml
          ',
          @ASOF,
          @STARTDATE,
          @ENDDATE,
          @BUSINESSUNITS,
          @CATEGORIES,
          @REPORTCODES,
          @SITES
  else
    begin
      declare @TARGET nvarchar(100);
      declare @TARGET_FIELD nvarchar(128);

      select
          @TARGET = TABLECATALOG.TABLENAME,
          @TARGET_FIELD = SMARTFIELD.VALUECOLUMNNAME
      from dbo.SMARTFIELD
      left join dbo.TABLECATALOG on SMARTFIELD.TABLECATALOGID = TABLECATALOG.ID
      where SMARTFIELD.ID = @SMARTFIELDID

      set @SQL = @SQL + ')
          merge dbo.' + @TARGET + ' as TARGET
          using SF_CTE as SOURCE
          on TARGET.ID = SOURCE.ID
          when matched then
              update set ' + @TARGET_FIELD + ' = SOURCE.VALUE
          when not matched by TARGET then
              insert (ID, ' + @TARGET_FIELD + ')
              values (SOURCE.ID, SOURCE.VALUE);';

      exec sp_executesql @SQL, N'@ASOF datetime,
            @STARTDATE datetime,
            @ENDDATE datetime,
            @BUSINESSUNITS xml,
            @CATEGORIES xml,
            @REPORTCODES xml,
            @SITES xml
            ',
            @ASOF,
            @STARTDATE,
            @ENDDATE,
            @BUSINESSUNITS,
            @CATEGORIES,
            @REPORTCODES,
            @SITES

   select @@ROWCOUNT;
    end