@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
public Integer getNombreAdherentsContrat(String eidContrat) {
final StringBuffer requete =
new StringBuffer("select count(distinct uidAssure) from Garantie ")
.append("where contrat.identifiantExterieur like :idContrat ")
.append("and contrat.uidAssure is not null ")
.append("and contrat.statut.id = :idStatutContratEnCours ")
.append("and statut.id = :idStatutGarantieEnCours ")
.append("and isVisible = true");
final Query query = createQuery(requete.toString());
query.setString("idContrat", eidContrat.substring(0, NB_CARACTERE_ID_CONTRAT) + POURCENT);
query.setLong("idStatutContratEnCours", adherentMappingService.getIdStatutContratEnCours());
query.setLong("idStatutGarantieEnCours", adherentMappingService.getIdStatutGarantieEnCours());
final Integer nbAdherents = Integer.valueOf(query.uniqueResult().toString());
return nbAdherents;
}
private Criteria createCriteriaByCriteres(CritereRechercheContratDto criteres) {
final Criteria crit = createCriteria(Contrat.class);
crit.createAlias("statut", "statut");
if (criteres != null && criteres.getIdAssure() != null) {
crit.add(Restrictions.eq("uidAssure", criteres.getIdAssure()));
}
if (criteres != null && criteres.getIdSouscripteur() != null) {
crit.add(Restrictions.eq("uidSouscripteur", criteres.getIdSouscripteur()));
}
if (criteres != null && criteres.isIdAssureNull()) {
crit.add(Restrictions.isNull("uidAssure"));
}
if (criteres != null && StringUtils.isNotBlank(criteres.getNumeroContrat())) {
crit.add(Restrictions.ilike("numeroContrat", criteres.getNumeroContrat() + "%"));
}
if (criteres != null
&& criteres.getHasContratEnCours() != null
&& criteres.getHasContratEnCours()) {
crit.add(Restrictions.eq("statut.id", adherentMappingService.getIdStatutContratEnCours()));
}
if (criteres != null
&& criteres.getContratEid() != null
&& StringUtils.isNotBlank(criteres.getContratEid())) {
crit.add(Restrictions.like("identifiantExterieur", "%" + criteres.getContratEid() + "%"));
}
crit.add(Restrictions.eq("isVisible", true));
return crit;
}
@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;
}
@SuppressWarnings("unchecked")
@Override
public List<GarantieBeneficiaireDto> getListeBeneficiairesFromContrats(
List<Long> listeUidContrats, Long uidAssure, boolean filtrerContratEnCOurs) {
final StringBuffer requete =
new StringBuffer("select uidBeneficiaire, contrat.id, role.id, role.libelle ");
requete.append("from Garantie where ");
if (listeUidContrats != null && listeUidContrats.size() > 0) {
requete.append("contrat.id in (:listeUidContrats) and ");
}
if (filtrerContratEnCOurs) {
requete.append("statut.id = :idStatutGarantieEnCours and ");
}
requete.append("contrat.isVisible = true and isVisible = true and ");
requete.append("uidAssure = :uidAssure order by statut.ordre, role.ordre");
final Query query = createQuery(requete.toString());
if (listeUidContrats != null && listeUidContrats.size() > 0) {
query.setParameterList("listeUidContrats", listeUidContrats);
}
if (filtrerContratEnCOurs) {
query.setLong("idStatutGarantieEnCours", adherentMappingService.getIdStatutGarantieEnCours());
}
query.setLong("uidAssure", uidAssure);
final List<Object[]> listResult = query.list();
final List<GarantieBeneficiaireDto> listeBeneficiaires =
new ArrayList<GarantieBeneficiaireDto>();
final List<IdsBeneficiaireContrat> listeIdsBenefsContrats =
new ArrayList<IdsBeneficiaireContrat>();
if (listResult != null && listResult.size() > 0) {
for (Object[] result : listResult) {
final Long idBenef = (Long) result[0];
final Long idContrat = (Long) result[1];
final IdsBeneficiaireContrat idBenefContrat = new IdsBeneficiaireContrat();
idBenefContrat.setIdBenef(idBenef);
idBenefContrat.setIdContrat(idContrat);
if (!listeIdsBenefsContrats.contains(idBenefContrat)) {
final GarantieBeneficiaireDto benef = new GarantieBeneficiaireDto();
benef.setIdBenef(idBenef);
benef.setIdContrat(idContrat);
benef.setRole(new IdentifiantLibelleDto((Long) result[2], String.valueOf(result[3])));
listeBeneficiaires.add(benef);
listeIdsBenefsContrats.add(idBenefContrat);
}
}
}
return listeBeneficiaires;
}