Untitled
unknown
plain_text
23 days ago
28 kB
9
Indexable
EXPLAIN analyze
select
to_char(orders."createdAt", 'DD-MM-YYYY HH24:MI:SS') as "Ngày tạo đơn hàng",
orders."orderNumber" as "Mã đơn hàng",
orders."orderNumberClient" as "Mã đơn hàng tham chiếu",
case
when false then anon_1.curr_station_manifest
else station_orig.name
end as "Trạm gốc",
case
when false then anon_1.next_station_manifest
else station_curr.name
end as "Trạm xử lý",
coalesce(customer_businesses."customerShortName", 'B2C') as "Khách hàng",
orders.remark as "Ghi chú đơn hàng",
case
when (orders."statusId" = 7) then 'Mới'
when (orders."statusId" = 10) then 'Đã xác nhận'
when (orders."statusId" = 11) then 'Đã dịch lọc'
when (orders."statusId" = 16) then 'Đã nhận hàng'
when (orders."statusId" = 18) then 'Delay'
when (orders."statusId" = 23) then case
when (station_curr.name != '') then 'Check-in station ' || station_curr.name
else 'Check-in station'
end
when (orders."statusId" = 26) then 'Đóng gói'
when (orders."statusId" = 33) then 'Đang giao hàng'
when (orders."statusId" = 34) then case
when (orders."hubTo" != '') then 'Chuyển đến trạm ' || orders."hubTo"
else 'Chuyển đến trạm ' || station_curr.name
end
when (orders."statusId" = 36) then 'Chuyển đến Gateway ' || station_curr.name
when (orders."statusId" = 38) then 'Giao hàng không thành công'
when (orders."statusId" = 39) then 'Đã giao hàng'
when (orders."statusId" = 40) then 'Giao hàng thành công'
when (orders."statusId" = 43) then 'Chưa có trạng thái'
when (orders."statusId" = 50) then 'Trả giao hàng'
when (orders."statusId" = 51) then 'Xác nhận cần hoàn hàng'
when (orders."statusId" = 52) then 'Đã phân công trả hàng'
when (orders."statusId" = 54) then 'Trả hàng thành công'
when (orders."statusId" = 55) then 'Đã giao thành công và đang thu hồi'
when (orders."statusId" = 56) then 'Đã giao và thu hồi thành công'
when (orders."statusId" = 68) then 'Chờ trung chuyển'
when (orders."statusId" = 69) then 'Đang trung chuyển'
when (orders."statusId" = 80) then 'Hold'
when (orders."statusId" = 101) then 'Huỷ'
else 'Không xác định'
end as "Trạng thái đơn hàng",
coalesce(anon_2.count_order_id, 0) as "Số lần phát",
coalesce(orders."orderWeightKg", 0) as "Trọng lượng (kg)",
reasons.name as "Lý do",
replace(anon_3."Lý do giao hàng", '"', '') as "Lý do chi tiết",
orders."reasonNote" as "Ghi chú giao hàng",
to_char(orders."updatedAt", 'DD-MM-YYYY') as "Ngày cập nhật",
to_char(orders."updatedAt", 'HH24:MI:SS') as "Giờ cập nhật",
orders."senderData" -> 'customerLastName' as "Người gửi",
orders."senderData" -> 'customerPhone' as "SĐT người gửi",
concat(coalesce(cast(json_extract_path_text(orders."senderData", 'customerFullAddress') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."senderData", 'customerFullAddress') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."senderData", 'wardName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."senderData", 'wardName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."senderData", 'districtName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."senderData", 'districtName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."senderData", 'provinceName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."senderData", 'provinceName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."senderData", 'cityName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."senderData", 'cityName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."senderData", 'stateName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."senderData", 'stateName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."senderData", 'countryName') as VARCHAR), '')) as "Địa chỉ người gửi",
orders."senderData" -> 'wardName' as "Phường xã người gửi",
orders."senderData" -> 'districtName' as "Quận huyện người gửi",
orders."senderData" -> 'provinceName' as "Tỉnh thành người gửi",
orders."receiverData" -> 'customerLastName' as "Người nhận",
orders."receiverData" -> 'customerPhone' as "SĐT người nhận",
concat(coalesce(cast(json_extract_path_text(orders."receiverData", 'customerFullAddress') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."receiverData", 'customerFullAddress') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."receiverData", 'wardName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."receiverData", 'wardName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."receiverData", 'districtName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."receiverData", 'districtName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."receiverData", 'provinceName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."receiverData", 'provinceName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."receiverData", 'cityName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."receiverData", 'cityName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."receiverData", 'stateName') as VARCHAR), ''), case when (coalesce(cast(json_extract_path_text(orders."receiverData", 'stateName') as VARCHAR), '') != '') then ', ' else '' end, coalesce(cast(json_extract_path_text(orders."receiverData", 'countryName') as VARCHAR), '')) as "Địa chỉ người nhận",
orders."receiverData" -> 'wardName' as "Phường xã người nhận",
orders."receiverData" -> 'districtName' as "Quận huyện người nhận",
orders."receiverData" -> 'provinceName' as "Tỉnh thành người nhận",
coalesce(orders.pcs, 0) as "Số kiện",
(orders."packageDetails" -> 0) -> 'description' as "Thông tin kiện",
to_char(orders."updatedAt", 'DD-MM-YYYY HH24:MI:SS') as "Ngày giờ cập nhật",
orders."documentNumber" as "Mã chứng từ",
orders.data -> 'deliveryInstruction' as "Hướng dẫn giao hàng",
case
when (anon_4.action = 'Hoàn tất giao hàng') then to_char(anon_4."timeDeliveriedNew", 'DD-MM-YYYY HH24:MI:SS')
else to_char(anon_4."createdAt", 'DD-MM-YYYY HH24:MI:SS')
end as "Ngày thực hiện",
anon_4.action as "Trạng thái",
anon_4."userName" as "Người thực hiện",
anon_4."reasonName" as "Lý do ",
anon_4.generate_1 as "Ghi chú",
anon_4."senderOrReceiver" as "Người nhận hàng",
to_char(anon_4."timeDeliveriedNew", 'DD-MM-YYYY HH24:MI:SS') as "Ngày giao hàng",
coalesce(anon_4."licensePlate", '') as "Biển số xe",
anon_4.current_station_name as "Trạm giao hàng"
from
orders
join (
select
orders."orderNumber" as "orderNumber",
orders."subServiceId" as "subServiceId",
orders."reasonDetailIds" as "reasonDetailIds",
orders."orderId" as order_id
from
orders
where
orders."isDeleted" = false
and orders."orderTypeId" != 2
and orders."statusId" is not null
and orders."stationId" is not null
and orders."createdAt" between '2026-06-01 00:00:00' and '2026-06-04 23:59:59'
and orders."customerBusinessId" = 'eb113fa9-8157-40d9-bc8b-956e1725c3c2') as anon_5 on
orders."orderNumber" = anon_5."orderNumber"
left outer join customer_businesses on
orders."customerBusinessId" = customer_businesses."customerId"
left outer join reasons on
orders."reasonId" = reasons.id
left outer join stations as station_orig on
orders."stationId" = station_orig.id
left outer join stations as station_curr on
orders."currentStation" = station_curr.id
left outer join (
select
anon_5.order_id as order_id,
coalesce(string_agg(cast(reason_details.name -> 'vi' as VARCHAR), ', '), '') as "Lý do giao hàng"
from
(
select
orders."orderNumber" as "orderNumber",
orders."subServiceId" as "subServiceId",
orders."reasonDetailIds" as "reasonDetailIds",
orders."orderId" as order_id
from
orders
where
orders."isDeleted" = false
and orders."orderTypeId" != 2
and orders."statusId" is not null
and orders."stationId" is not null
and orders."createdAt" between '2026-06-01 00:00:00' and '2026-06-04 23:59:59'
and orders."customerBusinessId" = 'eb113fa9-8157-40d9-bc8b-956e1725c3c2') as anon_5
left outer join reason_details on
reason_details.id = any(anon_5."reasonDetailIds")
where
anon_5."reasonDetailIds" is not null
group by
anon_5.order_id) as anon_3 on
orders."orderId" = anon_3.order_id
left outer join (
select
anon_5.order_id as order_id,
coalesce(string_agg(sub_services.name, ', '), '') as "Sub sevices"
from
(
select
orders."orderNumber" as "orderNumber",
orders."subServiceId" as "subServiceId",
orders."reasonDetailIds" as "reasonDetailIds",
orders."orderId" as order_id
from
orders
where
orders."isDeleted" = false
and orders."orderTypeId" != 2
and orders."statusId" is not null
and orders."stationId" is not null
and orders."createdAt" between '2026-06-01 00:00:00' and '2026-06-04 23:59:59'
and orders."customerBusinessId" = 'eb113fa9-8157-40d9-bc8b-956e1725c3c2') as anon_5
left outer join sub_services on
sub_services."subServiceId" = any(anon_5."subServiceId")
and sub_services."deletedAt" is null
where
anon_5."subServiceId" is not null
group by
anon_5.order_id) as anon_6 on
orders."orderId" = anon_6.order_id
left outer join (
select
manifest_transactions."orderNumber" as "orderNumber",
count(distinct(date(manifest_transactions."dateFrom"))) as count_order_id
from
manifest_transactions
join (
select
orders."orderNumber" as "orderNumber",
orders."subServiceId" as "subServiceId",
orders."reasonDetailIds" as "reasonDetailIds",
orders."orderId" as order_id
from
orders
where
orders."isDeleted" = false
and orders."orderTypeId" != 2
and orders."statusId" is not null
and orders."stationId" is not null
and orders."createdAt" between '2026-06-01 00:00:00' and '2026-06-04 23:59:59'
and orders."customerBusinessId" = 'eb113fa9-8157-40d9-bc8b-956e1725c3c2') as anon_5 on
manifest_transactions."orderNumber" = anon_5."orderNumber"
where
manifest_transactions.action in (38, 40, 55)
and manifest_transactions."orderNumber" is not null
group by
manifest_transactions."orderNumber") as anon_2 on
orders."orderNumber" = anon_2."orderNumber"
left outer join (
select
anon_7."orderNumber" as "orderNumber",
anon_7.action as action,
anon_7."employeeId" as "employeeId",
anon_7."userNameId" as "userNameId",
anon_7."userName" as "userName",
anon_7."reasonName" as "reasonName",
anon_7."timeDeliveriedNew" as "timeDeliveriedNew",
anon_7."senderOrReceiver" as "senderOrReceiver",
anon_7."createdAt" as "createdAt",
anon_7.generate_1 as generate_1,
anon_7."licensePlate" as "licensePlate",
anon_7."currentStation" as "currentStation",
anon_7.current_station_name as current_station_name,
anon_7.order_id as order_id
from
(
select
manifest_transactions."orderNumber" as "orderNumber",
case
when (manifest_transactions.action = 2) then 'Nhận hàng nội địa'
when (manifest_transactions.action = 3) then 'Booking mới'
when (manifest_transactions.action = 4) then 'Đang nhận hàng'
when (manifest_transactions.action = 5) then 'Đã nhận hàng'
when (manifest_transactions.action = 6) then 'Tạo đơn hàng từ khách hàng'
when (manifest_transactions.action = 7) then 'Đơn hàng được tạo mới'
when (manifest_transactions.action = 8) then 'Báo giá'
when (manifest_transactions.action = 9) then 'Thanh toán'
when (manifest_transactions.action = 10) then 'Đã xác nhận'
when (manifest_transactions.action = 11) then 'Đã dịch lọc'
when (manifest_transactions.action = 12) then 'Chuyển nhận hàng'
when (manifest_transactions.action = 13) then 'Đã lấy hàng'
when (manifest_transactions.action = 14) then 'Cập nhật nhận hàng'
when (manifest_transactions.action = 15) then 'Nhận hàng 1 phần'
when (manifest_transactions.action = 16) then 'Đã nhận hàng'
when (manifest_transactions.action = 17) then 'Xử lý giao hàng'
when (manifest_transactions.action = 18) then 'Đơn bị delay'
when (manifest_transactions.action = 20) then 'Mới'
when (manifest_transactions.action = 21) then 'Đã xác nhận'
when (manifest_transactions.action = 22) then 'Xóa Manifest'
when (manifest_transactions.action = 23) then 'Check-in station ' || case
when (manifest_transactions."nextStation" = 1) then 'Cần Thơ'
when (manifest_transactions."nextStation" = 2) then 'Từ Liêm - Hà Nội'
when (manifest_transactions."nextStation" = 3) then '85 Thăng Long'
when (manifest_transactions."nextStation" = 4) then 'Bình Dương'
when (manifest_transactions."nextStation" = 5) then 'Nha Trang'
when (manifest_transactions."nextStation" = 6) then 'Hải Phòng'
when (manifest_transactions."nextStation" = 7) then 'Tây Ninh'
when (manifest_transactions."nextStation" = 18) then 'Vũng Tàu'
when (manifest_transactions."nextStation" = 23) then 'SCE 06 Thăng Long'
when (manifest_transactions."nextStation" = 24) then 'Tiền Giang - DTH'
when (manifest_transactions."nextStation" = 25) then 'Quảng Ngãi'
when (manifest_transactions."nextStation" = 26) then 'SAM-Houston-US'
when (manifest_transactions."nextStation" = 27) then 'SAM-Virginia-US'
when (manifest_transactions."nextStation" = 28) then 'SPE-Tokyo-JP'
when (manifest_transactions."nextStation" = 29) then 'GBT-Sydney-AU'
when (manifest_transactions."nextStation" = 30) then 'Đà Nẵng'
when (manifest_transactions."nextStation" = 31) then 'Đồng Nai'
when (manifest_transactions."nextStation" = 49) then 'FAD-Oregon-US'
when (manifest_transactions."nextStation" = 51) then 'SCE Nội Bài - Hà Nội'
when (manifest_transactions."nextStation" = 52) then 'FAD-Frankfurt-DE'
when (manifest_transactions."nextStation" = 53) then 'China - CN'
when (manifest_transactions."nextStation" = 57) then 'SAM-New Brunswick-CA'
when (manifest_transactions."nextStation" = 58) then 'DTL-Newyork-US'
when (manifest_transactions."nextStation" = 59) then 'Hongkong - CN'
when (manifest_transactions."nextStation" = 60) then 'ETR-Seoul-KR'
when (manifest_transactions."nextStation" = 61) then '06 Thăng Long'
when (manifest_transactions."nextStation" = 62) then 'Japan - JP'
when (manifest_transactions."nextStation" = 63) then 'Nội Bài - Hà Nội'
when (manifest_transactions."nextStation" = 65) then 'TCE ICD'
else ''
end
when (manifest_transactions.action = 24) then 'Check-in Hub'
when (manifest_transactions.action = 25) then 'Check-in Gateway'
when (manifest_transactions.action = 26) then 'Đóng gói'
when (manifest_transactions.action = 27) then 'Hủy đóng gói'
when (manifest_transactions.action = 28) then 'Hàng hoá được thông quan'
when (manifest_transactions.action = 29) then 'Gom đơn hàng'
when (manifest_transactions.action = 30) then 'Hàng hoá đến trạm'
when (manifest_transactions.action = 31) then 'Đang giao hàng'
when (manifest_transactions.action = 32) then 'Đẩy thông quan'
when (manifest_transactions.action = 33) then 'Đang giao hàng'
when (manifest_transactions.action = 34) then 'Chuyển đến trạm ' || case
when (manifest_transactions."nextStation" = 1) then 'Cần Thơ'
when (manifest_transactions."nextStation" = 2) then 'Từ Liêm - Hà Nội'
when (manifest_transactions."nextStation" = 3) then '85 Thăng Long'
when (manifest_transactions."nextStation" = 4) then 'Bình Dương'
when (manifest_transactions."nextStation" = 5) then 'Nha Trang'
when (manifest_transactions."nextStation" = 6) then 'Hải Phòng'
when (manifest_transactions."nextStation" = 7) then 'Tây Ninh'
when (manifest_transactions."nextStation" = 18) then 'Vũng Tàu'
when (manifest_transactions."nextStation" = 23) then 'SCE 06 Thăng Long'
when (manifest_transactions."nextStation" = 24) then 'Tiền Giang - DTH'
when (manifest_transactions."nextStation" = 25) then 'Quảng Ngãi'
when (manifest_transactions."nextStation" = 26) then 'SAM-Houston-US'
when (manifest_transactions."nextStation" = 27) then 'SAM-Virginia-US'
when (manifest_transactions."nextStation" = 28) then 'SPE-Tokyo-JP'
when (manifest_transactions."nextStation" = 29) then 'GBT-Sydney-AU'
when (manifest_transactions."nextStation" = 30) then 'Đà Nẵng'
when (manifest_transactions."nextStation" = 31) then 'Đồng Nai'
when (manifest_transactions."nextStation" = 49) then 'FAD-Oregon-US'
when (manifest_transactions."nextStation" = 51) then 'SCE Nội Bài - Hà Nội'
when (manifest_transactions."nextStation" = 52) then 'FAD-Frankfurt-DE'
when (manifest_transactions."nextStation" = 53) then 'China - CN'
when (manifest_transactions."nextStation" = 57) then 'SAM-New Brunswick-CA'
when (manifest_transactions."nextStation" = 58) then 'DTL-Newyork-US'
when (manifest_transactions."nextStation" = 59) then 'Hongkong - CN'
when (manifest_transactions."nextStation" = 60) then 'ETR-Seoul-KR'
when (manifest_transactions."nextStation" = 61) then '06 Thăng Long'
when (manifest_transactions."nextStation" = 62) then 'Japan - JP'
when (manifest_transactions."nextStation" = 63) then 'Nội Bài - Hà Nội'
when (manifest_transactions."nextStation" = 65) then 'TCE ICD'
else ''
end
when (manifest_transactions.action = 35) then 'Chuyển đến Hub ' || case
when (manifest_transactions."nextHub" = 1) then 'JFK'
when (manifest_transactions."nextHub" = 2) then 'LAX'
when (manifest_transactions."nextHub" = 3) then 'ORD'
when (manifest_transactions."nextHub" = 4) then 'AMS'
when (manifest_transactions."nextHub" = 5) then 'SGN'
when (manifest_transactions."nextHub" = 6) then 'HAN'
when (manifest_transactions."nextHub" = 7) then 'HOUSTON'
when (manifest_transactions."nextHub" = 8) then 'VIRGINIA'
when (manifest_transactions."nextHub" = 9) then 'KUL'
when (manifest_transactions."nextHub" = 10) then 'TCE-ICD'
else ''
end
when (manifest_transactions.action = 36) then 'Chuyển đến Gateway ' || case
when (manifest_transactions."nextHub" = 1) then 'SCE 06 Thăng Long'
when (manifest_transactions."nextHub" = 2) then 'SCE 06 Thăng Long'
when (manifest_transactions."nextHub" = 3) then 'SCE 06 Thăng Long'
when (manifest_transactions."nextHub" = 4) then 'SCE 06 Thăng Long'
when (manifest_transactions."nextHub" = 5) then 'SCE 06 Thăng Long'
when (manifest_transactions."nextHub" = 6) then 'SCE Nội Bài - Hà Nội'
when (manifest_transactions."nextHub" = 7) then 'SAM-Houston-US'
when (manifest_transactions."nextHub" = 8) then 'SAM-Virginia-US'
when (manifest_transactions."nextHub" = 9) then 'SCE 06 Thăng Long'
when (manifest_transactions."nextHub" = 10) then 'TCE-ICD'
else ''
end
when (manifest_transactions.action = 37) then 'Đến sân bay'
when (manifest_transactions.action = 38) then 'Giao hàng không thành công'
when (manifest_transactions.action = 39) then 'Đã giao hàng'
when (manifest_transactions.action = 40) then 'Hoàn tất giao hàng'
when (manifest_transactions.action = 41) then 'POD thành công'
when (manifest_transactions.action = 42) then 'Hàng hóa giữ hàng với những lý do chi tiết kèm theo'
when (manifest_transactions.action = 43) then 'Xuất kho đi giao'
when (manifest_transactions.action = 45) then 'Chuyển MAWB'
when (manifest_transactions.action = 50) then 'Trả giao hàng'
when (manifest_transactions.action = 51) then 'Xác nhận trả hàng'
when (manifest_transactions.action = 52) then 'Đã phân công trả hàng'
when (manifest_transactions.action = 54) then 'Hoàn trả người gửi'
when (manifest_transactions.action = 55) then 'Đã giao thành công và đang thu hồi'
when (manifest_transactions.action = 56) then 'Đã giao và thu hồi thành công'
when (manifest_transactions.action = 60) then 'Cập nhật Manfifest'
when (manifest_transactions.action = 61) then 'Pre-checkout'
when (manifest_transactions.action = 63) then 'Cập nhật giao hàng'
when (manifest_transactions.action = 64) then 'Cập nhật chuyển đến gateway'
when (manifest_transactions.action = 65) then 'Giữ hàng thông quan'
when (manifest_transactions.action = 66) then 'Huỷ giữ hàng thông quan'
when (manifest_transactions.action = 67) then 'Cập nhật shipment delivery'
when (manifest_transactions.action = 72) then 'Cập nhật shipment delivery'
when (manifest_transactions.action = 80) then 'Đơn bị hold'
when (manifest_transactions.action = 100) then 'Xóa đơn hàng'
when (manifest_transactions.action = 101) then 'Huỷ'
when (manifest_transactions.action = 102) then 'Hủy nhận hàng'
when (manifest_transactions.action = 103) then 'Hủy dịch lọc'
when (manifest_transactions.action = 104) then 'Hủy booking'
when (manifest_transactions.action = 105) then ' '
when (manifest_transactions.action = 112) then 'Tạo mới MAWB'
when (manifest_transactions.action = 200) then 'Gỡ MAWB khỏi Manifest'
when (manifest_transactions.action = 201) then 'Thêm MAWB khỏi Manifest'
when (manifest_transactions.action = 202) then 'Giữ thông quan'
when (manifest_transactions.action = 203) then 'Bỏ giữ thông quan'
when (manifest_transactions.action = 204) then 'Xoá chi tiết manifest'
when (manifest_transactions.action = 205) then 'Xóa toàn bộ MAWB'
when (manifest_transactions.action = 206) then 'Đã phân công trả hàng'
when (manifest_transactions.action = 207) then 'Cập nhật đơn hàng'
else ''
end as action,
manifest_transactions."employeeId" as "employeeId",
users.username as "userNameId",
users.firstname || ' ' || users.lastname as "userName",
manifest_transactions."timeDeliveriedNew" as "timeDeliveriedNew",
manifest_transactions."senderOrReceiver" as "senderOrReceiver",
coalesce(cast(manifest_transactions."reasonDetailId" as VARCHAR) || ' - ' || (reason_details.name -> 'vi'), '') as "reasonName",
manifest_transactions.generate_1 as generate_1,
manifest_transactions."createdAt" as "createdAt",
manifest_transactions."licensePlate" as "licensePlate",
manifest_transactions."orderId" as order_id,
manifest_transactions."currentStation" as "currentStation",
station_curr.name as current_station_name,
row_number() over (partition by manifest_transactions."employeeId",
manifest_transactions.action,
manifest_transactions."orderNumber"
order by
manifest_transactions."createdAt" desc) as rn
from
manifest_transactions
join (
select
orders."orderNumber" as "orderNumber",
orders."subServiceId" as "subServiceId",
orders."reasonDetailIds" as "reasonDetailIds",
orders."orderId" as order_id
from
orders
where
orders."isDeleted" = false
and orders."orderTypeId" != 2
and orders."statusId" is not null
and orders."stationId" is not null
and orders."createdAt" between '2026-06-01 00:00:00' and '2026-06-04 23:59:59'
and orders."customerBusinessId" = 'eb113fa9-8157-40d9-bc8b-956e1725c3c2') as anon_5 on
manifest_transactions."orderNumber" = anon_5."orderNumber"
left outer join users on
manifest_transactions."employeeId" = users."employeeId"
left outer join reason_details on
manifest_transactions."reasonDetailId" = reason_details.id
left outer join stations as station_curr on
manifest_transactions."currentStation" = station_curr.id
where
manifest_transactions.action in (1, 2, 3, 4, 5, 7, 10, 11, 13, 16, 18, 20, 21, 23, 26, 27, 31, 32, 33, 34, 36, 38, 39, 40, 42, 43, 50, 51, 52, 54, 55, 56, 60, 61, 63, 64, 65, 67, 72, 80, 100, 101, 102, 104, 105, 203, 204, 206, 207)
and manifest_transactions."orderNumber" is not null) as anon_7
where
anon_7.rn = 1) as anon_4 on
orders."orderNumber" = anon_4."orderNumber"
left outer join (
select
anon_8."HAWBClearance" as "HAWBClearance",
anon_8.order_id as order_id,
anon_8."orderNumber" as "orderNumber",
anon_8."orderNumberClient" as "orderNumberClient",
anon_8."trackingNumber" as "trackingNumber",
anon_8."senderOrReceiver" as "senderOrReceiver",
anon_8."successDeliveryDate" as "successDeliveryDate",
anon_8."clientId" as "clientId",
anon_8."customerBusinessId" as "customerBusinessId",
anon_8."serviceId" as "serviceId",
anon_8."MAWB" as "MAWB",
anon_8.curr_station_manifest as curr_station_manifest,
anon_8.next_station_manifest as next_station_manifest,
anon_8."createdAtStationId" as "createdAtStationId",
anon_8."stationId" as "stationId",
anon_8."createdAt" as "createdAt"
from
(
select
anon_9."HAWBClearance" as "HAWBClearance",
anon_9.order_id as order_id,
anon_9."orderNumber" as "orderNumber",
anon_9."orderNumberClient" as "orderNumberClient",
anon_9."trackingNumber" as "trackingNumber",
anon_9."senderOrReceiver" as "senderOrReceiver",
anon_9."successDeliveryDate" as "successDeliveryDate",
anon_9."clientId" as "clientId",
anon_9."customerBusinessId" as "customerBusinessId",
anon_9."serviceId" as "serviceId",
anon_9."MAWB" as "MAWB",
anon_9.curr_station_manifest as curr_station_manifest,
anon_9.next_station_manifest as next_station_manifest,
anon_9."createdAtStationId" as "createdAtStationId",
anon_9."stationId" as "stationId",
anon_9."createdAt" as "createdAt",
row_number() over (partition by anon_9.order_id
order by
anon_9."successDeliveryDate" asc) as rn
from
(
select
manifests."HAWBClearance" as "HAWBClearance",
manifests."orderId" as order_id,
manifests."orderNumber" as "orderNumber",
manifests."orderNumberClient" as "orderNumberClient",
manifests."trackingNumber" as "trackingNumber",
manifests."senderOrReceiver" as "senderOrReceiver",
manifests."successDeliveryDate" as "successDeliveryDate",
manifests."MAWB" as "MAWB",
manifests."clientId" as "clientId",
manifests."customerBusinessId" as "customerBusinessId",
manifests."serviceId" as "serviceId",
station_orig.name as curr_station_manifest,
station_curr.name as next_station_manifest,
manifests."createdAtStationId" as "createdAtStationId",
manifests."stationId" as "stationId",
manifests."createdAt" as "createdAt"
from
manifests
left outer join stations as station_orig on
manifests."createdAtStationId" = station_orig.id
left outer join stations as station_curr on
manifests."stationId" = station_curr.id
where
manifests."isDeleted" = false
and (manifests."serviceId" not in ('4', '5'))
and manifests."deletedAt" is null) as anon_9) as anon_8
where
anon_8.rn = 1) as anon_1 on
orders."orderNumber" = anon_1."orderNumber"
where
orders."isDeleted" = false
and orders."orderTypeId" != 2
and orders."statusId" is not null
and orders."stationId" is not nullEditor is loading...
Leave a Comment