Untitled

    SELECT
        ht.canter_code AS palletBarcode,
        GROUP_CONCAT(DISTINCT 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,
        ht.created_at AS pallet_created_at,
        IF (ht.id_status IN (13, 2), 'yes', 'no') AS ready_for_dispatch,
        al1u.username AS dispatched_by,
        driver.username AS driver,
        COUNT(DISTINCT sh.id_shipment) AS total_shipments,
        COUNT(DISTINCT shl.id_shipment_line) AS total_items,
        COUNT(DISTINCT IF(htl.id_status IN (36, 69) AND ht.id_status = 13, sh.id_shipment, NULL)) AS dispatched_shipments,
        COUNT(DISTINCT IF(htl.id_status IN (36, 69) AND ht.id_status = 13, shl.id_shipment_line, NULL)) AS dispatched_items,
        COUNT(DISTINCT IF(htl.id_status = 69, sh.id_shipment, NULL)) AS received_shipments,
        COUNT(DISTINCT IF(htl.id_status = 69, shl.id_shipment_line, NULL)) AS received_items,
        MAX(ht.created_at) AS released_staging_at,
        MAX(al1.created_at) AS dispatched_at,
        MAX(al2.created_at) AS received_at_darkstore
    FROM hub_transfer ht
    LEFT JOIN hub_transfer_line htl USING (id_hub_transfer)
    LEFT JOIN hub_transfer_group htg USING (id_hub_transfer_group)
    LEFT JOIN shipment sh ON (htl.line_type, htl.line_id) = ('shipment', sh.id_shipment)
    LEFT JOIN shipment_line shl USING (id_shipment)
    LEFT JOIN warehouse src_w ON (sh.id_warehouse = src_w.id_warehouse)
    LEFT JOIN warehouse dst_w ON (htg.destination_type, htg.destination_id) = ('warehouse', dst_w.id_warehouse)
    LEFT JOIN audit_log al1 ON (al1.id_audit_action, al1.action_key, al1.misc ->> '$.to') = (11, ht.id_hub_transfer, 'sealed')
    LEFT JOIN audit_log al2 ON (al2.id_audit_action, al2.action_key, al2.misc ->> '$.to') = (11, ht.id_hub_transfer, 'closed')
    LEFT JOIN `user` al1u ON (al1u.id_user = al1.id_user)
    LEFT JOIN driver_task_line dtl ON (dtl.entity_id, dtl.entity_type) = (ht.id_hub_transfer, 'pallet')
	LEFT JOIN driver_task_route dtr USING (id_driver_task_route)
    LEFT JOIN driver_task dt USING (id_driver_task)
    LEFT JOIN `user` driver ON (driver.id_user = dt.id_user)
    WHERE ht.type = 'forward_nim'
        AND ht.canter_code IS NOT NULL
        AND sh.id_status != 51
        {% if pallet_barcode %}
            AND ht.canter_code = :pallet_barcode
        {% elif pallet_created_at_from and pallet_created_at_to %}
            AND ht.created_at BETWEEN :pallet_created_at_from AND :pallet_created_at_to
        {% elif pallet_dispatched_max_hours %}
            AND HOUR(TIMEDIFF(current_timestamp, al1.created_at)) <= :pallet_dispatched_max_hours
        {% elif box_barcode %}
            AND ht.id_hub_transfer = (
                SELECT _htl.id_hub_transfer
                FROM shipment _sh
                LEFT JOIN hub_transfer_line _htl ON (_htl.type, _htl.line_type, _htl.line_id) = ('forward_nim', 'shipment', _sh.id_shipment)
                WHERE _sh.awb_nr = :box_barcode
                LIMIT 1
            )
        {% elif tote_code %}
            AND ht.id_hub_transfer = (
                    SELECT _htl.id_hub_transfer
                    FROM shipment _sh
                    LEFT JOIN hub_transfer_line _htl ON (_htl.type, _htl.line_type, _htl.line_id) = ('forward_nim', 'shipment', _sh.id_shipment)
                    WHERE _sh.created_at = (SELECT MAX(created_at) FROM shipment WHERE tote_code = :tote_code)
                    AND _sh.tote_code = :tote_code
                    LIMIT 1
                )
        {% else %}
            AND ht.created_at > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
        {% endif %}

        {% if id_country %}
            AND src_w.id_country = :id_country
        {% endif %}
    GROUP BY 1,3,4,5,6,7,8

    HAVING 1=1
    {% if destination_darkstore %}
        AND destination_warehouse = :destination_darkstore
    {% endif %}

    {% if driver_username %}
        AND driver.username = :driver_username
    {% endif %}

    {% if dispatcher_username %}
        AND al1u.username = :dispatcher_username
    {% endif %}

    ORDER BY pallet_created_at DESC