AdminUserFieldDAO.java

  1. /*
  2.  * Copyright (c) 2002-2022, City of Paris
  3.  * All rights reserved.
  4.  *
  5.  * Redistribution and use in source and binary forms, with or without
  6.  * modification, are permitted provided that the following conditions
  7.  * are met:
  8.  *
  9.  *  1. Redistributions of source code must retain the above copyright notice
  10.  *     and the following disclaimer.
  11.  *
  12.  *  2. Redistributions in binary form must reproduce the above copyright notice
  13.  *     and the following disclaimer in the documentation and/or other materials
  14.  *     provided with the distribution.
  15.  *
  16.  *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
  17.  *     contributors may be used to endorse or promote products derived from
  18.  *     this software without specific prior written permission.
  19.  *
  20.  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
  21.  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
  22.  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
  23.  * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
  24.  * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
  25.  * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
  26.  * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
  27.  * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
  28.  * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
  29.  * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
  30.  * POSSIBILITY OF SUCH DAMAGE.
  31.  *
  32.  * License 1.0
  33.  */
  34. package fr.paris.lutece.portal.business.user.attribute;

  36. import fr.paris.lutece.portal.business.file.File;
  37. import fr.paris.lutece.portal.business.user.AdminUser;
  38. import fr.paris.lutece.portal.service.util.AppLogService;
  39. import fr.paris.lutece.util.sql.DAOUtil;

  41. import java.sql.Statement;
  42. import java.util.ArrayList;
  43. import java.util.List;
  44. import java.util.Locale;

  46. import org.apache.commons.collections.CollectionUtils;

  48. /**
  49.  *
  50.  * AdminUserFieldDAO
  51.  *
  52.  */
  53. public class AdminUserFieldDAO implements IAdminUserFieldDAO
  54. {
  55.     // CONSTANTS
  56.     private static final String CONSTANT_PERCENT = "%";
  57.     private static final String CONSTANT_OPEN_BRACKET = "(";
  58.     private static final String CONSTANT_CLOSED_BRACKET = ")";

  60.     // SELECT
  61.     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, "
  62.             + " au.access_code, au.last_name, au.first_name, au.email, au.status, au.locale, au.level_user, "
  63.             + " a.type_class_name, a.title, a.help_message, a.is_mandatory, a.attribute_position, "
  64.             + " af.title, af.DEFAULT_value, af.is_DEFAULT_value, af.height, af.width, af.max_size_enter, af.is_multiple, af.field_position "
  65.             + " FROM core_admin_user_field auf " + " INNER JOIN core_admin_user au ON auf.id_user = au.id_user "
  66.             + " 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 ";
  67.     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, "
  68.             + " a.type_class_name, a.title, a.help_message, a.is_mandatory, a.attribute_position " + " FROM core_admin_user_field auf "
  69.             + " INNER JOIN core_attribute a ON a.id_attribute = auf.id_attribute " + " WHERE auf.id_user = ? AND auf.id_attribute = ? ";
  70.     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 "
  71.             + " FROM core_admin_user u INNER JOIN core_admin_user_field uf ON u.id_user = uf.id_user ";
  72.     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 ? ";
  73.     private static final String SQL_QUERY_EXISTS_WITH_FILE = " SELECT id_user_field from core_admin_user_field where id_file = ? ";

  75.     // INSERT
  76.     private static final String SQL_QUERY_INSERT = " INSERT INTO core_admin_user_field (id_user, id_attribute, id_field, id_file, user_field_value) "
  77.             + " VALUES (?,?,?,?,?) ";

  79.     // UPDATE
  80.     private static final String SQL_QUERY_UPDATE = " UPDATE core_admin_user_field SET user_field_value = ? WHERE id_user_field = ? ";

  82.     // DELETE
  83.     private static final String SQL_QUERY_DELETE = " DELETE FROM core_admin_user_field WHERE id_user_field = ? ";
  84.     private static final String SQL_QUERY_DELETE_FROM_ID_FIELD = " DELETE FROM core_admin_user_field WHERE id_field = ? ";
  85.     private static final String SQL_QUERY_DELETE_FROM_ID_USER = " DELETE FROM core_admin_user_field WHERE id_user = ? ";
  86.     private static final String SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE = " DELETE FROM core_admin_user_field WHERE id_attribute = ? ";

  88.     // FILTER
  89.     private static final String SQL_ID_ATTRIBUTE_AND_USER_FIELD_VALUE = " WHERE id_attribute = ? AND id_field = ? AND user_field_value LIKE ? ";
  90.     private static final String SQL_AND_ID_USER_IN = " AND id_user IN ";
  91.     private static final String SQL_AND_ID_USER_IN_FIRST = " AND uf.id_user IN ";
  92.     private static final String SQL_WHERE = " WHERE ";
  93.     private static final String SQL_AND = " AND ";
  94.     private static final String SQL_FILTER_ID_USER_FIELD = " WHERE auf.id_user_field = ? ";
  95.     private static final String SQL_FILTER_ID_USER = " auf.id_user = ? ";
  96.     private static final String SQL_FILTER_ID_ATTRIBUTE = " auf.id_attribute = ? ";
  97.     private static final String SQL_FILTER_ID_FIELD = " auf.id_field = ? ";

  99.     /**
  100.      * Load the user field
  101.      * 
  102.      * @param nIdUserField
  103.      *            ID
  104.      * @return AdminUserField
  105.      */
  106.     @Override
  107.     public AdminUserField load( int nIdUserField )
  108.     {
  109.         AdminUserField userField = null;
  110.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_WHERE + SQL_FILTER_ID_USER_FIELD ) )
  111.         {
  112.             daoUtil.setInt( 1, nIdUserField );
  113.             daoUtil.executeQuery( );

  115.             if ( daoUtil.next( ) )
  116.             {
  117.                 userField = dataToObject( daoUtil );
  118.             }

  120.         }

  122.         return userField;
  123.     }

  125.     /**
  126.      * Insert a new user field
  127.      * 
  128.      * @param userField
  129.      *            the user field
  130.      */
  131.     @Override
  132.     public void insert( AdminUserField userField )
  133.     {
  134.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
  135.         {
  136.             int nIndex = 1;
  137.             daoUtil.setInt( nIndex++, userField.getUser( ).getUserId( ) );
  138.             daoUtil.setInt( nIndex++, userField.getAttribute( ).getIdAttribute( ) );
  139.             daoUtil.setInt( nIndex++, userField.getAttributeField( ).getIdField( ) );

  141.             if ( userField.getFile( ) != null )
  142.             {
  143.                 daoUtil.setInt( nIndex++, userField.getFile( ).getIdFile( ) );
  144.             }
  145.             else
  146.             {
  147.                 daoUtil.setIntNull( nIndex++ );
  148.             }

  150.             daoUtil.setString( nIndex, userField.getValue( ) );

  152.             daoUtil.executeUpdate( );

  154.             if ( daoUtil.nextGeneratedKey( ) )
  155.             {
  156.                 userField.setIdUserField( daoUtil.getGeneratedKeyInt( 1 ) );
  157.             }
  158.         }
  159.     }

  161.     /**
  162.      * Update an user field
  163.      * 
  164.      * @param userField
  165.      *            the adminuser field
  166.      */
  167.     @Override
  168.     public void store( AdminUserField userField )
  169.     {
  170.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
  171.         {
  172.             daoUtil.setString( 1, userField.getValue( ) );
  173.             daoUtil.setInt( 2, userField.getIdUserField( ) );

  175.             daoUtil.executeUpdate( );
  176.         }
  177.     }

  179.     /**
  180.      * Delete an attribute
  181.      * 
  182.      * @param nIdUserField
  183.      *            the ID of the user field
  184.      */
  185.     @Override
  186.     public void delete( int nIdUserField )
  187.     {
  188.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
  189.         {
  190.             daoUtil.setInt( 1, nIdUserField );

  192.             daoUtil.executeUpdate( );
  193.         }
  194.     }

  196.     /**
  197.      * Delete all user fields from given id field
  198.      * 
  199.      * @param nIdField
  200.      *            id field
  201.      */
  202.     @Override
  203.     public void deleteUserFieldsFromIdField( int nIdField )
  204.     {
  205.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_FIELD ) )
  206.         {
  207.             daoUtil.setInt( 1, nIdField );

  209.             daoUtil.executeUpdate( );
  210.         }
  211.     }

  213.     /**
  214.      * Delete all user fields from given id user
  215.      * 
  216.      * @param nIdUser
  217.      *            id user
  218.      */
  219.     @Override
  220.     public void deleteUserFieldsFromIdUser( int nIdUser )
  221.     {
  222.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_USER ) )
  223.         {
  224.             daoUtil.setInt( 1, nIdUser );

  226.             daoUtil.executeUpdate( );
  227.         }
  228.     }

  230.     /**
  231.      * Delete all user fields from given id attribute
  232.      * 
  233.      * @param nIdAttribute
  234.      *            the id attribute
  235.      */
  236.     @Override
  237.     public void deleteUserFieldsFromIdAttribute( int nIdAttribute )
  238.     {
  239.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE ) )
  240.         {
  241.             daoUtil.setInt( 1, nIdAttribute );

  243.             daoUtil.executeUpdate( );
  244.         }
  245.     }

  247.     /**
  248.      * Load all the user field by a given ID user
  249.      * 
  250.      * @param nIdUser
  251.      *            the ID user
  252.      * @param nIdAttribute
  253.      *            the ID attribute
  254.      * @return a list of adminuserfield
  255.      */
  256.     @Override
  257.     public List<AdminUserField> selectUserFieldsByIdUserIdAttribute( int nIdUser, int nIdAttribute )
  258.     {
  259.         List<AdminUserField> listUserFields = new ArrayList<>( );
  260.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FIELDS_BY_ID_USER_ID_ATTRIBUTE ) )
  261.         {
  262.             daoUtil.setInt( 1, nIdUser );
  263.             daoUtil.setInt( 2, nIdAttribute );
  264.             daoUtil.executeQuery( );

  266.             while ( daoUtil.next( ) )
  267.             {
  268.                 AdminUserField userField = new AdminUserField( );
  269.                 userField.setIdUserField( daoUtil.getInt( 1 ) );
  270.                 userField.setValue( daoUtil.getString( 6 ) );

  272.                 // FILE
  273.                 if ( daoUtil.getObject( 5 ) != null ) // f.id_file
  274.                 {
  275.                     File file = new File( );
  276.                     file.setIdFile( daoUtil.getInt( 5 ) ); // f.id_file
  277.                     userField.setFile( file );
  278.                 }

  280.                 // USER
  281.                 AdminUser user = new AdminUser( );
  282.                 user.setUserId( nIdUser );
  283.                 userField.setUser( user );

  285.                 // ATTRIBUTE
  286.                 IAttribute attribute = null;

  288.                 try
  289.                 {
  290.                     attribute = (IAttribute) Class.forName( daoUtil.getString( 7 ) ).newInstance( );
  291.                 }
  292.                 catch( IllegalAccessException | InstantiationException | ClassNotFoundException e )
  293.                 {
  294.                     AppLogService.error( e );
  295.                 }

  297.                 if ( attribute != null )
  298.                 {
  299.                     attribute.setIdAttribute( nIdAttribute );
  300.                     attribute.setTitle( daoUtil.getString( 8 ) );
  301.                     attribute.setHelpMessage( daoUtil.getString( 9 ) );
  302.                     attribute.setMandatory( daoUtil.getBoolean( 10 ) );
  303.                     attribute.setPosition( daoUtil.getInt( 11 ) );
  304.                     userField.setAttribute( attribute );
  305.                 }
  306.                 // ATTRIBUTEFIELD
  307.                 AttributeField attributeField = new AttributeField( );
  308.                 attributeField.setIdField( daoUtil.getInt( 4 ) );
  309.                 userField.setAttributeField( attributeField );

  311.                 listUserFields.add( userField );
  312.             }

  314.         }

  316.         return listUserFields;
  317.     }

  319.     /**
  320.      * Load users by a given filter
  321.      * 
  322.      * @param auFieldFilter
  323.      *            the filter
  324.      * @return a list of users
  325.      */
  326.     @Override
  327.     public List<AdminUser> selectUsersByFilter( AdminUserFieldFilter auFieldFilter )
  328.     {
  329.         List<AdminUserField> listUserFields = auFieldFilter.getListUserFields( );

  331.         if ( CollectionUtils.isEmpty( listUserFields ) )
  332.         {
  333.             return null;
  334.         }

  336.         List<AdminUser> listUsers = new ArrayList<>( );
  337.         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_USERS_BY_FILTER );

  339.         for ( int i = 1; i <= listUserFields.size( ); i++ )
  340.         {
  341.             if ( i == 1 )
  342.             {
  343.                 sbSQL.append( SQL_ID_ATTRIBUTE_AND_USER_FIELD_VALUE );
  344.             }
  345.             else
  346.             {
  347.                 sbSQL.append( CONSTANT_OPEN_BRACKET + SQL_QUERY_SELECT_ID_USER );
  348.             }

  350.             if ( ( i != listUserFields.size( ) ) && ( i != 1 ) )
  351.             {
  352.                 sbSQL.append( SQL_AND_ID_USER_IN );
  353.             }
  354.             else
  355.                 if ( ( i != listUserFields.size( ) ) && ( i == 1 ) )
  356.                 {
  357.                     sbSQL.append( SQL_AND_ID_USER_IN_FIRST );
  358.                 }
  359.         }

  361.         for ( int i = 2; i <= listUserFields.size( ); i++ )
  362.         {
  363.             sbSQL.append( CONSTANT_CLOSED_BRACKET );
  364.         }

  366.         try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
  367.         {

  369.             int nbCount = 1;

  371.             for ( AdminUserField userField : listUserFields )
  372.             {
  373.                 daoUtil.setInt( nbCount++, userField.getAttribute( ).getIdAttribute( ) );
  374.                 daoUtil.setInt( nbCount++, userField.getAttributeField( ).getIdField( ) );
  375.                 daoUtil.setString( nbCount++, CONSTANT_PERCENT + userField.getValue( ) + CONSTANT_PERCENT );
  376.             }

  378.             daoUtil.executeQuery( );

  380.             while ( daoUtil.next( ) )
  381.             {
  382.                 AdminUser user = new AdminUser( );
  383.                 user.setUserId( daoUtil.getInt( 1 ) );
  384.                 user.setAccessCode( daoUtil.getString( 2 ) );
  385.                 user.setLastName( daoUtil.getString( 3 ) );
  386.                 user.setFirstName( daoUtil.getString( 4 ) );
  387.                 user.setEmail( daoUtil.getString( 5 ) );
  388.                 user.setStatus( daoUtil.getInt( 6 ) );

  390.                 Locale locale = new Locale( daoUtil.getString( 7 ) );
  391.                 user.setLocale( locale );
  392.                 listUsers.add( user );
  393.             }

  395.         }

  397.         return listUsers;
  398.     }

  400.     /**
  401.      * select by filter.
  402.      *
  403.      * @param auFieldFilter
  404.      *            the filter
  405.      * @return the list
  406.      */
  407.     @Override
  408.     public List<AdminUserField> selectByFilter( AdminUserFieldFilter auFieldFilter )
  409.     {
  410.         List<AdminUserField> listUserFields = new ArrayList<>( );
  411.         List<String> listFilter = new ArrayList<>( );

  413.         if ( auFieldFilter.containsIdAttribute( ) )
  414.         {
  415.             listFilter.add( SQL_FILTER_ID_ATTRIBUTE );
  416.         }

  418.         if ( auFieldFilter.containsIdUser( ) )
  419.         {
  420.             listFilter.add( SQL_FILTER_ID_USER );
  421.         }

  423.         if ( auFieldFilter.containsIdField( ) )
  424.         {
  425.             listFilter.add( SQL_FILTER_ID_FIELD );
  426.         }

  428.         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );

  430.         if ( CollectionUtils.isNotEmpty( listFilter ) )
  431.         {
  432.             boolean bIsFirst = true;

  434.             for ( String filter : listFilter )
  435.             {
  436.                 if ( bIsFirst )
  437.                 {
  438.                     sbSQL.append( SQL_WHERE );
  439.                     bIsFirst = false;
  440.                 }
  441.                 else
  442.                 {
  443.                     sbSQL.append( SQL_AND );
  444.                 }

  446.                 sbSQL.append( filter );
  447.             }
  448.         }

  450.         try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
  451.         {
  452.             int nIndex = 1;

  454.             if ( auFieldFilter.containsIdAttribute( ) )
  455.             {
  456.                 daoUtil.setInt( nIndex++, auFieldFilter.getIdAttribute( ) );
  457.             }

  459.             if ( auFieldFilter.containsIdUser( ) )
  460.             {
  461.                 daoUtil.setInt( nIndex++, auFieldFilter.getIdUser( ) );
  462.             }

  464.             if ( auFieldFilter.containsIdField( ) )
  465.             {
  466.                 daoUtil.setInt( nIndex++, auFieldFilter.getIdField( ) );
  467.             }

  469.             daoUtil.executeQuery( );

  471.             while ( daoUtil.next( ) )
  472.             {
  473.                 AdminUserField userField = dataToObject( daoUtil );
  474.                 listUserFields.add( userField );
  475.             }

  477.         }

  479.         return listUserFields;
  480.     }

  482.     /**
  483.      * {@inheritDoc}
  484.      */
  485.     @Override
  486.     public boolean existsWithFile( int nIdFile )
  487.     {
  488.         boolean result;
  489.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_EXISTS_WITH_FILE ) )
  490.         {
  491.             daoUtil.setInt( 1, nIdFile );
  492.             daoUtil.executeQuery( );
  493.             result = daoUtil.next( );
  494.         }

  496.         return result;
  497.     }

  499.     private AdminUserField dataToObject( DAOUtil daoUtil )
  500.     {
  501.         AdminUserField userField = new AdminUserField( );
  502.         userField.setIdUserField( daoUtil.getInt( 1 ) );
  503.         userField.setValue( daoUtil.getString( 6 ) );

  505.         // USER
  506.         AdminUser user = new AdminUser( );
  507.         user.setUserId( daoUtil.getInt( 2 ) );
  508.         user.setAccessCode( daoUtil.getString( 7 ) );
  509.         user.setLastName( daoUtil.getString( 8 ) );
  510.         user.setFirstName( daoUtil.getString( 9 ) );
  511.         user.setEmail( daoUtil.getString( 10 ) );
  512.         user.setStatus( daoUtil.getInt( 11 ) );
  513.         user.setLocale( new Locale( daoUtil.getString( 12 ) ) );
  514.         user.setUserLevel( daoUtil.getInt( 13 ) );
  515.         userField.setUser( user );

  517.         // ATTRIBUTE
  518.         IAttribute attribute = null;

  520.         try
  521.         {
  522.             attribute = (IAttribute) Class.forName( daoUtil.getString( 14 ) ).newInstance( );
  523.         }
  524.         catch( ClassNotFoundException | InstantiationException | IllegalAccessException e )
  525.         {
  526.             AppLogService.error( e );
  527.         }

  529.         if ( attribute != null )
  530.         {
  531.             attribute.setIdAttribute( daoUtil.getInt( 3 ) );
  532.             attribute.setTitle( daoUtil.getString( 15 ) );
  533.             attribute.setHelpMessage( daoUtil.getString( 16 ) );
  534.             attribute.setMandatory( daoUtil.getBoolean( 17 ) );
  535.             attribute.setPosition( daoUtil.getInt( 18 ) );
  536.             attribute.setAttributeType( new Locale( daoUtil.getString( 12 ) ) );
  537.             userField.setAttribute( attribute );
  538.         }
  539.         // ATTRIBUTEFIELD
  540.         // Here the attribute field may not exist (for example when
  541.         // using an non-mandatory combo box attribute). It will have
  542.         // and idField of 0.
  543.         // Use an empty object (with nulls, zeroes and false) because
  544.         // most of the code relies on this value beeing not null
  545.         // to access the idField.
  546.         AttributeField attributeField = new AttributeField( );
  547.         attributeField.setIdField( daoUtil.getInt( 4 ) );
  548.         attributeField.setTitle( daoUtil.getString( 19 ) );
  549.         attributeField.setValue( daoUtil.getString( 20 ) );
  550.         attributeField.setDefaultValue( daoUtil.getBoolean( 21 ) );
  551.         attributeField.setHeight( daoUtil.getInt( 22 ) );
  552.         attributeField.setWidth( daoUtil.getInt( 23 ) );
  553.         attributeField.setMaxSizeEnter( daoUtil.getInt( 24 ) );
  554.         attributeField.setMultiple( daoUtil.getBoolean( 25 ) );
  555.         attributeField.setPosition( daoUtil.getInt( 26 ) );
  556.         userField.setAttributeField( attributeField );

  558.         // FILE
  559.         if ( daoUtil.getObject( 5 ) != null ) // f.id_file
  560.         {
  561.             File file = new File( );
  562.             file.setIdFile( daoUtil.getInt( 5 ) ); // f.id_file
  563.             userField.setFile( file );
  564.         }

  566.         return userField;
  567.     }
  568. }
Created with JaCoCo 0.8.4.201905082037