1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
|
SELECT DISTINCT
(NA.TITLE || ' ' || RN.SGUEST_FIRSTNAME || ' '|| RN.SGUEST_NAME) AS CLIENT_NOM,
DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE','F') AS client_pays,
CASE
WHEN RN.RESORT = 'OPERA'
THEN 'Best Western Premier Opera Diamond'
WHEN RN.RESORT = 'BATIGN'
THEN 'Opera Batignolles Hotel'
WHEN RN.RESORT = 'PPN'
THEN 'Comfort et Quality Hotel Centre del Mon'
END CLIENT_HOTEL,
TO_CHAR (RN.TRUNC_ACTUAL_CHECK_IN_DATE,'DD/MM/YYYY') AS HOTEL_DATE_ENTREE,
TO_CHAR (RN.TRUNC_ACTUAL_CHECK_OUT_DATE,'DD/MM/YYYY') AS HOTEL_DATE_SORTIE,
REPLACE(LOWER (PH.PHONE_NUMBER),'*') AS client_email,
RO.DESCRIPTION AS ORIGINE_RESERVATION,
TRUNC (SYSDATE - TO_DATE (RN.trunc_actual_check_out_date)) AS NB_JOURS
FROM OPERA.RESERVATION_NAME RN
INNER JOIN OPERA.NAME NA ON NA.NAME_ID =RN.NAME_ID
INNER JOIN OPERA.NAME_PHONE PH ON PH.NAME_ID=RN.NAME_ID
LEFT OUTER JOIN OPERA.RESERVATION_SUMMARY RS ON RS.EVENT_ID=RN.RESV_NAME_ID
LEFT OUTER JOIN OPERA.RESORT_ORIGINS_OF_BOOKING RO ON RO.RESORT=RS.RESORT AND RO.SOURCE_CODE =RS.SOURCE_CODE
WHERE 1=1
AND RN.RESV_STATUS = 'CHECKED OUT' -- dans tous les cas ils sont CHECKED OUT
AND
(-- premier cas on prend tous ceux qui n'ont pas de * dans leur email (sauf PPN)
(RN.RESORT != 'PPN'
AND regexp_like (LOWER(PH.PHONE_NUMBER), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
)
OR-- Deuxieme cas on prend ceux qui ont une * dans leur mail et qui sont
(RN.RESORT = 'PPN'
AND regexp_like (LOWER(PH.PHONE_NUMBER), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_\*]*@[a-zA-Z0-9\-_\*]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
)
)
AND TRUNC( SYSDATE - TO_DATE(RN.TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0
AND RN.TRUNC_ACTUAL_CHECK_OUT_DATE >= TO_DATE('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC |
Partager