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