PaymentSqlDao.sql.stg

group PaymentSqlDao: EntitySqlDao;


tableFields(prefix) ::= <<
  <prefix>account_id
, <prefix>invoice_id
, <prefix>payment_method_id
, <prefix>amount
, <prefix>effective_date
, <prefix>currency
, <prefix>payment_status
, <prefix>ext_first_payment_ref_id
, <prefix>ext_second_payment_ref_id
, <prefix>created_by
, <prefix>created_date
, <prefix>updated_by
, <prefix>updated_date
>>

tableValues() ::= <<
  :account_id
, :invoice_id
, :payment_method_id
, :amount
, :effective_date
, :currency
, :payment_status
, :ext_first_payment_ref_id
, :ext_second_payment_ref_id
, :created_by
, :created_date
, :updated_by
, :updated_date
>>


getPayment() ::= <<
    SELECT <tableFields()>
    , record_id as payment_number
      FROM payments
    WHERE id = :id
    <AND_CHECK_TENANT()>
    ;
>>

getLastPaymentForAccountAndPaymentMethod() ::= <<
    SELECT <paymentFields()>
    , record_id as payment_number
      FROM payments
    WHERE account_id = :accountId AND payment_method_id = :paymentMethodId
    <AND_CHECK_TENANT()>
    ORDER BY effective_date desc limit 1;
>> 

getPaymentsForInvoice() ::= <<
    SELECT <paymentFields()>
    , record_id as payment_number
      FROM payments
    WHERE invoice_id = :invoiceId
    <AND_CHECK_TENANT()>
    ;
>>

getPaymentsForAccount() ::= <<
    SELECT <paymentFields()>
    , record_id as payment_number
      FROM payments
    WHERE account_id = :accountId
    <AND_CHECK_TENANT()>
    ;
>>


updatePaymentStatusAndExtRef() ::= <<
    UPDATE payments
    SET payment_status = :paymentStatus,
        ext_first_payment_ref_id = :extFirstPaymentRefId,
        ext_second_payment_ref_id = :extSecondPaymentRefId
    WHERE id = :id
    <AND_CHECK_TENANT()>
    ;
>>

updatePaymentAmount() ::= <<
    UPDATE payments
    SET amount = :amount
    WHERE id = :id
    <AND_CHECK_TENANT()>
    ;
>>


historyTableName() ::= "payment_history"