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.util.ArrayList;
39  import java.util.List;
40  
41  import fr.paris.lutece.portal.service.plugin.Plugin;
42  import fr.paris.lutece.util.ReferenceList;
43  import fr.paris.lutece.util.sql.DAOUtil;
44  
45  /**
46   * This class provides Data Access methods for EleveArpege objects
47   */
48  public final class EleveArpegeDAO implements IEleveArpegeDAO
49  {
50      // Constants
51      private static final String SQL_QUERY_SELECT                = "SELECT id_eleve_arpege, nom, prenom, email_contact, telephone_contact FROM atelieraba_eleve_arpege WHERE id_eleve_arpege = ?";
52      private static final String SQL_QUERY_SELECTCSV             = "SELECT id_eleve_arpege, nom, prenom, email_contact, telephone_contact FROM atelieraba_eleve_arpege WHERE nom = ? and prenom = ? and email_contact = ? and telephone_contact = ?";
53      private static final String SQL_QUERY_INSERT                = "INSERT INTO atelieraba_eleve_arpege ( nom, prenom, email_contact, telephone_contact ) VALUES ( ?, ?, ?, ? ) ";
54      private static final String SQL_QUERY_DELETE                = "DELETE FROM atelieraba_eleve_arpege WHERE id_eleve_arpege = ? ";
55      private static final String SQL_QUERY_UPDATE                = "UPDATE atelieraba_eleve_arpege SET id_eleve_arpege = ?, nom = ?, prenom = ?, email_contact = ?, telephone_contact = ? WHERE id_eleve_arpege = ?";
56      private static final String SQL_QUERY_SELECTALL             = "SELECT id_eleve_arpege, nom, prenom, email_contact, telephone_contact FROM atelieraba_eleve_arpege";
57      private static final String SQL_QUERY_SELECTALL_ID          = "SELECT id_eleve_arpege FROM atelieraba_eleve_arpege";
58      private static final String SQL_QUERY_SELECT_ELEVE_PROBABLE = "SELECT id_eleve_arpege FROM atelieraba_eleve_arpege WHERE email_contact = ? or telephone_contact = ?";
59  
60      /**
61       * {@inheritDoc }
62       */
63      @Override
64      public void insert( EleveArpege eleveArpege, Plugin plugin )
65      {
66          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin );
67          try
68          {
69              int nIndex = 1;
70              daoUtil.setString( nIndex++, eleveArpege.getNom( ) );
71              daoUtil.setString( nIndex++, eleveArpege.getPrenom( ) );
72              daoUtil.setString( nIndex++, eleveArpege.getEmailContact( ) );
73              daoUtil.setString( nIndex++, eleveArpege.getTelephoneContact( ) );
74  
75              daoUtil.executeUpdate( );
76              if ( daoUtil.nextGeneratedKey( ) )
77              {
78                  eleveArpege.setId( daoUtil.getGeneratedKeyInt( 1 ) );
79              }
80          } finally
81          {
82              daoUtil.free( );
83          }
84      }
85  
86      /**
87       * {@inheritDoc }
88       */
89      @Override
90      public EleveArpege load( int nKey, Plugin plugin )
91      {
92          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
93          daoUtil.setInt( 1, nKey );
94          daoUtil.executeQuery( );
95          EleveArpege eleveArpege = null;
96  
97          if ( daoUtil.next( ) )
98          {
99              eleveArpege = new EleveArpege( );
100             int nIndex = 1;
101 
102             eleveArpege.setId( daoUtil.getInt( nIndex++ ) );
103             eleveArpege.setNom( daoUtil.getString( nIndex++ ) );
104             eleveArpege.setPrenom( daoUtil.getString( nIndex++ ) );
105             eleveArpege.setEmailContact( daoUtil.getString( nIndex++ ) );
106             eleveArpege.setTelephoneContact( daoUtil.getString( nIndex++ ) );
107         }
108 
109         daoUtil.free( );
110         return eleveArpege;
111     }
112 
113     /**
114      * {@inheritDoc }
115      */
116     @Override
117     public void delete( int nKey, Plugin plugin )
118     {
119         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
120         daoUtil.setInt( 1, nKey );
121         daoUtil.executeUpdate( );
122         daoUtil.free( );
123     }
124 
125     /**
126      * {@inheritDoc }
127      */
128     @Override
129     public void store( EleveArpege eleveArpege, Plugin plugin )
130     {
131         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
132         int nIndex = 1;
133 
134         daoUtil.setInt( nIndex++, eleveArpege.getId( ) );
135         daoUtil.setString( nIndex++, eleveArpege.getNom( ) );
136         daoUtil.setString( nIndex++, eleveArpege.getPrenom( ) );
137         daoUtil.setString( nIndex++, eleveArpege.getEmailContact( ) );
138         daoUtil.setString( nIndex++, eleveArpege.getTelephoneContact( ) );
139         daoUtil.setInt( nIndex, eleveArpege.getId( ) );
140 
141         daoUtil.executeUpdate( );
142         daoUtil.free( );
143     }
144 
145     /**
146      * {@inheritDoc }
147      */
148     @Override
149     public List<EleveArpege> selectEleveArpegesList( Plugin plugin )
150     {
151         List<EleveArpege> eleveArpegeList = new ArrayList<EleveArpege>( );
152         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
153         daoUtil.executeQuery( );
154 
155         while ( daoUtil.next( ) )
156         {
157             EleveArpege eleveArpege = new EleveArpege( );
158             int nIndex = 1;
159 
160             eleveArpege.setId( daoUtil.getInt( nIndex++ ) );
161             eleveArpege.setNom( daoUtil.getString( nIndex++ ) );
162             eleveArpege.setPrenom( daoUtil.getString( nIndex++ ) );
163             eleveArpege.setEmailContact( daoUtil.getString( nIndex++ ) );
164             eleveArpege.setTelephoneContact( daoUtil.getString( nIndex++ ) );
165 
166             eleveArpegeList.add( eleveArpege );
167         }
168 
169         daoUtil.free( );
170         return eleveArpegeList;
171     }
172 
173     /**
174      * {@inheritDoc }
175      */
176     @Override
177     public List<Integer> selectIdEleveArpegesList( Plugin plugin )
178     {
179         List<Integer> eleveArpegeList = new ArrayList<Integer>( );
180         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
181         daoUtil.executeQuery( );
182 
183         while ( daoUtil.next( ) )
184         {
185             eleveArpegeList.add( daoUtil.getInt( 1 ) );
186         }
187 
188         daoUtil.free( );
189         return eleveArpegeList;
190     }
191 
192     /**
193      * {@inheritDoc }
194      */
195     @Override
196     public ReferenceList selectEleveArpegesReferenceList( Plugin plugin )
197     {
198         ReferenceList eleveArpegeList = new ReferenceList( );
199         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
200         daoUtil.executeQuery( );
201 
202         while ( daoUtil.next( ) )
203         {
204             eleveArpegeList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
205         }
206 
207         daoUtil.free( );
208         return eleveArpegeList;
209     }
210 
211     /**
212      * {@inheritDoc }
213      */
214     @Override
215     public EleveArpege selectByCode( String nom, String prenom, String mail, String telephone, Plugin plugin )
216     {
217         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTCSV, plugin );
218         daoUtil.setString( 1, nom );
219         daoUtil.setString( 2, prenom );
220         daoUtil.setString( 3, telephone );
221         daoUtil.setString( 4, mail );
222         daoUtil.executeQuery( );
223         EleveArpege eleveArpege = null;
224 
225         if ( daoUtil.next( ) )
226         {
227             eleveArpege = new EleveArpege( );
228             int nIndex = 1;
229 
230             eleveArpege.setId( daoUtil.getInt( nIndex++ ) );
231             eleveArpege.setNom( daoUtil.getString( nIndex++ ) );
232             eleveArpege.setPrenom( daoUtil.getString( nIndex++ ) );
233             eleveArpege.setTelephoneContact( daoUtil.getString( nIndex++ ) );
234             eleveArpege.setEmailContact( daoUtil.getString( nIndex++ ) );
235         }
236 
237         daoUtil.free( );
238         return eleveArpege;
239     }
240 
241     /**
242      * {@inheritDoc }
243      */
244     @Override
245     public List<Integer> selectEleveProbable( String email, String telephone, Plugin plugin )
246     {
247         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ELEVE_PROBABLE, plugin );
248         daoUtil.setString( 1, email );
249         daoUtil.setString( 2, telephone );
250         daoUtil.executeQuery( );
251 
252         List<Integer> idList = new ArrayList<>( );
253         if ( daoUtil.next( ) )
254         {
255             idList.add( daoUtil.getInt( 1 ) );
256         }
257 
258         daoUtil.free( );
259         return idList;
260     }
261 }