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