001    /**
002     * Copyright (c) 2000-2012 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.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.CalendarUtil;
024    import com.liferay.portal.kernel.util.StringPool;
025    import com.liferay.portal.kernel.util.StringUtil;
026    import com.liferay.portal.kernel.workflow.WorkflowConstants;
027    import com.liferay.portal.security.permission.InlineSQLHelperUtil;
028    import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
029    import com.liferay.portlet.messageboards.model.MBMessage;
030    import com.liferay.portlet.messageboards.model.impl.MBMessageImpl;
031    import com.liferay.util.dao.orm.CustomSQLUtil;
032    
033    import java.sql.Timestamp;
034    
035    import java.util.Date;
036    import java.util.Iterator;
037    import java.util.List;
038    
039    /**
040     * @author Brian Wing Shun Chan
041     */
042    public class MBMessageFinderImpl
043            extends BasePersistenceImpl<MBMessage> implements MBMessageFinder {
044    
045            public static final String COUNT_BY_C_T =
046                    MBMessageFinder.class.getName() + ".countByC_T";
047    
048            public static final String COUNT_BY_G_U_C_S =
049                    MBMessageFinder.class.getName() + ".countByG_U_C_S";
050    
051            public static final String COUNT_BY_G_U_MD_C_S =
052            MBMessageFinder.class.getName() + ".countByG_U_MD_C_S";
053    
054            public static final String COUNT_BY_G_U_C_A_S =
055                    MBMessageFinder.class.getName() + ".countByG_U_C_A_S";
056    
057            public static final String FIND_BY_NO_ASSETS =
058                    MBMessageFinder.class.getName() + ".findByNoAssets";
059    
060            public static final String FIND_BY_G_U_C_S =
061                    MBMessageFinder.class.getName() + ".findByG_U_C_S";
062    
063            public static final String FIND_BY_G_U_MD_C_S =
064            MBMessageFinder.class.getName() + ".findByG_U_MD_C_S";
065    
066            public static final String FIND_BY_G_U_C_A_S =
067                    MBMessageFinder.class.getName() + ".findByG_U_C_A_S";
068    
069            public int countByC_T(Date createDate, long threadId)
070                    throws SystemException {
071    
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.createSQLQuery(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            public int countByG_U_C_S(
111                            long groupId, long userId, long[] categoryIds, int status)
112                    throws SystemException {
113    
114                    return doCountByG_U_C_S(groupId, userId, categoryIds, status, false);
115            }
116    
117            public int countByG_U_C_A_S(
118                            long groupId, long userId, long[] categoryIds, boolean anonymous,
119                            int status)
120                    throws SystemException {
121    
122                    return doCountByG_U_C_A_S(
123                            groupId, userId, categoryIds, anonymous, status, false);
124            }
125    
126            public int filterCountByG_U_C_S(
127                            long groupId, long userId, long[] categoryIds, int status)
128                    throws SystemException {
129    
130                    return doCountByG_U_C_S(groupId, userId, categoryIds, status, true);
131            }
132    
133            public int filterCountByG_U_MD_C_S(
134                            long groupId, long userId, Date modifiedDate, long[] categoryIds,
135                            int status)
136                    throws SystemException {
137    
138                    return doCountByG_U_MD_C_S(
139                            groupId, userId, modifiedDate, categoryIds, status, true);
140            }
141    
142            public int filterCountByG_U_C_A_S(
143                            long groupId, long userId, long[] categoryIds, boolean anonymous,
144                            int status)
145                    throws SystemException {
146    
147                    return doCountByG_U_C_A_S(
148                            groupId, userId, categoryIds, anonymous, status, true);
149            }
150    
151            public List<Long> filterFindByG_U_C_S(
152                            long groupId, long userId, long[] categoryIds, int status,
153                            int start, int end)
154                    throws SystemException {
155    
156                    return doFindByG_U_C_S(
157                            groupId, userId, categoryIds, status, start, end, true);
158            }
159    
160            public List<Long> filterFindByG_U_MD_C_S(
161                            long groupId, long userId, Date modifiedDate, long[] categoryIds,
162                            int status, int start, int end)
163                    throws SystemException {
164    
165                    return doFindByG_U_MD_C_S(
166                            groupId, userId, modifiedDate, categoryIds, status, start, end,
167                            true);
168            }
169    
170            public List<Long> filterFindByG_U_C_A_S(
171                            long groupId, long userId, long[] categoryIds, boolean anonymous,
172                            int status, int start, int end)
173                    throws SystemException {
174    
175                    return doFindByG_U_C_A_S(
176                            groupId, userId, categoryIds, anonymous, status, start, end, true);
177            }
178    
179            public List<MBMessage> findByNoAssets() throws SystemException {
180                    Session session = null;
181    
182                    try {
183                            session = openSession();
184    
185                            String sql = CustomSQLUtil.get(FIND_BY_NO_ASSETS);
186    
187                            SQLQuery q = session.createSQLQuery(sql);
188    
189                            q.addEntity("MBMessage", MBMessageImpl.class);
190    
191                            return q.list(true);
192                    }
193                    catch (Exception e) {
194                            throw new SystemException(e);
195                    }
196                    finally {
197                            closeSession(session);
198                    }
199            }
200    
201            public List<Long> findByG_U_C_S(
202                            long groupId, long userId, long[] categoryIds, int status,
203                            int start, int end)
204                    throws SystemException {
205    
206                    return doFindByG_U_C_S(
207                            groupId, userId, categoryIds, status, start, end, false);
208            }
209    
210            public List<Long> findByG_U_C_A_S(
211                            long groupId, long userId, long[] categoryIds, boolean anonymous,
212                            int status, int start, int end)
213                    throws SystemException {
214    
215                    return doFindByG_U_C_A_S(
216                            groupId, userId, categoryIds, anonymous, status, start, end, false);
217            }
218    
219            protected int doCountByG_U_C_S(
220                            long groupId, long userId, long[] categoryIds, int status,
221                            boolean inlineSQLHelper)
222                    throws SystemException {
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 ((categoryIds == null) || (categoryIds.length == 0)) {
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.createSQLQuery(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                    throws SystemException {
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 ((categoryIds == null) || (categoryIds.length == 0)) {
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.createSQLQuery(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                    throws SystemException {
377    
378                    Session session = null;
379    
380                    try {
381                            session = openSession();
382    
383                            String sql = CustomSQLUtil.get(COUNT_BY_G_U_C_A_S);
384    
385                            if ((categoryIds == null) || (categoryIds.length == 0)) {
386                                    sql = StringUtil.replace(
387                                            sql, "(currentMessage.categoryId = ?) AND",
388                                            StringPool.BLANK);
389                            }
390                            else {
391                                    sql = StringUtil.replace(
392                                            sql, "currentMessage.categoryId = ?",
393                                            "currentMessage.categoryId = " +
394                                                    StringUtil.merge(
395                                                            categoryIds, " OR currentMessage.categoryId = "));
396                            }
397    
398                            if (status != WorkflowConstants.STATUS_ANY) {
399                                    sql = CustomSQLUtil.appendCriteria(
400                                            sql, "AND (currentMessage.status = ?)");
401                            }
402    
403                            if (inlineSQLHelper) {
404                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
405                                            sql, MBMessage.class.getName(),
406                                            "currentMessage.rootMessageId", groupId);
407                            }
408    
409                            SQLQuery q = session.createSQLQuery(sql);
410    
411                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
412    
413                            QueryPos qPos = QueryPos.getInstance(q);
414    
415                            qPos.add(groupId);
416                            qPos.add(userId);
417                            qPos.add(anonymous);
418    
419                            if (status != WorkflowConstants.STATUS_ANY) {
420                                    qPos.add(status);
421                            }
422    
423                            Iterator<Long> itr = q.iterate();
424    
425                            if (itr.hasNext()) {
426                                    Long count = itr.next();
427    
428                                    if (count != null) {
429                                            return count.intValue();
430                                    }
431                            }
432    
433                            return 0;
434                    }
435                    catch (Exception e) {
436                            throw new SystemException(e);
437                    }
438                    finally {
439                            closeSession(session);
440                    }
441            }
442    
443            protected List<Long> doFindByG_U_C_S(
444                            long groupId, long userId, long[] categoryIds, int status,
445                            int start, int end, boolean inlineSQLHelper)
446                    throws SystemException {
447    
448                    Session session = null;
449    
450                    try {
451                            session = openSession();
452    
453                            String sql = CustomSQLUtil.get(FIND_BY_G_U_C_S);
454    
455                            if (userId <= 0) {
456                                    sql = StringUtil.replace(sql, _USER_ID_SQL, StringPool.BLANK);
457                            }
458    
459                            if ((categoryIds == null) || (categoryIds.length == 0)) {
460                                    sql = StringUtil.replace(
461                                            sql, "(currentMessage.categoryId = ?) AND",
462                                            StringPool.BLANK);
463                            }
464                            else {
465                                    sql = StringUtil.replace(
466                                            sql, "currentMessage.categoryId = ?",
467                                            "currentMessage.categoryId = " +
468                                                    StringUtil.merge(
469                                                            categoryIds, " OR currentMessage.categoryId = "));
470                            }
471    
472                            if (status != WorkflowConstants.STATUS_ANY) {
473                                    sql = CustomSQLUtil.appendCriteria(
474                                            sql, "AND (currentMessage.status = ?)");
475                            }
476    
477                            if (inlineSQLHelper) {
478                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
479                                            sql, MBMessage.class.getName(),
480                                            "currentMessage.rootMessageId", groupId);
481                            }
482    
483                            SQLQuery q = session.createSQLQuery(sql);
484    
485                            q.addScalar("threadId", Type.LONG);
486    
487                            QueryPos qPos = QueryPos.getInstance(q);
488    
489                            qPos.add(groupId);
490    
491                            if (userId > 0) {
492                                    qPos.add(userId);
493                            }
494    
495                            if (status != WorkflowConstants.STATUS_ANY) {
496                                    qPos.add(status);
497                            }
498    
499                            return (List<Long>)QueryUtil.list(q, getDialect(), start, end);
500                    }
501                    catch (Exception e) {
502                            throw new SystemException(e);
503                    }
504                    finally {
505                            closeSession(session);
506                    }
507            }
508    
509            protected List<Long> doFindByG_U_MD_C_S(
510                            long groupId, long userId, Date modifiedDate, long[] categoryIds,
511                            int status, int start, int end, boolean inlineSQLHelper)
512                    throws SystemException {
513    
514                    Session session = null;
515    
516                    try {
517                            session = openSession();
518    
519                            String sql = CustomSQLUtil.get(FIND_BY_G_U_MD_C_S);
520    
521                            if (userId <= 0) {
522                                    sql = StringUtil.replace(sql, _USER_ID_SQL, StringPool.BLANK);
523                            }
524    
525                            if ((categoryIds == null) || (categoryIds.length == 0)) {
526                                    sql = StringUtil.replace(
527                                            sql, "(currentMessage.categoryId = ?) AND",
528                                            StringPool.BLANK);
529                            }
530                            else {
531                                    sql = StringUtil.replace(
532                                            sql, "currentMessage.categoryId = ?",
533                                            "currentMessage.categoryId = " +
534                                                    StringUtil.merge(
535                                                            categoryIds, " OR currentMessage.categoryId = "));
536                            }
537    
538                            if (status != WorkflowConstants.STATUS_ANY) {
539                                    sql = CustomSQLUtil.appendCriteria(
540                                            sql, "AND (currentMessage.status = ?)");
541                            }
542    
543                            if (inlineSQLHelper) {
544                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
545                                            sql, MBMessage.class.getName(),
546                                            "currentMessage.rootMessageId", groupId);
547                            }
548    
549                            SQLQuery q = session.createSQLQuery(sql);
550    
551                            q.addScalar("threadId", Type.LONG);
552    
553                            QueryPos qPos = QueryPos.getInstance(q);
554    
555                            qPos.add(groupId);
556    
557                            if (userId > 0) {
558                                    qPos.add(userId);
559                            }
560    
561                            qPos.add(modifiedDate);
562    
563                            if (status != WorkflowConstants.STATUS_ANY) {
564                                    qPos.add(status);
565                            }
566    
567                            return (List<Long>)QueryUtil.list(q, getDialect(), start, end);
568                    }
569                    catch (Exception e) {
570                            throw new SystemException(e);
571                    }
572                    finally {
573                            closeSession(session);
574                    }
575            }
576    
577            protected List<Long> doFindByG_U_C_A_S(
578                            long groupId, long userId, long[] categoryIds, boolean anonymous,
579                            int status, int start, int end, boolean inlineSQLHelper)
580                    throws SystemException {
581    
582                    Session session = null;
583    
584                    try {
585                            session = openSession();
586    
587                            String sql = CustomSQLUtil.get(FIND_BY_G_U_C_A_S);
588    
589                            if ((categoryIds == null) || (categoryIds.length == 0)) {
590                                    sql = StringUtil.replace(
591                                            sql, "(currentMessage.categoryId = ?) AND",
592                                            StringPool.BLANK);
593                            }
594                            else {
595                                    sql = StringUtil.replace(
596                                            sql, "currentMessage.categoryId = ?",
597                                            "currentMessage.categoryId = " +
598                                                    StringUtil.merge(
599                                                            categoryIds, " OR currentMessage.categoryId = "));
600                            }
601    
602                            if (status != WorkflowConstants.STATUS_ANY) {
603                                    sql = CustomSQLUtil.appendCriteria(
604                                            sql, "AND (currentMessage.status = ?)");
605                            }
606    
607                            if (inlineSQLHelper) {
608                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
609                                            sql, MBMessage.class.getName(),
610                                            "currentMessage.rootMessageId", groupId);
611                            }
612    
613                            SQLQuery q = session.createSQLQuery(sql);
614    
615                            q.addScalar("threadId", Type.LONG);
616    
617                            QueryPos qPos = QueryPos.getInstance(q);
618    
619                            qPos.add(groupId);
620                            qPos.add(userId);
621                            qPos.add(anonymous);
622    
623                            if (status != WorkflowConstants.STATUS_ANY) {
624                                    qPos.add(status);
625                            }
626    
627                            return (List<Long>)QueryUtil.list(q, getDialect(), start, end);
628                    }
629                    catch (Exception e) {
630                            throw new SystemException(e);
631                    }
632                    finally {
633                            closeSession(session);
634                    }
635            }
636    
637            private static final String _USER_ID_SQL =
638                    "AND (currentMessage.userId = ?)";
639    
640    }