789 lines
25 KiB
PL/PgSQL
789 lines
25 KiB
PL/PgSQL
DROP FUNCTION IF EXISTS rlarp.plcore_build_pretty;
|
|
CREATE FUNCTION rlarp.plcore_build_pretty(_price_lev text, _segment_regex text)
|
|
RETURNS TABLE (
|
|
-- header text
|
|
--,header_sort int
|
|
-- detail_sort int
|
|
--,comp_sort bigint
|
|
--,seq bigint
|
|
--,listcode text
|
|
--,flag text
|
|
"Product" text
|
|
,"Description" text
|
|
,"ColTier" text
|
|
,"Pack" text
|
|
,"Brnd" text
|
|
,"Accs" text
|
|
,"Opt" text
|
|
,"Colors" text
|
|
,"Qty (M)1" text
|
|
,"Price (M)1" text
|
|
,"Price (CS)1" text
|
|
,"Qty (M)2" text
|
|
,"Price (M)2" text
|
|
,"Price (CS)2" text
|
|
,"Qty (M)3" text
|
|
,"Price (M)3" text
|
|
,"Price (PL)3" text
|
|
,flag text
|
|
,dsort text
|
|
,band boolean
|
|
,currency text
|
|
,segment text
|
|
)
|
|
LANGUAGE plpgsql AS $function$
|
|
BEGIN
|
|
|
|
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'),
|
|
('PC','PC','01'),
|
|
('PC','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 IN (SELECT DISTINCT jbplcd FROM "CMS.CUSLG".iprcbhc i WHERE jbplvl = 'U.BOC.DI' AND current_date BETWEEN jbfdat AND jbtdat)
|
|
AND listcode IN (SELECT DISTINCT jbplcd FROM "CMS.CUSLG".iprcbhc i WHERE jbplvl = _price_lev AND current_date BETWEEN jbfdat AND jbtdat)
|
|
--only pull target listcodes--
|
|
AND listcode ~ _segment_regex
|
|
)
|
|
-----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'
|
|
AND i.ming <> 'C10' --exclude packed for retail
|
|
AND i.descr !~ ' AUTO ' --exclude automation packaging
|
|
)
|
|
--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
|
|
,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
|
|
,jsonb_agg(DISTINCT to_char(c.nm/c.dm,'999,999')) FILTER(WHERE rn = 1 AND c.nm IS NOT NULL) price_qty
|
|
--,jsonb_agg(c.nm/c.dm) FILTER(WHERE rn = 1 AND c.nm IS NOT NULL) price_qty_num
|
|
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 jsonb_arr_aggcd(colors) 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 ORDER BY 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 ORDER BY 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 ORDER BY 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 ,'')
|
|
)
|
|
--select * from stage4
|
|
,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)
|
|
AND colors_array IS NOT NULL
|
|
AND jsonb_array_length(c1_vol) > 0
|
|
AND jsonb_array_length(c2_vol) > 0
|
|
AND jsonb_array_length(c3_vol) > 0
|
|
)
|
|
--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 ASC) accs
|
|
,jsonb_agg(DISTINCT b.suff ORDER BY b.suff ASC) 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'
|
|
---dont list trays for nursery items---
|
|
AND NOT m.glec ~ '1NU'
|
|
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
|
|
,ltrim(public.jsonb_array_string_agg(b.c1_vol,' ')) pack_qty
|
|
,to_char((b.c1_price->>0)::numeric,'FM999,999.00') pack_price
|
|
,to_char(CASE WHEN jsonb_array_length(b.c1_vol) = 1 THEN (b.c1_price->>0)::numeric/1000*(replace(b.c1_vol->>0,',',''))::numeric ELSE null::numeric END,'FM999,999.00') price_per_pack
|
|
,ltrim(public.jsonb_array_string_agg(b.c2_vol,' ')) mp_qty
|
|
,to_char((b.c2_price->>0)::numeric,'FM999,999.00') mp_price
|
|
----------------------------------------this price divided by c1_vol is price per case instead of c2_vol which would give price per pallet--------------------------------------------
|
|
,to_char(CASE WHEN jsonb_array_length(b.c1_vol) = 1 THEN (b.c2_price->>0)::numeric/1000*(replace(b.c1_vol->>0,',',''))::numeric ELSE null::numeric END,'FM999,999.00') price_per_mp
|
|
,ltrim(public.jsonb_array_string_agg(b.c3_vol,' ')) bulk_qty
|
|
,to_char((b.c3_price->>0)::numeric,'FM999,999.00') bulk_price
|
|
,to_char(CASE WHEN jsonb_array_length(b.c3_vol) = 1 THEN (b.c3_price->>0)::numeric/1000*(replace(b.c3_vol->>0,',',''))::numeric ELSE null::numeric END,'FM999,999.00') price_per_bulk
|
|
,row_number() OVER (PARTITION BY b.listcode, s.stlc, s.comp_sort ORDER BY b.pgroup ASC) seq
|
|
,substring(listcode,4,1) currency
|
|
,substring(listcode,1,1) segment
|
|
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
|
|
----any item that has a compatible, but it's base item gets dropped leaving only the compatible item
|
|
,orphans AS (
|
|
SELECT
|
|
detail_sort
|
|
,jsonb_agg(DISTINCT s.flag) jflag
|
|
FROM
|
|
stage6 s
|
|
GROUP BY
|
|
detail_sort
|
|
HAVING
|
|
NOT jsonb_agg(DISTINCT s.flag) @? '$[*] ? (@ == "base")'
|
|
)
|
|
,get_header AS (
|
|
SELECT
|
|
s.header
|
|
,s.header_sort
|
|
,0 detail_sort
|
|
,0 comp_sort
|
|
,null::text listcode
|
|
,'header' flag
|
|
,s.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
|
|
,null::text
|
|
,null::text
|
|
,null::text
|
|
,0 seq
|
|
,substring(listcode,4,1) currency
|
|
,string_agg(DISTINCT substring(listcode,1,1),', ') segment
|
|
FROM
|
|
stage6 s
|
|
WHERE
|
|
s.header IS NOT NULL
|
|
GROUP BY
|
|
s.header
|
|
,s.header_sort
|
|
,substring(listcode,4,1)
|
|
)
|
|
,stage7 AS (
|
|
SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT * FROM stage6 WHERE
|
|
TRUE
|
|
AND detail_sort NOT IN (SELECT detail_sort FROM orphans)
|
|
--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
|
|
)
|
|
,colors AS (
|
|
SELECT
|
|
COALESCE(min(colcode) FILTER (WHERE c.col = 0) || ' - ',' ') || RPAD(COALESCE(min(coldesc) FILTER (WHERE c.col = 0),''),31) ||
|
|
COALESCE(min(colcode) FILTER (WHERE c.col = 1) || ' - ',' ') || RPAD(COALESCE(min(coldesc) FILTER (WHERE c.col = 1),''),31) ||
|
|
COALESCE(min(colcode) FILTER (WHERE c.col = 2) || ' - ',' ') || RPAD(COALESCE(min(coldesc) FILTER (WHERE c.col = 2),''),31) ||
|
|
COALESCE(min(colcode) FILTER (WHERE c.col = 3) || ' - ',' ') || RPAD(COALESCE(min(coldesc) FILTER (WHERE c.col = 3),''),31) ||
|
|
COALESCE(min(colcode) FILTER (WHERE c.col = 4) || ' - ',' ') || RPAD(COALESCE(min(coldesc) FILTER (WHERE c.col = 4),''),31) ||
|
|
COALESCE(min(colcode) FILTER (WHERE c.col = 5) || ' - ',' ') || RPAD(COALESCE(min(coldesc) FILTER (WHERE c.col = 5),''),31) collist
|
|
,null::text descr
|
|
,null::text colortiers
|
|
,null::text uomp
|
|
,null::text branding
|
|
,null::text accs
|
|
,null::text suff
|
|
,null::text colors
|
|
,null::text pack_qty
|
|
,null::text pack_price
|
|
,null::text price_per_pack
|
|
,null::text mp_qty
|
|
,null::text pt_price
|
|
,null::text price_per_mp
|
|
,null::text bulk_qty
|
|
,null::text bulk_price
|
|
,null::text price_per_bulk
|
|
,'colors' flag
|
|
,max(c.col)::text detail_sort
|
|
,null::boolean band
|
|
,null::text currency
|
|
,null::text segment
|
|
FROM
|
|
(
|
|
SELECT
|
|
rn.colcode
|
|
,rn.coldesc
|
|
,rn.col
|
|
,row_number() OVER (PARTITION BY rn.col ORDER BY rn.rn ASC) rn
|
|
FROM
|
|
(
|
|
SELECT
|
|
c.colcode
|
|
,c.coldesc
|
|
,row_number() over (order by c.colcode) rn
|
|
,row_number() over (order by c.colcode)/greatest((count(*) over () /3),1) col
|
|
FROM
|
|
"CMS.CUSLG".color c
|
|
WHERE
|
|
c.colcode IN (
|
|
SELECT
|
|
jsonb_array_elements_text(jsonb_arr_aggcd(s.colors)) ae
|
|
FROM
|
|
stage3 s
|
|
)
|
|
AND COALESCE(c.colcode,'') <> ''
|
|
) rn
|
|
) c
|
|
GROUP BY
|
|
c.rn
|
|
ORDER BY
|
|
c.rn
|
|
)
|
|
,colors_header AS (
|
|
SELECT
|
|
'' stlc
|
|
,null::text descr
|
|
,null::text colortiers
|
|
,null::text uomp
|
|
,null::text branding
|
|
,null::text accs
|
|
,null::text suff
|
|
,null::text colors
|
|
,null::text pack_qty
|
|
,null::text pack_price
|
|
,null::text price_per_pack
|
|
,null::text mp_qty
|
|
,null::text pt_price
|
|
,null::text price_per_mp
|
|
,null::text bulk_qty
|
|
,null::text bulk_price
|
|
,null::text price_per_bulk
|
|
,'colors' flag
|
|
,'0' detail_sort
|
|
,null::boolean band
|
|
,null::text currency
|
|
,null::text segment
|
|
UNION ALL
|
|
SELECT
|
|
repeat('-',(max(c.detail_sort::int+1)*16)::int) || 'Color Codes' || repeat('-',(max(c.detail_sort::int+1)*16)::int) stlc
|
|
,null::text descr
|
|
,null::text colortiers
|
|
,null::text uomp
|
|
,null::text branding
|
|
,null::text accs
|
|
,null::text suff
|
|
,null::text colors
|
|
,null::text pack_qty
|
|
,null::text pack_price
|
|
,null::text price_per_pack
|
|
,null::text mp_qty
|
|
,null::text pt_price
|
|
,null::text price_per_mp
|
|
,null::text bulk_qty
|
|
,null::text bulk_price
|
|
,null::text price_per_bulk
|
|
,'colors' flag
|
|
,'0' detail_sort
|
|
,null::boolean band
|
|
,null::text currency
|
|
,null::text segment
|
|
FROM
|
|
colors c
|
|
GROUP BY
|
|
c.flag
|
|
)
|
|
---any headers that don't have any associated rows------
|
|
,orphan_headers AS (
|
|
SELECT
|
|
header
|
|
,count(*)
|
|
FROM
|
|
stage7 s
|
|
GROUP BY
|
|
header
|
|
HAVING
|
|
COUNT(*) <= 1
|
|
)
|
|
,stage8 AS (
|
|
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
|
|
,CASE WHEN substring(s.suff,1,2) = ', ' THEN '(' || substring(s.suff,3,100) || ')' ELSE s.suff END suff
|
|
,s.colors
|
|
,CASE WHEN s.segment = 'N' THEN '' ELSE s.pack_qty END pack_qty
|
|
,CASE WHEN s.segment = 'N' THEN '' ELSE CASE WHEN s.pack_qty IS NULL THEN null::text ELSE s.pack_price END END pack_price
|
|
,CASE WHEN s.segment = 'N' THEN '' ELSE s.price_per_pack END price_per_pack
|
|
,s.mp_qty
|
|
,CASE WHEN s.mp_qty IS NULL THEN null::text ELSE s.mp_price END
|
|
,s.price_per_mp
|
|
,s.bulk_qty
|
|
,CASE WHEN s.bulk_qty IS NULL THEN null::text ELSE s.bulk_price END
|
|
,s.price_per_bulk
|
|
,s.flag
|
|
,s.detail_sort::text
|
|
,CASE dense_rank() OVER (PARTITION BY s.header_sort ORDER BY s.detail_sort) % 2 WHEN 1 THEN true ELSE false END band
|
|
,s.currency
|
|
,s.segment
|
|
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 s.header IS NOT NULL
|
|
AND s.header NOT IN (SELECT header FROM orphan_headers)
|
|
ORDER BY
|
|
s.header_sort ASC
|
|
,s.detail_sort ASC
|
|
,s.comp_sort ASC
|
|
,s.seq ASC
|
|
)
|
|
,ctier AS (
|
|
SELECT * FROM (VALUES
|
|
('B','Base','00'),
|
|
('T','Traditional','01'),
|
|
('L','Principal','02'),
|
|
('M','Premium','03'),
|
|
('D','Decorative Premium','04'),
|
|
('F','Premium Finish','05'),
|
|
('R','Bio','06'),
|
|
('P','Program','07'),
|
|
('C','Custom','08'),
|
|
('E','EcoGrow','09'),
|
|
('O','Organic','10'),
|
|
('S','Waxed One-Side','11'),
|
|
('W','WaxTough','12')
|
|
) x(cltier,cldesc,srt)
|
|
)
|
|
,tiers AS (
|
|
SELECT '' descr
|
|
UNION ALL
|
|
SELECT '----------------Product Tier Codes----------------' descr
|
|
UNION ALL
|
|
(
|
|
SELECT
|
|
s.coltier || ' - ' || c.cldesc descr
|
|
--,c.srt
|
|
--,JSONB_AGG(DISTINCT substring(listcode,1,1)) lc
|
|
FROM
|
|
stage3 s
|
|
LEFT OUTER JOIN ctier c ON
|
|
c.cltier = s.coltier
|
|
GROUP BY
|
|
s.coltier || ' - ' || c.cldesc
|
|
,c.srt
|
|
ORDER BY
|
|
c.srt
|
|
)
|
|
)
|
|
,accsd AS (
|
|
SELECT * FROM (VALUES
|
|
('N','Handle','06'),
|
|
('SH','Saucer & Hanger','04'),
|
|
('H','Hanger','05'),
|
|
('D','Dish','01'),
|
|
('DH','Dish & Hanger','02'),
|
|
('S','Saucer','03')
|
|
) x(accs,descr,srt)
|
|
)
|
|
--SELECT * from tiers
|
|
,accs AS (
|
|
SELECT '' descr
|
|
UNION ALL
|
|
SELECT '-------------Included Accessory Codes-------------'
|
|
UNION ALL
|
|
(
|
|
SELECT
|
|
s.accs || ' - ' || d.descr descr
|
|
FROM
|
|
stage3 s
|
|
INNER JOIN accsd d ON
|
|
d.accs = s.accs
|
|
WHERE
|
|
s.accs <> ''
|
|
GROUP BY
|
|
s.accs || ' - ' || d.descr
|
|
,d.srt
|
|
ORDER BY
|
|
d.srt ASC
|
|
)
|
|
)
|
|
--SELECT * FROM accs
|
|
,optn AS (
|
|
SELECT '' descr
|
|
UNION ALL
|
|
SELECT '----------------Product Option Codes--------------'
|
|
UNION ALL
|
|
(
|
|
SELECT
|
|
s.suff || ' - ' || u.uqdesc descr
|
|
FROM
|
|
stage3 s
|
|
INNER JOIN "CMS.CUSLG".uqf u ON
|
|
u.uqcode = s.suff
|
|
WHERE
|
|
s.suff <> ''
|
|
GROUP BY
|
|
s.suff || ' - ' || u.uqdesc
|
|
)
|
|
)
|
|
--SELECT * FROM optn
|
|
,notes AS (
|
|
VALUES
|
|
('')
|
|
,('--------------Freight Information----------------- ')
|
|
,('Freight charges may apply ')
|
|
,(' ')
|
|
,('Confidentiality - The information transmitted is intended only for the addressee and may contain confidential and/or proprietary material. ')
|
|
,('Any unauthorized review, distribution or other use of this information, or the taking of any action in reliance upon this information, ')
|
|
,('is prohibited. If you received this in error, please contact the sender and delete or destroy this message and any copies. ')
|
|
,('')
|
|
)
|
|
,footer AS (
|
|
SELECT *, 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, null::text, null::text, null::text,'notes','0', null::boolean, null::text, null::text FROM tiers WHERE (select count(*) from tiers) > 2
|
|
UNION ALL
|
|
SELECT *, 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, null::text, null::text, null::text,'notes','0', null::boolean, null::text, null::text FROM accs WHERE (select count(*) from accs) > 2
|
|
UNION ALL
|
|
SELECT *, 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, null::text, null::text, null::text,'notes','0', null::boolean, null::text, null::text FROM optn WHERE (select count(*) from optn) > 2
|
|
UNION ALL
|
|
SELECT *, 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, null::text, null::text, null::text,'notes','0', null::boolean, null::text, null::text FROM notes
|
|
)
|
|
--SELECT * FROM footer
|
|
SELECT * FROM stage8
|
|
UNION ALL
|
|
SELECT * FROM colors_header
|
|
UNION ALL
|
|
SELECT * FROM colors
|
|
UNION ALL
|
|
SELECT * FROM footer;
|
|
|
|
END
|
|
$function$
|