aonestar.public

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;