payment_report.sql

WITH payments AS(
    SELECT
        p.id,
        p.name,
        p.payment_date,
        p.payment_type,
        p.partner_type,
        s.name AS partner,
        p.currency_id,
        p.amount,
        p.state
    FROM
        account_payment AS p
    INNER JOIN
        res_partner AS s ON s.id = p.partner_id
    WHERE
        p.payment_type = 'outbound'
), paid AS (
SELECT
    p.id,
    i.number AS numbers,
    i.currency_id AS icurrency,
    i.date_invoice AS invoice_date,
    i.date AS date,
    c.name AS user,
    ROUND(CAST(sum(CASE WHEN
        p.currency_id = 2 AND i.currency_id != p.currency_id THEN
        (abs(rec.amount) / (abs(deb_line.balance) / abs(deb_line.amount_currency)))
    WHEN
        p.currency_id = 2 AND i.currency_id = p.currency_id THEN
        ((abs(rec.amount_currency)))
    WHEN
        p.currency_id = 33 AND i.currency_id != p.currency_id THEN
        ((abs(rec.amount)))
    ELSE (abs(rec.amount)) END) AS numeric), 4) AS amount_currency
FROM
    account_partial_reconcile AS rec
INNER JOIN
    account_move_line AS cre_line ON cre_line.id = rec.credit_move_id AND
                                     cre_line.invoice_id IS NOT NULL
INNER JOIN
    account_move_line AS deb_line ON deb_line.id = rec.debit_move_id AND
                                     deb_line.payment_id IS NOT NULL
INNER JOIN
    account_invoice AS i ON i.id=cre_line.invoice_id AND
                            i.type = 'in_invoice'
INNER JOIN
    res_users AS u ON u.id = i.user_id
INNER JOIN
    res_partner AS c ON c.id = u.partner_id
INNER JOIN
    account_payment AS p ON p.id = deb_line.payment_id AND
                            p.payment_type = 'outbound'
GROUP BY
    p.id,
    i.number,
    i.currency_id,
    i.date_invoice,
    i.date,
    c.name
    ),
    sum_paid AS (
SELECT
    p.id,
    sum(p.amount_currency) AS total
FROM
    paid AS p
GROUP BY
    p.id
)
SELECT
    p1.id,
    p1.name,
    p1.partner,
    p1.currency_id,
    p1.payment_date,
    p1.payment_type,
    p1.partner_type,
    p1.amount AS payment_amount,
    p2.numbers,
    p2.icurrency,
    p2.invoice_date,
    p2.date,
    p2.user,
    COALESCE(p2.amount_currency, 0) AS used_amount,
    p1.amount - COALESCE((SELECT total FROM sum_paid WHERE id=p1.id), 0) AS available_amount,
    p1.state

FROM
    payments AS p1
LEFT OUTER JOIN
    paid AS p2 ON p2.id = p1.id