monsanto_query

SELECT
    distinct rw.id,
    coalesce(rw.disapproval_comment) as disapproval_comment,
    rw.end_day,
    rw.friday,
    rw.monday,
    rw.saturday,
    rw.start_day,
    rw.sunday,
    rw.thursday,
    rw.tuesday,
    rw.wednesday,
    rw.week,
    c.name as company,
    u.full_name as user,
    rw.status,
    array_agg(distinct lsz.description) as location_sub_zone_list,
    array_agg(distinct lz.description) as location_zone_list,
    array_agg(distinct podlsz.description) as pod_location_sub_zone_list,
    array_agg(distinct podlz.description) as pod_location_zone_list,
    e.id,
    e.comments,
    e.event_date,
    e.latitude as event_latitude,
    e.longitude as event_longitude,
    coalesce(et.description, '') as description,
    coalesce(et.code, '') as code,
    rd.id as route_detail_id,
    rd.visit_status,
    coalesce(rd.ev_reason, '') as ev_reason,
    pod.name,
    pod.plant_name,
    pod.plant_number,
    pod.latitude as pod_latitude,
    pod.longitude as pod_longitude,
    podc.description as category,
    coalesce(et.description, '') as event
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 company c ON rw.company_id = c.id
    LEFT JOIN users u ON rw.user_id = u.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 location_sub_zone podlsz ON pod.location_sub_zone_id = podlsz.id
    LEFT JOIN location_zone podlz ON pod.location_zone_id = podlz.id
    LEFT JOIN event e ON e.route_detail_id = rd.id
    LEFT JOIN event_type et ON e.event_type_id = et.id
    AND et.code != '_ha'
WHERE
    rw.deleted is not true
    AND rw.deleted = 'FALSE'
    AND sz.deleted = 'FALSE'
    AND r.deleted = 'FALSE'
    AND rd.tag ISNULL
    AND rd.deleted = 'FALSE'
    AND rd.visit_status != '4'
    AND e.deleted = 'FALSE'
    AND rw.start_day >= '2023-01-23 00:00:00-03'
    AND rw.end_day <= '2023-01-29 23:59:59-03'
GROUP BY
    rw.id,
    rw.approval_date,
    rw.disapproval_comment,
    rw.end_day,
    rw.friday,
    rw.monday,
    rw.saturday,
    rw.start_day,
    rw.sunday,
    rw.thursday,
    rw.tuesday,
    rw.wednesday,
    rw.week,
    c.name,
    u.full_name,
    rw.status,
    e.id,
    et.description,
    et.code,
    rd.ev_reason,
    podc.description,
    pod.name,
    pod.plant_name,
    pod.plant_number,
    rd.id,
    rd.visit_status,
    pod.latitude,
    pod.longitude
ORDER BY
    rw.start_day ASC,
    e.event_date ASC,
    u.full_name ASC