158 lines
4.5 KiB
SQL
158 lines
4.5 KiB
SQL
DROP VIEW IF EXISTS v_funnel_pricelist;
|
|
DROP VIEW IF EXISTS v_pricelist_wallpaper;
|
|
DROP VIEW IF EXISTS v_pricelist_external_products;
|
|
DROP VIEW IF EXISTS v_pricelist_other_products;
|
|
|
|
-- ***************************************************************************
|
|
-- v_pricelist_wallpaper
|
|
-- ***************************************************************************
|
|
CREATE VIEW v_pricelist_wallpaper
|
|
AS
|
|
SELECT 'Wallpaper' product,
|
|
ps.name segment,
|
|
CASE p.material_id
|
|
WHEN 1 THEN 'Standard'
|
|
WHEN 2 THEN 'Peel And Stick'
|
|
WHEN 4 THEN 'Premium'
|
|
END wallpaper_material,
|
|
m.name market,
|
|
m.currency,
|
|
p.price,
|
|
updated
|
|
FROM prices_wallpaper_m2 p
|
|
JOIN markets m
|
|
ON market_id = m.id
|
|
JOIN price_segments ps
|
|
ON segment_id = ps.id;
|
|
|
|
-- ***************************************************************************
|
|
-- v_pricelist_external_products
|
|
-- ***************************************************************************
|
|
CREATE VIEW v_pricelist_external_products
|
|
AS
|
|
SELECT CASE
|
|
WHEN epp.accessory ~ '^canvas' THEN 'Canvas'
|
|
ELSE 'Poster'
|
|
END product,
|
|
ps.name segment,
|
|
CASE epp.accessory
|
|
WHEN 'canvas-frame_slim' THEN 'Canvas Frame: Slim'
|
|
WHEN 'frame_black' THEN 'Frame: Black'
|
|
WHEN 'frame_natural-wood' THEN 'Frame: Frame Natural Wood'
|
|
WHEN 'frame_white' THEN 'Frame: White'
|
|
WHEN 'hanger_black' THEN 'Hanger: Black'
|
|
WHEN 'hanger_dark-wood' THEN 'Hanger: Dark Wood'
|
|
WHEN 'hanger_natural-wood' THEN 'Hanger: Natural Wood'
|
|
WHEN 'hanger_none' THEN 'None'
|
|
WHEN 'hanger_white' THEN 'Hanger: White'
|
|
END accessory,
|
|
epp.width_mm,
|
|
epp.height_mm,
|
|
m.name market,
|
|
m.currency,
|
|
pep.price,
|
|
pep.updated
|
|
FROM prices_external_products pep
|
|
JOIN external_print_products epp
|
|
ON epp.external_product_id = pep.external_product_id
|
|
JOIN markets m
|
|
ON pep.market_id = m.id
|
|
JOIN price_segments ps
|
|
ON segment_id = ps.id;
|
|
|
|
-- ***************************************************************************
|
|
-- v_pricelist_other_products
|
|
-- ***************************************************************************
|
|
CREATE VIEW v_pricelist_other_products
|
|
AS
|
|
SELECT 'Wallpaper Kit' product,
|
|
m.name market,
|
|
m.currency,
|
|
wallpaper_kit_price price,
|
|
updated
|
|
FROM prices_other_products pop
|
|
JOIN markets m
|
|
ON pop.market_id = m.id
|
|
|
|
UNION
|
|
|
|
SELECT 'Wallpaper Kit Peel And Stick' product,
|
|
m.name market,
|
|
m.currency,
|
|
peel_and_stick_wallpaper_kit_price price,
|
|
updated
|
|
FROM prices_other_products pop
|
|
JOIN markets m
|
|
ON pop.market_id = m.id
|
|
|
|
UNION
|
|
|
|
SELECT 'Wallpaper Sample' product,
|
|
m.name market,
|
|
m.currency,
|
|
wallpaper_sample_price price,
|
|
updated
|
|
FROM prices_other_products pop
|
|
JOIN markets m
|
|
ON pop.market_id = m.id;
|
|
|
|
|
|
-- ***************************************************************************
|
|
-- v_funnel_pricelist
|
|
-- ***************************************************************************
|
|
CREATE VIEW v_funnel_pricelist
|
|
AS
|
|
SELECT product,
|
|
segment,
|
|
wallpaper_material,
|
|
width_mm,
|
|
height_mm,
|
|
accessory,
|
|
market,
|
|
currency,
|
|
price,
|
|
updated
|
|
FROM (
|
|
SELECT product,
|
|
segment,
|
|
wallpaper_material,
|
|
NULL width_mm,
|
|
NULL height_mm,
|
|
NULL accessory,
|
|
market,
|
|
currency,
|
|
price,
|
|
updated
|
|
FROM v_pricelist_wallpaper
|
|
|
|
UNION
|
|
|
|
SELECT product,
|
|
segment,
|
|
NULL,
|
|
width_mm,
|
|
height_mm,
|
|
accessory,
|
|
market,
|
|
currency,
|
|
price,
|
|
updated
|
|
FROM v_pricelist_external_products
|
|
|
|
UNION
|
|
|
|
SELECT product,
|
|
NULL segment,
|
|
NULL wallpaper_material,
|
|
NULL width_mm,
|
|
NULL height_mm,
|
|
NULL accessory,
|
|
market,
|
|
currency,
|
|
price,
|
|
updated
|
|
FROM v_pricelist_other_products
|
|
) s;
|
|
|
|
GRANT SELECT ON v_funnel_pricelist TO funnel;
|