Untitled

SELECT 
    CURRENT_TIMESTAMP() AS logdate,
    siteflag,
    product,
    size,
    SUBSTRING_INDEX(inventoryiteminfo, '=', 1) AS serials,
    SUBSTRING_INDEX(inventoryiteminfo, '=', - 1) AS loans,
    orders,
    unsellableqty,
    sellableqty,
    quantity AS current_quantity,
    /* calculate if there's an inventory defiency:
    if 0, it means that the inventory quantity matches the physical inventory on hand
    if less than 0, it means that the inventory quantity is greater than the physical inventory on hand
    if greater than 0, it means that the inventory quantity is less than the physical inventory on hand 
    */
    ((SELECT serials) + (SELECT loans)) - (quantity + orders) AS deficiency,
    quantity + (SELECT deficiency) AS correct_quantity,
    (SELECT 
            last_update
        FROM
            inventory
        WHERE
            product = j3.product
                AND option1 = j3.size) AS inventory_lastupdate,
    (SELECT 
            MAX(last_update)
        FROM
            shipment
        WHERE
            productcode = j3.product
                AND option1 = j3.size) AS shipment_lastupdate
FROM
    (SELECT 
        j1.*,
            IFNULL(pending_orders, 0) AS orders,
            IFNULL((SELECT 
                    CONCAT(CAST(SUM(qtyonhand) AS CHAR (20)), '=', CAST(SUM(onloan) AS CHAR (20)))
                FROM
                    inventoryitem FORCE INDEX (PRODUCT)
                WHERE
                    product = j1.product AND size = j1.size), 0) AS inventoryiteminfo
    FROM
        (SELECT 
        product,
            option1 AS size,
            siteflag,
            wholesalecost,
            quantity,
            sellableqty,
            quantity - sellableqty AS unsellableqty
    FROM
        (SELECT 
        code, siteflag, wholesalecost
    FROM
        product
    WHERE
        iscurrent = 1
            AND brandname NOT IN ('Beauty Samples' , 'Gift Certificates', 'Gift Wrap', 'GRLFRND Embroidery')) t1
    JOIN inventory ON code = product
    WHERE
        last_update >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
            AND sellableqty <= 20
    GROUP BY product , size) j1
    LEFT JOIN (SELECT 
        productcode, option1, COUNT(*) AS pending_orders
    FROM
        (SELECT 
        productcode,
            option1,
            IF(boxtime != '1969-12-31 16:00:00', REPLACE(serialnumber, 's', ''), '') AS serial
    FROM
        orders t0
    JOIN shipment t1 ON invoice = invoicenum
    WHERE
        t1.status IN ('ready' , 'printing', 'printed', 'picked', 'picksorted', 'verify', 'boxed', 'cant find', 'on hold', 'unvalidated', 'could not charge', 'invalid address', 'unvalidated address show reCAPTCHA')) t3
    WHERE
        IF(serial != '', (SELECT 
                qtyonhand
            FROM
                inventoryitem FORCE INDEX (SERIALNUMBER)
            WHERE
                serialnumber = serial), 1)
    GROUP BY productcode , option1) j2 ON j1.product = j2.productcode
        AND j1.size = j2.option1
    GROUP BY product , size) j3
/* if we have defiency in the inventory and there was no update in the inventory since the discrepancy was logged, this a "verified" discrepancy */ 
HAVING (deficiency < 0
    AND inventory_lastupdate < logdate)
/* Also make sure that there was no shipment update in case a pending order was completed after the order count was calcualted since this event does not trigger an inventory update, only when the order was orginally placed (reasoncode:3) or canceled etc */
    AND shipment_lastupdate < logdate
ORDER BY sellableqty
LIMIT 10000000