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