SELECT
                    -- distinct(postal_encomenda.id),
                postal_encomenda.no as numero,
                CASE
                    WHEN efetivados.status != ''   THEN  efetivados.status
                    WHEN p.status != '' THEN p.status
                ELSE  varejo_efetivados.status
                END AS 'status',
                postal_encomenda.norm, postal_encomenda.storagestatus,
                IFNULL(track.trackno, '-') as trackno,
                IFNULL( efetivados.password, '') as password, varejo_efetivados.password as password2,
                    d.nome as tamanho,
                d.dimension,
                CASE
                    WHEN  DATEDIFF(NOW(), FROM_UNIXTIME(efetivados.createtime, '%Y/%m/%d')) >=0 AND DATEDIFF(NOW(), FROM_UNIXTIME(efetivados.createtime, '%Y/%m/%d')) <=3 THEN '0'
                    WHEN  DATEDIFF(NOW(), FROM_UNIXTIME(efetivados.createtime, '%Y/%m/%d')) >3 AND DATEDIFF(NOW(), FROM_UNIXTIME(efetivados.createtime, '%Y/%m/%d')) <=5 THEN '3'
                    WHEN  DATEDIFF(NOW(), FROM_UNIXTIME(efetivados.createtime, '%Y/%m/%d')) >5 AND DATEDIFF(NOW(), FROM_UNIXTIME(efetivados.createtime, '%Y/%m/%d')) <=7 THEN '5'
                    WHEN  DATEDIFF(NOW(), FROM_UNIXTIME(efetivados.createtime, '%Y/%m/%d')) >7 THEN '7'

                    WHEN datediff(NOW(), varejo_efetivados.created_at ) >=0 AND datediff(NOW(), varejo_efetivados.created_at ) <=3 THEN '0'
                    WHEN datediff(NOW(), varejo_efetivados.created_at ) >3 AND datediff(NOW(), varejo_efetivados.created_at ) <=5 THEN '3'
                    WHEN datediff(NOW(), varejo_efetivados.created_at ) >5 AND datediff(NOW(), varejo_efetivados.created_at ) <=7 THEN '5'
                    WHEN datediff(NOW(), varejo_efetivados.created_at ) >7 THEN '7'

                    ELSE ''
                END AS 'delay_box',
                CASE
                    WHEN track.ordernumber != ''   THEN track.ordernumber
                    WHEN p.codigo_objeto != '' THEN p.codigo_objeto
                ELSE   enc.codigo_encomenda
                END AS 'ordernumber'
            FROM wf_sys_box as b
                LEFT JOIN wf_logistics_order as efetivados ON postal_encomenda.id = efetivados.boxid AND efetivados.updateuser IS NULL and  efetivados.status = 1
                left JOIN wf_logistics_track as t ON efetivados.trackid = track.id  and track.status = efetivados.status and track.status = 1
                LEFT JOIN postal.postal_encomenda as postal_encomenda ON postal_encomenda.id = p.id_box and p.status = 1
                LEFT JOIN interface_api_varejo_pedidos_efetivados efe  ON  varejo_efetivados.fk_id_box = postal_encomenda.id and varejo_efetivados.status = 1
                LEFT JOIN interface_api_varejo_reservas as res ON res.id = varejo_efetivados.fk_id_reserva
                LEFT JOIN interface_api_varejo_encomenda as enc ON enc.id = res.fk_id_encomenda
                LEFT JOIN tipo_gavetas AS d ON  postal_encomenda.norm = d.old_norm
                WHERE
                postal_encomenda.clientid = 
                and postal_encomenda.status = 1
            -- GROUP BY postal_encomenda.id
            ORDER BY postal_encomenda.no, enc.codigo_encomenda ASC