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