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.appointment.business.comment;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.ReferenceList;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.sql.Date;
41 import java.sql.Statement;
42 import java.sql.Time;
43 import java.util.ArrayList;
44 import java.util.List;
45
46
47
48
49 public final class CommentDAO implements ICommentDAO
50 {
51
52 private static final String SQL_QUERY_SELECT = "SELECT id_comment, id_form, starting_validity_date, starting_validity_time, ending_validity_date, ending_validity_time, comment, comment_creation_date, comment_user_creator FROM appointment_comment WHERE id_comment = ?";
53 private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_comment ( id_form, starting_validity_date, starting_validity_time, ending_validity_date, ending_validity_time, comment, comment_creation_date, comment_user_creator ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) ";
54 private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_comment WHERE id_comment = ? ";
55 private static final String SQL_QUERY_DELETE_BY_ID_FORM = "DELETE FROM appointment_comment WHERE id_form = ? ";
56 private static final String SQL_QUERY_UPDATE = "UPDATE appointment_comment SET id_comment = ?, id_form = ?, starting_validity_date = ?, starting_validity_time = ?, ending_validity_date = ?, ending_validity_time = ?, comment = ?, comment_creation_date = ?, comment_user_creator = ? WHERE id_comment = ?";
57 private static final String SQL_QUERY_SELECTALL = "SELECT id_comment, id_form, starting_validity_date, starting_validity_time, ending_validity_date, ending_validity_time, comment, comment_creation_date, comment_user_creator FROM appointment_comment";
58 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_comment FROM appointment_comment";
59 private static final String SQL_QUERY_SELECT_BETWEEN = "SELECT id_comment, id_form, starting_validity_date, starting_validity_time, ending_validity_date, ending_validity_time, comment, comment_creation_date, comment_user_creator FROM appointment_comment WHERE starting_validity_date <= ? and ending_validity_date >= ? and id_form = ?";
60 private static final String SQL_QUERY_SELECT_INCLUSIVE = "SELECT id_comment, id_form, starting_validity_date, starting_validity_time, ending_validity_date, ending_validity_time, comment, comment_creation_date, comment_user_creator FROM appointment_comment WHERE starting_validity_date <= ? and ending_validity_date >= ? and id_form = ?";
61
62
63
64
65 @Override
66 public void insert( Comment comment, Plugin plugin )
67 {
68 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
69 {
70 int nIndex = 1;
71 daoUtil.setInt( nIndex++, comment.getIdForm( ) );
72 daoUtil.setDate( nIndex++, Date.valueOf( comment.getStartingValidityDate( ) ) );
73 if ( comment.getStartingValidityTime( ) != null )
74 {
75 daoUtil.setTime( nIndex++, Time.valueOf( comment.getStartingValidityTime( ) ) );
76 }
77 else
78 {
79 daoUtil.setTime( nIndex++, null );
80 }
81 daoUtil.setDate( nIndex++, Date.valueOf( comment.getEndingValidityDate( ) ) );
82 if ( comment.getEndingValidityTime( ) != null )
83 {
84 daoUtil.setTime( nIndex++, Time.valueOf( comment.getEndingValidityTime( ) ) );
85 }
86 else
87 {
88 daoUtil.setTime( nIndex++, null );
89 }
90 daoUtil.setString( nIndex++, comment.getComment( ) );
91 daoUtil.setDate( nIndex++, Date.valueOf( comment.getCreationDate( ) ) );
92 daoUtil.setString( nIndex++, comment.getCreatorUserName( ) );
93
94 daoUtil.executeUpdate( );
95 if ( daoUtil.nextGeneratedKey( ) )
96 {
97 comment.setId( daoUtil.getGeneratedKeyInt( 1 ) );
98 }
99 }
100
101 }
102
103
104
105
106 @Override
107 public Comment load( int nKey, Plugin plugin )
108 {
109 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
110 {
111 daoUtil.setInt( 1, nKey );
112 daoUtil.executeQuery( );
113 Comment comment = null;
114
115 if ( daoUtil.next( ) )
116 {
117 comment = new Comment( );
118 int nIndex = 1;
119
120 comment.setId( daoUtil.getInt( nIndex++ ) );
121 comment.setIdForm( daoUtil.getInt( nIndex++ ) );
122 comment.setStartingValidityDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
123 Time startingTime = daoUtil.getTime( nIndex++ );
124 if ( startingTime != null )
125 {
126 comment.setStartingValidityTime( startingTime.toLocalTime( ) );
127 }
128 comment.setEndingValidityDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
129 Time endingTime = daoUtil.getTime( nIndex++ );
130 if ( endingTime != null )
131 {
132 comment.setEndingValidityTime( endingTime.toLocalTime( ) );
133 }
134 comment.setComment( daoUtil.getString( nIndex++ ) );
135 comment.setCreationDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
136 comment.setCreatorUserName( daoUtil.getString( nIndex ) );
137
138 }
139 return comment;
140 }
141 }
142
143
144
145
146 @Override
147 public void delete( int nKey, Plugin plugin )
148 {
149 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
150 {
151 daoUtil.setInt( 1, nKey );
152 daoUtil.executeUpdate( );
153 }
154 }
155
156
157
158
159 @Override
160 public void deleteByIdFom( int nIdFom, Plugin plugin )
161 {
162 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_FORM, plugin ) )
163 {
164 daoUtil.setInt( 1, nIdFom );
165 daoUtil.executeUpdate( );
166 }
167 }
168
169
170
171
172 @Override
173 public void store( Comment comment, Plugin plugin )
174 {
175 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
176 {
177 int nIndex = 1;
178
179 daoUtil.setInt( nIndex++, comment.getId( ) );
180 daoUtil.setInt( nIndex++, comment.getIdForm( ) );
181 daoUtil.setDate( nIndex++, Date.valueOf( comment.getStartingValidityDate( ) ) );
182 daoUtil.setTime( nIndex++, Time.valueOf( comment.getStartingValidityTime( ) ) );
183 daoUtil.setDate( nIndex++, Date.valueOf( comment.getEndingValidityDate( ) ) );
184 daoUtil.setTime( nIndex++, Time.valueOf( comment.getEndingValidityTime( ) ) );
185 daoUtil.setString( nIndex++, comment.getComment( ) );
186 daoUtil.setDate( nIndex++, Date.valueOf( comment.getCreationDate( ) ) );
187 daoUtil.setString( nIndex++, comment.getCreatorUserName( ) );
188 daoUtil.setInt( nIndex++, comment.getId( ) );
189
190 daoUtil.executeUpdate( );
191 }
192 }
193
194
195
196
197 @Override
198 public List<Comment> selectCommentsList( Plugin plugin )
199 {
200 List<Comment> commentList = new ArrayList<>( );
201 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
202 {
203 daoUtil.executeQuery( );
204
205 while ( daoUtil.next( ) )
206 {
207 Commentns/appointment/business/comment/Comment.html#Comment">Comment comment = new Comment( );
208 int nIndex = 1;
209
210 comment.setId( daoUtil.getInt( nIndex++ ) );
211 comment.setIdForm( daoUtil.getInt( nIndex++ ) );
212 comment.setStartingValidityDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
213 Time startingTime = daoUtil.getTime( nIndex++ );
214 if ( startingTime != null )
215 {
216 comment.setStartingValidityTime( startingTime.toLocalTime( ) );
217 }
218 comment.setEndingValidityDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
219 Time endingTime = daoUtil.getTime( nIndex++ );
220 if ( endingTime != null )
221 {
222 comment.setEndingValidityTime( endingTime.toLocalTime( ) );
223 }
224 comment.setComment( daoUtil.getString( nIndex++ ) );
225 comment.setCreationDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
226 comment.setCreatorUserName( daoUtil.getString( nIndex++ ) );
227
228 commentList.add( comment );
229 }
230
231 return commentList;
232 }
233 }
234
235
236
237
238 @Override
239 public List<Comment> selectCommentsList( Plugin plugin, Date startingDate, Date endingDate, int nIdForm )
240 {
241 List<Comment> commentList = new ArrayList<>( );
242 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BETWEEN, plugin ) )
243 {
244 daoUtil.setDate( 1, endingDate );
245 daoUtil.setDate( 2, startingDate );
246 daoUtil.setInt( 3, nIdForm );
247
248 daoUtil.executeQuery( );
249
250 while ( daoUtil.next( ) )
251 {
252 Commentns/appointment/business/comment/Comment.html#Comment">Comment comment = new Comment( );
253 int nIndex = 1;
254
255 comment.setId( daoUtil.getInt( nIndex++ ) );
256 comment.setIdForm( daoUtil.getInt( nIndex++ ) );
257 comment.setStartingValidityDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
258 Time startingTime = daoUtil.getTime( nIndex++ );
259 if ( startingTime != null )
260 {
261 comment.setStartingValidityTime( startingTime.toLocalTime( ) );
262 }
263 comment.setEndingValidityDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
264 Time endingTime = daoUtil.getTime( nIndex++ );
265 if ( endingTime != null )
266 {
267 comment.setEndingValidityTime( endingTime.toLocalTime( ) );
268 }
269 comment.setComment( daoUtil.getString( nIndex++ ) );
270 comment.setCreationDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
271 comment.setCreatorUserName( daoUtil.getString( nIndex++ ) );
272
273 commentList.add( comment );
274 }
275
276 return commentList;
277 }
278 }
279
280
281
282
283 @Override
284 public List<Comment> selectCommentsListInclusive( Plugin plugin, Date startingDate, Date endingDate, int nIdForm )
285 {
286 List<Comment> commentList = new ArrayList<>( );
287 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_INCLUSIVE, plugin ) )
288 {
289 daoUtil.setDate( 1, startingDate );
290 daoUtil.setDate( 2, endingDate );
291 daoUtil.setInt( 3, nIdForm );
292
293 daoUtil.executeQuery( );
294
295 while ( daoUtil.next( ) )
296 {
297 Commentns/appointment/business/comment/Comment.html#Comment">Comment comment = new Comment( );
298 int nIndex = 1;
299
300 comment.setId( daoUtil.getInt( nIndex++ ) );
301 comment.setIdForm( daoUtil.getInt( nIndex++ ) );
302 comment.setStartingValidityDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
303 Time startingTime = daoUtil.getTime( nIndex++ );
304 if ( startingTime != null )
305 {
306 comment.setStartingValidityTime( startingTime.toLocalTime( ) );
307 }
308 comment.setEndingValidityDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
309 Time endingTime = daoUtil.getTime( nIndex++ );
310 if ( endingTime != null )
311 {
312 comment.setEndingValidityTime( endingTime.toLocalTime( ) );
313 }
314 comment.setComment( daoUtil.getString( nIndex++ ) );
315 comment.setCreationDate( daoUtil.getDate( nIndex++ ).toLocalDate( ) );
316 comment.setCreatorUserName( daoUtil.getString( nIndex++ ) );
317
318 commentList.add( comment );
319 }
320
321 return commentList;
322 }
323 }
324
325
326
327
328 @Override
329 public List<Integer> selectIdCommentsList( Plugin plugin )
330 {
331 List<Integer> commentList = new ArrayList<>( );
332 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
333 {
334 daoUtil.executeQuery( );
335
336 while ( daoUtil.next( ) )
337 {
338 commentList.add( daoUtil.getInt( 1 ) );
339 }
340
341 return commentList;
342 }
343 }
344
345
346
347
348 @Override
349 public ReferenceList selectCommentsReferenceList( Plugin plugin )
350 {
351 ReferenceList commentList = new ReferenceList( );
352 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
353 {
354 daoUtil.executeQuery( );
355
356 while ( daoUtil.next( ) )
357 {
358 commentList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
359 }
360
361 return commentList;
362 }
363 }
364
365 }