InvoiceSqlDao.sql.stg

Home / invoice / src / main / resources / com / ning / billing / invoice / dao / InvoiceSqlDao.sql.stg

64 lines | 1.238 kB Blame History Raw Download

group InvoiceDao: EntitySqlDao;

tableName() ::= "invoices"

tableFields(prefix) ::= <<
  <prefix>account_id
, <prefix>invoice_date
, <prefix>target_date
, <prefix>currency
, <prefix>migrated
, <prefix>created_by
, <prefix>created_date
>>

tableValues() ::= <<
  :accountId
, :invoiceDate
, :targetDate
, :currency
, :migrated
, :createdBy
, :createdDate
>>

extraTableFieldsWithComma(prefix) ::= <<
, <prefix>record_id as invoice_number
>>

getInvoicesBySubscription() ::= <<
  SELECT <allTableFields("i.")>
  FROM <tableName()> i
  JOIN invoice_items ii ON i.id = ii.invoice_id
  WHERE ii.subscription_id = :subscriptionId AND i.migrated = '0'
  <AND_CHECK_TENANT("i.")>
  <AND_CHECK_TENANT("ii.")>
  ;
>>

searchInvoices() ::= <<
select SQL_CALC_FOUND_ROWS
<allTableFields("t.")>
from <tableName()> t
where 1 = 1
and (
     <idField("t.")> = :searchKey
  or <recordIdField("t.")> = :searchKey
  or t.account_id = :searchKey
  or t.currency = :searchKey
)
<AND_CHECK_TENANT("t.")>
order by <recordIdField("t.")> ASC
limit :offset, :rowCount
;
>>

getInvoiceIdByPaymentId() ::= <<
  SELECT i.id
    FROM <tableName()> i, invoice_payments ip
   WHERE ip.invoice_id = i.id
     AND ip.payment_id = :paymentId
   <AND_CHECK_TENANT("i.")>
   <AND_CHECK_TENANT("ip.")>
>>