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.grustoragedb.business;
35
36 import fr.paris.lutece.plugins.grubusiness.business.customer.Customer;
37 import fr.paris.lutece.plugins.grubusiness.business.demand.Demand;
38 import fr.paris.lutece.plugins.grubusiness.business.demand.IDemandDAO;
39 import fr.paris.lutece.plugins.grubusiness.business.notification.NotificationFilter;
40 import fr.paris.lutece.plugins.grustoragedb.service.GruStorageDbPlugin;
41 import fr.paris.lutece.util.sql.DAOUtil;
42
43 import java.sql.Statement;
44 import java.util.ArrayList;
45 import java.util.Collection;
46 import java.util.List;
47 import java.util.stream.Collectors;
48
49 import org.apache.commons.lang3.StringUtils;
50
51
52
53
54 public final class DemandDAO implements IDemandDAO
55 {
56
57 private static final String COLUMN_DEMAND_ID = "demand_id";
58 private static final String COLUMN_ID = "id";
59 private static final String COLUMN_TYPE_ID = "type_id";
60 private static final String COLUMN_SUBTYPE_ID = "subtype_id";
61 private static final String COLUMN_REFERENCE = "reference";
62 private static final String COLUMN_STATUS_ID = "status_id";
63 private static final String COLUMN_CUSTOMER_ID = "customer_id";
64 private static final String COLUMN_CREATION_DATE = "creation_date";
65 private static final String COLUMN_CLOSURE_DATE = "closure_date";
66 private static final String COLUMN_MAX_STEPS = "max_steps";
67 private static final String COLUMN_CURRENT_STEP = "current_step";
68 private static final String COLUMN_MODIFY_DATE = "modify_date";
69
70 private static final String SQL_QUERY_DEMAND_ALL_FIELDS = " demand_id, id, type_id, subtype_id, reference, status_id, customer_id, creation_date, closure_date, max_steps, current_step, modify_date";
71 private static final String SQL_QUERY_DEMAND_ALL_FIELDS_WITH_NO_DEMAND_ID = " id, type_id, subtype_id, reference, status_id, customer_id, creation_date, closure_date, max_steps, current_step, modify_date";
72 private static final String SQL_QUERY_DEMAND_SELECT_BY_ID = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS + " FROM grustoragedb_demand WHERE id = ? AND type_id = ?";
73 private static final String SQL_QUERY_DEMAND_SELECT_BY_DEMAND_ID = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS
74 + " FROM grustoragedb_demand WHERE demand_id = ? ";
75 private static final String SQL_QUERY_DEMAND_SELECT_ALL = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS + " FROM grustoragedb_demand";
76 private static final String SQL_QUERY_DEMAND_SELECT_DEMAND_IDS = "SELECT demand_id FROM grustoragedb_demand ";
77 private static final String SQL_QUERY_DEMAND_SELECT_BY_IDS = SQL_QUERY_DEMAND_SELECT_ALL + " where demand_id in ( %s )";
78 private static final String SQL_QUERY_DEMAND_INSERT = "INSERT INTO grustoragedb_demand ( " + SQL_QUERY_DEMAND_ALL_FIELDS_WITH_NO_DEMAND_ID
79 + " ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
80 private static final String SQL_QUERY_DEMAND_UPDATE = "UPDATE grustoragedb_demand SET status_id = ?, customer_id = ?, closure_date = ?, current_step = ?, subtype_id = ?, modify_date = ? WHERE demand_id = ? AND type_id = ?";
81 private static final String SQL_QUERY_DEMAND_DELETE = "DELETE FROM grustoragedb_demand WHERE demand_id = ? AND type_id = ? ";
82 private static final String SQL_QUERY_DEMAND_SELECT_BY_CUSTOMER_ID = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS
83 + " FROM grustoragedb_demand WHERE customer_id = ?";
84 private static final String SQL_QUERY_DEMAND_SELECT_BY_REFERENCE = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS
85 + " FROM grustoragedb_demand WHERE reference = ?";
86
87 private static final String SQL_QUERY_IDS_BY_CUSTOMER_ID_AND_DEMANDTYPE_ID = "SELECT distinct(gd.demand_id) "
88 + " FROM grustoragedb_demand gd, grustoragedb_notification gn, grustoragedb_notification_content gc "
89 + " WHERE gd.demand_id = gn.demand_id and gn.id = gc.notification_id "
90 + " AND gd.customer_id = ? ";
91
92 private static final String SQL_QUERY_IDS_BY_STATUS = "SELECT distinct(gd.demand_id) "
93 + " FROM grustoragedb_demand gd, grustoragedb_notification gn, grustoragedb_notification_content gc "
94 + " WHERE gd.demand_id = gn.demand_id and gn.id = gc.notification_id "
95 + " AND gd.customer_id = ? "
96 + " AND gc.status_id IN ( ";
97
98 private static final String SQL_QUERY_FILTER_WHERE_BASE = " WHERE 1 ";
99 private static final String SQL_FILTER_BY_DEMAND_ID = " AND id = ? ";
100 private static final String SQL_FILTER_BY_DEMAND_TYPE_ID = " AND type_id = ? ";
101 private static final String SQL_FILTER_BY_START_DATE = " AND creation_date >= ? ";
102 private static final String SQL_FILTER_BY_END_DATE = " AND creation_date <= ? ";
103 private static final String SQL_FILTER_NOTIFICATION_TYPE = " AND gc.notification_type = ? ";
104 private static final String SQL_QUERY_FILTER_ORDER = " ORDER BY id ASC";
105 private static final String SQL_QUERY_DATE_ORDER = " ORDER BY modify_date DESC";
106
107
108
109
110
111 @Override
112 public Demand load( String strDemandId, String strDemandTypeId )
113 {
114 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_BY_ID, GruStorageDbPlugin.getPlugin( ) );
115
116 daoUtil.setString( 1, strDemandId );
117 daoUtil.setString( 2, strDemandTypeId );
118 daoUtil.executeQuery( );
119
120 Demand demand = null;
121
122 if ( daoUtil.next( ) )
123 {
124 demand = dao2Demand( daoUtil );
125 }
126
127 daoUtil.free( );
128
129 return demand;
130 }
131
132
133
134
135 @Override
136 public Collection<Demand> loadByCustomerId( String strCustomerId )
137 {
138 Collection<Demand> collectionDemands = new ArrayList<>( );
139
140 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_BY_CUSTOMER_ID, GruStorageDbPlugin.getPlugin( ) );
141
142 daoUtil.setString( 1, strCustomerId );
143 daoUtil.executeQuery( );
144
145 while ( daoUtil.next( ) )
146 {
147 collectionDemands.add( dao2Demand( daoUtil ) );
148 }
149
150 daoUtil.free( );
151
152 return collectionDemands;
153 }
154
155
156
157
158 @Override
159 public List<Demand> loadByIds( List<Integer> listIds )
160 {
161 List<Demand> listDemands = new ArrayList<>( );
162
163 if (listIds.isEmpty( ) ) return listDemands;
164
165 String sql = String.format( SQL_QUERY_DEMAND_SELECT_BY_IDS, listIds.stream( ).map(v -> "?").collect(Collectors.joining(", ")));
166 DAOUtil daoUtil = new DAOUtil( sql, GruStorageDbPlugin.getPlugin( ) );
167
168 int index = 1;
169 for( Integer strId : listIds ) {
170 daoUtil.setInt( index++, strId );
171 }
172
173 daoUtil.executeQuery( );
174
175 while ( daoUtil.next( ) )
176 {
177 listDemands.add( dao2Demand( daoUtil ) );
178 }
179
180 daoUtil.free( );
181
182 return listDemands;
183 }
184
185
186
187
188 @Override
189 public Collection<Demand> loadByReference( String strReference )
190 {
191 Collection<Demand> collectionDemands = new ArrayList<>( );
192
193 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_BY_REFERENCE, GruStorageDbPlugin.getPlugin( ) );
194
195 daoUtil.setString( 1, strReference );
196 daoUtil.executeQuery( );
197
198 while ( daoUtil.next( ) )
199 {
200 collectionDemands.add( dao2Demand( daoUtil ) );
201 }
202
203 daoUtil.free( );
204
205 return collectionDemands;
206 }
207
208
209
210
211 @Override
212 public Collection<Demand> loadByFilter( NotificationFilter filter )
213 {
214 Collection<Demand> collectionDemands = new ArrayList<>( );
215 StringBuilder sql = new StringBuilder( SQL_QUERY_DEMAND_SELECT_ALL + SQL_QUERY_FILTER_WHERE_BASE );
216
217 buildSql( sql, filter);
218
219 DAOUtil daoUtil = new DAOUtil( sql.toString( ), GruStorageDbPlugin.getPlugin( ) );
220
221 fillDao( daoUtil, filter);
222
223 daoUtil.executeQuery( );
224 while ( daoUtil.next( ) )
225 {
226 collectionDemands.add( dao2Demand( daoUtil ) );
227 }
228
229 daoUtil.free( );
230
231 return collectionDemands;
232 }
233
234
235
236
237 @Override
238 public List<Integer> loadIdsByFilter( NotificationFilter filter )
239 {
240 List<Integer> listIds = new ArrayList<>( );
241 StringBuilder sql = new StringBuilder( SQL_QUERY_DEMAND_SELECT_DEMAND_IDS + SQL_QUERY_FILTER_WHERE_BASE );
242
243 buildSql( sql, filter);
244
245 DAOUtil daoUtil = new DAOUtil( sql.toString( ), GruStorageDbPlugin.getPlugin( ) );
246
247 fillDao( daoUtil, filter);
248
249 daoUtil.executeQuery( );
250 while ( daoUtil.next( ) )
251 {
252 listIds.add( daoUtil.getInt( 1 ) );
253 }
254
255 daoUtil.free( );
256
257 return listIds;
258 }
259
260
261
262
263 @Override
264 public Demand insert( Demand demand )
265 {
266 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_INSERT, Statement.RETURN_GENERATED_KEYS, GruStorageDbPlugin.getPlugin( ) );
267
268 int nIndex = 1;
269
270 daoUtil.setString( nIndex++, demand.getId( ) );
271 daoUtil.setString( nIndex++, demand.getTypeId( ) );
272 daoUtil.setString( nIndex++, demand.getSubtypeId( ) );
273 daoUtil.setString( nIndex++, demand.getReference( ) );
274 daoUtil.setInt( nIndex++, demand.getStatusId( ) );
275 daoUtil.setString( nIndex++, demand.getCustomer( ).getId( ) );
276 daoUtil.setLong( nIndex++, demand.getCreationDate( ) );
277 daoUtil.setLong( nIndex++, demand.getClosureDate( ) );
278 daoUtil.setInt( nIndex++, demand.getMaxSteps( ) );
279 daoUtil.setInt( nIndex++, demand.getCurrentStep( ) );
280 daoUtil.setLong( nIndex++, demand.getModifyDate( ) );
281
282 daoUtil.executeUpdate( );
283 if( daoUtil.nextGeneratedKey( ) ) {
284 demand.setDemandId( daoUtil.getGeneratedKeyInt( 1 ) );
285 }
286 daoUtil.free( );
287
288 return demand;
289 }
290
291
292
293
294 @Override
295 public Demand store( Demand demand )
296 {
297 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_UPDATE, GruStorageDbPlugin.getPlugin( ) );
298
299 int nIndex = 1;
300
301
302 daoUtil.setInt( nIndex++, demand.getStatusId( ) );
303 daoUtil.setString( nIndex++, demand.getCustomer( ).getId( ) );
304 daoUtil.setLong( nIndex++, demand.getClosureDate( ) );
305 daoUtil.setInt( nIndex++, demand.getCurrentStep( ) );
306 daoUtil.setString( nIndex++, demand.getSubtypeId( ) );
307 daoUtil.setLong( nIndex++, demand.getModifyDate( ) );
308
309
310 daoUtil.setInt( nIndex++, demand.getDemandId( ) );
311 daoUtil.setString( nIndex++, demand.getTypeId( ) );
312
313 daoUtil.executeUpdate( );
314 daoUtil.free( );
315
316 return demand;
317 }
318
319
320
321
322 @Override
323 public void delete( String strDemandId, String strDemandTypeId )
324 {
325 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_DELETE, GruStorageDbPlugin.getPlugin( ) );
326
327 daoUtil.setString( 1, strDemandId );
328 daoUtil.setString( 2, strDemandTypeId );
329
330 daoUtil.executeUpdate( );
331 daoUtil.free( );
332 }
333
334
335
336
337 @Override
338 public List<String> loadAllIds( )
339 {
340 List<String> collectionIds = new ArrayList<>( );
341
342 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_ALL, GruStorageDbPlugin.getPlugin( ) );
343 daoUtil.executeQuery( );
344
345 while ( daoUtil.next( ) )
346 {
347 collectionIds.add( String.valueOf( dao2Demand( daoUtil ).getDemandId( ) ) );
348 }
349
350 daoUtil.free( );
351
352 return collectionIds;
353 }
354
355
356
357
358 @Override
359 public Demand loadById( String strId )
360 {
361 Demand demand = null;
362
363 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_BY_DEMAND_ID, GruStorageDbPlugin.getPlugin( ) );
364
365 daoUtil.setString( 1, strId );
366 daoUtil.executeQuery( );
367
368 while ( daoUtil.next( ) )
369 {
370 demand = dao2Demand( daoUtil );
371 break;
372 }
373
374 daoUtil.free( );
375
376 return demand;
377 }
378
379
380
381
382
383
384
385
386 private Demand dao2Demand( DAOUtil daoUtil )
387 {
388 Demand demand = new Demand( );
389
390 demand.setDemandId( daoUtil.getInt( COLUMN_DEMAND_ID ) );
391 demand.setId( daoUtil.getString( COLUMN_ID ) );
392 demand.setTypeId( daoUtil.getString( COLUMN_TYPE_ID ) );
393 demand.setSubtypeId( daoUtil.getString( COLUMN_SUBTYPE_ID ) );
394 demand.setStatusId( daoUtil.getInt( COLUMN_STATUS_ID ) );
395
396 Customer customer = new Customer( );
397 customer.setId( daoUtil.getString( COLUMN_CUSTOMER_ID ) );
398 demand.setCustomer( customer );
399
400 demand.setReference( daoUtil.getString( COLUMN_REFERENCE ) );
401 demand.setCreationDate( daoUtil.getLong( COLUMN_CREATION_DATE ) );
402 demand.setClosureDate( daoUtil.getLong( COLUMN_CLOSURE_DATE ) );
403 demand.setMaxSteps( daoUtil.getInt( COLUMN_MAX_STEPS ) );
404 demand.setCurrentStep( daoUtil.getInt( COLUMN_CURRENT_STEP ) );
405 demand.setModifyDate( daoUtil.getLong( COLUMN_MODIFY_DATE) );
406
407 return demand;
408 }
409
410
411
412
413
414
415
416 private void buildSql( StringBuilder sql, NotificationFilter filter)
417 {
418
419 if ( filter.containsDemandId( ) )
420 {
421 sql.append( SQL_FILTER_BY_DEMAND_ID );
422 }
423
424 if ( filter.containsDemandTypeId( ) )
425 {
426 sql.append( SQL_FILTER_BY_DEMAND_TYPE_ID );
427 }
428
429 if ( filter.containsStartDate( ) )
430 {
431 sql.append( SQL_FILTER_BY_START_DATE );
432 }
433
434 if ( filter.containsEndDate( ) )
435 {
436 sql.append( SQL_FILTER_BY_END_DATE );
437 }
438
439 sql.append( SQL_QUERY_FILTER_ORDER );
440 }
441
442
443
444
445
446
447 private void fillDao( DAOUtil daoUtil, NotificationFilter filter)
448 {
449 int i=1;
450 if ( filter.containsDemandId( ) )
451 {
452 daoUtil.setString( i++, filter.getDemandId( ) );
453 }
454
455 if ( filter.containsDemandTypeId( ) )
456 {
457 daoUtil.setString( i++, filter.getDemandTypeId( ) );
458 }
459
460 if ( filter.containsStartDate( ) )
461 {
462 daoUtil.setLong( i++, filter.getStartDate( ) );
463 }
464
465 if ( filter.containsEndDate( ) )
466 {
467 daoUtil.setLong( i++, filter.getEndDate( ) );
468 }
469 }
470
471 @Override
472 public List<Integer> loadIdsByCustomerIdAndIdDemandType( String strCustomerId, String strNotificationType, String strIdDemandType )
473 {
474 List<Integer> listIds = new ArrayList<>();
475 String strSql = SQL_QUERY_IDS_BY_CUSTOMER_ID_AND_DEMANDTYPE_ID;
476
477 if( StringUtils.isNotEmpty( strNotificationType ) )
478 {
479 strSql += SQL_FILTER_NOTIFICATION_TYPE;
480 }
481
482 if( StringUtils.isNotEmpty( strIdDemandType ) )
483 {
484 strSql += SQL_FILTER_BY_DEMAND_TYPE_ID;
485 }
486
487 strSql += SQL_QUERY_DATE_ORDER;
488
489 try( DAOUtil daoUtil = new DAOUtil( strSql, GruStorageDbPlugin.getPlugin( ) ) )
490 {
491 int nIndex=1;
492 daoUtil.setString( nIndex++, strCustomerId );
493
494 if( StringUtils.isNotEmpty( strNotificationType ) )
495 {
496 daoUtil.setString( nIndex++, strNotificationType );
497 }
498
499 if( StringUtils.isNotEmpty( strIdDemandType ) )
500 {
501 daoUtil.setString( nIndex++, strIdDemandType );
502 }
503
504 daoUtil.executeQuery( );
505
506 while( daoUtil.next( ) )
507 {
508 listIds.add( daoUtil.getInt( 1 ) );
509 }
510
511 return listIds;
512 }
513 }
514
515 @Override
516 public List<Integer> loadIdsByStatus( String strCustomerId, List<String> listStatus, String strNotificationType, String strIdDemandType )
517 {
518 List<Integer> listIds = new ArrayList<>();
519 String strQuery = SQL_QUERY_IDS_BY_STATUS;
520
521 if( !listStatus.isEmpty( ) )
522 {
523 strQuery += listStatus.stream( ).map( i -> "?" ).collect( Collectors.joining( "," ) ) + " ) ";
524 }
525
526 if( StringUtils.isNotEmpty( strNotificationType ) )
527 {
528 strQuery += SQL_FILTER_NOTIFICATION_TYPE;
529 }
530 if( StringUtils.isNotEmpty( strIdDemandType ) )
531 {
532 strQuery += SQL_FILTER_BY_DEMAND_TYPE_ID;
533 }
534
535 strQuery += SQL_QUERY_DATE_ORDER;
536
537 try( DAOUtil daoUtil = new DAOUtil( strQuery, GruStorageDbPlugin.getPlugin( ) ) )
538 {
539 int nIndexIn = 1;
540 daoUtil.setString( nIndexIn++, strCustomerId );
541
542 for ( String strStatus : listStatus )
543 {
544 daoUtil.setString( nIndexIn, strStatus );
545 nIndexIn++;
546 }
547 if( StringUtils.isNotEmpty( strNotificationType ) )
548 {
549 daoUtil.setString( nIndexIn++, strNotificationType );
550 }
551 if( StringUtils.isNotEmpty( strIdDemandType ) )
552 {
553 daoUtil.setString( nIndexIn++, strIdDemandType );
554 }
555
556 daoUtil.executeQuery( );
557
558 while( daoUtil.next( ) )
559 {
560 listIds.add( daoUtil.getInt( 1 ) );
561 }
562
563 return listIds;
564 }
565 }
566 }