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.forms.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 org.apache.commons.collections.CollectionUtils;
42
43 import fr.paris.lutece.plugins.genericattributes.business.Entry;
44 import fr.paris.lutece.plugins.genericattributes.business.EntryHome;
45 import fr.paris.lutece.portal.service.plugin.Plugin;
46 import fr.paris.lutece.util.ReferenceList;
47 import fr.paris.lutece.util.sql.DAOUtil;
48
49
50
51
52 public final class QuestionDAO implements IQuestionDAO
53 {
54
55
56 private static final String SQL_QUERY_SELECT_ALL = "SELECT id_question, title, code, description, id_entry, id_step, is_visible_multiview_global, is_visible_multiview_form_selected, column_title, is_filterable_multiview_global, is_filterable_multiview_form_selected, multiview_column_order, export_display_order FROM forms_question";
57 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_ALL + " WHERE id_question = ?";
58 private static final String SQL_QUERY_SELECT_BY_CODE = SQL_QUERY_SELECT_ALL + " WHERE code = ?";
59 private static final String SQL_QUERY_SELECT_BY_CODE_AND_ENTRY_ID = SQL_QUERY_SELECT_BY_CODE + " AND id_entry = ? ";
60 private static final String SQL_QUERY_INSERT = "INSERT INTO forms_question ( title, code, description, id_entry, id_step, is_visible_multiview_global, is_visible_multiview_form_selected, column_title, is_filterable_multiview_global, is_filterable_multiview_form_selected,multiview_column_order, export_display_order ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
61 private static final String SQL_QUERY_DELETE = "DELETE FROM forms_question WHERE id_question = ? ";
62 private static final String SQL_QUERY_UPDATE = "UPDATE forms_question SET id_question = ?, title = ?, code = ?, description = ?, id_entry = ?, id_step = ?, is_visible_multiview_global = ?, is_visible_multiview_form_selected = ?, column_title = ?, is_filterable_multiview_global = ?, is_filterable_multiview_form_selected = ?, multiview_column_order = ?, export_display_order = ? WHERE id_question = ?";
63 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_question FROM forms_question";
64 private static final String SQL_QUERY_SELECT_BY_STEP = SQL_QUERY_SELECT_ALL + " WHERE id_step = ?";
65 private static final String SQL_QUERY_SELECTALL_BY_FORM = "SELECT fq.id_question, fq.title, fq.code, fq.description, fq.id_entry, fq.id_step, fq.is_visible_multiview_global, fq.is_visible_multiview_form_selected , fq.column_title, fq.is_filterable_multiview_global, fq.is_filterable_multiview_form_selected,fq.multiview_column_order,fq.export_display_order FROM forms_question fq INNER JOIN forms_step fs ON fq.id_step = fs.id_step WHERE fs.id_form = ?";
66 private static final String SQL_QUERY_SELECT_IN = SQL_QUERY_SELECT_ALL + " WHERE id_question IN ( ";
67 private static final String SQL_QUERY_SELECT_ALL_UNCOMPLETE = SQL_QUERY_SELECT_ALL + " where (is_visible_multiview_global = '1' or is_filterable_multiview_global = '1')";
68 private static final String SQL_QUERY_SELECT_BY_STEP_UNCOMPLETE = SQL_QUERY_SELECTALL_BY_FORM + " and (is_visible_multiview_global = '1' or is_visible_multiview_form_selected = '1' or is_filterable_multiview_global = '1' or is_filterable_multiview_form_selected = '1')";
69
70
71
72
73 @Override
74 public void insert( Question question, Plugin plugin )
75 {
76 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
77 {
78 int nIndex = 1;
79 daoUtil.setString( nIndex++, question.getTitle( ) );
80 daoUtil.setString( nIndex++, question.getCode( ) );
81 daoUtil.setString( nIndex++, question.getDescription( ) );
82 daoUtil.setInt( nIndex++, question.getIdEntry( ) );
83 daoUtil.setInt( nIndex++, question.getIdStep( ) );
84 daoUtil.setBoolean( nIndex++, question.isVisibleMultiviewGlobal( ) );
85 daoUtil.setBoolean( nIndex++, question.isVisibleMultiviewFormSelected( ) );
86 daoUtil.setString( nIndex++, question.getColumnTitle( ) );
87 daoUtil.setBoolean( nIndex++, question.isFiltrableMultiviewGlobal( ) );
88 daoUtil.setBoolean( nIndex++, question.isFiltrableMultiviewFormSelected( ) );
89 daoUtil.setInt( nIndex++, question.getMultiviewColumnOrder( ) );
90 daoUtil.setInt( nIndex++, question.getExportDisplayOrder() );
91
92 daoUtil.executeUpdate( );
93 if ( daoUtil.nextGeneratedKey( ) )
94 {
95 question.setId( daoUtil.getGeneratedKeyInt( 1 ) );
96 }
97 }
98 }
99
100
101
102
103 @Override
104 public Question load( int nKey, Plugin plugin )
105 {
106 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
107 {
108
109 daoUtil.setInt( 1, nKey );
110 daoUtil.executeQuery( );
111 Question question = null;
112
113 if ( daoUtil.next( ) )
114 {
115 question = dataToObject( daoUtil );
116 }
117
118 if ( question != null )
119 {
120
121 question.setEntry( getQuestionEntry( question.getIdEntry( ) ) );
122 }
123
124 return question;
125 }
126 }
127
128
129
130
131 @Override
132 public List<Question> loadByCode( String strCode, Plugin plugin )
133 {
134 List<Question> questionList = new ArrayList<>( );
135 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CODE, plugin ) )
136 {
137 daoUtil.setString( 1, strCode );
138 daoUtil.executeQuery( );
139
140 while ( daoUtil.next( ) )
141 {
142 Question question = dataToObject( daoUtil );
143 question.setEntry( getQuestionEntry( question.getIdEntry( ) ) );
144 questionList.add( question );
145 }
146 return questionList;
147 }
148 }
149
150 @Override
151 public Question loadByCodeAndEntry( String strCode, int idEntry, Plugin plugin )
152 {
153 Question question = null;
154 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CODE_AND_ENTRY_ID, plugin ) )
155 {
156 daoUtil.setString( 1, strCode );
157 daoUtil.setInt( 2, idEntry );
158 daoUtil.executeQuery( );
159
160 if ( daoUtil.next( ) )
161 {
162 question = dataToObject( daoUtil );
163 question.setEntry( getQuestionEntry( question.getIdEntry( ) ) );
164 }
165 }
166 return question;
167 }
168
169
170
171
172 @Override
173 public void delete( int nKey, Plugin plugin )
174 {
175 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
176 {
177 daoUtil.setInt( 1, nKey );
178 daoUtil.executeUpdate( );
179 }
180 }
181
182
183
184
185 @Override
186 public void store( Question question, Plugin plugin )
187 {
188 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
189 {
190 int nIndex = 1;
191
192 daoUtil.setInt( nIndex++, question.getId( ) );
193 daoUtil.setString( nIndex++, question.getTitle( ) );
194 daoUtil.setString( nIndex++, question.getCode( ) );
195 daoUtil.setString( nIndex++, question.getDescription( ) );
196 daoUtil.setInt( nIndex++, question.getIdEntry( ) );
197 daoUtil.setInt( nIndex++, question.getIdStep( ) );
198 daoUtil.setBoolean( nIndex++, question.isVisibleMultiviewGlobal( ) );
199 daoUtil.setBoolean( nIndex++, question.isVisibleMultiviewFormSelected( ) );
200 daoUtil.setString( nIndex++, question.getColumnTitle( ) );
201 daoUtil.setBoolean( nIndex++, question.isFiltrableMultiviewGlobal( ) );
202 daoUtil.setBoolean( nIndex++, question.isFiltrableMultiviewFormSelected( ) );
203 daoUtil.setInt( nIndex++, question.getMultiviewColumnOrder( ) );
204 daoUtil.setInt( nIndex++, question.getExportDisplayOrder() );
205
206 daoUtil.setInt( nIndex, question.getId( ) );
207
208 daoUtil.executeUpdate( );
209 }
210 }
211
212
213
214
215 @Override
216 public List<Question> selectQuestionsList( Plugin plugin )
217 {
218 List<Question> questionList = new ArrayList<>( );
219 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin ) )
220 {
221 daoUtil.executeQuery( );
222
223 while ( daoUtil.next( ) )
224 {
225 questionList.add( dataToObject( daoUtil ) );
226 }
227 }
228
229 for ( Question quest : questionList )
230 {
231 quest.setEntry( getQuestionEntry( quest.getIdEntry( ) ) );
232 }
233 return questionList;
234 }
235
236
237
238
239 @Override
240 public List<Question> selectQuestionsListByStep( int nIdStep, Plugin plugin )
241 {
242 List<Question> questionList = new ArrayList<>( );
243 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_STEP, plugin ) )
244 {
245 daoUtil.setInt( 1, nIdStep );
246 daoUtil.executeQuery( );
247
248 while ( daoUtil.next( ) )
249 {
250 questionList.add( dataToObject( daoUtil ) );
251 }
252 }
253 for ( Question quest : questionList )
254 {
255 quest.setEntry( getQuestionEntry( quest.getIdEntry( ) ) );
256 }
257 return questionList;
258 }
259
260
261
262
263 @Override
264 public List<Integer> selectIdQuestionsList( Plugin plugin )
265 {
266 List<Integer> questionList = new ArrayList<>( );
267 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
268 {
269 daoUtil.executeQuery( );
270
271 while ( daoUtil.next( ) )
272 {
273 questionList.add( daoUtil.getInt( 1 ) );
274 }
275 }
276 return questionList;
277 }
278
279 @Override
280 public List<Question> selectQuestionsListUncomplete( Plugin plugin )
281 {
282 List<Question> questionList = new ArrayList<>( );
283 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_UNCOMPLETE, plugin ) )
284 {
285 daoUtil.executeQuery( );
286
287 while ( daoUtil.next( ) )
288 {
289 questionList.add( dataToObjectWithoutStepEntry( daoUtil ) );
290 }
291 }
292 return questionList;
293 }
294
295
296
297
298 @Override
299 public ReferenceList selectQuestionsReferenceList( Plugin plugin )
300 {
301 ReferenceList questionList = new ReferenceList( );
302 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin ) )
303 {
304 daoUtil.executeQuery( );
305 while ( daoUtil.next( ) )
306 {
307 questionList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
308 }
309 }
310 return questionList;
311 }
312
313 @Override
314 public List<Question> selectQuestionsListByFormId( int nIdForm, Plugin plugin )
315 {
316 List<Question> questionList = new ArrayList<>( );
317
318 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_BY_FORM, plugin ) )
319 {
320 daoUtil.setInt( 1, nIdForm );
321 daoUtil.executeQuery( );
322
323 while ( daoUtil.next( ) )
324 {
325 questionList.add( dataToObject( daoUtil ) );
326 }
327 for ( Question quest : questionList )
328 {
329
330 quest.setEntry( getQuestionEntry( quest.getIdEntry( ) ) );
331
332 }
333 }
334 return questionList;
335
336 }
337
338 @Override
339 public List<Question> selectQuestionsListByFormIdUncomplete( int nIdForm, Plugin plugin )
340 {
341 List<Question> questionList = new ArrayList<>( );
342 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_STEP_UNCOMPLETE , plugin ) )
343 {
344 daoUtil.setInt( 1, nIdForm );
345 daoUtil.executeQuery( );
346
347 while ( daoUtil.next( ) )
348 {
349 questionList.add( dataToObjectWithoutStepEntry( daoUtil ) );
350 }
351 }
352 return questionList;
353 }
354
355
356
357
358 @Override
359 public ReferenceList selectQuestionsReferenceListByForm( int nIdForm, Plugin plugin )
360 {
361 ReferenceList questionList = new ReferenceList( );
362 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_BY_FORM, plugin ) )
363 {
364 daoUtil.setInt( 1, nIdForm );
365 daoUtil.executeQuery( );
366
367 while ( daoUtil.next( ) )
368 {
369 questionList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
370 }
371 }
372 return questionList;
373 }
374
375
376
377
378
379
380 private Entry getQuestionEntry( int nIdEntry )
381 {
382 return EntryHome.findByPrimaryKey( nIdEntry );
383 }
384
385
386
387
388
389
390 private Step getQuestionStep( int nIdStep )
391 {
392 return StepHome.findByPrimaryKey( nIdStep );
393 }
394
395
396
397
398
399
400
401 private Question dataToObject( DAOUtil daoUtil )
402 {
403 Questionrms/business/Question.html#Question">Question question = new Question( );
404
405 question.setId( daoUtil.getInt( "id_question" ) );
406 question.setTitle( daoUtil.getString( "title" ) );
407 question.setCode( daoUtil.getString( "code" ) );
408 question.setDescription( daoUtil.getString( "description" ) );
409 question.setIdEntry( daoUtil.getInt( "id_entry" ) );
410 question.setIdStep( daoUtil.getInt( "id_step" ) );
411 question.setVisibleMultiviewGlobal( daoUtil.getBoolean( "is_visible_multiview_global" ) );
412 question.setVisibleMultiviewFormSelected( daoUtil.getBoolean( "is_visible_multiview_form_selected" ) );
413 question.setColumnTitle( daoUtil.getString( "column_title" ) );
414 question.setFiltrableMultiviewGlobal( daoUtil.getBoolean( "is_filterable_multiview_global" ) );
415 question.setFiltrableMultiviewFormSelected( daoUtil.getBoolean( "is_filterable_multiview_form_selected" ) );
416 question.setMultiviewColumnOrder( daoUtil.getInt( "multiview_column_order" ) );
417 question.setExportDisplayOrder( daoUtil.getInt( "export_display_order" ) );
418 question.setStep( getQuestionStep( question.getIdStep( ) ) );
419
420 return question;
421 }
422
423 @Override
424 public List<Question> loadMultiple( List<Integer> keyList, Plugin plugin )
425 {
426 List<Question> list = new ArrayList<>( );
427 if ( CollectionUtils.isEmpty( keyList ) )
428 {
429 return list;
430 }
431
432 String query = SQL_QUERY_SELECT_IN + keyList.stream( ).distinct( ).map( i -> "?" ).collect( Collectors.joining( "," ) ) + " )";
433 try ( DAOUtil daoUtil = new DAOUtil( query, plugin ) )
434 {
435 for ( int i = 0; i < keyList.size( ); i++ )
436 {
437 daoUtil.setInt( i + 1, keyList.get( i ) );
438 }
439 daoUtil.executeQuery( );
440
441 while ( daoUtil.next( ) )
442 {
443 list.add( dataToObjectWithoutStepEntry( daoUtil ) );
444 }
445 }
446 return list;
447 }
448
449
450
451
452
453
454
455 private Question dataToObjectWithoutStepEntry( DAOUtil daoUtil )
456 {
457 Questionrms/business/Question.html#Question">Question question = new Question( );
458
459 question.setId( daoUtil.getInt( "id_question" ) );
460 question.setTitle( daoUtil.getString( "title" ) );
461 question.setCode( daoUtil.getString( "code" ) );
462 question.setDescription( daoUtil.getString( "description" ) );
463 question.setIdEntry( daoUtil.getInt( "id_entry" ) );
464 question.setIdStep( daoUtil.getInt( "id_step" ) );
465 question.setVisibleMultiviewGlobal( daoUtil.getBoolean( "is_visible_multiview_global" ) );
466 question.setVisibleMultiviewFormSelected( daoUtil.getBoolean( "is_visible_multiview_form_selected" ) );
467 question.setColumnTitle( daoUtil.getString( "column_title" ) );
468 question.setFiltrableMultiviewGlobal( daoUtil.getBoolean( "is_filterable_multiview_global" ) );
469 question.setFiltrableMultiviewFormSelected( daoUtil.getBoolean( "is_filterable_multiview_form_selected" ) );
470 question.setMultiviewColumnOrder( daoUtil.getInt( "multiview_column_order" ) );
471 question.setExportDisplayOrder( daoUtil.getInt( "export_display_order" ) );
472
473 return question;
474 }
475
476 }