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.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.List;
41
42
43
44
45 public final class SendingNewsLetterDAO implements ISendingNewsLetterDAO
46 {
47
48 private static final String SQL_QUERY_INSERT = "INSERT INTO newsletter_sending ( id_sending, id_newsletter , date_sending, subscriber_count, html, email_subject ) VALUES ( ?, ?, ?, ?, ?, ? )";
49 private static final String SQL_QUERY_DELETE = "DELETE FROM newsletter_sending WHERE id_sending = ? ";
50 private static final String SQL_QUERY_SELECT = "SELECT id_newsletter, date_sending, subscriber_count, html, email_subject FROM newsletter_sending WHERE id_sending = ? ";
51 private static final String SQL_QUERY_SELECT_LAST_SENDIND_BY_NEWSLETTER = " SELECT id_sending, id_newsletter, date_sending, subscriber_count, html, email_subject FROM newsletter_sending WHERE id_newsletter = ? ORDER BY date_sending DESC";
52 private static final String SQL_QUERY_SELECT_ALL_SENDINDS = "SELECT id_sending, id_newsletter, date_sending, subscriber_count, html, email_subject FROM newsletter_sending ORDER BY date_sending DESC";
53 private static final String SQL_QUERY_UPDATE = "UPDATE newsletter_sending SET date_sending = ? WHERE id_sending = ?";
54 private static final String SQL_QUERY_CHECK_PRIMARY_KEY = "SELECT id_sending FROM newsletter_sending WHERE id_sending = ?";
55 private static final String SQL_QUERY_NEW_PRIMARY_KEY = "SELECT max(id_sending) FROM newsletter_sending ";
56
57
58
59
60
61
62
63
64
65
66
67
68 public void insert( SendingNewsLetter sending, Plugin plugin )
69 {
70 int nNewPrimaryKey = newPrimaryKey( plugin );
71 sending.setId( nNewPrimaryKey );
72
73 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
74
75 daoUtil.setInt( 1, sending.getId( ) );
76 daoUtil.setInt( 2, sending.getNewsLetterId( ) );
77 daoUtil.setTimestamp( 3, sending.getDate( ) );
78 daoUtil.setInt( 4, sending.getCountSubscribers( ) );
79 daoUtil.setString( 5, sending.getHtml( ) );
80 daoUtil.setString( 6, sending.getEmailSubject( ) );
81 daoUtil.executeUpdate( );
82 daoUtil.free( );
83 }
84
85
86
87
88
89
90
91
92
93 public void delete( int nSendingId, Plugin plugin )
94 {
95 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
96 daoUtil.setInt( 1, nSendingId );
97 daoUtil.executeUpdate( );
98 daoUtil.free( );
99 }
100
101
102
103
104
105
106
107
108
109
110 public SendingNewsLetter load( int nSendingId, Plugin plugin )
111 {
112 SendingNewsLetter sending = null;
113
114 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
115
116 daoUtil.setInt( 1, nSendingId );
117 daoUtil.executeQuery( );
118
119 if ( daoUtil.next( ) )
120 {
121 sending = new SendingNewsLetter( );
122 sending.setId( nSendingId );
123 sending.setNewsLetterId( daoUtil.getInt( 1 ) );
124 sending.setDate( daoUtil.getTimestamp( 2 ) );
125 sending.setCountSubscribers( daoUtil.getInt( 3 ) );
126 sending.setHtml( daoUtil.getString( 4 ) );
127 sending.setEmailSubject( daoUtil.getString( 5 ) );
128 }
129
130 daoUtil.free( );
131
132 return sending;
133 }
134
135
136
137
138
139
140
141
142
143 public void store( SendingNewsLetter sending, Plugin plugin )
144 {
145 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
146 daoUtil.setTimestamp( 1, sending.getDate( ) );
147 daoUtil.setInt( 2, sending.getId( ) );
148 daoUtil.executeUpdate( );
149 daoUtil.free( );
150 }
151
152
153
154
155
156
157
158
159
160
161 boolean checkPrimaryKey( int nKey, Plugin plugin )
162 {
163 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_PRIMARY_KEY, plugin );
164 daoUtil.setInt( 1, nKey );
165 daoUtil.executeQuery( );
166
167 if ( !daoUtil.next( ) )
168 {
169 daoUtil.free( );
170
171 return false;
172 }
173
174 daoUtil.free( );
175
176 return true;
177 }
178
179
180
181
182
183
184
185
186 int newPrimaryKey( Plugin plugin )
187 {
188 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY, plugin );
189 int nKey;
190
191 daoUtil.executeQuery( );
192
193 if ( !daoUtil.next( ) )
194 {
195
196 nKey = 1;
197 }
198
199 nKey = daoUtil.getInt( 1 ) + 1;
200
201 daoUtil.free( );
202
203 return nKey;
204 }
205
206
207
208
209
210
211
212
213
214
215 public SendingNewsLetter selectLastSendingForNewsletterId( int newsletterId, Plugin plugin )
216 {
217 SendingNewsLetter sending = null;
218
219 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_SENDIND_BY_NEWSLETTER, plugin );
220 daoUtil.setInt( 1, newsletterId );
221 daoUtil.executeQuery( );
222
223 if ( daoUtil.next( ) )
224 {
225 sending = new SendingNewsLetter( );
226 sending.setId( daoUtil.getInt( 1 ) );
227 sending.setNewsLetterId( daoUtil.getInt( 2 ) );
228 sending.setDate( daoUtil.getTimestamp( 3 ) );
229 sending.setCountSubscribers( daoUtil.getInt( 4 ) );
230 sending.setHtml( daoUtil.getString( 5 ) );
231 sending.setEmailSubject( daoUtil.getString( 6 ) );
232 }
233
234 daoUtil.free( );
235
236 return sending;
237 }
238
239
240
241
242
243
244
245
246
247
248 public List<SendingNewsLetter> selectAllLastSendingForNewsletterId( int newsletterId, Plugin plugin )
249 {
250 SendingNewsLetter sending = null;
251 List<SendingNewsLetter> results = new ArrayList<SendingNewsLetter>( );
252 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_SENDIND_BY_NEWSLETTER, plugin );
253 daoUtil.setInt( 1, newsletterId );
254 daoUtil.executeQuery( );
255
256 while ( daoUtil.next( ) )
257 {
258 sending = new SendingNewsLetter( );
259 sending.setId( daoUtil.getInt( 1 ) );
260 sending.setNewsLetterId( daoUtil.getInt( 2 ) );
261 sending.setDate( daoUtil.getTimestamp( 3 ) );
262 sending.setCountSubscribers( daoUtil.getInt( 4 ) );
263 sending.setHtml( daoUtil.getString( 5 ) );
264 sending.setEmailSubject( daoUtil.getString( 6 ) );
265
266 results.add( sending );
267 }
268
269 daoUtil.free( );
270
271 return results;
272 }
273
274
275
276
277
278
279
280
281 public List<SendingNewsLetter> findAllSendings( Plugin plugin )
282 {
283 List<SendingNewsLetter> results = new ArrayList<SendingNewsLetter>( );
284
285 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_SENDINDS, plugin );
286 daoUtil.executeQuery( );
287
288 while ( daoUtil.next( ) )
289 {
290 SendingNewsLetter/business/SendingNewsLetter.html#SendingNewsLetter">SendingNewsLetter sending = new SendingNewsLetter( );
291 sending.setId( daoUtil.getInt( 1 ) );
292 sending.setNewsLetterId( daoUtil.getInt( 2 ) );
293 sending.setDate( daoUtil.getTimestamp( 3 ) );
294 sending.setCountSubscribers( daoUtil.getInt( 4 ) );
295 sending.setHtml( daoUtil.getString( 5 ) );
296 sending.setEmailSubject( daoUtil.getString( 6 ) );
297
298 results.add( sending );
299 }
300
301 daoUtil.free( );
302
303 return results;
304 }
305
306
307
308
309
310
311
312
313
314
315 public ArrayList<SendingNewsLetter> findSendingsByIds( ArrayList<Integer> listId, Plugin plugin )
316 {
317 ArrayList<SendingNewsLetter> results = new ArrayList<SendingNewsLetter>( );
318 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_SENDINDS, plugin );
319 daoUtil.executeQuery( );
320
321 while ( daoUtil.next( ) )
322 {
323 if ( listId.contains( Integer.valueOf( daoUtil.getInt( 1 ) ) ) )
324 {
325 SendingNewsLetter/business/SendingNewsLetter.html#SendingNewsLetter">SendingNewsLetter sending = new SendingNewsLetter( );
326 sending.setId( daoUtil.getInt( 1 ) );
327 sending.setNewsLetterId( daoUtil.getInt( 2 ) );
328 sending.setDate( daoUtil.getTimestamp( 3 ) );
329 sending.setCountSubscribers( daoUtil.getInt( 4 ) );
330 sending.setHtml( daoUtil.getString( 5 ) );
331 sending.setEmailSubject( daoUtil.getString( 6 ) );
332
333 results.add( sending );
334 }
335 }
336
337 daoUtil.free( );
338
339 return results;
340 }
341 }