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;

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

  39. import java.sql.Statement;
  40. import java.util.ArrayList;
  41. import java.util.List;
  42. import java.util.Locale;

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

  44. /**
  45.  *
  46.  * AdminUserFieldDAO
  47.  *
  48.  */
  49. public class AdminUserFieldDAO implements IAdminUserFieldDAO
  50. {
  51.     // CONSTANTS
  52.     private static final String CONSTANT_PERCENT = "%";
  53.     private static final String CONSTANT_OPEN_BRACKET = "(";
  54.     private static final String CONSTANT_CLOSED_BRACKET = ")";

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

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

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

  74.     // DELETE
  75.     private static final String SQL_QUERY_DELETE = " DELETE FROM core_admin_user_field WHERE id_user_field = ? ";
  76.     private static final String SQL_QUERY_DELETE_FROM_ID_FIELD = " DELETE FROM core_admin_user_field WHERE id_field = ? ";
  77.     private static final String SQL_QUERY_DELETE_FROM_ID_USER = " DELETE FROM core_admin_user_field WHERE id_user = ? ";
  78.     private static final String SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE = " DELETE FROM core_admin_user_field WHERE id_attribute = ? ";

  79.     // FILTER
  80.     private static final String SQL_ID_ATTRIBUTE_AND_USER_FIELD_VALUE = " WHERE id_attribute = ? AND id_field = ? AND user_field_value LIKE ? ";
  81.     private static final String SQL_AND_ID_USER_IN = " AND id_user IN ";
  82.     private static final String SQL_AND_ID_USER_IN_FIRST = " AND uf.id_user IN ";
  83.     private static final String SQL_WHERE = " WHERE ";
  84.     private static final String SQL_AND = " AND ";
  85.     private static final String SQL_FILTER_ID_USER_FIELD = " WHERE auf.id_user_field = ? ";
  86.     private static final String SQL_FILTER_ID_USER = " auf.id_user = ? ";
  87.     private static final String SQL_FILTER_ID_ATTRIBUTE = " auf.id_attribute = ? ";
  88.     private static final String SQL_FILTER_ID_FIELD = " auf.id_field = ? ";

  89.     /**
  90.      * Load the user field
  91.      *
  92.      * @param nIdUserField
  93.      *            ID
  94.      * @return AdminUserField
  95.      */
  96.     @Override
  97.     public AdminUserField load( int nIdUserField )
  98.     {
  99.         AdminUserField userField = null;
  100.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_WHERE + SQL_FILTER_ID_USER_FIELD ) )
  101.         {
  102.             daoUtil.setInt( 1, nIdUserField );
  103.             daoUtil.executeQuery( );

  104.             if ( daoUtil.next( ) )
  105.             {
  106.                 userField = dataToObject( daoUtil );
  107.             }

  108.         }

  109.         return userField;
  110.     }

  111.     /**
  112.      * Insert a new user field
  113.      *
  114.      * @param userField
  115.      *            the user field
  116.      */
  117.     @Override
  118.     public void insert( AdminUserField userField )
  119.     {
  120.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
  121.         {
  122.             int nIndex = 1;
  123.             daoUtil.setInt( nIndex++, userField.getUser( ).getUserId( ) );
  124.             daoUtil.setInt( nIndex++, userField.getAttribute( ).getIdAttribute( ) );
  125.             daoUtil.setInt( nIndex++, userField.getAttributeField( ).getIdField( ) );

  126.             if ( userField.getFile( ) != null )
  127.             {
  128.                 daoUtil.setInt( nIndex++, userField.getFile( ).getIdFile( ) );
  129.             }
  130.             else
  131.             {
  132.                 daoUtil.setIntNull( nIndex++ );
  133.             }

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

  135.             daoUtil.executeUpdate( );

  136.             if ( daoUtil.nextGeneratedKey( ) )
  137.             {
  138.                 userField.setIdUserField( daoUtil.getGeneratedKeyInt( 1 ) );
  139.             }
  140.         }
  141.     }

  142.     /**
  143.      * Update an user field
  144.      *
  145.      * @param userField
  146.      *            the adminuser field
  147.      */
  148.     @Override
  149.     public void store( AdminUserField userField )
  150.     {
  151.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
  152.         {
  153.             daoUtil.setString( 1, userField.getValue( ) );
  154.             daoUtil.setInt( 2, userField.getIdUserField( ) );

  155.             daoUtil.executeUpdate( );
  156.         }
  157.     }

  158.     /**
  159.      * Delete an attribute
  160.      *
  161.      * @param nIdUserField
  162.      *            the ID of the user field
  163.      */
  164.     @Override
  165.     public void delete( int nIdUserField )
  166.     {
  167.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
  168.         {
  169.             daoUtil.setInt( 1, nIdUserField );

  170.             daoUtil.executeUpdate( );
  171.         }
  172.     }

  173.     /**
  174.      * Delete all user fields from given id field
  175.      *
  176.      * @param nIdField
  177.      *            id field
  178.      */
  179.     @Override
  180.     public void deleteUserFieldsFromIdField( int nIdField )
  181.     {
  182.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_FIELD ) )
  183.         {
  184.             daoUtil.setInt( 1, nIdField );

  185.             daoUtil.executeUpdate( );
  186.         }
  187.     }

  188.     /**
  189.      * Delete all user fields from given id user
  190.      *
  191.      * @param nIdUser
  192.      *            id user
  193.      */
  194.     @Override
  195.     public void deleteUserFieldsFromIdUser( int nIdUser )
  196.     {
  197.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_USER ) )
  198.         {
  199.             daoUtil.setInt( 1, nIdUser );

  200.             daoUtil.executeUpdate( );
  201.         }
  202.     }

  203.     /**
  204.      * Delete all user fields from given id attribute
  205.      *
  206.      * @param nIdAttribute
  207.      *            the id attribute
  208.      */
  209.     @Override
  210.     public void deleteUserFieldsFromIdAttribute( int nIdAttribute )
  211.     {
  212.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE ) )
  213.         {
  214.             daoUtil.setInt( 1, nIdAttribute );

  215.             daoUtil.executeUpdate( );
  216.         }
  217.     }

  218.     /**
  219.      * Load all the user field by a given ID user
  220.      *
  221.      * @param nIdUser
  222.      *            the ID user
  223.      * @param nIdAttribute
  224.      *            the ID attribute
  225.      * @return a list of adminuserfield
  226.      */
  227.     @Override
  228.     public List<AdminUserField> selectUserFieldsByIdUserIdAttribute( int nIdUser, int nIdAttribute )
  229.     {
  230.         List<AdminUserField> listUserFields = new ArrayList<>( );
  231.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FIELDS_BY_ID_USER_ID_ATTRIBUTE ) )
  232.         {
  233.             daoUtil.setInt( 1, nIdUser );
  234.             daoUtil.setInt( 2, nIdAttribute );
  235.             daoUtil.executeQuery( );

  236.             while ( daoUtil.next( ) )
  237.             {
  238.                 AdminUserField userField = new AdminUserField( );
  239.                 userField.setIdUserField( daoUtil.getInt( 1 ) );
  240.                 userField.setValue( daoUtil.getString( 6 ) );

  241.                 // FILE
  242.                 if ( daoUtil.getObject( 5 ) != null ) // f.id_file
  243.                 {
  244.                     File file = new File( );
  245.                     file.setIdFile( daoUtil.getInt( 5 ) ); // f.id_file
  246.                     userField.setFile( file );
  247.                 }

  248.                 // USER
  249.                 AdminUser user = new AdminUser( );
  250.                 user.setUserId( nIdUser );
  251.                 userField.setUser( user );

  252.                 // ATTRIBUTE
  253.                 IAttribute attribute = null;

  254.                 try
  255.                 {
  256.                     attribute = (IAttribute) Class.forName( daoUtil.getString( 7 ) ).newInstance( );
  257.                 }
  258.                 catch( IllegalAccessException | InstantiationException | ClassNotFoundException e )
  259.                 {
  260.                     AppLogService.error( e );
  261.                 }

  262.                 if ( attribute != null )
  263.                 {
  264.                     attribute.setIdAttribute( nIdAttribute );
  265.                     attribute.setTitle( daoUtil.getString( 8 ) );
  266.                     attribute.setHelpMessage( daoUtil.getString( 9 ) );
  267.                     attribute.setMandatory( daoUtil.getBoolean( 10 ) );
  268.                     attribute.setPosition( daoUtil.getInt( 11 ) );
  269.                     userField.setAttribute( attribute );
  270.                 }
  271.                 // ATTRIBUTEFIELD
  272.                 AttributeField attributeField = new AttributeField( );
  273.                 attributeField.setIdField( daoUtil.getInt( 4 ) );
  274.                 userField.setAttributeField( attributeField );

  275.                 listUserFields.add( userField );
  276.             }

  277.         }

  278.         return listUserFields;
  279.     }

  280.     /**
  281.      * Load users by a given filter
  282.      *
  283.      * @param auFieldFilter
  284.      *            the filter
  285.      * @return a list of users
  286.      */
  287.     @Override
  288.     public List<AdminUser> selectUsersByFilter( AdminUserFieldFilter auFieldFilter )
  289.     {
  290.         List<AdminUserField> listUserFields = auFieldFilter.getListUserFields( );

  291.         if ( CollectionUtils.isEmpty( listUserFields ) )
  292.         {
  293.             return null;
  294.         }

  295.         List<AdminUser> listUsers = new ArrayList<>( );
  296.         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_USERS_BY_FILTER );

  297.         for ( int i = 1; i <= listUserFields.size( ); i++ )
  298.         {
  299.             if ( i == 1 )
  300.             {
  301.                 sbSQL.append( SQL_ID_ATTRIBUTE_AND_USER_FIELD_VALUE );
  302.             }
  303.             else
  304.             {
  305.                 sbSQL.append( CONSTANT_OPEN_BRACKET + SQL_QUERY_SELECT_ID_USER );
  306.             }

  307.             if ( ( i != listUserFields.size( ) ) && ( i != 1 ) )
  308.             {
  309.                 sbSQL.append( SQL_AND_ID_USER_IN );
  310.             }
  311.             else
  312.                 if ( ( i != listUserFields.size( ) ) && ( i == 1 ) )
  313.                 {
  314.                     sbSQL.append( SQL_AND_ID_USER_IN_FIRST );
  315.                 }
  316.         }

  317.         for ( int i = 2; i <= listUserFields.size( ); i++ )
  318.         {
  319.             sbSQL.append( CONSTANT_CLOSED_BRACKET );
  320.         }

  321.         try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
  322.         {

  323.             int nbCount = 1;

  324.             for ( AdminUserField userField : listUserFields )
  325.             {
  326.                 daoUtil.setInt( nbCount++, userField.getAttribute( ).getIdAttribute( ) );
  327.                 daoUtil.setInt( nbCount++, userField.getAttributeField( ).getIdField( ) );
  328.                 daoUtil.setString( nbCount++, CONSTANT_PERCENT + userField.getValue( ) + CONSTANT_PERCENT );
  329.             }

  330.             daoUtil.executeQuery( );

  331.             while ( daoUtil.next( ) )
  332.             {
  333.                 AdminUser user = new AdminUser( );
  334.                 user.setUserId( daoUtil.getInt( 1 ) );
  335.                 user.setAccessCode( daoUtil.getString( 2 ) );
  336.                 user.setLastName( daoUtil.getString( 3 ) );
  337.                 user.setFirstName( daoUtil.getString( 4 ) );
  338.                 user.setEmail( daoUtil.getString( 5 ) );
  339.                 user.setStatus( daoUtil.getInt( 6 ) );

  340.                 Locale locale = new Locale( daoUtil.getString( 7 ) );
  341.                 user.setLocale( locale );
  342.                 listUsers.add( user );
  343.             }

  344.         }

  345.         return listUsers;
  346.     }

  347.     /**
  348.      * select by filter.
  349.      *
  350.      * @param auFieldFilter
  351.      *            the filter
  352.      * @return the list
  353.      */
  354.     @Override
  355.     public List<AdminUserField> selectByFilter( AdminUserFieldFilter auFieldFilter )
  356.     {
  357.         List<AdminUserField> listUserFields = new ArrayList<>( );
  358.         List<String> listFilter = new ArrayList<>( );

  359.         if ( auFieldFilter.containsIdAttribute( ) )
  360.         {
  361.             listFilter.add( SQL_FILTER_ID_ATTRIBUTE );
  362.         }

  363.         if ( auFieldFilter.containsIdUser( ) )
  364.         {
  365.             listFilter.add( SQL_FILTER_ID_USER );
  366.         }

  367.         if ( auFieldFilter.containsIdField( ) )
  368.         {
  369.             listFilter.add( SQL_FILTER_ID_FIELD );
  370.         }

  371.         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );

  372.         if ( CollectionUtils.isNotEmpty( listFilter ) )
  373.         {
  374.             boolean bIsFirst = true;

  375.             for ( String filter : listFilter )
  376.             {
  377.                 if ( bIsFirst )
  378.                 {
  379.                     sbSQL.append( SQL_WHERE );
  380.                     bIsFirst = false;
  381.                 }
  382.                 else
  383.                 {
  384.                     sbSQL.append( SQL_AND );
  385.                 }

  386.                 sbSQL.append( filter );
  387.             }
  388.         }

  389.         try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
  390.         {
  391.             int nIndex = 1;

  392.             if ( auFieldFilter.containsIdAttribute( ) )
  393.             {
  394.                 daoUtil.setInt( nIndex++, auFieldFilter.getIdAttribute( ) );
  395.             }

  396.             if ( auFieldFilter.containsIdUser( ) )
  397.             {
  398.                 daoUtil.setInt( nIndex++, auFieldFilter.getIdUser( ) );
  399.             }

  400.             if ( auFieldFilter.containsIdField( ) )
  401.             {
  402.                 daoUtil.setInt( nIndex++, auFieldFilter.getIdField( ) );
  403.             }

  404.             daoUtil.executeQuery( );

  405.             while ( daoUtil.next( ) )
  406.             {
  407.                 AdminUserField userField = dataToObject( daoUtil );
  408.                 listUserFields.add( userField );
  409.             }

  410.         }

  411.         return listUserFields;
  412.     }

  413.     /**
  414.      * {@inheritDoc}
  415.      */
  416.     @Override
  417.     public boolean existsWithFile( int nIdFile )
  418.     {
  419.         boolean result;
  420.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_EXISTS_WITH_FILE ) )
  421.         {
  422.             daoUtil.setInt( 1, nIdFile );
  423.             daoUtil.executeQuery( );
  424.             result = daoUtil.next( );
  425.         }

  426.         return result;
  427.     }

  428.     private AdminUserField dataToObject( DAOUtil daoUtil )
  429.     {
  430.         AdminUserField userField = new AdminUserField( );
  431.         userField.setIdUserField( daoUtil.getInt( 1 ) );
  432.         userField.setValue( daoUtil.getString( 6 ) );

  433.         // USER
  434.         AdminUser user = new AdminUser( );
  435.         user.setUserId( daoUtil.getInt( 2 ) );
  436.         user.setAccessCode( daoUtil.getString( 7 ) );
  437.         user.setLastName( daoUtil.getString( 8 ) );
  438.         user.setFirstName( daoUtil.getString( 9 ) );
  439.         user.setEmail( daoUtil.getString( 10 ) );
  440.         user.setStatus( daoUtil.getInt( 11 ) );
  441.         user.setLocale( new Locale( daoUtil.getString( 12 ) ) );
  442.         user.setUserLevel( daoUtil.getInt( 13 ) );
  443.         userField.setUser( user );

  444.         // ATTRIBUTE
  445.         IAttribute attribute = null;

  446.         try
  447.         {
  448.             attribute = (IAttribute) Class.forName( daoUtil.getString( 14 ) ).newInstance( );
  449.         }
  450.         catch( ClassNotFoundException | InstantiationException | IllegalAccessException e )
  451.         {
  452.             AppLogService.error( e );
  453.         }

  454.         if ( attribute != null )
  455.         {
  456.             attribute.setIdAttribute( daoUtil.getInt( 3 ) );
  457.             attribute.setTitle( daoUtil.getString( 15 ) );
  458.             attribute.setHelpMessage( daoUtil.getString( 16 ) );
  459.             attribute.setMandatory( daoUtil.getBoolean( 17 ) );
  460.             attribute.setPosition( daoUtil.getInt( 18 ) );
  461.             attribute.setAttributeType( new Locale( daoUtil.getString( 12 ) ) );
  462.             userField.setAttribute( attribute );
  463.         }
  464.         // ATTRIBUTEFIELD
  465.         // Here the attribute field may not exist (for example when
  466.         // using an non-mandatory combo box attribute). It will have
  467.         // and idField of 0.
  468.         // Use an empty object (with nulls, zeroes and false) because
  469.         // most of the code relies on this value beeing not null
  470.         // to access the idField.
  471.         AttributeField attributeField = new AttributeField( );
  472.         attributeField.setIdField( daoUtil.getInt( 4 ) );
  473.         attributeField.setTitle( daoUtil.getString( 19 ) );
  474.         attributeField.setValue( daoUtil.getString( 20 ) );
  475.         attributeField.setDefaultValue( daoUtil.getBoolean( 21 ) );
  476.         attributeField.setHeight( daoUtil.getInt( 22 ) );
  477.         attributeField.setWidth( daoUtil.getInt( 23 ) );
  478.         attributeField.setMaxSizeEnter( daoUtil.getInt( 24 ) );
  479.         attributeField.setMultiple( daoUtil.getBoolean( 25 ) );
  480.         attributeField.setPosition( daoUtil.getInt( 26 ) );
  481.         userField.setAttributeField( attributeField );

  482.         // FILE
  483.         if ( daoUtil.getObject( 5 ) != null ) // f.id_file
  484.         {
  485.             File file = new File( );
  486.             file.setIdFile( daoUtil.getInt( 5 ) ); // f.id_file
  487.             userField.setFile( file );
  488.         }

  489.         return userField;
  490.     }
  491. }