AdminWorkgroupDAO.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.workgroup;

  36. import fr.paris.lutece.portal.business.user.AdminUser;
  37. import fr.paris.lutece.portal.business.user.AdminUserHome;
  38. import fr.paris.lutece.util.ReferenceList;
  39. import fr.paris.lutece.util.sql.DAOUtil;

  41. import java.util.ArrayList;
  42. import java.util.Collection;

  44. /**
  45.  * This class provides Data Access methods for AdminWorkgroup objects
  46.  */
  47. public final class AdminWorkgroupDAO implements IAdminWorkgroupDAO
  48. {
  49.     // Constants
  50.     private static final String CONSTANT_PERCENT = "%";
  51.     private static final String SQL_QUERY_SELECT = " SELECT workgroup_key, workgroup_description FROM core_admin_workgroup WHERE workgroup_key = ?  ";
  52.     private static final String SQL_QUERY_INSERT = " INSERT INTO core_admin_workgroup ( workgroup_key, workgroup_description ) VALUES ( ?, ? ) ";
  53.     private static final String SQL_QUERY_DELETE = " DELETE FROM core_admin_workgroup WHERE workgroup_key = ?  ";
  54.     private static final String SQL_QUERY_UPDATE = " UPDATE core_admin_workgroup SET workgroup_description = ? WHERE workgroup_key = ?  ";
  55.     private static final String SQL_QUERY_SELECTALL = " SELECT workgroup_key, workgroup_description FROM core_admin_workgroup ORDER BY workgroup_key";
  56.     private static final String SQL_QUERY_SELECT_USER_WORKGROUP = " SELECT workgroup_key FROM core_admin_workgroup_user WHERE id_user = ? AND workgroup_key = ? ";
  57.     private static final String SQL_QUERY_USER_IN_WORKGROUP = " SELECT id_user FROM core_admin_workgroup_user WHERE id_user = ? ";
  58.     private static final String SQL_QUERY_SELECT_USER_WORKGROUPS = " SELECT a.workgroup_key, a.workgroup_description "
  59.             + " FROM core_admin_workgroup a, core_admin_workgroup_user b " + " WHERE a.workgroup_key = b.workgroup_key AND b.id_user = ?  ";
  60.     private static final String SQL_QUERY_SELECT_USERS_LIST_FOR_WORKGROUP = " SELECT b.id_user " + " FROM core_admin_workgroup a, core_admin_workgroup_user b "
  61.             + " WHERE a.workgroup_key = b.workgroup_key AND a.workgroup_key = ?";
  62.     private static final String SQL_QUERY_DELETE_ALL_USERS_WORKGROUP = " DELETE FROM core_admin_workgroup_user WHERE workgroup_key = ?  ";
  63.     private static final String SQL_QUERY_INSERT_USER_WORKGROUP = " INSERT INTO core_admin_workgroup_user ( workgroup_key, id_user ) VALUES ( ?, ? ) ";
  64.     private static final String SQL_QUERY_DELETE_USER_FROM_WORKGROUP = " DELETE FROM core_admin_workgroup_user WHERE workgroup_key = ?  AND id_user = ?";
  65.     private static final String SQL_QUERY_SELECT_WORKGROUP_FROM_SEARCH = " SELECT workgroup_key, workgroup_description FROM core_admin_workgroup "
  66.             + " WHERE workgroup_key LIKE ? AND workgroup_description LIKE ? ORDER BY workgroup_key ";

  68.     /**
  69.      * {@inheritDoc}
  70.      */
  71.     public void insert( AdminWorkgroup workgroup )
  72.     {
  73.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
  74.         {
  75.             daoUtil.setString( 1, workgroup.getKey( ) );
  76.             daoUtil.setString( 2, workgroup.getDescription( ) );

  78.             daoUtil.executeUpdate( );
  79.         }
  80.     }

  82.     /**
  83.      * {@inheritDoc}
  84.      */
  85.     public AdminWorkgroup load( String strWorkgroupKey )
  86.     {
  87.         AdminWorkgroup workgroup = null;
  88.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
  89.         {
  90.             daoUtil.setString( 1, strWorkgroupKey );
  91.             daoUtil.executeQuery( );

  93.             if ( daoUtil.next( ) )
  94.             {
  95.                 workgroup = new AdminWorkgroup( );
  96.                 workgroup.setKey( daoUtil.getString( 1 ) );
  97.                 workgroup.setDescription( daoUtil.getString( 2 ) );
  98.             }

  100.         }

  102.         return workgroup;
  103.     }

  105.     /**
  106.      * {@inheritDoc}
  107.      */
  108.     public void delete( String strWorkgroupKey )
  109.     {
  110.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
  111.         {
  112.             daoUtil.setString( 1, strWorkgroupKey );

  114.             daoUtil.executeUpdate( );
  115.         }
  116.     }

  118.     /**
  119.      * {@inheritDoc}
  120.      */
  121.     public void store( AdminWorkgroup workgroup )
  122.     {
  123.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
  124.         {
  125.             daoUtil.setString( 1, workgroup.getDescription( ) );
  126.             daoUtil.setString( 2, workgroup.getKey( ) );

  128.             daoUtil.executeUpdate( );
  129.         }
  130.     }

  132.     /**
  133.      * {@inheritDoc}
  134.      */
  135.     public Collection<AdminWorkgroup> selectWorkgroupList( )
  136.     {
  137.         Collection<AdminWorkgroup> listWorkgroups = new ArrayList<>( );
  138.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL ) )
  139.         {
  140.             daoUtil.executeQuery( );

  142.             while ( daoUtil.next( ) )
  143.             {
  144.                 AdminWorkgroup workgroup = new AdminWorkgroup( );
  145.                 workgroup.setKey( daoUtil.getString( 1 ) );
  146.                 workgroup.setDescription( daoUtil.getString( 2 ) );

  148.                 listWorkgroups.add( workgroup );
  149.             }

  151.         }

  153.         return listWorkgroups;
  154.     }

  156.     /**
  157.      * {@inheritDoc}
  158.      */
  159.     public boolean checkExistWorkgroup( String strWorkgroupKey )
  160.     {
  161.         boolean check = false;
  162.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
  163.         {
  164.             daoUtil.setString( 1, strWorkgroupKey );
  165.             daoUtil.executeQuery( );

  167.             if ( daoUtil.next( ) )
  168.             {
  169.                 check = true;
  170.             }
  171.         }
  172.         return check;
  173.     }

  175.     /**
  176.      * {@inheritDoc}
  177.      */
  178.     public boolean isUserInWorkgroup( int nIdUser, String strWorkgroupKey )
  179.     {
  180.         boolean bInWorkgroup = false;
  181.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_WORKGROUP ) )
  182.         {
  183.             daoUtil.setInt( 1, nIdUser );
  184.             daoUtil.setString( 2, strWorkgroupKey );
  185.             daoUtil.executeQuery( );

  187.             if ( daoUtil.next( ) )
  188.             {
  189.                 bInWorkgroup = true;
  190.             }

  192.         }

  194.         return bInWorkgroup;
  195.     }

  197.     /**
  198.      * {@inheritDoc}
  199.      */
  200.     public boolean checkUserHasWorkgroup( int nIdUser )
  201.     {
  202.         boolean check = false;
  203.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_USER_IN_WORKGROUP ) )
  204.         {
  205.             daoUtil.setInt( 1, nIdUser );
  206.             daoUtil.executeQuery( );

  208.             if ( daoUtil.next( ) )
  209.             {
  210.                 check = true;
  211.             }
  212.         }
  213.         return check;
  214.     }

  216.     /**
  217.      * {@inheritDoc}
  218.      */
  219.     public ReferenceList getUserWorkgroups( int nIdUser )
  220.     {
  221.         ReferenceList listWorkgroups = new ReferenceList( );
  222.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_WORKGROUPS ) )
  223.         {
  224.             daoUtil.setInt( 1, nIdUser );
  225.             daoUtil.executeQuery( );

  227.             while ( daoUtil.next( ) )
  228.             {
  229.                 listWorkgroups.addItem( daoUtil.getString( 1 ), daoUtil.getString( 2 ) );
  230.             }

  232.         }

  234.         return listWorkgroups;
  235.     }

  237.     /**
  238.      * {@inheritDoc}
  239.      */
  240.     public Collection<AdminUser> getUsersListForWorkgroup( String strWorkgroupKey )
  241.     {
  242.         Collection<AdminUser> listUsers = new ArrayList<>( );
  243.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USERS_LIST_FOR_WORKGROUP ) )
  244.         {
  245.             daoUtil.setString( 1, strWorkgroupKey );
  246.             daoUtil.executeQuery( );

  248.             AdminUser adminUser = null;

  250.             while ( daoUtil.next( ) )
  251.             {
  252.                 adminUser = AdminUserHome.findByPrimaryKey( daoUtil.getInt( 1 ) );

  254.                 if ( adminUser != null )
  255.                 {
  256.                     listUsers.add( adminUser );
  257.                 }
  258.             }

  260.         }

  262.         return listUsers;
  263.     }

  265.     /**
  266.      * {@inheritDoc}
  267.      */
  268.     public void deleteAllUsersForWorkgroup( String strWorkgroupKey )
  269.     {
  270.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL_USERS_WORKGROUP ) )
  271.         {
  272.             daoUtil.setString( 1, strWorkgroupKey );
  273.             daoUtil.executeUpdate( );
  274.         }
  275.     }

  277.     /**
  278.      * {@inheritDoc}
  279.      */
  280.     public void insertUserForWorkgroup( AdminUser user, String strWorkgroupKey )
  281.     {
  282.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_USER_WORKGROUP ) )
  283.         {
  284.             daoUtil.setString( 1, strWorkgroupKey );
  285.             daoUtil.setInt( 2, user.getUserId( ) );
  286.             daoUtil.executeUpdate( );
  287.         }
  288.     }

  290.     /**
  291.      * {@inheritDoc}
  292.      */
  293.     public void deleteUserFromWorkgroup( int nUserId, String strWorkgroupKey )
  294.     {
  295.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_USER_FROM_WORKGROUP ) )
  296.         {
  297.             daoUtil.setString( 1, strWorkgroupKey );
  298.             daoUtil.setInt( 2, nUserId );
  299.             daoUtil.executeUpdate( );
  300.         }
  301.     }

  303.     /**
  304.      * Find workgroups from a filter
  305.      * 
  306.      * @param awFilter
  307.      *            the filter
  308.      * @return the list of workgroups
  309.      */
  310.     public Collection<AdminWorkgroup> selectWorkgroupsByFilter( AdminWorkgroupFilter awFilter )
  311.     {
  312.         Collection<AdminWorkgroup> listFilteredWorkgroups = new ArrayList<>( );
  313.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_WORKGROUP_FROM_SEARCH ) )
  314.         {

  316.             daoUtil.setString( 1, CONSTANT_PERCENT + awFilter.getKey( ) + CONSTANT_PERCENT );
  317.             daoUtil.setString( 2, CONSTANT_PERCENT + awFilter.getDescription( ) + CONSTANT_PERCENT );

  319.             daoUtil.executeQuery( );

  321.             while ( daoUtil.next( ) )
  322.             {
  323.                 AdminWorkgroup workgroup = new AdminWorkgroup( );
  324.                 workgroup.setKey( daoUtil.getString( 1 ) );
  325.                 workgroup.setDescription( daoUtil.getString( 2 ) );

  327.                 listFilteredWorkgroups.add( workgroup );
  328.             }

  330.         }

  332.         return listFilteredWorkgroups;
  333.     }
  334. }
Created with JaCoCo 0.8.4.201905082037