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