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