BlockingStateSqlDao.sql.stg

group BlockingStateSqlDao: EntitySqlDao;


tableName() ::= "blocking_states"

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


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


getBlockingStateForService() ::= <<
select
<allTableFields()>
from
<tableName()>
where blockable_id = :blockableId
and service = :service
and effective_date \<= :effectiveDate
<AND_CHECK_TENANT()>
-- We want the current state, hence the order desc and limit 1
order by 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 effective_date \<= :effectiveDate
         <AND_CHECK_TENANT()>
         group by service
 ) tmp
 on t.record_id = tmp.record_id
 order by t.record_id asc
 ;
 >>

getBlockingHistoryForService() ::= <<
select
<allTableFields()>
from
<tableName()>
where blockable_id = :blockableId
and service = :service
<AND_CHECK_TENANT()>
order by record_id asc
;
>>

getBlockingHistory() ::= <<
select
<allTableFields()>
from
<tableName()>
where blockable_id = :blockableId
<AND_CHECK_TENANT()>
order by record_id asc
;
>>

getBlockingAll() ::= <<
select
<allTableFields()>
from
<tableName()>
where blockable_id = :blockableId
<AND_CHECK_TENANT()>
order by record_id asc
;
>>