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