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.blog.business.portlet;
35  
36  import fr.paris.lutece.portal.business.portlet.Portlet;
37  import fr.paris.lutece.portal.service.plugin.Plugin;
38  import fr.paris.lutece.util.ReferenceItem;
39  import fr.paris.lutece.util.ReferenceList;
40  import fr.paris.lutece.util.sql.DAOUtil;
41  
42  import java.util.ArrayList;
43  import java.util.Collection;
44  import java.util.HashMap;
45  import java.util.List;
46  import java.util.Map;
47  
48  /**
49   * This class provides Data Access methods for BlogsListPortlet objects
50   */
51  public final class BlogListPortletDAO implements IBlogListPortletDAO
52  {
53  
54      private static final String SQL_QUERY_SELECTALL = "SELECT id_portlet , id_page_template_document FROM blog_list_portlet ";
55      private static final String SQL_QUERY_INSERT = "INSERT INTO blog_list_portlet ( id_portlet , id_page_template_document ) VALUES ( ? , ? )";
56      private static final String SQL_QUERY_SELECT = "SELECT id_portlet , id_page_template_document FROM blog_list_portlet WHERE id_portlet = ? ";
57      private static final String SQL_QUERY_UPDATE = "UPDATE blog_list_portlet SET id_portlet = ?, id_page_template_document = ? WHERE id_portlet = ? ";
58      private static final String SQL_QUERY_DELETE = "DELETE FROM blog_list_portlet WHERE id_portlet= ? ";
59      private static final String SQL_QUERY_CHECK_IS_ALIAS = "SELECT id_alias FROM core_portlet_alias WHERE id_alias = ?";
60  
61      private static final String SQL_QUERY_SELECT_PORTLET_BY_TYPE = "SELECT DISTINCT b.id_portlet , a.name, a.date_update " + "FROM blog_list_portlet b "
62              + "LEFT JOIN blog_list_portlet_htmldocs c ON b.id_portlet = c.id_portlet AND c.id_blog= ? "
63              + "INNER JOIN core_portlet a ON b.id_portlet = a.id_portlet " + "INNER JOIN core_page f ON a.id_page = f.id_page WHERE c.id_portlet IS NULL ";
64      // Category
65      private static final String SQL_QUERY_INSERT_BLOGS_PORTLET = "INSERT INTO blog_list_portlet_htmldocs ( id_portlet , id_blog, status, document_order ) VALUES ( ? , ?, ?, ? )";
66      private static final String SQL_QUERY_INSERT_BLOGS_PORTLET_ON_UPDATE = "INSERT INTO blog_list_portlet_htmldocs ( id_portlet , id_blog, status, document_order, date_begin_publishing, date_end_publishing) VALUES ( ? , ?, ?, ?, ?, ? )";
67      private static final String SQL_QUERY_DELETE_BLOGS_PORTLET = " DELETE FROM blog_list_portlet_htmldocs WHERE id_portlet = ? ";
68      private static final String SQL_QUERY_SELECT_CATEGORY_PORTLET = "SELECT id_blog, document_order, date_begin_publishing, date_end_publishing, status FROM blog_list_portlet_htmldocs WHERE id_portlet = ? order by document_order ";
69      private static final String SQL_QUERY_SELECT_PAGE_PORTLET = "SELECT id_page_template_document,description from  blog_page_template where portlet_type= ?";
70  
71      private static final String SQL_QUERY_SELECT_MIN_DOC_ORDER = "SELECT MIN( document_order ) FROM blog_list_portlet_htmldocs ";
72  
73      // /////////////////////////////////////////////////////////////////////////////////////
74      // Access methods to data
75  
76      /**
77       * {@inheritDoc }
78       */
79      @Override
80      public void insert( Portlet portlet )
81      {
82          BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
83  
84          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
85          {
86              daoUtil.setInt( 1, p.getId( ) );
87              daoUtil.setInt( 2, p.getPageTemplateDocument( ) );
88  
89              daoUtil.executeUpdate( );
90          }
91  
92          insertBlogsId( portlet );
93      }
94  
95      /**
96       * Insert a list of doc for a specified portlet
97       * 
98       * @param portlet
99       *            the DocumentListPortlet to insert
100      */
101     private void insertBlogsId( Portlet portlet )
102     {
103         BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
104 
105         if ( !p.getArrayBlogs( ).isEmpty( ) )
106         {
107             try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_BLOGS_PORTLET ) )
108             {
109 
110                 for ( BlogPublication docPub : p.getArrayBlogs( ) )
111                 {
112                     daoUtil.setInt( 1, p.getId( ) );
113                     daoUtil.setInt( 2, docPub.getIdBlog( ) );
114                     daoUtil.setInt( 3, 1 );
115                     daoUtil.setInt( 4, docPub.getBlogOrder( ) );
116                     daoUtil.executeUpdate( );
117                 }
118 
119             }
120         }
121     }
122 
123     /**
124      * Insert a list of blog publication for a specified portlet
125      * 
126      * @param portlet
127      *            the DocumentListPortlet to insert
128      */
129     private void insertBlogsPublicationOnUpdate( Portlet portlet )
130     {
131         BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
132 
133         if ( !p.getArrayBlogs( ).isEmpty( ) )
134         {
135             try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_BLOGS_PORTLET_ON_UPDATE ) )
136             {
137 
138                 for ( BlogPublication docPub : p.getArrayBlogs( ) )
139                 {
140                     daoUtil.setInt( 1, p.getId( ) );
141                     daoUtil.setInt( 2, docPub.getIdBlog( ) );
142                     daoUtil.setInt( 3, 1 );
143                     daoUtil.setInt( 4, docPub.getBlogOrder( ) );
144                     daoUtil.setDate( 5, docPub.getDateBeginPublishing( ) );
145                     daoUtil.setDate( 6, docPub.getDateEndPublishing( ) );
146                     daoUtil.executeUpdate( );
147                 }
148 
149             }
150         }
151     }
152 
153     /**
154      * {@inheritDoc }
155      */
156     @Override
157     public void delete( int nPortletId )
158     {
159         deleteHtmlsDocsId( nPortletId );
160         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
161         {
162             daoUtil.setInt( 1, nPortletId );
163             daoUtil.executeUpdate( );
164         }
165 
166     }
167 
168     /**
169      * Delete docs for the specified portlet
170      * 
171      * @param nPortletId
172      *            The portlet identifier
173      */
174     private void deleteHtmlsDocsId( int nPortletId )
175     {
176         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BLOGS_PORTLET ) )
177         {
178             daoUtil.setInt( 1, nPortletId );
179             daoUtil.executeUpdate( );
180         }
181     }
182 
183     /**
184      * {@inheritDoc }
185      */
186     @Override
187     public Portlet load( int nPortletId )
188     {
189         BlogListPortletbusiness/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet portlet = new BlogListPortlet( );
190         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
191         {
192             daoUtil.setInt( 1, nPortletId );
193             daoUtil.executeQuery( );
194 
195             if ( daoUtil.next( ) )
196             {
197                 portlet.setId( daoUtil.getInt( 1 ) );
198                 portlet.setPageTemplateDocument( daoUtil.getInt( 2 ) );
199             }
200         }
201 
202         portlet.setArrayBlogs( loadBlogsId( nPortletId ) );
203         return portlet;
204     }
205 
206     /**
207      * {@inheritDoc }
208      */
209     @Override
210     public Map<Integer, String> loadPages( String strPortletType )
211     {
212         Map<Integer, String> page = new HashMap<>( );
213         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PAGE_PORTLET ) )
214         {
215             daoUtil.setString( 1, strPortletType );
216             daoUtil.executeQuery( );
217 
218             while ( daoUtil.next( ) )
219             {
220                 page.put( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
221             }
222         }
223         return page;
224     }
225 
226     /**
227      * Load a list of Id Blogs
228      * 
229      * @param nPortletId
230      * @return List of IdDoc
231      */
232     private List<BlogPublication> loadBlogsId( int nPortletId )
233     {
234         List<BlogPublication> listDocPublication = new ArrayList<>( );
235         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORY_PORTLET ) )
236         {
237             daoUtil.setInt( 1, nPortletId );
238             daoUtil.executeQuery( );
239 
240             while ( daoUtil.next( ) )
241             {
242                 BlogPublication/business/portlet/BlogPublication.html#BlogPublication">BlogPublication docPub = new BlogPublication( );
243                 docPub.setIdBlog( daoUtil.getInt( 1 ) );
244                 docPub.setBlogOrder( daoUtil.getInt( 2 ) );
245                 docPub.setDateBeginPublishing( daoUtil.getDate( 3 ) );
246                 docPub.setDateEndPublishing( daoUtil.getDate( 4 ) );
247                 docPub.setStatus( daoUtil.getInt( 5 ) );
248                 listDocPublication.add( docPub );
249 
250             }
251 
252         }
253         return listDocPublication;
254     }
255 
256     /**
257      * {@inheritDoc }
258      */
259     @Override
260     public void store( Portlet portlet )
261     {
262         BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
263         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
264         {
265             daoUtil.setInt( 1, p.getId( ) );
266             daoUtil.setInt( 2, p.getPageTemplateDocument( ) );
267             daoUtil.setInt( 3, p.getId( ) );
268 
269             daoUtil.executeUpdate( );
270         }
271 
272         deleteHtmlsDocsId( p.getId( ) );
273         insertBlogsPublicationOnUpdate( p );
274     }
275 
276     /**
277      * {@inheritDoc }
278      */
279     @Override
280     public boolean checkIsAliasPortlet( int nPortletId )
281     {
282         boolean bIsAlias = false;
283         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_IS_ALIAS ) )
284         {
285             daoUtil.setInt( 1, nPortletId );
286             daoUtil.executeQuery( );
287 
288             if ( daoUtil.next( ) )
289             {
290                 bIsAlias = true;
291             }
292         }
293         return bIsAlias;
294     }
295 
296     /**
297      * {@inheritDoc }
298      */
299     @Override
300     public ReferenceList selectBlogListPortletReferenceList( Plugin plugin )
301     {
302         ReferenceList blogPortletList = new ReferenceList( );
303         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
304         {
305             daoUtil.executeQuery( );
306 
307             while ( daoUtil.next( ) )
308             {
309                 blogPortletList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
310             }
311 
312         }
313         return blogPortletList;
314     }
315 
316     /**
317      * {@inheritDoc }
318      */
319     @Override
320     public Collection<ReferenceItem> selectPortletByType( int nDocumentId, PortletOrder pOrder, PortletFilter pFilter )
321     {
322         StringBuilder strSQl = new StringBuilder( );
323         strSQl.append( SQL_QUERY_SELECT_PORTLET_BY_TYPE );
324 
325         String strFilter = ( pFilter != null ) ? pFilter.getSQLFilter( ) : null;
326 
327         if ( strFilter != null )
328         {
329             strSQl.append( "AND" );
330             strSQl.append( strFilter );
331         }
332 
333         strSQl.append( pOrder.getSQLOrderBy( ) );
334 
335         ReferenceList list = new ReferenceList( );
336         try ( DAOUtil daoUtil = new DAOUtil( strSQl.toString( ) ) )
337         {
338 
339             daoUtil.setInt( 1, nDocumentId );
340 
341             if ( strFilter != null )
342             {
343                 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_NAME ) )
344                 {
345                     for ( int i = 0; i < pFilter.getPageName( ).length; i++ )
346                     {
347                         daoUtil.setString( i + 2, "%" + pFilter.getPageName( ) [i] + "%" );
348                     }
349                 }
350                 else
351                     if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PORTLET_NAME ) )
352                     {
353                         for ( int i = 0; i < pFilter.getPortletName( ).length; i++ )
354                         {
355                             daoUtil.setString( i + 2, "%" + pFilter.getPortletName( ) [i] + "%" );
356                         }
357                     }
358                     else
359                         if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_ID ) )
360                         {
361                             daoUtil.setInt( 2, pFilter.getIdPage( ) );
362                         }
363             }
364 
365             daoUtil.executeQuery( );
366 
367             while ( daoUtil.next( ) )
368             {
369                 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
370             }
371         }
372 
373         return list;
374     }
375 
376     /**
377      * {@inheritDoc }
378      */
379     @Override
380     public int selectMinDocumentBlogOrder( )
381     {
382         int nKey = 1;
383         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MIN_DOC_ORDER ) )
384         {
385             daoUtil.executeQuery( );
386 
387             if ( daoUtil.next( ) )
388             {
389                 nKey = daoUtil.getInt( 1 );
390             }
391         }
392         return nKey;
393     }
394 
395 }