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.QueryPos;
018    import com.liferay.portal.kernel.dao.orm.QueryUtil;
019    import com.liferay.portal.kernel.dao.orm.SQLQuery;
020    import com.liferay.portal.kernel.dao.orm.Session;
021    import com.liferay.portal.kernel.dao.orm.Type;
022    import com.liferay.portal.kernel.exception.SystemException;
023    import com.liferay.portal.kernel.util.ArrayUtil;
024    import com.liferay.portal.kernel.util.CalendarUtil;
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.portlet.messageboards.model.MBMessage;
030    import com.liferay.portlet.messageboards.model.impl.MBMessageImpl;
031    import com.liferay.portlet.messageboards.service.persistence.MBMessageFinder;
032    import com.liferay.util.dao.orm.CustomSQLUtil;
033    
034    import java.sql.Timestamp;
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     */
043    public class MBMessageFinderImpl
044            extends MBMessageFinderBaseImpl implements MBMessageFinder {
045    
046            public static final String COUNT_BY_C_T =
047                    MBMessageFinder.class.getName() + ".countByC_T";
048    
049            public static final String COUNT_BY_G_U_C_S =
050                    MBMessageFinder.class.getName() + ".countByG_U_C_S";
051    
052            public static final String COUNT_BY_G_U_MD_C_S =
053                    MBMessageFinder.class.getName() + ".countByG_U_MD_C_S";
054    
055            public static final String COUNT_BY_G_U_C_A_S =
056                    MBMessageFinder.class.getName() + ".countByG_U_C_A_S";
057    
058            public static final String FIND_BY_NO_ASSETS =
059                    MBMessageFinder.class.getName() + ".findByNoAssets";
060    
061            public static final String FIND_BY_G_U_C_S =
062                    MBMessageFinder.class.getName() + ".findByG_U_C_S";
063    
064            public static final String FIND_BY_G_U_MD_C_S =
065                    MBMessageFinder.class.getName() + ".findByG_U_MD_C_S";
066    
067            public static final String FIND_BY_G_U_C_A_S =
068                    MBMessageFinder.class.getName() + ".findByG_U_C_A_S";
069    
070            @Override
071            public int countByC_T(Date createDate, long threadId) {
072                    Timestamp createDate_TS = CalendarUtil.getTimestamp(createDate);
073    
074                    Session session = null;
075    
076                    try {
077                            session = openSession();
078    
079                            String sql = CustomSQLUtil.get(COUNT_BY_C_T);
080    
081                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
082    
083                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
084    
085                            QueryPos qPos = QueryPos.getInstance(q);
086    
087                            qPos.add(createDate_TS);
088                            qPos.add(threadId);
089    
090                            Iterator<Long> itr = q.iterate();
091    
092                            if (itr.hasNext()) {
093                                    Long count = itr.next();
094    
095                                    if (count != null) {
096                                            return count.intValue();
097                                    }
098                            }
099    
100                            return 0;
101                    }
102                    catch (Exception e) {
103                            throw new SystemException(e);
104                    }
105                    finally {
106                            closeSession(session);
107                    }
108            }
109    
110            @Override
111            public int countByG_U_C_S(
112                    long groupId, long userId, long[] categoryIds, int status) {
113    
114                    return doCountByG_U_C_S(groupId, userId, categoryIds, status, false);
115            }
116    
117            @Override
118            public int countByG_U_C_A_S(
119                    long groupId, long userId, long[] categoryIds, boolean anonymous,
120                    int status) {
121    
122                    return doCountByG_U_C_A_S(
123                            groupId, userId, categoryIds, anonymous, status, false);
124            }
125    
126            @Override
127            public int filterCountByG_U_C_S(
128                    long groupId, long userId, long[] categoryIds, int status) {
129    
130                    return doCountByG_U_C_S(groupId, userId, categoryIds, status, true);
131            }
132    
133            @Override
134            public int filterCountByG_U_MD_C_S(
135                    long groupId, long userId, Date modifiedDate, long[] categoryIds,
136                    int status) {
137    
138                    return doCountByG_U_MD_C_S(
139                            groupId, userId, modifiedDate, categoryIds, status, true);
140            }
141    
142            @Override
143            public int filterCountByG_U_C_A_S(
144                    long groupId, long userId, long[] categoryIds, boolean anonymous,
145                    int status) {
146    
147                    return doCountByG_U_C_A_S(
148                            groupId, userId, categoryIds, anonymous, status, true);
149            }
150    
151            @Override
152            public List<Long> filterFindByG_U_C_S(
153                    long groupId, long userId, long[] categoryIds, int status, int start,
154                    int end) {
155    
156                    return doFindByG_U_C_S(
157                            groupId, userId, categoryIds, status, start, end, true);
158            }
159    
160            @Override
161            public List<Long> filterFindByG_U_MD_C_S(
162                    long groupId, long userId, Date modifiedDate, long[] categoryIds,
163                    int status, int start, int end) {
164    
165                    return doFindByG_U_MD_C_S(
166                            groupId, userId, modifiedDate, categoryIds, status, start, end,
167                            true);
168            }
169    
170            @Override
171            public List<Long> filterFindByG_U_C_A_S(
172                    long groupId, long userId, long[] categoryIds, boolean anonymous,
173                    int status, int start, int end) {
174    
175                    return doFindByG_U_C_A_S(
176                            groupId, userId, categoryIds, anonymous, status, start, end, true);
177            }
178    
179            @Override
180            public List<MBMessage> findByNoAssets() {
181                    Session session = null;
182    
183                    try {
184                            session = openSession();
185    
186                            String sql = CustomSQLUtil.get(FIND_BY_NO_ASSETS);
187    
188                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
189    
190                            q.addEntity("MBMessage", MBMessageImpl.class);
191    
192                            return q.list(true);
193                    }
194                    catch (Exception e) {
195                            throw new SystemException(e);
196                    }
197                    finally {
198                            closeSession(session);
199                    }
200            }
201    
202            @Override
203            public List<Long> findByG_U_C_S(
204                    long groupId, long userId, long[] categoryIds, int status, int start,
205                    int end) {
206    
207                    return doFindByG_U_C_S(
208                            groupId, userId, categoryIds, status, start, end, false);
209            }
210    
211            @Override
212            public List<Long> findByG_U_C_A_S(
213                    long groupId, long userId, long[] categoryIds, boolean anonymous,
214                    int status, int start, int end) {
215    
216                    return doFindByG_U_C_A_S(
217                            groupId, userId, categoryIds, anonymous, status, start, end, false);
218            }
219    
220            protected int doCountByG_U_C_S(
221                    long groupId, long userId, long[] categoryIds, int status,
222                    boolean inlineSQLHelper) {
223    
224                    Session session = null;
225    
226                    try {
227                            session = openSession();
228    
229                            String sql = CustomSQLUtil.get(COUNT_BY_G_U_C_S);
230    
231                            if (userId <= 0) {
232                                    sql = StringUtil.replace(sql, _USER_ID_SQL, StringPool.BLANK);
233                            }
234    
235                            if (ArrayUtil.isEmpty(categoryIds)) {
236                                    sql = StringUtil.replace(
237                                            sql, "(currentMessage.categoryId = ?) AND",
238                                            StringPool.BLANK);
239                            }
240                            else {
241                                    sql = StringUtil.replace(
242                                            sql, "currentMessage.categoryId = ?",
243                                            "currentMessage.categoryId = " +
244                                                    StringUtil.merge(
245                                                            categoryIds, " OR currentMessage.categoryId = "));
246                            }
247    
248                            if (status != WorkflowConstants.STATUS_ANY) {
249                                    sql = CustomSQLUtil.appendCriteria(
250                                            sql, "AND (currentMessage.status = ?)");
251                            }
252    
253                            if (inlineSQLHelper) {
254                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
255                                            sql, MBMessage.class.getName(),
256                                            "currentMessage.rootMessageId", groupId);
257                            }
258    
259                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
260    
261                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
262    
263                            QueryPos qPos = QueryPos.getInstance(q);
264    
265                            qPos.add(groupId);
266    
267                            if (userId > 0) {
268                                    qPos.add(userId);
269                            }
270    
271                            if (status != WorkflowConstants.STATUS_ANY) {
272                                    qPos.add(status);
273                            }
274    
275                            Iterator<Long> itr = q.iterate();
276    
277                            if (itr.hasNext()) {
278                                    Long count = itr.next();
279    
280                                    if (count != null) {
281                                            return count.intValue();
282                                    }
283                            }
284    
285                            return 0;
286                    }
287                    catch (Exception e) {
288                            throw new SystemException(e);
289                    }
290                    finally {
291                            closeSession(session);
292                    }
293            }
294    
295            protected int doCountByG_U_MD_C_S(
296                    long groupId, long userId, Date modifiedDate, long[] categoryIds,
297                    int status, boolean inlineSQLHelper) {
298    
299                    Session session = null;
300    
301                    try {
302                            session = openSession();
303    
304                            String sql = CustomSQLUtil.get(COUNT_BY_G_U_MD_C_S);
305    
306                            if (userId <= 0) {
307                                    sql = StringUtil.replace(sql, _USER_ID_SQL, StringPool.BLANK);
308                            }
309    
310                            if (ArrayUtil.isEmpty(categoryIds)) {
311                                    sql = StringUtil.replace(
312                                            sql, "(currentMessage.categoryId = ?) AND",
313                                            StringPool.BLANK);
314                            }
315                            else {
316                                    sql = StringUtil.replace(
317                                            sql, "currentMessage.categoryId = ?",
318                                            "currentMessage.categoryId = " +
319                                                    StringUtil.merge(
320                                                            categoryIds, " OR currentMessage.categoryId = "));
321                            }
322    
323                            if (status != WorkflowConstants.STATUS_ANY) {
324                                    sql = CustomSQLUtil.appendCriteria(
325                                            sql, "AND (currentMessage.status = ?)");
326                            }
327    
328                            if (inlineSQLHelper) {
329                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
330                                            sql, MBMessage.class.getName(),
331                                            "currentMessage.rootMessageId", groupId);
332                            }
333    
334                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
335    
336                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
337    
338                            QueryPos qPos = QueryPos.getInstance(q);
339    
340                            qPos.add(groupId);
341    
342                            if (userId > 0) {
343                                    qPos.add(userId);
344                            }
345    
346                            qPos.add(modifiedDate);
347    
348                            if (status != WorkflowConstants.STATUS_ANY) {
349                                    qPos.add(status);
350                            }
351    
352                            Iterator<Long> itr = q.iterate();
353    
354                            if (itr.hasNext()) {
355                                    Long count = itr.next();
356    
357                                    if (count != null) {
358                                            return count.intValue();
359                                    }
360                            }
361    
362                            return 0;
363                    }
364                    catch (Exception e) {
365                            throw new SystemException(e);
366                    }
367                    finally {
368                            closeSession(session);
369                    }
370            }
371    
372            protected int doCountByG_U_C_A_S(
373                    long groupId, long userId, long[] categoryIds, boolean anonymous,
374                    int status, boolean inlineSQLHelper) {
375    
376                    Session session = null;
377    
378                    try {
379                            session = openSession();
380    
381                            String sql = CustomSQLUtil.get(COUNT_BY_G_U_C_A_S);
382    
383                            if (ArrayUtil.isEmpty(categoryIds)) {
384                                    sql = StringUtil.replace(
385                                            sql, "(currentMessage.categoryId = ?) AND",
386                                            StringPool.BLANK);
387                            }
388                            else {
389                                    sql = StringUtil.replace(
390                                            sql, "currentMessage.categoryId = ?",
391                                            "currentMessage.categoryId = " +
392                                                    StringUtil.merge(
393                                                            categoryIds, " OR currentMessage.categoryId = "));
394                            }
395    
396                            if (status != WorkflowConstants.STATUS_ANY) {
397                                    sql = CustomSQLUtil.appendCriteria(
398                                            sql, "AND (currentMessage.status = ?)");
399                            }
400    
401                            if (inlineSQLHelper) {
402                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
403                                            sql, MBMessage.class.getName(),
404                                            "currentMessage.rootMessageId", groupId);
405                            }
406    
407                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
408    
409                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
410    
411                            QueryPos qPos = QueryPos.getInstance(q);
412    
413                            qPos.add(groupId);
414                            qPos.add(userId);
415                            qPos.add(anonymous);
416    
417                            if (status != WorkflowConstants.STATUS_ANY) {
418                                    qPos.add(status);
419                            }
420    
421                            Iterator<Long> itr = q.iterate();
422    
423                            if (itr.hasNext()) {
424                                    Long count = itr.next();
425    
426                                    if (count != null) {
427                                            return count.intValue();
428                                    }
429                            }
430    
431                            return 0;
432                    }
433                    catch (Exception e) {
434                            throw new SystemException(e);
435                    }
436                    finally {
437                            closeSession(session);
438                    }
439            }
440    
441            protected List<Long> doFindByG_U_C_S(
442                    long groupId, long userId, long[] categoryIds, int status, int start,
443                    int end, boolean inlineSQLHelper) {
444    
445                    Session session = null;
446    
447                    try {
448                            session = openSession();
449    
450                            String sql = CustomSQLUtil.get(FIND_BY_G_U_C_S);
451    
452                            if (userId <= 0) {
453                                    sql = StringUtil.replace(sql, _USER_ID_SQL, StringPool.BLANK);
454                            }
455    
456                            if (ArrayUtil.isEmpty(categoryIds)) {
457                                    sql = StringUtil.replace(
458                                            sql, "(currentMessage.categoryId = ?) AND",
459                                            StringPool.BLANK);
460                            }
461                            else {
462                                    sql = StringUtil.replace(
463                                            sql, "currentMessage.categoryId = ?",
464                                            "currentMessage.categoryId = " +
465                                                    StringUtil.merge(
466                                                            categoryIds, " OR currentMessage.categoryId = "));
467                            }
468    
469                            if (status != WorkflowConstants.STATUS_ANY) {
470                                    sql = CustomSQLUtil.appendCriteria(
471                                            sql, "AND (currentMessage.status = ?)");
472                            }
473    
474                            if (inlineSQLHelper) {
475                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
476                                            sql, MBMessage.class.getName(),
477                                            "currentMessage.rootMessageId", groupId);
478                            }
479    
480                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
481    
482                            q.addScalar("threadId", Type.LONG);
483    
484                            QueryPos qPos = QueryPos.getInstance(q);
485    
486                            qPos.add(groupId);
487    
488                            if (userId > 0) {
489                                    qPos.add(userId);
490                            }
491    
492                            if (status != WorkflowConstants.STATUS_ANY) {
493                                    qPos.add(status);
494                            }
495    
496                            return (List<Long>)QueryUtil.list(q, getDialect(), start, end);
497                    }
498                    catch (Exception e) {
499                            throw new SystemException(e);
500                    }
501                    finally {
502                            closeSession(session);
503                    }
504            }
505    
506            protected List<Long> doFindByG_U_MD_C_S(
507                    long groupId, long userId, Date modifiedDate, long[] categoryIds,
508                    int status, int start, int end, boolean inlineSQLHelper) {
509    
510                    Session session = null;
511    
512                    try {
513                            session = openSession();
514    
515                            String sql = CustomSQLUtil.get(FIND_BY_G_U_MD_C_S);
516    
517                            if (userId <= 0) {
518                                    sql = StringUtil.replace(sql, _USER_ID_SQL, StringPool.BLANK);
519                            }
520    
521                            if (ArrayUtil.isEmpty(categoryIds)) {
522                                    sql = StringUtil.replace(
523                                            sql, "(currentMessage.categoryId = ?) AND",
524                                            StringPool.BLANK);
525                            }
526                            else {
527                                    sql = StringUtil.replace(
528                                            sql, "currentMessage.categoryId = ?",
529                                            "currentMessage.categoryId = " +
530                                                    StringUtil.merge(
531                                                            categoryIds, " OR currentMessage.categoryId = "));
532                            }
533    
534                            if (status != WorkflowConstants.STATUS_ANY) {
535                                    sql = CustomSQLUtil.appendCriteria(
536                                            sql, "AND (currentMessage.status = ?)");
537                            }
538    
539                            if (inlineSQLHelper) {
540                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
541                                            sql, MBMessage.class.getName(),
542                                            "currentMessage.rootMessageId", groupId);
543                            }
544    
545                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
546    
547                            q.addScalar("threadId", Type.LONG);
548    
549                            QueryPos qPos = QueryPos.getInstance(q);
550    
551                            qPos.add(groupId);
552    
553                            if (userId > 0) {
554                                    qPos.add(userId);
555                            }
556    
557                            qPos.add(modifiedDate);
558    
559                            if (status != WorkflowConstants.STATUS_ANY) {
560                                    qPos.add(status);
561                            }
562    
563                            return (List<Long>)QueryUtil.list(q, getDialect(), start, end);
564                    }
565                    catch (Exception e) {
566                            throw new SystemException(e);
567                    }
568                    finally {
569                            closeSession(session);
570                    }
571            }
572    
573            protected List<Long> doFindByG_U_C_A_S(
574                    long groupId, long userId, long[] categoryIds, boolean anonymous,
575                    int status, int start, int end, boolean inlineSQLHelper) {
576    
577                    Session session = null;
578    
579                    try {
580                            session = openSession();
581    
582                            String sql = CustomSQLUtil.get(FIND_BY_G_U_C_A_S);
583    
584                            if (ArrayUtil.isEmpty(categoryIds)) {
585                                    sql = StringUtil.replace(
586                                            sql, "(currentMessage.categoryId = ?) AND",
587                                            StringPool.BLANK);
588                            }
589                            else {
590                                    sql = StringUtil.replace(
591                                            sql, "currentMessage.categoryId = ?",
592                                            "currentMessage.categoryId = " +
593                                                    StringUtil.merge(
594                                                            categoryIds, " OR currentMessage.categoryId = "));
595                            }
596    
597                            if (status != WorkflowConstants.STATUS_ANY) {
598                                    sql = CustomSQLUtil.appendCriteria(
599                                            sql, "AND (currentMessage.status = ?)");
600                            }
601    
602                            if (inlineSQLHelper) {
603                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
604                                            sql, MBMessage.class.getName(),
605                                            "currentMessage.rootMessageId", groupId);
606                            }
607    
608                            SQLQuery q = session.createSynchronizedSQLQuery(sql);
609    
610                            q.addScalar("threadId", Type.LONG);
611    
612                            QueryPos qPos = QueryPos.getInstance(q);
613    
614                            qPos.add(groupId);
615                            qPos.add(userId);
616                            qPos.add(anonymous);
617    
618                            if (status != WorkflowConstants.STATUS_ANY) {
619                                    qPos.add(status);
620                            }
621    
622                            return (List<Long>)QueryUtil.list(q, getDialect(), start, end);
623                    }
624                    catch (Exception e) {
625                            throw new SystemException(e);
626                    }
627                    finally {
628                            closeSession(session);
629                    }
630            }
631    
632            private static final String _USER_ID_SQL =
633                    "AND (currentMessage.userId = ?)";
634    
635    }