-- Group:
-- Name: Items on Website
-- Notes:
SELECT
item.item_number AS "g:id",
substring(CONCAT(item.item_number, ': ', item.item_descrip1) for 150) AS "g:title",
--substring(REPLACE(REPLACE(REPLACE(item_ext.item_mrkt_descrip, 'h3>', 'strong>'), '>', '>'), '<', '<') for 5000) AS "g:description",
substring(REPLACE(REPLACE(item_ext.item_mrkt_descrip, 'h3>', 'strong>'), '>', '>') for 5000) AS "g:description",
FORMAT('https://metersales.com/products/%s',item.item_id) AS "g:link",
(SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1) AS "g:image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 1), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 2), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 3), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 4), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 5), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 6), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 7), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 8), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 9), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 10), '') AS "g:additional_image_link",
(CASE WHEN itemsite_qtyonhand > 0 THEN 'in stock'
WHEN item.item_type = 'M' THEN 'preorder'
WHEN item.item_type = 'P' AND item.item_active = TRUE THEN 'preorder'
ELSE 'out of stock'
END) AS "g:availability",
(CASE WHEN itemsite_qtyonhand > 0 THEN current_date
WHEN itemsite_qtyonhand = 0 AND item.item_active = TRUE THEN -- AND item.item_type = 'M'
(SELECT current_date + (
(SELECT charopt.charopt_order FROM public.charopt WHERE charopt.charopt_value =
(SELECT charass.charass_value FROM public.charass WHERE charass.charass_char_id = 40 AND item.item_id = charass.charass_target_id)--40=*Estimated Lead Time
) % 1000))
WHEN itemsite_qtyonhand = 0 THEN current_date
END) AS "g:availability_date",
(CASE WHEN itemsite.itemsite_qtyonhand > 0 THEN FORMAT('%s USD', ROUND((itemsite.itemsite_value/itemsite.itemsite_qtyonhand), 2))
ELSE FORMAT('%s USD', ROUND(itemcost_actcost, 2))
END) AS "g:cost_of_goods_sold",
FORMAT('%s USD', ROUND(item.item_listprice, 2)) AS "g:price",
charass.charass_value AS "g:brand",
'no' AS "g:identifier_exists",
FORMAT('%s lb',(item.item_prodweight + item.item_packweight + 0.5)) AS "g:shipping_weight",
FORMAT('%s in', round(item_ext.item_pack_length + 2, 3)) AS "g:shipping_length",
FORMAT('%s in', round(item_ext.item_pack_width + 2, 3)) AS "g:shipping_width",
FORMAT('%s in', round(item_ext.item_pack_height + 2, 3)) AS "g:shipping_height"
FROM
public.item LEFT JOIN public.charass ON item.item_id = charass.charass_target_id,
xdruple.item_ext,
public.itemsite,
public.itemcost
WHERE
item.item_id = item_ext.item_item_id AND
item.item_id = itemsite.itemsite_item_id AND
item.item_id = itemcost.itemcost_item_id AND
charass.charass_char_id = 23 AND
charass.charass_target_type = 'I' AND
--item.item_id = <? value("item_code") ?> AND <? value("item_code") ?>
item.item_sold = TRUE AND
item.item_active = TRUE AND
item_ext.item_web_published = TRUE;