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