aonestar.public

Parameters

Name	Type	Mode
i_booking_id	integer	IN (DEFAULT NULL)
i_register_id	integer	IN (DEFAULT NULL)
print_mode	character	IN (DEFAULT NULL)
booking_id	integer	OUT
booking_item	integer	OUT
register_id	integer	OUT
confirm_number	integer	OUT
room_number	t_room_number	OUT
room_type	text	OUT
roomtype_code	text	OUT
rate_plan	text	OUT
booking_name	text	OUT
adult	integer	OUT
child	integer	OUT
infant	integer	OUT
channel	text	OUT
arrival	date	OUT
departure	date	OUT
nights	integer	OUT
arrival_time	time without time zone	OUT
departure_time	time without time zone	OUT
arrive_by	text	OUT
arrive_from	text	OUT
depart_by	text	OUT
depart_to	text	OUT
voucher_number	text	OUT
deposit_amount	t_money	OUT
payment_method	text	OUT
payment_voucher	boolean	OUT
payment_card	boolean	OUT
guest_id	integer	OUT
guest_title	text	OUT
guest_first_name	text	OUT
guest_last_name	text	OUT
guest_middle_name	text	OUT
guest_full_name	text	OUT
guest_sex	character	OUT
guest_birthdate	date	OUT
guest_nationality	text	OUT
guest_language	text	OUT
guest_vip_level	text	OUT
guest_address1	text	OUT
guest_address2	text	OUT
guest_country	text	OUT
guest_country_of_residence	text	OUT
guest_city	text	OUT
guest_email	text	OUT
guest_telephone	text	OUT
guest_mobile	text	OUT
guest_fax	text	OUT
guest_company	text	OUT
guest_occupation	text	OUT
guest_doc_type	smallint	OUT
guest_doc_number	text	OUT
guest_doc_expire	date	OUT
guest_member_code	text	OUT
guest_carplate_no	text	OUT
guest_credit_cards	t_credit_card[]	OUT

Definition

DECLARE
    rec record;
    _guest_id int;
    _guests int[];
    _unknown_guests int;
BEGIN
/*
*  print_mode             
*   R = Print by ROOM (default for individual)
*   B = Print by Booking
*   G = Print by Guest 
*   P = Print by PAX (Adult and child)
*/  

    IF i_register_id IS NOT NULL THEN 
        print_mode := coalesce(print_mode, 'R');
    
        SELECT rg.guest_id, rg.booking_id, rg.booking_item, rg.id, cn.name, coalesce(grp.name, bk.booking_name), bk.confirm_no, rm.room_number, 
            rt."name" AS room_type, rt.code AS roomtype_code, rp.name, rg.adult, rg.child, rg.infant, 
            rg.arrival, rg.checkin_time::time, rg.departure, rg.departure_time, rg.nights, rg.arrive_by, rg.arrive_from, 
            rg.depart_by, rg.depart_to, rg.payment_method, rg.voucher_no 
        FROM registration rg LEFT JOIN booking b ON rg.booking_id = b.id
            left join channel cn on cn.id = rg.channel_id
            left join group_info grp on grp.id = rg.group_id
            LEFT JOIN booking bk ON bk.id = rg.booking_id 
            left join rate_plan rp on rp.id = rg.rateplan_id 
            left join room rm on rm.id = rg.room_id 
            left join room_type rt on rt.id = rm.roomtype_id
        WHERE rg.id = i_register_id
        INTO _guest_id, booking_id, booking_item, register_id, channel, booking_name, confirm_number, room_number, room_type, roomtype_code, rate_plan,  
            adult, child, infant, arrival, arrival_time, departure, departure_time, nights, arrive_by, arrive_from, depart_by, depart_to, 
            payment_method, voucher_number;

        payment_voucher := (voucher_number NOTNULL AND trim(voucher_number) <> '');
        deposit_amount  := (SELECT sum(amount) FROM deposit WHERE status IN ('R','C','H'));
        _guests := array[_guest_id];
    
        IF print_mode IN ('G','P') THEN 
            _guests := _guests || ARRAY(SELECT rg.guest_id FROM register_guests rg WHERE rg.register_id = i_register_id);
        END IF;
    
        RAISE NOTICE '%', _guests::TEXT;
    
        /* Known guests */
        FOR guest_id, guest_title, guest_first_name, guest_last_name, guest_middle_name, guest_full_name, guest_sex, guest_birthdate, guest_nationality, guest_language, 
            guest_vip_level, guest_address1, guest_address2, guest_country, guest_country_of_residence, guest_telephone, guest_mobile, guest_email,
            guest_company, guest_occupation, guest_doc_type, guest_doc_number, guest_doc_expire, guest_member_code, guest_carplate_no--, guest_credit_cards
        IN SELECT g.id, t."name", g.first_name, g.last_name, g.middle_name, g.full_name, g.sex, g.birthdate, n.name, l.name, v.name, g.address1, g.address2, c.name, 
                cr.name, array_to_string(g.telephones,', '), array_to_string(g.mobiles,', '), array_to_string(g.emails,', '), g.company, oc.name, 
                g.doc_type, g.doc_number, g.doc_expire, g.member_code, g.carplate_no--, g.credit_cards
            FROM guest g 
                 left join title t on t.id = g.title_id
                 left join nationality n on n.id = g.nation_id 
                 left join "language" l on l.id = g.lang_id 
                 left join country c on c.id = g.country_id
                 left join country cr on cr.id = g.country_res_id
                 left join occupation oc on oc.id = g.occupation_id 
                 left join city ct on ct.id = g.city_id  
                 left join vip v on v.id = g.vip_id 
            WHERE g.id = ANY(_guests) 
        LOOP 
            _guest_id := guest_id;
            --adult  := iif(print_mode IN ('G','P'), 1, adult);
            --child  := iif(print_mode IN ('G','P'), 0, child);
            --infant := iif(print_mode IN ('G','P'), 0, infant);
            guest_credit_cards := ARRAY(SELECT (cr.id, cr.card_number, cr.card_type, cr.expire_month, cr.expire_year)::t_credit_card 
                                        FROM credit_card cr WHERE cr.guest_id = _guest_id AND cr.card_number IS NOT NULL);
            payment_card = fn_length(guest_credit_cards) > 0;
            RETURN NEXT;
        END LOOP; 
    
        /* Generate unknown guests */
        _unknown_guests := adult + child - fn_length(_guests);
        IF (print_mode = 'P') AND (_unknown_guests > 0) THEN 
            guest_id := NULL;  guest_title := NULL; guest_first_name := NULL; guest_last_name := NULL; guest_middle_name := NULL; 
            guest_full_name := NULL; guest_sex := NULL; guest_birthdate := NULL; guest_nationality := NULL; guest_language := NULL; 
            guest_vip_level := NULL; guest_address1 := NULL; guest_address2 := NULL; guest_country := NULL; guest_country_of_residence := NULL; 
            guest_telephone := NULL; guest_mobile := NULL; guest_email := NULL; guest_occupation := NULL; guest_doc_type := NULL; 
            guest_doc_number := NULL; guest_doc_expire := NULL; guest_member_code := NULL; guest_carplate_no := NULL; 
            guest_credit_cards := NULL;  payment_card := FALSE;
            --guest_company := NULL; 
            --adult := 1; child := 0; infant := 0; 
            WHILE (_unknown_guests > 0) LOOP
                RETURN NEXT;
                _unknown_guests := _unknown_guests -1;
            END LOOP;
        END IF;
    END IF;
END

sp_register_card

Parameters

Definition