USP_DATALIST_DEPOSITPAYMENTLINK_OPTIMIZED

CREATE procedure dbo.USP_DATALIST_DEPOSITPAYMENTLINK_OPTIMIZED
(
    @DEPOSITID uniqueidentifier = null output,
    @CONTEXTID uniqueidentifier,
    @BATCHID uniqueidentifier = null output,
    @ADDEDBY nvarchar(128) = null output,
    @PAYMENTMETHODCODE tinyint = null output,
    @CARDTYPECODEID uniqueidentifier = null output,
    @STARTDATE datetime = null output,
    @ENDDATE datetime = null output,
    @REFERENCE nvarchar(255) = '' output,
    @POSTSTATUSCODE tinyint = null output,
    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
    @APPLICATIONCODE tinyint = null,
    @MAXROWS int = 1000000,
    @CURRENTAPPUSERID uniqueidentifier = null
)
with execute as owner
as
    set nocount on;

    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BANKACCOUNTSYSTEMID uniqueidentifier;
    declare @BATCHNUMBER nvarchar(100); --dbo.UFN_BATCH_GETBATCHNUMBER(@BATCHID)

    select @BATCHNUMBER = BATCHNUMBER from dbo.BATCH where ID = @BATCHID;                     

    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

    select @BANKACCOUNTSYSTEMID = BA.PDACCOUNTSYSTEMID
        ,@TRANSACTIONCURRENCYID = D.TRANSACTIONCURRENCYID
        ,@POSTSTATUSCODE = BAT.POSTSTATUSCODE
    from BANKACCOUNT BA
    inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.BANKACCOUNTID = BA.ID
    inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = BAT.ID
    where D.ID = @CONTEXTID

    set @MAXROWS = COALESCE(@MAXROWS,100);

    declare @ISSYSADMIN bit
    set @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)

    declare @SQLTOEXEC nvarchar(max) = 'select top(@MAXROWS)
            [REVENUE].[ID]
            ,cast([REVENUE].[DATE] as datetime) as [TRANSACTIONDATE]
            ,REVENUEPAYMENTMETHOD.PAYMENTMETHOD
            ,[REVENUE].[BASEAMOUNT] as AMOUNT
            ,[REVENUE].TYPE as [TRANSACTIONTYPE]
            ,REVENUEREFERENCE.REFERENCE
            ,CONSTITUENT.NAME as CONSTITUENT
            ,T1.APPLCATION  as [APPLICATION]            
            ,[REVENUE].[TRANSACTIONAMOUNT]
            ,CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE
        from 
            dbo.FINANCIALTRANSACTION as REVENUE
            join dbo.REVENUE_EXT as REVENUEREFERENCE on REVENUEREFERENCE.ID = REVENUE.ID        
            inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.ADJUSTMENT on ADJUSTMENT.REVENUEID = REVENUE.ID and REVENUE.POSTSTATUSCODE = 2
            left join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = REVENUE.ID
            left join dbo.SALESORDER on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
            left join dbo.RECONCILIATION on SALESORDERPAYMENT.RECONCILIATIONID = RECONCILIATION.ID
            left join dbo.OTHERPAYMENTMETHODDETAIL on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
            left join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID 
            left join dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
            left join dbo.RESERVATIONSECURITYDEPOSITPAYMENT on RESERVATIONSECURITYDEPOSITPAYMENT.PAYMENTID = REVENUE.ID';

    declare @WHERECLAUSE nvarchar(max) = ' where REVENUE.DELETEDON is null
        and [BANKACCOUNTDEPOSITPAYMENT].[DEPOSITID] IS NULL AND [REVENUE].[TYPECODE] = 0
        and [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] in (0,1,2,10)
        and ((@POSTSTATUSCODE = 1 and  REVENUE.POSTSTATUSCODE = 1)
            or (@POSTSTATUSCODE = 2 and REVENUE.POSTSTATUSCODE = 3)
            or (ADJUSTMENT.ID is not null and ADJUSTMENT.POSTSTATUSCODE = @POSTSTATUSCODE))
        and (SALESORDERPAYMENT.ID is null or (RECONCILIATION.STATUSCODE = 3 and SALESORDERPAYMENT.DEPOSITED = 1) or (SALESORDERPAYMENT.DONOTRECONCILE = 1 and SALESORDER.STATUSCODE = 1))
        and (NOT exists(select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD where ID = OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID))
        and    ((RESERVATIONSECURITYDEPOSITPAYMENT.ID is null) or 
            (RESERVATIONSECURITYDEPOSITPAYMENT.RECONCILIATIONID is not null and RESERVATIONSECURITYDEPOSITPAYMENT.DEPOSITED = 1 and exists 
                (select 1 from dbo.RECONCILIATION REC where REC.STATUSCODE = 3 and REC.ID = RESERVATIONSECURITYDEPOSITPAYMENT.RECONCILIATIONID)
            ))';

    if exists (select 1 from dbo.INSTALLEDPRODUCTLIST where id = '133F9BCA-00F1-4007-9792-586B931340C6')
    begin
        --set @SQLTOEXEC = @SQLTOEXEC + ' inner join dbo.PDACCOUNTSYSTEMFORREVENUE on PDACCOUNTSYSTEMFORREVENUE.ID = REVENUE.ID ';

        set @WHERECLAUSE = @WHERECLAUSE + ' and REVENUE.PDACCOUNTSYSTEMID = @BANKACCOUNTSYSTEMID '
    end

    if (@ISSYSADMIN = 0)
    begin
        -- only show payments with designation sites that match the user's site

        set @WHERECLAUSE = @WHERECLAUSE + ' and exists (select U.SITEID from dbo.UFN_SITEID_MAPFROM_APPUSERID(@CURRENTAPPUSERID) U inner join dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) R on R.SITEID = U.SITEID or (R.SITEID is null and U.SITEID is null)) '
    end

    if exists (select 1 from dbo.INSTALLEDPRODUCTLIST where id = '97D98E59-5B0B-446F-BF48-DE8709F50AFE')
        set @WHERECLAUSE = @WHERECLAUSE + ' and REVENUE.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID '

    if @BATCHID is not null
        set @WHERECLAUSE = @WHERECLAUSE + ' and REVENUEREFERENCE.BATCHNUMBER = @BATCHNUMBER ';

    if @ADDEDBY is not null or len(@ADDEDBY) > 0
    begin
        set @SQLTOEXEC = @SQLTOEXEC + ' inner join dbo.CHANGEAGENT on CHANGEAGENT.ID = REVENUE.ADDEDBYID ';
        set @WHERECLAUSE = @WHERECLAUSE + ' and CHANGEAGENT.USERNAME like ''%'' + @ADDEDBY + ''%'' ';
    end

    if @PAYMENTMETHODCODE is not null
        set @WHERECLAUSE = @WHERECLAUSE + ' and (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = case
                        when @PAYMENTMETHODCODE = 1 then 1
                        when @PAYMENTMETHODCODE = 2 then 0
                        when @PAYMENTMETHODCODE = 8 then 2
                        when @PAYMENTMETHODCODE = 32 then 10
                        end) ';

    if @CARDTYPECODEID is not null
        set @WHERECLAUSE = @WHERECLAUSE + ' and CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID = @CARDTYPECODEID ';

    if @STARTDATE is not null
        set @WHERECLAUSE = @WHERECLAUSE + ' and cast(REVENUE.DATE as datetime) >= @STARTDATE ';
    if @ENDDATE is not null
        set @WHERECLAUSE = @WHERECLAUSE + ' and cast(REVENUE.DATE as datetime) <= @ENDDATE ';

    if @REFERENCE is not null and len(@REFERENCE) > 0
        set @WHERECLAUSE = @WHERECLAUSE + ' and REVENUEREFERENCE.REFERENCE like ''%'' + @REFERENCE + ''%'' ';

    if @OTHERPAYMENTMETHODCODEID is not null
        set @WHERECLAUSE = @WHERECLAUSE + ' and OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID ';

    if @APPLICATIONCODE is not null
        set @WHERECLAUSE = @WHERECLAUSE + ' and REVENUE.ID in (select REVENUEID from REVENUESPLIT where (APPLICATIONCODE = 3 and TYPECODE = 2 and @APPLICATIONCODE = 5) or (not (APPLICATIONCODE = 3 and TYPECODE = 2) and APPLICATIONCODE = @APPLICATIONCODE)) ';

    set @SQLTOEXEC = @SQLTOEXEC + ' outer apply (select isnull((select MAX(case when T2.TYPECODE = 2 and T2.APPLICATIONCODE = 3 then ''Recurring membership'' else T2.APPLICATION end) from dbo.FINANCIALTRANSACTIONLINEITEM T3 inner join dbo.REVENUESPLIT_EXT T2 on T3.ID = T2.ID where T3.FINANCIALTRANSACTIONID = REVENUE.ID  group by T3.FINANCIALTRANSACTIONID  having COUNT(T3.FINANCIALTRANSACTIONID) = 1),''Multiple'') as APPLCATION) T1 ';        

    set @SQLTOEXEC = @SQLTOEXEC + @WHERECLAUSE + ' order by [REVENUE].[TYPE] asc, [REVENUE].[DATE] desc '
    exec sp_executesql @SQLTOEXEC,
        N'@MAXROWS int, @BANKACCOUNTSYSTEMID uniqueidentifier, @TRANSACTIONCURRENCYID uniqueidentifier, @POSTSTATUSCODE tinyint, @BATCHNUMBER nvarchar(100), @PAYMENTMETHODCODE tinyint, @CARDTYPECODEID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime,@OTHERPAYMENTMETHODCODEID uniqueidentifier,@APPLICATIONCODE tinyint, @REFERENCE nvarchar(255), @ADDEDBY nvarchar(128), @CURRENTAPPUSERID uniqueidentifier',
        @MAXROWS=@MAXROWS,@BANKACCOUNTSYSTEMID=@BANKACCOUNTSYSTEMID,@TRANSACTIONCURRENCYID=@TRANSACTIONCURRENCYID,@POSTSTATUSCODE=@POSTSTATUSCODE,@BATCHNUMBER=@BATCHNUMBER, @PAYMENTMETHODCODE=@PAYMENTMETHODCODE,@CARDTYPECODEID=@CARDTYPECODEID,@STARTDATE=@STARTDATE,@ENDDATE=@ENDDATE,@OTHERPAYMENTMETHODCODEID=@OTHERPAYMENTMETHODCODEID,@APPLICATIONCODE=@APPLICATIONCODE, @REFERENCE=@REFERENCE, @ADDEDBY=@ADDEDBY,@CURRENTAPPUSERID=@CURRENTAPPUSERID

    return 0;