EventSqlDao.sql.stg

group EventSqlDao;

fields(prefix) ::= <<
    <prefix>event_id,
    <prefix>event_type,
    <prefix>user_type,
    <prefix>requested_dt,
    <prefix>effective_dt,
    <prefix>subscription_id,
    <prefix>plan_name,
    <prefix>phase_name,
    <prefix>plist_name,
    <prefix>current_version,
    <prefix>is_active,
    <prefix>created_by,
    <prefix>created_date,
    <prefix>updated_by,
    <prefix>updated_date
>>

getEventById() ::= <<
  select id, <fields()>
  from entitlement_events
  where
      event_id = :event_id
  ;
>>

insertEvent() ::= <<
    insert into entitlement_events (<fields()>)
    values (
      :event_id
      , :event_type
      , :user_type
      , :requested_dt
      , :effective_dt
      , :subscription_id
      , :plan_name
      , :phase_name
      , :plist_name
      , :current_version
      , :is_active
      , :userName
      , :createdDate
      , :userName
      , :updatedDate
    );   
>>

unactiveEvent() ::= <<
    update entitlement_events
    set
      is_active = 0
      , updated_by = :userName
      , updated_date = :updatedDate
    where
      event_id = :event_id
    ;
>>

reactiveEvent() ::= <<
    update entitlement_events
    set
      is_active = 1
      , updated_by = :userName
      , updated_date = :updatedDate
    where
      event_id = :event_id
    ;
>>

getFutureActiveEventForSubscription() ::= <<
    select id, <fields()>
    from entitlement_events
    where
      subscription_id = :subscription_id
      and is_active = 1
      and effective_dt > :now
    order by
      effective_dt asc
      , created_date asc
      , requested_dt asc
      , id asc
    ;
>> 

getEventsForSubscription(subscription_id) ::= <<
    select id, <fields()>
    from entitlement_events
    where
      subscription_id = :subscription_id
    order by
      effective_dt asc
      , created_date asc
      , requested_dt asc
      , id asc
    ;      
>>