View Javadoc
1   /*
2    * Copyright (c) 2002-2023, 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;
35  
36  import fr.paris.lutece.plugins.document.business.attributes.DocumentAttribute;
37  import fr.paris.lutece.plugins.document.business.category.Category;
38  import fr.paris.lutece.plugins.document.business.workflow.DocumentState;
39  import fr.paris.lutece.portal.service.util.AppLogService;
40  import fr.paris.lutece.util.sql.DAOUtil;
41  
42  import org.apache.commons.lang3.BooleanUtils;
43  import org.apache.commons.lang3.StringUtils;
44  
45  import java.util.ArrayList;
46  import java.util.Collection;
47  import java.util.List;
48  
49  /**
50   * This class provides Data Access methods for Document objects
51   */
52  public final class DocumentDAO implements IDocumentDAO
53  {
54      // Documents queries
55      private static final String SQL_QUERY_NEW_PK = " SELECT max( id_document ) FROM document ";
56      private static final String SQL_QUERY_SELECT = " SELECT a.id_document, a.code_document_type, a.title, a.date_creation, "
57              + " a.date_modification, a.xml_working_content, a.xml_validated_content, a.id_space , b.document_space_name , "
58              + " a.id_state , c.name_key, d.document_type_name , a.document_summary, a.document_comment , a.date_validity_begin,"
59              + " a.date_validity_end , a.xml_metadata , a.id_creator, a.id_mailinglist, a.id_page_template_document, a.skip_portlet, a.skip_categories FROM document a,"
60              + " document_space b, document_workflow_state c, document_type d WHERE a.id_space = b.id_space AND a.id_state = c.id_state"
61              + " AND a.code_document_type = d.code_document_type AND a.id_document = ?  ";
62      private static final String SQL_QUERY_SELECT_FROM_SPACE_ID = " SELECT a.id_document, a.title, a.document_summary"
63              + " FROM document a WHERE a.id_space = ?  ";
64      private static final String SQL_QUERY_INSERT = " INSERT INTO document ( id_document, code_document_type, title, date_creation, "
65              + " date_modification, xml_working_content, xml_validated_content, id_space, id_state	, document_summary, document_comment , "
66              + " date_validity_begin , date_validity_end , xml_metadata , id_creator, "
67              + " id_mailinglist, id_page_template_document, skip_portlet, skip_categories ) "
68              + " VALUES ( ?, ?, ?, ?, ?, ?, ? ,?, ?, ?, ?, ?, ? ,?, ?, ?, ?, ?, ? ) ";
69      private static final String SQL_QUERY_DELETE = " DELETE FROM document WHERE id_document = ?  ";
70      private static final String SQL_QUERY_UPDATE = " UPDATE document SET id_document = ?, "
71              + " code_document_type = ?, title = ?, date_creation = ?, date_modification = ?, xml_working_content = ?, "
72              + " xml_validated_content = ?, id_space = ?, id_state = ? , document_summary = ?, document_comment = ? , date_validity_begin = ? , date_validity_end = ? , "
73              + " xml_metadata = ? , id_creator = ?, id_mailinglist = ?, id_page_template_document = ?, skip_portlet = ?, skip_categories = ? "
74              + " WHERE id_document = ?  ";
75      private static final String SQL_QUERY_SELECT_PRIMARY_KEY_BY_FILTER = " SELECT DISTINCT a.id_document, a.date_modification FROM document a "
76              + " INNER JOIN document_space b ON a.id_space = b.id_space " + " INNER JOIN document_workflow_state c ON a.id_state = c.id_state "
77              + " INNER JOIN document_type d ON a.code_document_type = d.code_document_type "
78              + " LEFT OUTER JOIN document_category_link f ON a.id_document = f.id_document ";
79      private static final String SQL_QUERY_SELECT_BY_FILTER = " SELECT DISTINCT a.id_document, a.code_document_type, a.title, "
80              + " a.date_creation, a.date_modification, a.xml_working_content, a.xml_validated_content, a.id_space , b.document_space_name , "
81              + " a.id_state , c.name_key , d.document_type_name ,  a.document_summary, a.document_comment , a.date_validity_begin , a.date_validity_end , "
82              + " a.xml_metadata , a.id_creator, a.id_mailinglist , a.id_page_template_document, a.skip_portlet, a.skip_categories FROM document a "
83              + " INNER JOIN document_space b ON a.id_space = b.id_space " + " INNER JOIN document_workflow_state c ON a.id_state = c.id_state "
84              + " INNER JOIN document_type d ON a.code_document_type = d.code_document_type "
85              + " LEFT OUTER JOIN document_category_link f ON a.id_document = f.id_document ";
86      private static final String SQL_QUERY_SELECT_LAST_MODIFIED_DOCUMENT_FROM_USER = " SELECT a.id_document, a.code_document_type, a.title, a.date_creation, "
87              + " a.date_modification, a.xml_working_content, a.xml_validated_content, a.id_space , b.document_space_name , "
88              + " a.id_state , c.name_key, d.document_type_name , a.document_summary, a.document_comment , a.date_validity_begin , a.date_validity_end , "
89              + " a.xml_metadata , a.id_creator, a.id_mailinglist, a.id_page_template_document, a.skip_portlet, a.skip_categories FROM document a"
90              + " INNER JOIN document_space b ON a.id_space = b.id_space" + " INNER JOIN document_workflow_state c ON a.id_state = c.id_state"
91              + " INNER JOIN document_type d ON a.code_document_type = d.code_document_type " + " INNER JOIN document_history e ON a.id_document = e.id_document "
92              + " WHERE e.event_user = ? ORDER BY e.event_date DESC LIMIT 1 ";
93      private static final String SQL_QUERY_SELECT_LAST_PUBLISHED_DOCUMENT = " SELECT a.id_document, a.code_document_type, a.title, a.date_creation, "
94              + " a.date_modification, a.xml_working_content, a.xml_validated_content, a.id_space , b.document_space_name , "
95              + " a.id_state , c.name_key, d.document_type_name , a.document_summary, a.document_comment , a.date_validity_begin , a.date_validity_end , "
96              + " a.xml_metadata , a.id_creator, a.id_mailinglist, a.id_page_template_document, a.skip_portlet, a.skip_categories FROM document a"
97              + " INNER JOIN document_space b ON a.id_space = b.id_space" + " INNER JOIN document_workflow_state c ON a.id_state = c.id_state"
98              + " INNER JOIN document_type d ON a.code_document_type = d.code_document_type "
99              + " INNER JOIN document_published e ON a.id_document = e.id_document " + " ORDER BY e.date_publishing DESC LIMIT 1 ";
100     private static final String SQL_FILTER_WHERE_CLAUSE = " WHERE ";
101     private static final String SQL_FILTER_AND = " AND ";
102     private static final String SQL_FILTER_DOCUMENT_TYPE = " a.code_document_type = ? ";
103     private static final String SQL_FILTER_SPACE = " a.id_space = ? ";
104     private static final String SQL_FILTER_STATE = " a.id_state = ? ";
105     private static final String SQL_FILTER_CATEGORIES_BEGIN = " (";
106     private static final String SQL_FILTER_CATEGORIES = " f.id_category = ? ";
107     private static final String SQL_FILTER_CATEGORIES_NULL = " f.id_category IS NULL ";
108     private static final String SQL_FILTER_CATEGORIES_OR = " OR ";
109     private static final String SQL_FILTER_CATEGORIES_END = ") ";
110     private static final String SQL_FILTER_ID_BEGIN = " (";
111     private static final String SQL_FILTER_ID = " a.id_document = ? ";
112     private static final String SQL_FILTER_ID_OR = " OR ";
113     private static final String SQL_FILTER_ID_END = ") ";
114     private static final String SQL_ORDER_BY_LAST_MODIFICATION = " ORDER BY a.date_modification DESC ";
115 
116     // Select only primary keys
117     private static final String SQL_QUERY_SELECT_PRIMARY_KEYS = " SELECT a.id_document FROM document a ";
118     private static final String SQL_QUERY_DELETE_DOCUMENT_HISTORY = "DELETE FROM document_history WHERE id_document = ?  ";
119 
120     // Document attributes queries
121     private static final String SQL_QUERY_SELECT_ATTRIBUTES = "SELECT c.id_document_attr , c.code , c.code_attr_type , "
122             + "c.code_document_type , c.document_type_attr_name, c.description, c.attr_order, c.required, c.searchable , "
123             + "b.text_value, b.mime_type , b.binary_value " + "FROM document a, document_content b, document_type_attr c "
124             + " WHERE a.code_document_type = c.code_document_type " + " AND a.id_document = b.id_document  "
125             + " AND b.id_document_attr = c.id_document_attr AND a.id_document = ? ";
126     private static final String SQL_QUERY_SELECT_ATTRIBUTES_WITHOUT_BINARIES = "SELECT c.id_document_attr , c.code , c.code_attr_type , "
127             + "c.code_document_type , c.document_type_attr_name, c.description, c.attr_order, c.required, c.searchable , " + "b.text_value, b.mime_type "
128             + "FROM document a, document_content b, document_type_attr c " + " WHERE a.code_document_type = c.code_document_type "
129             + " AND a.id_document = b.id_document  " + " AND b.id_document_attr = c.id_document_attr " + " AND a.id_document = ? AND b.validated = ?";
130     private static final String SQL_QUERY_INSERT_ATTRIBUTE = "INSERT INTO document_content (id_document ,  id_document_attr , text_value , binary_value, mime_type, validated ) VALUES ( ? , ? , ? , ? , ? , ?)";
131     private static final String SQL_QUERY_DELETE_ATTRIBUTES = "DELETE FROM document_content WHERE id_document = ? and validated = ? ";
132     private static final String SQL_QUERY_VALIDATE_ATTRIBUTES = "UPDATE document_content SET validated = ? WHERE id_document = ?";
133 
134     // Resources queries
135     private static final String SQL_QUERY_SELECT_DOCUMENT_SPECIFIC_RESOURCE = " SELECT binary_value , mime_type , text_value FROM document_content WHERE id_document = ? AND id_document_attr = ? and validated = ?";
136     private static final String SQL_QUERY_SELECT_DOCUMENT_RESOURCE = "SELECT a.binary_value , a.mime_type, a.text_value FROM document_content a, document b, document_type c WHERE a.id_document = ? "
137             + " AND a.id_document_attr = c.thumbnail_attr_id " + " AND a.id_document = b.id_document " + " AND b.code_document_type = c.code_document_type ";
138     private static final String SQL_QUERY_SELECT_PAGE_TEMPLATE_PATH = " SELECT page_template_path FROM document_page_template " + " "
139             + " WHERE id_page_template_document =  ? ";
140     private static final String SQL_QUERY_SELECTALL_CATEGORY = " SELECT a.id_category, a.document_category_name, a.description, a.icon_content, a.icon_mime_type FROM document_category a, document_category_link b WHERE a.id_category=b.id_category AND b.id_document = ? ORDER BY document_category_name";
141     private static final String SQL_QUERY_DELETE_LINKS_DOCUMENT = " DELETE FROM document_category_link WHERE id_document = ? ";
142     private static final String SQL_QUERY_INSERT_LINK_CATEGORY_DOCUMENT = " INSERT INTO document_category_link ( id_category, id_document ) VALUES ( ?, ? )";
143     private static final String SQL_QUERY_LAST_MODIFIED = "SELECT d.code_document_type, d.date_modification FROM document d WHERE d.id_document = ?";
144     private static final String SQL_QUERY_SELECT_RELATED_CATEGORY = "SELECT DISTINCT a.id_document, a.code_document_type, a.title, a.date_creation, "
145             + " a.date_modification, a.xml_working_content, a.xml_validated_content, a.id_space , b.document_space_name , "
146             + " a.id_state , c.name_key, d.document_type_name , a.document_summary, a.document_comment , a.date_validity_begin , a.date_validity_end , "
147             + " a.xml_metadata , a.id_creator, a.id_mailinglist, a.id_page_template_document, a.skip_portlet, a.skip_categories FROM document a "
148             + " INNER JOIN document_space b ON a.id_space = b.id_space " + " INNER JOIN document_workflow_state c ON a.id_state = c.id_state "
149             + " INNER JOIN document_type d ON a.code_document_type = d.code_document_type "
150             + " LEFT OUTER JOIN document_category_link f ON a.id_document = f.id_document "
151             + " WHERE f.id_category IN ( SELECT g.id_category FROM document_category_link g WHERE g.id_document = ?) ";
152 
153     /**
154      * Generates a new primary key
155      * 
156      * @return The new primary key
157      */
158     public int newPrimaryKey( )
159     {
160         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK );
161         daoUtil.executeQuery( );
162 
163         int nKey;
164 
165         if ( !daoUtil.next( ) )
166         {
167             // if the table is empty
168             nKey = 1;
169         }
170 
171         nKey = daoUtil.getInt( 1 ) + 1;
172         daoUtil.free( );
173 
174         return nKey;
175     }
176 
177     /**
178      * Insert a new record in the table.
179      *
180      * @param document
181      *            The document object
182      */
183     public synchronized void insert( Document document )
184     {
185         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
186         daoUtil.setInt( 1, document.getId( ) );
187         daoUtil.setString( 2, document.getCodeDocumentType( ) );
188         daoUtil.setString( 3, document.getTitle( ) );
189         daoUtil.setTimestamp( 4, document.getDateCreation( ) );
190         daoUtil.setTimestamp( 5, document.getDateModification( ) );
191         daoUtil.setString( 6, document.getXmlWorkingContent( ) );
192         daoUtil.setString( 7, document.getXmlValidatedContent( ) );
193         daoUtil.setInt( 8, document.getSpaceId( ) );
194         daoUtil.setInt( 9, document.getStateId( ) );
195         daoUtil.setString( 10, document.getSummary( ) );
196         daoUtil.setString( 11, document.getComment( ) );
197         daoUtil.setTimestamp( 12, document.getDateValidityBegin( ) );
198         daoUtil.setTimestamp( 13, document.getDateValidityEnd( ) );
199         daoUtil.setString( 14, document.getXmlMetadata( ) );
200         daoUtil.setInt( 15, document.getCreatorId( ) );
201         daoUtil.setInt( 16, document.getMailingListId( ) );
202         daoUtil.setInt( 17, document.getPageTemplateDocumentId( ) );
203         daoUtil.setBoolean( 18, document.isSkipPortlet( ) );
204         daoUtil.setBoolean( 19, document.isSkipCategories( ) );
205 
206         daoUtil.executeUpdate( );
207         daoUtil.free( );
208         insertAttributes( document );
209         insertCategories( document.getCategories( ), document.getId( ) );
210     }
211 
212     /**
213      * Insert attributes
214      * 
215      * @param document
216      *            The document object
217      */
218     private void insertAttributes( Document document )
219     {
220         List<DocumentAttribute> listAttributes = document.getAttributes( );
221 
222         for ( DocumentAttribute attribute : listAttributes )
223         {
224             insertAttribute( document.getId( ), attribute );
225         }
226     }
227 
228     /**
229      *
230      * @param nDocumentId
231      *            the document identifier
232      * @param attribute
233      *            The DocumentAttribute object
234      */
235     private void insertAttribute( int nDocumentId, DocumentAttribute attribute )
236     {
237         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_ATTRIBUTE );
238         daoUtil.setInt( 1, nDocumentId );
239         daoUtil.setInt( 2, attribute.getId( ) );
240 
241         if ( attribute.isBinary( ) )
242         {
243             // File attribute, save content type and data in the binary column
244             daoUtil.setString( 3, attribute.getTextValue( ) );
245             daoUtil.setBytes( 4, attribute.getBinaryValue( ) );
246             daoUtil.setString( 5, attribute.getValueContentType( ) );
247         }
248         else
249         {
250             // Text attribute, no content type and save data in the text column
251             daoUtil.setString( 3, attribute.getTextValue( ) );
252 
253             daoUtil.setBytes( 4, null );
254             daoUtil.setString( 5, StringUtils.EMPTY );
255         }
256 
257         daoUtil.setBoolean( 6, false );
258 
259         daoUtil.executeUpdate( );
260         daoUtil.free( );
261     }
262 
263     /**
264      * Load the data of Document from the table
265      *
266      * @param nDocumentId
267      *            The identifier of Document
268      * @return the instance of the Document
269      */
270     public Document load( int nDocumentId )
271     {
272         return loadDocument( nDocumentId, true );
273     }
274 
275     /**
276      * Load the data of Document from the table
277      *
278      * @param nDocumentId
279      *            The identifier of Document
280      * @return the instance of the Document
281      */
282     public Document loadWithoutBinaries( int nDocumentId )
283     {
284         return loadDocument( nDocumentId, false );
285     }
286 
287     /**
288      * Load the data of Document from the table
289      *
290      * @param nDocumentId
291      *            The identifier of Document
292      * @param bBinaries
293      *            load binaries
294      * @return the instance of the Document
295      */
296     private Document loadDocument( int nDocumentId, boolean bBinaries )
297     {
298         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
299         daoUtil.setInt( 1, nDocumentId );
300         daoUtil.executeQuery( );
301 
302         Document document = null;
303 
304         if ( daoUtil.next( ) )
305         {
306             document = new Document( );
307             document.setId( daoUtil.getInt( 1 ) );
308             document.setCodeDocumentType( daoUtil.getString( 2 ) );
309             document.setTitle( daoUtil.getString( 3 ) );
310             document.setDateCreation( daoUtil.getTimestamp( 4 ) );
311             document.setDateModification( daoUtil.getTimestamp( 5 ) );
312             document.setXmlWorkingContent( daoUtil.getString( 6 ) );
313             document.setXmlValidatedContent( daoUtil.getString( 7 ) );
314             document.setSpaceId( daoUtil.getInt( 8 ) );
315             document.setSpace( daoUtil.getString( 9 ) );
316             document.setStateId( daoUtil.getInt( 10 ) );
317             document.setStateKey( daoUtil.getString( 11 ) );
318             document.setType( daoUtil.getString( 12 ) );
319             document.setSummary( daoUtil.getString( 13 ) );
320             document.setComment( daoUtil.getString( 14 ) );
321             document.setDateValidityBegin( daoUtil.getTimestamp( 15 ) );
322             document.setDateValidityEnd( daoUtil.getTimestamp( 16 ) );
323             document.setXmlMetadata( daoUtil.getString( 17 ) );
324             document.setCreatorId( daoUtil.getInt( 18 ) );
325             document.setMailingListId( daoUtil.getInt( 19 ) );
326             document.setPageTemplateDocumentId( daoUtil.getInt( 20 ) );
327             document.setSkipPortlet( daoUtil.getBoolean( 21 ) );
328             document.setSkipCategories( daoUtil.getBoolean( 22 ) );
329         }
330 
331         daoUtil.free( );
332 
333         if ( document != null )
334         {
335             if ( bBinaries )
336             {
337                 loadAttributes( document );
338             }
339             else
340             {
341                 if ( document.getStateId( ) == DocumentState.STATE_VALIDATE )
342                 {
343                     loadAttributesWithoutBinaries( document, true );
344                 }
345                 else
346                 {
347                     loadAttributesWithoutBinaries( document, false );
348                 }
349             }
350 
351             document.setCategories( selectCategories( document.getId( ) ) );
352         }
353 
354         return document;
355     }
356 
357     /**
358      * Load from space id.
359      *
360      * @param nSpaceId
361      *            The id of the document space
362      * @return the instance of the Document
363      */
364     public List<Document> loadFromSpaceId( int nSpaceId )
365     {
366         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FROM_SPACE_ID );
367         daoUtil.setInt( 1, nSpaceId );
368         daoUtil.executeQuery( );
369 
370         List<Document> list = new ArrayList<Document>( );
371 
372         while ( daoUtil.next( ) )
373         {
374             Documentcument/business/Document.html#Document">Document document = new Document( );
375             document.setId( daoUtil.getInt( 1 ) );
376             document.setTitle( daoUtil.getString( 2 ) );
377             document.setSummary( daoUtil.getString( 3 ) );
378             list.add( document );
379         }
380 
381         daoUtil.free( );
382 
383         for ( Document d : list )
384         {
385             if ( d != null )
386             {
387                 loadAttributes( d );
388                 d.setCategories( selectCategories( d.getId( ) ) );
389             }
390         }
391 
392         return list;
393     }
394 
395     /**
396      * Load the attributes of Document from the table
397      * 
398      * @param document
399      *            Document object
400      */
401     public void loadAttributes( Document document )
402     {
403         List<DocumentAttribute> listAttributes = new ArrayList<DocumentAttribute>( );
404         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATTRIBUTES );
405         daoUtil.setInt( 1, document.getId( ) );
406         daoUtil.executeQuery( );
407 
408         while ( daoUtil.next( ) )
409         {
410             DocumentAttributeiness/attributes/DocumentAttribute.html#DocumentAttribute">DocumentAttribute attribute = new DocumentAttribute( );
411             attribute.setId( daoUtil.getInt( 1 ) );
412             attribute.setCode( daoUtil.getString( 2 ) );
413             attribute.setCodeAttributeType( daoUtil.getString( 3 ) );
414             attribute.setCodeDocumentType( daoUtil.getString( 4 ) );
415             attribute.setName( daoUtil.getString( 5 ) );
416             attribute.setDescription( daoUtil.getString( 6 ) );
417             attribute.setAttributeOrder( daoUtil.getInt( 7 ) );
418             attribute.setRequired( daoUtil.getInt( 8 ) != 0 );
419             attribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
420 
421             String strContentType = daoUtil.getString( 11 );
422 
423             if ( StringUtils.isNotBlank( strContentType ) )
424             {
425                 // File attribute
426                 attribute.setBinary( true );
427                 attribute.setTextValue( daoUtil.getString( 10 ) );
428                 attribute.setBinaryValue( daoUtil.getBytes( 12 ) );
429                 attribute.setValueContentType( strContentType );
430             }
431             else
432             {
433                 // Text attribute
434                 attribute.setBinary( false );
435                 attribute.setTextValue( daoUtil.getString( 10 ) );
436                 attribute.setValueContentType( StringUtils.EMPTY );
437             }
438 
439             listAttributes.add( attribute );
440         }
441 
442         document.setAttributes( listAttributes );
443         daoUtil.free( );
444     }
445 
446     /**
447      * Load the attributes of Document from the table
448      * 
449      * @param document
450      *            Document object
451      * @param bValidated
452      *            true if the content of the document must be validated, false otherwise
453      */
454     public void loadAttributesWithoutBinaries( Document document, boolean bValidated )
455     {
456         List<DocumentAttribute> listAttributes = new ArrayList<DocumentAttribute>( );
457         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATTRIBUTES_WITHOUT_BINARIES );
458         daoUtil.setInt( 1, document.getId( ) );
459         daoUtil.setBoolean( 2, bValidated );
460         daoUtil.executeQuery( );
461 
462         while ( daoUtil.next( ) )
463         {
464             DocumentAttributeiness/attributes/DocumentAttribute.html#DocumentAttribute">DocumentAttribute attribute = new DocumentAttribute( );
465             attribute.setId( daoUtil.getInt( 1 ) );
466             attribute.setCode( daoUtil.getString( 2 ) );
467             attribute.setCodeAttributeType( daoUtil.getString( 3 ) );
468             attribute.setCodeDocumentType( daoUtil.getString( 4 ) );
469             attribute.setName( daoUtil.getString( 5 ) );
470             attribute.setDescription( daoUtil.getString( 6 ) );
471             attribute.setAttributeOrder( daoUtil.getInt( 7 ) );
472             attribute.setRequired( daoUtil.getInt( 8 ) != 0 );
473             attribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
474 
475             String strContentType = daoUtil.getString( 11 );
476 
477             if ( StringUtils.isNotBlank( strContentType ) )
478             {
479                 // File attribute
480                 attribute.setBinary( true );
481                 attribute.setTextValue( daoUtil.getString( 10 ) );
482                 attribute.setValueContentType( strContentType );
483             }
484             else
485             {
486                 // Text attribute
487                 attribute.setBinary( false );
488                 attribute.setTextValue( daoUtil.getString( 10 ) );
489                 attribute.setValueContentType( StringUtils.EMPTY );
490             }
491 
492             listAttributes.add( attribute );
493         }
494 
495         document.setAttributes( listAttributes );
496         daoUtil.free( );
497     }
498 
499     /**
500      * Delete a record from the table
501      *
502      * @param nDocumentId
503      *            the document identifier
504      */
505     public void delete( int nDocumentId )
506     {
507         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
508         daoUtil.setInt( 1, nDocumentId );
509 
510         daoUtil.executeUpdate( );
511         daoUtil.free( );
512 
513         // Delete attributes
514         deleteAttributes( nDocumentId );
515         // Delete categories
516         deleteCategories( nDocumentId );
517         // Delete history
518         deleteHistory( nDocumentId );
519     }
520 
521     /**
522      * Delete a record from the table
523      * 
524      * @param nDocumentId
525      *            The Document identifier
526      */
527     private void deleteAttributes( int nDocumentId )
528     {
529         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ATTRIBUTES );
530         daoUtil.setInt( 1, nDocumentId );
531         daoUtil.setBoolean( 2, false );
532 
533         daoUtil.executeUpdate( );
534         daoUtil.free( );
535     }
536 
537     /**
538      * Delete a validated record from the table
539      * 
540      * @param nDocumentId
541      *            The Document identifier
542      */
543     private void deleteValidatedAttributes( int nDocumentId )
544     {
545         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ATTRIBUTES );
546         daoUtil.setInt( 1, nDocumentId );
547         daoUtil.setBoolean( 2, true );
548 
549         daoUtil.executeUpdate( );
550         daoUtil.free( );
551     }
552 
553     /**
554      * validate a record from the table
555      * 
556      * @param nDocumentId
557      *            The Document identifier
558      */
559     public void validateAttributes( int nDocumentId )
560     {
561         deleteValidatedAttributes( nDocumentId );
562 
563         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_VALIDATE_ATTRIBUTES );
564         daoUtil.setBoolean( 1, true );
565         daoUtil.setInt( 2, nDocumentId );
566 
567         daoUtil.executeUpdate( );
568         daoUtil.free( );
569     }
570 
571     /**
572      * Delete a record from the table
573      * 
574      * @param nDocumentId
575      *            The Document identifier
576      */
577     private void deleteHistory( int nDocumentId )
578     {
579         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_DOCUMENT_HISTORY );
580         daoUtil.setInt( 1, nDocumentId );
581 
582         daoUtil.executeUpdate( );
583         daoUtil.free( );
584     }
585 
586     /**
587      * Update the record in the table
588      *
589      * @param document
590      *            The reference of document
591      * @param bUpdateContent
592      *            the boolean
593      */
594     public void store( Document document, boolean bUpdateContent )
595     {
596         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
597         daoUtil.setInt( 1, document.getId( ) );
598         daoUtil.setString( 2, document.getCodeDocumentType( ) );
599         daoUtil.setString( 3, document.getTitle( ) );
600         daoUtil.setTimestamp( 4, document.getDateCreation( ) );
601         daoUtil.setTimestamp( 5, document.getDateModification( ) );
602         daoUtil.setString( 6, document.getXmlWorkingContent( ) );
603         daoUtil.setString( 7, document.getXmlValidatedContent( ) );
604         daoUtil.setInt( 8, document.getSpaceId( ) );
605         daoUtil.setInt( 9, document.getStateId( ) );
606         daoUtil.setString( 10, document.getSummary( ) );
607         daoUtil.setString( 11, document.getComment( ) );
608         daoUtil.setTimestamp( 12, document.getDateValidityBegin( ) );
609         daoUtil.setTimestamp( 13, document.getDateValidityEnd( ) );
610         daoUtil.setString( 14, document.getXmlMetadata( ) );
611         daoUtil.setInt( 15, document.getCreatorId( ) );
612         daoUtil.setInt( 16, document.getMailingListId( ) );
613         daoUtil.setInt( 17, document.getPageTemplateDocumentId( ) );
614         daoUtil.setBoolean( 18, document.isSkipPortlet( ) );
615         daoUtil.setBoolean( 19, document.isSkipCategories( ) );
616         daoUtil.setInt( 20, document.getId( ) );
617 
618         daoUtil.executeUpdate( );
619         daoUtil.free( );
620 
621         if ( bUpdateContent )
622         {
623             deleteAttributes( document.getId( ) );
624             insertAttributes( document );
625             deleteCategories( document.getId( ) );
626             insertCategories( document.getCategories( ), document.getId( ) );
627         }
628     }
629 
630     /**
631      * Load the list of documents
632      *
633      * @return The Collection of the Document ids
634      * @param filter
635      *            The DocumentFilter Object
636      */
637     public Collection<Integer> selectPrimaryKeysByFilter( DocumentFilter filter )
638     {
639         Collection<Integer> listDocumentIds = new ArrayList<Integer>( );
640         DAOUtil daoUtil = getDaoFromFilter( SQL_QUERY_SELECT_PRIMARY_KEY_BY_FILTER, filter );
641         daoUtil.executeQuery( );
642 
643         while ( daoUtil.next( ) )
644         {
645             listDocumentIds.add( daoUtil.getInt( 1 ) );
646         }
647 
648         daoUtil.free( );
649 
650         return listDocumentIds;
651     }
652 
653     /**
654      * Load the list of documents
655      *
656      * @return The Collection of the Documents
657      * @param filter
658      *            The DocumentFilter Object
659      */
660     public List<Document> selectByFilter( DocumentFilter filter )
661     {
662         List<Document> listDocuments = new ArrayList<Document>( );
663         DAOUtil daoUtil = getDaoFromFilter( SQL_QUERY_SELECT_BY_FILTER, filter );
664         daoUtil.executeQuery( );
665 
666         while ( daoUtil.next( ) )
667         {
668             Documentcument/business/Document.html#Document">Document document = new Document( );
669             document.setId( daoUtil.getInt( 1 ) );
670             document.setCodeDocumentType( daoUtil.getString( 2 ) );
671             document.setTitle( daoUtil.getString( 3 ) );
672             document.setDateCreation( daoUtil.getTimestamp( 4 ) );
673             document.setDateModification( daoUtil.getTimestamp( 5 ) );
674             document.setXmlWorkingContent( daoUtil.getString( 6 ) );
675             document.setXmlValidatedContent( daoUtil.getString( 7 ) );
676             document.setSpaceId( daoUtil.getInt( 8 ) );
677             document.setSpace( daoUtil.getString( 9 ) );
678             document.setStateId( daoUtil.getInt( 10 ) );
679             document.setStateKey( daoUtil.getString( 11 ) );
680             document.setType( daoUtil.getString( 12 ) );
681             document.setSummary( daoUtil.getString( 13 ) );
682             document.setComment( daoUtil.getString( 14 ) );
683             document.setDateValidityBegin( daoUtil.getTimestamp( 15 ) );
684             document.setDateValidityEnd( daoUtil.getTimestamp( 16 ) );
685             document.setXmlMetadata( daoUtil.getString( 17 ) );
686             document.setCreatorId( daoUtil.getInt( 18 ) );
687             document.setMailingListId( daoUtil.getInt( 19 ) );
688             document.setPageTemplateDocumentId( daoUtil.getInt( 20 ) );
689             document.setSkipPortlet( daoUtil.getBoolean( 21 ) );
690             document.setSkipCategories( daoUtil.getBoolean( 22 ) );
691 
692             if ( filter.getLoadBinaries( ) )
693             {
694                 loadAttributes( document );
695             }
696             else
697             {
698                 if ( document.getStateId( ) == DocumentState.STATE_VALIDATE )
699                 {
700                     loadAttributesWithoutBinaries( document, true );
701                 }
702                 else
703                 {
704                     loadAttributesWithoutBinaries( document, false );
705                 }
706             }
707 
708             document.setCategories( selectCategories( document.getId( ) ) );
709 
710             listDocuments.add( document );
711         }
712 
713         daoUtil.free( );
714 
715         return listDocuments;
716     }
717 
718     /**
719      * Return a dao initialized with the specified filter
720      * 
721      * @param strQuerySelect
722      *            the query
723      * @param filter
724      *            the DocumentFilter object
725      * @return the DaoUtil
726      */
727     private DAOUtil getDaoFromFilter( String strQuerySelect, DocumentFilter filter )
728     {
729         String strSQL = strQuerySelect;
730         StringBuilder sbWhere = new StringBuilder( StringUtils.EMPTY );
731         sbWhere.append( ( ( filter.containsDocumentTypeCriteria( ) ) ? SQL_FILTER_DOCUMENT_TYPE : "" ) );
732 
733         if ( filter.containsSpaceCriteria( ) )
734         {
735             sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( SQL_FILTER_SPACE );
736         }
737 
738         if ( filter.containsStateCriteria( ) )
739         {
740             sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( SQL_FILTER_STATE );
741         }
742 
743         if ( filter.containsCategoriesCriteria( ) )
744         {
745             StringBuilder sbCategories = new StringBuilder( SQL_FILTER_CATEGORIES_BEGIN );
746 
747             int i = 0;
748 
749             for ( int nCategoryId : filter.getCategoriesId( ) )
750             {
751                 if ( nCategoryId > 0 )
752                 {
753                     sbCategories.append( SQL_FILTER_CATEGORIES );
754                 }
755                 else
756                 {
757                     sbCategories.append( SQL_FILTER_CATEGORIES_NULL );
758                 }
759 
760                 if ( ( i + 1 ) < filter.getCategoriesId( ).length )
761                 {
762                     sbCategories.append( SQL_FILTER_CATEGORIES_OR );
763                 }
764 
765                 i++;
766             }
767 
768             sbCategories.append( SQL_FILTER_CATEGORIES_END );
769             sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( sbCategories.toString( ) );
770         }
771 
772         if ( filter.containsIdsCriteria( ) )
773         {
774             StringBuilder sbIds = new StringBuilder( SQL_FILTER_ID_BEGIN );
775 
776             for ( int i = 0; i < filter.getIds( ).length; i++ )
777             {
778                 sbIds.append( SQL_FILTER_ID );
779 
780                 if ( ( i + 1 ) < filter.getIds( ).length )
781                 {
782                     sbIds.append( SQL_FILTER_ID_OR );
783                 }
784             }
785 
786             sbIds.append( SQL_FILTER_ID_END );
787             sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( sbIds.toString( ) );
788         }
789 
790         if ( BooleanUtils.isFalse( filter.isPublished( ) ) )
791         {
792             sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY )
793                     .append( "a.id_document NOT IN (SELECT DISTINCT id_document FROM document_published) " );
794         }
795 
796         if ( StringUtils.isNotBlank( filter.getDateMin( ) ) && StringUtils.isNotBlank( filter.getDateMax( ) ) )
797         {
798             sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( "a.date_modification < " )
799                     .append( "'" + filter.getDateMax( ) + "'" ).append( SQL_FILTER_AND ).append( "a.date_modification > " )
800                     .append( "'" + filter.getDateMin( ) + "'" );
801         }
802         else
803             if ( StringUtils.isNotBlank( filter.getDateMin( ) ) )
804             {
805                 sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( "a.date_modification > " )
806                         .append( "'" + filter.getDateMin( ) + "'" );
807             }
808             else
809                 if ( StringUtils.isNotBlank( filter.getDateMax( ) ) )
810                 {
811                     sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( "a.date_modification <= " )
812                             .append( "'" + filter.getDateMax( ) + "'" );
813                 }
814 
815         String strWhere = sbWhere.toString( );
816 
817         if ( sbWhere.length( ) != 0 )
818         {
819             strSQL += ( SQL_FILTER_WHERE_CLAUSE + strWhere );
820         }
821 
822         strSQL += SQL_ORDER_BY_LAST_MODIFICATION;
823         AppLogService.debug( "Sql query filter : " + strSQL );
824 
825         DAOUtil daoUtil = new DAOUtil( strSQL );
826         int nIndex = 1;
827 
828         if ( filter.containsDocumentTypeCriteria( ) )
829         {
830             daoUtil.setString( nIndex, filter.getCodeDocumentType( ) );
831             AppLogService.debug( "Param" + nIndex + " (getCodeDocumentType) = " + filter.getCodeDocumentType( ) );
832             nIndex++;
833         }
834 
835         if ( filter.containsSpaceCriteria( ) )
836         {
837             daoUtil.setInt( nIndex, filter.getIdSpace( ) );
838             AppLogService.debug( "Param" + nIndex + " (getIdSpace) = " + filter.getIdSpace( ) );
839             nIndex++;
840         }
841 
842         if ( filter.containsStateCriteria( ) )
843         {
844             daoUtil.setInt( nIndex, filter.getIdState( ) );
845             AppLogService.debug( "Param" + nIndex + " (getIdState) = " + filter.getIdState( ) );
846             nIndex++;
847         }
848 
849         if ( filter.containsCategoriesCriteria( ) )
850         {
851             for ( int nCategoryId : filter.getCategoriesId( ) )
852             {
853                 if ( nCategoryId > 0 )
854                 {
855                     daoUtil.setInt( nIndex, nCategoryId );
856                     AppLogService.debug( "Param" + nIndex + " (getCategoriesId) = " + nCategoryId );
857                     nIndex++;
858                 }
859             }
860         }
861 
862         if ( filter.containsIdsCriteria( ) )
863         {
864             for ( int nId : filter.getIds( ) )
865             {
866                 daoUtil.setInt( nIndex, nId );
867                 AppLogService.debug( "Param" + nIndex + " (getIds) = " + nId );
868                 nIndex++;
869             }
870         }
871 
872         return daoUtil;
873     }
874 
875     /**
876      * Load the list of documents in relation with categories of specified document
877      * 
878      * @param document
879      *            The document with the categories
880      * @return The Collection of the Documents
881      */
882     public List<Document> selectByRelatedCategories( Document document )
883     {
884         List<Document> listDocument = new ArrayList<Document>( );
885 
886         if ( ( document == null ) || ( document.getId( ) <= 0 ) )
887         {
888             return listDocument;
889         }
890 
891         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RELATED_CATEGORY );
892         daoUtil.setInt( 1, document.getId( ) );
893         daoUtil.executeQuery( );
894 
895         Document returnDocument = null;
896 
897         while ( daoUtil.next( ) )
898         {
899             returnDocument = new Document( );
900             returnDocument.setId( daoUtil.getInt( 1 ) );
901             returnDocument.setCodeDocumentType( daoUtil.getString( 2 ) );
902             returnDocument.setTitle( daoUtil.getString( 3 ) );
903             returnDocument.setDateCreation( daoUtil.getTimestamp( 4 ) );
904             returnDocument.setDateModification( daoUtil.getTimestamp( 5 ) );
905             returnDocument.setXmlWorkingContent( daoUtil.getString( 6 ) );
906             returnDocument.setXmlValidatedContent( daoUtil.getString( 7 ) );
907             returnDocument.setSpaceId( daoUtil.getInt( 8 ) );
908             returnDocument.setSpace( daoUtil.getString( 9 ) );
909             returnDocument.setStateId( daoUtil.getInt( 10 ) );
910             returnDocument.setStateKey( daoUtil.getString( 11 ) );
911             returnDocument.setType( daoUtil.getString( 12 ) );
912             returnDocument.setSummary( daoUtil.getString( 13 ) );
913             returnDocument.setComment( daoUtil.getString( 14 ) );
914             returnDocument.setDateValidityBegin( daoUtil.getTimestamp( 15 ) );
915             returnDocument.setDateValidityEnd( daoUtil.getTimestamp( 16 ) );
916             returnDocument.setXmlMetadata( daoUtil.getString( 17 ) );
917             returnDocument.setCreatorId( daoUtil.getInt( 18 ) );
918             returnDocument.setMailingListId( daoUtil.getInt( 19 ) );
919             returnDocument.setPageTemplateDocumentId( daoUtil.getInt( 20 ) );
920             returnDocument.setSkipPortlet( daoUtil.getBoolean( 21 ) );
921             returnDocument.setSkipCategories( daoUtil.getBoolean( 22 ) );
922 
923             listDocument.add( returnDocument );
924 
925             loadAttributes( document );
926             document.setCategories( selectCategories( document.getId( ) ) );
927         }
928 
929         daoUtil.free( );
930 
931         return listDocument;
932     }
933 
934     /**
935      * Load a resource (image, file, ...) corresponding to an attribute of a Document
936      *
937      * @param nDocumentId
938      *            The Document Id
939      * @return the instance of the DocumentResource
940      */
941     public DocumentResource loadResource( int nDocumentId )
942     {
943         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DOCUMENT_RESOURCE );
944         daoUtil.setInt( 1, nDocumentId );
945         daoUtil.executeQuery( );
946 
947         DocumentResource resource = null;
948 
949         if ( daoUtil.next( ) )
950         {
951             resource = new DocumentResource( );
952             resource.setContent( daoUtil.getBytes( 1 ) );
953             resource.setContentType( daoUtil.getString( 2 ) );
954             resource.setName( daoUtil.getString( 3 ) );
955         }
956 
957         daoUtil.free( );
958 
959         return resource;
960     }
961 
962     /**
963      * Load a resource (image, file, ...) corresponding to an attribute of a Document
964      *
965      * @param nDocumentId
966      *            The Document Id
967      * @param nAttributeId
968      *            The Attribute Id
969      * @param bValidated
970      *            true if we want the validated resource
971      * @return the instance of the DocumentResource
972      */
973     public DocumentResource loadSpecificResource( int nDocumentId, int nAttributeId, boolean bValidated )
974     {
975         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DOCUMENT_SPECIFIC_RESOURCE );
976         daoUtil.setInt( 1, nDocumentId );
977         daoUtil.setInt( 2, nAttributeId );
978         daoUtil.setBoolean( 3, bValidated );
979         daoUtil.executeQuery( );
980 
981         DocumentResource resource = null;
982 
983         if ( daoUtil.next( ) )
984         {
985             resource = new DocumentResource( );
986             resource.setContent( daoUtil.getBytes( 1 ) );
987             resource.setContentType( daoUtil.getString( 2 ) );
988             resource.setName( daoUtil.getString( 3 ) );
989         }
990 
991         daoUtil.free( );
992 
993         return resource;
994     }
995 
996     /**
997      * Gets all documents id
998      * 
999      * @return A collection of Integer
1000      */
1001     public Collection<Integer> selectAllPrimaryKeys( )
1002     {
1003         Collection<Integer> listPrimaryKeys = new ArrayList<Integer>( );
1004         String strSQL = SQL_QUERY_SELECT_PRIMARY_KEYS;
1005 
1006         DAOUtil daoUtil = new DAOUtil( strSQL );
1007         daoUtil.executeQuery( );
1008 
1009         while ( daoUtil.next( ) )
1010         {
1011             listPrimaryKeys.add( daoUtil.getInt( 1 ) );
1012         }
1013 
1014         daoUtil.free( );
1015 
1016         return listPrimaryKeys;
1017     }
1018 
1019     /**
1020      * Gets all documents
1021      * 
1022      * @return the document list
1023      * @deprecated
1024      */
1025     public List<Document> selectAll( )
1026     {
1027         List<Document> listDocuments = new ArrayList<Document>( );
1028         String strSQL = SQL_QUERY_SELECT_BY_FILTER;
1029 
1030         DAOUtil daoUtil = new DAOUtil( strSQL );
1031         daoUtil.executeQuery( );
1032 
1033         while ( daoUtil.next( ) )
1034         {
1035             Documentcument/business/Document.html#Document">Document document = new Document( );
1036             document.setId( daoUtil.getInt( 1 ) );
1037             document.setCodeDocumentType( daoUtil.getString( 2 ) );
1038             document.setTitle( daoUtil.getString( 3 ) );
1039             document.setDateCreation( daoUtil.getTimestamp( 4 ) );
1040             document.setDateModification( daoUtil.getTimestamp( 5 ) );
1041             document.setXmlWorkingContent( daoUtil.getString( 6 ) );
1042             document.setXmlValidatedContent( daoUtil.getString( 7 ) );
1043             document.setSpaceId( daoUtil.getInt( 8 ) );
1044             document.setSpace( daoUtil.getString( 9 ) );
1045             document.setStateId( daoUtil.getInt( 10 ) );
1046             document.setStateKey( daoUtil.getString( 11 ) );
1047             document.setType( daoUtil.getString( 12 ) );
1048             document.setSummary( daoUtil.getString( 13 ) );
1049             document.setComment( daoUtil.getString( 14 ) );
1050             document.setDateValidityBegin( daoUtil.getTimestamp( 15 ) );
1051             document.setDateValidityEnd( daoUtil.getTimestamp( 16 ) );
1052             document.setXmlMetadata( daoUtil.getString( 17 ) );
1053             document.setCreatorId( daoUtil.getInt( 18 ) );
1054             document.setMailingListId( daoUtil.getInt( 19 ) );
1055             document.setPageTemplateDocumentId( daoUtil.getInt( 20 ) );
1056             document.setSkipPortlet( daoUtil.getBoolean( 21 ) );
1057             document.setSkipCategories( daoUtil.getBoolean( 22 ) );
1058 
1059             loadAttributes( document );
1060             document.setCategories( selectCategories( document.getId( ) ) );
1061             listDocuments.add( document );
1062         }
1063 
1064         daoUtil.free( );
1065 
1066         return listDocuments;
1067     }
1068 
1069     /**
1070      * Load the path of page template
1071      *
1072      * @param nIdPageTemplateDocument
1073      *            The identifier of page template
1074      * @return the page template path
1075      */
1076     public String getPageTemplateDocumentPath( int nIdPageTemplateDocument )
1077     {
1078         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PAGE_TEMPLATE_PATH );
1079         daoUtil.setInt( 1, nIdPageTemplateDocument );
1080         daoUtil.executeQuery( );
1081 
1082         String strPageTemplatePath = "";
1083 
1084         if ( daoUtil.next( ) )
1085         {
1086             strPageTemplatePath = daoUtil.getString( 1 );
1087         }
1088 
1089         daoUtil.free( );
1090 
1091         return strPageTemplatePath;
1092     }
1093 
1094     /**
1095      * Select a list of Category for a specified Document id
1096      * 
1097      * @param nIdDocument
1098      *            The document Id
1099      * @return The Collection of Category (empty collection is no result)
1100      */
1101     private List<Category> selectCategories( int nIdDocument )
1102     {
1103         int nParam;
1104         List<Category> listCategory = new ArrayList<Category>( );
1105         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_CATEGORY );
1106         daoUtil.setInt( 1, nIdDocument );
1107         daoUtil.executeQuery( );
1108 
1109         while ( daoUtil.next( ) )
1110         {
1111             nParam = 0;
1112 
1113             Categorycument/business/category/Category.html#Category">Category category = new Category( );
1114             category.setId( daoUtil.getInt( ++nParam ) );
1115             category.setName( daoUtil.getString( ++nParam ) );
1116             category.setDescription( daoUtil.getString( ++nParam ) );
1117             category.setIconContent( daoUtil.getBytes( ++nParam ) );
1118             category.setIconMimeType( daoUtil.getString( ++nParam ) );
1119 
1120             listCategory.add( category );
1121         }
1122 
1123         daoUtil.free( );
1124 
1125         return listCategory;
1126     }
1127 
1128     /**
1129      * Insert links between Category and id document
1130      * 
1131      * @param listCategory
1132      *            The list of Category
1133      * @param nIdDocument
1134      *            The id of document
1135      *
1136      */
1137     private void insertCategories( List<Category> listCategory, int nIdDocument )
1138     {
1139         if ( listCategory != null )
1140         {
1141             DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_LINK_CATEGORY_DOCUMENT );
1142 
1143             for ( Category category : listCategory )
1144             {
1145                 daoUtil.setInt( 1, category.getId( ) );
1146                 daoUtil.setInt( 2, nIdDocument );
1147                 daoUtil.executeUpdate( );
1148             }
1149 
1150             daoUtil.free( );
1151         }
1152     }
1153 
1154     /**
1155      * Delete all links for a document
1156      * 
1157      * @param nIdDocument
1158      *            The identifier of the object Document
1159      */
1160     private void deleteCategories( int nIdDocument )
1161     {
1162         int nParam = 0;
1163         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINKS_DOCUMENT );
1164         daoUtil.setInt( ++nParam, nIdDocument );
1165         daoUtil.executeUpdate( );
1166         daoUtil.free( );
1167     }
1168 
1169     /**
1170      * Load document type and date last modification for HTTP GET conditional request ("If-Modified-Since")
1171      * 
1172      * @param nIdDocument
1173      *            The id of the document
1174      * @return the document
1175      */
1176     public Document loadLastModifiedAttributes( int nIdDocument )
1177     {
1178         Document document = null;
1179         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_LAST_MODIFIED );
1180         daoUtil.setInt( 1, nIdDocument );
1181         daoUtil.executeQuery( );
1182 
1183         if ( daoUtil.next( ) )
1184         {
1185             document = new Document( );
1186             document.setId( nIdDocument );
1187             document.setCodeDocumentType( daoUtil.getString( 1 ) );
1188             document.setDateModification( daoUtil.getTimestamp( 2 ) );
1189         }
1190 
1191         daoUtil.free( );
1192 
1193         return document;
1194     }
1195 
1196     /**
1197      * Load the data of last Document the user worked in from the table
1198      *
1199      * @param strUserName
1200      *            the user name
1201      * @return the instance of the Document
1202      */
1203     public Document loadLastModifiedDocumentFromUser( String strUserName )
1204     {
1205         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_MODIFIED_DOCUMENT_FROM_USER );
1206         daoUtil.setString( 1, strUserName );
1207         daoUtil.executeQuery( );
1208 
1209         Document document = null;
1210 
1211         if ( daoUtil.next( ) )
1212         {
1213             int nIndex = 1;
1214             document = new Document( );
1215             document.setId( daoUtil.getInt( nIndex++ ) );
1216             document.setCodeDocumentType( daoUtil.getString( nIndex++ ) );
1217             document.setTitle( daoUtil.getString( nIndex++ ) );
1218             document.setDateCreation( daoUtil.getTimestamp( nIndex++ ) );
1219             document.setDateModification( daoUtil.getTimestamp( nIndex++ ) );
1220             document.setXmlWorkingContent( daoUtil.getString( nIndex++ ) );
1221             document.setXmlValidatedContent( daoUtil.getString( nIndex++ ) );
1222             document.setSpaceId( daoUtil.getInt( nIndex++ ) );
1223             document.setSpace( daoUtil.getString( nIndex++ ) );
1224             document.setStateId( daoUtil.getInt( nIndex++ ) );
1225             document.setStateKey( daoUtil.getString( nIndex++ ) );
1226             document.setType( daoUtil.getString( nIndex++ ) );
1227             document.setSummary( daoUtil.getString( nIndex++ ) );
1228             document.setComment( daoUtil.getString( nIndex++ ) );
1229             document.setDateValidityBegin( daoUtil.getTimestamp( nIndex++ ) );
1230             document.setDateValidityEnd( daoUtil.getTimestamp( nIndex++ ) );
1231             document.setXmlMetadata( daoUtil.getString( nIndex++ ) );
1232             document.setCreatorId( daoUtil.getInt( nIndex++ ) );
1233             document.setMailingListId( daoUtil.getInt( nIndex++ ) );
1234             document.setPageTemplateDocumentId( daoUtil.getInt( nIndex++ ) );
1235             document.setSkipPortlet( daoUtil.getBoolean( nIndex++ ) );
1236             document.setSkipCategories( daoUtil.getBoolean( nIndex++ ) );
1237         }
1238 
1239         daoUtil.free( );
1240 
1241         return document;
1242     }
1243 
1244     /**
1245      * Load the data of last Document the user worked in from the table
1246      *
1247      * @return the instance of the Document
1248      */
1249     public Document loadLastPublishedDocument( )
1250     {
1251         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_PUBLISHED_DOCUMENT );
1252         daoUtil.executeQuery( );
1253 
1254         Document document = null;
1255 
1256         if ( daoUtil.next( ) )
1257         {
1258             int nIndex = 1;
1259             document = new Document( );
1260             document.setId( daoUtil.getInt( nIndex++ ) );
1261             document.setCodeDocumentType( daoUtil.getString( nIndex++ ) );
1262             document.setTitle( daoUtil.getString( nIndex++ ) );
1263             document.setDateCreation( daoUtil.getTimestamp( nIndex++ ) );
1264             document.setDateModification( daoUtil.getTimestamp( nIndex++ ) );
1265             document.setXmlWorkingContent( daoUtil.getString( nIndex++ ) );
1266             document.setXmlValidatedContent( daoUtil.getString( nIndex++ ) );
1267             document.setSpaceId( daoUtil.getInt( nIndex++ ) );
1268             document.setSpace( daoUtil.getString( nIndex++ ) );
1269             document.setStateId( daoUtil.getInt( nIndex++ ) );
1270             document.setStateKey( daoUtil.getString( nIndex++ ) );
1271             document.setType( daoUtil.getString( nIndex++ ) );
1272             document.setSummary( daoUtil.getString( nIndex++ ) );
1273             document.setComment( daoUtil.getString( nIndex++ ) );
1274             document.setDateValidityBegin( daoUtil.getTimestamp( nIndex++ ) );
1275             document.setDateValidityEnd( daoUtil.getTimestamp( nIndex++ ) );
1276             document.setXmlMetadata( daoUtil.getString( nIndex++ ) );
1277             document.setCreatorId( daoUtil.getInt( nIndex++ ) );
1278             document.setMailingListId( daoUtil.getInt( nIndex++ ) );
1279             document.setPageTemplateDocumentId( daoUtil.getInt( nIndex++ ) );
1280             document.setSkipPortlet( daoUtil.getBoolean( nIndex++ ) );
1281             document.setSkipCategories( daoUtil.getBoolean( nIndex++ ) );
1282         }
1283 
1284         daoUtil.free( );
1285 
1286         return document;
1287     }
1288 }