AdminUserFieldDAO.java
/*
* Copyright (c) 2002-2022, City of Paris
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
* 1. Redistributions of source code must retain the above copyright notice
* and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright notice
* and the following disclaimer in the documentation and/or other materials
* provided with the distribution.
*
* 3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
* contributors may be used to endorse or promote products derived from
* this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* License 1.0
*/
package fr.paris.lutece.portal.business.user.attribute;
import fr.paris.lutece.portal.business.file.File;
import fr.paris.lutece.portal.business.user.AdminUser;
import fr.paris.lutece.portal.service.util.AppLogService;
import fr.paris.lutece.util.sql.DAOUtil;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import org.apache.commons.collections.CollectionUtils;
/**
*
* AdminUserFieldDAO
*
*/
public class AdminUserFieldDAO implements IAdminUserFieldDAO
{
// CONSTANTS
private static final String CONSTANT_PERCENT = "%";
private static final String CONSTANT_OPEN_BRACKET = "(";
private static final String CONSTANT_CLOSED_BRACKET = ")";
// SELECT
private static final String SQL_QUERY_SELECT = " SELECT auf.id_user_field, auf.id_user, auf.id_attribute, auf.id_field, auf.id_file, auf.user_field_value, "
+ " au.access_code, au.last_name, au.first_name, au.email, au.status, au.locale, au.level_user, "
+ " a.type_class_name, a.title, a.help_message, a.is_mandatory, a.attribute_position, "
+ " af.title, af.DEFAULT_value, af.is_DEFAULT_value, af.height, af.width, af.max_size_enter, af.is_multiple, af.field_position "
+ " FROM core_admin_user_field auf " + " INNER JOIN core_admin_user au ON auf.id_user = au.id_user "
+ " INNER JOIN core_attribute a ON auf.id_attribute = a.id_attribute " + " LEFT JOIN core_attribute_field af ON auf.id_field = af.id_field ";
private static final String SQL_QUERY_SELECT_USER_FIELDS_BY_ID_USER_ID_ATTRIBUTE = " SELECT auf.id_user_field, auf.id_user, auf.id_attribute, auf.id_field, auf.id_file, auf.user_field_value, "
+ " a.type_class_name, a.title, a.help_message, a.is_mandatory, a.attribute_position " + " FROM core_admin_user_field auf "
+ " INNER JOIN core_attribute a ON a.id_attribute = auf.id_attribute " + " WHERE auf.id_user = ? AND auf.id_attribute = ? ";
private static final String SQL_QUERY_SELECT_USERS_BY_FILTER = " SELECT DISTINCT u.id_user, u.access_code, u.last_name, u.first_name, u.email, u.status, u.locale, u.level_user "
+ " FROM core_admin_user u INNER JOIN core_admin_user_field uf ON u.id_user = uf.id_user ";
private static final String SQL_QUERY_SELECT_ID_USER = " SELECT id_user FROM core_admin_user_field WHERE id_attribute = ? AND id_field = ? AND user_field_value LIKE ? ";
private static final String SQL_QUERY_EXISTS_WITH_FILE = " SELECT id_user_field from core_admin_user_field where id_file = ? ";
// INSERT
private static final String SQL_QUERY_INSERT = " INSERT INTO core_admin_user_field (id_user, id_attribute, id_field, id_file, user_field_value) "
+ " VALUES (?,?,?,?,?) ";
// UPDATE
private static final String SQL_QUERY_UPDATE = " UPDATE core_admin_user_field SET user_field_value = ? WHERE id_user_field = ? ";
// DELETE
private static final String SQL_QUERY_DELETE = " DELETE FROM core_admin_user_field WHERE id_user_field = ? ";
private static final String SQL_QUERY_DELETE_FROM_ID_FIELD = " DELETE FROM core_admin_user_field WHERE id_field = ? ";
private static final String SQL_QUERY_DELETE_FROM_ID_USER = " DELETE FROM core_admin_user_field WHERE id_user = ? ";
private static final String SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE = " DELETE FROM core_admin_user_field WHERE id_attribute = ? ";
// FILTER
private static final String SQL_ID_ATTRIBUTE_AND_USER_FIELD_VALUE = " WHERE id_attribute = ? AND id_field = ? AND user_field_value LIKE ? ";
private static final String SQL_AND_ID_USER_IN = " AND id_user IN ";
private static final String SQL_AND_ID_USER_IN_FIRST = " AND uf.id_user IN ";
private static final String SQL_WHERE = " WHERE ";
private static final String SQL_AND = " AND ";
private static final String SQL_FILTER_ID_USER_FIELD = " WHERE auf.id_user_field = ? ";
private static final String SQL_FILTER_ID_USER = " auf.id_user = ? ";
private static final String SQL_FILTER_ID_ATTRIBUTE = " auf.id_attribute = ? ";
private static final String SQL_FILTER_ID_FIELD = " auf.id_field = ? ";
/**
* Load the user field
*
* @param nIdUserField
* ID
* @return AdminUserField
*/
@Override
public AdminUserField load( int nIdUserField )
{
AdminUserField userField = null;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_WHERE + SQL_FILTER_ID_USER_FIELD ) )
{
daoUtil.setInt( 1, nIdUserField );
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
userField = dataToObject( daoUtil );
}
}
return userField;
}
/**
* Insert a new user field
*
* @param userField
* the user field
*/
@Override
public void insert( AdminUserField userField )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
{
int nIndex = 1;
daoUtil.setInt( nIndex++, userField.getUser( ).getUserId( ) );
daoUtil.setInt( nIndex++, userField.getAttribute( ).getIdAttribute( ) );
daoUtil.setInt( nIndex++, userField.getAttributeField( ).getIdField( ) );
if ( userField.getFile( ) != null )
{
daoUtil.setInt( nIndex++, userField.getFile( ).getIdFile( ) );
}
else
{
daoUtil.setIntNull( nIndex++ );
}
daoUtil.setString( nIndex, userField.getValue( ) );
daoUtil.executeUpdate( );
if ( daoUtil.nextGeneratedKey( ) )
{
userField.setIdUserField( daoUtil.getGeneratedKeyInt( 1 ) );
}
}
}
/**
* Update an user field
*
* @param userField
* the adminuser field
*/
@Override
public void store( AdminUserField userField )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
{
daoUtil.setString( 1, userField.getValue( ) );
daoUtil.setInt( 2, userField.getIdUserField( ) );
daoUtil.executeUpdate( );
}
}
/**
* Delete an attribute
*
* @param nIdUserField
* the ID of the user field
*/
@Override
public void delete( int nIdUserField )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
{
daoUtil.setInt( 1, nIdUserField );
daoUtil.executeUpdate( );
}
}
/**
* Delete all user fields from given id field
*
* @param nIdField
* id field
*/
@Override
public void deleteUserFieldsFromIdField( int nIdField )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_FIELD ) )
{
daoUtil.setInt( 1, nIdField );
daoUtil.executeUpdate( );
}
}
/**
* Delete all user fields from given id user
*
* @param nIdUser
* id user
*/
@Override
public void deleteUserFieldsFromIdUser( int nIdUser )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_USER ) )
{
daoUtil.setInt( 1, nIdUser );
daoUtil.executeUpdate( );
}
}
/**
* Delete all user fields from given id attribute
*
* @param nIdAttribute
* the id attribute
*/
@Override
public void deleteUserFieldsFromIdAttribute( int nIdAttribute )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE ) )
{
daoUtil.setInt( 1, nIdAttribute );
daoUtil.executeUpdate( );
}
}
/**
* Load all the user field by a given ID user
*
* @param nIdUser
* the ID user
* @param nIdAttribute
* the ID attribute
* @return a list of adminuserfield
*/
@Override
public List<AdminUserField> selectUserFieldsByIdUserIdAttribute( int nIdUser, int nIdAttribute )
{
List<AdminUserField> listUserFields = new ArrayList<>( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FIELDS_BY_ID_USER_ID_ATTRIBUTE ) )
{
daoUtil.setInt( 1, nIdUser );
daoUtil.setInt( 2, nIdAttribute );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
AdminUserField userField = new AdminUserField( );
userField.setIdUserField( daoUtil.getInt( 1 ) );
userField.setValue( daoUtil.getString( 6 ) );
// FILE
if ( daoUtil.getObject( 5 ) != null ) // f.id_file
{
File file = new File( );
file.setIdFile( daoUtil.getInt( 5 ) ); // f.id_file
userField.setFile( file );
}
// USER
AdminUser user = new AdminUser( );
user.setUserId( nIdUser );
userField.setUser( user );
// ATTRIBUTE
IAttribute attribute = null;
try
{
attribute = (IAttribute) Class.forName( daoUtil.getString( 7 ) ).newInstance( );
}
catch( IllegalAccessException | InstantiationException | ClassNotFoundException e )
{
AppLogService.error( e );
}
if ( attribute != null )
{
attribute.setIdAttribute( nIdAttribute );
attribute.setTitle( daoUtil.getString( 8 ) );
attribute.setHelpMessage( daoUtil.getString( 9 ) );
attribute.setMandatory( daoUtil.getBoolean( 10 ) );
attribute.setPosition( daoUtil.getInt( 11 ) );
userField.setAttribute( attribute );
}
// ATTRIBUTEFIELD
AttributeField attributeField = new AttributeField( );
attributeField.setIdField( daoUtil.getInt( 4 ) );
userField.setAttributeField( attributeField );
listUserFields.add( userField );
}
}
return listUserFields;
}
/**
* Load users by a given filter
*
* @param auFieldFilter
* the filter
* @return a list of users
*/
@Override
public List<AdminUser> selectUsersByFilter( AdminUserFieldFilter auFieldFilter )
{
List<AdminUserField> listUserFields = auFieldFilter.getListUserFields( );
if ( CollectionUtils.isEmpty( listUserFields ) )
{
return null;
}
List<AdminUser> listUsers = new ArrayList<>( );
StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_USERS_BY_FILTER );
for ( int i = 1; i <= listUserFields.size( ); i++ )
{
if ( i == 1 )
{
sbSQL.append( SQL_ID_ATTRIBUTE_AND_USER_FIELD_VALUE );
}
else
{
sbSQL.append( CONSTANT_OPEN_BRACKET + SQL_QUERY_SELECT_ID_USER );
}
if ( ( i != listUserFields.size( ) ) && ( i != 1 ) )
{
sbSQL.append( SQL_AND_ID_USER_IN );
}
else
if ( ( i != listUserFields.size( ) ) && ( i == 1 ) )
{
sbSQL.append( SQL_AND_ID_USER_IN_FIRST );
}
}
for ( int i = 2; i <= listUserFields.size( ); i++ )
{
sbSQL.append( CONSTANT_CLOSED_BRACKET );
}
try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
{
int nbCount = 1;
for ( AdminUserField userField : listUserFields )
{
daoUtil.setInt( nbCount++, userField.getAttribute( ).getIdAttribute( ) );
daoUtil.setInt( nbCount++, userField.getAttributeField( ).getIdField( ) );
daoUtil.setString( nbCount++, CONSTANT_PERCENT + userField.getValue( ) + CONSTANT_PERCENT );
}
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
AdminUser user = new AdminUser( );
user.setUserId( daoUtil.getInt( 1 ) );
user.setAccessCode( daoUtil.getString( 2 ) );
user.setLastName( daoUtil.getString( 3 ) );
user.setFirstName( daoUtil.getString( 4 ) );
user.setEmail( daoUtil.getString( 5 ) );
user.setStatus( daoUtil.getInt( 6 ) );
Locale locale = new Locale( daoUtil.getString( 7 ) );
user.setLocale( locale );
listUsers.add( user );
}
}
return listUsers;
}
/**
* select by filter.
*
* @param auFieldFilter
* the filter
* @return the list
*/
@Override
public List<AdminUserField> selectByFilter( AdminUserFieldFilter auFieldFilter )
{
List<AdminUserField> listUserFields = new ArrayList<>( );
List<String> listFilter = new ArrayList<>( );
if ( auFieldFilter.containsIdAttribute( ) )
{
listFilter.add( SQL_FILTER_ID_ATTRIBUTE );
}
if ( auFieldFilter.containsIdUser( ) )
{
listFilter.add( SQL_FILTER_ID_USER );
}
if ( auFieldFilter.containsIdField( ) )
{
listFilter.add( SQL_FILTER_ID_FIELD );
}
StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );
if ( CollectionUtils.isNotEmpty( listFilter ) )
{
boolean bIsFirst = true;
for ( String filter : listFilter )
{
if ( bIsFirst )
{
sbSQL.append( SQL_WHERE );
bIsFirst = false;
}
else
{
sbSQL.append( SQL_AND );
}
sbSQL.append( filter );
}
}
try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
{
int nIndex = 1;
if ( auFieldFilter.containsIdAttribute( ) )
{
daoUtil.setInt( nIndex++, auFieldFilter.getIdAttribute( ) );
}
if ( auFieldFilter.containsIdUser( ) )
{
daoUtil.setInt( nIndex++, auFieldFilter.getIdUser( ) );
}
if ( auFieldFilter.containsIdField( ) )
{
daoUtil.setInt( nIndex++, auFieldFilter.getIdField( ) );
}
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
AdminUserField userField = dataToObject( daoUtil );
listUserFields.add( userField );
}
}
return listUserFields;
}
/**
* {@inheritDoc}
*/
@Override
public boolean existsWithFile( int nIdFile )
{
boolean result;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_EXISTS_WITH_FILE ) )
{
daoUtil.setInt( 1, nIdFile );
daoUtil.executeQuery( );
result = daoUtil.next( );
}
return result;
}
private AdminUserField dataToObject( DAOUtil daoUtil )
{
AdminUserField userField = new AdminUserField( );
userField.setIdUserField( daoUtil.getInt( 1 ) );
userField.setValue( daoUtil.getString( 6 ) );
// USER
AdminUser user = new AdminUser( );
user.setUserId( daoUtil.getInt( 2 ) );
user.setAccessCode( daoUtil.getString( 7 ) );
user.setLastName( daoUtil.getString( 8 ) );
user.setFirstName( daoUtil.getString( 9 ) );
user.setEmail( daoUtil.getString( 10 ) );
user.setStatus( daoUtil.getInt( 11 ) );
user.setLocale( new Locale( daoUtil.getString( 12 ) ) );
user.setUserLevel( daoUtil.getInt( 13 ) );
userField.setUser( user );
// ATTRIBUTE
IAttribute attribute = null;
try
{
attribute = (IAttribute) Class.forName( daoUtil.getString( 14 ) ).newInstance( );
}
catch( ClassNotFoundException | InstantiationException | IllegalAccessException e )
{
AppLogService.error( e );
}
if ( attribute != null )
{
attribute.setIdAttribute( daoUtil.getInt( 3 ) );
attribute.setTitle( daoUtil.getString( 15 ) );
attribute.setHelpMessage( daoUtil.getString( 16 ) );
attribute.setMandatory( daoUtil.getBoolean( 17 ) );
attribute.setPosition( daoUtil.getInt( 18 ) );
attribute.setAttributeType( new Locale( daoUtil.getString( 12 ) ) );
userField.setAttribute( attribute );
}
// ATTRIBUTEFIELD
// Here the attribute field may not exist (for example when
// using an non-mandatory combo box attribute). It will have
// and idField of 0.
// Use an empty object (with nulls, zeroes and false) because
// most of the code relies on this value beeing not null
// to access the idField.
AttributeField attributeField = new AttributeField( );
attributeField.setIdField( daoUtil.getInt( 4 ) );
attributeField.setTitle( daoUtil.getString( 19 ) );
attributeField.setValue( daoUtil.getString( 20 ) );
attributeField.setDefaultValue( daoUtil.getBoolean( 21 ) );
attributeField.setHeight( daoUtil.getInt( 22 ) );
attributeField.setWidth( daoUtil.getInt( 23 ) );
attributeField.setMaxSizeEnter( daoUtil.getInt( 24 ) );
attributeField.setMultiple( daoUtil.getBoolean( 25 ) );
attributeField.setPosition( daoUtil.getInt( 26 ) );
userField.setAttributeField( attributeField );
// FILE
if ( daoUtil.getObject( 5 ) != null ) // f.id_file
{
File file = new File( );
file.setIdFile( daoUtil.getInt( 5 ) ); // f.id_file
userField.setFile( file );
}
return userField;
}
}