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.portal.business.workgroup;
35
36 import fr.paris.lutece.portal.business.user.AdminUser;
37 import fr.paris.lutece.portal.business.user.AdminUserHome;
38 import fr.paris.lutece.util.ReferenceList;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.util.ArrayList;
42 import java.util.Collection;
43
44
45
46
47 public final class AdminWorkgroupDAO implements IAdminWorkgroupDAO
48 {
49
50 private static final String CONSTANT_PERCENT = "%";
51 private static final String SQL_QUERY_SELECT = " SELECT workgroup_key, workgroup_description FROM core_admin_workgroup WHERE workgroup_key = ? ";
52 private static final String SQL_QUERY_INSERT = " INSERT INTO core_admin_workgroup ( workgroup_key, workgroup_description ) VALUES ( ?, ? ) ";
53 private static final String SQL_QUERY_DELETE = " DELETE FROM core_admin_workgroup WHERE workgroup_key = ? ";
54 private static final String SQL_QUERY_UPDATE = " UPDATE core_admin_workgroup SET workgroup_description = ? WHERE workgroup_key = ? ";
55 private static final String SQL_QUERY_SELECTALL = " SELECT workgroup_key, workgroup_description FROM core_admin_workgroup ORDER BY workgroup_key";
56 private static final String SQL_QUERY_SELECT_USER_WORKGROUP = " SELECT workgroup_key FROM core_admin_workgroup_user WHERE id_user = ? AND workgroup_key = ? ";
57 private static final String SQL_QUERY_USER_IN_WORKGROUP = " SELECT id_user FROM core_admin_workgroup_user WHERE id_user = ? ";
58 private static final String SQL_QUERY_SELECT_USER_WORKGROUPS = " SELECT a.workgroup_key, a.workgroup_description "
59 + " FROM core_admin_workgroup a, core_admin_workgroup_user b " + " WHERE a.workgroup_key = b.workgroup_key AND b.id_user = ? ";
60 private static final String SQL_QUERY_SELECT_USERS_LIST_FOR_WORKGROUP = " SELECT b.id_user " + " FROM core_admin_workgroup a, core_admin_workgroup_user b "
61 + " WHERE a.workgroup_key = b.workgroup_key AND a.workgroup_key = ?";
62 private static final String SQL_QUERY_DELETE_ALL_USERS_WORKGROUP = " DELETE FROM core_admin_workgroup_user WHERE workgroup_key = ? ";
63 private static final String SQL_QUERY_INSERT_USER_WORKGROUP = " INSERT INTO core_admin_workgroup_user ( workgroup_key, id_user ) VALUES ( ?, ? ) ";
64 private static final String SQL_QUERY_DELETE_USER_FROM_WORKGROUP = " DELETE FROM core_admin_workgroup_user WHERE workgroup_key = ? AND id_user = ?";
65 private static final String SQL_QUERY_SELECT_WORKGROUP_FROM_SEARCH = " SELECT workgroup_key, workgroup_description FROM core_admin_workgroup "
66 + " WHERE workgroup_key LIKE ? AND workgroup_description LIKE ? ORDER BY workgroup_key ";
67
68
69
70
71 public void insert( AdminWorkgroup workgroup )
72 {
73 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
74 {
75 daoUtil.setString( 1, workgroup.getKey( ) );
76 daoUtil.setString( 2, workgroup.getDescription( ) );
77
78 daoUtil.executeUpdate( );
79 }
80 }
81
82
83
84
85 public AdminWorkgroup load( String strWorkgroupKey )
86 {
87 AdminWorkgroup workgroup = null;
88 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
89 {
90 daoUtil.setString( 1, strWorkgroupKey );
91 daoUtil.executeQuery( );
92
93 if ( daoUtil.next( ) )
94 {
95 workgroup = new AdminWorkgroup( );
96 workgroup.setKey( daoUtil.getString( 1 ) );
97 workgroup.setDescription( daoUtil.getString( 2 ) );
98 }
99
100 }
101
102 return workgroup;
103 }
104
105
106
107
108 public void delete( String strWorkgroupKey )
109 {
110 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
111 {
112 daoUtil.setString( 1, strWorkgroupKey );
113
114 daoUtil.executeUpdate( );
115 }
116 }
117
118
119
120
121 public void store( AdminWorkgroup workgroup )
122 {
123 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
124 {
125 daoUtil.setString( 1, workgroup.getDescription( ) );
126 daoUtil.setString( 2, workgroup.getKey( ) );
127
128 daoUtil.executeUpdate( );
129 }
130 }
131
132
133
134
135 public Collection<AdminWorkgroup> selectWorkgroupList( )
136 {
137 Collection<AdminWorkgroup> listWorkgroups = new ArrayList<>( );
138 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL ) )
139 {
140 daoUtil.executeQuery( );
141
142 while ( daoUtil.next( ) )
143 {
144 AdminWorkgrouporkgroup/AdminWorkgroup.html#AdminWorkgroup">AdminWorkgroup workgroup = new AdminWorkgroup( );
145 workgroup.setKey( daoUtil.getString( 1 ) );
146 workgroup.setDescription( daoUtil.getString( 2 ) );
147
148 listWorkgroups.add( workgroup );
149 }
150
151 }
152
153 return listWorkgroups;
154 }
155
156
157
158
159 public boolean checkExistWorkgroup( String strWorkgroupKey )
160 {
161 boolean check = false;
162 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
163 {
164 daoUtil.setString( 1, strWorkgroupKey );
165 daoUtil.executeQuery( );
166
167 if ( daoUtil.next( ) )
168 {
169 check = true;
170 }
171 }
172 return check;
173 }
174
175
176
177
178 public boolean isUserInWorkgroup( int nIdUser, String strWorkgroupKey )
179 {
180 boolean bInWorkgroup = false;
181 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_WORKGROUP ) )
182 {
183 daoUtil.setInt( 1, nIdUser );
184 daoUtil.setString( 2, strWorkgroupKey );
185 daoUtil.executeQuery( );
186
187 if ( daoUtil.next( ) )
188 {
189 bInWorkgroup = true;
190 }
191
192 }
193
194 return bInWorkgroup;
195 }
196
197
198
199
200 public boolean checkUserHasWorkgroup( int nIdUser )
201 {
202 boolean check = false;
203 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_USER_IN_WORKGROUP ) )
204 {
205 daoUtil.setInt( 1, nIdUser );
206 daoUtil.executeQuery( );
207
208 if ( daoUtil.next( ) )
209 {
210 check = true;
211 }
212 }
213 return check;
214 }
215
216
217
218
219 public ReferenceList getUserWorkgroups( int nIdUser )
220 {
221 ReferenceListml#ReferenceList">ReferenceList listWorkgroups = new ReferenceList( );
222 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_WORKGROUPS ) )
223 {
224 daoUtil.setInt( 1, nIdUser );
225 daoUtil.executeQuery( );
226
227 while ( daoUtil.next( ) )
228 {
229 listWorkgroups.addItem( daoUtil.getString( 1 ), daoUtil.getString( 2 ) );
230 }
231
232 }
233
234 return listWorkgroups;
235 }
236
237
238
239
240 public Collection<AdminUser> getUsersListForWorkgroup( String strWorkgroupKey )
241 {
242 Collection<AdminUser> listUsers = new ArrayList<>( );
243 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USERS_LIST_FOR_WORKGROUP ) )
244 {
245 daoUtil.setString( 1, strWorkgroupKey );
246 daoUtil.executeQuery( );
247
248 AdminUser adminUser = null;
249
250 while ( daoUtil.next( ) )
251 {
252 adminUser = AdminUserHome.findByPrimaryKey( daoUtil.getInt( 1 ) );
253
254 if ( adminUser != null )
255 {
256 listUsers.add( adminUser );
257 }
258 }
259
260 }
261
262 return listUsers;
263 }
264
265
266
267
268 public void deleteAllUsersForWorkgroup( String strWorkgroupKey )
269 {
270 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL_USERS_WORKGROUP ) )
271 {
272 daoUtil.setString( 1, strWorkgroupKey );
273 daoUtil.executeUpdate( );
274 }
275 }
276
277
278
279
280 public void insertUserForWorkgroup( AdminUser user, String strWorkgroupKey )
281 {
282 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_USER_WORKGROUP ) )
283 {
284 daoUtil.setString( 1, strWorkgroupKey );
285 daoUtil.setInt( 2, user.getUserId( ) );
286 daoUtil.executeUpdate( );
287 }
288 }
289
290
291
292
293 public void deleteUserFromWorkgroup( int nUserId, String strWorkgroupKey )
294 {
295 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_USER_FROM_WORKGROUP ) )
296 {
297 daoUtil.setString( 1, strWorkgroupKey );
298 daoUtil.setInt( 2, nUserId );
299 daoUtil.executeUpdate( );
300 }
301 }
302
303
304
305
306
307
308
309
310 public Collection<AdminWorkgroup> selectWorkgroupsByFilter( AdminWorkgroupFilter awFilter )
311 {
312 Collection<AdminWorkgroup> listFilteredWorkgroups = new ArrayList<>( );
313 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_WORKGROUP_FROM_SEARCH ) )
314 {
315
316 daoUtil.setString( 1, CONSTANT_PERCENT + awFilter.getKey( ) + CONSTANT_PERCENT );
317 daoUtil.setString( 2, CONSTANT_PERCENT + awFilter.getDescription( ) + CONSTANT_PERCENT );
318
319 daoUtil.executeQuery( );
320
321 while ( daoUtil.next( ) )
322 {
323 AdminWorkgrouporkgroup/AdminWorkgroup.html#AdminWorkgroup">AdminWorkgroup workgroup = new AdminWorkgroup( );
324 workgroup.setKey( daoUtil.getString( 1 ) );
325 workgroup.setDescription( daoUtil.getString( 2 ) );
326
327 listFilteredWorkgroups.add( workgroup );
328 }
329
330 }
331
332 return listFilteredWorkgroups;
333 }
334 }