InvoicePaymentSqlDao.sql.stg

group InvoicePayment: EntitySqlDao;


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

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

getByPaymentId() ::= <<
  SELECT <invoicePaymentFields()>
  FROM invoice_payments
  WHERE payment_id = :paymentId
  <AND_CHECK_TENANT()>
  ;
>>


getPaymentsForCookieId() ::= <<
  SELECT <invoicePaymentFields()>
  FROM invoice_payments
  WHERE payment_cookie_id = :paymentCookieId
  <AND_CHECK_TENANT()>
  ;
>>

getPaymentsForInvoice() ::= <<
  SELECT <invoicePaymentFields()>
  FROM invoice_payments
  WHERE invoice_id = :invoiceId
  <AND_CHECK_TENANT()>
  ;
>>

notifyOfPayment() ::= <<
  INSERT INTO invoice_payments(<invoicePaymentFields()>)
  VALUES(:id, :type, :invoiceId, :paymentId, :paymentDate, :amount, :currency,
         :paymentCookieId, :linkedInvoicePaymentId, :userName, :createdDate, :accountRecordId, :tenantRecordId);
>>

getInvoicePayments() ::= <<
    SELECT <invoicePaymentFields()>
    FROM invoice_payments
    WHERE payment_id = :paymentId
    <AND_CHECK_TENANT()>
    ;
>>

getRemainingAmountPaid() ::= <<
    SELECT SUM(amount)
    FROM invoice_payments
    WHERE (id = :invoicePaymentId OR linked_invoice_payment_id = :invoicePaymentId)
    <AND_CHECK_TENANT()>
    ;
>>

getAccountIdFromInvoicePaymentId() ::= <<
    SELECT account_id
    FROM invoice_payments 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 <invoicePaymentFields("ip.")>
    FROM invoice_payments 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 <invoicePaymentFields()>
    FROM invoice_payments
    WHERE type = 'CHARGED_BACK'
    AND linked_invoice_payment_id IN (SELECT id FROM invoice_payments WHERE payment_id = :paymentId)
    <AND_CHECK_TENANT()>
    ;
>>