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.newsletter.modules.document.business;
35
36 import fr.paris.lutece.plugins.document.business.Document;
37 import fr.paris.lutece.plugins.document.business.publication.DocumentPublication;
38 import fr.paris.lutece.portal.service.plugin.Plugin;
39 import fr.paris.lutece.util.ReferenceList;
40 import fr.paris.lutece.util.sql.DAOUtil;
41
42 import java.sql.Timestamp;
43 import java.util.ArrayList;
44 import java.util.Collection;
45 import java.util.Date;
46 import java.util.List;
47
48
49
50
51
52 public class NewsletterDocumentDAO implements INewsletterDocumentDAO
53 {
54 private static final String SQL_QUERY_SELECT_NEWSLETTER_DOCUMENT_TOPIC = " SELECT id_topic, id_template, use_categories FROM newsletter_document_topic WHERE id_topic = ? ";
55 private static final String SQL_QUERY_INSERT_NEWSLETTER_DOCUMENT_TOPIC = " INSERT INTO newsletter_document_topic(id_topic, id_template, use_categories) VALUES (?,?,?) ";
56 private static final String SQL_QUERY_UPDATE_NEWSLETTER_DOCUMENT_TOPIC = " UPDATE newsletter_document_topic SET id_template = ?, use_categories = ? WHERE id_topic = ? ";
57 private static final String SQL_QUERY_DELETE_NEWSLETTER_DOCUMENT_TOPIC = " DELETE FROM newsletter_document_topic WHERE id_topic = ? ";
58
59 private static final String SQL_QUERY_SELECT_DOCUMENT_BY_DATE_AND_LIST_DOCUMENT = "SELECT DISTINCT a.id_document , a.code_document_type, a.date_creation , a.date_modification, a.title, a.document_summary FROM document a INNER JOIN document_published b ON a.id_document=b.id_document INNER JOIN core_portlet c ON b.id_portlet=c.id_portlet WHERE c.id_portlet_type='DOCUMENT_LIST_PORTLET' ";
60 private static final String SQL_QUERY_DOCUMENT_TYPE_PORTLET = " SELECT DISTINCT id_portlet , name FROM core_portlet WHERE id_portlet_type='DOCUMENT_LIST_PORTLET' ";
61 private static final String SQL_QUERY_ASSOCIATE_NEWSLETTER_CATEGORY_LIST = "INSERT INTO newsletter_document_category ( id_topic , id_category ) VALUES ( ?, ? ) ";
62 private static final String SQL_QUERY_DELETE_NEWSLETTER_CATEGORY_LIST = "DELETE FROM newsletter_document_category WHERE id_topic = ?";
63 private static final String SQL_QUERY_SELECT_NEWSLETTER_CATEGORY_IDS = "SELECT id_category FROM newsletter_document_category WHERE id_topic = ?";
64 private static final String SQL_QUERY_ASSOCIATE_NEWSLETTER_PORTLET = "INSERT INTO newsletter_document_portlet ( id_topic , id_portlet ) VALUES ( ?, ? ) ";
65 private static final String SQL_QUERY_SELECT_NEWSLETTER_PORTLET_IDS = " SELECT id_portlet FROM newsletter_document_portlet WHERE id_topic = ? ";
66 private static final String SQL_QUERY_DELETE_NEWSLETTER_PORTLET = "DELETE FROM newsletter_document_portlet WHERE id_topic = ?";
67 private static final String SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS = " SELECT DISTINCT pub.id_document FROM document_published pub, document doc "
68 + "WHERE doc.id_document=pub.id_document AND pub.status = ? AND doc.date_modification >= ? AND pub.id_portlet IN ";
69 private static final String SQL_QUERY_FIND_TEMPLATE = " SELECT count(id_template) FROM newsletter_document_topic WHERE id_template = ? ";
70 private static final String SQL_FILTER_DATE_MODIF = " a.date_modification >=? ";
71 private static final String SQL_FILTER_ID_PORTLET = " c.id_portlet = ? ";
72
73 private static final String CONSTANT_AND = " AND ";
74 private static final String CONSTANT_OPEN_PARENTHESIS = "(";
75 private static final String CONSTANT_CLOSE_PARENTHESIS = ")";
76 private static final String CONSTANT_QUESTION_MARK = "?";
77 private static final String CONSTANT_COMMA = ",";
78 private static final String CONSTANT_ORDER_BY_DATE_MODIF = " ORDER BY a.date_modification DESC ";
79
80
81
82
83 @Override
84 public NewsletterDocument findByPrimaryKey( int nIdTopic, Plugin plugin )
85 {
86 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NEWSLETTER_DOCUMENT_TOPIC, plugin );
87 daoUtil.setInt( 1, nIdTopic );
88 daoUtil.executeQuery( );
89 NewsletterDocument topic = null;
90 if ( daoUtil.next( ) )
91 {
92 topic = new NewsletterDocument( );
93 topic.setId( daoUtil.getInt( 1 ) );
94 topic.setIdTemplate( daoUtil.getInt( 2 ) );
95 topic.setUseDocumentCategories( daoUtil.getBoolean( 3 ) );
96 }
97 daoUtil.free( );
98 return topic;
99 }
100
101
102
103
104 @Override
105 public void updateDocumentTopic( NewsletterDocument topic, Plugin plugin )
106 {
107 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_NEWSLETTER_DOCUMENT_TOPIC, plugin );
108 daoUtil.setInt( 1, topic.getIdTemplate( ) );
109 daoUtil.setBoolean( 2, topic.getUseDocumentCategories( ) );
110 daoUtil.setInt( 3, topic.getId( ) );
111 daoUtil.executeUpdate( );
112 daoUtil.free( );
113 }
114
115
116
117
118 @Override
119 public void deleteDocumentTopic( int nIdTopic, Plugin plugin )
120 {
121 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_NEWSLETTER_DOCUMENT_TOPIC, plugin );
122 daoUtil.setInt( 1, nIdTopic );
123 daoUtil.executeUpdate( );
124 daoUtil.free( );
125 }
126
127
128
129
130 @Override
131 public void createDocumentTopic( NewsletterDocument topic, Plugin plugin )
132 {
133 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_NEWSLETTER_DOCUMENT_TOPIC, plugin );
134 daoUtil.setInt( 1, topic.getId( ) );
135 daoUtil.setInt( 2, topic.getIdTemplate( ) );
136 daoUtil.setBoolean( 3, topic.getUseDocumentCategories( ) );
137 daoUtil.executeUpdate( );
138 daoUtil.free( );
139 }
140
141
142
143
144 @Override
145 public Collection<Document> selectDocumentsByDateAndCategory( int nPortletId, Timestamp dateLastSending,
146 Plugin plugin )
147 {
148 StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT_DOCUMENT_BY_DATE_AND_LIST_DOCUMENT );
149
150 sbSql.append( CONSTANT_AND );
151 sbSql.append( SQL_FILTER_DATE_MODIF );
152 if ( nPortletId > 0 )
153 {
154 sbSql.append( CONSTANT_AND );
155 sbSql.append( SQL_FILTER_ID_PORTLET );
156 }
157 sbSql.append( CONSTANT_ORDER_BY_DATE_MODIF );
158
159 DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin );
160 int nIndex = 1;
161 daoUtil.setTimestamp( nIndex++, dateLastSending );
162 if ( nPortletId > 0 )
163 {
164 daoUtil.setInt( nIndex, nPortletId );
165 }
166
167 daoUtil.executeQuery( );
168
169 List<Document> list = new ArrayList<Document>( );
170
171 while ( daoUtil.next( ) )
172 {
173 Document document = new Document( );
174 document.setId( daoUtil.getInt( 1 ) );
175 document.setCodeDocumentType( daoUtil.getString( 2 ) );
176 document.setDateCreation( daoUtil.getTimestamp( 3 ) );
177 document.setDateModification( daoUtil.getTimestamp( 4 ) );
178 document.setTitle( daoUtil.getString( 5 ) );
179 document.setSummary( daoUtil.getString( 6 ) );
180 list.add( document );
181 }
182
183 daoUtil.free( );
184
185 return list;
186 }
187
188
189
190
191 @Override
192 public ReferenceList selectDocumentListPortlets( )
193 {
194 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DOCUMENT_TYPE_PORTLET );
195 daoUtil.executeQuery( );
196
197 ReferenceList list = new ReferenceList( );
198
199 while ( daoUtil.next( ) )
200 {
201 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
202 }
203
204 daoUtil.free( );
205
206 return list;
207 }
208
209
210
211
212 @Override
213 public void associateNewsLetterDocumentCategory( int nTopicId, int nCategoryId, Plugin plugin )
214 {
215 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ASSOCIATE_NEWSLETTER_CATEGORY_LIST, plugin );
216 daoUtil.setInt( 1, nTopicId );
217 daoUtil.setInt( 2, nCategoryId );
218
219 daoUtil.executeUpdate( );
220 daoUtil.free( );
221 }
222
223
224
225
226 @Override
227 public void deleteNewsLetterDocumentCategories( int nTopicId, Plugin plugin )
228 {
229 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_NEWSLETTER_CATEGORY_LIST, plugin );
230
231 daoUtil.setInt( 1, nTopicId );
232
233 daoUtil.executeUpdate( );
234 daoUtil.free( );
235 }
236
237
238
239
240 @Override
241 public int[] selectNewsletterCategoryIds( int nTopicId, Plugin plugin )
242 {
243 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NEWSLETTER_CATEGORY_IDS, plugin );
244 daoUtil.setInt( 1, nTopicId );
245 daoUtil.executeQuery( );
246
247 List<Integer> list = new ArrayList<Integer>( );
248
249 while ( daoUtil.next( ) )
250 {
251 int nResultId = daoUtil.getInt( 1 );
252 list.add( Integer.valueOf( nResultId ) );
253 }
254
255 int[] nIdsArray = new int[list.size( )];
256
257 for ( int i = 0; i < list.size( ); i++ )
258 {
259 Integer nId = list.get( i );
260 nIdsArray[i] = nId.intValue( );
261 }
262
263 daoUtil.free( );
264
265 return nIdsArray;
266 }
267
268
269
270
271 @Override
272 public void associateNewsLetterDocumentPortlet( int nTopicId, int nPortletId, Plugin plugin )
273 {
274 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ASSOCIATE_NEWSLETTER_PORTLET, plugin );
275 daoUtil.setInt( 1, nTopicId );
276 daoUtil.setInt( 2, nPortletId );
277
278 daoUtil.executeUpdate( );
279 daoUtil.free( );
280 }
281
282
283
284
285 @Override
286 public void deleteNewsLetterDocumentPortlet( int nTopicId, Plugin plugin )
287 {
288 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_NEWSLETTER_PORTLET, plugin );
289
290 daoUtil.setInt( 1, nTopicId );
291
292 daoUtil.executeUpdate( );
293 daoUtil.free( );
294 }
295
296
297
298
299 @Override
300 public int[] selectNewsletterPortletsIds( int nTopicId, Plugin plugin )
301 {
302 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NEWSLETTER_PORTLET_IDS, plugin );
303 daoUtil.setInt( 1, nTopicId );
304 daoUtil.executeQuery( );
305
306 List<Integer> list = new ArrayList<Integer>( );
307
308 while ( daoUtil.next( ) )
309 {
310 int nResultId = daoUtil.getInt( 1 );
311 list.add( Integer.valueOf( nResultId ) );
312 }
313
314 int[] nIdsArray = new int[list.size( )];
315
316 for ( int i = 0; i < list.size( ); i++ )
317 {
318 Integer nId = list.get( i );
319 nIdsArray[i] = nId.intValue( );
320 }
321
322 daoUtil.free( );
323
324 return nIdsArray;
325 }
326
327
328
329
330 @Override
331 public List<Integer> getPublishedDocumentsIdsListByPortletIds( int[] nPortletsIds, Date datePublishing,
332 Plugin plugin )
333 {
334 List<Integer> listIds = new ArrayList<Integer>( );
335 if ( nPortletsIds == null || nPortletsIds.length == 0 )
336 {
337 return listIds;
338 }
339 StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS );
340 sbSql.append( CONSTANT_OPEN_PARENTHESIS );
341
342 for ( int i = 0; i < nPortletsIds.length; i++ )
343 {
344 sbSql.append( CONSTANT_QUESTION_MARK );
345 if ( i + 1 < nPortletsIds.length )
346 {
347 sbSql.append( CONSTANT_COMMA );
348 }
349 }
350 sbSql.append( CONSTANT_CLOSE_PARENTHESIS );
351
352 DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin );
353 int nIndex = 1;
354 daoUtil.setInt( nIndex++, DocumentPublication.STATUS_PUBLISHED );
355 daoUtil.setTimestamp( nIndex++, new Timestamp( datePublishing.getTime( ) ) );
356 for ( int nPortletId : nPortletsIds )
357 {
358 daoUtil.setInt( nIndex++, nPortletId );
359 }
360 daoUtil.executeQuery( );
361 while ( daoUtil.next( ) )
362 {
363 listIds.add( daoUtil.getInt( 1 ) );
364 }
365 daoUtil.free( );
366 return listIds;
367
368 }
369
370
371
372
373 @Override
374 public boolean findTemplate( int nIdNewsletterTemplate, Plugin plugin )
375 {
376 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_TEMPLATE, plugin );
377 daoUtil.setInt( 1, nIdNewsletterTemplate );
378 daoUtil.executeQuery( );
379
380 boolean bRes = false;
381 if ( daoUtil.next( ) )
382 {
383 bRes = daoUtil.getInt( 1 ) > 0;
384 }
385
386 daoUtil.free( );
387
388 return bRes;
389 }
390 }