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.digglike.business;
35
36 import fr.paris.lutece.plugins.digglike.utils.DiggUtils;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.sql.Timestamp;
41
42 import java.util.ArrayList;
43 import java.util.Date;
44 import java.util.List;
45
46
47
48
49
50 public final class CommentSubmitDAO implements ICommentSubmitDAO
51 {
52
53 private static final String SQL_QUERY_NEW_PK = "SELECT MAX( id_comment_submit ) FROM digglike_comment_submit";
54 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = "SELECT id_comment_submit,id_digg_submit,date_comment,comment_value,active, lutece_user_key, official_answer, id_parent_comment,date_modify " +
55 "FROM digglike_comment_submit WHERE id_comment_submit=? ";
56 private static final String SQL_QUERY_INSERT = "INSERT INTO digglike_comment_submit ( id_comment_submit,id_digg_submit,date_comment,comment_value,active,lutece_user_key,official_answer,id_parent_comment,date_modify ) " +
57 "VALUES(?,?,?,?,?,?,?,?,?)";
58 private static final String SQL_QUERY_DELETE = "DELETE FROM digglike_comment_submit WHERE id_comment_submit = ? ";
59 private static final String SQL_QUERY_DELETE_BY_ID_PARENT = "DELETE FROM digglike_comment_submit WHERE id_parent_comment = ? ";
60 private static final String SQL_QUERY_UPDATE = "UPDATE digglike_comment_submit SET " +
61 "id_comment_submit=?,id_digg_submit=?,date_comment=?,comment_value=?,active=? ,lutece_user_key=? ,official_answer=? ,id_parent_comment=?" +
62 " WHERE id_comment_submit=? ";
63 private static final String SQL_QUERY_SELECT_COMMENT_SUBMIT_BY_FILTER = "SELECT dc.id_comment_submit,dc.id_digg_submit,date_comment,dc.comment_value,dc.active,dc.lutece_user_key,dc.official_answer,dc.id_parent_comment,dc.date_modify " +
64 "FROM digglike_comment_submit dc ";
65 private static final String SQL_QUERY_SELECT_COUNT_BY_FILTER = "SELECT COUNT(dc.id_comment_submit) " +
66 "FROM digglike_comment_submit dc INNER JOIN digglike_digg_submit ds ON dc.id_digg_submit = ds.id_digg_submit ";
67 private static final String SQL_FILTER_ID_DIGG_SUBMIT = " dc.id_digg_submit = ? ";
68 private static final String SQL_FILTER_ID_PARENT_COMMENT = "dc.id_parent_comment = ? ";
69 private static final String SQL_FILTER_ID_DIGG = " ds.id_digg = ? ";
70 private static final String SQL_FILTER_COMMENT_SUBMIT_STATE = " dc.active = ? ";
71 private static final String SQL_FILTER_CONTAINS_SUB_COMMENT_DISABLE = " dc.id_digg_submit IN ( SELECT id_parent_comment FROM digglike_comment_submit WHERE id_parent_comment = dc.id_comment_submit and active=0 ) ";
72 private static final String SQL_FILTER_NOT_CONTAINS_SUB_COMMENT_DISABLE = " dc.id_digg_submit NOT IN ( SELECT id_parent_comment FROM digglike_comment_submit WHERE id_parent_comment = dc.id_comment_submit and active=0 ) ";
73 private static final String SQL_QUERY_SELECT_COMMENTED_DIGG_SUBMIT = " select id_comment_submit,id_digg_submit,date_comment,comment_value,active, lutece_user_key, official_answer, id_parent_comment,date_modify FROM digglike_comment_submit WHERE date_comment > ? ";
74 private static final String SQL_FILTER_SORT_BY_DATE_COMMENT_DESC = " dc.date_comment DESC";
75 private static final String SQL_FILTER_SORT_BY_DATE_COMMENT_ASC = " dc.date_comment ASC";
76 private static final String SQL_FILTER_SORT_BY_DATE_MODIFY_COMMENT_DESC = " dc.date_modify DESC";
77 private static final String SQL_FILTER_SORT_BY_DATE_MODIFY_COMMENT_ASC = " dc.date_modify ASC";
78 private static final String SQL_QUERY_UPDATE_DATE_MODIFY = "UPDATE digglike_comment_submit SET date_modify=? WHERE id_comment_submit=? ";
79 private static final String SQL_ORDER_BY = " ORDER BY ";
80 private static final String SQL_LIMIT = " LIMIT ";
81
82
83
84
85
86
87
88 private int newPrimaryKey( Plugin plugin )
89 {
90 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
91 daoUtil.executeQuery( );
92
93 int nKey;
94
95 if ( !daoUtil.next( ) )
96 {
97
98 nKey = 1;
99 }
100
101 nKey = daoUtil.getInt( 1 ) + 1;
102 daoUtil.free( );
103
104 return nKey;
105 }
106
107
108
109
110 @Override
111 public void insert( CommentSubmit commentSubmit, Plugin plugin )
112 {
113 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
114 commentSubmit.setIdCommentSubmit( newPrimaryKey( plugin ) );
115 daoUtil.setInt( 1, commentSubmit.getIdCommentSubmit( ) );
116 daoUtil.setInt( 2, commentSubmit.getDiggSubmit( ).getIdDiggSubmit( ) );
117 daoUtil.setTimestamp( 3, commentSubmit.getDateComment( ) );
118 daoUtil.setString( 4, commentSubmit.getValue( ) );
119 daoUtil.setBoolean( 5, commentSubmit.isActive( ) );
120 daoUtil.setString( 6, commentSubmit.getLuteceUserKey( ) );
121 daoUtil.setBoolean( 7, commentSubmit.isOfficialAnswer( ) );
122 daoUtil.setInt( 8, commentSubmit.getIdParent( ) );
123 daoUtil.setTimestamp( 9, commentSubmit.getDateModify( ) );
124 daoUtil.executeUpdate( );
125 daoUtil.free( );
126 }
127
128
129
130
131 @Override
132 public CommentSubmit load( int nIdCommentSubmit, Plugin plugin )
133 {
134 CommentSubmit commentSubmit = null;
135 DiggSubmit diggSubmit = null;
136
137 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
138 daoUtil.setInt( 1, nIdCommentSubmit );
139 daoUtil.executeQuery( );
140
141 if ( daoUtil.next( ) )
142 {
143 commentSubmit = new CommentSubmit( );
144 commentSubmit.setIdCommentSubmit( daoUtil.getInt( 1 ) );
145
146 diggSubmit = new DiggSubmit( );
147 diggSubmit.setIdDiggSubmit( daoUtil.getInt( 2 ) );
148 commentSubmit.setDiggSubmit( diggSubmit );
149
150 commentSubmit.setDateComment( daoUtil.getTimestamp( 3 ) );
151 commentSubmit.setValue( daoUtil.getString( 4 ) );
152 commentSubmit.setActive( daoUtil.getBoolean( 5 ) );
153 commentSubmit.setLuteceUserKey( daoUtil.getString( 6 ) );
154 commentSubmit.setOfficialAnswer( daoUtil.getBoolean( 7 ) );
155 commentSubmit.setIdParent( daoUtil.getInt( 8 ) );
156 commentSubmit.setDateModify( daoUtil.getTimestamp( 9 ) );
157 }
158
159 daoUtil.free( );
160
161 return commentSubmit;
162 }
163
164
165
166
167 @Override
168 public void delete( int nIdTagSubmit, Plugin plugin )
169 {
170 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
171 daoUtil.setInt( 1, nIdTagSubmit );
172 daoUtil.executeUpdate( );
173 daoUtil.free( );
174 }
175
176
177
178
179 @Override
180 public void store( CommentSubmit commentSubmit, Plugin plugin )
181 {
182 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
183 daoUtil.setInt( 1, commentSubmit.getIdCommentSubmit( ) );
184 daoUtil.setInt( 2, commentSubmit.getDiggSubmit( ).getIdDiggSubmit( ) );
185 daoUtil.setTimestamp( 3, commentSubmit.getDateComment( ) );
186 daoUtil.setString( 4, commentSubmit.getValue( ) );
187 daoUtil.setBoolean( 5, commentSubmit.isActive( ) );
188 daoUtil.setString( 6, commentSubmit.getLuteceUserKey( ) );
189 daoUtil.setBoolean( 7, commentSubmit.isOfficialAnswer( ) );
190 daoUtil.setInt( 8, commentSubmit.getIdParent( ) );
191 daoUtil.setInt( 9, commentSubmit.getIdCommentSubmit( ) );
192 daoUtil.executeUpdate( );
193 daoUtil.free( );
194 }
195
196
197
198
199 @Override
200 public void storeDateModify( Timestamp dateModify, int idCommentSubmit, Plugin plugin )
201 {
202 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_DATE_MODIFY, plugin );
203 daoUtil.setTimestamp( 1, dateModify );
204 daoUtil.setInt( 2, idCommentSubmit );
205 daoUtil.executeUpdate( );
206 daoUtil.free( );
207 }
208
209
210
211
212 @Override
213 public List<CommentSubmit> selectListByFilter( SubmitFilter filter, Integer nLimit, Plugin plugin )
214 {
215 List<CommentSubmit> commentSubmitList = new ArrayList<CommentSubmit>( );
216 CommentSubmit commentSubmit = null;
217 DiggSubmit diggSubmit = null;
218 List<String> listStrFilter = new ArrayList<String>( );
219 String strOrderBy = null;
220
221 if ( filter.containsIdDiggSubmit( ) )
222 {
223 listStrFilter.add( SQL_FILTER_ID_DIGG_SUBMIT );
224 }
225
226 if ( filter.containsIdCommentSubmitState( ) )
227 {
228 listStrFilter.add( SQL_FILTER_COMMENT_SUBMIT_STATE );
229 }
230
231 if ( filter.containsIdParent( ) )
232 {
233 listStrFilter.add( SQL_FILTER_ID_PARENT_COMMENT );
234 }
235
236 if ( filter.containsIdContainsCommentDisable( ) )
237 {
238 listStrFilter.add( ( filter.getIdContainsCommentDisable( ) == SubmitFilter.ID_TRUE )
239 ? SQL_FILTER_CONTAINS_SUB_COMMENT_DISABLE : SQL_FILTER_NOT_CONTAINS_SUB_COMMENT_DISABLE );
240 }
241
242 if ( filter.containsSortBy( ) )
243 {
244 strOrderBy = getOrderBy( filter.getSortBy( ) );
245 }
246
247 String strSQL = DiggUtils.buildRequestWithFilter( SQL_QUERY_SELECT_COMMENT_SUBMIT_BY_FILTER, listStrFilter,
248 strOrderBy );
249
250 if ( ( nLimit != null ) && ( nLimit != DiggUtils.CONSTANT_ID_NULL ) )
251 {
252 strSQL = strSQL + SQL_LIMIT + nLimit;
253 }
254
255 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
256 int nIndex = 1;
257
258 if ( filter.containsIdDiggSubmit( ) )
259 {
260 daoUtil.setInt( nIndex, filter.getIdDiggSubmit( ) );
261 nIndex++;
262 }
263
264 if ( filter.containsIdCommentSubmitState( ) )
265 {
266 daoUtil.setBoolean( nIndex, filter.convertIdBoolean( filter.getIdCommentSubmitState( ) ) );
267 nIndex++;
268 }
269
270 if ( filter.containsIdParent( ) )
271 {
272 daoUtil.setInt( nIndex, filter.getIdParent( ) );
273 nIndex++;
274 }
275
276 daoUtil.executeQuery( );
277
278 while ( daoUtil.next( ) )
279 {
280 commentSubmit = new CommentSubmit( );
281 commentSubmit.setIdCommentSubmit( daoUtil.getInt( 1 ) );
282
283 diggSubmit = new DiggSubmit( );
284 diggSubmit.setIdDiggSubmit( daoUtil.getInt( 2 ) );
285 commentSubmit.setDiggSubmit( diggSubmit );
286
287 commentSubmit.setDateComment( daoUtil.getTimestamp( 3 ) );
288 commentSubmit.setValue( daoUtil.getString( 4 ) );
289 commentSubmit.setActive( daoUtil.getBoolean( 5 ) );
290 commentSubmit.setLuteceUserKey( daoUtil.getString( 6 ) );
291 commentSubmit.setOfficialAnswer( daoUtil.getBoolean( 7 ) );
292 commentSubmit.setIdParent( daoUtil.getInt( 8 ) );
293 commentSubmit.setDateModify( daoUtil.getTimestamp( 9 ) );
294 commentSubmitList.add( commentSubmit );
295 }
296
297 daoUtil.free( );
298
299 return commentSubmitList;
300 }
301
302
303
304
305 @Override
306 public List<CommentSubmit> findDiggCommentByDate( Date dateCreation, Plugin plugin )
307 {
308 List<CommentSubmit> listComment = new ArrayList<CommentSubmit>( );
309 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COMMENTED_DIGG_SUBMIT, plugin );
310 daoUtil.setTimestamp( 1, new Timestamp( dateCreation.getTime( ) ) );
311 daoUtil.executeQuery( );
312
313 while ( daoUtil.next( ) )
314 {
315 CommentSubmit commentSubmit = new CommentSubmit( );
316 commentSubmit.setIdCommentSubmit( daoUtil.getInt( 1 ) );
317
318 DiggSubmit diggSubmit = new DiggSubmit( );
319 diggSubmit.setIdDiggSubmit( daoUtil.getInt( 2 ) );
320 commentSubmit.setDiggSubmit( diggSubmit );
321
322 commentSubmit.setDateComment( daoUtil.getTimestamp( 3 ) );
323 commentSubmit.setValue( daoUtil.getString( 4 ) );
324 commentSubmit.setActive( daoUtil.getBoolean( 5 ) );
325 commentSubmit.setLuteceUserKey( daoUtil.getString( 6 ) );
326 commentSubmit.setOfficialAnswer( daoUtil.getBoolean( 7 ) );
327 commentSubmit.setIdParent( daoUtil.getInt( 8 ) );
328 commentSubmit.setDateModify( daoUtil.getTimestamp( 9 ) );
329 listComment.add( commentSubmit );
330 }
331
332 daoUtil.free( );
333
334 return listComment;
335 }
336
337
338
339
340 @Override
341 public int selectCountByFilter( SubmitFilter filter, Plugin plugin )
342 {
343 int nIdCount = 0;
344 List<String> listStrFilter = new ArrayList<String>( );
345
346 if ( filter.containsIdDigg( ) )
347 {
348 listStrFilter.add( SQL_FILTER_ID_DIGG );
349 }
350
351 if ( filter.containsIdDiggSubmit( ) )
352 {
353 listStrFilter.add( SQL_FILTER_ID_DIGG_SUBMIT );
354 }
355
356 if ( filter.containsIdCommentSubmitState( ) )
357 {
358 listStrFilter.add( SQL_FILTER_COMMENT_SUBMIT_STATE );
359 }
360
361 if ( filter.containsIdContainsCommentDisable( ) )
362 {
363 listStrFilter.add( ( filter.getIdContainsCommentDisable( ) == SubmitFilter.ID_TRUE )
364 ? SQL_FILTER_CONTAINS_SUB_COMMENT_DISABLE : SQL_FILTER_NOT_CONTAINS_SUB_COMMENT_DISABLE );
365 }
366
367 String strSQL = DiggUtils.buildRequestWithFilter( SQL_QUERY_SELECT_COUNT_BY_FILTER, listStrFilter, null );
368 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
369 int nIndex = 1;
370
371 if ( filter.containsIdDigg( ) )
372 {
373 daoUtil.setInt( nIndex, filter.getIdDigg( ) );
374 nIndex++;
375 }
376
377 if ( filter.containsIdDiggSubmit( ) )
378 {
379 daoUtil.setInt( nIndex, filter.getIdDiggSubmit( ) );
380 nIndex++;
381 }
382
383 if ( filter.containsIdCommentSubmitState( ) )
384 {
385 daoUtil.setBoolean( nIndex, filter.convertIdBoolean( filter.getIdCommentSubmitState( ) ) );
386 nIndex++;
387 }
388
389 daoUtil.executeQuery( );
390
391 if ( daoUtil.next( ) )
392 {
393 nIdCount = daoUtil.getInt( 1 );
394 }
395
396 daoUtil.free( );
397
398 return nIdCount;
399 }
400
401
402
403
404
405
406 private String getOrderBy( List<Integer> listSortBy )
407 {
408 StringBuffer strOrderBy = new StringBuffer( );
409 int ncpt = 0;
410
411 if ( ( listSortBy != null ) && ( listSortBy.size( ) != 0 ) )
412 {
413 strOrderBy.append( SQL_ORDER_BY );
414
415 for ( Integer sort : listSortBy )
416 {
417 ncpt++;
418
419 switch ( sort )
420 {
421 case SubmitFilter.SORT_BY_DATE_RESPONSE_ASC:
422 strOrderBy.append( SQL_FILTER_SORT_BY_DATE_COMMENT_ASC );
423
424 break;
425
426 case SubmitFilter.SORT_BY_DATE_RESPONSE_DESC:
427 strOrderBy.append( SQL_FILTER_SORT_BY_DATE_COMMENT_DESC );
428
429 break;
430
431 case SubmitFilter.SORT_BY_DATE_MODIFY_ASC:
432 strOrderBy.append( SQL_FILTER_SORT_BY_DATE_MODIFY_COMMENT_ASC );
433
434 break;
435
436 case SubmitFilter.SORT_BY_DATE_MODIFY_DESC:
437 strOrderBy.append( SQL_FILTER_SORT_BY_DATE_MODIFY_COMMENT_DESC );
438
439 break;
440
441 default:
442 strOrderBy.append( SQL_FILTER_SORT_BY_DATE_MODIFY_COMMENT_DESC );
443
444 break;
445 }
446
447 if ( ncpt < listSortBy.size( ) )
448 {
449 strOrderBy.append( "," );
450 }
451 }
452 }
453
454 return strOrderBy.toString( );
455 }
456
457
458
459
460 @Override
461 public void deleteByIdParent( int nIdParentCommentSubmit, Plugin plugin )
462 {
463 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_PARENT, plugin );
464 daoUtil.setInt( 1, nIdParentCommentSubmit );
465 daoUtil.executeUpdate( );
466 daoUtil.free( );
467 }
468 }