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.documentlibrary.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.StringBundler;
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.documentlibrary.model.DLFileEntry;
031    import com.liferay.portlet.documentlibrary.model.DLFileShortcut;
032    import com.liferay.portlet.documentlibrary.model.DLFolder;
033    import com.liferay.util.dao.orm.CustomSQLUtil;
034    
035    import java.util.ArrayList;
036    import java.util.Iterator;
037    import java.util.List;
038    
039    /**
040     * @author Brian Wing Shun Chan
041     * @author Shuyang Zhou
042     */
043    public class DLFolderFinderImpl
044            extends BasePersistenceImpl<DLFolder> implements DLFolderFinder {
045    
046            public static final String COUNT_F_BY_G_M_F =
047                    DLFolderFinder.class.getName() + ".countF_ByG_M_F";
048    
049            public static final String COUNT_F_BY_G_M_S_F =
050                    DLFolderFinder.class.getName() + ".countF_ByG_M_S_F";
051    
052            public static final String COUNT_FE_BY_G_F =
053                    DLFolderFinder.class.getName() + ".countFE_ByG_F";
054    
055            public static final String COUNT_FE_BY_G_F_S =
056                    DLFolderFinder.class.getName() + ".countFE_ByG_F_S";
057    
058            public static final String COUNT_FS_BY_G_F_A_S =
059                    DLFolderFinder.class.getName() + ".countFS_ByG_F_A_S";
060    
061            public static final String FIND_F_BY_G_M_F =
062                    DLFolderFinder.class.getName() + ".findF_ByG_M_F";
063    
064            public static final String FIND_F_BY_G_M_S_F =
065                    DLFolderFinder.class.getName() + ".findF_ByG_M_S_F";
066    
067            public static final String FIND_FE_BY_G_F =
068                    DLFolderFinder.class.getName() + ".findFE_ByG_F";
069    
070            public static final String FIND_FE_BY_G_F_S =
071                    DLFolderFinder.class.getName() + ".findFE_ByG_F_S";
072    
073            public static final String FIND_FS_BY_G_F_A_S =
074                    DLFolderFinder.class.getName() + ".findFS_ByG_F_A_S";
075    
076            public static final String JOIN_FS_BY_DL_FILE_ENTRY =
077                    DLFolderFinder.class.getName() + ".joinFS_ByDLFileEntry";
078    
079            public static final String JOIN_FV_BY_DL_FILE_ENTRY =
080                    DLFolderFinder.class.getName() + ".joinFV_ByDLFileEntry";
081    
082            public int countF_FE_FS_ByG_F_M_M(
083                            long groupId, long folderId, String[] mimeTypes,
084                            boolean includeMountFolders, QueryDefinition queryDefinition)
085                    throws SystemException {
086    
087                    return doCountF_FE_FS_ByG_F_M_M(
088                            groupId, folderId, mimeTypes, includeMountFolders, queryDefinition,
089                            false);
090            }
091    
092            public int countFE_ByG_F(
093                            long groupId, long folderId, QueryDefinition queryDefinition)
094                    throws SystemException {
095    
096                    return doCountFE_ByG_F(groupId, folderId, queryDefinition, false);
097            }
098    
099            public int countFE_FS_ByG_F(
100                            long groupId, long folderId, QueryDefinition queryDefinition)
101                    throws SystemException {
102    
103                    return doCountFE_FS_ByG_F_M(
104                            groupId, folderId, null, queryDefinition, false);
105            }
106    
107            public int filterCountF_FE_FS_ByG_F_M_M(
108                            long groupId, long folderId, String[] mimeTypes,
109                            boolean includeMountFolders, QueryDefinition queryDefinition)
110                    throws SystemException {
111    
112                    return doCountF_FE_FS_ByG_F_M_M(
113                            groupId, folderId, mimeTypes, includeMountFolders, queryDefinition,
114                            true);
115            }
116    
117            public int filterCountFE_ByG_F(
118                            long groupId, long folderId, QueryDefinition queryDefinition)
119                    throws SystemException {
120    
121                    return doCountFE_ByG_F(groupId, folderId, queryDefinition, true);
122            }
123    
124            public int filterCountFE_FS_ByG_F(
125                            long groupId, long folderId, QueryDefinition queryDefinition)
126                    throws SystemException {
127    
128                    return doCountFE_FS_ByG_F_M(
129                            groupId, folderId, null, queryDefinition, true);
130            }
131    
132            public int filterCountFE_FS_ByG_F_M(
133                            long groupId, long folderId, String[] mimeTypes,
134                            QueryDefinition queryDefinition)
135                    throws SystemException {
136    
137                    return doCountFE_FS_ByG_F_M(
138                            groupId, folderId, mimeTypes, queryDefinition, true);
139            }
140    
141            public List<Object> filterFindF_FE_FS_ByG_F_M_M(
142                            long groupId, long folderId, String[] mimeTypes,
143                            boolean includeMountFolders, QueryDefinition queryDefinition)
144                    throws SystemException {
145    
146                    return doFindF_FE_FS_ByG_F_M_M(
147                            groupId, folderId, mimeTypes, includeMountFolders, queryDefinition,
148                            true);
149            }
150    
151            public List<Object> filterFindFE_FS_ByG_F(
152                            long groupId, long folderId, QueryDefinition queryDefinition)
153                    throws SystemException {
154    
155                    return doFindFE_FS_ByG_F(groupId, folderId, queryDefinition, true);
156            }
157    
158            public List<Object> findF_FE_FS_ByG_F_M_M(
159                            long groupId, long folderId, String[] mimeTypes,
160                            boolean includeMountFolders, QueryDefinition queryDefinition)
161                    throws SystemException {
162    
163                    return doFindF_FE_FS_ByG_F_M_M(
164                            groupId, folderId, mimeTypes, includeMountFolders, queryDefinition,
165                            false);
166            }
167    
168            public List<Object> findFE_FS_ByG_F(
169                            long groupId, long folderId, QueryDefinition queryDefinition)
170                    throws SystemException {
171    
172                    return doFindFE_FS_ByG_F(groupId, folderId, queryDefinition, false);
173            }
174    
175            protected int doCountF_FE_FS_ByG_F_M_M(
176                            long groupId, long folderId, String[] mimeTypes,
177                            boolean includeMountFolders, QueryDefinition queryDefinition,
178                            boolean inlineSQLHelper)
179                    throws SystemException {
180    
181                    Session session = null;
182    
183                    try {
184                            session = openSession();
185    
186                            StringBundler sb = new StringBundler(7);
187    
188                            sb.append(StringPool.OPEN_PARENTHESIS);
189    
190                            String sql = null;
191    
192                            if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
193                                    sql = CustomSQLUtil.get(COUNT_F_BY_G_M_F);
194                            }
195                            else {
196                                    sql = CustomSQLUtil.get(COUNT_F_BY_G_M_S_F);
197    
198                                    sql = replaceExcludeStatus(sql, queryDefinition);
199                            }
200    
201                            if (inlineSQLHelper) {
202                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
203                                            sql, DLFolder.class.getName(), "DLFolder.folderId",
204                                            groupId);
205                            }
206    
207                            sb.append(sql);
208                            sb.append(") UNION ALL (");
209                            sb.append(
210                                    getFileEntriesSQL(
211                                            groupId, mimeTypes, queryDefinition, inlineSQLHelper));
212                            sb.append(") UNION ALL (");
213                            sb.append(
214                                    getFileShortcutsSQL(
215                                            groupId, mimeTypes, queryDefinition, inlineSQLHelper));
216                            sb.append(StringPool.CLOSE_PARENTHESIS);
217    
218                            sql = sb.toString();
219    
220                            sql = updateSQL(
221                                    sql, folderId, queryDefinition.getStatus(),
222                                    includeMountFolders);
223    
224                            SQLQuery q = session.createSQLQuery(sql);
225    
226                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
227    
228                            QueryPos qPos = QueryPos.getInstance(q);
229    
230                            qPos.add(groupId);
231    
232                            if (!includeMountFolders) {
233                                    qPos.add(false);
234                            }
235    
236                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
237                                    qPos.add(queryDefinition.getStatus());
238                            }
239    
240                            qPos.add(folderId);
241                            qPos.add(groupId);
242    
243                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
244                                    qPos.add(queryDefinition.getStatus());
245                            }
246    
247                            qPos.add(folderId);
248    
249                            if (mimeTypes != null) {
250                                    qPos.add(mimeTypes);
251                            }
252    
253                            qPos.add(groupId);
254                            qPos.add(true);
255    
256                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
257                                    qPos.add(queryDefinition.getStatus());
258                            }
259                            else {
260                                    qPos.add(WorkflowConstants.STATUS_APPROVED);
261                            }
262    
263                            qPos.add(folderId);
264    
265                            if (mimeTypes != null) {
266                                    qPos.add(mimeTypes);
267                            }
268    
269                            int count = 0;
270    
271                            Iterator<Long> itr = q.iterate();
272    
273                            while (itr.hasNext()) {
274                                    Long l = itr.next();
275    
276                                    if (l != null) {
277                                            count += l.intValue();
278                                    }
279                            }
280    
281                            return count;
282                    }
283                    catch (Exception e) {
284                            throw new SystemException(e);
285                    }
286                    finally {
287                            closeSession(session);
288                    }
289            }
290    
291            protected int doCountFE_ByG_F(
292                            long groupId, long folderId, QueryDefinition queryDefinition,
293                            boolean inlineSQLHelper)
294                    throws SystemException {
295    
296                    Session session = null;
297    
298                    try {
299                            session = openSession();
300    
301                            String sql = CustomSQLUtil.get(COUNT_FE_BY_G_F_S);
302    
303                            sql = replaceExcludeStatus(sql, queryDefinition);
304    
305                            if (inlineSQLHelper && InlineSQLHelperUtil.isEnabled(groupId)) {
306                                    sql = StringUtil.replace(
307                                            sql, "[$JOIN$]",
308                                            CustomSQLUtil.get(JOIN_FV_BY_DL_FILE_ENTRY));
309    
310                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
311                                            sql, DLFileEntry.class.getName(), "DLFileEntry.fileEntryId",
312                                            groupId);
313                            }
314                            else {
315                                    sql = StringUtil.replace(sql, "[$JOIN$]", StringPool.BLANK);
316                            }
317    
318                            sql = StringUtil.replace(
319                                    sql, "[$FILE_ENTRY_FOLDER_ID$]",
320                                    getFolderId(folderId, "DLFileVersion"));
321    
322                            SQLQuery q = session.createSQLQuery(sql);
323    
324                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
325    
326                            QueryPos qPos = QueryPos.getInstance(q);
327    
328                            qPos.add(groupId);
329                            qPos.add(queryDefinition.getStatus());
330                            qPos.add(folderId);
331    
332                            Iterator<Long> itr = q.iterate();
333    
334                            if (itr.hasNext()) {
335                                    Long count = itr.next();
336    
337                                    if (count != null) {
338                                            return count.intValue();
339                                    }
340                            }
341    
342                            return 0;
343                    }
344                    catch (Exception e) {
345                            throw new SystemException(e);
346                    }
347                    finally {
348                            closeSession(session);
349                    }
350            }
351    
352            protected int doCountFE_FS_ByG_F_M(
353                            long groupId, long folderId, String[] mimeTypes,
354                            QueryDefinition queryDefinition, boolean inlineSQLHelper)
355                    throws SystemException {
356    
357                    Session session = null;
358    
359                    try {
360                            session = openSession();
361    
362                            StringBundler sb = new StringBundler(7);
363    
364                            sb.append(StringPool.OPEN_PARENTHESIS);
365    
366                            String sql = getFileEntriesSQL(
367                                    groupId, mimeTypes, queryDefinition, inlineSQLHelper);
368    
369                            sb.append(sql);
370                            sb.append(") UNION ALL (");
371                            sb.append(
372                                    getFileShortcutsSQL(
373                                            groupId, mimeTypes, queryDefinition, inlineSQLHelper));
374                            sb.append(StringPool.CLOSE_PARENTHESIS);
375    
376                            sql = sb.toString();
377    
378                            sql = updateSQL(sql, folderId, queryDefinition.getStatus(), false);
379    
380                            SQLQuery q = session.createSQLQuery(sql);
381    
382                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
383    
384                            QueryPos qPos = QueryPos.getInstance(q);
385    
386                            qPos.add(groupId);
387    
388                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
389                                    qPos.add(queryDefinition.getStatus());
390                            }
391    
392                            qPos.add(folderId);
393    
394                            if (mimeTypes != null) {
395                                    qPos.add(mimeTypes);
396                            }
397    
398                            qPos.add(groupId);
399                            qPos.add(true);
400    
401                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
402                                    qPos.add(queryDefinition.getStatus());
403                            }
404                            else {
405                                    qPos.add(WorkflowConstants.STATUS_APPROVED);
406                            }
407    
408                            qPos.add(folderId);
409    
410                            if (mimeTypes != null) {
411                                    qPos.add(mimeTypes);
412                            }
413    
414                            int count = 0;
415    
416                            Iterator<Long> itr = q.iterate();
417    
418                            while (itr.hasNext()) {
419                                    Long l = itr.next();
420    
421                                    if (l != null) {
422                                            count += l.intValue();
423                                    }
424                            }
425    
426                            return count;
427                    }
428                    catch (Exception e) {
429                            throw new SystemException(e);
430                    }
431                    finally {
432                            closeSession(session);
433                    }
434            }
435    
436            protected List<Object> doFindF_FE_FS_ByG_F_M_M(
437                            long groupId, long folderId, String[] mimeTypes,
438                            boolean includeMountFolders, QueryDefinition queryDefinition,
439                            boolean inlineSQLHelper)
440                    throws SystemException {
441    
442                    Session session = null;
443    
444                    try {
445                            session = openSession();
446    
447                            StringBundler sb = new StringBundler(7);
448    
449                            sb.append("SELECT * FROM (");
450    
451                            String sql = null;
452    
453                            if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
454                                    sql = CustomSQLUtil.get(FIND_F_BY_G_M_F);
455                            }
456                            else {
457                                    sql = CustomSQLUtil.get(FIND_F_BY_G_M_S_F);
458    
459                                    sql = replaceExcludeStatus(sql, queryDefinition);
460                            }
461    
462                            if (inlineSQLHelper) {
463                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
464                                            sql, DLFolder.class.getName(), "DLFolder.folderId",
465                                            groupId);
466                            }
467    
468                            sb.append(sql);
469                            sb.append(" UNION ALL ");
470    
471                            if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
472                                    sql = CustomSQLUtil.get(FIND_FE_BY_G_F);
473                            }
474                            else {
475                                    sql = CustomSQLUtil.get(FIND_FE_BY_G_F_S);
476    
477                                    sql = replaceExcludeStatus(sql, queryDefinition);
478                            }
479    
480                            if (inlineSQLHelper) {
481                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
482                                            sql, DLFileEntry.class.getName(), "DLFileEntry.fileEntryId",
483                                            groupId);
484                            }
485    
486                            sb.append(sql);
487    
488                            if ((mimeTypes != null) && (mimeTypes.length > 0)) {
489                                    for (int i = 0; i < mimeTypes.length; i++) {
490                                            if (i == 0) {
491                                                    sb.append(" AND (");
492                                            }
493                                            else {
494                                                    sb.append(" OR");
495                                            }
496    
497                                            sb.append(" DLFileEntry.mimeType = ?");
498                                    }
499    
500                                    sb.append(StringPool.CLOSE_PARENTHESIS);
501                            }
502    
503                            sb.append(" UNION ALL ");
504    
505                            sql = CustomSQLUtil.get(FIND_FS_BY_G_F_A_S);
506    
507                            sql = replaceExcludeStatus(sql, queryDefinition);
508    
509                            if (inlineSQLHelper) {
510                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
511                                            sql, DLFileShortcut.class.getName(),
512                                            "DLFileShortcut.fileShortcutId", groupId);
513                            }
514    
515                            sb.append(sql);
516    
517                            if ((mimeTypes != null) && (mimeTypes.length > 0)) {
518                                    for (int i = 0; i < mimeTypes.length; i++) {
519                                            if (i == 0) {
520                                                    sb.append(" AND (");
521                                            }
522                                            else {
523                                                    sb.append(" OR");
524                                            }
525    
526                                            sb.append(" mimeType = ?");
527                                    }
528    
529                                    sb.append(StringPool.CLOSE_PARENTHESIS);
530                            }
531    
532                            sb.append(") TEMP_TABLE ORDER BY modelFolder DESC, title ASC");
533    
534                            sql = sb.toString();
535    
536                            if (includeMountFolders) {
537                                    sql = StringUtil.replace(
538                                            sql, "(DLFolder.mountPoint = ?) AND", StringPool.BLANK);
539                            }
540    
541                            sql = StringUtil.replace(
542                                    sql, "[$FOLDER_PARENT_FOLDER_ID$]",
543                                    getFolderId(folderId, "DLFolder"));
544                            sql = StringUtil.replace(
545                                    sql, "[$FILE_ENTRY_FOLDER_ID$]",
546                                    getFolderId(folderId, "DLFileEntry"));
547                            sql = StringUtil.replace(
548                                    sql, "[$FILE_SHORTCUT_FOLDER_ID$]",
549                                    getFolderId(folderId, "DLFileShortcut"));
550                            sql = CustomSQLUtil.replaceOrderBy(
551                                    sql, queryDefinition.getOrderByComparator());
552    
553                            SQLQuery q = session.createSQLQuery(sql);
554    
555                            q.addScalar("modelFolderId", Type.LONG);
556                            q.addScalar("name", Type.STRING);
557                            q.addScalar("title", Type.STRING);
558                            q.addScalar("fileShortcutId", Type.LONG);
559                            q.addScalar("modelFolder", Type.LONG);
560    
561                            QueryPos qPos = QueryPos.getInstance(q);
562    
563                            qPos.add(groupId);
564    
565                            if (!includeMountFolders) {
566                                    qPos.add(false);
567                            }
568    
569                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
570                                    qPos.add(queryDefinition.getStatus());
571                            }
572    
573                            qPos.add(folderId);
574                            qPos.add(groupId);
575    
576                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
577                                    qPos.add(queryDefinition.getStatus());
578                            }
579    
580                            qPos.add(folderId);
581    
582                            if (mimeTypes != null) {
583                                    qPos.add(mimeTypes);
584                            }
585    
586                            qPos.add(groupId);
587                            qPos.add(true);
588    
589                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
590                                    qPos.add(queryDefinition.getStatus());
591                            }
592                            else {
593                                    qPos.add(WorkflowConstants.STATUS_APPROVED);
594                            }
595    
596                            qPos.add(folderId);
597    
598                            if (mimeTypes != null) {
599                                    qPos.add(mimeTypes);
600                            }
601    
602                            List<Object> models = new ArrayList<Object>();
603    
604                            Iterator<Object[]> itr = (Iterator<Object[]>)QueryUtil.iterate(
605                                    q, getDialect(), queryDefinition.getStart(),
606                                    queryDefinition.getEnd());
607    
608                            while (itr.hasNext()) {
609                                    Object[] array = itr.next();
610    
611                                    long curFolderId = (Long)array[0];
612                                    String name = (String)array[1];
613                                    //String title = (String)array[2];
614                                    long fileShortcutId = (Long)array[3];
615                                    long modelFolder = (Long)array[4];
616    
617                                    Object obj = null;
618    
619                                    if (modelFolder == 1) {
620                                            obj = DLFolderUtil.findByPrimaryKey(curFolderId);
621                                    }
622                                    else if (fileShortcutId > 0) {
623                                            obj = DLFileShortcutUtil.findByPrimaryKey(fileShortcutId);
624                                    }
625                                    else {
626                                            obj = DLFileEntryUtil.findByG_F_N(
627                                                    groupId, curFolderId, name);
628                                    }
629    
630                                    models.add(obj);
631                            }
632    
633                            return models;
634                    }
635                    catch (Exception e) {
636                            throw new SystemException(e);
637                    }
638                    finally {
639                            closeSession(session);
640                    }
641            }
642    
643            protected List<Object> doFindFE_FS_ByG_F(
644                            long groupId, long folderId, QueryDefinition queryDefinition,
645                            boolean inlineSQLHelper)
646                    throws SystemException {
647    
648                    Session session = null;
649    
650                    try {
651                            session = openSession();
652    
653                            StringBundler sb = new StringBundler(5);
654    
655                            sb.append("SELECT * FROM (");
656    
657                            String sql = null;
658    
659                            if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
660                                    sql = CustomSQLUtil.get(FIND_FE_BY_G_F);
661                            }
662                            else {
663                                    sql = CustomSQLUtil.get(FIND_FE_BY_G_F_S);
664    
665                                    sql = replaceExcludeStatus(sql, queryDefinition);
666                            }
667    
668                            if (inlineSQLHelper) {
669                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
670                                            sql, DLFileEntry.class.getName(), "DLFileEntry.fileEntryId",
671                                            groupId);
672                            }
673    
674                            sb.append(sql);
675                            sb.append(" UNION ALL ");
676    
677                            sql = CustomSQLUtil.get(FIND_FS_BY_G_F_A_S);
678    
679                            sql = replaceExcludeStatus(sql, queryDefinition);
680    
681                            if (inlineSQLHelper) {
682                                    sql = InlineSQLHelperUtil.replacePermissionCheck(
683                                            sql, DLFileShortcut.class.getName(),
684                                            "DLFileShortcut.fileShortcutId", groupId);
685                            }
686    
687                            sb.append(sql);
688                            sb.append(") TEMP_TABLE ORDER BY modelFolder DESC, title ASC");
689    
690                            sql = sb.toString();
691    
692                            sql = StringUtil.replace(
693                                    sql, "[$FILE_ENTRY_FOLDER_ID$]",
694                                    getFolderId(folderId, "DLFileEntry"));
695                            sql = StringUtil.replace(
696                                    sql, "[$FILE_SHORTCUT_FOLDER_ID$]",
697                                    getFolderId(folderId, "DLFileShortcut"));
698    
699                            SQLQuery q = session.createSQLQuery(sql);
700    
701                            q.addScalar("modelFolderId", Type.LONG);
702                            q.addScalar("name", Type.STRING);
703                            q.addScalar("title", Type.STRING);
704                            q.addScalar("fileShortcutId", Type.LONG);
705    
706                            QueryPos qPos = QueryPos.getInstance(q);
707    
708                            qPos.add(groupId);
709    
710                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
711                                    qPos.add(queryDefinition.getStatus());
712                            }
713    
714                            qPos.add(folderId);
715                            qPos.add(groupId);
716                            qPos.add(true);
717    
718                            if (queryDefinition.getStatus() != WorkflowConstants.STATUS_ANY) {
719                                    qPos.add(queryDefinition.getStatus());
720                            }
721                            else {
722                                    qPos.add(WorkflowConstants.STATUS_APPROVED);
723                            }
724    
725                            qPos.add(folderId);
726    
727                            List<Object> models = new ArrayList<Object>();
728    
729                            Iterator<Object[]> itr = (Iterator<Object[]>)QueryUtil.iterate(
730                                    q, getDialect(), queryDefinition.getStart(),
731                                    queryDefinition.getEnd());
732    
733                            while (itr.hasNext()) {
734                                    Object[] array = itr.next();
735    
736                                    long folderId2 = (Long)array[0];
737                                    String name = (String)array[1];
738                                    //String title = (String)array[2];
739                                    long fileShortcutId = (Long)array[3];
740    
741                                    Object obj = null;
742    
743                                    if (fileShortcutId > 0) {
744                                            obj = DLFileShortcutUtil.findByPrimaryKey(fileShortcutId);
745                                    }
746                                    else {
747                                            obj = DLFileEntryUtil.findByG_F_N(groupId, folderId2, name);
748                                    }
749    
750                                    models.add(obj);
751                            }
752    
753                            return models;
754                    }
755                    catch (Exception e) {
756                            throw new SystemException(e);
757                    }
758                    finally {
759                            closeSession(session);
760                    }
761            }
762    
763            protected String getFileEntriesSQL(
764                    long groupId, String[] mimeTypes, QueryDefinition queryDefinition,
765                    boolean inlineSQLHelper) {
766    
767                    StringBundler sb = new StringBundler();
768    
769                    String sql = null;
770    
771                    if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
772                            sql = CustomSQLUtil.get(COUNT_FE_BY_G_F);
773                    }
774                    else {
775                            sql = CustomSQLUtil.get(COUNT_FE_BY_G_F_S);
776    
777                            sql = replaceExcludeStatus(sql, queryDefinition);
778    
779                            if ((inlineSQLHelper &&
780                                     InlineSQLHelperUtil.isEnabled(groupId)) ||
781                                    ((mimeTypes != null) && (mimeTypes.length > 0))) {
782    
783                                    sql = StringUtil.replace(
784                                            sql, "[$JOIN$]",
785                                            CustomSQLUtil.get(JOIN_FV_BY_DL_FILE_ENTRY));
786                            }
787                            else {
788                                    sql = StringUtil.replace(sql, "[$JOIN$]", StringPool.BLANK);
789                            }
790                    }
791    
792                    if (inlineSQLHelper) {
793                            sql = InlineSQLHelperUtil.replacePermissionCheck(
794                                    sql, DLFileEntry.class.getName(), "DLFileEntry.fileEntryId",
795                                    groupId);
796                    }
797    
798                    sb.append(sql);
799    
800                    if ((mimeTypes != null) && (mimeTypes.length > 0)) {
801                            sb.append(WHERE_AND);
802                            sb.append(StringPool.OPEN_PARENTHESIS);
803                            sb.append(getMimeTypes(mimeTypes, "DLFileEntry"));
804                            sb.append(StringPool.CLOSE_PARENTHESIS);
805                    }
806    
807                    return sb.toString();
808            }
809    
810            protected String getFileShortcutsSQL(
811                    long groupId, String[] mimeTypes, QueryDefinition queryDefinition,
812                    boolean inlineSQLHelper) {
813    
814                    String sql = CustomSQLUtil.get(COUNT_FS_BY_G_F_A_S);
815    
816                    sql = replaceExcludeStatus(sql, queryDefinition);
817    
818                    if ((inlineSQLHelper && InlineSQLHelperUtil.isEnabled(groupId)) ||
819                            ((mimeTypes != null) && (mimeTypes.length > 0))) {
820    
821                            sql = StringUtil.replace(
822                                    sql, "[$JOIN$]", CustomSQLUtil.get(JOIN_FS_BY_DL_FILE_ENTRY));
823                    }
824                    else {
825                            sql = StringUtil.replace(sql, "[$JOIN$]", StringPool.BLANK);
826                    }
827    
828                    if (inlineSQLHelper) {
829                            sql = InlineSQLHelperUtil.replacePermissionCheck(
830                                    sql, DLFileShortcut.class.getName(),
831                                    "DLFileShortcut.fileShortcutId", groupId);
832                    }
833    
834                    StringBundler sb = new StringBundler(sql);
835    
836                    if ((mimeTypes != null) && (mimeTypes.length > 0)) {
837                            sb.append(WHERE_AND);
838                            sb.append(StringPool.OPEN_PARENTHESIS);
839                            sb.append(getMimeTypes(mimeTypes, "DLFileEntry"));
840                            sb.append(StringPool.CLOSE_PARENTHESIS);
841                    }
842    
843                    return sb.toString();
844            }
845    
846            protected String getFolderId(long folderId, String table) {
847                    StringBundler sb = new StringBundler(4);
848    
849                    sb.append(table);
850                    sb.append(".");
851    
852                    if (table.equals("DLFolder")) {
853                            sb.append("parentFolderId");
854                    }
855                    else {
856                            sb.append("folderId");
857                    }
858    
859                    sb.append("= ? ");
860    
861                    return sb.toString();
862            }
863    
864            protected String getMimeTypes(String[] mimeTypes, String table) {
865                    if (mimeTypes.length == 0) {
866                            return StringPool.BLANK;
867                    }
868    
869                    StringBundler sb = new StringBundler(mimeTypes.length * 2 - 1);
870    
871                    for (int i = 0; i < mimeTypes.length; i++) {
872                            sb.append(table);
873                            sb.append(".mimeType = ?");
874    
875                            if ((i + 1) != mimeTypes.length) {
876                                    sb.append(WHERE_OR);
877                            }
878                    }
879    
880                    return sb.toString();
881            }
882    
883            protected String replaceExcludeStatus(
884                    String sql, QueryDefinition queryDefinition) {
885    
886                    if (queryDefinition.isExcludeStatus()) {
887                            sql = StringUtil.replace(sql, ".status = ?)", ".status != ?)");
888                    }
889    
890                    return sql;
891            }
892    
893            protected String updateSQL(
894                    String sql, long folderId, int status, boolean includeMountFolders) {
895    
896                    if (includeMountFolders) {
897                            sql = StringUtil.replace(
898                                    sql, "(DLFolder.mountPoint = ?) AND", StringPool.BLANK);
899                    }
900    
901                    sql = StringUtil.replace(
902                            sql, "[$FOLDER_PARENT_FOLDER_ID$]",
903                            getFolderId(folderId, "DLFolder"));
904    
905                    if (status == WorkflowConstants.STATUS_ANY) {
906                            sql = StringUtil.replace(
907                                    sql, "[$FILE_ENTRY_FOLDER_ID$]",
908                                    getFolderId(folderId, "DLFileEntry"));
909                    }
910                    else {
911                            sql = StringUtil.replace(
912                                    sql, "[$FILE_ENTRY_FOLDER_ID$]",
913                                    getFolderId(folderId, "DLFileVersion"));
914                    }
915    
916                    return StringUtil.replace(
917                            sql, "[$FILE_SHORTCUT_FOLDER_ID$]",
918                            getFolderId(folderId, "DLFileShortcut"));
919            }
920    
921    }