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