aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_replace_more_guest_inhouse_json
Parameters
Name
Type
Mode
p_data
jsonb
IN
Definition
declare v_result json; eff_result int; v_new_guest_doc_id integer; v_msg_text text; v_sqlstate text; v_detail text; v_hint text; v_context text; v_item jsonb; v_new_generate_guest_id int4; v_guest_doc_data jsonb; v_surname text; v_doc_type int4; v_result_parse_guest_name record; ----------------------------------------- v_arrival_date date; v_register_id int4; v_guest_id int4; v_guest_doc_id int4; v_guest_doc record; v_guest_id_in_doc int4; v_country_code text; v_nation_code text; ------------------------ v_record record; v_results_array jsonb[]; begin /* Example input: { "data": [ { "arrival_date": "2028-05-20", "register_id": 11941, "old": {"guest_id": 620868}, "new": {"guest_doc_id": 2383} }, { "arrival_date": "2028-05-20", "register_id": 11941, "old": {"guest_id": 620867}, "new": {"guest_doc_id": 2384} } ] } */ -- เริ่มต้นด้วย array ว่าง v_results_array := array[]::jsonb[]; RAISE NOTICE '=== เริ่มการประมวลผลข้อมูล ==='; -- Loop through data array FOR v_record IN SELECT value FROM jsonb_array_elements(p_data -> 'data') LOOP -- แสดงค่าแต่ละ record -- RAISE NOTICE 'ข้อมูลที่กำลังประมวลผล:'; -- RAISE NOTICE 'arrival_date: %', v_record.value ->> 'arrival_date'; -- RAISE NOTICE 'register_id: %', v_record.value ->> 'register_id'; -- RAISE NOTICE 'old guest_id: %', v_record.value -> 'old' ->> 'guest_id'; -- RAISE NOTICE 'new guest_doc_id: %', v_record.value -> 'new' ->> 'guest_doc_id'; -- RAISE NOTICE '------------------------'; -- Extract values from current item v_arrival_date := (v_record.value ->> 'arrival_date')::date; v_register_id := (v_record.value ->> 'register_id')::int4; v_guest_id := (v_record.value -> 'old' ->> 'guest_id')::int4; v_guest_doc_id := (v_record.value -> 'new' ->> 'guest_doc_id')::int4; -- v_guest_doc := (select gd.* from guest_document gd where gd.id=v_guest_doc_id order by gd.id desc limit 1); -- perform sp_log_debug('sp_replace_more_guest_inhouse_json', ' v_guest_id ค่าก่อน select :' || coalesce(v_guest_id::text, 'บรรรลัยยยยยยยยยยยย') , true ); -- perform sp_log_debug('sp_replace_more_guest_inhouse_json', ' v_guest_doc_id ค่าก่อน select :' || coalesce(v_guest_doc_id::text, 'บรรรลัยยยยยยยยยยยย'), true); select gd.* into v_guest_doc from guest_document gd where gd.id=v_guest_doc_id limit 1; if found then v_guest_id_in_doc := coalesce(v_guest_doc.guest_id, 0); v_doc_type := v_guest_doc.doc_type; v_guest_doc_data := v_guest_doc.doc_data; if v_guest_id_in_doc = 0 then -- in guest_doc not have guest_id if v_doc_type = 1 then -- passport /* { "sex": "M", "surname": "LEONG KAR WAH", "given_name": "", "nation_code": "MYS", "passport_no": "A30194194", "country_code": "MYS", "date_of_birth": "1988-02-07", "date_of_expire": "2018-10-10", "personal_number": "880207675039" } */ v_country_code := v_guest_doc_data ->> 'country_code'; v_nation_code := v_guest_doc_data ->> 'nation_code'; select * from sp_parse_guest_name( p_surname := v_guest_doc_data ->> 'surname', p_given_name := v_guest_doc_data ->> 'given_name' ) into v_result_parse_guest_name; select * from sp_save_guest( i_guest_id := null, i_last_name := v_result_parse_guest_name.out_last_name , i_first_name := v_result_parse_guest_name.out_first_name, i_sex := v_guest_doc_data ->> 'sex', i_birthdate := (v_guest_doc_data ->> 'date_of_birth')::date, i_nation_id := (select n.id from nationality n where n.iso_code = v_nation_code limit 1 ), i_country_id := (select c.id from country c where c.code = v_country_code limit 1), i_doc_number := (v_guest_doc_data ->> 'passport_no')::citext, i_doc_type := v_doc_type::smallint , i_doc_expire := (v_guest_doc_data ->> 'date_of_expire')::date, i_doc_country := v_guest_doc.country_id ) into v_new_generate_guest_id; else -- id card /* { "sex": "F", "address": { "moo": "", "soi": "", "road": "", "trok": "", "addr_no": "88/48 ซอยพฤกษชาติ", "district": "เขตสะพานสูง", "province": "กรุงเทพมหานคร", "subdistrict": "แขวงสะพานสูง" }, "card_id": "3101200191882", "name_en": "", "name_th": "Waraphorn", "title_en": "", "title_th": "Mrs.", "lastname_en": "", "lastname_th": "Phongwilai", "date_of_birth": "1951-01-01", "date_of_expire": "2015-12-31" } */ v_guest_doc_data := v_guest_doc.doc_data; select * from sp_save_guest( i_guest_id := null, i_last_name := COALESCE(NULLIF(v_guest_doc_data ->> 'lastname_en', ''), v_guest_doc_data ->> 'lastname_th')::text, i_first_name := COALESCE(NULLIF(v_guest_doc_data ->> 'name_en', ''), v_guest_doc_data ->> 'name_th')::text, i_sex := v_guest_doc_data ->> 'sex', i_birthdate := (v_guest_doc_data ->> 'date_of_birth')::date, i_nation_id := 1, i_country_id := 1, i_doc_number := (v_guest_doc_data ->> 'card_id')::citext, i_doc_type := v_doc_type::smallint , i_doc_expire := (v_guest_doc_data ->> 'date_of_expire')::date, i_doc_country := 1 ) into v_new_generate_guest_id; end if; update guest_document set guest_id = v_new_generate_guest_id where id = v_guest_doc_id; else -- in guest_doc have guest_id v_new_generate_guest_id := v_guest_doc.guest_id; end if; -- Delete old guest DELETE FROM register_guests WHERE register_id = v_register_id AND guest_id = v_guest_id; -- Insert new guest INSERT INTO register_guests(register_id, guest_id) VALUES(v_register_id, v_new_generate_guest_id); end if; END LOOP; select * into v_result from sp_get_scan_inhouse_json(null); RETURN COALESCE(v_result, '[]'::json); 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_replace_more_guest_inhouse_json', NULL ); END;