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