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
35 package fr.paris.lutece.plugins.transparency.business;
36
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 import java.sql.Statement;
41
42 import java.util.ArrayList;
43 import java.util.List;
44 import java.util.stream.Collectors;
45 import org.apache.commons.lang.StringUtils;
46
47
48
49
50 public final class AppointmentDAO implements IAppointmentDAO
51 {
52
53 private static final String SQL_QUERY_SELECT = "SELECT DISTINCT transparency_appointment.id_appointment, transparency_appointment.title, description, start_date, end_date, type_id, type_label, transparency_appointment.url, contacts FROM transparency_appointment ";
54 private static final String SQL_QUERY_INSERT = "INSERT INTO transparency_appointment ( title, description, start_date, end_date, type_id, type_label, url, contacts ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) ";
55 private static final String SQL_QUERY_DELETE = "DELETE FROM transparency_appointment WHERE id_appointment = ? ";
56 private static final String SQL_QUERY_UPDATE = "UPDATE transparency_appointment SET id_appointment = ?, title = ?, description = ?, start_date = ?, end_date = ?, type_id = ?, type_label = ?, url = ?, contacts = ? WHERE id_appointment = ?";
57 private static final String SQL_QUERY_SELECT_IDS = "SELECT DISTINCT transparency_appointment.id_appointment FROM transparency_appointment ";
58
59 private static final String SQL_WHERE_BASE = " WHERE 1 ";
60 private static final String SQL_ADD_CLAUSE = " AND ( ";
61 private static final String SQL_END_ADD_CLAUSE = " ) ";
62
63 private static final String SQL_FROM_FITLER_BY_APPOINTMENT_AND = " LEFT JOIN transparency_elected_official_appointment ON transparency_elected_official_appointment.id_appointment = transparency_appointment.id_appointment ";
64 private static final String SQL_FROM_FITLER_BY_ELECTED_OFFICIAL = " LEFT JOIN core_role on core_role.role = transparency_elected_official_appointment.role_key ";
65 private static final String SQL_FROM_FILTER_BY_DELEGATION = " LEFT JOIN mylutece_database_user_role ON mylutece_database_user_role.role_key = transparency_elected_official_appointment.role_key LEFT JOIN mylutece_database_user ON mylutece_database_user.mylutece_database_user_id = mylutece_database_user_role.mylutece_database_user_id ";
66 private static final String SQL_FROM_FITLER_BY_LOBBY = " LEFT JOIN transparency_lobby_appointment on transparency_lobby_appointment.id_appointment = transparency_appointment.id_appointment LEFT JOIN transparency_lobby on transparency_lobby.id_lobby = transparency_lobby_appointment.id_lobby ";
67
68 private static final String SQL_WHERECLAUSE_FILTER_BY_PERIOD = " start_date >= date_add( current_timestamp , INTERVAL -? DAY) ";
69 private static final String SQL_WHERECLAUSE_FILTER_BY_ELECTED_OFFICIAL = " core_role.role_description like ? ";
70 private static final String SQL_WHERECLAUSE_FILTER_BY_LOBBY = " transparency_lobby.name like ? ";
71 private static final String SQL_WHERECLAUSE_FILTER_BY_DELEGATION = " login = ? ";
72 private static final String SQL_WHERECLAUSE_FILTER_BY_ID = " transparency_appointment.id_appointment = ? ";
73 private static final String SQL_WHERECLAUSE_FILTER_BY_TITLE = " transparency_appointment.title like ? ";
74 private static final String SQL_WHERECLAUSE_FILTER_BY_IDLIST_START = " transparency_appointment.id_appointment in ( ";
75 private static final String SQL_WHERECLAUSE_FILTER_BY_IDLIST_END = " ) ";
76
77 private static final String SQL_WHERECLAUSE_BY_ID = " WHERE id_appointment = ?";
78 private static final String SQL_ORDER_BY = " ORDER BY ";
79 private static final String SQL_DEFAULT_ORDER_BY = " start_date ";
80 private static final String SQL_DEFAULT_ASC = " DESC ";
81
82
83
84
85 @Override
86 public void insert( Appointment appointment, Plugin plugin )
87 {
88 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin );
89 try
90 {
91 int nIndex = 1;
92 daoUtil.setString( nIndex++, appointment.getTitle( ) );
93 daoUtil.setString( nIndex++, appointment.getDescription( ) );
94 daoUtil.setDate( nIndex++, appointment.getStartDate( ) );
95 daoUtil.setDate( nIndex++, appointment.getEndDate( ) );
96 daoUtil.setInt( nIndex++, appointment.getTypeId( ) );
97 daoUtil.setString( nIndex++, appointment.getTypeLabel( ) );
98 daoUtil.setString( nIndex++, appointment.getUrl( ) );
99 daoUtil.setString( nIndex++, appointment.getContacts( ) );
100
101 daoUtil.executeUpdate( );
102 if ( daoUtil.nextGeneratedKey( ) )
103 {
104 appointment.setId( daoUtil.getGeneratedKeyInt( 1 ) );
105 }
106 }
107 finally
108 {
109 daoUtil.free( );
110 }
111 }
112
113
114
115
116 @Override
117 public Appointment load( int nKey, Plugin plugin )
118 {
119 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_WHERECLAUSE_BY_ID, plugin );
120 daoUtil.setInt( 1, nKey );
121 daoUtil.executeQuery( );
122 Appointment appointment = null;
123
124 if ( daoUtil.next( ) )
125 {
126 appointment = new Appointment( );
127 int nIndex = 1;
128
129 appointment.setId( daoUtil.getInt( nIndex++ ) );
130 appointment.setTitle( daoUtil.getString( nIndex++ ) );
131 appointment.setDescription( daoUtil.getString( nIndex++ ) );
132 appointment.setStartDate( daoUtil.getDate( nIndex++ ) );
133 appointment.setEndDate( daoUtil.getDate( nIndex++ ) );
134 appointment.setTypeId( daoUtil.getInt( nIndex++ ) );
135 appointment.setTypeLabel( daoUtil.getString( nIndex++ ) );
136 appointment.setUrl( daoUtil.getString( nIndex++ ) );
137 appointment.setContacts( daoUtil.getString( nIndex++ ) );
138 }
139
140 daoUtil.free( );
141 return appointment;
142 }
143
144
145
146
147 @Override
148 public void delete( int nKey, Plugin plugin )
149 {
150 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
151 daoUtil.setInt( 1, nKey );
152 daoUtil.executeUpdate( );
153 daoUtil.free( );
154 }
155
156
157
158
159 @Override
160 public void store( Appointment appointment, Plugin plugin )
161 {
162 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
163 int nIndex = 1;
164
165 daoUtil.setInt( nIndex++, appointment.getId( ) );
166 daoUtil.setString( nIndex++, appointment.getTitle( ) );
167 daoUtil.setString( nIndex++, appointment.getDescription( ) );
168 daoUtil.setDate( nIndex++, appointment.getStartDate( ) );
169 daoUtil.setDate( nIndex++, appointment.getEndDate( ) );
170 daoUtil.setInt( nIndex++, appointment.getTypeId( ) );
171 daoUtil.setString( nIndex++, appointment.getTypeLabel( ) );
172 daoUtil.setString( nIndex++, appointment.getUrl( ) );
173 daoUtil.setString( nIndex++, appointment.getContacts( ) );
174 daoUtil.setInt( nIndex, appointment.getId( ) );
175
176 daoUtil.executeUpdate( );
177 daoUtil.free( );
178 }
179
180
181
182
183 @Override
184 public List<Appointment> selectAppointmentsList( Plugin plugin )
185 {
186 return selectAppointmentsList( null, plugin );
187 }
188
189
190
191
192 @Override
193 public List<Appointment> selectAppointmentsList( AppointmentFilter filter, Plugin plugin )
194 {
195 List<Appointment> appointmentList = new ArrayList<>( );
196
197 StringBuilder sql = buildSQL( filter, false );
198
199 DAOUtil daoUtil = new DAOUtil( sql.toString( ), plugin );
200 int i = 1;
201
202 if ( filter != null &&
203 (filter.getListIds( ) ==null || filter.getListIds( ).size( ) == 0 ) )
204 {
205 if ( filter.getNumberOfDays( ) > 0 )
206 daoUtil.setInt( i++, filter.getNumberOfDays( ) );
207 if ( !StringUtils.isBlank( filter.getElectedOfficialName( ) ) )
208 daoUtil.setString( i++, "%" + filter.getElectedOfficialName( ) + "%" );
209 if ( !StringUtils.isBlank( filter.getLobbyName( ) ) )
210 daoUtil.setString( i++, "%" + filter.getLobbyName( ) + "%" );
211 if ( !StringUtils.isBlank( filter.getUserId( ) ) )
212 daoUtil.setString( i++, filter.getUserId( ) );
213 if ( filter.getIdAppointment( ) > 0 )
214 daoUtil.setInt( i++, filter.getIdAppointment( ) );
215 if ( !StringUtils.isBlank( filter.getTitle( ) ) )
216 daoUtil.setString( i++, "%" + filter.getTitle( ) + "%" );
217 }
218
219
220
221 daoUtil.executeQuery( );
222
223 while ( daoUtil.next( ) )
224 {
225 Appointment appointment = new Appointment( );
226 int nIndex = 1;
227
228 appointment.setId( daoUtil.getInt( nIndex++ ) );
229
230 appointment.setTitle( daoUtil.getString( nIndex++ ) );
231 appointment.setDescription( daoUtil.getString( nIndex++ ) );
232 appointment.setStartDate( daoUtil.getDate( nIndex++ ) );
233 appointment.setEndDate( daoUtil.getDate( nIndex++ ) );
234 appointment.setTypeId( daoUtil.getInt( nIndex++ ) );
235 appointment.setTypeLabel( daoUtil.getString( nIndex++ ) );
236 appointment.setUrl( daoUtil.getString( nIndex++ ) );
237 appointment.setContacts( daoUtil.getString( nIndex++ ) );
238
239
240 appointmentList.add( appointment );
241 }
242
243 daoUtil.free( );
244 return appointmentList;
245 }
246
247
248
249
250 @Override
251 public List<Integer> selectAppointmentIdsList( AppointmentFilter filter, Plugin plugin )
252 {
253
254 List<Integer> appointmentIdsList = new ArrayList<>();
255
256 StringBuilder sql = buildSQL( filter, true );
257
258 DAOUtil daoUtil = new DAOUtil( sql.toString( ), plugin );
259 int i = 1;
260
261 if ( filter != null &&
262 (filter.getListIds( ) ==null || filter.getListIds( ).size( ) == 0 ) )
263 {
264 if ( filter.getNumberOfDays( ) > 0 )
265 daoUtil.setInt( i++, filter.getNumberOfDays( ) );
266 if ( !StringUtils.isBlank( filter.getElectedOfficialName( ) ) )
267 daoUtil.setString( i++, "%" + filter.getElectedOfficialName( ) + "%" );
268 if ( !StringUtils.isBlank( filter.getLobbyName( ) ) )
269 daoUtil.setString( i++, "%" + filter.getLobbyName( ) + "%" );
270 if ( !StringUtils.isBlank( filter.getUserId( ) ) )
271 daoUtil.setString( i++, filter.getUserId( ) );
272 if ( filter.getIdAppointment( ) > 0 )
273 daoUtil.setInt( i++, filter.getIdAppointment( ) );
274 if ( !StringUtils.isBlank( filter.getTitle( ) ) )
275 daoUtil.setString( i++, "%" + filter.getTitle( ) + "%" );
276 }
277
278
279 daoUtil.executeQuery( );
280
281 while ( daoUtil.next( ) )
282 {
283 appointmentIdsList.add( daoUtil.getInt( 1 ) );
284 }
285
286 daoUtil.free( );
287 return appointmentIdsList;
288 }
289
290
291
292
293 @Override
294 public ReferenceList selectAppointmentsReferenceList( Plugin plugin )
295 {
296 ReferenceList appointmentList = new ReferenceList( );
297 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_ORDER_BY + SQL_DEFAULT_ORDER_BY, plugin );
298 daoUtil.executeQuery( );
299
300 while ( daoUtil.next( ) )
301 {
302 appointmentList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
303 }
304
305 daoUtil.free( );
306 return appointmentList;
307 }
308
309
310
311
312
313
314
315
316 private StringBuilder buildSQL ( AppointmentFilter filter, boolean idsOnly)
317 {
318 StringBuilder sql = new StringBuilder( );
319 StringBuilder where = new StringBuilder( );
320 StringBuilder orderBy = new StringBuilder( );
321
322 boolean appointmentJoinAdded = false;
323
324 if (idsOnly)
325 {
326
327 sql.append( SQL_QUERY_SELECT_IDS ) ;
328 }
329 else
330 {
331
332 sql.append( SQL_QUERY_SELECT ) ;
333 }
334
335
336 if ( filter == null )
337 {
338
339 orderBy.append( SQL_ORDER_BY ).append( SQL_DEFAULT_ORDER_BY ).append( SQL_DEFAULT_ASC );
340 }
341 else
342 {
343 where.append( SQL_WHERE_BASE );
344
345
346 if (filter.getListIds( ) !=null && filter.getListIds( ).size( ) > 0 )
347 {
348 where.append( SQL_ADD_CLAUSE ).append( SQL_WHERECLAUSE_FILTER_BY_IDLIST_START ) ;
349 where.append( filter.getListIds( ).stream( ).map( id -> id.toString( )).collect( Collectors.joining(",") ) );
350 where.append( SQL_WHERECLAUSE_FILTER_BY_IDLIST_END ).append( SQL_END_ADD_CLAUSE );
351 }
352 else
353 {
354
355
356 if ( filter.getNumberOfDays( ) > 0 )
357 {
358 where.append( SQL_ADD_CLAUSE ).append( SQL_WHERECLAUSE_FILTER_BY_PERIOD ).append( SQL_END_ADD_CLAUSE );
359 }
360
361
362 if ( !StringUtils.isBlank( filter.getElectedOfficialName( ) ) )
363 {
364 sql.append( SQL_FROM_FITLER_BY_APPOINTMENT_AND ).append( SQL_FROM_FITLER_BY_ELECTED_OFFICIAL );
365 appointmentJoinAdded = true;
366 where.append( SQL_ADD_CLAUSE ).append( SQL_WHERECLAUSE_FILTER_BY_ELECTED_OFFICIAL ).append( SQL_END_ADD_CLAUSE );
367 }
368
369
370 if ( !StringUtils.isBlank( filter.getLobbyName( ) ) )
371 {
372 sql.append( SQL_FROM_FITLER_BY_LOBBY );
373 where.append( SQL_ADD_CLAUSE ).append( SQL_WHERECLAUSE_FILTER_BY_LOBBY ).append( SQL_END_ADD_CLAUSE );
374 }
375
376
377 if ( !StringUtils.isBlank( filter.getUserId( ) ) )
378 {
379 if ( !appointmentJoinAdded )
380 sql.append( SQL_FROM_FITLER_BY_APPOINTMENT_AND );
381 sql.append( SQL_FROM_FILTER_BY_DELEGATION );
382 where.append( SQL_ADD_CLAUSE ).append( SQL_WHERECLAUSE_FILTER_BY_DELEGATION ).append( SQL_END_ADD_CLAUSE );
383 }
384
385
386 if ( filter.getIdAppointment( ) > 0 )
387 {
388 where.append( SQL_ADD_CLAUSE ).append( SQL_WHERECLAUSE_FILTER_BY_ID ).append( SQL_END_ADD_CLAUSE );
389 }
390
391
392 if ( !StringUtils.isBlank( filter.getTitle( ) ) )
393 {
394 where.append( SQL_ADD_CLAUSE ).append( SQL_WHERECLAUSE_FILTER_BY_TITLE ).append( SQL_END_ADD_CLAUSE );
395 }
396 }
397
398
399 if ( !StringUtils.isBlank( filter.getOrderBy( ) ) )
400 {
401 orderBy.append( SQL_ORDER_BY ).append( filter.getOrderBy( ) );
402 }
403 else
404 {
405 orderBy.append( SQL_ORDER_BY ).append( SQL_DEFAULT_ORDER_BY ).append( SQL_DEFAULT_ASC );
406 }
407 }
408
409
410 sql.append( where ).append( orderBy );
411
412 return sql;
413 }
414 }