357 lines
10 KiB
PL/PgSQL
357 lines
10 KiB
PL/PgSQL
--explain (analyze, buffers)
|
|
DROP FUNCTION IF EXISTS rlarp.plcore_seq;
|
|
CREATE FUNCTION rlarp.plcore_seq()
|
|
RETURNS TABLE (
|
|
listcode text
|
|
,stlc text
|
|
,coltiers text
|
|
,uomp text
|
|
,branding text
|
|
,accs text
|
|
,suff text
|
|
,colors text
|
|
,pack_qty text
|
|
,pack_price text
|
|
,mp_qty text
|
|
,mp_price text
|
|
,bulk_qty text
|
|
,bulk_price text
|
|
,seq text
|
|
,descr text
|
|
,priceg text
|
|
,image_url text
|
|
,compatible text
|
|
,header text
|
|
,header_sort int
|
|
,detail_sort int
|
|
)
|
|
LANGUAGE plpgsql AS $function$
|
|
|
|
BEGIN
|
|
DROP TABLE IF EXISTS stage3;
|
|
|
|
CREATE TEMP TABLE 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
|
|
|
|
RETURN QUERY
|
|
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 --where c3_price->>1 IS NOT NULL
|
|
SELECT
|
|
b.listcode
|
|
,b.stlc
|
|
--,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
|
|
,b.listcode||'-'||b.stlc||'-'||b.uomp||'-'||row_number() OVER (PARTITION BY b.listcode, b.stlc, b.uomp ORDER BY b.pgroup ASC) seq
|
|
,m.descr
|
|
,m.priceg
|
|
,m.image_url
|
|
,m.compatible
|
|
,m.header
|
|
,m.header_sort::int
|
|
,m.detail_sort::int
|
|
FROM
|
|
stage5 b
|
|
LEFT OUTER JOIN rlarp.molds m ON
|
|
m.stlc = b.stlc
|
|
ORDER BY
|
|
b.stlc,
|
|
b.pgroup;
|
|
--if "colors" column is null there are no part numbers available for this price point
|
|
END
|
|
$function$;
|