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.state;
35
36 import fr.paris.lutece.plugins.workflow.utils.WorkflowUtils;
37 import fr.paris.lutece.plugins.workflowcore.business.icon.Icon;
38 import fr.paris.lutece.plugins.workflowcore.business.state.IStateDAO;
39 import fr.paris.lutece.plugins.workflowcore.business.state.State;
40 import fr.paris.lutece.plugins.workflowcore.business.state.StateFilter;
41 import fr.paris.lutece.plugins.workflowcore.business.workflow.Workflow;
42 import fr.paris.lutece.util.sql.DAOUtil;
43 import java.sql.Statement;
44
45 import java.util.ArrayList;
46 import java.util.List;
47
48
49
50
51
52
53 public class StateDAO implements IStateDAO
54 {
55 private static final String SQL_SELECT_ALL = "SELECT s.id_state,s.name,s.description,s.id_workflow,s.is_initial_state,s.is_required_workgroup_assigned,s.display_order,s.id_icon, s.uid_state ";
56 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = SQL_SELECT_ALL + " FROM workflow_state s WHERE s.id_state=?";
57 private static final String SQL_QUERY_FIND_BY_RESSOURCE = SQL_SELECT_ALL
58 + " FROM workflow_state s INNER JOIN workflow_resource_workflow r ON (r.id_state = s.id_state) WHERE r.id_resource=? AND r.id_workflow=? AND r.resource_type=?";
59 private static final String SQL_QUERY_SELECT_STATE_BY_FILTER = SQL_SELECT_ALL + " FROM workflow_state s ";
60 private static final String SQL_QUERY_INSERT = "INSERT INTO workflow_state "
61 + "(name,description,id_workflow,is_initial_state,is_required_workgroup_assigned,display_order,id_icon,uid_state)VALUES(?,?,?,?,?,?,?,?)";
62 private static final String SQL_QUERY_UPDATE = "UPDATE workflow_state SET name=?,description=?,id_workflow=?,is_initial_state=?,is_required_workgroup_assigned=?, display_order=?, id_icon=?, uid_state=? WHERE id_state=?";
63 private static final String SQL_QUERY_DELETE = "DELETE FROM workflow_state WHERE id_state=? ";
64 private static final String SQL_FILTER_ID_WORKFLOW = " s.id_workflow = ? ";
65 private static final String SQL_FILTER_IS_INITIAL_STATE = " s.is_initial_state = ? ";
66 private static final String SQL_ORDER_BY_STATE_ORDER = " ORDER BY display_order ";
67 private static final String SQL_QUERY_FIND_MAXIMUM_ORDER_BY_WORKFLOW = "SELECT MAX(display_order) FROM workflow_state WHERE id_workflow=?";
68 private static final String SQL_QUERY_DECREMENT_ORDER = "UPDATE workflow_state SET display_order = display_order - 1 WHERE display_order > ? AND id_workflow=? ";
69 private static final String SQL_QUERY_STATES_WITH_ORDER_BETWEEN = SQL_SELECT_ALL
70 + " FROM workflow_state s WHERE (s.display_order BETWEEN ? AND ?) AND s.id_workflow=?";
71 private static final String SQL_QUERY_STATES_AFTER_ORDER = SQL_SELECT_ALL + " FROM workflow_state s WHERE s.display_order >=? AND s.id_workflow=?";
72 private static final String SQL_QUERY_SELECT_STATE_FOR_ORDER_INIT = SQL_SELECT_ALL + " FROM workflow_state s WHERE s.id_workflow=? ORDER BY s.id_state";
73
74
75
76
77 @Override
78 public synchronized void insert( State state )
79 {
80
81 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, WorkflowUtils.getPlugin( ) ) )
82 {
83 int nPos = 0;
84 daoUtil.setString( ++nPos, state.getName( ) );
85 daoUtil.setString( ++nPos, state.getDescription( ) );
86 daoUtil.setInt( ++nPos, state.getWorkflow( ).getId( ) );
87 daoUtil.setBoolean( ++nPos, state.isInitialState( ) );
88 daoUtil.setBoolean( ++nPos, state.isRequiredWorkgroupAssigned( ) );
89 daoUtil.setInt( ++nPos, state.getOrder( ) );
90
91 if ( ( state.getIcon( ) == null ) || ( state.getIcon( ).getId( ) == -1 ) )
92 {
93 daoUtil.setIntNull( ++nPos );
94 }
95 else
96 {
97 daoUtil.setInt( ++nPos, state.getIcon( ).getId( ) );
98 }
99
100 daoUtil.setString( ++nPos, state.getUid( ) );
101 daoUtil.executeUpdate( );
102 if ( daoUtil.nextGeneratedKey( ) )
103 {
104 state.setId( daoUtil.getGeneratedKeyInt( 1 ) );
105 }
106 }
107 }
108
109
110
111
112 @Override
113 public void store( State state )
114 {
115 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, WorkflowUtils.getPlugin( ) ) )
116 {
117 int nPos = 0;
118
119 daoUtil.setString( ++nPos, state.getName( ) );
120 daoUtil.setString( ++nPos, state.getDescription( ) );
121 daoUtil.setInt( ++nPos, state.getWorkflow( ).getId( ) );
122 daoUtil.setBoolean( ++nPos, state.isInitialState( ) );
123 daoUtil.setBoolean( ++nPos, state.isRequiredWorkgroupAssigned( ) );
124 daoUtil.setInt( ++nPos, state.getOrder( ) );
125
126 if ( ( state.getIcon( ) == null ) || ( state.getIcon( ).getId( ) == -1 ) || ( state.getIcon( ).getId( ) == 0 ) )
127 {
128 daoUtil.setIntNull( ++nPos );
129 }
130 else
131 {
132 daoUtil.setInt( ++nPos, state.getIcon( ).getId( ) );
133 }
134
135 daoUtil.setString( ++nPos, state.getUid( ) );
136 daoUtil.setInt( ++nPos, state.getId( ) );
137 daoUtil.executeUpdate( );
138 }
139 }
140
141
142
143
144 @Override
145 public State load( int nIdState )
146 {
147 State state = null;
148 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, WorkflowUtils.getPlugin( ) ) )
149 {
150 daoUtil.setInt( 1, nIdState );
151 daoUtil.executeQuery( );
152
153 if ( daoUtil.next( ) )
154 {
155 state = dataToObject( daoUtil );
156 }
157 }
158 return state;
159 }
160
161
162
163
164 @Override
165 public State findByResource( int nIdResource, String strResourceType, int nIdWorkflow )
166 {
167 State state = null;
168 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_RESSOURCE, WorkflowUtils.getPlugin( ) ) )
169 {
170 daoUtil.setInt( 1, nIdResource );
171 daoUtil.setInt( 2, nIdWorkflow );
172 daoUtil.setString( 3, strResourceType );
173 daoUtil.executeQuery( );
174
175 if ( daoUtil.next( ) )
176 {
177 state = dataToObject( daoUtil );
178 }
179 }
180 return state;
181 }
182
183
184
185
186 @Override
187 public void delete( int nIdState )
188 {
189 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, WorkflowUtils.getPlugin( ) ) )
190 {
191 daoUtil.setInt( 1, nIdState );
192 daoUtil.executeUpdate( );
193 }
194 }
195
196
197
198
199 @Override
200 public int findMaximumOrderByWorkflowId( int nWorkflowId )
201 {
202 int nMaximumOrder = 0;
203 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_MAXIMUM_ORDER_BY_WORKFLOW, WorkflowUtils.getPlugin( ) ) )
204 {
205 daoUtil.setInt( 1, nWorkflowId );
206 daoUtil.executeQuery( );
207
208 while ( daoUtil.next( ) )
209 {
210 nMaximumOrder = daoUtil.getInt( 1 );
211 }
212 }
213 return nMaximumOrder;
214 }
215
216
217
218
219 @Override
220 public List<State> selectStatesByFilter( StateFilter filter )
221 {
222 List<State> listState = new ArrayList<>( );
223 List<String> listStrFilter = new ArrayList<>( );
224
225 if ( filter.containsIdWorkflow( ) )
226 {
227 listStrFilter.add( SQL_FILTER_ID_WORKFLOW );
228 }
229
230 if ( filter.containsIsInitialState( ) )
231 {
232 listStrFilter.add( SQL_FILTER_IS_INITIAL_STATE );
233 }
234
235 String strSQL = WorkflowUtils.buildRequestWithFilter( SQL_QUERY_SELECT_STATE_BY_FILTER, listStrFilter, SQL_ORDER_BY_STATE_ORDER );
236
237 try ( DAOUtil daoUtil = new DAOUtil( strSQL, WorkflowUtils.getPlugin( ) ) )
238 {
239 int nPos = 0;
240 if ( filter.containsIdWorkflow( ) )
241 {
242 daoUtil.setInt( ++nPos, filter.getIdWorkflow( ) );
243 }
244
245 if ( filter.containsIsInitialState( ) )
246 {
247 daoUtil.setInt( ++nPos, filter.getIsInitialState( ) );
248 }
249
250 daoUtil.executeQuery( );
251
252 while ( daoUtil.next( ) )
253 {
254 nPos = 0;
255 State state = dataToObject( daoUtil );
256 listState.add( state );
257 }
258
259 }
260
261 return listState;
262 }
263
264
265
266
267 @Override
268 public void decrementOrderByOne( int nOrder, int nIdWorkflow )
269 {
270 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DECREMENT_ORDER, WorkflowUtils.getPlugin( ) ) )
271 {
272 daoUtil.setInt( 1, nOrder );
273 daoUtil.setInt( 2, nIdWorkflow );
274 daoUtil.executeUpdate( );
275 }
276 }
277
278
279
280
281 @Override
282 public List<State> findStatesBetweenOrders( int nOrder1, int nOrder2, int nIdWorkflow )
283 {
284 List<State> listResult = new ArrayList<>( );
285 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_STATES_WITH_ORDER_BETWEEN, WorkflowUtils.getPlugin( ) ) )
286 {
287 daoUtil.setInt( 1, nOrder1 );
288 daoUtil.setInt( 2, nOrder2 );
289 daoUtil.setInt( 3, nIdWorkflow );
290 daoUtil.executeQuery( );
291
292 while ( daoUtil.next( ) )
293 {
294 State state = dataToObject( daoUtil );
295 listResult.add( state );
296 }
297 }
298 return listResult;
299 }
300
301
302
303
304 @Override
305 public List<State> findStatesAfterOrder( int nOrder, int nIdWorkflow )
306 {
307 List<State> listResult = new ArrayList<>( );
308
309 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_STATES_AFTER_ORDER, WorkflowUtils.getPlugin( ) ) )
310 {
311 int nPos = 0;
312 daoUtil.setInt( ++nPos, nOrder );
313 daoUtil.setInt( ++nPos, nIdWorkflow );
314 daoUtil.executeQuery( );
315
316 while ( daoUtil.next( ) )
317 {
318 State state = dataToObject( daoUtil );
319 listResult.add( state );
320 }
321 }
322 return listResult;
323 }
324
325
326
327
328 @Override
329 public List<State> findStatesForOrderInit( int nIdWorkflow )
330 {
331 List<State> listResult = new ArrayList<>( );
332 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_STATE_FOR_ORDER_INIT, WorkflowUtils.getPlugin( ) ) )
333 {
334 daoUtil.setInt( 1, nIdWorkflow );
335 daoUtil.executeQuery( );
336
337 while ( daoUtil.next( ) )
338 {
339 State state = dataToObject( daoUtil );
340 listResult.add( state );
341 }
342 }
343 return listResult;
344 }
345
346 private State dataToObject( DAOUtil daoUtil )
347 {
348 int nPos = 0;
349 State state = new State( );
350 state.setId( daoUtil.getInt( ++nPos ) );
351 state.setName( daoUtil.getString( ++nPos ) );
352 state.setDescription( daoUtil.getString( ++nPos ) );
353
354 Workflow workflow = new Workflow( );
355 workflow.setId( daoUtil.getInt( ++nPos ) );
356 state.setWorkflow( workflow );
357 state.setInitialState( daoUtil.getBoolean( ++nPos ) );
358 state.setRequiredWorkgroupAssigned( daoUtil.getBoolean( ++nPos ) );
359 state.setOrder( daoUtil.getInt( ++nPos ) );
360
361 Icon icon = new Icon( );
362 icon.setId( daoUtil.getInt( ++nPos ) );
363 state.setIcon( icon );
364
365 state.setUid( daoUtil.getString( ++nPos ) );
366
367 return state;
368 }
369 }