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