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.wssodatabase.authentication.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.Collection;
41 import java.util.List;
42
43 import org.apache.commons.lang.StringUtils;
44
45
46
47
48
49 public final class WssoUserDAO implements IWssoUserDAO
50 {
51
52 private static final String SQL_QUERY_NEW_PK = " SELECT max( mylutece_wsso_user_id ) FROM mylutece_wsso_user ";
53 private static final String SQL_QUERY_SELECT = " SELECT mylutece_wsso_user_id, guid, last_name, first_name, email, date_last_login FROM mylutece_wsso_user WHERE mylutece_wsso_user_id = ? ";
54 private static final String SQL_QUERY_INSERT = " INSERT INTO mylutece_wsso_user ( mylutece_wsso_user_id, guid, last_name, first_name, email ) VALUES ( ?, ?, ?, ?, ? ) ";
55 private static final String SQL_QUERY_DELETE = " DELETE FROM mylutece_wsso_user WHERE mylutece_wsso_user_id = ? ";
56 private static final String SQL_QUERY_UPDATE = " UPDATE mylutece_wsso_user SET mylutece_wsso_user_id = ?, guid = ?, last_name = ?, first_name = ?, email = ? WHERE mylutece_wsso_user_id = ? ";
57 private static final String SQL_QUERY_SELECTALL = " SELECT mylutece_wsso_user_id, guid, last_name, first_name, email, date_last_login FROM mylutece_wsso_user ORDER BY last_name, first_name, email ";
58 private static final String SQL_QUERY_SELECTALL_FOR_ROLE = " SELECT u.mylutece_wsso_user_id, u.guid, u.last_name, u.first_name, u.email, u.date_last_login FROM mylutece_wsso_user u, mylutece_wsso_user_role ur WHERE u.mylutece_wsso_user_id = ur.mylutece_wsso_user_id AND ur.mylutece_wsso_role_id = ? ORDER BY u.last_name, u.first_name, u.email ";
59 private static final String SQL_QUERY_SELECTALL_FOR_GUID = " SELECT mylutece_wsso_user_id, guid, last_name, first_name, email, date_last_login FROM mylutece_wsso_user WHERE guid = ? ORDER BY last_name, first_name, email ";
60 private static final String SQL_SELECT_WSSO_USER_ID_FROM_GUID = "SELECT mylutece_wsso_user_id FROM mylutece_wsso_user WHERE guid = ?";
61 private static final String SQL_QUERY_SELECT_WSSO_USER_IDS_WITH_ROLE = "SELECT distinct u.mylutece_wsso_user_id FROM mylutece_wsso_user u, mylutece_wsso_user_role ur WHERE u.mylutece_wsso_user_id = ur.mylutece_wsso_user_id AND ur.role = ?";
62
63
64 private static WssoUserDAO _dao = new WssoUserDAO( );
65
66
67
68
69 private WssoUserDAO( )
70 {
71 }
72
73
74
75
76
77
78 static WssoUserDAO getInstance( )
79 {
80 return _dao;
81 }
82
83
84
85
86
87
88 public int newPrimaryKey( Plugin plugin )
89 {
90 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
91 daoUtil.executeQuery( );
92
93 int nKey;
94
95 if ( !daoUtil.next( ) )
96 {
97
98 nKey = 1;
99 }
100
101 nKey = daoUtil.getInt( 1 ) + 1;
102
103 daoUtil.free( );
104
105 return nKey;
106 }
107
108
109
110
111
112
113
114 public void insert( WssoUser wssoUser, Plugin plugin )
115 {
116 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
117 wssoUser.setMyluteceWssoUserId( newPrimaryKey( plugin ) );
118 daoUtil.setInt( 1, wssoUser.getMyluteceWssoUserId( ) );
119 daoUtil.setString( 2, wssoUser.getGuid( ) );
120 daoUtil.setString( 3, wssoUser.getLastName( ) );
121 daoUtil.setString( 4, wssoUser.getFirstName( ) );
122 daoUtil.setString( 5, wssoUser.getEmail( ) );
123
124 daoUtil.executeUpdate( );
125 daoUtil.free( );
126 }
127
128
129
130
131
132
133
134
135 public WssoUser load( int nWssoUserId, Plugin plugin )
136 {
137 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
138 daoUtil.setInt( 1, nWssoUserId );
139 daoUtil.executeQuery( );
140
141 WssoUser wssoUser = null;
142
143 if ( daoUtil.next( ) )
144 {
145 wssoUser = new WssoUser( );
146 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
147 wssoUser.setGuid( daoUtil.getString( 2 ) );
148 wssoUser.setLastName( daoUtil.getString( 3 ) );
149 wssoUser.setFirstName( daoUtil.getString( 4 ) );
150 wssoUser.setEmail( daoUtil.getString( 5 ) );
151 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
152 }
153
154 daoUtil.free( );
155
156 return wssoUser;
157 }
158
159
160
161
162
163
164 public void delete( WssoUser wssoUser, Plugin plugin )
165 {
166 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
167 daoUtil.setInt( 1, wssoUser.getMyluteceWssoUserId( ) );
168
169 daoUtil.executeUpdate( );
170 daoUtil.free( );
171 }
172
173
174
175
176
177
178 public void store( WssoUser wssoUser, Plugin plugin )
179 {
180 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
181 daoUtil.setInt( 1, wssoUser.getMyluteceWssoUserId( ) );
182 daoUtil.setString( 2, wssoUser.getGuid( ) );
183 daoUtil.setString( 3, wssoUser.getLastName( ) );
184 daoUtil.setString( 4, wssoUser.getFirstName( ) );
185 daoUtil.setString( 5, wssoUser.getEmail( ) );
186 daoUtil.setInt( 6, wssoUser.getMyluteceWssoUserId( ) );
187
188 daoUtil.executeUpdate( );
189 daoUtil.free( );
190 }
191
192
193
194
195
196
197 public Collection<WssoUser> selectWssoUserList( Plugin plugin )
198 {
199 Collection<WssoUser> listWssoUsers = new ArrayList<WssoUser>( );
200 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
201 daoUtil.executeQuery( );
202
203 while ( daoUtil.next( ) )
204 {
205 WssoUser wssoUser = new WssoUser( );
206 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
207 wssoUser.setGuid( daoUtil.getString( 2 ) );
208 wssoUser.setLastName( daoUtil.getString( 3 ) );
209 wssoUser.setFirstName( daoUtil.getString( 4 ) );
210 wssoUser.setEmail( daoUtil.getString( 5 ) );
211 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
212
213 listWssoUsers.add( wssoUser );
214 }
215
216 daoUtil.free( );
217
218 return listWssoUsers;
219 }
220
221
222
223
224
225
226
227 public Collection<WssoUser> selectWssoUsersListForRole( int nIdRole, Plugin plugin )
228 {
229 Collection<WssoUser> listWssoUsers = new ArrayList<WssoUser>( );
230 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FOR_ROLE, plugin );
231 daoUtil.setInt( 1, nIdRole );
232 daoUtil.executeQuery( );
233
234 while ( daoUtil.next( ) )
235 {
236 WssoUser wssoUser = new WssoUser( );
237 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
238 wssoUser.setGuid( daoUtil.getString( 2 ) );
239 wssoUser.setLastName( daoUtil.getString( 3 ) );
240 wssoUser.setFirstName( daoUtil.getString( 4 ) );
241 wssoUser.setEmail( daoUtil.getString( 5 ) );
242 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
243
244 listWssoUsers.add( wssoUser );
245 }
246
247 daoUtil.free( );
248
249 return listWssoUsers;
250 }
251
252
253
254
255
256
257
258
259
260
261 public List<Integer> selectWssoUserIdsListForRole( String strRole, Plugin plugin )
262 {
263 List<Integer> listWssoUserIds = new ArrayList<Integer>( );
264 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_WSSO_USER_IDS_WITH_ROLE, plugin );
265 daoUtil.setString( 1, strRole );
266 daoUtil.executeQuery( );
267
268 while ( daoUtil.next( ) )
269 {
270 listWssoUserIds.add( daoUtil.getInt( 1 ) );
271 }
272
273 daoUtil.free( );
274
275 return listWssoUserIds;
276 }
277
278
279
280
281
282
283
284 public Collection<WssoUser> selectWssoUserListForGuid( String strGuid, Plugin plugin )
285 {
286 Collection<WssoUser> listWssoUsers = new ArrayList<WssoUser>( );
287 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FOR_GUID, plugin );
288 daoUtil.setString( 1, strGuid );
289 daoUtil.executeQuery( );
290
291 while ( daoUtil.next( ) )
292 {
293 WssoUser wssoUser = new WssoUser( );
294 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
295 wssoUser.setGuid( daoUtil.getString( 2 ) );
296 wssoUser.setLastName( daoUtil.getString( 3 ) );
297 wssoUser.setFirstName( daoUtil.getString( 4 ) );
298 wssoUser.setEmail( daoUtil.getString( 5 ) );
299 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
300
301 listWssoUsers.add( wssoUser );
302 }
303
304 daoUtil.free( );
305
306 return listWssoUsers;
307 }
308
309
310
311
312 @Override
313 public List<WssoUser> findWssoUserssByLastNameOrFirtNameOrEmailByProfil( String codeProfil, String strLastName,
314 String strFirstName, String strEmail, Plugin plugin )
315 {
316 List<WssoUser> listWssoUsers = new ArrayList<WssoUser>( );
317
318 StringBuffer strSQL = new StringBuffer(
319 "SELECT a.mylutece_wsso_user_id, a.guid, a.last_name, a.first_name, a.email, a.date_last_login FROM mylutece_wsso_user a INNER JOIN mylutece_wsso_profil_user b on a.mylutece_wsso_user_id = b.mylutece_wsso_user_id " );
320
321 strSQL.append( "WHERE b.mylutece_wsso_profil_code = ? " );
322
323 if ( StringUtils.isNotBlank( strLastName ) || StringUtils.isNotBlank( strFirstName )
324 || StringUtils.isNotBlank( strEmail ) )
325 {
326 if ( StringUtils.isNotBlank( strLastName ) )
327 {
328 strSQL.append( "AND a.last_name = ? " );
329
330 if ( StringUtils.isNotBlank( strFirstName ) )
331 {
332 strSQL.append( "AND a.first_name = ? " );
333
334 if ( StringUtils.isNotBlank( strEmail ) )
335 {
336 strSQL.append( "AND a.email = ? " );
337 }
338 }
339 else if ( StringUtils.isNotBlank( strEmail ) )
340 {
341 strSQL.append( "AND a.email = ? " );
342 }
343 }
344 else
345 {
346 if ( StringUtils.isNotBlank( strFirstName ) )
347 {
348 strSQL.append( "AND a.first_name = ? " );
349
350 if ( StringUtils.isNotBlank( strEmail ) )
351 {
352 strSQL.append( "AND a.email = ? " );
353 }
354 }
355 else if ( StringUtils.isNotBlank( strEmail ) )
356 {
357 strSQL.append( "AND a.email = ? " );
358 }
359 }
360 }
361
362 DAOUtil daoUtil = new DAOUtil( strSQL.toString( ), plugin );
363
364 daoUtil.setString( 1, codeProfil );
365
366 if ( StringUtils.isNotBlank( strLastName ) || StringUtils.isNotBlank( strFirstName )
367 || StringUtils.isNotBlank( strEmail ) )
368 {
369 if ( StringUtils.isNotBlank( strLastName ) )
370 {
371 daoUtil.setString( 2, strLastName );
372
373 if ( StringUtils.isNotBlank( strFirstName ) )
374 {
375 daoUtil.setString( 3, strFirstName );
376
377 if ( StringUtils.isNotBlank( strEmail ) )
378 {
379 daoUtil.setString( 4, strEmail );
380 }
381 }
382 else if ( StringUtils.isNotBlank( strEmail ) )
383 {
384 daoUtil.setString( 3, strEmail );
385 }
386 }
387 else
388 {
389 if ( StringUtils.isNotBlank( strFirstName ) )
390 {
391 daoUtil.setString( 2, strFirstName );
392
393 if ( StringUtils.isNotBlank( strEmail ) )
394 {
395 daoUtil.setString( 3, strEmail );
396 }
397 }
398 else if ( StringUtils.isNotBlank( strEmail ) )
399 {
400 daoUtil.setString( 2, strEmail );
401 }
402 }
403 }
404
405 daoUtil.executeQuery( );
406
407 while ( daoUtil.next( ) )
408 {
409 WssoUser wssoUser = new WssoUser( );
410 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
411 wssoUser.setGuid( daoUtil.getString( 2 ) );
412 wssoUser.setLastName( daoUtil.getString( 3 ) );
413 wssoUser.setFirstName( daoUtil.getString( 4 ) );
414 wssoUser.setEmail( daoUtil.getString( 5 ) );
415 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
416
417 listWssoUsers.add( wssoUser );
418 }
419
420 daoUtil.free( );
421
422 return listWssoUsers;
423 }
424
425
426
427
428 @Override
429 public List<WssoUser> findWssoUsersByLastNameOrFirstNameOrEmailByProfil( String strLastName, String strFirstName,
430 String strEmail, Plugin plugin )
431 {
432 List<WssoUser> listWssoUsers = new ArrayList<WssoUser>( );
433
434 StringBuffer strSQL = new StringBuffer(
435 "SELECT a.mylutece_wsso_user_id, a.guid, a.last_name, a.first_name, a.email, a.date_last_login FROM mylutece_wsso_user a " );
436
437 if ( StringUtils.isNotBlank( strLastName ) || StringUtils.isNotBlank( strFirstName )
438 || StringUtils.isNotBlank( strEmail ) )
439 {
440 strSQL.append( "WHERE " );
441 if ( StringUtils.isNotBlank( strLastName ) )
442 {
443 strSQL.append( "LOWER(a.last_name) LIKE ? " );
444
445 if ( StringUtils.isNotBlank( strFirstName ) )
446 {
447 strSQL.append( "AND LOWER(a.first_name) LIKE ? " );
448
449 if ( StringUtils.isNotBlank( strEmail ) )
450 {
451 strSQL.append( "AND LOWER(a.email) LIKE ? " );
452 }
453 }
454 else if ( StringUtils.isNotBlank( strEmail ) )
455 {
456 strSQL.append( "AND LOWER(a.email) LIKE ? " );
457 }
458 }
459 else
460 {
461 if ( StringUtils.isNotBlank( strFirstName ) )
462 {
463 strSQL.append( "LOWER(a.first_name) LIKE ? " );
464
465 if ( StringUtils.isNotBlank( strEmail ) )
466 {
467 strSQL.append( "AND LOWER(a.email) LIKE ? " );
468 }
469 }
470 else if ( StringUtils.isNotBlank( strEmail ) )
471 {
472 strSQL.append( "LOWER(a.email) LIKE ? " );
473 }
474 }
475 }
476
477 DAOUtil daoUtil = new DAOUtil( strSQL.toString( ), plugin );
478
479 if ( StringUtils.isNotBlank( strLastName ) || StringUtils.isNotBlank( strFirstName )
480 || StringUtils.isNotBlank( strEmail ) )
481 {
482 if ( StringUtils.isNotBlank( strLastName ) )
483 {
484 daoUtil.setString( 1, "%" + strLastName.toLowerCase( ) + "%" );
485
486 if ( StringUtils.isNotBlank( strFirstName ) )
487 {
488 daoUtil.setString( 2, "%" + strFirstName.toLowerCase( ) + "%" );
489
490 if ( StringUtils.isNotBlank( strEmail ) )
491 {
492 daoUtil.setString( 3, "%" + strEmail.toLowerCase( ) + "%" );
493 }
494 }
495 else if ( StringUtils.isNotBlank( strEmail ) )
496 {
497 daoUtil.setString( 2, "%" + strEmail.toLowerCase( ) + "%" );
498 }
499 }
500 else
501 {
502 if ( StringUtils.isNotBlank( strFirstName ) )
503 {
504 daoUtil.setString( 1, "%" + strFirstName.toLowerCase( ) + "%" );
505
506 if ( StringUtils.isNotBlank( strEmail ) )
507 {
508 daoUtil.setString( 2, "%" + strEmail.toLowerCase( ) + "%" );
509 }
510 }
511 else if ( StringUtils.isNotBlank( strEmail ) )
512 {
513 daoUtil.setString( 1, "%" + strEmail.toLowerCase( ) + "%" );
514 }
515 }
516 }
517
518 daoUtil.executeQuery( );
519
520 while ( daoUtil.next( ) )
521 {
522 WssoUser wssoUser = new WssoUser( );
523 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
524 wssoUser.setGuid( daoUtil.getString( 2 ) );
525 wssoUser.setLastName( daoUtil.getString( 3 ) );
526 wssoUser.setFirstName( daoUtil.getString( 4 ) );
527 wssoUser.setEmail( daoUtil.getString( 5 ) );
528 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
529
530 listWssoUsers.add( wssoUser );
531 }
532
533 daoUtil.free( );
534
535 return listWssoUsers;
536 }
537
538
539
540
541 @Override
542 public int findDatabaseUserIdFromGuid( String strGuid, Plugin plugin )
543 {
544 int nRecordId = 0;
545
546 DAOUtil daoUtil = new DAOUtil( SQL_SELECT_WSSO_USER_ID_FROM_GUID, plugin );
547 daoUtil.setString( 1, strGuid );
548 daoUtil.executeQuery( );
549
550 if ( daoUtil.next( ) )
551 {
552 nRecordId = daoUtil.getInt( 1 );
553 }
554
555 daoUtil.free( );
556
557 return nRecordId;
558 }
559 }