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.mylutece.modules.database.authentication.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.password.IPassword;
38 import fr.paris.lutece.util.password.IPasswordFactory;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.sql.Timestamp;
42
43 import java.util.ArrayList;
44 import java.util.Collection;
45 import java.util.List;
46
47 import javax.inject.Inject;
48
49 import org.apache.commons.collections.CollectionUtils;
50
51
52
53
54 public class DatabaseUserDAO implements IDatabaseUserDAO
55 {
56
57 private static final String PERCENT = "%";
58 private static final String SQL_QUERY_NEW_PK = " SELECT max( mylutece_database_user_id ) FROM mylutece_database_user ";
59 private static final String SQL_QUERY_SELECT = " SELECT mylutece_database_user_id, login, name_family, name_given, email, is_active, account_max_valid_date FROM mylutece_database_user WHERE mylutece_database_user_id = ?";
60 private static final String SQL_QUERY_INSERT = " INSERT INTO mylutece_database_user ( mylutece_database_user_id, login, name_family, name_given, email, is_active, password, password_max_valid_date, account_max_valid_date ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
61 private static final String SQL_QUERY_DELETE = " DELETE FROM mylutece_database_user WHERE mylutece_database_user_id = ? ";
62 private static final String SQL_QUERY_UPDATE = " UPDATE mylutece_database_user SET login = ?, name_family = ?, name_given = ?, email = ?, is_active = ? WHERE mylutece_database_user_id = ? ";
63 private static final String SQL_QUERY_UPDATE_PASSWORD = " UPDATE mylutece_database_user SET password = ?, password_max_valid_date = ? WHERE mylutece_database_user_id = ? ";
64 private static final String SQL_QUERY_UPDATE_RESET_PASSWORD = " UPDATE mylutece_database_user SET reset_password = ? WHERE mylutece_database_user_id = ? ";
65 private static final String SQL_QUERY_SELECTALL = " SELECT mylutece_database_user_id, login, name_family, name_given, email, is_active, account_max_valid_date, password_max_valid_date FROM mylutece_database_user ORDER BY name_family, login";
66 private static final String SQL_QUERY_SELECTALL_FOR_LOGIN = " SELECT mylutece_database_user_id, login, name_family, name_given, email, is_active FROM mylutece_database_user WHERE login = ? ";
67 private static final String SQL_QUERY_SELECTALL_FOR_EMAIL = " SELECT mylutece_database_user_id, login, name_family, name_given, email, is_active FROM mylutece_database_user WHERE email = ? ";
68 private static final String SQL_QUERY_CHECK_PASSWORD_FOR_USER_ID = " SELECT password FROM mylutece_database_user WHERE login = ?";
69 private static final String SQL_QUERY_SELECT_USER_FROM_SEARCH = " SELECT mylutece_database_user_id, login, name_family, name_given, email, is_active FROM mylutece_database_user "
70 + " WHERE login LIKE ? AND name_family LIKE ? and name_given LIKE ? AND email LIKE ? ORDER BY name_family ";
71 private static final String SQL_SELECT_USER_ID_FROM_PASSWORD = "SELECT mylutece_database_user_id FROM mylutece_database_user WHERE login = ?";
72 private static final String SQL_SELECT_USER_PASSWORD_HISTORY = "SELECT password FROM mylutece_database_user_password_history WHERE mylutece_database_user_id = ? ORDER BY date_password_change desc";
73 private static final String SQL_COUNT_USER_PASSWORD_HISTORY = "SELECT COUNT(*) FROM mylutece_database_user_password_history WHERE mylutece_database_user_id = ? AND date_password_change > ?";
74 private static final String SQL_INSERT_PASSWORD_HISTORY = "INSERT INTO mylutece_database_user_password_history (mylutece_database_user_id, password) VALUES ( ?, ? ) ";
75 private static final String SQL_DELETE_PASSWORD_HISTORY = "DELETE FROM mylutece_database_user_password_history WHERE mylutece_database_user_id = ?";
76 private static final String SQL_QUERY_SELECT_EXPIRED_USER_ID = "SELECT mylutece_database_user_id FROM mylutece_database_user WHERE is_active = ?";
77 private static final String SQL_QUERY_SELECT_EXPIRED_LIFE_TIME_USER_ID = "SELECT mylutece_database_user_id FROM mylutece_database_user WHERE account_max_valid_date < ? and is_active < ? ";
78 private static final String SQL_QUERY_SELECT_USER_ID_FIRST_ALERT = "SELECT mylutece_database_user_id FROM mylutece_database_user WHERE nb_alerts_sent = 0 and is_active < ? and account_max_valid_date < ? ";
79 private static final String SQL_QUERY_SELECT_USER_ID_OTHER_ALERT = "SELECT mylutece_database_user_id FROM mylutece_database_user "
80 + "WHERE nb_alerts_sent > 0 and nb_alerts_sent <= ? and is_active < ? and (account_max_valid_date + nb_alerts_sent * ?) < ? ";
81 private static final String SQL_QUERY_SELECT_USER_ID_PASSWORD_EXPIRED = " SELECT mylutece_database_user_id FROM mylutece_database_user WHERE password_max_valid_date < ? AND reset_password = 0 ";
82 private static final String SQL_QUERY_UPDATE_STATUS = " UPDATE mylutece_database_user SET is_active = ? WHERE mylutece_database_user_id IN ( ";
83 private static final String SQL_QUERY_UPDATE_NB_ALERT = " UPDATE mylutece_database_user SET nb_alerts_sent = nb_alerts_sent + 1 WHERE mylutece_database_user_id IN ( ";
84 private static final String SQL_QUERY_UPDATE_RESET_PASSWORD_LIST_ID = " UPDATE mylutece_database_user SET reset_password = 1 WHERE mylutece_database_user_id IN ( ";
85 private static final String SQL_QUERY_UPDATE_LAST_LOGGIN_DATE = " UPDATE mylutece_database_user SET last_login = ? WHERE login LIKE ? ";
86 private static final String SQL_QUERY_UPDATE_REACTIVATE_ACCOUNT = " UPDATE mylutece_database_user SET nb_alerts_sent = 0, account_max_valid_date = ? WHERE mylutece_database_user_id = ? ";
87 private static final String SQL_QUERY_SELECT_NB_ALERT_SENT = " SELECT nb_alerts_sent FROM mylutece_database_user WHERE mylutece_database_user_id = ? ";
88 private static final String SQL_QUERY_SELECT_BY_IDS = " SELECT mylutece_database_user_id, login, name_family, name_given, email, is_active, account_max_valid_date FROM mylutece_database_user WHERE mylutece_database_user_id IN ( ";
89 private static final String CONSTANT_CLOSE_PARENTHESIS = " ) ";
90 private static final String CONSTANT_COMMA = ", ";
91
92 @Inject
93 IPasswordFactory _passwordFactory;
94
95
96
97
98 @Override
99 public int newPrimaryKey( Plugin plugin )
100 {
101 int nKey = 1;
102 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
103 {
104 daoUtil.executeQuery( );
105
106 if ( daoUtil.next( ) )
107 {
108 nKey = daoUtil.getInt( 1 ) + 1;
109 }
110
111 }
112
113 return nKey;
114 }
115
116
117
118
119 @Override
120 public void insert( DatabaseUser databaseUser, IPassword password, Plugin plugin )
121 {
122 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
123 {
124 databaseUser.setUserId( newPrimaryKey( plugin ) );
125 daoUtil.setInt( 1, databaseUser.getUserId( ) );
126 daoUtil.setString( 2, databaseUser.getLogin( ) );
127 daoUtil.setString( 3, databaseUser.getLastName( ) );
128 daoUtil.setString( 4, databaseUser.getFirstName( ) );
129 daoUtil.setString( 5, databaseUser.getEmail( ) );
130 daoUtil.setInt( 6, databaseUser.getStatus( ) );
131 daoUtil.setString( 7, password.getStorableRepresentation( ) );
132 daoUtil.setTimestamp( 8, databaseUser.getPasswordMaxValidDate( ) );
133
134 if ( databaseUser.getAccountMaxValidDate( ) == null )
135 {
136 daoUtil.setLongNull( 9 );
137 }
138 else
139 {
140 daoUtil.setLong( 9, databaseUser.getAccountMaxValidDate( ).getTime( ) );
141 }
142
143 daoUtil.executeUpdate( );
144 }
145 }
146
147
148
149
150 @Override
151 public DatabaseUser load( int nUserId, Plugin plugin )
152 {
153 DatabaseUser databaseUser = null;
154 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
155 {
156 daoUtil.setInt( 1, nUserId );
157 daoUtil.executeQuery( );
158
159 if ( daoUtil.next( ) )
160 {
161 databaseUser = DatabaseUserFactory.getFactory( ).newDatabaseUser( );
162 databaseUser.setUserId( daoUtil.getInt( 1 ) );
163 databaseUser.setLogin( daoUtil.getString( 2 ) );
164 databaseUser.setLastName( daoUtil.getString( 3 ) );
165 databaseUser.setFirstName( daoUtil.getString( 4 ) );
166 databaseUser.setEmail( daoUtil.getString( 5 ) );
167 databaseUser.setStatus( daoUtil.getInt( 6 ) );
168
169 long accountTime = daoUtil.getLong( 7 );
170
171 if ( accountTime > 0 )
172 {
173 databaseUser.setAccountMaxValidDate( new Timestamp( accountTime ) );
174 }
175 }
176 }
177 return databaseUser;
178 }
179
180
181
182
183 @Override
184 public void delete( DatabaseUser databaseUser, Plugin plugin )
185 {
186 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
187 {
188 daoUtil.setInt( 1, databaseUser.getUserId( ) );
189 daoUtil.executeUpdate( );
190 }
191 }
192
193
194
195
196 @Override
197 public void store( DatabaseUser databaseUser, Plugin plugin )
198 {
199 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
200 {
201 daoUtil.setString( 1, databaseUser.getLogin( ) );
202 daoUtil.setString( 2, databaseUser.getLastName( ) );
203 daoUtil.setString( 3, databaseUser.getFirstName( ) );
204 daoUtil.setString( 4, databaseUser.getEmail( ) );
205 daoUtil.setInt( 5, databaseUser.getStatus( ) );
206
207 daoUtil.setInt( 6, databaseUser.getUserId( ) );
208
209 daoUtil.executeUpdate( );
210 }
211 }
212
213
214
215
216 @Override
217 public void updatePassword( DatabaseUser databaseUser, IPassword newPassword, Plugin plugin )
218 {
219 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_PASSWORD, plugin ) )
220 {
221 daoUtil.setString( 1, newPassword.getStorableRepresentation( ) );
222 daoUtil.setTimestamp( 2, databaseUser.getPasswordMaxValidDate( ) );
223 daoUtil.setInt( 3, databaseUser.getUserId( ) );
224
225 daoUtil.executeUpdate( );
226 }
227 }
228
229
230
231
232 @Override
233 public void updateResetPassword( DatabaseUser databaseUser, boolean bNewValue, Plugin plugin )
234 {
235 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_RESET_PASSWORD, plugin ) )
236 {
237 daoUtil.setBoolean( 1, bNewValue );
238 daoUtil.setInt( 2, databaseUser.getUserId( ) );
239
240 daoUtil.executeUpdate( );
241 }
242 }
243
244
245
246
247 @Override
248 public Collection<DatabaseUser> selectDatabaseUserList( Plugin plugin )
249 {
250 Collection<DatabaseUser> listDatabaseUsers = new ArrayList<>( );
251 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
252 {
253 daoUtil.executeQuery( );
254
255 while ( daoUtil.next( ) )
256 {
257 DatabaseUser databaseUser = DatabaseUserFactory.getFactory( ).newDatabaseUser( );
258 databaseUser.setUserId( daoUtil.getInt( 1 ) );
259 databaseUser.setLogin( daoUtil.getString( 2 ) );
260 databaseUser.setLastName( daoUtil.getString( 3 ) );
261 databaseUser.setFirstName( daoUtil.getString( 4 ) );
262 databaseUser.setEmail( daoUtil.getString( 5 ) );
263 databaseUser.setStatus( daoUtil.getInt( 6 ) );
264
265 long accountTime = daoUtil.getLong( 7 );
266
267 if ( accountTime > 0 )
268 {
269 databaseUser.setAccountMaxValidDate( new Timestamp( accountTime ) );
270 }
271
272 databaseUser.setPasswordMaxValidDate( daoUtil.getTimestamp( 8 ) );
273 listDatabaseUsers.add( databaseUser );
274 }
275 }
276 return listDatabaseUsers;
277 }
278
279
280
281
282 @Override
283 public Collection<DatabaseUser> selectDatabaseUserListForLogin( String strLogin, Plugin plugin )
284 {
285 Collection<DatabaseUser> listDatabaseUsers = new ArrayList<>( );
286 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FOR_LOGIN, plugin ) )
287 {
288 daoUtil.setString( 1, strLogin );
289 daoUtil.executeQuery( );
290
291 while ( daoUtil.next( ) )
292 {
293 DatabaseUser databaseUser = DatabaseUserFactory.getFactory( ).newDatabaseUser( );
294 databaseUser.setUserId( daoUtil.getInt( 1 ) );
295 databaseUser.setLogin( daoUtil.getString( 2 ) );
296 databaseUser.setLastName( daoUtil.getString( 3 ) );
297 databaseUser.setFirstName( daoUtil.getString( 4 ) );
298 databaseUser.setEmail( daoUtil.getString( 5 ) );
299 databaseUser.setStatus( daoUtil.getInt( 6 ) );
300
301 listDatabaseUsers.add( databaseUser );
302 }
303 }
304 return listDatabaseUsers;
305 }
306
307
308
309
310 @Override
311 public Collection<DatabaseUser> selectDatabaseUserListForEmail( String strEmail, Plugin plugin )
312 {
313 Collection<DatabaseUser> listDatabaseUsers = new ArrayList<>( );
314 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FOR_EMAIL, plugin ) )
315 {
316 daoUtil.setString( 1, strEmail );
317 daoUtil.executeQuery( );
318
319 while ( daoUtil.next( ) )
320 {
321 DatabaseUser databaseUser = DatabaseUserFactory.getFactory( ).newDatabaseUser( );
322 databaseUser.setUserId( daoUtil.getInt( 1 ) );
323 databaseUser.setLogin( daoUtil.getString( 2 ) );
324 databaseUser.setLastName( daoUtil.getString( 3 ) );
325 databaseUser.setFirstName( daoUtil.getString( 4 ) );
326 databaseUser.setEmail( daoUtil.getString( 5 ) );
327 databaseUser.setStatus( daoUtil.getInt( 6 ) );
328
329 listDatabaseUsers.add( databaseUser );
330 }
331 }
332 return listDatabaseUsers;
333 }
334
335
336
337
338 @Override
339 public IPassword loadPassword( String strLogin, Plugin plugin )
340 {
341 IPassword password = _passwordFactory.getDummyPassword( );
342 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_PASSWORD_FOR_USER_ID, plugin ) )
343 {
344 daoUtil.setString( 1, strLogin );
345 daoUtil.executeQuery( );
346 if ( daoUtil.next( ) )
347 {
348 password = _passwordFactory.getPassword( daoUtil.getString( 1 ) );
349 }
350 }
351 return password;
352 }
353
354
355
356
357 @Override
358 public List<DatabaseUser> selectDatabaseUsersListByFilter( DatabaseUserFilter duFilter, Plugin plugin )
359 {
360 List<DatabaseUser> listFilteredUsers = new ArrayList<>( );
361 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FROM_SEARCH, plugin ) )
362 {
363 daoUtil.setString( 1, PERCENT + duFilter.getLogin( ) + PERCENT );
364 daoUtil.setString( 2, PERCENT + duFilter.getLastName( ) + PERCENT );
365 daoUtil.setString( 3, PERCENT + duFilter.getFirstName( ) + PERCENT );
366 daoUtil.setString( 4, PERCENT + duFilter.getEmail( ) + PERCENT );
367
368 daoUtil.executeQuery( );
369
370 while ( daoUtil.next( ) )
371 {
372 DatabaseUser filteredUser = DatabaseUserFactory.getFactory( ).newDatabaseUser( );
373 filteredUser.setUserId( daoUtil.getInt( 1 ) );
374 filteredUser.setLogin( daoUtil.getString( 2 ) );
375 filteredUser.setLastName( daoUtil.getString( 3 ) );
376 filteredUser.setFirstName( daoUtil.getString( 4 ) );
377 filteredUser.setEmail( daoUtil.getString( 5 ) );
378 filteredUser.setStatus( daoUtil.getInt( 6 ) );
379 listFilteredUsers.add( filteredUser );
380 }
381 }
382 return listFilteredUsers;
383 }
384
385
386
387
388 @Override
389 public int findDatabaseUserIdFromLogin( String strLogin, Plugin plugin )
390 {
391 int nRecordId = 0;
392
393 try ( DAOUtil daoUtil = new DAOUtil( SQL_SELECT_USER_ID_FROM_PASSWORD, plugin ) )
394 {
395 daoUtil.setString( 1, strLogin );
396 daoUtil.executeQuery( );
397
398 if ( daoUtil.next( ) )
399 {
400 nRecordId = daoUtil.getInt( 1 );
401 }
402 }
403 return nRecordId;
404 }
405
406
407
408
409 @Override
410 public List<IPassword> selectUserPasswordHistory( int nUserID, Plugin plugin )
411 {
412 List<IPassword> listPasswordHistory = new ArrayList<>( );
413
414 try ( DAOUtil daoUtil = new DAOUtil( SQL_SELECT_USER_PASSWORD_HISTORY, plugin ) )
415 {
416 daoUtil.setInt( 1, nUserID );
417 daoUtil.executeQuery( );
418
419 while ( daoUtil.next( ) )
420 {
421 listPasswordHistory.add( _passwordFactory.getPassword( daoUtil.getString( 1 ) ) );
422 }
423 }
424 return listPasswordHistory;
425 }
426
427
428
429
430 @Override
431 public int countUserPasswordHistoryFromDate( Timestamp minDate, int nUserId, Plugin plugin )
432 {
433 int nNbRes = 0;
434
435 try ( DAOUtil daoUtil = new DAOUtil( SQL_COUNT_USER_PASSWORD_HISTORY, plugin ) )
436 {
437 daoUtil.setInt( 1, nUserId );
438 daoUtil.setTimestamp( 2, minDate );
439 daoUtil.executeQuery( );
440
441 if ( daoUtil.next( ) )
442 {
443 nNbRes = daoUtil.getInt( 1 );
444 }
445 }
446 return nNbRes;
447 }
448
449
450
451
452 @Override
453 public void insertNewPasswordInHistory( IPassword password, int nUserId, Plugin plugin )
454 {
455 try ( DAOUtil daoUtil = new DAOUtil( SQL_INSERT_PASSWORD_HISTORY, plugin ) )
456 {
457 daoUtil.setInt( 1, nUserId );
458 daoUtil.setString( 2, password.getStorableRepresentation( ) );
459
460 daoUtil.executeUpdate( );
461 }
462 }
463
464
465
466
467 @Override
468 public void removeAllPasswordHistoryForUser( int nUserId, Plugin plugin )
469 {
470 try ( DAOUtil daoUtil = new DAOUtil( SQL_DELETE_PASSWORD_HISTORY, plugin ) )
471 {
472 daoUtil.setInt( 1, nUserId );
473 daoUtil.executeUpdate( );
474 }
475 }
476
477
478
479
480 @Override
481 public List<Integer> findAllExpiredUserId( Plugin plugin )
482 {
483 List<Integer> listIdExpiredUser = new ArrayList<>( );
484 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_EXPIRED_USER_ID, plugin ) )
485 {
486 daoUtil.setInt( 1, DatabaseUser.STATUS_EXPIRED );
487 daoUtil.executeQuery( );
488
489 while ( daoUtil.next( ) )
490 {
491 listIdExpiredUser.add( daoUtil.getInt( 1 ) );
492 }
493 }
494 return listIdExpiredUser;
495 }
496
497
498
499
500 @Override
501 public List<Integer> getIdUsersWithExpiredLifeTimeList( Timestamp currentTimestamp, Plugin plugin )
502 {
503 List<Integer> listIdExpiredUser = new ArrayList<>( );
504 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_EXPIRED_LIFE_TIME_USER_ID, plugin ) )
505 {
506 daoUtil.setLong( 1, currentTimestamp.getTime( ) );
507 daoUtil.setInt( 2, DatabaseUser.STATUS_EXPIRED );
508 daoUtil.executeQuery( );
509
510 while ( daoUtil.next( ) )
511 {
512 listIdExpiredUser.add( daoUtil.getInt( 1 ) );
513 }
514 }
515 return listIdExpiredUser;
516 }
517
518
519
520
521 @Override
522 public List<Integer> getIdUsersToSendFirstAlert( Timestamp alertMaxDate, Plugin plugin )
523 {
524 List<Integer> listIdUserFirstAlertlist = new ArrayList<>( );
525 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_ID_FIRST_ALERT, plugin ) )
526 {
527 daoUtil.setInt( 1, DatabaseUser.STATUS_EXPIRED );
528 daoUtil.setLong( 2, alertMaxDate.getTime( ) );
529
530 daoUtil.executeQuery( );
531 while ( daoUtil.next( ) )
532 {
533 listIdUserFirstAlertlist.add( daoUtil.getInt( 1 ) );
534 }
535 }
536 return listIdUserFirstAlertlist;
537 }
538
539
540
541
542 @Override
543 public List<Integer> getIdUsersToSendOtherAlert( Timestamp alertMaxDate, Timestamp timeBetweenAlerts, int maxNumberAlerts, Plugin plugin )
544 {
545 List<Integer> listIdUserFirstAlert = new ArrayList<>( );
546 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_ID_OTHER_ALERT, plugin ) )
547 {
548 daoUtil.setInt( 1, maxNumberAlerts );
549 daoUtil.setInt( 2, DatabaseUser.STATUS_EXPIRED );
550 daoUtil.setLong( 3, timeBetweenAlerts.getTime( ) );
551 daoUtil.setLong( 4, alertMaxDate.getTime( ) );
552
553 daoUtil.executeQuery( );
554
555 while ( daoUtil.next( ) )
556 {
557 listIdUserFirstAlert.add( daoUtil.getInt( 1 ) );
558 }
559 }
560 return listIdUserFirstAlert;
561 }
562
563
564
565
566 @Override
567 public List<Integer> getIdUsersWithExpiredPasswordsList( Timestamp currentTimestamp, Plugin plugin )
568 {
569 List<Integer> idUserPasswordExpiredlist = new ArrayList<>( );
570 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_ID_PASSWORD_EXPIRED, plugin ) )
571 {
572 daoUtil.setTimestamp( 1, currentTimestamp );
573 daoUtil.executeQuery( );
574
575 while ( daoUtil.next( ) )
576 {
577 idUserPasswordExpiredlist.add( daoUtil.getInt( 1 ) );
578 }
579 }
580 return idUserPasswordExpiredlist;
581 }
582
583
584
585
586 @Override
587 public void updateUserStatus( List<Integer> listIdUser, int nNewStatus, Plugin plugin )
588 {
589 if ( CollectionUtils.isNotEmpty( listIdUser ) )
590 {
591 StringBuilder sbSQL = new StringBuilder( );
592 sbSQL.append( SQL_QUERY_UPDATE_STATUS );
593
594 for ( int i = 0; i < listIdUser.size( ); i++ )
595 {
596 if ( i > 0 )
597 {
598 sbSQL.append( CONSTANT_COMMA );
599 }
600
601 sbSQL.append( listIdUser.get( i ) );
602 }
603
604 sbSQL.append( CONSTANT_CLOSE_PARENTHESIS );
605
606 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin ) )
607 {
608 daoUtil.setInt( 1, nNewStatus );
609 daoUtil.executeUpdate( );
610 }
611 }
612 }
613
614
615
616
617 @Override
618 public void updateNbAlert( List<Integer> listIdUser, Plugin plugin )
619 {
620 if ( CollectionUtils.isNotEmpty( listIdUser ) )
621 {
622 StringBuilder sbSQL = new StringBuilder( );
623 sbSQL.append( SQL_QUERY_UPDATE_NB_ALERT );
624
625 for ( int i = 0; i < listIdUser.size( ); i++ )
626 {
627 if ( i > 0 )
628 {
629 sbSQL.append( CONSTANT_COMMA );
630 }
631
632 sbSQL.append( listIdUser.get( i ) );
633 }
634
635 sbSQL.append( CONSTANT_CLOSE_PARENTHESIS );
636
637 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin ) )
638 {
639 daoUtil.executeUpdate( );
640 }
641 }
642 }
643
644
645
646
647 @Override
648 public void updateChangePassword( List<Integer> listIdUser, Plugin plugin )
649 {
650 if ( CollectionUtils.isNotEmpty( listIdUser ) )
651 {
652 StringBuilder sbSQL = new StringBuilder( );
653 sbSQL.append( SQL_QUERY_UPDATE_RESET_PASSWORD_LIST_ID );
654
655 for ( int i = 0; i < listIdUser.size( ); i++ )
656 {
657 if ( i > 0 )
658 {
659 sbSQL.append( CONSTANT_COMMA );
660 }
661
662 sbSQL.append( listIdUser.get( i ) );
663 }
664
665 sbSQL.append( CONSTANT_CLOSE_PARENTHESIS );
666
667 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin ) )
668 {
669 daoUtil.executeUpdate( );
670 }
671 }
672 }
673
674
675
676
677 @Override
678 public void updateUserExpirationDate( int nIdUser, Timestamp newExpirationDate, Plugin plugin )
679 {
680 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_REACTIVATE_ACCOUNT, plugin ) )
681 {
682 if ( newExpirationDate == null )
683 {
684 daoUtil.setLongNull( 1 );
685 }
686 else
687 {
688 daoUtil.setLong( 1, newExpirationDate.getTime( ) );
689 }
690
691 daoUtil.setInt( 2, nIdUser );
692
693 daoUtil.executeUpdate( );
694 }
695 }
696
697
698
699
700 @Override
701 public int getNbAccountLifeTimeNotification( int nIdUser, Plugin plugin )
702 {
703 int nRes = 0;
704 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NB_ALERT_SENT, plugin ) )
705 {
706 daoUtil.setInt( 1, nIdUser );
707 daoUtil.executeQuery( );
708
709 if ( daoUtil.next( ) )
710 {
711 nRes = daoUtil.getInt( 1 );
712 }
713 }
714 return nRes;
715 }
716
717
718
719
720 @Override
721 public void updateUserLastLoginDate( String strLogin, Timestamp dateLastLogin, Plugin plugin )
722 {
723 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_LAST_LOGGIN_DATE, plugin ) )
724 {
725 daoUtil.setTimestamp( 1, dateLastLogin );
726 daoUtil.setString( 2, strLogin );
727 daoUtil.executeUpdate( );
728 }
729 }
730
731
732
733
734 @Override
735 public List<DatabaseUser> loadUsersByIds( List<Integer> userIds, Plugin plugin )
736 {
737 List<DatabaseUser> listUsers = new ArrayList<>( );
738
739 if ( CollectionUtils.isEmpty( userIds ) )
740 {
741 return listUsers;
742 }
743
744 StringBuilder sbSQL = new StringBuilder( );
745 sbSQL.append( SQL_QUERY_SELECT_BY_IDS );
746
747 for ( int i = 0; i < userIds.size( ); i++ )
748 {
749 if ( i > 0 )
750 {
751 sbSQL.append( CONSTANT_COMMA );
752 }
753
754 sbSQL.append( userIds.get( i ) );
755 }
756
757 sbSQL.append( CONSTANT_CLOSE_PARENTHESIS );
758
759 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin ) )
760 {
761 daoUtil.executeQuery( );
762
763 while ( daoUtil.next( ) )
764 {
765 DatabaseUser databaseUser = DatabaseUserFactory.getFactory( ).newDatabaseUser( );
766 databaseUser.setUserId( daoUtil.getInt( 1 ) );
767 databaseUser.setLogin( daoUtil.getString( 2 ) );
768 databaseUser.setLastName( daoUtil.getString( 3 ) );
769 databaseUser.setFirstName( daoUtil.getString( 4 ) );
770 databaseUser.setEmail( daoUtil.getString( 5 ) );
771 databaseUser.setStatus( daoUtil.getInt( 6 ) );
772
773 long accountTime = daoUtil.getLong( 7 );
774
775 if ( accountTime > 0 )
776 {
777 databaseUser.setAccountMaxValidDate( new Timestamp( accountTime ) );
778 }
779
780 listUsers.add( databaseUser );
781 }
782 }
783
784 return listUsers;
785 }
786 }