Untitled

select 
    date(max(lastscandate)) as moved_date,
    substring_index(group_concat(distinct substring_index(substring_index(locationhistory,
                                ']',
                                if(substring_index(substring_index(locationhistory, ']', 1),
                                            ',',
                                            - 1) in ('none' , ''),
                                    2,
                                    1)),
                        ',',
                        - 1)
                order by lastscandate desc),
            ',',
            1) as username,
    t1.product,
    t1.size,
    (select 
            siteflag
        from
            product
        where
            code = t1.product) as siteflag,
    group_concat(distinct t1.status) as status,
    count(distinct if(qtyonhand, serialnumber, null)) as moved,
    count(distinct if(qtyonhand, null, serialnumber)) as removed,
    (select 
            sum(if((location like '7-_%'
                        or location = 'inventory')
						and location not like '3-666Z-_%'
                        and qtyonhand = 1
                        and if(defective,
                        find_in_set(serialnumber, defective_serials),
                        1)
                        and serialnumber not in (select 
                            replace(serialnumber, 's', '')
                        from
                            helditem
                        where
                            productcode = t1.product
                                and option1 = t1.size
                                and status in ('purchased' , 'ready')
                                and expirationdate >= current_date
                                and find_in_set(email, emails) = 0),
                    1,
                    0))
        from
            inventoryitem
        where
            product = t1.product and size = t1.size
        group by product , size) as shelf,
    holds,
    orders,
    (select 
            t2.location
        from
            warehouselocation
        where
            unsellable = 1
                and location not in ('order packing' , 'returns', 'receiving')
                and if(likemodifier = 1,
                left(t2.location,
                    length(substring_index(location, '%', 1))) = substring_index(location, '%', 1),
                t2.location = location)
        group by location limit 1) as moved_location,
    sellableqty
from
    (select 
        least(ifnull(orderdate, creationdate), ifnull(creationdate, orderdate)) as logdate,
            product,
            size,
            concat_ws(',', j2.status, j3.status) as status,
            concat_ws(',', j2.emails, j3.emails) as emails,
            ifnull(orders, 0) as orders,
            ifnull(holds, 0) as holds,
            defective_serials,
            sellableqty
    from
        (select 
        product, option1 as size, sellableqty
    from
        inventory force index (last_update)
    where
        last_update >= date_sub(current_date, interval 45 day)
            and sellableqty < 0
    group by product , size
    limit 1000000000) j1
    left join (select 
        productcode,
            option1,
            group_concat(distinct status) as status,
            count(shipmentid) as orders,
            min(orderdate) as orderdate,
            group_concat(distinct useremail) as emails,
            group_concat(distinct replace(serialnumber, 's', '')) as defective_serials
    from
        shipment
    where
        orderdate >= date_sub(current_date(), interval 45 day)
            and status in ('printed' , 'printing', 'ready', 'cant find', 'verify', 'on hold')
            and extrastatus != 'on hold'
    group by productcode , option1
    limit 100000) j2 ON j1.product = j2.productcode
        and j1.size = j2.option1
    left join (select 
        productcode,
            option1,
            group_concat(distinct if(status = 'Ready', 'ready', 'purchased')) as status,
            count(id) as holds,
            min(creationdate) as creationdate,
            group_concat(distinct email) as emails
    from
        helditem
    where
        status in ('ready' , 'purchased')
            and expirationdate >= current_date
    group by productcode , option1
    limit 100000) j3 ON j1.product = j3.productcode
        and j1.size = j3.option1
    group by product , size
    having logdate
    limit 100000) t1
        join
    inventoryitem t2 ON t1.product = t2.product
        and t1.size = t2.size
where
    lastscandate > logdate
group by t1.product , t2.size , location
having ((holds + orders) - shelf) > 0
    and moved_location is not null
order by moved_date , username