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.action;
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.action.ActionFilter;
39 import fr.paris.lutece.plugins.workflowcore.business.action.IActionDAO;
40 import fr.paris.lutece.plugins.workflowcore.business.icon.Icon;
41 import fr.paris.lutece.plugins.workflowcore.business.state.State;
42 import fr.paris.lutece.plugins.workflowcore.business.workflow.Workflow;
43 import fr.paris.lutece.util.sql.DAOUtil;
44 import java.sql.Statement;
45
46 import java.util.ArrayList;
47 import java.util.Collection;
48 import java.util.LinkedHashSet;
49 import java.util.List;
50
51
52
53
54
55
56 public class ActionDAO implements IActionDAO
57 {
58 private static final String SQL_QUERY_SELECT_ALL = "SELECT a.id_action,a.name,a.description,a.id_workflow, "
59 + " a.id_state_after,a.id_icon,a.is_automatic,a.is_mass_action,a.display_order,a.is_automatic_reflexive_action,a.id_alternative_state_after, a.uid_action ";
60 private static final String SQL_QUERY_SELECT_ICON = ",i.name,i.mime_type,i.file_value,i.width,i.height ";
61 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = SQL_QUERY_SELECT_ALL + " FROM workflow_action a WHERE a.id_action=? ";
62 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY_WITH_ICON = SQL_QUERY_SELECT_ALL + SQL_QUERY_SELECT_ICON
63 + " FROM workflow_action a LEFT JOIN workflow_icon i ON (a.id_icon = i.id_icon) WHERE a.id_action=?";
64 private static final String SQL_QUERY_SELECT_ACTION_BY_FILTER = SQL_QUERY_SELECT_ALL + SQL_QUERY_SELECT_ICON
65 + " FROM workflow_action a LEFT JOIN workflow_icon i ON (a.id_icon = i.id_icon) ";
66 private static final String SQL_QUERY_INSERT = "INSERT INTO workflow_action "
67 + "(name,description,id_workflow,id_state_after,id_icon,is_automatic,is_mass_action,display_order,is_automatic_reflexive_action,id_alternative_state_after,uid_action)"
68 + " VALUES(?,?,?,?,?,?,?,?,?,?,?)";
69 private static final String SQL_QUERY_UPDATE = "UPDATE workflow_action SET id_action=?,name=?,description=?,"
70 + "id_workflow=?,id_state_after=?,id_icon=?,is_automatic=?,is_mass_action=?, display_order=?, is_automatic_reflexive_action=?, id_alternative_state_after=?, uid_action=? "
71 + " WHERE id_action=?";
72 private static final String SQL_QUERY_INSERT_LINKED_ACTION = " INSERT INTO workflow_action_action (id_action, id_linked_action) VALUES ( ?,? ) ";
73 private static final String SQL_QUERY_REMOVE_LINKED_ACTION = " DELETE FROM workflow_action_action WHERE id_action = ? OR id_linked_action = ? ";
74 private static final String SQL_QUERY_SELECT_LINKED_ACTION = " SELECT id_action FROM workflow_action_action WHERE id_linked_action = ?";
75 private static final String SQL_QUERY_SELECT_LINKED_ACTION_UID = "SELECT a.uid_action FROM workflow_action_action aa, workflow_action a WHERE aa.id_linked_action = a.id_action AND aa.id_linked_action = ?;";
76 private static final String SQL_QUERY_SELECT_LINKED_ACTION_2 = " SELECT id_linked_action FROM workflow_action_action WHERE id_action = ?";
77 private static final String SQL_QUERY_DELETE = "DELETE FROM workflow_action WHERE id_action=? ";
78 private static final String SQL_FILTER_ID_WORKFLOW = " a.id_workflow = ? ";
79 private static final String SQL_FILTER_ID_STATE_BEFORE_LIST = " a.id_action in (select b.id_action from workflow_action_state_before b where b.id_state_before = ? ) ";
80 private static final String SQL_FILTER_ID_STATE_AFTER = " a.id_state_after = ? ";
81 private static final String SQL_FILTER_ID_ICON = " a.id_icon = ? ";
82 private static final String SQL_FILTER_IS_AUTOMATIC = " a.is_automatic = ? ";
83 private static final String SQL_FILTER_IS_MASS_ACTION = " a.is_mass_action = ? ";
84 private static final String SQL_FILTER_IS_AUTOMATIC_REFLEXIVE_ACTION = " a.is_automatic_reflexive_action = ? ";
85 private static final String SQL_ORDER_BY_ORDER_DISPLAY = " ORDER BY display_order";
86 private static final String SQL_QUERY_FIND_MAXIMUM_ORDER_BY_WORKFLOW = "SELECT MAX(display_order) FROM workflow_action WHERE id_workflow=?";
87 private static final String SQL_QUERY_DECREMENT_ORDER = "UPDATE workflow_action SET display_order = display_order - 1 WHERE display_order > ? AND id_workflow=? ";
88 private static final String SQL_QUERY_ACTIONS_WITH_ORDER_BETWEEN = SQL_QUERY_SELECT_ALL + SQL_QUERY_SELECT_ICON
89 + " FROM workflow_action a LEFT JOIN workflow_icon i ON (a.id_icon = i.id_icon) WHERE (display_order BETWEEN ? AND ?) AND id_workflow=?";
90 private static final String SQL_QUERY_ACTIONS_AFTER_ORDER = SQL_QUERY_SELECT_ALL + SQL_QUERY_SELECT_ICON
91 + " FROM workflow_action a LEFT JOIN workflow_icon i ON (a.id_icon = i.id_icon) WHERE display_order >=? AND id_workflow=?";
92 private static final String SQL_QUERY_SELECT_ACTION_FOR_ORDER_INIT = SQL_QUERY_SELECT_ALL + SQL_QUERY_SELECT_ICON
93 + " FROM workflow_action a LEFT JOIN workflow_icon i ON (a.id_icon = i.id_icon) WHERE id_workflow=? AND is_automatic_reflexive_action = 0 ORDER BY id_action ";
94
95
96
97
98 @Override
99 public synchronized void insert( Action action )
100 {
101 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, WorkflowUtils.getPlugin( ) ) )
102 {
103 int nPos = 0;
104 daoUtil.setString( ++nPos, action.getName( ) );
105 daoUtil.setString( ++nPos, action.getDescription( ) );
106 daoUtil.setInt( ++nPos, action.getWorkflow( ).getId( ) );
107 daoUtil.setInt( ++nPos, action.getStateAfter( ).getId( ) );
108 daoUtil.setInt( ++nPos, action.getIcon( ).getId( ) );
109 daoUtil.setBoolean( ++nPos, action.isAutomaticState( ) );
110 daoUtil.setBoolean( ++nPos, action.isMassAction( ) );
111 daoUtil.setInt( ++nPos, action.getOrder( ) );
112 daoUtil.setBoolean( ++nPos, action.isAutomaticReflexiveAction( ) );
113 daoUtil.setInt( ++nPos, (action.getAlternativeStateAfter( )!=null?
114 action.getAlternativeStateAfter( ).getId( ):-1) );
115 daoUtil.setString( ++nPos, action.getUid( ) );
116 daoUtil.executeUpdate( );
117 if ( daoUtil.nextGeneratedKey( ) )
118 {
119 action.setId( daoUtil.getGeneratedKeyInt( 1 ) );
120 }
121 }
122 }
123
124
125
126
127 @Override
128 public void store( Action action )
129 {
130 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, WorkflowUtils.getPlugin( ) ) )
131 {
132 int nPos = 0;
133
134 daoUtil.setInt( ++nPos, action.getId( ) );
135 daoUtil.setString( ++nPos, action.getName( ) );
136 daoUtil.setString( ++nPos, action.getDescription( ) );
137 daoUtil.setInt( ++nPos, action.getWorkflow( ).getId( ) );
138 daoUtil.setInt( ++nPos, action.getStateAfter( ).getId( ) );
139 daoUtil.setInt( ++nPos, action.getIcon( ).getId( ) );
140 daoUtil.setBoolean( ++nPos, action.isAutomaticState( ) );
141 daoUtil.setBoolean( ++nPos, action.isMassAction( ) );
142 daoUtil.setInt( ++nPos, action.getOrder( ) );
143 daoUtil.setBoolean( ++nPos, action.isAutomaticReflexiveAction( ) );
144 daoUtil.setInt( ++nPos, (action.getAlternativeStateAfter( )!=null?
145 action.getAlternativeStateAfter( ).getId( ):-1) );
146 daoUtil.setString( ++nPos, action.getUid( ) );
147
148 daoUtil.setInt( ++nPos, action.getId( ) );
149 daoUtil.executeUpdate( );
150 }
151 }
152
153
154
155
156 @Override
157 public Action load( int nIdAction )
158 {
159 Action action = null;
160 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, WorkflowUtils.getPlugin( ) ) )
161 {
162 daoUtil.setInt( 1, nIdAction );
163 daoUtil.executeQuery( );
164
165 if ( daoUtil.next( ) )
166 {
167 action = dataToObject( daoUtil, false );
168 }
169
170 }
171 return action;
172 }
173
174
175
176
177 @Override
178 public Action loadWithIcon( int nIdAction )
179 {
180 Action action = null;
181
182 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY_WITH_ICON, WorkflowUtils.getPlugin( ) ) )
183 {
184 daoUtil.setInt( 1, nIdAction );
185 daoUtil.executeQuery( );
186
187 if ( daoUtil.next( ) )
188 {
189 action = dataToObject( daoUtil, true );
190 }
191 }
192 return action;
193 }
194
195
196
197
198 @Override
199 public void delete( int nIdAction )
200 {
201 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, WorkflowUtils.getPlugin( ) ) )
202 {
203 daoUtil.setInt( 1, nIdAction );
204 daoUtil.executeUpdate( );
205 }
206 }
207
208
209
210
211 @Override
212 public List<Action> selectActionsByFilter( ActionFilter filter )
213 {
214 List<Action> listAction = new ArrayList<>( );
215 List<String> listStrFilter = new ArrayList<>( );
216
217 if ( filter.containsIdWorkflow( ) )
218 {
219 listStrFilter.add( SQL_FILTER_ID_WORKFLOW );
220 }
221 if ( filter.containsIdStateBefore( ) )
222 {
223 listStrFilter.add( SQL_FILTER_ID_STATE_BEFORE_LIST );
224 }
225
226 if ( filter.containsIdStateAfter( ) )
227 {
228 listStrFilter.add( SQL_FILTER_ID_STATE_AFTER );
229 }
230
231 if ( filter.containsIdIcon( ) )
232 {
233 listStrFilter.add( SQL_FILTER_ID_ICON );
234 }
235
236 if ( filter.containsIsAutomaticState( ) )
237 {
238 listStrFilter.add( SQL_FILTER_IS_AUTOMATIC );
239 }
240
241 if ( filter.containsIsMassAction( ) )
242 {
243 listStrFilter.add( SQL_FILTER_IS_MASS_ACTION );
244 }
245 if ( filter.containsIsAutomaticReflexiveAction( ) )
246 {
247 listStrFilter.add( SQL_FILTER_IS_AUTOMATIC_REFLEXIVE_ACTION );
248 }
249
250 String strSQL = WorkflowUtils.buildRequestWithFilter( SQL_QUERY_SELECT_ACTION_BY_FILTER, listStrFilter, SQL_ORDER_BY_ORDER_DISPLAY );
251
252 try ( DAOUtil daoUtil = new DAOUtil( strSQL, WorkflowUtils.getPlugin( ) ) )
253 {
254 int nPos = 0;
255 if ( filter.containsIdWorkflow( ) )
256 {
257 daoUtil.setInt( ++nPos, filter.getIdWorkflow( ) );
258 }
259
260 if ( filter.containsIdStateBefore( ) )
261 {
262 daoUtil.setInt( ++nPos, filter.getIdStateBefore( ) );
263 }
264
265 if ( filter.containsIdStateAfter( ) )
266 {
267 daoUtil.setInt( ++nPos, filter.getIdStateAfter( ) );
268 }
269
270 if ( filter.containsIdIcon( ) )
271 {
272 daoUtil.setInt( ++nPos, filter.getIdIcon( ) );
273 }
274
275 if ( filter.containsIsAutomaticState( ) )
276 {
277 daoUtil.setInt( ++nPos, filter.getIsAutomaticState( ) );
278 }
279
280 if ( filter.containsIsMassAction( ) )
281 {
282 daoUtil.setInt( ++nPos, filter.getIsMassAction( ) );
283 }
284
285 if ( filter.containsIsAutomaticReflexiveAction( ) )
286 {
287 daoUtil.setInt( ++nPos, filter.isAutomaticReflexiveAction( ) );
288 }
289
290 daoUtil.executeQuery( );
291
292 while ( daoUtil.next( ) )
293 {
294 Action action = dataToObject( daoUtil, true );
295 listAction.add( action );
296 }
297
298 }
299
300 return listAction;
301 }
302
303
304
305
306 @Override
307 public synchronized void insertLinkedActions( int nIdAction, int nIdLinkedAction )
308 {
309 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_LINKED_ACTION, WorkflowUtils.getPlugin( ) ) )
310 {
311 int nPos = 0;
312 daoUtil.setInt( ++nPos, nIdAction );
313 daoUtil.setInt( ++nPos, nIdLinkedAction );
314
315 daoUtil.executeUpdate( );
316 }
317 }
318
319
320
321
322 @Override
323 public void removeLinkedActions( int nIdAction )
324 {
325 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_LINKED_ACTION, WorkflowUtils.getPlugin( ) ) )
326 {
327 int nIndex = 1;
328 daoUtil.setInt( nIndex++, nIdAction );
329 daoUtil.setInt( nIndex, nIdAction );
330
331 daoUtil.executeUpdate( );
332 }
333 }
334
335
336
337
338 @Override
339 public Collection<Integer> selectListIdsLinkedAction( int nIdAction )
340 {
341 Collection<Integer> listIdsLinkedAction = new LinkedHashSet<>( );
342 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LINKED_ACTION, WorkflowUtils.getPlugin( ) ) )
343 {
344 daoUtil.setInt( 1, nIdAction );
345 daoUtil.executeQuery( );
346
347 while ( daoUtil.next( ) )
348 {
349 listIdsLinkedAction.add( daoUtil.getInt( 1 ) );
350 }
351 }
352
353 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LINKED_ACTION_2, WorkflowUtils.getPlugin( ) ) )
354 {
355 daoUtil.setInt( 1, nIdAction );
356 daoUtil.executeQuery( );
357
358 while ( daoUtil.next( ) )
359 {
360 listIdsLinkedAction.add( daoUtil.getInt( 1 ) );
361 }
362 }
363 return listIdsLinkedAction;
364 }
365
366
367
368
369
370 @Override
371 public Collection<String> selectListUidsLinkedAction( int nIdAction )
372 {
373 Collection<String> listUidsLinkedAction = new LinkedHashSet<>( );
374 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LINKED_ACTION_UID, WorkflowUtils.getPlugin( ) ) )
375 {
376 daoUtil.setInt( 1, nIdAction );
377 daoUtil.executeQuery( );
378
379 while ( daoUtil.next( ) )
380 {
381 listUidsLinkedAction.add( daoUtil.getString( 1 ) );
382 }
383 }
384 return listUidsLinkedAction;
385 }
386
387
388
389
390
391 @Override
392 public int findMaximumOrderByWorkflowId( int nWorkflowId )
393 {
394 int nMaximumOrder = 0;
395 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_MAXIMUM_ORDER_BY_WORKFLOW, WorkflowUtils.getPlugin( ) ) )
396 {
397 daoUtil.setInt( 1, nWorkflowId );
398 daoUtil.executeQuery( );
399
400 while ( daoUtil.next( ) )
401 {
402 nMaximumOrder = daoUtil.getInt( 1 );
403 }
404 }
405 return nMaximumOrder;
406 }
407
408
409
410
411 @Override
412 public void decrementOrderByOne( int nOrder, int nIdWorkflow )
413 {
414 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DECREMENT_ORDER, WorkflowUtils.getPlugin( ) ) )
415 {
416 daoUtil.setInt( 1, nOrder );
417 daoUtil.setInt( 2, nIdWorkflow );
418 daoUtil.executeUpdate( );
419 }
420 }
421
422
423
424
425 @Override
426 public List<Action> findStatesBetweenOrders( int nOrder1, int nOrder2, int nIdWorkflow )
427 {
428 List<Action> listResult = new ArrayList<>( );
429 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ACTIONS_WITH_ORDER_BETWEEN, WorkflowUtils.getPlugin( ) ) )
430 {
431 daoUtil.setInt( 1, nOrder1 );
432 daoUtil.setInt( 2, nOrder2 );
433 daoUtil.setInt( 3, nIdWorkflow );
434 daoUtil.executeQuery( );
435
436 while ( daoUtil.next( ) )
437 {
438 Action action = dataToObject( daoUtil, true );
439 listResult.add( action );
440 }
441 }
442
443 return listResult;
444 }
445
446
447
448
449 @Override
450 public List<Action> findStatesAfterOrder( int nOrder, int nIdWorkflow )
451 {
452 List<Action> listResult = new ArrayList<>( );
453 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ACTIONS_AFTER_ORDER, WorkflowUtils.getPlugin( ) ) )
454 {
455 daoUtil.setInt( 1, nOrder );
456 daoUtil.setInt( 2, nIdWorkflow );
457 daoUtil.executeQuery( );
458
459 while ( daoUtil.next( ) )
460 {
461 Action action = dataToObject( daoUtil, true );
462 listResult.add( action );
463 }
464 }
465 return listResult;
466 }
467
468
469
470
471 @Override
472 public List<Action> findActionsForOrderInit( int nIdWorkflow )
473 {
474 List<Action> listAction = new ArrayList<>( );
475 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ACTION_FOR_ORDER_INIT, WorkflowUtils.getPlugin( ) ) )
476 {
477 daoUtil.setInt( 1, nIdWorkflow );
478 daoUtil.executeQuery( );
479
480 while ( daoUtil.next( ) )
481 {
482 Action action = dataToObject( daoUtil, true );
483 listAction.add( action );
484 }
485 }
486
487 return listAction;
488 }
489
490 private Action dataToObject( DAOUtil daoUtil, boolean populateIcon )
491 {
492 int nPos = 0;
493 Action action = new Action( );
494 action.setId( daoUtil.getInt( ++nPos ) );
495 action.setName( daoUtil.getString( ++nPos ) );
496 action.setDescription( daoUtil.getString( ++nPos ) );
497
498 Workflow workflow = new Workflow( );
499 workflow.setId( daoUtil.getInt( ++nPos ) );
500 action.setWorkflow( workflow );
501
502 State stateAfter = new State( );
503 stateAfter.setId( daoUtil.getInt( ++nPos ) );
504 action.setStateAfter( stateAfter );
505
506 Icon icon = new Icon( );
507 icon.setId( daoUtil.getInt( ++nPos ) );
508 action.setIcon( icon );
509
510 action.setAutomaticState( daoUtil.getBoolean( ++nPos ) );
511 action.setMassAction( daoUtil.getBoolean( ++nPos ) );
512 action.setOrder( daoUtil.getInt( ++nPos ) );
513 action.setAutomaticReflexiveAction( daoUtil.getBoolean( ++nPos ) );
514
515 State alternativeStateAfter = new State( );
516 alternativeStateAfter.setId( daoUtil.getInt( ++nPos ) );
517 action.setAlternativeStateAfter( alternativeStateAfter );
518 action.setUid( daoUtil.getString( ++nPos ) );
519
520 if ( populateIcon )
521 {
522 icon.setName( daoUtil.getString( ++nPos ) );
523 icon.setMimeType( daoUtil.getString( ++nPos ) );
524 icon.setValue( daoUtil.getBytes( ++nPos ) );
525 icon.setWidth( daoUtil.getInt( ++nPos ) );
526 icon.setHeight( daoUtil.getInt( ++nPos ) );
527 }
528 return action;
529 }
530 }