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.appointment;
35
36 import fr.paris.lutece.plugins.appointment.business.slot.Slot;
37 import fr.paris.lutece.plugins.appointment.business.slot.SlotHome;
38 import fr.paris.lutece.plugins.appointment.business.user.User;
39 import fr.paris.lutece.plugins.appointment.web.dto.AppointmentFilterDTO;
40 import fr.paris.lutece.portal.service.plugin.Plugin;
41 import fr.paris.lutece.util.sql.DAOUtil;
42 import org.apache.commons.collections.CollectionUtils;
43 import org.apache.commons.lang3.StringUtils;
44
45 import java.sql.Statement;
46 import java.sql.Timestamp;
47 import java.time.LocalDateTime;
48 import java.time.LocalTime;
49 import java.util.ArrayList;
50 import java.util.HashMap;
51 import java.util.List;
52 import java.util.Map;
53 import java.util.stream.Collectors;
54
55
56
57
58
59
60
61 public final class AppointmentDAO implements IAppointmentDAO
62 {
63
64 private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_appointment (reference, nb_places, is_cancelled, id_action_cancelled, id_action_reported, notification, id_admin_user, admin_access_code_create, id_user, date_appointment_create, is_surbooked) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
65 private static final String SQL_QUERY_UPDATE = "UPDATE appointment_appointment SET reference = ?, nb_places = ?, is_cancelled = ?, id_action_cancelled = ?, id_action_reported = ?, notification = ?, id_admin_user = ?, admin_access_code_create = ?, id_user = ?, date_appointment_create = ? WHERE id_appointment = ?";
66 private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_appointment WHERE id_appointment = ?";
67 private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT appointment.id_appointment, appointment.reference, appointment.nb_places, appointment.is_cancelled, appointment.id_action_cancelled, appointment.id_action_reported, appointment.notification, appointment.id_admin_user, appointment.admin_access_code_create, appointment.id_user, appointment.date_appointment_create ";
68 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + "FROM appointment_appointment appointment WHERE id_appointment = ?";
69 private static final String SQL_QUERY_SELECT_BY_ID_USER = SQL_QUERY_SELECT_COLUMNS + "FROM appointment_appointment appointment WHERE id_user = ?";
70 private static final String SQL_QUERY_SELECT_BY_GUID_USER = SQL_QUERY_SELECT_COLUMNS
71 + "FROM appointment_appointment appointment join appointment_user user on (user.id_user = appointment.id_user and user.guid = ?)";
72 private static final String SQL_QUERY_SELECT_BY_ID_SLOT = SQL_QUERY_SELECT_COLUMNS
73 + ",appt_slot.nb_places FROM appointment_appointment appointment INNER JOIN appointment_appointment_slot appt_slot on ( appt_slot.id_appointment = appointment.id_appointment and appt_slot.id_slot= ? )";
74 private static final String SQL_QUERY_SELECT_BY_REFERENCE = SQL_QUERY_SELECT_COLUMNS + "FROM appointment_appointment appointment WHERE reference = ?";
75
76 private static final String SQL_QUERY_SELECT_DISTINCT_COLUMNS = "SELECT DISTINCT appointment.id_appointment, appointment.reference, appointment.nb_places, appointment.is_cancelled, appointment.id_action_cancelled, appointment.id_action_reported, appointment.notification, appointment.id_admin_user, appointment.admin_access_code_create, appointment.id_user, appointment.date_appointment_create FROM appointment_appointment appointment";
77 private static final String SQL_QUERY_SELECT_BY_ID_FORM = SQL_QUERY_SELECT_DISTINCT_COLUMNS
78 + " INNER JOIN appointment_appointment_slot appt_slot on(appt_slot.id_appointment = appointment.id_appointment) INNER JOIN appointment_slot slot ON (appt_slot.id_slot = slot.id_slot) WHERE slot.id_form = ?";
79 private static final String SQL_QUERY_SELECT_BY_FILTER = "SELECT "
80 + " app.id_appointment, app.reference, app.nb_places, app.is_cancelled, app.id_action_cancelled, app.id_action_reported, app.notification, app.id_admin_user, app.admin_access_code_create, app.id_user, app.date_appointment_create, "
81 + " user.id_user, user.guid, user.first_name, user.last_name, user.email, user.phone_number, "
82 + " 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 "
83 + " FROM appointment_appointment app " + "INNER JOIN appointment_user user ON app.id_user = user.id_user "
84 + " INNER JOIN appointment_appointment_slot app_slot ON app.id_appointment = app_slot.id_appointment"
85 + " INNER JOIN appointment_slot slot ON app_slot.id_slot = slot.id_slot WHERE id_form != 0";
86 private static final String SQL_QUERY_SELECT_IDS_BY_FILTER = "SELECT "
87 + " app.id_appointment"
88 + " FROM appointment_appointment app " + "INNER JOIN appointment_user user ON app.id_user = user.id_user "
89 + " INNER JOIN appointment_appointment_slot app_slot ON app.id_appointment = app_slot.id_appointment"
90 + " INNER JOIN appointment_slot slot ON app_slot.id_slot = slot.id_slot WHERE id_form != 0";
91
92 private static final String SQL_QUERY_INSERT_APPT_SLT = "INSERT INTO appointment_appointment_slot (id_appointment, id_slot, nb_places) VALUES ( ?, ?, ?)";
93 private static final String SQL_QUERY_DELETE_APPT_SLT = "DELETE FROM appointment_appointment_slot WHERE id_appointment = ?";
94 private static final String SQL_QUERY_SELECT_APPT_SLT = "SELECT id_appointment, id_slot, nb_places FROM appointment_appointment_slot where id_appointment = ?";
95
96 private static final String SQL_QUERY_SELECT_BY_LIST_ID_SLOT = SQL_QUERY_SELECT_COLUMNS
97 + ",appt_slot.nb_places FROM appointment_appointment appointment INNER JOIN appointment_appointment_slot appt_slot on ( appt_slot.id_appointment = appointment.id_appointment ) where appt_slot.id_slot IN(";
98
99 private static final String SQL_QUERY_SELECT_BY_EMAIL_AND_CATEGORY = " Select app.id_appointment, app.reference, app.nb_places, app.is_cancelled, app.id_action_cancelled, app.id_action_reported, app.notification, app.id_admin_user, app.admin_access_code_create, app.id_user, app.date_appointment_create, "
100 + "user.id_user, user.guid, user.first_name, user.last_name, user.email, user.phone_number, "
101 + "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 "
102 + "from appointment_appointment app JOIN appointment_appointment_slot appSlot ON app.id_appointment = appSlot.id_appointment "
103 + "JOIN appointment_user user ON user.id_user = app.id_user and user.email = ? " + "JOIN appointment_slot slot ON slot.id_slot = appSlot.id_slot "
104 + "JOIN appointment_form form ON form.id_form = slot.id_form "
105 + "JOIN appointment_category cat on cat.id_category = form.id_category and cat.id_category = ? ";
106
107 private static final String SQL_QUERY_SELECT_ID = "SELECT id_appointment FROM appointment_appointment ";
108 private static final String SQL_FILTER_FIRST_NAME = "UPPER(user.first_name) LIKE ?";
109 private static final String SQL_FILTER_LAST_NAME = "UPPER(user.last_name) LIKE ?";
110 private static final String SQL_FILTER_EMAIL = "UPPER(user.email) LIKE ?";
111 private static final String SQL_FILTER_PHONE_NUMBER = "UPPER(user.phone_number) LIKE ?";
112 private static final String SQL_FILTER_ID_FORM = "slot.id_form = ?";
113 private static final String SQL_FILTER_GUID = "user.guid = ?";
114 private static final String SQL_FILTER_STATUS = "app.is_cancelled = ?";
115 private static final String SQL_FILTER_DATE_APPOINTMENT_MIN = "slot.starting_date_time >= ?";
116 private static final String SQL_FILTER_DATE_APPOINTMENT_MAX = "slot.starting_date_time < ?";
117 private static final String SQL_FILTER_ID_LIST_START = "app.id_appointment IN ( ";
118 private static final String SQL_FILTER_ID_LIST_END = " ) ";
119
120 private static final String SQL_SORT_USER_LAST_NAME = "user.last_name";
121 private static final String SQL_SORT_USER_FIRST_NAME = "user.first_name";
122 private static final String SQL_SORT_USER_EMAIL = "user.email";
123 private static final String SQL_SORT_USER_PHONE_NUMBER = "user.phone_number";
124 private static final String SQL_SORT_APP_NB_PLACES = "app.nb_places";
125 private static final String SQL_SORT_SLOT_STARTING_DATE_TIME = "slot.starting_date_time";
126 private static final String SQL_SORT_APP_ID_ADMIN_USER = "app.id_admin_user";
127 private static final String SQL_SORT_APP_IS_CANCELLED = "app.is_cancelled";
128 private static final String SQL_SORT_ASC = " ASC ";
129 private static final String SQL_SORT_DESC = " DESC ";
130
131 private static final String CONSTANT_AND = " AND ";
132 private static final String CONSTANT_PERCENT = "%";
133 private static final String CONSTANT_ORDER_BY = " ORDER BY ";
134
135 private static final String LAST_NAME = "last_name";
136 private static final String FIRST_NAME = "first_name";
137 private static final String EMAIL = "email";
138 private static final String PHONE_NUMBER = "phone_number";
139 private static final String NB_BOOKED_SEATS = "nbBookedSeats";
140 private static final String DATE_APPOINTMENT = "date_appointment";
141 private static final String ADMIN = "admin";
142 private static final String STATUS = "status";
143
144
145 @Override
146 public void insert( Appointment appointment, Plugin plugin )
147 {
148 appointment.setDateAppointmentTaken( LocalDateTime.now( ) );
149
150 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, appointment, plugin, true ) )
151 {
152 daoUtil.executeUpdate( );
153 if ( daoUtil.nextGeneratedKey( ) )
154 {
155 appointment.setIdAppointment( daoUtil.getGeneratedKeyInt( 1 ) );
156 }
157
158 }
159 for ( AppointmentSlot apptSlot : appointment.getListAppointmentSlot( ) )
160 {
161
162 apptSlot.setIdAppointment( appointment.getIdAppointment( ) );
163 insertAppointmentSlot( apptSlot, plugin );
164 }
165 }
166
167 private List<AppointmentSlot> selectAppointmentSlot( int nIdAppointment, Plugin plugin )
168 {
169 List<AppointmentSlot> listAppointmentSlot = new ArrayList<>( );
170 AppointmentSlot appointmentSlot = null;
171 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_APPT_SLT, plugin ) )
172 {
173 daoUtil.setInt( 1, nIdAppointment );
174 daoUtil.executeQuery( );
175 while ( daoUtil.next( ) )
176 {
177 appointmentSlot = new AppointmentSlot( );
178 appointmentSlot.setIdAppointment( daoUtil.getInt( 1 ) );
179 appointmentSlot.setIdSlot( daoUtil.getInt( 2 ) );
180 appointmentSlot.setNbPlaces( daoUtil.getInt( 3 ) );
181
182 listAppointmentSlot.add( appointmentSlot );
183 }
184 }
185 return listAppointmentSlot;
186 }
187
188 private void insertAppointmentSlot( AppointmentSlot apptSlot, Plugin plugin )
189 {
190 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_APPT_SLT, plugin ) )
191 {
192 daoUtil.setInt( 1, apptSlot.getIdAppointment( ) );
193 daoUtil.setInt( 2, apptSlot.getIdSlot( ) );
194 daoUtil.setInt( 3, apptSlot.getNbPlaces( ) );
195
196 daoUtil.executeUpdate( );
197 }
198 }
199
200 private void deleteAppointmentSlot( int nIdAppointment, Plugin plugin )
201 {
202 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_APPT_SLT, plugin ) )
203 {
204 daoUtil.setInt( 1, nIdAppointment );
205 daoUtil.executeUpdate( );
206 }
207 }
208
209 @Override
210 public void update( Appointment appointment, Plugin plugin )
211 {
212 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, appointment, plugin, false ) )
213 {
214 daoUtil.executeUpdate( );
215 }
216 }
217
218 @Override
219 public void updateAppointmentDate( Appointment appointment, Plugin plugin )
220 {
221
222 if ( appointment.getListAppointmentSlot( ) != null && !appointment.getListAppointmentSlot( ).isEmpty( ) )
223 {
224 deleteAppointmentSlot( appointment.getIdAppointment( ), plugin );
225 for ( AppointmentSlot apptSlot : appointment.getListAppointmentSlot( ) )
226 {
227 insertAppointmentSlot( apptSlot, plugin );
228 }
229 }
230 }
231
232 @Override
233 public void delete( int nIdAppointment, Plugin plugin )
234 {
235 deleteAppointmentSlot( nIdAppointment, plugin );
236 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
237 {
238 daoUtil.setInt( 1, nIdAppointment );
239 daoUtil.executeUpdate( );
240 }
241 }
242
243 @Override
244 public Appointment select( int nIdAppointment, Plugin plugin )
245 {
246 Appointment appointment = null;
247 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
248 {
249 daoUtil.setInt( 1, nIdAppointment );
250 daoUtil.executeQuery( );
251 if ( daoUtil.next( ) )
252 {
253 appointment = buildAppointment( daoUtil );
254 }
255 }
256 if ( appointment != null )
257 {
258
259 appointment.setListAppointmentSlot( selectAppointmentSlot( nIdAppointment, plugin ) );
260 }
261 return appointment;
262 }
263
264 @Override
265 public List<Appointment> findByIdUser( int nIdUser, Plugin plugin )
266 {
267 List<Appointment> listAppointment = new ArrayList<>( );
268 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_USER, plugin ) )
269 {
270 daoUtil.setInt( 1, nIdUser );
271 daoUtil.executeQuery( );
272 while ( daoUtil.next( ) )
273 {
274 Appointment appt = buildAppointment( daoUtil );
275 appt.setListAppointmentSlot( selectAppointmentSlot( appt.getIdAppointment( ), plugin ) );
276
277 listAppointment.add( appt );
278 }
279 }
280 return listAppointment;
281 }
282
283 @Override
284 public List<Appointment> findByGuidUser( String strGuidUser, Plugin plugin )
285 {
286 List<Appointment> listAppointment = new ArrayList<>( );
287 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_GUID_USER, plugin ) )
288 {
289 daoUtil.setString( 1, strGuidUser );
290 daoUtil.executeQuery( );
291 while ( daoUtil.next( ) )
292 {
293 Appointment appt = buildAppointment( daoUtil );
294 appt.setListAppointmentSlot( selectAppointmentSlot( appt.getIdAppointment( ), plugin ) );
295
296 listAppointment.add( appt );
297 }
298 }
299 return listAppointment;
300 }
301
302 @Override
303 public List<Appointment> findByIdSlot( int nIdSlot, Plugin plugin )
304 {
305 List<Appointment> listAppointment = new ArrayList<>( );
306 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_SLOT, plugin ) )
307 {
308 daoUtil.setInt( 1, nIdSlot );
309 daoUtil.executeQuery( );
310 while ( daoUtil.next( ) )
311 {
312 Appointment appointment = buildAppointment( daoUtil );
313 appointment.setListAppointmentSlot( selectAppointmentSlot( appointment.getIdAppointment( ), plugin ) );
314 listAppointment.add( appointment );
315 }
316 }
317 return listAppointment;
318 }
319
320 @Override
321 public List<Appointment> findByListIdSlot( List<Integer> listIdSlot, Plugin plugin )
322 {
323 List<Appointment> list = new ArrayList<>( );
324
325 if ( CollectionUtils.isEmpty( listIdSlot ) )
326 {
327
328 return list;
329 }
330 String query = SQL_QUERY_SELECT_BY_LIST_ID_SLOT + listIdSlot.stream( ).distinct( ).map( i -> "?" ).collect( Collectors.joining( "," ) ) + " )";
331
332 try ( DAOUtil daoUtil = new DAOUtil( query, plugin ) )
333 {
334 for ( int i = 0; i < listIdSlot.size( ); i++ )
335 {
336 daoUtil.setInt( i + 1, listIdSlot.get( i ) );
337 }
338 daoUtil.executeQuery( );
339
340 while ( daoUtil.next( ) )
341 {
342 Appointment appointment = buildAppointment( daoUtil );
343 if ( list.stream( ).noneMatch( appt -> appt.getIdAppointment( ) == appointment.getIdAppointment( ) ) )
344 {
345
346 appointment.setListAppointmentSlot( selectAppointmentSlot( appointment.getIdAppointment( ), plugin ) );
347 list.add( appointment );
348 }
349 }
350 }
351 return list;
352 }
353
354 @Override
355 public Appointment findByReference( String strReference, Plugin plugin )
356 {
357 Appointment appointment = null;
358 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_REFERENCE, plugin ) )
359 {
360 daoUtil.setString( 1, strReference );
361 daoUtil.executeQuery( );
362 if ( daoUtil.next( ) )
363 {
364 appointment = buildAppointment( daoUtil );
365 appointment.setListAppointmentSlot( selectAppointmentSlot( appointment.getIdAppointment( ), plugin ) );
366 }
367 }
368 return appointment;
369 }
370
371 @Override
372 public List<Appointment> findByFilter( AppointmentFilterDTO appointmentFilter, Plugin plugin )
373 {
374 Map<Integer, Appointment> mapAppointment = new HashMap<>( );
375 boolean isFirst = true;
376 try ( DAOUtil daoUtil = new DAOUtil( getSqlQueryFromFilter( appointmentFilter, SQL_QUERY_SELECT_BY_FILTER ), plugin ) )
377 {
378 addFilterParametersToDAOUtil( appointmentFilter, daoUtil );
379 daoUtil.executeQuery( );
380
381 while ( daoUtil.next( ) )
382 {
383 Appointment appt = buildAppointment( daoUtil );
384
385 Slot slot = builSlot( daoUtil, 18 );
386 User user = buildUser( daoUtil, 12 );
387
388 if ( isFirst || daoUtil.isLast( ) )
389 {
390
391 appt.setSlot( SlotHome.findByIdAppointment( appt.getIdAppointment( ) ) );
392
393 }
394 else
395 {
396
397 appt.addSlot( slot );
398 }
399 appt.setUser( user );
400
401 Appointment apptAdded = mapAppointment.get( appt.getIdAppointment( ) );
402 if ( apptAdded == null )
403 {
404 mapAppointment.put( appt.getIdAppointment( ), appt );
405 }
406 else
407 {
408 apptAdded.addSlot( slot );
409 }
410
411 isFirst = false;
412 }
413
414 }
415 return new ArrayList<>( mapAppointment.values( ) );
416 }
417
418 @Override
419 public List<Integer> findIdsByFilter( AppointmentFilterDTO appointmentFilter, Plugin plugin )
420 {
421 List<Integer> list = new ArrayList<>( );
422
423 String sqlQueryFromFilter = getSqlQueryFromFilter(appointmentFilter, SQL_QUERY_SELECT_IDS_BY_FILTER);
424 String sqlQuery = getOrderQuery( appointmentFilter, sqlQueryFromFilter );
425 try (DAOUtil daoUtil = new DAOUtil(sqlQuery, plugin ) )
426 {
427 addFilterParametersToDAOUtil( appointmentFilter, daoUtil );
428 daoUtil.executeQuery( );
429
430
431 while ( daoUtil.next( ) )
432 {
433 list.add( daoUtil.getInt( 1 ) );
434 }
435
436
437 }
438 return list;
439 }
440
441 @Override
442 public List<Appointment> findByCategoryAndMail( int nIdCategory, String mail, Plugin plugin )
443 {
444 Map<Integer, Appointment> mapAppointment = new HashMap<>( );
445 boolean isFirst = true;
446 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_EMAIL_AND_CATEGORY, plugin ) )
447 {
448 daoUtil.setString( 1, mail );
449 daoUtil.setInt( 2, nIdCategory );
450 daoUtil.executeQuery( );
451
452 while ( daoUtil.next( ) )
453 {
454 Appointment appt = buildAppointment( daoUtil );
455
456 Slot slot = builSlot( daoUtil, 18 );
457 User user = buildUser( daoUtil, 12 );
458
459 if ( isFirst || daoUtil.isLast( ) )
460 {
461
462 appt.setSlot( SlotHome.findByIdAppointment( appt.getIdAppointment( ) ) );
463
464 }
465 else
466 {
467
468 appt.addSlot( slot );
469 }
470 appt.setUser( user );
471
472 Appointment apptAdded = mapAppointment.get( appt.getIdAppointment( ) );
473 if ( apptAdded == null )
474 {
475 mapAppointment.put( appt.getIdAppointment( ), appt );
476 }
477 else
478 {
479 apptAdded.addSlot( slot );
480 }
481
482 isFirst = false;
483 }
484
485 }
486 return new ArrayList<>( mapAppointment.values( ) );
487 }
488
489
490
491
492
493
494
495
496
497 private void addFilterParametersToDAOUtil( AppointmentFilterDTO appointmentFilter, DAOUtil daoUtil )
498 {
499 int nIndex = 0;
500 if ( appointmentFilter.getIdForm( ) != 0 )
501 {
502 daoUtil.setInt( ++nIndex, appointmentFilter.getIdForm( ) );
503 }
504 if ( appointmentFilter.getFirstName( ) != null )
505 {
506 daoUtil.setString( ++nIndex, CONSTANT_PERCENT + appointmentFilter.getFirstName( ).toUpperCase( ) + CONSTANT_PERCENT );
507 }
508 if ( appointmentFilter.getLastName( ) != null )
509 {
510 daoUtil.setString( ++nIndex, CONSTANT_PERCENT + appointmentFilter.getLastName( ).toUpperCase( ) + CONSTANT_PERCENT );
511 }
512 if ( appointmentFilter.getEmail( ) != null )
513 {
514 daoUtil.setString( ++nIndex, CONSTANT_PERCENT + appointmentFilter.getEmail( ).toUpperCase( ) + CONSTANT_PERCENT );
515 }
516 if ( StringUtils.isNotEmpty( appointmentFilter.getPhoneNumber( ) ) )
517 {
518 daoUtil.setString( ++nIndex, CONSTANT_PERCENT + appointmentFilter.getPhoneNumber( ).toUpperCase( ) + CONSTANT_PERCENT );
519 }
520 if ( appointmentFilter.getGuid( ) != null )
521 {
522 daoUtil.setString( ++nIndex, appointmentFilter.getGuid( ) );
523 }
524 if ( appointmentFilter.getStatus( ) != -1 )
525 {
526 daoUtil.setInt( ++nIndex, appointmentFilter.getStatus( ) );
527 }
528 if ( appointmentFilter.getStartingDateOfSearch( ) != null )
529 {
530 Timestamp startingTimestamp;
531 if ( StringUtils.isNotEmpty( appointmentFilter.getStartingTimeOfSearch( ) ) )
532 {
533 startingTimestamp = Timestamp.valueOf(
534 appointmentFilter.getStartingDateOfSearch( ).toLocalDate( ).atTime( LocalTime.parse( appointmentFilter.getStartingTimeOfSearch( ) ) ) );
535 }
536 else
537 {
538 startingTimestamp = Timestamp.valueOf( appointmentFilter.getStartingDateOfSearch( ).toLocalDate( ).atStartOfDay( ) );
539 }
540 daoUtil.setTimestamp( ++nIndex, startingTimestamp );
541 }
542 if ( appointmentFilter.getEndingDateOfSearch( ) != null )
543 {
544 Timestamp endingTimestamp;
545 if ( StringUtils.isNotEmpty( appointmentFilter.getEndingTimeOfSearch( ) ) )
546 {
547 endingTimestamp = Timestamp.valueOf(
548 appointmentFilter.getEndingDateOfSearch( ).toLocalDate( ).atTime( LocalTime.parse( appointmentFilter.getEndingTimeOfSearch( ) ) ) );
549 }
550 else
551 {
552 endingTimestamp = Timestamp.valueOf( appointmentFilter.getEndingDateOfSearch( ).toLocalDate( ).atTime( LocalTime.MAX ) );
553 }
554 daoUtil.setTimestamp( ++nIndex, endingTimestamp );
555 }
556 List<Integer> listId = appointmentFilter.getListIdAppointment( );
557 for ( Integer id : listId )
558 {
559 daoUtil.setInt( ++nIndex, id );
560 }
561 }
562
563
564
565
566
567
568
569
570 private String getSqlQueryFromFilter( AppointmentFilterDTO appointmentFilter, String strQuery )
571 {
572 StringBuilder sbSql = new StringBuilder( strQuery );
573
574 if ( appointmentFilter.getIdForm( ) != 0 )
575 {
576 sbSql.append( CONSTANT_AND );
577 sbSql.append( SQL_FILTER_ID_FORM );
578 }
579 if ( appointmentFilter.getFirstName( ) != null )
580 {
581 sbSql.append( CONSTANT_AND );
582 sbSql.append( SQL_FILTER_FIRST_NAME );
583 }
584 if ( appointmentFilter.getLastName( ) != null )
585 {
586 sbSql.append( CONSTANT_AND );
587 sbSql.append( SQL_FILTER_LAST_NAME );
588 }
589 if ( appointmentFilter.getEmail( ) != null )
590 {
591 sbSql.append( CONSTANT_AND );
592 sbSql.append( SQL_FILTER_EMAIL );
593 }
594 if ( StringUtils.isNotEmpty( appointmentFilter.getPhoneNumber( ) ) )
595 {
596 sbSql.append( CONSTANT_AND );
597 sbSql.append( SQL_FILTER_PHONE_NUMBER );
598 }
599 if ( appointmentFilter.getGuid( ) != null )
600 {
601 sbSql.append( CONSTANT_AND );
602 sbSql.append( SQL_FILTER_GUID );
603 }
604 if ( appointmentFilter.getStatus( ) != -1 )
605 {
606 sbSql.append( CONSTANT_AND );
607 sbSql.append( SQL_FILTER_STATUS );
608 }
609 if ( appointmentFilter.getStartingDateOfSearch( ) != null )
610 {
611 sbSql.append( CONSTANT_AND );
612 sbSql.append( SQL_FILTER_DATE_APPOINTMENT_MIN );
613 }
614 if ( appointmentFilter.getEndingDateOfSearch( ) != null )
615 {
616 sbSql.append( CONSTANT_AND );
617 sbSql.append( SQL_FILTER_DATE_APPOINTMENT_MAX );
618 }
619 List<Integer> listId = appointmentFilter.getListIdAppointment( );
620 if ( CollectionUtils.isNotEmpty( listId ) )
621 {
622 sbSql.append( CONSTANT_AND );
623 sbSql.append( SQL_FILTER_ID_LIST_START );
624 sbSql.append( listId.stream( ).map( s -> "?" ).collect( Collectors.joining( "," ) ) );
625 sbSql.append( SQL_FILTER_ID_LIST_END );
626 }
627
628 return sbSql.toString( );
629 }
630
631 private String getOrderQuery( AppointmentFilterDTO appointmentFilter, String strQuery )
632 {
633 StringBuilder sbSql = new StringBuilder( strQuery );
634
635 sbSql.append( CONSTANT_ORDER_BY );
636
637 if ( appointmentFilter.getOrderBy( ) == null )
638 {
639 appointmentFilter.setOrderBy( DATE_APPOINTMENT );
640 }
641
642 switch( appointmentFilter.getOrderBy( ) )
643 {
644 case LAST_NAME:
645 sbSql.append( SQL_SORT_USER_LAST_NAME );
646 break;
647 case FIRST_NAME:
648 sbSql.append( SQL_SORT_USER_FIRST_NAME );
649 break;
650 case EMAIL:
651 sbSql.append( SQL_SORT_USER_EMAIL );
652 break;
653 case PHONE_NUMBER:
654 sbSql.append( SQL_SORT_USER_PHONE_NUMBER );
655 break;
656 case NB_BOOKED_SEATS:
657 sbSql.append( SQL_SORT_APP_NB_PLACES );
658 break;
659 case DATE_APPOINTMENT:
660 sbSql.append( SQL_SORT_SLOT_STARTING_DATE_TIME );
661 break;
662 case ADMIN:
663 sbSql.append( SQL_SORT_APP_ID_ADMIN_USER );
664 break;
665 case STATUS:
666 sbSql.append( SQL_SORT_APP_IS_CANCELLED );
667 break;
668 default:
669 sbSql.append( SQL_SORT_SLOT_STARTING_DATE_TIME );
670 }
671 if ( appointmentFilter.isOrderAsc( ) )
672 {
673 sbSql.append( SQL_SORT_ASC );
674 }
675 else
676 {
677 sbSql.append( SQL_SORT_DESC );
678 }
679 return sbSql.toString( );
680 }
681
682 @Override
683 public List<Appointment> findByIdForm( int nIdForm, Plugin plugin )
684 {
685 List<Appointment> listAppointment = new ArrayList<>( );
686 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM, plugin ) )
687 {
688 daoUtil.setInt( 1, nIdForm );
689 daoUtil.executeQuery( );
690 while ( daoUtil.next( ) )
691 {
692 listAppointment.add( buildAppointment( daoUtil ) );
693 }
694 }
695 return listAppointment;
696 }
697
698 @Override
699 public List<Integer> selectAllAppointmentId( Plugin plugin )
700 {
701 List<Integer> list = new ArrayList<>( );
702 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ID, plugin ) )
703 {
704 daoUtil.executeQuery( );
705
706 while ( daoUtil.next( ) )
707 {
708 list.add( daoUtil.getInt( 1 ) );
709 }
710 }
711 return list;
712 }
713
714
715
716
717
718
719
720
721 private Appointment buildAppointment( DAOUtil daoUtil )
722 {
723 int nIndex = 1;
724 Appointmentntment/business/appointment/Appointment.html#Appointment">Appointment appointment = new Appointment( );
725 appointment.setIdAppointment( daoUtil.getInt( nIndex++ ) );
726 appointment.setReference( daoUtil.getString( nIndex++ ) );
727 appointment.setNbPlaces( daoUtil.getInt( nIndex++ ) );
728 appointment.setIsCancelled( daoUtil.getBoolean( nIndex++ ) );
729 appointment.setIdActionCancelled( daoUtil.getInt( nIndex++ ) );
730 appointment.setIdActionReported( daoUtil.getInt( nIndex++ ) );
731 appointment.setNotification( daoUtil.getInt( nIndex++ ) );
732 appointment.setIdAdminUser( daoUtil.getInt( nIndex++ ) );
733 appointment.setAdminUserCreate( daoUtil.getString( nIndex++ ) );
734 appointment.setIdUser( daoUtil.getInt( nIndex++ ) );
735 appointment.setAppointmentTakenSqlDate( daoUtil.getTimestamp( nIndex ) );
736 return appointment;
737 }
738
739
740
741
742
743
744
745
746 private Slot builSlot( DAOUtil daoUtil, int nIndex )
747 {
748 Slot/plugins/appointment/business/slot/Slot.html#Slot">Slot slot = new Slot( );
749 slot.setIdSlot( daoUtil.getInt( nIndex++ ) );
750 slot.setStartingTimeStampDate( daoUtil.getTimestamp( nIndex++ ) );
751 slot.setEndingTimeStampDate( daoUtil.getTimestamp( nIndex++ ) );
752 slot.setIsOpen( daoUtil.getBoolean( nIndex++ ) );
753 slot.setIsSpecific( daoUtil.getBoolean( nIndex++ ) );
754 slot.setMaxCapacity( daoUtil.getInt( nIndex++ ) );
755 slot.setNbRemainingPlaces( daoUtil.getInt( nIndex++ ) );
756 slot.setNbPotentialRemainingPlaces( daoUtil.getInt( nIndex++ ) );
757 slot.setNbPlacestaken( daoUtil.getInt( nIndex++ ) );
758 slot.setIdForm( daoUtil.getInt( nIndex ) );
759 return slot;
760 }
761
762
763
764
765
766
767
768
769 private User buildUser( DAOUtil daoUtil, int nIndex )
770 {
771
772 User/plugins/appointment/business/user/User.html#User">User user = new User( );
773 user.setIdUser( daoUtil.getInt( nIndex++ ) );
774 user.setGuid( daoUtil.getString( nIndex++ ) );
775 user.setFirstName( daoUtil.getString( nIndex++ ) );
776 user.setLastName( daoUtil.getString( nIndex++ ) );
777 user.setEmail( daoUtil.getString( nIndex++ ) );
778 user.setPhoneNumber( daoUtil.getString( nIndex ) );
779 return user;
780 }
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796 private DAOUtil buildDaoUtil( String query, Appointment appointment, Plugin plugin, boolean isInsert )
797 {
798 int nIndex = 1;
799 DAOUtil daoUtil = null;
800 if ( isInsert )
801 {
802 daoUtil = new DAOUtil( query, Statement.RETURN_GENERATED_KEYS, plugin );
803 }
804 else
805 {
806 daoUtil = new DAOUtil( query, plugin );
807 }
808 daoUtil.setString( nIndex++, appointment.getReference( ) );
809 daoUtil.setInt( nIndex++, appointment.getNbPlaces( ) );
810 daoUtil.setBoolean( nIndex++, appointment.getIsCancelled( ) );
811 daoUtil.setInt( nIndex++, appointment.getIdActionCancelled( ) );
812 daoUtil.setInt( nIndex++, appointment.getIdActionReported( ) );
813 daoUtil.setInt( nIndex++, appointment.getNotification( ) );
814 daoUtil.setInt( nIndex++, appointment.getIdAdminUser( ) );
815 daoUtil.setString( nIndex++, appointment.getAdminUserCreate( ) );
816 daoUtil.setInt( nIndex++, appointment.getIdUser( ) );
817 daoUtil.setTimestamp( nIndex++, appointment.getAppointmentTakenSqlDate( ) );
818
819 if ( isInsert )
820 {
821 daoUtil.setBoolean( nIndex++, appointment.getIsSurbooked( ) );
822 }
823
824 if ( !isInsert )
825 {
826 daoUtil.setInt( nIndex, appointment.getIdAppointment( ) );
827 }
828 return daoUtil;
829 }
830 }