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.genericattributes.business;
35
36 import java.sql.Statement;
37 import java.util.ArrayList;
38 import java.util.List;
39 import java.util.stream.Collectors;
40
41 import fr.paris.lutece.portal.business.file.File;
42 import fr.paris.lutece.portal.service.plugin.Plugin;
43 import fr.paris.lutece.util.sql.DAOUtil;
44
45
46
47
48 public final class ResponseDAO implements IResponseDAO
49 {
50
51 private static final String SQL_QUERY_SELECT_RESPONSE = "SELECT resp.id_response, resp.response_value, type.class_name, ent.id_type, ent.id_entry, ent.title, ent.code, "
52 + " resp.iteration_number, resp.id_field, resp.id_file, resp.status, resp.sort_order FROM genatt_response resp";
53 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = SQL_QUERY_SELECT_RESPONSE + ", genatt_entry ent, genatt_entry_type type "
54 + " WHERE resp.id_response = ? and resp.id_entry = ent.id_entry and ent.id_type = type.id_type ";
55 private static final String SQL_QUERY_SELECT_RESPONSE_BY_FILTER = SQL_QUERY_SELECT_RESPONSE + ", genatt_entry ent, genatt_entry_type type "
56 + " WHERE resp.id_entry = ent.id_entry and ent.id_type = type.id_type ";
57 private static final String SQL_QUERY_INSERT = "INSERT INTO genatt_response ( "
58 + " response_value, id_entry, iteration_number, id_field, id_file, status,sort_order ) VALUES ( ?,?,?,?,?,?,?)";
59 private static final String SQL_QUERY_UPDATE = "UPDATE genatt_response SET response_value = ?, id_entry = ?, iteration_number = ?, id_field = ?, id_file = ?, status = ?, sort_order = ? WHERE id_response = ?";
60 private static final String SQL_QUERY_DELETE = "DELETE FROM genatt_response WHERE id_response = ? ";
61 private static final String SQL_QUERY_SELECT_COUNT_RESPONSE_BY_ID_ENTRY = " SELECT field.title, COUNT( resp.id_response )"
62 + " FROM genatt_entry e LEFT JOIN genatt_field field ON ( e.id_entry = field.id_entry ) LEFT JOIN genatt_response resp on ( resp.id_field = field.id_field ) "
63 + " WHERE e.id_entry = ? GROUP BY field.id_field ORDER BY field.pos ";
64
65
66
67 private static final String SQL_QUERY_SELECT_MAX_NUMBER = " SELECT fr.response_value FROM genatt_response fr "
68 + " WHERE fr.id_entry = ? ORDER BY CAST(fr.response_value AS DECIMAL) DESC LIMIT 1 ";
69 private static final String SQL_FILTER_ID_RESOURCE = " AND ent.id_resource = ? ";
70 private static final String SQL_FILTER_ID_ENTRY = " AND resp.id_entry = ? ";
71 private static final String SQL_FILTER_ID_FIELD = " AND resp.id_field = ? ";
72 private static final String SQL_FILTER_CODE_ENTRY = " AND ent.code = ? ";
73 private static final String SQL_FILTER_RESPONSE_VALUE = " AND resp.response_value = ? ";
74 private static final String SQL_FILTER_ID_RESPONSE = " resp.id_response ";
75 private static final String SQL_FILTER_MULTI_ID_RESPONSE = "AND resp.id_response IN ";
76 private static final String SQL_ORDER_BY = " ORDER BY ";
77 private static final String SQL_ASC = " ASC ";
78 private static final String SQL_DESC = " DESC ";
79
80
81
82
83 @Override
84 public synchronized void insert( Response response, Plugin plugin )
85 {
86 int nIndex = 1;
87
88 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
89 {
90 daoUtil.setString( nIndex++, removeInvalidChars( response.getResponseValue( ) ) );
91 daoUtil.setInt( nIndex++, response.getEntry( ).getIdEntry( ) );
92 daoUtil.setInt( nIndex++, response.getIterationNumber( ) );
93
94 if ( response.getField( ) != null )
95 {
96 daoUtil.setInt( nIndex++, response.getField( ).getIdField( ) );
97 }
98 else
99 {
100 daoUtil.setIntNull( nIndex++ );
101 }
102
103 if ( response.getFile( ) != null )
104 {
105 daoUtil.setInt( nIndex++, response.getFile( ).getIdFile( ) );
106 }
107 else
108 {
109 daoUtil.setIntNull( nIndex++ );
110 }
111
112 daoUtil.setInt( nIndex++, Response.CONSTANT_STATUS_ACTIVE );
113 daoUtil.setInt( nIndex, response.getSortOrder( ) );
114
115 daoUtil.executeUpdate( );
116
117 if ( daoUtil.nextGeneratedKey( ) )
118 {
119 response.setIdResponse( daoUtil.getGeneratedKeyInt( 1 ) );
120 }
121
122 }
123 }
124
125
126
127
128 @Override
129 public Response load( int nIdResponse, Plugin plugin )
130 {
131 Response response = null;
132
133 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin ) )
134 {
135 daoUtil.setInt( 1, nIdResponse );
136 daoUtil.executeQuery( );
137
138 if ( daoUtil.next( ) )
139 {
140 response = getResponseFromDAOUtil( daoUtil );
141 }
142
143 }
144
145 return response;
146 }
147
148
149
150
151 @Override
152 public void delete( int nIdResponse, Plugin plugin )
153 {
154 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
155 {
156 daoUtil.setInt( 1, nIdResponse );
157 daoUtil.executeUpdate( );
158 }
159 }
160
161
162
163
164 @Override
165 public void store( Response response, Plugin plugin )
166 {
167 int nIndex = 1;
168 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
169 {
170 daoUtil.setString( nIndex++, response.getResponseValue( ) );
171 daoUtil.setInt( nIndex++, response.getEntry( ).getIdEntry( ) );
172 daoUtil.setInt( nIndex++, response.getIterationNumber( ) );
173
174 if ( response.getField( ) != null )
175 {
176 daoUtil.setInt( nIndex++, response.getField( ).getIdField( ) );
177 }
178 else
179 {
180 daoUtil.setIntNull( nIndex++ );
181 }
182
183 if ( response.getFile( ) != null )
184 {
185 daoUtil.setInt( nIndex++, response.getFile( ).getIdFile( ) );
186 }
187 else
188 {
189 daoUtil.setIntNull( nIndex++ );
190 }
191
192 daoUtil.setInt( nIndex++, response.getStatus( ) );
193 daoUtil.setInt( nIndex++, response.getSortOrder( ) );
194
195 daoUtil.setInt( nIndex, response.getIdResponse( ) );
196 daoUtil.executeUpdate( );
197 }
198 }
199
200
201
202
203 @Override
204 public List<Response> selectListByFilter( ResponseFilter filter, Plugin plugin )
205 {
206 List<Response> responseList = new ArrayList<>( );
207
208 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_RESPONSE_BY_FILTER );
209
210 if ( filter.containsIdResource( ) )
211 {
212 sbSQL.append( SQL_FILTER_ID_RESOURCE );
213 }
214
215 if ( filter.containsIdEntry( ) )
216 {
217 sbSQL.append( SQL_FILTER_ID_ENTRY );
218 }
219
220 if ( filter.containsIdField( ) )
221 {
222 sbSQL.append( SQL_FILTER_ID_FIELD );
223 }
224
225 if ( filter.containsCodeEntry( ) )
226 {
227 sbSQL.append( SQL_FILTER_CODE_ENTRY );
228 }
229
230 if ( filter.containsResponseValue( ) )
231 {
232 sbSQL.append( SQL_FILTER_RESPONSE_VALUE );
233 }
234
235 if ( filter.containsListIdResource( ) )
236 {
237 StringBuilder sb = new StringBuilder( SQL_FILTER_MULTI_ID_RESPONSE + " (" );
238 sb.append( filter.getListId( ).stream( ).map( String::valueOf ).collect( Collectors.joining( "," ) ) );
239 sb.append( ")" );
240 sbSQL.append( sb.toString( ) );
241 }
242
243 sbSQL.append( SQL_ORDER_BY );
244 sbSQL.append( ( filter.containsOrderBy( ) ) ? filter.getOrderBy( ) : SQL_FILTER_ID_RESPONSE );
245 sbSQL.append( ( filter.isOrderByAsc( ) ) ? SQL_ASC : SQL_DESC );
246
247 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin ) )
248 {
249 int nIndex = 1;
250
251 if ( filter.containsIdResource( ) )
252 {
253 daoUtil.setInt( nIndex++, filter.getIdResource( ) );
254 }
255
256 if ( filter.containsIdEntry( ) )
257 {
258 daoUtil.setInt( nIndex++, filter.getIdEntry( ) );
259 }
260
261 if ( filter.containsIdField( ) )
262 {
263 daoUtil.setInt( nIndex++, filter.getIdField( ) );
264 }
265
266 if ( filter.containsCodeEntry( ) )
267 {
268 daoUtil.setString( nIndex++, filter.getCodeEntry( ) );
269 }
270
271 if ( filter.containsResponseValue( ) )
272 {
273 daoUtil.setString( nIndex++, filter.getResponseValue( ) );
274 }
275
276 daoUtil.executeQuery( );
277
278 while ( daoUtil.next( ) )
279 {
280 responseList.add( getResponseFromDAOUtil( daoUtil ) );
281 }
282
283 }
284
285 return responseList;
286 }
287
288
289
290
291 @Override
292 public List<StatisticEntrySubmit> getStatisticByIdEntry( int nIdEntry, Plugin plugin )
293 {
294 List<StatisticEntrySubmit> listStatisticEntrySubmit = new ArrayList<>( );
295 StatisticEntrySubmit statisticEntrySubmit;
296
297 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COUNT_RESPONSE_BY_ID_ENTRY, plugin ) )
298 {
299 daoUtil.setInt( 1, nIdEntry );
300 daoUtil.executeQuery( );
301
302 while ( daoUtil.next( ) )
303 {
304 statisticEntrySubmit = new StatisticEntrySubmit( );
305 statisticEntrySubmit.setFieldLibelle( daoUtil.getString( 1 ) );
306 statisticEntrySubmit.setNumberResponse( daoUtil.getInt( 2 ) );
307 listStatisticEntrySubmit.add( statisticEntrySubmit );
308 }
309
310 }
311
312 return listStatisticEntrySubmit;
313 }
314
315
316
317
318 @Override
319 public int getMaxNumber( int nIdEntry, Plugin plugin )
320 {
321 int nKey = 1;
322 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MAX_NUMBER, plugin ) )
323 {
324 daoUtil.setInt( 1, nIdEntry );
325 daoUtil.executeQuery( );
326
327 if ( daoUtil.next( ) )
328 {
329 nKey = daoUtil.getInt( 1 ) + 1;
330 }
331
332 }
333 return nKey;
334 }
335
336
337
338
339
340
341
342
343 private Response getResponseFromDAOUtil( DAOUtil daoUtil )
344 {
345 int nIndex = 1;
346
347 Responsenericattributes/business/Response.html#Response">Response response = new Response( );
348 response.setIdResponse( daoUtil.getInt( nIndex++ ) );
349
350 response.setResponseValue( daoUtil.getString( nIndex++ ) );
351
352 EntryTypericattributes/business/EntryType.html#EntryType">EntryType entryType = new EntryType( );
353 entryType.setBeanName( daoUtil.getString( nIndex++ ) );
354 entryType.setIdType( daoUtil.getInt( nIndex++ ) );
355
356 Entryins/genericattributes/business/Entry.html#Entry">Entry entry = new Entry( );
357
358 entry.setEntryType( entryType );
359 entry.setIdEntry( daoUtil.getInt( nIndex++ ) );
360 entry.setTitle( daoUtil.getString( nIndex++ ) );
361 entry.setCode( daoUtil.getString( nIndex++ ) );
362 response.setEntry( entry );
363
364 response.setIterationNumber( daoUtil.getInt( nIndex++ ) );
365
366
367 if ( daoUtil.getObject( nIndex ) != null )
368 {
369 Fieldins/genericattributes/business/Field.html#Field">Field field = new Field( );
370 field.setIdField( daoUtil.getInt( nIndex ) );
371 response.setField( field );
372 }
373
374 nIndex++;
375
376
377 if ( daoUtil.getObject( nIndex ) != null )
378 {
379 File file = new File( );
380 file.setIdFile( daoUtil.getInt( nIndex ) );
381 response.setFile( file );
382 }
383
384 nIndex++;
385 response.setStatus( daoUtil.getInt( nIndex++ ) );
386 response.setSortOrder( daoUtil.getInt( nIndex ) );
387 return response;
388 }
389
390
391
392
393
394
395
396
397 private String removeInvalidChars( String s )
398 {
399 if ( s == null )
400 {
401 return null;
402 }
403
404 StringBuilder sb = new StringBuilder( );
405
406 for ( int i = 0; i < s.length( ); i++ )
407 {
408 Character c = s.charAt( i );
409
410 if ( Character.getType( c ) == Character.CONTROL )
411 {
412
413 if ( !( ( c == ' ' ) || ( c == '\n' ) || ( c == '\r' ) || ( c == '\t' ) ) )
414 {
415
416 continue;
417 }
418 }
419
420 sb.append( c );
421 }
422
423 return sb.toString( );
424 }
425 }