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.identitystore.business.duplicates.suspicions;
35
36 import com.fasterxml.jackson.core.JsonProcessingException;
37 import com.fasterxml.jackson.core.type.TypeReference;
38 import com.fasterxml.jackson.databind.ObjectMapper;
39 import fr.paris.lutece.plugins.identitystore.v3.web.rs.dto.search.SearchAttribute;
40 import fr.paris.lutece.plugins.identitystore.v3.web.rs.util.Constants;
41 import fr.paris.lutece.plugins.identitystore.web.exception.IdentityStoreException;
42 import fr.paris.lutece.portal.service.plugin.Plugin;
43 import fr.paris.lutece.util.ReferenceList;
44 import fr.paris.lutece.util.sql.DAOUtil;
45 import org.apache.commons.collections.CollectionUtils;
46 import org.apache.commons.lang3.StringUtils;
47
48 import java.sql.Statement;
49 import java.sql.Timestamp;
50 import java.time.Instant;
51 import java.util.ArrayList;
52 import java.util.Collections;
53 import java.util.List;
54 import java.util.Map;
55 import java.util.Optional;
56 import java.util.stream.Collectors;
57
58
59
60
61 public final class SuspiciousIdentityDAO implements ISuspiciousIdentityDAO
62 {
63
64 private static final String SQL_QUERY_PURGE = "TRUNCATE TABLE identitystore_quality_suspicious_identity";
65 private static final String SQL_QUERY_SELECT = "SELECT i.id_suspicious_identity, i.customer_id, i.id_duplicate_rule, r.code, l.date_lock_end, l.is_locked, l.author_type, l.author_name FROM identitystore_quality_suspicious_identity i LEFT JOIN identitystore_quality_suspicious_identity_lock l ON i.customer_id = l.customer_id LEFT JOIN identitystore_duplicate_rule r ON r.id_rule = i.id_duplicate_rule WHERE id_suspicious_identity = ?";
66 private static final String SQL_QUERY_INSERT = "INSERT INTO identitystore_quality_suspicious_identity ( customer_id, id_duplicate_rule ) VALUES ( ?, ?) ";
67 private static final String SQL_QUERY_ADD_LOCK = "INSERT INTO identitystore_quality_suspicious_identity_lock ( customer_id, is_locked, date_lock_end, author_type, author_name ) VALUES ( ?, ?, ?, ?, ?) ";
68 private static final String SQL_QUERY_REMOVE_LOCK = "DELETE FROM identitystore_quality_suspicious_identity_lock WHERE customer_id = ? ";
69 private static final String SQL_QUERY_REMOVE_LOCK_WITH_ID_SUSPICIOUS = "DELETE FROM identitystore_quality_suspicious_identity_lock WHERE customer_id IN ( SELECT customer_id FROM identitystore_quality_suspicious_identity WHERE id_suspicious_identity = ? ) ";
70 private static final String SQL_QUERY_PURGE_LOCKS = "DELETE FROM identitystore_quality_suspicious_identity_lock WHERE date_lock_end < NOW()";
71 private static final String SQL_QUERY_CHECK_EXCLUDED = "SELECT COUNT(*) FROM identitystore_quality_suspicious_identity_excluded WHERE (first_customer_id = ? AND second_customer_id = ?) OR (first_customer_id = ? AND second_customer_id = ?)";
72 private static final String SQL_QUERY_CHECK_LIST_EXCLUDED = "SELECT COUNT(*) FROM identitystore_quality_suspicious_identity_excluded WHERE ";
73 private static final String SQL_QUERY_CHECK_SUSPICIOUS = "SELECT COUNT(*) FROM identitystore_quality_suspicious_identity WHERE customer_id IN (";
74 private static final String SQL_QUERY_INSERT_EXCLUDED = "INSERT INTO identitystore_quality_suspicious_identity_excluded ( first_customer_id, second_customer_id, author_type, author_name, date_create ) VALUES ( ?, ?, ?, ?, NOW())";
75 private static final String SQL_QUERY_DELETE = "DELETE FROM identitystore_quality_suspicious_identity WHERE id_suspicious_identity = ? ";
76 private static final String SQL_QUERY_DELETE_CUID = "DELETE FROM identitystore_quality_suspicious_identity WHERE customer_id = ? ";
77 private static final String SQL_QUERY_UPDATE = "UPDATE identitystore_quality_suspicious_identity SET customer_id = ? WHERE id_suspicious_identity = ?";
78 private static final String SQL_QUERY_SELECTALL = "SELECT i.id_suspicious_identity, i.customer_id, i.id_duplicate_rule, d.code, i.date_create, h.metadata, l.date_lock_end, l.is_locked, l.author_type, l.author_name FROM identitystore_quality_suspicious_identity i LEFT JOIN identitystore_quality_suspicious_identity_lock l ON i.customer_id = l.customer_id LEFT JOIN identitystore_duplicate_rule d on d.id_rule = i.id_duplicate_rule LEFT JOIN identitystore_identity_history h on i.customer_id = h.customer_id AND i.date_create = h.modification_date ";
79 private static final String SQL_JOIN_SELECTALL_ATTRIBUTE_FILTER = "LEFT JOIN identitystore_identity id ON id.customer_id = i.customer_id LEFT JOIN identitystore_identity_attribute a ON a.id_identity = id.id_identity LEFT JOIN identitystore_ref_attribute r ON r.id_attribute = a.id_attribute ";
80 private static final String SQL_GROUPBY_HAVING_SELECTALL_ATTRIBUTE_FILTER = " GROUP BY i.id_suspicious_identity, d.code, l.date_lock_end, l.is_locked, l.author_type, l.author_name, h.id_history HAVING COUNT(i.id_suspicious_identity) = ${filter_count}";
81 private static final String SQL_QUERY_SELECTALL_EXCLUDED = "SELECT first_customer_id, second_customer_id, date_create, author_type, author_name FROM identitystore_quality_suspicious_identity_excluded";
82 private static final String SQL_QUERY_SELECTALL_EXCLUDED_BY_CUSTOMER_ID = "SELECT first_customer_id, second_customer_id, date_create, author_type, author_name FROM identitystore_quality_suspicious_identity_excluded WHERE first_customer_id = ? OR second_customer_id = ? ";
83 private static final String SQL_QUERY_SELECTALL_CUIDS = "SELECT customer_id FROM identitystore_quality_suspicious_identity si JOIN identitystore_duplicate_rule dr ON dr.id_rule = si.id_duplicate_rule WHERE dr.code = ? ";
84 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_suspicious_identity FROM identitystore_quality_suspicious_identity";
85 private static final String SQL_QUERY_SELECTALL_BY_IDS = "SELECT i.id_suspicious_identity, i.customer_id, i.id_duplicate_rule, r.code, l.date_lock_end, l.is_locked, l.author_type, l.author_name FROM identitystore_quality_suspicious_identity i LEFT JOIN identitystore_quality_suspicious_identity_lock l ON i.customer_id = l.customer_id LEFT JOIN identitystore_duplicate_rule r ON r.id_rule = i.id_duplicate_rule WHERE id_suspicious_identity IN ( ";
86 private static final String SQL_QUERY_SELECT_BY_CUSTOMER_ID = "SELECT i.id_suspicious_identity, i.customer_id, i.date_create, i.id_duplicate_rule, r.code, l.date_lock_end, l.is_locked, l.author_type, l.author_name FROM identitystore_quality_suspicious_identity i LEFT JOIN identitystore_quality_suspicious_identity_lock l ON i.customer_id = l.customer_id LEFT JOIN identitystore_duplicate_rule r ON r.id_rule = i.id_duplicate_rule WHERE i.customer_id = ? ";
87 private static final String SQL_QUERY_SELECT_BY_CUSTOMER_IDs = "SELECT i.id_suspicious_identity, i.customer_id, i.date_create, i.id_duplicate_rule, r.code, l.date_lock_end, l.is_locked, l.author_type, l.author_name FROM identitystore_quality_suspicious_identity i LEFT JOIN identitystore_quality_suspicious_identity_lock l ON i.customer_id = l.customer_id LEFT JOIN identitystore_duplicate_rule r ON r.id_rule = i.id_duplicate_rule WHERE i.customer_id IN ";
88 private static final String SQL_QUERY_SELECT_COUNT = "SELECT count(id_suspicious_identity) FROM identitystore_quality_suspicious_identity ";
89 private static final String SQL_QUERY_SELECT_COUNT_BY_RULE_ID = SQL_QUERY_SELECT_COUNT + " WHERE id_duplicate_rule = ? ";
90 private static final String SQL_QUERY_REMOVE_EXCLUDED_IDENTITIES = "DELETE FROM identitystore_quality_suspicious_identity_excluded WHERE first_customer_id = ? AND second_customer_id = ?";
91 private static final String SQL_QUERY_REMOVE_EXCLUDED_IDENTITIES_ONE_CUID = "DELETE FROM identitystore_quality_suspicious_identity_excluded WHERE first_customer_id = ? OR second_customer_id = ?";
92
93 private final ObjectMapper objectMapper = new ObjectMapper( );
94
95
96
97
98 @Override
99 public void insert( SuspiciousIdentity suspiciousIdentity, Plugin plugin )
100 {
101 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
102 {
103 int nIndex = 1;
104 daoUtil.setString( nIndex++, suspiciousIdentity.getCustomerId( ) );
105 daoUtil.setInt( nIndex,
106 suspiciousIdentity.getIdDuplicateRule( ) != null ? suspiciousIdentity.getIdDuplicateRule( ) : Constants.MANUAL_SUSPICIOUS_RULE_ID );
107
108 daoUtil.executeUpdate( );
109 if ( daoUtil.nextGeneratedKey( ) )
110 {
111 suspiciousIdentity.setId( daoUtil.getGeneratedKeyInt( 1 ) );
112 }
113 }
114 }
115
116 @Override
117 public void insertExcluded( String firstCuid, String secondCuid, String authorType, String authorName, Plugin plugin )
118 {
119 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_EXCLUDED, plugin ) )
120 {
121 daoUtil.setString( 1, firstCuid );
122 daoUtil.setString( 2, secondCuid );
123 daoUtil.setString( 3, authorType );
124 daoUtil.setString( 4, authorName );
125 daoUtil.executeUpdate( );
126 }
127 }
128
129
130
131
132 @Override
133 public Optional<SuspiciousIdentity> load( int nKey, Plugin plugin )
134 {
135 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
136 {
137 daoUtil.setInt( 1, nKey );
138 daoUtil.executeQuery( );
139 SuspiciousIdentity suspiciousIdentity = null;
140
141 if ( daoUtil.next( ) )
142 {
143 suspiciousIdentity = new SuspiciousIdentity( );
144 int nIndex = 1;
145
146 suspiciousIdentity.setId( daoUtil.getInt( nIndex++ ) );
147 suspiciousIdentity.setCustomerId( daoUtil.getString( nIndex++ ) );
148 suspiciousIdentity.setIdDuplicateRule( daoUtil.getInt( nIndex++ ) );
149 suspiciousIdentity.setDuplicateRuleCode( daoUtil.getString( nIndex++ ) );
150 final SuspiciousIdentityLocktystore/business/duplicates/suspicions/SuspiciousIdentityLock.html#SuspiciousIdentityLock">SuspiciousIdentityLock lock = new SuspiciousIdentityLock( );
151 suspiciousIdentity.setLock( lock );
152 lock.setLockEndDate( daoUtil.getTimestamp( nIndex++ ) );
153 if ( lock.getLockEndDate( ) != null )
154 {
155 lock.setLocked( daoUtil.getBoolean( nIndex++ ) );
156 lock.setAuthorType( daoUtil.getString( nIndex++ ) );
157 lock.setAuthorName( daoUtil.getString( nIndex ) );
158 }
159 else
160 {
161 lock.setLocked( false );
162 }
163 }
164 return Optional.ofNullable( suspiciousIdentity );
165 }
166 }
167
168
169
170
171 @Override
172 public SuspiciousIdentity selectByCustomerID( String customerId, Plugin plugin )
173 {
174 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CUSTOMER_ID, plugin ) )
175 {
176 daoUtil.setString( 1, customerId );
177 daoUtil.executeQuery( );
178 SuspiciousIdentity suspiciousIdentity = null;
179
180 if ( daoUtil.next( ) )
181 {
182 suspiciousIdentity = new SuspiciousIdentity( );
183 int nIndex = 1;
184
185 suspiciousIdentity.setId( daoUtil.getInt( nIndex++ ) );
186 suspiciousIdentity.setCustomerId( daoUtil.getString( nIndex++ ) );
187 suspiciousIdentity.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
188 suspiciousIdentity.setIdDuplicateRule( daoUtil.getInt( nIndex++ ) );
189 suspiciousIdentity.setDuplicateRuleCode( daoUtil.getString( nIndex++ ) );
190 final SuspiciousIdentityLocktystore/business/duplicates/suspicions/SuspiciousIdentityLock.html#SuspiciousIdentityLock">SuspiciousIdentityLock lock = new SuspiciousIdentityLock( );
191 suspiciousIdentity.setLock( lock );
192 lock.setLockEndDate( daoUtil.getTimestamp( nIndex++ ) );
193 if ( lock.getLockEndDate( ) != null )
194 {
195 lock.setLocked( daoUtil.getBoolean( nIndex++ ) );
196 lock.setAuthorType( daoUtil.getString( nIndex++ ) );
197 lock.setAuthorName( daoUtil.getString( nIndex ) );
198 }
199 else
200 {
201 lock.setLocked( false );
202 }
203 suspiciousIdentity.setLock( lock );
204 }
205
206 return suspiciousIdentity;
207 }
208 }
209
210
211
212
213 @Override
214 public List<SuspiciousIdentity> selectByCustomerIDs( List<String> customerIds, Plugin plugin )
215 {
216 final String sqlQuerySelectByCustomerIDs = SQL_QUERY_SELECT_BY_CUSTOMER_IDs + " ( '" + String.join("', '", customerIds) + "' )";
217 final List<SuspiciousIdentity> suspicions = new ArrayList<>( );
218 try (final DAOUtil daoUtil = new DAOUtil(sqlQuerySelectByCustomerIDs, plugin ) )
219 {
220 daoUtil.executeQuery( );
221
222 while ( daoUtil.next() )
223 {
224 final SuspiciousIdentitysiness/duplicates/suspicions/SuspiciousIdentity.html#SuspiciousIdentity">SuspiciousIdentity suspiciousIdentity = new SuspiciousIdentity( );
225 suspicions.add(suspiciousIdentity);
226
227 int nIndex = 1;
228
229 suspiciousIdentity.setId( daoUtil.getInt( nIndex++ ) );
230 suspiciousIdentity.setCustomerId( daoUtil.getString( nIndex++ ) );
231 suspiciousIdentity.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
232 suspiciousIdentity.setIdDuplicateRule( daoUtil.getInt( nIndex++ ) );
233 suspiciousIdentity.setDuplicateRuleCode( daoUtil.getString( nIndex++ ) );
234 final SuspiciousIdentityLocktystore/business/duplicates/suspicions/SuspiciousIdentityLock.html#SuspiciousIdentityLock">SuspiciousIdentityLock lock = new SuspiciousIdentityLock( );
235 suspiciousIdentity.setLock( lock );
236 lock.setLockEndDate( daoUtil.getTimestamp( nIndex++ ) );
237 if ( lock.getLockEndDate( ) != null )
238 {
239 lock.setLocked( daoUtil.getBoolean( nIndex++ ) );
240 lock.setAuthorType( daoUtil.getString( nIndex++ ) );
241 lock.setAuthorName( daoUtil.getString( nIndex ) );
242 }
243 else
244 {
245 lock.setLocked( false );
246 }
247 suspiciousIdentity.setLock( lock );
248 }
249
250 return suspicions;
251 }
252 }
253
254
255
256
257 @Override
258 public void delete( int nId, Plugin plugin )
259 {
260 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
261 {
262 daoUtil.setInt( 1, nId );
263 daoUtil.executeUpdate( );
264 }
265 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_LOCK_WITH_ID_SUSPICIOUS, plugin ) )
266 {
267 daoUtil.setInt( 1, nId );
268 daoUtil.executeUpdate( );
269 }
270 }
271
272
273
274
275 @Override
276 public void delete( String customerId, Plugin plugin )
277 {
278 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_CUID, plugin ) )
279 {
280 daoUtil.setString( 1, customerId );
281 daoUtil.executeUpdate( );
282 }
283 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_LOCK, plugin ) )
284 {
285 daoUtil.setString( 1, customerId );
286 daoUtil.executeUpdate( );
287 }
288 }
289
290
291
292
293 @Override
294 public void store( SuspiciousIdentity suspiciousIdentity, Plugin plugin )
295 {
296 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
297 {
298 int nIndex = 1;
299
300 daoUtil.setString( nIndex++, suspiciousIdentity.getCustomerId( ) );
301 daoUtil.setInt( nIndex, suspiciousIdentity.getId( ) );
302
303 daoUtil.executeUpdate( );
304 }
305 }
306
307
308
309
310 @Override
311 public List<SuspiciousIdentity> selectSuspiciousIdentitysList( final String ruleCode, final int max, final Integer priority, Plugin plugin )
312 throws IdentityStoreException
313 {
314 return selectSuspiciousIdentitysList( ruleCode, Collections.emptyList( ), max, priority, plugin );
315 }
316
317
318
319
320 @Override
321 public List<SuspiciousIdentity> selectSuspiciousIdentitysList( final String ruleCode, final List<SearchAttribute> attributes, final Integer max,
322 final Integer priority, Plugin plugin ) throws IdentityStoreException
323 {
324 final List<SuspiciousIdentity> suspiciousIdentityList = new ArrayList<>( );
325 final StringBuilder query = new StringBuilder( SQL_QUERY_SELECTALL );
326 if ( CollectionUtils.isNotEmpty( attributes ) )
327 {
328 query.append( SQL_JOIN_SELECTALL_ATTRIBUTE_FILTER );
329 }
330 query.append( " WHERE 1=1 " );
331
332 if ( StringUtils.isNotEmpty( ruleCode ) )
333 {
334 query.append( "AND d.code = '" ).append( ruleCode ).append( "' " );
335 }
336
337 if ( priority != null )
338 {
339 query.append( "AND d.priority = '" ).append( priority ).append( "' " );
340 }
341
342 if ( CollectionUtils.isNotEmpty( attributes ) )
343 {
344 query.append( attributes.stream( )
345 .map( attr -> "(r.key_name = '" + attr.getKey( ) + "' AND LOWER(a.attribute_value) = '" + attr.getValue( ).toLowerCase( ) + "')" )
346 .collect( Collectors.joining( " OR ", "AND ( ", " ) " ) ) )
347 .append( SQL_GROUPBY_HAVING_SELECTALL_ATTRIBUTE_FILTER.replace( "${filter_count}", String.valueOf( attributes.size( ) ) ) );
348 }
349
350 if ( max != null && max != 0 )
351 {
352 query.append( " LIMIT " ).append( max );
353 }
354
355 try ( final DAOUtil daoUtil = new DAOUtil( query.toString( ), plugin ) )
356 {
357 daoUtil.executeQuery( );
358
359 while ( daoUtil.next( ) )
360 {
361 final SuspiciousIdentitysiness/duplicates/suspicions/SuspiciousIdentity.html#SuspiciousIdentity">SuspiciousIdentity suspiciousIdentity = new SuspiciousIdentity( );
362 int nIndex = 1;
363
364 suspiciousIdentity.setId( daoUtil.getInt( nIndex++ ) );
365 suspiciousIdentity.setCustomerId( daoUtil.getString( nIndex++ ) );
366 suspiciousIdentity.setIdDuplicateRule( daoUtil.getInt( nIndex++ ) );
367 suspiciousIdentity.setDuplicateRuleCode( daoUtil.getString( nIndex++ ) );
368 suspiciousIdentity.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
369 final String jsonMap = daoUtil.getString( nIndex++ );
370 if ( StringUtils.isNotEmpty( jsonMap ) )
371 {
372 final Map<String, String> mapMetaData = objectMapper.readValue( jsonMap, new TypeReference<Map<String, String>>( )
373 {
374 } );
375 suspiciousIdentity.getMetadata( ).clear( );
376 if ( mapMetaData != null && !mapMetaData.isEmpty( ) )
377 {
378 suspiciousIdentity.getMetadata( ).putAll( mapMetaData );
379 }
380 }
381 final SuspiciousIdentityLocktystore/business/duplicates/suspicions/SuspiciousIdentityLock.html#SuspiciousIdentityLock">SuspiciousIdentityLock lock = new SuspiciousIdentityLock( );
382 suspiciousIdentity.setLock( lock );
383 lock.setLockEndDate( daoUtil.getTimestamp( nIndex++ ) );
384 if ( lock.getLockEndDate( ) != null )
385 {
386 lock.setLocked( daoUtil.getBoolean( nIndex++ ) );
387 lock.setAuthorType( daoUtil.getString( nIndex++ ) );
388 lock.setAuthorName( daoUtil.getString( nIndex ) );
389 }
390 else
391 {
392 lock.setLocked( false );
393 }
394 suspiciousIdentityList.add( suspiciousIdentity );
395 }
396
397 return suspiciousIdentityList;
398 }
399 catch( JsonProcessingException e )
400 {
401 throw new IdentityStoreException( e.getMessage( ), e );
402 }
403 }
404
405 @Override
406 public List<ExcludedIdentities> selectExcludedIdentitiesList( Plugin plugin )
407 {
408 final List<ExcludedIdentities> excludedIdentitiesList = new ArrayList<>( );
409
410 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_EXCLUDED, plugin ) )
411 {
412 daoUtil.executeQuery( );
413
414 while ( daoUtil.next( ) )
415 {
416 final ExcludedIdentitiessiness/duplicates/suspicions/ExcludedIdentities.html#ExcludedIdentities">ExcludedIdentities excludedIdentities = new ExcludedIdentities( );
417 int nIndex = 1;
418
419 excludedIdentities.setFirstCustomerId( daoUtil.getString( nIndex++ ) );
420 excludedIdentities.setSecondCustomerId( daoUtil.getString( nIndex++ ) );
421 excludedIdentities.setExclusionDate( daoUtil.getTimestamp( nIndex++ ) );
422 excludedIdentities.setAuthorType( daoUtil.getString( nIndex++ ) );
423 excludedIdentities.setAuthorName( daoUtil.getString( nIndex ) );
424 excludedIdentitiesList.add( excludedIdentities );
425 }
426
427 return excludedIdentitiesList;
428 }
429 }
430
431 @Override
432 public List<ExcludedIdentities> selectExcludedIdentitiesList( final String customerId, Plugin plugin )
433 {
434 final List<ExcludedIdentities> excludedIdentitiesList = new ArrayList<>( );
435
436 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_EXCLUDED_BY_CUSTOMER_ID, plugin ) )
437 {
438 daoUtil.setString( 1, customerId );
439 daoUtil.setString( 2, customerId );
440 daoUtil.executeQuery( );
441
442 while ( daoUtil.next( ) )
443 {
444 final ExcludedIdentitiessiness/duplicates/suspicions/ExcludedIdentities.html#ExcludedIdentities">ExcludedIdentities excludedIdentities = new ExcludedIdentities( );
445 int nIndex = 1;
446
447 excludedIdentities.setFirstCustomerId( daoUtil.getString( nIndex++ ) );
448 excludedIdentities.setSecondCustomerId( daoUtil.getString( nIndex++ ) );
449 excludedIdentities.setExclusionDate( daoUtil.getTimestamp( nIndex++ ) );
450 excludedIdentities.setAuthorType( daoUtil.getString( nIndex++ ) );
451 excludedIdentities.setAuthorName( daoUtil.getString( nIndex ) );
452 excludedIdentitiesList.add( excludedIdentities );
453 }
454
455 return excludedIdentitiesList;
456 }
457 }
458
459
460
461
462 @Override
463 public List<Integer> selectIdSuspiciousIdentitysList( Plugin plugin )
464 {
465 List<Integer> suspiciousIdentityList = new ArrayList<>( );
466 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
467 {
468 daoUtil.executeQuery( );
469
470 while ( daoUtil.next( ) )
471 {
472 suspiciousIdentityList.add( daoUtil.getInt( 1 ) );
473 }
474
475 return suspiciousIdentityList;
476 }
477 }
478
479
480
481
482 @Override
483 public ReferenceList selectSuspiciousIdentitysReferenceList( Plugin plugin )
484 {
485 ReferenceList suspiciousIdentityList = new ReferenceList( );
486 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
487 {
488 daoUtil.executeQuery( );
489
490 while ( daoUtil.next( ) )
491 {
492 suspiciousIdentityList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
493 }
494
495 return suspiciousIdentityList;
496 }
497 }
498
499
500
501
502 @Override
503 public List<SuspiciousIdentity> selectSuspiciousIdentitysListByIds( Plugin plugin, List<Integer> listIds )
504 {
505 final List<SuspiciousIdentity> suspiciousIdentityList = new ArrayList<>( );
506
507 StringBuilder builder = new StringBuilder( );
508
509 if ( !listIds.isEmpty( ) )
510 {
511 for ( int i = 0; i < listIds.size( ); i++ )
512 {
513 builder.append( "?," );
514 }
515
516 String placeHolders = builder.deleteCharAt( builder.length( ) - 1 ).toString( );
517 String stmt = SQL_QUERY_SELECTALL_BY_IDS + placeHolders + ")";
518
519 try ( DAOUtil daoUtil = new DAOUtil( stmt, plugin ) )
520 {
521 int index = 1;
522 for ( Integer n : listIds )
523 {
524 daoUtil.setInt( index++, n );
525 }
526
527 daoUtil.executeQuery( );
528 while ( daoUtil.next( ) )
529 {
530 final SuspiciousIdentitysiness/duplicates/suspicions/SuspiciousIdentity.html#SuspiciousIdentity">SuspiciousIdentity suspiciousIdentity = new SuspiciousIdentity( );
531 int nIndex = 1;
532
533 suspiciousIdentity.setId( daoUtil.getInt( nIndex++ ) );
534 suspiciousIdentity.setCustomerId( daoUtil.getString( nIndex++ ) );
535 suspiciousIdentity.setIdDuplicateRule( daoUtil.getInt( nIndex++ ) );
536 suspiciousIdentity.setDuplicateRuleCode( daoUtil.getString( nIndex++ ) );
537
538 final SuspiciousIdentityLocktystore/business/duplicates/suspicions/SuspiciousIdentityLock.html#SuspiciousIdentityLock">SuspiciousIdentityLock lock = new SuspiciousIdentityLock( );
539 suspiciousIdentity.setLock( lock );
540 lock.setLockEndDate( daoUtil.getTimestamp( nIndex++ ) );
541 if ( lock.getLockEndDate( ) != null )
542 {
543 lock.setLocked( daoUtil.getBoolean( nIndex++ ) );
544 lock.setAuthorType( daoUtil.getString( nIndex++ ) );
545 lock.setAuthorName( daoUtil.getString( nIndex ) );
546 }
547 else
548 {
549 lock.setLocked( false );
550 }
551
552 suspiciousIdentityList.add( suspiciousIdentity );
553 }
554
555 daoUtil.free( );
556
557 }
558 }
559 return suspiciousIdentityList;
560
561 }
562
563 @Override
564 public void purge( Plugin plugin )
565 {
566 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_PURGE, plugin ) )
567 {
568 daoUtil.executeUpdate( );
569 }
570 }
571
572
573
574
575 @Override
576 public int countSuspiciousIdentities( final Plugin plugin )
577 {
578 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COUNT, plugin ) )
579 {
580 daoUtil.executeQuery( );
581 if ( daoUtil.next( ) )
582 {
583 return daoUtil.getInt( 1 );
584 }
585 }
586 return 0;
587 }
588
589
590
591
592 @Override
593 public int countSuspiciousIdentities( final int ruleId, final Plugin plugin )
594 {
595 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COUNT_BY_RULE_ID, plugin ) )
596 {
597 daoUtil.setInt( 1, ruleId );
598 daoUtil.executeQuery( );
599 if ( daoUtil.next( ) )
600 {
601 return daoUtil.getInt( 1 );
602 }
603 }
604 return 0;
605 }
606
607 @Override
608 public boolean checkIfExcluded( String firstCuid, String secondCuid, Plugin plugin )
609 {
610 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_EXCLUDED, plugin ) )
611 {
612 daoUtil.setString( 1, firstCuid );
613 daoUtil.setString( 2, secondCuid );
614 daoUtil.setString( 3, secondCuid );
615 daoUtil.setString( 4, firstCuid );
616 daoUtil.executeQuery( );
617
618 if ( daoUtil.next( ) )
619 {
620 return daoUtil.getInt( 1 ) > 0;
621 }
622
623 return false;
624 }
625 }
626
627 @Override
628 public boolean checkIfExcluded( String firstCuid, List<String> cuids, Plugin plugin )
629 {
630 if ( CollectionUtils.isEmpty( cuids ) )
631 {
632 return false;
633 }
634 final String critaeria = String.join( "', '", cuids );
635 String query = SQL_QUERY_CHECK_LIST_EXCLUDED;
636 query += "(first_customer_id = '" + firstCuid + "' AND second_customer_id IN ( '" + critaeria + "' )) OR (first_customer_id IN ('" + critaeria
637 + "') AND second_customer_id = '" + firstCuid + "')";
638
639 try ( final DAOUtil daoUtil = new DAOUtil( query, plugin ) )
640 {
641 daoUtil.executeQuery( );
642
643 if ( daoUtil.next( ) )
644 {
645 return daoUtil.getInt( 1 ) > 0;
646 }
647
648 return false;
649 }
650 }
651
652 @Override
653 public boolean checkIfContainsSuspicious( final List<String> customerIds, final Plugin plugin )
654 {
655 final String query = SQL_QUERY_CHECK_SUSPICIOUS + customerIds.stream( ).map( s -> String.format( "'%s'", s ) ).collect( Collectors.joining( "," ) )
656 + ")";
657 try ( final DAOUtil daoUtil = new DAOUtil( query, plugin ) )
658 {
659 daoUtil.executeQuery( );
660
661 if ( daoUtil.next( ) )
662 {
663 return daoUtil.getInt( 1 ) > 0;
664 }
665
666 return false;
667 }
668 }
669
670 @Override
671 public boolean manageLock( String customerId, boolean lock, String authorType, String authorName, Plugin plugin )
672 {
673 if ( lock )
674 {
675 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ADD_LOCK, plugin ) )
676 {
677 daoUtil.setString( 1, customerId );
678 daoUtil.setBoolean( 2, lock );
679 daoUtil.setTimestamp( 3, Timestamp.from( Instant.now( ).plusSeconds( 1800 ) ) );
680 daoUtil.setString( 4, authorType );
681 daoUtil.setString( 5, authorName );
682 daoUtil.executeUpdate( );
683 return true;
684 }
685 }
686 else
687 {
688 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_LOCK, plugin ) )
689 {
690 daoUtil.setString( 1, customerId );
691 daoUtil.executeUpdate( );
692 return false;
693 }
694 }
695 }
696
697 @Override
698 public void purgeLocks( Plugin plugin )
699 {
700 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_PURGE_LOCKS, plugin ) )
701 {
702 daoUtil.executeUpdate( );
703 }
704 }
705
706 @Override
707 public List<String> selectSuspiciousIdentityCuidsList( String ruleCode, Plugin plugin )
708 {
709 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_CUIDS, plugin ) )
710 {
711 daoUtil.setString( 1, ruleCode );
712 daoUtil.executeQuery( );
713 final List<String> cuids = new ArrayList<>( );
714
715 while ( daoUtil.next( ) )
716 {
717 cuids.add( daoUtil.getString( 1 ) );
718 }
719
720 return cuids;
721 }
722 }
723
724 @Override
725 public void removeExcludedIdentities( String firstCuid, String secondCuid, Plugin plugin )
726 {
727 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_EXCLUDED_IDENTITIES, plugin ) )
728 {
729 daoUtil.setString( 1, firstCuid );
730 daoUtil.setString( 2, secondCuid );
731 daoUtil.executeUpdate( );
732 }
733 }
734
735 @Override
736 public void removeExcludedIdentities( final String cuid, final Plugin plugin )
737 {
738 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_EXCLUDED_IDENTITIES_ONE_CUID, plugin ) )
739 {
740 daoUtil.setString( 1, cuid );
741 daoUtil.setString( 2, cuid );
742 daoUtil.executeUpdate( );
743 }
744 }
745 }