001
014
015 package com.liferay.portlet.asset.service.persistence;
016
017 import com.liferay.portal.kernel.dao.orm.QueryPos;
018 import com.liferay.portal.kernel.dao.orm.QueryUtil;
019 import com.liferay.portal.kernel.dao.orm.SQLQuery;
020 import com.liferay.portal.kernel.dao.orm.Session;
021 import com.liferay.portal.kernel.dao.orm.Type;
022 import com.liferay.portal.kernel.exception.SystemException;
023 import com.liferay.portal.kernel.util.CalendarUtil;
024 import com.liferay.portal.kernel.util.CharPool;
025 import com.liferay.portal.kernel.util.StringBundler;
026 import com.liferay.portal.kernel.util.StringPool;
027 import com.liferay.portal.kernel.util.StringUtil;
028 import com.liferay.portal.kernel.util.Validator;
029 import com.liferay.portal.model.Layout;
030 import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
031 import com.liferay.portal.util.PropsValues;
032 import com.liferay.portlet.asset.model.AssetEntry;
033 import com.liferay.portlet.asset.model.impl.AssetEntryImpl;
034 import com.liferay.util.dao.orm.CustomSQLUtil;
035
036 import java.sql.Timestamp;
037
038 import java.util.ArrayList;
039 import java.util.Date;
040 import java.util.Iterator;
041 import java.util.List;
042
043
048 public class AssetEntryFinderImpl
049 extends BasePersistenceImpl<AssetEntry> implements AssetEntryFinder {
050
051 public static final String FIND_BY_AND_CATEGORY_IDS =
052 AssetEntryFinder.class.getName() + ".findByAndCategoryIds";
053
054 public static final String FIND_BY_AND_TAG_IDS =
055 AssetEntryFinder.class.getName() + ".findByAndTagIds";
056
057 @Override
058 public int countEntries(AssetEntryQuery entryQuery) throws SystemException {
059 Session session = null;
060
061 try {
062 session = openSession();
063
064 SQLQuery q = buildAssetQuerySQL(entryQuery, true, session);
065
066 Iterator<Long> itr = q.iterate();
067
068 if (itr.hasNext()) {
069 Long count = itr.next();
070
071 if (count != null) {
072 return count.intValue();
073 }
074 }
075
076 return 0;
077 }
078 catch (Exception e) {
079 throw new SystemException(e);
080 }
081 finally {
082 closeSession(session);
083 }
084 }
085
086 @Override
087 public List<AssetEntry> findEntries(AssetEntryQuery entryQuery)
088 throws SystemException {
089
090 Session session = null;
091
092 try {
093 session = openSession();
094
095 SQLQuery q = buildAssetQuerySQL(entryQuery, false, session);
096
097 return (List<AssetEntry>)QueryUtil.list(
098 q, getDialect(), entryQuery.getStart(), entryQuery.getEnd());
099 }
100 catch (Exception e) {
101 throw new SystemException(e);
102 }
103 finally {
104 closeSession(session);
105 }
106 }
107
108 protected void buildAllCategoriesSQL(long[] categoryIds, StringBundler sb)
109 throws SystemException {
110
111 String findByAndCategoryIdsSQL = CustomSQLUtil.get(
112 FIND_BY_AND_CATEGORY_IDS);
113
114 sb.append(" AND (");
115
116 for (int i = 0; i < categoryIds.length; i++) {
117 String sql = null;
118
119 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
120 List<Long> treeCategoryIds = AssetCategoryFinderUtil.findByG_L(
121 categoryIds[i]);
122
123 if (treeCategoryIds.size() > 1) {
124 sql = StringUtil.replace(
125 findByAndCategoryIdsSQL, "[$CATEGORY_ID$]",
126 StringUtil.merge(treeCategoryIds));
127 }
128 }
129
130 if (sql == null) {
131 sql = StringUtil.replace(
132 findByAndCategoryIdsSQL, " IN ([$CATEGORY_ID$])",
133 " = " + categoryIds[i]);
134 }
135
136 sb.append(sql);
137
138 if ((i + 1) < categoryIds.length) {
139 sb.append(" AND ");
140 }
141 }
142
143 sb.append(StringPool.CLOSE_PARENTHESIS);
144 }
145
146 protected void buildAllTagsSQL(long[][] tagIds, StringBundler sb) {
147 sb.append(" AND AssetEntry.entryId IN (");
148
149 for (int i = 0; i < tagIds.length; i++) {
150 String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
151
152 sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(tagIds[i]));
153
154 sb.append(sql);
155
156 if ((i + 1) < tagIds.length) {
157 sb.append(" AND AssetEntry.entryId IN (");
158 }
159 }
160
161 for (int i = 0; i < tagIds.length; i++) {
162 if ((i + 1) < tagIds.length) {
163 sb.append(StringPool.CLOSE_PARENTHESIS);
164 }
165 }
166
167 sb.append(StringPool.CLOSE_PARENTHESIS);
168 }
169
170 protected void buildAnyCategoriesSQL(long[] categoryIds, StringBundler sb)
171 throws SystemException {
172
173 String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
174
175 String categoryIdsString = null;
176
177 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
178 List<Long> categoryIdsList = new ArrayList<Long>();
179
180 for (long categoryId : categoryIds) {
181 categoryIdsList.addAll(
182 AssetCategoryFinderUtil.findByG_L(categoryId));
183 }
184
185 if (categoryIdsList.isEmpty()) {
186 return;
187 }
188
189 categoryIdsString = StringUtil.merge(categoryIdsList);
190 }
191 else {
192 categoryIdsString = StringUtil.merge(categoryIds);
193 }
194
195 sb.append(" AND (");
196 sb.append(
197 StringUtil.replace(sql, "[$CATEGORY_ID$]", categoryIdsString));
198 sb.append(StringPool.CLOSE_PARENTHESIS);
199 }
200
201 protected String buildAnyTagsSQL(long[] tagIds, StringBundler sb) {
202 sb.append(" AND (");
203
204 for (int i = 0; i < tagIds.length; i++) {
205 sb.append("AssetTag.tagId = ");
206 sb.append(tagIds[i]);
207
208 if ((i + 1) != tagIds.length) {
209 sb.append(" OR ");
210 }
211 }
212
213 sb.append(StringPool.CLOSE_PARENTHESIS);
214
215 return sb.toString();
216 }
217
218 protected SQLQuery buildAssetQuerySQL(
219 AssetEntryQuery entryQuery, boolean count, Session session)
220 throws SystemException {
221
222 StringBundler sb = new StringBundler();
223
224 if (count) {
225 sb.append(
226 "SELECT COUNT(DISTINCT AssetEntry.entryId) AS COUNT_VALUE ");
227 }
228 else {
229 sb.append("SELECT {AssetEntry.*} ");
230
231 boolean selectRatings = false;
232
233 String orderByCol1 = entryQuery.getOrderByCol1();
234 String orderByCol2 = entryQuery.getOrderByCol2();
235
236 if (orderByCol1.equals("ratings") ||
237 orderByCol2.equals("ratings")) {
238
239 selectRatings = true;
240
241 sb.append(", TEMP_TABLE.averageScore ");
242 }
243
244 sb.append("FROM (SELECT DISTINCT AssetEntry.entryId ");
245
246 if (selectRatings) {
247 sb.append(", RatingsStats.averageScore ");
248 }
249 }
250
251 sb.append("FROM AssetEntry ");
252
253 if (entryQuery.getAnyTagIds().length > 0) {
254 sb.append("INNER JOIN ");
255 sb.append("AssetEntries_AssetTags ON ");
256 sb.append("(AssetEntries_AssetTags.entryId = ");
257 sb.append("AssetEntry.entryId) ");
258 sb.append("INNER JOIN ");
259 sb.append("AssetTag ON ");
260 sb.append("(AssetTag.tagId = AssetEntries_AssetTags.tagId) ");
261 }
262
263 if (entryQuery.getLinkedAssetEntryId() > 0) {
264 sb.append("INNER JOIN ");
265 sb.append("AssetLink ON ");
266 sb.append("(AssetEntry.entryId = AssetLink.entryId1) ");
267 sb.append("OR (AssetEntry.entryId = AssetLink.entryId2)");
268 }
269
270 if (entryQuery.getOrderByCol1().equals("ratings") ||
271 entryQuery.getOrderByCol2().equals("ratings")) {
272
273 sb.append(" LEFT JOIN ");
274 sb.append("RatingsStats ON ");
275 sb.append("(RatingsStats.classNameId = ");
276 sb.append("AssetEntry.classNameId) AND ");
277 sb.append("(RatingsStats.classPK = AssetEntry.classPK)");
278 }
279
280 sb.append("WHERE ");
281
282 int whereIndex = sb.index();
283
284 if (entryQuery.getLinkedAssetEntryId() > 0) {
285 sb.append(" AND ((AssetLink.entryId1 = ?) OR ");
286 sb.append("(AssetLink.entryId2 = ?))");
287 sb.append(" AND (AssetEntry.entryId != ?)");
288 }
289
290 if (entryQuery.isVisible() != null) {
291 sb.append(" AND (visible = ?)");
292 }
293
294 if (entryQuery.isExcludeZeroViewCount()) {
295 sb.append(" AND (AssetEntry.viewCount > 0)");
296 }
297
298
299
300 if (Validator.isNotNull(entryQuery.getKeywords())) {
301 sb.append(" AND ((AssetEntry.title LIKE ?) OR");
302 sb.append(" (AssetEntry.description LIKE ?))");
303 }
304 else {
305 if (Validator.isNotNull(entryQuery.getTitle())) {
306 sb.append(" AND (AssetEntry.title LIKE ?)");
307 }
308
309 if (Validator.isNotNull(entryQuery.getDescription())) {
310 sb.append(" AND (AssetEntry.description LIKE ?)");
311 }
312 }
313
314
315
316 Layout layout = entryQuery.getLayout();
317
318 if (layout != null) {
319 sb.append(" AND (AssetEntry.layoutUuid = ?)");
320 }
321
322
323
324 if (entryQuery.getAllCategoryIds().length > 0) {
325 buildAllCategoriesSQL(entryQuery.getAllCategoryIds(), sb);
326 }
327
328 if (entryQuery.getAnyCategoryIds().length > 0) {
329 buildAnyCategoriesSQL(entryQuery.getAnyCategoryIds(), sb);
330 }
331
332 if (entryQuery.getNotAllCategoryIds().length > 0) {
333 buildNotAllCategoriesSQL(entryQuery.getNotAllCategoryIds(), sb);
334 }
335
336 if (entryQuery.getNotAnyCategoryIds().length > 0) {
337 buildNotAnyCategoriesSQL(entryQuery.getNotAnyCategoryIds(), sb);
338 }
339
340
341
342 if (entryQuery.getClassTypeIds().length > 0) {
343 buildClassTypeIdsSQL(entryQuery.getClassTypeIds(), sb);
344 }
345
346
347
348 if (entryQuery.getAllTagIds().length > 0) {
349 buildAllTagsSQL(entryQuery.getAllTagIdsArray(), sb);
350 }
351
352 if (entryQuery.getAnyTagIds().length > 0) {
353 buildAnyTagsSQL(entryQuery.getAnyTagIds(), sb);
354 }
355
356 if (entryQuery.getNotAllTagIds().length > 0) {
357 buildNotAllTagsSQL(entryQuery.getNotAllTagIdsArray(), sb);
358 }
359
360 if (entryQuery.getNotAnyTagIds().length > 0) {
361 buildNotAnyTagsSQL(entryQuery.getNotAnyTagIds(), sb);
362 }
363
364
365
366 sb.append(
367 getDates(
368 entryQuery.getPublishDate(), entryQuery.getExpirationDate()));
369 sb.append(getGroupIds(entryQuery.getGroupIds()));
370 sb.append(getClassNameIds(entryQuery.getClassNameIds()));
371
372 if (!count) {
373 sb.append(") TEMP_TABLE ");
374 sb.append("INNER JOIN ");
375 sb.append("AssetEntry AssetEntry ON ");
376 sb.append("TEMP_TABLE.entryId = AssetEntry.entryId");
377
378 sb.append(" ORDER BY ");
379
380 if (entryQuery.getOrderByCol1().equals("ratings")) {
381 sb.append("TEMP_TABLE.averageScore");
382 }
383 else {
384 sb.append("AssetEntry.");
385 sb.append(entryQuery.getOrderByCol1());
386 }
387
388 sb.append(StringPool.SPACE);
389 sb.append(entryQuery.getOrderByType1());
390
391 if (Validator.isNotNull(entryQuery.getOrderByCol2()) &&
392 !entryQuery.getOrderByCol1().equals(
393 entryQuery.getOrderByCol2())) {
394
395 if (entryQuery.getOrderByCol2().equals("ratings")) {
396 sb.append(", TEMP_TABLE.averageScore");
397 }
398 else {
399 sb.append(", AssetEntry.");
400 sb.append(entryQuery.getOrderByCol2());
401 }
402
403 sb.append(StringPool.SPACE);
404 sb.append(entryQuery.getOrderByType2());
405 }
406 }
407
408 if (sb.index() > whereIndex) {
409 String where = sb.stringAt(whereIndex);
410
411 if (where.startsWith(" AND")) {
412 sb.setStringAt(where.substring(4), whereIndex);
413 }
414 }
415
416 String sql = sb.toString();
417
418 SQLQuery q = session.createSQLQuery(sql);
419
420 if (count) {
421 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
422 }
423 else {
424 q.addEntity("AssetEntry", AssetEntryImpl.class);
425 }
426
427 QueryPos qPos = QueryPos.getInstance(q);
428
429 if (entryQuery.getLinkedAssetEntryId() > 0) {
430 qPos.add(entryQuery.getLinkedAssetEntryId());
431 qPos.add(entryQuery.getLinkedAssetEntryId());
432 qPos.add(entryQuery.getLinkedAssetEntryId());
433 }
434
435 if (entryQuery.isVisible() != null) {
436 qPos.add(entryQuery.isVisible());
437 }
438
439 if (Validator.isNotNull(entryQuery.getKeywords())) {
440 qPos.add(entryQuery.getKeywords() + CharPool.PERCENT);
441 qPos.add(entryQuery.getKeywords() + CharPool.PERCENT);
442 }
443 else {
444 if (Validator.isNotNull(entryQuery.getTitle())) {
445 qPos.add(entryQuery.getTitle() + CharPool.PERCENT);
446 }
447
448 if (Validator.isNotNull(entryQuery.getDescription())) {
449 qPos.add(entryQuery.getDescription() + CharPool.PERCENT);
450 }
451 }
452
453 if (layout != null) {
454 qPos.add(layout.getUuid());
455 }
456
457 setDates(
458 qPos, entryQuery.getPublishDate(), entryQuery.getExpirationDate());
459
460 qPos.add(entryQuery.getGroupIds());
461 qPos.add(entryQuery.getClassNameIds());
462
463 return q;
464 }
465
466 protected void buildClassTypeIdsSQL(long[] classTypeIds, StringBundler sb) {
467 sb.append(" AND (");
468
469 for (int i = 0; i < classTypeIds.length; i++) {
470 sb.append(" AssetEntry.classTypeId = ");
471 sb.append(classTypeIds[i]);
472
473 if ((i + 1) < classTypeIds.length) {
474 sb.append(" OR ");
475 }
476 else {
477 sb.append(StringPool.CLOSE_PARENTHESIS);
478 }
479 }
480 }
481
482 protected void buildNotAllCategoriesSQL(
483 long[] categoryIds, StringBundler sb)
484 throws SystemException {
485
486 String findByAndCategoryIdsSQL = CustomSQLUtil.get(
487 FIND_BY_AND_CATEGORY_IDS);
488
489 sb.append(" AND (");
490
491 for (int i = 0; i < categoryIds.length; i++) {
492 sb.append("NOT ");
493
494 String sql = null;
495
496 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
497 List<Long> treeCategoryIds = AssetCategoryFinderUtil.findByG_L(
498 categoryIds[i]);
499
500 if (treeCategoryIds.size() > 1) {
501 sql = StringUtil.replace(
502 findByAndCategoryIdsSQL, "[$CATEGORY_ID$]",
503 StringUtil.merge(treeCategoryIds));
504 }
505 }
506
507 if (sql == null) {
508 sql = StringUtil.replace(
509 findByAndCategoryIdsSQL, " IN ([$CATEGORY_ID$])",
510 " = " + categoryIds[i]);
511 }
512
513 sb.append(sql);
514
515 if ((i + 1) < categoryIds.length) {
516 sb.append(" OR ");
517 }
518 }
519
520 sb.append(StringPool.CLOSE_PARENTHESIS);
521 }
522
523 protected void buildNotAllTagsSQL(long[][] tagIds, StringBundler sb) {
524 sb.append(" AND (");
525
526 for (int i = 0; i < tagIds.length; i++) {
527 sb.append("AssetEntry.entryId NOT IN (");
528
529 String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
530
531 sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(tagIds[i]));
532
533 sb.append(sql);
534 sb.append(StringPool.CLOSE_PARENTHESIS);
535
536 if (((i + 1) < tagIds.length) && (tagIds[i + 1].length > 0)) {
537 sb.append(" OR ");
538 }
539 }
540
541 sb.append(StringPool.CLOSE_PARENTHESIS);
542 }
543
544 protected void buildNotAnyCategoriesSQL(
545 long[] notCategoryIds, StringBundler sb)
546 throws SystemException {
547
548 sb.append(" AND (NOT ");
549
550 String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
551
552 String notCategoryIdsString = null;
553
554 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
555 List<Long> notCategoryIdsList = new ArrayList<Long>();
556
557 for (long notCategoryId : notCategoryIds) {
558 notCategoryIdsList.addAll(
559 AssetCategoryFinderUtil.findByG_L(notCategoryId));
560 }
561
562 notCategoryIdsString = StringUtil.merge(notCategoryIdsList);
563 }
564 else {
565 notCategoryIdsString = StringUtil.merge(notCategoryIds);
566 }
567
568 sb.append(
569 StringUtil.replace(sql, "[$CATEGORY_ID$]", notCategoryIdsString));
570 sb.append(StringPool.CLOSE_PARENTHESIS);
571 }
572
573 protected String buildNotAnyTagsSQL(long[] notTagIds, StringBundler sb) {
574 sb.append(" AND (");
575
576 for (int i = 0; i < notTagIds.length; i++) {
577 sb.append("AssetEntry.entryId NOT IN (");
578
579 String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
580
581 sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(notTagIds));
582
583 sb.append(sql);
584 sb.append(StringPool.CLOSE_PARENTHESIS);
585
586 if ((i + 1) < notTagIds.length) {
587 sb.append(" AND ");
588 }
589 }
590
591 sb.append(StringPool.CLOSE_PARENTHESIS);
592
593 return sb.toString();
594 }
595
596 protected String getClassNameIds(long[] classNameIds) {
597 if (classNameIds.length == 0) {
598 return StringPool.BLANK;
599 }
600
601 StringBundler sb = new StringBundler(classNameIds.length + 1);
602
603 sb.append(" AND (AssetEntry.classNameId = ?");
604
605 for (int i = 0; i < (classNameIds.length - 1); i++) {
606 sb.append(" OR AssetEntry.classNameId = ?");
607 }
608
609 sb.append(StringPool.CLOSE_PARENTHESIS);
610
611 return sb.toString();
612 }
613
614 protected String getDates(Date publishDate, Date expirationDate) {
615 StringBundler sb = new StringBundler(4);
616
617 if (publishDate != null) {
618 sb.append(" AND (AssetEntry.publishDate IS NULL OR ");
619 sb.append("AssetEntry.publishDate < ?)");
620 }
621
622 if (expirationDate != null) {
623 sb.append(" AND (AssetEntry.expirationDate IS NULL OR ");
624 sb.append("AssetEntry.expirationDate > ?)");
625 }
626
627 return sb.toString();
628 }
629
630 protected String getGroupIds(long[] groupIds) {
631 if (groupIds.length == 0) {
632 return StringPool.BLANK;
633 }
634
635 StringBundler sb = new StringBundler(groupIds.length + 1);
636
637 sb.append(" AND (AssetEntry.groupId = ?");
638
639 for (int i = 0; i < (groupIds.length - 1); i++) {
640 sb.append(" OR AssetEntry.groupId = ?");
641 }
642
643 sb.append(StringPool.CLOSE_PARENTHESIS);
644
645 return sb.toString();
646 }
647
648 protected String getTagIds(long[] tagIds) {
649 StringBundler sb = new StringBundler((tagIds.length * 3) - 1);
650
651 for (int i = 0; i < tagIds.length; i++) {
652 sb.append("tagId = ");
653 sb.append(tagIds[i]);
654
655 if ((i + 1) != tagIds.length) {
656 sb.append(" OR ");
657 }
658 }
659
660 return sb.toString();
661 }
662
663 protected void setDates(
664 QueryPos qPos, Date publishDate, Date expirationDate) {
665
666 if (publishDate != null) {
667 Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
668
669 qPos.add(publishDate_TS);
670 }
671
672 if (expirationDate != null) {
673 Timestamp expirationDate_TS = CalendarUtil.getTimestamp(
674 expirationDate);
675
676 qPos.add(expirationDate_TS);
677 }
678 }
679
680 }