aonestar.public

Parameters

Name	Type	Mode
rateplan_id	integer	IN (DEFAULT NULL)
compact	boolean	IN (DEFAULT true)
enabled	boolean	IN (DEFAULT NULL)

Definition

declare json_result json; 
BEGIN
	if (compact) then
	--if compact === true that get list
		Select json_agg(R) 
        from ( 
            select p.id, p.code, p.name, p.enabled, (select count(*) from rate_plan_channels c where c.rateplan_id = p.id) channel_count, p.plan_type as plan_type_id,
            case
            	when p.plan_type = 1 then 'Maual rate'
            	when p.plan_type = 2 then 'Dynamic adjust (link rate)'
            	when p.plan_type = 3 then 'Earlybird'
            	when p.plan_type = 4 then 'Last minute'
            end as plan_type_name,
           	case
           		when  p.plan_type = 1 then false
           		else true
           	end as link_rate,
            ( 
                select jsonb_agg(Z) 
                from ( 
                    select t.id as roomtype_id, t.name as roomtype_name, d.description, d.rate_id, (select count(*) from rate_channels c where c.rate_id = d.rate_id) channel_count, d.rate_amount 
                    from room_type t  
                    left join rate_details d on t.id = d.roomtype_id and d.rateplan_id = p.id 
                    where d.rate_id is not null 
                    order by t.order_seq, t.id 
                ) Z 
            ) as details 
            from rate_plan p
            where ($1 is null or p.id = $1) or ($3 is null or p.enabled = $3) --$3 is null is mean not where but not null will enabled = $3
            order by p.order_seq, p.id 
        ) as R
        into json_result;
	else
	--if compact === false that get just one rate_plan
		Select json_agg(R)
		from (
			select p.id, p.code, p.name, p.plan_type, 
				p.start_date, p.end_date, p.book_start_date, p.book_end_date, p.stay_start_date, p.stay_end_date,
				p.description, p.link_plan_id, 
				p.link_adjust_type, 
				case 
					when coalesce(p.link_adjust_type,0) < 2 then null
					when (p.link_adjust_amt < 0 or p.link_adjust_percent < 0) then '-' 
					else '+' 
				end link_adjust_op,
				abs(p.link_adjust_amt) link_adjust_amt, 
				abs(p.link_adjust_percent) link_adjust_percent, 
				p.origin_id, o."name" origin_name, p.market_id, m."name" market_name, 
				p.min_stay, p.max_stay, p.min_adv_book, p.max_adv_book, p.rate_extrabed, p.rate_child, p.rate_infant,
				json_build_object('sun',day_sun,'mon',day_mon,'tue',day_tue,'wed',day_wed,'thu',day_thu,'fri',day_fri,'sat',day_sat) days, 
				(select count(*) from rate_plan_channels c where c.rateplan_id = p.id) channel_count,
				p.no_amend, p.no_cancel, p.no_refund, p.enabled, coalesce(p.order_seq, p.id) order_seq,
				-- Inclusions
				(select json_agg(Z) 
				 from (select i.id, i."name", r.charge_rate, r.charge_dept, (r.rateplan_id is not null) active 
					   from inclusion i left join rate_inclusion r on i.id = r.inclusion_id and r.rateplan_id = p.id
					   where i.frequency in (1,2)
				 	   order by i.id) as Z
				) as inclusions,
				-- Rate Details
				(select json_agg(Z) 
				 from (select t.id roomtype_id, t.name roomtype_name, d.description, 
				 			  rate_id, rate_amount, rate_extrabed, rate_child, rate_infant, 
				 			  d.include_adult, d.include_child, d.include_infant,
				 			  d.max_adult, d.max_child, d.max_infant, d.max_pax,
				 			  --(select count(*) from rate_channels c where c.rate_id = d.rate_id) channel_count,
							  (rate_id is not null) active 
				       --from room_type t left join rate_details d on t.id = d.roomtype_id and d.rateplan_id = p.id
				       from rate_details d left join room_type t on t.id = d.roomtype_id
				       where d.rateplan_id = p.id
				       order by t.order_seq, t.id) as Z	
				) as details	
			from rate_plan p 
				left join origin o on o.id = p.origin_id
				left join market m on m.id = p.market_id 
			where $1 is null or p.id = $1	
			order by p.order_seq, p.id
		) as R
		into json_result;
	end if;
	-- return data
	return json_result;
END

sp_get_rate_plans_js_old

Parameters

Definition