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.messageboards.service.persistence.impl;
016    
017    import com.liferay.portal.kernel.dao.orm.QueryDefinition;
018    import com.liferay.portal.kernel.dao.orm.QueryPos;
019    import com.liferay.portal.kernel.dao.orm.QueryUtil;
020    import com.liferay.portal.kernel.dao.orm.SQLQuery;
021    import com.liferay.portal.kernel.dao.orm.Session;
022    import com.liferay.portal.kernel.dao.orm.Type;
023    import com.liferay.portal.kernel.exception.SystemException;
024    import com.liferay.portal.kernel.util.ArrayUtil;
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.workflow.WorkflowConstants;
030    import com.liferay.portal.model.Group;
031    import com.liferay.portal.model.Subscription;
032    import com.liferay.portal.security.permission.InlineSQLHelperUtil;
033    import com.liferay.portal.service.GroupLocalServiceUtil;
034    import com.liferay.portal.service.SubscriptionLocalServiceUtil;
035    import com.liferay.portal.util.PortalUtil;
036    import com.liferay.portlet.messageboards.model.MBCategory;
037    import com.liferay.portlet.messageboards.model.MBCategoryConstants;
038    import com.liferay.portlet.messageboards.model.MBMessage;
039    import com.liferay.portlet.messageboards.model.impl.MBCategoryImpl;
040    import com.liferay.portlet.messageboards.service.MBMessageLocalServiceUtil;
041    import com.liferay.portlet.messageboards.service.MBThreadLocalServiceUtil;
042    import com.liferay.portlet.messageboards.service.persistence.MBCategoryFinder;
043    import com.liferay.portlet.messageboards.service.persistence.MBCategoryUtil;
044    import com.liferay.portlet.messageboards.service.persistence.MBThreadUtil;
045    import com.liferay.util.dao.orm.CustomSQLUtil;
046    
047    import java.util.ArrayList;
048    import java.util.Collections;
049    import java.util.Iterator;
050    import java.util.List;
051    
052    /**
053     * @author Raymond Aug??
054     * @author Sergio Gonz??lez
055     */
056    public class MBCategoryFinderImpl
057            extends MBCategoryFinderBaseImpl implements MBCategoryFinder {
058    
059            public static final String COUNT_C_BY_G_P =
060                    MBCategoryFinder.class.getName() + ".countC_ByG_P";
061    
062            public static final String COUNT_C_BY_G_P_S =
063                    MBCategoryFinder.class.getName() + ".countC_ByG_P_S";
064    
065            public static final String COUNT_C_BY_S_G_U_P =
066                    MBCategoryFinder.class.getName() + ".countC_ByS_G_U_P";
067    
068            public static final String COUNT_T_BY_G_C =
069                    MBCategoryFinder.class.getName() + ".countT_ByG_C";
070    
071            public static final String COUNT_T_BY_G_C_S =
072                    MBCategoryFinder.class.getName() + ".countT_ByG_C_S";
073    
074            public static final String FIND_C_BY_G_P =
075                    MBCategoryFinder.class.getName() + ".findC_ByG_P";
076    
077            public static final String FIND_C_BY_G_P_S =
078                    MBCategoryFinder.class.getName() + ".findC_ByG_P_S";
079    
080            public static final String FIND_C_BY_S_G_U_P =
081                    MBCategoryFinder.class.getName() + ".findC_ByS_G_U_P";
082    
083            public static final String FIND_T_BY_G_C =
084                    MBCategoryFinder.class.getName() + ".findT_ByG_C";
085    
086            public static final String FIND_T_BY_G_C_S =
087                    MBCategoryFinder.class.getName() + ".findT_ByG_C_S";
088    
089            @Override
090            public int countC_ByS_G_U_P(
091                    long groupId, long userId, long[] parentCategoryIds,
092                    QueryDefinition<MBCategory> queryDefinition) {
093    
094                    return doCountC_ByS_G_U_P(
095                            groupId, userId, parentCategoryIds, queryDefinition, false);
096            }
097    
098            @Override
099            public int countC_T_ByG_C(
100                    long groupId, long categoryId, QueryDefinition<?> queryDefinition) {
101    
102                    return doCountC_T_ByG_C(groupId, categoryId, queryDefinition, false);
103            }
104    
105            @Override
106            public List<MBCategory> filterFindC_ByS_G_U_P(
107                    long groupId, long userId, long[] parentCategoryIds,
108                    QueryDefinition<MBCategory> queryDefinition) {
109    
110                    return doFindC_ByS_G_U_P(
111                            groupId, userId, parentCategoryIds, queryDefinition, true);
112            }
113    
114            @Override
115            public List<Object> filterFindC_T_ByG_C(
116                    long groupId, long categoryId, QueryDefinition<?> queryDefinition) {
117    
118                    return doFindC_T_ByG_C(groupId, categoryId, queryDefinition, true);
119            }
120    
121            @Override
122            public List<Object> findC_T_ByG_C(
123                    long groupId, long categoryId, QueryDefinition<?> queryDefinition) {
124    
125                    return doFindC_T_ByG_C(groupId, categoryId, queryDefinition, false);
126            }
127    
128            @Override
129            public int filterCountC_ByS_G_U_P(
130                    long groupId, long userId, long[] parentCategoryIds,
131                    QueryDefinition<MBCategory> queryDefinition) {
132    
133                    return doCountC_ByS_G_U_P(
134                            groupId, userId, parentCategoryIds, queryDefinition, true);
135            }
136    
137            @Override
138            public int filterCountC_T_ByG_C(
139                    long groupId, long categoryId, QueryDefinition<?> queryDefinition) {
140    
141                    return doCountC_T_ByG_C(groupId, categoryId, queryDefinition, true);
142            }
143    
144            @Override
145            public List<MBCategory> findC_ByS_G_U_P(
146                    long groupId, long userId, long[] parentCategoryIds,
147                    QueryDefinition<MBCategory> queryDefinition) {
148    
149                    return doFindC_ByS_G_U_P(
150                            groupId, userId, parentCategoryIds, queryDefinition, false);
151            }
152    
153            protected int doCountC_ByS_G_U_P(
154                    long groupId, long userId, long[] parentCategoryIds,
155                    QueryDefinition<MBCategory> queryDefinition, boolean inlineSQLHelper) {
156    
157                    Session session = null;
158    
159                    try {
160                            session = openSession();
161    
162                            String sql = CustomSQLUtil.get(COUNT_C_BY_S_G_U_P);
163    
164                            if (ArrayUtil.isEmpty(parentCategoryIds)) {
165                                    sql = StringUtil.replace(
166                                            sql, "(MBCategory.parentCategoryId = ?) AND",
167                                            StringPool.BLANK);
168                            }
169                            else {
170                                    sql = StringUtil.replace(
171                                            sql, "MBCategory.parentCategoryId = ?",
172                                            "MBCategory.parentCategoryId = " +
173                                                    StringUtil.merge(
174                                                            parentCategoryIds,
175                                                            " OR MBCategory.parentCategoryId = "));
176                            }
177    
178                            sql = updateSQL(sql, queryDefinition);
179    
180                            if (inlineSQLHelper) {
181                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
182                                            sql, MBCategory.class.getName(), "MBCategory.categoryId",
183                                            groupId);
184                            }
185    
186                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
187    
188                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
189    
190                            QueryPos qPos = QueryPos.getInstance(q);
191    
192                            qPos.add(PortalUtil.getClassNameId(MBCategory.class.getName()));
193                            qPos.add(groupId);
194                            qPos.add(userId);
195    
196                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
197                                    qPos.add(queryDefinition.getStatus());
198                            }
199    
200                            int count = 0;
201    
202                            Iterator<Long> itr = q.iterate();
203    
204                            if (itr.hasNext()) {
205                                    Long l = itr.next();
206    
207                                    if (l != null) {
208                                            count = l.intValue();
209                                    }
210                            }
211    
212                            Group group = GroupLocalServiceUtil.getGroup(groupId);
213    
214                            Subscription subscription =
215                                    SubscriptionLocalServiceUtil.fetchSubscription(
216                                            group.getCompanyId(), userId, MBCategory.class.getName(),
217                                            groupId);
218    
219                            if (subscription != null) {
220                                    count++;
221                            }
222    
223                            return count;
224                    }
225                    catch (Exception e) {
226                            throw new SystemException(e);
227                    }
228                    finally {
229                            closeSession(session);
230                    }
231            }
232    
233            protected int doCountC_T_ByG_C(
234                    long groupId, long categoryId, QueryDefinition<?> queryDefinition,
235                    boolean inlineSQLHelper) {
236    
237                    Session session = null;
238    
239                    try {
240                            session = openSession();
241    
242                            StringBundler sb = new StringBundler(5);
243    
244                            sb.append(StringPool.OPEN_PARENTHESIS);
245    
246                            String sql = null;
247    
248                            if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
249                                    sql = CustomSQLUtil.get(COUNT_T_BY_G_C);
250                            }
251                            else {
252                                    sql = CustomSQLUtil.get(COUNT_T_BY_G_C_S);
253    
254                                    sql = replaceExcludeStatus(sql, queryDefinition);
255                            }
256    
257                            if (inlineSQLHelper) {
258                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
259                                            sql, MBMessage.class.getName(), "MBThread.rootMessageId",
260                                            groupId);
261                            }
262    
263                            sb.append(sql);
264                            sb.append(") UNION ALL (");
265    
266                            if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
267                                    sql = CustomSQLUtil.get(COUNT_C_BY_G_P);
268                            }
269                            else {
270                                    sql = CustomSQLUtil.get(COUNT_C_BY_G_P_S);
271    
272                                    sql = replaceExcludeStatus(sql, queryDefinition);
273                            }
274    
275                            if (inlineSQLHelper) {
276                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
277                                            sql, MBCategory.class.getName(), "MBCategory.categoryId",
278                                            groupId);
279                            }
280    
281                            sb.append(sql);
282                            sb.append(StringPool.CLOSE_PARENTHESIS);
283    
284                            sql = sb.toString();
285    
286                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
287    
288                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
289    
290                            QueryPos qPos = QueryPos.getInstance(q);
291    
292                            qPos.add(groupId);
293                            qPos.add(categoryId);
294    
295                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
296                                    qPos.add(queryDefinition.getStatus());
297                            }
298    
299                            qPos.add(groupId);
300                            qPos.add(categoryId);
301    
302                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
303                                    qPos.add(queryDefinition.getStatus());
304                            }
305    
306                            int count = 0;
307    
308                            Iterator<Long> itr = q.iterate();
309    
310                            while (itr.hasNext()) {
311                                    Long l = itr.next();
312    
313                                    if (l != null) {
314                                            count += l.intValue();
315                                    }
316                            }
317    
318                            return count;
319                    }
320                    catch (Exception e) {
321                            throw new SystemException(e);
322                    }
323                    finally {
324                            closeSession(session);
325                    }
326            }
327    
328            protected List<MBCategory> doFindC_ByS_G_U_P(
329                    long groupId, long userId, long[] parentCategoryIds,
330                    QueryDefinition<MBCategory> queryDefinition, boolean inlineSQLHelper) {
331    
332                    Session session = null;
333    
334                    try {
335                            session = openSession();
336    
337                            String sql = CustomSQLUtil.get(FIND_C_BY_S_G_U_P);
338    
339                            if (ArrayUtil.isEmpty(parentCategoryIds)) {
340                                    sql = StringUtil.replace(
341                                            sql, "(MBCategory.parentCategoryId = ?) AND",
342                                            StringPool.BLANK);
343                            }
344                            else {
345                                    sql = StringUtil.replace(
346                                            sql, "MBCategory.parentCategoryId = ?",
347                                            "MBCategory.parentCategoryId = " +
348                                                    StringUtil.merge(
349                                                            parentCategoryIds,
350                                                            " OR MBCategory.parentCategoryId = "));
351                            }
352    
353                            sql = updateSQL(sql, queryDefinition);
354    
355                            if (inlineSQLHelper) {
356                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
357                                            sql, MBCategory.class.getName(), "MBCategory.categoryId",
358                                            groupId);
359                            }
360    
361                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
362    
363                            q.addEntity("MBCategory", MBCategoryImpl.class);
364    
365                            QueryPos qPos = QueryPos.getInstance(q);
366    
367                            qPos.add(PortalUtil.getClassNameId(MBCategory.class.getName()));
368                            qPos.add(groupId);
369                            qPos.add(userId);
370    
371                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
372                                    qPos.add(queryDefinition.getStatus());
373                            }
374    
375                            List<MBCategory> list = (List<MBCategory>)QueryUtil.list(
376                                    q, getDialect(), QueryUtil.ALL_POS, QueryUtil.ALL_POS, false);
377    
378                            Group group = GroupLocalServiceUtil.getGroup(groupId);
379    
380                            Subscription subscription =
381                                    SubscriptionLocalServiceUtil.fetchSubscription(
382                                            group.getCompanyId(), userId, MBCategory.class.getName(),
383                                            groupId);
384    
385                            if (subscription != null) {
386                                    int threadCount =
387                                            MBThreadLocalServiceUtil.getCategoryThreadsCount(
388                                                    groupId, MBCategoryConstants.DEFAULT_PARENT_CATEGORY_ID,
389                                                    WorkflowConstants.STATUS_APPROVED);
390                                    int messageCount =
391                                            MBMessageLocalServiceUtil.getCategoryMessagesCount(
392                                                    groupId, MBCategoryConstants.DEFAULT_PARENT_CATEGORY_ID,
393                                                    WorkflowConstants.STATUS_APPROVED);
394    
395                                    MBCategory category = new MBCategoryImpl();
396    
397                                    category.setGroupId(group.getGroupId());
398                                    category.setCompanyId(group.getCompanyId());
399                                    category.setName(group.getDescriptiveName());
400                                    category.setDescription(group.getDescription());
401                                    category.setThreadCount(threadCount);
402                                    category.setMessageCount(messageCount);
403    
404                                    list.add(category);
405                            }
406    
407                            return Collections.unmodifiableList(
408                                    ListUtil.subList(
409                                            list, queryDefinition.getStart(),
410                                            queryDefinition.getEnd()));
411                    }
412                    catch (Exception e) {
413                            throw new SystemException(e);
414                    }
415                    finally {
416                            closeSession(session);
417                    }
418            }
419    
420            protected List<Object> doFindC_T_ByG_C(
421                    long groupId, long categoryId, QueryDefinition<?> queryDefinition,
422                    boolean inlineSQLHelper) {
423    
424                    Session session = null;
425    
426                    try {
427                            session = openSession();
428    
429                            StringBundler sb = new StringBundler(6);
430    
431                            sb.append("SELECT * FROM (");
432    
433                            String sql = null;
434    
435                            if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
436                                    sql = CustomSQLUtil.get(FIND_T_BY_G_C);
437                            }
438                            else {
439                                    sql = CustomSQLUtil.get(FIND_T_BY_G_C_S);
440    
441                                    sql = replaceExcludeStatus(sql, queryDefinition);
442                            }
443    
444                            if (inlineSQLHelper) {
445                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
446                                            sql, MBMessage.class.getName(), "MBThread.rootMessageId",
447                                            groupId);
448                            }
449    
450                            sb.append(sql);
451                            sb.append(" UNION ALL ");
452    
453                            if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
454                                    sql = CustomSQLUtil.get(FIND_C_BY_G_P);
455                            }
456                            else {
457                                    sql = CustomSQLUtil.get(FIND_C_BY_G_P_S);
458    
459                                    sql = replaceExcludeStatus(sql, queryDefinition);
460                            }
461    
462                            if (inlineSQLHelper) {
463                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
464                                            sql, MBCategory.class.getName(), "MBCategory.categoryId",
465                                            groupId);
466                            }
467    
468                            sb.append(sql);
469                            sb.append(") TEMP_TABLE ORDER BY modelCategory ASC, ");
470                            sb.append("priority DESC, modelId ASC");
471    
472                            sql = sb.toString();
473    
474                            sql = CustomSQLUtil.replaceOrderBy(
475                                    sql, queryDefinition.getOrderByComparator());
476    
477                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
478    
479                            q.addScalar("modelId", Type.LONG);
480                            q.addScalar("modelCategory", Type.LONG);
481    
482                            QueryPos qPos = QueryPos.getInstance(q);
483    
484                            qPos.add(groupId);
485                            qPos.add(categoryId);
486    
487                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
488                                    qPos.add(queryDefinition.getStatus());
489                            }
490    
491                            qPos.add(groupId);
492                            qPos.add(categoryId);
493    
494                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
495                                    qPos.add(queryDefinition.getStatus());
496                            }
497    
498                            List<Object> models = new ArrayList<>();
499    
500                            Iterator<Object[]> itr = (Iterator<Object[]>)QueryUtil.iterate(
501                                    q, getDialect(), queryDefinition.getStart(),
502                                    queryDefinition.getEnd());
503    
504                            while (itr.hasNext()) {
505                                    Object[] array = itr.next();
506    
507                                    long modelId = (Long)array[0];
508                                    long modelCategory = (Long)array[1];
509    
510                                    Object obj = null;
511    
512                                    if (modelCategory == 1) {
513                                            obj = MBThreadUtil.findByPrimaryKey(modelId);
514                                    }
515                                    else {
516                                            obj = MBCategoryUtil.findByPrimaryKey(modelId);
517                                    }
518    
519                                    models.add(obj);
520                            }
521    
522                            return models;
523                    }
524                    catch (Exception e) {
525                            throw new SystemException(e);
526                    }
527                    finally {
528                            closeSession(session);
529                    }
530            }
531    
532            protected String replaceExcludeStatus(
533                    String sql, QueryDefinition<?> queryDefinition) {
534    
535                    if (queryDefinition.isExcludeStatus()) {
536                            sql = StringUtil.replace(sql, ".status = ?)", ".status != ?)");
537                    }
538    
539                    return sql;
540            }
541    
542            protected String updateSQL(
543                    String sql, QueryDefinition<MBCategory> queryDefinition) {
544    
545                    if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
546                            return sql;
547                    }
548    
549                    if (queryDefinition.isExcludeStatus()) {
550                            return CustomSQLUtil.appendCriteria(
551                                    sql, "AND (MBCategory.status != ?)");
552                    }
553    
554                    return CustomSQLUtil.appendCriteria(sql, "AND (MBCategory.status = ?)");
555            }
556    
557    }