Untitled

SELECT
            -- Order Container fields
            oc.id,
            oc.seal_no,
            oc.container_symbol,
            oc.status,
            oc.type_of_transportation,
            oc.container_no,
            oc.date,
            oc.request_time,
            oc.actual_time,
            oc.license_plate,
            oc.driver_phone_number,
            oc.specific_address,
            oc.additional_info,
            oc.local_charge,
            oc.driver_name,
            oc.container_kind,
            oc.declaration_number,
            oc.ma_phan_loai_kiem_tra,
            oc.ma_loai_hinh,
            oc.ngay_dang_ky,
            oc.ten_nguoi_xk,
            oc.ten_nguoi_nk,
            oc.so_luong,
            oc.tong_trong_luong,
            oc.so_hoa_don,
            oc.note,
            oc.tare,
            oc.has_cuoc_cong,
            oc.has_hoan_cuoc_cong,
            oc.has_thu_phoi,
            oc.has_da_thu_phoi,
            oc.hai_kho,
            oc.has_to_khai_ops,
            oc.has_xac_nhan_thanh_ly_tk,
            oc.has_lay_phoi,
            oc.has_tra_phoi,
            oc.created_by,
            oc.created_at,
            oc.updated_by,
            oc.updated_at,
            oc.order_id,
            oc.loai_hang_hoa,
            oc.kpi_cus,
        
            -- Order data (nested field)
            o.id as order_id,
            o.booking_bill_number,
            o.bl,
            o.status as order_status,
            o.order_type,
            o.order_code,
            o.created_by as order_created_by,
            o.shipper,
            o.ship_id,
            o.cut_off,
            o.dropoff_terminal_id,
            o.pickup_terminal_id,
            pt.name pickup_terminal_name,
            st.name dropoff_terminal_name,
            o.cy_cut_off,
        
            -- Customer data (nested field)
            c.id as customer_id,
            c.company_name as company_name,
        
            -- Container data (nested field)
            ct.id as container_type_id,
            ct.name as container_type_name,
        
        
            CASE
                WHEN oc.status = 'CREATED'
                    AND oc.date IS NOT NULL
                    AND oc.date <= (CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Ho_Chi_Minh')::date + INTERVAL '1 day'
                THEN 1
                ELSE 0
            END as is_dong_tra_hang_due_date,
        
            CASE
                WHEN oc.status IN ('CREATED', 'PACKAGE', 'DELIVERY') AND o.cut_off IS NOT NULL
                    AND o.cut_off <= (CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Ho_Chi_Minh')::date + INTERVAL '1 day'
                THEN 1
                ELSE 0
            END as is_to_khai_due_date,
        
            -- Service flags
            CASE
                WHEN EXISTS (
                    SELECT 1 FROM order_container_pnl pnl
                    INNER JOIN services srv ON pnl.service_id = srv.id
                    WHERE pnl.order_container_id = oc.id AND srv.symbol = 'GIAM_SAT'
                ) THEN 1
                ELSE 0
            END as has_giam_sat,
        
            CASE
                WHEN EXISTS (
                    SELECT 1 FROM order_container_pnl pnl
                    INNER JOIN services srv ON pnl.service_id = srv.id
                    WHERE pnl.order_container_id = oc.id AND srv.symbol = 'CA_XE'
                ) THEN 1
                ELSE 0
            END as has_ca_xe,
        
            CASE
                WHEN EXISTS (
                    SELECT 1 FROM order_container_pnl pnl
                    INNER JOIN services srv ON pnl.service_id = srv.id
                    WHERE pnl.order_container_id = oc.id AND srv.symbol = 'GIAM_SAT_KO_PHOI'
                ) THEN 1
                ELSE 0
            END as has_k_phoi,
        
            CASE
                WHEN EXISTS (
                    SELECT 1 FROM order_container_pnl pnl
                    INNER JOIN services srv ON pnl.service_id = srv.id
                    WHERE pnl.order_container_id = oc.id AND srv.symbol IN ('LACH_HUYEN')
                ) THEN 1
                ELSE 0
            END as has_lh,
            
            CASE
                WHEN EXISTS (
                    SELECT 1 FROM order_container_pnl pnl
                    INNER JOIN services srv ON pnl.service_id = srv.id
                    WHERE pnl.order_container_id = oc.id AND srv.symbol IN ('LACH_HUYEN_NANG')
                ) THEN 1
                ELSE 0
            END as has_lh_nang
        
        FROM order_containers oc
        INNER JOIN orders o ON oc.order_id = o.id
        left join seaport_terminals st on o.dropoff_terminal_id = st.id
        left join seaport_terminals pt on o.pickup_terminal_id = pt.id
        LEFT JOIN customers c ON o.customer_id = c.id
        LEFT JOIN containers ct ON oc.container_type_id = ct.id
        LEFT JOIN seaports t ON o.dropoff_terminal_id = t.id
        LEFT JOIN ships s ON o.ship_id = s.id
        WHERE 1=1