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