aonestar.public

Parameters

Name	Type	Mode
src_guest_id	integer	IN
des_guest_id	integer	IN

Definition

DECLARE
    v_locked_count integer;
    v_src guest%ROWTYPE;
BEGIN
    PERFORM sp_raise_error_if(src_guest_id IS NULL, 'Please specify source guest to merge');
    PERFORM sp_raise_error_if(des_guest_id IS NULL, 'Please specify destination guest to merge');
    PERFORM sp_raise_error_if(src_guest_id = des_guest_id, 'Source and destination guest must be different');

PERFORM 1 FROM guest g
    WHERE g.id IN (src_guest_id, des_guest_id)
    ORDER BY g.id
    FOR UPDATE;

SELECT count(*) INTO v_locked_count
    FROM guest g WHERE g.id IN (src_guest_id, des_guest_id);
    PERFORM sp_raise_error_if(v_locked_count <> 2, 'Source or destination guest not found');

SELECT * INTO v_src FROM guest WHERE id = src_guest_id;

DELETE FROM guest_social s
    WHERE s.guest_id = src_guest_id
      AND EXISTS (SELECT 1 FROM guest_social d
                  WHERE d.guest_id = des_guest_id AND d.social_id = s.social_id);
    UPDATE guest_social SET guest_id = des_guest_id WHERE guest_id = src_guest_id;

DELETE FROM booking_guests s
    WHERE s.guest_id = src_guest_id
      AND EXISTS (SELECT 1 FROM booking_guests d
                  WHERE d.guest_id = des_guest_id AND d.booking_id = s.booking_id);
    UPDATE booking_guests SET guest_id = des_guest_id WHERE guest_id = src_guest_id;

DELETE FROM register_guests s
    WHERE s.guest_id = src_guest_id
      AND EXISTS (SELECT 1 FROM register_guests d
                  WHERE d.guest_id = des_guest_id AND d.register_id = s.register_id);
    UPDATE register_guests SET guest_id = des_guest_id WHERE guest_id = src_guest_id;

DELETE FROM backup.booking_guests s
    WHERE s.guest_id = src_guest_id
      AND EXISTS (SELECT 1 FROM backup.booking_guests d
                  WHERE d.guest_id = des_guest_id AND d.booking_id = s.booking_id);
    UPDATE backup.booking_guests SET guest_id = des_guest_id WHERE guest_id = src_guest_id;

DELETE FROM backup.register_guests s
    WHERE s.guest_id = src_guest_id
      AND EXISTS (SELECT 1 FROM backup.register_guests d
                  WHERE d.guest_id = des_guest_id AND d.register_id = s.register_id);
    UPDATE backup.register_guests SET guest_id = des_guest_id WHERE guest_id = src_guest_id;

DELETE FROM room_summary s
    WHERE s.guest_id = src_guest_id
      AND EXISTS (SELECT 1 FROM room_summary d
                  WHERE d.guest_id = des_guest_id
                    AND d.date_ref = s.date_ref
                    AND d.register_id IS NOT DISTINCT FROM s.register_id);
    UPDATE room_summary SET guest_id = des_guest_id WHERE guest_id = src_guest_id;

DELETE FROM guest_signature s
    WHERE s.guest_id = src_guest_id
      AND EXISTS (
          SELECT 1 FROM guest_signature d
          WHERE d.guest_id = des_guest_id
            AND ((s.booking_id IS NOT NULL AND d.booking_id = s.booking_id)
              OR (s.register_id IS NOT NULL AND d.register_id = s.register_id)
              OR (s.folio_item_id IS NOT NULL AND d.folio_item_id = s.folio_item_id))
      );
    UPDATE guest_signature SET guest_id = des_guest_id WHERE guest_id = src_guest_id;

UPDATE credit_card SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    UPDATE guest_document SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    UPDATE booking SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    UPDATE registration SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    UPDATE rsv_pickup SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    UPDATE guest_history SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    UPDATE precheckin_consent_logs SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    UPDATE precheckin_legal_acceptance SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    UPDATE backup.booking SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    UPDATE backup.registration SET guest_id = des_guest_id WHERE guest_id = src_guest_id;

IF to_regclass('public.regis') IS NOT NULL THEN
        UPDATE regis SET guest_id = des_guest_id WHERE guest_id = src_guest_id;
    END IF;

DELETE FROM guest WHERE id = src_guest_id;

UPDATE guest d
    SET title_id = COALESCE(d.title_id, v_src.title_id),
        first_name = COALESCE(NULLIF(d.first_name, ''), v_src.first_name),
        last_name = COALESCE(NULLIF(d.last_name, ''), v_src.last_name),
        sex = COALESCE(d.sex, v_src.sex),
        birthdate = COALESCE(d.birthdate, v_src.birthdate),
        nation_id = COALESCE(d.nation_id, v_src.nation_id),
        lang_id = COALESCE(d.lang_id, v_src.lang_id),
        vip_id = COALESCE(d.vip_id, v_src.vip_id),
        address1 = COALESCE(NULLIF(d.address1, ''), v_src.address1),
        address2 = COALESCE(NULLIF(d.address2, ''), v_src.address2),
        country_id = COALESCE(d.country_id, v_src.country_id),
        country_res_id = COALESCE(d.country_res_id, v_src.country_res_id),
        telephone = COALESCE(NULLIF(d.telephone, ''), v_src.telephone),
        mobile = COALESCE(NULLIF(d.mobile, ''), v_src.mobile),
        email = COALESCE(NULLIF(d.email, ''), v_src.email),
        company = COALESCE(NULLIF(d.company, ''), v_src.company),
        member_code = COALESCE(NULLIF(d.member_code, ''::citext), v_src.member_code),
        remark = COALESCE(NULLIF(d.remark, ''), v_src.remark),
        doc_number = COALESCE(NULLIF(d.doc_number, ''::citext), v_src.doc_number),
        doc_type = COALESCE(d.doc_type, v_src.doc_type),
        doc_country = COALESCE(NULLIF(d.doc_country, 0), v_src.doc_country),
        doc_expire = COALESCE(d.doc_expire, v_src.doc_expire),
        doc_id = COALESCE(d.doc_id, v_src.doc_id),
        created_at = LEAST(d.created_at, v_src.created_at),
        credit_cards = (SELECT COALESCE(array_agg(c.id ORDER BY c.id), '{}')
                        FROM credit_card c WHERE c.guest_id = des_guest_id)
    WHERE d.id = des_guest_id;

PERFORM sp_refresh_guest_stat(des_guest_id);
END

sp_guest_merge

Description

Parameters

Definition