all-products.sql

SELECT
       product.code AS itemcode,
       floor(piece.cached_possible_stock_available_count) AS stock_available,
       product.cached_name AS list_name,
       product.cached_%s_price AS price,
       barcode.barcode AS upc,
       SUBSTRING(product.code, 0, 3) AS category,
       CASE WHEN meta.is_organic THEN 'TRUE'
            ELSE 'FALSE'
       END AS organic,
       brand.name AS brandname,
       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 statuscode,
       piece.name AS std_pack,
       piece.shipping_weight_lb AS weight,
       CASE WHEN meta.is_shippable_ups THEN 'Y'
            ELSE 'N'
       END AS upsship,
       CASE WHEN char_length(product.cached_primary_imagefield) < 1 THEN ''
            ELSE CONCAT('https://media.azurestandard.com/', product.cached_primary_imagefield)
       END AS big_picture_rgb,
       CASE WHEN char_length(product.cached_primary_imagefield) < 1 THEN ''
            ELSE CONCAT('https://media.azurestandard.com/', 
                         translate(product.cached_primary_imagefield, '.', '_'), 
                        '_50x50_q85.jpg')
       END AS small_picture_rgb,
       CONCAT('http://www.azurestandard.com/shop/product/', product.id, '/') AS product_pages,
       CASE 
         WHEN sales.custom_percent_off > 0 THEN 'Y'
         WHEN sales.custom_amount_off > 0 THEN 'Y'
         WHEN vendor.percent_off > 0 THEN 'Y'
         WHEN vendor.amount_off > 0 THEN 'Y'
       ELSE 'N'
       END AS on_sale,
       CASE 
         WHEN COALESCE(sales.custom_percent_off, vendor.percent_off, 0) > 0 THEN
              (COALESCE(sales.custom_percent_off, 0) + COALESCE(vendor.percent_off, 0))
         WHEN COALESCE (sales.custom_amount_off, vendor.amount_off, 0) > 0 THEN
              ((COALESCE(sales.custom_amount_off, 0) + COALESCE(vendor.amount_off, 0)) 
                - product.cached_%s_price) / product.cached_%s_price * 100
         ELSE 0
       END AS percent_off,
       CONCAT('http://www.azurestandard.com/data/product-description/', product.id) AS description
FROM products_simpleproduct AS simpleproduct
INNER JOIN products_product AS product
      ON product.id = simpleproduct.product_ptr_id
INNER JOIN warehouse_pieces_piece AS piece
      ON simpleproduct.piece_id = piece.id
LEFT JOIN warehouse_pieces_piece_barcodes AS piece_barcode
      ON piece_barcode.piece_id = piece.id
LEFT JOIN barcodes_barcode AS barcode
      ON barcode.id = piece_barcode.barcode_id
INNER JOIN warehouse_pieces_piecemeta AS meta
      ON meta.id = piece.piece_meta_id
LEFT JOIN brands_brand AS brand
      ON meta.brand_id = brand.id
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 = %s
                                 LIMIT 1
                                 )
LEFT JOIN vendor_vendorpromotionline AS vendor
     ON sales.vendor_promotion_line_id = vendor.id
WHERE
      COALESCE((
       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
      ), 0) IN (0, 1, 2, 4, 6, 7, 8, 9)