aonestar.public

Parameters

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

Definition

declare json_result json; 
begin
	start_date := coalesce(start_date, fn_system_date(), current_date);

	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 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, rt.id) as value from generate_series(1, $1)) R
				 	  ) AVL
				 ) as available,
				 
				(select json_agg(dataset)
				 from (select d.adate "date", coalesce(stop_sell,true) sellable
					   from dates d 
					   		left join room_restriction r on r.roomtype_id = rt.id and r.effect_date = d.adate
				 	  ) dataset
				 ) as sell_status,
				 				 
				(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(daily.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 daily 
				 			 			  on daily.roomtype_id = X.roomtype_id 
											and daily.rate_date = X.adate 
											and daily.rate_id = X.rate_id
										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;

	--insert into json_cache values(md5(json_result::text), json_result);
	return json_result;
end

sp_rate_calendar_js

Parameters

Definition