Untitled

select 
    date(t1.orderdate) as orderdate,
    t1.invoice,
    t1.productcode,
    t1.option1,
    t1.ooscategory,
    substring_index(substring_index(oosnotes, ';', 2),
            ',',
            1) as notes,
    if(locate(';', (select notes)),
        if((select notes) like 'i%',
            (select 
                    date(orderdate)
                from
                    shipment
                where
                    productcode = t1.productcode
                        and option1 = t1.option1
                        and invoice = substring_index(replace(substring_index(substring_index(oosnotes, ';', 2),
                                        ',',
                                        1),
                                'i',
                                ''),
                            ';',
                            1)
                limit 1),
            DATE_FORMAT(STR_TO_DATE(right(concat(left(substring_index(substring_index(oosnotes, ',', - 2),
                                                    ',',
                                                    1),
                                            11),
                                        right(substring_index(substring_index(oosnotes, ',', - 2),
                                                    ',',
                                                    1),
                                            4)),
                                length(concat(left(substring_index(substring_index(oosnotes, ',', - 2),
                                                            ',',
                                                            1),
                                                    11),
                                                right(substring_index(substring_index(oosnotes, ',', - 2),
                                                            ',',
                                                            1),
                                                    4))) - 4),
                            '%M%D%Y'),
                    '20%y-%m-%d')),
        date(t1.orderdate)) as last_touched,
    if(ooscategory = 'Reverse Receive',
        'Receiving',
        substring_index(group_concat(distinct defaulttask
                    order by t4.id desc),
                ',',
                1)) as division,
    if(ooscategory = 'Reverse Receive',
        (select 
                initialstocker
            from
                inventoryitem
            where
                product = t1.productcode
                    and size = t1.option1
                    and qtyonhand = 0
                    and substring_index(notes, ':', 1) = 'This is a reverse received item'
            order by lastscandate desc
            limit 1),
        t4.name) as employee,
    t2.siteflag,
    round(wholesalecost, 2) as cost
from
    ooscat t1
        join
    product t2 ON t1.productcode = t2.code
        left join
    employee t4 ON if(substring_index(substring_index(oosnotes, ',', - 1),
                ':',
                1) = 'agentId',
        (select 
                name
            from
                employee
            where
                (id + 1000) = substring_index(substring_index(oosnotes, ',', - 1),
                        ':',
                        - 1)),
        substring_index(oosnotes, ',', - 1)) = t4.name
where
    year(orderdate) = year(current_date)
        and weekofyear(current_date) - date_format(date_add(orderdate, interval 2 day),
            '%V') = 2
group by shipmentid
order by ooscategory