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.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.List;
41
42
43
44
45
46
47 public class NoticeGroupDAO implements INoticeGroupDAO
48 {
49 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_group_notice ) FROM formengine_group_notice";
50 private static final String SQL_QUERY_SELECT = "SELECT id_group_notice, title, id_form, is_enabled, workgroup_key " +
51 "FROM formengine_group_notice ";
52 private static final String SQL_QUERY_SELECT_BY_PRIMARY_KEY = SQL_QUERY_SELECT + " WHERE id_group_notice = ?";
53 private static final String SQL_QUERY_INSERT = "INSERT INTO formengine_group_notice(id_group_notice, title, id_form, is_enabled, workgroup_key)" +
54 "VALUES (?,?,?,?,?)";
55 private static final String SQL_QUERY_UPDATE = "UPDATE formengine_group_notice " +
56 "SET title = ?, id_form = ?, is_enabled = ?, workgroup_key = ? WHERE id_group_notice = ? ";
57 private static final String SQL_QUERY_DELETE = "DELETE FROM formengine_group_notice WHERE id_group_notice = ? ";
58 private static final String SQL_FILTER_ID_FORM = " id_form = ? ";
59 private static final String SQL_FILTER_ENABLED = " is_enabled = ? ";
60 private static final String SQL_FILTER_WORKGROUP = " workgroup_key = ? ";
61 private static final String CONSTANT_WHERE = " WHERE ";
62 private static final String CONSTANT_AND = " AND ";
63 private static final String SQL_QUERY_SELECT_BY_ID_FORM = SQL_QUERY_SELECT + CONSTANT_WHERE + SQL_FILTER_ID_FORM;
64 private static final String SQL_QUERY_SELECT_IS_ENABLED = "SELECT is_enabled " + "FROM formengine_group_notice " +
65 CONSTANT_WHERE + SQL_FILTER_ENABLED;
66
67
68
69
70
71
72 private int newPrimaryKey( Plugin plugin )
73 {
74 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
75
76 daoUtil.executeQuery( );
77
78 int nKey = 1;
79
80 if ( daoUtil.next( ) )
81 {
82 nKey = daoUtil.getInt( 1 ) + 1;
83 }
84
85 daoUtil.free( );
86
87 return nKey;
88 }
89
90
91
92
93
94 public void delete( int nIdNoticeGroup, Plugin plugin )
95 {
96 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
97 daoUtil.setInt( 1, nIdNoticeGroup );
98
99 daoUtil.executeUpdate( );
100
101 daoUtil.free( );
102 }
103
104
105
106
107
108 public List<NoticeGroup> getNoticeGroupByIdForm( String strIdForm, Plugin plugin )
109 {
110 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM, plugin );
111
112 daoUtil.setString( 1, strIdForm );
113
114 daoUtil.executeQuery( );
115
116 List<NoticeGroup> listNoticeGroup = new ArrayList<NoticeGroup>( );
117
118 while ( daoUtil.next( ) )
119 {
120 NoticeGroup noticeGroup = new NoticeGroup( );
121 fillNoticeGroup( noticeGroup, daoUtil );
122
123 listNoticeGroup.add( noticeGroup );
124 }
125
126 daoUtil.free( );
127
128 return listNoticeGroup;
129 }
130
131
132
133
134
135 public int insert( NoticeGroup noticeGroup, Plugin plugin )
136 {
137 int nKey = newPrimaryKey( plugin );
138 noticeGroup.setId( nKey );
139
140 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
141
142 daoUtil.setInt( 1, nKey );
143 setInsertOrUpdateValues( 2, noticeGroup, daoUtil );
144
145 daoUtil.executeUpdate( );
146
147 daoUtil.free( );
148
149 return nKey;
150 }
151
152
153
154
155
156 public boolean isEnable( NoticeGroup noticeGroup, Plugin plugin )
157 {
158 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_IS_ENABLED, plugin );
159
160 daoUtil.setInt( 1, noticeGroup.getId( ) );
161
162 daoUtil.executeQuery( );
163
164 boolean bEnabled = false;
165
166 if ( daoUtil.next( ) )
167 {
168 bEnabled = daoUtil.getBoolean( 1 );
169 }
170
171 daoUtil.free( );
172
173 return bEnabled;
174 }
175
176
177
178
179
180 public NoticeGroup load( int nKey, Plugin plugin )
181 {
182 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PRIMARY_KEY, plugin );
183
184 daoUtil.setInt( 1, nKey );
185
186 daoUtil.executeQuery( );
187
188 NoticeGroup noticeGroup = null;
189
190 if ( daoUtil.next( ) )
191 {
192 noticeGroup = new NoticeGroup( );
193 fillNoticeGroup( noticeGroup, daoUtil );
194 }
195
196 daoUtil.free( );
197
198 return noticeGroup;
199 }
200
201
202
203
204
205 public void store( NoticeGroup noticeGroup, Plugin plugin )
206 {
207 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
208
209 int nIndex = setInsertOrUpdateValues( 1, noticeGroup, daoUtil );
210
211 daoUtil.setInt( nIndex, noticeGroup.getId( ) );
212
213 daoUtil.executeUpdate( );
214
215 daoUtil.free( );
216 }
217
218
219
220
221
222
223 private void fillNoticeGroup( NoticeGroup noticeGroup, DAOUtil daoUtil )
224 {
225 int nIndex = 1;
226 noticeGroup.setId( daoUtil.getInt( nIndex++ ) );
227 noticeGroup.setTitle( daoUtil.getString( nIndex++ ) );
228 noticeGroup.setForm( daoUtil.getString( nIndex++ ) );
229 noticeGroup.setEnabled( daoUtil.getBoolean( nIndex++ ) );
230 noticeGroup.setWorkgroupKey( daoUtil.getString( nIndex++ ) );
231 }
232
233
234
235
236
237
238
239
240 private int setInsertOrUpdateValues( int nStartIndex, NoticeGroup noticeGroup, DAOUtil daoUtil )
241 {
242 int nIndex = nStartIndex;
243
244 daoUtil.setString( nIndex++, noticeGroup.getTitle( ) );
245 daoUtil.setString( nIndex++, noticeGroup.getForm( ) );
246 daoUtil.setBoolean( nIndex++, noticeGroup.isEnabled( ) );
247 daoUtil.setString( nIndex++, noticeGroup.getWorkgroupKey( ) );
248
249 return nIndex;
250 }
251
252
253
254
255
256 public List<NoticeGroup> selectNoticeGroupByFilter( NoticeGroupFilter filter, Plugin plugin )
257 {
258 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );
259 buildSQLFilter( sbSQL, filter );
260
261 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
262
263 addSQLFilterParameters( 1, daoUtil, filter );
264
265 daoUtil.executeQuery( );
266
267 List<NoticeGroup> listNoticeGroup = new ArrayList<NoticeGroup>( );
268
269 while ( daoUtil.next( ) )
270 {
271 NoticeGroup noticeGroup = new NoticeGroup( );
272 fillNoticeGroup( noticeGroup, daoUtil );
273
274 listNoticeGroup.add( noticeGroup );
275 }
276
277 daoUtil.free( );
278
279 return listNoticeGroup;
280 }
281
282
283
284
285
286
287
288
289 private int addSQLFilterParameters( int nStartIndex, DAOUtil daoUtil, NoticeGroupFilter filter )
290 {
291 int nIndex = nStartIndex;
292
293 if ( filter.containsIsEnabled( ) )
294 {
295 daoUtil.setInt( nIndex++, filter.getIsEnabled( ) );
296 }
297
298 if ( filter.containsWorkgroupCriteria( ) )
299 {
300 daoUtil.setString( nIndex++, filter.getWorkgroup( ) );
301 }
302
303 if ( filter.containsFormCriteria( ) )
304 {
305 daoUtil.setString( nIndex++, filter.getForm( ) );
306 }
307
308 return nIndex;
309 }
310
311
312
313
314
315
316 private void buildSQLFilter( StringBuilder sbSQL, NoticeGroupFilter filter )
317 {
318 List<String> listFilters = new ArrayList<String>( );
319
320 if ( filter.containsIsEnabled( ) )
321 {
322 listFilters.add( SQL_FILTER_ENABLED );
323 }
324
325 if ( filter.containsWorkgroupCriteria( ) )
326 {
327 listFilters.add( SQL_FILTER_WORKGROUP );
328 }
329
330 if ( filter.containsFormCriteria( ) )
331 {
332 listFilters.add( SQL_FILTER_ID_FORM );
333 }
334
335 boolean bFirstFilter = true;
336
337 for ( String strFilter : listFilters )
338 {
339 if ( bFirstFilter )
340 {
341 sbSQL.append( CONSTANT_WHERE );
342 bFirstFilter = false;
343 }
344 else
345 {
346 sbSQL.append( CONSTANT_AND );
347 }
348
349 sbSQL.append( strFilter );
350 }
351 }
352 }