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.whatsnew.business;
35
36 import fr.paris.lutece.portal.service.i18n.I18nService;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import org.apache.commons.lang.StringUtils;
41
42 import java.sql.Timestamp;
43
44 import java.util.ArrayList;
45 import java.util.Collection;
46 import java.util.Date;
47 import java.util.List;
48 import java.util.Locale;
49
50
51
52
53
54
55
56 public class WhatsNewDAO implements IWhatsNewDAO
57 {
58 private static final String SQL_QUERY_SELECT_DOCUMENTS_BY_CRITERIAS = " SELECT a.title, a.document_summary as description, a.date_modification, a.id_document, b.id_portlet, c.document_type_name " +
59 " FROM document a INNER JOIN document_published b ON a.id_document = b.id_document " +
60 " INNER JOIN document_type c ON a.code_document_type = c.code_document_type WHERE " +
61 " ( a.date_modification between ? AND ? ) AND " +
62 " ( a.date_validity_begin is null or ? > a.date_validity_begin ) AND " +
63 " ( a.date_validity_end is null or ? < a.date_validity_end ) ";
64 private static final String SQL_QUERY_SELECT_PORTLETS_BY_CRITERIAS = " SELECT p.name, p.date_update, p.id_page, p.id_portlet, pt.name, pa.name " +
65 " FROM core_portlet p INNER JOIN core_portlet_type pt ON p.id_portlet_type = pt.id_portlet_type " +
66 " INNER JOIN core_page pa ON p.id_page = pa.id_page " + " WHERE p.date_update between ? AND ? ";
67 private static final String SQL_QUERY_SELECT_PAGES_BY_CRITERIAS = " SELECT name, description, date_update, id_page FROM core_page WHERE date_update between ? AND ? ";
68 private static final String SQL_QUERY_SELECT_PORTLETS = " SELECT p.name, p.date_update, p.id_page, p.id_portlet, pt.name, pa.name " +
69 " FROM core_portlet p INNER JOIN core_portlet_type pt ON p.id_portlet_type = pt.id_portlet_type " +
70 " INNER JOIN core_page pa ON p.id_page = pa.id_page ";
71 private static final String SQL_WHERE_ID_PORTLET = " id_portlet = ? ";
72 private static final String SQL_QUERY_SELECT_PAGES = " SELECT name, description, date_update, id_page FROM core_page ";
73 private static final String SQL_WHERE_ID_PAGE = " id_page = ? ";
74 private static final String SQL_QUERY_SELECT_DOCUMENTS = " SELECT a.title, a.document_summary as description, a.date_modification, a.id_document, b.id_portlet, c.document_type_name " +
75 " FROM document a INNER JOIN document_published b ON a.id_document = b.id_document " +
76 " INNER JOIN document_type c ON a.code_document_type = c.code_document_type ";
77 private static final String SQL_WHERE_ID_PORTLET_ID_DOCUMENT = " ( b.id_portlet = ? AND a.id_document = ? ) ";
78 private static final String SQL_WHERE = " WHERE ";
79 private static final String SQL_OR = " OR ";
80 private static final String SQL_AND = " AND ";
81
82
83
84
85 public Collection<IWhatsNew> selectPagesByCriterias( Timestamp dateLimit, Locale locale )
86 {
87 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PAGES_BY_CRITERIAS );
88 int nIndex = 1;
89 Timestamp timestampCurrent = new Timestamp( ( new Date( ) ).getTime( ) );
90 daoUtil.setTimestamp( nIndex++, dateLimit );
91 daoUtil.setTimestamp( nIndex++, timestampCurrent );
92 daoUtil.executeQuery( );
93
94 List<IWhatsNew> list = new ArrayList<IWhatsNew>( );
95
96 while ( daoUtil.next( ) )
97 {
98 nIndex = 1;
99
100 IWhatsNew whatsNew = new WhatsNewTypePage( );
101 whatsNew.setWhatsNewType( locale );
102 whatsNew.setTitle( daoUtil.getString( nIndex++ ) );
103 whatsNew.setDescription( daoUtil.getString( nIndex++ ) );
104 whatsNew.setDateUpdate( daoUtil.getTimestamp( nIndex++ ) );
105 whatsNew.setPageId( daoUtil.getInt( nIndex++ ) );
106 list.add( whatsNew );
107 }
108
109 daoUtil.free( );
110
111 return list;
112 }
113
114
115
116
117 public Collection<IWhatsNew> selectPortletsByCriterias( Timestamp dateLimit, Locale locale )
118 {
119 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLETS_BY_CRITERIAS );
120 int nIndex = 1;
121 Timestamp timestampCurrent = new Timestamp( ( new Date( ) ).getTime( ) );
122 daoUtil.setTimestamp( nIndex++, dateLimit );
123 daoUtil.setTimestamp( nIndex++, timestampCurrent );
124 daoUtil.executeQuery( );
125
126 List<IWhatsNew> list = new ArrayList<IWhatsNew>( );
127
128 while ( daoUtil.next( ) )
129 {
130 nIndex = 1;
131
132 WhatsNewTypePortlet whatsNew = new WhatsNewTypePortlet( );
133 whatsNew.setWhatsNewType( locale );
134 whatsNew.setTitle( daoUtil.getString( nIndex++ ) );
135 whatsNew.setDescription( StringUtils.EMPTY );
136 whatsNew.setDateUpdate( daoUtil.getTimestamp( nIndex++ ) );
137 whatsNew.setPageId( daoUtil.getInt( nIndex++ ) );
138 whatsNew.setPortletId( daoUtil.getInt( nIndex++ ) );
139
140 String strType = I18nService.getLocalizedString( daoUtil.getString( nIndex++ ), locale );
141 whatsNew.setType( strType );
142 whatsNew.setRefPageName( daoUtil.getString( nIndex++ ) );
143 list.add( whatsNew );
144 }
145
146 daoUtil.free( );
147
148 return list;
149 }
150
151
152
153
154 public Collection<IWhatsNew> selectDocumentsByCriterias( Timestamp dateLimit, Plugin plugin, Locale locale )
155 {
156 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DOCUMENTS_BY_CRITERIAS, plugin );
157 int nIndex = 1;
158 Timestamp timestampCurrent = new Timestamp( ( new Date( ) ).getTime( ) );
159 daoUtil.setTimestamp( nIndex++, dateLimit );
160 daoUtil.setTimestamp( nIndex++, timestampCurrent );
161 daoUtil.setTimestamp( nIndex++, timestampCurrent );
162 daoUtil.setTimestamp( nIndex++, timestampCurrent );
163 daoUtil.executeQuery( );
164
165 List<IWhatsNew> list = new ArrayList<IWhatsNew>( );
166
167 while ( daoUtil.next( ) )
168 {
169 nIndex = 1;
170
171 IWhatsNew whatsNew = new WhatsNewTypeDocument( );
172 whatsNew.setWhatsNewType( locale );
173 whatsNew.setTitle( daoUtil.getString( nIndex++ ) );
174 whatsNew.setDescription( daoUtil.getString( nIndex++ ) );
175 whatsNew.setDateUpdate( daoUtil.getTimestamp( nIndex++ ) );
176 whatsNew.setDocumentId( daoUtil.getInt( nIndex++ ) );
177 whatsNew.setPortletId( daoUtil.getInt( nIndex++ ) );
178 whatsNew.setType( daoUtil.getString( nIndex++ ) );
179 list.add( whatsNew );
180 }
181
182 daoUtil.free( );
183
184 return list;
185 }
186
187
188
189
190 public Collection<IWhatsNew> selectPages( List<Integer> listPageIds, Locale locale )
191 {
192 Collection<IWhatsNew> listWhatsNews = new ArrayList<IWhatsNew>( );
193 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_PAGES );
194
195 if ( listPageIds.size( ) > 0 )
196 {
197 sbSQL.append( SQL_WHERE );
198
199 for ( int nIndex = 0; nIndex < listPageIds.size( ); nIndex++ )
200 {
201 sbSQL.append( SQL_WHERE_ID_PAGE );
202
203 if ( nIndex < ( listPageIds.size( ) - 1 ) )
204 {
205 sbSQL.append( SQL_OR );
206 }
207 }
208
209 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) );
210 int nIndex = 1;
211
212 for ( Integer nPageId : listPageIds )
213 {
214 daoUtil.setInt( nIndex++, nPageId );
215 }
216
217 daoUtil.executeQuery( );
218
219 while ( daoUtil.next( ) )
220 {
221 nIndex = 1;
222
223 IWhatsNew whatsNew = new WhatsNewTypePage( );
224 whatsNew.setWhatsNewType( locale );
225 whatsNew.setTitle( daoUtil.getString( nIndex++ ) );
226 whatsNew.setDescription( daoUtil.getString( nIndex++ ) );
227 whatsNew.setDateUpdate( daoUtil.getTimestamp( nIndex++ ) );
228 whatsNew.setPageId( daoUtil.getInt( nIndex++ ) );
229 listWhatsNews.add( whatsNew );
230 }
231
232 daoUtil.free( );
233 }
234
235 return listWhatsNews;
236 }
237
238
239
240
241 public Collection<IWhatsNew> selectPortlets( List<Integer> listPortletIds, Locale locale )
242 {
243 Collection<IWhatsNew> listWhatsNews = new ArrayList<IWhatsNew>( );
244
245 if ( listPortletIds.size( ) > 0 )
246 {
247 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_PORTLETS );
248 sbSQL.append( SQL_WHERE );
249
250 for ( int nIndex = 0; nIndex < listPortletIds.size( ); nIndex++ )
251 {
252 sbSQL.append( SQL_WHERE_ID_PORTLET );
253
254 if ( nIndex < ( listPortletIds.size( ) - 1 ) )
255 {
256 sbSQL.append( SQL_OR );
257 }
258 }
259
260 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) );
261 int nIndex = 1;
262
263 for ( Integer nPortletId : listPortletIds )
264 {
265 daoUtil.setInt( nIndex++, nPortletId );
266 }
267
268 daoUtil.executeQuery( );
269
270 while ( daoUtil.next( ) )
271 {
272 nIndex = 1;
273
274 WhatsNewTypePortlet whatsNew = new WhatsNewTypePortlet( );
275 whatsNew.setWhatsNewType( locale );
276 whatsNew.setTitle( daoUtil.getString( nIndex++ ) );
277 whatsNew.setDescription( StringUtils.EMPTY );
278 whatsNew.setDateUpdate( daoUtil.getTimestamp( nIndex++ ) );
279 whatsNew.setPageId( daoUtil.getInt( nIndex++ ) );
280 whatsNew.setPortletId( daoUtil.getInt( nIndex++ ) );
281
282 String strType = I18nService.getLocalizedString( daoUtil.getString( nIndex++ ), locale );
283 whatsNew.setType( strType );
284 whatsNew.setRefPageName( daoUtil.getString( nIndex++ ) );
285 listWhatsNews.add( whatsNew );
286 }
287
288 daoUtil.free( );
289 }
290
291 return listWhatsNews;
292 }
293
294
295
296
297 public Collection<IWhatsNew> selectDocuments( List<PortletDocumentLink> listPortletDocumentLinks, Locale locale )
298 {
299 Collection<IWhatsNew> listWhatsNews = new ArrayList<IWhatsNew>( );
300 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_DOCUMENTS );
301
302 if ( listPortletDocumentLinks.size( ) > 0 )
303 {
304 sbSQL.append( SQL_WHERE );
305
306 for ( int nIndex = 0; nIndex < listPortletDocumentLinks.size( ); nIndex++ )
307 {
308 sbSQL.append( SQL_WHERE_ID_PORTLET_ID_DOCUMENT );
309
310 if ( nIndex < ( listPortletDocumentLinks.size( ) - 1 ) )
311 {
312 sbSQL.append( SQL_OR );
313 }
314 }
315
316 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) );
317 int nIndex = 1;
318
319 for ( PortletDocumentLink pdLink : listPortletDocumentLinks )
320 {
321 daoUtil.setInt( nIndex++, pdLink.getPortletId( ) );
322 daoUtil.setInt( nIndex++, pdLink.getDocumentId( ) );
323 }
324
325 daoUtil.executeQuery( );
326
327 while ( daoUtil.next( ) )
328 {
329 nIndex = 1;
330
331 IWhatsNew whatsNew = new WhatsNewTypeDocument( );
332 whatsNew.setWhatsNewType( locale );
333 whatsNew.setTitle( daoUtil.getString( nIndex++ ) );
334 whatsNew.setDescription( daoUtil.getString( nIndex++ ) );
335 whatsNew.setDateUpdate( daoUtil.getTimestamp( nIndex++ ) );
336 whatsNew.setDocumentId( daoUtil.getInt( nIndex++ ) );
337 whatsNew.setPortletId( daoUtil.getInt( nIndex++ ) );
338 whatsNew.setType( daoUtil.getString( nIndex++ ) );
339 listWhatsNews.add( whatsNew );
340 }
341
342 daoUtil.free( );
343 }
344
345 return listWhatsNews;
346 }
347
348
349
350
351 public boolean isPageOutOfDate( int nPageId, Timestamp dateLimit )
352 {
353 boolean bIsOutOfDate = true;
354 StringBuilder sbSQL = new StringBuilder( );
355 sbSQL.append( SQL_QUERY_SELECT_PAGES_BY_CRITERIAS );
356 sbSQL.append( SQL_AND );
357 sbSQL.append( SQL_WHERE_ID_PAGE );
358
359 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) );
360 int nIndex = 1;
361 Timestamp timestampCurrent = new Timestamp( ( new Date( ) ).getTime( ) );
362 daoUtil.setTimestamp( nIndex++, dateLimit );
363 daoUtil.setTimestamp( nIndex++, timestampCurrent );
364 daoUtil.setInt( nIndex++, nPageId );
365 daoUtil.executeQuery( );
366
367 if ( daoUtil.next( ) )
368 {
369 bIsOutOfDate = false;
370 }
371
372 daoUtil.free( );
373
374 return bIsOutOfDate;
375 }
376
377
378
379
380 public boolean isPortletOutOfDate( int nPortletId, Timestamp dateLimit )
381 {
382 boolean bIsOutOfDate = true;
383 StringBuilder sbSQL = new StringBuilder( );
384 sbSQL.append( SQL_QUERY_SELECT_PORTLETS_BY_CRITERIAS );
385 sbSQL.append( SQL_AND );
386 sbSQL.append( SQL_WHERE_ID_PORTLET );
387
388 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) );
389 int nIndex = 1;
390 Timestamp timestampCurrent = new Timestamp( ( new Date( ) ).getTime( ) );
391 daoUtil.setTimestamp( nIndex++, dateLimit );
392 daoUtil.setTimestamp( nIndex++, timestampCurrent );
393 daoUtil.setInt( nIndex++, nPortletId );
394 daoUtil.executeQuery( );
395
396 if ( daoUtil.next( ) )
397 {
398 bIsOutOfDate = false;
399 }
400
401 daoUtil.free( );
402
403 return bIsOutOfDate;
404 }
405
406
407
408
409 public boolean isDocumentOutOfDate( PortletDocumentLink pdLink, Timestamp dateLimit, Plugin plugin )
410 {
411 boolean bIsOutOfDate = true;
412
413 StringBuilder sbSQL = new StringBuilder( );
414 sbSQL.append( SQL_QUERY_SELECT_DOCUMENTS_BY_CRITERIAS );
415 sbSQL.append( SQL_AND );
416 sbSQL.append( SQL_WHERE_ID_PORTLET_ID_DOCUMENT );
417
418 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
419 int nIndex = 1;
420 Timestamp timestampCurrent = new Timestamp( ( new Date( ) ).getTime( ) );
421 daoUtil.setTimestamp( nIndex++, dateLimit );
422 daoUtil.setTimestamp( nIndex++, timestampCurrent );
423 daoUtil.setTimestamp( nIndex++, timestampCurrent );
424 daoUtil.setTimestamp( nIndex++, timestampCurrent );
425 daoUtil.setInt( nIndex++, pdLink.getPortletId( ) );
426 daoUtil.setInt( nIndex++, pdLink.getDocumentId( ) );
427 daoUtil.executeQuery( );
428
429 if ( daoUtil.next( ) )
430 {
431 bIsOutOfDate = false;
432 }
433
434 daoUtil.free( );
435
436 return bIsOutOfDate;
437 }
438 }