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