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