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 final String FIND_BY_AND_CATEGORY_IDS =
048 AssetEntryFinder.class.getName() + ".findByAndCategoryIds";
049
050 public static final String FIND_BY_AND_CATEGORY_IDS_TREE =
051 AssetEntryFinder.class.getName() + ".findByAndCategoryIdsTree";
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(
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(), entryQuery.getExpirationDate());
419
420 qPos.add(entryQuery.getGroupIds());
421 qPos.add(entryQuery.getClassNameIds());
422
423 return q;
424 }
425
426 protected void buildClassTypeIdsSQL(long[] classTypeIds, StringBundler sb) {
427 sb.append(" AND (");
428
429 for (int i = 0; i < classTypeIds.length; i++) {
430 sb.append(" AssetEntry.classTypeId = ");
431 sb.append(classTypeIds[i]);
432
433 if ((i + 1) < classTypeIds.length) {
434 sb.append(" OR ");
435 }
436 else {
437 sb.append(StringPool.CLOSE_PARENTHESIS);
438 }
439 }
440 }
441
442 protected void buildNotAnyCategoriesSQL(
443 String sqlId, long[] categoryIds, StringBundler sb) {
444
445 sb.append(" AND (");
446
447 for (int i = 0; i < categoryIds.length; i++) {
448 sb.append("AssetEntry.entryId NOT IN (");
449 sb.append(CustomSQLUtil.get(sqlId));
450 sb.append(StringPool.CLOSE_PARENTHESIS);
451
452 if ((i + 1) < categoryIds.length) {
453 sb.append(" OR ");
454 }
455 }
456
457 sb.append(StringPool.CLOSE_PARENTHESIS);
458 }
459
460 protected void buildNotAnyTagsSQL(long[] tagIds, StringBundler sb) {
461 sb.append(" AND (");
462
463 for (int i = 0; i < tagIds.length; i++) {
464 sb.append("AssetEntry.entryId NOT IN (");
465 sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
466 sb.append(StringPool.CLOSE_PARENTHESIS);
467
468 if ((i + 1) < tagIds.length) {
469 sb.append(" OR ");
470 }
471 }
472
473 sb.append(StringPool.CLOSE_PARENTHESIS);
474 }
475
476 protected String getCategoryIds(String sqlId, long[] categoryIds) {
477 StringBundler sb = new StringBundler();
478
479 sb.append(" AND (");
480
481 for (int i = 0; i < categoryIds.length; i++) {
482 sb.append("AssetEntry.entryId IN (");
483 sb.append(CustomSQLUtil.get(sqlId));
484 sb.append(StringPool.CLOSE_PARENTHESIS);
485
486 if ((i + 1) < categoryIds.length) {
487 sb.append(" OR ");
488 }
489 }
490
491 sb.append(StringPool.CLOSE_PARENTHESIS);
492
493 return sb.toString();
494 }
495
496 protected String getClassNameIds(long[] classNameIds) {
497 if (classNameIds.length == 0) {
498 return StringPool.BLANK;
499 }
500
501 StringBundler sb = new StringBundler(classNameIds.length + 2);
502
503 sb.append(" AND (AssetEntry.classNameId = ?");
504
505 for (int i = 1; i < classNameIds.length; i++) {
506 sb.append(" OR AssetEntry.classNameId = ? ");
507 }
508
509 sb.append(") ");
510
511 return sb.toString();
512 }
513
514 protected String getDates(Date publishDate, Date expirationDate) {
515 StringBundler sb = new StringBundler(4);
516
517 if (publishDate != null) {
518 sb.append(" AND (AssetEntry.publishDate IS NULL OR ");
519 sb.append("AssetEntry.publishDate < ?)");
520 }
521
522 if (expirationDate != null) {
523 sb.append(" AND (AssetEntry.expirationDate IS NULL OR ");
524 sb.append("AssetEntry.expirationDate > ?)");
525 }
526
527 return sb.toString();
528 }
529
530 protected String getGroupIds(long[] groupIds) {
531 if (groupIds.length == 0) {
532 return StringPool.BLANK;
533 }
534
535 StringBundler sb = new StringBundler(groupIds.length + 2);
536
537 sb.append(" AND (AssetEntry.groupId = ? ");
538
539 for (int i = 1; i < groupIds.length; i++) {
540 sb.append(" OR AssetEntry.groupId = ? ");
541 }
542
543 sb.append(")");
544
545 return sb.toString();
546 }
547
548 protected String getNotCategoryIds(String sqlId, long[] notCategoryIds) {
549 if (notCategoryIds.length == 0) {
550 return StringPool.BLANK;
551 }
552
553 StringBundler sb = new StringBundler(notCategoryIds.length * 4 - 1);
554
555 for (int i = 0; i < notCategoryIds.length; i++) {
556 sb.append("AssetEntry.entryId NOT IN (");
557 sb.append(CustomSQLUtil.get(sqlId));
558 sb.append(StringPool.CLOSE_PARENTHESIS);
559
560 if ((i + 1) < notCategoryIds.length) {
561 sb.append(" AND ");
562 }
563 }
564
565 return sb.toString();
566 }
567
568 protected String getNotTagIds(long[] notTagIds) {
569 if (notTagIds.length == 0) {
570 return StringPool.BLANK;
571 }
572
573 StringBundler sb = new StringBundler(notTagIds.length * 4 - 1);
574
575 for (int i = 0; i < notTagIds.length; i++) {
576 sb.append("AssetEntry.entryId NOT IN (");
577 sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
578 sb.append(StringPool.CLOSE_PARENTHESIS);
579
580 if ((i + 1) < notTagIds.length) {
581 sb.append(" AND ");
582 }
583 }
584
585 return sb.toString();
586 }
587
588 protected String getTagIds(long[] tagIds, String operator) {
589 StringBundler sb = new StringBundler(tagIds.length * 4 - 1);
590
591 for (int i = 0; i < tagIds.length; i++) {
592 sb.append("AssetTag.tagId ");
593 sb.append(operator);
594 sb.append(" ? ");
595
596 if ((i + 1) != tagIds.length) {
597 sb.append("OR ");
598 }
599 }
600
601 return sb.toString();
602 }
603
604 protected void setDates(
605 QueryPos qPos, Date publishDate, Date expirationDate) {
606
607 if (publishDate != null) {
608 Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
609
610 qPos.add(publishDate_TS);
611 }
612
613 if (expirationDate != null) {
614 Timestamp expirationDate_TS = CalendarUtil.getTimestamp(
615 expirationDate);
616
617 qPos.add(expirationDate_TS);
618 }
619 }
620
621 }