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.calendar.business;
35
36 import fr.paris.lutece.plugins.calendar.business.category.Category;
37 import fr.paris.lutece.plugins.calendar.business.category.CategoryHome;
38 import fr.paris.lutece.plugins.calendar.service.AgendaResource;
39 import fr.paris.lutece.plugins.calendar.service.Utils;
40 import fr.paris.lutece.plugins.calendar.web.Constants;
41 import fr.paris.lutece.portal.service.image.ImageResource;
42 import fr.paris.lutece.portal.service.plugin.Plugin;
43 import fr.paris.lutece.portal.service.util.AppLogService;
44 import fr.paris.lutece.portal.service.util.AppPropertiesService;
45 import fr.paris.lutece.util.sql.DAOUtil;
46
47 import java.sql.Date;
48 import java.util.ArrayList;
49 import java.util.Calendar;
50 import java.util.Collection;
51 import java.util.GregorianCalendar;
52 import java.util.List;
53
54 import org.apache.commons.lang.StringUtils;
55
56
57
58
59
60 public class CalendarDAO implements ICalendarDAO
61 {
62 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_agenda ) FROM calendar_agendas ";
63 private static final String SQL_QUERY_NEW_PK_EVENTS = " SELECT max( id_event ) FROM calendar_events ";
64 private static final String SQL_QUERY_INSERT_AGENDA = " INSERT INTO calendar_agendas ( id_agenda, agenda_name, agenda_image, agenda_prefix, role ,role_manage, workgroup_key, is_notify, period_validity) VALUES ( ?, ?, ?, ?, ?, ? ,?, ?, ? ) ";
65 private static final String SQL_QUERY_UPDATE_AGENDA = " UPDATE calendar_agendas SET agenda_name = ?, agenda_image = ?, agenda_prefix = ?, role = ?, role_manage = ?, workgroup_key = ?, is_notify = ?, period_validity = ? WHERE id_agenda = ? ";
66 private static final String SQL_QUERY_DELETE_AGENDA = " DELETE FROM calendar_agendas WHERE id_agenda = ? ";
67 private static final String SQL_QUERY_SELECT_AGENDA = "SELECT id_agenda, agenda_name, agenda_image, agenda_prefix, role, role_manage, workgroup_key, is_notify, period_validity FROM calendar_agendas WHERE id_agenda = ? ";
68 private static final String SQL_QUERY_SELECTALL_AGENDAS = "SELECT id_agenda, agenda_name, agenda_image, agenda_prefix, role,role_manage, workgroup_key, is_notify, period_validity FROM calendar_agendas ORDER BY agenda_name";
69 private static final String SQL_QUERY_INSERT_EVENT = " INSERT INTO calendar_events ( id_event, id_agenda, event_date, event_date_end, event_time_start, event_time_end, event_title, event_date_occurence, event_date_periodicity, event_date_creation, event_excluded_day ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
70 private static final String SQL_QUERY_UPDATE_EVENT = " UPDATE calendar_events SET id_agenda =?, event_date = ?, event_date_end = ? , event_time_start = ?, event_time_end = ?, event_title = ?, event_date_occurence = ?, event_date_periodicity = ?, event_excluded_day = ? WHERE id_event = ? ";
71 private static final String SQL_QUERY_UPDATE_EVENT_DATE = " UPDATE calendar_events SET event_date = ? WHERE id_event = ? AND id_agenda = ?";
72 private static final String SQL_QUERY_DELETE_EVENT = " DELETE FROM calendar_events WHERE id_agenda = ? AND id_event= ? ";
73 private static final String SQL_QUERY_SELECT_EVENT = "SELECT id_agenda, event_date, event_date_end, event_time_start, event_time_end, event_title, event_date_occurence, event_date_periodicity, event_date_creation, event_excluded_day FROM calendar_events WHERE id_event= ? ";
74 private static final String SQL_QUERY_SELECT_EVENTS = "SELECT id_event, id_agenda, event_date, event_date_end, event_time_start, event_time_end, event_title, event_date_occurence, event_date_periodicity, event_date_creation FROM calendar_events WHERE id_agenda = ? ORDER BY event_date ";
75 private static final String SQL_QUERY_NUMBER_DAYS_BY_EVENT = "SELECT event_date_periodicity FROM calendar_events WHERE id_event=?";
76 private static final String SQL_QUERY_SELECT_EVENTS_N_NEXT_DAYS = "SELECT DISTINCT ce.id_event, ce.id_agenda, ce.event_date, ce.event_date_end, ce.event_time_start, ce.event_time_end, ce.event_title, ce.event_date_occurence, ce.event_date_periodicity, ce.event_date_creation "
77 + " FROM calendar_events ce INNER JOIN calendar_events_occurrences ceo ON ce.id_event = ceo.id_event "
78 + " WHERE ceo.id_agenda = ? AND ceo.occurrence_date >= ? AND ceo.occurrence_date <= ? ORDER BY ce.event_title ";
79
80
81 private static final String SQL_QUERY_NEW_PK_OCCURRENCE = " SELECT max( id_occurrence ) FROM calendar_events_occurrences ";
82 private static final String SQL_QUERY_INSERT_OCCURRENCE = " INSERT INTO calendar_events_occurrences ( id_occurrence, id_event, id_agenda, occurrence_date, occurrence_time_start, occurrence_time_end, occurrence_title, occurrence_status) VALUES ( ?, ?, ?, ?, ?, ?, ?,? ) ";
83 private static final String SQL_QUERY_UPDATE_EVENT_OCCURRENCE_NUMBER = " UPDATE calendar_events SET event_date_occurence = ? WHERE id_event = ? AND id_agenda = ?";
84 private static final String SQL_QUERY_UPDATE_OCCURRENCE = " UPDATE calendar_events_occurrences SET occurrence_date = ?, occurrence_time_start = ?, occurrence_time_end = ?, occurrence_title = ?, occurrence_status = ? WHERE id_occurrence = ? ";
85 private static final String SQL_QUERY_DELETE_ALL_OCCURRENCE = " DELETE FROM calendar_events_occurrences WHERE id_agenda = ? AND id_event= ? ";
86 private static final String SQL_QUERY_DELETE_OCCURRENCE = " DELETE FROM calendar_events_occurrences WHERE id_occurrence = ? ";
87 private static final String SQL_QUERY_SELECT_OCCURRENCE = "SELECT a.id_event, a.occurrence_date, b.event_date_end, a.occurrence_time_start, a.occurrence_time_end, a.occurrence_title, a.occurrence_status"
88 + " FROM calendar_events_occurrences a, calendar_events b"
89 + " WHERE a.id_occurrence = ? and a.id_event = b.id_event";
90 private static final String SQL_QUERY_SELECT_OCCURRENCE_DATE_MIN = "SELECT MIN(occurrence_date) FROM calendar_events_occurrences WHERE id_event = ? ";
91 private static final String SQL_QUERY_SELECT_OCCURRENCES = "SELECT a.id_occurrence, b.id_event , a.occurrence_date, b.event_date_end, a.occurrence_time_start, a.occurrence_time_end, a.occurrence_title, a.occurrence_status"
92 + " FROM calendar_events_occurrences a, calendar_events b"
93 + " WHERE a.id_agenda = ? and a.id_event = ? and a.id_event = b.id_event " + " ORDER BY a.occurrence_date ";
94 private static final String SQL_QUERY_SELECT_ALL_OCCURRENCES = "SELECT a.id_occurrence, a.id_event , a.occurrence_date, b.event_date_end, a.occurrence_time_start, a.occurrence_time_end, a.occurrence_title, a.occurrence_status"
95 + " FROM calendar_events_occurrences a, calendar_events b"
96 + " WHERE a.id_agenda = ? and a.id_event = b.id_event ORDER BY a.occurrence_date ";
97 private static final String SQL_QUERY_SELECT_ALL_OCCURRENCES2 = "SELECT a.id_occurrence, a.id_event , a.occurrence_date, b.event_date_end, a.occurrence_time_start, a.occurrence_time_end, a.occurrence_title, a.occurrence_status"
98 + " FROM calendar_events_occurrences a, calendar_events b"
99 + " WHERE a.id_agenda = ? and a.id_event = b.id_event ORDER BY a.id_occurrence ";
100 private static final String SQL_QUERY_SELECT_NUMBER_OCCURRENCE = "SELECT count(id_event) FROM calendar_events_occurrences WHERE id_event=?";
101 private static final String SQL_QUERY_NEW_PK_FEATURE = " SELECT max( id_feature ) FROM calendar_events_features ";
102 private static final String SQL_QUERY_SELECT_FEATURE = " SELECT feature_description, feature_location, feature_location_town, feature_location_zip, feature_location_address, feature_map_url, feature_link_url, "
103 + " document_id, feature_page_url, feature_top_event, feature_image, image_mime_type, feature_tags from calendar_events_features fe where fe.id_event = ? ";
104 private static final String SQL_QUERY_INSERT_FEATURE = " INSERT INTO calendar_events_features ( id_feature , id_event , feature_description , feature_location, feature_location_town , feature_location_zip , "
105 + " feature_location_address , feature_map_url , feature_link_url , document_id , feature_page_url , feature_top_event, feature_image, image_mime_type, feature_tags ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
106 private static final String SQL_QUERY_DELETE_FEATURE = " DELETE FROM calendar_events_features WHERE id_event = ? ";
107 private static final String SQL_QUERY_UPDATE_FEATURE = " UPDATE calendar_events_features SET feature_description = ?, feature_location= ? , feature_location_town = ?, feature_location_zip = ?,"
108 + "feature_location_address = ?, feature_map_url = ?, feature_link_url = ?, document_id = ?, feature_page_url = ?, feature_top_event = ?, feature_image = ?, image_mime_type = ?, feature_tags = ? WHERE id_event = ?";
109 private static final String SQL_QUERY_INSERT_LINK_CATEGORY_CALENDAR = " INSERT INTO calendar_category_link ( id_category, id_event ) VALUES ( ?, ? )";
110 private static final String SQL_QUERY_DELETE_LINK_CATEGORY_CALENDAR = " DELETE FROM calendar_category_link WHERE id_event = ?";
111 private static final String SQL_QUERY_SELECT_EVENTS_BY_USER_LOGIN = " SELECT a.id_event, a.id_agenda, a.event_date, a.event_date_end, a.event_time_start, a.event_time_end, a.event_title, a.event_date_occurence, a.event_date_periodicity, a.event_date_creation "
112 + " FROM calendar_events a INNER JOIN calendar_events_users b ON a.id_event = b.id_event "
113 + " WHERE a.id_agenda = ? AND b.user_login = ? ORDER BY a.event_date ";
114 private static final String SQL_QUERY_INSERT_EVENT_USER = " INSERT INTO calendar_events_users ( id_event, user_login ) VALUES ( ?, ? ) ";
115 private static final String SQL_QUERY_DELETE_EVENT_USER = " DELETE FROM calendar_events_users WHERE id_event = ? ";
116 private static final String SQL_QUERY_SELECT_AGENDA_IDS = " SELECT id_agenda FROM calendar_agendas ORDER BY id_agenda ASC ";
117
118
119 private static final String SQL_QUERY_SELECT_RESOURCE_IMAGE = " SELECT feature_image, image_mime_type FROM calendar_events_features WHERE id_event = ? ";
120
121
122 private static final String SQL_QUERY_SELECT_BY_FILTER = "SELECT a.id_event, a.id_agenda, a.event_date, a.event_date_end, a.event_time_start, a.event_time_end, a.event_title, a.event_date_occurence, "
123 + "a.event_date_periodicity, a.event_date_creation "
124 + "FROM calendar_events a "
125 + "LEFT OUTER JOIN calendar_category_link b ON a.id_event = b.id_event";
126 private static final String SQL_FILTER_WHERE_CLAUSE = " WHERE ";
127 private static final String SQL_FILTER_AND = " AND ";
128 private static final String SQL_FILTER_CALENDAR = "a.id_agenda = ?";
129 private static final String SQL_FILTER_CATEGORIES_BEGIN = " (";
130 private static final String SQL_FILTER_CATEGORIES = " b.id_category = ? ";
131 private static final String SQL_FILTER_CATEGORIES_OR = " OR ";
132 private static final String SQL_FILTER_CATEGORIES_END = ") ";
133 private static final String SQL_FILTER_ID_BEGIN = " (";
134 private static final String SQL_FILTER_ID = " a.id_event = ? ";
135 private static final String SQL_FILTER_ID_OR = " OR ";
136 private static final String SQL_FILTER_ID_END = ") ";
137 private static final String SQL_ORDER_BY_EVENTS = " ORDER BY a.event_date";
138 private static final String SQL_FILTER_CALENDAR_ID = " a.id_agenda = ? ";
139 private static final String SQL_FILTER_ASC = " ASC ";
140 private static final String SQL_FILTER_DESC = " DESC ";
141
142
143 private static final String SQL_QUERY_HAS_EVENT = "SELECT id_occurrence FROM calendar_events_occurrences WHERE occurrence_date = ?";
144
145
146 private static final String SQL_QUERY_SELECT_TOP_EVENTS = "SELECT a.id_event, id_agenda, event_date, event_date_end, event_time_start, event_time_end, event_title, event_date_occurence, event_date_periodicity, event_date_creation"
147 + " FROM calendar_events a, calendar_events_features b"
148 + " WHERE a.id_event = b.id_event AND b.feature_top_event = 1 ORDER BY event_date ";
149
150
151
152
153
154
155
156 public void insertAgenda( AgendaResource agenda, Plugin plugin )
157 {
158 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_AGENDA, plugin );
159 agenda.setId( String.valueOf( getNewPrimaryKey( plugin, SQL_QUERY_NEW_PK ) ) );
160 daoUtil.setInt( 1, Integer.parseInt( agenda.getId( ) ) );
161 daoUtil.setString( 2, agenda.getName( ) );
162 daoUtil.setString( 3, agenda.getEventImage( ) );
163 daoUtil.setString( 4, agenda.getEventPrefix( ) );
164 daoUtil.setString( 5, agenda.getRole( ) );
165 daoUtil.setString( 6, agenda.getRoleManager( ) );
166 daoUtil.setString( 7, agenda.getWorkgroup( ) );
167 daoUtil.setBoolean( 8, agenda.isNotify( ) );
168 daoUtil.setInt( 9, agenda.getPeriodValidity( ) );
169
170 daoUtil.executeUpdate( );
171 daoUtil.free( );
172 }
173
174
175
176
177
178
179 public void storeAgenda( AgendaResource agenda, Plugin plugin )
180 {
181 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_AGENDA, plugin );
182 daoUtil.setString( 1, agenda.getName( ) );
183 daoUtil.setString( 2, agenda.getEventImage( ) );
184 daoUtil.setString( 3, agenda.getEventPrefix( ) );
185 daoUtil.setString( 4, agenda.getRole( ) );
186 daoUtil.setString( 5, agenda.getRoleManager( ) );
187 daoUtil.setString( 6, agenda.getWorkgroup( ) );
188 daoUtil.setBoolean( 7, agenda.isNotify( ) );
189 daoUtil.setInt( 8, agenda.getPeriodValidity( ) );
190 daoUtil.setInt( 9, Integer.parseInt( agenda.getId( ) ) );
191
192 daoUtil.executeUpdate( );
193 daoUtil.free( );
194 }
195
196
197
198
199
200
201 public void deleteAgenda( int nAgendaId, Plugin plugin )
202 {
203 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_AGENDA, plugin );
204 daoUtil.setInt( 1, nAgendaId );
205 daoUtil.executeUpdate( );
206 daoUtil.free( );
207 }
208
209
210
211
212
213
214
215 public void insertEvent( SimpleEvent event, Plugin plugin, String strUserLogin )
216 {
217 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_EVENT, plugin );
218 event.setId( getNewPrimaryKey( plugin, SQL_QUERY_NEW_PK_EVENTS ) );
219 daoUtil.setInt( 1, event.getId( ) );
220 daoUtil.setInt( 2, event.getIdCalendar( ) );
221 daoUtil.setDate( 3, new java.sql.Date( event.getDate( ).getTime( ) ) );
222 daoUtil.setDate( 4, new java.sql.Date( event.getDateEnd( ).getTime( ) ) );
223 daoUtil.setString( 5, event.getDateTimeStart( ) );
224 daoUtil.setString( 6, event.getDateTimeEnd( ) );
225 daoUtil.setString( 7, event.getTitle( ) );
226 daoUtil.setInt( 8, event.getOccurrence( ) );
227 daoUtil.setInt( 9, event.getPeriodicity( ) );
228 daoUtil.setTimestamp( 10, new java.sql.Timestamp( new java.util.Date( ).getTime( ) ) );
229 String[] arrayExcludedDays = event.getExcludedDays( );
230 if ( arrayExcludedDays != null && arrayExcludedDays.length != 0 )
231 {
232 StringBuilder sbExcludedDays = new StringBuilder( );
233 for ( int i = 0; i < arrayExcludedDays.length - 1; i++ )
234 {
235 sbExcludedDays.append( arrayExcludedDays[i] + Constants.COMMA );
236 }
237 sbExcludedDays.append( arrayExcludedDays[arrayExcludedDays.length - 1] );
238 daoUtil.setString( 11, sbExcludedDays.toString( ) );
239 }
240 else
241 {
242 daoUtil.setString( 11, Constants.EMPTY_STRING );
243 }
244 daoUtil.executeUpdate( );
245 daoUtil.free( );
246
247
248 insertOccurrence( event, plugin );
249
250 insertFeature( plugin, event );
251
252 insertLinkCategories( event.getListCategories( ), event.getId( ), plugin );
253
254 if ( StringUtils.isNotBlank( strUserLogin ) )
255 {
256 daoUtil = new DAOUtil( SQL_QUERY_INSERT_EVENT_USER, plugin );
257
258 daoUtil.setInt( 1, event.getId( ) );
259 daoUtil.setString( 2, strUserLogin );
260 daoUtil.executeUpdate( );
261 daoUtil.free( );
262 }
263 }
264
265
266
267
268
269
270
271 public void storeEvent( SimpleEvent event, Plugin plugin, boolean bPeriodiciteUpdated )
272 {
273 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_EVENT, plugin );
274 daoUtil.setInt( 1, event.getIdCalendar( ) );
275 daoUtil.setDate( 2, new java.sql.Date( event.getDate( ).getTime( ) ) );
276
277 if ( event.getDateEnd( ) != null )
278 {
279 daoUtil.setDate( 3, new java.sql.Date( event.getDateEnd( ).getTime( ) ) );
280 }
281 else
282 {
283 daoUtil.setDate( 3, null );
284 }
285
286 daoUtil.setString( 4, event.getDateTimeStart( ) );
287 daoUtil.setString( 5, event.getDateTimeEnd( ) );
288 daoUtil.setString( 6, event.getTitle( ) );
289 daoUtil.setInt( 7, event.getOccurrence( ) );
290 daoUtil.setInt( 8, event.getPeriodicity( ) );
291 String[] arrayExcludedDays = event.getExcludedDays( );
292 if ( arrayExcludedDays != null && arrayExcludedDays.length != 0 )
293 {
294 StringBuilder sbExcludedDays = new StringBuilder( );
295 for ( int i = 0; i < arrayExcludedDays.length - 1; i++ )
296 {
297 sbExcludedDays.append( arrayExcludedDays[i] + Constants.COMMA );
298 }
299 sbExcludedDays.append( arrayExcludedDays[arrayExcludedDays.length - 1] );
300 daoUtil.setString( 9, sbExcludedDays.toString( ) );
301 }
302 else
303 {
304 daoUtil.setString( 9, Constants.EMPTY_STRING );
305 }
306 daoUtil.setInt( 10, event.getId( ) );
307 daoUtil.executeUpdate( );
308 daoUtil.free( );
309
310 if ( bPeriodiciteUpdated )
311 {
312 deleteAllOccurrence( event.getIdCalendar( ), event.getId( ), plugin );
313 insertOccurrence( event, plugin );
314 }
315
316
317 updateFeature( plugin, event );
318
319
320 deleteLinkCategories( plugin, event.getId( ) );
321 insertLinkCategories( event.getListCategories( ), event.getId( ), plugin );
322 }
323
324
325
326
327
328
329
330 public void deleteEvent( int nAgendaId, int nEventId, Plugin plugin )
331 {
332 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_EVENT, plugin );
333 daoUtil.setInt( 1, nAgendaId );
334 daoUtil.setInt( 2, nEventId );
335 daoUtil.executeUpdate( );
336 daoUtil.free( );
337
338 deleteAllOccurrence( nAgendaId, nEventId, plugin );
339 deleteFeature( plugin, nEventId );
340 deleteLinkCategories( plugin, nEventId );
341 deleteEventUser( nEventId, plugin );
342 }
343
344
345
346
347
348
349
350
351
352 public AgendaResource loadAgenda( int nId, Plugin plugin )
353 {
354 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_AGENDA, plugin );
355 daoUtil.setInt( 1, nId );
356 daoUtil.executeQuery( );
357
358 AgendaResource agenda = null;
359
360 if ( daoUtil.next( ) )
361 {
362 agenda = new AgendaResource( );
363 agenda.setId( String.valueOf( daoUtil.getInt( 1 ) ) );
364 agenda.setName( daoUtil.getString( 2 ) );
365 agenda.setEventImage( daoUtil.getString( 3 ) );
366 agenda.setEventPrefix( daoUtil.getString( 4 ) );
367 agenda.setRole( daoUtil.getString( 5 ) );
368 agenda.setRoleManager( daoUtil.getString( 6 ) );
369 agenda.setWorkgroup( daoUtil.getString( 7 ) );
370 agenda.setNotify( daoUtil.getBoolean( 8 ) );
371 agenda.setPeriodValidity( daoUtil.getInt( 9 ) );
372 }
373
374 daoUtil.free( );
375
376 return agenda;
377 }
378
379
380
381
382
383
384
385 public List<AgendaResource> selectAgendaResourceList( Plugin plugin )
386 {
387 List<AgendaResource> agendaList = new ArrayList<AgendaResource>( );
388 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_AGENDAS, plugin );
389 daoUtil.executeQuery( );
390
391 while ( daoUtil.next( ) )
392 {
393 AgendaResource agenda = new AgendaResource( );
394 agenda.setId( String.valueOf( daoUtil.getInt( 1 ) ) );
395 agenda.setName( daoUtil.getString( 2 ) );
396 agenda.setEventImage( daoUtil.getString( 3 ) );
397 agenda.setEventPrefix( daoUtil.getString( 4 ) );
398 agenda.setRole( daoUtil.getString( 5 ) );
399 agenda.setRoleManager( daoUtil.getString( 6 ) );
400 agenda.setWorkgroup( daoUtil.getString( 7 ) );
401 agenda.setNotify( daoUtil.getBoolean( 8 ) );
402 agenda.setPeriodValidity( daoUtil.getInt( 9 ) );
403
404 agendaList.add( agenda );
405 }
406
407 daoUtil.free( );
408
409 return agendaList;
410 }
411
412
413
414
415
416
417
418 public SimpleEvent loadEvent( int nEventId, Plugin plugin )
419 {
420 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_EVENT, plugin );
421 daoUtil.setInt( 1, nEventId );
422 daoUtil.executeQuery( );
423
424 SimpleEvent event = null;
425
426 if ( daoUtil.next( ) )
427 {
428 event = new SimpleEvent( );
429 event.setId( nEventId );
430 event.setIdCalendar( daoUtil.getInt( 1 ) );
431 event.setDate( new java.util.Date( daoUtil.getDate( 2 ).getTime( ) ) );
432
433 if ( daoUtil.getDate( 2 ) != null )
434 {
435 event.setDateEnd( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
436 }
437
438 event.setDateTimeStart( daoUtil.getString( 4 ) );
439 event.setDateTimeEnd( daoUtil.getString( 5 ) );
440 event.setTitle( daoUtil.getString( 6 ) );
441 event.setOccurrence( daoUtil.getInt( 7 ) );
442 event.setPeriodicity( daoUtil.getInt( 8 ) );
443 event.setDateCreation( daoUtil.getTimestamp( 9 ) );
444 if ( daoUtil.getString( 10 ) != null )
445 {
446 String[] listExcludedDays = daoUtil.getString( 10 ).split( Constants.COMMA );
447 event.setExcludedDays( listExcludedDays );
448 }
449 else
450 {
451 String[] listExcludedDays = {};
452 event.setExcludedDays( listExcludedDays );
453 }
454 }
455
456 daoUtil.free( );
457
458 if ( event != null )
459 {
460 getFeature( plugin, event.getId( ), event );
461 event.setListCategories( CategoryHome.findByEvent( event.getId( ), plugin ) );
462 }
463
464 return event;
465 }
466
467
468
469
470
471
472
473
474 public List<SimpleEvent> selectEventsList( int nAgendaId, int nSortEvents, Plugin plugin )
475 {
476 List<SimpleEvent> eventList = new ArrayList<SimpleEvent>( );
477 String strSortEvents = null;
478
479 if ( nSortEvents == 1 )
480 {
481 strSortEvents = "ASC";
482 }
483 else
484 {
485 strSortEvents = "DESC";
486 }
487
488 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_EVENTS + strSortEvents, plugin );
489 daoUtil.setInt( 1, nAgendaId );
490 daoUtil.executeQuery( );
491
492 while ( daoUtil.next( ) )
493 {
494 SimpleEvent event = new SimpleEvent( );
495 event.setId( daoUtil.getInt( 1 ) );
496 event.setIdCalendar( daoUtil.getInt( 2 ) );
497 event.setDate( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
498 event.setDateEnd( new java.util.Date( daoUtil.getDate( 4 ).getTime( ) ) );
499 event.setDateTimeStart( daoUtil.getString( 5 ) );
500 event.setDateTimeEnd( daoUtil.getString( 6 ) );
501 event.setTitle( daoUtil.getString( 7 ) );
502 event.setOccurrence( daoUtil.getInt( 8 ) );
503 event.setPeriodicity( daoUtil.getInt( 9 ) );
504 event.setDateCreation( daoUtil.getTimestamp( 10 ) );
505 getFeature( plugin, event.getId( ), event );
506 event.setListCategories( CategoryHome.findByEvent( event.getId( ), plugin ) );
507 eventList.add( event );
508 }
509
510 daoUtil.free( );
511
512 return eventList;
513 }
514
515
516
517
518
519
520
521
522
523 int getNewPrimaryKey( Plugin plugin, String strSqlQuery )
524 {
525 DAOUtil daoUtil = new DAOUtil( strSqlQuery, plugin );
526 daoUtil.executeQuery( );
527
528 int nKey;
529
530 if ( !daoUtil.next( ) )
531 {
532
533 nKey = 1;
534 }
535
536 nKey = daoUtil.getInt( 1 ) + 1;
537
538 daoUtil.free( );
539
540 return nKey;
541 }
542
543
544
545
546
547
548 public void insertOccurrence( SimpleEvent event, Plugin plugin )
549 {
550 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_OCCURRENCE, plugin );
551
552
553 int nIdOccurrence = 1;
554 int i = 0;
555 int index = 0;
556
557
558 String strDateReference = Utils.getDate( event.getDate( ) );
559
560
561 String strDefaultStatus = AppPropertiesService.getProperty( Constants.PROPERTY_EVENT_DEFAULT_STATUS );
562
563 while ( index < event.getOccurrence( ) )
564 {
565 if ( event.getExcludedDays( ) != null && event.getExcludedDays( ).length == 7 )
566 {
567 break;
568 }
569
570 Date date = new java.sql.Date( event.getDate( ).getTime( ) );
571 String strDate = Utils.getDate( date );
572 if ( !Utils.isDayExcluded( Utils.getDayOfWeek( strDate ), event.getExcludedDays( ) ) )
573 {
574 nIdOccurrence = getNewPrimaryKey( plugin, SQL_QUERY_NEW_PK_OCCURRENCE );
575 daoUtil.setInt( 1, nIdOccurrence );
576 daoUtil.setInt( 2, event.getId( ) );
577 daoUtil.setInt( 3, event.getIdCalendar( ) );
578 daoUtil.setDate( 4, date );
579 daoUtil.setString( 5, event.getDateTimeStart( ) );
580 daoUtil.setString( 6, event.getDateTimeEnd( ) );
581 daoUtil.setString( 7, event.getTitle( ) );
582 daoUtil.setString( 8, StringUtils.isNotBlank( event.getStatus( ) ) ? event.getStatus( )
583 : strDefaultStatus );
584 daoUtil.executeUpdate( );
585 index++;
586 }
587 i++;
588
589 event = getNextOccurrence( event, strDateReference, i );
590 }
591
592 daoUtil.free( );
593 }
594
595
596
597
598
599
600
601 public void storeOccurrence( OccurrenceEvent occurrence, Plugin plugin )
602 {
603 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_OCCURRENCE, plugin );
604 daoUtil.setDate( 1, new java.sql.Date( occurrence.getDate( ).getTime( ) ) );
605 daoUtil.setString( 2, occurrence.getDateTimeStart( ) );
606 daoUtil.setString( 3, occurrence.getDateTimeEnd( ) );
607 daoUtil.setString( 4, occurrence.getTitle( ) );
608 daoUtil.setString( 5, occurrence.getStatus( ) );
609 daoUtil.setInt( 6, occurrence.getId( ) );
610 daoUtil.executeUpdate( );
611 daoUtil.free( );
612
613 Date newDateEvent = selectOccurrenceDateMin( occurrence.getEventId( ), plugin );
614 updateDateEvent( occurrence.getEventId( ), occurrence.getIdCalendar( ), plugin, newDateEvent );
615 }
616
617
618
619
620
621
622
623
624
625
626 public List<OccurrenceEvent> selectOccurrencesList( int nAgendaId, int nEventId, int nSortEvents, Plugin plugin )
627 {
628 List<OccurrenceEvent> occurrenceList = new ArrayList<OccurrenceEvent>( );
629 String strSortEvents = null;
630
631 if ( nSortEvents == 1 )
632 {
633 strSortEvents = "ASC";
634 }
635 else
636 {
637 strSortEvents = "DESC";
638 }
639
640 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_OCCURRENCES + strSortEvents, plugin );
641 daoUtil.setInt( 1, nAgendaId );
642 daoUtil.setInt( 2, nEventId );
643 daoUtil.executeQuery( );
644
645 while ( daoUtil.next( ) )
646 {
647 OccurrenceEvent occurrence = new OccurrenceEvent( );
648 occurrence.setId( daoUtil.getInt( 1 ) );
649 occurrence.setEventId( daoUtil.getInt( 2 ) );
650 occurrence.setDate( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
651 occurrence.setDateEnd( new java.util.Date( daoUtil.getDate( 4 ).getTime( ) ) );
652 occurrence.setDateTimeStart( daoUtil.getString( 5 ) );
653 occurrence.setDateTimeEnd( daoUtil.getString( 6 ) );
654 occurrence.setTitle( daoUtil.getString( 7 ) );
655 occurrence.setStatus( daoUtil.getString( 8 ) );
656 getFeature( plugin, occurrence.getEventId( ), occurrence );
657 occurrence.setListCategories( CategoryHome.findByEvent( occurrence.getEventId( ), plugin ) );
658 occurrenceList.add( occurrence );
659 }
660
661 daoUtil.free( );
662
663 return occurrenceList;
664 }
665
666
667
668
669
670
671
672
673
674 public List<OccurrenceEvent> selectOccurrencesList( int nAgendaId, int nSortEvents, Plugin plugin )
675 {
676 List<OccurrenceEvent> occurrenceList = new ArrayList<OccurrenceEvent>( );
677 String strSortEvents = null;
678
679 if ( nSortEvents == 1 )
680 {
681 strSortEvents = "ASC";
682 }
683 else
684 {
685 strSortEvents = "DESC";
686 }
687
688 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_OCCURRENCES + strSortEvents, plugin );
689 daoUtil.setInt( 1, nAgendaId );
690 daoUtil.executeQuery( );
691
692 while ( daoUtil.next( ) )
693 {
694 OccurrenceEvent occurrence = new OccurrenceEvent( );
695 occurrence.setId( daoUtil.getInt( 1 ) );
696 occurrence.setEventId( daoUtil.getInt( 2 ) );
697 occurrence.setDate( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
698 occurrence.setDateEnd( new java.util.Date( daoUtil.getDate( 4 ).getTime( ) ) );
699 occurrence.setDateTimeStart( daoUtil.getString( 5 ) );
700 occurrence.setDateTimeEnd( daoUtil.getString( 6 ) );
701 occurrence.setTitle( daoUtil.getString( 7 ) );
702 occurrence.setStatus( daoUtil.getString( 8 ) );
703 getFeature( plugin, occurrence.getEventId( ), occurrence );
704 occurrence.setListCategories( CategoryHome.findByEvent( occurrence.getEventId( ), plugin ) );
705 occurrenceList.add( occurrence );
706 }
707
708 daoUtil.free( );
709
710 return occurrenceList;
711 }
712
713
714
715
716
717
718
719
720 public List<OccurrenceEvent> selectOccurrencesByIdList( int nAgendaId, Plugin plugin )
721 {
722 List<OccurrenceEvent> occurrenceList = new ArrayList<OccurrenceEvent>( );
723
724 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_OCCURRENCES2, plugin );
725 daoUtil.setInt( 1, nAgendaId );
726 daoUtil.executeQuery( );
727
728 while ( daoUtil.next( ) )
729 {
730 OccurrenceEvent occurrence = new OccurrenceEvent( );
731 occurrence.setId( daoUtil.getInt( 1 ) );
732 occurrence.setEventId( daoUtil.getInt( 2 ) );
733 occurrence.setDate( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
734 occurrence.setDateEnd( new java.util.Date( daoUtil.getDate( 4 ).getTime( ) ) );
735 occurrence.setDateTimeStart( daoUtil.getString( 5 ) );
736 occurrence.setDateTimeEnd( daoUtil.getString( 6 ) );
737 occurrence.setTitle( daoUtil.getString( 7 ) );
738 occurrence.setStatus( daoUtil.getString( 8 ) );
739 getFeature( plugin, occurrence.getEventId( ), occurrence );
740 occurrenceList.add( occurrence );
741 }
742
743 daoUtil.free( );
744
745 return occurrenceList;
746 }
747
748
749
750
751
752
753
754 public OccurrenceEvent loadOccurrence( int nOccurenceId, Plugin plugin )
755 {
756 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_OCCURRENCE, plugin );
757 daoUtil.setInt( 1, nOccurenceId );
758 daoUtil.executeQuery( );
759
760 OccurrenceEvent occurrence = null;
761
762 if ( daoUtil.next( ) )
763 {
764 occurrence = new OccurrenceEvent( );
765 occurrence.setId( nOccurenceId );
766 occurrence.setEventId( daoUtil.getInt( 1 ) );
767 occurrence.setDate( new java.util.Date( daoUtil.getDate( 2 ).getTime( ) ) );
768 occurrence.setDateEnd( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
769 occurrence.setDateTimeStart( daoUtil.getString( 4 ) );
770 occurrence.setDateTimeEnd( daoUtil.getString( 5 ) );
771 occurrence.setTitle( daoUtil.getString( 6 ) );
772 occurrence.setStatus( daoUtil.getString( 7 ) );
773 getFeature( plugin, occurrence.getEventId( ), occurrence );
774 }
775
776 daoUtil.free( );
777
778 return occurrence;
779 }
780
781
782
783
784
785
786
787 public void deleteAllOccurrence( int nAgendaId, int nEventId, Plugin plugin )
788 {
789 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL_OCCURRENCE, plugin );
790 daoUtil.setInt( 1, nAgendaId );
791 daoUtil.setInt( 2, nEventId );
792 daoUtil.executeUpdate( );
793 daoUtil.free( );
794 }
795
796
797
798
799
800
801
802
803 public void deleteOccurrence( int nOccurrenceId, int nEventId, int nAgendaId, Plugin plugin )
804 {
805 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_OCCURRENCE, plugin );
806 daoUtil.setInt( 1, nOccurrenceId );
807 daoUtil.executeUpdate( );
808 daoUtil.free( );
809
810
811
812
813
814
815
816
817
818
819
820
821 }
822
823
824
825
826
827
828
829
830 public void updateDateEvent( int nEventId, int nAgendaId, Plugin plugin, Date newDateEvent )
831 {
832 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_EVENT_DATE, plugin );
833 daoUtil.setDate( 1, newDateEvent );
834 daoUtil.setInt( 2, nEventId );
835 daoUtil.setInt( 3, nAgendaId );
836 daoUtil.executeUpdate( );
837 daoUtil.free( );
838 }
839
840
841
842
843
844
845
846
847 public Date selectOccurrenceDateMin( int nIdEvent, Plugin plugin )
848 {
849 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_OCCURRENCE_DATE_MIN, plugin );
850 daoUtil.setInt( 1, nIdEvent );
851 daoUtil.executeQuery( );
852
853 Date newDateEvent = null;
854
855 while ( daoUtil.next( ) )
856 {
857 newDateEvent = daoUtil.getDate( 1 );
858 }
859
860 daoUtil.free( );
861
862 return newDateEvent;
863 }
864
865
866
867
868
869
870
871
872 public void updateNumberOccurrence( int nEventId, int nAgendaId, Plugin plugin, int nNewNumberOccurrence )
873 {
874 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_EVENT_OCCURRENCE_NUMBER, plugin );
875 daoUtil.setInt( 1, nNewNumberOccurrence );
876 daoUtil.setInt( 2, nEventId );
877 daoUtil.setInt( 3, nAgendaId );
878 daoUtil.executeUpdate( );
879 daoUtil.free( );
880 }
881
882
883
884
885
886
887
888 public int getRepetitionDays( int nEventId, Plugin plugin )
889 {
890 int nNumberDays = 0;
891 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NUMBER_DAYS_BY_EVENT, plugin );
892 daoUtil.setInt( 1, nEventId );
893 daoUtil.executeQuery( );
894
895 while ( daoUtil.next( ) )
896 {
897 nNumberDays = daoUtil.getInt( 1 );
898 }
899
900 daoUtil.free( );
901
902 return nNumberDays;
903 }
904
905
906
907
908
909
910
911 public int getOccurrenceNumber( int nEventId, Plugin plugin )
912 {
913 int nNumberDays = 0;
914 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NUMBER_OCCURRENCE, plugin );
915 daoUtil.setInt( 1, nEventId );
916 daoUtil.executeQuery( );
917
918 while ( daoUtil.next( ) )
919 {
920 nNumberDays = daoUtil.getInt( 1 );
921 }
922
923 daoUtil.free( );
924
925 return nNumberDays;
926 }
927
928
929
930
931
932
933
934
935 public SimpleEvent getNextOccurrence( SimpleEvent occurrence, String strDateRef, int nCptDate )
936 {
937 int nPeriodicity = occurrence.getPeriodicity( );
938 String strDateOccrurrence = Utils.getDate( occurrence.getDate( ) );
939 String strNewDateOccurrence = "";
940
941 switch ( nPeriodicity )
942 {
943 case Constants.PARAM_DAY:
944 strNewDateOccurrence = Utils.getNextDay( strDateOccrurrence );
945 occurrence.setDate( Utils.getDate( strNewDateOccurrence ) );
946
947 break;
948
949 case Constants.PARAM_WEEK:
950 strNewDateOccurrence = Utils.getNextWeek( strDateOccrurrence );
951 occurrence.setDate( Utils.getDate( strNewDateOccurrence ) );
952
953 break;
954
955 case Constants.PARAM_MONTH:
956 strNewDateOccurrence = Utils.getNextMonth( strDateRef, nCptDate );
957 occurrence.setDate( Utils.getDate( strNewDateOccurrence ) );
958
959 break;
960
961 default:
962 strNewDateOccurrence = Utils.getNextDay( strDateOccrurrence );
963 occurrence.setDate( Utils.getDate( strNewDateOccurrence ) );
964
965 break;
966 }
967
968 return occurrence;
969 }
970
971
972
973
974
975
976 private void insertFeature( Plugin plugin, SimpleEvent event )
977 {
978 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_FEATURE, plugin );
979 int nIdFeature = getNewPrimaryKey( plugin, SQL_QUERY_NEW_PK_FEATURE );
980 daoUtil.setInt( 1, nIdFeature );
981 daoUtil.setInt( 2, event.getId( ) );
982 daoUtil.setString( 3, event.getDescription( ) );
983 daoUtil.setString( 4, event.getLocation( ) );
984 daoUtil.setString( 5, event.getLocationTown( ) );
985 daoUtil.setString( 6, event.getLocationZip( ) );
986 daoUtil.setString( 7, event.getLocationAddress( ) );
987 daoUtil.setString( 8, event.getMapUrl( ) );
988 daoUtil.setString( 9, event.getLinkUrl( ) );
989 daoUtil.setInt( 10, event.getDocumentId( ) );
990 daoUtil.setString( 11, event.getPageUrl( ) );
991 daoUtil.setInt( 12, event.getTopEvent( ) );
992
993 ImageResource imageResource = event.getImageResource( );
994
995 if ( imageResource != null )
996 {
997 daoUtil.setBytes( 13, imageResource.getImage( ) );
998 daoUtil.setString( 14, imageResource.getMimeType( ) );
999 }
1000 else
1001 {
1002 daoUtil.setBytes( 13, null );
1003 daoUtil.setString( 14, null );
1004 }
1005
1006 String[] listTags = event.getTags( );
1007 StringBuffer strTags = new StringBuffer( );
1008
1009 if ( listTags != null )
1010 {
1011 for ( String tags : listTags )
1012 {
1013 strTags.append( tags + Constants.SPACE );
1014 }
1015 }
1016
1017 daoUtil.setString( 15, strTags.toString( ) );
1018 daoUtil.executeUpdate( );
1019 daoUtil.free( );
1020 }
1021
1022
1023
1024
1025
1026
1027 private void updateFeature( Plugin plugin, SimpleEvent event )
1028 {
1029 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_FEATURE, plugin );
1030 daoUtil.setString( 1, event.getDescription( ) );
1031 daoUtil.setString( 2, event.getLocation( ) );
1032 daoUtil.setString( 3, event.getLocationTown( ) );
1033 daoUtil.setString( 4, event.getLocationZip( ) );
1034 daoUtil.setString( 5, event.getLocationAddress( ) );
1035 daoUtil.setString( 6, event.getMapUrl( ) );
1036 daoUtil.setString( 7, event.getLinkUrl( ) );
1037 daoUtil.setInt( 8, event.getDocumentId( ) );
1038 daoUtil.setString( 9, event.getPageUrl( ) );
1039 daoUtil.setInt( 10, event.getTopEvent( ) );
1040
1041 ImageResource imageResource = event.getImageResource( );
1042
1043 if ( imageResource != null )
1044 {
1045 daoUtil.setBytes( 11, imageResource.getImage( ) );
1046 daoUtil.setString( 12, imageResource.getMimeType( ) );
1047 }
1048 else
1049 {
1050 daoUtil.setBytes( 11, null );
1051 daoUtil.setString( 12, null );
1052 }
1053
1054 StringBuffer strTags = new StringBuffer( );
1055 String[] listTags = event.getTags( );
1056
1057 if ( listTags != null )
1058 {
1059 for ( String tags : listTags )
1060 {
1061 strTags.append( tags + Constants.SPACE );
1062 }
1063 }
1064
1065 daoUtil.setString( 13, strTags.toString( ) );
1066 daoUtil.setInt( 14, event.getId( ) );
1067 daoUtil.executeUpdate( );
1068 daoUtil.free( );
1069 }
1070
1071
1072
1073
1074
1075
1076 private void deleteFeature( Plugin plugin, int nEventId )
1077 {
1078 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FEATURE, plugin );
1079 daoUtil.setInt( 1, nEventId );
1080 daoUtil.executeUpdate( );
1081 daoUtil.free( );
1082 }
1083
1084
1085
1086
1087
1088
1089
1090 private void getFeature( Plugin plugin, int nIdEvent, SimpleEvent event )
1091 {
1092 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FEATURE, plugin );
1093 daoUtil.setInt( 1, nIdEvent );
1094 daoUtil.executeQuery( );
1095
1096 while ( daoUtil.next( ) )
1097 {
1098 event.setDescription( daoUtil.getString( 1 ) );
1099 event.setLocation( daoUtil.getString( 2 ) );
1100 event.setLocationTown( daoUtil.getString( 3 ) );
1101 event.setLocationZip( daoUtil.getString( 4 ) );
1102 event.setLocationAddress( daoUtil.getString( 5 ) );
1103 event.setMapUrl( daoUtil.getString( 6 ) );
1104 event.setLinkUrl( daoUtil.getString( 7 ) );
1105 event.setDocumentId( daoUtil.getInt( 8 ) );
1106 event.setPageUrl( daoUtil.getString( 9 ) );
1107 event.setTopEvent( daoUtil.getInt( 10 ) );
1108
1109 ImageResource imageResource = new ImageResource( );
1110 imageResource.setImage( daoUtil.getBytes( 11 ) );
1111 imageResource.setMimeType( daoUtil.getString( 12 ) );
1112 event.setImageResource( imageResource );
1113
1114 if ( daoUtil.getString( 13 ) != null )
1115 {
1116 String strTags = daoUtil.getString( 13 );
1117 String[] listTags = strTags.split( Constants.SPACE );
1118 event.setListTags( strTags );
1119 event.setTags( listTags );
1120 }
1121 }
1122
1123 daoUtil.free( );
1124 }
1125
1126
1127
1128
1129
1130
1131
1132
1133 private void insertLinkCategories( Collection<Category> listCategory, int nIdEvent, Plugin plugin )
1134 {
1135 if ( listCategory != null )
1136 {
1137 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_LINK_CATEGORY_CALENDAR, plugin );
1138
1139 for ( Category category : listCategory )
1140 {
1141 daoUtil.setInt( 1, category.getId( ) );
1142 daoUtil.setInt( 2, nIdEvent );
1143 daoUtil.executeUpdate( );
1144 }
1145
1146 daoUtil.free( );
1147 }
1148 }
1149
1150
1151
1152
1153
1154
1155 private void deleteLinkCategories( Plugin plugin, int nEventId )
1156 {
1157 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINK_CATEGORY_CALENDAR, plugin );
1158 daoUtil.setInt( 1, nEventId );
1159 daoUtil.executeUpdate( );
1160 daoUtil.free( );
1161 }
1162
1163
1164
1165
1166
1167
1168
1169 public ImageResource loadImageResource( int nCategoryId, Plugin plugin )
1170 {
1171 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RESOURCE_IMAGE, plugin );
1172 daoUtil.setInt( 1, nCategoryId );
1173 daoUtil.executeQuery( );
1174
1175 ImageResource image = null;
1176
1177 if ( daoUtil.next( ) )
1178 {
1179 image = new ImageResource( );
1180 image.setImage( daoUtil.getBytes( 1 ) );
1181 image.setMimeType( daoUtil.getString( 2 ) );
1182 }
1183
1184 daoUtil.free( );
1185
1186 return image;
1187 }
1188
1189
1190
1191
1192
1193
1194
1195
1196 public List<Event> selectByFilter( CalendarFilter filter, Plugin plugin )
1197 {
1198 List<Event> eventList = new ArrayList<Event>( );
1199 DAOUtil daoUtil = getDaoFromFilter( SQL_QUERY_SELECT_BY_FILTER, filter, plugin );
1200 daoUtil.executeQuery( );
1201
1202 while ( daoUtil.next( ) )
1203 {
1204 SimpleEvent event = new SimpleEvent( );
1205 event.setId( daoUtil.getInt( 1 ) );
1206 event.setIdCalendar( daoUtil.getInt( 2 ) );
1207 event.setDate( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
1208 event.setDateEnd( new java.util.Date( daoUtil.getDate( 4 ).getTime( ) ) );
1209 event.setDateTimeStart( daoUtil.getString( 5 ) );
1210 event.setDateTimeEnd( daoUtil.getString( 6 ) );
1211 event.setTitle( daoUtil.getString( 7 ) );
1212 event.setOccurrence( daoUtil.getInt( 8 ) );
1213 event.setPeriodicity( daoUtil.getInt( 9 ) );
1214 event.setDateCreation( daoUtil.getTimestamp( 10 ) );
1215 getFeature( plugin, event.getId( ), event );
1216 eventList.add( event );
1217 }
1218
1219 daoUtil.free( );
1220
1221 return eventList;
1222 }
1223
1224
1225
1226
1227
1228
1229
1230
1231 private DAOUtil getDaoFromFilter( String strQuerySelect, CalendarFilter filter, Plugin plugin )
1232 {
1233 StringBuffer sbSQL = new StringBuffer( strQuerySelect );
1234 StringBuffer sbWhere = new StringBuffer( ( filter.containsCalendarCriteria( ) ) ? SQL_FILTER_CALENDAR
1235 : StringUtils.EMPTY );
1236
1237 if ( filter.containsCategoriesCriteria( ) )
1238 {
1239 StringBuffer sbCategories = new StringBuffer( SQL_FILTER_CATEGORIES_BEGIN );
1240
1241 for ( int i = 0; i < filter.getCategoriesId( ).length; i++ )
1242 {
1243 sbCategories.append( SQL_FILTER_CATEGORIES );
1244
1245 if ( ( i + 1 ) < filter.getCategoriesId( ).length )
1246 {
1247 sbCategories.append( SQL_FILTER_CATEGORIES_OR );
1248 }
1249 }
1250
1251 sbCategories.append( SQL_FILTER_CATEGORIES_END );
1252 if ( StringUtils.isNotBlank( sbWhere.toString( ) ) )
1253 {
1254 sbWhere.append( SQL_FILTER_AND );
1255 }
1256 sbWhere.append( sbCategories.toString( ) );
1257 }
1258
1259 if ( filter.containsIdsCriteria( ) )
1260 {
1261 StringBuffer sbIds = new StringBuffer( SQL_FILTER_ID_BEGIN );
1262
1263 for ( int i = 0; i < filter.getIds( ).length; i++ )
1264 {
1265 sbIds.append( SQL_FILTER_ID );
1266
1267 if ( ( i + 1 ) < filter.getIds( ).length )
1268 {
1269 sbIds.append( SQL_FILTER_ID_OR );
1270 }
1271 }
1272
1273 sbIds.append( SQL_FILTER_ID_END );
1274 if ( StringUtils.isNotBlank( sbWhere.toString( ) ) )
1275 {
1276 sbWhere.append( SQL_FILTER_AND );
1277 }
1278 sbWhere.append( sbIds.toString( ) );
1279 }
1280
1281 if ( filter.containsCalendarIdsCriteria( ) )
1282 {
1283 StringBuffer sbCalendarIds = new StringBuffer( SQL_FILTER_ID_BEGIN );
1284
1285 for ( int i = 0; i < filter.getCalendarIds( ).length; i++ )
1286 {
1287 sbCalendarIds.append( SQL_FILTER_CALENDAR_ID );
1288
1289 if ( ( i + 1 ) < filter.getCalendarIds( ).length )
1290 {
1291 sbCalendarIds.append( SQL_FILTER_ID_OR );
1292 }
1293 }
1294
1295 sbCalendarIds.append( SQL_FILTER_ID_END );
1296 if ( StringUtils.isNotBlank( sbWhere.toString( ) ) )
1297 {
1298 sbWhere.append( SQL_FILTER_AND );
1299 }
1300 sbWhere.append( sbCalendarIds.toString( ) );
1301 }
1302
1303 if ( StringUtils.isNotBlank( sbWhere.toString( ) ) )
1304 {
1305 sbSQL.append( SQL_FILTER_WHERE_CLAUSE );
1306 sbSQL.append( sbWhere.toString( ) );
1307 }
1308
1309 int nSortEvents = filter.containsSortCriteria( ) ? filter.getSortEvents( ) : 0;
1310 String strSortEvents;
1311
1312 if ( nSortEvents == 1 )
1313 {
1314 strSortEvents = SQL_FILTER_ASC;
1315 }
1316 else
1317 {
1318 strSortEvents = SQL_FILTER_DESC;
1319 }
1320
1321 sbSQL.append( SQL_ORDER_BY_EVENTS );
1322 sbSQL.append( strSortEvents );
1323 AppLogService.debug( "Sql query filter : " + sbSQL.toString( ) );
1324
1325 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
1326 int nIndex = 1;
1327
1328 if ( filter.containsCategoriesCriteria( ) )
1329 {
1330 for ( int nCategoryId : filter.getCategoriesId( ) )
1331 {
1332 daoUtil.setInt( nIndex, nCategoryId );
1333 AppLogService.debug( "Param" + nIndex + " (getCategoriesId) = " + nCategoryId );
1334 nIndex++;
1335 }
1336 }
1337
1338 if ( filter.containsIdsCriteria( ) )
1339 {
1340 for ( int nId : filter.getIds( ) )
1341 {
1342 daoUtil.setInt( nIndex, nId );
1343 AppLogService.debug( "Param" + nIndex + " (getIds) = " + nId );
1344 nIndex++;
1345 }
1346 }
1347
1348 if ( filter.containsCalendarIdsCriteria( ) )
1349 {
1350 for ( int nId : filter.getCalendarIds( ) )
1351 {
1352 daoUtil.setInt( nIndex, nId );
1353 AppLogService.debug( "Param" + nIndex + " (getCalendarIds) = " + nId );
1354 nIndex++;
1355 }
1356 }
1357
1358 return daoUtil;
1359 }
1360
1361
1362
1363
1364
1365
1366 public List<SimpleEvent> selectTopEventsList( Plugin plugin )
1367 {
1368 List<SimpleEvent> eventList = new ArrayList<SimpleEvent>( );
1369
1370 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TOP_EVENTS, plugin );
1371 daoUtil.executeQuery( );
1372
1373 while ( daoUtil.next( ) )
1374 {
1375 SimpleEvent event = new SimpleEvent( );
1376 event.setId( daoUtil.getInt( 1 ) );
1377 event.setIdCalendar( daoUtil.getInt( 2 ) );
1378 event.setDate( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
1379 event.setDateEnd( new java.util.Date( daoUtil.getDate( 4 ).getTime( ) ) );
1380 event.setDateTimeStart( daoUtil.getString( 5 ) );
1381 event.setDateTimeEnd( daoUtil.getString( 6 ) );
1382 event.setTitle( daoUtil.getString( 7 ) );
1383 event.setOccurrence( daoUtil.getInt( 8 ) );
1384 event.setPeriodicity( daoUtil.getInt( 9 ) );
1385 event.setDateCreation( daoUtil.getTimestamp( 10 ) );
1386 getFeature( plugin, event.getId( ), event );
1387 eventList.add( event );
1388 }
1389
1390 daoUtil.free( );
1391
1392 return eventList;
1393 }
1394
1395
1396
1397
1398
1399
1400
1401 public boolean hasOccurenceEvent( Calendar calendar, Plugin plugin )
1402 {
1403 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_HAS_EVENT, plugin );
1404 boolean isOccurrence = false;
1405 String date = Utils.getDate( calendar );
1406 java.util.Date dateEvent = Utils.getDate( date );
1407 daoUtil.setDate( 1, new java.sql.Date( dateEvent.getTime( ) ) );
1408 daoUtil.executeQuery( );
1409
1410 while ( daoUtil.next( ) )
1411 {
1412 isOccurrence = true;
1413 }
1414
1415 daoUtil.free( );
1416
1417 return isOccurrence;
1418 }
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428 public List<SimpleEvent> selectEventsListByUserLogin( int nAgendaId, int nSortEvents, Plugin plugin,
1429 String strUserLogin )
1430 {
1431 List<SimpleEvent> eventList = new ArrayList<SimpleEvent>( );
1432 String strSortEvents = null;
1433
1434 if ( nSortEvents == 1 )
1435 {
1436 strSortEvents = "ASC";
1437 }
1438 else
1439 {
1440 strSortEvents = "DESC";
1441 }
1442
1443 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_EVENTS_BY_USER_LOGIN + strSortEvents, plugin );
1444 daoUtil.setInt( 1, nAgendaId );
1445 daoUtil.setString( 2, strUserLogin );
1446 daoUtil.executeQuery( );
1447
1448 while ( daoUtil.next( ) )
1449 {
1450 SimpleEvent event = new SimpleEvent( );
1451 event.setId( daoUtil.getInt( 1 ) );
1452 event.setIdCalendar( daoUtil.getInt( 2 ) );
1453 event.setDate( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
1454 event.setDateEnd( new java.util.Date( daoUtil.getDate( 4 ).getTime( ) ) );
1455 event.setDateTimeStart( daoUtil.getString( 5 ) );
1456 event.setDateTimeEnd( daoUtil.getString( 6 ) );
1457 event.setTitle( daoUtil.getString( 7 ) );
1458 event.setOccurrence( daoUtil.getInt( 8 ) );
1459 event.setPeriodicity( daoUtil.getInt( 9 ) );
1460 event.setDateCreation( daoUtil.getTimestamp( 10 ) );
1461 getFeature( plugin, event.getId( ), event );
1462 event.setListCategories( CategoryHome.findByEvent( event.getId( ), plugin ) );
1463 eventList.add( event );
1464 }
1465
1466 daoUtil.free( );
1467
1468 return eventList;
1469 }
1470
1471
1472
1473
1474
1475
1476 public void deleteEventUser( int nEventId, Plugin plugin )
1477 {
1478 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_EVENT_USER, plugin );
1479 daoUtil.setInt( 1, nEventId );
1480 daoUtil.executeUpdate( );
1481 daoUtil.free( );
1482 }
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492 public List<SimpleEvent> selectEventsList( int nAgendaId, int nSortEvents, int nNextDays, Plugin plugin )
1493 {
1494 List<SimpleEvent> eventList = new ArrayList<SimpleEvent>( );
1495 String strSortEvents = null;
1496
1497 if ( nSortEvents == 1 )
1498 {
1499 strSortEvents = "ASC";
1500 }
1501 else
1502 {
1503 strSortEvents = "DESC";
1504 }
1505
1506 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_EVENTS_N_NEXT_DAYS + strSortEvents, plugin );
1507 daoUtil.setInt( 1, nAgendaId );
1508
1509 String strDate = Utils.getDateToday( );
1510 Calendar calendar = new GregorianCalendar( );
1511 calendar.set( Utils.getYear( strDate ), Utils.getMonth( strDate ), Utils.getDay( strDate ) );
1512
1513 daoUtil.setString( 2, Utils.getDate( calendar ) );
1514
1515 calendar.add( Calendar.DATE, nNextDays );
1516
1517 daoUtil.setString( 3, Utils.getDate( calendar ) );
1518 daoUtil.executeQuery( );
1519
1520 while ( daoUtil.next( ) )
1521 {
1522 SimpleEvent event = new SimpleEvent( );
1523 event.setId( daoUtil.getInt( 1 ) );
1524 event.setIdCalendar( daoUtil.getInt( 2 ) );
1525 event.setDate( new java.util.Date( daoUtil.getDate( 3 ).getTime( ) ) );
1526 event.setDateEnd( new java.util.Date( daoUtil.getDate( 4 ).getTime( ) ) );
1527 event.setDateTimeStart( daoUtil.getString( 5 ) );
1528 event.setDateTimeEnd( daoUtil.getString( 6 ) );
1529 event.setTitle( daoUtil.getString( 7 ) );
1530 event.setOccurrence( daoUtil.getInt( 8 ) );
1531 event.setPeriodicity( daoUtil.getInt( 9 ) );
1532 event.setDateCreation( daoUtil.getTimestamp( 10 ) );
1533 getFeature( plugin, event.getId( ), event );
1534 event.setListCategories( CategoryHome.findByEvent( event.getId( ), plugin ) );
1535 eventList.add( event );
1536 }
1537
1538 daoUtil.free( );
1539
1540 return eventList;
1541 }
1542
1543
1544
1545
1546 public List<Integer> selectCalendarIds( Plugin plugin )
1547 {
1548 List<Integer> listIds = new ArrayList<Integer>( );
1549 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_AGENDA_IDS, plugin );
1550 daoUtil.executeQuery( );
1551
1552 while ( daoUtil.next( ) )
1553 {
1554 listIds.add( daoUtil.getInt( 1 ) );
1555 }
1556
1557 daoUtil.free( );
1558
1559 return listIds;
1560 }
1561 }