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.form.business;
35
36 import fr.paris.lutece.plugins.form.utils.FormUtils;
37 import fr.paris.lutece.plugins.genericattributes.business.Response;
38 import fr.paris.lutece.plugins.genericattributes.business.ResponseFilter;
39 import fr.paris.lutece.plugins.genericattributes.util.GenericAttributesUtils;
40 import fr.paris.lutece.portal.service.plugin.Plugin;
41 import fr.paris.lutece.util.sql.DAOUtil;
42
43 import java.sql.Timestamp;
44
45 import java.util.ArrayList;
46 import java.util.List;
47
48
49
50
51 public final class FormSubmitDAO implements IFormSubmitDAO
52 {
53
54 private static final String SQL_QUERY_NEW_PK = "SELECT MAX( id_form_submit ) FROM form_submit";
55 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = "SELECT id_form_submit,date_response,ip,id_form " + "FROM form_submit WHERE id_form_submit=? ";
56 private static final String SQL_QUERY_INSERT = "INSERT INTO form_submit ( "
57 + "id_form_submit,date_response,day_date_response,week_date_response,month_date_response,year_date_response,ip,id_form) VALUES(?,?,?,?,?,?,?,?)";
58 private static final String SQL_QUERY_DELETE = "DELETE FROM form_submit WHERE id_form_submit = ? ";
59 private static final String SQL_QUERY_UPDATE = "UPDATE form_submit SET " + "id_form_submit=?,date_response=?,ip=?,id_form=? WHERE id_form_submit=?";
60 private static final String SQL_QUERY_SELECT_FORM_RESPONSE_BY_FILTER = "SELECT id_form_submit,date_response,ip,id_form " + "FROM form_submit ";
61 private static final String SQL_QUERY_SELECT_COUNT_BY_FILTER = "SELECT COUNT(id_form_submit) " + "FROM form_submit ";
62 private static final String SQL_QUERY_SELECT_STATISTIC_FORM_SUBMIT = "SELECT COUNT(*),date_response " + "FROM form_submit ";
63 private static final String SQL_FILTER_ID_FORM = " id_form = ? ";
64 private static final String SQL_FILTER_DATE_FIRST_SUBMIT = " date_response >= ? ";
65 private static final String SQL_FILTER_DATE_LAST_SUBMIT = " date_response <= ? ";
66 private static final String SQL_GROUP_BY_DAY = " GROUP BY day_date_response,month_date_response,year_date_response ";
67 private static final String SQL_GROUP_BY_WEEK = " GROUP BY week_date_response,year_date_response ";
68 private static final String SQL_GROUP_BY_MONTH = " GROUP BY month_date_response,year_date_response ";
69 private static final String SQL_ORDER_BY_DATE_RESPONSE_ASC = " ORDER BY date_response ASC ";
70 private static final String SQL_QUERY_ANONYMIZE_RESPONSES = " UPDATE genatt_response fr SET response_value = ?, status = ? WHERE status < ? AND ( SELECT date_response FROM form_submit fs WHERE fs.id_form_submit = fr.id_form_submit) < ? AND id_entry IN ( ";
71 private static final String SQL_QUERY_FIND_FORM_SUBMIT_FROM_ID_RESPONSE = "SELECT fs.id_form_submit,fs.date_response,fs.ip,fs.id_form "
72 + "FROM form_submit fs INNER JOIN form_response_submit frs ON fs.id_form_submit = frs.id_form_submit WHERE frs.id_response = ?";
73 private static final String SQL_QUERY_FIND_ID_RESPONSE_FROM_FORM_SUBMIT = "SELECT id_response FROM form_response_submit WHERE id_form_submit = ?";
74 private static final String SQL_QUERY_ASSOCIATE_RESPONSE_WITH_FORM_SUBMIT = "INSERT INTO form_response_submit (id_response,id_form_submit) VALUES (?,?)";
75 private static final String SQL_QUERY_REMOVE_RESPONSE_FORM_SUBMIT_ASSOCIATION = "DELETE FROM form_response_submit (id_response,id_form_submit) WHERE id_response = ? ";
76 private static final String CONSTANT_COMMA = ",";
77 private static final String CONSTANT_QUESTION_MARK = "?";
78 private static final String CONSTANT_CLOSE_PARENTHESIS = ")";
79
80
81
82
83
84
85
86
87 private int newPrimaryKey( Plugin plugin )
88 {
89 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
90 daoUtil.executeQuery( );
91
92 int nKey;
93
94 if ( !daoUtil.next( ) )
95 {
96
97 nKey = 1;
98 }
99
100 nKey = daoUtil.getInt( 1 ) + 1;
101 daoUtil.free( );
102
103 return nKey;
104 }
105
106
107
108
109 @Override
110 public synchronized int insert( FormSubmit formSubmit, Plugin plugin )
111 {
112 formSubmit.setIdFormSubmit( newPrimaryKey( plugin ) );
113
114 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
115 daoUtil.setInt( 1, formSubmit.getIdFormSubmit( ) );
116 daoUtil.setTimestamp( 2, formSubmit.getDateResponse( ) );
117 daoUtil.setInt( 3, FormUtils.getDay( formSubmit.getDateResponse( ) ) );
118 daoUtil.setInt( 4, FormUtils.getWeek( formSubmit.getDateResponse( ) ) );
119 daoUtil.setInt( 5, FormUtils.getMonth( formSubmit.getDateResponse( ) ) );
120 daoUtil.setInt( 6, FormUtils.getYear( formSubmit.getDateResponse( ) ) );
121 daoUtil.setString( 7, formSubmit.getIp( ) );
122 daoUtil.setInt( 8, formSubmit.getForm( ).getIdForm( ) );
123 daoUtil.executeUpdate( );
124 daoUtil.free( );
125
126 return formSubmit.getIdFormSubmit( );
127 }
128
129
130
131
132 @Override
133 public FormSubmit load( int nIdFormSubmit, Plugin plugin )
134 {
135 FormSubmit formSubmit = null;
136 Form form;
137 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
138 daoUtil.setInt( 1, nIdFormSubmit );
139 daoUtil.executeQuery( );
140
141 if ( daoUtil.next( ) )
142 {
143 formSubmit = new FormSubmit( );
144 formSubmit.setIdFormSubmit( daoUtil.getInt( 1 ) );
145 formSubmit.setDateResponse( daoUtil.getTimestamp( 2 ) );
146 formSubmit.setIp( daoUtil.getString( 3 ) );
147 form = new Form( );
148 form.setIdForm( daoUtil.getInt( 4 ) );
149 formSubmit.setForm( form );
150 }
151
152 daoUtil.free( );
153
154 return formSubmit;
155 }
156
157
158
159
160 @Override
161 public void delete( int nIdFormSubmit, Plugin plugin )
162 {
163 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
164 daoUtil.setInt( 1, nIdFormSubmit );
165 daoUtil.executeUpdate( );
166 daoUtil.free( );
167 }
168
169
170
171
172 @Override
173 public void store( FormSubmit formSubmit, Plugin plugin )
174 {
175 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
176 daoUtil.setInt( 1, formSubmit.getIdFormSubmit( ) );
177 daoUtil.setTimestamp( 2, formSubmit.getDateResponse( ) );
178 daoUtil.setString( 3, formSubmit.getIp( ) );
179 daoUtil.setInt( 4, formSubmit.getForm( ).getIdForm( ) );
180 daoUtil.setInt( 5, formSubmit.getIdFormSubmit( ) );
181 daoUtil.executeUpdate( );
182 daoUtil.free( );
183 }
184
185
186
187
188 @Override
189 public List<FormSubmit> selectListByFilter( ResponseFilter filter, Plugin plugin )
190 {
191 List<FormSubmit> formResponseList = new ArrayList<FormSubmit>( );
192 FormSubmit formSubmit;
193 Form form;
194 List<String> listStrFilter = new ArrayList<String>( );
195
196 if ( filter.containsIdResource( ) )
197 {
198 listStrFilter.add( SQL_FILTER_ID_FORM );
199 }
200
201 if ( filter.containsDateFirst( ) )
202 {
203 listStrFilter.add( SQL_FILTER_DATE_FIRST_SUBMIT );
204 }
205
206 if ( filter.containsDateLast( ) )
207 {
208 listStrFilter.add( SQL_FILTER_DATE_LAST_SUBMIT );
209 }
210
211 String strSQL = FormUtils.buildRequestWithFilter( SQL_QUERY_SELECT_FORM_RESPONSE_BY_FILTER, listStrFilter, null, SQL_ORDER_BY_DATE_RESPONSE_ASC );
212 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
213 int nIndex = 1;
214
215 if ( filter.containsIdResource( ) )
216 {
217 daoUtil.setInt( nIndex, filter.getIdResource( ) );
218 nIndex++;
219 }
220
221 if ( filter.containsDateFirst( ) )
222 {
223 daoUtil.setTimestamp( nIndex, filter.getDateFirst( ) );
224 nIndex++;
225 }
226
227 if ( filter.containsDateLast( ) )
228 {
229 daoUtil.setTimestamp( nIndex, filter.getDateLast( ) );
230 nIndex++;
231 }
232
233 daoUtil.executeQuery( );
234
235 while ( daoUtil.next( ) )
236 {
237 formSubmit = new FormSubmit( );
238 formSubmit.setIdFormSubmit( daoUtil.getInt( 1 ) );
239 formSubmit.setDateResponse( daoUtil.getTimestamp( 2 ) );
240 formSubmit.setIp( daoUtil.getString( 3 ) );
241 form = new Form( );
242 form.setIdForm( daoUtil.getInt( 4 ) );
243 formSubmit.setForm( form );
244 formResponseList.add( formSubmit );
245 }
246
247 daoUtil.free( );
248
249 return formResponseList;
250 }
251
252
253
254
255 @Override
256 public int selectCountByFilter( ResponseFilter filter, Plugin plugin )
257 {
258 int nIdCount = 0;
259 List<String> listStrFilter = new ArrayList<String>( );
260
261 if ( filter.containsIdResource( ) )
262 {
263 listStrFilter.add( SQL_FILTER_ID_FORM );
264 }
265
266 if ( filter.containsDateFirst( ) )
267 {
268 listStrFilter.add( SQL_FILTER_DATE_FIRST_SUBMIT );
269 }
270
271 if ( filter.containsDateLast( ) )
272 {
273 listStrFilter.add( SQL_FILTER_DATE_LAST_SUBMIT );
274 }
275
276 String strSQL = FormUtils.buildRequestWithFilter( SQL_QUERY_SELECT_COUNT_BY_FILTER, listStrFilter, null, null );
277 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
278 int nIndex = 1;
279
280 if ( filter.containsIdResource( ) )
281 {
282 daoUtil.setInt( nIndex, filter.getIdResource( ) );
283 nIndex++;
284 }
285
286 if ( filter.containsDateFirst( ) )
287 {
288 daoUtil.setTimestamp( nIndex, filter.getDateFirst( ) );
289 nIndex++;
290 }
291
292 if ( filter.containsDateLast( ) )
293 {
294 daoUtil.setTimestamp( nIndex, filter.getDateLast( ) );
295 nIndex++;
296 }
297
298 daoUtil.executeQuery( );
299
300 if ( daoUtil.next( ) )
301 {
302 nIdCount = daoUtil.getInt( 1 );
303 }
304
305 daoUtil.free( );
306
307 return nIdCount;
308 }
309
310
311
312
313 @Override
314 public List<StatisticFormSubmit> selectStatisticFormSubmit( ResponseFilter filter, Plugin plugin )
315 {
316 List<StatisticFormSubmit> statList = new ArrayList<StatisticFormSubmit>( );
317 StatisticFormSubmit statistic;
318 List<String> listStrFilter = new ArrayList<String>( );
319 List<String> listStrGroupBy = new ArrayList<String>( );
320
321 if ( filter.containsIdResource( ) )
322 {
323 listStrFilter.add( SQL_FILTER_ID_FORM );
324 }
325
326 if ( filter.containsDateFirst( ) )
327 {
328 listStrFilter.add( SQL_FILTER_DATE_FIRST_SUBMIT );
329 }
330
331 if ( filter.containsDateLast( ) )
332 {
333 listStrFilter.add( SQL_FILTER_DATE_LAST_SUBMIT );
334 }
335
336 if ( filter.isGroupbyDay( ) )
337 {
338 listStrGroupBy.add( SQL_GROUP_BY_DAY );
339 }
340
341 if ( filter.isGroupbyWeek( ) )
342 {
343 listStrGroupBy.add( SQL_GROUP_BY_WEEK );
344 }
345
346 if ( filter.isGroupbyMonth( ) )
347 {
348 listStrGroupBy.add( SQL_GROUP_BY_MONTH );
349 }
350
351 String strSQL = FormUtils
352 .buildRequestWithFilter( SQL_QUERY_SELECT_STATISTIC_FORM_SUBMIT, listStrFilter, listStrGroupBy, SQL_ORDER_BY_DATE_RESPONSE_ASC );
353 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
354 int nIndex = 1;
355
356 if ( filter.containsIdResource( ) )
357 {
358 daoUtil.setInt( nIndex, filter.getIdResource( ) );
359 nIndex++;
360 }
361
362 if ( filter.containsDateFirst( ) )
363 {
364 daoUtil.setTimestamp( nIndex, filter.getDateFirst( ) );
365 nIndex++;
366 }
367
368 if ( filter.containsDateLast( ) )
369 {
370 daoUtil.setTimestamp( nIndex, filter.getDateLast( ) );
371 nIndex++;
372 }
373
374 daoUtil.executeQuery( );
375
376 while ( daoUtil.next( ) )
377 {
378 statistic = new StatisticFormSubmit( );
379 statistic.setNumberResponse( daoUtil.getInt( 1 ) );
380 statistic.setStatisticDate( daoUtil.getTimestamp( 2 ) );
381 statList.add( statistic );
382 }
383
384 daoUtil.free( );
385
386 return statList;
387 }
388
389
390
391
392 @Override
393 public void anonymizeEntries( List<Integer> listIdEntries, Timestamp dateCleanTo, Plugin plugin )
394 {
395 if ( ( listIdEntries == null ) || ( listIdEntries.size( ) <= 0 ) )
396 {
397 return;
398 }
399
400 StringBuilder sbSql = new StringBuilder( SQL_QUERY_ANONYMIZE_RESPONSES );
401 sbSql.append( CONSTANT_QUESTION_MARK );
402
403 for ( int i = 1; i < listIdEntries.size( ); i++ )
404 {
405 sbSql.append( CONSTANT_COMMA ).append( CONSTANT_QUESTION_MARK );
406 }
407
408 sbSql.append( CONSTANT_CLOSE_PARENTHESIS );
409
410 DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin );
411 int nIndex = 1;
412 daoUtil.setString( nIndex++, GenericAttributesUtils.CONSTANT_RESPONSE_VALUE_ANONYMIZED );
413
414 daoUtil.setInt( nIndex++, Response.CONSTANT_STATUS_ANONYMIZED );
415 daoUtil.setInt( nIndex++, Response.CONSTANT_STATUS_ANONYMIZED );
416 daoUtil.setTimestamp( nIndex++, dateCleanTo );
417
418 for ( Integer nIdEntry : listIdEntries )
419 {
420 daoUtil.setInt( nIndex++, nIdEntry );
421 }
422
423 daoUtil.executeUpdate( );
424
425 daoUtil.free( );
426 }
427
428
429
430
431 @Override
432 public FormSubmit findFormSubmitFromResponseId( int nIdResponse, Plugin plugin )
433 {
434 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FORM_SUBMIT_FROM_ID_RESPONSE, plugin );
435 daoUtil.setInt( 1, nIdResponse );
436
437 FormSubmit formSubmit = null;
438 daoUtil.executeQuery( );
439
440 if ( daoUtil.next( ) )
441 {
442 formSubmit = new FormSubmit( );
443 formSubmit.setIdFormSubmit( daoUtil.getInt( 1 ) );
444 formSubmit.setDateResponse( daoUtil.getTimestamp( 2 ) );
445 formSubmit.setIp( daoUtil.getString( 3 ) );
446
447 Form form = new Form( );
448 form.setIdForm( daoUtil.getInt( 4 ) );
449 formSubmit.setForm( form );
450 }
451
452 daoUtil.free( );
453
454 return formSubmit;
455 }
456
457
458
459
460 @Override
461 public List<Integer> getResponseListFromIdFormSubmit( int nIdFormSubmit, Plugin plugin )
462 {
463 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_ID_RESPONSE_FROM_FORM_SUBMIT, plugin );
464 daoUtil.setInt( 1, nIdFormSubmit );
465
466 List<Integer> listIdResponse = new ArrayList<Integer>( );
467 daoUtil.executeQuery( );
468
469 while ( daoUtil.next( ) )
470 {
471 listIdResponse.add( daoUtil.getInt( 1 ) );
472 }
473
474 daoUtil.free( );
475
476 return listIdResponse;
477 }
478
479
480
481
482 @Override
483 public void associateResponseWithFormSubmit( int nIdResponse, int nIdFormSubmit, Plugin plugin )
484 {
485 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ASSOCIATE_RESPONSE_WITH_FORM_SUBMIT, plugin );
486
487 daoUtil.setInt( 1, nIdResponse );
488 daoUtil.setInt( 2, nIdFormSubmit );
489 daoUtil.executeUpdate( );
490
491 daoUtil.free( );
492 }
493
494
495
496
497 @Override
498 public void removeResponseFormSubmitAssociation( int nIdResponse, Plugin plugin )
499 {
500 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_RESPONSE_FORM_SUBMIT_ASSOCIATION, plugin );
501
502 daoUtil.setInt( 1, nIdResponse );
503 daoUtil.executeUpdate( );
504
505 daoUtil.free( );
506 }
507 }