1   /**
2    * Copyright (c) 2000-2008 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.OrderByComparator;
27  import com.liferay.portal.kernel.util.StringMaker;
28  import com.liferay.portal.kernel.util.StringPool;
29  import com.liferay.portal.kernel.util.StringUtil;
30  import com.liferay.portal.kernel.util.Validator;
31  import com.liferay.portal.model.User;
32  import com.liferay.portal.model.impl.UserImpl;
33  import com.liferay.portal.spring.hibernate.CustomSQLUtil;
34  import com.liferay.portal.spring.hibernate.HibernateUtil;
35  import com.liferay.util.dao.hibernate.QueryPos;
36  import com.liferay.util.dao.hibernate.QueryUtil;
37  
38  import java.util.Iterator;
39  import java.util.LinkedHashMap;
40  import java.util.List;
41  import java.util.Map;
42  
43  import org.hibernate.Hibernate;
44  import org.hibernate.SQLQuery;
45  import org.hibernate.Session;
46  
47  /**
48   * <a href="UserFinderImpl.java.html"><b><i>View Source</i></b></a>
49   *
50   * @author Brian Wing Shun Chan
51   * @author Jon Steer
52   * @author Raymond Augé
53   *
54   */
55  public class UserFinderImpl implements UserFinder {
56  
57      public static String COUNT_BY_C_FN_MN_LN_SN_EA_A =
58          UserFinder.class.getName() + ".countByC_FN_MN_LN_SN_EA_A";
59  
60      public static String FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES =
61          UserFinder.class.getName() + ".findByNoAnnouncementsDeliveries";
62  
63      public static String FIND_BY_C_FN_MN_LN_SN_EA_A =
64          UserFinder.class.getName() + ".findByC_FN_MN_LN_SN_EA_A";
65  
66      public static String JOIN_BY_PERMISSION =
67          UserFinder.class.getName() + ".joinByPermission";
68  
69      public static String JOIN_BY_USER_GROUP_ROLE =
70          UserFinder.class.getName() + ".joinByUserGroupRole";
71  
72      public static String JOIN_BY_USERS_GROUPS =
73          UserFinder.class.getName() + ".joinByUsersGroups";
74  
75      public static String JOIN_BY_USERS_ORGS =
76          UserFinder.class.getName() + ".joinByUsersOrgs";
77  
78      public static String JOIN_BY_USERS_PASSWORD_POLICIES =
79          UserFinder.class.getName() + ".joinByUsersPasswordPolicies";
80  
81      public static String JOIN_BY_USERS_ROLES =
82          UserFinder.class.getName() + ".joinByUsersRoles";
83  
84      public static String JOIN_BY_USERS_USER_GROUPS =
85          UserFinder.class.getName() + ".joinByUsersUserGroups";
86  
87      public static String JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS =
88          UserFinder.class.getName() + ".joinByAnnouncementsDeliveryEmailOrSms";
89  
90      public static String JOIN_BY_SOCIAL_RELATION_BI =
91          UserFinder.class.getName() + ".joinBySocialRelationBidirectional";
92  
93      public static String JOIN_BY_SOCIAL_RELATION_UNI =
94          UserFinder.class.getName() + ".joinBySocialRelationUnidirectional";
95  
96      public int countByKeywords(
97              long companyId, String keywords, Boolean active,
98              LinkedHashMap<String, Object> params)
99          throws SystemException {
100 
101         String[] firstNames = null;
102         String[] middleNames = null;
103         String[] lastNames = null;
104         String[] screenNames = null;
105         String[] emailAddresses = null;
106         boolean andOperator = false;
107 
108         if (Validator.isNotNull(keywords)) {
109             firstNames = CustomSQLUtil.keywords(keywords);
110             middleNames = CustomSQLUtil.keywords(keywords);
111             lastNames = CustomSQLUtil.keywords(keywords);
112             screenNames = CustomSQLUtil.keywords(keywords);
113             emailAddresses = CustomSQLUtil.keywords(keywords);
114         }
115         else {
116             andOperator = true;
117         }
118 
119         return countByC_FN_MN_LN_SN_EA_A(
120             companyId, firstNames, middleNames, lastNames, screenNames,
121             emailAddresses, active, params, andOperator);
122     }
123 
124     public int countByC_FN_MN_LN_SN_EA_A(
125             long companyId, String firstName, String middleName,
126             String lastName, String screenName, String emailAddress,
127             Boolean active, LinkedHashMap<String, Object> params,
128             boolean andOperator)
129         throws SystemException {
130 
131         return countByC_FN_MN_LN_SN_EA_A(
132             companyId, new String[] {firstName}, new String[] {middleName},
133             new String[] {lastName}, new String[] {screenName},
134             new String[] {emailAddress}, active, params, andOperator);
135     }
136 
137     public int countByC_FN_MN_LN_SN_EA_A(
138             long companyId, String[] firstNames, String[] middleNames,
139             String[] lastNames, String[] screenNames, String[] emailAddresses,
140             Boolean active, LinkedHashMap<String, Object> params,
141             boolean andOperator)
142         throws SystemException {
143 
144         firstNames = CustomSQLUtil.keywords(firstNames);
145         middleNames = CustomSQLUtil.keywords(middleNames);
146         lastNames = CustomSQLUtil.keywords(lastNames);
147         screenNames = CustomSQLUtil.keywords(screenNames);
148         emailAddresses = CustomSQLUtil.keywords(emailAddresses);
149 
150         Session session = null;
151 
152         try {
153             session = HibernateUtil.openSession();
154 
155             String sql = CustomSQLUtil.get(COUNT_BY_C_FN_MN_LN_SN_EA_A);
156 
157             sql = CustomSQLUtil.replaceKeywords(
158                 sql, "lower(Contact_.firstName)", StringPool.LIKE, false,
159                 firstNames);
160             sql = CustomSQLUtil.replaceKeywords(
161                 sql, "lower(Contact_.middleName)", StringPool.LIKE, false,
162                 middleNames);
163             sql = CustomSQLUtil.replaceKeywords(
164                 sql, "lower(Contact_.lastName)", StringPool.LIKE, false,
165                 lastNames);
166             sql = CustomSQLUtil.replaceKeywords(
167                 sql, "lower(User_.screenName)", StringPool.LIKE, false,
168                 screenNames);
169             sql = CustomSQLUtil.replaceKeywords(
170                 sql, "lower(User_.emailAddress)", StringPool.LIKE, true,
171                 emailAddresses);
172 
173             if (active == null) {
174                 sql = StringUtil.replace(sql, ACTIVE_SQL, StringPool.BLANK);
175             }
176 
177             sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
178             sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
179             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
180 
181             SQLQuery q = session.createSQLQuery(sql);
182 
183             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
184 
185             QueryPos qPos = QueryPos.getInstance(q);
186 
187             setJoin(qPos, params);
188             qPos.add(companyId);
189             qPos.add(false);
190             qPos.add(firstNames, 2);
191             qPos.add(middleNames, 2);
192             qPos.add(lastNames, 2);
193             qPos.add(screenNames, 2);
194             qPos.add(emailAddresses, 2);
195 
196             if (active != null) {
197                 qPos.add(active);
198             }
199 
200             Iterator<Long> itr = q.list().iterator();
201 
202             if (itr.hasNext()) {
203                 Long count = itr.next();
204 
205                 if (count != null) {
206                     return count.intValue();
207                 }
208             }
209 
210             return 0;
211         }
212         catch (Exception e) {
213             throw new SystemException(e);
214         }
215         finally {
216             HibernateUtil.closeSession(session);
217         }
218     }
219 
220     public List<User> findByKeywords(
221             long companyId, String keywords, Boolean active,
222             LinkedHashMap<String, Object> params, int begin, int end,
223             OrderByComparator obc)
224         throws SystemException {
225 
226         String[] firstNames = null;
227         String[] middleNames = null;
228         String[] lastNames = null;
229         String[] screenNames = null;
230         String[] emailAddresses = null;
231         boolean andOperator = false;
232 
233         if (Validator.isNotNull(keywords)) {
234             firstNames = CustomSQLUtil.keywords(keywords);
235             middleNames = CustomSQLUtil.keywords(keywords);
236             lastNames = CustomSQLUtil.keywords(keywords);
237             screenNames = CustomSQLUtil.keywords(keywords);
238             emailAddresses = CustomSQLUtil.keywords(keywords);
239         }
240         else {
241             andOperator = true;
242         }
243 
244         return findByC_FN_MN_LN_SN_EA_A(
245             companyId, firstNames, middleNames, lastNames, screenNames,
246             emailAddresses, active, params, andOperator, begin, end, obc);
247     }
248 
249     public List<User> findByNoAnnouncementsDeliveries(String type)
250         throws SystemException {
251 
252         Session session = null;
253 
254         try {
255             session = HibernateUtil.openSession();
256 
257             String sql = CustomSQLUtil.get(FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES);
258 
259             SQLQuery q = session.createSQLQuery(sql);
260 
261             q.addEntity("User_", UserImpl.class);
262 
263             QueryPos qPos = QueryPos.getInstance(q);
264 
265             qPos.add(type);
266 
267             return q.list();
268         }
269         catch (Exception e) {
270             throw new SystemException(e);
271         }
272         finally {
273             HibernateUtil.closeSession(session);
274         }
275     }
276 
277     public List<User> findByC_FN_MN_LN_SN_EA_A(
278             long companyId, String firstName, String middleName,
279             String lastName, String screenName, String emailAddress,
280             Boolean active, LinkedHashMap<String, Object> params,
281             boolean andOperator, int begin, int end, OrderByComparator obc)
282         throws SystemException {
283 
284         return findByC_FN_MN_LN_SN_EA_A(
285             companyId, new String[] {firstName}, new String[] {middleName},
286             new String[] {lastName}, new String[] {screenName},
287             new String[] {emailAddress}, active, params, andOperator, begin,
288             end, obc);
289     }
290 
291     public List<User> findByC_FN_MN_LN_SN_EA_A(
292             long companyId, String[] firstNames, String[] middleNames,
293             String[] lastNames, String[] screenNames, String[] emailAddresses,
294             Boolean active, LinkedHashMap<String, Object> params,
295             boolean andOperator, int begin, int end, OrderByComparator obc)
296         throws SystemException {
297 
298         firstNames = CustomSQLUtil.keywords(firstNames);
299         middleNames = CustomSQLUtil.keywords(middleNames);
300         lastNames = CustomSQLUtil.keywords(lastNames);
301         screenNames = CustomSQLUtil.keywords(screenNames);
302         emailAddresses = CustomSQLUtil.keywords(emailAddresses);
303 
304         Session session = null;
305 
306         try {
307             session = HibernateUtil.openSession();
308 
309             String sql = CustomSQLUtil.get(FIND_BY_C_FN_MN_LN_SN_EA_A);
310 
311             sql = CustomSQLUtil.replaceKeywords(
312                 sql, "lower(Contact_.firstName)", StringPool.LIKE, false,
313                 firstNames);
314             sql = CustomSQLUtil.replaceKeywords(
315                 sql, "lower(Contact_.middleName)", StringPool.LIKE, false,
316                 middleNames);
317             sql = CustomSQLUtil.replaceKeywords(
318                 sql, "lower(Contact_.lastName)", StringPool.LIKE, false,
319                 lastNames);
320             sql = CustomSQLUtil.replaceKeywords(
321                 sql, "lower(User_.screenName)", StringPool.LIKE, false,
322                 screenNames);
323             sql = CustomSQLUtil.replaceKeywords(
324                 sql, "lower(User_.emailAddress)", StringPool.LIKE, true,
325                 emailAddresses);
326 
327             if (active == null) {
328                 sql = StringUtil.replace(sql, ACTIVE_SQL, StringPool.BLANK);
329             }
330 
331             sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
332             sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
333             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
334             sql = CustomSQLUtil.replaceOrderBy(sql, obc);
335 
336             SQLQuery q = session.createSQLQuery(sql);
337 
338             q.addEntity("User_", UserImpl.class);
339 
340             QueryPos qPos = QueryPos.getInstance(q);
341 
342             setJoin(qPos, params);
343             qPos.add(companyId);
344             qPos.add(false);
345             qPos.add(firstNames, 2);
346             qPos.add(middleNames, 2);
347             qPos.add(lastNames, 2);
348             qPos.add(screenNames, 2);
349             qPos.add(emailAddresses, 2);
350 
351             if (active != null) {
352                 qPos.add(active);
353             }
354 
355             return (List<User>)QueryUtil.list(
356                 q, HibernateUtil.getDialect(), begin, end);
357         }
358         catch (Exception e) {
359             throw new SystemException(e);
360         }
361         finally {
362             HibernateUtil.closeSession(session);
363         }
364     }
365 
366     protected String getJoin(LinkedHashMap<String, Object> params) {
367         if (params == null) {
368             return StringPool.BLANK;
369         }
370 
371         StringMaker sm = new StringMaker();
372 
373         Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
374 
375         while (itr.hasNext()) {
376             Map.Entry<String, Object> entry = itr.next();
377 
378             String key = entry.getKey();
379             Object value = entry.getValue();
380 
381             if (Validator.isNotNull(value)) {
382                 sm.append(getJoin(key));
383             }
384         }
385 
386         return sm.toString();
387     }
388 
389     protected String getJoin(String key) {
390         String join = StringPool.BLANK;
391 
392         if (key.equals("permission")) {
393             join = CustomSQLUtil.get(JOIN_BY_PERMISSION);
394         }
395         else if (key.equals("userGroupRole")) {
396             join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
397         }
398         else if (key.equals("usersGroups")) {
399             join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
400         }
401         else if (key.equals("usersOrgs")) {
402             join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
403         }
404         else if (key.equals("usersPasswordPolicies")) {
405             join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
406         }
407         else if (key.equals("usersRoles")) {
408             join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
409         }
410         else if (key.equals("usersUserGroups")) {
411             join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
412         }
413         else if (key.equals("announcementsDeliveryEmailOrSms")) {
414             join = CustomSQLUtil.get(
415                 JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
416         }
417         else if (key.equals("socialRelationBidirectional")) {
418             join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_BI);
419         }
420         else if (key.equals("socialRelationUnidirectional")) {
421             join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_UNI);
422         }
423 
424         if (Validator.isNotNull(join)) {
425             int pos = join.indexOf("WHERE");
426 
427             if (pos != -1) {
428                 join = join.substring(0, pos);
429             }
430         }
431 
432         return join;
433     }
434 
435     protected String getWhere(LinkedHashMap<String, Object> params) {
436         if (params == null) {
437             return StringPool.BLANK;
438         }
439 
440         StringMaker sm = new StringMaker();
441 
442         Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
443 
444         while (itr.hasNext()) {
445             Map.Entry<String, Object> entry = itr.next();
446 
447             String key = entry.getKey();
448             Object value = entry.getValue();
449 
450             if (Validator.isNotNull(value)) {
451                 sm.append(getWhere(key, value));
452             }
453         }
454 
455         return sm.toString();
456     }
457 
458     protected String getWhere(String key, Object value) {
459         String join = StringPool.BLANK;
460 
461         if (key.equals("permission")) {
462             join = CustomSQLUtil.get(JOIN_BY_PERMISSION);
463         }
464         else if (key.equals("userGroupRole")) {
465             join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
466         }
467         else if (key.equals("usersGroups")) {
468             join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
469         }
470         else if (key.equals("usersOrgs")) {
471             join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
472 
473             if (value instanceof Long[]) {
474                 Long[] organizationIds = (Long[])value;
475 
476                 StringMaker sm = new StringMaker();
477 
478                 sm.append("WHERE (");
479 
480                 for (int i = 0; i < organizationIds.length; i++) {
481                     sm.append("(Users_Orgs.organizationId = ?) ");
482 
483                     if ((i + 1) < organizationIds.length) {
484                         sm.append("OR ");
485                     }
486                 }
487 
488                 sm.append(")");
489 
490                 join = sm.toString();
491             }
492         }
493         else if (key.equals("usersPasswordPolicies")) {
494             join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
495         }
496         else if (key.equals("usersRoles")) {
497             join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
498         }
499         else if (key.equals("usersUserGroups")) {
500             join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
501         }
502         else if (key.equals("announcementsDeliveryEmailOrSms")) {
503             join = CustomSQLUtil.get(
504                 JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
505         }
506         else if (key.equals("socialRelationBidirectional")) {
507             join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_BI);
508         }
509         else if (key.equals("socialRelationUnidirectional")) {
510             join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_UNI);
511         }
512 
513         if (Validator.isNotNull(join)) {
514             int pos = join.indexOf("WHERE");
515 
516             if (pos != -1) {
517                 join = join.substring(pos + 5, join.length()) + " AND ";
518             }
519         }
520 
521         return join;
522     }
523 
524     protected void setJoin(
525         QueryPos qPos, LinkedHashMap<String, Object> params) {
526 
527         if (params != null) {
528             Iterator<Map.Entry<String, Object>> itr =
529                 params.entrySet().iterator();
530 
531             while (itr.hasNext()) {
532                 Map.Entry<String, Object> entry = itr.next();
533 
534                 Object value = entry.getValue();
535 
536                 if (value instanceof Long) {
537                     Long valueLong = (Long)value;
538 
539                     if (Validator.isNotNull(valueLong)) {
540                         qPos.add(valueLong);
541                     }
542                 }
543                 else if (value instanceof Long[]) {
544                     Long[] valueArray = (Long[])value;
545 
546                     for (int i = 0; i < valueArray.length; i++) {
547                         if (Validator.isNotNull(valueArray[i])) {
548                             qPos.add(valueArray[i]);
549                         }
550                     }
551                 }
552                 else if (value instanceof String) {
553                     String valueString = (String)value;
554 
555                     if (Validator.isNotNull(valueString)) {
556                         qPos.add(valueString);
557                     }
558                 }
559                 else if (value instanceof String[]) {
560                     String[] valueArray = (String[])value;
561 
562                     for (int i = 0; i < valueArray.length; i++) {
563                         if (Validator.isNotNull(valueArray[i])) {
564                             qPos.add(valueArray[i]);
565                         }
566                     }
567                 }
568             }
569         }
570     }
571 
572     protected static String ACTIVE_SQL = "AND (User_.active_ = ?)";
573 
574 }