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.genericalert.business;
35
36 import java.util.ArrayList;
37 import java.util.Collection;
38 import java.util.List;
39
40 import fr.paris.lutece.plugins.genericalert.service.NotifyReminderPlugin;
41 import fr.paris.lutece.portal.service.plugin.Plugin;
42 import fr.paris.lutece.util.sql.DAOUtil;
43
44
45
46
47
48 public final class TaskNotifyReminderConfigDAO implements ITaskNotifyReminderConfigDAO
49 {
50
51 private static final String SQL_QUERY_SELECT = "SELECT id_task, id_form, nb_alerts FROM workflow_task_notify_reminder_cf ";
52 private static final String SQL_QUERY_INSERT = "INSERT INTO workflow_task_notify_reminder_cf ( id_task, id_form, nb_alerts ) VALUES ( ?, ?, ? ) ";
53 private static final String SQL_QUERY_DELETE = "DELETE FROM workflow_task_notify_reminder_cf WHERE id_task = ?";
54 private static final String SQL_QUERY_UPDATE = "UPDATE workflow_task_notify_reminder_cf SET nb_alerts = ? WHERE id_task = ?";
55 private static final String SQL_QUERY_SELECTALL = "SELECT id_task, id_form, nb_alerts FROM workflow_task_notify_reminder_cf";
56 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_task FROM workflow_task_notify_reminder_cf";
57 private static final String SQL_QUERY_SELECT_ID_FORM_BY_TASK = "SELECT id_form FROM workflow_task_notify_reminder_cf where id_task = ? ";
58 private static final String SQL_QUERY_WHERE = " WHERE ";
59 private static final String SQL_QUERY_AND = " AND ";
60 private static final String SQL_ID_TASK = " id_task = ?";
61 private static final String SQL_ID_FORM = " id_form = ?";
62
63 private static final String SQL_QUERY_FIND_REMINDER_APPOINTMENT_BY_PRIMARY_KEY = "SELECT id_task, id_form, rank, time_to_alert, email_notify, sms_notify, email_alert_message, sms_alert_message, alert_subject, email_cc, phone_number, id_state_after FROM workflow_appointment_reminder WHERE id_form = ? AND id_task = ? ";
64 private static final String SQL_QUERY_INSERT_REMINDER_APPOINTMENT_FORM_MESSAGE = "INSERT INTO workflow_appointment_reminder(id_task,id_form, rank, time_to_alert, email_notify, sms_notify, email_alert_message, sms_alert_message, alert_subject, email_cc, phone_number, id_state_after) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
65 private static final String SQL_QUERY_UPDATE_REMINDER_APPOINTMENT_FORM_MESSAGE = "UPDATE workflow_appointment_reminder SET time_to_alert = ?, email_notify = ?, sms_notify = ?, email_alert_message = ?, sms_alert_message = ?, alert_subject = ?, email_cc = ?, phone_number = ?, id_state_after = ? WHERE id_form = ? AND id_task = ?";
66 private static final String SQL_QUERY_DELETE_REMINDER_APPOINTMENT_FORM_MESSAGE = "DELETE FROM workflow_appointment_reminder WHERE id_form = ? AND id_task= ? ";
67 private static final String SQL_QUERY_RANK = " AND rank = ?";
68 private static final String SQL_QUERY_ORDER_BY_RANK = " ORDER BY rank";
69
70
71
72
73 @Override
74 public void insert( TaskNotifyReminderConfig taskReminderConfig )
75 {
76 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, NotifyReminderPlugin.getPlugin( ) ) )
77 {
78 daoUtil.setInt( 1, taskReminderConfig.getIdTask( ) );
79 daoUtil.setInt( 2, taskReminderConfig.getIdForm( ) );
80 daoUtil.setInt( 3, taskReminderConfig.getNbAlerts( ) );
81
82 daoUtil.executeUpdate( );
83 daoUtil.free( );
84 if ( taskReminderConfig != null )
85 {
86 if ( taskReminderConfig.getListReminderAppointment( ).size( ) > 0 )
87 {
88 insertListReminderAppointment( taskReminderConfig.getIdTask( ), taskReminderConfig.getListReminderAppointment( ),
89 NotifyReminderPlugin.getPlugin( ) );
90 }
91 }
92 }
93 }
94
95
96
97
98 @Override
99 public TaskNotifyReminderConfig load( int nKey )
100 {
101 TaskNotifyReminderConfig taskReminderConfig = null;
102
103 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_QUERY_WHERE + SQL_ID_TASK, NotifyReminderPlugin.getPlugin( ) ) )
104 {
105 daoUtil.setInt( 1, nKey );
106 daoUtil.executeQuery( );
107
108 if ( daoUtil.next( ) )
109 {
110 taskReminderConfig = new TaskNotifyReminderConfig( );
111 taskReminderConfig.setIdTask( daoUtil.getInt( 1 ) );
112 taskReminderConfig.setIdForm( daoUtil.getInt( 2 ) );
113 taskReminderConfig.setNbAlerts( daoUtil.getInt( 3 ) );
114 }
115 }
116
117 if ( taskReminderConfig != null )
118 {
119 List<ReminderAppointment> listReminder = loadListReminderAppointment( taskReminderConfig.getIdTask( ), taskReminderConfig.getIdForm( ),
120 NotifyReminderPlugin.getPlugin( ) );
121
122 if ( listReminder != null )
123 {
124 taskReminderConfig.setListReminderAppointment( listReminder );
125 }
126 }
127 return taskReminderConfig;
128 }
129
130
131
132
133 @Override
134 public TaskNotifyReminderConfig loadConfigByIdForm( int idForm, Plugin plugin )
135 {
136 TaskNotifyReminderConfig taskReminderConfig = null;
137
138 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_QUERY_WHERE + SQL_ID_FORM, plugin ) )
139 {
140 daoUtil.setInt( 1, idForm );
141 daoUtil.executeQuery( );
142
143 if ( daoUtil.next( ) )
144 {
145 taskReminderConfig = new TaskNotifyReminderConfig( );
146 taskReminderConfig.setIdTask( daoUtil.getInt( 1 ) );
147 taskReminderConfig.setIdForm( daoUtil.getInt( 2 ) );
148 taskReminderConfig.setNbAlerts( daoUtil.getInt( 3 ) );
149 }
150 }
151 if ( taskReminderConfig != null )
152 {
153 List<ReminderAppointment> listReminder = loadListReminderAppointment( taskReminderConfig.getIdTask( ), taskReminderConfig.getIdForm( ),
154 NotifyReminderPlugin.getPlugin( ) );
155
156 if ( listReminder != null )
157 {
158 taskReminderConfig.setListReminderAppointment( listReminder );
159 }
160 }
161 return taskReminderConfig;
162 }
163
164
165
166
167 @Override
168 public void store( TaskNotifyReminderConfig taskReminderConfig )
169 {
170 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE + SQL_QUERY_AND + SQL_ID_FORM, NotifyReminderPlugin.getPlugin( ) ) )
171 {
172 daoUtil.setInt( 1, taskReminderConfig.getNbAlerts( ) );
173 daoUtil.setInt( 2, taskReminderConfig.getIdTask( ) );
174 daoUtil.setInt( 3, taskReminderConfig.getIdForm( ) );
175
176 daoUtil.executeUpdate( );
177 }
178
179 if ( taskReminderConfig.getListReminderAppointment( ).size( ) > 0 )
180 {
181 storeReminderAppointment( taskReminderConfig.getIdTask( ), taskReminderConfig.getListReminderAppointment( ), NotifyReminderPlugin.getPlugin( ) );
182 }
183 }
184
185
186
187
188 @Override
189 public void delete( int idTask )
190 {
191 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, NotifyReminderPlugin.getPlugin( ) ) )
192 {
193 Collection<Integer> listIdFormList = selectIdFormByTask( idTask, NotifyReminderPlugin.getPlugin( ) );
194 for ( Integer e : listIdFormList )
195 {
196 deleteListReminderAppointment( idTask, e, NotifyReminderPlugin.getPlugin( ) );
197 }
198
199 daoUtil.setInt( 1, idTask );
200 daoUtil.executeUpdate( );
201 }
202 }
203
204
205
206
207 @Override
208 public Collection<TaskNotifyReminderConfig> selectTaskReminderConfigsList( Plugin plugin )
209 {
210 Collection<TaskNotifyReminderConfig> taskReminderConfigList = new ArrayList<>( );
211 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
212 {
213 daoUtil.executeQuery( );
214
215 while ( daoUtil.next( ) )
216 {
217 TaskNotifyReminderConfigtifyReminderConfig.html#TaskNotifyReminderConfig">TaskNotifyReminderConfig taskReminderConfig = new TaskNotifyReminderConfig( );
218
219 taskReminderConfig.setIdTask( daoUtil.getInt( 1 ) );
220 taskReminderConfig.setIdForm( daoUtil.getInt( 2 ) );
221 taskReminderConfig.setNbAlerts( daoUtil.getInt( 3 ) );
222
223 taskReminderConfigList.add( taskReminderConfig );
224 }
225 }
226 return taskReminderConfigList;
227 }
228
229
230
231
232 @Override
233 public void deleteReminderAppointment( int nIdTask, int nIdForm, int rank, boolean b, Plugin plugin )
234 {
235 DAOUtil daoUtil = null;
236 if ( b )
237 {
238 daoUtil = new DAOUtil( SQL_QUERY_DELETE_REMINDER_APPOINTMENT_FORM_MESSAGE, plugin );
239 daoUtil.setInt( 1, nIdForm );
240 daoUtil.setInt( 2, nIdTask );
241 }
242 else
243 {
244 daoUtil = new DAOUtil( SQL_QUERY_DELETE_REMINDER_APPOINTMENT_FORM_MESSAGE + SQL_QUERY_RANK, plugin );
245 daoUtil.setInt( 1, nIdForm );
246 daoUtil.setInt( 2, nIdTask );
247 daoUtil.setInt( 3, rank );
248 }
249
250 daoUtil.executeUpdate( );
251 daoUtil.free( );
252 }
253
254
255
256
257 @Override
258 public Collection<Integer> selectIdTaskReminderConfigsList( Plugin plugin )
259 {
260 Collection<Integer> taskReminderConfigList = new ArrayList<>( );
261 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
262 {
263 daoUtil.executeQuery( );
264
265 while ( daoUtil.next( ) )
266 {
267 taskReminderConfigList.add( daoUtil.getInt( 1 ) );
268 }
269
270 }
271 return taskReminderConfigList;
272 }
273
274
275
276
277 @Override
278 public List<ReminderAppointment> loadListReminderAppointment( int idTask, int nAppointmentFormId, Plugin plugin )
279 {
280 List<ReminderAppointment> list = new ArrayList<>( );
281
282 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_REMINDER_APPOINTMENT_BY_PRIMARY_KEY + SQL_QUERY_ORDER_BY_RANK, plugin ) )
283 {
284 daoUtil.setInt( 1, nAppointmentFormId );
285 daoUtil.setInt( 2, idTask );
286 daoUtil.executeQuery( );
287
288 ReminderAppointment reminderAppointment;
289 while ( daoUtil.next( ) )
290 {
291 reminderAppointment = new ReminderAppointment( );
292
293 int nIndex = 1;
294
295 reminderAppointment.setIdTask( daoUtil.getInt( nIndex++ ) );
296 reminderAppointment.setIdForm( daoUtil.getInt( nIndex++ ) );
297 reminderAppointment.setRank( daoUtil.getInt( nIndex++ ) );
298 reminderAppointment.setTimeToAlert( daoUtil.getInt( nIndex++ ) );
299 reminderAppointment.setEmailNotify( daoUtil.getBoolean( nIndex++ ) );
300 reminderAppointment.setSmsNotify( daoUtil.getBoolean( nIndex++ ) );
301 reminderAppointment.setEmailAlertMessage( daoUtil.getString( nIndex++ ) );
302 reminderAppointment.setSmsAlertMessage( daoUtil.getString( nIndex++ ) );
303 reminderAppointment.setAlertSubject( daoUtil.getString( nIndex++ ) );
304 reminderAppointment.setEmailCc( daoUtil.getString( nIndex++ ) );
305 reminderAppointment.setNumberPhone( daoUtil.getString( nIndex++ ) );
306 reminderAppointment.setIdStateAfter( daoUtil.getInt( nIndex ) );
307 list.add( reminderAppointment );
308 }
309 }
310
311 return list;
312 }
313
314
315
316
317 public List<TaskNotifyReminderConfig> loadListTaskNotifyConfig( int idTask, Plugin plugin )
318 {
319 List<TaskNotifyReminderConfig> listConfig = new ArrayList<>( );
320
321 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_QUERY_WHERE + SQL_ID_TASK, plugin ) )
322 {
323 daoUtil.setInt( 1, idTask );
324 daoUtil.executeQuery( );
325 while ( daoUtil.next( ) )
326 {
327 TaskNotifyReminderConfigtifyReminderConfig.html#TaskNotifyReminderConfig">TaskNotifyReminderConfig taskReminderConfig = new TaskNotifyReminderConfig( );
328 List<ReminderAppointment> listReminder = new ArrayList<>( );
329
330 taskReminderConfig.setIdTask( daoUtil.getInt( 1 ) );
331 taskReminderConfig.setIdForm( daoUtil.getInt( 2 ) );
332 taskReminderConfig.setNbAlerts( daoUtil.getInt( 3 ) );
333
334 listReminder = loadListReminderAppointment( idTask, taskReminderConfig.getIdForm( ), plugin );
335
336 if ( listReminder != null )
337 {
338 taskReminderConfig.setListReminderAppointment( listReminder );
339 }
340 listConfig.add( taskReminderConfig );
341
342 }
343 }
344 return listConfig;
345 }
346
347
348
349
350 @Override
351 public TaskNotifyReminderConfig loadByIdForm( int nKey, int idForm, Plugin plugin )
352 {
353 TaskNotifyReminderConfig taskReminderConfig = null;
354
355 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_QUERY_WHERE + SQL_ID_TASK + SQL_QUERY_AND + SQL_ID_FORM, plugin ) )
356 {
357 daoUtil.setInt( 1, nKey );
358 daoUtil.setInt( 2, idForm );
359 daoUtil.executeQuery( );
360
361 if ( daoUtil.next( ) )
362 {
363 taskReminderConfig = new TaskNotifyReminderConfig( );
364 taskReminderConfig.setIdTask( daoUtil.getInt( 1 ) );
365 taskReminderConfig.setIdForm( daoUtil.getInt( 2 ) );
366 taskReminderConfig.setNbAlerts( daoUtil.getInt( 3 ) );
367 }
368 }
369 if ( taskReminderConfig != null )
370 {
371 List<ReminderAppointment> listReminder = loadListReminderAppointment( nKey, idForm, plugin );
372
373 if ( listReminder != null )
374 {
375 taskReminderConfig.setListReminderAppointment( listReminder );
376 }
377 }
378 return taskReminderConfig;
379 }
380
381
382
383
384
385
386
387
388
389 private void insertReminderAppointment( int nIdTask, ReminderAppointment reminderAppointment, Plugin plugin )
390 {
391 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_REMINDER_APPOINTMENT_FORM_MESSAGE, plugin ) )
392 {
393 int nIndex = 1;
394
395 daoUtil.setInt( nIndex++, nIdTask );
396 daoUtil.setInt( nIndex++, reminderAppointment.getIdForm( ) );
397 daoUtil.setInt( nIndex++, reminderAppointment.getRank( ) );
398 daoUtil.setInt( nIndex++, reminderAppointment.getTimeToAlert( ) );
399 daoUtil.setBoolean( nIndex++, reminderAppointment.isEmailNotify( ) );
400 daoUtil.setBoolean( nIndex++, reminderAppointment.isSmsNotify( ) );
401 daoUtil.setString( nIndex++, reminderAppointment.getEmailAlertMessage( ) );
402 daoUtil.setString( nIndex++, reminderAppointment.getSmsAlertMessage( ) );
403 daoUtil.setString( nIndex++, reminderAppointment.getAlertSubject( ) );
404 daoUtil.setString( nIndex++, reminderAppointment.getEmailCc( ) );
405 daoUtil.setString( nIndex++, reminderAppointment.getNumberPhone( ) );
406 daoUtil.setInt( nIndex, reminderAppointment.getIdStateAfter( ) );
407 daoUtil.executeUpdate( );
408 }
409 }
410
411
412
413
414
415
416
417
418
419 private void insertListReminderAppointment( int nIdTask, List<ReminderAppointment> listReminderAppointment, Plugin plugin )
420 {
421 for ( ReminderAppointment reminderAppointment : listReminderAppointment )
422 {
423 insertReminderAppointment( nIdTask, reminderAppointment, plugin );
424 }
425 }
426
427
428
429
430
431
432
433
434
435
436
437 private void storeReminderAppointment( int idTask, List<ReminderAppointment> listReminderAppointment, Plugin plugin )
438 {
439
440 for ( ReminderAppointment reminderAppointment : listReminderAppointment )
441 {
442 if ( loadReminderAppointment( idTask, reminderAppointment.getIdForm( ), reminderAppointment.getRank( ), plugin ) == null )
443 {
444 insertReminderAppointment( idTask, reminderAppointment, plugin );
445 }
446 else
447 {
448 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_REMINDER_APPOINTMENT_FORM_MESSAGE + SQL_QUERY_RANK, plugin ) )
449 {
450 int nIndex = 1;
451
452 daoUtil.setInt( nIndex++, reminderAppointment.getTimeToAlert( ) );
453 daoUtil.setBoolean( nIndex++, reminderAppointment.isEmailNotify( ) );
454 daoUtil.setBoolean( nIndex++, reminderAppointment.isSmsNotify( ) );
455 daoUtil.setString( nIndex++, reminderAppointment.getEmailAlertMessage( ) );
456 daoUtil.setString( nIndex++, reminderAppointment.getSmsAlertMessage( ) );
457 daoUtil.setString( nIndex++, reminderAppointment.getAlertSubject( ) );
458 daoUtil.setString( nIndex++, reminderAppointment.getEmailCc( ) );
459 daoUtil.setString( nIndex++, reminderAppointment.getNumberPhone( ) );
460 daoUtil.setInt( nIndex++, reminderAppointment.getIdStateAfter( ) );
461 daoUtil.setInt( nIndex++, reminderAppointment.getIdForm( ) );
462 daoUtil.setInt( nIndex++, reminderAppointment.getIdTask( ) );
463 daoUtil.setInt( nIndex, reminderAppointment.getRank( ) );
464
465 daoUtil.executeUpdate( );
466 }
467 }
468 }
469 }
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484 private ReminderAppointment loadReminderAppointment( int idTask, int nAppointmentFormId, int rank, Plugin plugin )
485 {
486 ReminderAppointment reminderAppointment;
487
488 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_REMINDER_APPOINTMENT_BY_PRIMARY_KEY + SQL_QUERY_RANK, plugin ) )
489 {
490 daoUtil.setInt( 1, nAppointmentFormId );
491 daoUtil.setInt( 2, idTask );
492 daoUtil.setInt( 3, rank );
493 daoUtil.executeQuery( );
494 if ( daoUtil.next( ) )
495 {
496 reminderAppointment = new ReminderAppointment( );
497 int nIndex = 1;
498 reminderAppointment.setIdTask( daoUtil.getInt( nIndex++ ) );
499 reminderAppointment.setIdForm( daoUtil.getInt( nIndex++ ) );
500 reminderAppointment.setRank( daoUtil.getInt( nIndex++ ) );
501 reminderAppointment.setTimeToAlert( daoUtil.getInt( nIndex++ ) );
502 reminderAppointment.setEmailNotify( daoUtil.getBoolean( nIndex++ ) );
503 reminderAppointment.setSmsNotify( daoUtil.getBoolean( nIndex++ ) );
504 reminderAppointment.setEmailAlertMessage( daoUtil.getString( nIndex++ ) );
505 reminderAppointment.setSmsAlertMessage( daoUtil.getString( nIndex++ ) );
506 reminderAppointment.setAlertSubject( daoUtil.getString( nIndex++ ) );
507 reminderAppointment.setEmailCc( daoUtil.getString( nIndex++ ) );
508 reminderAppointment.setNumberPhone( daoUtil.getString( nIndex++ ) );
509 reminderAppointment.setIdStateAfter( daoUtil.getInt( nIndex ) );
510 }
511 else
512 {
513 reminderAppointment = null;
514 }
515 }
516
517 return reminderAppointment;
518 }
519
520
521
522
523
524
525
526
527
528
529
530 private void deleteListReminderAppointment( int idTask, int nAppointmentFormId, Plugin plugin )
531 {
532 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_REMINDER_APPOINTMENT_FORM_MESSAGE, plugin ) )
533 {
534 daoUtil.setInt( 1, nAppointmentFormId );
535 daoUtil.setInt( 2, idTask );
536 daoUtil.executeUpdate( );
537 }
538 }
539
540
541
542
543
544
545
546
547
548 private Collection<Integer> selectIdFormByTask( int idTask, Plugin plugin )
549 {
550 Collection<Integer> listIdFormList = new ArrayList<>( );
551 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ID_FORM_BY_TASK, plugin ) )
552 {
553 daoUtil.setInt( 1, idTask );
554 daoUtil.executeQuery( );
555
556 while ( daoUtil.next( ) )
557 {
558 listIdFormList.add( daoUtil.getInt( 1 ) );
559 }
560 }
561
562 return listIdFormList;
563 }
564 }