InvoicePaymentSqlDao.sql.stg

group InvoicePayment: EntitySqlDao;

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
>>

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

getPaymentsForCookieId() ::= <<
  SELECT <allTableFields()>
  FROM <tableName()>
  WHERE payment_cookie_id = :paymentCookieId
  <AND_CHECK_TENANT()>
  <defaultOrderBy()>
  ;
>>

getPaymentsForInvoice() ::= <<
  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_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_CHECK_TENANT()>
    ;
>>


updateAttempt() ::= <<
    UPDATE <tableName()>
    SET success = true,
    payment_date = :paymentDate,
    amount = :amount,
    processed_currency = :processedCurrency
    WHERE record_id = :recordId
    <AND_CHECK_TENANT("")>
    ;
>>