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