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