aonestar.public

Parameters

Name	Type	Mode
rateplan_id	integer	IN (DEFAULT NULL)
format	integer	IN (DEFAULT 1)
enabled	boolean	IN (DEFAULT NULL)

Definition

SELECT
	  CASE (format) 
	  WHEN 1 THEN (
        with dtl as (
			select rateplan_id, --jsonb_agg(D) as details, 
				   coalesce(array_agg(roomtype_id),'{}') as effected_roomtypes 
		    from rate_details as D 
		    group by rateplan_id
		)
        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,
				p.plan_type <> 1 as link_rate, dtl.effected_roomtypes
				--dtl.effected_roomtypes, dtl.details 
            from rate_plan p left join dtl on dtl.rateplan_id = p.id
            where ($1 is null or p.id = $1) and ($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
	  )

	  WHEN 2 THEN (
        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
	  )	
	  
	  when 3 then (
	    WITH RECURSIVE plan AS (
			SELECT p.id, p.code, p.name, p.plan_type, p.order_seq, p.enabled, p.link_plan_id,  
				p.id AS base_plan_id, p.link_adjust_type, p.link_adjust_percent, p.link_adjust_amt, null as link_description   	    
			FROM rate_plan p
			WHERE p.plan_type = 1
			UNION
			SELECT p.id, p.code, p.name, p.plan_type, p.order_seq, p.enabled, p.link_plan_id, 
				link.base_plan_id, p.link_adjust_type, p.link_adjust_percent, p.link_adjust_amt,
				format('Derived from %s (%s)', 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)) || '%'
						ELSE NULL
					END)    
			FROM rate_plan p JOIN plan link ON link.id = p.link_plan_id
			WHERE p.plan_type <> 1
		), all_plans as (
			SELECT p.id, p.code, p.name, p.plan_type, p.order_seq, p.enabled, p.link_plan_id, link_description, base_plan_id,
				(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, 
				p.plan_type <> 1 as link_rate
			FROM PLAN p
			where ($1 is null or p.id = $1) and ($3 is null or p.enabled = $3)
			order by order_seq
		), grouped as (
			select a.*,
				(select coalesce(json_agg(c), '[]') 
				 from (select * from all_plans 
					   where plan_type <> 1 and base_plan_id = a.id
					  ) as c
				) as children
			from all_plans as a
			where a.plan_type = 1 
			--order by a.order_seq
		)
		SELECT json_agg(grouped) 
		FROM grouped 
	  )
	end  
			--    into json_result;
    --end if;
    -- return data
--    return json_result;
--END

sp_get_rate_plans_js2

Parameters

Definition