CREATE PROCEDURE [dbo].[get_payments] @date_start DATETIME
	,@date_end DATETIME
	,@payable_id INT = 0
	,@transaction_reference VARCHAR(100) = NULL
	,@channel_transaction_reference VARCHAR(100) = NULL
	,@merchant_customer_id VARCHAR(100) = NULL
	,@masked_card_pan VARCHAR(20) = NULL
	,@merchant_code VARCHAR(50) = NULL
	,@response_code VARCHAR(1024) = NULL
	,@channel VARCHAR(10) = NULL
	,@retrieval_reference_number VARCHAR(20) = NULL
	,@bank_code VARCHAR(10) = NULL
	,@acquirer_code VARCHAR(10) = NULL
	,@amount BIGINT = 0
	,@count INT = 0
	,@page_num INT
	,@page_size INT
AS
SET NOCOUNT ON
SET NOCOUNT ON

DECLARE @response_table TABLE (response_code VARCHAR(8000))

INSERT INTO @response_table
SELECT Item
FROM Split(@response_code, ',')

IF @page_size IS NULL
    BEGIN
        IF @count <= 0
            BEGIN
                SELECT COUNT(*) AS cnt
                    FROM payments_notification p(NOLOCK)
                    INNER JOIN payables py(NOLOCK) ON p.payable_id = py.id
                    INNER JOIN @response_table resp ON (
                            @response_code IS NULL
                            OR p.response_code = resp.response_code
                            )
                    WHERE
                        date_of_payment >= @date_start AND date_of_payment < @date_end AND
                        (@payable_id = 0 OR @payable_id IS NULL  OR p.payable_id = @payable_id) AND
                        (@channel IS NULL OR p.channel = @channel) AND
                        (@merchant_code IS NULL OR p.merchant_code = @merchant_code) AND
                        (@transaction_reference IS NULL OR p.transaction_reference = @transaction_reference) AND
                        (@channel_transaction_reference IS NULL OR p.channel_transaction_reference = @channel_transaction_reference) AND
                        (@masked_card_pan IS NULL OR p.masked_card_pan = @masked_card_pan) AND
                        (@merchant_customer_id IS NULL OR p.merchant_customer_id = @merchant_customer_id) AND
                        (@bank_code IS NULL OR p.bank_code = @bank_code)AND
                        (@retrieval_reference_number IS NULL OR p.retrieval_reference_number = @retrieval_reference_number)AND
                        (@acquirer_code IS NULL OR p.acquirer_code = @acquirer_code)AND
                        (@amount =0 OR @amount IS NULL OR p.amount = @amount)
                    option(recompile)
            END
        ELSE
            BEGIN
                SELECT  @count as cnt
            END

        SELECT
            p.*,
            py.name,
            py.code,
            py.fee_type
            FROM payments_notification p (nolock)
            INNER JOIN payables py (nolock)
            ON p.payable_id = py.id
            INNER JOIN @response_table resp
            ON (@response_code IS NULL OR p.response_code=resp.response_code)
            WHERE
                date_of_payment >= @date_start AND date_of_payment < @date_end AND
                (@payable_id = 0 OR @payable_id IS NULL  OR p.payable_id = @payable_id) AND
                (@channel IS NULL OR p.channel = @channel) AND
                (@merchant_code IS NULL OR p.merchant_code = @merchant_code) AND
                (@transaction_reference IS NULL OR p.transaction_reference = @transaction_reference) AND
                (@channel_transaction_reference IS NULL OR p.channel_transaction_reference = @channel_transaction_reference) AND
                (@masked_card_pan IS NULL OR p.masked_card_pan = @masked_card_pan) AND
                (@merchant_customer_id IS NULL OR p.merchant_customer_id = @merchant_customer_id) AND
                (@bank_code IS NULL OR p.bank_code = @bank_code)AND
                (@retrieval_reference_number IS NULL OR p.retrieval_reference_number = @retrieval_reference_number)AND
                (@acquirer_code IS NULL OR p.acquirer_code = @acquirer_code)AND
                (@amount =0 OR @amount IS NULL OR p.amount = @amount)
            ORDER BY date_of_payment desc
            option(recompile)
    END
ELSE
    BEGIN
        IF @count <= 0
            BEGIN
                SELECT
                    COUNT(*) AS cnt
                    FROM payments_notification p (nolock)
                    INNER JOIN payables py (nolock)
                    ON p.payable_id = py.id
                    INNER JOIN @response_table resp
                    ON (@response_code IS NULL OR p.response_code=resp.response_code)
                    WHERE
                        date_of_payment >= @date_start AND date_of_payment < @date_end AND
                        (@payable_id = 0 OR @payable_id IS NULL  OR p.payable_id = @payable_id) AND
                        (@channel IS NULL OR p.channel = @channel) AND
                        (@merchant_code IS NULL OR p.merchant_code = @merchant_code) AND
                        (@transaction_reference IS NULL OR p.transaction_reference = @transaction_reference) AND
                        (@channel_transaction_reference IS NULL OR p.channel_transaction_reference = @channel_transaction_reference) AND
                        (@masked_card_pan IS NULL OR p.masked_card_pan = @masked_card_pan) AND
                        (@merchant_customer_id IS NULL OR p.merchant_customer_id = @merchant_customer_id) AND
                        (@bank_code IS NULL OR p.bank_code = @bank_code)AND
                        (@retrieval_reference_number IS NULL OR p.retrieval_reference_number = @retrieval_reference_number)AND
                        (@acquirer_code IS NULL OR p.acquirer_code = @acquirer_code)AND
                        (@amount =0 OR @amount IS NULL OR p.amount = @amount)
                    option(recompile)
            END
        ELSE
            BEGIN
                SELECT  @count as cnt
            END

        SET @page_num = ABS(@page_num)
        SET @page_size = ABS(@page_size)
        IF @page_num < 1
            SET @page_num = 1
        IF @page_size < 1
            SET @page_size = 1
        DECLARE @offset INT
        SET @offset = (@page_num - 1) * @page_size

        SELECT
            p.*,
            py.name,
            py.code,
            py.fee_type
            FROM payments_notification p (nolock)
            INNER JOIN payables py (nolock)
            ON p.payable_id = py.id
            INNER JOIN @response_table resp
            ON (@response_code IS NULL OR p.response_code=resp.response_code)
            WHERE
            date_of_payment >= @date_start AND date_of_payment < @date_end AND
            (@payable_id = 0 OR @payable_id IS NULL  OR p.payable_id = @payable_id) AND
            (@channel IS NULL OR p.channel = @channel) AND
            (@merchant_code IS NULL OR p.merchant_code = @merchant_code) AND
            (@transaction_reference IS NULL OR p.transaction_reference = @transaction_reference) AND
            (@channel_transaction_reference IS NULL OR p.channel_transaction_reference = @channel_transaction_reference) AND
            (@masked_card_pan IS NULL OR p.masked_card_pan = @masked_card_pan) AND
            (@merchant_customer_id IS NULL OR p.merchant_customer_id = @merchant_customer_id) AND
            (@bank_code IS NULL OR p.bank_code = @bank_code)AND
            (@retrieval_reference_number IS NULL OR p.retrieval_reference_number = @retrieval_reference_number)AND
            (@acquirer_code IS NULL OR p.acquirer_code = @acquirer_code)AND
            (@amount =0 OR @amount IS NULL OR p.amount = @amount)
        ORDER BY p.date_of_payment desc OFFSET @offset ROWS FETCH NEXT @page_size ROWS ONLY
        option(recompile)
    END
GO