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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
|
SELECT DISTINCT TOP 100 PERCENT
(ISNULL([7284V4].dbo.RESA.RES_CLIID, '') + [7284V4].dbo.RESA.RES_ID + [7284V4].dbo.RESA.RES_NOM + ISNULL([7284V4].dbo.RESA.RES_NOM2, '') + ISNULL([7284V4].dbo.RESA.RES_EMAIL, '')) AS resa_num_client_pms,
([7284V4].dbo.RESA.RES_ID + '_7284V4') AS resa_num_pms,
convert(VarChar(10), ISNULL([7284V4].dbo.FACTURE.FAC_DEBUT, [7284V4].dbo.RESA.RES_DATEARR), 120) AS resa_date_arrivee,
convert(VarChar(10), ISNULL([7284V4].dbo.FACTURE.FAC_FIN, [7284V4].dbo.RESA.RES_DATEDEP), 120) AS resa_date_depart,
convert(VarChar(10), [7284V4].dbo.RESA.RES_DATEPRISE, 120) AS resa_date,
convert(VarChar(20), [7284V4].dbo.FACDETAIL.FD_TIME, 120) AS date_update_resa,
[7284V4].dbo.CIVILITE.CIV_SHORT AS civilite_client,
[7284V4].dbo.FACTURE.FAC_CLINOM AS nom_client,
[7284V4].dbo.RESA.RES_NOM2 AS prenom_client,
[7284V4].dbo.RESA.RES_NOMPRESC AS nom_prescripteur,
[7284V4].dbo.RESA.RES_NOMSOC AS nom_societe,
[7284V4].dbo.RESA.RES_EMAIL AS client_email,
[7284V4].dbo.RESA.RES_CANCELED AS resa_canceled,
[7284V4].dbo.RESA.RES_NOSHOW AS resa_noshow,
[7284V4].dbo.RESA.RES_WAITING AS resa_waiting,
[7284V4].dbo.RESA.RES_ATTENDUE AS resa_attendue,
[7284V4].dbo.RESA.RES_ARRIVEE AS resa_arrivee,
[7284V4].dbo.RESA.RES_CLOSED AS resa_closed,
[7284V4].dbo.RESA.RES_DAYUSE AS resa_dayuse,
[7284V4].dbo.RESA.RES_PAYSID AS pays_client,
[7284V4].dbo.RESA.RES_NATID AS code_langue_client,
'TOTO' AS resa_hotel,
[7284V4].dbo.SOURCE.SOU_DESC AS resa_source,
[7284V4].dbo.FACDETAIL.FD_DATE AS date_nuitee,
[7284V4].dbo.FACDETAIL.FD_PROID AS rate_code,
[7284V4].dbo.FACDETAIL.FD_DESC AS rate_description,
[7284V4].dbo.FACTURE.FAC_CHID AS room,
[7284V4].dbo.FACTURE.FAC_NBADULTE AS nb_adults,
[7284V4].dbo.FACTURE.FAC_NBENFANT AS nb_children,
CAST(([7284V4].dbo.FACDETAIL.FD_PRIX) AS decimal(10,2)) AS resa_montant,
[7284V4].dbo.CANAL.CAN_DESC AS canal,
[7284V4].dbo.RESDETAIL.RD_DESC AS info_chambre,
'7284V4' AS discriminant_r
FROM
[7284V4].dbo.FACDETAIL
LEFT JOIN [7284V4].dbo.FACTURE ON [7284V4].dbo.FACTURE.FAC_ID = [7284V4].dbo.FACDETAIL.FD_FACID
LEFT JOIN [7284V4].dbo.RESDETAIL ON ([7284V4].dbo.FACTURE.FAC_RESID = [7284V4].dbo.RESDETAIL.RD_RESID)
LEFT JOIN [7284V4].dbo.RESA ON ([7284V4].dbo.RESDETAIL.RD_RESID = [7284V4].dbo.RESA.RES_ID)
LEFT JOIN [7284V4].dbo.CIVILITE ON [7284V4].dbo.RESA.RES_CIVID = [7284V4].dbo.CIVILITE.CIV_ID
LEFT JOIN [7284V4].dbo.SOURCE ON [7284V4].dbo.RESA.RES_SOUID = [7284V4].dbo.SOURCE.SOU_ID
LEFT JOIN [7284V4].dbo.CANAL ON [7284V4].dbo.CANAL.CAN_ID = [7284V4].dbo.RESA.RES_CANID
WHERE
1 = 1
AND [7284V4].dbo.FACDETAIL.FD_DATE >= '2011-01-01'
AND [7284V4].dbo.FACTURE.FAC_CHID IS NOT NULL
AND [7284V4].dbo.RESDETAIL.RD_PRIX > 0
AND [7284V4].dbo.FACTURE.FAC_MONTANT > 0
AND [7284V4].dbo.CANAL.CAN_DESC <> ''
AND [7284V4].dbo.FACDETAIL.FD_PROID IN ('BBDIS1','BBDIS2','BBDIS3','BBDIS4','BBDOUB','BBQUAD','BBSING','BBSOC1','BBSOC2','BBTRIP','CHAMB','CHDOUB','CHQUAD','CHSING','CHSUP','CHTRIP','CHTX RED')
UNION
SELECT DISTINCT TOP 100 PERCENT
(ISNULL([7284V4].dbo.RESA.RES_CLIID, '') + [7284V4].dbo.RESA.RES_ID + [7284V4].dbo.RESA.RES_NOM + ISNULL([7284V4].dbo.RESA.RES_NOM2, '') + ISNULL([7284V4].dbo.RESA.RES_EMAIL, '')) AS resa_num_client_pms,
([7284V4].dbo.RESA.RES_ID + '_7284V4') AS resa_num_pms,
convert(VarChar(10), ISNULL([7284V4].dbo.FACTURE2.FAC_DEBUT, [7284V4].dbo.RESA.RES_DATEARR), 120) AS resa_date_arrivee,
convert(VarChar(10), ISNULL([7284V4].dbo.FACTURE2.FAC_FIN, [7284V4].dbo.RESA.RES_DATEDEP), 120) AS resa_date_depart,
convert(VarChar(10), [7284V4].dbo.RESA.RES_DATEPRISE, 120) AS resa_date,
convert(VarChar(20), [7284V4].dbo.FACDETAIL2.FD_TIME, 120) AS date_update_resa,
[7284V4].dbo.CIVILITE.CIV_SHORT AS civilite_client,
[7284V4].dbo.FACTURE2.FAC_CLINOM AS nom_client,
[7284V4].dbo.RESA.RES_NOM2 AS prenom_client,
[7284V4].dbo.RESA.RES_NOMPRESC AS nom_prescripteur,
[7284V4].dbo.RESA.RES_NOMSOC AS nom_societe,
[7284V4].dbo.RESA.RES_EMAIL AS client_email,
[7284V4].dbo.RESA.RES_CANCELED AS resa_canceled,
[7284V4].dbo.RESA.RES_NOSHOW AS resa_noshow,
[7284V4].dbo.RESA.RES_WAITING AS resa_waiting,
[7284V4].dbo.RESA.RES_ATTENDUE AS resa_attendue,
[7284V4].dbo.RESA.RES_ARRIVEE AS resa_arrivee,
[7284V4].dbo.RESA.RES_CLOSED AS resa_closed,
[7284V4].dbo.RESA.RES_DAYUSE AS resa_dayuse,
[7284V4].dbo.RESA.RES_PAYSID AS pays_client,
[7284V4].dbo.RESA.RES_NATID AS code_langue_client,
'TOTO' AS resa_hotel,
[7284V4].dbo.SOURCE.SOU_DESC AS resa_source,
[7284V4].dbo.FACDETAIL2.FD_DATE AS date_nuitee,
[7284V4].dbo.FACDETAIL2.FD_PROID AS rate_code,
[7284V4].dbo.FACDETAIL2.FD_DESC AS rate_description,
[7284V4].dbo.FACTURE2.FAC_CHID AS room,
[7284V4].dbo.FACTURE2.FAC_NBADULTE AS nb_adults,
[7284V4].dbo.FACTURE2.FAC_NBENFANT AS nb_children,
CAST(([7284V4].dbo.FACDETAIL2.FD_PRIX) AS decimal(10,2)) AS resa_montant,
[7284V4].dbo.CANAL.CAN_DESC AS canal,
[7284V4].dbo.RESDETAIL.RD_DESC AS info_chambre,
'7284V4' AS discriminant_r
FROM
[7284V4].dbo.FACDETAIL2
LEFT JOIN [7284V4].dbo.FACTURE2 ON [7284V4].dbo.FACTURE2.FAC_ID = [7284V4].dbo.FACDETAIL2.FD_FACID
LEFT JOIN [7284V4].dbo.RESDETAIL ON ([7284V4].dbo.FACTURE2.FAC_RESID = [7284V4].dbo.RESDETAIL.RD_RESID)
LEFT JOIN [7284V4].dbo.RESA ON ([7284V4].dbo.RESDETAIL.RD_RESID = [7284V4].dbo.RESA.RES_ID)
LEFT JOIN [7284V4].dbo.CIVILITE ON [7284V4].dbo.RESA.RES_CIVID = [7284V4].dbo.CIVILITE.CIV_ID
LEFT JOIN [7284V4].dbo.SOURCE ON [7284V4].dbo.RESA.RES_SOUID = [7284V4].dbo.SOURCE.SOU_ID
LEFT JOIN [7284V4].dbo.CANAL ON [7284V4].dbo.CANAL.CAN_ID = [7284V4].dbo.RESA.RES_CANID
WHERE
1 = 1
AND [7284V4].dbo.FACDETAIL2.FD_DATE >= '2011-01-01'
AND [7284V4].dbo.FACTURE2.FAC_CHID IS NOT NULL
AND [7284V4].dbo.RESDETAIL.RD_PRIX > 0
AND [7284V4].dbo.FACTURE2.FAC_MONTANT > 0
AND [7284V4].dbo.CANAL.CAN_DESC <> ''
AND [7284V4].dbo.FACDETAIL2.FD_PROID IN ('BBDIS1','BBDIS2','BBDIS3','BBDIS4','BBDOUB','BBQUAD','BBSING','BBSOC1','BBSOC2','BBTRIP','CHAMB','CHDOUB','CHQUAD','CHSING','CHSUP','CHTRIP','CHTX RED')
UNION
SELECT
(ISNULL([7284V4].dbo.RESA.RES_CLIID, '') + [7284V4].dbo.RESA.RES_ID + [7284V4].dbo.RESA.RES_NOM + ISNULL([7284V4].dbo.RESA.RES_NOM2, '') + ISNULL([7284V4].dbo.RESA.RES_EMAIL, '')) AS resa_num_client_pms,
([7284V4].dbo.RESA.RES_ID + '_7284V4') AS resa_num_pms,
convert(VarChar(10), [7284V4].dbo.RESA.RES_DATEARR, 120) AS resa_date_arrivee,
convert(VarChar(10), [7284V4].dbo.RESA.RES_DATEDEP, 120) AS resa_date_depart,
convert(VarChar(10), [7284V4].dbo.RESA.RES_DATEPRISE, 120) AS resa_date,
convert(VarChar(20), [7284V4].dbo.RESA.RES_LASTEDIT, 120) AS date_update_resa,
[7284V4].dbo.CIVILITE.CIV_SHORT AS civilite_client,
[7284V4].dbo.RESA.RES_NOM AS nom_client,
[7284V4].dbo.RESA.RES_NOM2 AS prenom_client,
[7284V4].dbo.RESA.RES_NOMPRESC AS nom_prescripteur,
[7284V4].dbo.RESA.RES_NOMSOC AS nom_societe,
[7284V4].dbo.RESA.RES_EMAIL AS client_email,
[7284V4].dbo.RESA.RES_CANCELED AS resa_canceled,
[7284V4].dbo.RESA.RES_NOSHOW AS resa_noshow,
[7284V4].dbo.RESA.RES_WAITING AS resa_waiting,
[7284V4].dbo.RESA.RES_ATTENDUE AS resa_attendue,
[7284V4].dbo.RESA.RES_ARRIVEE AS resa_arrivee,
[7284V4].dbo.RESA.RES_CLOSED AS resa_closed,
[7284V4].dbo.RESA.RES_DAYUSE AS resa_dayuse,
[7284V4].dbo.RESA.RES_PAYSID AS pays_client,
[7284V4].dbo.RESA.RES_NATID AS code_langue_client,
'TOTO' AS resa_hotel,
[7284V4].dbo.SOURCE.SOU_DESC AS resa_source,
convert(VarChar(10), [7284V4].dbo.RESA.RES_DATEARR, 120) AS date_nuitee,
[7284V4].dbo.RESA.RES_OBS AS rate_code,
'Client a venir' AS rate_description,
'XXXX' AS room,
[7284V4].dbo.RESA.RES_ADULTE AS nb_adults,
[7284V4].dbo.RESA.RES_ENFANT AS nb_children,
0 AS resa_montant,
[7284V4].dbo.CANAL.CAN_DESC AS canal,
'En attente' AS info_chambre,
'7284V4' AS discriminant_r
FROM [7284V4].dbo.RESA
LEFT JOIN [7284V4].dbo.RESDETAIL ON ([7284V4].dbo.RESA.RES_RESID = [7284V4].dbo.RESDETAIL.RD_RESID)
LEFT JOIN [7284V4].dbo.CIVILITE ON [7284V4].dbo.RESA.RES_CIVID = [7284V4].dbo.CIVILITE.CIV_ID
LEFT JOIN [7284V4].dbo.SOURCE ON [7284V4].dbo.RESA.RES_SOUID = [7284V4].dbo.SOURCE.SOU_ID
LEFT JOIN [7284V4].dbo.CANAL ON [7284V4].dbo.CANAL.CAN_ID = [7284V4].dbo.RESA.RES_CANID
WHERE
1 = 1
AND [7284V4].dbo.RESA.RES_DATEARR > GETDATE()
AND [7284V4].dbo.CANAL.CAN_DESC <> ''
AND [7284V4].dbo.RESA.RES_DATEARR = '2014-03-07' |
Partager