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.business;
35
36 import fr.paris.lutece.plugins.newsletter.service.NewsletterPlugin;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.portal.service.plugin.PluginService;
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
46
47
48
49 public final class NewsLetterDAO implements INewsLetterDAO
50 {
51
52 private static final String SQL_QUERY_SELECT = "SELECT name, description, date_last_send, html, id_newsletter_template, workgroup_key, unsubscribe, sender_mail, sender_name, test_recipients, test_subject, nb_sections FROM newsletter_description WHERE id_newsletter = ? ";
53 private static final String SQL_QUERY_SELECT_ALL = "SELECT id_newsletter , name, description, date_last_send, html, id_newsletter_template, workgroup_key, test_recipients , sender_mail, sender_name, test_subject, nb_sections FROM newsletter_description ";
54 private static final String SQL_QUERY_SELECT_ALL_ID = "SELECT id_newsletter, name FROM newsletter_description ";
55 private static final String SQL_QUERY_SELECT_ALL_BY_ID_TEMPLATE = "SELECT id_newsletter , name, description, date_last_send, html, id_newsletter_template, workgroup_key, test_recipients , sender_mail, sender_name, test_subject, nb_sections FROM newsletter_description WHERE id_newsletter_template = ? ";
56 private static final String SQL_QUERY_SELECT_NBR_SUBSCRIBERS = "SELECT count(*) FROM newsletter_subscriber a, newsletter_subscriber_details b WHERE a.id_subscriber = b.id_subscriber AND b.email LIKE ? AND id_newsletter = ? ";
57 private static final String SQL_QUERY_SELECT_NBR_ACTIVE_SUBSCRIBERS = "SELECT count(*) FROM newsletter_subscriber a, newsletter_subscriber_details b WHERE a.id_subscriber = b.id_subscriber AND b.email LIKE ? AND id_newsletter = ? AND a.confirmed = 1";
58 private static final String SQL_QUERY_UPDATE = "UPDATE newsletter_description SET name = ?, description = ?, date_last_send = ?, html = ?, id_newsletter_template = ?, workgroup_key = ? , unsubscribe = ? ,sender_mail = ? ,sender_name = ? , test_recipients = ?, test_subject = ?, nb_sections = ? WHERE id_newsletter = ? ";
59 private static final String SQL_QUERY_INSERT = "INSERT INTO newsletter_description ( id_newsletter , name, description, date_last_send, html, id_newsletter_template, workgroup_key, unsubscribe, sender_mail, sender_name, test_recipients , test_subject, nb_sections ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?, ? )";
60 private static final String SQL_QUERY_INSERT_SUBSCRIBER = "INSERT INTO newsletter_subscriber ( id_newsletter , id_subscriber, date_subscription, confirmed ) VALUES ( ?, ?, ?, ? )";
61 private static final String SQL_QUERY_VALIDATE_SUBSCRIBER = "UPDATE newsletter_subscriber SET confirmed = 1 WHERE id_newsletter = ? AND id_subscriber = ?";
62 private static final String SQL_QUERY_DELETE = "DELETE FROM newsletter_description WHERE id_newsletter = ? ";
63 private static final String SQL_QUERY_DELETE_FROM_SUBSCRIBER = "DELETE FROM newsletter_subscriber WHERE id_newsletter = ? and id_subscriber = ? ";
64 private static final String SQL_QUERY_DELETE_OLD_FROM_SUBSCRIBER = "DELETE FROM newsletter_subscriber WHERE date_subscription < ? and confirmed = ? ";
65 private static final String SQL_QUERY_CHECK_PRIMARY_KEY = "SELECT id_newsletter FROM newsletter_description WHERE id_newsletter = ?";
66 private static final String SQL_QUERY_CHECK_LINKED_PORTLET = "SELECT id_newsletter FROM newsletter_portlet_subscribe WHERE id_newsletter = ?";
67 private static final String SQL_QUERY_NEW_PRIMARY_KEY = "SELECT max(id_newsletter) FROM newsletter_description ";
68 private static final String SQL_QUERY_CHECK_IS_REGISTERED = "SELECT id_newsletter FROM newsletter_subscriber WHERE id_newsletter = ? AND id_subscriber = ? ";
69 private static final String SQL_QUERY_CHECK_IS_TEMPLATE_USED = "SELECT id_newsletter FROM newsletter_description WHERE id_newsletter_template = ? ";
70 private static final String SQL_QUERY_DELETE_UNUSED_EMAIL = "DELETE FROM newsletter_subscriber_details WHERE id_subscriber NOT IN (SELECT id_subscriber FROM newsletter_subscriber)";
71
72 private static final String CONSTANT_PERCENT = "%";
73
74
75
76
77
78
79
80 @Override
81 public void insert( NewsLetter newsLetter, Plugin plugin )
82 {
83 int nNewPrimaryKey = newPrimaryKey( plugin );
84 newsLetter.setId( nNewPrimaryKey );
85
86 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
87
88 int nIndex = 1;
89 daoUtil.setInt( nIndex++, newsLetter.getId( ) );
90 daoUtil.setString( nIndex++, newsLetter.getName( ) );
91 daoUtil.setString( nIndex++, newsLetter.getDescription( ) );
92 daoUtil.setTimestamp( nIndex++, newsLetter.getDateLastSending( ) );
93 daoUtil.setString( nIndex++, newsLetter.getHtml( ) );
94 daoUtil.setInt( nIndex++, newsLetter.getNewsLetterTemplateId( ) );
95 daoUtil.setString( nIndex++, newsLetter.getWorkgroup( ) );
96 daoUtil.setString( nIndex++, newsLetter.getUnsubscribe( ) );
97 daoUtil.setString( nIndex++, newsLetter.getNewsletterSenderMail( ) );
98 daoUtil.setString( nIndex++, newsLetter.getNewsletterSenderName( ) );
99 daoUtil.setString( nIndex++, newsLetter.getTestRecipients( ) );
100 daoUtil.setString( nIndex++, newsLetter.getTestSubject( ) );
101 daoUtil.setInt( nIndex, newsLetter.getNbSections( ) );
102
103 daoUtil.executeUpdate( );
104 daoUtil.free( );
105 }
106
107
108
109
110 @Override
111 public void delete( int nNewsLetterId, Plugin plugin )
112 {
113 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
114 daoUtil.setInt( 1, nNewsLetterId );
115
116 daoUtil.executeUpdate( );
117 daoUtil.free( );
118 }
119
120
121
122
123 @Override
124 public NewsLetter load( int nNewsLetterId, Plugin plugin )
125 {
126 NewsLettertter/business/NewsLetter.html#NewsLetter">NewsLetter newsLetter = new NewsLetter( );
127
128 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
129 daoUtil.setInt( 1, nNewsLetterId );
130 daoUtil.executeQuery( );
131
132 if ( daoUtil.next( ) )
133 {
134 int nIndex = 1;
135 newsLetter.setId( nNewsLetterId );
136 newsLetter.setName( daoUtil.getString( nIndex++ ) );
137 newsLetter.setDescription( daoUtil.getString( nIndex++ ) );
138 newsLetter.setDateLastSending( daoUtil.getTimestamp( nIndex++ ) );
139 newsLetter.setHtml( daoUtil.getString( nIndex++ ) );
140 newsLetter.setNewsLetterTemplateId( daoUtil.getInt( nIndex++ ) );
141 newsLetter.setWorkgroup( daoUtil.getString( nIndex++ ) );
142 newsLetter.setUnsubscribe( daoUtil.getString( nIndex++ ) );
143 newsLetter.setNewsletterSenderMail( daoUtil.getString( nIndex++ ) );
144 newsLetter.setNewsletterSenderName( daoUtil.getString( nIndex++ ) );
145 newsLetter.setTestRecipients( daoUtil.getString( nIndex++ ) );
146 newsLetter.setTestSubject( daoUtil.getString( nIndex++ ) );
147 newsLetter.setNbSections( daoUtil.getInt( nIndex ) );
148 }
149
150 daoUtil.free( );
151
152 return newsLetter;
153 }
154
155
156
157
158 @Override
159 public void store( NewsLetter newsLetter, Plugin plugin )
160 {
161 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
162 int nIndex = 1;
163 daoUtil.setString( nIndex++, newsLetter.getName( ) );
164 daoUtil.setString( nIndex++, newsLetter.getDescription( ) );
165 daoUtil.setTimestamp( nIndex++, newsLetter.getDateLastSending( ) );
166 daoUtil.setString( nIndex++, newsLetter.getHtml( ) );
167 daoUtil.setInt( nIndex++, newsLetter.getNewsLetterTemplateId( ) );
168 daoUtil.setString( nIndex++, newsLetter.getWorkgroup( ) );
169 daoUtil.setString( nIndex++, newsLetter.getUnsubscribe( ) );
170 daoUtil.setString( nIndex++, newsLetter.getNewsletterSenderMail( ) );
171 daoUtil.setString( nIndex++, newsLetter.getNewsletterSenderName( ) );
172 daoUtil.setString( nIndex++, newsLetter.getTestRecipients( ) );
173 daoUtil.setString( nIndex++, newsLetter.getTestSubject( ) );
174 daoUtil.setInt( nIndex++, newsLetter.getNbSections( ) );
175 daoUtil.setInt( nIndex, newsLetter.getId( ) );
176
177 daoUtil.executeUpdate( );
178 daoUtil.free( );
179 }
180
181
182
183
184 @Override
185 public boolean checkPrimaryKey( int nKey, Plugin plugin )
186 {
187 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_PRIMARY_KEY, plugin );
188 daoUtil.setInt( 1, nKey );
189 daoUtil.executeQuery( );
190
191 if ( !daoUtil.next( ) )
192 {
193 daoUtil.free( );
194
195 return false;
196 }
197
198 daoUtil.free( );
199
200 return true;
201 }
202
203
204
205
206 @Override
207 public boolean checkLinkedPortlet( int nIdNewsletter )
208 {
209 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_LINKED_PORTLET, PluginService.getPlugin( NewsletterPlugin.PLUGIN_NAME ) );
210 daoUtil.setInt( 1, nIdNewsletter );
211 daoUtil.executeQuery( );
212
213 if ( !daoUtil.next( ) )
214 {
215 daoUtil.free( );
216
217 return false;
218 }
219
220 daoUtil.free( );
221
222 return true;
223 }
224
225
226
227
228 @Override
229 public int newPrimaryKey( Plugin plugin )
230 {
231 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY, plugin );
232 int nKey;
233
234 daoUtil.executeQuery( );
235
236 if ( !daoUtil.next( ) )
237 {
238
239 nKey = 1;
240 }
241
242 nKey = daoUtil.getInt( 1 ) + 1;
243
244 daoUtil.free( );
245
246 return nKey;
247 }
248
249
250
251
252 @Override
253 public Collection<NewsLetter> selectAll( Plugin plugin )
254 {
255 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin );
256 daoUtil.executeQuery( );
257
258 ArrayList<NewsLetter> list = new ArrayList<NewsLetter>( );
259
260 while ( daoUtil.next( ) )
261 {
262 int nIndex = 1;
263 NewsLettertter/business/NewsLetter.html#NewsLetter">NewsLetter newsLetter = new NewsLetter( );
264 newsLetter.setId( daoUtil.getInt( nIndex++ ) );
265 newsLetter.setName( daoUtil.getString( nIndex++ ) );
266 newsLetter.setDescription( daoUtil.getString( nIndex++ ) );
267 newsLetter.setDateLastSending( daoUtil.getTimestamp( nIndex++ ) );
268 newsLetter.setHtml( daoUtil.getString( nIndex++ ) );
269 newsLetter.setNewsLetterTemplateId( daoUtil.getInt( nIndex++ ) );
270 newsLetter.setWorkgroup( daoUtil.getString( nIndex++ ) );
271 newsLetter.setTestRecipients( daoUtil.getString( nIndex++ ) );
272 newsLetter.setNewsletterSenderMail( daoUtil.getString( nIndex++ ) );
273 newsLetter.setNewsletterSenderName( daoUtil.getString( nIndex++ ) );
274 newsLetter.setTestSubject( daoUtil.getString( nIndex++ ) );
275 newsLetter.setNbSections( daoUtil.getInt( nIndex ) );
276 list.add( newsLetter );
277 }
278
279 daoUtil.free( );
280
281 return list;
282 }
283
284
285
286
287 @Override
288 public ReferenceList selectAllId( Plugin plugin )
289 {
290 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_ID, plugin );
291 daoUtil.executeQuery( );
292
293 ReferenceList list = new ReferenceList( );
294
295 while ( daoUtil.next( ) )
296 {
297 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
298 }
299
300 daoUtil.free( );
301
302 return list;
303 }
304
305
306
307
308 @Override
309 public Collection<NewsLetter> selectAllByTemplateId( int nTemplateId, Plugin plugin )
310 {
311 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_BY_ID_TEMPLATE, plugin );
312 daoUtil.setInt( 1, nTemplateId );
313 daoUtil.executeQuery( );
314
315 ArrayList<NewsLetter> list = new ArrayList<NewsLetter>( );
316
317 while ( daoUtil.next( ) )
318 {
319 int nIndex = 1;
320 NewsLettertter/business/NewsLetter.html#NewsLetter">NewsLetter newsLetter = new NewsLetter( );
321 newsLetter.setId( daoUtil.getInt( nIndex++ ) );
322 newsLetter.setName( daoUtil.getString( nIndex++ ) );
323 newsLetter.setDescription( daoUtil.getString( nIndex++ ) );
324 newsLetter.setDateLastSending( daoUtil.getTimestamp( nIndex++ ) );
325 newsLetter.setHtml( daoUtil.getString( nIndex++ ) );
326 newsLetter.setNewsLetterTemplateId( daoUtil.getInt( nIndex++ ) );
327 newsLetter.setWorkgroup( daoUtil.getString( nIndex++ ) );
328 newsLetter.setTestRecipients( daoUtil.getString( nIndex++ ) );
329 newsLetter.setNewsletterSenderMail( daoUtil.getString( nIndex++ ) );
330 newsLetter.setNewsletterSenderName( daoUtil.getString( nIndex++ ) );
331 newsLetter.setTestSubject( daoUtil.getString( nIndex++ ) );
332 newsLetter.setNbSections( daoUtil.getInt( nIndex ) );
333 list.add( newsLetter );
334 }
335
336 daoUtil.free( );
337
338 return list;
339 }
340
341
342
343
344 @Override
345 public void insertSubscriber( int nNewsLetterId, int nSubscriberId, Timestamp tToday, Plugin plugin )
346 {
347 insertSubscriber( nNewsLetterId, nSubscriberId, true, tToday, plugin );
348 }
349
350
351
352
353 @Override
354 public void insertSubscriber( int nNewsLetterId, int nSubscriberId, boolean bValidate, Timestamp tToday, Plugin plugin )
355 {
356
357 if ( isRegistered( nNewsLetterId, nSubscriberId, plugin ) )
358 {
359 return;
360 }
361
362 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_SUBSCRIBER, plugin );
363
364 daoUtil.setInt( 1, nNewsLetterId );
365 daoUtil.setInt( 2, nSubscriberId );
366 daoUtil.setTimestamp( 3, tToday );
367 daoUtil.setBoolean( 4, bValidate );
368
369 daoUtil.executeUpdate( );
370 daoUtil.free( );
371 }
372
373
374
375
376 @Override
377 public void deleteSubscriber( int nNewsLetterId, int nSubscriberId, Plugin plugin )
378 {
379 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_SUBSCRIBER, plugin );
380
381 daoUtil.setInt( 1, nNewsLetterId );
382 daoUtil.setInt( 2, nSubscriberId );
383
384 daoUtil.executeUpdate( );
385 daoUtil.free( );
386 }
387
388
389
390
391 @Override
392 public void deleteOldUnconfirmed( Timestamp confirmLimitDate, Plugin plugin )
393 {
394 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_OLD_FROM_SUBSCRIBER, plugin );
395 daoUtil.setTimestamp( 1, confirmLimitDate );
396 daoUtil.setBoolean( 2, false );
397
398 daoUtil.executeUpdate( );
399 daoUtil.free( );
400
401 daoUtil = new DAOUtil( SQL_QUERY_DELETE_UNUSED_EMAIL, plugin );
402 daoUtil.executeUpdate( );
403 daoUtil.free( );
404 }
405
406
407
408
409 @Override
410 public boolean isRegistered( int nNewsLetterId, int nSubscriberId, Plugin plugin )
411 {
412 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_IS_REGISTERED, plugin );
413
414 daoUtil.setInt( 1, nNewsLetterId );
415 daoUtil.setInt( 2, nSubscriberId );
416 daoUtil.executeQuery( );
417
418 if ( !daoUtil.next( ) )
419 {
420 daoUtil.free( );
421
422 return false;
423 }
424
425 daoUtil.free( );
426
427 return true;
428 }
429
430
431
432
433 @Override
434 public boolean isTemplateUsed( int nTemplateId, Plugin plugin )
435 {
436 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_IS_TEMPLATE_USED, plugin );
437
438 daoUtil.setInt( 1, nTemplateId );
439 daoUtil.executeQuery( );
440
441 if ( !daoUtil.next( ) )
442 {
443 daoUtil.free( );
444
445 return false;
446 }
447
448 daoUtil.free( );
449
450 return true;
451 }
452
453
454
455
456 @Override
457 public int selectNbrSubscribers( int nNewsLetterId, String strSearchString, Plugin plugin )
458 {
459 int nCount;
460
461 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NBR_SUBSCRIBERS, plugin );
462
463 daoUtil.setString( 1, CONSTANT_PERCENT + strSearchString + CONSTANT_PERCENT );
464 daoUtil.setInt( 2, nNewsLetterId );
465
466 daoUtil.executeQuery( );
467
468 if ( !daoUtil.next( ) )
469 {
470
471 nCount = 0;
472 }
473 else
474 {
475 nCount = daoUtil.getInt( 1 );
476 }
477
478 daoUtil.free( );
479
480 return nCount;
481 }
482
483
484
485
486 @Override
487 public int selectNbrActiveSubscribers( int nNewsLetterId, String strSearchString, Plugin plugin )
488 {
489 int nCount;
490
491 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NBR_ACTIVE_SUBSCRIBERS, plugin );
492
493 daoUtil.setString( 1, CONSTANT_PERCENT + strSearchString + CONSTANT_PERCENT );
494 daoUtil.setInt( 2, nNewsLetterId );
495
496 daoUtil.executeQuery( );
497
498 if ( !daoUtil.next( ) )
499 {
500
501 nCount = 0;
502 }
503 else
504 {
505 nCount = daoUtil.getInt( 1 );
506 }
507
508 daoUtil.free( );
509
510 return nCount;
511 }
512
513
514
515
516 @Override
517 public void validateSubscriber( int nNewsLetterId, int nSubscriberId, Plugin plugin )
518 {
519 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_VALIDATE_SUBSCRIBER, plugin );
520
521 daoUtil.setInt( 1, nNewsLetterId );
522 daoUtil.setInt( 2, nSubscriberId );
523
524 daoUtil.executeUpdate( );
525 daoUtil.free( );
526 }
527 }