vgrabovets
9/30/2016 - 3:28 PM

user_service, orders, user_service_token, pay_system_order_response

user_service, orders, user_service_token, pay_system_order_response

SELECT 
  us.user_id,
  us.service_id,
  us.status,
  o.type,
  o.order_id,
  FROM_UNIXTIME(date_start) start_date,
  FROM_UNIXTIME(date_finish) end_date,
  year(adddate(FROM_UNIXTIME(date_finish), -2)) * 100 + month(adddate(FROM_UNIXTIME(date_finish), -2)) month_id_prolong,
  us.geo,
  o.date date,
  o.description,
  s.parent_id,
  ust.autorenew,
  case when psor.order_id is not null then 1 else 0 end token,
  card_exp
FROM 
  billing.user_service us
  LEFT JOIN billing.orders o ON us.order_id = o.order_id
  LEFT JOIN megogo2.service s ON o.service_id = s.service_id
  LEFT JOIN billing.user_service_token ust ON ust.user_id = us.user_id AND ust.service_id = s.parent_id
  left join billing.pay_system_order_response psor on ust.token_id = psor.order_id
WHERE 
  s.type NOT IN (1, 5) AND
  o.closed = 1 AND 
  o.date >= '2016-07-01'