BlockingStateSqlDao.sql.stg

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

tableName() ::= "blocking_states"

andCheckSoftDeletionWithComma(prefix) ::= "and <prefix>is_active"

defaultOrderBy(prefix) ::= <<
order by <prefix>effective_date ASC, <recordIdField(prefix)> ASC
>>

tableFields(prefix) ::= <<
  <prefix>blockable_id
, <prefix>type
, <prefix>state
, <prefix>service
, <prefix>block_change
, <prefix>block_entitlement
, <prefix>block_billing
, <prefix>effective_date
, <prefix>is_active
, <prefix>created_by
, <prefix>created_date
, <prefix>updated_by
, <prefix>updated_date
>>


tableValues() ::= <<
  :blockableId
, :type
, :state
, :service
, :blockChange
, :blockEntitlement
, :blockBilling
, :effectiveDate
, :isActive
, :createdBy
, :createdDate
, :updatedBy
, :updatedDate
>>


getBlockingStateForService() ::= <<
select
<allTableFields("")>
from
<tableName()>
where blockable_id = :blockableId
and service = :service
and effective_date \<= :effectiveDate
and is_active
<AND_CHECK_TENANT("")>
-- We want the current state, hence the order desc and limit 1
order by effective_date desc, record_id desc
limit 1
;
>>

getBlockingState() ::= <<
select
<allTableFields("t.")>
from
<tableName()> t
join (
  select max(record_id) record_id
        , service
        from blocking_states
        where blockable_id = :blockableId
        and type = :type
        and effective_date \<= :effectiveDate
        <andCheckSoftDeletionWithComma("")>
        and <accountRecordIdField("")> = :accountRecordId
        <AND_CHECK_TENANT("")>
        group by service
) tmp
on t.record_id = tmp.record_id
where t.type = :type
and <accountRecordIdField("t.")> = :accountRecordId
<AND_CHECK_TENANT("t.")>
<defaultOrderBy("t.")>
;
 >>

getBlockingAllUpToForAccount() ::= <<
select
<allTableFields("t.")>
from
<tableName()> t
join (
  select max(record_id) record_id
        , service
        from blocking_states
        where effective_date \<= :effectiveDate
        <andCheckSoftDeletionWithComma("")>
        and <accountRecordIdField("")> = :accountRecordId
        <AND_CHECK_TENANT("")>
        group by service
) tmp
on t.record_id = tmp.record_id
where <accountRecordIdField("t.")> = :accountRecordId
<AND_CHECK_TENANT("t.")>
<defaultOrderBy("t.")>
;
 >>

getBlockingHistoryForService() ::= <<
select
<allTableFields("")>
from
<tableName()>
where blockable_id = :blockableId
and service = :service
and is_active
<AND_CHECK_TENANT("")>
<defaultOrderBy("")>
;
>>

unactiveEvent() ::= <<
update
<tableName()>
set is_active = false
where id = :id
<AND_CHECK_TENANT("")>
;
>>