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.directory.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.sql.Date;
40
41 import java.util.ArrayList;
42 import java.util.List;
43
44
45
46
47 public final class FieldDAO implements IFieldDAO
48 {
49
50 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_field ) FROM directory_field";
51 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = "SELECT id_field,id_entry,title,default_value,height,width,"
52 + "is_default_value,max_size_enter,field_position,value_type_date,role_key,workgroup_key"
53 + ",is_shown_in_result_list,is_shown_in_result_record,image_type " + " FROM directory_field WHERE id_field = ? ";
54 private static final String SQL_QUERY_FIND_BY_VALUE = "SELECT id_field,id_entry,title,default_value,height,width,"
55 + "is_default_value,max_size_enter,field_position,value_type_date,role_key,workgroup_key"
56 + ",is_shown_in_result_list,is_shown_in_result_record, image_type" + " FROM directory_field WHERE id_entry=? and default_value = ? ";
57 private static final String SQL_QUERY_INSERT = "INSERT INTO directory_field(id_field,id_entry,title,default_value,height,"
58 + "width,is_default_value,max_size_enter,field_position,value_type_date,role_key,workgroup_key,is_shown_in_result_list,"
59 + " is_shown_in_result_record, image_type)" + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
60 private static final String SQL_QUERY_DELETE = "DELETE FROM directory_field WHERE id_field = ? ";
61 private static final String SQL_QUERY_INSERT_VERIF_BY = "INSERT INTO directory_verify_by(id_field,id_expression) VALUES(?,?) ";
62 private static final String SQL_QUERY_DELETE_VERIF_BY = "DELETE FROM directory_verify_by WHERE id_field = ? and id_expression= ?";
63 private static final String SQL_QUERY_UPDATE = "UPDATE directory_field SET "
64 + "id_field=?,id_entry=?,title=?,default_value=?,height=?,width=?,is_default_value=?,max_size_enter=?, "
65 + "field_position=?,value_type_date=?,role_key=?,workgroup_key=?,is_shown_in_result_list=?,is_shown_in_result_record=?, image_type=?"
66 + " WHERE id_field = ?";
67 private static final String SQL_QUERY_SELECT_FIELD_BY_ID_ENTRY = "SELECT id_field,id_entry,title,default_value,height,width,is_default_value,"
68 + "max_size_enter,field_position,value_type_date,role_key,workgroup_key,is_shown_in_result_list"
69 + ",is_shown_in_result_record, image_type FROM directory_field WHERE id_entry = ? ORDER BY field_position";
70 private static final String SQL_QUERY_NEW_POSITION = "SELECT MAX(field_position)" + " FROM directory_field ";
71 private static final String SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_FIELD = "SELECT id_expression " + " FROM directory_verify_by where id_field=?";
72 private static final String SQL_QUERY_COUNT_FIELD_BY_ID_REGULAR_EXPRESSION = "SELECT COUNT(id_field) "
73 + " FROM directory_verify_by where id_expression = ?";
74
75
76
77
78
79
80
81
82 public int newPrimaryKey( Plugin plugin )
83 {
84 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
85 daoUtil.executeQuery( );
86
87 int nKey;
88
89 if ( !daoUtil.next( ) )
90 {
91
92 nKey = 1;
93 }
94
95 nKey = daoUtil.getInt( 1 ) + 1;
96 daoUtil.free( );
97
98 return nKey;
99 }
100
101
102
103
104
105
106
107
108 private int newPosition( Plugin plugin )
109 {
110 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_POSITION, plugin );
111 daoUtil.executeQuery( );
112
113 int nPos;
114
115 if ( !daoUtil.next( ) )
116 {
117
118 nPos = 1;
119 }
120
121 nPos = daoUtil.getInt( 1 ) + 1;
122 daoUtil.free( );
123
124 return nPos;
125 }
126
127
128
129
130 @Override
131 public synchronized int insert( Field field, Plugin plugin )
132 {
133 field.setIdField( newPrimaryKey( plugin ) );
134 field.setPosition( newPosition( plugin ) );
135
136 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
137 daoUtil.setInt( 1, field.getIdField( ) );
138 daoUtil.setInt( 2, field.getEntry( ).getIdEntry( ) );
139 daoUtil.setString( 3, field.getTitle( ) );
140 daoUtil.setString( 4, field.getValue( ) );
141 daoUtil.setInt( 5, field.getHeight( ) );
142 daoUtil.setInt( 6, field.getWidth( ) );
143 daoUtil.setBoolean( 7, field.isDefaultValue( ) );
144 daoUtil.setInt( 8, field.getMaxSizeEnter( ) );
145 daoUtil.setInt( 9, field.getPosition( ) );
146 daoUtil.setDate( 10, ( field.getValueTypeDate( ) == null ) ? null : new Date( field.getValueTypeDate( ).getTime( ) ) );
147
148 daoUtil.setString( 11, field.getRoleKey( ) );
149 daoUtil.setString( 12, field.getWorkgroup( ) );
150 daoUtil.setBoolean( 13, field.isShownInResultList( ) );
151 daoUtil.setBoolean( 14, field.isShownInResultRecord( ) );
152 daoUtil.setString( 15, field.getImageType( ) );
153
154 daoUtil.executeUpdate( );
155 daoUtil.free( );
156
157 return field.getIdField( );
158 }
159
160
161
162
163 @Override
164 public Field load( int nId, Plugin plugin )
165 {
166 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
167 daoUtil.setInt( 1, nId );
168 daoUtil.executeQuery( );
169
170 Field field = null;
171 IEntry entry = null;
172
173 if ( daoUtil.next( ) )
174 {
175 field = new Field( );
176 field.setIdField( daoUtil.getInt( 1 ) );
177
178 entry = new Entry( );
179 entry.setIdEntry( daoUtil.getInt( 2 ) );
180 field.setEntry( entry );
181 field.setTitle( daoUtil.getString( 3 ) );
182 field.setValue( daoUtil.getString( 4 ) );
183 field.setHeight( daoUtil.getInt( 5 ) );
184 field.setWidth( daoUtil.getInt( 6 ) );
185 field.setDefaultValue( daoUtil.getBoolean( 7 ) );
186 field.setMaxSizeEnter( daoUtil.getInt( 8 ) );
187 field.setPosition( daoUtil.getInt( 9 ) );
188 field.setValueTypeDate( daoUtil.getDate( 10 ) );
189 field.setRoleKey( daoUtil.getString( 11 ) );
190 field.setWorkgroup( daoUtil.getString( 12 ) );
191 field.setShownInResultList( daoUtil.getBoolean( 13 ) );
192 field.setShownInResultRecord( daoUtil.getBoolean( 14 ) );
193 field.setImageType( daoUtil.getString( 15 ) );
194 }
195
196 daoUtil.free( );
197
198 return field;
199 }
200
201
202
203
204 @Override
205 public Field loadByValue( int nIdEntry, String strValue, Plugin plugin )
206 {
207 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_VALUE, plugin );
208 daoUtil.setInt( 1, nIdEntry );
209 daoUtil.setString( 2, strValue );
210 daoUtil.executeQuery( );
211
212 Field field = null;
213 IEntry entry = null;
214
215 if ( daoUtil.next( ) )
216 {
217 field = new Field( );
218 field.setIdField( daoUtil.getInt( 1 ) );
219
220 entry = new Entry( );
221 entry.setIdEntry( daoUtil.getInt( 2 ) );
222 field.setEntry( entry );
223 field.setTitle( daoUtil.getString( 3 ) );
224 field.setValue( daoUtil.getString( 4 ) );
225 field.setHeight( daoUtil.getInt( 5 ) );
226 field.setWidth( daoUtil.getInt( 6 ) );
227 field.setDefaultValue( daoUtil.getBoolean( 7 ) );
228 field.setMaxSizeEnter( daoUtil.getInt( 8 ) );
229 field.setPosition( daoUtil.getInt( 9 ) );
230 field.setValueTypeDate( daoUtil.getDate( 10 ) );
231 field.setRoleKey( daoUtil.getString( 11 ) );
232 field.setWorkgroup( daoUtil.getString( 12 ) );
233 field.setShownInResultList( daoUtil.getBoolean( 13 ) );
234 field.setShownInResultRecord( daoUtil.getBoolean( 14 ) );
235 field.setImageType( daoUtil.getString( 15 ) );
236 }
237
238 daoUtil.free( );
239
240 return field;
241 }
242
243
244
245
246 @Override
247 public void delete( int nIdField, Plugin plugin )
248 {
249 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
250 daoUtil.setInt( 1, nIdField );
251 daoUtil.executeUpdate( );
252 daoUtil.free( );
253 }
254
255
256
257
258 @Override
259 public void store( Field field, Plugin plugin )
260 {
261 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
262 daoUtil.setInt( 1, field.getIdField( ) );
263 daoUtil.setInt( 2, field.getEntry( ).getIdEntry( ) );
264 daoUtil.setString( 3, field.getTitle( ) );
265 daoUtil.setString( 4, field.getValue( ) );
266 daoUtil.setInt( 5, field.getHeight( ) );
267 daoUtil.setInt( 6, field.getWidth( ) );
268 daoUtil.setBoolean( 7, field.isDefaultValue( ) );
269 daoUtil.setInt( 8, field.getMaxSizeEnter( ) );
270 daoUtil.setInt( 9, field.getPosition( ) );
271 daoUtil.setDate( 10, ( field.getValueTypeDate( ) == null ) ? null : new Date( field.getValueTypeDate( ).getTime( ) ) );
272 daoUtil.setString( 11, field.getRoleKey( ) );
273 daoUtil.setString( 12, field.getWorkgroup( ) );
274 daoUtil.setBoolean( 13, field.isShownInResultList( ) );
275 daoUtil.setBoolean( 14, field.isShownInResultRecord( ) );
276 daoUtil.setString( 15, field.getImageType( ) );
277 daoUtil.setInt( 16, field.getIdField( ) );
278 daoUtil.executeUpdate( );
279 daoUtil.free( );
280 }
281
282
283
284
285 @Override
286 public List<Field> selectFieldListByIdEntry( int nIdEntry, Plugin plugin )
287 {
288 List<Field> fieldList = new ArrayList<Field>( );
289 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FIELD_BY_ID_ENTRY, plugin );
290 daoUtil.setInt( 1, nIdEntry );
291 daoUtil.executeQuery( );
292
293 Field field = null;
294 IEntry entry = null;
295
296 while ( daoUtil.next( ) )
297 {
298 field = new Field( );
299 field.setIdField( daoUtil.getInt( 1 ) );
300
301 entry = new Entry( );
302 entry.setIdEntry( daoUtil.getInt( 2 ) );
303 field.setEntry( entry );
304 field.setTitle( daoUtil.getString( 3 ) );
305 field.setValue( daoUtil.getString( 4 ) );
306 field.setHeight( daoUtil.getInt( 5 ) );
307 field.setWidth( daoUtil.getInt( 6 ) );
308 field.setDefaultValue( daoUtil.getBoolean( 7 ) );
309 field.setMaxSizeEnter( daoUtil.getInt( 8 ) );
310 field.setPosition( daoUtil.getInt( 9 ) );
311 field.setValueTypeDate( daoUtil.getDate( 10 ) );
312 field.setRoleKey( daoUtil.getString( 11 ) );
313 field.setWorkgroup( daoUtil.getString( 12 ) );
314 field.setShownInResultList( daoUtil.getBoolean( 13 ) );
315 field.setShownInResultRecord( daoUtil.getBoolean( 14 ) );
316 field.setImageType( daoUtil.getString( 15 ) );
317
318 fieldList.add( field );
319 }
320
321 daoUtil.free( );
322
323 return fieldList;
324 }
325
326
327
328
329 @Override
330 public void deleteVerifyBy( int nIdField, int nIdExpression, Plugin plugin )
331 {
332 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_VERIF_BY, plugin );
333 daoUtil.setInt( 1, nIdField );
334 daoUtil.setInt( 2, nIdExpression );
335 daoUtil.executeUpdate( );
336 daoUtil.free( );
337 }
338
339
340
341
342 @Override
343 public void insertVerifyBy( int nIdField, int nIdExpression, Plugin plugin )
344 {
345 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_VERIF_BY, plugin );
346 daoUtil.setInt( 1, nIdField );
347 daoUtil.setInt( 2, nIdExpression );
348 daoUtil.executeUpdate( );
349 daoUtil.free( );
350 }
351
352
353
354
355 @Override
356 public List<Integer> selectListRegularExpressionKeyByIdField( int nIdField, Plugin plugin )
357 {
358 List<Integer> regularExpressionList = new ArrayList<Integer>( );
359 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_FIELD, plugin );
360 daoUtil.setInt( 1, nIdField );
361 daoUtil.executeQuery( );
362
363 while ( daoUtil.next( ) )
364 {
365 regularExpressionList.add( daoUtil.getInt( 1 ) );
366 }
367
368 daoUtil.free( );
369
370 return regularExpressionList;
371 }
372
373
374
375
376 @Override
377 public boolean isRegularExpressionIsUse( int nIdExpression, Plugin plugin )
378 {
379 int nNumberEntry = 0;
380
381 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_FIELD_BY_ID_REGULAR_EXPRESSION, plugin );
382 daoUtil.setInt( 1, nIdExpression );
383 daoUtil.executeQuery( );
384
385 if ( daoUtil.next( ) )
386 {
387 nNumberEntry = daoUtil.getInt( 1 );
388 }
389
390 daoUtil.free( );
391
392 return nNumberEntry != 0;
393 }
394 }