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;
35
36 import fr.paris.lutece.portal.business.rbac.RBACRole;
37 import fr.paris.lutece.portal.business.right.Right;
38 import fr.paris.lutece.portal.business.user.authentication.LuteceDefaultAdminUser;
39 import fr.paris.lutece.util.password.IPassword;
40 import fr.paris.lutece.util.password.IPasswordFactory;
41 import fr.paris.lutece.util.sql.DAOUtil;
42
43 import java.sql.Statement;
44 import java.sql.Timestamp;
45 import java.util.ArrayList;
46 import java.util.Collection;
47 import java.util.HashMap;
48 import java.util.List;
49 import java.util.Locale;
50 import java.util.Map;
51
52 import javax.inject.Inject;
53
54 import org.apache.commons.collections.CollectionUtils;
55
56
57
58
59 public class AdminUserDAO implements IAdminUserDAO
60 {
61
62 private static final String CONSTANT_AND_STATUS = " AND status = ?";
63 private static final String CONSTANT_AND_USER_LEVEL = " AND level_user = ?";
64 private static final String CONSTANT_ORDER_BY_LAST_NAME = " ORDER BY last_name ";
65 private static final String CONSTANT_PERCENT = "%";
66 private static final String SQL_QUERY_INSERT = "INSERT INTO core_admin_user ( access_code, last_name , first_name, email, status, locale, level_user, accessibility_mode, password_max_valid_date, account_max_valid_date ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
67 private static final String SQL_QUERY_SELECTALL = "SELECT id_user , access_code, last_name , first_name, email, status, locale, level_user, accessibility_mode, reset_password, password_max_valid_date, account_max_valid_date, last_login, workgroup_key FROM core_admin_user ORDER BY last_name ";
68 private static final String SQL_QUERY_SELECT_USER_FROM_USER_ID = "SELECT id_user , access_code, last_name , first_name, email, status, password, locale, level_user, reset_password, accessibility_mode, password_max_valid_date, account_max_valid_date, workgroup_key FROM core_admin_user WHERE id_user = ? ";
69 private static final String SQL_QUERY_SELECT_USER_FROM_ACCESS_CODE = "SELECT id_user, access_code, last_name, first_name, email, status, locale, level_user, reset_password, accessibility_mode, password_max_valid_date, last_login FROM core_admin_user WHERE access_code = ? ";
70 private static final String SQL_QUERY_SELECT_USER_FROM_EMAIL = "SELECT access_code FROM core_admin_user WHERE email = ? ";
71 private static final String SQL_QUERY_SELECT_RIGHTS_FROM_USER_ID = " SELECT a.id_right , a.name, a.admin_url , a.description , a.plugin_name, a.id_feature_group, a.icon_url, a.level_right, a.documentation_url, a.id_order, a.is_external_feature "
72 + " FROM core_admin_right a , core_user_right b " + " WHERE a.id_right = b.id_right " + " AND b.id_user = ? "
73 + " ORDER BY a.id_order ASC, a.id_right ASC ";
74 private static final String SQL_QUERY_UPDATE = "UPDATE core_admin_user SET access_code = ? , last_name = ? , first_name = ?, email = ?, status = ?, locale = ?, reset_password = ?, accessibility_mode = ?, password_max_valid_date = ?, level_user = ? WHERE id_user = ? ";
75 private static final String SQL_QUERY_DELETE = "DELETE FROM core_admin_user WHERE id_user = ? ";
76 private static final String SQL_QUERY_INSERT_USER_RIGHT = "INSERT INTO core_user_right ( id_right, id_user ) VALUES ( ? , ? ) ";
77 private static final String SQL_QUERY_DELETE_ALL_USER_RIGHTS = "DELETE FROM core_user_right WHERE id_user = ? ";
78 private static final String SQL_QUERY_SELECT_ROLES_FROM_USER_ID = " SELECT a.role_key , a.role_description "
79 + " FROM core_admin_role a , core_user_role b WHERE a.role_key = b.role_key " + " AND b.id_user = ? ORDER BY a.role_key ";
80 private static final String SQL_QUERY_INSERT_USER_ROLE = " INSERT INTO core_user_role ( role_key, id_user ) VALUES ( ? , ? ) ";
81 private static final String SQL_QUERY_DELETE_ALL_USER_ROLES = " DELETE FROM core_user_role WHERE id_user = ? ";
82 private static final String SQL_CHECK_ROLE_ATTRIBUTED = " SELECT id_user FROM core_user_role WHERE role_key = ?";
83 private static final String SQL_CHECK_ACCESS_CODE_IN_USE = " SELECT id_user FROM core_admin_user WHERE access_code = ?";
84 private static final String SQL_CHECK_EMAIL_IN_USE = " SELECT id_user FROM core_admin_user WHERE email = ?";
85 private static final String SQL_QUERY_INSERT_DEFAULT_USER = " INSERT INTO core_admin_user ( access_code, last_name, first_name, email, status, password, locale, level_user, accessibility_mode, reset_password, password_max_valid_date, account_max_valid_date, last_login, workgroup_key ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
86 private static final String SQL_QUERY_UPDATE_DEFAULT_USER = " UPDATE core_admin_user SET access_code = ?, last_name = ?, first_name = ?, email = ?, status = ?, password = ?, locale = ?, reset_password = ?, accessibility_mode = ?, password_max_valid_date = ?, workgroup_key = ?, level_user = ? WHERE id_user = ? ";
87 private static final String SQL_QUERY_UPDATE_DEFAULT_USER_IGNORE_PASSWORD = " UPDATE core_admin_user SET access_code = ?, last_name = ?, first_name = ?, email = ?, status = ?, locale = ?, reset_password = ?, accessibility_mode = ?, password_max_valid_date = ?, workgroup_key = ?, level_user = ? WHERE id_user = ? ";
88 private static final String SQL_QUERY_SELECT_USERS_ID_BY_ROLES = " SELECT a.id_user , a.access_code, a.last_name , a.first_name, a.email, a.status, a.locale, a.accessibility_mode, a.password_max_valid_date "
89 + " FROM core_admin_user a, core_user_role b WHERE a.id_user = b.id_user AND b.role_key = ? ";
90 private static final String SQL_QUERY_SELECT_USER_RIGHTS_OWN = " SELECT DISTINCT b.id_right FROM core_admin_right a , core_user_right b WHERE b.id_user = ? and a.id_right = b.id_right and a.level_right >= ?";
91 private static final String SQL_QUERY_SELECT_USER_RIGHTS_DELEGATED = " SELECT DISTINCT b.id_right FROM core_admin_right a , core_user_right b WHERE b.id_user = ? and a.id_right = b.id_right and a.level_right < ?";
92 private static final String SQL_QUERY_DELETE_USER_RIGHTS = " DELETE FROM core_user_right WHERE id_user = ? and id_right = ?";
93 private static final String SQL_QUERY_SELECT_USERS_BY_LEVEL = " SELECT a.id_user, a.access_code, a.last_name, a.first_name, a.email, a.status, a.locale, a.accessibility_mode "
94 + " FROM core_admin_user a WHERE a.level_user = ? ";
95 private static final String SQL_QUERY_UPDATE_USERS_ROLE = "UPDATE core_user_role SET role_key = ? WHERE role_key = ?";
96 private static final String SQL_QUERY_SELECT_USER_ROLE = " SELECT id_user FROM core_user_role WHERE id_user = ? AND role_key = ? ";
97 private static final String SQL_QUERY_DELETE_ROLE_FOR_USER = " DELETE FROM core_user_role WHERE id_user = ? AND role_key = ? ";
98 private static final String SQL_QUERY_SELECT_USER_FROM_SEARCH = " SELECT id_user, access_code, last_name, first_name, email, status, locale, level_user, accessibility_mode, last_login "
99 + " FROM core_admin_user WHERE access_code LIKE ? AND last_name LIKE ? AND email LIKE ? AND first_name LIKE ? ";
100 private static final String SQL_QUERY_SELECT_USERS_BY_RIGHT = " SELECT u.id_user , u.access_code, u.last_name , u.first_name, u.email, u.status, u.locale, u.level_user, u.accessibility_mode "
101 + " FROM core_admin_user u INNER JOIN core_user_right r ON u.id_user = r.id_user WHERE r.id_right = ? ";
102 private static final String SQL_QUERY_SELECT_USER_RIGHT = " SELECT id_user FROM core_user_right WHERE id_user = ? AND id_right = ? ";
103 private static final String SQL_SELECT_USER_PASSWORD_HISTORY = "SELECT password FROM core_user_password_history WHERE id_user = ? ORDER BY date_password_change desc";
104 private static final String SQL_COUNT_USER_PASSWORD_HISTORY = "SELECT COUNT(*) FROM core_user_password_history WHERE id_user = ? AND date_password_change > ?";
105 private static final String SQL_INSERT_PASSWORD_HISTORY = "INSERT INTO core_user_password_history (id_user, password) VALUES ( ?, ? ) ";
106 private static final String SQL_DELETE_PASSWORD_HISTORY = "DELETE FROM core_user_password_history WHERE id_user = ?";
107 private static final String SQL_SELECT_ANONYMIZATION_STATUS_USER_FILED = "SELECT field_name, anonymize from core_admin_user_anonymize_field";
108 private static final String SQL_UPDATE_ANONYMIZATION_STATUS_USER_FILED = "UPDATE core_admin_user_anonymize_field SET anonymize = ? WHERE field_name = ? ";
109 private static final String SQL_QUERY_SELECT_EXPIRED_USER_ID = "SELECT id_user FROM core_admin_user WHERE status = ?";
110 private static final String SQL_QUERY_SELECT_EXPIRED_LIFE_TIME_USER_ID = "SELECT id_user FROM core_admin_user WHERE account_max_valid_date < ? and status < ? ";
111 private static final String SQL_QUERY_SELECT_USER_ID_FIRST_ALERT = "SELECT id_user FROM core_admin_user WHERE nb_alerts_sent = 0 and status < ? and account_max_valid_date < ? ";
112 private static final String SQL_QUERY_SELECT_USER_ID_OTHER_ALERT = "SELECT id_user FROM core_admin_user "
113 + "WHERE nb_alerts_sent > 0 and nb_alerts_sent <= ? and status < ? and (account_max_valid_date + nb_alerts_sent * ?) < ? ";
114 private static final String SQL_QUERY_SELECT_USER_ID_PASSWORD_EXPIRED = " SELECT id_user FROM core_admin_user WHERE password_max_valid_date < ? AND reset_password = 0 ";
115 private static final String SQL_QUERY_UPDATE_STATUS = " UPDATE core_admin_user SET status = ? WHERE id_user IN ( ";
116 private static final String SQL_QUERY_UPDATE_NB_ALERT = " UPDATE core_admin_user SET nb_alerts_sent = nb_alerts_sent + 1 WHERE id_user IN ( ";
117 private static final String SQL_QUERY_UPDATE_RESET_PASSWORD_LIST_ID = " UPDATE core_admin_user SET reset_password = 1 WHERE id_user IN ( ";
118 private static final String SQL_QUERY_UPDATE_REACTIVATE_ACCOUNT = " UPDATE core_admin_user SET nb_alerts_sent = 0, account_max_valid_date = ? WHERE id_user = ? ";
119 private static final String SQL_QUERY_UPDATE_DATE_LAST_LOGIN = " UPDATE core_admin_user SET last_login = ? WHERE id_user = ? ";
120 private static final String CONSTANT_CLOSE_PARENTHESIS = " ) ";
121 private static final String CONSTANT_COMMA = ", ";
122
123 @Inject
124 private IPasswordFactory _passwordFactory;
125
126
127
128
129 @Override
130 public AdminUser load( int nUserId )
131 {
132 AdminUser user = null;
133 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FROM_USER_ID ) )
134 {
135 daoUtil.setInt( 1, nUserId );
136 daoUtil.executeQuery( );
137
138 if ( daoUtil.next( ) )
139 {
140 user = new AdminUser( );
141 user.setUserId( daoUtil.getInt( 1 ) );
142 user.setAccessCode( daoUtil.getString( 2 ) );
143 user.setLastName( daoUtil.getString( 3 ) );
144 user.setFirstName( daoUtil.getString( 4 ) );
145 user.setEmail( daoUtil.getString( 5 ) );
146 user.setStatus( daoUtil.getInt( 6 ) );
147 user.setLocale( new Locale( daoUtil.getString( 8 ) ) );
148 user.setUserLevel( daoUtil.getInt( 9 ) );
149 user.setPasswordReset( daoUtil.getBoolean( 10 ) );
150 user.setAccessibilityMode( daoUtil.getBoolean( 11 ) );
151 user.setPasswordMaxValidDate( daoUtil.getTimestamp( 12 ) );
152
153 long accountTime = daoUtil.getLong( 13 );
154
155 if ( accountTime > 0 )
156 {
157 user.setAccountMaxValidDate( new Timestamp( accountTime ) );
158 }
159 }
160
161 }
162
163 return user;
164 }
165
166
167
168
169 @Override
170 public AdminUser selectUserByAccessCode( String strUserAccessCode )
171 {
172 AdminUser user = null;
173 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FROM_ACCESS_CODE ) )
174 {
175 daoUtil.setString( 1, strUserAccessCode );
176 daoUtil.executeQuery( );
177
178 if ( daoUtil.next( ) )
179 {
180 user = new AdminUser( );
181 user.setUserId( daoUtil.getInt( 1 ) );
182 user.setAccessCode( daoUtil.getString( 2 ) );
183 user.setLastName( daoUtil.getString( 3 ) );
184 user.setFirstName( daoUtil.getString( 4 ) );
185 user.setEmail( daoUtil.getString( 5 ) );
186 user.setStatus( daoUtil.getInt( 6 ) );
187 user.setLocale( new Locale( daoUtil.getString( 7 ) ) );
188 user.setUserLevel( daoUtil.getInt( 8 ) );
189 user.setPasswordReset( daoUtil.getBoolean( 9 ) );
190 user.setAccessibilityMode( daoUtil.getBoolean( 10 ) );
191 user.setPasswordMaxValidDate( daoUtil.getTimestamp( 11 ) );
192
193 Timestamp dateLastLogin = daoUtil.getTimestamp( 12 );
194
195 if ( ( dateLastLogin != null ) && !dateLastLogin.equals( AdminUser.getDefaultDateLastLogin( ) ) )
196 {
197 user.setDateLastLogin( dateLastLogin );
198 }
199 }
200
201 }
202
203 return user;
204 }
205
206
207
208
209 @Override
210 public <T extends AdminUser> T selectUserByAccessCode(String strUserAccessCode, T user) {
211
212 if(user != null)
213 {
214 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FROM_ACCESS_CODE ) )
215 {
216 daoUtil.setString( 1, strUserAccessCode );
217 daoUtil.executeQuery( );
218
219 if ( daoUtil.next( ) )
220 {
221 user.setUserId( daoUtil.getInt( 1 ) );
222 user.setAccessCode( daoUtil.getString( 2 ) );
223 user.setLastName( daoUtil.getString( 3 ) );
224 user.setFirstName( daoUtil.getString( 4 ) );
225 user.setEmail( daoUtil.getString( 5 ) );
226 user.setStatus( daoUtil.getInt( 6 ) );
227 user.setLocale( new Locale( daoUtil.getString( 7 ) ) );
228 user.setUserLevel( daoUtil.getInt( 8 ) );
229 user.setPasswordReset( daoUtil.getBoolean( 9 ) );
230 user.setAccessibilityMode( daoUtil.getBoolean( 10 ) );
231 user.setPasswordMaxValidDate( daoUtil.getTimestamp( 11 ) );
232
233 Timestamp dateLastLogin = daoUtil.getTimestamp( 12 );
234
235 if ( ( dateLastLogin != null ) && !dateLastLogin.equals( AdminUser.getDefaultDateLastLogin( ) ) )
236 {
237 user.setDateLastLogin( dateLastLogin );
238 }
239 }
240 else
241 {
242
243 user= null;
244 }
245
246
247 }
248 }
249
250 return user;
251 }
252
253
254
255
256 @Override
257 public String selectUserByEmail( String strEmail )
258 {
259 String strAccessCode = null;
260 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FROM_EMAIL ) )
261 {
262 daoUtil.setString( 1, strEmail );
263 daoUtil.executeQuery( );
264
265 if ( daoUtil.next( ) )
266 {
267 strAccessCode = daoUtil.getString( 1 );
268 }
269
270 }
271
272 return strAccessCode;
273 }
274
275
276
277
278 @Override
279 public Collection<AdminUser> selectUserList( )
280 {
281 Collection<AdminUser> userList = new ArrayList<>( );
282 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL ) )
283 {
284 daoUtil.executeQuery( );
285
286 while ( daoUtil.next( ) )
287 {
288 AdminUserbusiness/user/AdminUser.html#AdminUser">AdminUser user = new AdminUser( );
289 user.setUserId( daoUtil.getInt( 1 ) );
290 user.setAccessCode( daoUtil.getString( 2 ) );
291 user.setLastName( daoUtil.getString( 3 ) );
292 user.setFirstName( daoUtil.getString( 4 ) );
293 user.setEmail( daoUtil.getString( 5 ) );
294 user.setStatus( daoUtil.getInt( 6 ) );
295 user.setLocale( new Locale( daoUtil.getString( 7 ) ) );
296 user.setUserLevel( daoUtil.getInt( 8 ) );
297 user.setAccessibilityMode( daoUtil.getBoolean( 9 ) );
298 user.setPasswordReset( daoUtil.getBoolean( 10 ) );
299 user.setPasswordMaxValidDate( daoUtil.getTimestamp( 11 ) );
300
301 long accountTime = daoUtil.getLong( 12 );
302
303 if ( accountTime > 0 )
304 {
305 user.setAccountMaxValidDate( new Timestamp( accountTime ) );
306 }
307
308 Timestamp dateLastLogin = daoUtil.getTimestamp( 13 );
309
310 if ( ( dateLastLogin != null ) && !dateLastLogin.equals( AdminUser.getDefaultDateLastLogin( ) ) )
311 {
312 user.setDateLastLogin( dateLastLogin );
313 }
314
315 user.setWorkgroupKey( daoUtil.getString( 14 ) );
316 userList.add( user );
317 }
318
319 }
320
321 return userList;
322 }
323
324
325
326
327 @Override
328 public void insert( AdminUser user )
329 {
330 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
331 {
332 int nIndex = 1;
333 daoUtil.setString( nIndex++, user.getAccessCode( ) );
334 daoUtil.setString( nIndex++, user.getLastName( ) );
335 daoUtil.setString( nIndex++, user.getFirstName( ) );
336 daoUtil.setString( nIndex++, user.getEmail( ) );
337 daoUtil.setInt( nIndex++, user.getStatus( ) );
338 daoUtil.setString( nIndex++, user.getLocale( ).toString( ) );
339 daoUtil.setInt( nIndex++, user.getUserLevel( ) );
340 daoUtil.setBoolean( nIndex++, user.getAccessibilityMode( ) );
341 daoUtil.setTimestamp( nIndex++, user.getPasswordMaxValidDate( ) );
342
343 if ( user.getAccountMaxValidDate( ) == null )
344 {
345 daoUtil.setLongNull( nIndex );
346 }
347 else
348 {
349 daoUtil.setLong( nIndex, user.getAccountMaxValidDate( ).getTime( ) );
350 }
351
352 daoUtil.executeUpdate( );
353
354 if ( daoUtil.nextGeneratedKey( ) )
355 {
356 user.setUserId( daoUtil.getGeneratedKeyInt( 1 ) );
357 }
358 }
359 }
360
361
362
363
364 @Override
365 public void store( AdminUser user )
366 {
367 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
368 {
369
370 daoUtil.setString( 1, user.getAccessCode( ) );
371 daoUtil.setString( 2, user.getLastName( ) );
372 daoUtil.setString( 3, user.getFirstName( ) );
373 daoUtil.setString( 4, user.getEmail( ) );
374 daoUtil.setInt( 5, user.getStatus( ) );
375 daoUtil.setString( 6, user.getLocale( ).toString( ) );
376 daoUtil.setBoolean( 7, user.isPasswordReset( ) );
377 daoUtil.setBoolean( 8, user.getAccessibilityMode( ) );
378 daoUtil.setTimestamp( 9, user.getPasswordMaxValidDate( ) );
379 daoUtil.setInt( 10, user.getUserLevel( ) );
380
381 daoUtil.setInt( 11, user.getUserId( ) );
382
383 daoUtil.executeUpdate( );
384 }
385 }
386
387
388
389
390 @Override
391 public void delete( int nUserId )
392 {
393 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
394 {
395 daoUtil.setInt( 1, nUserId );
396 daoUtil.executeUpdate( );
397 }
398 }
399
400
401
402
403 @Override
404 public Map<String, Right> selectRightsListForUser( int nUserId )
405 {
406 Map<String, Right> rightsMap = new HashMap<>( );
407
408 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RIGHTS_FROM_USER_ID ) )
409 {
410 daoUtil.setInt( 1, nUserId );
411 daoUtil.executeQuery( );
412
413 while ( daoUtil.next( ) )
414 {
415 Rightal/business/right/Right.html#Right">Right right = new Right( );
416 right.setId( daoUtil.getString( 1 ) );
417 right.setNameKey( daoUtil.getString( 2 ) );
418 right.setUrl( daoUtil.getString( 3 ) );
419 right.setDescriptionKey( daoUtil.getString( 4 ) );
420 right.setPluginName( daoUtil.getString( 5 ) );
421 right.setFeatureGroup( daoUtil.getString( 6 ) );
422 right.setIconUrl( daoUtil.getString( 7 ) );
423 right.setLevel( daoUtil.getInt( 8 ) );
424 right.setDocumentationUrl( daoUtil.getString( 9 ) );
425 right.setOrder( daoUtil.getInt( 10 ) );
426 right.setExternalFeature( daoUtil.getBoolean( 11 ) );
427 rightsMap.put( right.getId( ), right );
428 }
429
430 }
431
432 return rightsMap;
433 }
434
435
436
437
438 @Override
439 public void insertRightsListForUser( int nUserId, String strRightId )
440 {
441 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_USER_RIGHT ) )
442 {
443 daoUtil.setString( 1, strRightId );
444 daoUtil.setInt( 2, nUserId );
445 daoUtil.executeUpdate( );
446 }
447 }
448
449
450
451
452 @Override
453 public void deleteAllRightsForUser( int nUserId )
454 {
455 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL_USER_RIGHTS ) )
456 {
457 daoUtil.setInt( 1, nUserId );
458 daoUtil.executeUpdate( );
459 }
460 }
461
462
463
464
465 @Override
466 public Map<String, RBACRole> selectRolesListForUser( int nUserId )
467 {
468 Map<String, RBACRole> rolesMap = new HashMap<>( );
469
470 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ROLES_FROM_USER_ID ) )
471 {
472 daoUtil.setInt( 1, nUserId );
473 daoUtil.executeQuery( );
474
475 while ( daoUtil.next( ) )
476 {
477 RBACRole/business/rbac/RBACRole.html#RBACRole">RBACRole role = new RBACRole( );
478 role.setKey( daoUtil.getString( 1 ) );
479 role.setDescription( daoUtil.getString( 2 ) );
480
481 rolesMap.put( role.getKey( ), role );
482 }
483
484 }
485
486 return rolesMap;
487 }
488
489
490
491
492 @Override
493 public void insertRolesListForUser( int nUserId, String strRoleKey )
494 {
495 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_USER_ROLE ) )
496 {
497 daoUtil.setString( 1, strRoleKey );
498 daoUtil.setInt( 2, nUserId );
499 daoUtil.executeUpdate( );
500 }
501 }
502
503
504
505
506 @Override
507 public void deleteAllRolesForUser( int nUserId )
508 {
509 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL_USER_ROLES ) )
510 {
511 daoUtil.setInt( 1, nUserId );
512 daoUtil.executeUpdate( );
513 }
514 }
515
516
517
518
519 @Override
520 public boolean checkRoleAttributed( String strRoleKey )
521 {
522 boolean bInUse = false;
523
524 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_CHECK_ROLE_ATTRIBUTED ) )
525 {
526 daoUtil.setString( 1, strRoleKey );
527 daoUtil.executeQuery( );
528
529 if ( daoUtil.next( ) )
530 {
531 bInUse = true;
532 }
533
534 }
535
536 return bInUse;
537 }
538
539
540
541
542 @Override
543 public int checkAccessCodeAlreadyInUse( String strAccessCode )
544 {
545 int nIdUser = -1;
546 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_CHECK_ACCESS_CODE_IN_USE ) )
547 {
548 daoUtil.setString( 1, strAccessCode );
549 daoUtil.executeQuery( );
550
551 if ( daoUtil.next( ) )
552 {
553 nIdUser = daoUtil.getInt( 1 );
554 }
555
556 }
557
558 return nIdUser;
559 }
560
561
562
563
564 @Override
565 public int checkEmailAlreadyInUse( String strEmail )
566 {
567 int nIdUser = -1;
568 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_CHECK_EMAIL_IN_USE ) )
569 {
570 daoUtil.setString( 1, strEmail );
571 daoUtil.executeQuery( );
572
573 if ( daoUtil.next( ) )
574 {
575 nIdUser = daoUtil.getInt( 1 );
576 }
577
578 }
579
580 return nIdUser;
581 }
582
583
584
585
586
587
588 @Override
589 public void insert( LuteceDefaultAdminUser user )
590 {
591 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_DEFAULT_USER, Statement.RETURN_GENERATED_KEYS ) )
592 {
593 int nIndex = 1;
594 daoUtil.setString( nIndex++, user.getAccessCode( ) );
595 daoUtil.setString( nIndex++, user.getLastName( ) );
596 daoUtil.setString( nIndex++, user.getFirstName( ) );
597 daoUtil.setString( nIndex++, user.getEmail( ) );
598 daoUtil.setInt( nIndex++, user.getStatus( ) );
599 daoUtil.setString( nIndex++, user.getPassword( ).getStorableRepresentation( ) );
600 daoUtil.setString( nIndex++, user.getLocale( ).toString( ) );
601 daoUtil.setInt( nIndex++, user.getUserLevel( ) );
602 daoUtil.setBoolean( nIndex++, user.getAccessibilityMode( ) );
603 daoUtil.setBoolean( nIndex++, user.isPasswordReset( ) );
604 daoUtil.setTimestamp( nIndex++, user.getPasswordMaxValidDate( ) );
605
606 if ( user.getAccountMaxValidDate( ) == null )
607 {
608 daoUtil.setLongNull( nIndex++ );
609 }
610 else
611 {
612 daoUtil.setLong( nIndex++, user.getAccountMaxValidDate( ).getTime( ) );
613 }
614
615 daoUtil.setTimestamp( nIndex++, user.getDateLastLogin( ) );
616 daoUtil.setString( nIndex, user.getWorkgroupKey( ) );
617 daoUtil.executeUpdate( );
618
619 if ( daoUtil.nextGeneratedKey( ) )
620 {
621 user.setUserId( daoUtil.getGeneratedKeyInt( 1 ) );
622 }
623 }
624 }
625
626
627
628
629 @Override
630 public void store( LuteceDefaultAdminUser user, PasswordUpdateMode passwordMode )
631 {
632 String query = SQL_QUERY_UPDATE_DEFAULT_USER_IGNORE_PASSWORD;
633 if ( PasswordUpdateMode.UPDATE.equals( passwordMode ) )
634 {
635 query = SQL_QUERY_UPDATE_DEFAULT_USER;
636 }
637 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( query ) )
638 {
639
640 int nArgIndex = 1;
641 daoUtil.setString( nArgIndex++, user.getAccessCode( ) );
642 daoUtil.setString( nArgIndex++, user.getLastName( ) );
643 daoUtil.setString( nArgIndex++, user.getFirstName( ) );
644 daoUtil.setString( nArgIndex++, user.getEmail( ) );
645 daoUtil.setInt( nArgIndex++, user.getStatus( ) );
646 if ( PasswordUpdateMode.UPDATE.equals( passwordMode ) )
647 {
648 daoUtil.setString( nArgIndex++, user.getPassword( ).getStorableRepresentation( ) );
649 }
650 daoUtil.setString( nArgIndex++, user.getLocale( ).toString( ) );
651 daoUtil.setBoolean( nArgIndex++, user.isPasswordReset( ) );
652 daoUtil.setBoolean( nArgIndex++, user.getAccessibilityMode( ) );
653 daoUtil.setTimestamp( nArgIndex++, user.getPasswordMaxValidDate( ) );
654 daoUtil.setString( nArgIndex++, user.getWorkgroupKey( ) );
655 daoUtil.setInt( nArgIndex++, user.getUserLevel( ) );
656
657 daoUtil.setInt( nArgIndex++, user.getUserId( ) );
658
659 daoUtil.executeUpdate( );
660 }
661 }
662
663
664
665
666 @Override
667 public LuteceDefaultAdminUser loadDefaultAdminUser( int nUserId )
668 {
669 LuteceDefaultAdminUser/authentication/LuteceDefaultAdminUser.html#LuteceDefaultAdminUser">LuteceDefaultAdminUser user = new LuteceDefaultAdminUser( );
670 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FROM_USER_ID ) )
671 {
672 daoUtil.setInt( 1, nUserId );
673 daoUtil.executeQuery( );
674
675 if ( daoUtil.next( ) )
676 {
677 user.setUserId( daoUtil.getInt( 1 ) );
678 user.setAccessCode( daoUtil.getString( 2 ) );
679 user.setLastName( daoUtil.getString( 3 ) );
680 user.setFirstName( daoUtil.getString( 4 ) );
681 user.setEmail( daoUtil.getString( 5 ) );
682 user.setStatus( daoUtil.getInt( 6 ) );
683 user.setPassword( _passwordFactory.getPassword( daoUtil.getString( 7 ) ) );
684
685 Locale locale = new Locale( daoUtil.getString( 8 ) );
686 user.setLocale( locale );
687 user.setUserLevel( daoUtil.getInt( 9 ) );
688 user.setPasswordReset( daoUtil.getBoolean( 10 ) );
689 user.setAccessibilityMode( daoUtil.getBoolean( 11 ) );
690 user.setWorkgroupKey( daoUtil.getString( 14 ) );
691 }
692 }
693
694 return user;
695 }
696
697
698
699
700 @Override
701 public Collection<AdminUser> selectUsersByRole( String strRoleKey )
702 {
703 Collection<AdminUser> userList = new ArrayList<>( );
704 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USERS_ID_BY_ROLES ) )
705 {
706 daoUtil.setString( 1, strRoleKey );
707 daoUtil.executeQuery( );
708
709 while ( daoUtil.next( ) )
710 {
711 AdminUserbusiness/user/AdminUser.html#AdminUser">AdminUser user = new AdminUser( );
712 user.setUserId( daoUtil.getInt( 1 ) );
713 user.setAccessCode( daoUtil.getString( 2 ) );
714 user.setLastName( daoUtil.getString( 3 ) );
715 user.setFirstName( daoUtil.getString( 4 ) );
716 user.setEmail( daoUtil.getString( 5 ) );
717 user.setStatus( daoUtil.getInt( 6 ) );
718 user.setLocale( new Locale( daoUtil.getString( 7 ) ) );
719 user.setAccessibilityMode( daoUtil.getBoolean( 8 ) );
720 user.setPasswordMaxValidDate( daoUtil.getTimestamp( 9 ) );
721 userList.add( user );
722 }
723
724 }
725
726 return userList;
727 }
728
729
730
731
732 @Override
733 public Collection<AdminUser> selectUsersByLevel( int nIdLevel )
734 {
735 Collection<AdminUser> userList = new ArrayList<>( );
736 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USERS_BY_LEVEL ) )
737 {
738 daoUtil.setInt( 1, nIdLevel );
739 daoUtil.executeQuery( );
740
741 while ( daoUtil.next( ) )
742 {
743 AdminUserbusiness/user/AdminUser.html#AdminUser">AdminUser user = new AdminUser( );
744 user.setUserId( daoUtil.getInt( 1 ) );
745 user.setAccessCode( daoUtil.getString( 2 ) );
746 user.setLastName( daoUtil.getString( 3 ) );
747 user.setFirstName( daoUtil.getString( 4 ) );
748 user.setEmail( daoUtil.getString( 5 ) );
749 user.setStatus( daoUtil.getInt( 6 ) );
750 user.setLocale( new Locale( daoUtil.getString( 7 ) ) );
751 user.setAccessibilityMode( daoUtil.getBoolean( 8 ) );
752 userList.add( user );
753 }
754
755 }
756
757 return userList;
758 }
759
760
761
762
763
764
765
766
767
768
769
770
771 private Collection<String> selectIdRights( int nUserId, int nUserLevel, boolean bDelegated )
772 {
773 String strSqlQuery = bDelegated ? SQL_QUERY_SELECT_USER_RIGHTS_DELEGATED : SQL_QUERY_SELECT_USER_RIGHTS_OWN;
774 Collection<String> idRightList = new ArrayList<>( );
775 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( strSqlQuery ) )
776 {
777 daoUtil.setInt( 1, nUserId );
778 daoUtil.setInt( 2, nUserLevel );
779 daoUtil.executeQuery( );
780
781 while ( daoUtil.next( ) )
782 {
783 idRightList.add( daoUtil.getString( 1 ) );
784 }
785
786 }
787
788 return idRightList;
789 }
790
791
792
793
794
795
796
797
798
799 private void deleteRightsForUser( int nUserId, Collection<String> idRightList )
800 {
801 for ( String strIdRight : idRightList )
802 {
803 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_USER_RIGHTS ) )
804 {
805 daoUtil.setInt( 1, nUserId );
806 daoUtil.setString( 2, strIdRight );
807 daoUtil.executeUpdate( );
808 }
809 }
810 }
811
812
813
814
815 @Override
816 public void deleteAllOwnRightsForUser( int nUserId, int nUserLevel )
817 {
818 Collection<String> idRightList = selectIdRights( nUserId, nUserLevel, false );
819
820 deleteRightsForUser( nUserId, idRightList );
821 }
822
823
824
825
826 @Override
827 public void deleteAllDelegatedRightsForUser( int nUserId, int nUserLevel )
828 {
829 Collection<String> idRightList = selectIdRights( nUserId, nUserLevel, true );
830
831 deleteRightsForUser( nUserId, idRightList );
832 }
833
834
835
836
837 @Override
838 public void storeUsersRole( String strOldRoleKey, RBACRole role )
839 {
840 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_USERS_ROLE ) )
841 {
842 daoUtil.setString( 1, role.getKey( ) );
843 daoUtil.setString( 2, strOldRoleKey );
844
845 daoUtil.executeUpdate( );
846 }
847 }
848
849
850
851
852 @Override
853 public boolean hasRole( int nUserId, String strRoleKey )
854 {
855 boolean bHasRole = false;
856 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_ROLE ) )
857 {
858 daoUtil.setInt( 1, nUserId );
859 daoUtil.setString( 2, strRoleKey );
860 daoUtil.executeQuery( );
861
862 if ( daoUtil.next( ) )
863 {
864 bHasRole = true;
865 }
866
867 }
868
869 return bHasRole;
870 }
871
872
873
874
875 @Override
876 public void deleteRoleForUser( int nUserId, String strRoleKey )
877 {
878 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ROLE_FOR_USER ) )
879 {
880 daoUtil.setInt( 1, nUserId );
881 daoUtil.setString( 2, strRoleKey );
882 daoUtil.executeUpdate( );
883 }
884 }
885
886
887
888
889 @Override
890 public Collection<AdminUser> selectUsersByFilter( AdminUserFilter auFilter )
891 {
892 Collection<AdminUser> userList = new ArrayList<>( );
893
894 String query = SQL_QUERY_SELECT_USER_FROM_SEARCH;
895
896 if ( auFilter.getStatus( ) != -1 )
897 {
898 query += CONSTANT_AND_STATUS;
899 }
900
901 if ( auFilter.getUserLevel( ) != -1 )
902 {
903 query += CONSTANT_AND_USER_LEVEL;
904 }
905
906 query += CONSTANT_ORDER_BY_LAST_NAME;
907
908 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( query ) )
909 {
910 int nIndex = 0;
911 daoUtil.setString( ++nIndex, CONSTANT_PERCENT + auFilter.getAccessCode( ) + CONSTANT_PERCENT );
912 daoUtil.setString( ++nIndex, CONSTANT_PERCENT + auFilter.getLastName( ) + CONSTANT_PERCENT );
913 daoUtil.setString( ++nIndex, CONSTANT_PERCENT + auFilter.getEmail( ) + CONSTANT_PERCENT );
914 daoUtil.setString( ++nIndex, CONSTANT_PERCENT + auFilter.getFirstName( ) + CONSTANT_PERCENT );
915
916 if ( auFilter.getStatus( ) != -1 )
917 {
918 daoUtil.setInt( ++nIndex, auFilter.getStatus( ) );
919 }
920
921 if ( auFilter.getUserLevel( ) != -1 )
922 {
923 daoUtil.setInt( ++nIndex, auFilter.getUserLevel( ) );
924 }
925
926 daoUtil.executeQuery( );
927
928 while ( daoUtil.next( ) )
929 {
930 AdminUserbusiness/user/AdminUser.html#AdminUser">AdminUser user = new AdminUser( );
931 user.setUserId( daoUtil.getInt( 1 ) );
932 user.setAccessCode( daoUtil.getString( 2 ) );
933 user.setLastName( daoUtil.getString( 3 ) );
934 user.setFirstName( daoUtil.getString( 4 ) );
935 user.setEmail( daoUtil.getString( 5 ) );
936 user.setStatus( daoUtil.getInt( 6 ) );
937 user.setLocale( new Locale( daoUtil.getString( 7 ) ) );
938 user.setUserLevel( daoUtil.getInt( 8 ) );
939 user.setAccessibilityMode( daoUtil.getBoolean( 9 ) );
940
941 Timestamp dateLastLogin = daoUtil.getTimestamp( 10 );
942 if ( ( dateLastLogin != null ) && !dateLastLogin.equals( AdminUser.getDefaultDateLastLogin( ) ) )
943 {
944 user.setDateLastLogin( dateLastLogin );
945 }
946
947 userList.add( user );
948 }
949
950 }
951
952 return userList;
953 }
954
955
956
957
958 @Override
959 public Collection<AdminUser> selectUsersByRight( String strIdRight )
960 {
961 Collection<AdminUser> userList = new ArrayList<>( );
962 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USERS_BY_RIGHT ) )
963 {
964 daoUtil.setString( 1, strIdRight );
965 daoUtil.executeQuery( );
966
967 while ( daoUtil.next( ) )
968 {
969 AdminUserbusiness/user/AdminUser.html#AdminUser">AdminUser user = new AdminUser( );
970 user.setUserId( daoUtil.getInt( 1 ) );
971 user.setAccessCode( daoUtil.getString( 2 ) );
972 user.setLastName( daoUtil.getString( 3 ) );
973 user.setFirstName( daoUtil.getString( 4 ) );
974 user.setEmail( daoUtil.getString( 5 ) );
975 user.setStatus( daoUtil.getInt( 6 ) );
976 user.setLocale( new Locale( daoUtil.getString( 7 ) ) );
977 user.setUserLevel( daoUtil.getInt( 8 ) );
978 user.setAccessibilityMode( daoUtil.getBoolean( 9 ) );
979 userList.add( user );
980 }
981
982 }
983
984 return userList;
985 }
986
987
988
989
990 @Override
991 public boolean hasRight( int nUserId, String strIdRight )
992 {
993 boolean bHasRight = false;
994 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_RIGHT ) )
995 {
996 daoUtil.setInt( 1, nUserId );
997 daoUtil.setString( 2, strIdRight );
998 daoUtil.executeQuery( );
999
1000 if ( daoUtil.next( ) )
1001 {
1002 bHasRight = true;
1003 }
1004
1005 }
1006
1007 return bHasRight;
1008 }
1009
1010
1011
1012
1013 @Override
1014 public void deleteRightForUser( int nUserId, String strIdRight )
1015 {
1016 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_USER_RIGHTS ) )
1017 {
1018 daoUtil.setInt( 1, nUserId );
1019 daoUtil.setString( 2, strIdRight );
1020 daoUtil.executeUpdate( );
1021 }
1022 }
1023
1024
1025
1026
1027 @Override
1028 public List<IPassword> selectUserPasswordHistory( int nUserID )
1029 {
1030 List<IPassword> listPasswordHistory = new ArrayList<>( );
1031
1032 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_SELECT_USER_PASSWORD_HISTORY ) )
1033 {
1034 daoUtil.setInt( 1, nUserID );
1035 daoUtil.executeQuery( );
1036
1037 while ( daoUtil.next( ) )
1038 {
1039 listPasswordHistory.add( _passwordFactory.getPassword( daoUtil.getString( 1 ) ) );
1040 }
1041
1042 }
1043
1044 return listPasswordHistory;
1045 }
1046
1047
1048
1049
1050 @Override
1051 public int countUserPasswordHistoryFromDate( Timestamp minDate, int nUserId )
1052 {
1053 int nNbRes = 0;
1054
1055 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_COUNT_USER_PASSWORD_HISTORY ) )
1056 {
1057 daoUtil.setInt( 1, nUserId );
1058 daoUtil.setTimestamp( 2, minDate );
1059 daoUtil.executeQuery( );
1060
1061 if ( daoUtil.next( ) )
1062 {
1063 nNbRes = daoUtil.getInt( 1 );
1064 }
1065
1066 }
1067
1068 return nNbRes;
1069 }
1070
1071
1072
1073
1074 @Override
1075 public void insertNewPasswordInHistory( IPassword password, int nUserId )
1076 {
1077 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_INSERT_PASSWORD_HISTORY ) )
1078 {
1079 daoUtil.setInt( 1, nUserId );
1080 daoUtil.setString( 2, password.getStorableRepresentation( ) );
1081
1082 daoUtil.executeUpdate( );
1083 }
1084 }
1085
1086
1087
1088
1089 @Override
1090 public void removeAllPasswordHistoryForUser( int nUserId )
1091 {
1092 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_DELETE_PASSWORD_HISTORY ) )
1093 {
1094 daoUtil.setInt( 1, nUserId );
1095 daoUtil.executeUpdate( );
1096 }
1097 }
1098
1099
1100
1101
1102 @Override
1103 public Map<String, Boolean> selectAnonymizationStatusUserStaticField( )
1104 {
1105 Map<String, Boolean> resultMap = new HashMap<>( );
1106 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_SELECT_ANONYMIZATION_STATUS_USER_FILED ) )
1107 {
1108 daoUtil.executeQuery( );
1109
1110 while ( daoUtil.next( ) )
1111 {
1112 resultMap.put( daoUtil.getString( 1 ), daoUtil.getBoolean( 2 ) );
1113 }
1114
1115 }
1116
1117 return resultMap;
1118 }
1119
1120
1121
1122
1123 @Override
1124 public void updateAnonymizationStatusUserStaticField( String strFieldName, boolean bAnonymizeFiled )
1125 {
1126 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_UPDATE_ANONYMIZATION_STATUS_USER_FILED ) )
1127 {
1128 daoUtil.setBoolean( 1, bAnonymizeFiled );
1129 daoUtil.setString( 2, strFieldName );
1130 daoUtil.executeUpdate( );
1131 }
1132 }
1133
1134
1135
1136
1137 @Override
1138 public List<Integer> findAllExpiredUserId( )
1139 {
1140 List<Integer> listIdExpiredUser = new ArrayList<>( );
1141 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_EXPIRED_USER_ID ) )
1142 {
1143 daoUtil.setInt( 1, AdminUser.EXPIRED_CODE );
1144
1145 daoUtil.executeQuery( );
1146
1147 while ( daoUtil.next( ) )
1148 {
1149 listIdExpiredUser.add( daoUtil.getInt( 1 ) );
1150 }
1151
1152 }
1153
1154 return listIdExpiredUser;
1155 }
1156
1157
1158
1159
1160 @Override
1161 public List<Integer> getIdUsersWithExpiredLifeTimeList( Timestamp currentTimestamp )
1162 {
1163 List<Integer> listIdExpiredUser = new ArrayList<>( );
1164 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_EXPIRED_LIFE_TIME_USER_ID ) )
1165 {
1166 daoUtil.setLong( 1, currentTimestamp.getTime( ) );
1167 daoUtil.setInt( 2, AdminUser.EXPIRED_CODE );
1168
1169 daoUtil.executeQuery( );
1170
1171 while ( daoUtil.next( ) )
1172 {
1173 listIdExpiredUser.add( daoUtil.getInt( 1 ) );
1174 }
1175
1176 }
1177
1178 return listIdExpiredUser;
1179 }
1180
1181
1182
1183
1184 @Override
1185 public List<Integer> getIdUsersToSendFirstAlert( Timestamp alertMaxDate )
1186 {
1187 List<Integer> listIdUserFirstAlert = new ArrayList<>( );
1188 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_ID_FIRST_ALERT ) )
1189 {
1190 daoUtil.setInt( 1, AdminUser.EXPIRED_CODE );
1191 daoUtil.setLong( 2, alertMaxDate.getTime( ) );
1192
1193 daoUtil.executeQuery( );
1194
1195 while ( daoUtil.next( ) )
1196 {
1197 listIdUserFirstAlert.add( daoUtil.getInt( 1 ) );
1198 }
1199
1200 }
1201
1202 return listIdUserFirstAlert;
1203 }
1204
1205
1206
1207
1208 @Override
1209 public List<Integer> getIdUsersToSendOtherAlert( Timestamp alertMaxDate, Timestamp timeBetweenAlerts, int maxNumberAlerts )
1210 {
1211 List<Integer> listIdUserFirstAlert = new ArrayList<>( );
1212 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_ID_OTHER_ALERT ) )
1213 {
1214 daoUtil.setInt( 1, maxNumberAlerts );
1215 daoUtil.setInt( 2, AdminUser.EXPIRED_CODE );
1216 daoUtil.setLong( 3, timeBetweenAlerts.getTime( ) );
1217 daoUtil.setLong( 4, alertMaxDate.getTime( ) );
1218
1219 daoUtil.executeQuery( );
1220
1221 while ( daoUtil.next( ) )
1222 {
1223 listIdUserFirstAlert.add( daoUtil.getInt( 1 ) );
1224 }
1225
1226 }
1227
1228 return listIdUserFirstAlert;
1229 }
1230
1231
1232
1233
1234 @Override
1235 public List<Integer> getIdUsersWithExpiredPasswordsList( Timestamp currentTimestamp )
1236 {
1237 List<Integer> idUserPasswordExpiredlist = new ArrayList<>( );
1238 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_ID_PASSWORD_EXPIRED ) )
1239 {
1240 daoUtil.setTimestamp( 1, currentTimestamp );
1241
1242 daoUtil.executeQuery( );
1243
1244 while ( daoUtil.next( ) )
1245 {
1246 idUserPasswordExpiredlist.add( daoUtil.getInt( 1 ) );
1247 }
1248
1249 }
1250
1251 return idUserPasswordExpiredlist;
1252 }
1253
1254
1255
1256
1257 @Override
1258 public void updateUserStatus( List<Integer> listIdUser, int nNewStatus )
1259 {
1260 if ( CollectionUtils.isNotEmpty( listIdUser ) )
1261 {
1262 StringBuilder sbSQL = new StringBuilder( );
1263 sbSQL.append( SQL_QUERY_UPDATE_STATUS );
1264
1265 for ( int i = 0; i < listIdUser.size( ); i++ )
1266 {
1267 if ( i > 0 )
1268 {
1269 sbSQL.append( CONSTANT_COMMA );
1270 }
1271
1272 sbSQL.append( listIdUser.get( i ) );
1273 }
1274
1275 sbSQL.append( CONSTANT_CLOSE_PARENTHESIS );
1276
1277 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
1278 {
1279 daoUtil.setInt( 1, nNewStatus );
1280 daoUtil.executeUpdate( );
1281 }
1282 }
1283 }
1284
1285
1286
1287
1288 @Override
1289 public void updateNbAlert( List<Integer> listIdUser )
1290 {
1291 if ( CollectionUtils.isNotEmpty( listIdUser ) )
1292 {
1293 StringBuilder sbSQL = new StringBuilder( );
1294 sbSQL.append( SQL_QUERY_UPDATE_NB_ALERT );
1295
1296 for ( int i = 0; i < listIdUser.size( ); i++ )
1297 {
1298 if ( i > 0 )
1299 {
1300 sbSQL.append( CONSTANT_COMMA );
1301 }
1302
1303 sbSQL.append( listIdUser.get( i ) );
1304 }
1305
1306 sbSQL.append( CONSTANT_CLOSE_PARENTHESIS );
1307
1308 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
1309 {
1310 daoUtil.executeUpdate( );
1311 }
1312 }
1313 }
1314
1315
1316
1317
1318 @Override
1319 public void updateChangePassword( List<Integer> listIdUser )
1320 {
1321 if ( CollectionUtils.isNotEmpty( listIdUser ) )
1322 {
1323 StringBuilder sbSQL = new StringBuilder( );
1324 sbSQL.append( SQL_QUERY_UPDATE_RESET_PASSWORD_LIST_ID );
1325
1326 for ( int i = 0; i < listIdUser.size( ); i++ )
1327 {
1328 if ( i > 0 )
1329 {
1330 sbSQL.append( CONSTANT_COMMA );
1331 }
1332
1333 sbSQL.append( listIdUser.get( i ) );
1334 }
1335
1336 sbSQL.append( CONSTANT_CLOSE_PARENTHESIS );
1337
1338 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
1339 {
1340 daoUtil.executeUpdate( );
1341 }
1342 }
1343 }
1344
1345
1346
1347
1348 @Override
1349 public void updateUserExpirationDate( int nIdUser, Timestamp newExpirationDate )
1350 {
1351 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_REACTIVATE_ACCOUNT ) )
1352 {
1353
1354 if ( newExpirationDate == null )
1355 {
1356 daoUtil.setLongNull( 1 );
1357 }
1358 else
1359 {
1360 daoUtil.setLong( 1, newExpirationDate.getTime( ) );
1361 }
1362
1363 daoUtil.setInt( 2, nIdUser );
1364
1365 daoUtil.executeUpdate( );
1366 }
1367 }
1368
1369
1370
1371
1372 @Override
1373 public void updateDateLastLogin( int nIdUser, Timestamp dateLastLogin )
1374 {
1375 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_DATE_LAST_LOGIN ) )
1376 {
1377 daoUtil.setTimestamp( 1, dateLastLogin );
1378 daoUtil.setInt( 2, nIdUser );
1379 daoUtil.executeUpdate( );
1380 }
1381 }
1382
1383
1384 }