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 java.sql.Timestamp;
37  import java.util.ArrayList;
38  import java.util.Collection;
39  import java.util.Date;
40  import java.util.List;
41  
42  import fr.paris.lutece.portal.service.plugin.Plugin;
43  import fr.paris.lutece.util.sql.DAOUtil;
44  
45  public class BlogPublicationDAO implements IBlogPublicationDAO
46  {
47  
48      // Category
49      private static final String SQL_QUERY_INSERT_BLOGS_PORTLET = "INSERT INTO blog_list_portlet_htmldocs ( id_portlet , id_blog, date_begin_publishing, date_end_publishing, status, document_order ) VALUES ( ? , ?, ?, ?, ?, ? )";
50      private static final String SQL_QUERY_DELETE_BLOGS_PORTLET = " DELETE FROM blog_list_portlet_htmldocs WHERE id_blog = ? ";
51      private static final String SQL_QUERY_DELETE_BLOGS_PORTLET_BY_ID_PORTLET = " DELETE FROM blog_list_portlet_htmldocs WHERE id_portlet = ? ";
52      private static final String SQL_QUERY_SELECT_CATEGORY_PORTLET = "SELECT id_portlet , id_blog, date_begin_publishing, date_end_publishing, status, document_order FROM blog_list_portlet_htmldocs WHERE id_blog = ? order by document_order ";
53      private static final String SQL_QUERY_REMOVE_BLOGS_PORTLET = " DELETE FROM blog_list_portlet_htmldocs WHERE id_portlet = ? and id_blog= ?";
54  
55      private static final String SQL_QUERY_UPDATE_BLOGS_PORTLET = "UPDATE blog_list_portlet_htmldocs set id_portlet= ?, id_blog= ?, date_begin_publishing= ?, date_end_publishing= ?, status= ?, document_order= ? WHERE  id_blog= ?";
56      private static final String SQL_QUERY_SELECT_PUBLICATION_PORTLET = "SELECT id_portlet , id_blog, date_begin_publishing, date_end_publishing, status, document_order FROM blog_list_portlet_htmldocs WHERE id_blog = ? and id_portlet = ? order by document_order";
57      private static final String SQL_QUERY_SELECT_PUBLICATION_ALL = "SELECT id_portlet , id_blog, date_begin_publishing, date_end_publishing, status, document_order FROM blog_list_portlet_htmldocs order by document_order";
58      private static final String SQL_QUERY_SELECT_DOC_PUBLICATION_BY_PORTLET = "SELECT id_portlet , id_blog, date_begin_publishing, date_end_publishing, status, document_order FROM blog_list_portlet_htmldocs WHERE id_portlet = ? order by document_order ";
59      private static final String SQL_QUERY_SELECT_BY_DATE_PUBLISHING_AND_STATUS = "SELECT id_portlet, id_blog, document_order, date_begin_publishing FROM blog_list_portlet_htmldocs WHERE date_begin_publishing >= ? AND date_end_publishing >= ? AND status = ? ORDER BY document_order ";
60      private static final String SQL_QUERY_SELECT_DOC_PUBLICATION_BY_PORTLET_AND_PUBLICATION_DATE = "SELECT id_portlet , id_blog, date_begin_publishing, date_end_publishing, status, document_order FROM blog_list_portlet_htmldocs WHERE id_portlet = ? AND date_begin_publishing <= ? AND date_end_publishing >= ? order by document_order ";
61      private static final String SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS = " SELECT DISTINCT pub.id_blog FROM blog_list_portlet_htmldocs pub WHERE  pub.status = ? AND pub.date_begin_publishing <= ? AND  pub.date_end_publishing >= ? AND pub.id_portlet IN ";
62      private static final String SQL_QUERY_SELECT_LAST_BY_PORTLET_ID_AND_STATUS = "SELECT DISTINCT pub.id_blog FROM blog_list_portlet_htmldocs pub, blog_blog doc WHERE doc.id_blog=pub.id_blog AND pub.status=? AND doc.update_date >=? AND pub.date_begin_publishing <= ? AND pub.date_end_publishing >= ? AND pub.id_portlet IN ";
63  
64      private static final String SQL_QUERY_COUNT_DOC_PUBLICATION_BY_BLOG_AND_PUBLICATION_DATE = "SELECT count(id_blog) FROM blog_list_portlet_htmldocs WHERE id_blog = ? AND date_begin_publishing <= ? AND date_end_publishing >= ?";
65      private static final String SQL_FILTER_BEGIN = " (";
66      private static final String SQL_TAGS_END = ") ";
67      private static final String CONSTANT_QUESTION_MARK = "?";
68      private static final String CONSTANT_COMMA = ",";
69  
70      // /////////////////////////////////////////////////////////////////////////////////////
71      // Access methods to data
72  
73      /**
74       * {@inheritDoc }
75       */
76      @Override
77      public void insertBlogsId( BlogPublication blogPublication, Plugin plugin )
78      {
79          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_BLOGS_PORTLET, plugin ) )
80          {
81              daoUtil.setInt( 1, blogPublication.getIdPortlet( ) );
82              daoUtil.setInt( 2, blogPublication.getIdBlog( ) );
83              daoUtil.setDate( 3, blogPublication.getDateBeginPublishing( ) );
84              daoUtil.setDate( 4, blogPublication.getDateEndPublishing( ) );
85              daoUtil.setInt( 5, blogPublication.getStatus( ) );
86              daoUtil.setInt( 6, blogPublication.getBlogOrder( ) );
87  
88              daoUtil.executeUpdate( );
89          }
90      }
91  
92      /**
93       * {@inheritDoc }
94       */
95      @Override
96      public void store( BlogPublication blogPublication, Plugin plugin )
97      {
98          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_BLOGS_PORTLET, plugin ) )
99          {
100             daoUtil.setInt( 1, blogPublication.getIdPortlet( ) );
101             daoUtil.setInt( 2, blogPublication.getIdBlog( ) );
102             daoUtil.setDate( 3, blogPublication.getDateBeginPublishing( ) );
103             daoUtil.setDate( 4, blogPublication.getDateEndPublishing( ) );
104             daoUtil.setInt( 5, blogPublication.getStatus( ) );
105             daoUtil.setInt( 6, blogPublication.getBlogOrder( ) );
106 
107             daoUtil.setInt( 7, blogPublication.getIdBlog( ) );
108 
109             daoUtil.executeUpdate( );
110         }
111     }
112 
113     /**
114      * {@inheritDoc }
115      */
116     @Override
117     public void deleteBlogsId( int nDocId, Plugin plugin )
118     {
119         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BLOGS_PORTLET, plugin ) )
120         {
121             daoUtil.setInt( 1, nDocId );
122             daoUtil.executeUpdate( );
123         }
124     }
125 
126     /**
127      * {@inheritDoc }
128      */
129     @Override
130     public void deleteBlogByIdPortlet( int nIdPortlet, Plugin plugin )
131     {
132         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BLOGS_PORTLET_BY_ID_PORTLET, plugin ) )
133         {
134             daoUtil.setInt( 1, nIdPortlet );
135             daoUtil.executeUpdate( );
136         }
137     }
138 
139     /**
140      * {@inheritDoc }
141      */
142     @Override
143     public void remove( int nDocId, int nIdPortlet, Plugin plugin )
144     {
145         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_BLOGS_PORTLET, plugin ) )
146         {
147             daoUtil.setInt( 1, nIdPortlet );
148             daoUtil.setInt( 2, nDocId );
149 
150             daoUtil.executeUpdate( );
151         }
152     }
153 
154     /**
155      * {@inheritDoc }
156      */
157     @Override
158     public List<BlogPublication> loadBlogsId( int nDocId, Plugin plugin )
159     {
160         List<BlogPublication> nListIdCategory = new ArrayList<>( );
161         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORY_PORTLET, plugin ) )
162         {
163             daoUtil.setInt( 1, nDocId );
164             daoUtil.executeQuery( );
165 
166             while ( daoUtil.next( ) )
167             {
168                 BlogPublicationbusiness/portlet/BlogPublication.html#BlogPublication">BlogPublication blogPub = new BlogPublication( );
169                 blogPub.setIdPortlet( daoUtil.getInt( 1 ) );
170                 blogPub.setIdBlog( daoUtil.getInt( 2 ) );
171                 blogPub.setDateBeginPublishing( daoUtil.getDate( 3 ) );
172                 blogPub.setDateEndPublishing( daoUtil.getDate( 4 ) );
173                 blogPub.setStatus( daoUtil.getInt( 5 ) );
174                 blogPub.setBlogOrder( daoUtil.getInt( 6 ) );
175 
176                 nListIdCategory.add( blogPub );
177             }
178         }
179         return nListIdCategory;
180     }
181 
182     /**
183      * {@inheritDoc }
184      */
185     @Override
186     public List<BlogPublication> loadBlogsByPortlet( int nIdPortlet, Plugin plugin )
187     {
188         List<BlogPublication> nListIdCategory = new ArrayList<>( );
189         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DOC_PUBLICATION_BY_PORTLET, plugin ) )
190         {
191             daoUtil.setInt( 1, nIdPortlet );
192             daoUtil.executeQuery( );
193 
194             while ( daoUtil.next( ) )
195             {
196                 BlogPublicationbusiness/portlet/BlogPublication.html#BlogPublication">BlogPublication blogPub = new BlogPublication( );
197                 blogPub.setIdPortlet( daoUtil.getInt( 1 ) );
198                 blogPub.setIdBlog( daoUtil.getInt( 2 ) );
199                 blogPub.setDateBeginPublishing( daoUtil.getDate( 3 ) );
200                 blogPub.setDateEndPublishing( daoUtil.getDate( 4 ) );
201                 blogPub.setStatus( daoUtil.getInt( 5 ) );
202                 blogPub.setBlogOrder( daoUtil.getInt( 6 ) );
203 
204                 nListIdCategory.add( blogPub );
205             }
206         }
207         return nListIdCategory;
208     }
209 
210     /**
211      * {@inheritDoc }
212      */
213     @Override
214     public List<BlogPublication> loadBlogsByPortletAndPublicationDate( int nIdPortlet, Date datePublishing, Date dateEndPublishing, Plugin plugin )
215     {
216         List<BlogPublication> nListIdCategory = new ArrayList<>( );
217         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DOC_PUBLICATION_BY_PORTLET_AND_PUBLICATION_DATE, plugin ) )
218         {
219             daoUtil.setInt( 1, nIdPortlet );
220             daoUtil.setTimestamp( 2, new Timestamp( datePublishing.getTime( ) ) );
221             daoUtil.setTimestamp( 3, new Timestamp( dateEndPublishing.getTime( ) ) );
222 
223             daoUtil.executeQuery( );
224 
225             while ( daoUtil.next( ) )
226             {
227                 BlogPublicationbusiness/portlet/BlogPublication.html#BlogPublication">BlogPublication blogPub = new BlogPublication( );
228                 blogPub.setIdPortlet( daoUtil.getInt( 1 ) );
229                 blogPub.setIdBlog( daoUtil.getInt( 2 ) );
230                 blogPub.setDateBeginPublishing( daoUtil.getDate( 3 ) );
231                 blogPub.setDateEndPublishing( daoUtil.getDate( 4 ) );
232                 blogPub.setStatus( daoUtil.getInt( 5 ) );
233                 blogPub.setBlogOrder( daoUtil.getInt( 6 ) );
234 
235                 nListIdCategory.add( blogPub );
236             }
237         }
238         return nListIdCategory;
239     }
240 
241     /**
242      * {@inheritDoc }
243      */
244     @Override
245     public BlogPublication loadBlogsPublication( int nPortletId, int nDocId, Plugin plugin )
246     {
247         BlogPublication blogPub = null;
248         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PUBLICATION_PORTLET, plugin ) )
249         {
250             daoUtil.setInt( 1, nDocId );
251             daoUtil.setInt( 2, nPortletId );
252             daoUtil.executeQuery( );
253 
254             if ( daoUtil.next( ) )
255             {
256                 blogPub = new BlogPublication( );
257                 blogPub.setIdPortlet( daoUtil.getInt( 1 ) );
258                 blogPub.setIdBlog( daoUtil.getInt( 2 ) );
259                 blogPub.setDateBeginPublishing( daoUtil.getDate( 3 ) );
260                 blogPub.setDateEndPublishing( daoUtil.getDate( 4 ) );
261                 blogPub.setStatus( daoUtil.getInt( 5 ) );
262                 blogPub.setBlogOrder( daoUtil.getInt( 6 ) );
263             }
264         }
265         return blogPub;
266     }
267 
268     /**
269      * {@inheritDoc }
270      */
271     @Override
272     public List<BlogPublication> loadAllBlogsPublication( Plugin plugin )
273     {
274         List<BlogPublication> nListIdCategory = new ArrayList<>( );
275         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PUBLICATION_ALL, plugin ) )
276         {
277             daoUtil.executeQuery( );
278 
279             while ( daoUtil.next( ) )
280             {
281                 BlogPublicationbusiness/portlet/BlogPublication.html#BlogPublication">BlogPublication blogPub = new BlogPublication( );
282                 blogPub.setIdPortlet( daoUtil.getInt( 1 ) );
283                 blogPub.setIdBlog( daoUtil.getInt( 2 ) );
284                 blogPub.setDateBeginPublishing( daoUtil.getDate( 3 ) );
285                 blogPub.setDateEndPublishing( daoUtil.getDate( 4 ) );
286                 blogPub.setStatus( daoUtil.getInt( 5 ) );
287                 blogPub.setBlogOrder( daoUtil.getInt( 6 ) );
288 
289                 nListIdCategory.add( blogPub );
290             }
291         }
292         return nListIdCategory;
293     }
294 
295     /**
296      * {@inheritDoc }
297      */
298     @Override
299     public Collection<BlogPublication> selectSinceDatePublishingAndStatus( Date datePublishing, Date dateEndPublication, int nStatus, Plugin plugin )
300     {
301         Collection<BlogPublication> listBlogPublication = new ArrayList<>( );
302         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DATE_PUBLISHING_AND_STATUS, plugin ) )
303         {
304             daoUtil.setTimestamp( 1, new Timestamp( datePublishing.getTime( ) ) );
305             daoUtil.setTimestamp( 2, new Timestamp( dateEndPublication.getTime( ) ) );
306 
307             daoUtil.setInt( 3, nStatus );
308             daoUtil.executeQuery( );
309 
310             while ( daoUtil.next( ) )
311             {
312                 BlogPublication/portlet/BlogPublication.html#BlogPublication">BlogPublication blogPublication = new BlogPublication( );
313                 blogPublication.setIdPortlet( daoUtil.getInt( 1 ) );
314                 blogPublication.setIdBlog( daoUtil.getInt( 2 ) );
315                 blogPublication.setBlogOrder( daoUtil.getInt( 3 ) );
316                 blogPublication.setStatus( nStatus );
317                 blogPublication.setDateBeginPublishing( daoUtil.getDate( 4 ) );
318                 listBlogPublication.add( blogPublication );
319             }
320         }
321         return listBlogPublication;
322     }
323 
324     /**
325      * {@inheritDoc}
326      */
327     @Override
328     public List<Integer> getPublishedBlogsIdsListByPortletIds( int [ ] nPortletsIds, Date datePublishing, Date dateEndPublishing, Plugin plugin )
329     {
330         List<Integer> listIds = new ArrayList<>( );
331         if ( nPortletsIds == null || nPortletsIds.length == 0 )
332         {
333             return listIds;
334         }
335         StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS );
336         sbSql.append( SQL_FILTER_BEGIN );
337 
338         for ( int i = 0; i < nPortletsIds.length; i++ )
339         {
340             sbSql.append( CONSTANT_QUESTION_MARK );
341             if ( i + 1 < nPortletsIds.length )
342             {
343                 sbSql.append( CONSTANT_COMMA );
344             }
345         }
346         sbSql.append( SQL_TAGS_END );
347 
348         try ( DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin ) )
349         {
350             int nIndex = 1;
351             daoUtil.setInt( nIndex++, 1 );
352             daoUtil.setTimestamp( nIndex++, new Timestamp( datePublishing.getTime( ) ) );
353             daoUtil.setTimestamp( nIndex++, new Timestamp( dateEndPublishing.getTime( ) ) );
354 
355             for ( int nPortletId : nPortletsIds )
356             {
357                 daoUtil.setInt( nIndex++, nPortletId );
358             }
359             daoUtil.executeQuery( );
360             while ( daoUtil.next( ) )
361             {
362                 listIds.add( daoUtil.getInt( 1 ) );
363             }
364         }
365         return listIds;
366 
367     }
368 
369     /**
370      * {@inheritDoc}
371      */
372     @Override
373     public List<Integer> getLastPublishedBlogsIdsListByPortletIds( int [ ] nPortletsIds, Date dateUpdated, Plugin plugin )
374     {
375         List<Integer> listIds = new ArrayList<>( );
376         if ( nPortletsIds == null || nPortletsIds.length == 0 )
377         {
378             return listIds;
379         }
380         StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT_LAST_BY_PORTLET_ID_AND_STATUS );
381         sbSql.append( SQL_FILTER_BEGIN );
382 
383         for ( int i = 0; i < nPortletsIds.length; i++ )
384         {
385             sbSql.append( CONSTANT_QUESTION_MARK );
386             if ( i + 1 < nPortletsIds.length )
387             {
388                 sbSql.append( CONSTANT_COMMA );
389             }
390         }
391         sbSql.append( SQL_TAGS_END );
392 
393         try ( DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin ) )
394         {
395             int nIndex = 1;
396             daoUtil.setInt( nIndex++, 1 );
397             daoUtil.setTimestamp( nIndex++, new Timestamp( dateUpdated.getTime( ) ) );
398             daoUtil.setTimestamp( nIndex++, new Timestamp( System.currentTimeMillis( ) ) );
399             daoUtil.setTimestamp( nIndex++, new Timestamp( System.currentTimeMillis( ) ) );
400 
401             for ( int nPortletId : nPortletsIds )
402             {
403                 daoUtil.setInt( nIndex++, nPortletId );
404             }
405             daoUtil.executeQuery( );
406             while ( daoUtil.next( ) )
407             {
408                 listIds.add( daoUtil.getInt( 1 ) );
409             }
410         }
411         return listIds;
412 
413     }
414 
415     @Override
416     public int countPublicationByIdBlogAndDate( int nIdBlog, Date date, Plugin plugin )
417     {
418         int count = 0;
419         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_DOC_PUBLICATION_BY_BLOG_AND_PUBLICATION_DATE, plugin ) )
420         {
421             Timestamp timestamp = new Timestamp( date.getTime( ) );
422             int nIndex = 0;
423             daoUtil.setInt( ++nIndex, nIdBlog );
424             daoUtil.setTimestamp( ++nIndex, timestamp );
425             daoUtil.setTimestamp( ++nIndex, timestamp );
426             daoUtil.executeQuery( );
427             if ( daoUtil.next( ) )
428             {
429                 count = daoUtil.getInt( 1 );
430             }
431         }
432         return count;
433     }
434 }