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
40 import fr.paris.lutece.portal.service.plugin.Plugin;
41 import fr.paris.lutece.util.ReferenceList;
42 import fr.paris.lutece.util.sql.DAOUtil;
43
44
45
46
47 public final class FormDisplayDAO implements IFormDisplayDAO
48 {
49
50 private static final String SQL_QUERY_SELECTALL = "SELECT id_display, id_form, id_step, id_composite, id_parent, display_order, composite_type, display_depth FROM forms_display";
51
52 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECTALL + " WHERE id_display = ?";
53 private static final String SQL_QUERY_SELECT_BY_FORM = SQL_QUERY_SELECTALL + " WHERE id_form = ?";
54 private static final String SQL_QUERY_SELECT_BY_PARENT = SQL_QUERY_SELECTALL + " WHERE id_step = ? AND id_parent = ? ORDER BY display_order ASC";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO forms_display ( id_form, id_step, id_composite, id_parent, display_order, composite_type, display_depth ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM forms_display WHERE id_display = ? ";
57 private static final String SQL_QUERY_UPDATE = "UPDATE forms_display SET id_display = ?, id_form = ?, id_step = ?, id_composite = ?, id_parent = ?, display_order = ?, composite_type = ?, display_depth = ? WHERE id_display = ?";
58 private static final String SQL_QUERY_NEXT_POSITION_BY_PARENT = "SELECT MAX(display_order) from forms_display WHERE id_step = ? AND id_parent = ?";
59 private static final String SQL_QUERY_SELECTALL_GROUP_DISPLAY_BY_STEP = "SELECT d.id_display, g.title, d.id_form, d.id_step, d.id_composite, d.id_parent, d.display_order, d.composite_type, d.display_depth "
60 + "FROM forms_display d INNER JOIN forms_group g ON d.id_composite = g.id_group "
61 + "WHERE d.id_step = ? AND d.composite_type = ? order by d.id_parent, d.display_order";
62 private static final String SQL_QUERY_SELECT_BY_FROM_STEP_COMPOSITE = SQL_QUERY_SELECTALL + " WHERE id_form = ? AND id_step = ? AND id_composite = ?";
63 private static final String SQL_QUERY_SELECT_BY_PARENT_ORDER_BY_QUESTION_EXPORT_DISPLAY_ORDER = "SELECT fd.id_display, fd.id_form, fd.id_step, fd.id_composite, fd.id_parent, fd.display_order, fd.composite_type, fd.display_depth "
64 + "FROM forms_display fd LEFT JOIN forms_question fq ON fd.id_composite = fq.id_question "
65 + "WHERE fd.id_step = ? AND id_parent = ? ORDER BY fq.export_display_order ASC";
66
67
68
69
70 @Override
71 public void insert( FormDisplay formDisplay, Plugin plugin )
72 {
73 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
74 {
75 int nIndex = 1;
76 daoUtil.setInt( nIndex++, formDisplay.getFormId( ) );
77 daoUtil.setInt( nIndex++, formDisplay.getStepId( ) );
78 daoUtil.setInt( nIndex++, formDisplay.getCompositeId( ) );
79 daoUtil.setInt( nIndex++, formDisplay.getParentId( ) );
80 int nDisplayOrder = getNextPositionInGroup( formDisplay.getStepId( ), formDisplay.getParentId( ), plugin );
81 daoUtil.setInt( nIndex++, nDisplayOrder );
82 daoUtil.setString( nIndex++, formDisplay.getCompositeType( ) );
83 daoUtil.setInt( nIndex++, formDisplay.getDepth( ) );
84
85 daoUtil.executeUpdate( );
86 if ( daoUtil.nextGeneratedKey( ) )
87 {
88 formDisplay.setId( daoUtil.getGeneratedKeyInt( 1 ) );
89 }
90 }
91 }
92
93
94
95
96 @Override
97 public FormDisplay load( int nKey, Plugin plugin )
98 {
99 FormDisplay formDisplay = null;
100 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
101 {
102 daoUtil.setInt( 1, nKey );
103 daoUtil.executeQuery( );
104
105 if ( daoUtil.next( ) )
106 {
107 formDisplay = dataToObject( daoUtil );
108 }
109 }
110 return formDisplay;
111 }
112
113
114
115
116 @Override
117 public void delete( int nKey, Plugin plugin )
118 {
119 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
120 {
121 daoUtil.setInt( 1, nKey );
122 daoUtil.executeUpdate( );
123 }
124
125 }
126
127
128
129
130 @Override
131 public void store( FormDisplay formDisplay, Plugin plugin )
132 {
133 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
134 {
135 int nIndex = 1;
136
137 daoUtil.setInt( nIndex++, formDisplay.getId( ) );
138 daoUtil.setInt( nIndex++, formDisplay.getFormId( ) );
139 daoUtil.setInt( nIndex++, formDisplay.getStepId( ) );
140 daoUtil.setInt( nIndex++, formDisplay.getCompositeId( ) );
141 daoUtil.setInt( nIndex++, formDisplay.getParentId( ) );
142 daoUtil.setInt( nIndex++, formDisplay.getDisplayOrder( ) );
143 daoUtil.setString( nIndex++, formDisplay.getCompositeType( ) );
144 daoUtil.setInt( nIndex++, formDisplay.getDepth( ) );
145 daoUtil.setInt( nIndex, formDisplay.getId( ) );
146
147 daoUtil.executeUpdate( );
148 }
149 }
150
151
152
153
154 @Override
155 public List<FormDisplay> selectFormDisplayList( Plugin plugin )
156 {
157 List<FormDisplay> formDisplayList = new ArrayList<>( );
158
159 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
160 {
161 daoUtil.executeQuery( );
162
163 while ( daoUtil.next( ) )
164 {
165 formDisplayList.add( dataToObject( daoUtil ) );
166 }
167 }
168
169 return formDisplayList;
170 }
171
172
173
174
175 @Override
176 public List<FormDisplay> selectFormDisplayListByParent( int nIdStep, int nIdParent, Plugin plugin )
177 {
178 List<FormDisplay> formDisplayList = new ArrayList<>( );
179
180 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PARENT, plugin ) )
181 {
182 daoUtil.setInt( 1, nIdStep );
183 daoUtil.setInt( 2, nIdParent );
184 daoUtil.executeQuery( );
185
186 while ( daoUtil.next( ) )
187 {
188 formDisplayList.add( dataToObject( daoUtil ) );
189 }
190 }
191
192 return formDisplayList;
193 }
194
195 @Override
196 public List<FormDisplay> selectFormDisplayListByParentOrderByQuestionExportDisplayOrder( int nIdStep, int nIdParent, Plugin plugin )
197 {
198 List<FormDisplay> formDisplayList = new ArrayList<>( );
199 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PARENT_ORDER_BY_QUESTION_EXPORT_DISPLAY_ORDER, plugin ) )
200 {
201 daoUtil.setInt( 1, nIdStep );
202 daoUtil.setInt( 2, nIdParent );
203 daoUtil.executeQuery( );
204
205 while ( daoUtil.next( ) )
206 {
207 formDisplayList.add( dataToObject( daoUtil ) );
208 }
209 }
210 return formDisplayList;
211 }
212
213
214
215
216
217
218
219
220
221
222
223
224 private int getNextPositionInGroup( int nIdStep, int nIdParent, Plugin plugin )
225 {
226 int nNextPosition = 0;
227 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEXT_POSITION_BY_PARENT, plugin ) )
228 {
229 daoUtil.setInt( 1, nIdStep );
230 daoUtil.setInt( 2, nIdParent );
231 daoUtil.executeQuery( );
232
233 if ( daoUtil.next( ) )
234 {
235 nNextPosition = daoUtil.getInt( 1 ) + 1;
236 }
237 }
238 return nNextPosition;
239 }
240
241 @Override
242 public ReferenceList selectGroupDisplayReferenceListByStep( int nIdStep, Plugin plugin )
243 {
244 ReferenceList groupList = new ReferenceList( );
245 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_GROUP_DISPLAY_BY_STEP, plugin ) )
246 {
247 daoUtil.setInt( 1, nIdStep );
248 daoUtil.setString( 2, CompositeDisplayType.GROUP.getLabel( ) );
249 daoUtil.executeQuery( );
250
251 while ( daoUtil.next( ) )
252 {
253 groupList.addItem( daoUtil.getInt( "id_display" ), daoUtil.getString( "title" ) );
254 }
255 }
256 return groupList;
257 }
258
259
260
261
262 @Override
263 public FormDisplay selectFormdisplayByFormStepAndComposite( int nIdForm, int nIdStep, int nIdComposite, Plugin plugin )
264 {
265 FormDisplay formDisplay = null;
266
267 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_FROM_STEP_COMPOSITE, plugin ) )
268 {
269 daoUtil.setInt( 1, nIdForm );
270 daoUtil.setInt( 2, nIdStep );
271 daoUtil.setInt( 3, nIdComposite );
272 daoUtil.executeQuery( );
273
274 if ( daoUtil.next( ) )
275 {
276 formDisplay = dataToObject( daoUtil );
277 }
278 }
279 return formDisplay;
280 }
281
282 @Override
283 public List<FormDisplay> selectFormDisplayListByForm( int nIdForm, Plugin plugin )
284 {
285 List<FormDisplay> list = new ArrayList<>( );
286 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_FORM, plugin ) )
287 {
288 daoUtil.setInt( 1, nIdForm );
289 daoUtil.executeQuery( );
290
291 while ( daoUtil.next( ) )
292 {
293 list.add( dataToObject( daoUtil ) );
294 }
295 }
296 return list;
297 }
298
299
300
301
302
303
304
305 private FormDisplay dataToObject( DAOUtil daoUtil )
306 {
307 FormDisplaysiness/FormDisplay.html#FormDisplay">FormDisplay formDisplay = new FormDisplay( );
308
309 formDisplay.setId( daoUtil.getInt( "id_display" ) );
310 formDisplay.setFormId( daoUtil.getInt( "id_form" ) );
311 formDisplay.setStepId( daoUtil.getInt( "id_step" ) );
312 formDisplay.setCompositeId( daoUtil.getInt( "id_composite" ) );
313 formDisplay.setParentId( daoUtil.getInt( "id_parent" ) );
314 formDisplay.setDisplayOrder( daoUtil.getInt( "display_order" ) );
315 formDisplay.setCompositeType( daoUtil.getString( "composite_type" ) );
316 formDisplay.setDepth( daoUtil.getInt( "display_depth" ) );
317
318 return formDisplay;
319 }
320 }