aonestar.public

Parameters

Name	Type	Mode
i_guest_id	integer	IN
i_last_name	text	IN
i_first_name	text	IN
i_title_id	integer	IN (DEFAULT NULL)
i_sex	character	IN (DEFAULT NULL)
i_birthdate	date	IN (DEFAULT NULL)
i_nation_id	integer	IN (DEFAULT NULL)
i_lang_id	integer	IN (DEFAULT NULL)
i_vip_id	integer	IN (DEFAULT NULL)
i_address1	text	IN (DEFAULT NULL)
i_address2	text	IN (DEFAULT NULL)
i_city_id	integer	IN (DEFAULT NULL)
i_country_id	integer	IN (DEFAULT NULL)
i_country_res_id	integer	IN (DEFAULT NULL)
i_emails	text[]	IN (DEFAULT NULL)
i_telephones	text[]	IN (DEFAULT NULL)
i_mobiles	text[]	IN (DEFAULT NULL)
i_company	text	IN (DEFAULT NULL)
i_occupation_id	integer	IN (DEFAULT NULL)
i_doc_number	citext	IN (DEFAULT NULL)
i_doc_type	smallint	IN (DEFAULT NULL)
i_doc_expire	date	IN (DEFAULT NULL)
i_doc_country	integer	IN (DEFAULT NULL)
i_credit_limit	t_money	IN (DEFAULT NULL)
i_confidential	boolean	IN (DEFAULT NULL)
i_member_code	citext	IN (DEFAULT NULL)
i_carplate_no	text	IN (DEFAULT NULL)
i_remark	text	IN (DEFAULT NULL)
socials	t_social_account[]	IN (DEFAULT NULL)
i_likes	text[]	IN (DEFAULT NULL)
i_dislikes	text[]	IN (DEFAULT NULL)
credit_cards	t_credit_card[]	IN (DEFAULT NULL)
photo	bytea	IN (DEFAULT NULL)
photo_url	text	IN (DEFAULT NULL)
preserve_old_values	boolean	IN (DEFAULT true)
i_check_guest_exist	boolean	IN (DEFAULT false)
out_guest_id	integer	OUT

Definition

DECLARE 
    params text[];
    cr record;
    cards int[] = '{}';
    _photo_id bigint;
    is_photo_deleted bool = FALSE;
BEGIN
   -- PERFORM sp_log_debug('sp_save_guest', i_mobiles::text); 
    
    is_photo_deleted := (i_guest_id IS NOT NULL) AND (photo IS NULL) AND (photo_url IS NULL); 
    
    IF (photo IS NOT NULL) AND (i_guest_id IS NOT NULL) THEN 
        _photo_id := doc.save_file('guest_photo', i_guest_id, null, photo);
    ELSIF is_photo_deleted THEN  
        _photo_id := NULL;
    END IF;
   
    
    i_doc_number  := nullif(i_doc_number, ''::citext);
    i_doc_type    := IIF(i_doc_number is null, null, i_doc_type); -- coalesce(i_doc_type, 0::smallint);
    i_doc_country := coalesce(i_doc_country , 0::integer); 
    i_city_id     := nullif(i_city_id, 0);  
    i_occupation_id := nullif(i_occupation_id, 0);  
    i_lang_id     := nullif(i_lang_id, 0);  
    i_sex         := case  
                        when i_sex in ('1','M') then 'M' 
                        when i_sex in ('2','F') then 'F' 
                        else null
                     end;
   
   
    if (i_check_guest_exist = true) and (i_doc_number <> '') and (i_doc_type <> 0) and (i_doc_country <> 0) then
        select g.id into i_guest_id
        from guest g 
        where g.doc_number = v_card_id
        and g.country_id = v_country_id 
        limit 1;
    end if;
   
    if (i_guest_id is NULL OR i_guest_id < 1) THEN
        params := fn_get_params('DEFAULT', array['TITLE','VIP','NATIONALITY', 'TITLE_MALE', 'TITLE_FEMALE']);
        
        if i_sex not in ('F','M') then
            i_sex = null;
        end if;
        
        if (i_sex = 'M') and (i_title_id is null) then
            i_title_id := params[4]::int;
        end if;
        
        if (i_sex = 'F') and (i_title_id is null) then
            i_title_id := params[5]::int;
        end if;
        
        i_title_id  = coalesce(i_title_id, params[1]::int);
        
        if (i_sex is null) and (i_title_id is not null) then
            i_sex := (select t.sex from title t where t.id = i_title_id);
        end if;
        
        i_vip_id    = coalesce(i_vip_id, params[2]::int);
        i_nation_id = coalesce(NULLIF(i_nation_id,0), params[3]::int);

        -- Add new guest record
        INSERT INTO guest (title_id, first_name, last_name, sex, birthdate, nation_id, lang_id, vip_id, address1, address2, 
              city_id, country_id, country_res_id, company, occupation_id, doc_number, doc_type, credit_limit, 
              confidential, member_code, carplate_no, remark, telephones, mobiles, emails, doc_country, doc_expire, likes, dislikes) 
        VALUES (i_title_id, i_first_name, i_last_name, i_sex, i_birthdate, i_nation_id, i_lang_id, i_vip_id, i_address1, i_address2, 
              i_city_id, i_country_id, i_country_res_id, i_company, i_occupation_id, i_doc_number, i_doc_type, i_credit_limit, 
              i_confidential, i_member_code, i_carplate_no, i_remark, i_telephones, i_mobiles, i_emails, i_doc_country, i_doc_expire, i_likes, i_dislikes)
        returning id into i_guest_id;
        
        IF (photo IS NOT NULL) THEN 
            _photo_id := doc.save_file('guest_photo', i_guest_id, null, photo);    
            UPDATE guest SET photo_id = _photo_id WHERE id = i_guest_id;
        END IF;
    elsif preserve_old_values = true then
        -- Update existing guest, preserve old data
        UPDATE guest
        SET title_id        = coalesce(i_title_id, title_id), 
            first_name      = iif(i_first_name is not null and i_last_name is not null, i_first_name, first_name), 
            last_name       = iif(i_first_name is not null and i_last_name is not null, i_last_name, last_name),
            sex             = coalesce(i_sex, sex), 
            birthdate       = coalesce(i_birthdate, birthdate),
            nation_id       = coalesce(i_nation_id, nation_id), 
            lang_id         = coalesce(i_lang_id, lang_id), 
            vip_id          = coalesce(i_vip_id, vip_id), 
            address1        = coalesce(i_address1, address1), 
            address2        = coalesce(i_address2, address2), 
            city_id         = coalesce(i_city_id, city_id), 
            country_id      = coalesce(i_country_id, country_id), 
            country_res_id  = coalesce(i_country_res_id, country_res_id), 
            company         = coalesce(i_company, company), 
            occupation_id   = coalesce(i_occupation_id, occupation_id), 
            credit_limit    = coalesce(i_credit_limit, credit_limit), 
            confidential    = coalesce(i_confidential, confidential), 
            member_code     = coalesce(i_member_code, member_code), 
            carplate_no     = coalesce(i_carplate_no, carplate_no),  
            remark          = coalesce(i_remark, remark),  
            telephones      = coalesce(i_telephones, telephones), 
            mobiles         = coalesce(i_mobiles, mobiles), 
            emails          = coalesce(i_emails, emails), 
            doc_number      = coalesce(i_doc_number, doc_number), 
            doc_type        = coalesce(i_doc_type, doc_type), 
            doc_country     = coalesce(i_doc_country, doc_country), 
            doc_expire      = coalesce(i_doc_expire, doc_expire),
            likes           = coalesce(nullif(i_likes, '{}'), likes),  
            dislikes        = coalesce(nullif(i_dislikes, '{}'), dislikes),
            photo_id        = coalesce(_photo_id, photo_id)
        WHERE id = i_guest_id;
    else
        -- Update existing guest, replace old data
        UPDATE guest
        SET title_id        = i_title_id, 
            first_name      = i_first_name, 
            last_name       = i_last_name, 
            sex             = i_sex, 
            birthdate       = i_birthdate, 
            nation_id       = i_nation_id, 
            lang_id         = i_lang_id, 
            vip_id          = i_vip_id, 
            address1        = i_address1, 
            address2        = i_address2, 
            city_id         = i_city_id, 
            country_id      = i_country_id, 
            country_res_id  = i_country_res_id, 
            company         = i_company, 
            occupation_id   = i_occupation_id, 
            credit_limit    = i_credit_limit, 
            confidential    = i_confidential, 
            member_code     = i_member_code, 
            carplate_no     = i_carplate_no,  
            remark          = i_remark,  
            telephones      = i_telephones, 
            mobiles         = i_mobiles,
            emails          = i_emails, 
            doc_number      = i_doc_number, 
            doc_type        = i_doc_type, 
            doc_country     = i_doc_country, 
            doc_expire      = i_doc_expire,
            likes           = i_likes, 
            dislikes        = i_dislikes,
            photo_id        = COALESCE(_photo_id, IIF(is_photo_deleted, NULL, photo_id))
        WHERE id = i_guest_id;
    end if;
    
    IF is_photo_deleted THEN 
       PERFORM doc.delete_file('guest_photo', i_guest_id);
    END IF;
    
    out_guest_id = i_guest_id;

    IF coalesce(out_guest_id, 0) > 0 AND credit_cards IS NOT NULL THEN
        FOR cr IN SELECT * from unnest(credit_cards)
        LOOP
            IF (cr.id IS NULL OR cr.id < 1) then
                INSERT INTO credit_card(guest_id, card_number, card_type, expire_month, expire_year)
                values(out_guest_id, cr.card_number, cr.card_type, cr.expire_month, cr.expire_year)
                RETURNING id INTO cr.id;
            ELSE 
                UPDATE credit_card
                SET card_number = cr.card_number, 
                    card_type = cr.card_type, 
                    expire_month = cr.expire_month, 
                    expire_year = cr.expire_year
                WHERE id = cr.id;
            END IF;
            cards = cards || cr.id;
        END LOOP;
    
        UPDATE guest 
        SET credit_cards = cards
        WHERE id = out_guest_id;
    END IF;
    
    DELETE FROM guest_social WHERE guest_id = out_guest_id;
    
    INSERT INTO guest_social(guest_id, social_id, account_name)
    SELECT out_guest_id, sc.id, sc.account_name
    FROM unnest(socials) AS sc;
    
    
--  IF coalesce(out_guest_id, 0) > 0 THEN 
--      DELETE FROM credit_card 
--      WHERE guest_id = out_guest_id AND id NOT IN (SELECT id FROM unnest(credit_cards));
--  END IF;
 
end