SELECT if(aa.id IS NOT NULL, 'Si', 'No') AS Asignada, aa.id AS id_asignacion,
       if(a.id IS NOT NULL, 'Si', 'No') AS Ejecutada, a.id as id_ejecucion,
       a.in_cancelacion, ct.tipo_cancelacion as tipo_cancelacion_actividad,
       if(vra.id_actividad IS NOT NULL, 'Si', 'No') AS Reporte,
       aa.created_at AS fe_asignacion, uas.name AS user_asignacion, aa.fecha_hora AS fe_asignada, aa.co_oficina, o.unidad AS tipo_oficina,
       o.nombre AS nombre_oficina, aa.nombre_gestor, aa.telefono_gestor, aa.email_gestor, aa.cliente, aa.direccion,
       aa.observaciones as observaciones_asignada, ta.tipo, sa.subtipo,
       o.latitud as latitud_oficina, o.longitud as longitud_oficina, o.co_dan, o.no_dan, o.co_dt, o.no_dt, o.store,
       ua.id as delegado_user_id, ua.name AS nombre_delegado,
       li.created_at AS fe_checkin, li.latitude AS latitud_checkin, li.longitude AS longitud_checkin,
       round(st_distance_sphere(POINT(lo.longitude, lo.latitude), POINT(o.longitud, o.latitud)), 0) AS dist_checkin,
       lo.created_at AS fe_checkout, li.latitude AS latitud_checkout, lo.longitude AS longitud_checkout,
       round(st_distance_sphere(POINT(lo.longitude, lo.latitude), POINT(o.longitud, o.latitud)), 0) AS dist_checkout,
       TIMESTAMPDIFF(SECOND, li.created_at, lo.created_at) AS segundos_actividad,
       sec_to_time(TIMESTAMPDIFF(SECOND, li.created_at, lo.created_at)) AS duracion_actividad,
       TIMESTAMPDIFF(SECOND, aa.fecha_hora, li.created_at) AS segundos_diferencia_asignacion,
       sec_to_time(TIMESTAMPDIFF(SECOND, aa.fecha_hora, li.created_at)) AS diferencia_asignacion,
       vra.*, a.reporte_edit_at, (aa.deleted_at is not null) as deleted
FROM actividad_asignadas aa LEFT JOIN actividades a
     ON aa.id = a.id_actividad_asignada
JOIN users uas
     ON aa.action_user_id = uas.id
JOIN users ua
     ON aa.id_user = ua.id
JOIN vw_oficina o
     ON aa.co_oficina = o.co_oficina
JOIN subtipo_actividades sa
     ON aa.id_subtipo_actividad = sa.id
JOIN tipo_actividades ta
     ON sa.id_tipo_actividad = ta.id
LEFT JOIN locations li
     ON a.id_location_in = li.id
LEFT JOIN locations lo
     ON a.id_location_out = lo.id
left join cancelacion_tipos ct
    on a.id_tipo_cancelacion = ct.id
left join vw_report_answers vra
     on a.id = vra.id_actividad
