165 lines
4.3 KiB
PL/PgSQL
165 lines
4.3 KiB
PL/PgSQL
DROP FUNCTION pricequote.build_pricing_path;
|
||
|
||
CREATE OR REPLACE FUNCTION pricequote.build_pricing_path(
|
||
_json JSONB
|
||
)
|
||
RETURNS TABLE (
|
||
stlc TEXT
|
||
,seq BIGINT
|
||
,srtcode TEXT
|
||
,ds TEXT
|
||
,chan TEXT
|
||
,tier TEXT
|
||
,vol INT4RANGE
|
||
,func TEXT
|
||
,val NUMERIC
|
||
,price NUMERIC
|
||
,math TEXT[]
|
||
,lastflag BOOLEAN
|
||
)
|
||
LANGUAGE plpgsql AS
|
||
$$
|
||
BEGIN
|
||
RETURN QUERY
|
||
|
||
WITH RECURSIVE
|
||
-- 1️⃣ Parse JSONB into rows of (entity, attr, val)
|
||
parsed AS (
|
||
SELECT
|
||
e.entity,
|
||
COALESCE(e.attr, '') AS attr,
|
||
e.val,
|
||
e.func
|
||
FROM jsonb_to_recordset(_json)
|
||
AS e(entity TEXT, attr TEXT, val NUMERIC, func TEXT)
|
||
),
|
||
-- 2️⃣ Attach sequence & func from master option_sequence table
|
||
sequenced AS (
|
||
SELECT
|
||
p.entity,
|
||
p.attr,
|
||
p.val,
|
||
p.func,
|
||
s.DOMAIN,
|
||
DENSE_RANK() OVER (ORDER BY s.seq) AS seq,
|
||
ROW_NUMBER() OVER (PARTITION BY p.entity ORDER BY COALESCE(p.val,0) ASC) srt
|
||
FROM parsed p
|
||
JOIN pricequote.option_sequence s
|
||
ON p.entity = s.entity
|
||
)
|
||
--select * from sequenced ORDER BY seq, srt
|
||
-- 3️⃣ Recursively accumulate pricing path
|
||
,combos AS (
|
||
-- 🚀 Base case: first in sequence
|
||
SELECT
|
||
s.entity,
|
||
s.attr,
|
||
s.seq,
|
||
to_char(s.srt,'FM000') srtcode,
|
||
'' ds,
|
||
'' chan,
|
||
'' tier,
|
||
null::TEXT vol,
|
||
s.func,
|
||
s.val,
|
||
s.val agg,
|
||
-- jsonb_build_array(jsonb_build_object('entity',s.entity,'attr',s.attr,'function',s.func, 'val',s.val,'seq',s.seq)) math
|
||
ARRAY[
|
||
CASE
|
||
WHEN s.func = 'Price' THEN
|
||
RPAD(s.entity || ':' || s.attr, 17, ' ') || ' + ' || LPAD(to_char(s.val, 'FM9999999990.00000'), 10, ' ')
|
||
WHEN s.func = 'Factor' THEN
|
||
RPAD(s.entity || ':' || s.attr, 17, ' ') || ' x ' || LPAD(to_char(s.val, 'FM9999999990.00000'), 10, ' ')
|
||
ELSE
|
||
RPAD(s.entity || ':' || s.attr, 17, ' ') || ' ' || LPAD(to_char(s.val, 'FM9999999990.00000'), 10, ' ')
|
||
END
|
||
] math
|
||
FROM
|
||
sequenced s
|
||
WHERE
|
||
s.seq = (SELECT MIN(x.seq) FROM sequenced x)
|
||
UNION ALL
|
||
-- 🔁 Recursive step: process next in sequence
|
||
SELECT
|
||
c.entity,
|
||
c.attr,
|
||
o.seq,
|
||
c.srtcode || '.' || to_char(o.srt,'FM000'),
|
||
c.ds || CASE WHEN o.DOMAIN = 'Product' THEN '.' || o.attr ELSE '' END ds,
|
||
CASE WHEN o.DOMAIN = 'Channel' THEN o.attr ELSE c.chan END chan,
|
||
CASE WHEN o.DOMAIN = 'Tier' THEN o.attr ELSE c.tier END tier,
|
||
CASE WHEN o.DOMAIN = 'Volume' THEN o.attr ELSE c.vol END vol,
|
||
o.func,
|
||
o.val,
|
||
CASE o.func WHEN 'Price' THEN c.agg + o.val WHEN 'Factor' THEN c.agg * o.val END agg,
|
||
-- c.math || jsonb_build_array(jsonb_build_object('entity',o.entity,'attr',o.attr,'function',o.func, 'val',o.val,'seq',o.seq)) math,
|
||
CASE WHEN (o.func = 'Price' AND o.val <> 0) OR (o.func = 'Factor' AND o.val <> 1) THEN
|
||
c.math ||
|
||
ARRAY[
|
||
CASE
|
||
WHEN o.func = 'Price' THEN
|
||
RPAD(o.entity || ':' || o.attr, 17, ' ') || ' + ' || LPAD(to_char(o.val, 'FM9999999990.00000'), 10, ' ')
|
||
WHEN o.func = 'Factor' THEN
|
||
RPAD(o.entity || ':' || o.attr, 17, ' ') || ' x ' || LPAD(to_char(o.val, 'FM9999999990.00000'), 10, ' ')
|
||
ELSE
|
||
RPAD(o.entity || ':' || o.attr, 17, ' ') || ' ' || LPAD(to_char(o.val, 'FM9999999990.00000'), 10, ' ')
|
||
END
|
||
]
|
||
ELSE
|
||
c.math
|
||
END math
|
||
FROM
|
||
combos c
|
||
JOIN sequenced o ON
|
||
o.seq = c.seq + 1
|
||
)
|
||
SELECT
|
||
-- c.entity
|
||
c.attr
|
||
,c.seq
|
||
,c.srtcode
|
||
,'v1:'||SUBSTRING(c.ds,2,100) ds
|
||
,c.chan
|
||
,c.tier
|
||
-- ,c.vol
|
||
,CASE
|
||
WHEN c.vol ~ '^[0-9]+-[0-9]+$' THEN
|
||
int4range(
|
||
split_part(c.vol, '-', 1)::int,
|
||
split_part(c.vol, '-', 2)::int,
|
||
'[)'
|
||
)
|
||
WHEN c.vol ~ '^[0-9]+$' THEN
|
||
int4range(
|
||
c.vol::int,
|
||
NULL,
|
||
'[)'
|
||
)
|
||
ELSE NULL
|
||
END AS vol
|
||
,c.func
|
||
,c.val
|
||
,c.agg
|
||
,c.math
|
||
,c.seq = (SELECT max(x.seq) FROM sequenced x) lastflag
|
||
FROM
|
||
combos c /*WHERE seq = (SELECT max(seq) FROM sequenced)*/
|
||
ORDER BY
|
||
c.srtcode ASC;
|
||
|
||
END;
|
||
$$;
|
||
|
||
/*
|
||
Anchor:EU170S50 + 0.08
|
||
Color Tier:P x 1.30
|
||
Branding: + 0.00
|
||
Packaging:SLV + 0.00
|
||
Suffix:PCR x 1.00
|
||
Accessories: + 0.00
|
||
Channel:WHS + 0.00
|
||
Volume:8 x 1.00
|
||
-----------------------
|
||
0.104
|
||
*/
|