1   /**
2    * Copyright (c) 2000-2007 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.service.persistence;
24  
25  import com.liferay.portal.SystemException;
26  import com.liferay.portal.kernel.util.StringMaker;
27  import com.liferay.portal.model.User;
28  import com.liferay.portal.spring.hibernate.CustomSQLUtil;
29  import com.liferay.portal.spring.hibernate.HibernateUtil;
30  import com.liferay.util.dao.hibernate.QueryPos;
31  import com.liferay.util.dao.hibernate.QueryUtil;
32  
33  import java.util.ArrayList;
34  import java.util.Iterator;
35  import java.util.List;
36  
37  import org.hibernate.Hibernate;
38  import org.hibernate.SQLQuery;
39  import org.hibernate.Session;
40  
41  /**
42   * <a href="PermissionUserFinder.java.html"><b><i>View Source</i></b></a>
43   *
44   * @author Charles May
45   *
46   */
47  public class PermissionUserFinder {
48  
49      public static String COUNT_BY_ADMIN_ROLE =
50          PermissionUserFinder.class.getName() + ".countByAdminRole";
51  
52      public static String COUNT_BY_GROUP_PERMISSION =
53          PermissionUserFinder.class.getName() + ".countByGroupPermission";
54  
55      public static String COUNT_BY_GROUP_ROLE =
56          PermissionUserFinder.class.getName() + ".countByGroupRole";
57  
58      public static String COUNT_BY_ORG_GROUP_PERMISSION =
59          PermissionUserFinder.class.getName() + ".countByOrgGroupPermission";
60  
61      public static String COUNT_BY_ORG_GROUP_PERMISSIONS =
62          PermissionUserFinder.class.getName() + ".countByOrgGroupPermissions";
63  
64      public static String COUNT_BY_ORG_PERMISSION =
65          PermissionUserFinder.class.getName() + ".countByOrgPermission";
66  
67      public static String COUNT_BY_ORG_ROLE =
68          PermissionUserFinder.class.getName() + ".countByOrgRole";
69  
70      public static String COUNT_BY_USER_PERMISSION =
71          PermissionUserFinder.class.getName() + ".countByUserPermission";
72  
73      public static String COUNT_BY_USER_ROLE =
74          PermissionUserFinder.class.getName() + ".countByUserRole";
75  
76      public static String FIND_BY_ADMIN_ROLE =
77          PermissionUserFinder.class.getName() + ".findByAdminRole";
78  
79      public static String FIND_BY_GROUP_PERMISSION =
80          PermissionUserFinder.class.getName() + ".findByGroupPermission";
81  
82      public static String FIND_BY_GROUP_ROLE =
83          PermissionUserFinder.class.getName() + ".findByGroupRole";
84  
85      public static String FIND_BY_ORG_GROUP_PERMISSION =
86          PermissionUserFinder.class.getName() + ".findByOrgGroupPermission";
87  
88      public static String FIND_BY_ORG_PERMISSION =
89          PermissionUserFinder.class.getName() + ".findByOrgPermission";
90  
91      public static String FIND_BY_ORG_ROLE =
92          PermissionUserFinder.class.getName() + ".findByOrgRole";
93  
94      public static String FIND_BY_USER_PERMISSION =
95          PermissionUserFinder.class.getName() + ".findByUserPermission";
96  
97      public static String FIND_BY_USER_ROLE =
98          PermissionUserFinder.class.getName() + ".findByUserRole";
99  
100     public static int COUNT_USERS_TYPE_ADMIN = 1;
101 
102     public static int COUNT_USERS_TYPE_PERMISSION = 2;
103 
104     public static int COUNT_USERS_TYPE_ROLE = 3;
105 
106     public static int countByOrgGroupPermissions(
107             long companyId, String name, String primKey, String actionId)
108         throws SystemException {
109 
110         Session session = null;
111 
112         try {
113             session = HibernateUtil.openSession();
114 
115             String sql = CustomSQLUtil.get(COUNT_BY_ORG_GROUP_PERMISSIONS);
116 
117             SQLQuery q = session.createSQLQuery(sql);
118 
119             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
120 
121             QueryPos qPos = QueryPos.getInstance(q);
122 
123             qPos.add(companyId);
124             qPos.add(name);
125             qPos.add(primKey);
126             qPos.add(actionId);
127 
128             Iterator itr = q.list().iterator();
129 
130             if (itr.hasNext()) {
131                 Long count = (Long)itr.next();
132 
133                 if (count != null) {
134                     return count.intValue();
135                 }
136             }
137 
138             return 0;
139         }
140         catch (Exception e) {
141             throw new SystemException(e);
142         }
143         finally {
144             HibernateUtil.closeSession(session);
145         }
146     }
147 
148     public static int countByPermissionAndRole(
149             long companyId, long groupId, String name, String primKey,
150             String actionId, String firstName, String middleName,
151             String lastName, String emailAddress, boolean andOperator)
152         throws SystemException {
153 
154         Session session = null;
155 
156         try {
157             session = HibernateUtil.openSession();
158 
159             int count = countUsers(
160                 session, CustomSQLUtil.get(COUNT_BY_ADMIN_ROLE), companyId,
161                 groupId, name, primKey, actionId, firstName, middleName,
162                 lastName, emailAddress, andOperator, COUNT_USERS_TYPE_ADMIN);
163 
164             count += countUsers(
165                 session, CustomSQLUtil.get(COUNT_BY_USER_PERMISSION), companyId,
166                 groupId, name, primKey, actionId, firstName, middleName,
167                 lastName, emailAddress, andOperator,
168                 COUNT_USERS_TYPE_PERMISSION);
169 
170             count += countUsers(
171                 session, CustomSQLUtil.get(COUNT_BY_GROUP_PERMISSION),
172                 companyId, groupId, name, primKey, actionId, firstName,
173                 middleName, lastName, emailAddress, andOperator,
174                 COUNT_USERS_TYPE_PERMISSION);
175 
176             count += countUsers(
177                 session, CustomSQLUtil.get(COUNT_BY_ORG_PERMISSION), companyId,
178                 groupId, name, primKey, actionId, firstName, middleName,
179                 lastName, emailAddress, andOperator,
180                 COUNT_USERS_TYPE_PERMISSION);
181 
182             count += countUsers(
183                 session, CustomSQLUtil.get(COUNT_BY_USER_ROLE), companyId,
184                 groupId, name, primKey, actionId, firstName, middleName,
185                 lastName, emailAddress, andOperator, COUNT_USERS_TYPE_ROLE);
186 
187             count += countUsers(
188                 session, CustomSQLUtil.get(COUNT_BY_GROUP_ROLE), companyId,
189                 groupId, name, primKey, actionId, firstName, middleName,
190                 lastName, emailAddress, andOperator, COUNT_USERS_TYPE_ROLE);
191 
192             count += countUsers(
193                 session, CustomSQLUtil.get(COUNT_BY_ORG_ROLE), companyId,
194                 groupId, name, primKey, actionId, firstName, middleName,
195                 lastName, emailAddress, andOperator, COUNT_USERS_TYPE_ROLE);
196 
197             return count;
198         }
199         catch (Exception e) {
200             throw new SystemException(e);
201         }
202         finally {
203             HibernateUtil.closeSession(session);
204         }
205     }
206 
207     public static int countByUserAndOrgGroupPermission(
208             long companyId, String name, String primKey, String actionId,
209             String firstName, String middleName, String lastName,
210             String emailAddress, boolean andOperator)
211         throws SystemException {
212 
213         Session session = null;
214 
215         try {
216             session = HibernateUtil.openSession();
217 
218             int count = countUsers(
219                 session, CustomSQLUtil.get(COUNT_BY_ADMIN_ROLE), companyId,
220                 0, name, primKey, actionId, firstName, middleName, lastName,
221                 emailAddress, andOperator, COUNT_USERS_TYPE_ADMIN);
222 
223             count += countUsers(
224                 session, CustomSQLUtil.get(COUNT_BY_USER_PERMISSION), companyId,
225                 0, name, primKey, actionId, firstName, middleName, lastName,
226                 emailAddress, andOperator, COUNT_USERS_TYPE_PERMISSION);
227 
228             count += countUsers(
229                 session, CustomSQLUtil.get(COUNT_BY_ORG_GROUP_PERMISSION),
230                 companyId, 0, name, primKey, actionId, firstName, middleName,
231                 lastName, emailAddress, andOperator,
232                 COUNT_USERS_TYPE_PERMISSION);
233 
234             return count;
235         }
236         catch (Exception e) {
237             throw new SystemException(e);
238         }
239         finally {
240             HibernateUtil.closeSession(session);
241         }
242     }
243 
244     public static int countUsers(
245             Session session, String sql, long companyId, long groupId,
246             String name, String primKey, String actionId, String firstName,
247             String middleName, String lastName, String emailAddress,
248             boolean andOperator, int countUsersType)
249         throws SystemException {
250 
251         sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
252 
253         SQLQuery q = session.createSQLQuery(sql);
254 
255         q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
256 
257         QueryPos qPos = QueryPos.getInstance(q);
258 
259         qPos.add(companyId);
260 
261         if (countUsersType != COUNT_USERS_TYPE_ADMIN) {
262             qPos.add(name);
263 
264             if (countUsersType == COUNT_USERS_TYPE_PERMISSION) {
265                 qPos.add(primKey);
266             }
267             else if (countUsersType == COUNT_USERS_TYPE_ROLE){
268                 qPos.add(companyId);
269                 qPos.add(groupId);
270             }
271 
272             qPos.add(actionId);
273         }
274 
275         qPos.add(firstName);
276         qPos.add(firstName);
277         qPos.add(middleName);
278         qPos.add(middleName);
279         qPos.add(lastName);
280         qPos.add(lastName);
281         qPos.add(emailAddress);
282         qPos.add(emailAddress);
283 
284         Iterator itr = q.list().iterator();
285 
286         if (itr.hasNext()) {
287             Long count = (Long)itr.next();
288 
289             if (count != null) {
290                 return count.intValue();
291             }
292         }
293 
294         return 0;
295     }
296 
297     public static List findByPermissionAndRole(
298             long companyId, long groupId, String name, String primKey,
299             String actionId, String firstName, String middleName,
300             String lastName, String emailAddress, boolean andOperator,
301             int begin, int end)
302         throws SystemException {
303 
304         Session session = null;
305 
306         try {
307             session = HibernateUtil.openSession();
308 
309             StringMaker sm = new StringMaker();
310 
311             sm.append("(");
312             sm.append(CustomSQLUtil.get(FIND_BY_ADMIN_ROLE));
313             sm.append(") UNION (");
314             sm.append(CustomSQLUtil.get(FIND_BY_USER_PERMISSION));
315             sm.append(") UNION (");
316             sm.append(CustomSQLUtil.get(FIND_BY_GROUP_PERMISSION));
317             sm.append(") UNION (");
318             sm.append(CustomSQLUtil.get(FIND_BY_ORG_PERMISSION));
319             sm.append(") UNION (");
320             sm.append(CustomSQLUtil.get(FIND_BY_USER_ROLE));
321             sm.append(") UNION (");
322             sm.append(CustomSQLUtil.get(FIND_BY_GROUP_ROLE));
323             sm.append(") UNION (");
324             sm.append(CustomSQLUtil.get(FIND_BY_ORG_ROLE));
325             sm.append(") ");
326             sm.append("ORDER BY lastName ASC, firstName ASC, middleName ASC ");
327 
328             String sql = sm.toString();
329 
330             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
331 
332             SQLQuery q = session.createSQLQuery(sql);
333 
334             q.addScalar("userId", Hibernate.LONG);
335 
336             QueryPos qPos = QueryPos.getInstance(q);
337 
338             for (int i = 0; i < 7; i++) {
339                 qPos.add(companyId);
340 
341                 if (i > 0) {
342                     qPos.add(name);
343 
344                     if (i < 4) {
345                         qPos.add(primKey);
346                     }
347                     else {
348                         qPos.add(companyId);
349                         qPos.add(groupId);
350                     }
351 
352                     qPos.add(actionId);
353                 }
354 
355                 qPos.add(firstName);
356                 qPos.add(firstName);
357                 qPos.add(middleName);
358                 qPos.add(middleName);
359                 qPos.add(lastName);
360                 qPos.add(lastName);
361                 qPos.add(emailAddress);
362                 qPos.add(emailAddress);
363             }
364 
365             List list = new ArrayList();
366 
367             Iterator itr = QueryUtil.iterate(
368                 q, HibernateUtil.getDialect(), begin, end);
369 
370             while (itr.hasNext()) {
371                 Long userIdObj = (Long)itr.next();
372 
373                 User user = UserUtil.findByPrimaryKey(userIdObj.longValue());
374 
375                 list.add(user);
376             }
377 
378             return list;
379         }
380         catch (Exception e) {
381             throw new SystemException(e);
382         }
383         finally {
384             HibernateUtil.closeSession(session);
385         }
386     }
387 
388     public static List findByUserAndOrgGroupPermission(
389             long companyId, String name, String primKey, String actionId,
390             String firstName, String middleName, String lastName,
391             String emailAddress, boolean andOperator, int begin, int end)
392         throws SystemException {
393 
394         Session session = null;
395 
396         try {
397             session = HibernateUtil.openSession();
398 
399             StringMaker sm = new StringMaker();
400 
401             sm.append("(");
402             sm.append(CustomSQLUtil.get(FIND_BY_ADMIN_ROLE));
403             sm.append(") UNION (");
404             sm.append(CustomSQLUtil.get(FIND_BY_USER_PERMISSION));
405             sm.append(") UNION (");
406             sm.append(CustomSQLUtil.get(FIND_BY_ORG_GROUP_PERMISSION));
407             sm.append(") ");
408             sm.append("ORDER BY lastName ASC, firstName ASC, middleName ASC ");
409 
410             String sql = sm.toString();
411 
412             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
413 
414             SQLQuery q = session.createSQLQuery(sql);
415 
416             q.addScalar("userId", Hibernate.LONG);
417 
418             QueryPos qPos = QueryPos.getInstance(q);
419 
420             for (int i = 0; i < 3; i++) {
421                 qPos.add(companyId);
422 
423                 if (i > 0) {
424                     qPos.add(name);
425                     qPos.add(primKey);
426                     qPos.add(actionId);
427                 }
428 
429                 qPos.add(firstName);
430                 qPos.add(firstName);
431                 qPos.add(middleName);
432                 qPos.add(middleName);
433                 qPos.add(lastName);
434                 qPos.add(lastName);
435                 qPos.add(emailAddress);
436                 qPos.add(emailAddress);
437             }
438 
439             List list = new ArrayList();
440 
441             Iterator itr = QueryUtil.iterate(
442                 q, HibernateUtil.getDialect(), begin, end);
443 
444             while (itr.hasNext()) {
445                 Long userIdObj = (Long)itr.next();
446 
447                 User user = UserUtil.findByPrimaryKey(userIdObj.longValue());
448 
449                 list.add(user);
450             }
451 
452             return list;
453         }
454         catch (Exception e) {
455             throw new SystemException(e);
456         }
457         finally {
458             HibernateUtil.closeSession(session);
459         }
460     }
461 
462 }