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