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)
      /* Get rate plans for lookup/dropdown */
      WHEN 1 THEN (
        with dtl as (
            select rateplan_id, array_agg(roomtype_id) as effected_roomtypes 
            from rate_details as D 
            group by rateplan_id
        )
        Select COALESCE(json_agg(R), '[]') 
        from (          
            select p.id, p.code, p.name, p.enabled, 
                p.mapped_channels as channel_count, 
                p.plan_type as plan_type_id, fn_plantype_name(p.plan_type ) as plan_type_name,
                p.plan_type in (2,3,4) as link_rate, 
                coalesce(dtl.effected_roomtypes,'{}') as 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
      )

      /* Get specified rate plan and its details for masterfile edit */
      WHEN 2 THEN (
        WITH def AS (
            SELECT fn_sys_param('ROOM_RATE', 'INCLUDE_ADULT', 2) AS include_adult,
                   fn_sys_param('ROOM_RATE', 'EXTRA_ADULT', 0.0) AS extra_adult,
                   fn_sys_param('ROOM_RATE', 'EXTRA_CHILD', 0.0) AS extra_child,
                   fn_sys_param('ROOM_RATE', 'EXTRA_INFANT', 0.0) AS extra_infant
        )
        Select COALESCE(json_agg(R), '[]') 
        from (
            select p.id, p.code, p.name, p.rate_type, p.plan_type AS plan_type_id, 
                fn_plantype_name(p.plan_type ) as plan_type_name, 
                --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, room_charge_dept,
                p.min_stay, p.max_stay, p.min_adv_book, p.max_adv_book, p.rate_extrabed, p.rate_child, p.rate_infant, p.comp, p.comp_abf,
                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, 
                p.min_rate, 
                p.rategroup_id, rg.name as rategroup_name, 
                p.mealtype_id,  mt.name as mealtype_name,
                p.mapped_channels as channel_count,
                (select count(*) from rate_plan where link_plan_id = p.id) children_count,
                (EXISTS(SELECT 1 FROM booking_items bi WHERE bi.rateplan_id = p.id) 
                 OR 
                 EXISTS(SELECT 1 FROM registration rg WHERE rg.rateplan_id = p.id)) AS in_use_flag,
                p.no_amend, p.no_cancel, p.no_refund, p.enabled, coalesce(p.order_seq, p.id) order_seq,
                
                -- Inclusions
                ARRAY(SELECT ri.inclusion_id FROM rate_inclusion ri WHERE ri.rateplan_id = p.id) AS 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, 
                              d.rate_id, 
                              case 
                                when p.plan_type in (5,6) then 0 
                                when p.plan_type = 9 then null
                                else d.rate_amount 
                              end as rate_amount,
                              --iif(p.plan_type=1, d.rate_amount, rd.rate_amount) as rate_amount, 
                              d.rate_extrabed, d.rate_child, d.rate_infant, 
                              d.include_adult, d.include_child, d.include_infant,
                              d.max_adult, d.max_child, d.max_infant, d.max_pax,
--                              case 
--                                when p.plan_type in (5,6,9) then null
--                                when p.link_plan_id IS NULL then t.room_rate
--                                else rd.rate_amount 
--                              end as rate_amount,
                              IIF(p.link_plan_id IS NULL, t.room_rate, rd.rate_amount) AS default_rate_amount, 
                              COALESCE(t.extrabed_rate, p.rate_extrabed,def.extra_adult)  AS default_rate_extrabed, 
                              COALESCE(t.child_rate   , p.rate_child,   def.extra_child)  AS default_rate_child, 
                              COALESCE(t.infant_rate  , p.rate_infant , def.extra_infant) AS default_rate_infant, 
                              COALESCE(t.include_adult, def.include_adult)AS default_include_adult, 
                              t.include_child AS default_include_child, 
                              t.include_infant AS default_include_infant,
                              --(select count(*) from rate_channels c where c.rate_id = d.rate_id) channel_count,
                              --(d.rate_id is not null) active 
                              coalesce(d.active, FALSE) AS active
                       --from room_type t left join rate_details d on t.id = d.roomtype_id and d.rateplan_id = p.id
                       FROM room_type t 
                            LEFT JOIN rate_details link ON link.rateplan_id = p.link_plan_id AND link.roomtype_id = t.id  
                            LEFT JOIN rate_details d ON d.rateplan_id = p.id AND d.roomtype_id = t.id
                            LEFT JOIN v_rate_details rd ON rd.rate_id = d.rate_id
                       where (p.link_plan_id IS NULL OR link.active)
                       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
                left join meal_type mt on mt.id = p.mealtype_id
                left join rate_group rg on rg.id = p.rategroup_id, def 
            where $1 is null or p.id = $1   
            order by p.order_seq, p.id
        ) as R
      ) 
      
      /* Get rate plans and its children for masterfile list */
      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.mapped_channels as channel_count, 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.mapped_channels, p.link_adjust_type, p.link_adjust_percent, p.link_adjust_amt,
                fn_rate_link_description(link.name, p.link_adjust_type, p.link_adjust_percent, p.link_adjust_amt) 
            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 as plan_type_id, p.order_seq, p.enabled, p.link_plan_id, link_description, base_plan_id,
                p.channel_count, 
                fn_plantype_name(p.plan_type ) 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_id <> 1 and base_plan_id = a.id
                      ) as c
                ) as children
            from all_plans as a
            where a.plan_type_id = 1 
            --order by a.order_seq
            union all
            SELECT p.id, p.code, p.name, p.plan_type as plan_type_id, p.order_seq, p.enabled, p.link_plan_id, 
                null as link_description, null as base_plan_id,
                p.mapped_channels as channel_count, 
                fn_plantype_name(p.plan_type ) as plan_type_name,
                false as link_rate, '[]'::json as children
            FROM rate_plan p
            where plan_type in (5,6,9) and ($1 is null or p.id = $1) and ($3 is null or p.enabled = $3)
            order by order_seq                
        )
        SELECT COALESCE(json_agg(grouped), '[]') 
        FROM grouped    
      )
    end

sp_get_rate_plans_js

Parameters

Definition