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.myportal.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.Collection;
41 import java.util.List;
42
43
44
45
46
47
48 public final class WidgetDAO implements IWidgetDAO
49 {
50
51 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_widget ) FROM myportal_widget ";
52 private static final String SQL_QUERY_SELECT = " SELECT a.id_widget, a.name, a.description, a.id_category, a.widget_type, a.id_icon, a.config_data, a.status, b.name, a.id_style, c.name, c.css_class, a.is_essential, a.is_new "
53 + " FROM myportal_widget a INNER JOIN myportal_category b ON a.id_category = b.id_category INNER JOIN myportal_widget_style c ON a.id_style = c.id_style "
54 + " WHERE a.id_widget = ?";
55 private static final String SQL_QUERY_INSERT = " INSERT INTO myportal_widget ( id_widget, name, description, id_category, widget_type, id_icon, config_data , id_style, status, is_essential, is_new ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
56 private static final String SQL_QUERY_DELETE = " DELETE FROM myportal_widget WHERE id_widget = ? ";
57 private static final String SQL_QUERY_UPDATE = " UPDATE myportal_widget SET name = ?, description = ?, id_category = ?, widget_type = ?, id_icon = ?, config_data = ?, id_style = ?, status = ?, is_essential = ?, is_new = ? WHERE id_widget = ? ";
58 private static final String SQL_QUERY_SELECTALL = " SELECT a.id_widget, a.name, a.description, a.id_category, a.widget_type, a.id_icon, a.config_data, a.status, b.name, a.id_style, c.name, c.css_class, a.is_essential, a.is_new "
59 + " FROM myportal_widget a INNER JOIN myportal_category b ON a.id_category = b.id_category INNER JOIN myportal_widget_style c ON a.id_style = c.id_style ";
60 private static final String SQL_QUERY_SELECT_PUBLIC_MANDATORY_WIDGETS = " SELECT a.id_widget, a.name, a.description, a.id_category, a.widget_type, a.id_icon, a.config_data, a.status, b.name, a.id_style, c.name, c.css_class, a.is_essential, a.is_new "
61 + " FROM myportal_widget a INNER JOIN myportal_category b ON a.id_category = b.id_category INNER JOIN myportal_widget_style c ON a.id_style = c.id_style "
62 + " WHERE a.status = ? OR a.status = ? ";
63 private static final String SQL_ORDER_BY = " ORDER BY ";
64 private static final String SQL_ASC = " ASC ";
65 private static final String SQL_NAME = " a.name ";
66 private static final String SQL_OR = " OR ";
67 private static final String SQL_AND = " AND ";
68 private static final String SQL_WHERE = " WHERE ";
69 private static final String SQL_FILTER_NAME = " a.name LIKE ? ";
70 private static final String SQL_FILTER_DESCRIPTION = " a.description LIKE ? ";
71 private static final String SQL_FILTER_ID_CATEGORY = " a.id_category = ? ";
72 private static final String SQL_FILTER_ID_STYLE = " a.id_style = ? ";
73 private static final String SQL_FILTER_WIDGET_TYPE = " a.widget_type LIKE ? ";
74 private static final String SQL_FILTER_STATUS = " a.status = ? ";
75 private static final String SQL_FILTER_IS_ESSENTIAL = " a.is_essential = ? ";
76 private static final String SQL_FILTER_IS_NEW = " a.is_new = ? ";
77 private static final String PERCENT = "%";
78
79
80
81
82
83
84
85
86 public int newPrimaryKey( Plugin plugin )
87 {
88 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
89 daoUtil.executeQuery( );
90
91 int nKey;
92
93 if ( !daoUtil.next( ) )
94 {
95
96 nKey = 1;
97 }
98
99 nKey = daoUtil.getInt( 1 ) + 1;
100 daoUtil.free( );
101
102 return nKey;
103 }
104
105
106
107
108
109
110
111
112
113 public void insert( Widget widget, Plugin plugin )
114 {
115 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
116
117 int nIndex = 1;
118 widget.setIdWidget( newPrimaryKey( plugin ) );
119
120 daoUtil.setInt( nIndex++, widget.getIdWidget( ) );
121 daoUtil.setString( nIndex++, widget.getName( ) );
122 daoUtil.setString( nIndex++, widget.getDescription( ) );
123 daoUtil.setInt( nIndex++, widget.getIdCategory( ) );
124 daoUtil.setString( nIndex++, widget.getWidgetType( ) );
125 daoUtil.setInt( nIndex++, widget.getIdIcon( ) );
126 daoUtil.setBytes( nIndex++, widget.getConfigData( ).getBytes( ) );
127 daoUtil.setInt( nIndex++, widget.getIdStyle( ) );
128 daoUtil.setInt( nIndex++, widget.getStatus( ) );
129 daoUtil.setBoolean( nIndex++, widget.getIsEssential( ) );
130 daoUtil.setBoolean( nIndex++, widget.getIsNew( ) );
131
132 daoUtil.executeUpdate( );
133 daoUtil.free( );
134 }
135
136
137
138
139
140
141
142
143
144
145 public Widget load( int nId, Plugin plugin )
146 {
147 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
148 daoUtil.setInt( 1, nId );
149 daoUtil.executeQuery( );
150
151 Widget widget = null;
152
153 if ( daoUtil.next( ) )
154 {
155 int nIndex = 1;
156 widget = new Widget( );
157
158 widget.setIdWidget( daoUtil.getInt( nIndex++ ) );
159 widget.setName( daoUtil.getString( nIndex++ ) );
160 widget.setDescription( daoUtil.getString( nIndex++ ) );
161 widget.setIdCategory( daoUtil.getInt( nIndex++ ) );
162 widget.setWidgetType( daoUtil.getString( nIndex++ ) );
163 widget.setIdIcon( daoUtil.getInt( nIndex++ ) );
164 widget.setConfigData( new String( daoUtil.getBytes( nIndex++ ) ) );
165 widget.setStatus( daoUtil.getInt( nIndex++ ) );
166 widget.setCategory( daoUtil.getString( nIndex++ ) );
167 widget.setIdStyle( daoUtil.getInt( nIndex++ ) );
168 widget.setStyle( daoUtil.getString( nIndex++ ) );
169 widget.setCssClass( daoUtil.getString( nIndex++ ) );
170 widget.setIsEssential( daoUtil.getBoolean( nIndex++ ) );
171 widget.setIsNew( daoUtil.getBoolean( nIndex++ ) );
172 }
173
174 daoUtil.free( );
175
176 return widget;
177 }
178
179
180
181
182
183
184
185
186
187 public void delete( int nWidgetId, Plugin plugin )
188 {
189 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
190 daoUtil.setInt( 1, nWidgetId );
191 daoUtil.executeUpdate( );
192 daoUtil.free( );
193 }
194
195
196
197
198
199
200
201
202
203 public void store( Widget widget, Plugin plugin )
204 {
205 String strSQL = SQL_QUERY_UPDATE;
206 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
207
208 int nIndex = 1;
209 daoUtil.setString( nIndex++, widget.getName( ) );
210 daoUtil.setString( nIndex++, widget.getDescription( ) );
211 daoUtil.setInt( nIndex++, widget.getIdCategory( ) );
212 daoUtil.setString( nIndex++, widget.getWidgetType( ) );
213 daoUtil.setInt( nIndex++, widget.getIdIcon( ) );
214 daoUtil.setBytes( nIndex++, widget.getConfigData( ).getBytes( ) );
215 daoUtil.setInt( nIndex++, widget.getIdStyle( ) );
216 daoUtil.setInt( nIndex++, widget.getStatus( ) );
217 daoUtil.setBoolean( nIndex++, widget.getIsEssential( ) );
218 daoUtil.setBoolean( nIndex++, widget.getIsNew( ) );
219
220 daoUtil.setInt( nIndex++, widget.getIdWidget( ) );
221
222 daoUtil.executeUpdate( );
223 daoUtil.free( );
224 }
225
226
227
228
229
230
231
232
233 public Collection<Widget> selectWidgetsList( Plugin plugin )
234 {
235 String strSQL = SQL_QUERY_SELECTALL + SQL_ORDER_BY + SQL_NAME + SQL_ASC;
236 Collection<Widget> widgetsList = new ArrayList<Widget>( );
237 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
238 daoUtil.executeQuery( );
239
240 while ( daoUtil.next( ) )
241 {
242 int nIndex = 1;
243 Widgets/myportal/business/Widget.html#Widget">Widget widget = new Widget( );
244
245 widget.setIdWidget( daoUtil.getInt( nIndex++ ) );
246 widget.setName( daoUtil.getString( nIndex++ ) );
247 widget.setDescription( daoUtil.getString( nIndex++ ) );
248 widget.setIdCategory( daoUtil.getInt( nIndex++ ) );
249 widget.setWidgetType( daoUtil.getString( nIndex++ ) );
250 widget.setIdIcon( daoUtil.getInt( nIndex++ ) );
251 widget.setConfigData( new String( daoUtil.getBytes( nIndex++ ) ) );
252 widget.setStatus( daoUtil.getInt( nIndex++ ) );
253 widget.setCategory( daoUtil.getString( nIndex++ ) );
254 widget.setIdStyle( daoUtil.getInt( nIndex++ ) );
255 widget.setStyle( daoUtil.getString( nIndex++ ) );
256 widget.setCssClass( daoUtil.getString( nIndex++ ) );
257 widget.setIsEssential( daoUtil.getBoolean( nIndex++ ) );
258 widget.setIsNew( daoUtil.getBoolean( nIndex++ ) );
259 widgetsList.add( widget );
260 }
261
262 daoUtil.free( );
263
264 return widgetsList;
265 }
266
267
268
269
270 public List<Widget> getPublicMandatoryWidgets( Plugin plugin )
271 {
272 String strSQL = SQL_QUERY_SELECT_PUBLIC_MANDATORY_WIDGETS + SQL_ORDER_BY + SQL_NAME + SQL_ASC;
273 List<Widget> widgetsList = new ArrayList<Widget>( );
274 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
275 daoUtil.setInt( 1, WidgetStatusEnum.PUBLIC.getId( ) );
276 daoUtil.setInt( 2, WidgetStatusEnum.MANDATORY.getId( ) );
277 daoUtil.executeQuery( );
278
279 while ( daoUtil.next( ) )
280 {
281 int nIndex = 1;
282 Widgets/myportal/business/Widget.html#Widget">Widget widget = new Widget( );
283
284 widget.setIdWidget( daoUtil.getInt( nIndex++ ) );
285 widget.setName( daoUtil.getString( nIndex++ ) );
286 widget.setDescription( daoUtil.getString( nIndex++ ) );
287 widget.setIdCategory( daoUtil.getInt( nIndex++ ) );
288 widget.setWidgetType( daoUtil.getString( nIndex++ ) );
289 widget.setIdIcon( daoUtil.getInt( nIndex++ ) );
290 widget.setConfigData( new String( daoUtil.getBytes( nIndex++ ) ) );
291 widget.setStatus( daoUtil.getInt( nIndex++ ) );
292 widget.setCategory( daoUtil.getString( nIndex++ ) );
293 widget.setIdStyle( daoUtil.getInt( nIndex++ ) );
294 widget.setStyle( daoUtil.getString( nIndex++ ) );
295 widget.setCssClass( daoUtil.getString( nIndex++ ) );
296 widget.setIsEssential( daoUtil.getBoolean( nIndex++ ) );
297 widget.setIsNew( daoUtil.getBoolean( nIndex++ ) );
298 widgetsList.add( widget );
299 }
300
301 daoUtil.free( );
302
303 return widgetsList;
304 }
305
306
307
308
309 public List<Widget> getWidgetsByFilter( WidgetFilter wFilter, Plugin plugin )
310 {
311 List<Widget> widgetsList = new ArrayList<Widget>( );
312 StringBuilder sbSQL = new StringBuilder( buildSQLQuery( wFilter ) );
313 sbSQL.append( SQL_ORDER_BY + SQL_NAME + SQL_ASC );
314
315 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
316 setFilterValues( wFilter, daoUtil );
317 daoUtil.executeQuery( );
318
319 while ( daoUtil.next( ) )
320 {
321 int nIndex = 1;
322 Widgets/myportal/business/Widget.html#Widget">Widget widget = new Widget( );
323
324 widget.setIdWidget( daoUtil.getInt( nIndex++ ) );
325 widget.setName( daoUtil.getString( nIndex++ ) );
326 widget.setDescription( daoUtil.getString( nIndex++ ) );
327 widget.setIdCategory( daoUtil.getInt( nIndex++ ) );
328 widget.setWidgetType( daoUtil.getString( nIndex++ ) );
329 widget.setIdIcon( daoUtil.getInt( nIndex++ ) );
330 widget.setConfigData( new String( daoUtil.getString( nIndex++ ).getBytes( ) ) );
331 widget.setStatus( daoUtil.getInt( nIndex++ ) );
332 widget.setCategory( daoUtil.getString( nIndex++ ) );
333 widget.setIdStyle( daoUtil.getInt( nIndex++ ) );
334 widget.setStyle( daoUtil.getString( nIndex++ ) );
335 widget.setCssClass( daoUtil.getString( nIndex++ ) );
336 widget.setIsEssential( daoUtil.getBoolean( nIndex++ ) );
337 widget.setIsNew( daoUtil.getBoolean( nIndex++ ) );
338 widgetsList.add( widget );
339 }
340
341 daoUtil.free( );
342
343 return widgetsList;
344 }
345
346
347
348
349
350
351
352
353 private String buildSQLQuery( WidgetFilter wFilter )
354 {
355 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECTALL );
356 int nIndex = 1;
357
358 if ( wFilter.containsName( ) )
359 {
360 nIndex = addSQLWhereOr( wFilter.getIsWideSearch( ), sbSQL, nIndex );
361 sbSQL.append( SQL_FILTER_NAME );
362 }
363
364 if ( wFilter.containsDescription( ) )
365 {
366 nIndex = addSQLWhereOr( wFilter.getIsWideSearch( ), sbSQL, nIndex );
367 sbSQL.append( SQL_FILTER_DESCRIPTION );
368 }
369
370 if ( wFilter.containsIdCategory( ) )
371 {
372 nIndex = addSQLWhereOr( wFilter.getIsWideSearch( ), sbSQL, nIndex );
373 sbSQL.append( SQL_FILTER_ID_CATEGORY );
374 }
375
376 if ( wFilter.containsIdStyle( ) )
377 {
378 nIndex = addSQLWhereOr( wFilter.getIsWideSearch( ), sbSQL, nIndex );
379 sbSQL.append( SQL_FILTER_ID_STYLE );
380 }
381
382 if ( wFilter.containsWidgetType( ) )
383 {
384 nIndex = addSQLWhereOr( wFilter.getIsWideSearch( ), sbSQL, nIndex );
385 sbSQL.append( SQL_FILTER_WIDGET_TYPE );
386 }
387
388 if ( wFilter.containsStatus( ) )
389 {
390 nIndex = addSQLWhereOr( wFilter.getIsWideSearch( ), sbSQL, nIndex );
391 sbSQL.append( SQL_FILTER_STATUS );
392 }
393
394 if ( wFilter.containsIsEssential( ) )
395 {
396 nIndex = addSQLWhereOr( wFilter.getIsWideSearch( ), sbSQL, nIndex );
397 sbSQL.append( SQL_FILTER_IS_ESSENTIAL );
398 }
399
400 if ( wFilter.containsIsNew( ) )
401 {
402 nIndex = addSQLWhereOr( wFilter.getIsWideSearch( ), sbSQL, nIndex );
403 sbSQL.append( SQL_FILTER_IS_NEW );
404 }
405
406 return sbSQL.toString( );
407 }
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424 private int addSQLWhereOr( boolean bIsWideSearch, StringBuilder sbSQL, int nIndex )
425 {
426 if ( nIndex == 1 )
427 {
428 sbSQL.append( SQL_WHERE );
429 }
430 else
431 {
432 sbSQL.append( bIsWideSearch ? SQL_OR : SQL_AND );
433 }
434
435 return nIndex + 1;
436 }
437
438
439
440
441
442
443
444
445
446 private void setFilterValues( WidgetFilter wFilter, DAOUtil daoUtil )
447 {
448 int nIndex = 1;
449
450 if ( wFilter.containsName( ) )
451 {
452 daoUtil.setString( nIndex, PERCENT + wFilter.getName( ) + PERCENT );
453 nIndex++;
454 }
455
456 if ( wFilter.containsDescription( ) )
457 {
458 daoUtil.setString( nIndex, PERCENT + wFilter.getDescription( ) + PERCENT );
459 nIndex++;
460 }
461
462 if ( wFilter.containsIdCategory( ) )
463 {
464 daoUtil.setInt( nIndex, wFilter.getIdCategory( ) );
465 nIndex++;
466 }
467
468 if ( wFilter.containsIdStyle( ) )
469 {
470 daoUtil.setInt( nIndex, wFilter.getIdStyle( ) );
471 nIndex++;
472 }
473
474 if ( wFilter.containsWidgetType( ) )
475 {
476 daoUtil.setString( nIndex, PERCENT + wFilter.getWidgetType( ) + PERCENT );
477 nIndex++;
478 }
479
480 if ( wFilter.containsStatus( ) )
481 {
482 daoUtil.setInt( nIndex, wFilter.getStatus( ) );
483 nIndex++;
484 }
485
486 if ( wFilter.containsIsEssential( ) )
487 {
488 daoUtil.setBoolean( nIndex, wFilter.getIsEssential( ) == WidgetFilter.FILTER_TRUE );
489 nIndex++;
490 }
491
492 if ( wFilter.containsIsNew( ) )
493 {
494 daoUtil.setBoolean( nIndex, wFilter.getIsNew( ) == WidgetFilter.FILTER_TRUE );
495 nIndex++;
496 }
497 }
498 }