tmp.sql

SELECT
  product.code AS "Product Code",
  product.cached_name AS "Product Name",
  floor(piece.cached_stock_available_count) AS "Current Inventory",
  floor(piece.cached_possible_stock_available_count) AS "Current Inventory including case breakdown",
  floor(piece.cached_stock_on_orders_count) AS "Inventory in Transit",

  CASE
    WHEN sales.custom_percent_off > 0 THEN 'Y'
    WHEN sales.custom_amount_off > 0 THEN 'Y'
    WHEN promotion.percent_off > 0 THEN 'Y'
    WHEN promotion.amount_off > 0 THEN 'Y'
    ELSE 'N'
  END AS "On Sale",

  CASE
    WHEN COALESCE(sales.custom_percent_off, promotion.percent_off, 0) > 0 THEN
         (COALESCE(sales.custom_percent_off, 0) + COALESCE(promotion.percent_off, 0))
  WHEN COALESCE (sales.custom_amount_off, promotion.amount_off, 0) > 0 THEN
       ((COALESCE(sales.custom_amount_off, 0) + COALESCE(promotion.amount_off, 0))
       - product.cached_retail_price) / product.cached_retail_price * 100
  ELSE 0
  END AS "Percent Off",

  CASE (SELECT status
        FROM products_productstatus
        WHERE product_id = product.id AND (end_date > now() OR end_date IS NULL)
        ORDER BY id
        DESC LIMIT 1)
    WHEN 1 THEN 'Featured'
    WHEN 2 THEN 'New'
    WHEN 3 THEN 'Discontinued'
    WHEN 4 THEN 'Soon to be discontinued'
    WHEN 5 THEN 'In House'
    WHEN 6 THEN 'Out short term'
    WHEN 7 THEN 'Seasonally Out'
    WHEN 8 THEN 'Out long term'
    WHEN 9 THEN 'New on sale'
    ELSE 'Active'
  END AS "Status",
  
  floor(piece.cached_predicted_daily_sales_rate) AS "Avg Sold per Day",
  
  (SELECT
      stock.expiration_date
    FROM inventory_stock AS stock
    WHERE
      stock.expiration_date > NOW()
      AND stock.piece_id = piece.id
    ORDER BY stock.expiration_date
    LIMIT 1) AS "Next Expiration Date",

  CASE
    WHEN (SELECT
            COALESCE(floor(piece.cached_predicted_daily_sales_rate), 1) *
            EXTRACT(day from (SELECT
                                stock.expiration_date
                              FROM inventory_stock AS stock
                              WHERE
                                stock.expiration_date > NOW()
                                AND stock.piece_id = piece.id
                              ORDER BY stock.expiration_date
                              LIMIT 1)
                        - NOW()
                        )
           ) < piece.cached_stock_available_count
     THEN 'Y'
     ELSE 'N'
   END AS "Warning! Product Likely to Expire"

FROM vendor_vendor AS vendor
LEFT JOIN vendor_vendorpiece AS vp ON vp.vendor_id = vendor.id
LEFT JOIN warehouse_pieces_piece AS piece ON piece.id = vp.piece_id
LEFT JOIN products_product AS product ON product.piece_id = piece.id
LEFT JOIN inventory_stock AS stock ON stock.piece_id = piece.id AND stock.expiration_date > NOW()
LEFT JOIN sales_sheets_salessheetline AS sales
      ON sales.product_id = product.id AND sales.sales_sheet_id = (
        -- Current price sheet
       SELECT id
       FROM sales_sheets_salessheet
       WHERE
         end_date > now() AND start_date < now()
         AND price_level = 1 -- retail
         AND id not in (228, 230) -- overstock sales sheets
       LIMIT 1
       )
LEFT JOIN vendor_vendorpromotionline AS promotion ON sales.vendor_promotion_line_id = promotion.id       
WHERE
  vendor.name LIKE 'SPRINGFIELD%'
  AND product.code IS NOT NULL
GROUP BY product.id, piece.id, sales.id, promotion.id
ORDER BY product.code;