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.portal.service.workgroup.AdminWorkgroupService;
39 import fr.paris.lutece.util.ReferenceItem;
40 import fr.paris.lutece.util.sql.DAOUtil;
41
42 import org.apache.commons.lang.StringUtils;
43
44 import java.sql.Timestamp;
45
46 import java.util.ArrayList;
47 import java.util.Arrays;
48 import java.util.List;
49
50
51
52
53 public final class RecordDAO implements IRecordDAO
54 {
55
56 private static final String SQL_QUERY_NEW_PK = "SELECT MAX( id_record ) FROM directory_record";
57 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = "SELECT id_record,date_creation,id_directory,is_enabled,role_key,workgroup_key,date_modification "
58 + "FROM directory_record WHERE id_record=? ";
59 private static final String SQL_QUERY_FIND_BY_LIST_PRIMARY_KEY = "SELECT id_record,date_creation,id_directory,is_enabled,role_key,workgroup_key,date_modification "
60 + "FROM directory_record WHERE id_record IN ( ?";
61 private static final String SQL_QUERY_INSERT = "INSERT INTO directory_record ( "
62 + "id_record,date_creation,id_directory,is_enabled,role_key,workgroup_key,date_modification ) VALUES(?,?,?,?,?,?,?)";
63 private static final String SQL_QUERY_DELETE = "DELETE FROM directory_record WHERE id_record = ? ";
64 private static final String SQL_QUERY_DELETE_BY_ID_DIRECTORY = "DELETE FROM directory_record WHERE id_directory = ?";
65 private static final String SQL_QUERY_UPDATE = "UPDATE directory_record SET "
66 + "id_record=?,date_creation=?,id_directory=?,is_enabled=?,role_key=?,workgroup_key=?,date_modification=? WHERE id_record=?";
67 private static final String SQL_QUERY_SELECT_RECORD_BY_FILTER = "SELECT dr.id_record,dr.date_creation,dr.id_directory,dr.is_enabled,dr.role_key,dr.workgroup_key,dr.date_modification "
68 + "FROM directory_record dr ";
69 private static final String SQL_QUERY_SELECT_RECORD_ID_BY_FILTER = "SELECT dr.id_record FROM directory_record dr ";
70 private static final String SQL_QUERY_SELECT_COUNT_BY_FILTER = "SELECT COUNT(dr.id_record) " + "FROM directory_record dr ";
71 private static final String SQL_QUERY_SELECT_DIRECTORY_ID = "SELECT id_directory FROM directory_record WHERE id_record=?";
72 private static final String SQL_QUERY_SELECT_COUNT_DIRECYTORY_RECORD_HAS_WORKFLOW = "SELECT COUNT(*) FROM directory_record WHERE id_directory = ? AND workgroup_key IS NOT NULL";
73 private static final String SQL_FILTER_ID_DIRECTORY = " dr.id_directory = ? ";
74 private static final String SQL_FILTER_IS_ENABLED = " dr.is_enabled = ? ";
75 private static final String SQL_FILTER_OR = " OR ";
76 private static final String SQL_FILTER_OPEN_PARENTHESIS = " ( ";
77 private static final String SQL_FILTER_CLOSE_PARENTHESIS = " ) ";
78 private static final String SQL_FILTER_WORKGROUP_LIST = " dr.workgroup_key IN ( ? ";
79 private static final String SQL_FILTER_ROLE_LIST = " dr.role_key IN ( ? ";
80 private static final String SQL_ADITIONAL_PARAMETER = ",?";
81 private static final String SQL_FILTER_WORKGROUP_IS_NULL = " dr.workgroup_key IS NULL ";
82 private static final String SQL_FILTER_ROLE_IS_NULL = " dr.role_key IS NULL ";
83 private static final String SQL_ORDER_BY_DEFAULT = " ORDER BY dr.date_creation ";
84 private static final String SQL_ORDER_BY_DATE_MODIFICATION = " ORDER BY dr.date_modification ";
85 private static final String SQL_ORDER_ASC = " ASC";
86 private static final String SQL_ORDER_DESC = " DESC ";
87 private static final String SQL_ORDER_BY_DEFAULT_ASC = SQL_ORDER_BY_DEFAULT + SQL_ORDER_ASC;
88
89
90
91
92
93
94
95
96 private int newPrimaryKey( Plugin plugin )
97 {
98 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
99 daoUtil.executeQuery( );
100
101 int nKey;
102
103 if ( !daoUtil.next( ) )
104 {
105
106 nKey = 1;
107 }
108
109 nKey = daoUtil.getInt( 1 ) + 1;
110 daoUtil.free( );
111
112 return nKey;
113 }
114
115
116
117
118 @Override
119 public synchronized int insert( Record record, Plugin plugin )
120 {
121 record.setIdRecord( newPrimaryKey( plugin ) );
122
123 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
124 daoUtil.setInt( 1, record.getIdRecord( ) );
125 daoUtil.setTimestamp( 2, record.getDateCreation( ) );
126 daoUtil.setInt( 3, record.getDirectory( ).getIdDirectory( ) );
127 daoUtil.setBoolean( 4, record.isEnabled( ) );
128 daoUtil.setString( 5, record.getRoleKey( ) );
129 daoUtil.setString( 6, record.getWorkgroup( ) );
130 daoUtil.setTimestamp( 7, record.getDateModification( ) );
131
132 daoUtil.executeUpdate( );
133 daoUtil.free( );
134
135 return record.getIdRecord( );
136 }
137
138
139
140
141 @Override
142 public Record load( int nIdRecord, Plugin plugin )
143 {
144 Record record = null;
145 Directory directory;
146 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
147 daoUtil.setInt( 1, nIdRecord );
148 daoUtil.executeQuery( );
149
150 if ( daoUtil.next( ) )
151 {
152 record = new Record( );
153 record.setIdRecord( daoUtil.getInt( 1 ) );
154 record.setDateCreation( daoUtil.getTimestamp( 2 ) );
155 directory = new Directory( );
156 directory.setIdDirectory( daoUtil.getInt( 3 ) );
157 record.setDirectory( directory );
158 record.setEnabled( daoUtil.getBoolean( 4 ) );
159 record.setRoleKey( daoUtil.getString( 5 ) );
160 record.setWorkgroup( daoUtil.getString( 6 ) );
161 record.setDateModification( daoUtil.getTimestamp( 7 ) );
162 }
163
164 daoUtil.free( );
165
166 return record;
167 }
168
169
170
171
172 @Override
173 public Boolean direcytoryRecordListHasWorkflow( int nIdDirectory, Plugin plugin )
174 {
175 Boolean bResult = null;
176
177 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COUNT_DIRECYTORY_RECORD_HAS_WORKFLOW, plugin );
178 daoUtil.setInt( 1, nIdDirectory );
179
180 daoUtil.executeQuery( );
181
182 if ( daoUtil.next( ) )
183 {
184 int nNb = daoUtil.getInt( 1 );
185
186 if ( nNb > 0 )
187 {
188 bResult = Boolean.TRUE;
189 }
190 else
191 {
192 bResult = Boolean.FALSE;
193 }
194 }
195
196 daoUtil.free( );
197
198 return bResult;
199 }
200
201
202
203
204 @Override
205 public List<Record> loadList( List<Integer> listId, Plugin plugin )
206 {
207 List<Record> lRecord;
208 int nSize = listId.size( );
209
210 if ( nSize > 0 )
211 {
212
213
214 Record [ ] tabRecords = new Record [ nSize];
215
216 Directory directory;
217
218 StringBuilder sb = new StringBuilder( SQL_QUERY_FIND_BY_LIST_PRIMARY_KEY );
219
220 for ( int i = 1; i < nSize; i++ )
221 {
222 sb.append( SQL_ADITIONAL_PARAMETER );
223 }
224
225 sb.append( SQL_FILTER_CLOSE_PARENTHESIS );
226
227 DAOUtil daoUtil = new DAOUtil( sb.toString( ), plugin );
228
229 for ( int i = 0; i < nSize; i++ )
230 {
231 daoUtil.setInt( 1 + i, listId.get( i ) );
232 }
233
234 daoUtil.executeQuery( );
235
236 while ( daoUtil.next( ) )
237 {
238 Record record = new Record( );
239 record.setIdRecord( daoUtil.getInt( 1 ) );
240
241 if ( daoUtil.getTimestamp( 2 ) == null )
242 {
243 record.setDateCreation( new Timestamp( 0 ) );
244 }
245 else
246 {
247 record.setDateCreation( daoUtil.getTimestamp( 2 ) );
248 }
249
250 directory = new Directory( );
251 directory.setIdDirectory( daoUtil.getInt( 3 ) );
252 record.setDirectory( directory );
253 record.setEnabled( daoUtil.getBoolean( 4 ) );
254 record.setRoleKey( daoUtil.getString( 5 ) );
255 record.setWorkgroup( daoUtil.getString( 6 ) );
256
257 if ( daoUtil.getTimestamp( 7 ) == null )
258 {
259 record.setDateModification( new Timestamp( 0 ) );
260 }
261 else
262 {
263 record.setDateModification( daoUtil.getTimestamp( 7 ) );
264 }
265
266
267 tabRecords [listId.indexOf( record.getIdRecord( ) )] = record;
268 }
269
270 daoUtil.free( );
271
272
273 lRecord = Arrays.asList( tabRecords );
274 }
275 else
276 {
277 lRecord = new ArrayList<Record>( );
278 }
279
280 return lRecord;
281 }
282
283
284
285
286 @Override
287 public void delete( int nIdRecord, Plugin plugin )
288 {
289 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
290 daoUtil.setInt( 1, nIdRecord );
291 daoUtil.executeUpdate( );
292 daoUtil.free( );
293 }
294
295
296
297
298 @Override
299 public void deleteRecordByDirectoryId( Integer nDirectoryId, Plugin plugin )
300 {
301 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_DIRECTORY, plugin );
302 daoUtil.setInt( 1, nDirectoryId );
303 daoUtil.executeUpdate( );
304 daoUtil.free( );
305 }
306
307
308
309
310 @Override
311 public void store( Record record, Plugin plugin )
312 {
313 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
314 daoUtil.setInt( 1, record.getIdRecord( ) );
315 daoUtil.setTimestamp( 2, record.getDateCreation( ) );
316 daoUtil.setInt( 3, record.getDirectory( ).getIdDirectory( ) );
317 daoUtil.setBoolean( 4, record.isEnabled( ) );
318 daoUtil.setString( 5, record.getRoleKey( ) );
319 daoUtil.setString( 6, record.getWorkgroup( ) );
320 daoUtil.setTimestamp( 7, record.getDateModification( ) );
321
322 daoUtil.setInt( 8, record.getIdRecord( ) );
323 daoUtil.executeUpdate( );
324 daoUtil.free( );
325 }
326
327
328
329
330 @Override
331 public List<Record> selectListByFilter( RecordFieldFilter filter, Plugin plugin )
332 {
333 List<Record> recordList = new ArrayList<Record>( );
334 Record record;
335 Directory directory;
336
337 List<String> listStrFilter = buildFilterQueryHeader( filter );
338
339 String strSQL = DirectoryUtils.buildRequetteWithFilter( SQL_QUERY_SELECT_RECORD_BY_FILTER, listStrFilter, SQL_ORDER_BY_DEFAULT_ASC );
340
341 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
342
343 daoUtil = buildFilterQueryFooter( daoUtil, filter, 1 );
344
345 daoUtil.executeQuery( );
346
347 while ( daoUtil.next( ) )
348 {
349 record = new Record( );
350 record.setIdRecord( daoUtil.getInt( 1 ) );
351 record.setDateCreation( daoUtil.getTimestamp( 2 ) );
352 directory = new Directory( );
353 directory.setIdDirectory( daoUtil.getInt( 3 ) );
354 record.setDirectory( directory );
355 record.setEnabled( daoUtil.getBoolean( 4 ) );
356 record.setRoleKey( daoUtil.getString( 5 ) );
357 record.setWorkgroup( daoUtil.getString( 6 ) );
358 record.setDateModification( daoUtil.getTimestamp( 7 ) );
359
360 recordList.add( record );
361 }
362
363 daoUtil.free( );
364
365 return recordList;
366 }
367
368
369
370
371 @Override
372 public Integer getDirectoryIdByRecordId( Integer nRecordId, Plugin plugin )
373 {
374 Integer nResult = null;
375 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DIRECTORY_ID, plugin );
376
377 daoUtil.setInt( 1, nRecordId );
378 daoUtil.executeQuery( );
379
380 if ( daoUtil.next( ) )
381 {
382 nResult = daoUtil.getInt( 1 );
383 }
384
385 daoUtil.free( );
386
387 return nResult;
388 }
389
390
391
392
393 @Override
394 public int selectCountByFilter( RecordFieldFilter filter, Plugin plugin )
395 {
396 int nIdCount = 0;
397 List<String> listStrFilter = buildFilterQueryHeader( filter );
398
399 String strSQL = DirectoryUtils.buildRequetteWithFilter( SQL_QUERY_SELECT_COUNT_BY_FILTER, listStrFilter, null );
400
401 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
402
403 daoUtil = buildFilterQueryFooter( daoUtil, filter, 1 );
404
405 daoUtil.executeQuery( );
406
407 if ( daoUtil.next( ) )
408 {
409 nIdCount = daoUtil.getInt( 1 );
410 }
411
412 daoUtil.free( );
413
414 return nIdCount;
415 }
416
417
418
419
420 @Override
421 public List<Integer> selectListIdByFilter( RecordFieldFilter filter, Plugin plugin )
422 {
423 List<Integer> recordList = new ArrayList<Integer>( );
424 List<String> listFilter = buildFilterQueryHeader( filter );
425
426 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_RECORD_ID_BY_FILTER );
427
428
429 if ( filter.containsSortEntry( ) )
430 {
431 sbSQL.append( filter.getSortEntry( ).getSQLJoin( ) );
432 }
433
434 String strOrderBy = StringUtils.EMPTY;
435
436 if ( filter.isOrderByDateModification( ) )
437 {
438 strOrderBy = getOrderByQuery( filter, SQL_ORDER_BY_DATE_MODIFICATION );
439 }
440 else
441 {
442 strOrderBy = getOrderByQuery( filter, SQL_ORDER_BY_DEFAULT );
443 }
444
445 String strSQL = DirectoryUtils.buildQueryWithFilter( sbSQL, listFilter, strOrderBy );
446
447 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
448
449 daoUtil = buildFilterQueryFooter( daoUtil, filter, 1 );
450
451 daoUtil.executeQuery( );
452
453 while ( daoUtil.next( ) )
454 {
455 recordList.add( daoUtil.getInt( 1 ) );
456 }
457
458 daoUtil.free( );
459
460 return recordList;
461 }
462
463
464
465
466
467
468
469
470
471
472 private String getOrderByQuery( RecordFieldFilter filter, String strDefaultOrderQuery )
473 {
474 String strOrderBy;
475
476 if ( filter.containsSortEntry( ) )
477 {
478 strOrderBy = filter.getSortEntry( ).getSQLOrderBy( );
479 }
480 else
481 {
482
483 strOrderBy = strDefaultOrderQuery;
484 }
485
486 if ( filter.getSortOrder( ) == RecordFieldFilter.ORDER_ASC )
487 {
488 strOrderBy += SQL_ORDER_ASC;
489 }
490 else
491 {
492 strOrderBy += SQL_ORDER_DESC;
493 }
494
495 return strOrderBy;
496 }
497
498
499
500
501
502
503
504
505 private List<String> buildFilterQueryHeader( RecordFieldFilter filter )
506 {
507 List<String> listStrFilter = new ArrayList<String>( );
508
509 if ( filter.containsIdDirectory( ) )
510 {
511 listStrFilter.add( SQL_FILTER_ID_DIRECTORY );
512 }
513
514 if ( filter.containsIsDisabled( ) )
515 {
516 listStrFilter.add( SQL_FILTER_IS_ENABLED );
517 }
518
519 if ( filter.containsWorkgroupKeyList( ) )
520 {
521 StringBuffer sbWorkgroupFilter = new StringBuffer( );
522
523 boolean bContaintsAll = false;
524 List<String> lWorkgroupKey = new ArrayList<String>( );
525
526 for ( ReferenceItem rWorkgroupKey : filter.getWorkgroupKeyList( ) )
527 {
528 if ( AdminWorkgroupService.ALL_GROUPS.equals( rWorkgroupKey.getCode( ) ) )
529 {
530 bContaintsAll = true;
531 }
532 lWorkgroupKey.add( rWorkgroupKey.getCode( ) );
533 }
534
535 int nWorkgroupKeySize = lWorkgroupKey.size( );
536
537 if ( bContaintsAll )
538 {
539 if ( nWorkgroupKeySize > 0 )
540 {
541 sbWorkgroupFilter.append( SQL_FILTER_OPEN_PARENTHESIS + SQL_FILTER_WORKGROUP_IS_NULL + SQL_FILTER_OR );
542 }
543 else
544 {
545 sbWorkgroupFilter.append( SQL_FILTER_WORKGROUP_IS_NULL );
546 }
547 }
548
549 if ( nWorkgroupKeySize > 0 )
550 {
551 for ( int i = 0; i < nWorkgroupKeySize; i++ )
552 {
553 if ( i < 1 )
554 {
555 sbWorkgroupFilter.append( SQL_FILTER_WORKGROUP_LIST );
556 }
557 else
558 {
559 sbWorkgroupFilter.append( SQL_ADITIONAL_PARAMETER );
560 }
561 }
562
563 sbWorkgroupFilter.append( SQL_FILTER_CLOSE_PARENTHESIS );
564 }
565
566 if ( bContaintsAll && ( nWorkgroupKeySize > 0 ) )
567 {
568 sbWorkgroupFilter.append( SQL_FILTER_CLOSE_PARENTHESIS );
569 }
570
571 listStrFilter.add( sbWorkgroupFilter.toString( ) );
572 }
573
574 if ( filter.containsRoleKeyList( ) )
575 {
576 List<String> lRoleKeyList = filter.getRoleKeyList( );
577 int nSize = lRoleKeyList.size( );
578
579 StringBuffer sbRoleKeyFilter = new StringBuffer( );
580
581 if ( filter.includeRoleNull( ) )
582 {
583 if ( nSize > 0 )
584 {
585 sbRoleKeyFilter.append( SQL_FILTER_OPEN_PARENTHESIS + SQL_FILTER_ROLE_IS_NULL + SQL_FILTER_OR );
586 }
587 else
588 {
589 sbRoleKeyFilter.append( SQL_FILTER_ROLE_IS_NULL );
590 }
591 }
592
593 if ( nSize > 0 )
594 {
595 for ( int i = 0; i < nSize; i++ )
596 {
597 if ( i < 1 )
598 {
599 sbRoleKeyFilter.append( SQL_FILTER_ROLE_LIST );
600 }
601 else
602 {
603 sbRoleKeyFilter.append( SQL_ADITIONAL_PARAMETER );
604 }
605 }
606
607 sbRoleKeyFilter.append( SQL_FILTER_CLOSE_PARENTHESIS );
608 }
609
610 if ( filter.includeRoleNull( ) && ( nSize > 0 ) )
611 {
612 sbRoleKeyFilter.append( SQL_FILTER_CLOSE_PARENTHESIS );
613 }
614
615 listStrFilter.add( sbRoleKeyFilter.toString( ) );
616 }
617
618 return listStrFilter;
619 }
620
621
622
623
624
625
626
627
628
629 private DAOUtil buildFilterQueryFooter( DAOUtil daoUtil, RecordFieldFilter filter, int nDefaultIndex )
630 {
631 DAOUtil result = daoUtil;
632 int nIndex = nDefaultIndex;
633
634
635 if ( filter.containsSortEntry( ) )
636 {
637 for ( Object oValue : filter.getSortEntry( ).getSQLParametersValues( ) )
638 {
639
640 if ( oValue instanceof Integer )
641 {
642 result.setInt( nIndex++, (Integer) oValue );
643 }
644 else
645 {
646 result.setString( nIndex++, oValue.toString( ) );
647 }
648 }
649 }
650
651 if ( filter.containsIdDirectory( ) )
652 {
653 result.setInt( nIndex, filter.getIdDirectory( ) );
654 nIndex++;
655 }
656
657 if ( filter.containsIsDisabled( ) )
658 {
659 result.setInt( nIndex, filter.getIsDisabled( ) );
660 nIndex++;
661 }
662
663 if ( filter.containsWorkgroupKeyList( ) )
664 {
665 for ( ReferenceItem rWorkgroupKey : filter.getWorkgroupKeyList( ) )
666 {
667 result.setString( nIndex, rWorkgroupKey.getCode( ) );
668 nIndex++;
669 }
670 }
671
672 if ( filter.containsRoleKeyList( ) )
673 {
674 for ( String strRole : filter.getRoleKeyList( ) )
675 {
676 result.setString( nIndex, strRole );
677 nIndex++;
678 }
679 }
680
681 return result;
682 }
683 }