001    /**
002     * Copyright (c) 2000-present Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portlet.asset.service.persistence.impl;
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.dao.orm.WildcardMode;
023    import com.liferay.portal.kernel.exception.SystemException;
024    import com.liferay.portal.kernel.util.CalendarUtil;
025    import com.liferay.portal.kernel.util.ListUtil;
026    import com.liferay.portal.kernel.util.StringBundler;
027    import com.liferay.portal.kernel.util.StringPool;
028    import com.liferay.portal.kernel.util.StringUtil;
029    import com.liferay.portal.kernel.util.Validator;
030    import com.liferay.portal.model.Layout;
031    import com.liferay.portal.util.PropsValues;
032    import com.liferay.portlet.asset.model.AssetCategory;
033    import com.liferay.portlet.asset.model.AssetEntry;
034    import com.liferay.portlet.asset.model.impl.AssetEntryImpl;
035    import com.liferay.portlet.asset.service.persistence.AssetCategoryUtil;
036    import com.liferay.portlet.asset.service.persistence.AssetEntryFinder;
037    import com.liferay.portlet.asset.service.persistence.AssetEntryQuery;
038    import com.liferay.portlet.documentlibrary.service.persistence.impl.DLFileEntryFinderImpl;
039    import com.liferay.portlet.documentlibrary.service.persistence.impl.DLFolderFinderImpl;
040    import com.liferay.util.dao.orm.CustomSQLUtil;
041    
042    import java.sql.Timestamp;
043    
044    import java.util.ArrayList;
045    import java.util.Collections;
046    import java.util.Date;
047    import java.util.Iterator;
048    import java.util.List;
049    
050    /**
051     * @author Brian Wing Shun Chan
052     * @author Jorge Ferrer
053     * @author Shuyang Zhou
054     */
055    public class AssetEntryFinderImpl
056            extends AssetEntryFinderBaseImpl implements AssetEntryFinder {
057    
058            public static final String FIND_BY_AND_CATEGORY_IDS =
059                    AssetEntryFinder.class.getName() + ".findByAndCategoryIds";
060    
061            public static final String FIND_BY_AND_TAG_IDS =
062                    AssetEntryFinder.class.getName() + ".findByAndTagIds";
063    
064            public static final String FIND_BY_CLASS_NAME_ID =
065                    AssetEntryFinder.class.getName() + ".findByClassNameId";
066    
067            @Override
068            public int countEntries(AssetEntryQuery entryQuery) {
069                    Session session = null;
070    
071                    try {
072                            session = openSession();
073    
074                            SQLQuery q = buildAssetQuerySQL(entryQuery, true, session);
075    
076                            Iterator<Long> itr = q.iterate();
077    
078                            if (itr.hasNext()) {
079                                    Long count = itr.next();
080    
081                                    if (count != null) {
082                                            return count.intValue();
083                                    }
084                            }
085    
086                            return 0;
087                    }
088                    catch (Exception e) {
089                            throw new SystemException(e);
090                    }
091                    finally {
092                            closeSession(session);
093                    }
094            }
095    
096            @Override
097            public List<AssetEntry> findByDLFileEntryC_T(
098                    long classNameId, String treePath) {
099    
100                    Session session = null;
101    
102                    try {
103                            session = openSession();
104    
105                            String sql = CustomSQLUtil.get(FIND_BY_CLASS_NAME_ID);
106    
107                            sql = StringUtil.replace(
108                                    sql, "[$JOIN$]",
109                                    CustomSQLUtil.get(
110                                            DLFileEntryFinderImpl.JOIN_AE_BY_DL_FILE_ENTRY));
111                            sql = StringUtil.replace(
112                                    sql, "[$WHERE$]", "DLFileEntry.treePath LIKE ? AND");
113    
114                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
115    
116                            QueryPos qPos = QueryPos.getInstance(q);
117    
118                            qPos.add(
119                                    CustomSQLUtil.keywords(treePath, WildcardMode.TRAILING)[0]);
120                            qPos.add(classNameId);
121    
122                            q.addEntity(AssetEntryImpl.TABLE_NAME, AssetEntryImpl.class);
123    
124                            return q.list(true);
125                    }
126                    catch (Exception e) {
127                            throw new SystemException(e);
128                    }
129                    finally {
130                            closeSession(session);
131                    }
132            }
133    
134            @Override
135            public List<AssetEntry> findByDLFolderC_T(
136                    long classNameId, String treePath) {
137    
138                    Session session = null;
139    
140                    try {
141                            session = openSession();
142    
143                            String sql = CustomSQLUtil.get(FIND_BY_CLASS_NAME_ID);
144    
145                            sql = StringUtil.replace(
146                                    sql, "[$JOIN$]",
147                                    CustomSQLUtil.get(DLFolderFinderImpl.JOIN_AE_BY_DL_FOLDER));
148                            sql = StringUtil.replace(
149                                    sql, "[$WHERE$]", "DLFolder.treePath LIKE ? AND");
150    
151                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
152    
153                            QueryPos qPos = QueryPos.getInstance(q);
154    
155                            qPos.add(
156                                    CustomSQLUtil.keywords(treePath, WildcardMode.TRAILING)[0]);
157                            qPos.add(classNameId);
158    
159                            q.addEntity(AssetEntryImpl.TABLE_NAME, AssetEntryImpl.class);
160    
161                            return q.list(true);
162                    }
163                    catch (Exception e) {
164                            throw new SystemException(e);
165                    }
166                    finally {
167                            closeSession(session);
168                    }
169            }
170    
171            @Override
172            public List<AssetEntry> findEntries(AssetEntryQuery entryQuery) {
173                    Session session = null;
174    
175                    try {
176                            session = openSession();
177    
178                            SQLQuery q = buildAssetQuerySQL(entryQuery, false, session);
179    
180                            return (List<AssetEntry>)QueryUtil.list(
181                                    q, getDialect(), entryQuery.getStart(), entryQuery.getEnd());
182                    }
183                    catch (Exception e) {
184                            throw new SystemException(e);
185                    }
186                    finally {
187                            closeSession(session);
188                    }
189            }
190    
191            protected void buildAllCategoriesSQL(long[] categoryIds, StringBundler sb) {
192                    String findByAndCategoryIdsSQL = CustomSQLUtil.get(
193                            FIND_BY_AND_CATEGORY_IDS);
194    
195                    sb.append(" AND (");
196    
197                    for (int i = 0; i < categoryIds.length; i++) {
198                            String sql = null;
199    
200                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
201                                    List<Long> treeCategoryIds = getSubcategoryIds(categoryIds[i]);
202    
203                                    if (treeCategoryIds.size() > 1) {
204                                            sql = StringUtil.replace(
205                                                    findByAndCategoryIdsSQL, "[$CATEGORY_ID$]",
206                                                    StringUtil.merge(treeCategoryIds));
207                                    }
208                            }
209    
210                            if (sql == null) {
211                                    sql = StringUtil.replace(
212                                            findByAndCategoryIdsSQL, " IN ([$CATEGORY_ID$])",
213                                            " = " + categoryIds[i]);
214                            }
215    
216                            sb.append(sql);
217    
218                            if ((i + 1) < categoryIds.length) {
219                                    sb.append(" AND ");
220                            }
221                    }
222    
223                    sb.append(StringPool.CLOSE_PARENTHESIS);
224            }
225    
226            protected void buildAllTagsSQL(long[][] tagIds, StringBundler sb) {
227                    sb.append(" AND AssetEntry.entryId IN (");
228    
229                    for (int i = 0; i < tagIds.length; i++) {
230                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
231    
232                            sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(tagIds[i]));
233    
234                            sb.append(sql);
235    
236                            if ((i + 1) < tagIds.length) {
237                                    sb.append(" AND AssetEntry.entryId IN (");
238                            }
239                    }
240    
241                    for (int i = 0; i < tagIds.length; i++) {
242                            if ((i + 1) < tagIds.length) {
243                                    sb.append(StringPool.CLOSE_PARENTHESIS);
244                            }
245                    }
246    
247                    sb.append(StringPool.CLOSE_PARENTHESIS);
248            }
249    
250            protected void buildAnyCategoriesSQL(long[] categoryIds, StringBundler sb) {
251                    String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
252    
253                    String categoryIdsString = null;
254    
255                    if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
256                            List<Long> categoryIdsList = new ArrayList<>();
257    
258                            for (long categoryId : categoryIds) {
259                                    categoryIdsList.addAll(getSubcategoryIds(categoryId));
260                            }
261    
262                            if (categoryIdsList.isEmpty()) {
263                                    return;
264                            }
265    
266                            categoryIdsString = StringUtil.merge(categoryIdsList);
267                    }
268                    else {
269                            categoryIdsString = StringUtil.merge(categoryIds);
270                    }
271    
272                    sb.append(" AND (");
273                    sb.append(
274                            StringUtil.replace(sql, "[$CATEGORY_ID$]", categoryIdsString));
275                    sb.append(StringPool.CLOSE_PARENTHESIS);
276            }
277    
278            protected String buildAnyTagsSQL(long[] tagIds, StringBundler sb) {
279                    sb.append(" AND (");
280    
281                    for (int i = 0; i < tagIds.length; i++) {
282                            sb.append("AssetTag.tagId = ");
283                            sb.append(tagIds[i]);
284    
285                            if ((i + 1) != tagIds.length) {
286                                    sb.append(" OR ");
287                            }
288                    }
289    
290                    sb.append(StringPool.CLOSE_PARENTHESIS);
291    
292                    return sb.toString();
293            }
294    
295            protected SQLQuery buildAssetQuerySQL(
296                    AssetEntryQuery entryQuery, boolean count, Session session) {
297    
298                    StringBundler sb = new StringBundler(52);
299    
300                    if (count) {
301                            sb.append(
302                                    "SELECT COUNT(DISTINCT AssetEntry.entryId) AS COUNT_VALUE ");
303                    }
304                    else {
305                            sb.append("SELECT {AssetEntry.*} ");
306    
307                            boolean selectRatings = false;
308    
309                            String orderByCol1 = entryQuery.getOrderByCol1();
310                            String orderByCol2 = entryQuery.getOrderByCol2();
311    
312                            if (orderByCol1.equals("ratings") ||
313                                    orderByCol2.equals("ratings")) {
314    
315                                    selectRatings = true;
316    
317                                    sb.append(", TEMP_TABLE.averageScore ");
318                            }
319    
320                            sb.append("FROM (SELECT DISTINCT AssetEntry.entryId ");
321    
322                            if (selectRatings) {
323                                    sb.append(", RatingsStats.averageScore ");
324                            }
325                    }
326    
327                    sb.append("FROM AssetEntry ");
328    
329                    if (entryQuery.getAnyTagIds().length > 0) {
330                            sb.append("INNER JOIN ");
331                            sb.append("AssetEntries_AssetTags ON ");
332                            sb.append("(AssetEntries_AssetTags.entryId = ");
333                            sb.append("AssetEntry.entryId) ");
334                            sb.append("INNER JOIN ");
335                            sb.append("AssetTag ON ");
336                            sb.append("(AssetTag.tagId = AssetEntries_AssetTags.tagId) ");
337                    }
338    
339                    if (entryQuery.getLinkedAssetEntryId() > 0) {
340                            sb.append("INNER JOIN ");
341                            sb.append("AssetLink ON ");
342                            sb.append("(AssetEntry.entryId = AssetLink.entryId1) ");
343                            sb.append("OR (AssetEntry.entryId = AssetLink.entryId2)");
344                    }
345    
346                    if (entryQuery.getOrderByCol1().equals("ratings") ||
347                            entryQuery.getOrderByCol2().equals("ratings")) {
348    
349                            sb.append(" LEFT JOIN ");
350                            sb.append("RatingsStats ON ");
351                            sb.append("(RatingsStats.classNameId = ");
352                            sb.append("AssetEntry.classNameId) AND ");
353                            sb.append("(RatingsStats.classPK = AssetEntry.classPK)");
354                    }
355    
356                    sb.append("WHERE ");
357    
358                    int whereIndex = sb.index();
359    
360                    if (entryQuery.getLinkedAssetEntryId() > 0) {
361                            sb.append(" AND ((AssetLink.entryId1 = ?) OR ");
362                            sb.append("(AssetLink.entryId2 = ?))");
363                            sb.append(" AND (AssetEntry.entryId != ?)");
364                    }
365    
366                    if (entryQuery.isListable() != null) {
367                            sb.append(" AND (listable = ?)");
368                    }
369    
370                    if (entryQuery.isVisible() != null) {
371                            sb.append(" AND (visible = ?)");
372                    }
373    
374                    if (entryQuery.isExcludeZeroViewCount()) {
375                            sb.append(" AND (AssetEntry.viewCount > 0)");
376                    }
377    
378                    // Keywords
379    
380                    if (Validator.isNotNull(entryQuery.getKeywords())) {
381                            sb.append(" AND ((AssetEntry.userName LIKE ?) OR");
382                            sb.append(" (AssetEntry.title LIKE ?) OR");
383                            sb.append(" (AssetEntry.description LIKE ?))");
384                    }
385                    else if (Validator.isNotNull(entryQuery.getUserName()) ||
386                                     Validator.isNotNull(entryQuery.getTitle()) ||
387                                     Validator.isNotNull(entryQuery.getDescription())) {
388    
389                            sb.append(" AND (");
390    
391                            boolean requiresOperator = false;
392    
393                            if (Validator.isNotNull(entryQuery.getUserName())) {
394                                    sb.append("(AssetEntry.userName LIKE ?)");
395    
396                                    requiresOperator = true;
397                            }
398    
399                            if (Validator.isNotNull(entryQuery.getTitle())) {
400                                    if (requiresOperator) {
401                                            sb.append(entryQuery.isAndOperator() ? " AND " : " OR ");
402                                    }
403    
404                                    sb.append("(AssetEntry.title LIKE ?)");
405    
406                                    requiresOperator = true;
407                            }
408    
409                            if (Validator.isNotNull(entryQuery.getDescription())) {
410                                    if (requiresOperator) {
411                                            sb.append(entryQuery.isAndOperator() ? " AND " : " OR ");
412                                    }
413    
414                                    sb.append("(AssetEntry.description LIKE ?)");
415                            }
416    
417                            sb.append(")");
418                    }
419    
420                    // Layout
421    
422                    Layout layout = entryQuery.getLayout();
423    
424                    if (layout != null) {
425                            sb.append(" AND (AssetEntry.layoutUuid = ?)");
426                    }
427    
428                    // Category conditions
429    
430                    if (entryQuery.getAllCategoryIds().length > 0) {
431                            buildAllCategoriesSQL(entryQuery.getAllCategoryIds(), sb);
432                    }
433    
434                    if (entryQuery.getAnyCategoryIds().length > 0) {
435                            buildAnyCategoriesSQL(entryQuery.getAnyCategoryIds(), sb);
436                    }
437    
438                    if (entryQuery.getNotAllCategoryIds().length > 0) {
439                            buildNotAllCategoriesSQL(entryQuery.getNotAllCategoryIds(), sb);
440                    }
441    
442                    if (entryQuery.getNotAnyCategoryIds().length > 0) {
443                            buildNotAnyCategoriesSQL(entryQuery.getNotAnyCategoryIds(), sb);
444                    }
445    
446                    // Asset entry subtypes
447    
448                    if (entryQuery.getClassTypeIds().length > 0) {
449                            buildClassTypeIdsSQL(entryQuery.getClassTypeIds(), sb);
450                    }
451    
452                    // Tag conditions
453    
454                    if (entryQuery.getAllTagIds().length > 0) {
455                            buildAllTagsSQL(entryQuery.getAllTagIdsArray(), sb);
456                    }
457    
458                    if (entryQuery.getAnyTagIds().length > 0) {
459                            buildAnyTagsSQL(entryQuery.getAnyTagIds(), sb);
460                    }
461    
462                    if (entryQuery.getNotAllTagIds().length > 0) {
463                            buildNotAllTagsSQL(entryQuery.getNotAllTagIdsArray(), sb);
464                    }
465    
466                    if (entryQuery.getNotAnyTagIds().length > 0) {
467                            buildNotAnyTagsSQL(entryQuery.getNotAnyTagIds(), sb);
468                    }
469    
470                    // Other conditions
471    
472                    sb.append(
473                            getDates(
474                                    entryQuery.getPublishDate(), entryQuery.getExpirationDate()));
475                    sb.append(getGroupIds(entryQuery.getGroupIds()));
476                    sb.append(getClassNameIds(entryQuery.getClassNameIds()));
477    
478                    if (!count) {
479                            sb.append(") TEMP_TABLE ");
480                            sb.append("INNER JOIN ");
481                            sb.append("AssetEntry AssetEntry ON ");
482                            sb.append("TEMP_TABLE.entryId = AssetEntry.entryId");
483    
484                            sb.append(" ORDER BY ");
485    
486                            if (entryQuery.getOrderByCol1().equals("ratings")) {
487                                    sb.append("TEMP_TABLE.averageScore");
488                            }
489                            else {
490                                    sb.append("AssetEntry.");
491                                    sb.append(entryQuery.getOrderByCol1());
492                            }
493    
494                            sb.append(StringPool.SPACE);
495                            sb.append(entryQuery.getOrderByType1());
496    
497                            if (Validator.isNotNull(entryQuery.getOrderByCol2()) &&
498                                    !entryQuery.getOrderByCol1().equals(
499                                            entryQuery.getOrderByCol2())) {
500    
501                                    if (entryQuery.getOrderByCol2().equals("ratings")) {
502                                            sb.append(", TEMP_TABLE.averageScore");
503                                    }
504                                    else {
505                                            sb.append(", AssetEntry.");
506                                            sb.append(entryQuery.getOrderByCol2());
507                                    }
508    
509                                    sb.append(StringPool.SPACE);
510                                    sb.append(entryQuery.getOrderByType2());
511                            }
512                    }
513    
514                    if (sb.index() > whereIndex) {
515                            String where = sb.stringAt(whereIndex);
516    
517                            if (where.startsWith(" AND")) {
518                                    sb.setStringAt(where.substring(4), whereIndex);
519                            }
520                    }
521    
522                    String sql = sb.toString();
523    
524                    SQLQuery q = session.createSynchronizedSQLQuery(sql);
525    
526                    if (count) {
527                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
528                    }
529                    else {
530                            q.addEntity("AssetEntry", AssetEntryImpl.class);
531                    }
532    
533                    QueryPos qPos = QueryPos.getInstance(q);
534    
535                    if (entryQuery.getLinkedAssetEntryId() > 0) {
536                            qPos.add(entryQuery.getLinkedAssetEntryId());
537                            qPos.add(entryQuery.getLinkedAssetEntryId());
538                            qPos.add(entryQuery.getLinkedAssetEntryId());
539                    }
540    
541                    if (entryQuery.isListable() != null) {
542                            qPos.add(entryQuery.isListable());
543                    }
544    
545                    if (entryQuery.isVisible() != null) {
546                            qPos.add(entryQuery.isVisible());
547                    }
548    
549                    if (Validator.isNotNull(entryQuery.getKeywords())) {
550                            qPos.add(
551                                    StringUtil.quote(entryQuery.getKeywords(), StringPool.PERCENT));
552                            qPos.add(
553                                    StringUtil.quote(entryQuery.getKeywords(), StringPool.PERCENT));
554                            qPos.add(
555                                    StringUtil.quote(entryQuery.getKeywords(), StringPool.PERCENT));
556                    }
557                    else {
558                            if (Validator.isNotNull(entryQuery.getUserName())) {
559                                    qPos.add(
560                                            StringUtil.quote(
561                                                    entryQuery.getUserName(), StringPool.PERCENT));
562                            }
563    
564                            if (Validator.isNotNull(entryQuery.getTitle())) {
565                                    qPos.add(
566                                            StringUtil.quote(
567                                                    entryQuery.getTitle(), StringPool.PERCENT));
568                            }
569    
570                            if (Validator.isNotNull(entryQuery.getDescription())) {
571                                    qPos.add(
572                                            StringUtil.quote(
573                                                    entryQuery.getDescription(), StringPool.PERCENT));
574                            }
575                    }
576    
577                    if (layout != null) {
578                            qPos.add(layout.getUuid());
579                    }
580    
581                    setDates(
582                            qPos, entryQuery.getPublishDate(), entryQuery.getExpirationDate());
583    
584                    qPos.add(entryQuery.getGroupIds());
585                    qPos.add(entryQuery.getClassNameIds());
586    
587                    return q;
588            }
589    
590            protected void buildClassTypeIdsSQL(long[] classTypeIds, StringBundler sb) {
591                    sb.append(" AND (");
592    
593                    for (int i = 0; i < classTypeIds.length; i++) {
594                            sb.append(" AssetEntry.classTypeId = ");
595                            sb.append(classTypeIds[i]);
596    
597                            if ((i + 1) < classTypeIds.length) {
598                                    sb.append(" OR ");
599                            }
600                            else {
601                                    sb.append(StringPool.CLOSE_PARENTHESIS);
602                            }
603                    }
604            }
605    
606            protected void buildNotAllCategoriesSQL(
607                    long[] categoryIds, StringBundler sb) {
608    
609                    String findByAndCategoryIdsSQL = CustomSQLUtil.get(
610                            FIND_BY_AND_CATEGORY_IDS);
611    
612                    sb.append(" AND (");
613    
614                    for (int i = 0; i < categoryIds.length; i++) {
615                            sb.append("NOT ");
616    
617                            String sql = null;
618    
619                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
620                                    List<Long> treeCategoryIds = getSubcategoryIds(categoryIds[i]);
621    
622                                    if (treeCategoryIds.size() > 1) {
623                                            sql = StringUtil.replace(
624                                                    findByAndCategoryIdsSQL, "[$CATEGORY_ID$]",
625                                                    StringUtil.merge(treeCategoryIds));
626                                    }
627                            }
628    
629                            if (sql == null) {
630                                    sql = StringUtil.replace(
631                                            findByAndCategoryIdsSQL, " IN ([$CATEGORY_ID$])",
632                                            " = " + categoryIds[i]);
633                            }
634    
635                            sb.append(sql);
636    
637                            if ((i + 1) < categoryIds.length) {
638                                    sb.append(" OR ");
639                            }
640                    }
641    
642                    sb.append(StringPool.CLOSE_PARENTHESIS);
643            }
644    
645            protected void buildNotAllTagsSQL(long[][] tagIds, StringBundler sb) {
646                    sb.append(" AND (");
647    
648                    for (int i = 0; i < tagIds.length; i++) {
649                            sb.append("AssetEntry.entryId NOT IN (");
650    
651                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
652    
653                            sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(tagIds[i]));
654    
655                            sb.append(sql);
656                            sb.append(StringPool.CLOSE_PARENTHESIS);
657    
658                            if (((i + 1) < tagIds.length) && (tagIds[i + 1].length > 0)) {
659                                    sb.append(" OR ");
660                            }
661                    }
662    
663                    sb.append(StringPool.CLOSE_PARENTHESIS);
664            }
665    
666            protected void buildNotAnyCategoriesSQL(
667                    long[] notCategoryIds, StringBundler sb) {
668    
669                    sb.append(" AND (NOT ");
670    
671                    String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
672    
673                    String notCategoryIdsString = null;
674    
675                    if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
676                            List<Long> notCategoryIdsList = new ArrayList<>();
677    
678                            for (long notCategoryId : notCategoryIds) {
679                                    notCategoryIdsList.addAll(getSubcategoryIds(notCategoryId));
680                            }
681    
682                            notCategoryIdsString = StringUtil.merge(notCategoryIdsList);
683                    }
684                    else {
685                            notCategoryIdsString = StringUtil.merge(notCategoryIds);
686                    }
687    
688                    sb.append(
689                            StringUtil.replace(sql, "[$CATEGORY_ID$]", notCategoryIdsString));
690                    sb.append(StringPool.CLOSE_PARENTHESIS);
691            }
692    
693            protected String buildNotAnyTagsSQL(long[] notTagIds, StringBundler sb) {
694                    sb.append(" AND (");
695    
696                    for (int i = 0; i < notTagIds.length; i++) {
697                            sb.append("AssetEntry.entryId NOT IN (");
698    
699                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
700    
701                            sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(notTagIds));
702    
703                            sb.append(sql);
704                            sb.append(StringPool.CLOSE_PARENTHESIS);
705    
706                            if ((i + 1) < notTagIds.length) {
707                                    sb.append(" AND ");
708                            }
709                    }
710    
711                    sb.append(StringPool.CLOSE_PARENTHESIS);
712    
713                    return sb.toString();
714            }
715    
716            protected String getClassNameIds(long[] classNameIds) {
717                    if (classNameIds.length == 0) {
718                            return StringPool.BLANK;
719                    }
720    
721                    StringBundler sb = new StringBundler(classNameIds.length + 1);
722    
723                    sb.append(" AND (AssetEntry.classNameId = ?");
724    
725                    for (int i = 0; i < (classNameIds.length - 1); i++) {
726                            sb.append(" OR AssetEntry.classNameId = ?");
727                    }
728    
729                    sb.append(StringPool.CLOSE_PARENTHESIS);
730    
731                    return sb.toString();
732            }
733    
734            protected String getDates(Date publishDate, Date expirationDate) {
735                    StringBundler sb = new StringBundler(4);
736    
737                    if (publishDate != null) {
738                            sb.append(" AND (AssetEntry.publishDate IS NULL OR ");
739                            sb.append("AssetEntry.publishDate < ?)");
740                    }
741    
742                    if (expirationDate != null) {
743                            sb.append(" AND (AssetEntry.expirationDate IS NULL OR ");
744                            sb.append("AssetEntry.expirationDate > ?)");
745                    }
746    
747                    return sb.toString();
748            }
749    
750            protected String getGroupIds(long[] groupIds) {
751                    if (groupIds.length == 0) {
752                            return StringPool.BLANK;
753                    }
754    
755                    StringBundler sb = new StringBundler(groupIds.length + 1);
756    
757                    sb.append(" AND (AssetEntry.groupId = ?");
758    
759                    for (int i = 0; i < (groupIds.length - 1); i++) {
760                            sb.append(" OR AssetEntry.groupId = ?");
761                    }
762    
763                    sb.append(StringPool.CLOSE_PARENTHESIS);
764    
765                    return sb.toString();
766            }
767    
768            protected List<Long> getSubcategoryIds(long parentCategoryId) {
769                    AssetCategory parentAssetCategory = AssetCategoryUtil.fetchByPrimaryKey(
770                            parentCategoryId);
771    
772                    if (parentAssetCategory == null) {
773                            return Collections.emptyList();
774                    }
775    
776                    return ListUtil.toList(
777                            AssetCategoryUtil.getDescendants(parentAssetCategory),
778                            AssetCategory.CATEGORY_ID_ACCESSOR);
779            }
780    
781            protected String getTagIds(long[] tagIds) {
782                    StringBundler sb = new StringBundler((tagIds.length * 3) - 1);
783    
784                    for (int i = 0; i < tagIds.length; i++) {
785                            sb.append("tagId = ");
786                            sb.append(tagIds[i]);
787    
788                            if ((i + 1) != tagIds.length) {
789                                    sb.append(" OR ");
790                            }
791                    }
792    
793                    return sb.toString();
794            }
795    
796            protected void setDates(
797                    QueryPos qPos, Date publishDate, Date expirationDate) {
798    
799                    if (publishDate != null) {
800                            Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
801    
802                            qPos.add(publishDate_TS);
803                    }
804    
805                    if (expirationDate != null) {
806                            Timestamp expirationDate_TS = CalendarUtil.getTimestamp(
807                                    expirationDate);
808    
809                            qPos.add(expirationDate_TS);
810                    }
811            }
812    
813    }