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.mylutece.business.attribute;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.portal.service.util.AppLogService;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.List;
42 import java.util.Locale;
43
44
45
46
47
48
49 public class MyLuteceUserFieldDAO implements IMyLuteceUserFieldDAO
50 {
51
52 private static final String CONSTANT_PERCENT = "%";
53 private static final String CONSTANT_OPEN_BRACKET = "(";
54 private static final String CONSTANT_CLOSED_BRACKET = ")";
55
56
57 private static final String SQL_QUERY_NEW_PK = " SELECT max(id_user_field) FROM mylutece_user_field ";
58
59
60 private static final String SQL_QUERY_SELECT = " SELECT auf.id_user_field, auf.id_user, auf.id_attribute, auf.id_field, auf.user_field_value, "
61 + " a.type_class_name, a.title, a.help_message, a.is_mandatory, a.attribute_position, "
62 + " af.title, af.DEFAULT_value, af.is_DEFAULT_value, af.field_position " + " FROM mylutece_user_field auf "
63 + " INNER JOIN mylutece_attribute a ON auf.id_attribute = a.id_attribute "
64 + " INNER JOIN mylutece_attribute_field af ON auf.id_field = af.id_field " + " WHERE auf.id_user_field = ? ";
65 private static final String SQL_QUERY_SELECT_USER_FIELDS_BY_ID_USER_ID_ATTRIBUTE = " SELECT auf.id_user_field, auf.id_user, auf.id_attribute, auf.id_field, auf.user_field_value, "
66 + " a.type_class_name, a.title, a.help_message, a.is_mandatory, a.attribute_position " + " FROM mylutece_user_field auf "
67 + " INNER JOIN mylutece_attribute a ON a.id_attribute = auf.id_attribute " + " WHERE auf.id_user = ? AND auf.id_attribute = ? ";
68 private static final String SQL_QUERY_SELECT_ID_USER = " SELECT id_user FROM mylutece_user_field WHERE id_attribute = ? AND id_field = ? AND user_field_value LIKE ? ";
69 private static final String SQL_AND_ID_USER_IN = " AND id_user IN ";
70
71
72 private static final String SQL_QUERY_INSERT = " INSERT INTO mylutece_user_field (id_user_field, id_user, id_attribute, id_field, user_field_value) "
73 + " VALUES (?,?,?,?,?) ";
74
75
76 private static final String SQL_QUERY_UPDATE = " UPDATE mylutece_user_field SET user_field_value = ? WHERE id_user_field = ? ";
77
78
79 private static final String SQL_QUERY_DELETE = " DELETE FROM mylutece_user_field WHERE id_user_field = ? ";
80 private static final String SQL_QUERY_DELETE_FROM_ID_FIELD = " DELETE FROM mylutece_user_field WHERE id_field = ? ";
81 private static final String SQL_QUERY_DELETE_FROM_ID_USER = " DELETE FROM mylutece_user_field WHERE id_user = ? ";
82 private static final String SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE = " DELETE FROM mylutece_user_field WHERE id_attribute = ? ";
83
84
85
86
87
88
89
90
91 private int newPrimaryKey( Plugin plugin )
92 {
93 int nKey = 1;
94 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_NEW_PK );
95 try( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin ) )
96 {
97 daoUtil.executeQuery( );
98
99 if ( daoUtil.next( ) )
100 {
101 nKey = daoUtil.getInt( 1 ) + 1;
102 }
103
104 }
105
106 return nKey;
107 }
108
109
110
111
112 @Override
113 public MyLuteceUserField load( int nIdUserField, Locale locale, Plugin plugin )
114 {
115 MyLuteceUserField userField = null;
116 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
117 {
118 daoUtil.setInt( 1, nIdUserField );
119 daoUtil.executeQuery( );
120
121 if ( daoUtil.next( ) )
122 {
123 userField = new MyLuteceUserField( );
124 userField.setIdUserField( daoUtil.getInt( 1 ) );
125 userField.setValue( daoUtil.getString( 5 ) );
126
127
128 userField.setUserId( daoUtil.getInt( 2 ) );
129
130
131 IAttribute attribute = null;
132
133 try
134 {
135 attribute = (IAttribute) Class.forName( daoUtil.getString( 6 ) ).newInstance( );
136 }
137 catch( ClassNotFoundException | InstantiationException | IllegalAccessException e )
138 {
139
140
141
142 AppLogService.error( e );
143 }
144
145 if( attribute != null )
146 {
147 attribute.setIdAttribute( daoUtil.getInt( 3 ) );
148 attribute.setTitle( daoUtil.getString( 7 ) );
149 attribute.setHelpMessage( daoUtil.getString( 8 ) );
150 attribute.setMandatory( daoUtil.getBoolean( 9 ) );
151 attribute.setPosition( daoUtil.getInt( 10 ) );
152 attribute.setAttributeType( locale );
153 userField.setAttribute( attribute );
154
155
156 AttributeFieldsiness/attribute/AttributeField.html#AttributeField">AttributeField attributeField = new AttributeField( );
157 attributeField.setIdField( daoUtil.getInt( 4 ) );
158 attributeField.setTitle( daoUtil.getString( 11 ) );
159 attributeField.setValue( daoUtil.getString( 12 ) );
160 attributeField.setDefaultValue( daoUtil.getBoolean( 13 ) );
161 attributeField.setPosition( daoUtil.getInt( 14 ) );
162 userField.setAttributeField( attributeField );
163 }
164 }
165
166 }
167
168 return userField;
169 }
170
171
172
173
174 @Override
175 public void insert( MyLuteceUserField userField, Plugin plugin )
176 {
177 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
178 {
179 daoUtil.setInt( 1, newPrimaryKey( plugin ) );
180 daoUtil.setInt( 2, userField.getUserId( ) );
181 daoUtil.setInt( 3, userField.getAttribute( ).getIdAttribute( ) );
182 daoUtil.setInt( 4, userField.getAttributeField( ).getIdField( ) );
183 daoUtil.setString( 5, userField.getValue( ) );
184
185 daoUtil.executeUpdate( );
186 }
187 }
188
189
190
191
192 @Override
193 public void store( MyLuteceUserField userField, Plugin plugin )
194 {
195 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
196 {
197 daoUtil.setString( 1, userField.getValue( ) );
198 daoUtil.setInt( 2, userField.getIdUserField( ) );
199
200 daoUtil.executeUpdate( );
201 }
202 }
203
204
205
206
207 @Override
208 public void delete( int nIdUserField, Plugin plugin )
209 {
210 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
211 {
212 daoUtil.setInt( 1, nIdUserField );
213
214 daoUtil.executeUpdate( );
215 }
216 }
217
218
219
220
221 @Override
222 public void deleteUserFieldsFromIdField( int nIdField, Plugin plugin )
223 {
224 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_FIELD, plugin ) )
225 {
226 daoUtil.setInt( 1, nIdField );
227
228 daoUtil.executeUpdate( );
229 }
230 }
231
232
233
234
235 @Override
236 public void deleteUserFieldsFromIdUser( int nIdUser, Plugin plugin )
237 {
238 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_USER, plugin ) )
239 {
240 daoUtil.setInt( 1, nIdUser );
241
242 daoUtil.executeUpdate( );
243 }
244 }
245
246
247
248
249 @Override
250 public void deleteUserFieldsFromIdAttribute( int nIdAttribute, Plugin plugin )
251 {
252 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_ID_ATTRIBUTE, plugin ) )
253 {
254 daoUtil.setInt( 1, nIdAttribute );
255
256 daoUtil.executeUpdate( );
257 }
258 }
259
260
261
262
263 @Override
264 public List<MyLuteceUserField> selectUserFieldsByIdUserIdAttribute( int nIdUser, int nIdAttribute, Plugin plugin )
265 {
266 List<MyLuteceUserField> listUserFields = new ArrayList<>( );
267 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FIELDS_BY_ID_USER_ID_ATTRIBUTE, plugin ) )
268 {
269 daoUtil.setInt( 1, nIdUser );
270 daoUtil.setInt( 2, nIdAttribute );
271 daoUtil.executeQuery( );
272
273 while ( daoUtil.next( ) )
274 {
275 MyLuteceUserFieldbusiness/attribute/MyLuteceUserField.html#MyLuteceUserField">MyLuteceUserField userField = new MyLuteceUserField( );
276 userField.setIdUserField( daoUtil.getInt( 1 ) );
277 userField.setValue( daoUtil.getString( 5 ) );
278
279
280 userField.setUserId( nIdUser );
281
282
283 IAttribute attribute = null;
284
285 try
286 {
287 attribute = (IAttribute) Class.forName( daoUtil.getString( 6 ) ).newInstance( );
288 }
289 catch( ClassNotFoundException | InstantiationException | IllegalAccessException e )
290 {
291
292
293
294 AppLogService.error( e );
295 }
296
297 if( attribute != null )
298 {
299 attribute.setIdAttribute( nIdAttribute );
300 attribute.setTitle( daoUtil.getString( 7 ) );
301 attribute.setHelpMessage( daoUtil.getString( 8 ) );
302 attribute.setMandatory( daoUtil.getBoolean( 9 ) );
303 attribute.setPosition( daoUtil.getInt( 10 ) );
304 userField.setAttribute( attribute );
305
306
307 AttributeFieldsiness/attribute/AttributeField.html#AttributeField">AttributeField attributeField = new AttributeField( );
308 attributeField.setIdField( daoUtil.getInt( 4 ) );
309 userField.setAttributeField( attributeField );
310
311 listUserFields.add( userField );
312 }
313 }
314
315 }
316
317 return listUserFields;
318 }
319
320
321
322
323 @Override
324 public List<Integer> selectUsersByFilter( MyLuteceUserFieldFilter mlFieldFilter, Plugin plugin )
325 {
326 List<MyLuteceUserField> listUserFields = mlFieldFilter.getListUserFields( );
327
328 if ( ( listUserFields == null ) || ( listUserFields.isEmpty( ) ) )
329 {
330 return null;
331 }
332
333 List<Integer> listUsers = new ArrayList<>( );
334 StringBuilder sbSQL = new StringBuilder( );
335
336 for ( int i = 1; i <= listUserFields.size( ); i++ )
337 {
338 if ( i == 1 )
339 {
340 sbSQL.append( SQL_QUERY_SELECT_ID_USER );
341 }
342 else
343 {
344 sbSQL.append( CONSTANT_OPEN_BRACKET + SQL_QUERY_SELECT_ID_USER );
345 }
346
347 if ( i != listUserFields.size( ) )
348 {
349 sbSQL.append( SQL_AND_ID_USER_IN );
350 }
351 }
352
353 for ( int i = 2; i <= listUserFields.size( ); i++ )
354 {
355 sbSQL.append( CONSTANT_CLOSED_BRACKET );
356 }
357
358 try( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin ) )
359 {
360 int nbCount = 1;
361
362 for ( MyLuteceUserField userField : listUserFields )
363 {
364 daoUtil.setInt( nbCount++, userField.getAttribute( ).getIdAttribute( ) );
365 daoUtil.setInt( nbCount++, userField.getAttributeField( ).getIdField( ) );
366 daoUtil.setString( nbCount++, CONSTANT_PERCENT + userField.getValue( ) + CONSTANT_PERCENT );
367 }
368
369 daoUtil.executeQuery( );
370
371 while ( daoUtil.next( ) )
372 {
373 listUsers.add( daoUtil.getInt( 1 ) );
374 }
375
376 }
377
378 return listUsers;
379 }
380 }