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.portal.business.user.attribute;
35
36 import fr.paris.lutece.portal.business.file.File;
37 import fr.paris.lutece.portal.business.user.AdminUser;
38 import fr.paris.lutece.portal.service.util.AppLogService;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.sql.Statement;
42 import java.util.ArrayList;
43 import java.util.List;
44 import java.util.Locale;
45
46 import org.apache.commons.collections.CollectionUtils;
47
48
49
50
51
52
53 public class AdminUserFieldDAO implements IAdminUserFieldDAO
54 {
55
56 private static final String CONSTANT_PERCENT = "%";
57 private static final String CONSTANT_OPEN_BRACKET = "(";
58 private static final String CONSTANT_CLOSED_BRACKET = ")";
59
60
61 private static final String SQL_QUERY_SELECT = " SELECT auf.id_user_field, auf.id_user, auf.id_attribute, auf.id_field, auf.id_file, auf.user_field_value, "
62 + " au.access_code, au.last_name, au.first_name, au.email, au.status, au.locale, au.level_user, "
63 + " a.type_class_name, a.title, a.help_message, a.is_mandatory, a.attribute_position, "
64 + " af.title, af.DEFAULT_value, af.is_DEFAULT_value, af.height, af.width, af.max_size_enter, af.is_multiple, af.field_position "
65 + " FROM core_admin_user_field auf " + " INNER JOIN core_admin_user au ON auf.id_user = au.id_user "
66 + " INNER JOIN core_attribute a ON auf.id_attribute = a.id_attribute " + " LEFT JOIN core_attribute_field af ON auf.id_field = af.id_field ";
67 private static final String SQL_QUERY_SELECT_USER_FIELDS_BY_ID_USER_ID_ATTRIBUTE = " SELECT auf.id_user_field, auf.id_user, auf.id_attribute, auf.id_field, auf.id_file, auf.user_field_value, "
68 + " a.type_class_name, a.title, a.help_message, a.is_mandatory, a.attribute_position " + " FROM core_admin_user_field auf "
69 + " INNER JOIN core_attribute a ON a.id_attribute = auf.id_attribute " + " WHERE auf.id_user = ? AND auf.id_attribute = ? ";
70 private static final String SQL_QUERY_SELECT_USERS_BY_FILTER = " SELECT DISTINCT u.id_user, u.access_code, u.last_name, u.first_name, u.email, u.status, u.locale, u.level_user "
71 + " FROM core_admin_user u INNER JOIN core_admin_user_field uf ON u.id_user = uf.id_user ";
72 private static final String SQL_QUERY_SELECT_ID_USER = " SELECT id_user FROM core_admin_user_field WHERE id_attribute = ? AND id_field = ? AND user_field_value LIKE ? ";
73 private static final String SQL_QUERY_EXISTS_WITH_FILE = " SELECT id_user_field from core_admin_user_field where id_file = ? ";
74
75
76 private static final String SQL_QUERY_INSERT = " INSERT INTO core_admin_user_field (id_user, id_attribute, id_field, id_file, user_field_value) "
77 + " VALUES (?,?,?,?,?) ";
78
79
80 private static final String SQL_QUERY_UPDATE = " UPDATE core_admin_user_field SET user_field_value = ? WHERE id_user_field = ? ";
81
82
83 private static final String SQL_QUERY_DELETE = " DELETE FROM core_admin_user_field WHERE id_user_field = ? ";
84 private static final String SQL_QUERY_DELETE_FROM_ID_FIELD = " DELETE FROM core_admin_user_field WHERE id_field = ? ";
85 private static final String SQL_QUERY_DELETE_FROM_ID_USER = " DELETE FROM core_admin_user_field WHERE id_user = ? ";
86 private static final String SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE = " DELETE FROM core_admin_user_field WHERE id_attribute = ? ";
87
88
89 private static final String SQL_ID_ATTRIBUTE_AND_USER_FIELD_VALUE = " WHERE id_attribute = ? AND id_field = ? AND user_field_value LIKE ? ";
90 private static final String SQL_AND_ID_USER_IN = " AND id_user IN ";
91 private static final String SQL_AND_ID_USER_IN_FIRST = " AND uf.id_user IN ";
92 private static final String SQL_WHERE = " WHERE ";
93 private static final String SQL_AND = " AND ";
94 private static final String SQL_FILTER_ID_USER_FIELD = " WHERE auf.id_user_field = ? ";
95 private static final String SQL_FILTER_ID_USER = " auf.id_user = ? ";
96 private static final String SQL_FILTER_ID_ATTRIBUTE = " auf.id_attribute = ? ";
97 private static final String SQL_FILTER_ID_FIELD = " auf.id_field = ? ";
98
99
100
101
102
103
104
105
106 @Override
107 public AdminUserField load( int nIdUserField )
108 {
109 AdminUserField userField = null;
110 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_WHERE + SQL_FILTER_ID_USER_FIELD ) )
111 {
112 daoUtil.setInt( 1, nIdUserField );
113 daoUtil.executeQuery( );
114
115 if ( daoUtil.next( ) )
116 {
117 userField = dataToObject( daoUtil );
118 }
119
120 }
121
122 return userField;
123 }
124
125
126
127
128
129
130
131 @Override
132 public void insert( AdminUserField userField )
133 {
134 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
135 {
136 int nIndex = 1;
137 daoUtil.setInt( nIndex++, userField.getUser( ).getUserId( ) );
138 daoUtil.setInt( nIndex++, userField.getAttribute( ).getIdAttribute( ) );
139 daoUtil.setInt( nIndex++, userField.getAttributeField( ).getIdField( ) );
140
141 if ( userField.getFile( ) != null )
142 {
143 daoUtil.setInt( nIndex++, userField.getFile( ).getIdFile( ) );
144 }
145 else
146 {
147 daoUtil.setIntNull( nIndex++ );
148 }
149
150 daoUtil.setString( nIndex, userField.getValue( ) );
151
152 daoUtil.executeUpdate( );
153
154 if ( daoUtil.nextGeneratedKey( ) )
155 {
156 userField.setIdUserField( daoUtil.getGeneratedKeyInt( 1 ) );
157 }
158 }
159 }
160
161
162
163
164
165
166
167 @Override
168 public void store( AdminUserField userField )
169 {
170 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
171 {
172 daoUtil.setString( 1, userField.getValue( ) );
173 daoUtil.setInt( 2, userField.getIdUserField( ) );
174
175 daoUtil.executeUpdate( );
176 }
177 }
178
179
180
181
182
183
184
185 @Override
186 public void delete( int nIdUserField )
187 {
188 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
189 {
190 daoUtil.setInt( 1, nIdUserField );
191
192 daoUtil.executeUpdate( );
193 }
194 }
195
196
197
198
199
200
201
202 @Override
203 public void deleteUserFieldsFromIdField( int nIdField )
204 {
205 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_FIELD ) )
206 {
207 daoUtil.setInt( 1, nIdField );
208
209 daoUtil.executeUpdate( );
210 }
211 }
212
213
214
215
216
217
218
219 @Override
220 public void deleteUserFieldsFromIdUser( int nIdUser )
221 {
222 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_USER ) )
223 {
224 daoUtil.setInt( 1, nIdUser );
225
226 daoUtil.executeUpdate( );
227 }
228 }
229
230
231
232
233
234
235
236 @Override
237 public void deleteUserFieldsFromIdAttribute( int nIdAttribute )
238 {
239 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE ) )
240 {
241 daoUtil.setInt( 1, nIdAttribute );
242
243 daoUtil.executeUpdate( );
244 }
245 }
246
247
248
249
250
251
252
253
254
255
256 @Override
257 public List<AdminUserField> selectUserFieldsByIdUserIdAttribute( int nIdUser, int nIdAttribute )
258 {
259 List<AdminUserField> listUserFields = new ArrayList<>( );
260 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FIELDS_BY_ID_USER_ID_ATTRIBUTE ) )
261 {
262 daoUtil.setInt( 1, nIdUser );
263 daoUtil.setInt( 2, nIdAttribute );
264 daoUtil.executeQuery( );
265
266 while ( daoUtil.next( ) )
267 {
268 AdminUserFields/user/attribute/AdminUserField.html#AdminUserField">AdminUserField userField = new AdminUserField( );
269 userField.setIdUserField( daoUtil.getInt( 1 ) );
270 userField.setValue( daoUtil.getString( 6 ) );
271
272
273 if ( daoUtil.getObject( 5 ) != null )
274 {
275 File/portal/business/file/File.html#File">File file = new File( );
276 file.setIdFile( daoUtil.getInt( 5 ) );
277 userField.setFile( file );
278 }
279
280
281 AdminUseral/business/user/AdminUser.html#AdminUser">AdminUser user = new AdminUser( );
282 user.setUserId( nIdUser );
283 userField.setUser( user );
284
285
286 IAttribute attribute = null;
287
288 try
289 {
290 attribute = (IAttribute) Class.forName( daoUtil.getString( 7 ) ).newInstance( );
291 }
292 catch( IllegalAccessException | InstantiationException | ClassNotFoundException e )
293 {
294 AppLogService.error( e );
295 }
296
297 if ( attribute != null )
298 {
299 attribute.setIdAttribute( nIdAttribute );
300 attribute.setTitle( daoUtil.getString( 8 ) );
301 attribute.setHelpMessage( daoUtil.getString( 9 ) );
302 attribute.setMandatory( daoUtil.getBoolean( 10 ) );
303 attribute.setPosition( daoUtil.getInt( 11 ) );
304 userField.setAttribute( attribute );
305 }
306
307 AttributeFieldr/attribute/AttributeField.html#AttributeField">AttributeField attributeField = new AttributeField( );
308 attributeField.setIdField( daoUtil.getInt( 4 ) );
309 userField.setAttributeField( attributeField );
310
311 listUserFields.add( userField );
312 }
313
314 }
315
316 return listUserFields;
317 }
318
319
320
321
322
323
324
325
326 @Override
327 public List<AdminUser> selectUsersByFilter( AdminUserFieldFilter auFieldFilter )
328 {
329 List<AdminUserField> listUserFields = auFieldFilter.getListUserFields( );
330
331 if ( CollectionUtils.isEmpty( listUserFields ) )
332 {
333 return null;
334 }
335
336 List<AdminUser> listUsers = new ArrayList<>( );
337 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_USERS_BY_FILTER );
338
339 for ( int i = 1; i <= listUserFields.size( ); i++ )
340 {
341 if ( i == 1 )
342 {
343 sbSQL.append( SQL_ID_ATTRIBUTE_AND_USER_FIELD_VALUE );
344 }
345 else
346 {
347 sbSQL.append( CONSTANT_OPEN_BRACKET + SQL_QUERY_SELECT_ID_USER );
348 }
349
350 if ( ( i != listUserFields.size( ) ) && ( i != 1 ) )
351 {
352 sbSQL.append( SQL_AND_ID_USER_IN );
353 }
354 else
355 if ( ( i != listUserFields.size( ) ) && ( i == 1 ) )
356 {
357 sbSQL.append( SQL_AND_ID_USER_IN_FIRST );
358 }
359 }
360
361 for ( int i = 2; i <= listUserFields.size( ); i++ )
362 {
363 sbSQL.append( CONSTANT_CLOSED_BRACKET );
364 }
365
366 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
367 {
368
369 int nbCount = 1;
370
371 for ( AdminUserField userField : listUserFields )
372 {
373 daoUtil.setInt( nbCount++, userField.getAttribute( ).getIdAttribute( ) );
374 daoUtil.setInt( nbCount++, userField.getAttributeField( ).getIdField( ) );
375 daoUtil.setString( nbCount++, CONSTANT_PERCENT + userField.getValue( ) + CONSTANT_PERCENT );
376 }
377
378 daoUtil.executeQuery( );
379
380 while ( daoUtil.next( ) )
381 {
382 AdminUseral/business/user/AdminUser.html#AdminUser">AdminUser user = new AdminUser( );
383 user.setUserId( daoUtil.getInt( 1 ) );
384 user.setAccessCode( daoUtil.getString( 2 ) );
385 user.setLastName( daoUtil.getString( 3 ) );
386 user.setFirstName( daoUtil.getString( 4 ) );
387 user.setEmail( daoUtil.getString( 5 ) );
388 user.setStatus( daoUtil.getInt( 6 ) );
389
390 Locale locale = new Locale( daoUtil.getString( 7 ) );
391 user.setLocale( locale );
392 listUsers.add( user );
393 }
394
395 }
396
397 return listUsers;
398 }
399
400
401
402
403
404
405
406
407 @Override
408 public List<AdminUserField> selectByFilter( AdminUserFieldFilter auFieldFilter )
409 {
410 List<AdminUserField> listUserFields = new ArrayList<>( );
411 List<String> listFilter = new ArrayList<>( );
412
413 if ( auFieldFilter.containsIdAttribute( ) )
414 {
415 listFilter.add( SQL_FILTER_ID_ATTRIBUTE );
416 }
417
418 if ( auFieldFilter.containsIdUser( ) )
419 {
420 listFilter.add( SQL_FILTER_ID_USER );
421 }
422
423 if ( auFieldFilter.containsIdField( ) )
424 {
425 listFilter.add( SQL_FILTER_ID_FIELD );
426 }
427
428 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );
429
430 if ( CollectionUtils.isNotEmpty( listFilter ) )
431 {
432 boolean bIsFirst = true;
433
434 for ( String filter : listFilter )
435 {
436 if ( bIsFirst )
437 {
438 sbSQL.append( SQL_WHERE );
439 bIsFirst = false;
440 }
441 else
442 {
443 sbSQL.append( SQL_AND );
444 }
445
446 sbSQL.append( filter );
447 }
448 }
449
450 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
451 {
452 int nIndex = 1;
453
454 if ( auFieldFilter.containsIdAttribute( ) )
455 {
456 daoUtil.setInt( nIndex++, auFieldFilter.getIdAttribute( ) );
457 }
458
459 if ( auFieldFilter.containsIdUser( ) )
460 {
461 daoUtil.setInt( nIndex++, auFieldFilter.getIdUser( ) );
462 }
463
464 if ( auFieldFilter.containsIdField( ) )
465 {
466 daoUtil.setInt( nIndex++, auFieldFilter.getIdField( ) );
467 }
468
469 daoUtil.executeQuery( );
470
471 while ( daoUtil.next( ) )
472 {
473 AdminUserField userField = dataToObject( daoUtil );
474 listUserFields.add( userField );
475 }
476
477 }
478
479 return listUserFields;
480 }
481
482
483
484
485 @Override
486 public boolean existsWithFile( int nIdFile )
487 {
488 boolean result;
489 try ( DAOUtilsql/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_EXISTS_WITH_FILE ) )
490 {
491 daoUtil.setInt( 1, nIdFile );
492 daoUtil.executeQuery( );
493 result = daoUtil.next( );
494 }
495
496 return result;
497 }
498
499 private AdminUserField dataToObject( DAOUtil daoUtil )
500 {
501 AdminUserFields/user/attribute/AdminUserField.html#AdminUserField">AdminUserField userField = new AdminUserField( );
502 userField.setIdUserField( daoUtil.getInt( 1 ) );
503 userField.setValue( daoUtil.getString( 6 ) );
504
505
506 AdminUseral/business/user/AdminUser.html#AdminUser">AdminUser user = new AdminUser( );
507 user.setUserId( daoUtil.getInt( 2 ) );
508 user.setAccessCode( daoUtil.getString( 7 ) );
509 user.setLastName( daoUtil.getString( 8 ) );
510 user.setFirstName( daoUtil.getString( 9 ) );
511 user.setEmail( daoUtil.getString( 10 ) );
512 user.setStatus( daoUtil.getInt( 11 ) );
513 user.setLocale( new Locale( daoUtil.getString( 12 ) ) );
514 user.setUserLevel( daoUtil.getInt( 13 ) );
515 userField.setUser( user );
516
517
518 IAttribute attribute = null;
519
520 try
521 {
522 attribute = (IAttribute) Class.forName( daoUtil.getString( 14 ) ).newInstance( );
523 }
524 catch( ClassNotFoundException | InstantiationException | IllegalAccessException e )
525 {
526 AppLogService.error( e );
527 }
528
529 if ( attribute != null )
530 {
531 attribute.setIdAttribute( daoUtil.getInt( 3 ) );
532 attribute.setTitle( daoUtil.getString( 15 ) );
533 attribute.setHelpMessage( daoUtil.getString( 16 ) );
534 attribute.setMandatory( daoUtil.getBoolean( 17 ) );
535 attribute.setPosition( daoUtil.getInt( 18 ) );
536 attribute.setAttributeType( new Locale( daoUtil.getString( 12 ) ) );
537 userField.setAttribute( attribute );
538 }
539
540
541
542
543
544
545
546 AttributeFieldr/attribute/AttributeField.html#AttributeField">AttributeField attributeField = new AttributeField( );
547 attributeField.setIdField( daoUtil.getInt( 4 ) );
548 attributeField.setTitle( daoUtil.getString( 19 ) );
549 attributeField.setValue( daoUtil.getString( 20 ) );
550 attributeField.setDefaultValue( daoUtil.getBoolean( 21 ) );
551 attributeField.setHeight( daoUtil.getInt( 22 ) );
552 attributeField.setWidth( daoUtil.getInt( 23 ) );
553 attributeField.setMaxSizeEnter( daoUtil.getInt( 24 ) );
554 attributeField.setMultiple( daoUtil.getBoolean( 25 ) );
555 attributeField.setPosition( daoUtil.getInt( 26 ) );
556 userField.setAttributeField( attributeField );
557
558
559 if ( daoUtil.getObject( 5 ) != null )
560 {
561 File/portal/business/file/File.html#File">File file = new File( );
562 file.setIdFile( daoUtil.getInt( 5 ) );
563 userField.setFile( file );
564 }
565
566 return userField;
567 }
568 }