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