InvoicePaymentSqlDao.sql.stg

import "org/killbill/billing/util/entity/dao/EntitySqlDao.sql.stg"

tableName() ::= "invoice_payments"

tableFields(prefix) ::= <<
  <prefix>type
, <prefix>invoice_id
, <prefix>payment_id
, <prefix>payment_date
, <prefix>amount
, <prefix>currency
, <prefix>processed_currency
, <prefix>payment_cookie_id
, <prefix>linked_invoice_payment_id
, <prefix>success
, <prefix>created_by
, <prefix>created_date
>>

tableValues() ::= <<
  :type
, :invoiceId
, :paymentId
, :paymentDate
, :amount
, :currency
, :processedCurrency
, :paymentCookieId
, :linkedInvoicePaymentId
, :success
, :createdBy
, :createdDate
>>

getByAccountRecordId(accountRecordId) ::= <<
select
<allTableFields("t.")>
from <tableName()> t
where (<accountRecordIdField("t.")> = :accountRecordId or (<accountRecordIdField("t.")> is null and :accountRecordId is null))
<andCheckSoftDeletionWithComma("t.")>
AND payment_id IS NOT NULL
<AND_CHECK_TENANT("t.")>
<defaultOrderBy("t.")>
;
>>

getByPaymentId() ::= <<
  SELECT <allTableFields("")>
  FROM <tableName()>
  WHERE payment_id = :paymentId
  <AND_CHECK_TENANT("")>
  <defaultOrderBy("")>
  ;
>>

getPaymentForCookieId() ::= <<
  SELECT <allTableFields("")>
  FROM <tableName()>
  WHERE payment_cookie_id = :paymentCookieId
  <AND_CHECK_TENANT("")>
  <defaultOrderBy("")>
  LIMIT 1
  ;
>>

getAllPaymentsForInvoiceIncludedInit() ::= <<
  SELECT <allTableFields("")>
  FROM <tableName()>
  WHERE invoice_id = :invoiceId
  <AND_CHECK_TENANT("")>
  <defaultOrderBy("")>
  ;
>>

getInvoicePayments() ::= <<
    SELECT <allTableFields("")>
    FROM <tableName()>
    WHERE payment_id = :paymentId
    <AND_CHECK_TENANT("")>
    <defaultOrderBy("")>
    ;
>>

getRemainingAmountPaid() ::= <<
    SELECT SUM(amount)
    FROM <tableName()>
    WHERE (id = :invoicePaymentId OR linked_invoice_payment_id = :invoicePaymentId)
    AND success
    <AND_CHECK_TENANT("")>
    ;
>>

getAccountIdFromInvoicePaymentId() ::= <<
    SELECT i.account_id
    FROM <tableName()> ip
    INNER JOIN invoices i ON i.id = ip.invoice_id
    WHERE ip.id = :invoicePaymentId
    <AND_CHECK_TENANT("i.")>
    <AND_CHECK_TENANT("ip.")>
    ;
>>

getChargeBacksByAccountId() ::= <<
    SELECT <allTableFields("ip.")>
    FROM <tableName()> ip
    INNER JOIN invoices i ON i.id = ip.invoice_id
    WHERE ip.type = 'CHARGED_BACK' AND i.account_id = :accountId
    AND success
    <AND_CHECK_TENANT("i.")>
    <AND_CHECK_TENANT("ip.")>
    ;
>>

getChargebacksByPaymentId() ::= <<
    SELECT <allTableFields("")>
    FROM <tableName()>
    WHERE type = 'CHARGED_BACK'
    AND linked_invoice_payment_id IN (SELECT id FROM invoice_payments WHERE payment_id = :paymentId)
    AND success
    <AND_CHECK_TENANT("")>
    ;
>>

updateAttempt() ::= <<
    UPDATE <tableName()>
    SET payment_id := :paymentId,
    payment_date = :paymentDate,
    amount = :amount,
    currency = :currency,
    processed_currency = :processedCurrency,
    payment_cookie_id = :paymentCookieId,
    linked_invoice_payment_id := :linkedInvoicePaymentId,
    success := :success
    WHERE record_id = :recordId
    <AND_CHECK_TENANT("")>
    ;
>>