Skip to content

Instantly share code, notes, and snippets.

@josemoralesp
Last active April 25, 2019 15:24
Show Gist options
  • Save josemoralesp/44308acb9fb537112b05c45a893c1b96 to your computer and use it in GitHub Desktop.
Save josemoralesp/44308acb9fb537112b05c45a893c1b96 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment