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 import java.sql.Statement;
39 import java.sql.Timestamp;
40
41 import org.apache.commons.lang3.BooleanUtils;
42 import org.apache.commons.lang3.StringUtils;
43
44 import fr.paris.lutece.portal.service.plugin.Plugin;
45 import fr.paris.lutece.portal.service.util.AppLogService;
46 import fr.paris.lutece.util.ReferenceList;
47 import fr.paris.lutece.util.sql.DAOUtil;
48
49
50
51
52 public final class BlogDAO implements IBlogDAO
53 {
54
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, is_archived 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, is_archived 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, is_archived 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 ( version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description, shareable, url, is_archived ) 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 version = ?, content_label = ?, creation_date = ?, update_date = ?, html_content = ?, user_editor = ?, user_creator = ?, attached_portlet_id = ?, edit_comment = ?, description = ?, shareable = ?, url= ?, is_archived=? 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, is_archived FROM blog_blog order by creation_date DESC";
65 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_blog FROM blog_blog WHERE !is_archived 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 private static final String SQL_QUERY_SELECT_BY_ARCHIVE_STATUS = "SELECT id_blog, version, content_label, creation_date, update_date, html_content, user_editor, user_creator, attached_portlet_id, edit_comment, description, shareable, url, is_archived FROM blog_blog WHERE is_archived = ?";
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_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, a.is_archived, 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, b.is_archived, 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, a.is_archived FROM blog_blog a";
81 private static final String SQL_QUERY_SELECT_VERSION_NUMBER_BY_BLOG_ID_AND_CREATION_DATE = "SELECT version FROM blog_versions WHERE id_blog = ? ORDER BY ABS( update_date - ? );";
82
83 private static final String SQL_FILTER_WHERE_CLAUSE = " WHERE ";
84 private static final String SQL_FILTER_AND = " AND ";
85 private static final String SQL_FILTER_TAGS_BEGIN = " (";
86 private static final String SQL_FILTER_TAGS = " f.id_tag = ? ";
87 private static final String SQL_FILTER_TAGS_NULL = " f.id_tag IS NULL ";
88 private static final String SQL_FILTER_TAGS_OR = " OR ";
89 private static final String SQL_FILTER_TAGS_END = ") ";
90 private static final String SQL_FILTER_ID_BEGIN = " (";
91 private static final String SQL_FILTER_ID = " a.id_blog = ? ";
92 private static final String SQL_FILTER_ID_OR = " OR ";
93 private static final String SQL_FILTER_ID_END = ") ";
94 private static final String SQL_ORDER_BY_LAST_MODIFICATION = " ORDER BY a.update_date DESC ";
95 private static final String SQL_ORDER_BY_ORDER_DOCUMENT = " ORDER by p.document_order ";
96 private static final String SQL_UPDATE_BLOG_ARCHIVE = "UPDATE blog_blog SET is_archived = ? WHERE id_blog = ? ";
97
98
99
100
101 @Override
102 public void insert( Blog blog, Plugin plugin )
103 {
104 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
105 {
106 int nIndex = 1;
107
108 daoUtil.setInt( nIndex++, blog.getVersion( ) );
109 daoUtil.setString( nIndex++, blog.getContentLabel( ) );
110 daoUtil.setTimestamp( nIndex++, blog.getCreationDate( ) );
111 daoUtil.setTimestamp( nIndex++, blog.getUpdateDate( ) );
112 daoUtil.setString( nIndex++, blog.getHtmlContent( ) );
113 daoUtil.setString( nIndex++, blog.getUser( ) );
114 daoUtil.setString( nIndex++, blog.getUserCreator( ) );
115 daoUtil.setInt( nIndex++, blog.getAttachedPortletId( ) );
116 daoUtil.setString( nIndex++, blog.getEditComment( ) );
117 daoUtil.setString( nIndex++, blog.getDescription( ) );
118 daoUtil.setBoolean( nIndex++, blog.getShareable( ) );
119 daoUtil.setString( nIndex++, blog.getUrl( ) );
120 daoUtil.setBoolean( nIndex, false );
121
122 daoUtil.executeUpdate( );
123 if ( daoUtil.nextGeneratedKey( ) )
124 {
125 blog.setId( daoUtil.getGeneratedKeyInt( 1 ) );
126 }
127 }
128 }
129
130
131
132
133 @Override
134 public void insertVersion( Blog blog, Plugin plugin )
135 {
136 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_VERSION, plugin ) )
137 {
138 int nIndex = 1;
139
140 daoUtil.setInt( nIndex++, blog.getId( ) );
141 daoUtil.setInt( nIndex++, blog.getVersion( ) );
142 daoUtil.setString( nIndex++, blog.getContentLabel( ) );
143 daoUtil.setTimestamp( nIndex++, blog.getCreationDate( ) );
144 daoUtil.setTimestamp( nIndex++, blog.getUpdateDate( ) );
145 daoUtil.setString( nIndex++, blog.getHtmlContent( ) );
146 daoUtil.setString( nIndex++, blog.getUser( ) );
147 daoUtil.setString( nIndex++, blog.getUserCreator( ) );
148 daoUtil.setInt( nIndex++, blog.getAttachedPortletId( ) );
149 daoUtil.setString( nIndex++, blog.getEditComment( ) );
150 daoUtil.setString( nIndex++, blog.getDescription( ) );
151 daoUtil.setBoolean( nIndex++, blog.getShareable( ) );
152 daoUtil.setString( nIndex++, blog.getUrl( ) );
153
154 daoUtil.executeUpdate( );
155 }
156 }
157
158
159
160
161 @Override
162 public Blog load( int nKey, Plugin plugin )
163 {
164 Blog blog = null;
165 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
166 {
167 daoUtil.setInt( 1, nKey );
168 daoUtil.executeQuery( );
169
170 if ( daoUtil.next( ) )
171 {
172 blog = new Blog( );
173 int nIndex = 1;
174
175 blog.setId( daoUtil.getInt( nIndex++ ) );
176 blog.setVersion( daoUtil.getInt( nIndex++ ) );
177 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
178 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
179 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
180 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
181 blog.setUser( daoUtil.getString( nIndex++ ) );
182 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
183 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
184 blog.setEditComment( daoUtil.getString( nIndex++ ) );
185 blog.setDescription( daoUtil.getString( nIndex++ ) );
186 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
187 blog.setUrl( daoUtil.getString( nIndex++ ) );
188 blog.setArchived( daoUtil.getBoolean( nIndex ) );
189
190 }
191
192 }
193 return blog;
194 }
195
196
197
198
199 @Override
200 public Blog loadByName( String strName, Plugin plugin )
201 {
202 Blog blog = null;
203 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_NAME, plugin ) )
204 {
205 daoUtil.setString( 1, strName );
206 daoUtil.executeQuery( );
207
208 if ( daoUtil.next( ) )
209 {
210 blog = new Blog( );
211 int nIndex = 1;
212
213 blog.setId( daoUtil.getInt( nIndex++ ) );
214 blog.setVersion( daoUtil.getInt( nIndex++ ) );
215 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
216 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
217 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
218 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
219 blog.setUser( daoUtil.getString( nIndex++ ) );
220 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
221 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
222 blog.setEditComment( daoUtil.getString( nIndex++ ) );
223 blog.setDescription( daoUtil.getString( nIndex++ ) );
224 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
225 blog.setUrl( daoUtil.getString( nIndex++ ) );
226 blog.setArchived( daoUtil.getBoolean( nIndex ) );
227
228 }
229 }
230 return blog;
231 }
232
233
234
235
236 @Override
237 public Blog loadVersion( int nId, int nVersion, Plugin plugin )
238 {
239 Blog blog = null;
240 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_VERSION, plugin ) )
241 {
242 daoUtil.setInt( 1, nId );
243 daoUtil.setInt( 2, nVersion );
244 daoUtil.executeQuery( );
245
246 if ( daoUtil.next( ) )
247 {
248 blog = new Blog( );
249 int nIndex = 1;
250
251 blog.setId( daoUtil.getInt( nIndex++ ) );
252 blog.setVersion( daoUtil.getInt( nIndex++ ) );
253 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
254 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
255 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
256 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
257 blog.setUser( daoUtil.getString( nIndex++ ) );
258 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
259 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
260 blog.setEditComment( daoUtil.getString( nIndex++ ) );
261 blog.setDescription( daoUtil.getString( nIndex++ ) );
262 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
263 blog.setUrl( daoUtil.getString( nIndex++ ) );
264 }
265
266 }
267 return blog;
268 }
269
270
271
272
273 @Override
274 public void delete( int nKey, Plugin plugin )
275 {
276 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
277 {
278 daoUtil.setInt( 1, nKey );
279 daoUtil.executeUpdate( );
280 }
281 }
282
283
284
285
286 @Override
287 public void deleteVersions( int nKey, Plugin plugin )
288 {
289 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_VERSIONS, plugin ) )
290 {
291 daoUtil.setInt( 1, nKey );
292 daoUtil.executeUpdate( );
293 }
294 }
295
296
297
298
299 @Override
300 public void deleteSpecificVersion( int nIdBlog, int nVersion, Plugin plugin )
301 {
302 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_SPECIFIC_VERSION, plugin ) )
303 {
304 daoUtil.setInt( 1, nIdBlog );
305 daoUtil.setInt( 2, nVersion );
306 daoUtil.executeUpdate( );
307 }
308 }
309
310
311
312
313 @Override
314 public void store( Blog blog, Plugin plugin )
315 {
316 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
317 {
318 int nIndex = 1;
319
320 daoUtil.setInt( nIndex++, blog.getVersion( ) );
321 daoUtil.setString( nIndex++, blog.getContentLabel( ) );
322 daoUtil.setTimestamp( nIndex++, blog.getCreationDate( ) );
323 daoUtil.setTimestamp( nIndex++, blog.getUpdateDate( ) );
324 daoUtil.setString( nIndex++, blog.getHtmlContent( ) );
325 daoUtil.setString( nIndex++, blog.getUser( ) );
326 daoUtil.setString( nIndex++, blog.getUserCreator( ) );
327 daoUtil.setInt( nIndex++, blog.getAttachedPortletId( ) );
328 daoUtil.setString( nIndex++, blog.getEditComment( ) );
329 daoUtil.setString( nIndex++, blog.getDescription( ) );
330 daoUtil.setBoolean( nIndex++, blog.getShareable( ) );
331 daoUtil.setString( nIndex++, blog.getUrl( ) );
332 daoUtil.setInt( nIndex++, blog.isArchived( )?1:0 );
333
334 daoUtil.setInt( nIndex, blog.getId( ) );
335
336 daoUtil.executeUpdate( );
337 }
338 }
339
340
341
342
343 @Override
344 public List<Blog> selectBlogsList( Plugin plugin )
345 {
346 List<Blog> blogList = new ArrayList<>( );
347 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
348 {
349 daoUtil.executeQuery( );
350
351 while ( daoUtil.next( ) )
352 {
353 Blogugins/blog/business/Blog.html#Blog">Blog blog = new Blog( );
354 int nIndex = 1;
355
356 blog.setId( daoUtil.getInt( nIndex++ ) );
357 blog.setVersion( daoUtil.getInt( nIndex++ ) );
358 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
359 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
360 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
361 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
362 blog.setUser( daoUtil.getString( nIndex++ ) );
363 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
364 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
365 blog.setEditComment( daoUtil.getString( nIndex++ ) );
366 blog.setDescription( daoUtil.getString( nIndex++ ) );
367 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
368 blog.setUrl( daoUtil.getString( nIndex++ ) );
369 blog.setArchived( daoUtil.getBoolean( nIndex ) );
370
371 blogList.add( blog );
372 }
373
374 }
375 return blogList;
376 }
377
378
379
380 @Override
381 public List<Blog> selectBlogsListByArchiveStatus(boolean isArchived, Plugin plugin )
382 {
383 List<Blog> blogList = new ArrayList<>( );
384 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ARCHIVE_STATUS, plugin ) )
385 {
386 daoUtil.setBoolean( 1, isArchived );
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 blog.setArchived( daoUtil.getBoolean( nIndex ) );
408
409 blogList.add( blog );
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 blog.setArchived( daoUtil.getBoolean( nIndex ) );
446
447 blogList.add( blog );
448 }
449
450 }
451 return blogList;
452 }
453
454
455
456
457 @Override
458 public List<Blog> selectBlogsVersionsList( int nId, Plugin plugin )
459 {
460 List<Blog> blogVersionsList = new ArrayList<>( );
461 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_VERSION, plugin ) )
462 {
463 daoUtil.setInt( 1, nId );
464 daoUtil.executeQuery( );
465
466 while ( daoUtil.next( ) )
467 {
468 Blogugins/blog/business/Blog.html#Blog">Blog blog = new Blog( );
469 int nIndex = 1;
470
471 blog.setId( daoUtil.getInt( nIndex++ ) );
472 blog.setVersion( daoUtil.getInt( nIndex++ ) );
473 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
474 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
475 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
476 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
477 blog.setUser( daoUtil.getString( nIndex++ ) );
478 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
479 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
480 blog.setEditComment( daoUtil.getString( nIndex++ ) );
481 blog.setDescription( daoUtil.getString( nIndex++ ) );
482 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
483 blog.setUrl( daoUtil.getString( nIndex++ ) );
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 blog.setArchived( daoUtil.getBoolean( nIndex ) );
582
583 if ( filter.getLoadBinaries( ) )
584 {
585 blog.setDocContent( DocContentHome.getDocsContentByHtmlDoc( blog.getId( ) ) );
586 }
587
588 blog.setTag( TagHome.getTagListByDoc( blog.getId( ) ) );
589
590 listDocuments.add( blog );
591 }
592 }
593 return listDocuments;
594 }
595
596
597
598
599
600
601
602
603
604
605 private DAOUtil getDaoFromFilter( String strQuerySelect, BlogFilter filter )
606 {
607 String strSQL = strQuerySelect;
608 StringBuilder sbWhere = new StringBuilder( StringUtils.EMPTY );
609
610 if ( filter.containsTagsCriteria( ) )
611 {
612 StringBuilder sbCategories = new StringBuilder( SQL_FILTER_TAGS_BEGIN );
613
614 int i = 0;
615
616 for ( int nTagId : filter.getTagsId( ) )
617 {
618 if ( nTagId > 0 )
619 {
620 sbCategories.append( SQL_FILTER_TAGS );
621 }
622 else
623 {
624 sbCategories.append( SQL_FILTER_TAGS_NULL );
625 }
626
627 if ( ( i + 1 ) < filter.getTagsId( ).length )
628 {
629 sbCategories.append( SQL_FILTER_TAGS_OR );
630 }
631
632 i++;
633 }
634
635 sbCategories.append( SQL_FILTER_TAGS_END );
636 sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( sbCategories.toString( ) );
637 }
638
639 if ( filter.containsIdsCriteria( ) )
640 {
641 StringBuilder sbIds = new StringBuilder( SQL_FILTER_ID_BEGIN );
642
643 for ( int i = 0; i < filter.getIds( ).length; i++ )
644 {
645 sbIds.append( SQL_FILTER_ID );
646
647 if ( ( i + 1 ) < filter.getIds( ).length )
648 {
649 sbIds.append( SQL_FILTER_ID_OR );
650 }
651 }
652
653 sbIds.append( SQL_FILTER_ID_END );
654 sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( sbIds.toString( ) );
655 }
656
657 if ( BooleanUtils.isFalse( filter.isPublished( ) ) )
658 {
659 sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY )
660 .append( "a.id_blog NOT IN (SELECT DISTINCT id_blog FROM blogs_tag_document) " );
661 }
662 if ( filter.isArchived( ) )
663 {
664 sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( "a.is_archived = 1 " );
665 }
666 else
667 {
668 sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( "a.is_archived != 1 " );
669 }
670 if ( filter.getPortletId( ) != 0 )
671 {
672 sbWhere.append( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ).append( "p.id_portlet=" )
673 .append( String.valueOf( filter.getPortletId( ) ) );
674 }
675
676 if ( StringUtils.isNotBlank( filter.getDateMin( ) ) && StringUtils.isNotBlank( filter.getDateMax( ) ) )
677 {
678 sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( "a.update_date < " ).append( '\'' )
679 .append( filter.getDateMax( ) ).append( '\'' ).append( SQL_FILTER_AND ).append( "a.update_date > " ).append( '\'' )
680 .append( filter.getDateMin( ) ).append( '\'' );
681 }
682 else
683 if ( StringUtils.isNotBlank( filter.getDateMin( ) ) )
684 {
685 sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( "a.update_date > " ).append( '\'' )
686 .append( filter.getDateMin( ) ).append( '\'' );
687 }
688 else
689 if ( StringUtils.isNotBlank( filter.getDateMax( ) ) )
690 {
691 sbWhere.append( ( ( sbWhere.length( ) != 0 ) ? SQL_FILTER_AND : StringUtils.EMPTY ) ).append( "a.update_date <= " ).append( '\'' )
692 .append( filter.getDateMax( ) ).append( '\'' );
693 }
694
695 String strWhere = sbWhere.toString( );
696
697 if ( sbWhere.length( ) != 0 )
698 {
699 strSQL += ( SQL_FILTER_WHERE_CLAUSE + strWhere );
700 }
701
702 if ( filter.getOrderInPortlet( ) )
703 {
704
705 strSQL += SQL_ORDER_BY_ORDER_DOCUMENT;
706 }
707 else
708 {
709 strSQL += SQL_ORDER_BY_LAST_MODIFICATION;
710 }
711
712 AppLogService.debug( "Sql query filter : " + strSQL );
713
714 DAOUtil daoUtil = new DAOUtil( strSQL );
715 int nIndex = 1;
716
717 if ( filter.containsTagsCriteria( ) )
718 {
719 for ( int nCategoryId : filter.getTagsId( ) )
720 {
721 if ( nCategoryId > 0 )
722 {
723 daoUtil.setInt( nIndex, nCategoryId );
724 AppLogService.debug( "Param" + nIndex + " (getTagsId) = " + nCategoryId );
725 nIndex++;
726 }
727 }
728 }
729
730 if ( filter.containsIdsCriteria( ) )
731 {
732 for ( int nId : filter.getIds( ) )
733 {
734 daoUtil.setInt( nIndex, nId );
735 AppLogService.debug( "Param" + nIndex + " (getIds) = " + nId );
736 nIndex++;
737 }
738 }
739
740 return daoUtil;
741 }
742
743
744
745
746 @Override
747 public List<Blog> loadBlogByIdTag( int nIdTag, Plugin plugin )
748 {
749 List<Blog> listBlog = new ArrayList<>( );
750
751 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BLOG_BY_ID_TAG, plugin ) )
752 {
753 daoUtil.setInt( 1, nIdTag );
754 daoUtil.executeQuery( );
755 Blog blog;
756
757 while ( daoUtil.next( ) )
758 {
759 blog = new Blog( );
760 int nIndex = 1;
761
762 blog.setId( daoUtil.getInt( nIndex++ ) );
763 blog.setVersion( daoUtil.getInt( nIndex++ ) );
764 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
765 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
766 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
767 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
768 blog.setUser( daoUtil.getString( nIndex++ ) );
769 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
770 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
771 blog.setEditComment( daoUtil.getString( nIndex++ ) );
772 blog.setDescription( daoUtil.getString( nIndex++ ) );
773 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
774 blog.setUrl( daoUtil.getString( nIndex++ ) );
775 blog.setArchived( daoUtil.getBoolean( nIndex ) );
776 listBlog.add( blog );
777
778 }
779
780 }
781 return listBlog;
782 }
783
784
785
786
787 @Override
788 public List<Blog> selectWithoutBinaries( Plugin plugin )
789 {
790 List<Blog> listDocuments = new ArrayList<>( );
791
792 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_BLOG, plugin ) )
793 {
794 daoUtil.executeQuery( );
795
796 while ( daoUtil.next( ) )
797 {
798 Blogugins/blog/business/Blog.html#Blog">Blog blog = new Blog( );
799 int nIndex = 1;
800 int idBlog = daoUtil.getInt( nIndex++ );
801 blog.setId( idBlog );
802 blog.setVersion( daoUtil.getInt( nIndex++ ) );
803 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
804 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
805 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
806 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
807 blog.setUser( daoUtil.getString( nIndex++ ) );
808 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
809 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
810 blog.setEditComment( daoUtil.getString( nIndex++ ) );
811 blog.setDescription( daoUtil.getString( nIndex++ ) );
812 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
813 blog.setUrl( daoUtil.getString( nIndex++ ) );
814 blog.setArchived( daoUtil.getBoolean( nIndex ) );
815
816 listDocuments.add( blog );
817 }
818 }
819
820 return listDocuments;
821 }
822
823
824
825
826 @Override
827 public List<Blog> selectLastBlogVersionsList( int nId, int nLimit, Plugin plugin )
828 {
829 List<Blog> blogList = new ArrayList<>( );
830 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_VERSIONS_BY_BLOG_ID, plugin ) )
831 {
832 daoUtil.setInt( 1, nId );
833 daoUtil.setInt( 2, nLimit );
834 daoUtil.executeQuery( );
835
836 while ( daoUtil.next( ) )
837 {
838 Blogugins/blog/business/Blog.html#Blog">Blog blog = new Blog( );
839 int nIndex = 1;
840
841 blog.setId( daoUtil.getInt( nIndex++ ) );
842 blog.setVersion( daoUtil.getInt( nIndex++ ) );
843 blog.setContentLabel( daoUtil.getString( nIndex++ ) );
844 blog.setCreationDate( daoUtil.getTimestamp( nIndex++ ) );
845 blog.setUpdateDate( daoUtil.getTimestamp( nIndex++ ) );
846 blog.setHtmlContent( daoUtil.getString( nIndex++ ) );
847 blog.setUser( daoUtil.getString( nIndex++ ) );
848 blog.setUserCreator( daoUtil.getString( nIndex++ ) );
849 blog.setAttachedPortletId( daoUtil.getInt( nIndex++ ) );
850 blog.setEditComment( daoUtil.getString( nIndex++ ) );
851 blog.setDescription( daoUtil.getString( nIndex++ ) );
852 blog.setShareable( daoUtil.getBoolean( nIndex++ ) );
853 blog.setUrl( daoUtil.getString( nIndex++ ) );
854
855 blog.setTag( TagHome.getTagListByDoc( blog.getId( ) ) );
856
857 blogList.add( blog );
858 }
859 }
860 return blogList;
861 }
862
863
864
865
866
867
868
869
870
871
872 @Override
873 public int getActualVersionNumber( Timestamp strUpdateDate, int nId, Plugin plugin )
874 {
875 int nVersion = 0;
876 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_VERSION_NUMBER_BY_BLOG_ID_AND_CREATION_DATE, plugin ) )
877 {
878 daoUtil.setInt( 1, nId );
879 daoUtil.setTimestamp( 2, strUpdateDate );
880 daoUtil.executeQuery( );
881
882 if ( daoUtil.next( ) )
883 {
884 nVersion = daoUtil.getInt( 1 );
885 }
886 return nVersion;
887 }
888 }
889
890
891
892
893
894
895 @Override
896 public void updateBlogArchiveId( int nIdBlog, boolean bArchive, Plugin plugin )
897 {
898 try ( DAOUtil daoUtil = new DAOUtil( SQL_UPDATE_BLOG_ARCHIVE, plugin ) )
899 {
900 daoUtil.setBoolean( 1, bArchive );
901 daoUtil.setInt( 2, nIdBlog );
902 daoUtil.executeUpdate( );
903 }
904 }
905 }