PROCEDURE PRIVÉE ConstruitReq()
sReq est une chaîne
sPartReq est une chaîne
nRooms est un entier = stParamDevis.tabRoom..Occurrence
//bOnRequest est un booléen = stParamDevis.bEnableOnRequest
sReq = [
/* REQUETE */
SELECT
idHotel,
qry_Fin.idChannelManager,
ROUND(globalschema.VerifSupplementTest(idHotel,@CodeClient,@DateDeb,@DateFin,TotalPrice,@Application),2) AS TotalPrice,
-- GG MODIF BDD
-- TotalPrice,
TotalPrice-TotalPriceBase AS TotalPromo,
isPromo,
f.nom,
f.latitude,
f.longitude,
f.CDService,
CONCAT(f.rue,' ',f.suite,' ',f.CP,' ',f.ville) AS adresse,
IF(INSTR(f.typologie,'l')=0, 0, 1) AS bCoeur,
#p.Notes_Enfant,
IF( (@nbChild1 != 0 AND mts1.bDouble = 1 )
]
POUR i=2 _A_ nRooms
sPartReq = [
OR (@nbChild%1 != 0 AND mts%1.bDouble = 1 )
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
, p.Notes_Enfant, "" ) AS Notes_Enfant,
i.Image,
d.Description,
f.COCAT as nEtoiles,
f.Region,
f.Ville,
f.Nom_qu,
f.AccesWeb,
f.idActivite_Touristique,
f.CP,
cmc.idChannel_Manager,
f.TYPOLOGIE,
IF(cgmc.IDCLIENT_GESTION_MAJ_CONTRAT IS NULL, 0, 1) AS bPrefere,
TotalPrice,
idRoomCategory_1,
IF(f.CDService = 'A', CONCAT(@NbPaxDem1+@nbChild1,' PAX ', IF(@bTwin1, 'TWIN', '')),
CASE
WHEN mts1.bDouble=1 THEN 'DOUBLE'
WHEN mts1.bSingle=1 THEN 'SINGLE'
WHEN mts1.bTwin=1 THEN 'TWIN'
WHEN mts1.bTriple=1 THEN 'TRIPLE'
WHEN mts1.bQuad=1 THEN 'QUAD'
ELSE '_'
END
) AS idRoomService_1,
-- AJOUT CALCUL SUPPLEMENT --
-- AdultPrice_1,
-- AdultPrice_1-AdultPriceBase_1 AS PromoPrice_1,
ROUND(globalschema.VerifSupplementTest(idHotel,@CodeClient,@DateDeb,@DateFin,AdultPrice_1,@Application),2) AS AdultPrice_1,
AdultPrice_1-AdultPriceBase_1 AS PromoPrice_1,
LibellePromo_1,
Inv_1,
Designation_1,
Regime_1
]
POUR i=2 _A_ nRooms
sPartReq = [
,
idRoomCategory_%1,
IF(f.CDService = 'A', CONCAT(@NbPaxDem%1+@nbChild%1,' PAX ', IF(@bTwin%1, 'TWIN', '')),
CASE
WHEN mts%1.bDouble=1 THEN 'DOUBLE'
WHEN mts%1.bSingle=1 THEN 'SINGLE'
WHEN mts%1.bTwin=1 THEN 'TWIN'
WHEN mts%1.bTriple=1 THEN 'TRIPLE'
WHEN mts%1.bQuad=1 THEN 'QUAD'
ELSE '_'
END
) AS idRoomService_%1,
-- AJOUT CALCUL SUPPLEMENT --
-- AdultPrice_%1,
-- AdultPrice_%1-AdultPriceBase_%1 AS PromoPrice_%1,
ROUND(globalschema.VerifSupplementTest(idHotel,@CodeClient,@DateDeb,@DateFin,AdultPrice_%1,@Application),2) AS AdultPrice_%1,
AdultPrice_%1-AdultPriceBase_%1 AS PromoPrice_%1,
LibellePromo_%1,
Inv_%1,
Designation_%1,
Regime_%1
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
FROM(
SELECT
idHotel_1 AS idHotel,
idChannelManager_1 AS idChannelManager,
ROUND(TotalPrice,2) AS TotalPrice,
ROUND(TotalPriceBase,2) AS TotalPriceBase,
isPromo,
idRoomCategory_1,
idRoomService_1,
ROUND(AdultPrice_1,2) AS AdultPrice_1,
ROUND(AdultPriceBase_1,2) AS AdultPriceBase_1,
LibellePromo_1,
Inv_1,
Designation_1,
Regime_1
]
POUR i=2 _A_ nRooms
sPartReq = [
,
idRoomCategory_%1,
idRoomService_%1,
ROUND(AdultPrice_%1,2) AS AdultPrice_%1,
ROUND(AdultPriceBase_%1,2) AS AdultPriceBase_%1,
LibellePromo_%1,
Inv_%1,
Designation_%1,
Regime_%1
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
FROM
(
SELECT
qry1.*,
AdultPrice_1
]
POUR i=2 _A_ nRooms
sPartReq = [
+AdultPrice_%1
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
AS TotalPrice,
AdultPriceBase_1
]
POUR i=2 _A_ nRooms
sPartReq = [
+AdultPriceBase_%1
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
AS TotalPriceBase
FROM
(
SELECT
idRate_1,
idHotel_1,
idChannelManager_1,
IF(COUNT(DISTINCT IdPromo_1)
]
POUR i=2 _A_ nRooms
sPartReq = [
+COUNT(DISTINCT IdPromo_%1)
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
>0 ,1, 0) AS isPromo,
idRoomCategory_1,
idRoomGroup_1,
idRoomService_1,
IF(
MAX(cnt_promo)>1 OR COUNT(DISTINCT IdPromo_1)>1,
ROUND(globalschema.promoCumulMulti(idHotel_1,idChannelManager_1,idRoomCategory_1,idRoomGroup_1,idRoomService_1,idRate_1,duree,
(MAX(cnt_promo)>1 OR COUNT(DISTINCT IdPromo_1)>1)),2),
SUM(AdultPrice_1)) AS AdultPrice_1,
SUM(AdultPriceBase_1) AS AdultPriceBase_1,
-- SUPRESSION ARRONDI --
-- ROUND(SUM(AdultPrice_1),0)) AS AdultPrice_1,
-- ROUND(SUM(AdultPriceBase_1),0) AS AdultPriceBase_1,
IF(
MAX(cnt_promo)>1 OR COUNT(DISTINCT IdPromo_1)>1,
@LibellePromo ,
MAX(LibellePromo_1))
AS LibellePromo_1,
MIN(Inv_1) AS Inv_1,
COUNT(DISTINCT IdPromo_1) AS nbPromo_1,
Designation_1,
Regime_1,
]
POUR i=2 _A_ nRooms
sPartReq = [
idHotel_%1,
idChannelManager_%1,
idRoomCategory_%1,
idRoomGroup_%1,
idRoomService_%1,
IF(
MAX(cnt_promo)>1 OR COUNT(DISTINCT IdPromo_%1)>1,
ROUND(globalschema.promoCumulMulti(idHotel_1,idChannelManager_1,idRoomCategory_%1,idRoomGroup_%1,idRoomService_%1,idRate_1,duree,
(MAX(cnt_promo)>1 OR COUNT(DISTINCT IdPromo_1)>1)),2),
SUM(AdultPrice_%1)) AS AdultPrice_%1,
SUM(AdultPriceBase_%1) AS AdultPriceBase_%1,
-- SUPPRESSION ARRONDI --
-- ROUND(SUM(AdultPrice_%1),0)) AS AdultPrice_%1,
-- ROUND(SUM(AdultPriceBase_%1),0) AS AdultPriceBase_%1,
IF(
MAX(cnt_promo)>1 OR COUNT(DISTINCT IdPromo_%1)>1,
@LibellePromo ,
MAX(LibellePromo_%1))
AS LibellePromo_%1,
MIN(Inv_%1) AS Inv_%1,
COUNT(DISTINCT IdPromo_%1) AS nbPromo_%1,
Designation_%1,
Regime_%1,
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
MAX(cnt_promo) AS nbPromo_Jour,
IF(duree=0, COUNT(*), duree) AS cnt
FROM(
/**/
SELECT
i1.Jour AS Jour_1,
i1.idRate AS idRate_1,
i1.duree,
ro1.idHotel AS idHotel_1,
ro1.idChannelManager AS idChannelManager_1,
ro1.idRoomCategory AS idRoomCategory_1,
ro1.idRoomGroup AS idRoomGroup_1,
ro1.idRoomService AS idRoomService_1,
CASE p1.TypePromo
WHEN 1 THEN i1.AdultPrice*(1-i1.commission)*(1+i1.marge)*( 1-p1.ValeurReduc )
WHEN 2 THEN i1.AdultPrice*(1-i1.commission)*(1+i1.marge)*( 1-p1.ValeurReduc )
WHEN 3 THEN i1.AdultPrice*(1-i1.commission)*(1+i1.marge)-p1.ValeurReduc
ELSE i1.AdultPrice*(1-i1.commission)*(1+i1.marge)
END AS AdultPrice_1,
i1.AdultPrice*(1-i1.commission)*(1+i1.marge) AS AdultPriceBase_1,
i1.Inv AS Inv_1,
p1.IdPromo AS IdPromo_1,
p1.libelle AS LibellePromo_1,
ro1.Designation as Designation_1,
i1.Regime AS Regime_1,
]
POUR i=2 _A_ nRooms
sPartReq = [
ro%1.idHotel AS idHotel_%1,
ro%1.idChannelManager AS idChannelManager_%1,
ro%1.idRoomCategory AS idRoomCategory_%1,
ro%1.idRoomGroup AS idRoomGroup_%1,
ro%1.idRoomService AS idRoomService_%1,
CASE p%1.TypePromo
WHEN 1 THEN i%1.AdultPrice*(1-i%1.commission)*(1+i%1.marge)*( 1-p%1.ValeurReduc )
WHEN 2 THEN i%1.AdultPrice*(1-i%1.commission)*(1+i%1.marge)*( 1-p%1.ValeurReduc )
WHEN 3 THEN i%1.AdultPrice*(1-i%1.commission)*(1+i%1.marge)-p%1.ValeurReduc
ELSE i%1.AdultPrice*(1-i%1.commission)*(1+i%1.marge)
END AS AdultPrice_%1,
i%1.AdultPrice*(1-i%1.commission)*(1+i%1.marge) AS AdultPriceBase_%1,
i%1.Inv AS Inv_%1,
p%1.IdPromo AS IdPromo_%1,
p%1.libelle AS LibellePromo_%1,
ro%1.Designation as Designation_%1,
i%1.Regime AS Regime_%1,
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
COUNT(*) AS cnt_promo
FROM globalschema.rooms ro1
JOIN globalschema.inventories i1
ON(i1.idHotel = ro1.idHotel AND i1.idChannelManager = ro1.idChannelManager AND i1.idRoomCategory = ro1.idRoomCategory
AND i1.idRoomGroup = ro1.idRoomGroup AND i1.idRoomService = ro1.idRoomService)
JOIN globalschema.rates ra1
ON(ra1.idRate = i1.idRate)
LEFT JOIN globalschema.Promos p1
ON(
/* new */
p1.Jour = i1.Jour
AND p1.CDFourniss = i1.idHotel
AND p1.CDContrat = @CodeContrat
AND (p1.NiveauTarifaire = i1.idRoomCategory OR p1.NiveauTarifaire = 0)
AND (p1.Service = i1.idRoomService OR p1.Service = 0)
AND (p1.Pays = 0 OR p1.Pays = @CodePays)
AND (p1.Client = 0 OR p1.Client = @CodeClient)
AND i1.idChannelManager IN ('RT', 'GE')
AND globalschema.verifPromo(p1.IdPromo,p1.Jour,p1.CDFourniss,p1.CDContrat,p1.DateLimite,p1.NtsMin,p1.NtsMax,p1.bCumul,p1.bTjsApplique,p1.ValeurReduc,p1.Pays,p1.Client,p1.NiveauTarifaire,p1.Service,
p1.binJourArrivee,p1.binJourValidite,p1.nbJoursLimite,p1.bLastNight,p1.bCumul_In,p1.nStay,p1.nPaid,p1.libelle,p1.Ordre,p1.Applicabilite,p1.bPromoLibelle,p1.bForfait,p1.bAchat,p1.bVente,p1.TypePromo,p1.DateDebPromo,p1.DateFinPromo)
)
]
POUR i=2 _A_ nRooms
sPartReq = [
JOIN globalschema.rooms ro%1
ON(ro1.idHotel = ro%1.idHotel)
JOIN globalschema.inventories i%1
ON(i%1.idHotel = ro%1.idHotel AND i%1.idChannelManager = ro%1.idChannelManager AND i%1.idRoomCategory = ro%1.idRoomCategory
AND i%1.idRoomGroup = ro%1.idRoomGroup AND i%1.idRoomService = ro%1.idRoomService)
JOIN globalschema.rates ra%1
ON(ra%1.idRate = i%1.idRate)
LEFT JOIN globalschema.Promos p%1
ON(p%1.Jour = i%1.Jour AND p%1.CDFourniss = i%1.idHotel
AND p%1.CDContrat = @CodeContrat
AND (p%1.NiveauTarifaire = i%1.idRoomCategory OR p%1.NiveauTarifaire = 0)
AND (p%1.Service = i%1.idRoomService OR p%1.Service = 0)
AND i%1.idChannelManager IN ('RT', 'GE')
AND (p%1.Pays = 0 OR p%1.Pays = @CodePays)
AND (p%1.Client = 0 OR p%1.Client = @CodeClient)
AND globalschema.verifPromo(p%1.IdPromo,p%1.Jour,p%1.CDFourniss,p%1.CDContrat,p%1.DateLimite,p%1.NtsMin,p%1.NtsMax,p%1.bCumul,p%1.bTjsApplique,p%1.ValeurReduc,p%1.Pays,p%1.Client,p%1.NiveauTarifaire,p%1.Service,
p%1.binJourArrivee,p%1.binJourValidite,p%1.nbJoursLimite,p%1.bLastNight,p%1.bCumul_In,p%1.nStay,p%1.nPaid,p%1.libelle,p%1.Ordre,p%1.Applicabilite,p%1.bPromoLibelle,p%1.bForfait,p%1.bAchat,p%1.bVente,p%1.TypePromo,p%1.DateDebPromo,p%1.DateFinPromo)
)
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
SI PAS EnModeWeb() ALORS
sReq += [
JOIN globalschema.fourniss f
ON(ro1.idHotel = f.cdfourniss)
WHERE 1
-- AJOUT CONTROLE SA --
AND CASE @iTypeAllot
-- ALLOTEMENT PUBLIC --
WHEN 1 THEN (ra1.CodeRate = 0)
-- ALLOTEMENT PUBLIC + SA --
WHEN 2 THEN (ra1.CodeRate IN (0,@CodeClient) AND IF(ra1.CodeRate <> 0,ra1.CodeRate = @CodeClient,1) )
-- SA UNIQUEMENT --
WHEN 3 THEN (ra1.CodeRate = @CodeClient )
WHEN 4 THEN (ra1.CodeRate IN (0,@CodeClient))
ELSE 0
END
AND ( IF(@bHotel, f.CDService = 'H', 0) OR IF(@bAppart, f.CDService = 'A', 0) )
]
SINON
sReq += [
JOIN globalschema.fourniss f
ON(ro1.idHotel = f.cdfourniss)
WHERE 1
-- AJOUT CONTROLE SA --
AND CASE @iTypeAllot
-- ALLOTEMENT PUBLIC --
WHEN 1 THEN (i1.Inv > 0 AND ra1.CodeRate = 0)
-- ALLOTEMENT PUBLIC + SA --
WHEN 2 THEN (ra1.CodeRate IN (0,@CodeClient) AND IF(ra1.CodeRate <> 0,ra1.CodeRate = @CodeClient,1) AND i1.Inv > 0)
-- SA UNIQUEMENT --
WHEN 3 THEN (ra1.CodeRate = @CodeClient AND i1.Inv > 0)
ELSE 0
END
AND ( IF(@bHotel, f.CDService = 'H', 0) OR IF(@bAppart, f.CDService = 'A', 0) )
]
FIN
sReq += [
AND f.region LIKE @region
AND f.ville LIKE @ville
AND f.nom_qu LIKE @quartier
AND f.CP LIKE @CodePostal
AND f.CDFourniss LIKE @idHotel
AND f.nom LIKE @nomHotel
AND f.COCAT LIKE @nbEtoile
AND IF(@RateName = 'WEB',f.ACCESWEB = 1, 1)
AND i1.Jour BETWEEN @DateDeb AND @DateFin
AND IF(i1.duree=0, 1, i1.duree = @duree AND i1.jour = @DateDeb)
AND ro1.PaxMax >= @NbPaxDem1+IF(@nbChild1>0, IF(@ageChild11 BETWEEN ro1.AgeMin AND ro1.AgeMax, 0, 1), 0)+IF(@nbChild1>1, IF(@ageChild12 BETWEEN ro1.AgeMin AND ro1.AgeMax, 0, 1), 0)
AND ro1.NbChild+ro1.PaxMax >= @nbChild1+@NbPaxDem1
]
SI PAS stParamDevis.bRechercheVide ALORS
sReq += [
AND i1.Inv > 0
]
FIN
sReq += [
AND IF(ro1.PaxMax = 2, ro1.bTwin = @bTwin1 OR f.CDService = 'A', 1)
AND (ra1.RateName = @RateName OR ra1.idChannelmanager != 'RT')
AND ra1.CodeContrat = @CodeContrat
AND ra1.MinLos <= @duree AND ra1.MaxLos >= @duree
AND i1.MinLos <= @duree AND i1.MaxLos >= @duree
AND IF(i1.jour = @DateDeb, i1.CloseOnArrival = 0, 1)
#AND i1.ReleaseDays <= @ReleaseDays
AND IF(@niveauTarifaire = 0 OR @niveauTarifaire IS NULL, 1, ro1.idRoomCategory = @niveauTarifaire)
AND CASE ra1.TypeRate
WHEN 'CLIENT' THEN ra1.CodeRate = @CodeClient
WHEN 'PAYS' THEN ra1.CodeRate = @CodePays
WHEN 'PAYSGROUPE' THEN ra1.CodeRate IN (@CodePaysGroup1, @CodePaysGroup2, @CodePaysGroup3) /* On ne teste que 3 codePaysGroup parceque bon... */
WHEN 'STD' THEN 1
ELSE 1
END
AND i1.AdultPrice > 0
]
POUR i=2 _A_ nRooms
sPartReq = [
AND ro%1.PaxMax >= @NbPaxDem%1+IF(@nbChild%1>0, IF(@ageChild%1%2 BETWEEN ro%1.AgeMin AND ro%1.AgeMax, 0, 1), 0)+IF(@nbChild%1>1, IF(@ageChild%1%3 BETWEEN ro%1.AgeMin AND ro%1.AgeMax, 0, 1), 0)
AND ro%1.NbChild+ro%1.PaxMax >= @nbChild%1+@NbPaxDem%1
AND i%1.duree = i1.duree
AND i%1.Jour = i1.Jour
AND i%1.idRate = i1.idRate
AND i%1.Inv > 0
AND IF(ro%1.PaxMax = %1, ro%1.bTwin = @bTwin%1 OR f.CDService = 'A', 1)
AND IF(@nbChild1 = 0, ro1.PaxMax <= ro%1.PaxMax, 1)
AND i%1.MinLos <= @duree AND i%1.MaxLos >= @duree
AND IF(i%1.jour = @DateDeb, i%1.CloseOnArrival = 0, 1)
#AND i%1.ReleaseDays <= @ReleaseDays
AND IF(@niveauTarifaire = 0 OR @niveauTarifaire IS NULL, 1, ro%1.idRoomCategory = @niveauTarifaire)
AND CASE ra%1.TypeRate
WHEN 'CLIENT' THEN ra%1.CodeRate = @CodeClient
WHEN 'PAYS' THEN ra%1.CodeRate = @CodePays
WHEN 'PAYSGROUPE' THEN ra%1.CodeRate IN (@CodePaysGroup1, @CodePaysGroup2, @CodePaysGroup3) /* On ne teste que 3 codePaysGroup parceque bon... */
WHEN 'STD' THEN 1
ELSE 1
END
AND i%1.AdultPrice > 0
]
sReq += ChaîneConstruit(sPartReq,i,1,2)
FIN
SI PAS stParamDevis.bRechercheVide ALORS
POUR i=1 _A_ nRooms
sPartReq = [
AND IF(
1
]
SI i < nRooms ALORS
sPartReq += [
+(i%1.idRoomGroup = i%2.idRoomGroup)
]
FIN
sPartReq += [
> i%1.Inv-i%1.InvContrat
, i%1.ReleaseDays <= @ReleaseDays
, 1
)
]
sReq += ChaîneConstruit(sPartReq,i,i+1)
FIN
FIN
SI PAS stParamDevis.bRechercheVide ALORS
POUR i=1 _A_ nRooms-1
sReq += [
AND 1
]
POUR j=i+1 _A_ nRooms
sPartReq = [
+(i%1.idRoomGroup = i%2.idRoomGroup)
]
sReq += ChaîneConstruit(sPartReq,i,j)
FIN
sPartReq = [
<= i%1.Inv
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
FIN
//Optimisation
POUR i=1 _A_ nRooms-1
POUR j=i+1 _A_ nRooms
sPartReq = [
AND IF(i%1.idRoomGroup = i%2.idRoomGroup AND @NbPaxDem%1 = @NbPaxDem%2 AND @nbChild%1 = @nbChild%2 AND @bTwin%1 = @bTwin%2, i%1.idRoomService = i%2.idRoomService, 1)
]
sReq += ChaîneConstruit(sPartReq,i,j)
FIN
FIN
SI nRooms >= 4 ALORS
sReq += [
/* Seulement pour les demandes de 4 chambres ou plus */
]
POUR i=1 _A_ nRooms-1
sPartReq = [
AND i%1.idRoomCategory = i%2.idRoomCategory
]
sReq += ChaîneConstruit(sPartReq,i,i+1)
FIN
FIN
SI nRooms = 3 ALORS
sReq += [
/* Seulement pour les demandes de 4 chambres ou plus */
]
POUR i=1 _A_ nRooms-1
sPartReq = [
AND i%1.idRoomCategory <= i%2.idRoomCategory
]
sReq += ChaîneConstruit(sPartReq,i,i+1)
FIN
FIN
//optimisation appart ***
sReq += [
AND IF(f.CDSERVICE = 'A' AND i1.idChannelManager = 'RT',
1
AND
CASE @NbPaxDem1+@nbChild1
WHEN 1 THEN i1.idRoomService = 21040
WHEN 2 THEN IF(@bTwin1 = 1, i1.idRoomService = 21044, i1.idRoomService = 21040)
WHEN 3 THEN i1.idRoomService = 21042
WHEN 4 THEN i1.idRoomService = 21043
WHEN 5 THEN i1.idRoomService = 21045
WHEN 6 THEN i1.idRoomService = 21046
WHEN 7 THEN i1.idRoomService = 21047
WHEN 8 THEN i1.idRoomService = 21048
WHEN 9 THEN i1.idRoomService = 21049
ELSE 1
END
]
POUR i=2 _A_ nRooms
sPartReq = [
AND
CASE @NbPaxDem%1+@nbChild%1
WHEN 1 THEN i%1.idRoomService = 21040
WHEN 2 THEN IF(@bTwin%1 = 1, i%1.idRoomService = 21044, i%1.idRoomService = 21040)
WHEN 3 THEN i%1.idRoomService = 21042
WHEN 4 THEN i%1.idRoomService = 21043
WHEN 5 THEN i%1.idRoomService = 21045
WHEN 6 THEN i%1.idRoomService = 21046
WHEN 7 THEN i%1.idRoomService = 21047
WHEN 8 THEN i%1.idRoomService = 21048
WHEN 9 THEN i%1.idRoomService = 21049
ELSE 1
END
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
, 1)
]
// INSERTION EXCLUSION CLIENT POUR CERTAINS HOTELS
sReq += [
AND NOT EXISTS (SELECT * FROM globalschema.scncltexclu sx WHERE sx.CdCltExclu = @CodeClient AND CdFrsExclu = ro1.idHotel)
] + RC
POUR i=2 _A_ nRooms
sPartReq = [
AND NOT EXISTS (SELECT * FROM globalschema.scncltexclu sx WHERE sx.CdCltExclu = @CodeClient AND CdFrsExclu = ro%1.idHotel)
] + RC
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
GROUP BY
ro1.idHotel,
ro1.idChannelManager,
ra1.idRate,
i1.Jour,
ro1.idRoomCategory,
-- ATTENTION DESACTIVATION DU GROUP BY SUR LE IDTBLROOMTYPEFRS --
-- ro1.idRoomGroup,
ro1.idRoomService,
IF(ro1.idChannelManager = 'IT',ro1.idRoomGroup,ro1.idRoomService)
]
POUR i=2 _A_ nRooms
sPartReq = [
,
ro%1.idRoomCategory,
-- ATTENTION DESACTIVATION DU GROUP BY SUR LE IDTBLROOMTYPEFRS --
-- ro%1.idRoomGroup,
ro%1.idRoomService,
IF(ro%1.idChannelManager = 'IT',ro%1.idRoomGroup,ro%1.idRoomService)
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
/**/
) AS qpromo
GROUP BY
idHotel_1,
idChannelManager_1,
idRate_1,
idRoomCategory_1,
-- ATTENTION DESACTIVATION DU GROUP BY SUR LE IDTBLROOMTYPEFRS --
-- idRoomGroup_1,
idRoomService_1,
IF(idChannelManager_1 = 'IT',idRoomGroup_1,idRoomService_1)
]
POUR i=2 _A_ nRooms
sPartReq = [
,
idRoomCategory_%1,
-- ATTENTION DESACTIVATION DU GROUP BY SUR LE IDTBLROOMTYPEFRS --
-- idRoomGroup_%1,
idRoomService_%1,
IF(idChannelManager_%1 = 'IT',idRoomGroup_%1,idRoomService_%1)
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
sReq += [
HAVING cnt = @duree
) AS qry1
WHERE 1
/* critere verif dispo */
]
sReq += [
/* fin critere verif dispo */
ORDER BY idHotel_1, idChannelManager_1, TotalPrice
) AS qry2
GROUP BY idHotel_1, IF(@RateName = 'WEB', idHotel_1, idChannelManager_1)
ORDER BY TotalPrice
) AS qry_Fin
JOIN globalschema.fourniss f ON(qry_Fin.idHotel = f.CDFourniss)
JOIN globalschema.cm_codes cmc ON(qry_Fin.idChannelManager = cmc.idChannelManager AND cmc.bActiveNew = 1)
LEFT JOIN globalschema.prix p ON(qry_Fin.idHotel = p.CDFourniss AND p.Annee = YEAR(@DateDeb) AND p.CDContrat = @CodeContrat)
LEFT JOIN (
SELECT CDFourniss,CONCAT(NOM_IM,EXT_IM) AS Image FROM globalschema.images WHERE typdoc = 'I' GROUP BY Cdfourniss ORDER BY pos_im
) AS i ON(qry_Fin.idHotel = i.CDFourniss)
LEFT JOIN globalschema.descript d ON(d.CDFourniss = f.CDFourniss AND IF(@Langue IS NULL, d.CDLangue = 'ANG', d.CDLangue = @Langue))
LEFT JOIN bdd_channel_manager.mappage_type_service mts1 ON(mts1.idtype_service = qry_Fin.idRoomService_1)
]
POUR i=2 _A_ nRooms
sPartReq = [
LEFT JOIN bdd_channel_manager.mappage_type_service mts%1 ON(mts%1.idtype_service = qry_Fin.idRoomService_%1)
]
sReq += ChaîneConstruit(sPartReq,i)
FIN
// NOUVEAUX CONTRATS on fait référence à ETAT dans fourniss_contrat_info
sReq += [
LEFT JOIN bdd_channel_manager.Channel_manager cm ON(cm.idChannel_Manager = cmc.idChannel_Manager)
LEFT JOIN bdd_channel_manager.affiliation_hotel ah ON(ah.cdfourniss = qry_Fin.idHotel AND ah.idAffiliation_channel_manager = cmc.idAffiliation_Channel_Manager)
LEFT JOIN globalschema.client_gestion_maj_contrat cgmc ON(cgmc.cdclient = @CodeClient AND cgmc.CDCONTRAT = @CodeContrat AND cgmc.ANNEE = YEAR(@DateDeb) AND cgmc.CDFOURNISS = qry_Fin.idHotel)
WHERE 1
AND IF(qry_Fin.idChannelManager != 'RT', cm.bActivity = 1, 1)
AND IF(qry_Fin.idChannelManager != 'RT', ah.etat = 5, 1)
AND IF(qry_Fin.idChannelManager = 'RT',
EXISTS (SELECT fci.CDFOURNISS FROM globalschema.fourniss_contrat_info fci WHERE 1 AND fci.cdfourniss = qry_Fin.idHotel AND fci.CDCONTRAT = @CodeContrat
AND IF(MONTH(@DateDeb)>10, fci.ANNEE = YEAR(@DateDeb)+1, fci.ANNEE = YEAR(@DateDeb))
AND fci.etat = 1
)
AND EXISTS (SELECT fci.CDFOURNISS FROM globalschema.fourniss_contrat_info fci WHERE 1 AND fci.cdfourniss = qry_Fin.idHotel AND fci.CDCONTRAT = @CodeContrat
AND IF(MONTH(@DateFin)>10, fci.ANNEE = YEAR(@DateFin)+1, fci.ANNEE = YEAR(@DateFin))
AND fci.etat = 1
)
,1)
AND NOT EXISTS(
SELECT * FROM globalschema.fourniss_contraint fc
WHERE 1
AND fc.cdfourniss = qry_Fin.idHotel
-- NON UTILISE
-- and fc.VILLE = f.Ville
-- AND DATE_SUB(fc.DATE_FIN, INTERVAL 1 DAY) >= DATE(NOW())+0
AND fc.DATE_FIN >= DATE(NOW())+0
AND fc.CDContrat = @CodeContrat
AND (
fc.DATE_DEBUT BETWEEN DATE(@DateDeb)+0 AND DATE_ADD(DATE(@DateFin)+0,INTERVAL 1 DAY)
OR DATE_SUB(fc.DATE_FIN, INTERVAL 1 DAY) BETWEEN DATE(@DateDeb)+0 AND DATE_ADD(DATE(@DateFin)+0,INTERVAL 1 DAY)
OR DATE(@DateDeb)+0 BETWEEN fc.DATE_DEBUT AND fc.DATE_FIN)
-- NON UTILISE
-- and if(fc.VILLE is not null, fc.VILLE = f.Ville, 1)
-- AND IF(fc.idType_service != 0, fc.idType_service IN(qry_Fin.idRoomService_1
AND CASE fc.contraint_type
WHEN 1 THEN fc.NB_JR_MIN > @duree
WHEN 2 THEN 1
ELSE 0
END)
;
]
RENVOYER sReq
---------------
C'est pas bon, Neriki, tu recommences à glander, là. :o
Users/flonumero/AppData/Local/Android/android-sdk
