promoSELECT cr.ref_prod, cr.id_cruise, cr.nom nom_croisiere, cr.txt_iti, cr.vol, cr.duree, cr.prefixe_itineraire,cr.suffixe_itineraire,
cp.nom nom_comp,
bt.id_bateau idBat, bt.nom nom_bat, bt.etoile etoile,
px.id_prix id_prix,
p.titre promo,
pk.id_package, pk.type_package,
d.nom nom_dest,
r.nom nom_region,
IF(FIND_IN_SET(SUBSTRING_INDEX(cr.itineraire_new,'|',1),'21,455,454,81'), true, false) pictoCarte,
IF(pk.prix_package IS NOT NULL AND pk.type_package REGEXP 'vols?|vuelos?|vol[i|o]', IF(cr.add_package=1, 'bleu', false), false) pictoAvion ,
IF(cr.add_package=1 AND pk.prix_package IS NOT NULL, MIN(ROUND(IFNULL(px.prix_int,IFNULL(px.prix_ext, IFNULL(px.prix_balcon, px.prix_suite))))+ROUND(pk.prix_package)), MIN(ROUND(IFNULL(px.prix_int,IFNULL(px.prix_ext, IFNULL(px.prix_balcon, px.prix_suite)))))) prix,
IF(cr.add_package=1 AND pk.prix_package IS NOT NULL, MIN(ROUND(IFNULL(p.nv_prix_int,IFNULL(p.nv_prix_ext, IFNULL(p.nv_prix_balcon, p.nv_prix_suite))))+ROUND(pk.prix_package)), MIN(ROUND(IFNULL(p.nv_prix_int,IFNULL(p.nv_prix_ext, IFNULL(p.nv_prix_balcon, p.nv_prix_suite)))))) prixPromo,
IF(cr.add_package=1 AND pk.prix_package IS NOT NULL, MIN(ROUND(IFNULL(p.nv_prix_int,IFNULL(p.nv_prix_ext, IFNULL(p.nv_prix_balcon, IFNULL(p.nv_prix_suite, IFNULL(px.prix_int,IFNULL(px.prix_ext, IFNULL(px.prix_balcon, px.prix_suite))))))))+ROUND(pk.prix_package)), MIN(ROUND(IFNULL(p.nv_prix_int,IFNULL(p.nv_prix_ext, IFNULL(p.nv_prix_balcon, IFNULL(p.nv_prix_suite, IFNULL(px.prix_int,IFNULL(px.prix_ext, IFNULL(px.prix_balcon, px.prix_suite)))))))))) bestPrice,
CONCAT('http://www.qcnscruise.com/templates_mc/compagnies/',REPLACE(LOWER(cp.nom),' ','-'),'/images/',REPLACE(LOWER(bt.nom),' ','-'),'/',REPLACE(LOWER(bt.nom),' ','-'),'.jpg') imgBat,
CONCAT('http://www.qcnscruise.com/templates_mc/compagnies/',REPLACE(LOWER(cp.nom),' ','-'),'/images/th_',LOWER(cp.logo)) imgLogo,
CONCAT('crucero : ', d.nom, ', ', cr.duree+1, ' dias, ', bt.nom, ' (', cp.nom, ').') txtCroisiere
FROM croisiere cr
JOIN prix px ON px.ref_prod=cr.ref_prod AND px.es=1 AND px.date>'2012-02-05' AND px.visible>0 AND px.coup_coeur=0 AND EXISTS (SELECT * FROM promo pr WHERE pr.id_prix=px.id_prix AND pr.promotion=1 AND pr.coup_coeur=0 AND pr.confidentiel=0 AND pr.langue='es' AND pr.debut_validite<='2012-02-05' AND pr.fin_validite>'2012-02-05')
JOIN compagnie cp ON cp.id_compagnie=cr.compagnie AND cp.visible=1 AND cp.hidden_es='0'
JOIN bateau bt ON bt.id_bateau =cr.bateau
JOIN destination d ON d.id_destination=cr.destination
JOIN regions r ON r.id_region=cr.region
JOIN promo p ON p.id_prix=px.id_prix AND p.fin_validite>'2012-02-05' AND p.confidentiel=0 AND p.promotion=1 AND p.langue='es'
LEFT OUTER JOIN packages pk ON pk.id_package=cr.id_package AND pk.lang='es'
WHERE cr.lang='es' AND (FIND_IN_SET(431,REPLACE(cr.multi_dest, '|', ',')) OR cr.destination=431) AND NOT EXISTS (SELECT * FROM croisiere_exclude_affiliates WHERE affiliates_id_affiliate='288' AND croisiere_id_cruise=cr.id_cruise)
GROUP BY cr.ref_prod
ORDER BY bestPrice
;
