001    /**
002     * Copyright (c) 2000-2013 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;
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.StringPool;
026    import com.liferay.portal.kernel.util.StringUtil;
027    import com.liferay.portal.kernel.workflow.WorkflowConstants;
028    import com.liferay.portal.security.permission.InlineSQLHelperUtil;
029    import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
030    import com.liferay.portal.util.PortalUtil;
031    import com.liferay.portlet.messageboards.model.MBMessage;
032    import com.liferay.portlet.messageboards.model.MBThread;
033    import com.liferay.portlet.messageboards.model.impl.MBThreadImpl;
034    import com.liferay.util.dao.orm.CustomSQLUtil;
035    
036    import java.util.Date;
037    import java.util.Iterator;
038    import java.util.List;
039    
040    /**
041     * @author Brian Wing Shun Chan
042     * @author Shuyang Zhou
043     */
044    public class MBThreadFinderImpl
045            extends BasePersistenceImpl<MBThread> implements MBThreadFinder {
046    
047            public static final String COUNT_BY_G_U =
048                    MBThreadFinder.class.getName() + ".countByG_U";
049    
050            public static final String COUNT_BY_G_C =
051                    MBThreadFinder.class.getName() + ".countByG_C";
052    
053            public static final String COUNT_BY_G_U_C =
054                    MBThreadFinder.class.getName() + ".countByG_U_C";
055    
056            public static final String COUNT_BY_G_U_LPD =
057                    MBThreadFinder.class.getName() + ".countByG_U_LPD";
058    
059            public static final String COUNT_BY_G_U_A =
060                    MBThreadFinder.class.getName() + ".countByG_U_A";
061    
062            public static final String COUNT_BY_S_G_U =
063                    MBThreadFinder.class.getName() + ".countByS_G_U";
064    
065            public static final String COUNT_BY_G_U_C_A =
066                    MBThreadFinder.class.getName() + ".countByG_U_C_A";
067    
068            public static final String COUNT_BY_S_G_U_C =
069                    MBThreadFinder.class.getName() + ".countByS_G_U_C";
070    
071            public static final String FIND_BY_NO_ASSETS =
072                    MBThreadFinder.class.getName() + ".findByNoAssets";
073    
074            public static final String FIND_BY_G_U =
075                    MBThreadFinder.class.getName() + ".findByG_U";
076    
077            public static final String FIND_BY_G_C =
078                    MBThreadFinder.class.getName() + ".findByG_C";
079    
080            public static final String FIND_BY_G_U_C =
081                    MBThreadFinder.class.getName() + ".findByG_U_C";
082    
083            public static final String FIND_BY_G_U_LPD =
084                    MBThreadFinder.class.getName() + ".findByG_U_LPD";
085    
086            public static final String FIND_BY_G_U_A =
087                    MBThreadFinder.class.getName() + ".findByG_U_A";
088    
089            public static final String FIND_BY_S_G_U =
090                    MBThreadFinder.class.getName() + ".findByS_G_U";
091    
092            public static final String FIND_BY_G_U_C_A =
093                    MBThreadFinder.class.getName() + ".findByG_U_C_A";
094    
095            public static final String FIND_BY_S_G_U_C =
096                    MBThreadFinder.class.getName() + ".findByS_G_U_C";
097    
098            @Override
099            public int countByG_U(
100                            long groupId, long userId, QueryDefinition queryDefinition)
101                    throws SystemException {
102    
103                    Session session = null;
104    
105                    try {
106                            session = openSession();
107    
108                            String sql = CustomSQLUtil.get(COUNT_BY_G_U);
109    
110                            sql = updateSQL(sql, queryDefinition);
111    
112                            SQLQuery q = session.createSQLQuery(sql);
113    
114                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
115    
116                            QueryPos qPos = QueryPos.getInstance(q);
117    
118                            qPos.add(groupId);
119                            qPos.add(userId);
120    
121                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
122                                    qPos.add(queryDefinition.getStatus());
123                            }
124    
125                            Iterator<Long> itr = q.iterate();
126    
127                            if (itr.hasNext()) {
128                                    Long count = itr.next();
129    
130                                    if (count != null) {
131                                            return count.intValue();
132                                    }
133                            }
134    
135                            return 0;
136                    }
137                    catch (Exception e) {
138                            throw new SystemException(e);
139                    }
140                    finally {
141                            closeSession(session);
142                    }
143            }
144    
145            @Override
146            public int countByG_C(
147                            long groupId, long[] categoryIds, QueryDefinition queryDefinition)
148                    throws SystemException {
149    
150                    return doCountByG_C(groupId, categoryIds, queryDefinition, false);
151            }
152    
153            @Override
154            public int countByG_U_C(
155                            long groupId, long userId, long[] categoryIds,
156                            QueryDefinition queryDefinition)
157                    throws SystemException {
158    
159                    Session session = null;
160    
161                    try {
162                            session = openSession();
163    
164                            String sql = CustomSQLUtil.get(COUNT_BY_G_U_C);
165    
166                            if (ArrayUtil.isEmpty(categoryIds)) {
167                                    sql = StringUtil.replace(
168                                            sql, "(MBThread.categoryId = ?) AND", StringPool.BLANK);
169                            }
170                            else {
171                                    sql = StringUtil.replace(
172                                            sql, "MBThread.categoryId = ?",
173                                            "MBThread.categoryId = " +
174                                                    StringUtil.merge(
175                                                            categoryIds, " OR MBThread.categoryId = "));
176                            }
177    
178                            sql = updateSQL(sql, queryDefinition);
179    
180                            SQLQuery q = session.createSQLQuery(sql);
181    
182                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
183    
184                            QueryPos qPos = QueryPos.getInstance(q);
185    
186                            qPos.add(groupId);
187                            qPos.add(userId);
188    
189                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
190                                    qPos.add(queryDefinition.getStatus());
191                            }
192    
193                            Iterator<Long> itr = q.iterate();
194    
195                            if (itr.hasNext()) {
196                                    Long count = itr.next();
197    
198                                    if (count != null) {
199                                            return count.intValue();
200                                    }
201                            }
202    
203                            return 0;
204                    }
205                    catch (Exception e) {
206                            throw new SystemException(e);
207                    }
208                    finally {
209                            closeSession(session);
210                    }
211            }
212    
213            @Override
214            public int countByG_U_LPD(
215                            long groupId, long userId, long[] categoryIds, Date lastPostDate,
216                            QueryDefinition queryDefinition)
217                    throws SystemException {
218    
219                    Session session = null;
220    
221                    try {
222                            session = openSession();
223    
224                            String sql = CustomSQLUtil.get(COUNT_BY_G_U_LPD);
225    
226                            if (ArrayUtil.isNotEmpty(categoryIds)) {
227                                    sql = StringUtil.replace(
228                                            sql, "MBThread.categoryId != -1",
229                                            "MBThread.categoryId = " +
230                                                    StringUtil.merge(
231                                                            categoryIds, " OR MBThread.categoryId = "));
232                            }
233    
234                            if (userId <= 0) {
235                                    sql = StringUtil.replace(
236                                            sql, _INNER_JOIN_SQL, StringPool.BLANK);
237                                    sql = StringUtil.replace(sql, _USER_ID_SQL, StringPool.BLANK);
238                            }
239    
240                            sql = updateSQL(sql, queryDefinition);
241    
242                            SQLQuery q = session.createSQLQuery(sql);
243    
244                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
245    
246                            QueryPos qPos = QueryPos.getInstance(q);
247    
248                            qPos.add(groupId);
249                            qPos.add(lastPostDate);
250    
251                            if (userId > 0) {
252                                    qPos.add(userId);
253                            }
254    
255                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
256                                    qPos.add(queryDefinition.getStatus());
257                            }
258    
259                            Iterator<Long> itr = q.iterate();
260    
261                            if (itr.hasNext()) {
262                                    Long count = itr.next();
263    
264                                    if (count != null) {
265                                            return count.intValue();
266                                    }
267                            }
268    
269                            return 0;
270                    }
271                    catch (Exception e) {
272                            throw new SystemException(e);
273                    }
274                    finally {
275                            closeSession(session);
276                    }
277            }
278    
279            @Override
280            public int countByG_U_A(
281                            long groupId, long userId, boolean anonymous,
282                            QueryDefinition queryDefinition)
283                    throws SystemException {
284    
285                    Session session = null;
286    
287                    try {
288                            session = openSession();
289    
290                            String sql = CustomSQLUtil.get(COUNT_BY_G_U_A);
291    
292                            sql = updateSQL(sql, queryDefinition);
293    
294                            SQLQuery q = session.createSQLQuery(sql);
295    
296                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
297    
298                            QueryPos qPos = QueryPos.getInstance(q);
299    
300                            qPos.add(groupId);
301                            qPos.add(userId);
302                            qPos.add(anonymous);
303    
304                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
305                                    qPos.add(queryDefinition.getStatus());
306                            }
307    
308                            Iterator<Long> itr = q.iterate();
309    
310                            if (itr.hasNext()) {
311                                    Long count = itr.next();
312    
313                                    if (count != null) {
314                                            return count.intValue();
315                                    }
316                            }
317    
318                            return 0;
319                    }
320                    catch (Exception e) {
321                            throw new SystemException(e);
322                    }
323                    finally {
324                            closeSession(session);
325                    }
326            }
327    
328            @Override
329            public int countByS_G_U(
330                            long groupId, long userId, QueryDefinition queryDefinition)
331                    throws SystemException {
332    
333                    return doCountByS_G_U(groupId, userId, queryDefinition);
334            }
335    
336            @Override
337            public int countByG_U_C_A(
338                            long groupId, long userId, long[] categoryIds, boolean anonymous,
339                            QueryDefinition queryDefinition)
340                    throws SystemException {
341    
342                    Session session = null;
343    
344                    try {
345                            session = openSession();
346    
347                            String sql = CustomSQLUtil.get(COUNT_BY_G_U_C);
348    
349                            if (ArrayUtil.isEmpty(categoryIds)) {
350                                    sql = StringUtil.replace(
351                                            sql, "(MBThread.categoryId = ?) AND", StringPool.BLANK);
352                            }
353                            else {
354                                    sql = StringUtil.replace(
355                                            sql, "MBThread.categoryId = ?",
356                                            "MBThread.categoryId = " +
357                                                    StringUtil.merge(
358                                                            categoryIds, " OR MBThread.categoryId = "));
359                            }
360    
361                            sql = updateSQL(sql, queryDefinition);
362    
363                            SQLQuery q = session.createSQLQuery(sql);
364    
365                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
366    
367                            QueryPos qPos = QueryPos.getInstance(q);
368    
369                            qPos.add(groupId);
370                            qPos.add(userId);
371                            qPos.add(anonymous);
372    
373                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
374                                    qPos.add(queryDefinition.getStatus());
375                            }
376    
377                            Iterator<Long> itr = q.iterate();
378    
379                            if (itr.hasNext()) {
380                                    Long count = itr.next();
381    
382                                    if (count != null) {
383                                            return count.intValue();
384                                    }
385                            }
386    
387                            return 0;
388                    }
389                    catch (Exception e) {
390                            throw new SystemException(e);
391                    }
392                    finally {
393                            closeSession(session);
394                    }
395            }
396    
397            @Override
398            public int countByS_G_U_C(
399                            long groupId, long userId, long[] categoryIds,
400                            QueryDefinition queryDefinition)
401                    throws SystemException {
402    
403                    return doCountByS_G_U_C(
404                            groupId, userId, categoryIds, queryDefinition, false);
405            }
406    
407            @Override
408            public int filterCountByG_C(long groupId, long categoryId)
409                    throws SystemException {
410    
411                    if (!InlineSQLHelperUtil.isEnabled(groupId)) {
412                            return MBThreadUtil.countByG_C(groupId, categoryId);
413                    }
414    
415                    Session session = null;
416    
417                    try {
418                            session = openSession();
419    
420                            String sql = CustomSQLUtil.get(COUNT_BY_G_C);
421    
422                            sql = InlineSQLHelperUtil.replacePermissionCheck(
423                                    sql, MBMessage.class.getName(), "MBThread.rootMessageId",
424                                    groupId);
425    
426                            SQLQuery q = session.createSQLQuery(sql);
427    
428                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
429    
430                            QueryPos qPos = QueryPos.getInstance(q);
431    
432                            qPos.add(groupId);
433                            qPos.add(categoryId);
434    
435                            Iterator<Long> itr = q.iterate();
436    
437                            if (itr.hasNext()) {
438                                    Long count = itr.next();
439    
440                                    if (count != null) {
441                                            return count.intValue();
442                                    }
443                            }
444    
445                            return 0;
446                    }
447                    catch (Exception e) {
448                            throw processException(e);
449                    }
450                    finally {
451                            closeSession(session);
452                    }
453            }
454    
455            @Override
456            public int filterCountByG_C(
457                            long groupId, long[] categoryIds, QueryDefinition queryDefinition)
458                    throws SystemException {
459    
460                    return doCountByG_C(groupId, categoryIds, queryDefinition, true);
461            }
462    
463            @Override
464            public int filterCountByS_G_U_C(
465                            long groupId, long userId, long[] categoryIds,
466                            QueryDefinition queryDefinition)
467                    throws SystemException {
468    
469                    return doCountByS_G_U_C(
470                            groupId, userId, categoryIds, queryDefinition, true);
471            }
472    
473            @Override
474            public List<MBThread> filterFindByG_C(
475                            long groupId, long categoryId, int start, int end)
476                    throws SystemException {
477    
478                    if (!InlineSQLHelperUtil.isEnabled(groupId)) {
479                            return MBThreadUtil.findByG_C(groupId, categoryId, start, end);
480                    }
481    
482                    Session session = null;
483    
484                    try {
485                            session = openSession();
486    
487                            String sql = CustomSQLUtil.get(FIND_BY_G_C);
488    
489                            sql = InlineSQLHelperUtil.replacePermissionCheck(
490                                    sql, MBMessage.class.getName(), "MBThread.rootMessageId",
491                                    groupId);
492    
493                            SQLQuery q = session.createSQLQuery(sql);
494    
495                            q.addEntity("MBThread", MBThreadImpl.class);
496    
497                            QueryPos qPos = QueryPos.getInstance(q);
498    
499                            qPos.add(groupId);
500                            qPos.add(categoryId);
501    
502                            return (List<MBThread>)QueryUtil.list(q, getDialect(), start, end);
503                    }
504                    catch (Exception e) {
505                            throw new SystemException(e);
506                    }
507                    finally {
508                            closeSession(session);
509                    }
510            }
511    
512            @Override
513            public List<MBThread> filterFindByG_C(
514                            long groupId, long[] categoryIds, QueryDefinition queryDefinition)
515                    throws SystemException {
516    
517                    return doFindByG_C(groupId, categoryIds, queryDefinition, true);
518            }
519    
520            @Override
521            public List<MBThread> filterFindByS_G_U_C(
522                            long groupId, long userId, long[] categoryIds,
523                            QueryDefinition queryDefinition)
524                    throws SystemException {
525    
526                    return doFindByS_G_U_C(
527                            groupId, userId, categoryIds, queryDefinition, true);
528            }
529    
530            @Override
531            public List<MBThread> findByNoAssets() throws SystemException {
532                    Session session = null;
533    
534                    try {
535                            session = openSession();
536    
537                            String sql = CustomSQLUtil.get(FIND_BY_NO_ASSETS);
538    
539                            SQLQuery q = session.createSQLQuery(sql);
540    
541                            q.addEntity("MBThread", MBThreadImpl.class);
542    
543                            return q.list(true);
544                    }
545                    catch (Exception e) {
546                            throw new SystemException(e);
547                    }
548                    finally {
549                            closeSession(session);
550                    }
551            }
552    
553            @Override
554            public List<MBThread> findByG_U(
555                            long groupId, long userId, QueryDefinition queryDefinition)
556                    throws SystemException {
557    
558                    Session session = null;
559    
560                    try {
561                            session = openSession();
562    
563                            String sql = CustomSQLUtil.get(FIND_BY_G_U);
564    
565                            sql = updateSQL(sql, queryDefinition);
566    
567                            SQLQuery q = session.createSQLQuery(sql);
568    
569                            q.addEntity("MBThread", MBThreadImpl.class);
570    
571                            QueryPos qPos = QueryPos.getInstance(q);
572    
573                            qPos.add(groupId);
574                            qPos.add(userId);
575    
576                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
577                                    qPos.add(queryDefinition.getStatus());
578                            }
579    
580                            return (List<MBThread>)QueryUtil.list(
581                                    q, getDialect(), queryDefinition.getStart(),
582                                    queryDefinition.getEnd());
583                    }
584                    catch (Exception e) {
585                            throw new SystemException(e);
586                    }
587                    finally {
588                            closeSession(session);
589                    }
590            }
591    
592            @Override
593            public List<MBThread> findByG_C(
594                            long groupId, long[] categoryIds, QueryDefinition queryDefinition)
595                    throws SystemException {
596    
597                    return doFindByG_C(groupId, categoryIds, queryDefinition, false);
598            }
599    
600            @Override
601            public List<MBThread> findByG_U_C(
602                            long groupId, long userId, long[] categoryIds,
603                            QueryDefinition queryDefinition)
604                    throws SystemException {
605    
606                    Session session = null;
607    
608                    try {
609                            session = openSession();
610    
611                            String sql = CustomSQLUtil.get(FIND_BY_G_U_C);
612    
613                            if (ArrayUtil.isEmpty(categoryIds)) {
614                                    sql = StringUtil.replace(
615                                            sql, "(MBThread.categoryId = ?) AND", StringPool.BLANK);
616                            }
617                            else {
618                                    sql = StringUtil.replace(
619                                            sql, "MBThread.categoryId = ?",
620                                            "MBThread.categoryId = " +
621                                                    StringUtil.merge(
622                                                            categoryIds, " OR MBThread.categoryId = "));
623                            }
624    
625                            sql = updateSQL(sql, queryDefinition);
626    
627                            SQLQuery q = session.createSQLQuery(sql);
628    
629                            q.addEntity("MBThread", MBThreadImpl.class);
630    
631                            QueryPos qPos = QueryPos.getInstance(q);
632    
633                            qPos.add(groupId);
634                            qPos.add(userId);
635    
636                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
637                                    qPos.add(queryDefinition.getStatus());
638                            }
639    
640                            return (List<MBThread>)QueryUtil.list(
641                                    q, getDialect(), queryDefinition.getStart(),
642                                    queryDefinition.getEnd());
643                    }
644                    catch (Exception e) {
645                            throw new SystemException(e);
646                    }
647                    finally {
648                            closeSession(session);
649                    }
650            }
651    
652            @Override
653            public List<MBThread> findByG_U_LPD(
654                            long groupId, long userId, long[] categoryIds, Date lastPostDate,
655                            QueryDefinition queryDefinition)
656                    throws SystemException {
657    
658                    Session session = null;
659    
660                    try {
661                            session = openSession();
662    
663                            String sql = CustomSQLUtil.get(FIND_BY_G_U_LPD);
664    
665                            if (ArrayUtil.isNotEmpty(categoryIds)) {
666                                    sql = StringUtil.replace(
667                                            sql, "MBThread.categoryId != -1",
668                                            "MBThread.categoryId = " +
669                                                    StringUtil.merge(
670                                                            categoryIds, " OR MBThread.categoryId = "));
671                            }
672    
673                            if (userId <= 0) {
674                                    sql = StringUtil.replace(
675                                            sql, _INNER_JOIN_SQL, StringPool.BLANK);
676                                    sql = StringUtil.replace(sql, _USER_ID_SQL, StringPool.BLANK);
677                            }
678    
679                            sql = updateSQL(sql, queryDefinition);
680    
681                            SQLQuery q = session.createSQLQuery(sql);
682    
683                            q.addEntity("MBThread", MBThreadImpl.class);
684    
685                            QueryPos qPos = QueryPos.getInstance(q);
686    
687                            qPos.add(groupId);
688                            qPos.add(lastPostDate);
689    
690                            if (userId > 0) {
691                                    qPos.add(userId);
692                            }
693    
694                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
695                                    qPos.add(queryDefinition.getStatus());
696                            }
697    
698                            return (List<MBThread>)QueryUtil.list(
699                                    q, getDialect(), queryDefinition.getStart(),
700                                    queryDefinition.getEnd());
701                    }
702                    catch (Exception e) {
703                            throw new SystemException(e);
704                    }
705                    finally {
706                            closeSession(session);
707                    }
708            }
709    
710            @Override
711            public List<MBThread> findByG_U_A(
712                            long groupId, long userId, boolean anonymous,
713                            QueryDefinition queryDefinition)
714                    throws SystemException {
715    
716                    Session session = null;
717    
718                    try {
719                            session = openSession();
720    
721                            String sql = CustomSQLUtil.get(FIND_BY_G_U_A);
722    
723                            sql = updateSQL(sql, queryDefinition);
724    
725                            SQLQuery q = session.createSQLQuery(sql);
726    
727                            q.addEntity("MBThread", MBThreadImpl.class);
728    
729                            QueryPos qPos = QueryPos.getInstance(q);
730    
731                            qPos.add(groupId);
732                            qPos.add(userId);
733                            qPos.add(anonymous);
734    
735                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
736                                    qPos.add(queryDefinition.getStatus());
737                            }
738    
739                            return (List<MBThread>)QueryUtil.list(
740                                    q, getDialect(), queryDefinition.getStart(),
741                                    queryDefinition.getEnd());
742                    }
743                    catch (Exception e) {
744                            throw new SystemException(e);
745                    }
746                    finally {
747                            closeSession(session);
748                    }
749            }
750    
751            @Override
752            public List<MBThread> findByS_G_U(
753                            long groupId, long userId, QueryDefinition queryDefinition)
754                    throws SystemException {
755    
756                    Session session = null;
757    
758                    try {
759                            session = openSession();
760    
761                            String sql = CustomSQLUtil.get(FIND_BY_S_G_U);
762    
763                            sql = updateSQL(sql, queryDefinition);
764    
765                            SQLQuery q = session.createSQLQuery(sql);
766    
767                            q.addEntity("MBThread", MBThreadImpl.class);
768    
769                            QueryPos qPos = QueryPos.getInstance(q);
770    
771                            qPos.add(PortalUtil.getClassNameId(MBThread.class.getName()));
772                            qPos.add(groupId);
773                            qPos.add(userId);
774    
775                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
776                                    qPos.add(queryDefinition.getStatus());
777                            }
778    
779                            return (List<MBThread>)QueryUtil.list(
780                                    q, getDialect(), queryDefinition.getStart(),
781                                    queryDefinition.getEnd());
782                    }
783                    catch (Exception e) {
784                            throw new SystemException(e);
785                    }
786                    finally {
787                            closeSession(session);
788                    }
789            }
790    
791            @Override
792            public List<MBThread> findByG_U_C_A(
793                            long groupId, long userId, long[] categoryIds, boolean anonymous,
794                            QueryDefinition queryDefinition)
795                    throws SystemException {
796    
797                    Session session = null;
798    
799                    try {
800                            session = openSession();
801    
802                            String sql = CustomSQLUtil.get(FIND_BY_G_U_C_A);
803    
804                            if (ArrayUtil.isEmpty(categoryIds)) {
805                                    sql = StringUtil.replace(
806                                            sql, "(MBThread.categoryId = ?) AND", StringPool.BLANK);
807                            }
808                            else {
809                                    sql = StringUtil.replace(
810                                            sql, "MBThread.categoryId = ?",
811                                            "MBThread.categoryId = " +
812                                                    StringUtil.merge(
813                                                            categoryIds, " OR MBThread.categoryId = "));
814                            }
815    
816                            sql = updateSQL(sql, queryDefinition);
817    
818                            SQLQuery q = session.createSQLQuery(sql);
819    
820                            q.addEntity("MBThread", MBThreadImpl.class);
821    
822                            QueryPos qPos = QueryPos.getInstance(q);
823    
824                            qPos.add(groupId);
825                            qPos.add(userId);
826                            qPos.add(anonymous);
827    
828                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
829                                    qPos.add(queryDefinition.getStatus());
830                            }
831    
832                            return (List<MBThread>)QueryUtil.list(
833                                    q, getDialect(), queryDefinition.getStart(),
834                                    queryDefinition.getEnd());
835                    }
836                    catch (Exception e) {
837                            throw new SystemException(e);
838                    }
839                    finally {
840                            closeSession(session);
841                    }
842            }
843    
844            @Override
845            public List<MBThread> findByS_G_U_C(
846                            long groupId, long userId, long[] categoryIds,
847                            QueryDefinition queryDefinition)
848                    throws SystemException {
849    
850                    return doFindByS_G_U_C(
851                            groupId, userId, categoryIds, queryDefinition, false);
852            }
853    
854            protected int doCountByG_C(
855                            long groupId, long[] categoryIds, QueryDefinition queryDefinition,
856                            boolean inlineSQLHelper)
857                    throws SystemException {
858    
859                    if (!inlineSQLHelper || !InlineSQLHelperUtil.isEnabled(groupId)) {
860                            if (queryDefinition.isExcludeStatus()) {
861                                    return MBThreadUtil.countByG_C_NotS(
862                                            groupId, categoryIds, queryDefinition.getStatus());
863                            }
864                            else {
865                                    if (queryDefinition.getStatus() !=
866                                                    WorkflowConstants.STATUS_ANY) {
867    
868                                            return MBThreadUtil.countByG_C_S(
869                                                    groupId, categoryIds, queryDefinition.getStatus());
870                                    }
871                                    else {
872                                            return MBThreadUtil.countByG_C(groupId, categoryIds);
873                                    }
874                            }
875                    }
876    
877                    Session session = null;
878    
879                    try {
880                            session = openSession();
881    
882                            String sql = CustomSQLUtil.get(COUNT_BY_G_C);
883    
884                            sql = updateSQL(sql, queryDefinition);
885    
886                            sql = InlineSQLHelperUtil.replacePermissionCheck(
887                                    sql, MBMessage.class.getName(), "MBThread.rootMessageId",
888                                    groupId);
889    
890                            if (ArrayUtil.isEmpty(categoryIds)) {
891                                    sql = StringUtil.replace(
892                                            sql, "(MBThread.groupId = ?) AND",
893                                            "(MBThread.groupId = ?)");
894    
895                                    sql = StringUtil.replace(
896                                            sql, "(MBThread.categoryId = ?)", StringPool.BLANK);
897                            }
898                            else {
899                                    sql = StringUtil.replace(
900                                            sql, "MBThread.categoryId = ?",
901                                            "MBThread.categoryId = " +
902                                                    StringUtil.merge(
903                                                            categoryIds, " OR MBThread.categoryId = "));
904                            }
905    
906                            SQLQuery q = session.createSQLQuery(sql);
907    
908                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
909    
910                            QueryPos qPos = QueryPos.getInstance(q);
911    
912                            qPos.add(groupId);
913    
914                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
915                                    qPos.add(queryDefinition.getStatus());
916                            }
917    
918                            Iterator<Long> itr = q.iterate();
919    
920                            if (itr.hasNext()) {
921                                    Long count = itr.next();
922    
923                                    if (count != null) {
924                                            return count.intValue();
925                                    }
926                            }
927    
928                            return 0;
929                    }
930                    catch (Exception e) {
931                            throw processException(e);
932                    }
933                    finally {
934                            closeSession(session);
935                    }
936            }
937    
938            protected int doCountByS_G_U(
939                            long groupId, long userId, QueryDefinition queryDefinition)
940                    throws SystemException {
941    
942                    Session session = null;
943    
944                    try {
945                            session = openSession();
946    
947                            String sql = CustomSQLUtil.get(COUNT_BY_S_G_U);
948    
949                            sql = updateSQL(sql, queryDefinition);
950    
951                            SQLQuery q = session.createSQLQuery(sql);
952    
953                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
954    
955                            QueryPos qPos = QueryPos.getInstance(q);
956    
957                            qPos.add(PortalUtil.getClassNameId(MBThread.class.getName()));
958                            qPos.add(groupId);
959                            qPos.add(userId);
960    
961                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
962                                    qPos.add(queryDefinition.getStatus());
963                            }
964    
965                            Iterator<Long> itr = q.iterate();
966    
967                            if (itr.hasNext()) {
968                                    Long count = itr.next();
969    
970                                    if (count != null) {
971                                            return count.intValue();
972                                    }
973                            }
974    
975                            return 0;
976                    }
977                    catch (Exception e) {
978                            throw new SystemException(e);
979                    }
980                    finally {
981                            closeSession(session);
982                    }
983            }
984    
985            protected int doCountByS_G_U_C(
986                            long groupId, long userId, long[] categoryIds,
987                            QueryDefinition queryDefinition, boolean inlineSQLHelper)
988                    throws SystemException {
989    
990                    Session session = null;
991    
992                    try {
993                            session = openSession();
994    
995                            String sql = CustomSQLUtil.get(COUNT_BY_S_G_U_C);
996    
997                            if (ArrayUtil.isEmpty(categoryIds)) {
998                                    sql = StringUtil.replace(
999                                            sql, "(MBThread.categoryId = ?) AND", StringPool.BLANK);
1000                            }
1001                            else {
1002                                    sql = StringUtil.replace(
1003                                            sql, "MBThread.categoryId = ?",
1004                                            "MBThread.categoryId = " +
1005                                                    StringUtil.merge(
1006                                                            categoryIds, " OR MBThread.categoryId = "));
1007                            }
1008    
1009                            sql = updateSQL(sql, queryDefinition);
1010    
1011                            if (inlineSQLHelper) {
1012                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
1013                                            sql, MBMessage.class.getName(), "MBThread.rootMessageId",
1014                                            groupId);
1015                            }
1016    
1017                            SQLQuery q = session.createSQLQuery(sql);
1018    
1019                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
1020    
1021                            QueryPos qPos = QueryPos.getInstance(q);
1022    
1023                            qPos.add(PortalUtil.getClassNameId(MBThread.class.getName()));
1024                            qPos.add(groupId);
1025                            qPos.add(userId);
1026    
1027                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
1028                                    qPos.add(queryDefinition.getStatus());
1029                            }
1030    
1031                            Iterator<Long> itr = q.iterate();
1032    
1033                            if (itr.hasNext()) {
1034                                    Long count = itr.next();
1035    
1036                                    if (count != null) {
1037                                            return count.intValue();
1038                                    }
1039                            }
1040    
1041                            return 0;
1042                    }
1043                    catch (Exception e) {
1044                            throw new SystemException(e);
1045                    }
1046                    finally {
1047                            closeSession(session);
1048                    }
1049            }
1050    
1051            protected List<MBThread> doFindByG_C(
1052                            long groupId, long[] categoryIds, QueryDefinition queryDefinition,
1053                            boolean inlineSQLHelper)
1054                    throws SystemException {
1055    
1056                    if (!inlineSQLHelper || !InlineSQLHelperUtil.isEnabled(groupId)) {
1057                            if (queryDefinition.isExcludeStatus()) {
1058                                    return MBThreadUtil.findByG_C_NotS(
1059                                            groupId, categoryIds, queryDefinition.getStatus(),
1060                                            queryDefinition.getStart(), queryDefinition.getEnd());
1061                            }
1062                            else {
1063                                    if (queryDefinition.getStatus() !=
1064                                                    WorkflowConstants.STATUS_ANY) {
1065    
1066                                            return MBThreadUtil.findByG_C_S(
1067                                                    groupId, categoryIds, queryDefinition.getStatus(),
1068                                                    queryDefinition.getStart(), queryDefinition.getEnd());
1069                                    }
1070                                    else {
1071                                            return MBThreadUtil.findByG_C(
1072                                                    groupId, categoryIds, queryDefinition.getStart(),
1073                                                    queryDefinition.getEnd());
1074                                    }
1075                            }
1076                    }
1077    
1078                    Session session = null;
1079    
1080                    try {
1081                            session = openSession();
1082    
1083                            String sql = CustomSQLUtil.get(FIND_BY_G_C);
1084    
1085                            sql = updateSQL(sql, queryDefinition);
1086    
1087                            sql = InlineSQLHelperUtil.replacePermissionCheck(
1088                                    sql, MBMessage.class.getName(), "MBThread.rootMessageId",
1089                                    groupId);
1090    
1091                            if (ArrayUtil.isEmpty(categoryIds)) {
1092                                    sql = StringUtil.replace(
1093                                            sql, "(MBThread.groupId = ?) AND",
1094                                            "(MBThread.groupId = ?)");
1095    
1096                                    sql = StringUtil.replace(
1097                                            sql, "(MBThread.categoryId = ?)", StringPool.BLANK);
1098                            }
1099                            else {
1100                                    sql = StringUtil.replace(
1101                                            sql, "MBThread.categoryId = ?",
1102                                            "MBThread.categoryId = " +
1103                                                    StringUtil.merge(
1104                                                            categoryIds, " OR MBThread.categoryId = "));
1105                            }
1106    
1107                            SQLQuery q = session.createSQLQuery(sql);
1108    
1109                            q.addEntity("MBThread", MBThreadImpl.class);
1110    
1111                            QueryPos qPos = QueryPos.getInstance(q);
1112    
1113                            qPos.add(groupId);
1114    
1115                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
1116                                    qPos.add(queryDefinition.getStatus());
1117                            }
1118    
1119                            return (List<MBThread>)QueryUtil.list(
1120                                    q, getDialect(), queryDefinition.getStart(),
1121                                    queryDefinition.getEnd());
1122                    }
1123                    catch (Exception e) {
1124                            throw new SystemException(e);
1125                    }
1126                    finally {
1127                            closeSession(session);
1128                    }
1129            }
1130    
1131            protected List<MBThread> doFindByS_G_U_C(
1132                            long groupId, long userId, long[] categoryIds,
1133                            QueryDefinition queryDefinition, boolean inlineSQLHelper)
1134                    throws SystemException {
1135    
1136                    Session session = null;
1137    
1138                    try {
1139                            session = openSession();
1140    
1141                            String sql = CustomSQLUtil.get(FIND_BY_S_G_U_C);
1142    
1143                            if (ArrayUtil.isEmpty(categoryIds)) {
1144                                    sql = StringUtil.replace(
1145                                            sql, "(MBThread.categoryId = ?) AND", StringPool.BLANK);
1146                            }
1147                            else {
1148                                    sql = StringUtil.replace(
1149                                            sql, "MBThread.categoryId = ?",
1150                                            "MBThread.categoryId = " +
1151                                                    StringUtil.merge(
1152                                                            categoryIds, " OR MBThread.categoryId = "));
1153                            }
1154    
1155                            sql = updateSQL(sql, queryDefinition);
1156    
1157                            if (inlineSQLHelper) {
1158                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
1159                                            sql, MBMessage.class.getName(), "MBThread.rootMessageId",
1160                                            groupId);
1161                            }
1162    
1163                            SQLQuery q = session.createSQLQuery(sql);
1164    
1165                            q.addEntity("MBThread", MBThreadImpl.class);
1166    
1167                            QueryPos qPos = QueryPos.getInstance(q);
1168    
1169                            qPos.add(PortalUtil.getClassNameId(MBThread.class.getName()));
1170                            qPos.add(groupId);
1171                            qPos.add(userId);
1172    
1173                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
1174                                    qPos.add(queryDefinition.getStatus());
1175                            }
1176    
1177                            return (List<MBThread>)QueryUtil.list(
1178                                    q, getDialect(), queryDefinition.getStart(),
1179                                    queryDefinition.getEnd());
1180                    }
1181                    catch (Exception e) {
1182                            throw new SystemException(e);
1183                    }
1184                    finally {
1185                            closeSession(session);
1186                    }
1187            }
1188    
1189            protected String updateSQL(String sql, QueryDefinition queryDefinition) {
1190                    if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
1191                            return sql;
1192                    }
1193    
1194                    if (queryDefinition.isExcludeStatus()) {
1195                            return CustomSQLUtil.appendCriteria(
1196                                    sql, "AND (MBThread.status != ?)");
1197                    }
1198    
1199                    return CustomSQLUtil.appendCriteria(sql, "AND (MBThread.status = ?)");
1200            }
1201    
1202            private static final String _INNER_JOIN_SQL =
1203                    "INNER JOIN MBMessage ON (MBThread.threadId = MBMessage.threadId)";
1204    
1205            private static final String _USER_ID_SQL = "AND (MBMessage.userId = ?)";
1206    
1207    }