aonestar.public

Parameters

Name	Type	Mode
doc_data	jsonb	IN
i_photo	bytea	IN (DEFAULT NULL)
guest_id	integer	IN (DEFAULT NULL)

Definition

declare
   v_title text;
   v_address1 text;
   v_address2 text;
   v_guest_id int4;
   v_title_id int4;
   v_firstname text;
   v_lastname text;
   v_photo_path text;
   v_image_path text;
   v_doc_type int2;
   v_doc_number text;
   v_country_id int;
   v_nation_id int;
   v_sex char(1);
   v_date_of_birth date;
   v_date_of_expire date;
   v_nation_code text;
   v_country_code text;
   v_lang text = fn_intf_param('IDCARD','lang', 'en');
begin
    v_doc_type := IIF(doc_data ? 'passport_no', 1, 2);
    v_doc_number := case v_doc_type
                       when 1 then doc_data->>'passport_no'
                       when 2 then doc_data->>'card_id'
                    end;
    v_sex := coalesce((doc_data ->> 'sex')::bpchar,'');
    v_sex := case v_sex when '1' then 'M' when '2' then 'F' else null end;
    v_date_of_birth  := to_date(doc_data->> 'date_of_birth', 'YYYY-MM-DD');
    v_date_of_expire := to_date(doc_data->>'date_of_expire', 'YYYY-MM-DD');

    /* ID Card */
    if v_doc_type = 2 then
        v_country_id := (select id from country c where c.code = 'THA' limit 1);
        v_nation_id  := (select n.id from nationality n where n.iso_code = 'THA' limit 1);
        v_title    := IIF(v_lang = 'th', doc_data->>'title_th', doc_data->>'title_en');
        v_title_id := (select t.id from title t where lower(t.name) = lower(v_title) or v_title = any(t.map_names) limit 1);
        v_title_id := coalesce(v_title_id,
                         case v_sex
                            when 'M' then fn_default('TITLE_MALE', null::int)
                            when 'F' then fn_default('TITLE_FEMALE', null::int)
                            else fn_default('TITLE', null::int)
                         end
                      );
        v_firstname := IIF(v_lang = 'th', doc_data->>'name_th', doc_data->>'name_en');
        v_lastname  := IIF(v_lang = 'th', doc_data->>'lastname_th', doc_data->>'lastname_en');

        select line1, line2
        from sp_get_address_lines(doc_data -> 'address')
        into v_address1, v_address2;
    else /* Passport */
        select out_first_name, out_last_name
        from sp_parse_guest_name(doc_data ->> 'surname', doc_data ->> 'given_name')
        into v_firstname, v_lastname;

        v_country_code := doc_data ->> 'country_code';
        v_nation_code  := doc_data ->> 'nation_code';
        v_nation_code  := IIF(v_nation_code = 'D', 'DEU', v_nation_code);
        v_country_id := (select c.id from country c where c.code = v_country_code limit 1);
        v_nation_id  := (select n.id from nationality n where n.iso_code = v_nation_code limit 1);

    end if;

    ----- find existing guest profile
    if (guest_id is null) or (guest_id < 1) then
        guest_id := (Select g.id from guest g
                     where g.doc_number = v_doc_number and g.country_id = v_country_id
                     order by g.id desc limit 1);
    end if;

    guest_id := sp_save_guest(
        i_guest_id => guest_id,
        i_last_name => v_lastname,
        i_first_name => v_firstname,
        i_title_id => v_title_id,
        i_sex => v_sex,
        i_birthdate => v_date_of_birth,
        i_nation_id => v_nation_id,
        i_address1 => v_address1,
        i_address2 => v_address2,
        i_country_id => v_country_id,
        i_doc_number => v_doc_number::citext,
        i_doc_type => v_doc_type,
        i_doc_expire => v_date_of_expire,
        i_doc_country => v_country_id,
        photo => i_photo
    );

    return guest_id;
end

sp_save_guest_document

Parameters

Definition