View Javadoc
1   /*
2    * Copyright (c) 2002-2018, 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  package fr.paris.lutece.plugins.appointment.business.slot;
35  
36  import java.sql.Timestamp;
37  import java.time.LocalDateTime;
38  import java.util.ArrayList;
39  import java.util.List;
40  
41  import fr.paris.lutece.plugins.appointment.business.UtilDAO;
42  import fr.paris.lutece.portal.service.plugin.Plugin;
43  import fr.paris.lutece.util.sql.DAOUtil;
44  
45  /**
46   * This class provides Data Access methods for Slot objects
47   * 
48   * @author Laurent Payen
49   *
50   */
51  public final class SlotDAO extends UtilDAO implements ISlotDAO
52  {
53  
54      private static final String SQL_QUERY_NEW_PK = "SELECT max(id_slot) FROM appointment_slot";
55      private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_slot (id_slot, starting_date_time, ending_date_time, is_open, is_specific, max_capacity, nb_remaining_places, nb_potential_remaining_places, nb_places_taken, id_form) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
56      private static final String SQL_QUERY_UPDATE = "UPDATE appointment_slot SET starting_date_time = ?, ending_date_time = ?, is_open = ?, is_specific = ?, max_capacity = ?, nb_remaining_places = ?, nb_potential_remaining_places = ?, nb_places_taken = ?, id_form = ? WHERE id_slot = ?";
57      private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_slot WHERE id_slot = ?";
58      private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT id_slot, starting_date_time, ending_date_time, is_open, is_specific, max_capacity, nb_remaining_places, nb_potential_remaining_places, nb_places_taken, id_form ";
59      private static final String SQL_FROM_APPOINTMENT_SLOT = "FROM appointment_slot";
60      private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT + " WHERE id_slot = ?";
61      private static final String SQL_QUERY_SELECT_BY_ID_FORM = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT + " WHERE id_form = ?";
62      private static final String SQL_QUERY_SELECT_BY_ID_FORM_AND_IS_SPECIFIC = SQL_QUERY_SELECT_BY_ID_FORM + " AND is_specific = 1";
63      private static final String SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_RANGE = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT
64              + " WHERE id_form = ? AND starting_date_time >= ? AND ending_date_time <= ?";
65      private static final String SQL_QUERY_SELECT_OPEN_SLOTS_BY_ID_FORM_AND_DATE_RANGE = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT
66              + " WHERE id_form = ? AND starting_date_time >= ? AND ending_date_time <= ? AND is_open = 1";
67      private static final String SQL_QUERY_SELECT_OPEN_SLOTS_BY_ID_FORM = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT
68              + " WHERE id_form = ? AND is_open = 1";
69      private static final String SQL_QUERY_SELECT_SLOT_WITH_MAX_DATE = SQL_QUERY_SELECT_COLUMNS + "FROM appointment_slot slot"
70              + " WHERE slot.id_form = ? ORDER BY slot.starting_date_time DESC LIMIT 1";
71  
72      @Override
73      public synchronized void insert( Slot slot, Plugin plugin )
74      {
75          slot.setIdSlot( getNewPrimaryKey( SQL_QUERY_NEW_PK, plugin ) );
76          DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, slot, plugin, true );
77          executeUpdate( daoUtil );
78      }
79  
80      @Override
81      public void update( Slot slot, Plugin plugin )
82      {
83          DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, slot, plugin, false );
84          executeUpdate( daoUtil );
85      }
86  
87      @Override
88      public void delete( int nIdSlot, Plugin plugin )
89      {
90          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
91          daoUtil.setInt( 1, nIdSlot );
92          executeUpdate( daoUtil );
93      }
94  
95      @Override
96      public Slot select( int nIdSlot, Plugin plugin )
97      {
98          DAOUtil daoUtil = null;
99          Slot slot = null;
100         try
101         {
102             daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
103             daoUtil.setInt( 1, nIdSlot );
104             daoUtil.executeQuery( );
105             if ( daoUtil.next( ) )
106             {
107                 slot = buildSlot( daoUtil );
108             }
109         }
110         finally
111         {
112             if ( daoUtil != null )
113             {
114                 daoUtil.free( );
115             }
116         }
117         return slot;
118     }
119 
120     @Override
121     public List<Slot> findByIdFormAndDateRange( int nIdForm, LocalDateTime startingDateTime, LocalDateTime endingDateTime, Plugin plugin )
122     {
123         DAOUtil daoUtil = null;
124         List<Slot> listSlots = new ArrayList<>( );
125         try
126         {
127             daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_RANGE, plugin );
128             daoUtil.setInt( 1, nIdForm );
129             daoUtil.setTimestamp( 2, Timestamp.valueOf( startingDateTime ) );
130             daoUtil.setTimestamp( 3, Timestamp.valueOf( endingDateTime ) );
131             daoUtil.executeQuery( );
132             while ( daoUtil.next( ) )
133             {
134                 listSlots.add( buildSlot( daoUtil ) );
135             }
136         }
137         finally
138         {
139             if ( daoUtil != null )
140             {
141                 daoUtil.free( );
142             }
143         }
144         return listSlots;
145     }
146 
147     @Override
148     public List<Slot> findIsSpecificByIdForm( int nIdForm, Plugin plugin )
149     {
150         DAOUtil daoUtil = null;
151         List<Slot> listSpecificSlots = new ArrayList<>( );
152         try
153         {
154             daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM_AND_IS_SPECIFIC, plugin );
155             daoUtil.setInt( 1, nIdForm );
156             daoUtil.executeQuery( );
157             while ( daoUtil.next( ) )
158             {
159                 listSpecificSlots.add( buildSlot( daoUtil ) );
160             }
161         }
162         finally
163         {
164             if ( daoUtil != null )
165             {
166                 daoUtil.free( );
167             }
168         }
169         return listSpecificSlots;
170     }
171 
172     @Override
173     public List<Slot> findByIdForm( int nIdForm, Plugin plugin )
174     {
175         DAOUtil daoUtil = null;
176         List<Slot> listSlot = new ArrayList<>( );
177         try
178         {
179             daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM, plugin );
180             daoUtil.setInt( 1, nIdForm );
181             daoUtil.executeQuery( );
182             while ( daoUtil.next( ) )
183             {
184                 listSlot.add( buildSlot( daoUtil ) );
185             }
186         }
187         finally
188         {
189             if ( daoUtil != null )
190             {
191                 daoUtil.free( );
192             }
193         }
194         return listSlot;
195     }
196 
197     @Override
198     public List<Slot> findOpenSlotsByIdFormAndDateRange( int nIdForm, LocalDateTime startingDateTime, LocalDateTime endingDateTime, Plugin plugin )
199     {
200         DAOUtil daoUtil = null;
201         List<Slot> listSLot = new ArrayList<>( );
202         try
203         {
204             daoUtil = new DAOUtil( SQL_QUERY_SELECT_OPEN_SLOTS_BY_ID_FORM_AND_DATE_RANGE, plugin );
205             daoUtil.setInt( 1, nIdForm );
206             daoUtil.setTimestamp( 2, Timestamp.valueOf( startingDateTime ) );
207             daoUtil.setTimestamp( 3, Timestamp.valueOf( endingDateTime ) );
208             daoUtil.executeQuery( );
209             while ( daoUtil.next( ) )
210             {
211                 listSLot.add( buildSlot( daoUtil ) );
212             }
213         }
214         finally
215         {
216             if ( daoUtil != null )
217             {
218                 daoUtil.free( );
219             }
220         }
221         return listSLot;
222     }
223 
224     @Override
225     public List<Slot> findOpenSlotsByIdForm( int nIdForm, Plugin plugin )
226     {
227         DAOUtil daoUtil = null;
228         List<Slot> listSLot = new ArrayList<>( );
229         try
230         {
231             daoUtil = new DAOUtil( SQL_QUERY_SELECT_OPEN_SLOTS_BY_ID_FORM, plugin );
232             daoUtil.setInt( 1, nIdForm );
233             daoUtil.executeQuery( );
234             while ( daoUtil.next( ) )
235             {
236                 listSLot.add( buildSlot( daoUtil ) );
237             }
238         }
239         finally
240         {
241             if ( daoUtil != null )
242             {
243                 daoUtil.free( );
244             }
245         }
246         return listSLot;
247     }
248 
249     @Override
250     public Slot findSlotWithMaxDate( int nIdForm, Plugin plugin )
251     {
252         DAOUtil daoUtil = null;
253         Slot slot = null;
254         try
255         {
256             daoUtil = new DAOUtil( SQL_QUERY_SELECT_SLOT_WITH_MAX_DATE, plugin );
257             daoUtil.setInt( 1, nIdForm );
258             daoUtil.executeQuery( );
259             if ( daoUtil.next( ) )
260             {
261                 slot = buildSlot( daoUtil );
262             }
263         }
264         finally
265         {
266             if ( daoUtil != null )
267             {
268                 daoUtil.free( );
269             }
270         }
271         return slot;
272     }
273 
274     /**
275      * Build a Slot business object from the resultset
276      * 
277      * @param daoUtil
278      *            the prepare statement util object
279      * @return a new Slot with all its attributes assigned
280      */
281     private Slot buildSlot( DAOUtil daoUtil )
282     {
283         int nIndex = 1;
284         Slot slot = new Slot( );
285         slot.setIdSlot( daoUtil.getInt( nIndex++ ) );
286         slot.setStartingTimeStampDate( daoUtil.getTimestamp( nIndex++ ) );
287         slot.setEndingTimeStampDate( daoUtil.getTimestamp( nIndex++ ) );
288         slot.setIsOpen( daoUtil.getBoolean( nIndex++ ) );
289         slot.setIsSpecific( daoUtil.getBoolean( nIndex++ ) );
290         slot.setMaxCapacity( daoUtil.getInt( nIndex++ ) );
291         slot.setNbRemainingPlaces( daoUtil.getInt( nIndex++ ) );
292         slot.setNbPotentialRemainingPlaces( daoUtil.getInt( nIndex++ ) );
293         slot.setNbPlacestaken( daoUtil.getInt( nIndex++ ) );
294         slot.setIdForm( daoUtil.getInt( nIndex ) );
295 
296         return slot;
297     }
298 
299     /**
300      * Build a daoUtil object with the Slot business object
301      * 
302      * @param query
303      *            the query
304      * @param slot
305      *            the SLot
306      * @param plugin
307      *            the plugin
308      * @param isInsert
309      *            true if it is an insert query (in this case, need to set the id). If false, it is an update, in this case, there is a where parameter id to
310      *            set
311      * @return a new daoUtil with all its values assigned
312      */
313     private DAOUtil buildDaoUtil( String query, Slot slot, Plugin plugin, boolean isInsert )
314     {
315         int nIndex = 1;
316         DAOUtil daoUtil = new DAOUtil( query, plugin );
317         if ( isInsert )
318         {
319             daoUtil.setInt( nIndex++, slot.getIdSlot( ) );
320         }
321         daoUtil.setTimestamp( nIndex++, slot.getStartingTimestampDate( ) );
322         daoUtil.setTimestamp( nIndex++, slot.getEndingTimestampDate( ) );
323         daoUtil.setBoolean( nIndex++, slot.getIsOpen( ) );
324         daoUtil.setBoolean( nIndex++, slot.getIsSpecific( ) );
325         daoUtil.setInt( nIndex++, slot.getMaxCapacity( ) );
326         daoUtil.setInt( nIndex++, slot.getNbRemainingPlaces( ) );
327         daoUtil.setInt( nIndex++, slot.getNbPotentialRemainingPlaces( ) );
328         daoUtil.setInt( nIndex++, slot.getNbPlacesTaken( ) );
329         daoUtil.setInt( nIndex++, slot.getIdForm( ) );
330         if ( !isInsert )
331         {
332             daoUtil.setInt( nIndex, slot.getIdSlot( ) );
333         }
334         return daoUtil;
335     }
336 
337     /**
338      * Execute a safe update (Free the connection in case of error when execute the query)
339      * 
340      * @param daoUtil
341      *            the daoUtil
342      */
343     private void executeUpdate( DAOUtil daoUtil )
344     {
345         try
346         {
347             daoUtil.executeUpdate( );
348         }
349         finally
350         {
351             if ( daoUtil != null )
352             {
353                 daoUtil.free( );
354             }
355         }
356     }
357 
358 }