29 lines
1.3 KiB
SQL
29 lines
1.3 KiB
SQL
SELECT row_number() OVER (ORDER BY linex.line_id) AS id, current_timestamp AS create_date, room,
|
|
main_guest, arrival_date, departure_date, hotel_booking.reference, hotel_booking.state
|
|
FROM
|
|
(SELECT book_line.line_id as line_id, book_line.booking, room.name AS room, book_line.main_guest, book_line.arrival_date,
|
|
book_line.departure_date FROM
|
|
(SELECT line.idx AS line_id, line.room, booking, party.name AS main_guest, line.arrival_date,
|
|
line.departure_date
|
|
FROM
|
|
(SELECT id as idx, booking, room, main_guest, arrival_date, departure_date FROM hotel_booking_lines
|
|
WHERE arrival_date>='2013-07-01' AND departure_date<='2013-12-31'
|
|
ORDER BY room) AS line
|
|
INNER JOIN
|
|
(SELECT id, name FROM party_party) AS party
|
|
ON (party.id=line.main_guest)) AS book_line
|
|
JOIN
|
|
(SELECT room.id AS id, pid, pro.name FROM
|
|
(SELECT tid, pid, t.name
|
|
FROM
|
|
(SELECT p.id AS pid, p.template FROM product_product AS p) AS p
|
|
INNER JOIN
|
|
(SELECT t.id AS tid, t.name AS name FROM product_template AS t) AS t
|
|
ON (tid=p.template)) AS pro
|
|
INNER JOIN
|
|
(SELECT room.id, room.product FROM hotel_room AS room) AS room
|
|
ON (room.product=pro.pid) ORDER BY pro.name) AS room
|
|
ON (room.id=book_line.room)) AS linex
|
|
JOIN
|
|
(SELECT id AS bid, reference, state FROM hotel_booking) AS hotel_booking
|
|
ON (linex.booking=hotel_booking.bid) ORDER BY booking |