1
14
15 package com.liferay.portlet.tags.service.persistence;
16
17 import com.liferay.portal.SystemException;
18 import com.liferay.portal.kernel.dao.orm.QueryPos;
19 import com.liferay.portal.kernel.dao.orm.QueryUtil;
20 import com.liferay.portal.kernel.dao.orm.SQLQuery;
21 import com.liferay.portal.kernel.dao.orm.Session;
22 import com.liferay.portal.kernel.dao.orm.Type;
23 import com.liferay.portal.kernel.util.CalendarUtil;
24 import com.liferay.portal.kernel.util.StringPool;
25 import com.liferay.portal.kernel.util.StringUtil;
26 import com.liferay.portal.kernel.util.Validator;
27 import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
28 import com.liferay.portlet.tags.model.TagsAsset;
29 import com.liferay.portlet.tags.model.impl.TagsAssetImpl;
30 import com.liferay.util.dao.orm.CustomSQLUtil;
31
32 import java.sql.Timestamp;
33
34 import java.util.Date;
35 import java.util.Iterator;
36 import java.util.List;
37
38
43 public class TagsAssetFinderImpl
44 extends BasePersistenceImpl<TagsAsset> implements TagsAssetFinder {
45
46 public static String COUNT_BY_AND_ENTRY_IDS =
47 TagsAssetFinder.class.getName() + ".countByAndEntryIds";
48
49 public static String COUNT_BY_OR_ENTRY_IDS =
50 TagsAssetFinder.class.getName() + ".countByOrEntryIds";
51
52 public static String FIND_BY_AND_ENTRY_IDS =
53 TagsAssetFinder.class.getName() + ".findByAndEntryIds";
54
55 public static String FIND_BY_OR_ENTRY_IDS =
56 TagsAssetFinder.class.getName() + ".findByOrEntryIds";
57
58 public static String FIND_BY_VIEW_COUNT =
59 TagsAssetFinder.class.getName() + ".findByViewCount";
60
61 public static String[] ORDER_BY_COLUMNS = new String[] {
62 "title", "createDate", "modifiedDate", "publishDate", "expirationDate",
63 "priority", "viewCount"
64 };
65
66 public static String[] ORDER_BY_TYPE = new String[] {
67 "ASC", "DESC"
68 };
69
70 public int countAssets(
71 long groupId, long[] classNameIds, boolean excludeZeroViewCount,
72 Date publishDate, Date expirationDate)
73 throws SystemException {
74
75 Session session = null;
76
77 try {
78 session = openSession();
79
80 StringBuilder sb = new StringBuilder();
81
82 sb.append("SELECT COUNT(assetId) AS COUNT_VALUE ");
83 sb.append("FROM TagsAsset WHERE");
84 sb.append(" (visible = ?)");
85
86 if (excludeZeroViewCount) {
87 sb.append(" AND (TagsAsset.viewCount > 0)");
88 }
89
90 sb.append("[$DATES$]");
91
92 if (groupId > 0) {
93 sb.append(" AND (TagsAsset.groupId = ?)");
94 }
95
96 sb.append(getClassNameIds(classNameIds));
97
98 String sql = sb.toString();
99
100 sql = getDates(sql, publishDate, expirationDate);
101
102 SQLQuery q = session.createSQLQuery(sql);
103
104 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
105
106 QueryPos qPos = QueryPos.getInstance(q);
107
108 qPos.add(true);
109
110 setDates(qPos, publishDate, expirationDate);
111
112 if (groupId > 0) {
113 setGroupId(qPos, groupId);
114 }
115
116 setClassNamedIds(qPos, classNameIds);
117
118 Iterator<Long> itr = q.list().iterator();
119
120 if (itr.hasNext()) {
121 Long count = itr.next();
122
123 if (count != null) {
124 return count.intValue();
125 }
126 }
127
128 return 0;
129 }
130 catch (Exception e) {
131 throw new SystemException(e);
132 }
133 finally {
134 closeSession(session);
135 }
136 }
137
138 public int countByAndEntryIds(
139 long groupId, long[] classNameIds, long[] entryIds,
140 long[] notEntryIds, boolean excludeZeroViewCount, Date publishDate,
141 Date expirationDate)
142 throws SystemException {
143
144 Session session = null;
145
146 try {
147 session = openSession();
148
149 StringBuilder sb = new StringBuilder();
150
151 sb.append("SELECT COUNT(DISTINCT assetId) AS COUNT_VALUE ");
152 sb.append("FROM TagsAsset WHERE");
153 sb.append(" (visible = ?)");
154
155 if (entryIds.length > 0) {
156 sb.append(" AND TagsAsset.assetId IN (");
157
158 for (int i = 0; i < entryIds.length; i++) {
159 sb.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
160
161 if ((i + 1) < entryIds.length) {
162 sb.append(" AND TagsAsset.assetId IN (");
163 }
164 }
165
166 for (int i = 0; i < entryIds.length; i++) {
167 if ((i + 1) < entryIds.length) {
168 sb.append(StringPool.CLOSE_PARENTHESIS);
169 }
170 }
171
172 if (excludeZeroViewCount) {
173 sb.append(" AND (TagsAsset.viewCount > 0)");
174 }
175
176 sb.append(StringPool.CLOSE_PARENTHESIS);
177 }
178
179 if (notEntryIds.length > 0) {
180 sb.append(" AND (");
181
182 for (int i = 0; i < notEntryIds.length; i++) {
183 sb.append("TagsAsset.assetId NOT IN (");
184 sb.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
185 sb.append(StringPool.CLOSE_PARENTHESIS);
186
187 if ((i + 1) < notEntryIds.length) {
188 sb.append(" OR ");
189 }
190 }
191
192 sb.append(StringPool.CLOSE_PARENTHESIS);
193 }
194
195 sb.append("[$DATES$]");
196
197 if (groupId > 0) {
198 sb.append(" AND (TagsAsset.groupId = ?)");
199 }
200
201 sb.append(getClassNameIds(classNameIds));
202
203 String sql = sb.toString();
204
205 sql = getDates(sql, publishDate, expirationDate);
206
207 SQLQuery q = session.createSQLQuery(sql);
208
209 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
210
211 QueryPos qPos = QueryPos.getInstance(q);
212
213 qPos.add(true);
214
215 setEntryIds(qPos, entryIds);
216 setEntryIds(qPos, notEntryIds);
217 setDates(qPos, publishDate, expirationDate);
218
219 if (groupId > 0) {
220 setGroupId(qPos, groupId);
221 }
222
223 setClassNamedIds(qPos, classNameIds);
224
225 Iterator<Long> itr = q.list().iterator();
226
227 if (itr.hasNext()) {
228 Long count = itr.next();
229
230 if (count != null) {
231 return count.intValue();
232 }
233 }
234
235 return 0;
236 }
237 catch (Exception e) {
238 throw new SystemException(e);
239 }
240 finally {
241 closeSession(session);
242 }
243 }
244
245 public int countByOrEntryIds(
246 long groupId, long[] classNameIds, long[] entryIds,
247 long[] notEntryIds, boolean excludeZeroViewCount, Date publishDate,
248 Date expirationDate)
249 throws SystemException {
250
251 Session session = null;
252
253 try {
254 session = openSession();
255
256 String sql = CustomSQLUtil.get(COUNT_BY_OR_ENTRY_IDS);
257
258 sql = StringUtil.replace(
259 sql, "[$ENTRY_ID$]", getEntryIds(entryIds, StringPool.EQUAL));
260
261 if (notEntryIds.length > 0) {
262 StringBuilder sb = new StringBuilder();
263
264 sb.append(" AND (");
265
266 for (int i = 0; i < notEntryIds.length; i++) {
267 sb.append("TagsAsset.assetId NOT IN (");
268 sb.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
269 sb.append(StringPool.CLOSE_PARENTHESIS);
270
271 if ((i + 1) < notEntryIds.length) {
272 sb.append(" AND ");
273 }
274 }
275
276 sb.append(StringPool.CLOSE_PARENTHESIS);
277
278 sql = StringUtil.replace(
279 sql, "[$NOT_ENTRY_ID$]", sb.toString());
280 }
281 else {
282 sql = StringUtil.replace(
283 sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
284 }
285
286 sql = getDates(sql, publishDate, expirationDate);
287
288 sql += " AND (visible = ?)";
289
290 if (groupId > 0) {
291 sql += " AND (TagsAsset.groupId = ?)";
292 }
293
294 sql += getClassNameIds(classNameIds);
295
296 if (excludeZeroViewCount) {
297 sql += " AND (TagsAsset.viewCount > 0)";
298 }
299
300 SQLQuery q = session.createSQLQuery(sql);
301
302 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
303
304 QueryPos qPos = QueryPos.getInstance(q);
305
306 setEntryIds(qPos, entryIds);
307 setEntryIds(qPos, notEntryIds);
308 setDates(qPos, publishDate, expirationDate);
309
310 qPos.add(true);
311
312 if (groupId > 0) {
313 setGroupId(qPos, groupId);
314 }
315
316 setClassNamedIds(qPos, classNameIds);
317
318 Iterator<Long> itr = q.list().iterator();
319
320 if (itr.hasNext()) {
321 Long count = itr.next();
322
323 if (count != null) {
324 return count.intValue();
325 }
326 }
327
328 return 0;
329 }
330 catch (Exception e) {
331 throw new SystemException(e);
332 }
333 finally {
334 closeSession(session);
335 }
336 }
337
338 public List<TagsAsset> findAssets(
339 long groupId, long[] classNameIds, String orderByCol1,
340 String orderByCol2, String orderByType1, String orderByType2,
341 boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
342 int start, int end)
343 throws SystemException {
344
345 orderByCol1 = checkOrderByCol(orderByCol1);
346 orderByCol2 = checkOrderByCol(orderByCol2);
347 orderByType1 = checkOrderByType(orderByType1);
348 orderByType2 = checkOrderByType(orderByType2);
349
350 Session session = null;
351
352 try {
353 session = openSession();
354
355 StringBuilder sb = new StringBuilder();
356
357 sb.append("SELECT {TagsAsset.*} ");
358 sb.append("FROM TagsAsset WHERE");
359 sb.append(" (visible = ?)");
360
361 if (excludeZeroViewCount) {
362 sb.append(" AND (TagsAsset.viewCount > 0)");
363 }
364
365 sb.append("[$DATES$]");
366
367 if (groupId > 0) {
368 sb.append(" AND (TagsAsset.groupId = ?)");
369 }
370
371 sb.append(getClassNameIds(classNameIds));
372
373 sb.append(" ORDER BY TagsAsset.");
374 sb.append(orderByCol1);
375 sb.append(StringPool.SPACE);
376 sb.append(orderByType1);
377
378 if (Validator.isNotNull(orderByCol2) &&
379 !orderByCol1.equals(orderByCol2)) {
380
381 sb.append(", TagsAsset.");
382 sb.append(orderByCol2);
383 sb.append(StringPool.SPACE);
384 sb.append(orderByType2);
385 }
386
387 String sql = sb.toString();
388
389 sql = getDates(sql, publishDate, expirationDate);
390
391 SQLQuery q = session.createSQLQuery(sql);
392
393 q.addEntity("TagsAsset", TagsAssetImpl.class);
394
395 QueryPos qPos = QueryPos.getInstance(q);
396
397 qPos.add(true);
398
399 setDates(qPos, publishDate, expirationDate);
400
401 if (groupId > 0) {
402 setGroupId(qPos, groupId);
403 }
404
405 setClassNamedIds(qPos, classNameIds);
406
407 return (List<TagsAsset>)QueryUtil.list(q, getDialect(), start, end);
408 }
409 catch (Exception e) {
410 throw new SystemException(e);
411 }
412 finally {
413 closeSession(session);
414 }
415 }
416
417 public List<TagsAsset> findByAndEntryIds(
418 long groupId, long[] classNameIds, long[] entryIds,
419 long[] notEntryIds, String orderByCol1, String orderByCol2,
420 String orderByType1, String orderByType2,
421 boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
422 int start, int end)
423 throws SystemException {
424
425 orderByCol1 = checkOrderByCol(orderByCol1);
426 orderByCol2 = checkOrderByCol(orderByCol2);
427 orderByType1 = checkOrderByType(orderByType1);
428 orderByType2 = checkOrderByType(orderByType2);
429
430 Session session = null;
431
432 try {
433 session = openSession();
434
435 StringBuilder sb = new StringBuilder();
436
437 sb.append("SELECT DISTINCT {TagsAsset.*} ");
438 sb.append("FROM TagsAsset WHERE");
439 sb.append(" (visible = ?)");
440
441 if (entryIds.length > 0) {
442 sb.append(" AND TagsAsset.assetId IN (");
443
444 for (int i = 0; i < entryIds.length; i++) {
445 sb.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
446
447 if ((i + 1) < entryIds.length) {
448 sb.append(" AND TagsAsset.assetId IN (");
449 }
450 }
451
452 for (int i = 0; i < entryIds.length; i++) {
453 if ((i + 1) < entryIds.length) {
454 sb.append(StringPool.CLOSE_PARENTHESIS);
455 }
456 }
457
458 if (excludeZeroViewCount) {
459 sb.append(" AND (TagsAsset.viewCount > 0)");
460 }
461
462 sb.append(StringPool.CLOSE_PARENTHESIS);
463 }
464
465 if (notEntryIds.length > 0) {
466 sb.append(" AND (");
467
468 for (int i = 0; i < notEntryIds.length; i++) {
469 sb.append("TagsAsset.assetId NOT IN (");
470 sb.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
471 sb.append(StringPool.CLOSE_PARENTHESIS);
472
473 if ((i + 1) < notEntryIds.length) {
474 sb.append(" OR ");
475 }
476 }
477
478 sb.append(StringPool.CLOSE_PARENTHESIS);
479 }
480
481 sb.append("[$DATES$]");
482
483 if (groupId > 0) {
484 sb.append(" AND (TagsAsset.groupId = ?)");
485 }
486
487 sb.append(getClassNameIds(classNameIds));
488
489 sb.append(" ORDER BY TagsAsset.");
490 sb.append(orderByCol1);
491 sb.append(StringPool.SPACE);
492 sb.append(orderByType1);
493
494 if (Validator.isNotNull(orderByCol2) &&
495 !orderByCol1.equals(orderByCol2)) {
496
497 sb.append(", TagsAsset.");
498 sb.append(orderByCol2);
499 sb.append(StringPool.SPACE);
500 sb.append(orderByType2);
501 }
502
503 String sql = sb.toString();
504
505 sql = getDates(sql, publishDate, expirationDate);
506
507 SQLQuery q = session.createSQLQuery(sql);
508
509 q.addEntity("TagsAsset", TagsAssetImpl.class);
510
511 QueryPos qPos = QueryPos.getInstance(q);
512
513 qPos.add(true);
514
515 setEntryIds(qPos, entryIds);
516 setEntryIds(qPos, notEntryIds);
517 setDates(qPos, publishDate, expirationDate);
518
519 if (groupId > 0) {
520 setGroupId(qPos, groupId);
521 }
522
523 setClassNamedIds(qPos, classNameIds);
524
525 return (List<TagsAsset>)QueryUtil.list(q, getDialect(), start, end);
526 }
527 catch (Exception e) {
528 throw new SystemException(e);
529 }
530 finally {
531 closeSession(session);
532 }
533 }
534
535 public List<TagsAsset> findByOrEntryIds(
536 long groupId, long[] classNameIds, long[] entryIds,
537 long[] notEntryIds, Date publishDate, Date expirationDate )
538 throws SystemException {
539
540 return findByOrEntryIds(
541 groupId, classNameIds, entryIds, notEntryIds, null, null, null,
542 null, false, publishDate, expirationDate, QueryUtil.ALL_POS,
543 QueryUtil.ALL_POS);
544 }
545
546 public List<TagsAsset> findByOrEntryIds(
547 long groupId, long[] classNameIds, long[] entryIds,
548 long[] notEntryIds, String orderByCol1, String orderByCol2,
549 String orderByType1, String orderByType2,
550 boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
551 int start, int end)
552 throws SystemException {
553
554 orderByCol1 = checkOrderByCol(orderByCol1);
555 orderByCol2 = checkOrderByCol(orderByCol2);
556 orderByType1 = checkOrderByType(orderByType1);
557 orderByType2 = checkOrderByType(orderByType2);
558
559 Session session = null;
560
561 try {
562 session = openSession();
563
564 String sql = CustomSQLUtil.get(FIND_BY_OR_ENTRY_IDS);
565
566 sql = StringUtil.replace(
567 sql, "[$ENTRY_ID$]", getEntryIds(entryIds, StringPool.EQUAL));
568
569 if (notEntryIds.length > 0) {
570 StringBuilder sb = new StringBuilder();
571
572 sb.append(" AND (");
573
574 for (int i = 0; i < notEntryIds.length; i++) {
575 sb.append("TagsAsset.assetId NOT IN (");
576 sb.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
577 sb.append(StringPool.CLOSE_PARENTHESIS);
578
579 if ((i + 1) < notEntryIds.length) {
580 sb.append(" AND ");
581 }
582 }
583
584 sb.append(StringPool.CLOSE_PARENTHESIS);
585
586 sql = StringUtil.replace(
587 sql, "[$NOT_ENTRY_ID$]", sb.toString());
588 }
589 else {
590 sql = StringUtil.replace(
591 sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
592 }
593
594 sql = getDates(sql, publishDate, expirationDate);
595
596 sql += " AND (visible = ?)";
597
598 if (groupId > 0) {
599 sql += " AND (TagsAsset.groupId = ?)";
600 }
601
602 sql += getClassNameIds(classNameIds);
603
604 if (excludeZeroViewCount) {
605 sql += " AND (TagsAsset.viewCount > 0)";
606 }
607
608 StringBuilder sb = new StringBuilder();
609
610 sb.append(" ORDER BY TagsAsset.");
611 sb.append(orderByCol1);
612 sb.append(StringPool.SPACE);
613 sb.append(orderByType1);
614
615 if (Validator.isNotNull(orderByCol2) &&
616 !orderByCol1.equals(orderByCol2)) {
617
618 sb.append(", TagsAsset.");
619 sb.append(orderByCol2);
620 sb.append(StringPool.SPACE);
621 sb.append(orderByType2);
622 }
623
624 sql += sb.toString();
625
626 SQLQuery q = session.createSQLQuery(sql);
627
628 q.addEntity("TagsAsset", TagsAssetImpl.class);
629
630 QueryPos qPos = QueryPos.getInstance(q);
631
632 setEntryIds(qPos, entryIds);
633 setEntryIds(qPos, notEntryIds);
634 setDates(qPos, publishDate, expirationDate);
635
636 qPos.add(true);
637
638 if (groupId > 0) {
639 setGroupId(qPos, groupId);
640 }
641
642 setClassNamedIds(qPos, classNameIds);
643
644 return (List<TagsAsset>)QueryUtil.list(q, getDialect(), start, end);
645 }
646 catch (Exception e) {
647 throw new SystemException(e);
648 }
649 finally {
650 closeSession(session);
651 }
652 }
653
654 public List<TagsAsset> findByViewCount(
655 long[] classNameId, boolean asc, int start, int end)
656 throws SystemException {
657
658 Session session = null;
659
660 try {
661 session = openSession();
662
663 String sql = CustomSQLUtil.get(FIND_BY_VIEW_COUNT);
664
665 StringBuilder sb = new StringBuilder();
666
667 for (int i = 0; i < classNameId.length; i++) {
668 sb.append("(TagsAsset.classNameId = ?)");
669
670 if ((i+1) < classNameId.length) {
671 sb.append(" OR ");
672 }
673 }
674
675 sql = StringUtil.replace(
676 sql, "(TagsAsset.classNameId = ?)", sb.toString());
677
678 sql += " AND (visible = ?)";
679
680 sb = new StringBuilder();
681
682 sb.append(" ORDER BY TagsAsset.viewCount");
683
684 if (asc) {
685 sb.append(" ASC");
686 }
687 else {
688 sb.append(" DESC");
689 }
690
691 sql += sb.toString();
692
693 SQLQuery q = session.createSQLQuery(sql);
694
695 q.addEntity("TagsAsset", TagsAssetImpl.class);
696
697 QueryPos qPos = QueryPos.getInstance(q);
698
699 for (int i = 0; i < classNameId.length; i++) {
700 qPos.add(classNameId[i]);
701 }
702
703 qPos.add(true);
704
705 return (List<TagsAsset>)QueryUtil.list(q, getDialect(), start, end);
706 }
707 catch (Exception e) {
708 throw new SystemException(e);
709 }
710 finally {
711 closeSession(session);
712 }
713 }
714
715 protected String checkOrderByCol(String orderByCol) {
716 if (orderByCol == null) {
717 return "modifiedDate";
718 }
719
720 for (int i = 0; i < ORDER_BY_COLUMNS.length; i++) {
721 if (orderByCol.equals(ORDER_BY_COLUMNS[i])) {
722 return orderByCol;
723 }
724 }
725
726 return "modifiedDate";
727 }
728
729 protected String checkOrderByType(String orderByType) {
730 if (orderByType == null) {
731 return "DESC";
732 }
733
734 for (int i = 0; i < ORDER_BY_TYPE.length; i++) {
735 if (orderByType.equals(ORDER_BY_TYPE[i])) {
736 return orderByType;
737 }
738 }
739
740 return "DESC";
741 }
742
743 protected String getClassNameIds(long[] classNameIds) {
744 StringBuilder sb = new StringBuilder();
745
746 if (classNameIds.length > 0) {
747 sb.append(" AND (classNameId = ?");
748
749 for (int i = 1; i < classNameIds.length; i++) {
750 sb.append(" OR classNameId = ? ");
751 }
752
753 sb.append(") ");
754 }
755
756 return sb.toString();
757 }
758
759 protected String getDates(
760 String sql, Date publishDate, Date expirationDate) {
761
762 StringBuilder sb = new StringBuilder();
763
764 if (publishDate != null) {
765 sb.append(" AND (publishDate IS NULL OR publishDate < ?)");
766 }
767
768 if (expirationDate != null) {
769 sb.append(" AND (expirationDate IS NULL OR expirationDate > ?)");
770 }
771
772 sql = StringUtil.replace(sql, "[$DATES$]", sb.toString());
773
774 return sql;
775 }
776
777 protected String getEntryIds(long[] entryIds, String operator) {
778 StringBuilder sb = new StringBuilder();
779
780 for (int i = 0; i < entryIds.length; i++) {
781 sb.append("TagsEntry.entryId ");
782 sb.append(operator);
783 sb.append(" ? ");
784
785 if ((i + 1) != entryIds.length) {
786 sb.append("OR ");
787 }
788 }
789
790 if (sb.length() == 0) {
791 sb.append("(1 = 1)");
792 }
793
794 return sb.toString();
795 }
796
797 protected void setClassNamedIds(QueryPos qPos, long[] classNameIds) {
798 for (int i = 0; i < classNameIds.length; i++) {
799 qPos.add(classNameIds[i]);
800 }
801 }
802
803 protected void setDates(
804 QueryPos qPos, Date publishDate, Date expirationDate) {
805
806 if (publishDate != null) {
807 Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
808
809 qPos.add(publishDate_TS);
810 }
811
812 if (expirationDate != null) {
813 Timestamp expirationDate_TS =
814 CalendarUtil.getTimestamp(expirationDate);
815
816 qPos.add(expirationDate_TS);
817 }
818 }
819
820 protected void setGroupId(QueryPos qPos, long groupId) {
821 qPos.add(groupId);
822 }
823
824 protected void setEntryIds(QueryPos qPos, long[] entryIds) {
825 for (int i = 0; i < entryIds.length; i++) {
826 qPos.add(entryIds[i]);
827 }
828 }
829
830 }