--- /dev/null
+/*
+ * Copyright © FYLAB and the Conseil Régional d'Île-de-France, 2009
+ * This file is part of L'Interface Libre et Interactive de l'Enseignement (Lilie).
+ *
+ * Lilie is free software. You can redistribute it and/or modify since
+ * you respect the terms of either (at least one of the both license) :
+ * - under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ * - the CeCILL-C as published by CeCILL-C; either version 1 of the
+ * License, or any later version
+ *
+ * There are special exceptions to the terms and conditions of the
+ * licenses as they are applied to this software. View the full text of
+ * the exception in file LICENSE.txt in the directory of this software
+ * distribution.
+ *
+ * Lilie is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * Licenses for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * and the CeCILL-C along with Lilie. If not, see :
+ * <http://www.gnu.org/licenses/> and
+ * <http://www.cecill.info/licences.fr.html>.
+ */
+
+package org.lilie.services.eliot.applications.absences.bilan
+
+class BilanRequetesConstantes {
+
+ public final static String ALL_APPEL = 'allAppel'
+
+ public static final String HQL_DE_BASE = """
+ select al, appel
+ from AppelLigne al
+ left join al.appel appel
+ left join appel.preferencesEtablissementAbsences pref
+ left join pref.etablissement etab
+ left join al.absenceJournee aj
+ left join aj.etablissement etab2
+ inner join al.autorite autorite
+ where al.autorite.id = :eleveId
+ and (
+ (
+ appel.id is not null
+ and etab.id = :etabCourantId
+ and appel.dateHeureDebut < :dateFin
+ and appel.dateHeureFin > :dateDebut
+ ) or (
+ aj.id is not null
+ and etab2.id = :etabCourantId
+ and (aj.date + al.heureDebut) < :dateFin
+ and (aj.date + al.heureFin) > :dateDebut
+ )
+ )
+ and al.absencePrevisionnelle = false
+ """
+
+ // juste un fetch en plus par rapport à HQL_DE_BASE
+ public static final String HQL_DE_BASE_FETCH_ABSENCE_JOURNEE = """
+ select al, appel
+ from AppelLigne al
+ left join al.appel appel
+ left join appel.preferencesEtablissementAbsences pref
+ left join pref.etablissement etab
+ left join fetch al.absenceJournee aj
+ left join aj.etablissement etab2
+ inner join al.autorite autorite
+ where al.autorite.id = :eleveId
+ and (
+ (
+ appel.id is not null
+ and etab.id = :etabCourantId
+ and appel.dateHeureDebut < :dateFin
+ and appel.dateHeureFin > :dateDebut
+ ) or (
+ aj.id is not null
+ and etab2.id = :etabCourantId
+ and (aj.date + al.heureDebut) < :dateFin
+ and (aj.date + al.heureFin) > :dateDebut
+ )
+ )
+ and al.absencePrevisionnelle = false
+ """
+
+ public static final String ORDER_BY = " order by appel.dateHeureDebut, aj.date, al.heureDebut"
+
+ public static final String HQL_ABSENCES_RETARDS = """
+ select distinct al, personne, a.id
+ from AppelLigne al
+ left join fetch al.appel appel
+ left join appel.preferencesEtablissementAbsences pref
+ left join pref.etablissement etab
+ left join fetch al.absenceJournee aj
+ left join aj.etablissement etab2
+ inner join al.autorite a,
+ Personne personne
+
+ where a.id in (:elevesIds)
+ and personne.autorite = a
+ and (
+ (
+ appel is not null
+ and etab.id = :etabCourantId
+ and appel.dateHeureFin <= :dateFin
+ and appel.dateHeureDebut >= :dateDebut
+ )
+ or
+ (
+ aj is not null
+ and etab2.id = :etabCourantId
+ and aj.date >= :dateDebut
+ and aj.date <= :dateFin
+ )
+ )
+ """
+
+
+ public static final String SQL_GET_APPELS = """
+ WITH allAppel AS (
+ (SELECT unnest(array[:allAppel]) AS id)
+ )
+
+ SELECT ap.* FROM enttemps.appel ap
+ INNER JOIN enttemps.appel_plage_horaire aph ON ap.id = aph.appel_id
+ INNER JOIN enttemps.plage_horaire ph ON aph.plage_horaire_id = ph.id
+ INNER JOIN allAppel aa ON ap.id = aa.id
+ GROUP BY ap.id;
+ """
+
+}