@Override
  @SuppressWarnings("unchecked")
  public List<PopulationDto> getListePopulations(Long uidPersonneMorale) {
    final StringBuffer requete =
        new StringBuffer("select population, count(distinct uidAssure) from Contrat ")
            .append("where uidSouscripteur = :uidPersonneMorale ")
            .append("and uidAssure is not null ")
            .append("and statut.id = :idStatutEnCours ")
            .append("and nature.id = :idNatureSante ")
            .append("and isVisible = true ")
            .append("group by population");
    final Query query = createQuery(requete.toString());
    query.setLong("uidPersonneMorale", uidPersonneMorale);
    query.setLong("idStatutEnCours", adherentMappingService.getIdStatutContratEnCours());
    query.setLong("idNatureSante", adherentMappingService.getIdNatureContratSante());

    final List<Object[]> listResult = query.list();
    final List<PopulationDto> listePopulations = new ArrayList<PopulationDto>();
    if (listResult != null && listResult.size() > 0) {
      for (Object[] result : listResult) {
        final String libellePopulation = (String) result[0];
        final Integer effectif = Integer.valueOf((String) result[1].toString());
        final PopulationDto population = new PopulationDto();
        population.setLibelle(libellePopulation);
        population.setEffectif(effectif);
        listePopulations.add(population);
      }
    }
    return listePopulations;
  }
 @Override
 @SuppressWarnings("unchecked")
 public Contrat getContratSantePersonneAVenir(Long idPersonne) {
   final StringBuffer requete = new StringBuffer("select contrat_numero from data_contrat ");
   requete.append("where data_contrat.contrat_assure_uid = :idPersonne ");
   requete.append("and data_contrat.contrat_statut_uid = :idStatutFutur ");
   requete.append("and data_contrat.contrat_nature_uid = :idNatureSante ");
   // requete.append("and data_contrat.contrat_adhesion_onum <> 'groupe' ");
   requete.append("and data_contrat.contrat_date_adhesion > now() ");
   requete.append("and data_contrat.contrat_visible = true ");
   requete.append("group by contrat_numero");
   final Query crit = createSqlQuery(requete.toString());
   crit.setLong("idPersonne", idPersonne);
   crit.setLong("idStatutFutur", adherentMappingService.getIdStatutContratFutur());
   crit.setLong("idNatureSante", adherentMappingService.getIdNatureContratSante());
   final List<String> resultats = (ArrayList<String>) crit.list();
   return resultats.size() == 1 ? ((Contrat) getContratByNumero(resultats.get(0))) : null;
 }
 @Override
 @SuppressWarnings("unchecked")
 public Contrat getContratSantePersonneInactifXMois(
     Long idPersonne, Calendar dateInactiviteGarantieSante) {
   final StringBuffer requete = new StringBuffer("select contrat_numero from data_contrat ");
   requete.append("where data_contrat.contrat_assure_uid = :idPersonne ");
   requete.append(
       "and data_contrat.contrat_statut_uid <> :idStatutEnCours and data_contrat.contrat_nature_uid = :idNatureSante ");
   // requete.append("and data_contrat.contrat_adhesion_onum <> 'groupe' ");
   requete.append(
       "and data_contrat.contrat_date_resiliation < now() and data_contrat.contrat_date_resiliation >= :dateInactiviteGarantieSante ");
   requete.append("and data_contrat.contrat_visible = true ");
   requete.append("group by contrat_numero ");
   final Query crit = createSqlQuery(requete.toString());
   crit.setLong("idPersonne", idPersonne);
   crit.setLong("idStatutEnCours", adherentMappingService.getIdStatutContratEnCours());
   crit.setLong("idNatureSante", adherentMappingService.getIdNatureContratSante());
   crit.setCalendar("dateInactiviteGarantieSante", dateInactiviteGarantieSante);
   final List<String> resultats = (ArrayList<String>) crit.list();
   return resultats.size() == 1 ? ((Contrat) getContratByNumero(resultats.get(0))) : null;
 }