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