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.List;
44
45
46
47
48
49 public final class DiggSubmitDAO implements IDiggSubmitDAO
50 {
51
52 private static final String SQL_QUERY_NEW_PK = "SELECT MAX( id_digg_submit ) FROM digglike_digg_submit";
53 private static final String SQL_QUERY_SELECT_DIGG_SUBMIT_BY_FILTER = "SELECT d.id_digg_submit,d.id_digg,s.id_state,s.title,s.number,d.date_response, " +
54 "d.vote_number,d.score_number,d.id_category,d.digg_submit_value,d.digg_submit_title,d.comment_enable_number,d.digg_submit_value_show_in_the_list, " +
55 "d.reported, d.lutece_user_key, d.digg_submit_list_order, d.digg_submit_type,d.number_view,d.disable_vote,d.is_pinned,d.disable_comment,d.id_image_resource,d.comment_number FROM digglike_digg_submit d,digglike_digg_submit_state s ";
56 private static final String SQL_FILTER_ID_DIGG_SUBMIT = " d.id_digg_submit = ? ";
57 private static final String SQL_FILTER_ID_DIGG = " d.id_digg = ? ";
58 private static final String SQL_FILTER_ID_CATEGORY = " d.id_category = ? ";
59 private static final String SQL_FILTER_ID_TYPE = " d.digg_submit_type = ? ";
60 private static final String SQL_FILTER_ID_DIGG_SUBMIT_STATE = " d.id_state = ? ";
61 private static final String SQL_FILTER_DATE_FIRST_SUBMIT = " d.date_response >= ? ";
62 private static final String SQL_FILTER_DATE_LAST_SUBMIT = " d.date_response <= ? ";
63 private static final String SQL_FILTER_REPORTED = " d.reported = ? ";
64 private static final String SQL_FILTER_IS_PINNED = " d.is_pinned = ? ";
65 private static final String SQL_FILTER_NUMBER_VOTE = " d.vote_number = ? ";
66 private static final String SQL_FILTER_CONTAINS_COMMENT_DISABLE = " d.comment_number <> d.comment_enable_number ";
67 private static final String SQL_FILTER_NOT_CONTAINS_COMMENT_DISABLE = " d.comment_number = d.comment_enable_number ";
68 private static final String SQL_FILTER_LUTECE_USER_KEY = " d.lutece_user_key = ? ";
69 private static final String SQL_FILTER_SORT_BY_DATE_RESPONSE_ASC = " d.date_response ASC ";
70 private static final String SQL_FILTER_SORT_BY_DATE_RESPONSE_DESC = " d.date_response DESC ";
71 private static final String SQL_FILTER_SORT_BY_SCORE_ASC = " d.score_number ASC ";
72 private static final String SQL_FILTER_SORT_BY_NUMBER_VOTE_ASC = " d.vote_number ASC ";
73 private static final String SQL_FILTER_SORT_BY_NUMBER_VOTE_DESC = " d.vote_number DESC ";
74 private static final String SQL_FILTER_SORT_BY_SCORE_DESC = " d.score_number DESC ";
75 private static final String SQL_FILTER_SORT_BY_NUMBER_COMMENT_ENABLE_ASC = " d.comment_enable_number ASC ";
76 private static final String SQL_FILTER_SORT_BY_NUMBER_COMMENT_ENABLE_DESC = " d.comment_enable_number DESC ";
77 private static final String SQL_FILTER_SORT_BY_NUMBER_VIEW_ASC = " d.number_view ASC ";
78 private static final String SQL_FILTER_SORT_BY_NUMBER_VIEW_DESC = " d.number_view DESC ";
79 private static final String SQL_FILTER_SORT_MANUALLY = " d.digg_submit_list_order ASC ";
80 private static final String SQL_ORDER_BY = " ORDER BY ";
81 private static final String SQL_JOIN_STATE = " d.id_state = s.id_state ";
82 private static final String SQL_CONSTANTE_WHERE = " WHERE ";
83 private static final String SQL_CONSTANT_AND = " AND ";
84 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = SQL_QUERY_SELECT_DIGG_SUBMIT_BY_FILTER +
85 SQL_CONSTANTE_WHERE + SQL_JOIN_STATE + SQL_CONSTANT_AND + SQL_FILTER_ID_DIGG_SUBMIT;
86 private static final String SQL_QUERY_INSERT = "INSERT INTO digglike_digg_submit ( id_digg_submit,id_digg,id_state,date_response, " +
87 "vote_number,score_number,id_category,digg_submit_value ,digg_submit_title,comment_enable_number,digg_submit_value_show_in_the_list,reported," +
88 "lutece_user_key,digg_submit_list_order,digg_submit_type,number_view,disable_vote,is_pinned,disable_comment,id_image_resource,comment_number ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
89 private static final String SQL_QUERY_DELETE = "DELETE FROM digglike_digg_submit WHERE id_digg_submit = ? ";
90 private static final String SQL_QUERY_UPDATE = "UPDATE digglike_digg_submit SET " +
91 "id_digg_submit=?,id_digg=?,id_state=?" +
92 ",vote_number=?,score_number=?,id_category=?,digg_submit_value=?,digg_submit_title=? ,comment_enable_number=? ,digg_submit_value_show_in_the_list=?,reported=?,lutece_user_key= ?,digg_submit_list_order=?,digg_submit_type=?,number_view=?,disable_vote=?,is_pinned= ?,disable_comment=?,id_image_resource= ?,comment_number = ? " +
93 "WHERE id_digg_submit=? ";
94 private static final String SQL_QUERY_SELECT_ID_DIGG_SUBMIT_BY_FILTER = "SELECT d.id_digg_submit " +
95 "FROM digglike_digg_submit d ";
96 private static final String SQL_QUERY_SELECT_COUNT_BY_FILTER = "SELECT COUNT(d.id_digg_submit) " +
97 "FROM digglike_digg_submit d ";
98
99
100 private static final String SQL_QUERY_SELECT_MAX_DIGG_SUBMIT_LIST_ORDER = "SELECT max(d.digg_submit_list_order) FROM digglike_digg_submit d WHERE d.id_digg = ? AND " +
101 SQL_FILTER_IS_PINNED;
102 private static final String SQL_QUERY_UPDATE_DIGG_SUBMIT_LIST_ORDER = "UPDATE digglike_digg_submit SET digg_submit_list_order = ? WHERE id_digg_submit = ?";
103
104
105
106
107
108
109
110 private int newPrimaryKey( Plugin plugin )
111 {
112 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
113 daoUtil.executeQuery( );
114
115 int nKey;
116
117 if ( !daoUtil.next( ) )
118 {
119
120 nKey = 1;
121 }
122
123 nKey = daoUtil.getInt( 1 ) + 1;
124 daoUtil.free( );
125
126 return nKey;
127 }
128
129
130
131
132
133
134
135
136 public int insert( DiggSubmit diggSubmit, Plugin plugin )
137 {
138 Timestamp timestamp = new java.sql.Timestamp( new java.util.Date( ).getTime( ) );
139 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
140
141 if ( diggSubmit.getIdDiggSubmit( ) == 0 )
142 {
143 diggSubmit.setIdDiggSubmit( newPrimaryKey( plugin ) );
144 }
145
146 diggSubmit.setDiggSubmitOrder( maxOrderDiggSubmit( diggSubmit.getDigg( ).getIdDigg( ), false, plugin ) + 1 );
147 daoUtil.setInt( 1, diggSubmit.getIdDiggSubmit( ) );
148 daoUtil.setInt( 2, diggSubmit.getDigg( ).getIdDigg( ) );
149 daoUtil.setInt( 3, diggSubmit.getDiggSubmitState( ).getIdDiggSubmitState( ) );
150 daoUtil.setTimestamp( 4, timestamp );
151 daoUtil.setInt( 5, diggSubmit.getNumberVote( ) );
152 daoUtil.setInt( 6, diggSubmit.getNumberScore( ) );
153
154 if ( diggSubmit.getCategory( ) != null )
155 {
156 daoUtil.setInt( 7, diggSubmit.getCategory( ).getIdCategory( ) );
157 }
158 else
159 {
160 daoUtil.setIntNull( 7 );
161 }
162
163 daoUtil.setString( 8, diggSubmit.getDiggSubmitValue( ) );
164 daoUtil.setString( 9, diggSubmit.getDiggSubmitTitle( ) );
165 daoUtil.setInt( 10, diggSubmit.getNumberCommentEnable( ) );
166 daoUtil.setString( 11, diggSubmit.getDiggSubmitValueShowInTheList( ) );
167 daoUtil.setBoolean( 12, diggSubmit.isReported( ) );
168 daoUtil.setString( 13, diggSubmit.getLuteceUserKey( ) );
169 daoUtil.setInt( 14, diggSubmit.getDiggSubmitOrder( ) );
170
171 if ( diggSubmit.getDiggSubmitType( ) != null )
172 {
173 daoUtil.setInt( 15, diggSubmit.getDiggSubmitType( ).getIdType( ) );
174 }
175 else
176 {
177 daoUtil.setIntNull( 15 );
178 }
179
180 daoUtil.setInt( 16, diggSubmit.getNumberView( ) );
181 daoUtil.setBoolean( 17, diggSubmit.isDisableVote( ) );
182 daoUtil.setBoolean( 18, diggSubmit.isPinned( ) );
183 daoUtil.setBoolean( 19, diggSubmit.isDisableComment( ) );
184
185 if ( diggSubmit.getIdImageResource( ) != null )
186 {
187 daoUtil.setInt( 20, diggSubmit.getIdImageResource( ) );
188 }
189 else
190 {
191 daoUtil.setIntNull( 20 );
192 }
193
194 daoUtil.setInt( 21, diggSubmit.getNumberComment( ) );
195 daoUtil.executeUpdate( );
196 daoUtil.free( );
197
198 return diggSubmit.getIdDiggSubmit( );
199 }
200
201
202
203
204
205
206
207
208 public DiggSubmit load( int nIdDiggSubmit, Plugin plugin )
209 {
210 DiggSubmit diggSubmit = null;
211
212 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
213 daoUtil.setInt( 1, nIdDiggSubmit );
214 daoUtil.executeQuery( );
215
216 if ( daoUtil.next( ) )
217 {
218 diggSubmit = getRow( daoUtil );
219 }
220
221 daoUtil.free( );
222
223 return diggSubmit;
224 }
225
226
227
228
229
230
231
232 public void delete( int nIdDiggSubmit, Plugin plugin )
233 {
234 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
235 daoUtil.setInt( 1, nIdDiggSubmit );
236 daoUtil.executeUpdate( );
237 daoUtil.free( );
238 }
239
240
241
242
243
244
245
246 public void store( DiggSubmit diggSubmit, Plugin plugin )
247 {
248 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
249 daoUtil.setInt( 1, diggSubmit.getIdDiggSubmit( ) );
250 daoUtil.setInt( 2, diggSubmit.getDigg( ).getIdDigg( ) );
251 daoUtil.setInt( 3, diggSubmit.getDiggSubmitState( ).getIdDiggSubmitState( ) );
252 daoUtil.setInt( 4, diggSubmit.getNumberVote( ) );
253 daoUtil.setInt( 5, diggSubmit.getNumberScore( ) );
254
255 if ( diggSubmit.getCategory( ) != null )
256 {
257 daoUtil.setInt( 6, diggSubmit.getCategory( ).getIdCategory( ) );
258 }
259 else
260 {
261 daoUtil.setIntNull( 6 );
262 }
263
264 daoUtil.setString( 7, diggSubmit.getDiggSubmitValue( ) );
265 daoUtil.setString( 8, diggSubmit.getDiggSubmitTitle( ) );
266 daoUtil.setInt( 9, diggSubmit.getNumberCommentEnable( ) );
267 daoUtil.setString( 10, diggSubmit.getDiggSubmitValueShowInTheList( ) );
268 daoUtil.setBoolean( 11, diggSubmit.isReported( ) );
269 daoUtil.setString( 12, diggSubmit.getLuteceUserKey( ) );
270 daoUtil.setInt( 13, diggSubmit.getDiggSubmitOrder( ) );
271
272 if ( diggSubmit.getDiggSubmitType( ) != null )
273 {
274 daoUtil.setInt( 14, diggSubmit.getDiggSubmitType( ).getIdType( ) );
275 }
276 else
277 {
278 daoUtil.setIntNull( 14 );
279 }
280
281 daoUtil.setInt( 15, diggSubmit.getNumberView( ) );
282 daoUtil.setBoolean( 16, diggSubmit.isDisableVote( ) );
283 daoUtil.setBoolean( 17, diggSubmit.isPinned( ) );
284 daoUtil.setBoolean( 18, diggSubmit.isDisableComment( ) );
285
286 if ( diggSubmit.getIdImageResource( ) != null )
287 {
288 daoUtil.setInt( 19, diggSubmit.getIdImageResource( ) );
289 }
290 else
291 {
292 daoUtil.setIntNull( 19 );
293 }
294
295 daoUtil.setInt( 20, diggSubmit.getNumberComment( ) );
296 daoUtil.setInt( 21, diggSubmit.getIdDiggSubmit( ) );
297
298 daoUtil.executeUpdate( );
299 daoUtil.free( );
300 }
301
302
303
304
305
306
307
308 public List<DiggSubmit> selectListByFilter( SubmitFilter filter, Plugin plugin )
309 {
310 List<DiggSubmit> diggSubmitList = new ArrayList<DiggSubmit>( );
311
312 List<String> listStrFilter = new ArrayList<String>( );
313 String strOrderBy = null;
314
315 listStrFilter.add( SQL_JOIN_STATE );
316
317 if ( filter.containsIdDiggSubmit( ) )
318 {
319 listStrFilter.add( SQL_FILTER_ID_DIGG_SUBMIT );
320 }
321
322 if ( filter.containsIdDigg( ) )
323 {
324 listStrFilter.add( SQL_FILTER_ID_DIGG );
325 }
326
327 if ( filter.containsIdDiggSubmitState( ) )
328 {
329 listStrFilter.add( SQL_FILTER_ID_DIGG_SUBMIT_STATE );
330 }
331
332 if ( filter.containsIdCategory( ) )
333 {
334 listStrFilter.add( SQL_FILTER_ID_CATEGORY );
335 }
336
337 if ( filter.containsDateFirst( ) )
338 {
339 listStrFilter.add( SQL_FILTER_DATE_FIRST_SUBMIT );
340 }
341
342 if ( filter.containsDateLast( ) )
343 {
344 listStrFilter.add( SQL_FILTER_DATE_LAST_SUBMIT );
345 }
346
347 if ( filter.containsIdReported( ) )
348 {
349 listStrFilter.add( SQL_FILTER_REPORTED );
350 }
351
352 if ( filter.containsNumberVote( ) )
353 {
354 listStrFilter.add( SQL_FILTER_NUMBER_VOTE );
355 }
356
357 if ( filter.containsIdPinned( ) )
358 {
359 listStrFilter.add( SQL_FILTER_IS_PINNED );
360 }
361
362 if ( filter.containsIdType( ) )
363 {
364 listStrFilter.add( SQL_FILTER_ID_TYPE );
365 }
366
367 if ( filter.containsIdContainsCommentDisable( ) )
368 {
369 listStrFilter.add( ( filter.getIdContainsCommentDisable( ) == SubmitFilter.ID_TRUE )
370 ? SQL_FILTER_CONTAINS_COMMENT_DISABLE : SQL_FILTER_NOT_CONTAINS_COMMENT_DISABLE );
371 }
372
373 if ( filter.containsSortBy( ) )
374 {
375 strOrderBy = getOrderBy( filter.getSortBy( ) );
376 }
377
378 String strSQL = DiggUtils.buildRequestWithFilter( SQL_QUERY_SELECT_DIGG_SUBMIT_BY_FILTER, listStrFilter,
379 strOrderBy );
380 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
381 int nIndex = 1;
382
383 if ( filter.containsIdDiggSubmit( ) )
384 {
385 daoUtil.setInt( nIndex, filter.getIdDiggSubmit( ) );
386 nIndex++;
387 }
388
389 if ( filter.containsIdDigg( ) )
390 {
391 daoUtil.setInt( nIndex, filter.getIdDigg( ) );
392 nIndex++;
393 }
394
395 if ( filter.containsIdDiggSubmitState( ) )
396 {
397 daoUtil.setInt( nIndex, filter.getIdDiggSubmitState( ) );
398 nIndex++;
399 }
400
401 if ( filter.containsIdCategory( ) )
402 {
403 daoUtil.setInt( nIndex, filter.getIdCategory( ) );
404 nIndex++;
405 }
406
407 if ( filter.containsDateFirst( ) )
408 {
409 daoUtil.setTimestamp( nIndex, filter.getDateFirst( ) );
410 nIndex++;
411 }
412
413 if ( filter.containsDateLast( ) )
414 {
415 daoUtil.setTimestamp( nIndex, filter.getDateLast( ) );
416 nIndex++;
417 }
418
419 if ( filter.containsIdReported( ) )
420 {
421 daoUtil.setBoolean( nIndex, filter.convertIdBoolean( filter.getIdReported( ) ) );
422 nIndex++;
423 }
424
425 if ( filter.containsNumberVote( ) )
426 {
427 daoUtil.setInt( nIndex, filter.getNumberVote( ) );
428 nIndex++;
429 }
430
431 if ( filter.containsIdPinned( ) )
432 {
433 daoUtil.setInt( nIndex, filter.getIdPinned( ) );
434 nIndex++;
435 }
436
437 if ( filter.containsIdType( ) )
438 {
439 daoUtil.setInt( nIndex, filter.getIdType( ) );
440 nIndex++;
441 }
442
443 daoUtil.executeQuery( );
444
445 while ( daoUtil.next( ) )
446 {
447 diggSubmitList.add( getRow( daoUtil ) );
448 }
449
450 daoUtil.free( );
451
452 return diggSubmitList;
453 }
454
455
456
457
458
459
460
461 public List<Integer> selectIdListByFilter( SubmitFilter filter, Plugin plugin )
462 {
463 List<Integer> diggSubmitIdList = new ArrayList<Integer>( );
464 List<String> listStrFilter = new ArrayList<String>( );
465 String strOrderBy = null;
466
467 if ( filter.containsIdDigg( ) )
468 {
469 listStrFilter.add( SQL_FILTER_ID_DIGG );
470 }
471
472 if ( filter.containsIdDiggSubmit( ) )
473 {
474 listStrFilter.add( SQL_FILTER_ID_DIGG_SUBMIT );
475 }
476
477 if ( filter.containsIdDiggSubmitState( ) )
478 {
479 listStrFilter.add( SQL_FILTER_ID_DIGG_SUBMIT_STATE );
480 }
481
482 if ( filter.containsIdCategory( ) )
483 {
484 listStrFilter.add( SQL_FILTER_ID_CATEGORY );
485 }
486
487 if ( filter.containsDateFirst( ) )
488 {
489 listStrFilter.add( SQL_FILTER_DATE_FIRST_SUBMIT );
490 }
491
492 if ( filter.containsDateLast( ) )
493 {
494 listStrFilter.add( SQL_FILTER_DATE_LAST_SUBMIT );
495 }
496
497 if ( filter.containsIdReported( ) )
498 {
499 listStrFilter.add( SQL_FILTER_REPORTED );
500 }
501
502 if ( filter.containsNumberVote( ) )
503 {
504 listStrFilter.add( SQL_FILTER_NUMBER_VOTE );
505 }
506
507 if ( filter.containsIdPinned( ) )
508 {
509 listStrFilter.add( SQL_FILTER_IS_PINNED );
510 }
511 if ( filter.containsLuteceUserKey())
512 {
513 listStrFilter.add( SQL_FILTER_LUTECE_USER_KEY);
514 }
515
516 if ( filter.containsIdType( ) )
517 {
518 listStrFilter.add( SQL_FILTER_ID_TYPE );
519 }
520
521
522
523
524 if ( filter.containsIdContainsCommentDisable( ) )
525 {
526 listStrFilter.add( ( filter.getIdContainsCommentDisable( ) == SubmitFilter.ID_TRUE )
527 ? SQL_FILTER_CONTAINS_COMMENT_DISABLE : SQL_FILTER_NOT_CONTAINS_COMMENT_DISABLE );
528 }
529
530 if ( filter.containsSortBy( ) )
531 {
532 strOrderBy = getOrderBy( filter.getSortBy( ) );
533 }
534
535
536
537
538
539
540
541
542
543
544
545
546
547 String strSQL = DiggUtils.buildRequestWithFilter( SQL_QUERY_SELECT_ID_DIGG_SUBMIT_BY_FILTER, listStrFilter,
548 strOrderBy );
549 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
550 int nIndex = 1;
551
552 if ( filter.containsIdDigg( ) )
553 {
554 daoUtil.setInt( nIndex, filter.getIdDigg( ) );
555 nIndex++;
556 }
557
558 if ( filter.containsIdDiggSubmit( ) )
559 {
560 daoUtil.setInt( nIndex, filter.getIdDiggSubmit( ) );
561 nIndex++;
562 }
563
564 if ( filter.containsIdDiggSubmitState( ) )
565 {
566 daoUtil.setInt( nIndex, filter.getIdDiggSubmitState( ) );
567 nIndex++;
568 }
569
570 if ( filter.containsIdCategory( ) )
571 {
572 daoUtil.setInt( nIndex, filter.getIdCategory( ) );
573 nIndex++;
574 }
575
576 if ( filter.containsDateFirst( ) )
577 {
578 daoUtil.setTimestamp( nIndex, filter.getDateFirst( ) );
579 nIndex++;
580 }
581
582 if ( filter.containsDateLast( ) )
583 {
584 daoUtil.setTimestamp( nIndex, filter.getDateLast( ) );
585 nIndex++;
586 }
587
588 if ( filter.containsIdReported( ) )
589 {
590 daoUtil.setBoolean( nIndex, filter.convertIdBoolean( filter.getIdReported( ) ) );
591 nIndex++;
592 }
593
594 if ( filter.containsNumberVote( ) )
595 {
596 daoUtil.setInt( nIndex, filter.getNumberVote( ) );
597 nIndex++;
598 }
599
600 if ( filter.containsIdPinned( ) )
601 {
602 daoUtil.setInt( nIndex, filter.getIdPinned( ) );
603 nIndex++;
604 }
605
606 if ( filter.containsLuteceUserKey())
607 {
608 daoUtil.setString( nIndex, filter.getLuteceUserKey());
609 nIndex++;
610 }
611
612 if ( filter.containsIdType( ) )
613 {
614 daoUtil.setInt( nIndex, filter.getIdType( ) );
615 nIndex++;
616 }
617
618
619 daoUtil.executeQuery( );
620
621 while ( daoUtil.next( ) )
622 {
623 diggSubmitIdList.add( daoUtil.getInt( 1 ) );
624 }
625
626 daoUtil.free( );
627
628 return diggSubmitIdList;
629 }
630
631
632
633
634
635
636
637 public int selectCountByFilter( SubmitFilter filter, Plugin plugin )
638 {
639 int nIdCount = 0;
640 List<String> listStrFilter = new ArrayList<String>( );
641
642 if ( filter.containsIdDigg( ) )
643 {
644 listStrFilter.add( SQL_FILTER_ID_DIGG );
645 }
646
647 if ( filter.containsIdDiggSubmit( ) )
648 {
649 listStrFilter.add( SQL_FILTER_ID_DIGG_SUBMIT );
650 }
651
652 if ( filter.containsIdDiggSubmitState( ) )
653 {
654 listStrFilter.add( SQL_FILTER_ID_DIGG_SUBMIT_STATE );
655 }
656
657 if ( filter.containsIdCategory( ) )
658 {
659 listStrFilter.add( SQL_FILTER_ID_CATEGORY );
660 }
661
662 if ( filter.containsDateFirst( ) )
663 {
664 listStrFilter.add( SQL_FILTER_DATE_FIRST_SUBMIT );
665 }
666
667 if ( filter.containsDateLast( ) )
668 {
669 listStrFilter.add( SQL_FILTER_DATE_LAST_SUBMIT );
670 }
671
672 if ( filter.containsIdReported( ) )
673 {
674 listStrFilter.add( SQL_FILTER_REPORTED );
675 }
676
677 if ( filter.containsNumberVote( ) )
678 {
679 listStrFilter.add( SQL_FILTER_NUMBER_VOTE );
680 }
681
682 if ( filter.containsIdPinned( ) )
683 {
684 listStrFilter.add( SQL_FILTER_IS_PINNED );
685 }
686
687 if ( filter.containsLuteceUserKey( ) )
688 {
689 listStrFilter.add( SQL_FILTER_LUTECE_USER_KEY );
690 }
691
692 if ( filter.containsIdType( ) )
693 {
694 listStrFilter.add( SQL_FILTER_ID_TYPE );
695 }
696
697 if ( filter.containsIdContainsCommentDisable( ) )
698 {
699 listStrFilter.add( ( filter.getIdContainsCommentDisable( ) == SubmitFilter.ID_TRUE )
700 ? SQL_FILTER_CONTAINS_COMMENT_DISABLE : SQL_FILTER_NOT_CONTAINS_COMMENT_DISABLE );
701 }
702
703 String strSQL = DiggUtils.buildRequestWithFilter( SQL_QUERY_SELECT_COUNT_BY_FILTER, listStrFilter, null );
704 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
705 int nIndex = 1;
706
707 if ( filter.containsIdDigg( ) )
708 {
709 daoUtil.setInt( nIndex, filter.getIdDigg( ) );
710 nIndex++;
711 }
712
713 if ( filter.containsIdDiggSubmit( ) )
714 {
715 daoUtil.setInt( nIndex, filter.getIdDiggSubmit( ) );
716 nIndex++;
717 }
718
719 if ( filter.containsIdDiggSubmitState( ) )
720 {
721 daoUtil.setInt( nIndex, filter.getIdDiggSubmitState( ) );
722 nIndex++;
723 }
724
725 if ( filter.containsIdCategory( ) )
726 {
727 daoUtil.setInt( nIndex, filter.getIdCategory( ) );
728 nIndex++;
729 }
730
731 if ( filter.containsDateFirst( ) )
732 {
733 daoUtil.setTimestamp( nIndex, filter.getDateFirst( ) );
734 nIndex++;
735 }
736
737 if ( filter.containsDateLast( ) )
738 {
739 daoUtil.setTimestamp( nIndex, filter.getDateLast( ) );
740 nIndex++;
741 }
742
743 if ( filter.containsIdReported( ) )
744 {
745 daoUtil.setBoolean( nIndex, filter.convertIdBoolean( filter.getIdReported( ) ) );
746 nIndex++;
747 }
748
749 if ( filter.containsNumberVote( ) )
750 {
751 daoUtil.setInt( nIndex, filter.getNumberVote( ) );
752 nIndex++;
753 }
754
755 if ( filter.containsIdPinned( ) )
756 {
757 daoUtil.setInt( nIndex, filter.getIdPinned( ) );
758 nIndex++;
759 }
760
761 if ( filter.containsLuteceUserKey( ) )
762 {
763 daoUtil.setString( nIndex, filter.getLuteceUserKey( ) );
764 nIndex++;
765 }
766
767 if ( filter.containsIdType( ) )
768 {
769 daoUtil.setInt( nIndex, filter.getIdType( ) );
770 nIndex++;
771 }
772
773 daoUtil.executeQuery( );
774
775 if ( daoUtil.next( ) )
776 {
777 nIdCount = daoUtil.getInt( 1 );
778 }
779
780 daoUtil.free( );
781
782 return nIdCount;
783 }
784
785
786
787
788
789
790 private String getOrderBy( List<Integer> listSortBy )
791 {
792 StringBuffer strOrderBy = new StringBuffer( );
793 String strReturn = DiggUtils.EMPTY_STRING;
794 int ncpt = 0;
795
796 if ( ( listSortBy != null ) && ( listSortBy.size( ) != 0 ) )
797 {
798 strOrderBy.append( SQL_ORDER_BY );
799
800 for ( Integer sort : listSortBy )
801 {
802 ncpt++;
803
804 switch ( sort )
805 {
806 case SubmitFilter.SORT_BY_DATE_RESPONSE_ASC:
807 strOrderBy.append( SQL_FILTER_SORT_BY_DATE_RESPONSE_ASC );
808
809 break;
810
811 case SubmitFilter.SORT_BY_DATE_RESPONSE_DESC:
812 strOrderBy.append( SQL_FILTER_SORT_BY_DATE_RESPONSE_DESC );
813
814 break;
815
816 case SubmitFilter.SORT_BY_SCORE_ASC:
817 strOrderBy.append( SQL_FILTER_SORT_BY_SCORE_ASC );
818
819 break;
820
821 case SubmitFilter.SORT_BY_SCORE_DESC:
822 strOrderBy.append( SQL_FILTER_SORT_BY_SCORE_DESC );
823
824 break;
825
826 case SubmitFilter.SORT_BY_NUMBER_COMMENT_ASC:
827 strOrderBy.append( SQL_FILTER_SORT_BY_NUMBER_COMMENT_ENABLE_ASC );
828
829 break;
830
831 case SubmitFilter.SORT_BY_NUMBER_COMMENT_DESC:
832 strOrderBy.append( SQL_FILTER_SORT_BY_NUMBER_COMMENT_ENABLE_DESC );
833
834 break;
835
836 case SubmitFilter.SORT_BY_NUMBER_VIEW_ASC:
837 strOrderBy.append( SQL_FILTER_SORT_BY_NUMBER_VIEW_ASC );
838
839 break;
840
841 case SubmitFilter.SORT_BY_NUMBER_VIEW_DESC:
842 strOrderBy.append( SQL_FILTER_SORT_BY_NUMBER_VIEW_DESC );
843
844 break;
845
846 case SubmitFilter.SORT_BY_NUMBER_VOTE_ASC:
847 strOrderBy.append( SQL_FILTER_SORT_BY_NUMBER_VOTE_ASC );
848
849 break;
850
851 case SubmitFilter.SORT_BY_NUMBER_VOTE_DESC:
852 strOrderBy.append( SQL_FILTER_SORT_BY_NUMBER_VOTE_DESC );
853
854 break;
855
856 case SubmitFilter.SORT_MANUALLY:
857 strOrderBy.append( SQL_FILTER_SORT_MANUALLY );
858
859 break;
860
861 default:
862 break;
863 }
864
865 if ( ncpt < listSortBy.size( ) )
866 {
867 strOrderBy.append( "," );
868 }
869 }
870
871 strReturn = strOrderBy.toString( );
872
873 if ( strReturn.endsWith( "," ) )
874 {
875 strReturn = strReturn.substring( 0, strReturn.length( ) - 1 );
876 }
877 }
878
879 return strReturn;
880 }
881
882
883
884
885
886
887
888
889
890
891 public void storeDiggSubmitOrder( int nNewOrder, int nId, Plugin plugin )
892 {
893 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_DIGG_SUBMIT_LIST_ORDER, plugin );
894 daoUtil.setInt( 1, nNewOrder );
895 daoUtil.setInt( 2, nId );
896 daoUtil.executeUpdate( );
897 daoUtil.free( );
898 }
899
900
901
902
903
904
905
906 public int maxOrderDiggSubmit( int nIdDigg, boolean bListPinned, Plugin plugin )
907 {
908 int nOrder = 0;
909 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MAX_DIGG_SUBMIT_LIST_ORDER, plugin );
910 daoUtil.setInt( 1, nIdDigg );
911 daoUtil.setBoolean( 2, bListPinned );
912 daoUtil.executeQuery( );
913
914 if ( daoUtil.next( ) )
915 {
916 nOrder = daoUtil.getInt( 1 );
917 }
918
919 daoUtil.free( );
920
921 return nOrder;
922 }
923
924
925
926
927
928
929 private DiggSubmit getRow( DAOUtil daoUtil )
930 {
931 DiggSubmit diggSubmit = new DiggSubmit( );
932 diggSubmit.setIdDiggSubmit( daoUtil.getInt( 1 ) );
933
934 Digg digg = new Digg( );
935 digg.setIdDigg( daoUtil.getInt( 2 ) );
936 diggSubmit.setDigg( digg );
937
938 DiggSubmitState diggSubmitState = new DiggSubmitState( );
939 diggSubmitState.setIdDiggSubmitState( daoUtil.getInt( 3 ) );
940 diggSubmitState.setTitle( daoUtil.getString( 4 ) );
941 diggSubmitState.setNumber( daoUtil.getInt( 5 ) );
942 diggSubmit.setDiggSubmitState( diggSubmitState );
943 diggSubmit.setDateResponse( daoUtil.getTimestamp( 6 ) );
944 diggSubmit.setNumberVote( daoUtil.getInt( 7 ) );
945 diggSubmit.setNumberScore( daoUtil.getInt( 8 ) );
946
947 if ( daoUtil.getObject( 9 ) != null )
948 {
949 Category category = new Category( );
950 category.setIdCategory( daoUtil.getInt( 9 ) );
951 diggSubmit.setCategory( category );
952 }
953
954 diggSubmit.setDiggSubmitValue( daoUtil.getString( 10 ) );
955 diggSubmit.setDiggSubmitTitle( daoUtil.getString( 11 ) );
956 diggSubmit.setNumberCommentEnable( daoUtil.getInt( 12 ) );
957 diggSubmit.setDiggSubmitValueShowInTheList( daoUtil.getString( 13 ) );
958 diggSubmit.setReported( daoUtil.getBoolean( 14 ) );
959 diggSubmit.setLuteceUserKey( daoUtil.getString( 15 ) );
960 diggSubmit.setDiggSubmitOrder( daoUtil.getInt( 16 ) );
961
962 if ( daoUtil.getObject( 17 ) != null )
963 {
964 DiggSubmitType diggSubmitType = new DiggSubmitType( );
965 diggSubmitType.setIdType( daoUtil.getInt( 17 ) );
966 diggSubmit.setDiggSubmitType( diggSubmitType );
967 }
968
969 diggSubmit.setNumberView( daoUtil.getInt( 18 ) );
970 diggSubmit.setDisableVote( daoUtil.getBoolean( 19 ) );
971 diggSubmit.setPinned( daoUtil.getBoolean( 20 ) );
972 diggSubmit.setDisableComment( daoUtil.getBoolean( 21 ) );
973
974 if ( daoUtil.getObject( 22 ) != null )
975 {
976 diggSubmit.setIdImageResource( daoUtil.getInt( 22 ) );
977 }
978
979 diggSubmit.setNumberComment( daoUtil.getInt( 23 ) );
980
981 return diggSubmit;
982 }
983 }