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.Date;
38  import java.sql.Statement;
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.portal.service.util.AppLogService;
46  import fr.paris.lutece.util.ReferenceList;
47  import fr.paris.lutece.util.sql.DAOUtil;
48  
49  /**
50   * This class provides Data Access methods for Inscrit objects
51   */
52  public final class InscritDAO implements IInscritDAO
53  {
54      // Constants
55      private static final String SQL_QUERY_SELECT           = "SELECT id_inscrit, nom, prenom, naissance, adresse_domicile, contact_nom, contact_prenom, contact_email, contact_telephone, source, date_inscription, decouverte, motivation, demarche, probable_arpege, decouverte_2, decouverte_3, decouverte_4, decouverte_5, decouverte_6, decouverte_7, motivation_2, motivation_3, motivation_4, demarche_2, demarche_3, demarche_4, guid FROM atelieraba_inscrit WHERE id_inscrit = ?";
56      private static final String SQL_QUERY_INSERT           = "INSERT INTO atelieraba_inscrit ( nom, prenom, naissance, adresse_domicile, contact_nom, contact_prenom,  contact_email, contact_telephone, source, date_inscription, decouverte, motivation, demarche, probable_arpege, decouverte_2, decouverte_3, decouverte_4, decouverte_5, decouverte_6, decouverte_7, motivation_2, motivation_3, motivation_4, demarche_2, demarche_3, demarche_4, guid) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
57      private static final String SQL_QUERY_DELETE           = "DELETE FROM atelieraba_inscrit WHERE id_inscrit = ? ";
58      private static final String SQL_QUERY_UPDATE           = "UPDATE atelieraba_inscrit SET id_inscrit = ?, nom = ?, prenom = ?, naissance = ?, adresse_domicile = ?, contact_nom = ?, contact_prenom = ?, contact_email = ?, contact_telephone = ?, source = ?, date_inscription = ?, decouverte = ?, motivation = ?, demarche = ?, probable_arpege = ? , decouverte_2 = ?, decouverte_3 = ?, decouverte_4 = ?, decouverte_5 = ?, decouverte_6 = ?, decouverte_7 = ?, motivation_2 = ?, motivation_3 = ?, motivation_4 = ?,  demarche_2 = ?, demarche_3 = ?, demarche_4 = ? , guid = ?  WHERE id_inscrit = ?";
59      private static final String SQL_QUERY_SELECTALL        = "SELECT id_inscrit, nom, prenom, naissance, adresse_domicile, contact_nom, contact_prenom,  contact_email, contact_telephone, source, date_inscription, decouverte, motivation, demarche, probable_arpege, decouverte_2, decouverte_3, decouverte_4, decouverte_5, decouverte_6, decouverte_7, motivation_2, motivation_3, motivation_4, demarche_2, demarche_3, demarche_4, guid FROM atelieraba_inscrit";
60      private static final String SQL_QUERY_SELECTALL_ID     = "SELECT id_inscrit FROM atelieraba_inscrit";
61      private static final String SQL_QUERY_SELECTALL_FILTER = "SELECT i.id_inscrit, i.nom, i.prenom, i.naissance, i.adresse_domicile, i.contact_nom, i.contact_prenom, i.contact_email,"
62              + " i.contact_telephone, i.source, i.date_inscription, i.decouverte, i.motivation, i.demarche, i.probable_arpege, t1.id_cours, t1.id_atelier, t2.id_cours, t2.id_atelier FROM atelieraba_inscrit i"
63              + " INNER JOIN (SELECT * FROM atelieraba_inscrit i1"
64              + " INNER JOIN atelieraba_atelier a1 ON i1.id_inscrit = a1.fk_id_inscrit AND a1.numero_atelier = 1 INNER JOIN atelieraba_cours c1 ON a1.fk_id_cours = c1.id_cours"
65              + " INNER JOIN workflow_resource_workflow w1 ON a1.id_atelier = w1.id_resource) t1 ON i.id_inscrit = t1.id_inscrit" + " LEFT JOIN (SELECT * FROM atelieraba_inscrit i2"
66              + " INNER JOIN atelieraba_atelier a2 ON i2.id_inscrit = a2.fk_id_inscrit AND a2.numero_atelier = 2 INNER JOIN atelieraba_cours c2 ON a2.fk_id_cours = c2.id_cours"
67              + " INNER JOIN workflow_resource_workflow w2 ON a2.id_atelier = w2.id_resource) t2 ON i.id_inscrit = t2.id_inscrit";
68      private static final String SQL_QUERY_DOUBLONS         = "SELECT id_inscrit from atelieraba_inscrit INNER JOIN atelieraba_atelier ON id_inscrit = fk_id_inscrit 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 WHERE NOT (w2.name LIKE '%(M)%') AND NOT (w2.name LIKE '%(D)%') AND nom = ? AND prenom = ? AND naissance = ?";
69      private static final String SQL_QUERY_SELECT_BY_GUID   = "SELECT id_inscrit, nom, prenom, naissance, adresse_domicile, contact_nom, contact_prenom, contact_email, contact_telephone, source, date_inscription, decouverte, motivation, demarche, probable_arpege, decouverte_2, decouverte_3, decouverte_4, decouverte_5, decouverte_6, decouverte_7, motivation_2, motivation_3, motivation_4, demarche_2, demarche_3, demarche_4, guid FROM atelieraba_inscrit WHERE guid = ?";
70  
71      // Variables
72      private static final String MARK_INSCRIT               = "inscrit";
73      private static final String MARK_COURS_1               = "premier_cours";
74      private static final String MARK_ATELIER_1             = "premier_atelier";
75      private static final String MARK_COURS_2               = "second_cours";
76      private static final String MARK_ATELIER_2             = "second_atelier";
77  
78      /**
79       * {@inheritDoc }
80       */
81      @Override
82      public void insert( Inscrit inscrit, Plugin plugin )
83      {
84          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin );
85          try
86          {
87              int nIndex = 1;
88              daoUtil.setString( nIndex++, inscrit.getNom( ) );
89              daoUtil.setString( nIndex++, inscrit.getPrenom( ) );
90              daoUtil.setDate( nIndex++, inscrit.getNaissance( ) );
91              daoUtil.setString( nIndex++, inscrit.getAdresseDomicile( ) );
92              daoUtil.setString( nIndex++, inscrit.getContactNom( ) );
93              daoUtil.setString( nIndex++, inscrit.getContactPrenom( ) );
94              daoUtil.setString( nIndex++, inscrit.getContactEmail( ) );
95              daoUtil.setString( nIndex++, inscrit.getContactTelephone( ) );
96              daoUtil.setString( nIndex++, inscrit.getSource( ) );
97              daoUtil.setTimestamp( nIndex++, inscrit.getDateInscription( ) );
98              daoUtil.setString( nIndex++, inscrit.getDecouverte( ) );
99              daoUtil.setString( nIndex++, inscrit.getMotivation( ) );
100             daoUtil.setString( nIndex++, inscrit.getDemarche( ) );
101             daoUtil.setBoolean( nIndex++, inscrit.getProbableArpege( ) );
102             daoUtil.setBoolean( nIndex++, inscrit.getDecouverte2( ) );
103             daoUtil.setBoolean( nIndex++, inscrit.getDecouverte3( ) );
104             daoUtil.setBoolean( nIndex++, inscrit.getDecouverte4( ) );
105             daoUtil.setBoolean( nIndex++, inscrit.getDecouverte5( ) );
106             daoUtil.setBoolean( nIndex++, inscrit.getDecouverte6( ) );
107             daoUtil.setBoolean( nIndex++, inscrit.getDecouverte7( ) );
108             daoUtil.setBoolean( nIndex++, inscrit.getMotivation2( ) );
109             daoUtil.setBoolean( nIndex++, inscrit.getMotivation3( ) );
110             daoUtil.setBoolean( nIndex++, inscrit.getMotivation4( ) );
111             daoUtil.setBoolean( nIndex++, inscrit.getDemarche2( ) );
112             daoUtil.setBoolean( nIndex++, inscrit.getDemarche3( ) );
113             daoUtil.setBoolean( nIndex++, inscrit.getDemarche4( ) );
114             daoUtil.setString( nIndex++, inscrit.getGuid( ) );
115 
116             daoUtil.executeUpdate( );
117             if ( daoUtil.nextGeneratedKey( ) )
118             {
119                 inscrit.setId( daoUtil.getGeneratedKeyInt( 1 ) );
120             }
121         } finally
122         {
123             daoUtil.free( );
124         }
125     }
126 
127     /**
128      * {@inheritDoc }
129      */
130     @Override
131     public Inscrit load( int nKey, Plugin plugin )
132     {
133         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
134         daoUtil.setInt( 1, nKey );
135         daoUtil.executeQuery( );
136         Inscrit inscrit = null;
137 
138         if ( daoUtil.next( ) )
139         {
140             inscrit = new Inscrit( );
141             int nIndex = 1;
142 
143             inscrit.setId( daoUtil.getInt( nIndex++ ) );
144             inscrit.setNom( daoUtil.getString( nIndex++ ) );
145             inscrit.setPrenom( daoUtil.getString( nIndex++ ) );
146             inscrit.setNaissance( daoUtil.getDate( nIndex++ ) );
147             inscrit.setAdresseDomicile( daoUtil.getString( nIndex++ ) );
148             inscrit.setContactNom( daoUtil.getString( nIndex++ ) );
149             inscrit.setContactPrenom( daoUtil.getString( nIndex++ ) );
150             inscrit.setContactEmail( daoUtil.getString( nIndex++ ) );
151             inscrit.setContactTelephone( daoUtil.getString( nIndex++ ) );
152             inscrit.setSource( daoUtil.getString( nIndex++ ) );
153             inscrit.setDateInscription( daoUtil.getTimestamp( nIndex++ ) );
154             inscrit.setDecouverte( daoUtil.getString( nIndex++ ) );
155             inscrit.setMotivation( daoUtil.getString( nIndex++ ) );
156             inscrit.setDemarche( daoUtil.getString( nIndex++ ) );
157             inscrit.setProbableArpege( daoUtil.getBoolean( nIndex++ ) );
158             inscrit.setDecouverte2( daoUtil.getBoolean( nIndex++ ) );
159             inscrit.setDecouverte3( daoUtil.getBoolean( nIndex++ ) );
160             inscrit.setDecouverte4( daoUtil.getBoolean( nIndex++ ) );
161             inscrit.setDecouverte5( daoUtil.getBoolean( nIndex++ ) );
162             inscrit.setDecouverte6( daoUtil.getBoolean( nIndex++ ) );
163             inscrit.setDecouverte7( daoUtil.getBoolean( nIndex++ ) );
164             inscrit.setMotivation2( daoUtil.getBoolean( nIndex++ ) );
165             inscrit.setMotivation3( daoUtil.getBoolean( nIndex++ ) );
166             inscrit.setMotivation4( daoUtil.getBoolean( nIndex++ ) );
167             inscrit.setDemarche2( daoUtil.getBoolean( nIndex++ ) );
168             inscrit.setDemarche3( daoUtil.getBoolean( nIndex++ ) );
169             inscrit.setDemarche4( daoUtil.getBoolean( nIndex++ ) );
170             inscrit.setGuid( daoUtil.getString( nIndex++ ) );
171         }
172 
173         daoUtil.free( );
174         return inscrit;
175     }
176 
177     /**
178      * {@inheritDoc }
179      */
180     @Override
181     public void delete( int nKey, Plugin plugin )
182     {
183         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
184         daoUtil.setInt( 1, nKey );
185         daoUtil.executeUpdate( );
186         daoUtil.free( );
187     }
188 
189     /**
190      * {@inheritDoc }
191      */
192     @Override
193     public void store( Inscrit inscrit, Plugin plugin )
194     {
195         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
196         int nIndex = 1;
197 
198         daoUtil.setInt( nIndex++, inscrit.getId( ) );
199         daoUtil.setString( nIndex++, inscrit.getNom( ) );
200         daoUtil.setString( nIndex++, inscrit.getPrenom( ) );
201         daoUtil.setDate( nIndex++, inscrit.getNaissance( ) );
202         daoUtil.setString( nIndex++, inscrit.getAdresseDomicile( ) );
203         daoUtil.setString( nIndex++, inscrit.getContactNom( ) );
204         daoUtil.setString( nIndex++, inscrit.getContactPrenom( ) );
205         daoUtil.setString( nIndex++, inscrit.getContactEmail( ) );
206         daoUtil.setString( nIndex++, inscrit.getContactTelephone( ) );
207         daoUtil.setString( nIndex++, inscrit.getSource( ) );
208         daoUtil.setTimestamp( nIndex++, inscrit.getDateInscription( ) );
209         daoUtil.setString( nIndex++, inscrit.getDecouverte( ) );
210         daoUtil.setString( nIndex++, inscrit.getMotivation( ) );
211         daoUtil.setString( nIndex++, inscrit.getDemarche( ) );
212         daoUtil.setBoolean( nIndex++, inscrit.getProbableArpege( ) );
213         daoUtil.setBoolean( nIndex++, inscrit.getDecouverte2( ) );
214         daoUtil.setBoolean( nIndex++, inscrit.getDecouverte3( ) );
215         daoUtil.setBoolean( nIndex++, inscrit.getDecouverte4( ) );
216         daoUtil.setBoolean( nIndex++, inscrit.getDecouverte5( ) );
217         daoUtil.setBoolean( nIndex++, inscrit.getDecouverte6( ) );
218         daoUtil.setBoolean( nIndex++, inscrit.getDecouverte7( ) );
219         daoUtil.setBoolean( nIndex++, inscrit.getMotivation2( ) );
220         daoUtil.setBoolean( nIndex++, inscrit.getMotivation3( ) );
221         daoUtil.setBoolean( nIndex++, inscrit.getMotivation4( ) );
222         daoUtil.setBoolean( nIndex++, inscrit.getDemarche2( ) );
223         daoUtil.setBoolean( nIndex++, inscrit.getDemarche3( ) );
224         daoUtil.setBoolean( nIndex++, inscrit.getDemarche4( ) );
225         daoUtil.setString( nIndex++, inscrit.getGuid( ) );
226         daoUtil.setInt( nIndex, inscrit.getId( ) );
227 
228         daoUtil.executeUpdate( );
229         daoUtil.free( );
230     }
231 
232     /**
233      * {@inheritDoc }
234      */
235     @Override
236     public List<Inscrit> selectInscritsList( Plugin plugin )
237     {
238         List<Inscrit> inscritList = new ArrayList<>( );
239         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
240         daoUtil.executeQuery( );
241 
242         while ( daoUtil.next( ) )
243         {
244             Inscrit inscrit = new Inscrit( );
245             int nIndex = 1;
246 
247             inscrit.setId( daoUtil.getInt( nIndex++ ) );
248             inscrit.setNom( daoUtil.getString( nIndex++ ) );
249             inscrit.setPrenom( daoUtil.getString( nIndex++ ) );
250             inscrit.setNaissance( daoUtil.getDate( nIndex++ ) );
251             inscrit.setAdresseDomicile( daoUtil.getString( nIndex++ ) );
252             inscrit.setContactNom( daoUtil.getString( nIndex++ ) );
253             inscrit.setContactPrenom( daoUtil.getString( nIndex++ ) );
254             inscrit.setContactEmail( daoUtil.getString( nIndex++ ) );
255             inscrit.setContactTelephone( daoUtil.getString( nIndex++ ) );
256             inscrit.setSource( daoUtil.getString( nIndex++ ) );
257             inscrit.setDateInscription( daoUtil.getTimestamp( nIndex++ ) );
258             inscrit.setDecouverte( daoUtil.getString( nIndex++ ) );
259             inscrit.setMotivation( daoUtil.getString( nIndex++ ) );
260             inscrit.setDemarche( daoUtil.getString( nIndex++ ) );
261             inscrit.setProbableArpege( daoUtil.getBoolean( nIndex++ ) );
262             inscrit.setDecouverte2( daoUtil.getBoolean( nIndex++ ) );
263             inscrit.setDecouverte3( daoUtil.getBoolean( nIndex++ ) );
264             inscrit.setDecouverte4( daoUtil.getBoolean( nIndex++ ) );
265             inscrit.setDecouverte5( daoUtil.getBoolean( nIndex++ ) );
266             inscrit.setDecouverte6( daoUtil.getBoolean( nIndex++ ) );
267             inscrit.setDecouverte7( daoUtil.getBoolean( nIndex++ ) );
268             inscrit.setMotivation2( daoUtil.getBoolean( nIndex++ ) );
269             inscrit.setMotivation3( daoUtil.getBoolean( nIndex++ ) );
270             inscrit.setMotivation4( daoUtil.getBoolean( nIndex++ ) );
271             inscrit.setDemarche2( daoUtil.getBoolean( nIndex++ ) );
272             inscrit.setDemarche3( daoUtil.getBoolean( nIndex++ ) );
273             inscrit.setDemarche4( daoUtil.getBoolean( nIndex++ ) );
274             inscrit.setGuid( daoUtil.getString( nIndex++ ) );
275 
276             inscritList.add( inscrit );
277         }
278 
279         daoUtil.free( );
280         return inscritList;
281     }
282 
283     /**
284      * {@inheritDoc }
285      */
286     @Override
287     public List<Integer> selectIdInscritsList( Plugin plugin )
288     {
289         List<Integer> inscritList = new ArrayList<>( );
290         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
291         daoUtil.executeQuery( );
292 
293         while ( daoUtil.next( ) )
294         {
295             inscritList.add( daoUtil.getInt( 1 ) );
296         }
297 
298         daoUtil.free( );
299         return inscritList;
300     }
301 
302     /**
303      * {@inheritDoc }
304      */
305     @Override
306     public ReferenceList selectInscritsReferenceList( Plugin plugin )
307     {
308         ReferenceList inscritList = new ReferenceList( );
309         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
310         daoUtil.executeQuery( );
311 
312         while ( daoUtil.next( ) )
313         {
314             inscritList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
315         }
316 
317         daoUtil.free( );
318         return inscritList;
319     }
320 
321     /**
322      * Applies the validatedFilters for export and search
323      *
324      * @param query
325      *            The begining of the query
326      * @param validatedFilters
327      *            The validatedFilters
328      * @param groupBy
329      *            whether the query adds a groupBy inscritId
330      * @return The sql statement
331      */
332     private String appendFilters( String query, Map<String[], String> validatedFilters )
333     {
334         StringBuilder stringBuilder = new StringBuilder( );
335         stringBuilder.append( query );
336         ArrayList<String[]> keys = new ArrayList<>( validatedFilters.keySet( ) );
337 
338         if ( !keys.isEmpty( ) )
339         {
340             stringBuilder.append( " WHERE " );
341             for ( String[] fieldDescription : keys )
342             {
343                 stringBuilder.append( fieldDescription[1] );
344                 stringBuilder.append( " AND " );
345             }
346             stringBuilder.setLength( stringBuilder.length( ) - 5 );
347         }
348         stringBuilder.append( " GROUP BY i.id_inscrit" );
349         stringBuilder.append( " ORDER BY i.id_inscrit" );
350 
351         return stringBuilder.toString( );
352     }
353 
354     /**
355      * Sets the validatedFilters values for export and search
356      *
357      * @param daoUtil
358      *            The daoUtil
359      * @param validatedFilters
360      *            The validatedFilters
361      */
362     private void setFilterValues( DAOUtil daoUtil, Map<String[], String> validatedFilters )
363     {
364         int index = 1;
365         ArrayList<String[]> keys = new ArrayList<>( validatedFilters.keySet( ) );
366         for ( String[] fieldDescription : keys )
367         {
368             String value = validatedFilters.get( fieldDescription );
369             if ( "STR_PATTERN".equals( fieldDescription[2] ) )
370             {
371                 daoUtil.setString( index, "%" + value.toLowerCase( ) + "%" );
372             } else if ( "STR".equals( fieldDescription[2] ) )
373             {
374                 daoUtil.setString( index, value );
375             } else if ( "INT".equals( fieldDescription[2] ) )
376             {
377                 try
378                 {
379                     daoUtil.setInt( index, Integer.parseInt( value ) );
380                 } catch ( NumberFormatException nfe )
381                 {
382                     AppLogService.error( "ATELIERABA: InscritDAO search error: shouldn't happen, value should be validated:" + nfe );
383                 }
384             } else if ( "DATE".equals( fieldDescription[2] ) )
385             {
386                 Date date = Date.valueOf( value.substring( 6, 10 ) + "-" + value.substring( 3, 5 ) + "-" + value.substring( 0, 2 ) );
387                 daoUtil.setDate( index, date );
388             } else if ( "BOOL".equals( fieldDescription[2] ) )
389             {
390                 daoUtil.setBoolean( index, Boolean.parseBoolean( value ) );
391             }
392             index++;
393         }
394     }
395 
396     /**
397      * {@inheritDoc }
398      */
399     @Override
400     public List<Map<String, Object>> selectInscritsListByFilter( Map<String[], String> validatedFilters, Plugin plugin )
401     {
402         List<Map<String, Object>> inscritList = new ArrayList<>( );
403         DAOUtil daoUtil = new DAOUtil( appendFilters( SQL_QUERY_SELECTALL_FILTER, validatedFilters ), plugin );
404 
405         if ( !validatedFilters.isEmpty( ) )
406         {
407             setFilterValues( daoUtil, validatedFilters );
408         }
409 
410         daoUtil.executeQuery( );
411 
412         while ( daoUtil.next( ) )
413         {
414             Inscrit inscrit = new Inscrit( );
415             int nIndex = 1;
416 
417             inscrit.setId( daoUtil.getInt( nIndex++ ) );
418             inscrit.setNom( daoUtil.getString( nIndex++ ) );
419             inscrit.setPrenom( daoUtil.getString( nIndex++ ) );
420             inscrit.setNaissance( daoUtil.getDate( nIndex++ ) );
421             inscrit.setAdresseDomicile( daoUtil.getString( nIndex++ ) );
422             inscrit.setContactNom( daoUtil.getString( nIndex++ ) );
423             inscrit.setContactPrenom( daoUtil.getString( nIndex++ ) );
424             inscrit.setContactEmail( daoUtil.getString( nIndex++ ) );
425             inscrit.setContactTelephone( daoUtil.getString( nIndex++ ) );
426             inscrit.setSource( daoUtil.getString( nIndex++ ) );
427             inscrit.setDateInscription( daoUtil.getTimestamp( nIndex++ ) );
428             inscrit.setDecouverte( daoUtil.getString( nIndex++ ) );
429             inscrit.setMotivation( daoUtil.getString( nIndex++ ) );
430             inscrit.setDemarche( daoUtil.getString( nIndex++ ) );
431             inscrit.setProbableArpege( daoUtil.getBoolean( nIndex++ ) );
432 
433             Map<String, Object> inscritWithAteliers = new HashMap<>( );
434             inscritWithAteliers.put( MARK_INSCRIT, inscrit );
435             inscritWithAteliers.put( MARK_COURS_1, daoUtil.getInt( nIndex++ ) );
436             inscritWithAteliers.put( MARK_ATELIER_1, daoUtil.getInt( nIndex++ ) );
437             inscritWithAteliers.put( MARK_COURS_2, daoUtil.getInt( nIndex++ ) );
438             inscritWithAteliers.put( MARK_ATELIER_2, daoUtil.getInt( nIndex++ ) );
439 
440             inscritList.add( inscritWithAteliers );
441         }
442 
443         daoUtil.free( );
444         return inscritList;
445     }
446 
447     /**
448      * {@inheritDoc }
449      */
450     @Override
451     public Integer getInscritDoublon( Inscrit inscrit, Plugin plugin )
452     {
453         int idInscrit = 0;
454         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DOUBLONS, plugin );
455         daoUtil.setString( 1, inscrit.getNom( ).trim( ) );
456         daoUtil.setString( 2, inscrit.getPrenom( ).trim( ) );
457         daoUtil.setDate( 3, inscrit.getNaissance( ) );
458 
459         daoUtil.executeQuery( );
460 
461         if ( daoUtil.next( ) )
462         {
463             idInscrit = daoUtil.getInt( 1 );
464         }
465 
466         daoUtil.free( );
467 
468         return idInscrit;
469     }
470 
471     /**
472      * {@inheritDoc }
473      */
474     @Override
475     public List<Inscrit> findByGuid( String strGuid, Plugin plugin )
476     {
477         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_GUID, plugin );
478         daoUtil.setString( 1, strGuid );
479         daoUtil.executeQuery( );
480         List<Inscrit> inscritList = new ArrayList<>( );
481 
482         while ( daoUtil.next( ) )
483         {
484             Inscrit inscrit = new Inscrit( );
485             int nIndex = 1;
486 
487             inscrit.setId( daoUtil.getInt( nIndex++ ) );
488             inscrit.setNom( daoUtil.getString( nIndex++ ) );
489             inscrit.setPrenom( daoUtil.getString( nIndex++ ) );
490             inscrit.setNaissance( daoUtil.getDate( nIndex++ ) );
491             inscrit.setAdresseDomicile( daoUtil.getString( nIndex++ ) );
492             inscrit.setContactNom( daoUtil.getString( nIndex++ ) );
493             inscrit.setContactPrenom( daoUtil.getString( nIndex++ ) );
494             inscrit.setContactEmail( daoUtil.getString( nIndex++ ) );
495             inscrit.setContactTelephone( daoUtil.getString( nIndex++ ) );
496             inscrit.setSource( daoUtil.getString( nIndex++ ) );
497             inscrit.setDateInscription( daoUtil.getTimestamp( nIndex++ ) );
498             inscrit.setDecouverte( daoUtil.getString( nIndex++ ) );
499             inscrit.setMotivation( daoUtil.getString( nIndex++ ) );
500             inscrit.setDemarche( daoUtil.getString( nIndex++ ) );
501             inscrit.setProbableArpege( daoUtil.getBoolean( nIndex++ ) );
502             inscrit.setDecouverte2( daoUtil.getBoolean( nIndex++ ) );
503             inscrit.setDecouverte3( daoUtil.getBoolean( nIndex++ ) );
504             inscrit.setDecouverte4( daoUtil.getBoolean( nIndex++ ) );
505             inscrit.setDecouverte5( daoUtil.getBoolean( nIndex++ ) );
506             inscrit.setDecouverte6( daoUtil.getBoolean( nIndex++ ) );
507             inscrit.setDecouverte7( daoUtil.getBoolean( nIndex++ ) );
508             inscrit.setMotivation2( daoUtil.getBoolean( nIndex++ ) );
509             inscrit.setMotivation3( daoUtil.getBoolean( nIndex++ ) );
510             inscrit.setMotivation4( daoUtil.getBoolean( nIndex++ ) );
511             inscrit.setDemarche2( daoUtil.getBoolean( nIndex++ ) );
512             inscrit.setDemarche3( daoUtil.getBoolean( nIndex++ ) );
513             inscrit.setDemarche4( daoUtil.getBoolean( nIndex++ ) );
514             inscrit.setGuid( daoUtil.getString( nIndex++ ) );
515 
516             inscritList.add( inscrit );
517         }
518 
519         daoUtil.free( );
520         return inscritList;
521     }
522 }