Untitled

    SELECT
        sh.awb_nr AS awbNr,
        it.order_nr,
        it.target_shipped_at,
        src_w.partner_warehouse_code AS source_warehouse,
        dst_w.partner_warehouse_code AS destination_warehouse,
        dst_w.misc ->> '$.darkstore_name' as destination_ds_name,
        force_dst_w.partner_warehouse_code AS force_receive_destination_darkstore,
        force_dst_w.misc ->> '$.darkstore_name' as force_receive_destination_ds_name,
        CASE
            WHEN htl.id_status = 1 THEN 'picked'
            WHEN htl.id_status = 57 THEN 'staged'
            WHEN htl.id_status = 36 AND ht.id_status = 26 THEN 'released'
            WHEN htl.id_status = 36 AND ht.id_status = 13 THEN 'dispatched'
            WHEN htl.id_status = 69 THEN 'received'
        END AS status,
        CASE
            WHEN htl.id_status = 1 AND sa.id_staging_area IS NULL THEN 'Stage in Empty Staging Location'
            WHEN htl.id_status = 1 AND sa.id_staging_area IS NOT NULL THEN CONCAT('Stage in ', sa.code, ' Location')
            WHEN htl.id_status = 57 AND ht.id_hub_transfer IS NULL THEN 'Pending Release From Staging'
            WHEN htl.id_status = 36 AND ht.id_status = 26 THEN 'Pending Dispatch to Truck'
            WHEN htl.id_status = 36 AND ht.id_status = 13 THEN 'Pending Receive in Darkstore'
            WHEN htl.id_status = 69 THEN 'Received in Darkstore'
        END AS instruction,
        COUNT(it.id_item) AS items_count,
        COALESCE(fz.zone_code, pj.zone_picklist_group) as picked_zone,
        sa.code AS staging_location,
        ht.canter_code AS palletBarcode,
        sh.created_at AS picked_at,
        al1.created_at AS staged_at,
        u1.username as staged_user,
        al2.created_at AS released_at,
        u2.username as released_user,
        al4.created_at AS dispatched_at,
        u4.username as dispatched_user,
        al3.created_at AS received_at,
        u3.username as received_user,
        IF(htl.id_status = 36 AND ht.id_status = 13, TRUE, FALSE) AS enable_force_receive,
        IF(htl.id_status = 36 AND ht.id_status = 26 AND (
            SELECT COUNT(*)
            FROM hub_transfer_line htl1
            WHERE htl1.id_hub_transfer = ht.id_hub_transfer
        ) = 1, TRUE, FALSE) AS enable_download_gatepass_doc,
        IF(al3.id_audit_log IS NULL, TIMESTAMPDIFF(HOUR, al4.created_at, NOW()), NULL) AS dispatched_not_received_aging
    FROM item it
    LEFT JOIN warehouse src_w USING (id_warehouse)
    LEFT JOIN warehouse dst_w ON (it.misc ->> '$.id_dst_warehouse' = dst_w.id_warehouse)
    LEFT JOIN shipment_line sl ON (sl.id_item, sl.is_active) = (it.id_item, 1)
    LEFT JOIN shipment sh USING (id_shipment)
    LEFT JOIN warehouse force_dst_w ON (sh.misc ->> '$.force_received_warehouse_code' = force_dst_w.code)
    LEFT JOIN hub_transfer_line htl ON (htl.type, htl.line_type, htl.line_id) = ('forward_nim', 'shipment', sh.id_shipment)
    LEFT JOIN hub_transfer_group htg USING (id_hub_transfer_group)
    LEFT JOIN staging_area sa ON (htg.id_staging_area = sa.id_staging_area)
    LEFT JOIN hub_transfer ht ON (htl.id_hub_transfer = ht.id_hub_transfer)
    LEFT JOIN audit_log al1 ON (al1.id_audit_action, al1.action_key, al1.misc ->> '$.to') = (12, htl.id_hub_transfer_line, 'staged')
    LEFT JOIN user u1 ON u1.id_user = al1.id_user
    LEFT JOIN audit_log al2 ON (al2.id_audit_action, al2.action_key, al2.misc ->> '$.to') = (12, htl.id_hub_transfer_line, 'picked')
    LEFT JOIN user u2 ON u2.id_user = al2.id_user
    LEFT JOIN audit_log al3 ON (al3.id_audit_action, al3.action_key, al3.misc ->> '$.to') = (12, htl.id_hub_transfer_line, 'transferred')
    LEFT JOIN user u3 ON u3.id_user = al3.id_user
    LEFT JOIN audit_log al4 ON (al4.id_audit_action, al4.action_key, al4.misc ->> '$.to') = (11, ht.id_hub_transfer, 'sealed')
    LEFT JOIN user u4 ON u4.id_user = al4.id_user
    LEFT JOIN picking_container pc ON pc.box_barcode = sh.awb_nr
    LEFT JOIN picking_job pj ON pj.id_picking_job = pc.id_picking_job
    LEFT JOIN fbn_warehouse_zone fz ON fz.id_fbn_warehouse_zone = pj.id_fbn_warehouse_zone
    WHERE it.id_mp = 9
        AND it.id_status = 17
        AND sh.id_shipment IS NOT NULL
            AND it.target_shipped_at > DATE_SUB(NOW(), INTERVAL 1 DAY)

            AND src_w.id_country = 1
        
    GROUP BY 1,2,3,4,5,6,7,8,9,10,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26
    ORDER BY target_shipped_at DESC, picked_at DESC