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.rss.business;
35
36 import fr.paris.lutece.plugins.document.business.Document;
37 import fr.paris.lutece.plugins.document.business.portlet.DocumentListPortletHome;
38 import fr.paris.lutece.portal.business.portlet.PortletImpl;
39 import fr.paris.lutece.portal.business.stylesheet.StyleSheet;
40 import fr.paris.lutece.util.sql.DAOUtil;
41
42 import java.util.ArrayList;
43 import java.util.Collection;
44 import java.util.List;
45
46
47
48
49
50 public final class RssGeneratedFileDAO implements IRssGeneratedFileDAO
51 {
52
53 private static final String SQL_QUERY_NEW_PK = " SELECT max(id_rss) FROM rss_generation ";
54 private static final String SQL_QUERY_INSERT = " INSERT INTO rss_generation ( id_rss, id_portlet, name, state, date_update, description,workgroup_key, type_resource_rss, max_items, feed_type, feed_encoding )" +
55 " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
56 private static final String SQL_QUERY_DELETE = " DELETE FROM rss_generation WHERE id_rss = ? ";
57
58
59 private static final String SQL_QUERY_UPDATE = " UPDATE rss_generation SET state = ?, date_update = ? WHERE id_rss = ? ";
60 private static final String SQL_QUERY_RSS_FILE_LIST = "SELECT a.id_rss, a.id_portlet, a.name, " +
61 "a.state, a.date_update, a.description, a.workgroup_key,a.type_resource_rss, a.max_items, a.feed_type, a.feed_encoding FROM rss_generation as a " +
62 "LEFT JOIN core_portlet as b ON (a.id_portlet=b.id_portlet) WHERE a.id_portlet=b.id_portlet OR a.type_resource_rss IS NOT NULL ORDER BY a.name ASC ";
63 private static final String SQL_QUERY_UPDATE_RSS_FILE = " UPDATE rss_generation SET id_portlet = ?, name = ?, state = ?, date_update = ?, description =?, " +
64 " workgroup_key =?, type_resource_rss=? , max_items = ? , feed_type = ? , feed_encoding = ? WHERE id_rss = ?";
65 private static final String SQL_QUERY_SELECT_GENERATE_FILE = " SELECT id_portlet, name, state, date_update,description,workgroup_key,type_resource_rss, max_items,feed_type,feed_encoding" +
66 " FROM rss_generation" + " WHERE id_rss = ?";
67 private static final String SQL_QUERY_EXIST_RSS_FILE = " SELECT id_rss, name, state, date_update" +
68 " FROM rss_generation" + " WHERE id_portlet = ?";
69 private static final String SQL_QUERY_FILE_NAME_EXIST = "SELECT id_rss " + "FROM rss_generation " +
70 "WHERE name = ?";
71 private static final String SQL_QUERY_SELECT_RSS_PORTLET = " SELECT a.id_portlet, a.name, a.date_update " +
72 " FROM portlet a LEFT JOIN rss_generation b ON a.id_portlet=b.id_portlet " +
73 " WHERE b.id_portlet IS NULL AND a.id_portlet_type = ? ";
74 private static final String SQL_QUERY_SELECT_PORTLET_NAME = " SELECT name " + " FROM portlet " +
75 " WHERE id_portlet = ? ";
76 private static final String SQL_QUERY_SELECT_ALL_RSS = " SELECT portlet.id_portlet, portlet.name, portlet.date_update " +
77 "FROM portlet WHERE portlet.id_portlet_type = ? ";
78 private static final String SQL_QUERY_CHECK_PORTLET_EXISTENCE = "SELECT id_portlet" + " FROM core_portlet" +
79 " WHERE id_portlet = ?";
80 private static final String SQL_QUERY_SELECT_DOCUMENT_BY_PORTLET = "SELECT a.id_document , a.code_document_type ," +
81 "a.date_creation ,a.date_modification, a.title, a.document_summary, a.xml_validated_content " +
82 "FROM document a INNER JOIN document_published b ON a.id_document=b.id_document " +
83 "WHERE b.id_portlet = ? AND b.status = 0 ORDER BY b.date_publishing DESC";
84 private static final String SQL_QUERY_SELECT_XSL_FILE = " SELECT id_stylesheet , description , file_name, source " +
85 " FROM stylesheet " + " WHERE id_stylesheet = ? ";
86
87
88
89
90
91 private int newPrimaryKey( )
92 {
93 int nKey;
94 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK ) )
95 {
96 daoUtil.executeQuery( );
97
98 if ( daoUtil.next( ) )
99 {
100 nKey = daoUtil.getInt( 1 ) + 1;
101 }
102 else
103 {
104
105 nKey = 1;
106 }
107 }
108
109 return nKey;
110 }
111
112
113
114
115
116 public void insert( RssGeneratedFile rssFile )
117 {
118 int nNewPrimaryKey = newPrimaryKey( );
119 rssFile.setId( nNewPrimaryKey );
120
121 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
122 {
123 daoUtil.setInt( 1, rssFile.getId( ) );
124 daoUtil.setInt( 2, rssFile.getPortletId( ) );
125 daoUtil.setString( 3, rssFile.getName( ) );
126 daoUtil.setInt( 4, rssFile.getState( ) );
127 daoUtil.setTimestamp( 5, new java.sql.Timestamp( new java.util.Date( ).getTime( ) ) );
128 daoUtil.setString( 6, rssFile.getDescription( ) );
129 daoUtil.setString( 7, rssFile.getWorkgroup( ) );
130 daoUtil.setString( 8, rssFile.getTypeResourceRss( ) );
131 daoUtil.setInt( 9, rssFile.getMaxItems( ) );
132 daoUtil.setString( 10, rssFile.getFeedType( ) );
133 daoUtil.setString( 11, rssFile.getEncoding( ) );
134 daoUtil.executeUpdate( );
135 }
136 }
137
138
139
140
141
142 public void delete( int nRssFileId )
143 {
144 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
145 {
146 daoUtil.setInt( 1, nRssFileId );
147 daoUtil.executeUpdate( );
148 }
149 }
150
151
152
153
154
155 public void store( RssGeneratedFile rssFile )
156 {
157 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_RSS_FILE ) )
158 {
159 daoUtil.setInt( 1, rssFile.getPortletId( ) );
160 daoUtil.setString( 2, rssFile.getName( ) );
161 daoUtil.setInt( 3, rssFile.getState( ) );
162 daoUtil.setTimestamp( 4, new java.sql.Timestamp( new java.util.Date( ).getTime( ) ) );
163 daoUtil.setString( 5, rssFile.getDescription( ) );
164 daoUtil.setString( 6, rssFile.getWorkgroup( ) );
165 daoUtil.setString( 7, rssFile.getTypeResourceRss( ) );
166 daoUtil.setInt( 8, rssFile.getMaxItems( ) );
167 daoUtil.setString( 9, rssFile.getFeedType( ) );
168 daoUtil.setString( 10, rssFile.getEncoding( ) );
169 daoUtil.setInt( 11, rssFile.getId( ) );
170
171 daoUtil.executeUpdate( );
172 }
173 }
174
175
176
177
178
179 public void updateState( RssGeneratedFile rssFile )
180 {
181 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
182 {
183 daoUtil.setInt( 1, rssFile.getState( ) );
184
185 daoUtil.setTimestamp( 2, new java.sql.Timestamp( new java.util.Date( ).getTime( ) ) );
186 daoUtil.setInt( 3, rssFile.getId( ) );
187 daoUtil.executeUpdate( );
188 }
189 }
190
191
192
193
194
195
196 public RssGeneratedFile load( int nRssFileId )
197 {
198 RssGeneratedFile rssFile = null;
199
200 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_GENERATE_FILE ) )
201 {
202 daoUtil.setInt( 1, nRssFileId );
203 daoUtil.executeQuery( );
204
205 if ( daoUtil.next( ) )
206 {
207 rssFile = new RssGeneratedFile( );
208 rssFile.setId( nRssFileId );
209 rssFile.setPortletId( daoUtil.getInt( 1 ) );
210 rssFile.setName( daoUtil.getString( 2 ) );
211 rssFile.setState( daoUtil.getInt( 3 ) );
212 rssFile.setUpdateDate( daoUtil.getTimestamp( 4 ) );
213 rssFile.setDescription( daoUtil.getString( 5 ) );
214 rssFile.setWorkgroup( daoUtil.getString( 6 ) );
215 rssFile.setTypeResourceRss( daoUtil.getString( 7 ) );
216 rssFile.setMaxItems( daoUtil.getInt( 8 ) );
217 rssFile.setFeedType( daoUtil.getString( 9 ) );
218 rssFile.setEncoding( daoUtil.getString( 10 ) );
219 }
220
221 }
222
223 return rssFile;
224 }
225
226
227
228
229
230
231 public boolean checkExistPushrssByPortlet( int nPortletId )
232 {
233 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_EXIST_RSS_FILE ) )
234 {
235 daoUtil.setInt( 1, nPortletId );
236 daoUtil.executeQuery( );
237
238 if ( !daoUtil.next( ) )
239 {
240 daoUtil.free( );
241
242 return false;
243 }
244
245 }
246
247 return true;
248 }
249
250
251
252
253
254
255 public boolean checkRssFileFileName( String strRssFileName )
256 {
257 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FILE_NAME_EXIST ) )
258 {
259 daoUtil.setString( 1, strRssFileName );
260 daoUtil.executeQuery( );
261
262 if ( !daoUtil.next( ) )
263 {
264 daoUtil.free( );
265
266 return false;
267 }
268 }
269
270 return true;
271 }
272
273
274
275
276
277 public List<RssGeneratedFile> selectRssFileList( )
278 {
279 List<RssGeneratedFile> list = new ArrayList<>( );
280 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_RSS_FILE_LIST ) )
281 {
282 daoUtil.executeQuery( );
283
284 while ( daoUtil.next( ) )
285 {
286 RssGeneratedFileess/RssGeneratedFile.html#RssGeneratedFile">RssGeneratedFile rssFile = new RssGeneratedFile( );
287 rssFile.setId( daoUtil.getInt( 1 ) );
288 rssFile.setPortletId( daoUtil.getInt( 2 ) );
289 rssFile.setName( daoUtil.getString( 3 ) );
290 rssFile.setState( daoUtil.getInt( 4 ) );
291 rssFile.setUpdateDate( daoUtil.getTimestamp( 5 ) );
292 rssFile.setDescription( daoUtil.getString( 6 ) );
293 rssFile.setWorkgroup( daoUtil.getString( 7 ) );
294 rssFile.setTypeResourceRss( daoUtil.getString( 8 ) );
295 rssFile.setMaxItems( daoUtil.getInt( 9 ) );
296 rssFile.setFeedType( daoUtil.getString( 10 ) );
297 rssFile.setEncoding( daoUtil.getString( 11 ) );
298
299 list.add( rssFile );
300 }
301 }
302
303 return list;
304 }
305
306
307
308
309
310 public Collection<PortletImpl> selectRssPortlets( )
311 {
312 ArrayList<PortletImpl> list = new ArrayList<>( );
313 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RSS_PORTLET ) )
314 {
315 daoUtil.executeQuery( );
316
317 while ( daoUtil.next( ) )
318 {
319 PortletImpl portlet = new PortletImpl( );
320 portlet.setId( daoUtil.getInt( 1 ) );
321 portlet.setName( daoUtil.getString( 2 ) );
322 portlet.setDateUpdate( daoUtil.getTimestamp( 3 ) );
323 list.add( portlet );
324 }
325
326 }
327
328 return list;
329 }
330
331
332
333
334
335 public Collection<PortletImpl> selectAllRssPortlets( )
336 {
337 ArrayList<PortletImpl> list = new ArrayList<>( );
338 String strPortletTypeId = DocumentListPortletHome.getInstance( ).getPortletTypeId( );
339 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_RSS ) )
340 {
341 daoUtil.setString( 1, strPortletTypeId );
342
343 daoUtil.executeQuery( );
344
345 while ( daoUtil.next( ) )
346 {
347 PortletImpl portlet = new PortletImpl( );
348 portlet.setId( daoUtil.getInt( 1 ) );
349 portlet.setName( daoUtil.getString( 2 ) );
350 portlet.setDateUpdate( daoUtil.getTimestamp( 3 ) );
351 list.add( portlet );
352 }
353 }
354
355 return list;
356 }
357
358
359
360
361
362
363 public String selectRssFilePortletName( int nPortletId )
364 {
365 String strPortletName = "";
366 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_NAME ) )
367 {
368 daoUtil.setInt( 1, nPortletId );
369
370 daoUtil.executeQuery( );
371
372 if ( daoUtil.next( ) )
373 {
374 strPortletName = daoUtil.getString( 1 );
375 }
376 }
377
378 return strPortletName;
379 }
380
381
382
383
384
385
386 public boolean checkRssFilePortlet( int nPortletId )
387 {
388 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_PORTLET_EXISTENCE ) )
389 {
390 daoUtil.setInt( 1, nPortletId );
391
392 daoUtil.executeQuery( );
393
394 if ( !daoUtil.next( ) )
395 {
396 daoUtil.free( );
397
398 return false;
399 }
400 }
401
402 return true;
403 }
404
405
406
407
408
409
410
411 public List<Document> selectDocumentsByPortlet( int nPortletId )
412 {
413 List<Document> list = new ArrayList<>( );
414 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DOCUMENT_BY_PORTLET ) )
415 {
416 daoUtil.setInt( 1, nPortletId );
417
418 daoUtil.executeQuery( );
419
420 while ( daoUtil.next( ) )
421 {
422 Document document = new Document( );
423 document.setId( daoUtil.getInt( 1 ) );
424 document.setCodeDocumentType( daoUtil.getString( 2 ) );
425 document.setDateCreation( daoUtil.getTimestamp( 3 ) );
426 document.setDateModification( daoUtil.getTimestamp( 4 ) );
427 document.setTitle( daoUtil.getString( 5 ) );
428 document.setSummary( daoUtil.getString( 6 ) );
429 document.setXmlValidatedContent( daoUtil.getString( 7 ) );
430 list.add( document );
431 }
432 }
433
434 return list;
435 }
436
437
438
439
440
441
442 public StyleSheet selectXslFile( int nStyleSheetId )
443 {
444 StyleSheet stylesheet = new StyleSheet( );
445 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_XSL_FILE ) )
446 {
447 daoUtil.setInt( 1, nStyleSheetId );
448
449 daoUtil.executeQuery( );
450
451 if ( daoUtil.next( ) )
452 {
453 stylesheet.setId( daoUtil.getInt( 1 ) );
454 stylesheet.setDescription( daoUtil.getString( 2 ) );
455 stylesheet.setFile( daoUtil.getString( 3 ) );
456 stylesheet.setSource( daoUtil.getBytes( 4 ) );
457 }
458 }
459
460 return stylesheet;
461 }
462 }