aonestar.public

Parameters

Name	Type	Mode

Definition

declare 
        json_updates text;
        current_propcode text;
    begin
        /**
         * Create by KenG (2020/08/04)
         */
        --Get param group old value that changed
        --json_old_group_update := fn_json_diff(to_jsonb(old.system_params), to_jsonb(new.system_params));
        --Get param group new value that changed
        --json_new_group_update := fn_json_diff(to_jsonb(new.system_params), to_jsonb(old.system_params));
        
        --Get key of param_group changed
        /*if(json_new_group_update is not null) then
            FOR keyname_param_group IN SELECT * FROM jsonb_object_keys(json_new_group_update)
            LOOP
              --RAISE NOTICE '%: %', key_toplevel, js->>col;
              --perform sp_sys_log('update_syspams_key', keyname_param_group::text);
              json_updates := fn_json_diff(to_jsonb(json_new_group_update->keyname_param_group), to_jsonb(json_old_group_update->keyname_param_group));
           END LOOP;
        end if;*/
    
        json_updates := fn_json_diff(to_jsonb(new.system_params), to_jsonb(old.system_params));
        
    
        /**
         * Check null
         * if json_updates return null is mean value not change 
         */
    
        if json_updates is not null then
            current_propcode = fn_current_propcode();
            perform sp_enqueue_realtime('*', null, 'system_params', json_updates::json);
        end if;

        if new.system_params->'SYSTEM'->>'SYSTEM_DATE' is distinct from old.system_params->'SYSTEM'->>'SYSTEM_DATE' then
            perform sp_enqueue_realtime('*', null, 'system_date_changed', 
                    json_build_object('SYSTEM_DATE', new.system_params->'SYSTEM'->>'SYSTEM_DATE')
            );
        end if;

        if new.system_params->'SYSTEM'->>'NAME_FORMAT' is distinct from old.system_params->'SYSTEM'->>'NAME_FORMAT' then
            with upd as (
                select g.id, 
                       fn_fullname(
                            last_name, 
                            first_name, 
                            middle_name, 
                            t.name, 
                            coalesce(new.system_params->'SYSTEM'->>'NAME_FORMAT', 'LFMT')
                       ) as fullname
                from guest g left join title t on t.id = g.title_id
            )
            update guest g
            set full_name = u.fullname
            from upd u
            where g.id = u.id;
        end if;

        return null;
    END;

sp_tr_update_system_params

Parameters

Definition