aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_save_inventory
Parameters
Name
Type
Mode
inventory_data
jsonb
IN
currency
character varying
IN (DEFAULT NULL)
Definition
DECLARE inv record; st record; plan record; rate record; log_line TEXT = ''; log_text TEXT = ''; stop_sell_on TEXT = ''; stop_sell_off TEXT = ''; min_rate t_money; _sqlstate TEXT; _detail TEXT; _hint TEXT; _context TEXT; _msg_text TEXT; BEGIN FOR inv IN SELECT R.*, rt.name AS roomtype_name, rt.min_rate as room_min_rate, rt.code FROM jsonb_to_recordset(inventory_data->'inventory') AS R(roomtype_id int, available jsonb, sell_status jsonb, rate_plans jsonb) LEFT JOIN room_type rt ON rt.id = r.roomtype_id ORDER BY rt.order_seq LOOP log_text := log_text || inv.roomtype_name ||E' :\n'; log_line := ''; -- Sace sell status FOR st IN SELECT * FROM jsonb_to_recordset(inv.sell_status) AS ("date" date, stop_sell bool, min_avail int2, max_avail int2) WHERE stop_sell IS NOT NULL ORDER BY "date" LOOP INSERT INTO room_restriction(roomtype_id, effect_date, stop_sell, min_avail, max_avail) VALUES (inv.roomtype_id, st."date", st.stop_sell, st.min_avail, st.max_avail) ON CONFLICT(roomtype_id, effect_date) DO UPDATE SET stop_sell = excluded.stop_sell, min_avail = excluded.min_avail, max_avail = excluded.max_avail; IF st.stop_sell THEN stop_sell_on := stop_sell_on || ', '|| st."date"; ELSE stop_sell_off := stop_sell_off || ', '|| st."date"; END IF; END LOOP; IF stop_sell_on <> '' THEN log_text := log_text ||' * Close Sell [ON] : '||substring(stop_sell_on FROM 3)|| E'\n'; END IF; IF stop_sell_off <> '' THEN log_text := log_text ||' * Close Sell [OFF] : '||substring(stop_sell_off FROM 3)|| E'\n'; END IF; -- Save rates and restrictions FOR plan IN SELECT P.*, rp.name AS rateplan_name, rp.min_rate as rate_min_rate, rp.code FROM jsonb_to_recordset(inv.rate_plans) as P(rate_id int, rateplan_id int, editable bool, rates jsonb) LEFT JOIN rate_plan rp ON rp.id = p.rateplan_id ORDER BY rp.order_seq, rp.name LOOP log_text := log_text || ' '||plan.rateplan_name || E'\n'; FOR rate IN SELECT r."date", $2, r.amount, (restrictions->>'stop_sell')::bool as stop_sell, (restrictions->>'cta')::bool as cta, (restrictions->>'ctd')::bool as ctd, (restrictions->>'min_stay')::int as min_stay, (restrictions->>'max_stay')::int as max_stay FROM jsonb_to_recordset(plan.rates) as R("date" date, amount t_money, restrictions jsonb) WHERE NOT (r.amount IS NULL AND restrictions->>'stop_sell' IS NULL AND restrictions->>'cta' IS NULL AND restrictions->>'ctd' IS NULL AND restrictions->>'min_stay' IS NULL AND restrictions->>'max_stay' IS NULL) ORDER BY 1 LOOP min_rate := coalesce(plan.rate_min_rate, inv.room_min_rate); IF rate.amount < min_rate THEN PERFORM sp_raise_error('10801', 'Room rate lower than the minimum rate are not allowed (%s - %s, Amount = %s, Minimum = %s)', inv.code, plan.code, rate.amount::text , min_rate::text ); END IF; INSERT INTO rate_daily AS rd (rate_id, rate_date, currency_code, rate_amount, stop_sell, cta, ctd, min_stay, max_stay) VALUES (plan.rate_id, rate."date", $2, rate.amount, rate.stop_sell, rate.cta, rate.ctd, rate.min_stay, rate.max_stay) ON CONFLICT(rate_id, rate_date) DO UPDATE SET rate_amount = COALESCE(excluded.rate_amount, rd.rate_amount), stop_sell = COALESCE(excluded.stop_sell, rd.stop_sell), cta = COALESCE(excluded.cta, rd.cta), ctd = COALESCE(excluded.ctd, rd.ctd), min_stay = NULLIF(COALESCE(excluded.min_stay, rd.min_stay), 0), max_stay = NULLIF(COALESCE(excluded.max_stay, rd.max_stay), 0); log_text := log_text || format(' * %s%s%s%s%s%s%s', --' %s: %s%s%s%s%s%s%s', rate."date", ', Rate = '||rate.amount, ', Close Sell = '||rate.stop_sell, ', CTA = '||rate.cta, ', CTD = '||rate.ctd, ', Min Stay = '||rate.min_stay, ', Max Stay = '||rate.max_stay) || E'\n'; END LOOP; END LOOP; log_text := log_text || E'\n'; -- -- WITH modified AS ( -- INSERT INTO rate_daily AS rd (rate_id, rate_date, currency_code, rate_amount, stop_sell, cta, ctd, min_stay, max_stay) -- with A as ( -- select R.*--, rp."name" AS rateplan_name, rp.order_seq -- from jsonb_to_recordset(inv.rate_plans) -- as R(rate_id int, editable bool, rates jsonb) -- ) -- SELECT A.rate_id, rec."date", $2, rec.amount, -- (restrictions->>'stop_sell')::bool as stop_sell, -- (restrictions->>'cta')::bool as cta, -- (restrictions->>'ctd')::bool as ctd, -- (restrictions->>'min_stay')::int as min_stay, -- (restrictions->>'max_stay')::int as max_stay -- FROM A, jsonb_to_recordset(A.rates) as rec("date" date, amount t_money, restrictions jsonb) -- WHERE NOT (rec.amount IS NULL -- AND restrictions->>'stop_sell' IS NULL -- AND restrictions->>'cta' IS NULL -- AND restrictions->>'ctd' IS NULL -- AND restrictions->>'min_stay' IS NULL -- AND restrictions->>'max_stay' IS NULL) -- ORDER BY A.rate_id, rec."date" -- ON CONFLICT(rate_id, rate_date) -- DO UPDATE SET -- rate_amount = COALESCE(excluded.rate_amount, rd.rate_amount), -- stop_sell = COALESCE(excluded.stop_sell, rd.stop_sell), -- cta = COALESCE(excluded.cta, rd.cta), -- ctd = COALESCE(excluded.ctd, rd.ctd), -- min_stay = NULLIF(COALESCE(excluded.min_stay, rd.min_stay), 0), -- max_stay = NULLIF(COALESCE(excluded.max_stay, rd.max_stay), 0) -- RETURNING * -- ) -- SELECT string_agg(format(' %s: %s%s%s%s%s%s%s', --' %s: %s%s%s%s%s%s%s', -- rp.name, -- 'Date = '||m.rate_date, -- ', Rate = '||m.rate_amount, -- ', Close Sell = '||m.stop_sell, -- ', CTA = '||m.cta, -- ', CTD = '||m.ctd, -- ', Min Stay = '||m.min_stay, -- ', Max Stay = '||m.max_stay) -- , E'\n') -- FROM modified m -- LEFT JOIN rate_details rd ON rd.rate_id = m.rate_id -- LEFT JOIN rate_plan rp ON rp.id = rd.rateplan_id -- INTO log_line; -- --RETURNING rate_id, rate_date, currency_code, rate_amount, stop_sell, cta, ctd, min_stay, max_stay ; -- log_text := log_text ||log_line||E'\n'; END LOOP; PERFORM sp_user_log('inventory', 'Inventory Update', trim(log_text)); RETURN 0; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS _msg_text = MESSAGE_TEXT, _sqlstate = RETURNED_SQLSTATE, _detail = PG_EXCEPTION_DETAIL, _hint = PG_EXCEPTION_HINT, _context = PG_EXCEPTION_CONTEXT; PERFORM fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_save_inventory', inventory_data); RETURN 4; end;