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.notificationstore.business;
35
36 import java.io.IOException;
37 import java.nio.charset.StandardCharsets;
38 import java.sql.Timestamp;
39 import java.util.ArrayList;
40 import java.util.List;
41 import java.util.Optional;
42 import java.util.stream.Collectors;
43
44 import org.apache.commons.lang3.BooleanUtils;
45 import org.apache.commons.lang3.StringUtils;
46
47 import com.fasterxml.jackson.core.JsonParser.Feature;
48 import com.fasterxml.jackson.core.type.TypeReference;
49 import com.fasterxml.jackson.databind.DeserializationFeature;
50 import com.fasterxml.jackson.databind.ObjectMapper;
51 import com.fasterxml.jackson.databind.SerializationFeature;
52
53 import fr.paris.lutece.plugins.grubusiness.business.customer.Customer;
54 import fr.paris.lutece.plugins.grubusiness.business.notification.BackofficeNotification;
55 import fr.paris.lutece.plugins.grubusiness.business.notification.BroadcastNotification;
56 import fr.paris.lutece.plugins.grubusiness.business.notification.EmailNotification;
57 import fr.paris.lutece.plugins.grubusiness.business.notification.EnumNotificationType;
58 import fr.paris.lutece.plugins.grubusiness.business.notification.INotificationDAO;
59 import fr.paris.lutece.plugins.grubusiness.business.notification.MyDashboardNotification;
60 import fr.paris.lutece.plugins.grubusiness.business.notification.Notification;
61 import fr.paris.lutece.plugins.grubusiness.business.notification.NotificationFilter;
62 import fr.paris.lutece.plugins.grubusiness.business.notification.SMSNotification;
63 import fr.paris.lutece.plugins.notificationstore.service.NotificationStorePlugin;
64 import fr.paris.lutece.portal.business.file.File;
65 import fr.paris.lutece.portal.service.file.FileService;
66 import fr.paris.lutece.portal.service.file.FileServiceException;
67 import fr.paris.lutece.portal.service.util.AppLogService;
68 import fr.paris.lutece.portal.service.util.AppPropertiesService;
69 import fr.paris.lutece.util.sql.DAOUtil;
70 import fr.paris.lutece.util.string.StringUtil;
71
72
73
74
75 public final class NotificationDAO implements INotificationDAO
76 {
77 private static final String COLUMN_NOTIFICATION_ID = "id";
78 private static final String COLUMN_DEMAND_ID = "demand_id";
79 private static final String COLUMN_DEMAND_TYPE_ID = "demand_type_id";
80 private static final String COLUMN_DATE = "date";
81 private static final String COLUMN_CUSTOMER = "customer_id";
82
83 private static final String SQL_QUERY_NEW_PK = "SELECT max( id ) FROM notificationstore_notification";
84 private static final String SQL_QUERY_FILTER_SELECT_BASE = "SELECT id, demand_id, demand_type_id, customer_id, date FROM notificationstore_notification ";
85 private static final String SQL_QUERY_FILTER_SELECT_ID_BASE = "SELECT distinct id FROM notificationstore_notification ";
86 private static final String SQL_QUERY_FILTER_WHERE_BASE = " WHERE ";
87 private static final String SQL_QUERY_FILTER_WHERE_DEMANDID = " demand_id = ? ";
88 private static final String SQL_QUERY_FILTER_WHERE_ID_IN = " id in ( %s )";
89 private static final String SQL_QUERY_FILTER_WHERE_DEMANDTYPEID = " demand_type_id = ? ";
90 private static final String SQL_QUERY_FILTER_ORDER = " ORDER BY id ASC";
91 private static final String SQL_QUERY_FILTER_WHERE_START_DATE = " date >= ? ";
92 private static final String SQL_QUERY_FILTER_WHERE_END_DATE = " date <= ? ";
93 private static final String SQL_QUERY_AND = " AND ";
94 private static final String SQL_QUERY_FILTER_NOTIFICATION_TYPE = " id IN (SELECT notification_id FROM notificationstore_notification_content WHERE notification_type in ( ";
95
96 private static final String SQL_QUERY_INSERT = "INSERT INTO notificationstore_notification ( id, demand_id, demand_type_id, customer_id, date ) VALUES ( ?, ?, ?, ?, ? );";
97 private static final String SQL_QUERY_DELETE = "DELETE FROM notificationstore_notification WHERE id = ?";
98 private static final String SQL_QUERY_DELETE_BY_DEMAND = "DELETE FROM notificationstore_notification WHERE demand_id = ? AND demand_type_id = ?";
99 private static final String SQL_QUERY_DISTINCT_DEMAND_TYPE_ID = " SELECT DISTINCT demand_type_id FROM notificationstore_notification ORDER BY demand_type_id ";
100 private static final String SQL_QUERY_SELECT_BY_DEMAND_CUSTOMER_TYPE = " SELECT * FROM notificationstore_notification"
101 + " WHERE demand_id = ? AND demand_type_id = ? AND customer_id = ? ";
102
103 private static final String SQL_QUERY_SELECT_LAST_NOTIFICATION = "SELECT * FROM notificationstore_notification " + " WHERE demand_id = ?"
104 + " AND demand_type_id = ?" + " ORDER BY date desc, id desc " + " LIMIT 1";
105
106 private static final String PROPERTY_DECOMPRESS_NOTIFICATION = "grustoragedb.notification.decompress";
107
108 ObjectMapper _mapper;
109
110
111
112
113 public NotificationDAO( )
114 {
115 super( );
116 _mapper = new ObjectMapper( );
117 _mapper.configure( DeserializationFeature.UNWRAP_ROOT_VALUE, false );
118 _mapper.configure( DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false );
119 _mapper.configure( SerializationFeature.WRAP_ROOT_VALUE, false );
120 _mapper.configure( Feature.ALLOW_UNQUOTED_CONTROL_CHARS, true );
121
122 }
123
124
125
126
127 @Override
128 public List<Notification> loadByDemand( String strDemandId, String strDemandTypeId )
129 {
130 NotificationFilter filter = new NotificationFilter( );
131 filter.setDemandId( strDemandId );
132 filter.setDemandTypeId( strDemandTypeId );
133
134 return loadByFilter( filter );
135 }
136
137
138
139
140 @Override
141 public List<Notification> loadByFilter( NotificationFilter notificationFilter )
142 {
143 String strSQL = getFilterCriteriaClauses( SQL_QUERY_FILTER_SELECT_BASE, notificationFilter );
144
145 try ( DAOUtil daoUtil = new DAOUtil( strSQL, NotificationStorePlugin.getPlugin( ) ) )
146 {
147 addFilterCriteriaValues( daoUtil, notificationFilter );
148
149 daoUtil.executeQuery( );
150
151 return getNotificationsFromDao( daoUtil, notificationFilter );
152 }
153 }
154
155
156
157
158 @Override
159 public List<Integer> loadIdsByFilter( NotificationFilter notificationFilter )
160 {
161 String strSQL = getFilterCriteriaClauses( SQL_QUERY_FILTER_SELECT_ID_BASE, notificationFilter );
162 List<Integer> listIds = new ArrayList<>( );
163
164 try ( DAOUtil daoUtil = new DAOUtil( strSQL, NotificationStorePlugin.getPlugin( ) ) )
165 {
166
167 addFilterCriteriaValues( daoUtil, notificationFilter );
168
169 daoUtil.executeQuery( );
170
171 while ( daoUtil.next( ) )
172 {
173 int nNotificationId = daoUtil.getInt( COLUMN_NOTIFICATION_ID );
174 listIds.add( nNotificationId );
175 }
176
177 return listIds;
178 }
179 }
180
181
182
183
184
185 private String getFilterCriteriaClauses( String strBaseQuery, NotificationFilter notificationFilter )
186 {
187 StringBuilder sbQuery = new StringBuilder( strBaseQuery );
188 boolean hasOneWhere = false;
189
190
191
192 if ( notificationFilter.containsId( ) )
193 {
194 sbQuery.append( BooleanUtils.toString( hasOneWhere, SQL_QUERY_AND, SQL_QUERY_FILTER_WHERE_BASE ) );
195
196 String sql = String.format( SQL_QUERY_FILTER_WHERE_ID_IN,
197 notificationFilter.getIds( ).stream( ).map( v -> "?" ).collect( Collectors.joining( ", " ) ) );
198
199 sbQuery.append( sql );
200 hasOneWhere = true;
201 }
202 if ( notificationFilter.containsDemandId( ) )
203 {
204 sbQuery.append( SQL_QUERY_FILTER_WHERE_BASE );
205 sbQuery.append( SQL_QUERY_FILTER_WHERE_DEMANDID );
206 hasOneWhere = true;
207 }
208 if ( notificationFilter.containsDemandTypeId( ) )
209 {
210 sbQuery.append( BooleanUtils.toString( hasOneWhere, SQL_QUERY_AND, SQL_QUERY_FILTER_WHERE_BASE ) );
211 sbQuery.append( SQL_QUERY_FILTER_WHERE_DEMANDTYPEID );
212 hasOneWhere = true;
213 }
214 if ( notificationFilter.containsNotificationTypeFilter( ) )
215 {
216 sbQuery.append( BooleanUtils.toString( hasOneWhere, SQL_QUERY_AND, SQL_QUERY_FILTER_WHERE_BASE ) );
217 sbQuery.append( SQL_QUERY_FILTER_NOTIFICATION_TYPE );
218 boolean hasOneNotiType = false;
219 if ( notificationFilter.containsBackofficeNotificationType( ) )
220 {
221 sbQuery.append( BooleanUtils.toString( hasOneNotiType, ", ", StringUtils.EMPTY ) );
222 sbQuery.append( "'" + EnumNotificationType.BACKOFFICE.name( ) + "'" );
223 hasOneNotiType = true;
224 }
225 if ( notificationFilter.containsSmsNotificationType( ) )
226 {
227 sbQuery.append( BooleanUtils.toString( hasOneNotiType, ", ", StringUtils.EMPTY ) );
228 sbQuery.append( "'" + EnumNotificationType.SMS.name( ) + "'" );
229 hasOneNotiType = true;
230 }
231 if ( notificationFilter.containsCustomerEmailNotificationType( ) )
232 {
233 sbQuery.append( BooleanUtils.toString( hasOneNotiType, ", ", StringUtils.EMPTY ) );
234 sbQuery.append( "'" + EnumNotificationType.CUSTOMER_EMAIL.name( ) + "'" );
235 hasOneNotiType = true;
236 }
237 if ( notificationFilter.containsMyDashboardNotificationType( ) )
238 {
239 sbQuery.append( BooleanUtils.toString( hasOneNotiType, ", ", StringUtils.EMPTY ) );
240 sbQuery.append( "'" + EnumNotificationType.MYDASHBOARD.name( ) + "'" );
241 hasOneNotiType = true;
242 }
243 if ( notificationFilter.containsBroadcastEmailNotificationType( ) )
244 {
245 sbQuery.append( BooleanUtils.toString( hasOneNotiType, ", ", StringUtils.EMPTY ) );
246 sbQuery.append( "'" + EnumNotificationType.BROADCAST_EMAIL.name( ) + "'" );
247 hasOneNotiType = true;
248 }
249 if ( hasOneNotiType )
250 {
251 sbQuery.append( "))" );
252 }
253 }
254
255 if ( notificationFilter.containsStartDate( ) )
256 {
257 sbQuery.append( BooleanUtils.toString( hasOneWhere, SQL_QUERY_AND, SQL_QUERY_FILTER_WHERE_BASE ) );
258 sbQuery.append( SQL_QUERY_FILTER_WHERE_START_DATE );
259 hasOneWhere = true;
260 }
261 if ( notificationFilter.containsEndDate( ) )
262 {
263 sbQuery.append( BooleanUtils.toString( hasOneWhere, SQL_QUERY_AND, SQL_QUERY_FILTER_WHERE_BASE ) );
264 sbQuery.append( SQL_QUERY_FILTER_WHERE_END_DATE );
265 hasOneWhere = true;
266 }
267
268
269 sbQuery.append( SQL_QUERY_FILTER_ORDER );
270
271 return sbQuery.toString( );
272 }
273
274
275
276
277
278 private void addFilterCriteriaValues( DAOUtil daoUtil, NotificationFilter notificationFilter )
279 {
280 int nIndex = 1;
281
282 if ( notificationFilter.containsId( ) )
283 {
284 for ( Integer id : notificationFilter.getIds( ) )
285 {
286 daoUtil.setInt( nIndex++, id );
287 }
288 }
289 if ( notificationFilter.containsDemandId( ) )
290 {
291 daoUtil.setString( nIndex++, notificationFilter.getDemandId( ) );
292 }
293 if ( notificationFilter.containsDemandTypeId( ) )
294 {
295 daoUtil.setString( nIndex++, notificationFilter.getDemandTypeId( ) );
296 }
297 if ( notificationFilter.containsStartDate( ) )
298 {
299 daoUtil.setTimestamp( nIndex++, new Timestamp( notificationFilter.getStartDate( ) ) );
300 }
301 if ( notificationFilter.containsEndDate( ) )
302 {
303 daoUtil.setTimestamp( nIndex++, new Timestamp( notificationFilter.getEndDate( ) ) );
304 }
305 }
306
307
308
309
310 @Override
311 public synchronized Notification insert( Notification notification )
312 {
313 int nNotificationId = newPrimaryKey( );
314 notification.setId( nNotificationId );
315
316 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, NotificationStorePlugin.getPlugin( ) ) )
317 {
318
319 int nIndex = 1;
320
321 daoUtil.setInt( nIndex++, notification.getId( ) );
322 daoUtil.setString( nIndex++, notification.getDemand( ).getId( ) );
323 daoUtil.setString( nIndex++, notification.getDemand( ).getTypeId( ) );
324
325 String strCustomerId = StringUtils.EMPTY;
326 if( notification.getDemand( ).getCustomer( ) != null
327 && StringUtils.isNotEmpty( notification.getDemand( ).getCustomer( ).getId( ) ) )
328 {
329 strCustomerId = notification.getDemand( ).getCustomer( ).getId( );
330 }
331 daoUtil.setString( nIndex++, strCustomerId );
332
333 daoUtil.setTimestamp( nIndex++, notification.getDate( ) > 0 ? new Timestamp( notification.getDate( ) ) : null );
334
335 daoUtil.executeUpdate( );
336 }
337
338 return notification;
339 }
340
341
342
343
344 @Override
345 public void delete( int id )
346 {
347 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, NotificationStorePlugin.getPlugin( ) ) )
348 {
349 daoUtil.setInt( 1, id );
350 daoUtil.executeUpdate( );
351 }
352 }
353
354
355
356
357 @Override
358 public void deleteByDemand( String strDemandId, String strDemandTypeId )
359 {
360 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_DEMAND, NotificationStorePlugin.getPlugin( ) ) )
361 {
362
363 daoUtil.setString( 1, strDemandId );
364 daoUtil.setString( 2, strDemandTypeId );
365
366 daoUtil.executeUpdate( );
367 }
368 }
369
370
371
372
373
374
375 private int newPrimaryKey( )
376 {
377 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, NotificationStorePlugin.getPlugin( ) ) )
378 {
379 daoUtil.executeQuery( );
380
381 int nKey = 1;
382
383 if ( daoUtil.next( ) )
384 {
385 nKey = daoUtil.getInt( 1 ) + 1;
386 }
387
388 return nKey;
389 }
390
391 }
392
393
394
395
396 @Override
397 public Optional<Notification> loadById( int id )
398 {
399 NotificationFilter filter = new NotificationFilter( );
400 List<Integer> list = new ArrayList<>( );
401 list.add( id );
402 filter.setIds( list );
403
404 List<Notification> listNotifs = loadByFilter( filter );
405
406 if ( listNotifs.size( ) == 1 )
407 {
408 return Optional.of( listNotifs.get( 0 ) );
409 }
410
411 return Optional.empty( );
412 }
413
414
415
416
417 @Override
418 public List<Notification> loadByIds( List<Integer> listIds )
419 {
420 if ( listIds != null && listIds.isEmpty( ) )
421 {
422 return new ArrayList<>( );
423 }
424 else
425 {
426 NotificationFilter filter = new NotificationFilter( );
427 filter.setIds( listIds );
428
429 return loadByFilter( filter );
430 }
431 }
432
433
434
435
436 @Override
437 public List<Notification> loadByDemandAndDate( String strDemandId, String strDemandTypeId, long lDate )
438 {
439 NotificationFilter filter = new NotificationFilter( );
440 filter.setDemandId( strDemandId );
441 filter.setDemandTypeId( strDemandTypeId );
442 filter.setStartDate( lDate );
443 filter.setEndDate( lDate );
444
445 return loadByFilter( filter );
446 }
447
448
449
450
451 @Override
452 public List<String> loadDistinctDemandTypeIds( )
453 {
454 List<String> listIds = new ArrayList<>( );
455
456 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DISTINCT_DEMAND_TYPE_ID, NotificationStorePlugin.getPlugin( ) ) )
457 {
458
459 daoUtil.executeQuery( );
460
461 while ( daoUtil.next( ) )
462 {
463 String strId = daoUtil.getString( 1 );
464 listIds.add( strId );
465 }
466
467 return listIds;
468 }
469 }
470
471
472
473
474
475
476
477 private List<Notification> getNotificationsFromDao( DAOUtil daoUtil, NotificationFilter notificationFilter )
478 {
479 List<Notification> listNotifications = new ArrayList<>( );
480 while ( daoUtil.next( ) )
481 {
482 Notification notification = new Notification( );
483 notification.setId( daoUtil.getInt( COLUMN_NOTIFICATION_ID ) );
484 notification.setDate( daoUtil.getTimestamp( COLUMN_DATE ) != null ? daoUtil.getTimestamp( COLUMN_DATE ).getTime ( ) : 0 );
485
486 String strIdDemand = daoUtil.getString( COLUMN_DEMAND_ID );
487 String strDemandTypeId = daoUtil.getString( COLUMN_DEMAND_TYPE_ID );
488 notification.setDemand( DemandHome.getDemandByDemandIdAndTypeId( strIdDemand, strDemandTypeId ) );
489 setNotificationContent( notification, notificationFilter );
490
491 Customer customer = new Customer ();
492 customer.setId( daoUtil.getString( COLUMN_CUSTOMER ) );
493 notification.getDemand( ).setCustomer( customer );
494
495 listNotifications.add( notification );
496 }
497
498 return listNotifications;
499 }
500
501
502
503
504
505
506 private void setNotificationContent( Notification notif, NotificationFilter notificationFilter )
507 {
508 List<NotificationContent> listNotificiationContent = NotificationContentHome.getNotificationContentsByIdAndTypeNotification( notif.getId( ),
509 notificationFilter.getListNotificationType( ) );
510
511 for ( NotificationContent notifContent : listNotificiationContent )
512 {
513 if ( EnumNotificationType.BACKOFFICE.name( ).equals( notifContent.getNotificationType( ) ) )
514 {
515 notif.setBackofficeNotification( convertToObject( notifContent, new TypeReference<BackofficeNotification>( )
516 {
517 } ) );
518 }
519 if ( EnumNotificationType.BROADCAST_EMAIL.name( ).equals( notifContent.getNotificationType( ) ) )
520 {
521 notif.setBroadcastEmail( convertToObject( notifContent, new TypeReference<List<BroadcastNotification>>( )
522 {
523 } ) );
524 }
525 if ( EnumNotificationType.CUSTOMER_EMAIL.name( ).equals( notifContent.getNotificationType( ) ) )
526 {
527 notif.setEmailNotification( convertToObject( notifContent, new TypeReference<EmailNotification>( )
528 {
529 } ) );
530 }
531 if ( EnumNotificationType.MYDASHBOARD.name( ).equals( notifContent.getNotificationType( ) ) )
532 {
533 notif.setMyDashboardNotification( convertToObject( notifContent, new TypeReference<MyDashboardNotification>( )
534 {
535 } ) );
536 }
537 if ( EnumNotificationType.SMS.name( ).equals( notifContent.getNotificationType( ) ) )
538 {
539 notif.setSmsNotification( convertToObject( notifContent, new TypeReference<SMSNotification>( )
540 {
541 } ) );
542 }
543 }
544 }
545
546
547
548
549
550
551
552
553 private <T> T convertToObject( NotificationContent notifContent, TypeReference<T> typeReference )
554 {
555 try
556 {
557 File file = FileService.getInstance( ).getFileStoreServiceProvider( notifContent.getFileStore( ) ).getFile( notifContent.getFileKey( ) );
558
559 String strNotification;
560
561 if ( AppPropertiesService.getPropertyBoolean( PROPERTY_DECOMPRESS_NOTIFICATION, false ) )
562 {
563 strNotification = StringUtil.decompress( file.getPhysicalFile( ).getValue( ) );
564 }
565 else
566 {
567 strNotification = new String( file.getPhysicalFile( ).getValue( ), StandardCharsets.UTF_8 );
568 }
569 return _mapper.readValue( strNotification, typeReference );
570
571 }
572 catch( FileServiceException | IOException e )
573 {
574 AppLogService.error( "Error while reading JSON of notification " + notifContent.getIdNotification( ), e );
575 }
576
577 return null;
578 }
579
580 @Override
581 public List<Notification> loadByDemandIdTypeIdCustomerId( String strDemandId, String strDemandTypeId, String strCustomerId )
582 {
583 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DEMAND_CUSTOMER_TYPE, NotificationStorePlugin.getPlugin( ) ) )
584 {
585 daoUtil.setString( 1, strDemandId );
586 daoUtil.setString( 2, strDemandTypeId );
587 daoUtil.setString( 3, strCustomerId );
588
589 daoUtil.executeQuery( );
590
591 List<Notification> listNotifications = new ArrayList<>( );
592
593 while ( daoUtil.next( ) )
594 {
595 Notification notification = new Notification( );
596 notification.setId( daoUtil.getInt( COLUMN_NOTIFICATION_ID ) );
597 notification.setDate( daoUtil.getTimestamp( COLUMN_DATE ) != null ? daoUtil.getTimestamp( COLUMN_DATE ).getTime ( ) : 0 );
598
599 notification.setDemand( DemandHome.getDemandByDemandIdAndTypeId( strDemandId, strDemandTypeId ) );
600 setNotificationContent( notification, new NotificationFilter( ) );
601
602 Customer customer = new Customer ();
603 customer.setId( daoUtil.getString( COLUMN_CUSTOMER ) );
604 notification.getDemand( ).setCustomer( customer );
605
606 listNotifications.add( notification );
607 }
608
609 return listNotifications;
610 }
611 }
612
613 @Override
614 public Notification loadLastNotifByDemandIdAndDemandTypeId( String strDemandId, String strDemandTypeId )
615 {
616 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_NOTIFICATION, NotificationStorePlugin.getPlugin( ) ) )
617 {
618 daoUtil.setString( 1, strDemandId );
619 daoUtil.setString( 2, strDemandTypeId );
620
621 daoUtil.executeQuery( );
622
623 Notification notification = null;
624
625 while ( daoUtil.next( ) )
626 {
627 notification = new Notification( );
628 notification.setId( daoUtil.getInt( COLUMN_NOTIFICATION_ID ) );
629 notification.setDate( daoUtil.getTimestamp( COLUMN_DATE ) != null ? daoUtil.getTimestamp( COLUMN_DATE ).getTime ( ) : 0 );
630 setNotificationContent( notification, new NotificationFilter( ) );
631
632 }
633
634 return notification;
635 }
636 }
637 }