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.appcenter.business;
35
36 import fr.paris.lutece.plugins.appcenter.business.organization.OrganizationManager;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
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.HashMap;
43 import java.util.List;
44 import java.util.Map;
45
46
47
48
49 public final class ApplicationDAO implements IApplicationDAO
50 {
51
52 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_application ) FROM appcenter_application";
53 private static final String SQL_QUERY_SELECT = "SELECT appcenter_application.id_application, name, description, id_organization_manager, application_data,code, environment_code, id_file, is_active FROM appcenter_application LEFT JOIN appcenter_application_environment ON appcenter_application.id_application = appcenter_application_environment.id_application WHERE appcenter_application.id_application = ? ";
54 private static final String SQL_QUERY_SELECT_BY_CODE = "SELECT appcenter_application.id_application, name, description, id_organization_manager, application_data,code, environment_code, id_file, is_active FROM appcenter_application LEFT JOIN appcenter_application_environment ON appcenter_application.id_application = appcenter_application_environment.id_application WHERE appcenter_application.code = ? ";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO appcenter_application ( id_application, name, description, id_organization_manager, application_data,code, id_file ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM appcenter_application WHERE id_application = ? ";
57 private static final String SQL_QUERY_UPDATE = "UPDATE appcenter_application SET name = ?, description = ? , id_organization_manager = ?, code = ?, id_file = ? WHERE id_application = ?";
58 private static final String SQL_QUERY_UPDATE_DATA = "UPDATE appcenter_application SET application_data = ? WHERE id_application = ?";
59 private static final String SQL_QUERY_SELECTALL = "SELECT id_application, name, description, id_organization_manager, application_data, code, id_file, is_active FROM appcenter_application";
60 private static final String SQL_QUERY_SELECT_USER_ROLE = "SELECT id_role FROM appcenter_user_application_role WHERE id_application = ? AND id_user = ? ";
61 private static final String SQL_QUERY_DELETE_AUTHORIZED = "DELETE FROM appcenter_user_application_role WHERE id_application = ? ";
62 private static final String SQL_QUERY_INSERT_ENVIRONMENT = " INSERT INTO appcenter_application_environment ( id_application, environment_code ) VALUES ( ? , ? ) ";
63 private static final String SQL_QUERY_DELETE_ENVIRONMENT = " DELETE FROM appcenter_application_environment WHERE id_application = ? ";
64 private static final String SQL_QUERY_UPDATE_STATUS = "UPDATE appcenter_application SET is_active = ?, code = ? WHERE id_application = ?";
65
66
67 private static final String CONSTANT_WHERE = " WHERE ";
68 private static final String CONSTANT_WHERE_SEARCH = " ( code LIKE ? OR name LIKE ? ) ";
69 private static final String SQL_LIKE_WILDCARD = "%";
70
71
72
73
74
75
76
77
78 public int newPrimaryKey( Plugin plugin )
79 {
80 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
81 daoUtil.executeQuery( );
82 int nKey = 1;
83
84 if ( daoUtil.next( ) )
85 {
86 nKey = daoUtil.getInt( 1 ) + 1;
87 }
88
89 daoUtil.free( );
90 return nKey;
91 }
92
93
94
95
96 @Override
97 public void insert( Application application, Plugin plugin )
98 {
99 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
100 application.setId( newPrimaryKey( plugin ) );
101 int nIndex = 1;
102
103 daoUtil.setInt( nIndex++, application.getId( ) );
104 daoUtil.setString( nIndex++, application.getName( ) );
105 daoUtil.setString( nIndex++, application.getDescription( ) );
106 if( application.getOrganizationManager( )!=null)
107 {
108 daoUtil.setInt( nIndex++, application.getOrganizationManager( ).getIdOrganizationManager( ) );
109 }
110 else
111 {
112 daoUtil.setIntNull( nIndex++);
113
114 }
115 daoUtil.setString( nIndex++, application.getApplicationData( ) );
116 daoUtil.setString( nIndex++, application.getCode( ) );
117 daoUtil.setInt( nIndex++, application.getIdFileLogo( ) );
118
119 daoUtil.executeUpdate( );
120 daoUtil.free( );
121
122 for ( Environment envi : application.getListEnvironment( ) )
123 {
124 daoUtil = new DAOUtil( SQL_QUERY_INSERT_ENVIRONMENT, plugin );
125 nIndex = 1;
126
127 daoUtil.setInt( nIndex++, application.getId( ) );
128 daoUtil.setString( nIndex++, envi.getPrefix( ) );
129
130 daoUtil.executeUpdate( );
131 daoUtil.free( );
132 }
133 }
134
135
136
137
138 @Override
139 public Application load( int nKey, Plugin plugin )
140 {
141 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
142 daoUtil.setInt( 1, nKey );
143 daoUtil.executeQuery( );
144 Application application = null;
145 List<String> listEnvironmentCode = new ArrayList<>( );
146 List<Environment> listEnvironment = new ArrayList<>( );
147
148 if ( daoUtil.next( ) )
149 {
150
151 application = new Application( );
152 int nIndex = 1;
153
154 application.setId( daoUtil.getInt( nIndex++ ) );
155 application.setName( daoUtil.getString( nIndex++ ) );
156 application.setDescription( daoUtil.getString( nIndex++ ) );
157 OrganizationManagerization/OrganizationManager.html#OrganizationManager">OrganizationManager organizationManager = new OrganizationManager( );
158 organizationManager.setIdOrganizationManager( daoUtil.getInt( nIndex++ ) );
159 application.setOrganizationManager( organizationManager );
160 application.setApplicationData( daoUtil.getString( nIndex++ ) );
161 application.setCode( daoUtil.getString( nIndex++ ) );
162 String strEnviCode = daoUtil.getString( nIndex++ );
163 application.setIdFileLogo( daoUtil.getInt( nIndex++ ) );
164 if ( strEnviCode != null )
165 {
166 listEnvironmentCode.add( strEnviCode );
167 }
168 application.setActive( daoUtil.getBoolean( nIndex++ ) );
169 while ( daoUtil.next( ) )
170 {
171 strEnviCode = daoUtil.getString( 7 );
172 if ( strEnviCode != null )
173 {
174 listEnvironmentCode.add( strEnviCode );
175 }
176 }
177 for ( String enviCode : listEnvironmentCode )
178 {
179 Environment envi = Environment.getEnvironment( enviCode );
180 if ( envi != null )
181 {
182 listEnvironment.add( envi );
183 }
184 }
185 application.setListEnvironment( listEnvironment );
186 }
187
188 daoUtil.free( );
189 return application;
190 }
191
192
193
194
195 @Override
196 public void delete( int nKey, Plugin plugin )
197 {
198 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
199 daoUtil.setInt( 1, nKey );
200 daoUtil.executeUpdate( );
201 daoUtil.free( );
202
203
204 daoUtil = new DAOUtil( SQL_QUERY_DELETE_ENVIRONMENT, plugin );
205 daoUtil.setInt( 1, nKey );
206 daoUtil.executeUpdate( );
207 daoUtil.free( );
208
209
210 daoUtil = new DAOUtil( SQL_QUERY_DELETE_AUTHORIZED, plugin );
211 daoUtil.setInt( 1, nKey );
212 daoUtil.executeUpdate( );
213 daoUtil.free( );
214 }
215
216
217
218
219 @Override
220 public void store( Application application, Plugin plugin )
221 {
222 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
223 int nIndex = 1;
224
225 daoUtil.setString( nIndex++, application.getName( ) );
226 daoUtil.setString( nIndex++, application.getDescription( ) );
227 if(application.getOrganizationManager()!=null)
228 {
229 daoUtil.setInt( nIndex++, application.getOrganizationManager( ).getIdOrganizationManager( ) );
230 }
231 else
232 {
233 daoUtil.setIntNull(nIndex++);
234
235 }
236
237 daoUtil.setString( nIndex++, application.getCode( ) );
238 daoUtil.setInt( nIndex++, application.getIdFileLogo( ) );
239
240 daoUtil.setInt( nIndex, application.getId( ) );
241
242 daoUtil.executeUpdate( );
243 daoUtil.free( );
244
245
246 daoUtil = new DAOUtil( SQL_QUERY_DELETE_ENVIRONMENT, plugin );
247 daoUtil.setInt( 1, application.getId( ) );
248 daoUtil.executeUpdate( );
249 daoUtil.free( );
250
251
252 for ( Environment envi : application.getListEnvironment( ) )
253 {
254 daoUtil = new DAOUtil( SQL_QUERY_INSERT_ENVIRONMENT, plugin );
255 nIndex = 1;
256
257 daoUtil.setInt( nIndex++, application.getId( ) );
258 daoUtil.setString( nIndex++, envi.getPrefix( ) );
259
260 daoUtil.executeUpdate( );
261 daoUtil.free( );
262 }
263
264 }
265
266
267
268
269 @Override
270 public void storeData( int nApplicationId, String strData, Plugin plugin )
271 {
272 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_DATA, plugin );
273 int nIndex = 1;
274
275 daoUtil.setString( nIndex++, strData );
276 daoUtil.setInt( nIndex, nApplicationId );
277
278 daoUtil.executeUpdate( );
279 daoUtil.free( );
280 }
281
282
283
284
285 @Override
286 public List<Application> selectApplicationsList( Plugin plugin )
287 {
288 List<Application> applicationList = new ArrayList<>( );
289 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
290 daoUtil.executeQuery( );
291
292 while ( daoUtil.next( ) )
293 {
294 Applicationr/business/Application.html#Application">Application application = new Application( );
295 int nIndex = 1;
296
297 application.setId( daoUtil.getInt( nIndex++ ) );
298 application.setName( daoUtil.getString( nIndex++ ) );
299 application.setDescription( daoUtil.getString( nIndex++ ) );
300 OrganizationManagerization/OrganizationManager.html#OrganizationManager">OrganizationManager organizationManager = new OrganizationManager( );
301 organizationManager.setIdOrganizationManager( daoUtil.getInt( nIndex++ ) );
302 application.setOrganizationManager( organizationManager );
303 application.setApplicationData( daoUtil.getString( nIndex++ ) );
304 application.setCode( daoUtil.getString( nIndex++ ) );
305 application.setIdFileLogo( daoUtil.getInt( nIndex++ ) );
306 application.setActive( daoUtil.getBoolean( nIndex++ ) );
307
308 applicationList.add( application );
309 }
310
311 daoUtil.free( );
312 return applicationList;
313 }
314
315
316
317
318 @Override
319 public List<Application> selectApplicationsListByFilter( ApplicationFilter filter, Plugin plugin )
320 {
321 List<Application> applicationList = new ArrayList<>( );
322 StringBuilder strSqlQuery = new StringBuilder( SQL_QUERY_SELECTALL );
323
324 if ( filter.hasSearch( ) )
325 {
326 strSqlQuery.append( CONSTANT_WHERE );
327 strSqlQuery.append( CONSTANT_WHERE_SEARCH );
328 }
329
330 DAOUtil daoUtil = new DAOUtil( strSqlQuery.toString( ), plugin );
331 int nIndex = 1;
332 if ( filter.hasSearch( ) )
333 {
334 daoUtil.setString( nIndex++, SQL_LIKE_WILDCARD + filter.getSearch( ) + SQL_LIKE_WILDCARD );
335 daoUtil.setString( nIndex++, SQL_LIKE_WILDCARD + filter.getSearch( ) + SQL_LIKE_WILDCARD );
336 }
337
338 daoUtil.executeQuery( );
339
340 while ( daoUtil.next( ) )
341 {
342 Applicationr/business/Application.html#Application">Application application = new Application( );
343 nIndex = 1;
344
345 application.setId( daoUtil.getInt( nIndex++ ) );
346 application.setName( daoUtil.getString( nIndex++ ) );
347 application.setDescription( daoUtil.getString( nIndex++ ) );
348 OrganizationManagerization/OrganizationManager.html#OrganizationManager">OrganizationManager organizationManager = new OrganizationManager( );
349 organizationManager.setIdOrganizationManager( daoUtil.getInt( nIndex++ ) );
350 application.setOrganizationManager( organizationManager );
351 application.setApplicationData( daoUtil.getString( nIndex++ ) );
352 application.setCode( daoUtil.getString( nIndex++ ) );
353 application.setIdFileLogo( daoUtil.getInt( nIndex++ ) );
354 application.setActive( daoUtil.getBoolean( nIndex++ ) );
355
356 applicationList.add( application );
357 }
358
359 daoUtil.free( );
360 return applicationList;
361 }
362
363
364
365
366 @Override
367 public ReferenceList selectApplicationsReferenceList( Plugin plugin )
368 {
369 ReferenceList applicationList = new ReferenceList( );
370 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
371 daoUtil.executeQuery( );
372
373 while ( daoUtil.next( ) )
374 {
375 applicationList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
376 }
377
378 daoUtil.free( );
379 return applicationList;
380 }
381
382
383
384
385 @Override
386 public Map<String, Application> selectApplicationsMap( Plugin plugin )
387 {
388 Map<String, Application> applicationsMap = new HashMap<String, Application>( );
389
390 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
391 daoUtil.executeQuery( );
392
393 while ( daoUtil.next( ) )
394 {
395 Applicationr/business/Application.html#Application">Application application = new Application( );
396 int nIndex = 1;
397
398 application.setId( daoUtil.getInt( nIndex++ ) );
399 application.setName( daoUtil.getString( nIndex++ ) );
400 application.setDescription( daoUtil.getString( nIndex++ ) );
401 OrganizationManagerization/OrganizationManager.html#OrganizationManager">OrganizationManager organizationManager = new OrganizationManager( );
402 organizationManager.setIdOrganizationManager( daoUtil.getInt( nIndex++ ) );
403 application.setOrganizationManager( organizationManager );
404 application.setApplicationData( daoUtil.getString( nIndex++ ) );
405 application.setCode( daoUtil.getString( nIndex++ ) );
406 application.setIdFileLogo( daoUtil.getInt( nIndex++ ) );
407
408 applicationsMap.put( Integer.toString( application.getId( ) ), application );
409 }
410
411 daoUtil.free( );
412
413 return applicationsMap;
414 }
415
416
417
418
419 @Override
420 public int getUserRole( int nApplicationId, String strUserId, int nDefaultRole, Plugin plugin )
421 {
422 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_ROLE );
423 daoUtil.setInt( 1, nApplicationId );
424 daoUtil.setString( 2, strUserId );
425 daoUtil.executeQuery( );
426
427 int nRole = nDefaultRole;
428 if ( daoUtil.next( ) )
429 {
430 nRole = daoUtil.getInt( 1 );
431 }
432
433 daoUtil.free( );
434 return nRole;
435
436 }
437
438
439
440
441 @Override
442 public Application loadByCode( String strCode, Plugin plugin )
443 {
444 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CODE, plugin );
445 daoUtil.setString( 1, strCode );
446 daoUtil.executeQuery( );
447 Application application = null;
448 List<String> listEnvironmentCode = new ArrayList<>( );
449 List<Environment> listEnvironment = new ArrayList<>( );
450
451 if ( daoUtil.next( ) )
452 {
453
454 application = new Application( );
455 int nIndex = 1;
456
457 application.setId( daoUtil.getInt( nIndex++ ) );
458 application.setName( daoUtil.getString( nIndex++ ) );
459 application.setDescription( daoUtil.getString( nIndex++ ) );
460 OrganizationManagerization/OrganizationManager.html#OrganizationManager">OrganizationManager organizationManager = new OrganizationManager( );
461 organizationManager.setIdOrganizationManager( daoUtil.getInt( nIndex++ ) );
462 application.setOrganizationManager( organizationManager );
463 application.setApplicationData( daoUtil.getString( nIndex++ ) );
464 application.setCode( daoUtil.getString( nIndex++ ) );
465 String strEnviCode = daoUtil.getString( nIndex++ );
466
467 if ( strEnviCode != null )
468 {
469 listEnvironmentCode.add( strEnviCode );
470 }
471 application.setIdFileLogo( daoUtil.getInt( nIndex++ ) );
472 application.setActive( daoUtil.getBoolean( nIndex++ ) );
473 while ( daoUtil.next( ) )
474 {
475 strEnviCode = daoUtil.getString( 7 );
476 if ( strEnviCode != null )
477 {
478 listEnvironmentCode.add( strEnviCode );
479 }
480 }
481 for ( String enviCode : listEnvironmentCode )
482 {
483 Environment envi = Environment.getEnvironment( enviCode );
484 if ( envi != null )
485 {
486 listEnvironment.add( envi );
487 }
488 }
489 application.setListEnvironment( listEnvironment );
490 }
491
492 daoUtil.free( );
493 return application;
494 }
495
496 @Override
497 public void updateStatus( int nApplicationId, String strCodeApp, boolean isActive, Plugin plugin )
498 {
499 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_STATUS, plugin ) )
500 {
501 daoUtil.setBoolean( 1, isActive );
502 daoUtil.setString( 2, strCodeApp );
503 daoUtil.setInt( 3, nApplicationId );
504 daoUtil.executeUpdate( );
505 daoUtil.free( );
506 }
507 }
508 }