View Javadoc
1   /*
2    * Copyright (c) 2002-2019, Mairie de Paris
3    * All rights reserved.
4    *
5    * Redistribution and use in source and binary forms, with or without
6    * modification, are permitted provided that the following conditions
7    * are met:
8    *
9    *  1. Redistributions of source code must retain the above copyright notice
10   *     and the following disclaimer.
11   *
12   *  2. Redistributions in binary form must reproduce the above copyright notice
13   *     and the following disclaimer in the documentation and/or other materials
14   *     provided with the distribution.
15   *
16   *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
17   *     contributors may be used to endorse or promote products derived from
18   *     this software without specific prior written permission.
19   *
20   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
21   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23   * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
24   * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
25   * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
26   * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
27   * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
28   * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
29   * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
30   * POSSIBILITY OF SUCH DAMAGE.
31   *
32   * License 1.0
33   */
34  
35  package fr.paris.lutece.plugins.atelieraba.business;
36  
37  import java.sql.Statement;
38  import java.sql.Timestamp;
39  import java.util.ArrayList;
40  import java.util.HashMap;
41  import java.util.List;
42  import java.util.Map;
43  
44  import fr.paris.lutece.portal.service.plugin.Plugin;
45  import fr.paris.lutece.util.ReferenceList;
46  import fr.paris.lutece.util.sql.DAOUtil;
47  
48  /**
49   * This class provides Data Access methods for Cours objects
50   */
51  public final class CoursDAO implements ICoursDAO
52  {
53      // Constants
54      private static final String SQL_QUERY_SELECT                   = "SELECT id_cours, discipline, titre_atelier, creneau, places_disponibles, places_liste_attente, description_atelier, site FROM atelieraba_cours WHERE id_cours = ?";
55      private static final String SQL_QUERY_SELECTCSV                = "SELECT id_cours, discipline, titre_atelier, creneau, places_disponibles, places_liste_attente, description_atelier, site FROM atelieraba_cours WHERE discipline = ? and titre_atelier = ? and creneau = ?";
56      private static final String SQL_QUERY_INSERT                   = "INSERT INTO atelieraba_cours ( discipline, titre_atelier, creneau, places_disponibles, places_liste_attente, description_atelier, site ) VALUES ( ?, ?, ?, ?, ?, ?, ?) ";
57      private static final String SQL_QUERY_DELETE                   = "DELETE FROM atelieraba_cours WHERE id_cours = ? ";
58      private static final String SQL_QUERY_UPDATE                   = "UPDATE atelieraba_cours SET id_cours = ?, discipline = ?, titre_atelier = ?, creneau = ?, places_disponibles = ?, places_liste_attente = ?, description_atelier = ?, site = ? WHERE id_cours = ?";
59      private static final String SQL_QUERY_SELECTALL                = "SELECT id_cours, discipline, titre_atelier, creneau, places_disponibles, places_liste_attente, description_atelier, site FROM atelieraba_cours";
60      private static final String SQL_QUERY_SELECTALL_ID             = "SELECT id_cours FROM atelieraba_cours";
61      private static final String SQL_QUERY_SELECT_DISCIPLINES       = "SELECT discipline FROM atelieraba_cours GROUP BY discipline ORDER BY discipline";
62      private static final String SQL_QUERY_SELECT_ATELIERS          = "SELECT titre_atelier FROM atelieraba_cours GROUP BY titre_atelier ORDER BY titre_atelier";
63      private static final String SQL_QUERY_SELECT_CRENEAUX          = "SELECT creneau FROM atelieraba_cours GROUP BY creneau ORDER BY creneau";
64      private static final String SQL_QUERY_SELECT_NB                = "select aa.fk_id_cours, sum(case when wrf.id_state=2 or wrf.id_state=4 or wrf.id_state=10 then 1 else 0 end) convocations, sum(case when wrf.id_state=1 or wrf.id_state=3 then 1 else 0 end) demandesSuspens1,	sum(case when wrf.id_state=9 then 1 else 0 end) demandesSuspens2, sum(case when wrf.id_state=5 or wrf.id_state=6 then 1 else 0 end) rejets, sum(case when wrf.id_state=8 then 1 else 0 end) desistement from CORE.workflow_resource_workflow wrf, CORE.atelieraba_atelier aa where  wrf.id_resource=aa.id_atelier group by aa.fk_id_cours";
65      private static final String SQL_QUERY_COUNT_PLACES_VIVANTES    = "SELECT x.id_cours, IFNULL(y.places_vivantes, 0) FROM atelieraba_cours x LEFT JOIN (SELECT id_cours, COUNT(id_atelier) AS places_vivantes FROM atelieraba_cours"
66              + " INNER JOIN atelieraba_atelier ON id_cours = fk_id_cours AND numero_atelier = 1 INNER JOIN workflow_resource_workflow w1 ON id_atelier = w1.id_resource INNER JOIN workflow_state w2 ON w1.id_state = w2.id_state"
67              + " WHERE (w2.name LIKE '%(A)%' OR w2.name LIKE '%(C)%' OR w2.name LIKE '%(X)%') GROUP BY id_cours) y ON x.id_cours = y.id_cours";
68      private static final String SQL_QUERY_ACCES_PLACES_DISPONIBLES = "SELECT IFNULL(y.places_vivantes, 0) < x.places_disponibles FROM atelieraba_cours x LEFT JOIN (SELECT id_cours, IFNULL(COUNT(id_atelier), 0) AS places_vivantes, places_disponibles FROM atelieraba_cours INNER JOIN atelieraba_atelier ON id_cours = fk_id_cours AND numero_atelier = 1 INNER JOIN atelieraba_inscrit ON id_inscrit = fk_id_inscrit INNER JOIN workflow_resource_workflow w1 ON id_atelier = w1.id_resource INNER JOIN workflow_state w2 ON w1.id_state = w2.id_state WHERE (w2.name LIKE '%(A)%' OR w2.name LIKE '%(C)%' OR w2.name LIKE '%(X)%') AND date_inscription < ? GROUP BY id_cours ) y ON x.id_cours = y.id_cours WHERE x.id_cours = ?";
69  
70      /**
71       * {@inheritDoc }
72       */
73      @Override
74      public void insert( Cours cours, Plugin plugin )
75      {
76          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin );
77          try
78          {
79              int nIndex = 1;
80              daoUtil.setString( nIndex++, cours.getDiscipline( ) );
81              daoUtil.setString( nIndex++, cours.getTitreAtelier( ) );
82              daoUtil.setString( nIndex++, cours.getCreneau( ) );
83              daoUtil.setInt( nIndex++, cours.getPlacesDisponibles( ) );
84              daoUtil.setInt( nIndex++, cours.getPlacesListeAttente( ) );
85              daoUtil.setString( nIndex++, cours.getDescriptionAtelier( ) );
86              daoUtil.setString( nIndex++, cours.getSite( ) );
87  
88              daoUtil.executeUpdate( );
89              if ( daoUtil.nextGeneratedKey( ) )
90              {
91                  cours.setId( daoUtil.getGeneratedKeyInt( 1 ) );
92              }
93          } finally
94          {
95              daoUtil.free( );
96          }
97      }
98  
99      /**
100      * {@inheritDoc }
101      */
102     @Override
103     public Cours load( int nKey, Plugin plugin )
104     {
105         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
106         daoUtil.setInt( 1, nKey );
107         daoUtil.executeQuery( );
108         Cours cours = null;
109 
110         if ( daoUtil.next( ) )
111         {
112             cours = new Cours( );
113             int nIndex = 1;
114 
115             cours.setId( daoUtil.getInt( nIndex++ ) );
116             cours.setDiscipline( daoUtil.getString( nIndex++ ) );
117             cours.setTitreAtelier( daoUtil.getString( nIndex++ ) );
118             cours.setCreneau( daoUtil.getString( nIndex++ ) );
119             cours.setPlacesDisponibles( daoUtil.getInt( nIndex++ ) );
120             cours.setPlacesListeAttente( daoUtil.getInt( nIndex++ ) );
121             cours.setDescriptionAtelier( daoUtil.getString( nIndex++ ) );
122             cours.setSite( daoUtil.getString( nIndex++ ) );
123         }
124 
125         daoUtil.free( );
126         return cours;
127     }
128 
129     /**
130      * {@inheritDoc }
131      */
132     @Override
133     public void delete( int nKey, Plugin plugin )
134     {
135         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
136         daoUtil.setInt( 1, nKey );
137         daoUtil.executeUpdate( );
138         daoUtil.free( );
139     }
140 
141     /**
142      * {@inheritDoc }
143      */
144     @Override
145     public void store( Cours cours, Plugin plugin )
146     {
147         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
148         int nIndex = 1;
149 
150         daoUtil.setInt( nIndex++, cours.getId( ) );
151         daoUtil.setString( nIndex++, cours.getDiscipline( ) );
152         daoUtil.setString( nIndex++, cours.getTitreAtelier( ) );
153         daoUtil.setString( nIndex++, cours.getCreneau( ) );
154         daoUtil.setInt( nIndex++, cours.getPlacesDisponibles( ) );
155         daoUtil.setInt( nIndex++, cours.getPlacesListeAttente( ) );
156         daoUtil.setString( nIndex++, cours.getDescriptionAtelier( ) );
157         daoUtil.setString( nIndex++, cours.getSite( ) );
158         daoUtil.setInt( nIndex, cours.getId( ) );
159 
160         daoUtil.executeUpdate( );
161         daoUtil.free( );
162     }
163 
164     /**
165      * {@inheritDoc }
166      */
167     @Override
168     public List<Cours> selectCourssList( Plugin plugin )
169     {
170         List<Cours> coursList = new ArrayList<Cours>( );
171         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
172         daoUtil.executeQuery( );
173 
174         while ( daoUtil.next( ) )
175         {
176             Cours cours = new Cours( );
177             int nIndex = 1;
178 
179             cours.setId( daoUtil.getInt( nIndex++ ) );
180             cours.setDiscipline( daoUtil.getString( nIndex++ ) );
181             cours.setTitreAtelier( daoUtil.getString( nIndex++ ) );
182             cours.setCreneau( daoUtil.getString( nIndex++ ) );
183             cours.setPlacesDisponibles( daoUtil.getInt( nIndex++ ) );
184             cours.setPlacesListeAttente( daoUtil.getInt( nIndex++ ) );
185             cours.setDescriptionAtelier( daoUtil.getString( nIndex++ ) );
186             cours.setSite( daoUtil.getString( nIndex++ ) );
187 
188             coursList.add( cours );
189         }
190 
191         daoUtil.free( );
192         return coursList;
193     }
194 
195     /**
196      * {@inheritDoc }
197      */
198     @Override
199     public List<Integer> selectIdCourssList( Plugin plugin )
200     {
201         List<Integer> coursList = new ArrayList<Integer>( );
202         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
203         daoUtil.executeQuery( );
204 
205         while ( daoUtil.next( ) )
206         {
207             coursList.add( daoUtil.getInt( 1 ) );
208         }
209 
210         daoUtil.free( );
211         return coursList;
212     }
213 
214     /**
215      * {@inheritDoc }
216      */
217     @Override
218     public ReferenceList selectCourssReferenceList( Plugin plugin )
219     {
220         ReferenceList coursList = new ReferenceList( );
221         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
222         daoUtil.executeQuery( );
223 
224         while ( daoUtil.next( ) )
225         {
226             coursList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
227         }
228 
229         daoUtil.free( );
230         return coursList;
231     }
232 
233     /**
234      * {@inheritDoc }
235      */
236     @Override
237     public Cours selectByCode( String discipline, String atelier, String creneau, Plugin plugin )
238     {
239         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTCSV, plugin );
240         daoUtil.setString( 1, discipline );
241         daoUtil.setString( 2, atelier );
242         daoUtil.setString( 3, creneau );
243         daoUtil.executeQuery( );
244         Cours cours = null;
245 
246         if ( daoUtil.next( ) )
247         {
248             cours = new Cours( );
249             int nIndex = 1;
250 
251             cours.setId( daoUtil.getInt( nIndex++ ) );
252             cours.setDiscipline( daoUtil.getString( nIndex++ ) );
253             cours.setTitreAtelier( daoUtil.getString( nIndex++ ) );
254             cours.setCreneau( daoUtil.getString( nIndex++ ) );
255             cours.setPlacesDisponibles( daoUtil.getInt( nIndex++ ) );
256             cours.setPlacesListeAttente( daoUtil.getInt( nIndex++ ) );
257             cours.setDescriptionAtelier( daoUtil.getString( nIndex++ ) );
258             cours.setSite( daoUtil.getString( nIndex++ ) );
259         }
260 
261         daoUtil.free( );
262         return cours;
263     }
264 
265     /**
266      * {@inheritDoc }
267      */
268     @Override
269     public List<String> selectDisciplinesList( Plugin plugin )
270     {
271         List<String> disciplinesList = new ArrayList<String>( );
272         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DISCIPLINES, plugin );
273         daoUtil.executeQuery( );
274 
275         while ( daoUtil.next( ) )
276         {
277             disciplinesList.add( daoUtil.getString( 1 ) );
278         }
279 
280         daoUtil.free( );
281         return disciplinesList;
282     }
283 
284     /**
285      * {@inheritDoc }
286      */
287     @Override
288     public List<String> selectAteliersList( Plugin plugin )
289     {
290         List<String> ateliersList = new ArrayList<String>( );
291         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATELIERS, plugin );
292         daoUtil.executeQuery( );
293 
294         while ( daoUtil.next( ) )
295         {
296             ateliersList.add( daoUtil.getString( 1 ) );
297         }
298 
299         daoUtil.free( );
300         return ateliersList;
301     }
302 
303     /**
304      * {@inheritDoc }
305      */
306     @Override
307     public List<String> selectCreneauxList( Plugin plugin )
308     {
309         List<String> creneauxList = new ArrayList<String>( );
310         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CRENEAUX, plugin );
311         daoUtil.executeQuery( );
312 
313         while ( daoUtil.next( ) )
314         {
315             creneauxList.add( daoUtil.getString( 1 ) );
316         }
317 
318         daoUtil.free( );
319         return creneauxList;
320     }
321 
322     /**
323      * {@inheritDoc }
324      */
325     @Override
326     public Map<Integer, CoursCounter> selectNbInscritsByStateByCours( Plugin plugin )
327     {
328         Map<Integer, CoursCounter> coursCounterMap = new HashMap<>( );
329 
330         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NB, plugin );
331         daoUtil.executeQuery( );
332 
333         while ( daoUtil.next( ) )
334         {
335             CoursCounter cc = new CoursCounter( );
336 
337             cc.setId( daoUtil.getInt( 1 ) );
338             cc.setConvocations( daoUtil.getInt( 2 ) );
339             cc.setDemandesSuspens1( daoUtil.getInt( 3 ) );
340             cc.setDemandesSuspens2( daoUtil.getInt( 4 ) );
341             cc.setRejets( daoUtil.getInt( 5 ) );
342             cc.setDesistement( daoUtil.getInt( 6 ) );
343 
344             coursCounterMap.put( cc.getId( ), cc );
345         }
346 
347         daoUtil.free( );
348         return coursCounterMap;
349     }
350 
351     /**
352      * {@inheritDoc }
353      */
354     @Override
355     public Map<Integer, Integer> countPlacesVivantesList( Plugin plugin )
356     {
357         Map<Integer, Integer> placesVivantesList = new HashMap<>( );
358         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_PLACES_VIVANTES, plugin );
359         daoUtil.executeQuery( );
360 
361         while ( daoUtil.next( ) )
362         {
363             placesVivantesList.put( daoUtil.getInt( 1 ), daoUtil.getInt( 2 ) );
364         }
365 
366         daoUtil.free( );
367         return placesVivantesList;
368     }
369 
370     /**
371      * {@inheritDoc }
372      */
373     @Override
374     public boolean accesPlacesDisponibles( Plugin plugin, Timestamp dateInscription, int idCours )
375     {
376         boolean disponible = true;
377         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ACCES_PLACES_DISPONIBLES, plugin );
378         daoUtil.setTimestamp( 1, dateInscription );
379         daoUtil.setInt( 2, idCours );
380         daoUtil.executeQuery( );
381 
382         if ( daoUtil.next( ) )
383         {
384             disponible = daoUtil.getBoolean( 1 );
385         }
386 
387         daoUtil.free( );
388         return disponible;
389     }
390 }