@NamedQuery(
   name="Operation.mailListWithValidLicense",
   query="SELECT DISTINCT o "
      + "FROM Operation o " // Operations for
      + "INNER JOIN o.protocols p "
      + "INNER JOIN p.protocolSchedules ps "
      + "WHERE EXISTS ("
         + "SELECT 'found' "
         + "FROM License l " // a license
         + "WHERE l.surgery = o.surgery " // for the current surgery
         + "AND l.revoked = FALSE " // which is unrevoked
         + "AND CURRENT_DATE BETWEEN l.startDate AND l.expiryDate" // and current
      + ") "
      + "AND p.cancelled = FALSE " // protocols not cancelled
      + "AND ps.submitted = FALSE AND ps.expired = FALSE " // schedules not submitted and not expired
      + "AND CURRENT_TIMESTAMP <= ps.scheduleDate " // not after schedule date
      + "AND ( "
         + "( "
            + "ps.mailed IS NULL " // the schedule isn't mailed
            + "AND CURRENT_TIMESTAMP >= ps.mailingDate " // after mailing date
            + "AND CURRENT_TIMESTAMP < ps.reminderDate " // before reminder date
         + ") OR ( "
            + "ps.reminder IS NULL " // the schedule isn't reminded
            + "AND CURRENT_TIMESTAMP >= ps.reminderDate " // after the reminder date
         + ") "
      + ")"),

select distinct operation0_.id as id1_2_, operation0_.subscription_batch_id as subscri12_2_, operation0_.bounce_reason as bounce_r2_2_, operation0_.date as date3_2_, operation0_.email as email4_2_, operation0_.mail_id_high as mail_id_5_2_, operation0_.mail_id_low as mail_id_6_2_, operation0_.operation_name as operatio7_2_, operation0_.pass as pass8_2_, operation0_.patient_name as patient_9_2_, operation0_.subscription_notification as subscri10_2_, operation0_.surgery_id as surgery13_2_, operation0_.unsubscribe as unsubsc11_2_
from webscore.operation operation0_
inner join webscore.protocol protocols1_ on operation0_.id=protocols1_.operation_id
inner join webscore.protocol_schedule protocolsc2_ on protocols1_.id=protocolsc2_.protocol_id
where (
   exists (
      select 'found'
      from webscore.license license3_
      where license3_.surgery_id=operation0_.surgery_id
      and license3_.revoked=false
      and (
         CURRENT_DATE between license3_.start_date and license3_.expiry_date
      )
   )
)
and protocols1_.cancelled=false
and protocolsc2_.submitted=false
and protocolsc2_.expired=false
and CURRENT_TIMESTAMP<=protocolsc2_.schedule_date
and (
   (
      protocolsc2_.mailed is null
   )
   and CURRENT_TIMESTAMP>=protocolsc2_.mailing_date
   and CURRENT_TIMESTAMP<protocolsc2_.reminder_date
   or (
      protocolsc2_.reminder is null
   )
   and CURRENT_TIMESTAMP>=protocolsc2_.reminder_date
)

and (
   (
      protocolsc2_.mailed is null
      and CURRENT_TIMESTAMP>=protocolsc2_.mailing_date
      and CURRENT_TIMESTAMP<protocolsc2_.reminder_date
   )
   or (
      protocolsc2_.reminder is null
      and CURRENT_TIMESTAMP>=protocolsc2_.reminder_date
   )
)