SELECT /* NET */ @rowNum:=@rowNum+1 numeroLigne, b.* FROM (SELECT @rowNum :=0) num, (SELECT SUM( CASE WHEN dmdcr.lang = "sa" AND dmdcr.datedemande BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW() THEN dmdcr.nbdemande END) nbDemande, /* requete getNbDemandeLastWeek */ CASE WHEN dp.date >= DATE( NOW() ) THEN dp.date END AS `date`, /* requete getDatePrixCroisiere */ CASE WHEN p.lang="sa" THEN CONCAT(p.idpression, "|", p.accroche, "|", p.idtype) END AS `tabPression`, /* requete getPressionMarketing */ CEIL(100 - ((count(distinct(case when cc.idbateau = a.idbateau AND (px.prix_croisiere > 0 || px.prix_package > 0) AND cc.codecabine <>"" AND px.isactive = 1 then cc.idcategorie_cabine end)) - count(distinct(case when cc.idbateau = a.idbateau AND (px.prix_croisiere > 0 || px.prix_package > 0) AND cc.codecabine <>"" AND px.isactive = 1 and px.isfull=1 then cc.idcategorie_cabine end)))/2) * 100 / count(distinct(case when cc.idbateau = a.idbateau AND (px.prix_croisiere > 0 || px.prix_package > 0) AND cc.codecabine <>"" AND px.isactive = 1 then cc.idcategorie_cabine end))) as tauxRemplissage /* requete getTauxRemplissage */ , a.* FROM ( SELECT cr.idcroisiere, cr.metacroisiere, cr.currency, cr.lang langCroisiere, cr.nom nomcroisiere, cr.iditineraire, cr.description, bp.compagnieluxe croisiereLuxe, it.isroundtrip, IF(it.isroundtrip = 1 AND ipd.idport IN( 199,48,348,456,455,4,460 ), 1, 0) portPrioritaireRoundtrip, IF(it.isroundtrip = 1 AND bp.idcompagnie IN(5,7,11,31,2,25,32) AND ipd.idport IN( 199,48,348,456,455,4,460 ) , 1, 0) roundTripPrioritaire, IF(bp.idcompagnie IN(5,7,11,31,2,25,32) AND bp.idcompagnie NOT IN(0), 1, 0) compagniePrioritaire, IF( ipd.idport IN( 199,48,348,456,455,4,460 ), 1, 0) portPrioritaire, IF(itd.iddestination IN (0), 1, 0) destinationPrioritaire, ipd.idport, cr.duree, cr.duree + 1 dureeJour, bp.isCDC, bp.isCDCPrio, bp.nomcompagnie, bp.urlcompagnie, bp.idcompagnie, bp.compagnieluxe, bp.compagnieishautdegamme, bp.compagniehashautdegamme, bp.logo, bp.idbateau, bp.nombateau, bp.etoile, bp.miseenservice, bp.nbpassager, bp.int, bp.ext, bp.balcon, bp.suite, bp.bateauhashautdegamme, bp.iddepart2 iddepart, bp.idprix, d.iddestination, d.nom nomDestination, d.url urlDestination, IF( ipd.idport IN(0 ), true, false ) pictoCarte, IFNULL(bp.voloption, 0) voloption, IFNULL(bp.volinclus, 0) volinclus, IFNULL(bp.transfertoption, 0) transfertoption, IFNULL(bp.transfertinclus, 0) transfertinclus, bp.date as bpDate, bp.idcategorie_cabine, bp.prixNormalSansPackage, IF(bp.prixPromoSansPackage != 999999, bp.prixPromoSansPackage, 0) prixPromoSansPackage, bp.bestPriceSansPackage, bp.prixNormalAvecPackage1 AS prixNormalAvecPackage, IF(bp.prixPromoAvecPackage1 != 999999, bp.prixPromoAvecPackage1, 0) prixPromoAvecPackage, bp.bestPriceAvecPackage1 AS bestPriceAvecPackage, LEAST ( IF(bp.bestPriceAvecPackage1 != 0, bp.bestPriceAvecPackage1, 999999), IF(bp.bestPriceSansPackage != 0, bp.bestPriceSansPackage, 999999) ) bestPriceMinBudget, GREATEST( IF(bp.bestPriceAvecPackage1 != 999999, bp.bestPriceAvecPackage1, 0), IF(bp.bestPriceSansPackage != 999999, bp.bestPriceSansPackage, 0) ) bestPriceMaxBudget, IF ( bp.prixNormalAvecPackage1 != 0 && bp.prixPromoSansPackage != 0 && bp.prixPromoSansPackage != bp.prixNormalAvecPackage1, (ROUND((bp.prixNormalAvecPackage1 - bp.prixPromoSansPackage) * 100 / bp.prixNormalAvecPackage1) * (-1)), 0) reduction, bp.volInclusPrix, bp.totalTaxes /*bp.prixNormalAvecPackage1, bp.prixPromoAvecPackage1, bp.bestPriceAvecPackage1*/ FROM croisiere cr JOIN ( select /* Recupération des prix avec package pour les croisières qui ont un prix sans package */ ROUND(px.prix_package) as prixNormalAvecPackage1, ROUND(px.promo_package) prixPromoAvecPackage1, ROUND(IF(ROUND(IF(px.promo_package != 0, px.promo_package, 999999)) < ROUND(IF(px.prix_package != 0, px.prix_package, 999999)) AND ROUND(IF(px.promo_package != 0, px.promo_package, 999999)) > 0, ROUND(IF(px.promo_package != 0, px.promo_package, 999999)) , ROUND(IF(px.prix_package != 0, px.prix_package, 999999)) )) AS bestPriceAvecPackage1, /*ROUND(MIN(IF(px.prix_package != 0, px.prix_package, 999999))) prixNormalAvecPackage1, ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) prixPromoAvecPackage1, ROUND(IF(ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) < ROUND(MIN(IF(px.prix_package != 0, px.prix_package, 999999))) AND ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) > 0, ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) , ROUND(MIN(IF(px.prix_package != 0, px.prix_package, 999999))) )) AS bestPriceAvecPackage1,*/ IF(ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) = 0 || ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) = 999999, IF(ROUND(MIN(IF(px.prix_package != 0, px.prix_package, 999999))) = 0 || ROUND(MIN(IF(px.prix_package != 0, px.prix_package, 999999))) = 999999, 0, 1), 1) volinclus, ROUND(pxt.departuretax + pxt.portcharges ) as totalTaxes, pxSansPackage.* from ( SELECT cc.idcategorie_cabine, typecabine, px.idprix, px.iddepart iddepart2, px.prix_croisiere, px.promo_croisiere, px.prix_package, px.promo_package, px2.*, dp.date FROM ( SELECT cr.idcroisiere, cr.duree, cr.lang, 0 isCDC, 0 isCDCPrio, bt.idbateau, bt.nom nombateau, bt.etoile, bt.miseenservice, bt.nbpassager, bt.int, bt.ext, bt.balcon, bt.suite, bt.hashautdegamme bateauhashautdegamme, cp.idcompagnie, cp.isluxe compagnieluxe, cp.ishautdegamme compagnieishautdegamme, cp.hashautdegamme compagniehashautdegamme, cpd.nom nomcompagnie, cpd.url urlcompagnie, cp.logo, pk.voloption, pk.transfertoption, pk.transfertinclus, IFNULL(pk.prix, 0) prixPackage, ROUND(MIN(IF(px.prix_croisiere != 0, px.prix_croisiere, 999999))) prixNormalSansPackage, ROUND(MIN(IF(px.promo_croisiere != 0, px.promo_croisiere, 999999))) prixPromoSansPackage, ROUND(IF(ROUND(MIN(IF(px.promo_croisiere != 0, px.promo_croisiere, 999999))) < ROUND(MIN(IF(px.prix_croisiere != 0, px.prix_croisiere, 999999))) AND ROUND(MIN(IF(px.promo_croisiere != 0, px.promo_croisiere, 999999))) > 0, ROUND(MIN(IF(px.promo_croisiere != 0, px.promo_croisiere , 999999))), ROUND(MIN(IF(px.prix_croisiere != 0, px.prix_croisiere, 999999))) )) AS bestPriceSansPackage, /* On récupère le prix minimum avec package ici également, car s il n existe pas de prix croisiere normal, on affiche au moins le prix avec package */ ROUND(MIN(IF(px.prix_package != 0, px.prix_package, 999999))) prixNormalAvecPackage, ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) prixPromoAvecPackage, ROUND(IF(ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) < ROUND(MIN(IF(px.prix_package != 0, px.prix_package, 999999))) AND ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) > 0, ROUND(MIN(IF(px.promo_package != 0, px.promo_package, 999999))) , ROUND(MIN(IF(px.prix_package != 0, px.prix_package, 999999))) )) AS bestPriceAvecPackage, px.vol volInclusPrix, px.iddepart FROM croisiere cr JOIN depart dp USING(idcroisiere) JOIN bateau bt USING(idbateau) JOIN compagnie cp USING(idcompagnie) JOIN compagnie_detail cpd USING(idcompagnie) JOIN itineraire_destination itd USING(iditineraire) JOIN destination d USING(iddestination) JOIN escale e USING(iditineraire) JOIN prix_sa px ON px.iddepart = dp.iddepart LEFT JOIN package pk ON pk.idpackage = cr.idpackage AND pk.lang = "sa" AND ( pk.volinclus = 1 OR pk.transfertinclus = 1 OR businclus = 1 ) WHERE 1 AND cpd.hidden = 0 AND cp.visible = 1 AND cr.active = 1 AND px.isactive = 1 AND px.isfull = 0 AND (px.prix_croisiere > 0 || px.prix_package > 0) AND cpd.lang = "sa" AND dp.date > "2019-10-21" AND cr.lang = "sa" AND d.lang = "sa" AND cp.idcompagnie NOT IN( "345, 396" ) AND e.idport IN( "1250" ) AND e.ordre > 1 GROUP BY cr.idcroisiere ) px2 /* jointure sur la croisiere*/ join depart dp on dp.idcroisiere = px2.idcroisiere /*and px2.iddepart = dp.iddepart*/ JOIN prix_sa px ON (px.iddepart = dp.iddepart) JOIN categorie_cabine cc USING(idcategorie_cabine) WHERE 1 and /*(px.promo_croisiere = bestPriceSansPackage or px.prix_croisiere = bestPriceSansPackage) or (bestPriceSansPackage=999999)*/ if(bestPriceSansPackage != 999999 , (px.promo_croisiere = bestPriceSansPackage or px.prix_croisiere = bestPriceSansPackage), (px.promo_package = bestPriceAvecPackage or px.prix_package = bestPriceAvecPackage) ) AND dp.date > "2019-10-21" AND px.isactive = 1 AND px.isfull = 0 AND (px.prix_croisiere > 0 || px.prix_package > 0) )pxSansPackage join prix_sa px on pxSansPackage.idprix = px.idprix and pxSansPackage.iddepart2 = px.iddepart LEFT JOIN prix_taxe pxt ON pxt.idprix = px.idprix WHERE 1 GROUP BY pxSansPackage.idcroisiere ) bp USING(idcroisiere) JOIN itineraire it USING(iditineraire) JOIN itineraire_destination itd USING(iditineraire) JOIN destination d USING(iddestination) JOIN itineraire_port_depart ipd USING(iditineraire) WHERE cr.lang = bp.lang AND cr.lang = d.lang AND itd.principale = 1 AND ipd.principal = 1 GROUP BY cr.idcroisiere ORDER BY isCDCPrio DESC, isCDC DESC, roundTripPrioritaire DESC, compagniePrioritaire DESC, portPrioritaire DESC, destinationPrioritaire DESC, bestPriceMinBudget, bpDate ) a LEFT JOIN demande_croisiere dmdcr ON a.idcroisiere = dmdcr.idcroisiere LEFT JOIN depart dp ON a.iddepart = dp.iddepart LEFT JOIN pression p ON a.idcroisiere = p.idcroisiere LEFT JOIN prix_sa px ON dp.iddepart = px.iddepart LEFT JOIN categorie_cabine cc ON px.idcategorie_cabine = cc.idcategorie_cabine WHERE 1 AND cc.idbateau = a.idbateau AND (px.prix_croisiere > 0 || px.prix_package > 0) AND codecabine <>"" AND px.isactive = 1 GROUP BY a.idcroisiere ORDER BY IF(isCDCPrio = 1, CAST(@rowNum AS UNSIGNED), 9999) ASC, IF( isCDC = 1 , CAST(@rowNum AS UNSIGNED), 9999) ASC , roundTripPrioritaire DESC, compagniePrioritaire DESC, portPrioritaire DESC, destinationPrioritaire DESC, bestPriceMinBudget, bpDate ) b

N°1 de cruceros
Mis favoritos
0
Información y Reservas
+1 (786) 842-4533
Estamos cerrados - Ver horario de apertura
encuentra el crucero ideal
Cruceros Crucerosnet > Todos los puertos > escala : Rin > Cruceros Pasando Por Krefeld
Cruceros Crucerosnet > Todos los puertos > escala : Rin > Cruceros Pasando Por Krefeld
Puntúa nuestra página !
Tu opinión nos interesa
NECESITAS AYUDA?
Información y Reservas
+1 (786) 842-4533
Lun.Vie. 9:00/19:00 Sáb. 9:00/19:00 Dom. 9:00/19:00   (GMT +1)
Un especialista te llamará
Introduce tus datos de contacto