USP_DATALIST_PLANNEDGIFTHISTORY

CREATE procedure dbo.USP_DATALIST_PLANNEDGIFTHISTORY
(
  @PLANNEDGIFTID uniqueidentifier,
  @USERID uniqueidentifier = null,
  @ACTIONTYPECODE tinyint = 0,
  @SHOWCODE tinyint = 1,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @GROUPCODE bit = 0
)
as
  set nocount on;

  declare @CURRENTDATE datetime = getdate();

  if @SHOWCODE in (1, 2, 3, 4, 5, 6)
  begin
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

    if @SHOWCODE = 1 --Last 30 days

      set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day, -29, @CURRENTDATE));
    else if @SHOWCODE = 2 --Last 60 days

      set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day, -59, @CURRENTDATE));
    else if @SHOWCODE = 3 --Last 90 days

      set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day, -89, @CURRENTDATE));
    else if @SHOWCODE = 4 --Last 6 months

      set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day, 1, dateadd(month, -6, @CURRENTDATE)));
    else if @SHOWCODE = 5 --Last year

      set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day, 1, dateadd(year, -1, @CURRENTDATE)));
    else if @SHOWCODE = 6 --Last 5 years

      set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day, 1, dateadd(year, -5, @CURRENTDATE)));
  end
  else if @SHOWCODE = 0
  begin
    set @STARTDATE = null;
    set @ENDDATE = null;
  end
  else if @SHOWCODE = 99
  begin
    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
  end

  declare @USERNAME nvarchar(128);

  if not @USERID is null
    select
      @USERNAME = USERNAME
    from
      dbo.APPUSER
    where
      APPUSER.ID = @USERID;
  else
    set @USERNAME = '';

  declare @PLANNEDGIFTDATEADDED datetime;

  select
    @PLANNEDGIFTDATEADDED = DATEADDED
  from
    dbo.PLANNEDGIFT
  where
    ID = @PLANNEDGIFTID;

  declare @AUDIT table
  (
    RECORDID uniqueidentifier,
    AUDITDATE datetime,
    CHANGEDBYUSER nvarchar(128),
    PROCESS nvarchar(255),
    FIELD nvarchar(128),
    OLD nvarchar(4000),
    NEW nvarchar(4000),
    SEQUENCE int,
    [ACTION] nvarchar(8),
    CHANGEDBYAPP nvarchar(200),
    GROUPONVALUE uniqueidentifier
  );

  if @ACTIONTYPECODE in (0, 1)
    insert into @AUDIT
    (
      RECORDID,
      AUDITDATE,
      [ACTION],
      FIELD,
      OLD,
      NEW,
      CHANGEDBYUSER,
      PROCESS,
      CHANGEDBYAPP,
      SEQUENCE,
      GROUPONVALUE
    )
    select
      RECORDID,
      DATEADDED,
      'Insert' as [ACTION],
      FIELD,
      OLD,
      NEW,
      CHANGEDBYUSER,
      PROCESS,
      CHANGEDBYAPP,
      -1 as SEQUENCE,
      null as GROUPONVALUE
    from
      dbo.UFN_AUDIT_GETINSERTS_FORTABLE('PLANNEDGIFT', 'ID', @PLANNEDGIFTID, 0, null)
    where
      @USERID is null or CHANGEDBYUSER = @USERNAME;

  insert into @AUDIT
  (
    RECORDID,
    AUDITDATE,
    [ACTION],
    FIELD,
    OLD,
    NEW,
    CHANGEDBYUSER,
    PROCESS,
    CHANGEDBYAPP,
    SEQUENCE,
    GROUPONVALUE
  )
  select
    @PLANNEDGIFTID as [RECORDID],
    [CHANGES].DATECHANGED,
    'Update' as [ACTION],
    DETAIL.FIELD,
    DETAIL.OLD,
    DETAIL.NEW,
    [CHANGES].CHANGEDBYUSER,
    '' as PROCESS,
    [CHANGES].CHANGEDBYAPP,
    0 as SEQUENCE,
    null as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETRECENTCHANGES_FORTABLE('PLANNEDGIFT', 500, null, null, 0, 1, 0, null, null, @PLANNEDGIFTID) as [CHANGES]
  cross apply
    dbo.UFN_AUDIT_GETDETAIL('PLANNEDGIFTAUDIT', [CHANGES].AUDITKEY, @PLANNEDGIFTID) as DETAIL
  where
  (
    DETAIL.FIELD <> 'DATECHANGED' and
    DETAIL.FIELD <> 'CHANGEDBYID' and
    DETAIL.FIELD <> 'ADDEDBYID' and
    DETAIL.FIELD <> 'DATEADDED'
  )
  and
  (
    @USERID is null or CHANGEDBYUSER = @USERNAME
  )
  and
  (
    @ACTIONTYPECODE = 0 or @ACTIONTYPECODE = 2
  )

  union all

  select
    RECORDID,
    AUDITDATE,
    [ACTION],
    FIELD,
    OLD,
    NEW,
    CHANGEDBYUSER,
    PROCESS,
    CHANGEDBYAPP,
    SEQUENCE,
    GROUPONVALUE
  from
    dbo.UFN_PLANNEDGIFTHISTORY_PLANNEDGIFTCHILDTABLES_AUDIT(@PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, @ACTIONTYPECODE)
  where
    @USERID is null or CHANGEDBYUSER = @USERNAME;

  if @STARTDATE is not null
    delete from @AUDIT where AUDITDATE < @STARTDATE;

  if @ENDDATE is not null
    delete from @AUDIT where AUDITDATE > @ENDDATE;

  -- Added 12/17/2010 by SlyyMu

  -- The following block removes all records that do not have a revenue ID. 

  -- In essence, when the bit value of "1" is used it removes all non payment records with the exception of the planned gift itself.

  -- In order to remove the planned gift, add the following to the delete statement below: or GROUPONVALUE = RECORDID

  if @GROUPCODE = 1
    delete from @AUDIT where GROUPONVALUE is null;

  select 
    AUDIT.RECORDID,
    AUDIT.AUDITDATE,
    AUDIT.[ACTION],
    AUDIT.FIELD,
    AUDIT.OLD,
    AUDIT.NEW,
    AUDIT.CHANGEDBYUSER,
    AUDIT.PROCESS,
    AUDIT.CHANGEDBYAPP,
    AUDIT.SEQUENCE,
    case
      when AUDIT.[ACTION] = 'Delete'
        then 0
      when AUDIT.[ACTION] = 'Update'
        then 1
      else
        2
    end as ACTIONSORT,
    case
      when @GROUPCODE = 0
        then 'Date'
      else
        case
          when FT.ID is null
            then 'Planned gift'
          else
            --The following block reconstructs the "Field" column to a value by which we can sort chronologically --

            replace(dbo.UFN_PAYMENT_GETDESCRIPTION(FT.ID), '(Payment)', '(Payment ' + substring(AUDIT.FIELD, 9, charindex(':', AUDIT.FIELD, 1) - charindex(' ', AUDIT.FIELD, 1) - 1) + ')')
        end
    end as GROUPONVALUE
  from
    @AUDIT as AUDIT
  left join
    dbo.FINANCIALTRANSACTION FT on FT.ID = AUDIT.GROUPONVALUE
  order by
    AUDITDATE desc,
    FIELD desc,
    [ACTIONSORT] asc,
    SEQUENCE asc,
    RECORDID