445 lines
13 KiB
SQL
445 lines
13 KiB
SQL
DROP TABLE IF EXISTS stage3;
|
|
CREATE TEMP TABLE IF NOT EXISTS stage3 AS (
|
|
WITH
|
|
--------------------------helper table to choose column based on uom-----------------------------------------------------------------
|
|
pivot_guide AS (
|
|
SELECT * FROM ( VALUES
|
|
('BDL','BDL','01'),
|
|
('BDL','PLT','02'),
|
|
('SLV','SLV','01'),
|
|
('SLV','PLT','02'),
|
|
('CSE','CSE','01'),
|
|
('CSE','PLT','02'),
|
|
('PLT','PLT','03')
|
|
) x(uomp,vol_uom,col)
|
|
-------------------------------------------------------------------------------------------------------------------------------------
|
|
)
|
|
,stage1 AS (
|
|
SELECT
|
|
p.listcode
|
|
,p.stlc
|
|
,p.listcode||'-'||p.stlc||'.'||COALESCE(p.coltier,'')||'.'||COALESCE(p.branding,'')||'.'||COALESCE(p.accs,'')||'.'||COALESCE(p.suff,'')||'.'||COALESCE(p.uomp)||'-'||g.col pkey1
|
|
,p.coltier
|
|
,p.branding
|
|
,p.accs
|
|
,p.suff
|
|
,p.uomp
|
|
,p.vol_uom
|
|
,p.vol_qty
|
|
,p.vol_price
|
|
,p.orig_col
|
|
,g.col
|
|
FROM
|
|
rlarp.plcore p
|
|
LEFT OUTER JOIN pivot_guide g ON
|
|
g.uomp = p.uomp
|
|
AND g.vol_uom = p.vol_uom
|
|
WHERE
|
|
p.vol_price IS NOT NULL
|
|
--AND listcode = 'GUAU'
|
|
)
|
|
-----run this to check for duplicate rows-------------------------------------------------------------
|
|
--SELECT * FROM (SELECT *, count(*) OVER (PARTITION BY pkey1) dup FROM stage1) x where dup > 1
|
|
-------------------------------------------------------------------------------------------------------------------------------------
|
|
,stage2 AS (
|
|
SELECT
|
|
p.listcode
|
|
,p.stlc
|
|
,p.pkey1
|
|
,p.coltier
|
|
,p.branding
|
|
,p.accs
|
|
,p.suff
|
|
,p.uomp
|
|
,p.vol_uom
|
|
,p.vol_qty
|
|
,p.vol_price
|
|
,p.orig_col
|
|
,p.col
|
|
,i.item
|
|
--every part is linked so all the colors are already here
|
|
,i.colc
|
|
,pkey1||'-'||substring(i.item,13,3) pkey2
|
|
--look at this position to identify potential pack sizes without doing the conversion on every item
|
|
,substring(i.item,13,3) nc
|
|
,row_number() OVER (PARTITION BY pkey1, substring(i.item,13,3)) rn
|
|
FROM
|
|
stage1 p
|
|
LEFT OUTER JOIN "CMS.CUSLG".itemm i ON
|
|
i.stlc = p.stlc
|
|
AND i.coltier = p.coltier
|
|
AND COALESCE(p.branding,'') = COALESCE(i.branding,'')
|
|
AND COALESCE(p.accs,'') = COALESCE(i.accs_ps,'')
|
|
AND COALESCE(p.suff,'') = COALESCE(i.suffix,'')
|
|
AND i.uomp = p.uomp
|
|
AND i.aplnt <> 'I'
|
|
)
|
|
--SELECT * FROM stage2 order by pkey1
|
|
---------------see how many potnential pack sizes there are per price point-------------------------------------------------
|
|
--SELECT * FROM (SELECT *, count(*) OVER (PARTITION BY pkey1) dup FROM stage2 WHERE rn = 1) X WHERE dup > 1 ORDER BY pkey2 ASC
|
|
,items AS (
|
|
SELECT
|
|
jsonb_agg( DISTINCT
|
|
jsonb_build_object(
|
|
'partn', item
|
|
,'fu', vol_uom
|
|
,'tu','PC'
|
|
)
|
|
) conv
|
|
FROM
|
|
stage2
|
|
WHERE
|
|
rn = 1
|
|
)
|
|
--do the conversion-------------------------------------------------------------
|
|
, conversion AS (
|
|
SELECT
|
|
rslt.p
|
|
,rslt.f
|
|
,rslt.t
|
|
,rslt.nm
|
|
,rslt.dm
|
|
FROM
|
|
items
|
|
JOIN LATERAL rlarp.uom_array(conv) rslt ON true
|
|
)
|
|
--SELECT * FROM CONVERSION
|
|
,stage3 AS (
|
|
SELECT
|
|
p.listcode
|
|
,p.stlc
|
|
,p.pkey1
|
|
,p.coltier
|
|
,p.branding
|
|
,p.accs
|
|
,p.suff
|
|
,p.uomp
|
|
,p.vol_uom
|
|
,p.vol_qty
|
|
,p.vol_price
|
|
,p.orig_col
|
|
,p.col
|
|
--,p.item
|
|
--,p.pkey2
|
|
--,string_agg(DISTINCT item, ', ') items
|
|
,jsonb_agg(DISTINCT p.colc) FILTER (WHERE p.colc IS NOT NULL) colors --this array will have to be aggregated again when columns pivot out
|
|
--,string_agg(DISTINCT to_char(c.nm/c.dm,'FM999,999'),' | ') FILTER(WHERE rn = 1) price_qty
|
|
,jsonb_agg(DISTINCT to_char(c.nm/c.dm,'FM999,999')) FILTER(WHERE rn = 1 AND c.nm IS NOT NULL) price_qty
|
|
FROM
|
|
stage2 p
|
|
LEFT OUTER JOIN conversion c ON
|
|
c.p = p.item
|
|
AND c.f = p.vol_uom
|
|
WHERE
|
|
TRUE
|
|
--this filter shoudl not be used since every color is linked in stage 2 exists on rn <> 1
|
|
--AND rn = 1
|
|
GROUP BY
|
|
p.listcode
|
|
,p.stlc
|
|
,p.pkey1
|
|
,p.coltier
|
|
,p.branding
|
|
,p.accs
|
|
,p.suff
|
|
,p.uomp
|
|
,p.vol_uom
|
|
,p.vol_qty
|
|
,p.vol_price
|
|
,p.orig_col
|
|
,p.col
|
|
)
|
|
SELECT * FROM stage3
|
|
) WITH DATA;
|
|
|
|
--select * from stage3
|
|
|
|
WITH
|
|
no_bulk AS (
|
|
SELECT
|
|
b.listcode
|
|
,b.stlc
|
|
,b.coltier
|
|
,b.branding
|
|
,b.accs
|
|
,b.suff
|
|
--,pkey1
|
|
,b.uomp
|
|
,jsonb_arr_aggcd(b.colors) colors
|
|
,jsonb_arr_aggcd(b.price_qty) FILTER (WHERE b.col = '01' AND b.price_qty IS NOT NULL) c1_vol
|
|
,jsonb_agg(DISTINCT b.vol_price) FILTER (WHERE b.col = '01') c1_price
|
|
,jsonb_arr_aggcd(b.price_qty) FILTER (WHERE b.col = '02' AND b.price_qty IS NOT NULL) c2_vol
|
|
,jsonb_agg(DISTINCT b.vol_price) FILTER (WHERE b.col = '02') c2_price
|
|
FROM
|
|
stage3 b
|
|
WHERE
|
|
b.col <> '03'
|
|
GROUP BY
|
|
b.listcode
|
|
,b.stlc
|
|
,b.coltier
|
|
,b.branding
|
|
,b.accs
|
|
,b.suff
|
|
,b.uomp
|
|
)
|
|
--SELECT * FROM no_bulk
|
|
,only_bulk AS (
|
|
SELECT
|
|
b.listcode
|
|
,b.stlc
|
|
,b.coltier
|
|
,b.branding
|
|
,b.accs
|
|
,b.suff
|
|
--,pkey1
|
|
,b.uomp
|
|
,jsonb_arr_aggcd(b.colors) FILTER (WHERE b.colors IS NOT NULL) colors
|
|
,jsonb_arr_aggcd(b.price_qty) FILTER (WHERE b.col = '03' AND b.price_qty IS NOT NULL) c3_vol
|
|
,jsonb_agg(DISTINCT b.vol_price) FILTER (WHERE b.col = '03') c3_price
|
|
FROM
|
|
stage3 b
|
|
WHERE
|
|
b.col = '03'
|
|
GROUP BY
|
|
b.listcode
|
|
,b.stlc
|
|
,b.coltier
|
|
,b.branding
|
|
,b.accs
|
|
,b.suff
|
|
,b.uomp
|
|
)
|
|
--SELECT * FROM only_bulk
|
|
--SELECT * FROM only_bulk where c3_price->>1 IS NOT NULL
|
|
--SELECT * FROM no_bulk where c1_price->>1 IS NOT NULL
|
|
----full outer join in case there are bulk prices with no case pricing----
|
|
,stage4 AS (
|
|
SELECT
|
|
COALESCE(n.listcode,b.listcode) listcode
|
|
,COALESCE(n.stlc,b.stlc) stlc
|
|
,COALESCE(n.coltier,b.coltier) coltier
|
|
,COALESCE(n.branding,b.branding,'') branding
|
|
,COALESCE(n.accs,b.accs,'') accs
|
|
,COALESCE(n.suff,b.suff,'') suff
|
|
,COALESCE(n.uomp, b.uomp) uomp
|
|
,public.jsonb_concat_distinct_arr(COALESCE(n.colors,'[]'::jsonb),COALESCE(b.colors,'[]'::jsonb)) colors_array
|
|
--,(SELECT string_agg(ae,', ') FROM (SELECT ae FROM jsonb_array_elements_text(public.jsonb_concat_distinct_arr(COALESCE(n.colors,'[]'::jsonb),COALESCE(b.colors,'[]'::jsonb))) ae) x) colors
|
|
,n.c1_vol
|
|
---there better not be multiple prices---
|
|
,COALESCE(n.c1_price,'[]'::jsonb) c1_price
|
|
,n.c2_vol
|
|
,COALESCE(n.c2_price,'[]'::jsonb) c2_price
|
|
,b.c3_vol
|
|
,COALESCE(b.c3_price,'[]'::jsonb) c3_price
|
|
,COALESCE(n.listcode,b.listcode)||'.'||COALESCE(n.stlc,b.stlc)||'.'||COALESCE(n.uomp, b.uomp)||'.'||COALESCE(n.branding,b.branding,'')||'.'||COALESCE(n.accs,b.accs,'')||'.'||COALESCE(n.suff,b.suff,'') item
|
|
FROM
|
|
no_bulk n
|
|
FULL OUTER JOIN only_bulk b ON
|
|
b.listcode = n.listcode
|
|
AND b.stlc = n.stlc
|
|
AND b.coltier = n.coltier
|
|
AND COALESCE(b.branding,'') = COALESCE(n.branding,'')
|
|
AND COALESCE(b.accs ,'') = COALESCE(n.accs ,'')
|
|
AND COALESCE(b.suff ,'') = COALESCE(n.suff ,'')
|
|
)
|
|
,test_multiprice AS (
|
|
--should return no rows
|
|
SELECT * FROM stage4 where c1_price->>1 IS NOT NULL OR c2_price->>1 IS NOT NULL OR c3_price->>1 IS NOT NULL
|
|
)
|
|
--SELECT * FROM test_multiprice
|
|
--SELECT * FROM stage4 ORDER BY item ASC
|
|
------group to the price level and aggregate attributes----------------------
|
|
,stage5 AS (
|
|
SELECT
|
|
b.listcode
|
|
,b.stlc
|
|
,jsonb_agg(DISTINCT b.coltier) coltier
|
|
,b.uomp
|
|
,jsonb_agg(DISTINCT b.branding ORDER BY b.branding DESC) branding
|
|
,jsonb_agg(DISTINCT b.accs ORDER BY b.accs DESC) accs
|
|
,jsonb_agg(DISTINCT b.suff ORDER BY b.suff DESC) suff
|
|
,jsonb_arr_aggcd(b.colors_array) colors_array
|
|
,jsonb_arr_aggcd(b.c1_vol) c1_vol
|
|
,b.c1_price
|
|
,jsonb_arr_aggcd(b.c2_vol) c2_vol
|
|
,b.c2_price
|
|
,jsonb_arr_aggcd(b.c3_vol) c3_vol
|
|
,b.c3_price
|
|
--,b.item
|
|
,b.c1_price||b.c2_price||b.c3_price pgroup
|
|
--,jsonb_build_object('pack',c1_price,'mp',c2_price,'bulk',c3_price) pgroupo
|
|
--how to sort 3 items, one of which may be null
|
|
--start by attempting to evaluate non-null columns
|
|
FROM
|
|
stage4 b
|
|
GROUP BY
|
|
b.listcode
|
|
,b.stlc
|
|
,b.uomp
|
|
--,b.branding
|
|
--,b.accs
|
|
--,b.suff
|
|
,b.c1_price
|
|
,b.c2_price
|
|
,b.c3_price
|
|
--,b.item
|
|
ORDER BY
|
|
stlc ASC
|
|
)
|
|
--SELECT * FROM stage5
|
|
,dsort AS (
|
|
SELECT
|
|
m."header"
|
|
,m.header_sort::int header_sort
|
|
,m.detail_sort::int detail_sort
|
|
,m.stlc
|
|
,f.flag
|
|
,COALESCE(rstt.rn,0) comp_sort
|
|
,COALESCE(rstt.c,m.stlc) stlcc
|
|
,mj.descr
|
|
--,mj."header"
|
|
FROM
|
|
rlarp.molds m
|
|
CROSS JOIN ( VALUES
|
|
('base'),
|
|
('compatible')
|
|
) AS f(flag)
|
|
LEFT JOIN LATERAL regexp_split_to_table(m.compatible,', ') WITH ORDINALITY rstt(c, rn) ON
|
|
f.flag = 'compatible'
|
|
LEFT OUTER JOIN rlarp.molds mj ON
|
|
mj.stlc = COALESCE(rstt.c,m.stlc)
|
|
WHERE
|
|
true
|
|
AND NOT (f.flag = 'compatible' AND COALESCE(rstt.rn,0) = 0)
|
|
AND NOT (f.flag = 'compatible' AND COALESCE(m.header,'') = '')
|
|
AND m.status <> 'I'
|
|
--only list active compatible items
|
|
AND mj.status <> 'I'
|
|
--exclude compatible items that have their own price list header
|
|
AND NOT (f.flag = 'compatible' AND mj."header" IS NOT NULL)
|
|
ORDER BY
|
|
m.header_sort
|
|
,m.detail_sort
|
|
,comp_sort
|
|
)
|
|
--SELECT * FROM dsort where stlc = 'SPH1DPI0'
|
|
--SELECT * FROM dsort WHERE header = 'Standard Inserts'
|
|
--SELECT * FROM stage5 --where c3_price->>1 IS NOT NULL
|
|
,stage6 AS (
|
|
SELECT
|
|
s."header"
|
|
,s.header_sort
|
|
,s.detail_sort
|
|
,s.comp_sort
|
|
,b.listcode
|
|
,s.flag
|
|
,b.stlc
|
|
,s.descr
|
|
--,b.coltier
|
|
,public.jsonb_array_string_agg(b.coltier,', ') colortiers
|
|
,b.uomp
|
|
,public.jsonb_array_string_agg(b.branding,', ') branding
|
|
,public.jsonb_array_string_agg(b.accs,', ') accs
|
|
,public.jsonb_array_string_agg(b.suff,', ') suff
|
|
,public.jsonb_array_string_agg(b.colors_array,', ') colors
|
|
,public.jsonb_array_string_agg(b.c1_vol,' | ') pack_qty
|
|
,to_char((b.c1_price->>0)::numeric,'FM999,999.00') pack_price
|
|
,public.jsonb_array_string_agg(b.c2_vol,', | ') mp_qty
|
|
,to_char((b.c2_price->>0)::numeric,'FM999,999.00') mp_price
|
|
,public.jsonb_array_string_agg(b.c3_vol,' | ') bulk_qty
|
|
,to_char((b.c3_price->>0)::numeric,'FM999,999.00') bulk_price
|
|
,row_number() OVER (PARTITION BY b.listcode, s.stlc, s.comp_sort ORDER BY b.pgroup ASC) seq
|
|
FROM
|
|
dsort s
|
|
INNER JOIN stage5 b ON
|
|
b.stlc = s.stlcc
|
|
WHERE
|
|
TRUE
|
|
--AND listcode = 'GUAU'
|
|
--exclude price points that don't have any part numbers otherwise you may have sequences above
|
|
--that are not real
|
|
AND b.colors_array IS NOT NULL
|
|
--ORDER BY
|
|
-- s.header_sort
|
|
-- ,s.detail_sort
|
|
-- ,s.comp_sort
|
|
--if "colors" column is null there are no part numbers available for this price point
|
|
)
|
|
--SELECT * FROM stage6 WHERE detail_sort = 1
|
|
,get_header AS (
|
|
SELECT DISTINCT
|
|
header
|
|
,header_sort
|
|
,0 detail_sort
|
|
,0 comp_sort
|
|
,listcode
|
|
,'header' flag
|
|
,header
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,0 seq
|
|
FROM
|
|
stage6
|
|
WHERE
|
|
header IS NOT NULL
|
|
)
|
|
,stage7 AS (
|
|
SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT * FROM stage6 WHERE
|
|
TRUE
|
|
--only list the first price option for compatible products
|
|
--AND NOT (flag = 'compatible' AND seq <> 1)
|
|
UNION ALL
|
|
SELECT * FROM get_header
|
|
) x
|
|
ORDER BY
|
|
header_sort ASC
|
|
,detail_sort ASC
|
|
,comp_sort ASC
|
|
,seq ASC
|
|
)
|
|
SELECT
|
|
s.header
|
|
,s.header_sort
|
|
,s.detail_sort
|
|
,s.comp_sort
|
|
,s.seq
|
|
,s.listcode
|
|
,s.flag
|
|
,s.stlc
|
|
,s.descr
|
|
,s.colortiers
|
|
,s.uomp
|
|
,s.branding
|
|
,s.accs
|
|
,s.suff
|
|
,s.colors
|
|
,s.pack_qty
|
|
,CASE WHEN s.pack_qty IS NULL THEN null::text ELSE s.pack_price END
|
|
,s.mp_qty
|
|
,CASE WHEN s.mp_qty IS NULL THEN null::text ELSE s.mp_price END
|
|
,s.bulk_qty
|
|
,CASE WHEN s.bulk_qty IS NULL THEN null::text ELSE s.bulk_price END
|
|
FROM
|
|
stage7 s
|
|
WHERE
|
|
TRUE
|
|
AND s.listcode = 'GUAU'
|
|
--only list the first price point for compatible items
|
|
AND NOT (s.flag = 'compatible' AND s.seq <> 1)
|
|
AND header IS NOT NULL |