1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 package fr.paris.lutece.plugins.appointment.business.slot;
35
36 import java.sql.Statement;
37 import java.sql.Timestamp;
38 import java.time.LocalDate;
39 import java.time.LocalDateTime;
40 import java.util.ArrayList;
41 import java.util.List;
42
43 import fr.paris.lutece.portal.service.plugin.Plugin;
44 import fr.paris.lutece.util.sql.DAOUtil;
45
46
47
48
49
50
51
52 public final class SlotDAO implements ISlotDAO
53 {
54
55 private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_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_UPDATE_POTENTIAL_REMAINING_PLACE = "UPDATE appointment_slot SET nb_potential_remaining_places = ? WHERE id_slot = ?";
58 private static final String SQL_QUERY_UPDATE_POTENTIAL_REMAINING_PLACE_IF_SHUTDOWN = "UPDATE appointment_slot SET nb_potential_remaining_places = nb_remaining_places WHERE nb_potential_remaining_places < nb_remaining_places ";
59 private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_slot WHERE id_slot = ?";
60 private static final String SQL_QUERY_DELETE_BY_ID_FORM = "DELETE FROM appointment_slot WHERE id_form = ?";
61 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 ";
62 private static final String SQL_FROM_APPOINTMENT_SLOT = "FROM appointment_slot";
63 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT + " WHERE id_slot = ?";
64 private static final String SQL_QUERY_SELECT_BY_ID_FORM = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT + " WHERE id_form = ?";
65 private static final String SQL_QUERY_SELECT_BY_ID_FORM_AND_IS_SPECIFIC = SQL_QUERY_SELECT_BY_ID_FORM + " AND is_specific = 1";
66 private static final String SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_RANGE = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT
67 + " WHERE id_form = ? AND starting_date_time >= ? AND ending_date_time <= ?";
68 private static final String SQL_QUERY_SELECT_OPEN_SLOTS_BY_ID_FORM_AND_DATE_RANGE = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT
69 + " WHERE id_form = ? AND starting_date_time >= ? AND ending_date_time <= ? AND is_open = 1";
70 private static final String SQL_QUERY_SELECT_OPEN_SLOTS_BY_ID_FORM = SQL_QUERY_SELECT_COLUMNS + SQL_FROM_APPOINTMENT_SLOT
71 + " WHERE id_form = ? AND is_open = 1";
72 private static final String SQL_QUERY_SELECT_SLOT_WITH_MAX_DATE = SQL_QUERY_SELECT_COLUMNS + "FROM appointment_slot slot"
73 + " WHERE slot.id_form = ? ORDER BY slot.starting_date_time DESC LIMIT 1";
74 private static final String SQL_QUERY_SELECT_BY_ID_APPOINTMENT = "SELECT slot.id_slot, slot.starting_date_time, slot.ending_date_time, slot.is_open, slot.is_specific, slot.max_capacity, slot.nb_remaining_places, slot.nb_potential_remaining_places, slot.nb_places_taken, slot.id_form "
75 + " FROM appointment_slot slot INNER JOIN appointment_appointment_slot appt_slot ON (slot.id_slot = appt_slot.id_slot) "
76 + " INNER JOIN appointment_appointment appt ON (appt_slot.id_appointment = appt.id_appointment ) WHERE appt.id_appointment = ?";
77
78 private static final String SQL_QUERY_SELECT_SLOT_WITH_APPOINTMNT_BY_ID_FORM_AND_DATE_RANGE = "SELECT distinct slot.id_slot, slot.starting_date_time, slot.ending_date_time, slot.is_open, slot.is_specific, slot.max_capacity, slot.nb_remaining_places, slot.nb_potential_remaining_places, slot.nb_places_taken, slot.id_form from appointment_slot slot JOIN appointment_appointment_slot appt_slot on ( slot.id_slot = appt_slot.id_slot ) WHERE slot.id_form = ? AND slot.starting_date_time >= ? AND slot.ending_date_time <= ? ";
79 private static final String SQL_QUERY_SELECT_SPECIFIC_DATE_SLOT = "SELECT distinct DATE( starting_date_time) as date_value from appointment_slot where is_specific = 1 and id_form = ? ";
80
81 @Override
82 public void insert( Slot slot, Plugin plugin )
83 {
84 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, slot, plugin, true ) )
85 {
86 daoUtil.executeUpdate( );
87 if ( daoUtil.nextGeneratedKey( ) )
88 {
89 slot.setIdSlot( daoUtil.getGeneratedKeyInt( 1 ) );
90 }
91 }
92 }
93
94 @Override
95 public void update( Slot slot, Plugin plugin )
96 {
97 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, slot, plugin, false ) )
98 {
99 daoUtil.executeUpdate( );
100 }
101 }
102
103 @Override
104 public void delete( int nIdSlot, Plugin plugin )
105 {
106 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
107 {
108 daoUtil.setInt( 1, nIdSlot );
109 daoUtil.executeUpdate( );
110 }
111 }
112
113 @Override
114 public void deleteByIdForm( int nIdForm, Plugin plugin )
115 {
116 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_FORM, plugin ) )
117 {
118 daoUtil.setInt( 1, nIdForm );
119 daoUtil.executeUpdate( );
120 }
121 }
122
123 @Override
124 public Slot select( int nIdSlot, Plugin plugin )
125 {
126 Slot slot = null;
127 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
128 {
129 daoUtil.setInt( 1, nIdSlot );
130 daoUtil.executeQuery( );
131 if ( daoUtil.next( ) )
132 {
133 slot = buildSlot( daoUtil );
134 }
135 }
136 return slot;
137 }
138
139 @Override
140 public List<Slot> findByIdFormAndDateRange( int nIdForm, LocalDateTime startingDateTime, LocalDateTime endingDateTime, Plugin plugin )
141 {
142 List<Slot> listSlots = new ArrayList<>( );
143 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_RANGE, plugin ) )
144 {
145 daoUtil.setInt( 1, nIdForm );
146 daoUtil.setTimestamp( 2, Timestamp.valueOf( startingDateTime ) );
147 daoUtil.setTimestamp( 3, Timestamp.valueOf( endingDateTime ) );
148 daoUtil.executeQuery( );
149 while ( daoUtil.next( ) )
150 {
151 listSlots.add( buildSlot( daoUtil ) );
152 }
153 }
154 return listSlots;
155 }
156
157 @Override
158 public List<Slot> findSlotWithAppointmentByDateRange( int nIdForm, LocalDateTime startingDateTime, LocalDateTime endingDateTime, Plugin plugin )
159 {
160 List<Slot> listSlots = new ArrayList<>( );
161 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_SLOT_WITH_APPOINTMNT_BY_ID_FORM_AND_DATE_RANGE, plugin ) )
162 {
163 daoUtil.setInt( 1, nIdForm );
164 daoUtil.setTimestamp( 2, Timestamp.valueOf( startingDateTime ) );
165 daoUtil.setTimestamp( 3, Timestamp.valueOf( endingDateTime ) );
166 daoUtil.executeQuery( );
167 while ( daoUtil.next( ) )
168 {
169 listSlots.add( buildSlot( daoUtil ) );
170 }
171 }
172 return listSlots;
173 }
174
175 @Override
176 public List<Slot> findIsSpecificByIdForm( int nIdForm, Plugin plugin )
177 {
178 List<Slot> listSpecificSlots = new ArrayList<>( );
179 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM_AND_IS_SPECIFIC, plugin ) )
180 {
181 daoUtil.setInt( 1, nIdForm );
182 daoUtil.executeQuery( );
183 while ( daoUtil.next( ) )
184 {
185 listSpecificSlots.add( buildSlot( daoUtil ) );
186 }
187 }
188 return listSpecificSlots;
189 }
190
191 @Override
192 public List<Slot> findByIdForm( int nIdForm, Plugin plugin )
193 {
194 List<Slot> listSlot = new ArrayList<>( );
195 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM, plugin ) )
196 {
197 daoUtil.setInt( 1, nIdForm );
198 daoUtil.executeQuery( );
199 while ( daoUtil.next( ) )
200 {
201 listSlot.add( buildSlot( daoUtil ) );
202 }
203 }
204 return listSlot;
205 }
206
207 @Override
208 public List<Slot> findOpenSlotsByIdFormAndDateRange( int nIdForm, LocalDateTime startingDateTime, LocalDateTime endingDateTime, Plugin plugin )
209 {
210 List<Slot> listSLot = new ArrayList<>( );
211 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_OPEN_SLOTS_BY_ID_FORM_AND_DATE_RANGE, plugin ) )
212 {
213 daoUtil.setInt( 1, nIdForm );
214 daoUtil.setTimestamp( 2, Timestamp.valueOf( startingDateTime ) );
215 daoUtil.setTimestamp( 3, Timestamp.valueOf( endingDateTime ) );
216 daoUtil.executeQuery( );
217 while ( daoUtil.next( ) )
218 {
219 listSLot.add( buildSlot( daoUtil ) );
220 }
221 }
222 return listSLot;
223 }
224
225 @Override
226 public List<Slot> findOpenSlotsByIdForm( int nIdForm, Plugin plugin )
227 {
228 List<Slot> listSLot = new ArrayList<>( );
229 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_OPEN_SLOTS_BY_ID_FORM, plugin ) )
230 {
231 daoUtil.setInt( 1, nIdForm );
232 daoUtil.executeQuery( );
233 while ( daoUtil.next( ) )
234 {
235 listSLot.add( buildSlot( daoUtil ) );
236 }
237 }
238 return listSLot;
239 }
240
241 @Override
242 public List<Slot> findByIdAppointment( int nIdAppointment, Plugin plugin )
243 {
244 List<Slot> listSlot = new ArrayList<>( );
245 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_APPOINTMENT, plugin ) )
246 {
247 daoUtil.setInt( 1, nIdAppointment );
248 daoUtil.executeQuery( );
249 while ( daoUtil.next( ) )
250 {
251 listSlot.add( buildSlot( daoUtil ) );
252 }
253 }
254 return listSlot;
255 }
256
257 @Override
258 public Slot findSlotWithMaxDate( int nIdForm, Plugin plugin )
259 {
260 Slot slot = null;
261 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_SLOT_WITH_MAX_DATE, plugin ) )
262 {
263 daoUtil.setInt( 1, nIdForm );
264 daoUtil.executeQuery( );
265 if ( daoUtil.next( ) )
266 {
267 slot = buildSlot( daoUtil );
268 }
269 }
270 return slot;
271 }
272
273 @Override
274 public void updatePotentialRemainingPlaces( int nbPotentialRemainingPlaces, int nIdSlot, Plugin plugin )
275 {
276 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_POTENTIAL_REMAINING_PLACE, plugin ) )
277 {
278 daoUtil.setInt( 1, nbPotentialRemainingPlaces );
279 daoUtil.setInt( 2, nIdSlot );
280 daoUtil.executeUpdate( );
281 }
282 }
283
284
285
286
287
288
289
290
291 private Slot buildSlot( DAOUtil daoUtil )
292 {
293 int nIndex = 1;
294 Slot/plugins/appointment/business/slot/Slot.html#Slot">Slot slot = new Slot( );
295 slot.setIdSlot( daoUtil.getInt( nIndex++ ) );
296 slot.setStartingTimeStampDate( daoUtil.getTimestamp( nIndex++ ) );
297 slot.setEndingTimeStampDate( daoUtil.getTimestamp( nIndex++ ) );
298 slot.setIsOpen( daoUtil.getBoolean( nIndex++ ) );
299 slot.setIsSpecific( daoUtil.getBoolean( nIndex++ ) );
300 slot.setMaxCapacity( daoUtil.getInt( nIndex++ ) );
301 slot.setNbRemainingPlaces( daoUtil.getInt( nIndex++ ) );
302 slot.setNbPotentialRemainingPlaces( daoUtil.getInt( nIndex++ ) );
303 slot.setNbPlacestaken( daoUtil.getInt( nIndex++ ) );
304 slot.setIdForm( daoUtil.getInt( nIndex ) );
305
306 return slot;
307 }
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323 private DAOUtil buildDaoUtil( String query, Slot slot, Plugin plugin, boolean isInsert )
324 {
325 int nIndex = 1;
326 DAOUtil daoUtil = null;
327 if ( isInsert )
328 {
329 daoUtil = new DAOUtil( query, Statement.RETURN_GENERATED_KEYS, plugin );
330 }
331 else
332 {
333 daoUtil = new DAOUtil( query, plugin );
334 }
335 daoUtil.setTimestamp( nIndex++, slot.getStartingTimestampDate( ) );
336 daoUtil.setTimestamp( nIndex++, slot.getEndingTimestampDate( ) );
337 daoUtil.setBoolean( nIndex++, slot.getIsOpen( ) );
338 daoUtil.setBoolean( nIndex++, slot.getIsSpecific( ) );
339 daoUtil.setInt( nIndex++, slot.getMaxCapacity( ) );
340 daoUtil.setInt( nIndex++, slot.getNbRemainingPlaces( ) );
341 daoUtil.setInt( nIndex++, slot.getNbPotentialRemainingPlaces( ) );
342 daoUtil.setInt( nIndex++, slot.getNbPlacesTaken( ) );
343 daoUtil.setInt( nIndex++, slot.getIdForm( ) );
344 if ( !isInsert )
345 {
346 daoUtil.setInt( nIndex, slot.getIdSlot( ) );
347 }
348 return daoUtil;
349 }
350
351 @Override
352 public void resetPotentialRemainingPlaces( Plugin plugin )
353 {
354 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_POTENTIAL_REMAINING_PLACE_IF_SHUTDOWN, plugin ) )
355 {
356 daoUtil.executeUpdate( );
357 }
358 }
359
360 @Override
361 public List<LocalDate> findSpecificSlotDates( int nIdForm, Plugin plugin )
362 {
363 List<LocalDate> listDate = new ArrayList<>( );
364 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_SPECIFIC_DATE_SLOT, plugin ) )
365 {
366 daoUtil.setInt( 1, nIdForm );
367 daoUtil.executeQuery( );
368 while ( daoUtil.next( ) )
369 {
370 listDate.add( daoUtil.getDate( 1 ).toLocalDate( ) );
371 }
372 }
373 return listDate;
374 }
375 }