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