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.sql.Statement;
37 import java.sql.Timestamp;
38 import java.util.ArrayList;
39 import java.util.List;
40 import java.util.Optional;
41 import java.util.stream.Collectors;
42
43 import org.apache.commons.lang3.StringUtils;
44
45 import fr.paris.lutece.plugins.grubusiness.business.customer.Customer;
46 import fr.paris.lutece.plugins.grubusiness.business.demand.Demand;
47 import fr.paris.lutece.plugins.grubusiness.business.notification.EnumNotificationType;
48 import fr.paris.lutece.plugins.grubusiness.business.notification.Event;
49 import fr.paris.lutece.plugins.grubusiness.business.notification.INotificationEventDAO;
50 import fr.paris.lutece.plugins.grubusiness.business.notification.NotificationEvent;
51 import fr.paris.lutece.plugins.grubusiness.business.notification.NotificationFilter;
52 import fr.paris.lutece.plugins.notificationstore.service.NotificationStorePlugin;
53 import fr.paris.lutece.util.sql.DAOUtil;
54
55
56
57
58 public final class NotificationEventDAO implements INotificationEventDAO
59 {
60
61 private static final String SQL_QUERY_SELECTALL = "SELECT id, event_date, type, status, redelivry, message, msg_id, demand_id, demand_type_id, customer_id, notification_date FROM notificationstore_notification_event ";
62 private static final String SQL_QUERY_INSERT = "INSERT INTO notificationstore_notification_event ( event_date, type, status, redelivry, message, demand_id, demand_type_id, customer_id, notification_date, msg_id ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
63 private static final String SQL_QUERY_DELETE = "DELETE FROM notificationstore_notification_event WHERE id = ? ";
64 private static final String SQL_QUERY_DELETE_BY_CUSTOMER_ID = "DELETE FROM notificationstore_notification_event WHERE customer_id = ? ";
65 private static final String SQL_QUERY_DELETE_BY_DATE = "DELETE FROM notificationstore_notification_event WHERE event_date < ? ";
66 private static final String SQL_QUERY_SELECT_BY_DEMAND = SQL_QUERY_SELECTALL + " WHERE demand_id = ? AND demand_type_id = ? ";
67 private static final String SQL_QUERY_SELECT_BY_NOTIFICATION = SQL_QUERY_SELECTALL
68 + " WHERE demand_id = ? AND demand_type_id = ? and notification_date = ? ";
69 private static final String SQL_QUERY_SELECT_BY_FILTER = SQL_QUERY_SELECTALL + " WHERE 1 ";
70 private static final String SQL_QUERY_FILTER_BY_ID = " AND id in ( %s ) ";
71 private static final String SQL_QUERY_FILTER_BY_DEMAND_ID = " AND demand_id = ? ";
72 private static final String SQL_QUERY_FILTER_BY_DEMAND_TYPE_ID = " AND demand_type_id = ? ";
73 private static final String SQL_QUERY_FILTER_BY_STARTDATE = " AND event_date >= ? ";
74 private static final String SQL_QUERY_FILTER_BY_ENDDATE = " AND event_date <= ? ";
75 private static final String SQL_QUERY_FILTER_BY_STATUS = " AND status = ? ";
76 private static final String SQL_QUERY_FILTER_BY_TYPE = " AND type in ( ";
77 private static final String SQL_QUERY_FILTER_ORDER_BY = " ORDER BY event_date DESC ";
78
79
80
81
82 @Override
83 public NotificationEvent insert( NotificationEvent notificationEvent )
84 {
85 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, NotificationStorePlugin.getPlugin( ) ) )
86 {
87 int nIndex = 1;
88 daoUtil.setTimestamp( nIndex++,
89 notificationEvent.getEvent( ).getEventDate( ) > 0 ? new Timestamp( notificationEvent.getEvent( ).getEventDate( ) ) : null );
90 daoUtil.setString( nIndex++, notificationEvent.getEvent( ).getType( ) );
91 daoUtil.setString( nIndex++, notificationEvent.getEvent( ).getStatus( ) );
92 daoUtil.setInt( nIndex++, notificationEvent.getEvent( ).getRedelivry( ) );
93 daoUtil.setString( nIndex++, notificationEvent.getEvent( ).getMessage( ) );
94 daoUtil.setString( nIndex++, String.valueOf( notificationEvent.getDemand( ).getId( ) ) );
95 daoUtil.setString( nIndex++, String.valueOf( notificationEvent.getDemand( ).getTypeId( ) ) );
96 String strCustomerId = StringUtils.EMPTY;
97
98 if ( notificationEvent.getDemand( ).getCustomer( ) != null
99 && StringUtils.isNotEmpty( notificationEvent.getDemand( ).getCustomer( ).getCustomerId( ) ) )
100 {
101 strCustomerId = notificationEvent.getDemand( ).getCustomer( ).getCustomerId( );
102 }
103 daoUtil.setString( nIndex++, strCustomerId );
104 daoUtil.setTimestamp( nIndex++, notificationEvent.getNotificationDate( ) > 0 ? new Timestamp( notificationEvent.getNotificationDate( ) ) : null );
105 daoUtil.setString( nIndex++, notificationEvent.getMsgId( ) );
106
107 daoUtil.executeUpdate( );
108 if ( daoUtil.nextGeneratedKey( ) )
109 {
110 notificationEvent.setId( daoUtil.getGeneratedKeyInt( 1 ) );
111 }
112
113 return notificationEvent;
114 }
115
116 }
117
118
119
120
121 @Override
122 public Optional<NotificationEvent> loadById( int nKey )
123 {
124 List<Integer> list = new ArrayList<>( );
125 list.add( nKey );
126
127 List<NotificationEvent> listEvent = loadByIds( list );
128
129 if ( listEvent.size( ) == 1 )
130 {
131 return Optional.of( listEvent.get( 0 ) );
132 }
133 else
134 {
135 return Optional.empty( );
136 }
137 }
138
139
140
141
142 @Override
143 public List<NotificationEvent> loadByIds( List<Integer> listIds )
144 {
145 if ( listIds != null && !listIds.isEmpty( ) )
146 {
147 NotificationFilter filter = new NotificationFilter( );
148 filter.setIds( listIds );
149
150 return loadByFilter( filter );
151 }
152 else
153 {
154 return new ArrayList<>( );
155 }
156 }
157
158
159
160
161 @Override
162 public void delete( int nKey )
163 {
164 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, NotificationStorePlugin.getPlugin( ) ) )
165 {
166 daoUtil.setInt( 1, nKey );
167 daoUtil.executeUpdate( );
168 daoUtil.free( );
169 }
170 }
171
172
173
174
175 @Override
176 public void deleteByCustomerId( String strCustomerId )
177 {
178 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_CUSTOMER_ID, NotificationStorePlugin.getPlugin( ) ) )
179 {
180 daoUtil.setString( 1, strCustomerId );
181 daoUtil.executeUpdate( );
182 }
183 }
184
185
186
187
188 @Override
189 public List<NotificationEvent> loadByDemand( String strDemandId, String strDemandTypeId )
190 {
191 List<NotificationEvent> notificationEventList = new ArrayList<>( );
192 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DEMAND, NotificationStorePlugin.getPlugin( ) ) )
193 {
194 daoUtil.setString( 1, strDemandId );
195 daoUtil.setString( 2, strDemandTypeId );
196
197 daoUtil.executeQuery( );
198
199 while ( daoUtil.next( ) )
200 {
201 NotificationEvent notificationEvent = getItemFromDao( daoUtil );
202
203 notificationEventList.add( notificationEvent );
204 }
205
206 daoUtil.free( );
207 return notificationEventList;
208 }
209 }
210
211
212
213
214 @Override
215 public List<NotificationEvent> loadByNotification( String strDemandId, String strDemandTypeId, long lNotificationDate )
216 {
217 List<NotificationEvent> notificationEventList = new ArrayList<>( );
218 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_NOTIFICATION, NotificationStorePlugin.getPlugin( ) ) )
219 {
220 daoUtil.setString( 1, strDemandId );
221 daoUtil.setString( 2, strDemandTypeId );
222 daoUtil.setTimestamp( 3, new Timestamp( lNotificationDate ) );
223
224 daoUtil.executeQuery( );
225
226 while ( daoUtil.next( ) )
227 {
228 NotificationEvent notificationEvent = getItemFromDao( daoUtil );
229
230 notificationEventList.add( notificationEvent );
231 }
232
233 daoUtil.free( );
234 return notificationEventList;
235 }
236 }
237
238
239
240
241 @Override
242 public List<NotificationEvent> loadByFilter( NotificationFilter filter )
243 {
244
245 List<NotificationEvent> notificationEventList = new ArrayList<>( );
246 StringBuilder strSql = new StringBuilder( SQL_QUERY_SELECT_BY_FILTER );
247
248 getFilterCriteriaClauses( strSql, filter );
249
250 try ( DAOUtil daoUtil = new DAOUtil( strSql.toString( ), NotificationStorePlugin.getPlugin( ) ) )
251 {
252 addFilterCriteriaValues( daoUtil, filter );
253
254 daoUtil.executeQuery( );
255
256 while ( daoUtil.next( ) )
257 {
258 NotificationEvent notificationEvent = getItemFromDao( daoUtil );
259
260 notificationEventList.add( notificationEvent );
261 }
262
263 daoUtil.free( );
264 return notificationEventList;
265 }
266 }
267
268
269
270
271 @Override
272 public List<Integer> loadIdsByFilter( NotificationFilter filter )
273 {
274 List<Integer> notificationEventList = new ArrayList<>( );
275 StringBuilder strSql = new StringBuilder( SQL_QUERY_SELECT_BY_FILTER );
276
277 getFilterCriteriaClauses( strSql, filter );
278
279 try ( DAOUtil daoUtil = new DAOUtil( strSql.toString( ), NotificationStorePlugin.getPlugin( ) ) )
280 {
281 addFilterCriteriaValues( daoUtil, filter );
282
283 daoUtil.executeQuery( );
284
285 while ( daoUtil.next( ) )
286 {
287 notificationEventList.add( daoUtil.getInt( 1 ) );
288 }
289
290 daoUtil.free( );
291 return notificationEventList;
292 }
293 }
294
295
296
297
298
299
300
301 private NotificationEvent getItemFromDao( DAOUtil daoUtil )
302 {
303 NotificationEvent notificationEvent = new NotificationEvent( );
304 int nIndex = 1;
305
306 notificationEvent.setId( daoUtil.getInt( nIndex++ ) );
307
308 Event event = new Event( );
309 Timestamp eventDate = daoUtil.getTimestamp( nIndex++ );
310 event.setEventDate( eventDate != null ? eventDate.getTime( ) : 0 );
311 event.setType( daoUtil.getString( nIndex++ ) );
312 event.setStatus( daoUtil.getString( nIndex++ ) );
313 event.setRedelivry( daoUtil.getInt( nIndex++ ) );
314 event.setMessage( daoUtil.getString( nIndex++ ) );
315 notificationEvent.setEvent( event );
316
317 notificationEvent.setMsgId( daoUtil.getString( nIndex++ ) );
318
319 Demand demand = new Demand( );
320 demand.setId( daoUtil.getString( nIndex++ ) );
321 demand.setTypeId( daoUtil.getString( nIndex++ ) );
322
323 Customer customer = new Customer( );
324 customer.setCustomerId( daoUtil.getString( nIndex++ ) );
325 customer.setId( customer.getCustomerId( ) );
326
327 demand.setCustomer( customer );
328
329 notificationEvent.setDemand( demand );
330
331 notificationEvent.setNotificationDate( daoUtil.getTimestamp( nIndex ) != null ? daoUtil.getTimestamp( nIndex ).getTime( ) : 0 );
332
333 return notificationEvent;
334 }
335
336
337
338
339
340
341
342 private void getFilterCriteriaClauses( StringBuilder sbSql, NotificationFilter filter )
343 {
344
345 if ( filter.containsId( ) )
346 {
347 String sql = String.format( SQL_QUERY_FILTER_BY_ID, filter.getIds( ).stream( ).map( v -> "?" ).collect( Collectors.joining( ", " ) ) );
348
349 sbSql.append( sql );
350 }
351 if ( filter.containsDemandId( ) )
352 {
353 sbSql.append( SQL_QUERY_FILTER_BY_DEMAND_ID );
354 }
355 if ( filter.containsDemandTypeId( ) )
356 {
357 sbSql.append( SQL_QUERY_FILTER_BY_DEMAND_TYPE_ID );
358 }
359
360 if ( filter.containsStartDate( ) )
361 {
362 sbSql.append( SQL_QUERY_FILTER_BY_STARTDATE );
363 }
364
365 if ( filter.containsEndDate( ) )
366 {
367 sbSql.append( SQL_QUERY_FILTER_BY_ENDDATE );
368 }
369
370 if ( !StringUtils.isEmpty( filter.getEventStatus( ) ) )
371 {
372 sbSql.append( SQL_QUERY_FILTER_BY_STATUS );
373 }
374
375 if ( filter.containsNotificationTypeFilter( ) )
376 {
377 sbSql.append( SQL_QUERY_FILTER_BY_TYPE );
378 for ( int i = 0; i < filter.getListNotificationType( ).size( ) - 1; i++ )
379 {
380 sbSql.append( "?, " );
381 }
382 sbSql.append( "?)" );
383 }
384
385 sbSql.append( SQL_QUERY_FILTER_ORDER_BY );
386 }
387
388
389
390
391
392
393
394 private void addFilterCriteriaValues( DAOUtil daoUtil, NotificationFilter filter )
395 {
396
397 int i = 1;
398 if ( filter.containsId( ) )
399 {
400 for ( Integer id : filter.getIds( ) )
401 {
402 daoUtil.setInt( i++, id );
403 }
404 }
405 if ( filter.containsDemandId( ) )
406 {
407 daoUtil.setString( i++, filter.getDemandId( ) );
408 }
409 if ( filter.containsDemandTypeId( ) )
410 {
411 daoUtil.setString( i++, filter.getDemandTypeId( ) );
412 }
413 if ( filter.containsStartDate( ) )
414 {
415 daoUtil.setTimestamp( i++, new Timestamp( filter.getStartDate( ) ) );
416 }
417 if ( filter.containsEndDate( ) )
418 {
419 daoUtil.setTimestamp( i++, new Timestamp( filter.getEndDate( ) ) );
420 }
421 if ( !StringUtils.isEmpty( filter.getEventStatus( ) ) )
422 {
423 daoUtil.setString( i++, filter.getEventStatus( ) );
424 }
425 if ( filter.getListNotificationType( ) != null && !filter.getListNotificationType( ).isEmpty( ) )
426 {
427 for ( EnumNotificationType type : filter.getListNotificationType( ) )
428 {
429 daoUtil.setString( i++, type.name( ) );
430 }
431 }
432 }
433
434
435
436
437 public String deleteBeforeDate( long lDate )
438 {
439 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_DATE, NotificationStorePlugin.getPlugin( ) ) )
440 {
441 daoUtil.setTimestamp( 1, new Timestamp( lDate ) );
442 daoUtil.executeUpdate( );
443 daoUtil.free( );
444 }
445
446 return "Success";
447 }
448 }