v_room_activity

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

room_number

t_room_number

2147483647

√

null

activity_type

text

2147483647

√

null

avtivity_code

text

2147483647

√

null

room_id

int4

√

null

booking_id

int4

√

null

booking_item

int4

√

null

register_id

int4

√

null

guest_id

int4

√

null

name

citext

2147483647

√

null

start_date

date

√

null

end_date

date

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH inh AS (
         SELECT 'In-house'::text AS activity_type,
            '0'::text AS avtivity_code,
            r_1.room_id,
            r_1.booking_id,
            r_1.booking_item,
            r_1.id AS register_id,
            r_1.guest_id,
            g.full_name AS name,
            r_1.arrival AS start_date,
            r_1.departure AS end_date
           FROM (registration r_1
             LEFT JOIN guest g ON ((g.id = r_1.guest_id)))
          WHERE ((r_1.room_id IS NOT NULL) AND (r_1.status = 'I'::bpchar))
        ), activity AS (
         SELECT inh.activity_type,
            inh.avtivity_code,
            inh.room_id,
            inh.booking_id,
            inh.booking_item,
            inh.register_id,
            inh.guest_id,
            inh.name,
            inh.start_date,
            inh.end_date
           FROM inh
        UNION ALL
         SELECT 'In-house'::text AS text,
            '1'::text AS text,
            inh.room_id,
            inh.booking_id,
            inh.booking_item,
            inh.register_id,
            bg.guest_id,
            g.full_name,
            inh.start_date,
            inh.end_date
           FROM ((booking_guests bg
             JOIN inh ON ((inh.register_id = bg.register_id)))
             LEFT JOIN guest g ON ((g.id = bg.guest_id)))
        UNION ALL
         SELECT 'Block'::text AS text,
            '2'::text AS text,
            rb.room_id,
            rb.booking_id,
            rb.booking_item,
            NULL::integer AS int4,
            b.guest_id,
            g.full_name,
            bi.arrival,
            bi.departure
           FROM (((room_block rb
             LEFT JOIN booking_items bi ON ((bi.item_id = rb.booking_item)))
             LEFT JOIN booking b ON ((b.id = rb.booking_id)))
             LEFT JOIN guest g ON ((g.id = b.guest_id)))
          WHERE (NOT rb.checked_in)
        UNION ALL
         SELECT 'Out Of Order'::text AS text,
            '3'::text AS text,
            o.room_id,
            NULL::integer AS int4,
            NULL::integer AS int4,
            NULL::integer AS int4,
            NULL::integer AS int4,
            rs.name,
            o.start_date,
            o.end_date
           FROM (out_of_order o
             LEFT JOIN ooo_reason rs ON ((rs.id = o.reason_id)))
          WHERE (o.ooo_type = 'OOO'::bpchar)
        UNION ALL
         SELECT 'Out Of Service'::text AS text,
            '4'::text AS text,
            o.room_id,
            NULL::integer AS int4,
            NULL::integer AS int4,
            NULL::integer AS int4,
            NULL::integer AS int4,
            rs.name,
            o.start_date,
            o.end_date
           FROM (out_of_order o
             LEFT JOIN ooo_reason rs ON ((rs.id = o.reason_id)))
          WHERE (o.ooo_type = 'OOS'::bpchar)
        UNION ALL
         SELECT 'Out Of Inventory'::text AS text,
            '5'::text AS text,
            o.room_id,
            NULL::integer AS int4,
            NULL::integer AS int4,
            NULL::integer AS int4,
            NULL::integer AS int4,
            rs.name,
            o.start_date,
            o.end_date
           FROM (out_of_order o
             LEFT JOIN ooo_reason rs ON ((rs.id = o.reason_id)))
          WHERE (o.ooo_type = 'OOI'::bpchar)
        )
 SELECT r.room_number,
    a.activity_type,
    a.avtivity_code,
    a.room_id,
    a.booking_id,
    a.booking_item,
    a.register_id,
    a.guest_id,
    a.name,
    a.start_date,
    a.end_date
   FROM (activity a
     LEFT JOIN room r ON ((r.id = a.room_id)))
  ORDER BY r.room_number, a.avtivity_code, a.start_date DESC;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views