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.dashboard;
35
36 import fr.paris.lutece.portal.service.dashboard.IDashboardComponent;
37 import fr.paris.lutece.portal.service.dashboard.admin.IAdminDashboardComponent;
38 import fr.paris.lutece.portal.service.util.AppLogService;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.util.ArrayList;
42 import java.util.List;
43
44
45
46
47
48
49 public class AdminDashboardDAO implements IAdminDashboardDAO
50 {
51 private static final String LOG_ERROR_NOT_FOUND = "Admindashboard named {} not found";
52 private static final String SQL_QUERY_MAX_ORDER = "SELECT max(dashboard_order) FROM core_admin_dashboard";
53 private static final String SQL_QUERY_MAX_ORDER_COLUMN = SQL_QUERY_MAX_ORDER + " WHERE dashboard_column = ? ";
54 private static final String SQL_QUERY_DELETE = "DELETE FROM core_admin_dashboard ";
55 private static final String SQL_QUERY_DELETE_BY_NAME = SQL_QUERY_DELETE + " WHERE dashboard_name = ? ";
56 private static final String SQL_QUERY_SELECT = "SELECT dashboard_name, dashboard_order, dashboard_column FROM core_admin_dashboard ";
57 private static final String SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER = " ORDER BY dashboard_column, dashboard_order";
58 private static final String SQL_QUERY_SELECT_ALL = SQL_QUERY_SELECT + " WHERE dashboard_column != - 1 " + SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER;
59 private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT dashboard_column FROM core_admin_dashboard GROUP BY dashboard_column";
60 private static final String SQL_QUERY_FILTER_COLUMN = " dashboard_column = ? ";
61 private static final String SQL_QUERY_FILTER_ORDER = " dashboard_order = ? ";
62 private static final String SQL_QUERY_FILTER_NAME = " dashboard_name = ? ";
63 private static final String SQL_QUERY_SELECT_BY_PRIMARY_KEY = SQL_QUERY_SELECT + " WHERE " + SQL_QUERY_FILTER_NAME;
64 private static final String SQL_QUERY_INSERT = "INSERT INTO core_admin_dashboard( dashboard_name, dashboard_order, dashboard_column ) " + " VALUES(?,?,?)";
65 private static final String SQL_QUERY_UPDATE = "UPDATE core_admin_dashboard " + " SET dashboard_order = ?, dashboard_column = ? WHERE dashboard_name = ?";
66 private static final String SQL_QUERY_KEYWORD_WHERE = " WHERE ";
67 private static final String SQL_QUERY_KEYWORD_AND = " AND ";
68
69
70
71
72 @Override
73 public void delete( String strBeanName )
74 {
75 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_NAME ) )
76 {
77
78 daoUtil.setString( 1, strBeanName );
79
80 daoUtil.executeUpdate( );
81
82 }
83 }
84
85
86
87
88 @Override
89 public void deleteAll( )
90 {
91 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
92 {
93
94 daoUtil.executeUpdate( );
95
96 }
97 }
98
99
100
101
102 @Override
103 public void insert( IAdminDashboardComponent dashboardComponent )
104 {
105 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
106 {
107
108 daoUtil.setString( 1, dashboardComponent.getName( ) );
109 setInsertOrUpdateValues( 2, dashboardComponent, daoUtil );
110
111 daoUtil.executeUpdate( );
112
113 }
114 }
115
116
117
118
119
120
121
122
123
124 private IAdminDashboardComponent findDashboardFromFactory( String strBeanName )
125 {
126 return AdminDashboardFactory.getDashboardComponent( strBeanName );
127 }
128
129
130
131
132 @Override
133 public IAdminDashboardComponent load( String strClassName )
134 {
135 IAdminDashboardComponent dashboardComponent = null;
136
137 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PRIMARY_KEY ) )
138 {
139
140 daoUtil.setString( 1, strClassName );
141
142 daoUtil.executeQuery( );
143
144 if ( daoUtil.next( ) )
145 {
146 String strBeanName = daoUtil.getString( 1 );
147
148 dashboardComponent = findDashboardFromFactory( strBeanName );
149
150 if ( dashboardComponent != null )
151 {
152 load( dashboardComponent, daoUtil );
153 }
154 else
155 {
156 AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
157 }
158 }
159
160 }
161
162 return dashboardComponent;
163 }
164
165
166
167
168 @Override
169 public List<IAdminDashboardComponent> selectAllDashboardComponents( )
170 {
171 List<IAdminDashboardComponent> listDashboards = new ArrayList<>( );
172
173 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL ) )
174 {
175
176 daoUtil.executeQuery( );
177 while ( daoUtil.next( ) )
178 {
179 IAdminDashboardComponent dashboardComponent = null;
180
181 String strBeanName = daoUtil.getString( 1 );
182
183 dashboardComponent = findDashboardFromFactory( strBeanName );
184
185 if ( dashboardComponent != null )
186 {
187 load( dashboardComponent, daoUtil );
188 listDashboards.add( dashboardComponent );
189 }
190 else
191 {
192 AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
193 }
194 }
195
196 }
197
198 return listDashboards;
199 }
200
201
202
203
204 @Override
205 public int selectMaxOrder( )
206 {
207 int nMaxOrder = 0;
208
209 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER ) )
210 {
211 daoUtil.executeQuery( );
212
213 if ( daoUtil.next( ) )
214 {
215 nMaxOrder = daoUtil.getInt( 1 );
216 }
217
218 }
219
220 return nMaxOrder;
221 }
222
223
224
225
226 @Override
227 public int selectMaxOrder( int nColumn )
228 {
229 int nMaxOrder = 0;
230 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER_COLUMN ) )
231 {
232 daoUtil.setInt( 1, nColumn );
233
234 daoUtil.executeQuery( );
235
236 if ( daoUtil.next( ) )
237 {
238 nMaxOrder = daoUtil.getInt( 1 );
239 }
240
241 }
242
243 return nMaxOrder;
244 }
245
246
247
248
249 @Override
250 public List<IAdminDashboardComponent> selectDashboardComponents( AdminDashboardFilter filter )
251 {
252 List<IAdminDashboardComponent> listDashboards = new ArrayList<>( );
253
254 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );
255 buildSQLFilter( sbSQL, filter );
256 sbSQL.append( SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER );
257
258 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
259 {
260
261 applySQLFilter( daoUtil, 1, filter );
262
263 daoUtil.executeQuery( );
264
265 while ( daoUtil.next( ) )
266 {
267 IAdminDashboardComponent dashboardComponent = null;
268
269 String strBeanName = daoUtil.getString( 1 );
270
271 dashboardComponent = findDashboardFromFactory( strBeanName );
272
273 if ( dashboardComponent != null )
274 {
275 load( dashboardComponent, daoUtil );
276 listDashboards.add( dashboardComponent );
277 }
278 else
279 {
280 AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
281 }
282 }
283
284 }
285
286 return listDashboards;
287 }
288
289
290
291
292 @Override
293 public void store( IAdminDashboardComponent dashboardComponent )
294 {
295 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
296 {
297
298 int nIndex = setInsertOrUpdateValues( 1, dashboardComponent, daoUtil );
299 daoUtil.setString( nIndex, dashboardComponent.getName( ) );
300
301 daoUtil.executeUpdate( );
302
303 }
304 }
305
306
307
308
309 @Override
310 public List<Integer> selectColumns( )
311 {
312 List<Integer> listColumns = new ArrayList<>( );
313
314 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COLUMNS ) )
315 {
316
317 daoUtil.executeQuery( );
318
319 while ( daoUtil.next( ) )
320 {
321 listColumns.add( daoUtil.getInt( 1 ) );
322 }
323
324 }
325
326 return listColumns;
327 }
328
329
330
331
332
333
334
335
336
337 private void load( IDashboardComponent component, DAOUtil daoUtil )
338 {
339 int nIndex = 1;
340 component.setName( daoUtil.getString( nIndex++ ) );
341 component.setOrder( daoUtil.getInt( nIndex++ ) );
342 component.setZone( daoUtil.getInt( nIndex++ ) );
343 }
344
345
346
347
348
349
350
351
352
353
354
355
356 private int setInsertOrUpdateValues( int nStartIndex, IDashboardComponent component, DAOUtil daoUtil )
357 {
358 int nIndex = nStartIndex;
359 daoUtil.setInt( nIndex++, component.getOrder( ) );
360 daoUtil.setInt( nIndex++, component.getZone( ) );
361
362 return nIndex;
363 }
364
365
366
367
368
369
370
371
372
373 private void buildSQLFilter( StringBuilder sbSQL, AdminDashboardFilter filter )
374 {
375 List<String> listFilters = new ArrayList<>( );
376
377 if ( filter.containsFilterOrder( ) )
378 {
379 listFilters.add( SQL_QUERY_FILTER_ORDER );
380 }
381
382 if ( filter.containsFilterColumn( ) )
383 {
384 listFilters.add( SQL_QUERY_FILTER_COLUMN );
385 }
386
387 if ( !listFilters.isEmpty( ) )
388 {
389 sbSQL.append( SQL_QUERY_KEYWORD_WHERE );
390
391 boolean bFirstFilter = true;
392
393 for ( String strFilter : listFilters )
394 {
395 sbSQL.append( strFilter );
396
397 if ( !bFirstFilter )
398 {
399 sbSQL.append( SQL_QUERY_KEYWORD_AND );
400 }
401 else
402 {
403 bFirstFilter = false;
404 }
405 }
406 }
407 }
408
409
410
411
412
413
414
415
416
417
418
419
420 private int applySQLFilter( DAOUtil daoUtil, int nStartIndex, AdminDashboardFilter filter )
421 {
422 int nIndex = nStartIndex;
423
424 if ( filter.containsFilterOrder( ) )
425 {
426 daoUtil.setInt( nIndex++, filter.getFilterOrder( ) );
427 }
428
429 if ( filter.containsFilterColumn( ) )
430 {
431 daoUtil.setInt( nIndex++, filter.getFilterColumn( ) );
432 }
433
434 return nIndex;
435 }
436 }