aonestar.public

Parameters

Name	Type	Mode
p_data	jsonb	IN

Definition

declare
    v_result json;
    v_msg_text text;
    v_sqlstate text;
    v_detail text;
    v_hint text;
    v_context text;
--------------------------------------------

    v_limit_more_guest int4;
    v_register record;
    v_current_more_guest int4;
   
    v_register_id int4;
    v_guest_id int4;
--------------------------------------------
   
    messages    jsonb = $${
        "70001": "Limit add moreguest",
        "79999": "Moreguest already exists"
    }$$;
   
--------------------------------------------   
    begin
        /*
        {
            "data" : {
                "register_id" : 1,
                "guest_id" : 2
            }
        }
        */
        
        v_register_id := (p_data -> 'data' ->> 'register_id')::int4;
        v_guest_id := (p_data -> 'data' ->> 'guest_id')::int4;
                
        -- เช็คจำนวนคนไม่ให้เกิน  ถ้าไม่เกิน ก่อ insert ได้
        -- กรอง status  = I
        select r.* into v_register
        from registration r
        where r.status = 'I' 
        and r.id = v_register_id;
    
        if found then   -- เจอการ check in
            v_limit_more_guest := v_register.adult + v_register.child + v_register.infant;
        
            select count(1) into v_current_more_guest
            from register_guests rg 
            where rg.register_id = v_register_id;
           
            v_current_more_guest := v_current_more_guest + 1; -- บวกตัวเองเป็น guest 
           
            if v_current_more_guest < v_limit_more_guest then

                if exists (
                    select 1 
                    from register_guests
                    where register_id = v_register_id
                    and guest_id = v_guest_id
                ) then
                    perform sp_raise_error('79999', messages->>'79999'); 
                else
                    insert into register_guests (
                        register_id
                        , guest_id
                    ) values (
                        v_register_id
                        , v_guest_id
                    );
                               
                                
                    return fn_result_success(v_result); 
                end if;
            
            
               
            end if;
      --  else       
        end if;
        
  --  return fn_result_warning(v_result);   
       
     perform  sp_raise_error('70001', messages->>'70001');
        

exception
    when others then
        get stacked diagnostics
            v_msg_text = message_text,
            v_sqlstate = returned_sqlstate,
            v_detail = pg_exception_detail,
            v_hint = pg_exception_hint,
            v_context = pg_exception_context;
            
        return fn_handle_error(
            v_sqlstate,
            v_msg_text,
            v_detail,
            v_hint,
            v_context,
            'sp_save_reservation_more_guest_json',
            null
        );
end;