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