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