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