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.portlet;
35  
36  import fr.paris.lutece.portal.business.portlet.Portlet;
37  import fr.paris.lutece.util.ReferenceItem;
38  import fr.paris.lutece.util.ReferenceList;
39  import fr.paris.lutece.util.sql.DAOUtil;
40  
41  import java.util.ArrayList;
42  import java.util.Collection;
43  import java.util.List;
44  
45  
46  public class DocumentPortletDAO implements IDocumentPortletDAO
47  {
48      private static final String SQL_QUERY_INSERT = "INSERT INTO document_portlet ( id_portlet , code_document_type ) VALUES ( ? , ? )";
49      private static final String SQL_QUERY_SELECT = "SELECT id_portlet , code_document_type FROM document_portlet WHERE id_portlet = ? ";
50      private static final String SQL_QUERY_UPDATE = "UPDATE document_portlet SET id_portlet = ?, code_document_type = ? WHERE id_portlet = ? ";
51      private static final String SQL_QUERY_DELETE = "DELETE FROM document_portlet WHERE id_portlet= ? ";
52      private static final String SQL_QUERY_DELETE_PUBLISHED_DOCUMENT_PORTLET = " DELETE FROM document_published WHERE id_portlet = ? ";
53      private static final String SQL_QUERY_SELECT_DOCUMENTS_BY_TYPE_AND_CATEGORY = "SELECT DISTINCT b.id_portlet , a.name, a.date_update " +
54          "FROM document_portlet b " +
55          "LEFT JOIN document_published c ON b.id_portlet = c.id_portlet AND c.id_document= ? " +
56          "INNER JOIN core_portlet a ON b.id_portlet = a.id_portlet " +
57          "LEFT OUTER JOIN document_category_portlet d ON b.id_portlet = d.id_portlet " +
58          "INNER JOIN core_page f ON a.id_page = f.id_page " +
59          "WHERE c.id_portlet IS NULL AND b.code_document_type = ? AND (d.id_category IN (SELECT e.id_category " +
60          "FROM document_category_link e WHERE e.id_document = ?) OR d.id_category IS NULL) ";
61      private static final String SQL_QUERY_CHECK_IS_ALIAS = "SELECT id_alias FROM core_portlet_alias WHERE id_alias = ?";
62      private static final String SQL_QUERY_SELECT_PORTLETS_BY_DOCUMENT_ID = "SELECT id_portlet FROM document_published WHERE id_document = ?";
63  
64      // Category
65      private static final String SQL_QUERY_INSERT_CATEGORY_PORTLET = "INSERT INTO document_category_portlet ( id_portlet , id_category ) VALUES ( ? , ? )";
66      private static final String SQL_QUERY_DELETE_CATEGORY_PORTLET = " DELETE FROM document_category_portlet WHERE id_portlet = ? ";
67      private static final String SQL_QUERY_DELETE_AUTO_PUBLICATION_PORTLET = " DELETE FROM document_auto_publication WHERE id_portlet = ? ";
68      private static final String SQL_QUERY_SELECT_CATEGORY_PORTLET = "SELECT id_category FROM document_category_portlet WHERE id_portlet = ? ";
69      private static final String SQL_QUERY_CASE_AND = " AND ";
70  
71      // /////////////////////////////////////////////////////////////////////////////////////
72      // Access methods to data
73  
74      /**
75       * Insert a new record in the table document_portlet
76       *
77       * @param portlet
78       *            the instance of the Portlet object to insert
79       */
80      public void insert( Portlet portlet )
81      {
82          DocumentPortlet./../../../../../fr/paris/lutece/plugins/document/business/portlet/DocumentPortlet.html#DocumentPortlet">DocumentPortlet p = (DocumentPortlet) portlet;
83  
84          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
85          daoUtil.setInt( 1, p.getId(  ) );
86          daoUtil.setString( 2, p.getDocumentTypeCode(  ) );
87  
88          daoUtil.executeUpdate(  );
89          daoUtil.free(  );
90  
91          insertCategory( portlet );
92      }
93  
94      /**
95       * Insert a list of category for a specified portlet
96       *
97       * @param portlet
98       *            the {@link DocumentPortlet} to insert
99       */
100     private void insertCategory( Portlet portlet )
101     {
102         DocumentPortlet./../../../../../fr/paris/lutece/plugins/document/business/portlet/DocumentPortlet.html#DocumentPortlet">DocumentPortlet p = (DocumentPortlet) portlet;
103 
104         if ( p.getIdCategory(  ) != null )
105         {
106             DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_CATEGORY_PORTLET );
107 
108             for ( int nIdCategory : p.getIdCategory(  ) )
109             {
110                 daoUtil.setInt( 1, p.getId(  ) );
111                 daoUtil.setInt( 2, nIdCategory );
112 
113                 daoUtil.executeUpdate(  );
114             }
115 
116             daoUtil.free(  );
117         }
118     }
119 
120     /**
121      * Deletes records for a portlet identifier in the tables document_portlet,
122      * document_published, document_category_portlet
123      *
124      * @param nPortletId
125      *            the portlet identifier
126      */
127     public void delete( int nPortletId )
128     {
129         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
130         daoUtil.setInt( 1, nPortletId );
131         daoUtil.executeUpdate(  );
132         daoUtil.free(  );
133 
134         daoUtil = new DAOUtil( SQL_QUERY_DELETE_PUBLISHED_DOCUMENT_PORTLET );
135         daoUtil.setInt( 1, nPortletId );
136         daoUtil.executeUpdate(  );
137         daoUtil.free(  );
138 
139         deleteCategories( nPortletId );
140         deleteAutoPublication( nPortletId );
141     }
142 
143     /**
144      * Delete categories for the specified portlet
145      *
146      * @param nPortletId
147      *            The portlet identifier
148      */
149     private void deleteCategories( int nPortletId )
150     {
151         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_CATEGORY_PORTLET );
152         daoUtil.setInt( 1, nPortletId );
153         daoUtil.executeUpdate(  );
154         daoUtil.free(  );
155     }
156 
157     /**
158      * Delete auto publication records for the specified portlet
159      *
160      * @param nPortletId
161      *            The portlet identifier
162      */
163     private void deleteAutoPublication( int nPortletId )
164     {
165         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_AUTO_PUBLICATION_PORTLET );
166         daoUtil.setInt( 1, nPortletId );
167         daoUtil.executeUpdate(  );
168         daoUtil.free(  );
169     }
170 
171     /**
172      * Loads the data of Document Portlet whose identifier is specified in
173      * parameter
174      *
175      * @param nPortletId
176      *            The Portlet identifier
177      * @return the DocumentPortlet object
178      */
179     public Portlet load( int nPortletId )
180     {
181         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
182         daoUtil.setInt( 1, nPortletId );
183         daoUtil.executeQuery(  );
184 
185         DocumentPortletent/business/portlet/DocumentPortlet.html#DocumentPortlet">DocumentPortlet portlet = new DocumentPortlet(  );
186 
187         if ( daoUtil.next(  ) )
188         {
189             portlet.setId( daoUtil.getInt( 1 ) );
190             portlet.setDocumentTypeCode( daoUtil.getString( 2 ) );
191         }
192 
193         daoUtil.free(  );
194 
195         portlet.setIdCategory( loadCategories( nPortletId ) );
196 
197         return portlet;
198     }
199 
200     /**
201      * Load a list of Id categories
202      *
203      * @param nPortletId
204      * @return Array of categories
205      */
206     private int[] loadCategories( int nPortletId )
207     {
208         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORY_PORTLET );
209         daoUtil.setInt( 1, nPortletId );
210         daoUtil.executeQuery(  );
211 
212         Collection<Integer> nListIdCategory = new ArrayList<Integer>(  );
213 
214         while ( daoUtil.next(  ) )
215         {
216             nListIdCategory.add( daoUtil.getInt( 1 ) );
217         }
218 
219         daoUtil.free(  );
220 
221         int[] nArrayIdCategory = new int[nListIdCategory.size(  )];
222         int i = 0;
223 
224         for ( Integer nIdCategory : nListIdCategory )
225         {
226             nArrayIdCategory[i++] = nIdCategory.intValue(  );
227         }
228 
229         return nArrayIdCategory;
230     }
231 
232     /**
233      * Update the record in the table
234      *
235      * @param portlet
236      *            A portlet
237      */
238     public void store( Portlet portlet )
239     {
240         DocumentPortlet./../../../../../fr/paris/lutece/plugins/document/business/portlet/DocumentPortlet.html#DocumentPortlet">DocumentPortlet p = (DocumentPortlet) portlet;
241         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
242         daoUtil.setInt( 1, p.getId(  ) );
243         daoUtil.setString( 2, p.getDocumentTypeCode(  ) );
244         daoUtil.setInt( 3, p.getId(  ) );
245 
246         daoUtil.executeUpdate(  );
247 
248         daoUtil.free(  );
249 
250         deleteCategories( p.getId(  ) );
251         insertCategory( p );
252     }
253 
254     /**
255      * Returns a list of couple id_portlet/name filtered by documentType and
256      * category
257      *
258      * @param nDocumentId
259      *            the Document ID
260      * @param strCodeDocumentType
261      *            the code
262      * @param pOrder
263      *            the order of the portlets
264      * @return A collection of referenceItem
265      */
266     public Collection<ReferenceItem> selectByDocumentOdAndDocumentType( int nDocumentId, String strCodeDocumentType,
267         PortletOrder pOrder, PortletFilter pFilter )
268     {
269         StringBuilder strSQl = new StringBuilder(  );
270         strSQl.append( SQL_QUERY_SELECT_DOCUMENTS_BY_TYPE_AND_CATEGORY );
271 
272         String strFilter = ( pFilter != null ) ? pFilter.getSQLFilter(  ) : null;
273 
274         if ( strFilter != null )
275         {
276             strSQl.append( SQL_QUERY_CASE_AND );
277             strSQl.append( strFilter );
278         }
279 
280         strSQl.append( pOrder.getSQLOrderBy(  ) );
281 
282         DAOUtil daoUtil = new DAOUtil( strSQl.toString(  ) );
283         daoUtil.setInt( 1, nDocumentId );
284         daoUtil.setString( 2, strCodeDocumentType );
285         daoUtil.setInt( 3, nDocumentId );
286 
287         if ( strFilter != null )
288         {
289             if ( pFilter.getPortletFilterType(  ).equals( PortletFilter.PAGE_NAME ) )
290             {
291                 for ( int i = 0; i < pFilter.getPageName(  ).length; i++ )
292                 {
293                     daoUtil.setString( i + 4, "%" + pFilter.getPageName(  )[i] + "%" );
294                 }
295             }
296             else if ( pFilter.getPortletFilterType(  ).equals( PortletFilter.PORTLET_NAME ) )
297             {
298                 for ( int i = 0; i < pFilter.getPortletName(  ).length; i++ )
299                 {
300                     daoUtil.setString( i + 4, "%" + pFilter.getPortletName(  )[i] + "%" );
301                 }
302             }
303             else if ( pFilter.getPortletFilterType(  ).equals( PortletFilter.PAGE_ID ) )
304             {
305                 daoUtil.setInt( 4, pFilter.getIdPage(  ) );
306             }
307         }
308 
309         daoUtil.executeQuery(  );
310 
311         ReferenceList list = new ReferenceList(  );
312 
313         while ( daoUtil.next(  ) )
314         {
315             list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
316         }
317 
318         daoUtil.free(  );
319 
320         return list;
321     }
322 
323     /**
324      * Tests if is a portlet is portlet type alias
325      *
326      * @param nPortletId
327      *            The identifier of the document
328      * @return true if the portlet is alias, false otherwise
329      */
330     public boolean checkIsAliasPortlet( int nPortletId )
331     {
332         boolean bIsAlias = false;
333         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_IS_ALIAS );
334 
335         daoUtil.setInt( 1, nPortletId );
336         daoUtil.executeQuery(  );
337 
338         if ( daoUtil.next(  ) )
339         {
340             bIsAlias = true;
341         }
342 
343         daoUtil.free(  );
344 
345         return bIsAlias;
346     }
347 
348     /**
349      *
350      *{@inheritDoc}
351      */
352     public List<Integer> selectPortletsByDocumentId( int nDocumentId )
353     {
354         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLETS_BY_DOCUMENT_ID );
355         daoUtil.setInt( 1, nDocumentId );
356         daoUtil.executeQuery(  );
357 
358         List<Integer> list = new ArrayList<Integer>(  );
359 
360         while ( daoUtil.next(  ) )
361         {
362             list.add( daoUtil.getInt( 1 ) );
363         }
364 
365         daoUtil.free(  );
366 
367         return list;
368     }
369 }