SELECT
r.id AS 'reservationId',
r.project_id AS projectId,
d.id AS 'departureId',
t.code AS 'trip',
t_t.one_letter_code,
t.locale_id AS 'localeId',
d.departure_status_id AS 'status',
r.reservation_confirmation_date AS 'dateConfirm',
d.start_date AS 'dateDeparture',
(SELECT bank_slip_date FROM payment p WHERE p.invoice_number_id = r.invoice_number_id order by bank_slip_date asc limit 1) AS down_payment_date,
(SELECT COUNT(*) FROM client_reservation_relation crr WHERE crr. reservation_id = r.id and ((crr.is_storned = 0 AND crr.is_cancelled != 1) OR crr.is_storned = 1)) AS PAX,
t.default_max_no_of_pax AS 'MAX',
t.default_min_no_of_pax AS 'MIN',
r.cached_estimated_margin AS 'marginEstimated',
r.cached_estimated_margin_date AS 'dateDPayment',
r.cached_real_margin AS 'marginReal',
r.old_id AS 'reservationIdOld'




FROM trip t

LEFT JOIN departure d ON d.trip_id = t.id
LEFT JOIN trip_type tt ON tt.id = t.trip_type_id
LEFT JOIN departure_order do ON do.departure_id = d.id
LEFT JOIN client_reservation_relation crr ON crr.id = do.client_reservation_relation_id
LEFT JOIN reservation r ON r.id = crr.reservation_id
LEFT JOIN go_user gu ON gu.id = t.current_contact_person_go_user_id
LEFT JOIN trip_type t_t ON t_t.id = t.trip_type_id

WHERE r.created_at > '2014-01-01' AND
t.locale_id IN ('de_DE', 'fr_FR') AND
tt.trip_meta_type_id IN (1) AND
d.departure_status_id IN (2, 3, 4, 5)

GROUP BY r.id, d.id, t.code, t.locale_id
ORDER BY d.id DESC