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.directory.business;
35
36 import fr.paris.lutece.plugins.directory.utils.DirectoryUtils;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.ReferenceList;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import org.apache.commons.lang.StringUtils;
42
43 import java.util.ArrayList;
44 import java.util.List;
45
46
47
48
49 public final class DirectoryDAO implements IDirectoryDAO
50 {
51
52 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_directory ) FROM directory_directory";
53 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = "SELECT id_directory,title,description,"
54 + " unavailability_message,workgroup_key,role_key,"
55 + " is_enabled,date_creation,id_result_list_template,id_result_record_template,id_form_search_template,number_record_per_page,"
56 + " id_workflow, is_search_wf_state, is_search_comp_wf_state, "
57 + " is_ascending_sort, is_directory_record_activated, id_sort_entry, is_indexed,id_sort_entry_front,is_ascending_sort_front, front_office_title, automatic_record_removal_workflow_state "
58 + " FROM directory_directory WHERE id_directory = ?";
59 private static final String SQL_QUERY_INSERT = "INSERT INTO directory_directory (id_directory,title,description,"
60 + " unavailability_message,workgroup_key,role_key,"
61 + " is_enabled,date_creation,id_result_list_template,id_result_record_template,id_form_search_template,number_record_per_page,"
62 + " id_workflow, is_search_wf_state, is_search_comp_wf_state, is_directory_record_activated, is_indexed, front_office_title, automatic_record_removal_workflow_state) "
63 + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
64 private static final String SQL_QUERY_DELETE = "DELETE FROM directory_directory WHERE id_directory = ? ";
65 private static final String SQL_QUERY_UPDATE = "UPDATE directory_directory SET id_directory=?,title=?,description=?,"
66 + "unavailability_message=?,workgroup_key=?,role_key=?,"
67 + "is_enabled=?,id_result_list_template=?,id_result_record_template=? ,id_form_search_template=? ,number_record_per_page=?,"
68 + "id_workflow=?, is_search_wf_state=?, is_search_comp_wf_state=?, "
69 + "is_ascending_sort=?, is_directory_record_activated=?, id_sort_entry=?, is_indexed=?,id_sort_entry_front=?,is_ascending_sort_front=?, "
70 + "front_office_title=?, automatic_record_removal_workflow_state=? WHERE id_directory=?";
71 private static final String SQL_QUERY_SELECT_DIRECTORY_BY_FILTER = "SELECT id_directory,title,description,"
72 + "unavailability_message,workgroup_key,role_key,"
73 + "is_enabled,date_creation,id_result_list_template,id_result_record_template,id_form_search_template,number_record_per_page"
74 + ",id_workflow,is_search_wf_state, is_search_comp_wf_state, is_ascending_sort, "
75 + " is_directory_record_activated, id_sort_entry, is_indexed,id_sort_entry_front,is_ascending_sort_front, front_office_title, automatic_record_removal_workflow_state "
76 + "FROM directory_directory ";
77 private static final String SQL_FILTER_WORKGROUP = " workgroup_key = ? ";
78 private static final String SQL_FILTER_IS_ENABLED = " is_enabled = ? ";
79 private static final String SQL_FILTER_WORKFLOW = " id_workflow = ? ";
80 private static final String SQL_FILTER_IS_INDEXED = " is_indexed = ? ";
81 private static final String SQL_ORDER_BY_DATE_CREATION = " ORDER BY date_creation DESC ";
82 private static final String SQL_ORDER_BY_TITLE_DESC = " ORDER BY title DESC ";
83 private static final String SQL_ORDER_BY_TITLE_ASC = " ORDER BY title ASC ";
84
85
86
87
88
89
90
91
92 public int newPrimaryKey( Plugin plugin )
93 {
94 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
95 daoUtil.executeQuery( );
96
97 int nKey;
98
99 if ( !daoUtil.next( ) )
100 {
101
102 nKey = 1;
103 }
104
105 nKey = daoUtil.getInt( 1 ) + 1;
106 daoUtil.free( );
107
108 return nKey;
109 }
110
111
112
113
114 @Override
115 public synchronized int insert( Directory directory, Plugin plugin )
116 {
117 directory.setIdDirectory( newPrimaryKey( plugin ) );
118
119 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
120 daoUtil.setInt( 1, directory.getIdDirectory( ) );
121 daoUtil.setString( 2, directory.getTitle( ) );
122 daoUtil.setString( 3, directory.getDescription( ) );
123 daoUtil.setString( 4, directory.getUnavailabilityMessage( ) );
124 daoUtil.setString( 5, directory.getWorkgroup( ) );
125 daoUtil.setString( 6, directory.getRoleKey( ) );
126 daoUtil.setBoolean( 7, directory.isEnabled( ) );
127 daoUtil.setTimestamp( 8, directory.getDateCreation( ) );
128
129 if ( directory.getIdResultListTemplate( ) != DirectoryUtils.CONSTANT_ID_NULL )
130 {
131 daoUtil.setInt( 9, directory.getIdResultListTemplate( ) );
132 }
133 else
134 {
135 daoUtil.setIntNull( 9 );
136 }
137
138 if ( directory.getIdResultRecordTemplate( ) != DirectoryUtils.CONSTANT_ID_NULL )
139 {
140 daoUtil.setInt( 10, directory.getIdResultRecordTemplate( ) );
141 }
142 else
143 {
144 daoUtil.setIntNull( 10 );
145 }
146
147 if ( directory.getIdFormSearchTemplate( ) != DirectoryUtils.CONSTANT_ID_NULL )
148 {
149 daoUtil.setInt( 11, directory.getIdFormSearchTemplate( ) );
150 }
151 else
152 {
153 daoUtil.setIntNull( 11 );
154 }
155
156 daoUtil.setInt( 12, directory.getNumberRecordPerPage( ) );
157
158 if ( directory.getIdWorkflow( ) != DirectoryUtils.CONSTANT_ID_NULL )
159 {
160 daoUtil.setInt( 13, directory.getIdWorkflow( ) );
161 }
162 else
163 {
164 daoUtil.setIntNull( 13 );
165 }
166
167 daoUtil.setBoolean( 14, directory.isDisplaySearchState( ) );
168 daoUtil.setBoolean( 15, directory.isDisplayComplementarySearchState( ) );
169 daoUtil.setBoolean( 16, directory.isRecordActivated( ) );
170 daoUtil.setBoolean( 17, directory.isIndexed( ) );
171 daoUtil.setString( 18, directory.getFrontOfficeTitle( ) );
172 daoUtil.setInt( 19, directory.getIdWorkflowStateToRemove( ) );
173
174 daoUtil.executeUpdate( );
175 daoUtil.free( );
176
177 return directory.getIdDirectory( );
178 }
179
180
181
182
183 @Override
184 public Directory load( int nId, Plugin plugin )
185 {
186 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
187 daoUtil.setInt( 1, nId );
188 daoUtil.executeQuery( );
189
190 Directory directory = null;
191
192 if ( daoUtil.next( ) )
193 {
194 directory = new Directory( );
195 directory.setIdDirectory( daoUtil.getInt( 1 ) );
196 directory.setTitle( daoUtil.getString( 2 ) );
197 directory.setDescription( daoUtil.getString( 3 ) );
198 directory.setUnavailabilityMessage( daoUtil.getString( 4 ) );
199 directory.setWorkgroup( daoUtil.getString( 5 ) );
200 directory.setRoleKey( daoUtil.getString( 6 ) );
201 directory.setEnabled( daoUtil.getBoolean( 7 ) );
202 directory.setDateCreation( daoUtil.getTimestamp( 8 ) );
203
204 if ( daoUtil.getObject( 9 ) != null )
205 {
206 directory.setIdResultListTemplate( daoUtil.getInt( 9 ) );
207 }
208
209 if ( daoUtil.getObject( 10 ) != null )
210 {
211 directory.setIdResultRecordTemplate( daoUtil.getInt( 10 ) );
212 }
213
214 if ( daoUtil.getObject( 11 ) != null )
215 {
216 directory.setIdFormSearchTemplate( daoUtil.getInt( 11 ) );
217 }
218
219 directory.setNumberRecordPerPage( daoUtil.getInt( 12 ) );
220
221 if ( daoUtil.getObject( 13 ) != null )
222 {
223 directory.setIdWorkflow( daoUtil.getInt( 13 ) );
224 }
225
226 directory.setDisplaySearchState( daoUtil.getBoolean( 14 ) );
227 directory.setDisplayComplementarySearchState( daoUtil.getBoolean( 15 ) );
228
229 directory.setAscendingSort( daoUtil.getBoolean( 16 ) );
230 directory.setRecordActivated( daoUtil.getBoolean( 17 ) );
231 directory.setIdSortEntry( daoUtil.getString( 18 ) );
232 directory.setIndexed( daoUtil.getBoolean( 19 ) );
233 directory.setIdSortEntryFront( daoUtil.getString( 20 ) );
234 directory.setAscendingSortFront( daoUtil.getBoolean( 21 ) );
235 directory.setFrontOfficeTitle( daoUtil.getString( 22 ) );
236 directory.setIdWorkflowStateToRemove( daoUtil.getInt( 23 ) );
237 }
238
239 daoUtil.free( );
240
241 return directory;
242 }
243
244
245
246
247 @Override
248 public void delete( int nIdDirectory, Plugin plugin )
249 {
250 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
251 daoUtil.setInt( 1, nIdDirectory );
252 daoUtil.executeUpdate( );
253 daoUtil.free( );
254 }
255
256
257
258
259 @Override
260 public void store( Directory directory, Plugin plugin )
261 {
262 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
263
264 daoUtil.setInt( 1, directory.getIdDirectory( ) );
265 daoUtil.setString( 2, directory.getTitle( ) );
266 daoUtil.setString( 3, directory.getDescription( ) );
267 daoUtil.setString( 4, directory.getUnavailabilityMessage( ) );
268 daoUtil.setString( 5, directory.getWorkgroup( ) );
269 daoUtil.setString( 6, directory.getRoleKey( ) );
270 daoUtil.setBoolean( 7, directory.isEnabled( ) );
271
272 if ( directory.getIdResultListTemplate( ) != DirectoryUtils.CONSTANT_ID_NULL )
273 {
274 daoUtil.setInt( 8, directory.getIdResultListTemplate( ) );
275 }
276 else
277 {
278 daoUtil.setIntNull( 8 );
279 }
280
281 if ( directory.getIdResultRecordTemplate( ) != DirectoryUtils.CONSTANT_ID_NULL )
282 {
283 daoUtil.setInt( 9, directory.getIdResultRecordTemplate( ) );
284 }
285 else
286 {
287 daoUtil.setIntNull( 9 );
288 }
289
290 if ( directory.getIdFormSearchTemplate( ) != DirectoryUtils.CONSTANT_ID_NULL )
291 {
292 daoUtil.setInt( 10, directory.getIdFormSearchTemplate( ) );
293 }
294 else
295 {
296 daoUtil.setIntNull( 10 );
297 }
298
299 daoUtil.setInt( 11, directory.getNumberRecordPerPage( ) );
300
301 if ( directory.getIdWorkflow( ) != DirectoryUtils.CONSTANT_ID_NULL )
302 {
303 daoUtil.setInt( 12, directory.getIdWorkflow( ) );
304 }
305 else
306 {
307 daoUtil.setIntNull( 12 );
308 }
309
310 daoUtil.setBoolean( 13, directory.isDisplaySearchState( ) );
311 daoUtil.setBoolean( 14, directory.isDisplayComplementarySearchState( ) );
312
313 daoUtil.setBoolean( 15, directory.isAscendingSort( ) );
314 daoUtil.setBoolean( 16, directory.isRecordActivated( ) );
315 daoUtil.setString( 17, directory.getIdSortEntry( ) );
316 daoUtil.setBoolean( 18, directory.isIndexed( ) );
317 daoUtil.setString( 19, directory.getIdSortEntryFront( ) );
318 daoUtil.setBoolean( 20, directory.isAscendingSortFront( ) );
319 daoUtil.setString( 21, directory.getFrontOfficeTitle( ) );
320 daoUtil.setInt( 22, directory.getIdWorkflowStateToRemove( ) );
321
322 daoUtil.setInt( 23, directory.getIdDirectory( ) );
323
324 daoUtil.executeUpdate( );
325 daoUtil.free( );
326 }
327
328
329
330
331 @Override
332 public List<Directory> selectDirectoryList( DirectoryFilter filter, Plugin plugin )
333 {
334 List<Directory> directoryList = new ArrayList<Directory>( );
335 Directory directory = null;
336 List<String> listStrFilter = new ArrayList<String>( );
337
338 if ( filter.containsWorkgroupCriteria( ) )
339 {
340 listStrFilter.add( SQL_FILTER_WORKGROUP );
341 }
342
343 if ( filter.containsIsDisabled( ) )
344 {
345 listStrFilter.add( SQL_FILTER_IS_ENABLED );
346 }
347
348 if ( filter.containsIdWorkflow( ) )
349 {
350 listStrFilter.add( SQL_FILTER_WORKFLOW );
351 }
352
353 if ( filter.containsIsIndexed( ) )
354 {
355 listStrFilter.add( SQL_FILTER_IS_INDEXED );
356 }
357
358 String strOrderBy = SQL_ORDER_BY_DATE_CREATION;
359
360 if ( StringUtils.isNotBlank( filter.getOrder( ) ) )
361 {
362 if ( Boolean.TRUE.toString( ).equals( filter.getOrder( ) ) )
363 {
364 strOrderBy = SQL_ORDER_BY_TITLE_ASC;
365 }
366 else
367 {
368 strOrderBy = SQL_ORDER_BY_TITLE_DESC;
369 }
370 }
371
372 String strSQL = DirectoryUtils.buildRequetteWithFilter( SQL_QUERY_SELECT_DIRECTORY_BY_FILTER, listStrFilter, strOrderBy );
373 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
374 int nIndex = 1;
375
376 if ( filter.containsWorkgroupCriteria( ) )
377 {
378 daoUtil.setString( nIndex, filter.getWorkgroup( ) );
379 nIndex++;
380 }
381
382 if ( filter.containsIsDisabled( ) )
383 {
384 daoUtil.setInt( nIndex, filter.getIsDisabled( ) );
385 nIndex++;
386 }
387
388 if ( filter.containsIdWorkflow( ) )
389 {
390 daoUtil.setInt( nIndex, filter.getIdWorkflow( ) );
391 nIndex++;
392 }
393
394 if ( filter.containsIsIndexed( ) )
395 {
396 daoUtil.setInt( nIndex, filter.getIsIndexed( ) );
397 nIndex++;
398 }
399
400 daoUtil.executeQuery( );
401
402 while ( daoUtil.next( ) )
403 {
404 directory = new Directory( );
405 directory.setIdDirectory( daoUtil.getInt( 1 ) );
406 directory.setTitle( daoUtil.getString( 2 ) );
407 directory.setDescription( daoUtil.getString( 3 ) );
408 directory.setUnavailabilityMessage( daoUtil.getString( 4 ) );
409 directory.setWorkgroup( daoUtil.getString( 5 ) );
410 directory.setRoleKey( daoUtil.getString( 6 ) );
411 directory.setEnabled( daoUtil.getBoolean( 7 ) );
412 directory.setDateCreation( daoUtil.getTimestamp( 8 ) );
413
414 if ( daoUtil.getObject( 9 ) != null )
415 {
416 directory.setIdResultListTemplate( daoUtil.getInt( 9 ) );
417 }
418
419 if ( daoUtil.getObject( 10 ) != null )
420 {
421 directory.setIdResultRecordTemplate( daoUtil.getInt( 10 ) );
422 }
423
424 if ( daoUtil.getObject( 11 ) != null )
425 {
426 directory.setIdFormSearchTemplate( daoUtil.getInt( 11 ) );
427 }
428
429 directory.setNumberRecordPerPage( daoUtil.getInt( 12 ) );
430 directory.setIdWorkflow( daoUtil.getInt( 13 ) );
431 directory.setDisplaySearchState( daoUtil.getBoolean( 14 ) );
432 directory.setDisplayComplementarySearchState( daoUtil.getBoolean( 15 ) );
433 directory.setAscendingSort( daoUtil.getBoolean( 16 ) );
434 directory.setRecordActivated( daoUtil.getBoolean( 17 ) );
435 directory.setIdSortEntry( daoUtil.getString( 18 ) );
436 directory.setIndexed( daoUtil.getBoolean( 19 ) );
437 directory.setIdSortEntryFront( daoUtil.getString( 20 ) );
438 directory.setAscendingSortFront( daoUtil.getBoolean( 21 ) );
439 directory.setFrontOfficeTitle( daoUtil.getString( 22 ) );
440 directory.setIdWorkflowStateToRemove( daoUtil.getInt( 23 ) );
441
442 directoryList.add( directory );
443 }
444
445 daoUtil.free( );
446
447 return directoryList;
448 }
449
450
451
452
453 @Override
454 public ReferenceList getEnableDirectoryList( Plugin plugin )
455 {
456 ReferenceList listDirectory = new ReferenceList( );
457 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DIRECTORY_BY_FILTER + SQL_ORDER_BY_DATE_CREATION, plugin );
458 daoUtil.executeQuery( );
459
460 Directory directory;
461
462 while ( daoUtil.next( ) )
463 {
464 directory = new Directory( );
465 directory.setIdDirectory( daoUtil.getInt( 1 ) );
466 directory.setTitle( daoUtil.getString( 2 ) );
467 listDirectory.addItem( directory.getIdDirectory( ), directory.getTitle( ) );
468 }
469
470 daoUtil.free( );
471
472 return listDirectory;
473 }
474 }