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