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