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.workflow.business.resource;
35
36 import fr.paris.lutece.plugins.workflow.utils.WorkflowUtils;
37 import fr.paris.lutece.plugins.workflowcore.business.action.Action;
38 import fr.paris.lutece.plugins.workflowcore.business.resource.IResourceHistoryDAO;
39 import fr.paris.lutece.plugins.workflowcore.business.resource.ResourceHistory;
40 import fr.paris.lutece.plugins.workflowcore.business.resource.ResourceHistoryFilter;
41 import fr.paris.lutece.plugins.workflowcore.business.resource.ResourceUserHistory;
42 import fr.paris.lutece.plugins.workflowcore.business.workflow.Workflow;
43 import fr.paris.lutece.util.sql.DAOUtil;
44 import java.sql.Statement;
45 import java.util.ArrayList;
46 import java.util.List;
47 import java.util.stream.Collectors;
48
49 import org.apache.commons.collections.CollectionUtils;
50 import org.apache.commons.lang3.StringUtils;
51
52
53
54
55
56
57 public class ResourceHistoryDAO implements IResourceHistoryDAO
58 {
59 private static final String SQL_QUERY_SELECT_ALL = "SELECT wh.id_history,wh.id_resource,wh.resource_type,"
60 + "wh.id_workflow,wh.id_action,wh.creation_date,wh.user_access_code FROM workflow_resource_history wh ";
61 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = SQL_QUERY_SELECT_ALL + " WHERE wh.id_history=?";
62 private static final String SQL_QUERY_SELECT_BY_RESSOURCE = SQL_QUERY_SELECT_ALL + " WHERE wh.id_resource=? AND wh.resource_type=? AND wh.id_workflow=?";
63 private static final String SQL_QUERY_SELECT_BY_ACTION = SQL_QUERY_SELECT_ALL + " WHERE wh.id_action=?";
64 private static final String SQL_QUERY_INSERT = "INSERT INTO workflow_resource_history "
65 + "(id_resource,resource_type,id_workflow,id_action,creation_date,user_access_code )VALUES(?,?,?,?,?,?)";
66 private static final String SQL_QUERY_DELETE = "DELETE FROM workflow_resource_history WHERE id_history=?";
67 private static final String SQL_QUERY_DELETE_BY_LIST_ID_RESOURCE = "DELETE FROM workflow_resource_history WHERE id_workflow = ? AND resource_type = ? AND id_resource IN (?";
68 private static final String SQL_QUERY_SELECT_LIST_ID_HISTORY_BY_LIST_ID_RESOURCE_AND_TYPE = "SELECT id_history FROM workflow_resource_history WHERE id_workflow = ? AND resource_type = ? AND id_resource IN (?";
69 private static final String SQL_QUERY_SELECT_BY_FILTER = "SELECT wh.id_history, wh.id_resource,wh. resource_type, wh.id_workflow, wh.id_action, wh.creation_date, wh.user_access_code, a.name, a.description, a.is_automatic, a.is_mass_action, a.display_order, a.is_automatic_reflexive_action, user.email, user.first_name, user.last_name, user.realm FROM workflow_resource_history wh left join workflow_action a on ( wh.id_action=a.id_action) left join workflow_resource_user_history user on (wh.id_history=user.id_history) ";
70 private static final String SQL_QUERY_SELECT_ID_HISTORY_BY_FILTER = "SELECT wh.id_history FROM workflow_resource_history wh";
71 private static final String SQL_QUERY_SELECT_BY_LIST_HISTORY = SQL_QUERY_SELECT_BY_FILTER + " WHERE wh.id_history IN (?";
72 private static final String SQL_CLOSE_PARENTHESIS = " ) ";
73 private static final String SQL_ADITIONAL_PARAMETER = ",?";
74 private static final String SQL_ORDER_BY_CREATION_DATE_DESC = " ORDER BY creation_date DESC";
75 private static final String SQL_FILTER_ID_WORKFLOW = " wh.id_workflow = ? ";
76 private static final String SQL_FILTER_ID_ACTION = " wh.id_action = ? ";
77 private static final String SQL_FILTER_RESOURCE_TYPE = " wh.resource_type = ? ";
78 private static final String SQL_FILTER_USER_ACCESS_CODE = " wh.user_access_code = ? ";
79 private static final String SQL_FILTER_LIST_RESOURCES = " wh.id_resource IN ( ";
80 private static final String SQL_QUERY_UPDATE = "UPDATE workflow_resource_history SET user_access_code = ? WHERE id_history = ? ";
81
82
83
84
85 @Override
86 public synchronized void insert( ResourceHistory resourceHistory )
87 {
88
89 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, WorkflowUtils.getPlugin( ) ) )
90 {
91 int nPos = 0;
92 daoUtil.setInt( ++nPos, resourceHistory.getIdResource( ) );
93 daoUtil.setString( ++nPos, resourceHistory.getResourceType( ) );
94 daoUtil.setInt( ++nPos, resourceHistory.getWorkflow( ).getId( ) );
95 daoUtil.setInt( ++nPos, resourceHistory.getAction( ).getId( ) );
96 daoUtil.setTimestamp( ++nPos, resourceHistory.getCreationDate( ) );
97 daoUtil.setString( ++nPos, resourceHistory.getUserAccessCode( ) );
98
99 daoUtil.executeUpdate( );
100 if ( daoUtil.nextGeneratedKey( ) )
101 {
102 resourceHistory.setId( daoUtil.getGeneratedKeyInt( 1 ) );
103 }
104 }
105 }
106
107
108
109
110 @Override
111 public ResourceHistory load( int nIdHistory )
112 {
113 ResourceHistory resourceHistory = null;
114 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, WorkflowUtils.getPlugin( ) ) )
115 {
116 daoUtil.setInt( 1, nIdHistory );
117 daoUtil.executeQuery( );
118
119 if ( daoUtil.next( ) )
120 {
121 resourceHistory = dataToObject( daoUtil );
122 }
123 }
124 return resourceHistory;
125 }
126
127
128
129
130 @Override
131 public List<ResourceHistory> selectByResource( int nIdResource, String strResourceType, int nIdWorkflow )
132 {
133 List<ResourceHistory> listResourceHostory = new ArrayList<>( );
134 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_RESSOURCE + SQL_ORDER_BY_CREATION_DATE_DESC, WorkflowUtils.getPlugin( ) ) )
135 {
136 int nPos = 0;
137 daoUtil.setInt( ++nPos, nIdResource );
138 daoUtil.setString( ++nPos, strResourceType );
139 daoUtil.setInt( ++nPos, nIdWorkflow );
140
141 daoUtil.executeQuery( );
142
143 while ( daoUtil.next( ) )
144 {
145 ResourceHistory resourceHistory = dataToObject( daoUtil );
146 listResourceHostory.add( resourceHistory );
147 }
148 }
149 return listResourceHostory;
150 }
151
152
153
154
155 @Override
156 public List<ResourceHistory> selectByAction( int nIdAction )
157 {
158 List<ResourceHistory> listResourceHostory = new ArrayList<>( );
159 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ACTION + SQL_ORDER_BY_CREATION_DATE_DESC, WorkflowUtils.getPlugin( ) ) )
160 {
161 int nPos = 0;
162 daoUtil.setInt( ++nPos, nIdAction );
163 daoUtil.executeQuery( );
164
165 while ( daoUtil.next( ) )
166 {
167 ResourceHistory resourceHistory = dataToObject( daoUtil );
168 listResourceHostory.add( resourceHistory );
169 }
170 }
171 return listResourceHostory;
172 }
173
174
175
176
177 @Override
178 public void delete( int nIdHistory )
179 {
180 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, WorkflowUtils.getPlugin( ) ) )
181 {
182 daoUtil.setInt( 1, nIdHistory );
183 daoUtil.executeUpdate( );
184 }
185 }
186
187
188
189
190 @Override
191 public void deleteByListIdResource( List<Integer> listIdResource, String strResourceType, Integer nIdWorflow )
192 {
193 int nlistIdResourceSize = listIdResource.size( );
194
195 if ( nlistIdResourceSize > 0 )
196 {
197 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_DELETE_BY_LIST_ID_RESOURCE );
198
199 for ( int i = 1; i < nlistIdResourceSize; i++ )
200 {
201 sbSQL.append( SQL_ADITIONAL_PARAMETER );
202 }
203
204 sbSQL.append( SQL_CLOSE_PARENTHESIS );
205
206 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), WorkflowUtils.getPlugin( ) ) )
207 {
208 daoUtil.setInt( 1, nIdWorflow );
209 daoUtil.setString( 2, strResourceType );
210 for ( int i = 0; i < nlistIdResourceSize; i++ )
211 {
212 daoUtil.setInt( i + 3, listIdResource.get( i ) );
213 }
214 daoUtil.executeUpdate( );
215 }
216 }
217 }
218
219
220
221
222 @Override
223 public List<Integer> getListHistoryIdByListIdResourceId( List<Integer> listIdResource, String strResourceType, Integer nIdWorflow )
224 {
225 List<Integer> lListResult = new ArrayList<>( );
226
227 int nlistIdResourceSize = listIdResource.size( );
228
229 if ( nlistIdResourceSize > 0 )
230 {
231 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_LIST_ID_HISTORY_BY_LIST_ID_RESOURCE_AND_TYPE );
232
233 for ( int i = 1; i < nlistIdResourceSize; i++ )
234 {
235 sbSQL.append( SQL_ADITIONAL_PARAMETER );
236 }
237
238 sbSQL.append( SQL_CLOSE_PARENTHESIS );
239
240 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), WorkflowUtils.getPlugin( ) ) )
241 {
242 daoUtil.setInt( 1, nIdWorflow );
243 daoUtil.setString( 2, strResourceType );
244
245 for ( int i = 0; i < nlistIdResourceSize; i++ )
246 {
247 daoUtil.setInt( i + 3, listIdResource.get( i ) );
248 }
249
250 daoUtil.executeQuery( );
251
252 while ( daoUtil.next( ) )
253 {
254 lListResult.add( daoUtil.getInt( 1 ) );
255 }
256 }
257 }
258 return lListResult;
259 }
260
261
262
263
264 @Override
265 public List<Integer> selectListHistoryIdByFilter( ResourceHistoryFilter filter )
266 {
267 List<Integer> lListResult = new ArrayList<>( );
268
269 String strSQL = buildFilterQuerydHeader( filter, SQL_QUERY_SELECT_ID_HISTORY_BY_FILTER );
270 try ( DAOUtil daoUtil = new DAOUtil( strSQL, WorkflowUtils.getPlugin( ) ) )
271 {
272 int nPos = 0;
273 buildFilterQuerydFooter( filter, daoUtil, nPos );
274 daoUtil.executeQuery( );
275
276 while ( daoUtil.next( ) )
277 {
278 lListResult.add( daoUtil.getInt( 1 ) );
279 }
280 }
281
282 return lListResult;
283 }
284
285
286
287
288 @Override
289 public List<ResourceHistory> selectByFilter( ResourceHistoryFilter filter )
290 {
291 List<ResourceHistory> lListResult = new ArrayList<>( );
292
293 String strSQL = buildFilterQuerydHeader( filter, SQL_QUERY_SELECT_BY_FILTER );
294 try ( DAOUtil daoUtil = new DAOUtil( strSQL, WorkflowUtils.getPlugin( ) ) )
295 {
296 int nPos = 0;
297 buildFilterQuerydFooter( filter, daoUtil, nPos );
298 daoUtil.executeQuery( );
299
300 while ( daoUtil.next( ) )
301 {
302 lListResult.add( dataToResourceHistoryObject( daoUtil ) );
303 }
304 }
305
306 return lListResult;
307 }
308
309
310
311
312 @Override
313 public List<ResourceHistory> selectByPrimaryKeyList( List<Integer> listIdHistory )
314 {
315 List<ResourceHistory> lListResult = new ArrayList<>( );
316 int nlistIdResourceSize = listIdHistory.size( );
317
318 if ( nlistIdResourceSize > 0 )
319 {
320 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_BY_LIST_HISTORY );
321
322 for ( int i = 1; i < nlistIdResourceSize; i++ )
323 {
324 sbSQL.append( SQL_ADITIONAL_PARAMETER );
325 }
326
327 sbSQL.append( SQL_CLOSE_PARENTHESIS );
328
329 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), WorkflowUtils.getPlugin( ) ) )
330 {
331
332 for ( int i = 0; i < nlistIdResourceSize; i++ )
333 {
334 daoUtil.setInt( i + 1, listIdHistory.get( i ) );
335 }
336
337 daoUtil.executeQuery( );
338
339 while ( daoUtil.next( ) )
340 {
341 lListResult.add( dataToResourceHistoryObject( daoUtil ) );
342 }
343 }
344 }
345 return lListResult;
346 }
347
348 private ResourceHistory dataToObject( DAOUtil daoUtil )
349 {
350 int nPos = 0;
351 ResourceHistory resourceHistory = new ResourceHistory( );
352 resourceHistory.setId( daoUtil.getInt( ++nPos ) );
353 resourceHistory.setIdResource( daoUtil.getInt( ++nPos ) );
354 resourceHistory.setResourceType( daoUtil.getString( ++nPos ) );
355
356 Workflow workflow = new Workflow( );
357 workflow.setId( daoUtil.getInt( ++nPos ) );
358 resourceHistory.setWorkFlow( workflow );
359
360 Action action = new Action( );
361 action.setId( daoUtil.getInt( ++nPos ) );
362 resourceHistory.setAction( action );
363
364 resourceHistory.setCreationDate( daoUtil.getTimestamp( ++nPos ) );
365 resourceHistory.setUserAccessCode( daoUtil.getString( ++nPos ) );
366
367 return resourceHistory;
368 }
369
370 private ResourceHistory dataToResourceHistoryObject( DAOUtil daoUtil )
371 {
372 int nPos = 0;
373 ResourceHistory resourceHistory = new ResourceHistory( );
374 resourceHistory.setId( daoUtil.getInt( ++nPos ) );
375 resourceHistory.setIdResource( daoUtil.getInt( ++nPos ) );
376 resourceHistory.setResourceType( daoUtil.getString( ++nPos ) );
377
378 Workflow workflow = new Workflow( );
379 workflow.setId( daoUtil.getInt( ++nPos ) );
380 resourceHistory.setWorkFlow( workflow );
381
382 Action action = new Action( );
383 action.setId( daoUtil.getInt( ++nPos ) );
384
385 resourceHistory.setCreationDate( daoUtil.getTimestamp( ++nPos ) );
386 resourceHistory.setUserAccessCode( daoUtil.getString( ++nPos ) );
387 resourceHistory.setAction( dataToActionObject( daoUtil, action, nPos ) );
388
389 if ( StringUtils.isNotEmpty( resourceHistory.getUserAccessCode( ) ) )
390 {
391
392 ResourceUserHistory resourceUserHistory = new ResourceUserHistory( );
393 resourceUserHistory.setUserAccessCode( resourceHistory.getUserAccessCode( ) );
394 resourceUserHistory.setIdHistory( ( resourceHistory.getId( ) ) );
395 resourceHistory.setResourceUserHistory( dataToUserObject( daoUtil, resourceUserHistory, nPos ) );
396 }
397 return resourceHistory;
398 }
399
400 private Action dataToActionObject( DAOUtil daoUtil, Action action, int nPos )
401 {
402 action.setName( daoUtil.getString( ++nPos ) );
403 action.setDescription( daoUtil.getString( ++nPos ) );
404 action.setAutomaticState( daoUtil.getBoolean( ++nPos ) );
405 action.setMassAction( daoUtil.getBoolean( ++nPos ) );
406 action.setOrder( daoUtil.getInt( ++nPos ) );
407 action.setAutomaticReflexiveAction( daoUtil.getBoolean( ++nPos ) );
408
409 return action;
410 }
411
412 private ResourceUserHistory dataToUserObject( DAOUtil daoUtil, ResourceUserHistory resourceUserHistory, int nPos )
413 {
414 resourceUserHistory.setEmail( daoUtil.getString( ++nPos ) );
415 resourceUserHistory.setFirstName( daoUtil.getString( ++nPos ) );
416 resourceUserHistory.setLastName( daoUtil.getString( ++nPos ) );
417 resourceUserHistory.setRealm( daoUtil.getString( ++nPos ) );
418
419 return resourceUserHistory;
420 }
421
422
423
424
425
426
427
428
429
430
431
432
433 private String buildFilterQuerydHeader( ResourceHistoryFilter filter, String strSelectSQL )
434 {
435 List<String> listStrFilter = new ArrayList<>( );
436
437 if ( filter.getIdWorkflow( ) > 0 )
438 {
439 listStrFilter.add( SQL_FILTER_ID_WORKFLOW );
440 }
441
442 if ( filter.getIdAction( ) > 0 )
443 {
444 listStrFilter.add( SQL_FILTER_ID_ACTION );
445 }
446
447 if ( !StringUtils.isEmpty( filter.getResourceType( ) ) )
448 {
449 listStrFilter.add( SQL_FILTER_RESOURCE_TYPE );
450 }
451
452 if ( !StringUtils.isEmpty( filter.getUserAccessCode( ) ) )
453 {
454 listStrFilter.add( SQL_FILTER_USER_ACCESS_CODE );
455 }
456
457 if ( CollectionUtils.isNotEmpty( filter.getListIdResources( ) ) )
458 {
459 StringBuilder sb = new StringBuilder( );
460 sb.append( SQL_FILTER_LIST_RESOURCES );
461 sb.append( filter.getListIdResources( ).stream( ).map( i -> "?" ).collect( Collectors.joining( "," ) ) );
462 sb.append( SQL_CLOSE_PARENTHESIS );
463 listStrFilter.add( sb.toString( ) );
464 }
465 return WorkflowUtils.buildRequestWithFilter( strSelectSQL, listStrFilter, null );
466 }
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481 private int buildFilterQuerydFooter( ResourceHistoryFilter filter, DAOUtil daoUtil, Integer nPos )
482 {
483
484 if ( filter.getIdWorkflow( ) > 0 )
485 {
486 daoUtil.setInt( ++nPos, filter.getIdWorkflow( ) );
487 }
488 if ( filter.getIdAction( ) > 0 )
489 {
490 daoUtil.setInt( ++nPos, filter.getIdAction( ) );
491 }
492
493 if ( !StringUtils.isEmpty( filter.getResourceType( ) ) )
494 {
495 daoUtil.setString( ++nPos, filter.getResourceType( ) );
496 }
497
498 if ( !StringUtils.isEmpty( filter.getUserAccessCode( ) ) )
499 {
500 daoUtil.setString( ++nPos, filter.getUserAccessCode( ) );
501 }
502
503 if ( CollectionUtils.isNotEmpty( filter.getListIdResources( ) ) )
504 {
505 List<Integer> listIdResource = filter.getListIdResources( );
506 for ( int i = 0; i < listIdResource.size( ); i++ )
507 {
508 daoUtil.setInt( ++nPos, listIdResource.get( i ) );
509 }
510 }
511 return nPos;
512 }
513
514 @Override
515 public void store( ResourceHistory resourceHistory )
516 {
517 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, WorkflowUtils.getPlugin( ) ) )
518 {
519 int nPos = 0;
520
521 daoUtil.setString( ++nPos, resourceHistory.getUserAccessCode( ) );
522
523 daoUtil.setInt( ++nPos, resourceHistory.getId( ) );
524
525 daoUtil.executeUpdate( );
526 }
527 }
528 }