warehouse-locations.sql

SELECT
       meta.name AS "Product Name",
       CONCAT('A', location.aisle, ' S', location.shelf, ' B', location.bin) AS "Location",
       (
       SELECT 
               SUM(line.unit_price_at_checkout_time * line.quantity_shipped)
        FROM orders_orderline AS line
        INNER JOIN orders_order AS orders
             ON orders.id = line.order_id
        INNER JOIN products_product AS products
             ON line.product_id = products.id
        WHERE 
              orders.order_status = 8 AND  -- ORDER_STATUS_SHIPPED
              line.quantity_shipped IS NOT NULL AND
              line.quantity_shipped > 0 AND
              line.unit_price_at_checkout_time IS NOT NULL AND
              orders.order_date BETWEEN '1900-01-01' AND '2100-12-31' AND
              line.product_id IN 
              (
                SELECT product_ptr_id 
                FROM products_simpleproduct AS simpleproduct
                WHERE simpleproduct.piece_id = piece.id
              )
       ) AS "Sales Volume"
FROM warehouse_pieces_piece AS piece
INNER JOIN warehouse_pieces_piecemeta AS meta
      ON meta.id = piece.piece_meta_id
INNER JOIN location_location AS location
      ON location.id = piece.primary_location_id
WHERE
      location.small_or_bulk_area = 2 -- 2 is bulk, 1 is small pick
      AND meta.name LIKE '@%' AND
LIMIT 50;