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