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.formengine.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.ReferenceList;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.List;
42
43
44
45
46
47 public final class NoticeDAO implements INoticeDAO
48 {
49
50 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_notice ) FROM formengine_notice";
51 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = "SELECT id_notice,title,message," +
52 "workgroup_key, date_debut, date_fin, id_diffusion, is_enabled, id_notification, notice_order, id_notice_group " +
53 " FROM formengine_notice WHERE id_notice = ?";
54 private static final String SQL_QUERY_INSERT = "INSERT INTO formengine_notice (id_notice,title,message," +
55 "workgroup_key, date_debut, date_fin, id_diffusion, is_enabled, id_notification, notice_order, id_notice_group) " +
56 "VALUES(?,?,?,?,?,?,?,?,?,?,?)";
57 private static final String SQL_QUERY_DELETE = "DELETE FROM formengine_notice WHERE id_notice = ? ";
58 private static final String SQL_QUERY_UPDATE = "UPDATE formengine_notice SET id_notice=?,title=?,message=?," +
59 "workgroup_key=?, date_debut=?, date_fin=?, id_diffusion=?, is_enabled=?, id_notification=?, notice_order = ?, id_notice_group = ? " +
60 "WHERE id_notice=?";
61 private static final String SQL_QUERY_SELECT_FORM_BY_FILTER = "SELECT id_notice,title,message," +
62 "workgroup_key, date_debut, date_fin, id_diffusion, is_enabled, id_notification, notice_order, id_notice_group " +
63 "FROM formengine_notice ";
64 private static final String SQL_FILTER_WORKGROUP = " workgroup_key = ? ";
65 private static final String SQL_FILTER_IS_ENABLED = " is_enabled = ? ";
66 private static final String SQL_FILTER_TITLE = " title = ? ";
67 private static final String SQL_FILTER_ID_NOTICE_GROUP = " id_notice_group = ? ";
68 private static final String CONSTANT_WHERE = " WHERE ";
69 private static final String CONSTANT_AND = " AND ";
70 private static final String SQL_ORDER_BY_NOTICE_ORDER = " ORDER BY notice_order";
71 private static final String SQL_QUERY_MAX_ORDER = "SELECT MAX(notice_order) FROM formengine_notice WHERE id_notice_group = ? ";
72
73
74
75
76
77
78
79 public int newPrimaryKey( Plugin plugin )
80 {
81 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
82 daoUtil.executeQuery( );
83
84 int nKey = 1;
85
86 if ( daoUtil.next( ) )
87 {
88 nKey = daoUtil.getInt( 1 ) + 1;
89 }
90
91 daoUtil.free( );
92
93 return nKey;
94 }
95
96
97
98
99
100
101
102 public int selectMaxOrderByNoticeGroup( int nIdNoticeGroup, Plugin plugin )
103 {
104 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER, plugin );
105
106 daoUtil.setInt( 1, nIdNoticeGroup );
107
108 int nMaxOrder = -1;
109
110 daoUtil.executeQuery( );
111
112 if ( daoUtil.next( ) )
113 {
114 nMaxOrder = daoUtil.getInt( 1 );
115 }
116
117 daoUtil.free( );
118
119 return nMaxOrder;
120 }
121
122
123
124
125
126
127
128
129 public synchronized int insert( Notice notice, Plugin plugin )
130 {
131 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
132
133 int nIndex = 2;
134 daoUtil.setString( nIndex++, notice.getTitle( ) );
135 daoUtil.setString( nIndex++, notice.getMessage( ) );
136 daoUtil.setString( nIndex++, notice.getWorkgroupKey( ) );
137 daoUtil.setTimestamp( nIndex++, notice.getDateDebut( ) );
138 daoUtil.setTimestamp( nIndex++, notice.getDateFin( ) );
139 daoUtil.setInt( nIndex++, notice.getIdDiffusion( ) );
140 daoUtil.setBoolean( nIndex++, notice.isEnabled( ) );
141 daoUtil.setInt( nIndex++, notice.getIdNotification( ) );
142 daoUtil.setInt( nIndex++, notice.getOrder( ) );
143 daoUtil.setInt( nIndex++, notice.getIdNoticeGroup( ) );
144
145 notice.setIdNotice( newPrimaryKey( plugin ) );
146 daoUtil.setInt( 1, notice.getIdNotice( ) );
147
148 daoUtil.executeUpdate( );
149 daoUtil.free( );
150
151 return notice.getIdNotice( );
152 }
153
154
155
156
157
158
159
160
161 public Notice load( int nId, Plugin plugin )
162 {
163 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
164 daoUtil.setInt( 1, nId );
165 daoUtil.executeQuery( );
166
167 Notice notice = null;
168
169 if ( daoUtil.next( ) )
170 {
171 int nIndex = 1;
172 notice = new Notice( );
173 notice.setIdNotice( daoUtil.getInt( nIndex++ ) );
174 notice.setTitle( daoUtil.getString( nIndex++ ) );
175 notice.setMessage( daoUtil.getString( nIndex++ ) );
176 notice.setWorkgroupKey( daoUtil.getString( nIndex++ ) );
177 notice.setDateDebut( daoUtil.getTimestamp( nIndex++ ) );
178 notice.setDateFin( daoUtil.getTimestamp( nIndex++ ) );
179 notice.setIdDiffusion( daoUtil.getInt( nIndex++ ) );
180 notice.setEnabled( daoUtil.getBoolean( nIndex++ ) );
181 notice.setIdNotification( daoUtil.getInt( nIndex++ ) );
182 notice.setOrder( daoUtil.getInt( nIndex++ ) );
183 notice.setIdNoticeGroup( daoUtil.getInt( nIndex++ ) );
184 }
185
186 daoUtil.free( );
187
188 return notice;
189 }
190
191
192
193
194
195
196
197 public void delete( int nIdNotice, Plugin plugin )
198 {
199 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
200 daoUtil.setInt( 1, nIdNotice );
201 daoUtil.executeUpdate( );
202 daoUtil.free( );
203 }
204
205
206
207
208
209
210
211 public void store( Notice notice, Plugin plugin )
212 {
213 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
214
215 int nIndex = 1;
216 daoUtil.setInt( nIndex++, notice.getIdNotice( ) );
217 daoUtil.setString( nIndex++, notice.getTitle( ) );
218 daoUtil.setString( nIndex++, notice.getMessage( ) );
219 daoUtil.setString( nIndex++, notice.getWorkgroupKey( ) );
220 daoUtil.setTimestamp( nIndex++, notice.getDateDebut( ) );
221 daoUtil.setTimestamp( nIndex++, notice.getDateFin( ) );
222 daoUtil.setInt( nIndex++, notice.getIdDiffusion( ) );
223 daoUtil.setBoolean( nIndex++, notice.isEnabled( ) );
224 daoUtil.setInt( nIndex++, notice.getIdNotification( ) );
225 daoUtil.setInt( nIndex++, notice.getOrder( ) );
226 daoUtil.setInt( nIndex++, notice.getIdNoticeGroup( ) );
227
228 daoUtil.setInt( nIndex++, notice.getIdNotice( ) );
229
230 daoUtil.executeUpdate( );
231 daoUtil.free( );
232 }
233
234
235
236
237
238
239
240 public List<Notice> selectNoticeList( NoticeFilter filter, Plugin plugin )
241 {
242 List<Notice> noticeList = new ArrayList<Notice>( );
243 Notice notice = null;
244 List<String> listStrFilter = new ArrayList<String>( );
245
246 if ( filter.containsWorkgroupCriteria( ) )
247 {
248 listStrFilter.add( SQL_FILTER_WORKGROUP );
249 }
250
251 if ( filter.containsIsDisabled( ) )
252 {
253 listStrFilter.add( SQL_FILTER_IS_ENABLED );
254 }
255
256 if ( filter.containsIdNoticeGroupCriteria( ) )
257 {
258 listStrFilter.add( SQL_FILTER_ID_NOTICE_GROUP );
259 }
260
261 String strSQL = buildRequestWithFilter( SQL_QUERY_SELECT_FORM_BY_FILTER, listStrFilter );
262
263 strSQL += SQL_ORDER_BY_NOTICE_ORDER;
264
265 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
266 int nIndex = 1;
267
268 if ( filter.containsWorkgroupCriteria( ) )
269 {
270 daoUtil.setString( nIndex, filter.getWorkgroup( ) );
271 nIndex++;
272 }
273
274 if ( filter.containsIsDisabled( ) )
275 {
276 daoUtil.setInt( nIndex, filter.getIsDisabled( ) );
277 nIndex++;
278 }
279
280 if ( filter.containsIdNoticeGroupCriteria( ) )
281 {
282 daoUtil.setInt( nIndex, filter.getIdNoticeGroup( ) );
283 nIndex++;
284 }
285
286 daoUtil.executeQuery( );
287
288 while ( daoUtil.next( ) )
289 {
290 int nIndexDAO = 1;
291 notice = new Notice( );
292 notice.setIdNotice( daoUtil.getInt( nIndexDAO++ ) );
293 notice.setTitle( daoUtil.getString( nIndexDAO++ ) );
294 notice.setMessage( daoUtil.getString( nIndexDAO++ ) );
295 notice.setWorkgroupKey( daoUtil.getString( nIndexDAO++ ) );
296 notice.setDateDebut( daoUtil.getTimestamp( nIndexDAO++ ) );
297 notice.setDateFin( daoUtil.getTimestamp( nIndexDAO++ ) );
298 notice.setIdDiffusion( daoUtil.getInt( nIndexDAO++ ) );
299 notice.setEnabled( daoUtil.getBoolean( nIndexDAO++ ) );
300 notice.setIdNotification( daoUtil.getInt( nIndexDAO++ ) );
301 notice.setOrder( daoUtil.getInt( nIndexDAO++ ) );
302 notice.setIdNoticeGroup( daoUtil.getInt( nIndexDAO++ ) );
303
304 noticeList.add( notice );
305 }
306
307 daoUtil.free( );
308
309 return noticeList;
310 }
311
312
313
314
315
316
317 public ReferenceList getEnableNoticeList( Plugin plugin )
318 {
319 ReferenceList listNotice = new ReferenceList( );
320 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FORM_BY_FILTER + SQL_ORDER_BY_NOTICE_ORDER, plugin );
321 daoUtil.executeQuery( );
322
323 Notice notice;
324
325 while ( daoUtil.next( ) )
326 {
327 notice = new Notice( );
328 notice.setIdNotice( daoUtil.getInt( 1 ) );
329 notice.setTitle( daoUtil.getString( 2 ) );
330 listNotice.addItem( notice.getIdNotice( ), notice.getTitle( ) );
331 }
332
333 daoUtil.free( );
334
335 return listNotice;
336 }
337
338
339
340
341
342
343
344 public ReferenceList getNoticeByTitle( String strTitle, Plugin plugin )
345 {
346 ReferenceList listNotice = new ReferenceList( );
347 String strSQL = SQL_QUERY_SELECT_FORM_BY_FILTER + CONSTANT_WHERE + SQL_FILTER_TITLE +
348 SQL_ORDER_BY_NOTICE_ORDER;
349 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
350 daoUtil.setString( 1, strTitle );
351 daoUtil.executeQuery( );
352
353 Notice notice;
354
355 while ( daoUtil.next( ) )
356 {
357 notice = new Notice( );
358 notice.setIdNotice( daoUtil.getInt( 1 ) );
359 notice.setTitle( daoUtil.getString( 2 ) );
360 listNotice.addItem( notice.getIdNotice( ), notice.getTitle( ) );
361 }
362
363 daoUtil.free( );
364
365 return listNotice;
366 }
367
368
369
370
371
372
373
374 public List<Notice> getNoticeByIdForm( String strIdForm, Plugin plugin )
375 {
376 List<Notice> listNotice = new ArrayList<Notice>( );
377 String strSQL = SQL_QUERY_SELECT_FORM_BY_FILTER + SQL_ORDER_BY_NOTICE_ORDER;
378 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
379 daoUtil.setString( 1, strIdForm );
380 daoUtil.executeQuery( );
381
382 Notice notice;
383
384 while ( daoUtil.next( ) )
385 {
386 int nIndex = 1;
387 notice = new Notice( );
388 notice.setIdNotice( daoUtil.getInt( nIndex++ ) );
389 notice.setTitle( daoUtil.getString( nIndex++ ) );
390 notice.setMessage( daoUtil.getString( nIndex++ ) );
391 notice.setWorkgroupKey( daoUtil.getString( nIndex++ ) );
392 notice.setDateDebut( daoUtil.getTimestamp( nIndex++ ) );
393 notice.setDateFin( daoUtil.getTimestamp( nIndex++ ) );
394 notice.setIdDiffusion( daoUtil.getInt( nIndex++ ) );
395 notice.setEnabled( daoUtil.getBoolean( nIndex++ ) );
396 notice.setIdNotification( daoUtil.getInt( nIndex++ ) );
397 notice.setOrder( daoUtil.getInt( nIndex++ ) );
398 notice.setIdNoticeGroup( daoUtil.getInt( nIndex++ ) );
399
400 listNotice.add( notice );
401 }
402
403 daoUtil.free( );
404
405 return listNotice;
406 }
407
408
409
410
411
412
413
414
415 public boolean isEnable( Notice notice, Plugin plugin )
416 {
417 int nIdNotice = notice.getIdNotice( );
418 boolean result = false;
419 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
420 daoUtil.setInt( 1, nIdNotice );
421 daoUtil.executeQuery( );
422
423 if ( daoUtil.next( ) )
424 {
425 result = daoUtil.getBoolean( 9 );
426 }
427
428 daoUtil.free( );
429
430 return result;
431 }
432
433
434
435
436
437
438
439 private String buildRequestWithFilter( String strSelect, List<String> listStrFilter )
440 {
441 StringBuffer strBuffer = new StringBuffer( );
442 strBuffer.append( strSelect );
443
444 int nCount = 0;
445
446 for ( String strFilter : listStrFilter )
447 {
448 if ( ++nCount == 1 )
449 {
450 strBuffer.append( CONSTANT_WHERE );
451 }
452
453 strBuffer.append( strFilter );
454
455 if ( nCount != listStrFilter.size( ) )
456 {
457 strBuffer.append( CONSTANT_AND );
458 }
459 }
460
461 return strBuffer.toString( );
462 }
463 }