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_meal_type record;
    
    v_mealtype_id int;
    v_name text;
    v_abf numeric(15,2);
    v_abf_child numeric(15,2);
    v_abf_extra_adult numeric(15,2);
    v_abf_extra_child numeric(15,2);
    v_lunch numeric(15,2);
    v_lunch_child numeric(15,2);
    v_lunch_extra_adult numeric(15,2);
    v_lunch_extra_child numeric(15,2);
    v_dinner numeric(15,2);
    v_dinner_child numeric(15,2);
    v_dinner_extra_adult numeric(15,2);
    v_dinner_extra_child numeric(15,2);
    v_enabled boolean;

messages jsonb = $${
        "80001": "not found data"
    }$$;
    
    begin
        perform sp_log_debug('sp_update_mealtype_by_id_json', p_data::text, true);
        v_mealtype_id := (p_data ->> 'id')::int4;
        
        v_name := nullif(p_data ->> 'name', '');
         
        -- จัดการค่า null และสตริงว่าง
        v_abf := nullif(p_data ->> 'abf', '')::numeric(15,2);
        v_abf_child := nullif(p_data ->> 'abf_child', '')::numeric(15,2);
        v_abf_extra_adult := nullif(p_data ->> 'abf_extra_adult', '')::numeric(15,2);
        v_abf_extra_child := nullif(p_data ->> 'abf_extra_child', '')::numeric(15,2);
        v_lunch := nullif(p_data ->> 'lunch', '')::numeric(15,2);
        v_lunch_child := nullif(p_data ->> 'lunch_child', '')::numeric(15,2);
        v_lunch_extra_adult := nullif(p_data ->> 'lunch_extra_adult', '')::numeric(15,2);
        v_lunch_extra_child := nullif(p_data ->> 'lunch_extra_child', '')::numeric(15,2);
        v_dinner := nullif(p_data ->> 'dinner', '')::numeric(15,2);
        v_dinner_child := nullif(p_data ->> 'dinner_child', '')::numeric(15,2);
        v_dinner_extra_adult := nullif(p_data ->> 'dinner_extra_adult', '')::numeric(15,2);
        v_dinner_extra_child := nullif(p_data ->> 'dinner_extra_child', '')::numeric(15,2);
        
        -- สำหรับค่าบูลีน ตรวจสอบว่าเป็นค่าบูลีนที่ถูกต้อง
        v_enabled := case 
            when p_data ->> 'enabled' is null or p_data ->> 'enabled' = '' then null
            else (p_data ->> 'enabled')::boolean
        end;
        
        select mt.* into v_meal_type
        from meal_type mt 
        where mt.id = v_mealtype_id;
        
        if v_meal_type is null then 
            return fn_result_error('80001', messages->>'80001'); 
        end if;
        
        update meal_type 
        set 
            name = case when p_data ? 'name' then v_name else name end,
            abf = case when p_data ? 'abf' then v_abf else abf end,
            abf_child = case when p_data ? 'abf_child' then v_abf_child else abf_child end,
            abf_extra_adult = case when p_data ? 'abf_extra_adult' then v_abf_extra_adult else abf_extra_adult end,
            abf_extra_child = case when p_data ? 'abf_extra_child' then v_abf_extra_child else abf_extra_child end,
            lunch = case when p_data ? 'lunch' then v_lunch else lunch end,
            lunch_child = case when p_data ? 'lunch_child' then v_lunch_child else lunch_child end,
            lunch_extra_adult = case when p_data ? 'lunch_extra_adult' then v_lunch_extra_adult else lunch_extra_adult end,
            lunch_extra_child = case when p_data ? 'lunch_extra_child' then v_lunch_extra_child else lunch_extra_child end,
            dinner = case when p_data ? 'dinner' then v_dinner else dinner end,
            dinner_child = case when p_data ? 'dinner_child' then v_dinner_child else dinner_child end,
            dinner_extra_adult = case when p_data ? 'dinner_extra_adult' then v_dinner_extra_adult else dinner_extra_adult end,
            dinner_extra_child = case when p_data ? 'dinner_extra_child' then v_dinner_extra_child else dinner_extra_child end,
            enabled = case when p_data ? 'enabled' then v_enabled else enabled end
        where id = v_mealtype_id;
         
        return fn_result_success();
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_update_mealtype_by_id_json',
            null 
        );        
        
    end;