    
    SELECT
        p.id_page,
        p.reference,
        a.id_annonce,
        a.latitude,
        a.longitude,
        lpo.id_type_objet_lie AS id_type_objet,
        CONCAT( '/' , pp.slug , '/' , p.slug ) AS url,
        a.nom AS programme,
        a.lien_video,
        a.lien_plan_3d,
        p.introduction,
        p.titre AS titre_annonce,
        a.id_quartier,
        a.id_commune,
        a.pinel,
        a.pinel_plus,
        a.brs,
        l.annee_livraison,
        c.zone_pinel,
        o.titre AS titre_offre,

        IF( a.id_quartier IS NULL, c.nom_min , CONCAT(c.nom_min, ' : ' ,q.nom_min) ) AS lieu,
        c.code_postal,

        substring_index(
            GROUP_CONCAT( DISTINCT
                CONCAT( l.trimestre_livraison, if( l.trimestre_livraison > 1, 'ème' ,'er' ), ' trimestre ', l.annee_livraison )
                ORDER BY t.nombre_pieces ASC SEPARATOR ','
            ) , ',', 1
        ) AS 'livraison',

        GROUP_CONCAT( DISTINCT CONCAT( t.nombre_pieces ) ORDER BY t.nombre_pieces ASC SEPARATOR ',' ) AS 'typologies',
        a.nombre_lots,

        IFNULL( o.id_offre, 0 ) AS offres,
        IFNULL( o_fdn.id_offre, 0 ) AS frais_notaire,

        MIN( t.prix_min ) AS prix_min,
        MIN( a.prix_min_rp ) AS prix_min_RP,
        a.tva_reduite,
        
        (
            SELECT
                COUNT(t.id_typologie)
            FROM typologies t
            WHERE
                t.id_annonce = a.id_annonce
                AND t.id_type_bien = 1
        ) maisons,

        (
            SELECT
            COUNT(t.id_typologie)
            FROM typologies t
            WHERE
            t.id_annonce = a.id_annonce
            AND t.id_type_bien = 3
        ) appartements,

        (
            SELECT
            COUNT(t.id_typologie)
            FROM typologies t
            WHERE
            t.id_annonce = a.id_annonce
            AND t.id_type_bien = 5
        ) residence_etudiantes,

        (
            SELECT
            COUNT(t.id_typologie)
            FROM typologies t
            WHERE
            t.id_annonce = a.id_annonce
            AND t.id_type_bien IN (4,6)
        ) residence_seniors,
  
        (
            SELECT
            COUNT(t.id_typologie)
            FROM typologies t
            WHERE
            t.id_annonce = a.id_annonce
            AND t.id_type_bien IN (4,5,6)
        ) residence_LMNP,
  
        a.prix_maitrise,

        if( a.date_fin_lancement > CURRENT_TIMESTAMP, TRUE , FALSE ) AS nouveaute,

        IF(
            (
                l.annee_livraison = YEAR( CURRENT_DATE() ) AND l.trimestre_livraison = QUARTER( CURRENT_DATE() )
            ) OR (
                l.annee_livraison = YEAR( DATE_ADD(CURRENT_DATE(), INTERVAL +3 MONTH) ) AND
                l.trimestre_livraison = QUARTER( DATE_ADD(CURRENT_DATE(), INTERVAL +3 MONTH))
            )
        ,1,0) AS livraison_rapide,

        IF(
            (
                l.annee_livraison < YEAR( CURRENT_DATE() )
            ) OR (
                l.annee_livraison = YEAR( CURRENT_DATE() ) AND
                l.trimestre_livraison < QUARTER( CURRENT_DATE())
            )
        ,1,0) AS deja_livre,

        CONCAT( '/medias/' , d_fav.nom_fichier_encode) AS image,
        GROUP_CONCAT( DISTINCT CONCAT('"/medias/' , doc.nom_fichier_encode , '-400.jpg"') SEPARATOR ',' ) AS images,
        COUNT( DISTINCT doc.id_document ) AS nb_images,
        a.phrase_accroche,
        IF(a.date_archivage IS NULL, 0, 1) AS archive

    FROM annonces a

    RIGHT JOIN typologies t
    ON 	t.id_annonce = a.id_annonce AND
        t.prix_min > 0

    RIGHT JOIN lots l
    ON 	t.id_lot_representatif = l.id_lot AND
        l.actif = 1
    
    LEFT JOIN liens_pages_objets lpo
    ON 	lpo.id_objet_lie = a.id_annonce AND
        lpo.id_type_objet_lie = 33

    RIGHT JOIN pages p
    ON 	lpo.id_page = p.id_page
         AND p.id_domaine = 106 

    LEFT JOIN pages pp
    ON 	p.id_page_parent = pp.id_page

    LEFT JOIN liens_documents_objets ldo_fav
    ON  ldo_fav.id_document_role = 7 AND
        ldo_fav.id_type_objet_lie = 33 AND
        ldo_fav.id_objet_lie = a.id_annonce

    LEFT JOIN documents d_fav
    ON d_fav.id_document = ldo_fav.id_document AND d_fav.actif = 1

    LEFT JOIN liens_documents_objets ldo
    ON  ldo.id_document_role IN (5,6,7) AND
        ldo.id_type_objet_lie = 33 AND
        ldo.id_objet_lie = a.id_annonce

    LEFT JOIN documents doc
    ON doc.id_document = ldo.id_document AND doc.actif = 1

    LEFT JOIN communes c
    ON c.id_commune = a.id_commune

    LEFT JOIN quartiers q
    ON q.id_quartier = a.id_quartier

    LEFT JOIN offres o
    ON  o.id_objet_lie = a.id_annonce AND
        o.actif = 1 AND
        o.valide = 1 AND
        o.id_type_objet_lie = 33 AND
        o.date_debut < NOW() AND
        ( o.date_fin IS NULL OR DATE_ADD( o.date_fin, INTERVAL 1 DAY) > NOW() )
        
    LEFT JOIN offres o_fdn
    ON  o.id_objet_lie = a.id_annonce AND
        o.actif = 1 AND
        o.valide = 1 AND
        o.id_type_objet_lie = 33 AND
        o.date_debut < NOW() AND
        ( o.date_fin IS NULL OR DATE_ADD( o_fdn.date_fin, INTERVAL 1 DAY) > NOW() ) AND
        o.id_type_offre = 1

    WHERE
        a.id_annonce IS NOT NULL 
         AND p.id_gabarit = 101 
        AND p.id_page_statut = 4
        AND p.actif = 1
         AND p.id_domaine = 106 
    	 AND a.id_commune = '64189' 

    GROUP BY a.id_annonce

    HAVING
        1 = 1

    ORDER BY 
        archive ASC,
        a.id_annonce DESC

Chargement...