USP_DATALIST_GIFTLIST

        CREATE procedure [dbo].[USP_DATALIST_GIFTLIST] (
            @CONSTITUENTID nvarchar(1000) = null
            ,@GIFTTYPE nvarchar(200) = null
            ,@DATEADDED datetime = null
            ,@LASTMODIFIED datetime = null
            ,@DATEFILTER tinyint = 16
            ,@STARTDATE datetime = null
            ,@ENDDATE datetime = null
            ,@DESIGNATIONID nvarchar(500) = null
            ,@APPEALID nvarchar(500) = null
            ,@MAXROWS int = null
            ,@SORTPARAMETER nvarchar(100) = null
            ,@SORTORDER nvarchar(20) = 'ASC'
            ,@CURRENTAPPUSERID uniqueidentifier = null
            ,@SECURITYFEATUREID uniqueidentifier = null
            ,@SECURITYFEATURETYPE tinyint = null
            ) with execute as OWNER
        as
        begin
            set nocount on;

            declare @PARMDEFINITION nvarchar(1000)
            ,@SQLCOMMAND nvarchar(4000)
            ,@WHERECLAUSE nvarchar(2000)='';

            set @PARMDEFINITION = '@CONSTITUENTID nvarchar(1000), @GIFTTYPE nvarchar(200)
            ,@STARTDATE datetime, @ENDDATE datetime, @MAXROWS int, @SORTPARAMETER nvarchar(100), @SORTORDER nvarchar(20)
            ,@CURRENTAPPUSERID uniqueidentifier, @SECURITYFEATUREID uniqueidentifier, @SECURITYFEATURETYPE tinyint
            ,@DESIGNATIONID nvarchar(500), @APPEALID nvarchar(500) = null,@DATEADDED datetime, @LASTMODIFIED datetime';

            if(@MAXROWS is null)
                set @MAXROWS = 500;

            set @SORTORDER = case 
                    when @SORTORDER = 'DESC'
                        then 'DESC'
                    else 'ASC'
                    end

            --Validate and assigned sortparamter


            if (@SORTPARAMETER = '' or @SORTPARAMETER is null or @SORTPARAMETER = 'constituent_id')
                set @SORTPARAMETER = 'FINANCIALTRANSACTION.CONSTITUENTID';
            else if(@SORTPARAMETER = 'batch_number')
                set @SORTPARAMETER = 'REVENUE_EXT.BATCHNUMBER';
            else if(@SORTPARAMETER = 'date')
                set @SORTPARAMETER = 'FINANCIALTRANSACTION.DATE';
            else if(@SORTPARAMETER = 'date_added')
                set @SORTPARAMETER = 'FINANCIALTRANSACTION.DATEADDED';
            else if(@SORTPARAMETER = 'date_modified')
                set @SORTPARAMETER = 'FINANCIALTRANSACTION.DATECHANGED';
            else if(@SORTPARAMETER = 'given_anonymously')
                set @SORTPARAMETER = 'REVENUE_EXT.GIVENANONYMOUSLY';
            else if(@SORTPARAMETER = 'payment_method')
                set @SORTPARAMETER = 'REVENUEPAYMENTMETHOD.PAYMENTMETHOD';
            else if(@SORTPARAMETER = 'post_status')
                set @SORTPARAMETER = 'FINANCIALTRANSACTION.POSTSTATUS';
            else if(@SORTPARAMETER = 'receipt_amount')
                set @SORTPARAMETER = 'REVENUE_EXT.RECEIPTAMOUNT';
            else if(@SORTPARAMETER = 'reference')
                set @SORTPARAMETER = 'REVENUE_EXT.REFERENCE';
            else if(@SORTPARAMETER = 'appeal_id')
                set @SORTPARAMETER = 'REVENUE_EXT.APPEALID';
            else if(@SORTPARAMETER = 'acknowledgement_status')
                set @SORTPARAMETER = 'ACKNOWLEDGEMENTSTATUS';
            else if(@SORTPARAMETER = 'gift_status')
                set @SORTPARAMETER = 'GIFTSTATUS';
            else if(@SORTPARAMETER = 'lookup_id')
                set @SORTPARAMETER = 'LOOKUPID';
            else if(@SORTPARAMETER = 'transaction_type')
                set @SORTPARAMETER = 'TRANSACTIONTYPE';
            else if(@SORTPARAMETER = 'id')
                set @SORTPARAMETER = 'FINANCIALTRANSACTION.ID';
            else if(@SORTPARAMETER = 'base_currency_id')
                set @SORTPARAMETER = 'BASECURRENCYID';
            else if(@SORTPARAMETER = 'amount' or @SORTPARAMETER = 'balance')
                set @SORTPARAMETER = @SORTPARAMETER;
            else
                begin
                    raiserror(N'The sort field %s is not valid. Only non-collection output fields are usable.', 13, 1,@SORTPARAMETER);
                    return 0;
                end

            if @STARTDATE is null and @ENDDATE is null
                exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER
                    ,@STARTDATE output
                    ,@ENDDATE output;
            else
            begin
                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
                set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
            end


            set @SQLCOMMAND='with MOSTRECENTRECEIPT as (
            select
                REVENUEID,
                RECEIPTPROCESSDATE = MAX(RECEIPTPROCESSDATE),
                DATEADDED= case when MAX(RECEIPTPROCESSDATE) is null then max(DATEADDED) else null end
            from
                dbo.REVENUERECEIPT with (nolock)
                group by
                REVENUEID
            )
            '


            if @MAXROWS = 0
                set @SQLCOMMAND =@SQLCOMMAND+ ' select ';
            else
                set @SQLCOMMAND =@SQLCOMMAND+ ' select top (@MAXROWS) ';

            set @SQLCOMMAND = @SQLCOMMAND + 
                'FINANCIALTRANSACTION.ID
                    ,dbo.UFN_REVENUETRANSACTION_GETACKNOWLEDGESTATUS(FINANCIALTRANSACTION.ID) as ACKNOWLEDGEMENTSTATUS
                    ,FINANCIALTRANSACTION.BASEAMOUNT as AMOUNT
                    ,case FINANCIALTRANSACTION.TYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID) else null end as BALANCE
                    ,REVENUE_EXT.BATCHNUMBER
                    ,FINANCIALTRANSACTION.CONSTITUENTID
                    ,cast(FINANCIALTRANSACTION.DATE as datetime) as DATE
                    ,FINANCIALTRANSACTION.DATEADDED
                    ,FINANCIALTRANSACTION.DATECHANGED as DATEMODIFIED
                    ,(select FINANCIALTRANSACTIONLINEITEM.ID,REVENUESPLIT_EXT.DESIGNATIONID as DESIGNATIONID,FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT as AMOUNT,
                        REVENUESPLIT_EXT.APPLICATIONCODE as APPLICATION, 
                        REVENUESPLIT_EXT.TYPECODE as TYPE,
                        FINANCIALTRANSACTION.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID
                        from FINANCIALTRANSACTIONLINEITEM 
                        left join REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID=REVENUESPLIT_EXT.ID where 
                        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID=FINANCIALTRANSACTION.ID for xml path(''GIFTSPLITS_DATAITEM''), type, elements,binary base64) as GIFTSPLITS
                    ,case when REVENUESCHEDULE.STATUSCODE is null then PLANNEDGIFT.STATUS 
                        else case REVENUESCHEDULE.STATUSCODE when 3 
                        then ''Canceled'' else REVENUESCHEDULE.STATUS 
                    end end as GIFTSTATUS
                    ,REVENUE_EXT.GIVENANONYMOUSLY
                    ,FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as LOOKUPID
                    ,REVENUEPAYMENTMETHOD.PAYMENTMETHOD as PAYMENTMETHOD 
                    ,FINANCIALTRANSACTION.TYPE as TRANSACTIONTYPE
                    ,FINANCIALTRANSACTION.POSTSTATUS as POSTSTATUS
                    ,REVENUE_EXT.RECEIPTAMOUNT
                    ,REVENUE_EXT.REFERENCE as REFERENCE
                    ,(select REVENUE_EXT.RECEIPTAMOUNT as AMOUNT, RR.RECEIPTDATE as DATE from MOSTRECENTRECEIPT WITH (NOLOCK)
                    INNER join REVENUERECEIPT RR on RR.REVENUEID = MOSTRECENTRECEIPT.REVENUEID and 
                    (MOSTRECENTRECEIPT.RECEIPTPROCESSDATE = RR.RECEIPTPROCESSDATE or MOSTRECENTRECEIPT.DATEADDED = RR.DATEADDED)
                    where RR.REVENUEID =FINANCIALTRANSACTION.ID for xml path(''RECEIPTS_DATAITEM''),type, elements,binary base64) as RECEIPTS
                    ,REVENUE_EXT.APPEALID as APPEALID
                    ,isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID
                from dbo.FINANCIALTRANSACTION 
                left join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                left join dbo.PLANNEDGIFT on PLANNEDGIFT.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
                left join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
                left join dbo.REVENUESCHEDULE as REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
                left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
                left join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                '

            if @CONSTITUENTID is not null and @CONSTITUENTID <> ''
                set @WHERECLAUSE = '(FINANCIALTRANSACTION.CONSTITUENTID in (select ID from dbo.fnCSVStringToTable(@CONSTITUENTID))) ';


            if @GIFTTYPE is not null and @GIFTTYPE <> ''
            begin
                if @WHERECLAUSE <> ''
                    set @WHERECLAUSE = @WHERECLAUSE + char(10) + 'and';

                set @WHERECLAUSE = @WHERECLAUSE + ' (FINANCIALTRANSACTION.TYPE in (select ID from dbo.fnCSVStringToTable(@GIFTTYPE))) ';
            end


            if @APPEALID is not null and @APPEALID <> ''
            begin
                if @WHERECLAUSE <> ''
                    set @WHERECLAUSE = @WHERECLAUSE + char(10) + 'and';

                set @WHERECLAUSE = @WHERECLAUSE + ' (REVENUE_EXT.APPEALID in (select ID from dbo.fnCSVStringToTable(@APPEALID))) ';
            end


            if @DATEADDED is not null
            begin
                if @WHERECLAUSE <> ''
                    set @WHERECLAUSE = @WHERECLAUSE + char(10) + 'and';

                set @WHERECLAUSE = @WHERECLAUSE + ' (FINANCIALTRANSACTION.DATEADDED >= @DATEADDED)';
            end


            if @LASTMODIFIED is not null
            begin
                if @WHERECLAUSE <> ''
                    set @WHERECLAUSE = @WHERECLAUSE + char(10) + 'and';

                set @WHERECLAUSE = @WHERECLAUSE + ' (FINANCIALTRANSACTION.DATECHANGED >= @LASTMODIFIED)';
            end


            if @STARTDATE is not null and @ENDDATE is null
            begin
                if @WHERECLAUSE <> ''
                    set @WHERECLAUSE = @WHERECLAUSE + char(10) + 'and';

                set @WHERECLAUSE = @WHERECLAUSE + ' (FINANCIALTRANSACTION.DATE >= @STARTDATE)';
            end


            if @STARTDATE is null and @ENDDATE is not null
            begin
                if @WHERECLAUSE <> ''
                    set @WHERECLAUSE = @WHERECLAUSE + char(10) + 'and';

                set @WHERECLAUSE = @WHERECLAUSE + ' (FINANCIALTRANSACTION.DATE <= @ENDDATE)';
            end


            if @STARTDATE is not null and @ENDDATE is not null
            begin
                if @WHERECLAUSE <> ''
                    set @WHERECLAUSE = @WHERECLAUSE + char(10) + 'and';

                set @WHERECLAUSE = @WHERECLAUSE + ' (FINANCIALTRANSACTION.DATE between @STARTDATE and @ENDDATE)';
            end


            if @DESIGNATIONID is not null and @DESIGNATIONID <> ''
            begin
                if @WHERECLAUSE <> ''
                    set @WHERECLAUSE = @WHERECLAUSE + char(10) + 'and';

                set @WHERECLAUSE = @WHERECLAUSE+' EXISTS (select 1 from FINANCIALTRANSACTIONLINEITEM FINANCIALTRANSACTIONLINEITEM 
                left join REVENUESPLIT_EXT REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID=REVENUESPLIT_EXT.ID where
                FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                and REVENUESPLIT_EXT.DESIGNATIONID in (select ID from dbo.fnCSVStringToTable(@DESIGNATIONID)))';
            end


            if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)) <> 1
            begin
                if @WHERECLAUSE <> ''
                    set @WHERECLAUSE = @WHERECLAUSE + char(10) + 'and';

                set @WHERECLAUSE = @WHERECLAUSE + ' ((select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE)
                                where (SITEID in (select [SITEID] from UFN_SITEID_MAPFROM_REVENUEID_2(FINANCIALTRANSACTION.ID))) 
                                or (SITEID is null and (select COUNT([SITEID]) from UFN_SITEID_MAPFROM_REVENUEID_2(FINANCIALTRANSACTION.ID)) = 0)) = 1)';
            end

            if len(@WHERECLAUSE) > 0 
                set @SQLCOMMAND = @SQLCOMMAND + ' where ' + @WHERECLAUSE;


            set @SQLCOMMAND = @SQLCOMMAND + ' order by ' + @SORTPARAMETER + ' ' + @SORTORDER;


            /*will execute the final dynamic query*/
            exec sp_executesql @SQLCOMMAND
                ,@PARMDEFINITION
                ,@DATEADDED = @DATEADDED
                ,@LASTMODIFIED = @LASTMODIFIED
                ,@CONSTITUENTID = @CONSTITUENTID
                ,@GIFTTYPE = @GIFTTYPE
                ,@STARTDATE = @STARTDATE
                ,@ENDDATE = @ENDDATE
                ,@MAXROWS = @MAXROWS
                ,@SORTPARAMETER = @SORTPARAMETER
                ,@SORTORDER = @SORTORDER
                ,@CURRENTAPPUSERID = @CURRENTAPPUSERID
                ,@SECURITYFEATUREID = @SECURITYFEATUREID
                ,@SECURITYFEATURETYPE = @SECURITYFEATURETYPE
                ,@DESIGNATIONID =@DESIGNATIONID 
                ,@APPEALID =@APPEALID;

        end