hc-companies
/
plbuild
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
plbuild/pcore_seq.sql

312 lines
9.4 KiB
SQL
Raw Permalink Blame History

--CREATE OR REPLACE VIEW rlarp.pcore_seq AS
--EXPLAIN (ANALYZE, BUFFERS)
---------------------------------------------------------------------------------------------------
--there are 3 UOM involved:
--1. the original package (mpckg) from the core price row
--2. the array of pckg codes (pckg) that will allow joining to the item master (hard-coded)
--3. the UOM of the volume break (vol_uom)
---------------------------------------------------------------------------------------------------
WITH
-------------make BDL and SLV the same thing-------------------------------------------------------
-------------assumes that left column has all unique packages in rlarp.pcore-----------------------
vu AS (
SELECT * FROM ( VALUES
('BDL','SLV'),
('BDL','BDL'),
('PLT','PLT'),
('CSE','CSE')
) x(bdl,uom)
)
----------------add columns for a better understanding of what is otherwise implied----------------
,pc AS materialized (
SELECT
plist
,STLC
,COALESCE(COLTIER,'') COLTIER
,coalesce(BRANDING,'' ) branding
,COALESCE(ACCS ,'') ACCS
,COALESCE(SUFF,'') SUFFIX
,COALESCE(pckg,'') mpckg --retain the original price row package indicator
----price 1 -----------------
,jsonb_build_array(PCKG)||CASE WHEN pckg = 'BDL' THEN '["SLV"]' ELSE '[]' END::jsonb PCKG1
,1 VOLL1
,PCKG VOL1_UOM --the original package, same as mpckg
,PACK VOL1_PRICE
----price 2 -----------------
,jsonb_build_array(PCKG)||CASE WHEN pckg = 'BDL' THEN '["SLV"]' ELSE '[]' END::jsonb PCKG2
,1 VOLl2
,'PLT' VOL2_UOM --the original volume basis unit of measure (PLT = master pallet)
,MP VOL2_PRICE
----price 3 -----------------
,jsonb_build_array('PLT') PCKG3
,1 VOLL3
,'PLT' VOL3_UOM --this one is overriden to PLT to hook with bulk parts
,BULK VOL3_PRICE
FROM
rlarp.PCORE P
-- WHERE
-- stlc = 'FNP07080'
-- AND plist = 'FCAC'
)
--SELECT * FROM pc
--------------list unique colors per line-----------------------------------------------------------
,colors AS (
SELECT
pc.plist
,pc.STLC
,pc.COLTIER
,pc.branding
,pc.ACCS
,pc.SUFFIX
,pc.mpckg
,string_agg( DISTINCT ltrim(rtrim(i.COLC)),', ') tclist
,jsonb_agg( DISTINCT ltrim(rtrim(i.COLC))) clist
FROM
pc
LEFT OUTER JOIN "CMS.CUSLG".itemm i ON
i.STLC = pc.stlc
AND i.COLTIER = pc.coltier
AND i.colc NOT IN ('MIX','')
--relax relationship to pick up any colors that we run for the mold and color tier
--AND COALESCE(i.BRANDING,'') = pc.branding
--AND COALESCE(i.ACCS,'') = pc.ACCS
--AND COALESCE(i.SUFFIX,'') = pc.suffix
AND APLNT <> 'I'
--exclude hangers packed in 400 by targeting `AUTO` in the desription
AND i.descr !~ 'AUTO'
GROUP BY
pc.plist
,pc.STLC
,pc.COLTIER
,pc.branding
,pc.ACCS
,pc.SUFFIX
,pc.mpckg
)
--SELECT * FROM colors
-----------since joining to the item master will be differnt for the last bulk columm---------------
-----------must first pivot the data out then join based on the type of row-------------------------
,unpivot AS (
SELECT
pc.plist
,pc.stlc
,pc.coltier
,pc.branding
,pc.accs
,pc.suffix
---meta package (the one used on the customer facing price row)----------
,pc.mpckg
,1 col
,pc.pckg1 pckg
,pc.vol1_uom vol_uom
,pc.voll1 vol_qty
--,COALESCE(pc.vol1_price,0) price
,pc.vol1_price price
FROM
pc
WHERE
true
--pc.vol1_price IS NOT null
UNION
SELECT
pc.plist
,pc.stlc
,pc.coltier
,pc.branding
,pc.accs
,pc.suffix
---meta package (the one used on the customer facing price row)----------
,pc.mpckg
,2 col
,pc.pckg2 pckg
,pc.vol2_uom vol_uom
,pc.voll2 vol_qty
--,coalesce(pc.vol2_price,0) price
,pc.vol2_price price
FROM
pc
WHERE
true
--pc.vol2_price IS NOT null
UNION
SELECT
pc.plist
,pc.stlc
,pc.coltier
,pc.branding
,pc.accs
,pc.suffix
---meta package (the one used on the customer facing price row)----------
,pc.mpckg
,3 col
,pc.pckg3 pckg
,pc.vol3_uom vol_uom
,pc.voll3 vol_qty
--,coalesce(pc.vol3_price,0) price
,pc.vol3_price price
FROM
pc
WHERE
true
--pc.vol3_price IS NOT null
)
--SELECT * FROM unpivot
--SELECT * FROM unpivot WHERE pckg ? 'SLV'
--SELECT DISTINCT pckg FROM unpivot;
-----------------link to actual part numbers, tag the sequence so a single item can be chosen---------------------
,items_init AS (
SELECT
up.plist
,up.stlc
,up.coltier
,up.branding
,up.accs
,up.suffix
,up.mpckg
,up.pckg --item master joiner
--retain this column to re-pivot back to original format
,up.col
,up.vol_uom
,up.voL_qty
,up.price
,row_number() OVER (PARTITION BY up.plist, up.stlc, up.coltier, up.branding, up.accs, up.suffix, up.pckg, up.col, up.vol_uom ORDER BY item ASC) seq
,item
--subquery option is not performant at all
FROM
unpivot up
----make all the BDL units of measure BDL and also SLV with intentional duplication
----becusae some defaults units on itemm have SLV and not BDL, but still need a join
--LEFT OUTER JOIN vu ON
-- vu.bdl = up.pckg
LEFT OUTER JOIN "CMS.CUSLG".itemm i ON
i.STLC = up.stlc
AND i.COLTIER = up.coltier
--basically the same as does the core list package match the item master package
--the item master could have multiple since its defined per plant but will take the first item for now
AND up.pckg ? i.uomp
AND COALESCE(i.BRANDING,'') = up.branding
AND COALESCE(i.ACCS_PS,'') = up.ACCS
AND COALESCE(i.SUFFIX,'') = up.suffix
AND APLNT <> 'I'
--exclude hangers packaged in 400 for automation pruposes
AND i.descr !~ 'AUTO'
)
--SELECT * FROM items_init
--SELECT * FROM items_init WHERE stlc = 'TRV55420' ORDER BY plist, stlc, col, seq
----compile all the conversions into a single json object----------------------
,items AS (
SELECT
jsonb_agg( DISTINCT
jsonb_build_object(
'partn', item
,'fu', vol_uom
,'tu','PC'
)
) conv
FROM
items_init
WHERE
seq = 1
AND COALESCE(item,'') <> ''
)
--do the conversion-------------------------------------------------------------
, conversion AS (
SELECT
rslt.*
FROM
items
JOIN LATERAL rlarp.uom_array(conv) rslt ON true
)
----join conversion back to data initial part link------------------------------
,joinback_init AS (
SELECT
i.plist
,i.stlc
,i.coltier
,i.branding
,i.accs
,i.suffix
,i.mpckg
,i.pckg
,i.col
,i.vol_uom
,i.vol_qty
,i.price
,c.nm
,c.dm
,l.clist
FROM
items_init i
LEFT OUTER JOIN conversion c ON
c.p = i.item
AND c.f = i.vol_uom
LEFT OUTER JOIN colors l ON
l.stlc = i.stlc
AND l.coltier = i.coltier
AND l.branding = i.branding
AND l.accs = i.accs
AND l.suffix = i.suffix
AND l.mpckg = i.mpckg
WHERE
i.seq = 1
)
--SELECT * FROM joinback_init
----aggregate everything offered for the price point----------------------------
,agg_levels AS (
SELECT
plist
,stlc
,string_agg(DISTINCT coltier ,',') FILTER (WHERE clist IS NOT NULL) coltier
,COALESCE(string_agg(DISTINCT branding,',') FILTER (WHERE COALESCE(branding,'') <> ''),'') branding
-------replace blank with 'none'--------------
,regexp_replace(string_agg(DISTINCT accs ,','),'^,','','g') accs
,regexp_replace(string_agg(DISTINCT suffix ,','),'^,','','g') suffix
,mpckg
--,pckg
,col
,vol_uom
,voL_qty
--,CASE WHEN price = 0 then null::numeric ELSE max(nm) END nm
,max(nm) nm
--,CASE WHEN price = 0 then null::numeric ELSE max(dm) END dm
,max(dm) dm
,price
-----convert json array to a plain text representation as well as remove nulls------
,regexp_replace(jsonb_arr_aggcd(clist)::text,'["\[\]]|null, ','','g') clist
FROM
joinback_init
GROUP BY
plist
,stlc
,mpckg
--,pckg
,col
,vol_uom
,vol_qty
,price
)
--SELECT * FROM agg_levels order by stlc , coltier , branding ,accs , suffix, mpckg
,seq_levels AS (
SELECT
plist||'.'||stlc||'.'||mpckg||'.'||col||'.'||to_char(row_number() OVER (PARTITION BY plist, stlc, mpckg, col ORDER BY price ASC),'FM00') lookup
,plist
,stlc
,coltier
,branding
,accs
,suffix
,mpckg pckg
,clist
,col
,vol_uom
,vol_qty
,nm
,dm
,price
,CASE WHEN COALESCE(price,0) = 0 THEN '' ELSE plist||'.'||stlc||'.'||mpckg||'.'||to_char(row_number() OVER (PARTITION BY plist, stlc, mpckg, col ORDER BY price ASC),'FM00') END descr_lookup
FROM
agg_levels
)
SELECT * FROM seq_levels WHERE nm is not null
--ORDER BY stlc , coltier , branding ,accs , suffix, pckg
Reference in New Issue View Git Blame Copy Permalink