1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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
49
50 public final class BlogDAO implements IBlogDAO
51 {
52
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
98
99
100
101
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
119
120
121
122
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
550
551
552
553
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
599
600
601
602
603
604
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
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
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
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 }