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.publication;
35  
36  import fr.paris.lutece.util.sql.DAOUtil;
37  
38  import java.sql.Timestamp;
39  
40  import java.util.ArrayList;
41  import java.util.Collection;
42  import java.util.Date;
43  
44  
45  /**
46   * This class provides Data Access methods for DocumentPublication objects
47   */
48  public class DocumentPublicationDAO implements IDocumentPublicationDAO
49  {
50      private static final String SQL_QUERY_DELETE = " DELETE FROM document_published WHERE id_portlet = ? AND id_document = ? ";
51      private static final String SQL_QUERY_DELETE_FROM_PORTLET_ID = " DELETE FROM document_published WHERE id_portlet = ? ";
52      private static final String SQL_QUERY_DELETE_FROM_DOCUMENT_ID = " DELETE FROM document_published WHERE id_document = ? ";
53      private static final String SQL_QUERY_INSERT = " INSERT INTO document_published ( id_portlet, id_document, document_order, status, date_publishing ) VALUES ( ?, ?, ?, ?, ? )";
54      private static final String SQL_QUERY_UPDATE = " UPDATE document_published SET document_order = ?, status = ?, date_publishing = ? WHERE id_portlet = ? AND id_document = ? ";
55      private static final String SQL_QUERY_SELECT = " SELECT document_order, status, date_publishing FROM document_published WHERE id_portlet = ? AND id_document = ? ORDER BY document_order ASC ";
56      private static final String SQL_QUERY_SELECT_BY_PORTLET_ID = " SELECT id_document, document_order, status, date_publishing FROM document_published WHERE id_portlet = ? ORDER BY document_order ASC ";
57      private static final String SQL_QUERY_SELECT_BY_DOCUMENT_ID = " SELECT id_portlet, document_order, status, date_publishing FROM document_published WHERE id_document = ? ORDER BY document_order ASC ";
58      private static final String SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS = " SELECT id_document, document_order, date_publishing FROM document_published WHERE id_portlet = ? AND status = ? ORDER BY document_order ASC ";
59      private static final String SQL_QUERY_SELECT_BY_DOCUMENT_ID_AND_STATUS = " SELECT id_portlet, document_order, date_publishing FROM document_published WHERE id_document = ? AND status = ? ORDER BY document_order ASC ";
60      private static final String SQL_QUERY_SELECT_BY_DATE_PUBLISHING_AND_STATUS = " SELECT id_portlet, id_document, document_order, date_publishing FROM document_published WHERE date_publishing >= ? AND status = ? ORDER BY document_order ASC ";
61      private static final String SQL_QUERY_MAX_ORDER = "SELECT max(document_order) FROM document_published WHERE id_portlet = ?  ";
62      private static final String SQL_QUERY_MODIFY_ORDER_BY_ID = "SELECT id_document FROM document_published  WHERE document_order = ? AND id_portlet = ?";
63  
64      /**
65       * Insert the documentsPublication object
66       *
67       * @param documentPublication The document Publication object
68       */
69      public void insert( DocumentPublication documentPublication )
70      {
71          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
72  
73          daoUtil.setInt( 1, documentPublication.getPortletId(  ) );
74          daoUtil.setInt( 2, documentPublication.getDocumentId(  ) );
75          daoUtil.setInt( 3, documentPublication.getDocumentOrder(  ) );
76          daoUtil.setInt( 4, documentPublication.getStatus(  ) );
77          daoUtil.setTimestamp( 5, new Timestamp( documentPublication.getDatePublishing(  ).getTime(  ) ) );
78  
79          daoUtil.executeUpdate(  );
80          daoUtil.free(  );
81      }
82  
83      /**
84       * Update the {@link DocumentPublication} object
85       *
86       * @param documentPublication The {@link DocumentPublication} object
87       */
88      public void store( DocumentPublication documentPublication )
89      {
90          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
91          daoUtil.setInt( 1, documentPublication.getDocumentOrder(  ) );
92          daoUtil.setInt( 2, documentPublication.getStatus(  ) ); //FIXME old : daoUtil.setInt( 2, maxOrderDocumentList( nPortletId ) + 1 );
93          daoUtil.setTimestamp( 3, new Timestamp( documentPublication.getDatePublishing(  ).getTime(  ) ) );
94          daoUtil.setInt( 4, documentPublication.getPortletId(  ) );
95          daoUtil.setInt( 5, documentPublication.getDocumentId(  ) );
96  
97          daoUtil.executeUpdate(  );
98          daoUtil.free(  );
99      }
100 
101     /**
102     * Delete records for table document_published specified by portlet id and document id
103     *
104     * @param nPortletId the portlet identifier
105     * @param nDocumentId the document identifier
106     */
107     public void delete( int nPortletId, int nDocumentId )
108     {
109         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
110         daoUtil.setInt( 1, nPortletId );
111         daoUtil.setInt( 2, nDocumentId );
112         daoUtil.executeUpdate(  );
113         daoUtil.free(  );
114     }
115 
116     /**
117     * Delete records for table document_published specified by portlet id
118     *
119     * @param nPortletId the portlet identifier
120     */
121     public void deleteFromPortletId( int nPortletId )
122     {
123         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_PORTLET_ID );
124         daoUtil.setInt( 1, nPortletId );
125         daoUtil.executeUpdate(  );
126         daoUtil.free(  );
127     }
128 
129     /**
130     * Delete records for table document_published specified by portlet id
131     *
132     * @param nDocumentId the document identifier
133     */
134     public void deleteFromDocumentId( int nDocumentId )
135     {
136         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_DOCUMENT_ID );
137         daoUtil.setInt( 1, nDocumentId );
138         daoUtil.executeUpdate(  );
139         daoUtil.free(  );
140     }
141 
142     ///////////////////////////////////////////////////////////////////////////////
143     // Select
144 
145     /**
146      * Select the {@link DocumentPublication} object specified by the portlet id and document id
147      * @param nPortletId The portlet identifier
148      * @param nDocumentId The document identifier
149      * @return The {@link DocumentPublication} object or null if the object does not exists
150      */
151     public DocumentPublication select( int nPortletId, int nDocumentId )
152     {
153         DocumentPublication documentPublication = null;
154         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
155         daoUtil.setInt( 1, nPortletId );
156         daoUtil.setInt( 2, nDocumentId );
157         daoUtil.executeQuery(  );
158 
159         while ( daoUtil.next(  ) )
160         {
161             documentPublication = new DocumentPublication(  );
162             documentPublication.setPortletId( nPortletId );
163             documentPublication.setDocumentId( nDocumentId );
164             documentPublication.setDocumentOrder( daoUtil.getInt( 1 ) );
165             documentPublication.setStatus( daoUtil.getInt( 2 ) );
166             documentPublication.setDatePublishing( daoUtil.getTimestamp( 3 ) );
167         }
168 
169         daoUtil.free(  );
170 
171         return documentPublication;
172     }
173 
174     /**
175      * Select the list of {@link DocumentPublication} objects specified by the portlet id
176      * @param nPortletId The portlet identifier
177      * @return The {@link DocumentPublication} objects list (empty list if no objects found)
178      */
179     public Collection<DocumentPublication> selectByPortletId( int nPortletId )
180     {
181         Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>(  );
182         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PORTLET_ID );
183         daoUtil.setInt( 1, nPortletId );
184         daoUtil.executeQuery(  );
185 
186         while ( daoUtil.next(  ) )
187         {
188             DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication(  );
189             documentPublication.setPortletId( nPortletId );
190             documentPublication.setDocumentId( daoUtil.getInt( 1 ) );
191             documentPublication.setDocumentOrder( daoUtil.getInt( 2 ) );
192             documentPublication.setStatus( daoUtil.getInt( 3 ) );
193             documentPublication.setDatePublishing( daoUtil.getTimestamp( 4 ) );
194             listDocumentPublication.add( documentPublication );
195         }
196 
197         daoUtil.free(  );
198 
199         return listDocumentPublication;
200     }
201 
202     /**
203      * Select the list of {@link DocumentPublication} objects specified by the document id
204      * @param nDocumentId The document identifier
205      * @return The {@link DocumentPublication} objects list (empty list if no objects found)
206      */
207     public Collection<DocumentPublication> selectByDocumentId( int nDocumentId )
208     {
209         Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>(  );
210         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DOCUMENT_ID );
211         daoUtil.setInt( 1, nDocumentId );
212         daoUtil.executeQuery(  );
213 
214         while ( daoUtil.next(  ) )
215         {
216             DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication(  );
217             documentPublication.setPortletId( daoUtil.getInt( 1 ) );
218             documentPublication.setDocumentId( nDocumentId );
219             documentPublication.setDocumentOrder( daoUtil.getInt( 2 ) );
220             documentPublication.setStatus( daoUtil.getInt( 3 ) );
221             documentPublication.setDatePublishing( daoUtil.getTimestamp( 4 ) );
222             listDocumentPublication.add( documentPublication );
223         }
224 
225         daoUtil.free(  );
226 
227         return listDocumentPublication;
228     }
229 
230     /**
231      * Select the list of {@link DocumentPublication} objects specified by the portlet id and the status
232      * @param nPortletId The portlet identifier
233      * @param nStatus The status
234      * @return The {@link DocumentPublication} objects list (empty list if no objects found)
235      */
236     public Collection<DocumentPublication> selectByPortletIdAndStatus( int nPortletId, int nStatus )
237     {
238         Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>(  );
239         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS );
240         daoUtil.setInt( 1, nPortletId );
241         daoUtil.setInt( 2, nStatus );
242         daoUtil.executeQuery(  );
243 
244         while ( daoUtil.next(  ) )
245         {
246             DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication(  );
247             documentPublication.setPortletId( nPortletId );
248             documentPublication.setDocumentId( daoUtil.getInt( 1 ) );
249             documentPublication.setDocumentOrder( daoUtil.getInt( 2 ) );
250             documentPublication.setStatus( nStatus );
251             documentPublication.setDatePublishing( daoUtil.getTimestamp( 3 ) );
252             listDocumentPublication.add( documentPublication );
253         }
254 
255         daoUtil.free(  );
256 
257         return listDocumentPublication;
258     }
259 
260     /**
261      * Select the list of {@link DocumentPublication} objects specified by the document id and the status
262      * @param nDocumentId The document identifier
263      * @param nStatus The status
264      * @return The {@link DocumentPublication} objects list (empty list if no objects found)
265      */
266     public Collection<DocumentPublication> selectByDocumentIdAndStatus( int nDocumentId, int nStatus )
267     {
268         Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>(  );
269         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DOCUMENT_ID_AND_STATUS );
270         daoUtil.setInt( 1, nDocumentId );
271         daoUtil.setInt( 2, nStatus );
272         daoUtil.executeQuery(  );
273 
274         while ( daoUtil.next(  ) )
275         {
276             DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication(  );
277             documentPublication.setPortletId( daoUtil.getInt( 1 ) );
278             documentPublication.setDocumentId( nDocumentId );
279             documentPublication.setDocumentOrder( daoUtil.getInt( 2 ) );
280             documentPublication.setStatus( nStatus );
281             documentPublication.setDatePublishing( daoUtil.getTimestamp( 3 ) );
282             listDocumentPublication.add( documentPublication );
283         }
284 
285         daoUtil.free(  );
286 
287         return listDocumentPublication;
288     }
289 
290     /**
291      * Find the list of {@link DocumentPublication} objects specified the status and published at or after the specified date
292      * @param datePublishing The publication date
293      * @param nStatus The status
294      * @return The {@link DocumentPublication} objects {@link Collection} ordered by documentOrder ascending. The list is empty if no objects found.
295      */
296     public Collection<DocumentPublication> selectSinceDatePublishingAndStatus( Date datePublishing, int nStatus )
297     {
298         Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>(  );
299         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DATE_PUBLISHING_AND_STATUS );
300         daoUtil.setTimestamp( 1, new Timestamp( datePublishing.getTime(  ) ) );
301         daoUtil.setInt( 2, nStatus );
302         daoUtil.executeQuery(  );
303 
304         while ( daoUtil.next(  ) )
305         {
306             DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication(  );
307             documentPublication.setPortletId( daoUtil.getInt( 1 ) );
308             documentPublication.setDocumentId( daoUtil.getInt( 2 ) );
309             documentPublication.setDocumentOrder( daoUtil.getInt( 3 ) );
310             documentPublication.setStatus( nStatus );
311             documentPublication.setDatePublishing( daoUtil.getTimestamp( 4 ) );
312             listDocumentPublication.add( documentPublication );
313         }
314 
315         daoUtil.free(  );
316 
317         return listDocumentPublication;
318     }
319 
320     /**
321      * Select the max order from a list of {@link DocumentPublication} specified by portlet id
322      * @param nPortletId the portlet identifer
323      * @return The max order of document
324      */
325     public int selectMaxDocumentOrder( int nPortletId )
326     {
327         int nOrder = 0;
328         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER );
329         daoUtil.setInt( 1, nPortletId );
330         daoUtil.executeQuery(  );
331 
332         if ( daoUtil.next(  ) )
333         {
334             nOrder = daoUtil.getInt( 1 );
335         }
336 
337         daoUtil.free(  );
338 
339         return nOrder;
340     }
341 
342     /**
343      * Return a document identifier in a distinct order
344      *
345      * @param nDocumentOrder The order number
346      * @param nPortletId the portlet identifier
347      * @return The order of the Document
348      */
349     public int selectDocumentIdByOrder( int nDocumentOrder, int nPortletId )
350     {
351         //    	FIXME The document_order column is not a primary key, so this method have to return a collection
352         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MODIFY_ORDER_BY_ID );
353         int nResult = nDocumentOrder;
354         daoUtil.setInt( 1, nResult );
355         daoUtil.setInt( 2, nPortletId );
356         daoUtil.executeQuery(  );
357 
358         if ( !daoUtil.next(  ) )
359         {
360             // If number order doesn't exist
361             nResult = 1;
362         }
363         else
364         {
365             nResult = daoUtil.getInt( 1 );
366         }
367 
368         daoUtil.free(  );
369 
370         return nResult;
371     }
372 }