SELECT
  t2.code_dr,
  t2.libelle_dr,
  t2.libelle_contrat,
  SUM(t2.somme_echeance_calculee) AS montant,
  SUM(t2.nb_echeance_calculee) AS nb
FROM
  (
    SELECT
      t.num_version_contrat,
      t.libelle_contrat,
      t.libelle_type_contrat,
      t.libelle_etat_contrat,
      t.dernier_montant,
      t.libelle_dr,
      t.code_dr,
      t.flag_pris_en_compte_dans_V3_V2,
      t.date_effet_contrat,
      t.date_fin_contrat,
      GROUP_CONCAT(DISTINCT t.code_nidt SEPARATOR '\n') code_nidt,
      t.date_debut_periode_echeance,
      t.date_fin_periode_echeance,
      (
        CASE WHEN t.date_fin_contrat > DATE('2019-12-31') THEN FLOOR(
          TIMESTAMPDIFF(
            MONTH,
            t.date_quittancement_echeance2,
            DATE('2019-12-31')
          ) / 12
        ) ELSE FLOOR(
          TIMESTAMPDIFF(
            MONTH,
            t.date_quittancement_echeance2,
            t.date_fin_contrat
          ) / 12
        ) END
      ) nb_echeance_calculee,
      (
        CASE WHEN t.date_fin_contrat > DATE('2019-12-31') THEN t.montant_ht_actualise_echeance * (
          (
            1 - (
              pow(
                (1 + 0.02),(
                  FLOOR(
                    TIMESTAMPDIFF(
                      MONTH,
                      t.date_quittancement_echeance2,
                      DATE('2019-12-31')
                    ) / 12
                  )
                )
              )
            )
          ) / (1 -(1 + 0.02))
        ) ELSE t.montant_ht_actualise_echeance * (
          (
            1 - (
              pow(
                (1 + 0.02),(
                  FLOOR(
                    TIMESTAMPDIFF(
                      MONTH,
                      t.date_quittancement_echeance2,
                      t.date_fin_contrat
                    ) / 12
                  )
                )
              )
            )
          ) / (1 -(1 + 0.02))
        ) END
      ) somme_echeance_calculee,
      (
        CASE WHEN t.code_indice LIKE '%\%%' THEN ROUND(
          (
            t.montant_ht_actualise_echeance *(
              1 +(
                CAST(
                  REPLACE(
                    (
                      SUBSTRING(t.code_indice, 1, LOCATE('%', t.code_indice) -1)
                    ),
                    ',',
                    '.'
                  ) AS DECIMAL(10, 4)
                )
              ) / 100
            )
          ),
          2
        ) WHEN t.code_indice LIKE 'ICC%' THEN CASE WHEN REPLACE(
          t.periode_courante_indice_echeance,
          SUBSTRING(t.periode_courante_indice_echeance, 1, 4),(
            CAST(
              SUBSTRING(t.periode_courante_indice_echeance, 1, 4) AS SIGNED INTEGER
            ) + 1
          )
        ) IN (
          SELECT
            icc_periode
          FROM
            indice_icc
        ) THEN ROUND(
          (
            t.montant_ht_actualise_echeance *(
              SELECT
                CAST(
                  REPLACE(icc_multiplicateur, ',', '.') AS DECIMAL(10, 4)
                )
              FROM
                indice_icc
              WHERE
                REPLACE(
                  t.periode_courante_indice_echeance,
                  SUBSTRING(t.periode_courante_indice_echeance, 1, 4),(
                    CAST(
                      SUBSTRING(t.periode_courante_indice_echeance, 1, 4) AS SIGNED INTEGER
                    ) + 1
                  )
                ) = icc_periode
            )
          ),
          2
        ) ELSE ROUND((t.montant_ht_actualise_echeance *(1.02)), 2) END ELSE ROUND((t.montant_ht_actualise_echeance *(1.02)), 2) END
      ) montant_ht_actualise_echeance,
      t.date_quittancement_echeance2,
      t.libelle_rubrique_echeance,
      t.code_indice,
      t.nom_tiers
    FROM
      (
        SELECT
          g.num_version_contrat,
          g.num_contrat,
          g.libelle_contrat,
          g.libelle_dr,
          g.code_dr,
          g.libelle_type_contrat,
          g.date_effet_contrat,
          g.date_fin_contrat,
          g.libelle_etat_contrat,
          g.flag_pris_en_compte_dans_V3_V2,
          g.date_debut_periode_echeance,
          g.date_fin_periode_echeance,
          g.date_quittancement_echeance,
          MAX(g.date_quittancement_echeance) date_quittancement_echeance2,
          (
            CASE WHEN MAX(g.date_quittancement_echeance) > DATE(NOW()) THEN 0 WHEN g.num_version_contrat IN (
              SELECT
                DISTINCT gid3.num_version_contrat
              FROM
                base_gid gid3
              WHERE
                EXISTS (
                  SELECT
                    *
                  FROM
                    base_gid gid2
                  WHERE
                    gid2.libelle_rubrique_echeance <> gid3.libelle_rubrique_echeance
                    AND gid2.num_version_contrat = gid3.num_version_contrat
                    AND gid2.date_quittancement_echeance <> gid3.date_quittancement_echeance
                    AND gid2.code_nidt = gid3.code_nidt
                    AND gid2.nom_tiers = gid3.nom_tiers
                    AND gid2.libelle_rubrique_echeance IN ('LOYER NET', 'LOYER HT')
                )
            ) THEN 0 ELSE 1 END
          ) libelle_rubrique_echeance2,
          g.montant_ht_actualise_echeance,
          g.code_indice,
          g.code_nidt,
          b.nom_site,
          g.nom_tiers,
          g.libelle_rubrique_echeance,
          g.periode_courante_indice_echeance,
          groupe1.dernier_montant
        FROM
          base_bde b
          LEFT JOIN base_gid g ON b.code_nidt = g.code_nidt
          INNER JOIN (
            SELECT
              num_version_contrat,
              MAX(date_quittancement_echeance) AS maxDate,
              montant_ht_actualise_echeance AS dernier_montant
            FROM
              base_gid g4
            GROUP BY
              num_version_contrat
          ) groupe1 ON (
            g.num_version_contrat = groupe1.num_version_contrat
          )
          AND (g.date_quittancement_echeance = groupe1.maxDate)
        WHERE
          (
            g.libelle_etat_contrat IN ('Actif', 'En cours de création')
          )
          AND (g.flag_pris_en_compte_dans_V3_V2 = 1)
          AND (g.libelle_contrat NOT LIKE "%ORANGE REUNION%")
          AND (g.libelle_contrat NOT LIKE "%ORANGE CARAIBES%")
          AND (
            g.libelle_rubrique_echeance IN (
              'LOYER HT',
              'CHARGES HT',
              'ENERGIE HT',
              'CHARGES DOM HT',
              'CHARGES NETTES',
              'ENERGIE NETTE',
              'LOYER NET',
              'LOYER SOL HT',
              'LOYER HT (TVA 19%)'
            )
          )
          AND (
            g.code_nidt NOT IN (
              SELECT
                code_nidt
              FROM
                sites_logiques
            )
          )
          AND (g.date_fin_contrat > DATE('2019-01-01'))
          AND (g.timestampdiff_debut_fin_periode_echeance < 24)
        GROUP BY
          g.num_version_contrat,
          g.libelle_rubrique_echeance
        HAVING
          TIMESTAMPDIFF(
            MONTH,
            MAX(g.date_quittancement_echeance),
            g.date_fin_contrat
          ) > 13
      ) AS t
    WHERE
      (
        date_quittancement_echeance2 < DATE('2019-12-31')
      )
      AND (libelle_rubrique_echeance2 = 1)
    GROUP BY
      t.num_version_contrat,
      t.date_debut_periode_echeance,
      t.date_fin_periode_echeance,
      t.montant_ht_actualise_echeance,
      t.libelle_rubrique_echeance,
      t.nom_tiers
  ) AS t2
GROUP BY
  t2.code_dr