aonestar.public

Parameters

Name	Type	Mode
channel_id	integer	IN
number_of_days	integer	IN
start_date	date	IN (DEFAULT NULL)

Definition

declare json_result json; 
declare allot_enabled bool;
declare coallot_channel int;
declare channel_name text;
begin
	if (START_DATE is null) then
		START_DATE = coalesce(sp_get_param('SYSTEM', 'SYSTEM_DATE')::date, current_date);
	end if;

	select c.name, c.coallot_channel, c.allot_enabled from channel c
	where c.id = $1
	into channel_name, coallot_channel, allot_enabled;

	WITH RECURSIVE plan as(
	    Select id rateplan_id, p.name,
	    	p.name||coalesce(' ('||nullif(d.description,'')||')','') description, p.order_seq,
	    	d.rate_id, d.roomtype_id, link_plan_id, true as editable, p.enabled, d.rate_amount, '' as link_description 
	    from rate_plan p left join rate_details d on d.rateplan_id = p.id
	    where link_plan_id is null 
	    UNION 	
	    select d.rateplan_id, p.name, 
	    	p.name||coalesce(' ('||nullif(d.description,'')||')',''), p.order_seq,
	    	d.rate_id, d.roomtype_id, p.link_plan_id, false as editable, p.enabled,
	    	case P.link_adjust_type
	    		when 1 then link.rate_amount
	    		when 2 then link.rate_amount + (link.rate_amount * P.link_adjust_percent /100.00)
	    		when 3 then link.rate_amount + P.link_adjust_amt
	    		when 5 then link.rate_amount + (link.rate_amount * P.link_adjust_percent /100.00) +P.link_adjust_amt
	    		when 4 then link.rate_amount + P.link_adjust_amt + ((link.rate_amount + P.link_adjust_amt)* P.link_adjust_percent/100.00) 	    		
	    	end::t_money,
	    	'derived from '||link.name||' ('
		    ||case P.link_adjust_type
	    		when 1 then 'same rate'
	    		when 2 then iif(P.link_adjust_percent < 0, '-', '+')||abs(P.link_adjust_percent)||'%' 
	    		when 3 then iif(P.link_adjust_percent < 0, '-', '+')||abs(P.link_adjust_amt)
	    		when 5 then iif(P.link_adjust_percent < 0, '-', '+')||abs(P.link_adjust_percent)||'% and '||abs(P.link_adjust_amt) 
	    		when 4 then iif(P.link_adjust_percent < 0, '-', '+')||abs(P.link_adjust_amt)||' and '||abs(P.link_adjust_percent)||'%' 	    		
		      end||')'
	    from rate_details d
	    	left join rate_plan P on d.rateplan_id = p.id 
	    	inner join Plan link on link.rateplan_id = P.link_plan_id and link.roomtype_id = d.roomtype_id     
	    where p.link_plan_id is not null
	), 
	daily as (select r.roomtype_id, r.rate_id, r.rate_date, r.rate_amount from channel_rates r
			  where r.channel_id = $1 and r.rate_date between START_DATE and START_DATE + $2),
	dates as (select START_DATE + s.a as adate, s.a as seq 
			  from generate_series(0, $2-1) S(a)),
	X as (select plan.roomtype_id, Plan.rate_id, description, 
			dates.adate, plan.rate_amount plan_rate 
	      from dates, plan 			
	), OCC as (
		select START_DATE + (row_number() over(order by value desc)::int)-1 "date", value occ_percent
		from (select fn_random_range(5, 115) as value from generate_series(1, $2)) R
	) 
	
	Select json_build_object(
		'channel_id', $1, 
		'channel_name', channel_name,
		'allotment_editable', allot_enabled and coallot_channel is null,
		'inventory',
		(select json_agg(J0)
		 from (
			select rt.id roomtype_id, rt.name roomtype_name, order_seq, --setseed(rt.id / 100), 
--				(select json_agg(AVL)
--				 from (select START_DATE + (row_number() over(order by value)::int)-1 "date", value avail
--					   from (select fn_random_range(-4, 50, rt.id) as value from generate_series(1, $2)) R
--				 	  ) AVL
--				 ) as available,
				 
				(select json_agg(dataset)
				 from (select d.adate "date", allot_qty avail
					   from dates d left join allotment a 
					   		on a.channel_id = $1 and a.roomtype_id = rt.id and a.allot_date = d.adate
				 	  ) dataset
				 ) as available,
				 				 
				(select coalesce(json_agg(J1),'[]')
				 from (select rate_id, rateplan_id, description, link_plan_id, link_description, editable, order_seq,
				 			(select coalesce(json_agg(J2),'[]') 
				 			 from (select x.adate "date", coalesce(d.rate_amount, X.plan_rate, rt.room_rate) amount, plan.editable,
				 			 		json_build_object('stop_sell',coalesce(stop_sell,false), 
				 			 						  'cta',coalesce(cta,false), 
				 			 						  'ctd',coalesce(ctd,false), 
				 			 						  'min_stay',min_stay, 
				 			 						  'max_stay',max_stay) restrictions
				 			 	   from X left join channel_rates d 
				 			 			  on d.roomtype_id = X.roomtype_id 
											and d.rate_date = X.adate 
											and d.rate_id = X.rate_id
											AND d.channel_id = $1
--										left join rate_restriction r 
--										  on r.roomtype_id = X.roomtype_id 
--											and r.effect_date = X.adate 
--											and r.rateplan_id = plan.rateplan_id	
				 			 	   where X.roomtype_id = plan.roomtype_id and X.rate_id = plan.rate_id
				 			 	  ) J2
				 			 ) as rates
				 	   from plan 
				 	   where plan.roomtype_id = rt.id and plan.enabled = true 
				 	   order by order_seq
				 	   ) J1
				 ) as rate_plans
				 
			from room_type rt
			order by order_seq
		 ) J0
		),
		'occupancy', (Select json_agg(OCC) from OCC)
	)
	into json_result;

	return json_result;
end

sp_get_inventory_by_channel_js

Parameters

Definition