Consultas

# Nombre Código Valor Acción
1
 Servicios a punto de perderse
before_suspention
 Select s.id as altan_service_id, sc.iccid, sc.msisdn, u.name, u.last_name, u.email, s.altan_expires_at from public.altan_services as s inner join public.sim_cards as sc on s.id = sc.altan_service_id inner join public.users as u on s.user_id = u.id where date(altan_expires_at) >= date(now()) - interval '3' month and date(altan_expires_at) < date(now())
2
 Servicios sin recarga por 3 meses
idle_services
 Select s.id as altan_service_id, sc.iccid, sc.msisdn, u.name, u.last_name, u.email, s.altan_expires_at from public.altan_services as s inner join public.sim_cards as sc on s.id = sc.altan_service_id inner join public.users as u on s.user_id = u.id where date(altan_expires_at) < date(now()) - interval '3' month
3
 Servicios sin recarga por 3 meses exactos
idle_services_exact
 Select s.id as altan_service_id, sc.segment_id, seg.name as segment_name, sc.iccid, sc.msisdn, u.name, u.last_name, u.email, s.altan_expires_at from public.altan_services as s inner join public.sim_cards as sc on s.id = sc.altan_service_id inner join public.segments as seg on sc.segment_id = seg.id inner join public.users as u on s.user_id = u.id where date(altan_expires_at) = date(now()) - interval '3' month and sc.segment_id in (2,7,9)
4
 Servicios por vencer en 72 horas
before_expires
 Select s.id as altan_service_id, sc.iccid, sc.msisdn, u.name, u.last_name, u.email, s.altan_expires_at from public.altan_services as s inner join public.sim_cards as sc on s.id = sc.altan_service_id inner join public.users as u on s.user_id = u.id where date(altan_expires_at) >= date(now()) and date(altan_expires_at) <= date(now()) + interval '3' day
5
 Usuarios con más de una línea y alguna de ellas no se recarga
more_than_one_idle
 Select s.id, s.user_id, s.altan_expires_at, s.service_status, u.name, u.last_name, u.email, sc.iccid, sc.msisdn from public.altan_services as s inner join public.sim_cards as sc on s.id = sc.altan_service_id inner join public.users as u on u.id = s.user_id where user_id in (select user_id from public.altan_services group by user_id having count(*) > 1) and date(s.altan_expires_at) < date(now()) order by user_id
6
 Portabilidades fallidas
failed_portabilities
 Select p.id as portability_id, p.status, p.ported_did, s.id as altan_service_id, u.name, u.last_name, u.email, sc.iccid, sc.msisdn FROM public.portabilities as p inner join public.sim_cards as sc on sc.id = p.sim_card_id inner join public.altan_services as s on s.id = sc.altan_service_id inner join public.users as u on u.id = s.user_id where status = 11 OR status = 9 OR status = 5 OR status = 4
7
 Contrataciones en página web
web_purchases
 Select pay.id as payment_id, pay.user_id, u.name, u.last_name, u.email, pay.amount, pay.concept, pay.created_at, seg.name as segment_name, con.altan_service_id, con.bundle_id, bun.name as bundle_name, sc.iccid, sc.msisdn, sc.id as sim_card_id, sc.sim_card_type FROM public.payments as pay inner join public.altan_service_bundle_orders as con on con.id = pay.payable_id inner join public.segments as seg on pay.segment_id = seg.id inner join public.bundles as bun on bun.id = con.bundle_id inner join public.sim_cards as sc on sc.altan_service_id = con.altan_service_id inner join public.users as u on pay.user_id = u.id where pay.payable_type = 'AltanServiceBundleOrder' and pay.status = '1' and pay.payment_method = 11 and pay.created_at >= date(now()) - interval '3' month