gistfile1.txt

COPY (
SELECT DISTINCT
   r.id,
   c.name AS company,
   rw.week,
   u.code,
   u.full_name AS USER,
   array_agg(DISTINCT lz.description) AS user_location_zone_list,
   array_agg(DISTINCT lsz.description) AS user_location_sub_zone_list,
   r.status,
   r.start_day,
   rd.sequence,
   rd.hour,
   pod.plant_number,
   pod.fiscal_number,
   pod.name || ' - ' || plant_name AS plant_name,
   plz.description AS pod_location_zone,
   plsz.description AS pod_location_sub_zone,
   podc.description AS pod_category,
   rw.creation_date,
   cu.full_name AS creator_user,
   rd.visit_status,
   CASE
      WHEN rd.visit_status = '0' OR rd.visit_status = '1' THEN ''
      WHEN rd.visit_status = '4' THEN coalesce(rd.ev_reason, rd.ev_observation, 'Visita Cancelada Manualmente')
      ELSE coalesce(rd.ev_reason, rd.ev_observation, ev.comments, '')
   END AS reason,
   CASE
      WHEN ev.domain_name = 'Entrega de kits y equipamientos' THEN 'Entrega de kits y equipamientos'
      WHEN rd.tag = 'Avance de cosecha' THEN rd.tag
      ELSE 'ruta'
   END AS tag
FROM
   route_week rw
LEFT JOIN route r ON rw.id = r.route_week_id
LEFT JOIN route_detail rd ON r.id = rd.route_id
LEFT JOIN point_of_delivery pod ON rd.point_of_delivery_id = pod.id
LEFT JOIN point_of_delivery_category podc ON pod.point_of_delivery_category_id = podc.id
LEFT JOIN location_sub_zone plsz ON pod.location_sub_zone_id = plsz.id
LEFT JOIN location_zone plz ON pod.location_zone_id = plz.id
LEFT JOIN company c ON rw.company_id = c.id
LEFT JOIN users u ON rw.user_id = u.id
LEFT JOIN users cu ON rw.creation_user_id = cu.id
LEFT JOIN user_location_sub_zone sz ON u.id = sz.user_id
LEFT JOIN location_sub_zone lsz ON sz.location_sub_zone_id = lsz.id
LEFT JOIN location_zone lz ON lsz.location_zone_id = lz.id
LEFT JOIN event ev ON rd.id = ev.route_detail_id
LEFT JOIN event_type et ON ev.event_type_id = et.id
LEFT JOIN user_location_sub_zone szp ON sz.location_sub_zone_id = szp.location_sub_zone_id AND szp.deleted IS NOT TRUE
WHERE
   rw.deleted IS NOT TRUE
   AND rw.deleted = 'FALSE'
   AND sz.deleted = 'FALSE'
   AND r.deleted = 'FALSE'
   AND rd.deleted = 'FALSE'
   AND rw.start_day >= CAST('2023-11-27 00:00:00' AS TIMESTAMP)
   AND rw.end_day <= CAST('2024-06-30 23:59:59' AS TIMESTAMP)
GROUP BY
   r.id,
   c.name,
   rw.week,
   u.code,
   u.full_name,
   r.status,
   r.start_day,
   rd.sequence,
   rd.hour,
   pod.plant_number,
   pod.fiscal_number,
   pod.name,
   pod.plant_name,
   plz.description,
   plsz.description,
   podc.description,
   rw.creation_date,
   cu.full_name,
   rd.visit_status,
   rd.tag,
   rd.ev_reason,
   ev.domain_name,
   rd.ev_observation,
   ev.comments,
   et.description,
   et.code
ORDER BY
   r.start_day ASC,
   u.full_name ASC
) TO '/home/ubuntu/HDRReport5.csv' WITH CSV HEADER;


SELECT DISTINCT
   r.id,
   c.name AS company,
   rw.week,
   u.code,
   u.full_name AS USER,
   array_agg(DISTINCT lz.description) AS user_location_zone_list,
   array_agg(DISTINCT lsz.description) AS user_location_sub_zone_list,
   r.status,
   r.start_day,
   rd.sequence,
   rd.hour,
   pod.plant_number,
   pod.fiscal_number,
   pod.name || ' - ' || plant_name AS plant_name,
   plz.description AS pod_location_zone,
   plsz.description AS pod_location_sub_zone,
   podc.description AS pod_category,
   rw.creation_date,
   cu.full_name AS creator_user,
   rd.visit_status,
   CASE
      WHEN rd.visit_status = '0' OR rd.visit_status = '1' THEN ''
      WHEN rd.visit_status = '4' THEN coalesce(rd.ev_reason, rd.ev_observation, 'Visita Cancelada Manualmente')
      ELSE coalesce(rd.ev_reason, rd.ev_observation, ev.comments, '')
   END AS reason,
   CASE
      WHEN ev.domain_name = 'Entrega de kits y equipamientos' THEN 'Entrega de kits y equipamientos'
      WHEN rd.tag = 'Avance de cosecha' THEN rd.tag
      ELSE 'ruta'
   END AS tag
FROM
   route_week rw
LEFT JOIN route r ON rw.id = r.route_week_id
LEFT JOIN route_detail rd ON r.id = rd.route_id
LEFT JOIN point_of_delivery pod ON rd.point_of_delivery_id = pod.id
LEFT JOIN point_of_delivery_category podc ON pod.point_of_delivery_category_id = podc.id
LEFT JOIN location_sub_zone plsz ON pod.location_sub_zone_id = plsz.id
LEFT JOIN location_zone plz ON pod.location_zone_id = plz.id
LEFT JOIN company c ON rw.company_id = c.id
LEFT JOIN users u ON rw.user_id = u.id
LEFT JOIN users cu ON rw.creation_user_id = cu.id
LEFT JOIN user_location_sub_zone sz ON u.id = sz.user_id
LEFT JOIN location_sub_zone lsz ON sz.location_sub_zone_id = lsz.id
LEFT JOIN location_zone lz ON lsz.location_zone_id = lz.id
LEFT JOIN event ev ON rd.id = ev.route_detail_id
LEFT JOIN event_type et ON ev.event_type_id = et.id
LEFT JOIN user_location_sub_zone szp ON sz.location_sub_zone_id = szp.location_sub_zone_id AND szp.deleted IS NOT TRUE
WHERE
   rw.deleted IS NOT TRUE
   AND rw.deleted = 'FALSE'
   AND sz.deleted = 'FALSE'
   AND r.deleted = 'FALSE'
   AND rd.deleted = 'FALSE'
   AND rw.start_day >= CAST('2023-11-27 00:00:00' AS TIMESTAMP)
   AND rw.end_day <= CAST('2024-06-30 23:59:59' AS TIMESTAMP)
GROUP BY
   r.id,
   c.name,
   rw.week,
   u.code,
   u.full_name,
   r.status,
   r.start_day,
   rd.sequence,
   rd.hour,
   pod.plant_number,
   pod.fiscal_number,
   pod.name,
   pod.plant_name,
   plz.description,
   plsz.description,
   podc.description,
   rw.creation_date,
   cu.full_name,
   rd.visit_status,
   rd.tag,
   rd.ev_reason,
   ev.domain_name,
   rd.ev_observation,
   ev.comments,
   et.description,
   et.code
ORDER BY
   r.start_day ASC,
   u.full_name ASC
limit 1;