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