vgrabovets
10/4/2016 - 7:52 AM

future subscriptions

future subscriptions

SELECT
  main.user_id,
  main.actual,
  main.future,
  from_unixtime(us.`date_init`), 
  from_unixtime(us.`date_start`), 
  from_unixtime(us.`date_finish`), 
  us.`status`, 
  case when UNIX_TIMESTAMP(now()) between us.date_start and us.date_finish then 1 else 0 end should_be_active,
  o.type,
  o.closed,
  o.description,
  o.admin_description
from
  (
  SELECT 
    us.`user_id`, 
    sum(IF(us.`status`='active' AND us.`date_start`<unix_timestamp(now()) AND us.`date_finish`>unix_timestamp(now()) ,1,0)) as actual, 
    sum(IF(us.`status`='active' AND us.`date_start`>unix_timestamp(now()),1,0)) as future
  FROM billing.user_service us 
    LEFT JOIN megogo2.service ms ON ms.service_id=us.service_id 
  WHERE ms.type=2 
  group by us.`user_id` 
  having actual=0 and future>0
  ) main

  left join billing.orders o on o.user_id = main.user_id
  LEFT JOIN billing.user_service us ON us.`order_id`=o.`order_id` 
  LEFT JOIN megogo2.service ms ON ms.service_id=us.service_id 

WHERE 
  ms.type=2

HAVING
  should_be_active = 1