aonestar.public

Parameters

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

Definition

declare json_result json; 
begin
	if (START_DATE is null) then
		START_DATE = coalesce(sp_get_param('SYSTEM', 'SYSTEM_DATE')::date, current_date);
	end if;

	WITH RECURSIVE plan as(
	    Select id rateplan_id, 
	    	p.name||coalesce(' ('||nullif(d.description,'')||')','') description, p.order_seq,
	    	d.rate_id, d.roomtype_id, link_plan_id, true as editable, d.rate_amount 
	    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||coalesce(' ('||nullif(d.description,'')||')',''), p.order_seq,
	    	d.rate_id, d.roomtype_id, p.link_plan_id, false as editable, 
	    	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 + (link.rate_amount + P.link_adjust_amt) + ((link.rate_amount + P.link_adjust_amt)* P.link_adjust_percent/100.00) 	    		
	    	end::t_money 
	    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 roomtype_id, rate_id, rate_date, rate_amount from rate_daily),
	dates as (select START_DATE + s.a as adate, s.a as seq 
			  from generate_series(0, $1-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, $1)) R
	) 
	
	Select json_build_object(
		'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) as value from generate_series(1, $1)) R
				 	  ) AVL
				 ) available,
				(select coalesce(json_agg(J1),'[]')
				 from (select rate_id, rateplan_id, description, link_plan_id, editable, order_seq,
				 			(select json_agg(J2) 
				 			 from (select x.adate "date", coalesce(daily.rate_amount, X.plan_rate, rt.room_rate) amount, plan.editable
				 			 	   from X left join daily 
				 			 			on daily.roomtype_id = X.roomtype_id 
											and daily.rate_date = X.adate 
											and daily.rate_id = X.rate_id
				 			 	   where X.roomtype_id = plan.roomtype_id and X.rate_id = plan.rate_id
				 			 	  ) J2
				 			 ) rates
				 	   from plan where plan.roomtype_id = rt.id 
				 	   order by order_seq
				 	   ) J1
				 ) 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_rate_calendar_js2

Parameters

Definition