View Javadoc
1   /*
2    * Copyright (c) 2002-2020, 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.plugins.document.business.spaces;
35  
36  import fr.paris.lutece.portal.service.i18n.I18nService;
37  import fr.paris.lutece.util.ReferenceList;
38  import fr.paris.lutece.util.sql.DAOUtil;
39  
40  import java.util.ArrayList;
41  import java.util.List;
42  import java.util.Locale;
43  
44  
45  /**
46   * This class provides Data Access methods for DocumentSpace objects
47   */
48  public final class DocumentSpaceDAO implements IDocumentSpaceDAO
49  {
50      // Constants
51      private static final String SQL_QUERY_NEW_PK = " SELECT max( id_space ) FROM document_space ";
52      private static final String SQL_QUERY_SELECT = " SELECT a.id_space, a.id_parent, a.document_space_name, a.description, a.document_space_view, a.id_space_icon, b.icon_url, a.document_creation_allowed, a.workgroup_key " +
53          " FROM document_space a, document_space_icon b " +
54          " WHERE a.id_space_icon = b.id_space_icon AND id_space = ? ";
55      private static final String SQL_QUERY_INSERT = " INSERT INTO document_space ( id_space, id_parent, document_space_name, description, document_space_view, id_space_icon, document_creation_allowed ,workgroup_key ) VALUES ( ?, ?, ?, ?, ?, ?, ? ,?) ";
56      private static final String SQL_QUERY_DELETE = " DELETE FROM document_space WHERE id_space = ?  ";
57      private static final String SQL_QUERY_UPDATE = " UPDATE document_space SET id_space = ?, id_parent = ?, document_space_name = ?, description = ?, document_space_view = ?, id_space_icon = ?, document_creation_allowed = ? ,workgroup_key= ? WHERE id_space = ?  ";
58      private static final String SQL_QUERY_SELECT_CHILDS = " SELECT a.id_space, a.id_parent, a.document_space_name, a.description, a.document_space_view, a.id_space_icon, b.icon_url, a.document_creation_allowed ,a.workgroup_key" +
59          " FROM document_space a, document_space_icon b WHERE a.id_space_icon = b.id_space_icon AND id_parent = ? ORDER BY a.document_space_name ";
60      private static final String SQL_QUERY_SELECT_CHILDS_BY_CODE_TYPE = " SELECT a.id_space, a.id_parent, a.document_space_name, a.description, a.document_space_view, a.id_space_icon, b.icon_url, a.document_creation_allowed ,a.workgroup_key" +
61          " FROM document_space a, document_space_icon b, document_space_document_type c " +
62          " WHERE a.id_space_icon = b.id_space_icon AND id_parent = ? AND a.id_space = c.id_space AND c.code_document_type = ?" +
63          " ORDER BY a.document_space_name ";
64      private static final String SQL_QUERY_SELECT_SPACES_WITH_DOCUMENT_CREATION_IS_ALLOWED_BY_CODE_TYPE = "SELECT a.id_space, a.id_parent, a.document_space_name, a.description, a.document_space_view, a.id_space_icon, b.icon_url, a.document_creation_allowed ,a.workgroup_key" +
65          " FROM document_space a, document_space_icon b, document_space_document_type c " +
66          " WHERE a.id_space_icon = b.id_space_icon AND a.id_space = c.id_space AND c.code_document_type = ? AND a.document_creation_allowed = ?" +
67          " ORDER BY a.document_space_name ";
68      private static final String SQL_QUERY_SELECTALL = " SELECT a.id_space, a.id_parent, a.document_space_name, a.description, a.document_space_view, a.id_space_icon, b.icon_url, a.document_creation_allowed, a.workgroup_key " +
69          " FROM document_space a, document_space_icon b WHERE a.id_space_icon = b.id_space_icon ORDER BY a.document_space_name ";
70      private static final String SQL_QUERY_SELECTALL_VIEWTYPE = " SELECT code_view , name_key FROM document_view";
71      private static final String SQL_QUERY_SELECTALL_ICONS = " SELECT id_space_icon , icon_url FROM document_space_icon";
72      private static final String SQL_QUERY_INSERT_DOCUMENT_TYPE = "INSERT INTO document_space_document_type ( id_space , code_document_type ) VALUES ( ? , ? )";
73      private static final String SQL_QUERY_DELETE_DOCUMENT_TYPE = " DELETE FROM document_space_document_type WHERE id_space = ?  ";
74      private static final String SQL_QUERY_SELECT_DOCUMENT_TYPE = " SELECT code_document_type FROM document_space_document_type WHERE id_space = ?  ";
75      private static final String SQL_QUERY_SELECT_SPACE_DOCUMENT_TYPE = " SELECT a.code_document_type, a.document_type_name " +
76          " FROM document_type a, document_space_document_type b " +
77          " WHERE a.code_document_type = b.code_document_type AND b.id_space = ?" + " ORDER BY a.document_type_name";
78  
79      /**
80       * Generates a new primary key
81       * @return The new primary key
82       */
83      private int newPrimaryKey(  )
84      {
85          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK );
86          daoUtil.executeQuery(  );
87  
88          int nKey;
89  
90          if ( !daoUtil.next(  ) )
91          {
92              // if the table is empty
93              nKey = 1;
94          }
95  
96          nKey = daoUtil.getInt( 1 ) + 1;
97  
98          daoUtil.free(  );
99  
100         return nKey;
101     }
102 
103     /**
104      * Insert a new record in the table.
105      *
106      * @param space The space object
107      */
108     public synchronized void insert( DocumentSpace space )
109     {
110         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
111         space.setId( newPrimaryKey(  ) );
112         daoUtil.setInt( 1, space.getId(  ) );
113         daoUtil.setInt( 2, space.getIdParent(  ) );
114         daoUtil.setString( 3, space.getName(  ) );
115         daoUtil.setString( 4, space.getDescription(  ) );
116         daoUtil.setString( 5, space.getViewType(  ) );
117         daoUtil.setInt( 6, space.getIdIcon(  ) );
118         daoUtil.setInt( 7, space.isDocumentCreationAllowed(  ) ? 1 : 0 );
119         daoUtil.setString( 8, space.getWorkgroup(  ) );
120         daoUtil.executeUpdate(  );
121         daoUtil.free(  );
122 
123         // insert allowed document types
124         insertAllowedDocumenTypes( space );
125     }
126 
127     /**
128      * Insert allowed document types to a space
129      * @param space The space
130      */
131     private void insertAllowedDocumenTypes( DocumentSpace space )
132     {
133         String[] doctypes = space.getAllowedDocumentTypes(  );
134 
135         for ( int i = 0; i < doctypes.length; i++ )
136         {
137             DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_DOCUMENT_TYPE );
138             daoUtil.setInt( 1, space.getId(  ) );
139             daoUtil.setString( 2, doctypes[i] );
140 
141             daoUtil.executeUpdate(  );
142             daoUtil.free(  );
143         }
144     }
145 
146     /**
147      * Load the data of DocumentSpace from the table
148      *
149      * @param nDocumentSpaceId The identifier of DocumentSpace
150      * @return the instance of the DocumentSpace
151      */
152     public DocumentSpace load( int nDocumentSpaceId )
153     {
154         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
155         daoUtil.setInt( 1, nDocumentSpaceId );
156         daoUtil.executeQuery(  );
157 
158         DocumentSpace space = null;
159 
160         if ( daoUtil.next(  ) )
161         {
162             space = new DocumentSpace(  );
163             space.setId( daoUtil.getInt( 1 ) );
164             space.setIdParent( daoUtil.getInt( 2 ) );
165             space.setName( daoUtil.getString( 3 ) );
166             space.setDescription( daoUtil.getString( 4 ) );
167             space.setViewType( daoUtil.getString( 5 ) );
168             space.setIdIcon( daoUtil.getInt( 6 ) );
169             space.setIconUrl( daoUtil.getString( 7 ) );
170             space.setDocumentCreationAllowed( daoUtil.getInt( 8 ) != 0 );
171             space.setWorkgroup( daoUtil.getString( 9 ) );
172         }
173 
174         daoUtil.free(  );
175 
176         if ( space != null )
177         {
178             loadAllowedDocumentTypes( space );
179         }
180 
181         return space;
182     }
183 
184     /**
185      * Load allowed document types for a space
186      * @param space  The space
187      */
188     private void loadAllowedDocumentTypes( DocumentSpace space )
189     {
190         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DOCUMENT_TYPE );
191         daoUtil.setInt( 1, space.getId(  ) );
192         daoUtil.executeQuery(  );
193 
194         while ( daoUtil.next(  ) )
195         {
196             space.addAllowedDocumentType( daoUtil.getString( 1 ) );
197         }
198 
199         daoUtil.free(  );
200     }
201 
202     /**
203      * Delete a record from the table
204      *
205      * @param nSpaceId The Id to delete
206      */
207     public void delete( int nSpaceId )
208     {
209         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
210         daoUtil.setInt( 1, nSpaceId );
211         daoUtil.executeUpdate(  );
212 
213         daoUtil.free(  );
214         deleteAllowedDocumentTypes( nSpaceId );
215     }
216 
217     /**
218      * Delete allowed document types
219      * @param nSpaceId The space identifier
220      */
221     private void deleteAllowedDocumentTypes( int nSpaceId )
222     {
223         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_DOCUMENT_TYPE );
224         daoUtil.setInt( 1, nSpaceId );
225 
226         daoUtil.executeUpdate(  );
227         daoUtil.free(  );
228     }
229 
230     /**
231      * Update the record in the table
232      * @param space The reference of space
233      */
234     public void store( DocumentSpace space )
235     {
236         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
237         daoUtil.setInt( 1, space.getId(  ) );
238         daoUtil.setInt( 2, space.getIdParent(  ) );
239         daoUtil.setString( 3, space.getName(  ) );
240         daoUtil.setString( 4, space.getDescription(  ) );
241         daoUtil.setString( 5, space.getViewType(  ) );
242         daoUtil.setInt( 6, space.getIdIcon(  ) );
243         daoUtil.setInt( 7, space.isDocumentCreationAllowed(  ) ? 1 : 0 );
244         daoUtil.setString( 8, space.getWorkgroup(  ) );
245         daoUtil.setInt( 9, space.getId(  ) );
246 
247         daoUtil.executeUpdate(  );
248         daoUtil.free(  );
249 
250         deleteAllowedDocumentTypes( space.getId(  ) );
251         insertAllowedDocumenTypes( space );
252     }
253 
254     /**
255      * Load the list of documentSpaces childs
256      *
257      * @param strCodeType the document type filter if needed (null if not)
258      * @param nSpaceId The space identifier
259      * @return The Collection of the DocumentSpaces
260      */
261     public List<DocumentSpace> selectChilds( int nSpaceId, String strCodeType )
262     {
263         List<DocumentSpace> listDocumentSpaces = new ArrayList<DocumentSpace>(  );
264         DAOUtil daoUtil = null;
265 
266         if ( strCodeType != null )
267         {
268             daoUtil = new DAOUtil( SQL_QUERY_SELECT_CHILDS_BY_CODE_TYPE );
269             daoUtil.setInt( 1, nSpaceId );
270             daoUtil.setString( 2, strCodeType );
271         }
272         else
273         {
274             daoUtil = new DAOUtil( SQL_QUERY_SELECT_CHILDS );
275             daoUtil.setInt( 1, nSpaceId );
276         }
277 
278         daoUtil.executeQuery(  );
279 
280         while ( daoUtil.next(  ) )
281         {
282             DocumentSpaceocument/business/spaces/DocumentSpace.html#DocumentSpace">DocumentSpace space = new DocumentSpace(  );
283             space.setId( daoUtil.getInt( 1 ) );
284             space.setIdParent( daoUtil.getInt( 2 ) );
285             space.setName( daoUtil.getString( 3 ) );
286             space.setDescription( daoUtil.getString( 4 ) );
287             space.setViewType( daoUtil.getString( 5 ) );
288             space.setIdIcon( daoUtil.getInt( 6 ) );
289             space.setIconUrl( daoUtil.getString( 7 ) );
290             space.setDocumentCreationAllowed( daoUtil.getInt( 8 ) != 0 );
291             space.setWorkgroup( daoUtil.getString( 9 ) );
292             listDocumentSpaces.add( space );
293         }
294 
295         daoUtil.free(  );
296 
297         return listDocumentSpaces;
298     }
299 
300     /**
301      * Load the list of documentSpaces authorizing the selected document type
302      *
303      * @param strCodeType the document type filter
304      * @return The Collection of the DocumentSpaces
305      */
306     public List<DocumentSpace> selectSpacesAllowingDocumentCreationByDocumentType( String strCodeType,
307         int createDocumentIsAllowed )
308     {
309         List<DocumentSpace> listDocumentSpaces = new ArrayList<DocumentSpace>(  );
310         DAOUtil daoUtil = null;
311 
312         daoUtil = new DAOUtil( SQL_QUERY_SELECT_SPACES_WITH_DOCUMENT_CREATION_IS_ALLOWED_BY_CODE_TYPE );
313         daoUtil.setString( 1, strCodeType );
314         daoUtil.setInt( 2, createDocumentIsAllowed );
315 
316         daoUtil.executeQuery(  );
317 
318         while ( daoUtil.next(  ) )
319         {
320             DocumentSpaceocument/business/spaces/DocumentSpace.html#DocumentSpace">DocumentSpace space = new DocumentSpace(  );
321             space.setId( daoUtil.getInt( 1 ) );
322             space.setIdParent( daoUtil.getInt( 2 ) );
323             space.setName( daoUtil.getString( 3 ) );
324             space.setDescription( daoUtil.getString( 4 ) );
325             space.setViewType( daoUtil.getString( 5 ) );
326             space.setIdIcon( daoUtil.getInt( 6 ) );
327             space.setIconUrl( daoUtil.getString( 7 ) );
328             space.setDocumentCreationAllowed( daoUtil.getInt( 8 ) != 0 );
329             space.setWorkgroup( daoUtil.getString( 9 ) );
330             listDocumentSpaces.add( space );
331         }
332 
333         daoUtil.free(  );
334 
335         return listDocumentSpaces;
336     }
337 
338     /**
339      * Load the list of documentSpaces
340      * @return The Collection of the DocumentSpaces
341      */
342     public ReferenceList getDocumentSpaceList(  )
343     {
344         ReferenceList list = new ReferenceList(  );
345         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL );
346         daoUtil.executeQuery(  );
347 
348         while ( daoUtil.next(  ) )
349         {
350             DocumentSpaceocument/business/spaces/DocumentSpace.html#DocumentSpace">DocumentSpace space = new DocumentSpace(  );
351             space.setId( daoUtil.getInt( 1 ) );
352             space.setName( daoUtil.getString( 3 ) );
353 
354             list.addItem( space.getId(  ), space.getName(  ) );
355         }
356 
357         daoUtil.free(  );
358 
359         return list;
360     }
361 
362     /**
363      * Load the list of documentSpaces
364      * @param locale The locale
365      * @return The Collection of the DocumentSpaces
366      */
367     public ReferenceList getViewTypeList( Locale locale )
368     {
369         ReferenceList list = new ReferenceList(  );
370         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_VIEWTYPE );
371         daoUtil.executeQuery(  );
372 
373         while ( daoUtil.next(  ) )
374         {
375             String strCodeView = daoUtil.getString( 1 );
376             String strViewNameKey = daoUtil.getString( 2 );
377             list.addItem( strCodeView, I18nService.getLocalizedString( strViewNameKey, locale ) );
378         }
379 
380         daoUtil.free(  );
381 
382         return list;
383     }
384 
385     /**
386      * Gets a list of icons available or space customization
387      * @return A list of icons
388      */
389     public ReferenceList getIconsList(  )
390     {
391         ReferenceList list = new ReferenceList(  );
392         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ICONS );
393         daoUtil.executeQuery(  );
394 
395         while ( daoUtil.next(  ) )
396         {
397             int nIconId = daoUtil.getInt( 1 );
398             String strIconUrl = daoUtil.getString( 2 );
399             list.addItem( nIconId, strIconUrl );
400         }
401 
402         daoUtil.free(  );
403 
404         return list;
405     }
406 
407     /**
408      * Select all spaces
409      * @return A collection of all spaces.
410      */
411     public List<DocumentSpace> selectAll(  )
412     {
413         List<DocumentSpace> listDocumentSpaces = new ArrayList<DocumentSpace>(  );
414         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL );
415         daoUtil.executeQuery(  );
416 
417         while ( daoUtil.next(  ) )
418         {
419             DocumentSpaceocument/business/spaces/DocumentSpace.html#DocumentSpace">DocumentSpace space = new DocumentSpace(  );
420             space.setId( daoUtil.getInt( 1 ) );
421             space.setIdParent( daoUtil.getInt( 2 ) );
422             space.setName( daoUtil.getString( 3 ) );
423             space.setDescription( daoUtil.getString( 4 ) );
424             space.setViewType( daoUtil.getString( 5 ) );
425             space.setIdIcon( daoUtil.getInt( 6 ) );
426             space.setIconUrl( daoUtil.getString( 7 ) );
427             space.setDocumentCreationAllowed( daoUtil.getInt( 8 ) != 0 );
428             space.setWorkgroup( daoUtil.getString( 9 ) );
429             listDocumentSpaces.add( space );
430         }
431 
432         daoUtil.free(  );
433 
434         return listDocumentSpaces;
435     }
436 
437     /**
438      * Returns all allowed document types for a given space
439      * @param nSpaceId The space Id
440      * @return Allowed documents types as a ReferenceList
441      */
442     public ReferenceList getAllowedDocumentTypes( int nSpaceId )
443     {
444         ReferenceList list = new ReferenceList(  );
445         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_SPACE_DOCUMENT_TYPE );
446         daoUtil.setInt( 1, nSpaceId );
447         daoUtil.executeQuery(  );
448 
449         while ( daoUtil.next(  ) )
450         {
451             list.addItem( daoUtil.getString( 1 ), daoUtil.getString( 2 ) );
452         }
453 
454         daoUtil.free(  );
455 
456         return list;
457     }
458 }