← back to Sanderson Onboard

scripts/reconcile.sql

13 lines

\set ON_ERROR_STOP on
-- Join retail colorways -> trade catalog via the D-code embedded in image filenames; flag confidence.
ALTER TABLE sanderson_catalog ADD COLUMN IF NOT EXISTS trade_code varchar;
ALTER TABLE sanderson_catalog ADD COLUMN IF NOT EXISTS colorway_match_verified boolean;
UPDATE sanderson_catalog SET trade_code = substring(image_url from '([A-Z]{2,5}[0-9]{4,7})_');
UPDATE sanderson_catalog c SET cost_usd=t.trade_usd, retail_usd=t.dw_retail_usd, trade_discount=0.50
  FROM sanderson_trade_catalog t WHERE c.trade_code=t.mfr_code;
UPDATE sanderson_catalog SET colorway_match_verified=FALSE WHERE trade_code IS NOT NULL;
UPDATE sanderson_catalog c SET colorway_match_verified=TRUE
  FROM sanderson_trade_catalog t
  WHERE c.trade_code=t.mfr_code AND c.color_name IS NOT NULL AND c.color_name<>''
    AND t.pattern_name ILIKE '%'||split_part(regexp_replace(c.color_name,'[/]',' ','g'),' ',1)||'%';