aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN (DEFAULT NULL)
reset_extra_qty	boolean	IN (DEFAULT false)

Definition

DECLARE 
    _other_charge t_money;
    total record;
    rateplan_id int;
    roomtype_id int;
BEGIN

if reset_extra_qty then
        for total in (
            with rg as (
                select rg.id as register_id,
                    rg.rateplan_id,
                    rg.adult,
                    rg.child,
                    rg.mealtype_id,
                    coalesce(ex.extra_adult, rg.extra_adult) as extra_adult,
                    coalesce(ex.extra_child, rg.extra_child) as extra_child,
                    coalesce(ex.extra_infant, rg.extra_infant) as extra_infant
                from registration rg 
                    left join room rm on rm.id = rg.room_id,
                    lateral sp_calc_extra(rg.rateplan_id, rm.roomtype_id, rg.adult, rg.child, rg.infant) ex
                where $1 is null or rg.id = $1
            )
            select rg.register_id, rg.extra_adult, rg.extra_child, rg.extra_infant,
                   sum(IIF(rp.plan_type = 5, 0, rr.room_rate)
                       + IIF(rp.plan_type = 5 OR COALESCE(rr.room_rate::numeric, 0) = 0, COALESCE(mp.meal_amount, 0::t_money), 0::t_money)
                   ) as room_charge,
                     coalesce(sum(rg.extra_adult  * rr.extrabed_rate), 0.0)
                   + coalesce(sum(rg.extra_child  * rr.child_rate), 0.0)
                   + coalesce(sum(rg.extra_infant * rr.infant_rate), 0.0) as extra_charge
            from  rg
                left join registration_rates rr on rr.register_id = rg.register_id
                left join rate_plan rp on rp.id = COALESCE(rr.rateplan_id, rg.rateplan_id)
                left join lateral (
                    select (
                        COALESCE(inc_abf::numeric, 0)
                        + COALESCE(inc_lunch::numeric, 0)
                        + COALESCE(inc_dinner::numeric, 0)
                    )::t_money as meal_amount
                    from sp_calc_meal_prices(
                        COALESCE(rr.mealtype_id, rg.mealtype_id),
                        rg.adult - rg.extra_adult,
                        rg.child - rg.extra_child,
                        rg.extra_adult,
                        rg.extra_child
                    )
                ) mp on true
            group by 1,2,3,4)
        loop
            _other_charge = (select sum(charge_qty * charge_amount) 
                             from charge_schedule cs 
                             where cs.register_id = total.register_id and cs.charge_type <> 'inclusion');
    
            update registration r
            set room_charge = total.room_charge, 
                extra_charge = total.extra_charge,
                other_charge = _other_charge,
                extra_adult  = total.extra_adult,
                extra_child  = total.extra_child,
                extra_infant = total.extra_infant
            where r.id = total.register_id;
        end loop;
    else
        for total in (
            select rg.id as register_id,
                   sum(IIF(rp.plan_type = 5, 0, rr.room_rate)
                       + IIF(rp.plan_type = 5 OR COALESCE(rr.room_rate::numeric, 0) = 0, COALESCE(mp.meal_amount, 0::t_money), 0::t_money)
                   ) as room_charge,
                     coalesce(sum(rg.extra_adult  * rr.extrabed_rate), 0.0)
                   + coalesce(sum(rg.extra_child  * rr.child_rate), 0.0)
                   + coalesce(sum(rg.extra_infant * rr.infant_rate), 0.0) as extra_charge
            from registration rg
                left join registration_rates rr on rr.register_id = rg.id
                left join rate_plan rp on rp.id = COALESCE(rr.rateplan_id, rg.rateplan_id)
                left join lateral (
                    select (
                        COALESCE(inc_abf::numeric, 0)
                        + COALESCE(inc_lunch::numeric, 0)
                        + COALESCE(inc_dinner::numeric, 0)
                    )::t_money as meal_amount
                    from sp_calc_meal_prices(
                        COALESCE(rr.mealtype_id, rg.mealtype_id),
                        rg.included_adult,
                        rg.included_child,
                        rg.extra_adult,
                        rg.extra_child
                    )
                ) mp on true
            where $1 is null or rg.id = $1
            group by 1)
        loop
            _other_charge = (select sum(charge_qty * charge_amount) 
                             from charge_schedule cs 
                             where cs.register_id = total.register_id and cs.charge_type <> 'inclusion');
    
            update registration rg
            set room_charge = total.room_charge, 
                extra_charge = total.extra_charge,
                other_charge = _other_charge
            where rg.id = total.register_id;
        end loop;
    end if;

END

sp_update_registration_summary

Parameters

Definition