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;
35  
36  import java.util.ArrayList;
37  import java.util.List;
38  
39  import org.apache.commons.lang.BooleanUtils;
40  import org.apache.commons.lang.StringUtils;
41  
42  import fr.paris.lutece.portal.service.plugin.Plugin;
43  import fr.paris.lutece.portal.service.util.AppLogService;
44  import fr.paris.lutece.util.ReferenceList;
45  import fr.paris.lutece.util.sql.DAOUtil;
46  
47  /**
48   * This class provides Data Access methods for Blog objects
49   */
50  public final class BlogDAO implements IBlogDAO
51  {
52      // Constants
53      private static final String SQL_QUERY_NEW_PK = "SELECT max( id_blog ) FROM blog_blog";
54      private static final String SQL_QUERY_NEW_PK_VERSION = "SELECT max( id_version ) FROM blog_versions";
55      private static final String SQL_QUERY_SELECT = "SELECT id_blog,  version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description,  shareable, url FROM blog_blog WHERE id_blog = ?";
56      private static final String SQL_QUERY_SELECT_LAST_DOCUMENTS = "SELECT id_blog,  version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description,  shareable, url FROM blog_blog ORDER BY update_date DESC LIMIT ?";
57      private static final String SQL_QUERY_SELECT_BY_NAME = "SELECT id_blog,  version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description, shareable, url FROM blog_blog WHERE content_label = ?";
58      private static final String SQL_QUERY_SELECT_VERSION = "SELECT id_blog, version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description, shareable, url FROM blog_versions WHERE id_blog = ? AND version = ? ";
59      private static final String SQL_QUERY_INSERT = "INSERT INTO blog_blog ( id_blog,  version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description, shareable, url ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
60      private static final String SQL_QUERY_DELETE = "DELETE FROM blog_blog WHERE id_blog = ?";
61      private static final String SQL_QUERY_DELETE_VERSIONS = "DELETE FROM blog_versions WHERE id_blog = ? ";
62      private static final String SQL_QUERY_UPDATE = "UPDATE blog_blog SET id_blog = ?, version = ?, content_label = ?, creation_date = ?, update_date = ?, html_content = ?, user_editor = ?, user_creator = ?, attached_portlet_id = ?, edit_comment = ?, description = ?, shareable = ?, url= ? WHERE id_blog = ?";
63      private static final String SQL_QUERY_SELECTALL = "SELECT id_blog, version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description, shareable, url FROM blog_blog order by creation_date DESC";
64      private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_blog FROM blog_blog ORDER BY creation_date DESC";
65      private static final String SQL_QUERY_SELECTALL_VERSION = "SELECT id_blog, version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description, shareable, url FROM blog_versions where id_blog = ?";
66      private static final String SQL_QUERY_SELECTALL_USERS_EDITED_BLOG_VERSION = "SELECT distinct user_editor FROM blog_versions where id_blog = ?";
67  
68      private static final String SQL_QUERY_INSERT_VERSION = "INSERT INTO blog_versions ( id_version, id_blog,  version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description, shareable, url ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
69  
70      private static final String SQL_QUERY_SELECT_BY_FILTER = " SELECT DISTINCT a.id_blog, a.version, a.content_label, "
71              + " a.creation_date, a.update_date, a.html_content, a.user_editor, a.user_creator , a.attached_portlet_id , "
72              + " a.edit_comment , a.description, a.shareable, a.url FROM blog_blog a " + " LEFT OUTER JOIN blog_tag_document f ON a.id_blog = f.id_blog"
73              + " LEFT OUTER JOIN blog_list_portlet_htmldocs p ON  a.id_blog = p.id_blog";
74  
75      private static final String SQL_QUERY_SELECT_BLOG_BY_ID_TAG = " SELECT b.id_blog, b.version, b.content_label, b.creation_date, b.update_date, b.html_content, b.user_editor, b.user_creator, b.attached_portlet_id, b.edit_comment, b.description, b.shareable, b.url, a.id_tag FROM blog_tag_document a Inner join blog_blog b on (b.id_blog = a.id_blog) WHERE a.id_tag = ? ORDER BY priority";
76  
77      private static final String SQL_QUERY_SELECT_ALL_BLOG = " SELECT DISTINCT a.id_blog, a.version, a.content_label, a.creation_date, a.update_date, a.html_content, a.user_editor, a.user_creator , a.attached_portlet_id, a.edit_comment , a.description, a.shareable, a.url FROM blog_blog a";
78  
79      private static final String SQL_FILTER_WHERE_CLAUSE = " WHERE ";
80      private static final String SQL_FILTER_AND = " AND ";
81      private static final String SQL_FILTER_TAGS_BEGIN = " (";
82      private static final String SQL_FILTER_TAGS = " f.id_tag = ? ";
83      private static final String SQL_FILTER_TAGS_NULL = " f.id_tag IS NULL ";
84      private static final String SQL_FILTER_TAGS_OR = " OR ";
85      private static final String SQL_FILTER_TAGS_END = ") ";
86      private static final String SQL_FILTER_ID_BEGIN = " (";
87      private static final String SQL_FILTER_ID = " a.id_blog = ? ";
88      private static final String SQL_FILTER_ID_OR = " OR ";
89      private static final String SQL_FILTER_ID_END = ") ";
90      private static final String SQL_ORDER_BY_LAST_MODIFICATION = " ORDER BY a.update_date DESC ";
91      private static final String SQL_ORDER_BY_ORDER_DOCUMENT = " ORDER by p.document_order ";
92  
93      /**
94       * Generates a new primary key
95       * 
96       * @param plugin
97       *            The Plugin
98       * @return The new primary key
99       */
100     public int newPrimaryKey( Plugin plugin )
101     {
102         int nKey = 1;
103         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
104         {
105             daoUtil.executeQuery( );
106             if ( daoUtil.next( ) )
107             {
108                 nKey = daoUtil.getInt( 1 ) + 1;
109             }
110         }
111         return nKey;
112     }
113 
114     /**
115      * Generates a new primary key
116      * 
117      * @param plugin
118      *            The Plugin
119      * @return The new primary key
120      */
121     public int newVersionPrimaryKey( Plugin plugin )
122     {
123         int nKey = 1;
124         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK_VERSION, plugin ) )
125         {
126             daoUtil.executeQuery( );
127 
128             if ( daoUtil.next( ) )
129             {
130                 nKey = daoUtil.getInt( 1 ) + 1;
131             }
132         }
133         return nKey;
134     }
135 
136     /**
137      * {@inheritDoc }
138      */
139     @Override
140     public void insert( Blog blog, Plugin plugin )
141     {
142         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
143         {
144             blog.setId( newPrimaryKey( plugin ) );
145             int nIndex = 1;
146 
147             daoUtil.setInt( nIndex++, blog.getId( ) );
148             daoUtil.setInt( nIndex++, blog.getVersion( ) );
149             daoUtil.setString( nIndex++, blog.getContentLabel( ) );
150             daoUtil.setTimestamp( nIndex++, blog.getCreationDate( ) );
151             daoUtil.setTimestamp( nIndex++, blog.getUpdateDate( ) );
152             daoUtil.setString( nIndex++, blog.getHtmlContent( ) );
153             daoUtil.setString( nIndex++, blog.getUser( ) );
154             daoUtil.setString( nIndex++, blog.getUserCreator( ) );
155             daoUtil.setInt( nIndex++, blog.getAttachedPortletId( ) );
156             daoUtil.setString( nIndex++, blog.getEditComment( ) );
157             daoUtil.setString( nIndex++, blog.getDescription( ) );
158             daoUtil.setBoolean( nIndex++, blog.getShareable( ) );
159             daoUtil.setString( nIndex++, blog.getUrl( ) );
160 
161             daoUtil.executeUpdate( );
162         }
163     }
164 
165     /**
166      * {@inheritDoc }
167      */
168     @Override
169     public void insertVersion( Blog blog, Plugin plugin )
170     {
171         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_VERSION, plugin ) )
172         {
173             int nVersion = newVersionPrimaryKey( plugin );
174             int nIndex = 1;
175 
176             daoUtil.setInt( nIndex++, nVersion );
177             daoUtil.setInt( nIndex++, blog.getId( ) );
178             daoUtil.setInt( nIndex++, blog.getVersion( ) );
179             daoUtil.setString( nIndex++, blog.getContentLabel( ) );
180             daoUtil.setTimestamp( nIndex++, blog.getCreationDate( ) );
181             daoUtil.setTimestamp( nIndex++, blog.getUpdateDate( ) );
182             daoUtil.setString( nIndex++, blog.getHtmlContent( ) );
183             daoUtil.setString( nIndex++, blog.getUser( ) );
184             daoUtil.setString( nIndex++, blog.getUserCreator( ) );
185             daoUtil.setInt( nIndex++, blog.getAttachedPortletId( ) );
186             daoUtil.setString( nIndex++, blog.getEditComment( ) );
187             daoUtil.setString( nIndex++, blog.getDescription( ) );
188             daoUtil.setBoolean( nIndex++, blog.getShareable( ) );
189             daoUtil.setString( nIndex++, blog.getUrl( ) );
190 
191             daoUtil.executeUpdate( );
192         }
193     }
194 
195     /**
196      * {@inheritDoc }
197      */
198     @Override
199     public Blog load( int nKey, Plugin plugin )
200     {
201         Blog blog = null;
202         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
203         {
204             daoUtil.setInt( 1, nKey );
205             daoUtil.executeQuery( );
206 
207             if ( daoUtil.next( ) )
208             {
209                 blog = new Blog( );
210                 int nIndex = 1;
211 
212                 blog.setId( daoUtil.getInt( nIndex++ ) );
213                 blog.setVersion( daoUtil.getInt( nIndex++ ) );
214                 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
215                 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
216                 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
217                 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
218                 blog.setUser( daoUtil.getString( nIndex++ ) );
219                 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
220                 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
221                 blog.setEditComment( daoUtil.getString( nIndex++ ) );
222                 blog.setDescription( daoUtil.getString( nIndex++ ) );
223                 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
224                 blog.setUrl( daoUtil.getString( nIndex++ ) );
225 
226             }
227 
228         }
229         return blog;
230     }
231 
232     /**
233      * {@inheritDoc }
234      */
235     @Override
236     public Blog loadByName( String strName, Plugin plugin )
237     {
238         Blog blog = null;
239         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_NAME, plugin ) )
240         {
241             daoUtil.setString( 1, strName );
242             daoUtil.executeQuery( );
243 
244             if ( daoUtil.next( ) )
245             {
246                 blog = new Blog( );
247                 int nIndex = 1;
248 
249                 blog.setId( daoUtil.getInt( nIndex++ ) );
250                 blog.setVersion( daoUtil.getInt( nIndex++ ) );
251                 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
252                 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
253                 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
254                 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
255                 blog.setUser( daoUtil.getString( nIndex++ ) );
256                 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
257                 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
258                 blog.setEditComment( daoUtil.getString( nIndex++ ) );
259                 blog.setDescription( daoUtil.getString( nIndex++ ) );
260                 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
261                 blog.setUrl( daoUtil.getString( nIndex++ ) );
262 
263             }
264         }
265         return blog;
266     }
267 
268     /**
269      * {@inheritDoc }
270      */
271     @Override
272     public Blog loadVersion( int nId, int nVersion, Plugin plugin )
273     {
274         Blog blog = null;
275         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_VERSION, plugin ) )
276         {
277             daoUtil.setInt( 1, nId );
278             daoUtil.setInt( 2, nVersion );
279             daoUtil.executeQuery( );
280 
281             if ( daoUtil.next( ) )
282             {
283                 blog = new Blog( );
284                 int nIndex = 1;
285 
286                 blog.setId( daoUtil.getInt( nIndex++ ) );
287                 blog.setVersion( daoUtil.getInt( nIndex++ ) );
288                 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
289                 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
290                 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
291                 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
292                 blog.setUser( daoUtil.getString( nIndex++ ) );
293                 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
294                 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
295                 blog.setEditComment( daoUtil.getString( nIndex++ ) );
296                 blog.setDescription( daoUtil.getString( nIndex++ ) );
297                 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
298                 blog.setUrl( daoUtil.getString( nIndex++ ) );
299             }
300 
301         }
302         return blog;
303     }
304 
305     /**
306      * {@inheritDoc }
307      */
308     @Override
309     public void delete( int nKey, Plugin plugin )
310     {
311         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
312         {
313             daoUtil.setInt( 1, nKey );
314             daoUtil.executeUpdate( );
315         }
316     }
317 
318     /**
319      * {@inheritDoc }
320      */
321     @Override
322     public void deleteVersions( int nKey, Plugin plugin )
323     {
324         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_VERSIONS, plugin ) )
325         {
326             daoUtil.setInt( 1, nKey );
327             daoUtil.executeUpdate( );
328         }
329     }
330 
331     /**
332      * {@inheritDoc }
333      */
334     @Override
335     public void store( Blog blog, Plugin plugin )
336     {
337         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
338         {
339             int nIndex = 1;
340 
341             daoUtil.setInt( nIndex++, blog.getId( ) );
342             daoUtil.setInt( nIndex++, blog.getVersion( ) );
343             daoUtil.setString( nIndex++, blog.getContentLabel( ) );
344             daoUtil.setTimestamp( nIndex++, blog.getCreationDate( ) );
345             daoUtil.setTimestamp( nIndex++, blog.getUpdateDate( ) );
346             daoUtil.setString( nIndex++, blog.getHtmlContent( ) );
347             daoUtil.setString( nIndex++, blog.getUser( ) );
348             daoUtil.setString( nIndex++, blog.getUserCreator( ) );
349             daoUtil.setInt( nIndex++, blog.getAttachedPortletId( ) );
350             daoUtil.setString( nIndex++, blog.getEditComment( ) );
351             daoUtil.setString( nIndex++, blog.getDescription( ) );
352             daoUtil.setBoolean( nIndex++, blog.getShareable( ) );
353             daoUtil.setString( nIndex++, blog.getUrl( ) );
354 
355             daoUtil.setInt( nIndex, blog.getId( ) );
356 
357             daoUtil.executeUpdate( );
358         }
359     }
360 
361     /**
362      * {@inheritDoc }
363      */
364     @Override
365     public List<Blog> selectBlogsList( Plugin plugin )
366     {
367         List<Blog> blogList = new ArrayList<>( );
368         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
369         {
370             daoUtil.executeQuery( );
371 
372             while ( daoUtil.next( ) )
373             {
374                 Blogugins/blog/business/Blog.html#Blog">Blog blog = new Blog( );
375                 int nIndex = 1;
376 
377                 blog.setId( daoUtil.getInt( nIndex++ ) );
378                 blog.setVersion( daoUtil.getInt( nIndex++ ) );
379                 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
380                 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
381                 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
382                 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
383                 blog.setUser( daoUtil.getString( nIndex++ ) );
384                 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
385                 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
386                 blog.setEditComment( daoUtil.getString( nIndex++ ) );
387                 blog.setDescription( daoUtil.getString( nIndex++ ) );
388                 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
389                 blog.setUrl( daoUtil.getString( nIndex++ ) );
390 
391                 blogList.add( blog );
392             }
393 
394         }
395         return blogList;
396     }
397 
398     /**
399      * {@inheritDoc }
400      */
401     @Override
402     public List<Blog> selectlastModifiedBlogsList( Plugin plugin, int nLimit )
403     {
404         List<Blog> blogList = new ArrayList<>( );
405         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_DOCUMENTS, plugin ) )
406         {
407             daoUtil.setInt( 1, nLimit );
408             daoUtil.executeQuery( );
409 
410             while ( daoUtil.next( ) )
411             {
412                 Blogugins/blog/business/Blog.html#Blog">Blog blog = new Blog( );
413                 int nIndex = 1;
414 
415                 blog.setId( daoUtil.getInt( nIndex++ ) );
416                 blog.setVersion( daoUtil.getInt( nIndex++ ) );
417                 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
418                 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
419                 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
420                 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
421                 blog.setUser( daoUtil.getString( nIndex++ ) );
422                 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
423                 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
424                 blog.setEditComment( daoUtil.getString( nIndex++ ) );
425                 blog.setDescription( daoUtil.getString( nIndex++ ) );
426                 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
427                 blog.setUrl( daoUtil.getString( nIndex++ ) );
428 
429                 blogList.add( blog );
430             }
431 
432         }
433         return blogList;
434     }
435 
436     /**
437      * {@inheritDoc }
438      */
439     @Override
440     public List<Blog> selectBlogsVersionsList( int nId, Plugin plugin )
441     {
442         List<Blog> blogVersionsList = new ArrayList<>( );
443         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_VERSION, plugin ) )
444         {
445             daoUtil.setInt( 1, nId );
446             daoUtil.executeQuery( );
447 
448             while ( daoUtil.next( ) )
449             {
450                 Blogugins/blog/business/Blog.html#Blog">Blog blog = new Blog( );
451                 int nIndex = 1;
452 
453                 blog.setId( daoUtil.getInt( nIndex++ ) );
454                 blog.setVersion( daoUtil.getInt( nIndex++ ) );
455                 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
456                 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
457                 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
458                 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
459                 blog.setUser( daoUtil.getString( nIndex++ ) );
460                 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
461                 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
462                 blog.setEditComment( daoUtil.getString( nIndex++ ) );
463                 blog.setDescription( daoUtil.getString( nIndex++ ) );
464                 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
465                 blog.setUrl( daoUtil.getString( nIndex++ ) );
466 
467                 blogVersionsList.add( blog );
468             }
469         }
470         return blogVersionsList;
471     }
472 
473     /**
474      * {@inheritDoc }
475      */
476     @Override
477     public List<String> selectAllUsersEditedBlog( int nId, Plugin plugin )
478     {
479         List<String> blogUsersVersionsList = new ArrayList<>( );
480         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_USERS_EDITED_BLOG_VERSION, plugin ) )
481         {
482             daoUtil.setInt( 1, nId );
483             daoUtil.executeQuery( );
484 
485             while ( daoUtil.next( ) )
486             {
487                 blogUsersVersionsList.add( daoUtil.getString( 1 ) );
488             }
489         }
490         return blogUsersVersionsList;
491     }
492 
493     /**
494      * {@inheritDoc }
495      */
496     @Override
497     public List<Integer> selectIdBlogsList( Plugin plugin )
498     {
499         List<Integer> blogList = new ArrayList<>( );
500         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
501         {
502             daoUtil.executeQuery( );
503 
504             while ( daoUtil.next( ) )
505             {
506                 blogList.add( daoUtil.getInt( 1 ) );
507             }
508         }
509         return blogList;
510     }
511 
512     /**
513      * {@inheritDoc }
514      */
515     @Override
516     public ReferenceList selectBlogsReferenceList( Plugin plugin )
517     {
518         ReferenceList blogList = new ReferenceList( );
519         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
520         {
521             daoUtil.executeQuery( );
522 
523             while ( daoUtil.next( ) )
524             {
525                 blogList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
526             }
527         }
528         return blogList;
529     }
530 
531     /**
532      * Load the list of blogs
533      *
534      * @return The Collection of the blogs
535      * @param filter
536      *            The blogFilter Object
537      */
538     @Override
539     public List<Blog> selectByFilter( BlogFilter filter )
540     {
541         List<Blog> listDocuments = new ArrayList<>( );
542         try ( DAOUtil daoUtil = getDaoFromFilter( SQL_QUERY_SELECT_BY_FILTER, filter ) )
543         {
544             daoUtil.executeQuery( );
545 
546             while ( daoUtil.next( ) )
547             {
548                 Blogugins/blog/business/Blog.html#Blog">Blog blog = new Blog( );
549                 int nIndex = 1;
550 
551                 blog.setId( daoUtil.getInt( nIndex++ ) );
552                 blog.setVersion( daoUtil.getInt( nIndex++ ) );
553                 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
554                 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
555                 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
556                 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
557                 blog.setUser( daoUtil.getString( nIndex++ ) );
558                 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
559                 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
560                 blog.setEditComment( daoUtil.getString( nIndex++ ) );
561                 blog.setDescription( daoUtil.getString( nIndex++ ) );
562                 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
563                 blog.setUrl( daoUtil.getString( nIndex ) );
564 
565                 if ( filter.getLoadBinaries( ) )
566                 {
567                     blog.setDocContent( DocContentHome.getDocsContentByHtmlDoc( blog.getId( ) ) );
568                 }
569 
570                 blog.setTag( TagHome.getTagListByDoc( blog.getId( ) ) );
571 
572                 listDocuments.add( blog );
573             }
574 
575             daoUtil.free( );
576         }
577         return listDocuments;
578     }
579 
580     /**
581      * Return a dao initialized with the specified filter
582      * 
583      * @param strQuerySelect
584      *            the query
585      * @param filter
586      *            the DocumentFilter object
587      * @return the DaoUtil
588      */
589     private DAOUtil getDaoFromFilter( String strQuerySelect, BlogFilter filter )
590     {
591         String strSQL = strQuerySelect;
592         StringBuilder sbWhere = new StringBuilder( StringUtils.EMPTY );
593 
594         if ( filter.containsTagsCriteria( ) )
595         {
596             StringBuilder sbCategories = new StringBuilder( SQL_FILTER_TAGS_BEGIN );
597 
598             int i = 0;
599 
600             for ( int nTagId : filter.getTagsId( ) )
601             {
602                 if ( nTagId > 0 )
603                 {
604                     sbCategories.append( SQL_FILTER_TAGS );
605                 }
606                 else
607                 {
608                     sbCategories.append( SQL_FILTER_TAGS_NULL );
609                 }
610 
611                 if ( ( i + 1 ) < filter.getTagsId( ).length )
612                 {
613                     sbCategories.append( SQL_FILTER_TAGS_OR );
614                 }
615 
616                 i++;
617             }
618 
619             sbCategories.append( SQL_FILTER_TAGS_END );
620             sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( sbCategories.toString( ) );
621         }
622 
623         if ( filter.containsIdsCriteria( ) )
624         {
625             StringBuilder sbIds = new StringBuilder( SQL_FILTER_ID_BEGIN );
626 
627             for ( int i = 0; i < filter.getIds( ).length; i++ )
628             {
629                 sbIds.append( SQL_FILTER_ID );
630 
631                 if ( ( i + 1 ) < filter.getIds( ).length )
632                 {
633                     sbIds.append( SQL_FILTER_ID_OR );
634                 }
635             }
636 
637             sbIds.append( SQL_FILTER_ID_END );
638             sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( sbIds.toString( ) );
639         }
640 
641         if ( BooleanUtils.isFalse( filter.isPublished( ) ) )
642         {
643             sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY )
644                     .append( "a.id_blog NOT IN (SELECT DISTINCT id_blog FROM blogs_tag_document) " );
645         }
646         if ( filter.getPortletId( ) != 0 )
647         {
648             sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( "p.id_portlet=" )
649                     .append( String.valueOf( filter.getPortletId( ) ) );
650         }
651 
652         if ( StringUtils.isNotBlank( filter.getDateMin( ) ) && StringUtils.isNotBlank( filter.getDateMax( ) ) )
653         {
654             sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( "a.update_date < " ).append( '\'' )
655                     .append( filter.getDateMax( ) ).append( '\'' ).append( SQL_FILTER_AND ).append( "a.update_date > " ).append( '\'' )
656                     .append( filter.getDateMin( ) ).append( '\'' );
657         }
658         else
659             if ( StringUtils.isNotBlank( filter.getDateMin( ) ) )
660             {
661                 sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( "a.update_date > " ).append( '\'' )
662                         .append( filter.getDateMin( ) ).append( '\'' );
663             }
664             else
665                 if ( StringUtils.isNotBlank( filter.getDateMax( ) ) )
666                 {
667                     sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( "a.update_date <= " ).append( '\'' )
668                             .append( filter.getDateMax( ) ).append( '\'' );
669                 }
670 
671         String strWhere = sbWhere.toString( );
672 
673         if ( sbWhere.length( ) != 0 )
674         {
675             strSQL += ( SQL_FILTER_WHERE_CLAUSE + strWhere );
676         }
677 
678         strSQL += SQL_ORDER_BY_LAST_MODIFICATION;
679 
680         AppLogService.debug( "Sql query filter : " + strSQL );
681 
682         DAOUtil daoUtil = new DAOUtil( strSQL );
683         int nIndex = 1;
684 
685         if ( filter.containsTagsCriteria( ) )
686         {
687             for ( int nCategoryId : filter.getTagsId( ) )
688             {
689                 if ( nCategoryId > 0 )
690                 {
691                     daoUtil.setInt( nIndex, nCategoryId );
692                     AppLogService.debug( "Param" + nIndex + " (getTagsId) = " + nCategoryId );
693                     nIndex++;
694                 }
695             }
696         }
697 
698         if ( filter.containsIdsCriteria( ) )
699         {
700             for ( int nId : filter.getIds( ) )
701             {
702                 daoUtil.setInt( nIndex, nId );
703                 AppLogService.debug( "Param" + nIndex + " (getIds) = " + nId );
704                 nIndex++;
705             }
706         }
707 
708         return daoUtil;
709     }
710 
711     /**
712      * {@inheritDoc }
713      */
714     @Override
715     public List<Blog> loadBlogByIdTag( int nIdTag, Plugin plugin )
716     {
717         List<Blog> listBlog = new ArrayList<>( );
718 
719         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BLOG_BY_ID_TAG, plugin ) )
720         {
721             daoUtil.setInt( 1, nIdTag );
722             daoUtil.executeQuery( );
723             Blog blog;
724 
725             while ( daoUtil.next( ) )
726             {
727                 blog = new Blog( );
728                 int nIndex = 1;
729 
730                 blog.setId( daoUtil.getInt( nIndex++ ) );
731                 blog.setVersion( daoUtil.getInt( nIndex++ ) );
732                 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
733                 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
734                 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
735                 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
736                 blog.setUser( daoUtil.getString( nIndex++ ) );
737                 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
738                 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
739                 blog.setEditComment( daoUtil.getString( nIndex++ ) );
740                 blog.setDescription( daoUtil.getString( nIndex++ ) );
741                 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
742                 blog.setUrl( daoUtil.getString( nIndex++ ) );
743                 listBlog.add( blog );
744 
745             }
746 
747         }
748         return listBlog;
749     }
750 
751     /**
752      * {@inheritDoc }
753      */
754     @Override
755     public List<Blog> selectWithoutBinaries( Plugin plugin )
756     {
757         List<Blog> listDocuments = new ArrayList<>( );
758 
759         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_BLOG, plugin ) )
760         {
761             daoUtil.executeQuery( );
762 
763             while ( daoUtil.next( ) )
764             {
765                 Blogugins/blog/business/Blog.html#Blog">Blog blog = new Blog( );
766                 int nIndex = 1;
767                 int idBlog = daoUtil.getInt( nIndex++ );
768                 blog.setId( idBlog );
769                 blog.setVersion( daoUtil.getInt( nIndex++ ) );
770                 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
771                 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
772                 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
773                 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
774                 blog.setUser( daoUtil.getString( nIndex++ ) );
775                 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
776                 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
777                 blog.setEditComment( daoUtil.getString( nIndex++ ) );
778                 blog.setDescription( daoUtil.getString( nIndex++ ) );
779                 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
780                 blog.setUrl( daoUtil.getString( nIndex++ ) );
781 
782                 listDocuments.add( blog );
783             }
784         }
785 
786         return listDocuments;
787     }
788 
789 }