2 * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved.
\r
4 * This library is free software; you can redistribute it and/or modify it under
\r
5 * the terms of the GNU Lesser General Public License as published by the Free
\r
6 * Software Foundation; either version 2.1 of the License, or (at your option)
\r
9 * This library is distributed in the hope that it will be useful, but WITHOUT
\r
10 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
\r
11 * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
\r
15 package com.liferay.portal.service.persistence;
\r
17 import com.liferay.portal.kernel.dao.orm.CustomSQLParam;
\r
18 import com.liferay.portal.kernel.dao.orm.QueryPos;
\r
19 import com.liferay.portal.kernel.dao.orm.QueryUtil;
\r
20 import com.liferay.portal.kernel.dao.orm.SQLQuery;
\r
21 import com.liferay.portal.kernel.dao.orm.Session;
\r
22 import com.liferay.portal.kernel.dao.orm.Type;
\r
23 import com.liferay.portal.kernel.exception.SystemException;
\r
24 import com.liferay.portal.kernel.util.GetterUtil;
\r
25 import com.liferay.portal.kernel.util.OrderByComparator;
\r
26 import com.liferay.portal.kernel.util.StringBundler;
\r
27 import com.liferay.portal.kernel.util.StringPool;
\r
28 import com.liferay.portal.kernel.util.StringUtil;
\r
29 import com.liferay.portal.kernel.util.Validator;
\r
30 import com.liferay.portal.kernel.workflow.WorkflowConstants;
\r
31 import com.liferay.portal.model.Group;
\r
32 import com.liferay.portal.model.Organization;
\r
33 import com.liferay.portal.model.User;
\r
34 import com.liferay.portal.model.UserGroup;
\r
35 import com.liferay.portal.model.impl.UserImpl;
\r
36 import com.liferay.portal.service.GroupLocalServiceUtil;
\r
37 import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
\r
38 import com.liferay.util.dao.orm.CustomSQLUtil;
\r
40 import java.util.ArrayList;
\r
41 import java.util.HashSet;
\r
42 import java.util.Iterator;
\r
43 import java.util.LinkedHashMap;
\r
44 import java.util.List;
\r
45 import java.util.Map;
\r
46 import java.util.Set;
\r
49 * @author Brian Wing Shun Chan
\r
51 * @author Raymond Augé
\r
52 * @author Connor McKay
\r
54 public class UserFinderImpl
\r
55 extends BasePersistenceImpl<User> implements UserFinder {
\r
57 public static final String COUNT_BY_USER =
\r
58 UserFinder.class.getName() + ".countByUser";
\r
60 public static final String COUNT_BY_C_FN_MN_LN_SN_EA_S =
\r
61 UserFinder.class.getName() + ".countByC_FN_MN_LN_SN_EA_S";
\r
63 public static final String FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES =
\r
64 UserFinder.class.getName() + ".findByNoAnnouncementsDeliveries";
\r
66 public static final String FIND_BY_NO_CONTACTS =
\r
67 UserFinder.class.getName() + ".findByNoContacts";
\r
69 public static final String FIND_BY_NO_GROUPS =
\r
70 UserFinder.class.getName() + ".findByNoGroups";
\r
72 public static final String FIND_BY_C_FN_MN_LN_SN_EA_S =
\r
73 UserFinder.class.getName() + ".findByC_FN_MN_LN_SN_EA_S";
\r
75 public static final String JOIN_BY_CONTACT_TWITTER_SN =
\r
76 UserFinder.class.getName() + ".joinByContactTwitterSN";
\r
78 public static final String JOIN_BY_NO_ORGANIZATIONS =
\r
79 UserFinder.class.getName() + ".joinByNoOrganizations";
\r
81 public static final String JOIN_BY_PERMISSION =
\r
82 UserFinder.class.getName() + ".joinByPermission";
\r
84 public static final String JOIN_BY_USER_GROUP_ROLE =
\r
85 UserFinder.class.getName() + ".joinByUserGroupRole";
\r
87 public static final String JOIN_BY_USERS_GROUPS =
\r
88 UserFinder.class.getName() + ".joinByUsersGroups";
\r
90 public static final String JOIN_BY_USERS_ORGS =
\r
91 UserFinder.class.getName() + ".joinByUsersOrgs";
\r
93 public static String JOIN_BY_USERS_ENSEIGNEMENT =
\r
94 UserFinder.class.getName() + ".joinByUserEnseignement";
\r
96 public static final String JOIN_BY_USERS_ORGS_TREE =
\r
97 UserFinder.class.getName() + ".joinByUsersOrgsTree";
\r
99 public static final String JOIN_BY_USERS_PASSWORD_POLICIES =
\r
100 UserFinder.class.getName() + ".joinByUsersPasswordPolicies";
\r
102 public static final String JOIN_BY_USERS_ROLES =
\r
103 UserFinder.class.getName() + ".joinByUsersRoles";
\r
105 public static final String JOIN_BY_USERS_TEAMS =
\r
106 UserFinder.class.getName() + ".joinByUsersTeams";
\r
108 public static final String JOIN_BY_USERS_USER_GROUPS =
\r
109 UserFinder.class.getName() + ".joinByUsersUserGroups";
\r
111 public static final String JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS =
\r
112 UserFinder.class.getName() + ".joinByAnnouncementsDeliveryEmailOrSms";
\r
114 public static final String JOIN_BY_SOCIAL_MUTUAL_RELATION =
\r
115 UserFinder.class.getName() + ".joinBySocialMutualRelation";
\r
117 public static final String JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE =
\r
118 UserFinder.class.getName() + ".joinBySocialMutualRelationType";
\r
120 public static final String JOIN_BY_SOCIAL_RELATION =
\r
121 UserFinder.class.getName() + ".joinBySocialRelation";
\r
123 public static final String JOIN_BY_SOCIAL_RELATION_TYPE =
\r
124 UserFinder.class.getName() + ".joinBySocialRelationType";
\r
126 public int countByUser(long userId, LinkedHashMap<String, Object> params)
\r
127 throws SystemException {
\r
129 Session session = null;
\r
132 session = openSession();
\r
134 String sql = CustomSQLUtil.get(COUNT_BY_USER);
\r
136 sql = replaceJoinAndWhere(sql, params);
\r
138 SQLQuery q = session.createSQLQuery(sql);
\r
140 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
\r
142 QueryPos qPos = QueryPos.getInstance(q);
\r
144 setJoin(qPos, params);
\r
148 Iterator<Long> itr = q.iterate();
\r
150 if (itr.hasNext()) {
\r
151 Long count = itr.next();
\r
153 if (count != null) {
\r
154 return count.intValue();
\r
160 catch (Exception e) {
\r
161 throw new SystemException(e);
\r
164 closeSession(session);
\r
168 public int countByKeywords(
\r
169 long companyId, String keywords, int status,
\r
170 LinkedHashMap<String, Object> params)
\r
171 throws SystemException {
\r
173 String[] firstNames = null;
\r
174 String[] middleNames = null;
\r
175 String[] lastNames = null;
\r
176 String[] screenNames = null;
\r
177 String[] emailAddresses = null;
\r
178 boolean andOperator = false;
\r
180 if (Validator.isNotNull(keywords)) {
\r
181 firstNames = CustomSQLUtil.keywords(keywords);
\r
182 middleNames = CustomSQLUtil.keywords(keywords);
\r
183 lastNames = CustomSQLUtil.keywords(keywords);
\r
184 screenNames = CustomSQLUtil.keywords(keywords);
\r
185 emailAddresses = CustomSQLUtil.keywords(keywords);
\r
188 andOperator = true;
\r
191 return countByC_FN_MN_LN_SN_EA_S(
\r
192 companyId, firstNames, middleNames, lastNames, screenNames,
\r
193 emailAddresses, status, params, andOperator);
\r
196 public int countByC_FN_MN_LN_SN_EA_S(
\r
197 long companyId, String firstName, String middleName,
\r
198 String lastName, String screenName, String emailAddress, int status,
\r
199 LinkedHashMap<String, Object> params, boolean andOperator)
\r
200 throws SystemException {
\r
202 // Search on the whole keyword and not over all parts of the keyword (split by space)
\r
203 String[] firstNames = convertToTab(firstName);
\r
204 String[] middleNames = convertToTab(middleName);
\r
205 String[] lastNames = convertToTab(lastName);
\r
206 String[] screenNames = convertToTab(screenName);
\r
207 String[] emailAddresses = convertToTab(emailAddress);
\r
209 return countByC_FN_MN_LN_SN_EA_S(
\r
210 companyId, firstNames, middleNames, lastNames, screenNames,
\r
211 emailAddresses, status, params, andOperator);
\r
214 public int countByC_FN_MN_LN_SN_EA_S(
\r
215 long companyId, String[] firstNames, String[] middleNames,
\r
216 String[] lastNames, String[] screenNames, String[] emailAddresses,
\r
217 int status, LinkedHashMap<String, Object> params,
\r
218 boolean andOperator)
\r
219 throws SystemException {
\r
221 firstNames = CustomSQLUtil.keywords(firstNames);
\r
222 middleNames = CustomSQLUtil.keywords(middleNames);
\r
223 lastNames = CustomSQLUtil.keywords(lastNames);
\r
224 screenNames = CustomSQLUtil.keywords(screenNames);
\r
225 emailAddresses = CustomSQLUtil.keywords(emailAddresses);
\r
227 if (params == null) {
\r
228 params = _emptyLinkedHashMap;
\r
231 Long groupId = (Long)params.get("usersGroups");
\r
232 boolean inherit = GetterUtil.getBoolean(params.get("inherit"));
\r
234 boolean doUnion = Validator.isNotNull(groupId) && inherit;
\r
236 LinkedHashMap<String, Object> params1 = params;
\r
238 LinkedHashMap<String, Object> params2 = null;
\r
240 LinkedHashMap<String, Object> params3 = null;
\r
243 params2 = new LinkedHashMap<String, Object>(params1);
\r
245 List<Long> organizationIds = new ArrayList<Long>();
\r
247 Group group = GroupLocalServiceUtil.fetchGroup(groupId);
\r
249 if ((group != null) && group.isOrganization()) {
\r
250 organizationIds.add(group.getOrganizationId());
\r
253 List<Organization> organizations = GroupUtil.getOrganizations(
\r
256 for (Organization organization : organizations) {
\r
257 organizationIds.add(organization.getOrganizationId());
\r
260 params2.remove("usersGroups");
\r
263 organizationIds.toArray(new Long[organizationIds.size()]));
\r
265 params3 = new LinkedHashMap<String, Object>(params1);
\r
267 List<UserGroup> userGroups = GroupUtil.getUserGroups(groupId);
\r
269 Long[] userGroupIds = new Long[userGroups.size()];
\r
271 for (int i = 0; i < userGroups.size(); i++) {
\r
272 UserGroup userGroup = userGroups.get(i);
\r
274 userGroupIds[i] = userGroup.getUserGroupId();
\r
277 params3.remove("usersGroups");
\r
278 params3.put("usersUserGroups", userGroupIds);
\r
281 Session session = null;
\r
284 session = openSession();
\r
286 Set<Long> userIds = new HashSet<Long>();
\r
289 countByC_FN_MN_LN_SN_EA_S(
\r
290 session, companyId, firstNames, middleNames, lastNames,
\r
291 screenNames, emailAddresses, status, params1, andOperator));
\r
295 countByC_FN_MN_LN_SN_EA_S(
\r
296 session, companyId, firstNames, middleNames, lastNames,
\r
297 screenNames, emailAddresses, status, params2,
\r
301 countByC_FN_MN_LN_SN_EA_S(
\r
302 session, companyId, firstNames, middleNames, lastNames,
\r
303 screenNames, emailAddresses, status, params3,
\r
307 return userIds.size();
\r
309 catch (Exception e) {
\r
310 throw new SystemException(e);
\r
313 closeSession(session);
\r
317 public List<User> findByKeywords(
\r
318 long companyId, String keywords, int status,
\r
319 LinkedHashMap<String, Object> params, int start, int end,
\r
320 OrderByComparator obc)
\r
321 throws SystemException {
\r
323 String[] firstNames = null;
\r
324 String[] middleNames = null;
\r
325 String[] lastNames = null;
\r
326 String[] screenNames = null;
\r
327 String[] emailAddresses = null;
\r
328 boolean andOperator = false;
\r
330 if (Validator.isNotNull(keywords)) {
\r
331 firstNames = CustomSQLUtil.keywords(keywords);
\r
332 middleNames = CustomSQLUtil.keywords(keywords);
\r
333 lastNames = CustomSQLUtil.keywords(keywords);
\r
334 screenNames = CustomSQLUtil.keywords(keywords);
\r
335 emailAddresses = CustomSQLUtil.keywords(keywords);
\r
338 andOperator = true;
\r
341 return findByC_FN_MN_LN_SN_EA_S(
\r
342 companyId, firstNames, middleNames, lastNames, screenNames,
\r
343 emailAddresses, status, params, andOperator, start, end, obc);
\r
346 public List<User> findByNoAnnouncementsDeliveries(String type)
\r
347 throws SystemException {
\r
349 Session session = null;
\r
352 session = openSession();
\r
354 String sql = CustomSQLUtil.get(FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES);
\r
356 SQLQuery q = session.createSQLQuery(sql);
\r
358 q.addEntity("User_", UserImpl.class);
\r
360 QueryPos qPos = QueryPos.getInstance(q);
\r
364 return q.list(true);
\r
366 catch (Exception e) {
\r
367 throw new SystemException(e);
\r
370 closeSession(session);
\r
374 public List<User> findByNoContacts() throws SystemException {
\r
375 Session session = null;
\r
378 session = openSession();
\r
380 String sql = CustomSQLUtil.get(FIND_BY_NO_CONTACTS);
\r
382 SQLQuery q = session.createSQLQuery(sql);
\r
384 q.addEntity("User_", UserImpl.class);
\r
386 return q.list(true);
\r
388 catch (Exception e) {
\r
389 throw new SystemException(e);
\r
392 closeSession(session);
\r
396 public List<User> findByEmail(String email) throws SystemException {
\r
397 Session session = null;
\r
400 session = openSession();
\r
402 String sql = CustomSQLUtil.get(FIND_BY_NO_CONTACTS);
\r
404 SQLQuery q = session.createSQLQuery(sql);
\r
406 q.addEntity("User_", UserImpl.class);
\r
408 return q.list(true);
\r
410 catch (Exception e) {
\r
411 throw new SystemException(e);
\r
414 closeSession(session);
\r
418 public List<User> findByNoGroups() throws SystemException {
\r
419 Session session = null;
\r
422 session = openSession();
\r
424 String sql = CustomSQLUtil.get(FIND_BY_NO_GROUPS);
\r
426 SQLQuery q = session.createSQLQuery(sql);
\r
428 q.addEntity("User_", UserImpl.class);
\r
430 return q.list(true);
\r
432 catch (Exception e) {
\r
433 throw new SystemException(e);
\r
436 closeSession(session);
\r
441 private String[] convertToTab(String value) {
\r
443 if (value == null) {
\r
446 tab = new String[1];
\r
447 tab[0] = "%" + value + "%";
\r
452 public List<User> findByC_FN_MN_LN_SN_EA_S(
\r
453 long companyId, String firstName, String middleName,
\r
454 String lastName, String screenName, String emailAddress, int status,
\r
455 LinkedHashMap<String, Object> params, boolean andOperator,
\r
456 int start, int end, OrderByComparator obc)
\r
457 throws SystemException {
\r
459 // Search on the whole keyword and not over all parts of the keyword (split by space)
\r
460 String[] firstNames = convertToTab(firstName);
\r
461 String[] middleNames = convertToTab(middleName);
\r
462 String[] lastNames = convertToTab(lastName);
\r
463 String[] screenNames = convertToTab(screenName);
\r
464 String[] emailAddresses = convertToTab(emailAddress);
\r
466 return findByC_FN_MN_LN_SN_EA_S(
\r
467 companyId, firstNames, middleNames, lastNames, screenNames,
\r
468 emailAddresses, status, params, andOperator, start, end, obc);
\r
471 public List<User> findByC_FN_MN_LN_SN_EA_S(
\r
472 long companyId, String[] firstNames, String[] middleNames,
\r
473 String[] lastNames, String[] screenNames, String[] emailAddresses,
\r
474 int status, LinkedHashMap<String, Object> params,
\r
475 boolean andOperator, int start, int end, OrderByComparator obc)
\r
476 throws SystemException {
\r
477 firstNames = CustomSQLUtil.keywords(firstNames);
\r
478 middleNames = CustomSQLUtil.keywords(middleNames);
\r
479 lastNames = CustomSQLUtil.keywords(lastNames);
\r
480 screenNames = CustomSQLUtil.keywords(screenNames);
\r
481 emailAddresses = CustomSQLUtil.keywords(emailAddresses);
\r
483 if (params == null) {
\r
484 params = _emptyLinkedHashMap;
\r
487 Long groupId = (Long)params.get("usersGroups");
\r
488 boolean inherit = GetterUtil.getBoolean(params.get("inherit"));
\r
490 boolean doUnion = Validator.isNotNull(groupId) && inherit;
\r
492 LinkedHashMap<String, Object> params1 = params;
\r
494 LinkedHashMap<String, Object> params2 = null;
\r
496 LinkedHashMap<String, Object> params3 = null;
\r
499 params2 = new LinkedHashMap<String, Object>(params1);
\r
501 List<Long> organizationIds = new ArrayList<Long>();
\r
503 Group group = GroupLocalServiceUtil.fetchGroup(groupId);
\r
505 if ((group != null) && group.isOrganization()) {
\r
506 organizationIds.add(group.getOrganizationId());
\r
509 List<Organization> organizations = GroupUtil.getOrganizations(
\r
512 for (Organization organization : organizations) {
\r
513 organizationIds.add(organization.getOrganizationId());
\r
516 params2.remove("usersGroups");
\r
519 organizationIds.toArray(new Long[organizationIds.size()]));
\r
521 params3 = new LinkedHashMap<String, Object>(params1);
\r
523 List<UserGroup> userGroups = GroupUtil.getUserGroups(groupId);
\r
525 Long[] userGroupIds = new Long[userGroups.size()];
\r
527 for (int i = 0; i < userGroups.size(); i++) {
\r
528 UserGroup userGroup = userGroups.get(i);
\r
530 userGroupIds[i] = userGroup.getUserGroupId();
\r
533 params3.remove("usersGroups");
\r
534 params3.put("usersUserGroups", userGroupIds);
\r
537 Session session = null;
\r
540 session = openSession();
\r
542 //String sql = CustomSQLUtil.get(FIND_BY_C_FN_MN_LN_SN_EA_S);
\r
543 String sql = "SELECT * FROM User_ "+
\r
545 " WHERE [$WHERE$] "+
\r
546 " (User_.companyId = ?) AND "+
\r
547 " (User_.defaultUser = ?) AND "+
\r
549 " (lower(User_.firstName) LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$] "+
\r
550 " (lower(User_.middleName) LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$] "+
\r
551 " (lower(User_.lastName) LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$] "+
\r
552 " (lower(User_.screenName) LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$] "+
\r
553 " (lower(User_.emailAddress) LIKE ? [$AND_OR_NULL_CHECK$]) "+
\r
554 " ) AND (User_.status = ?) ";
\r
556 sql = CustomSQLUtil.replaceKeywords(
\r
557 sql, "lower(User_.firstName)", StringPool.LIKE, false,
\r
559 sql = CustomSQLUtil.replaceKeywords(
\r
560 sql, "lower(User_.middleName)", StringPool.LIKE, false,
\r
562 sql = CustomSQLUtil.replaceKeywords(
\r
563 sql, "lower(User_.lastName)", StringPool.LIKE, false,
\r
565 sql = CustomSQLUtil.replaceKeywords(
\r
566 sql, "lower(User_.screenName)", StringPool.LIKE, false,
\r
568 sql = CustomSQLUtil.replaceKeywords(
\r
569 sql, "lower(User_.emailAddress)", StringPool.LIKE, true,
\r
572 if (status == WorkflowConstants.STATUS_ANY) {
\r
573 sql = StringUtil.replace(sql, _STATUS_SQL, StringPool.BLANK);
\r
576 StringBundler sb = new StringBundler();
\r
578 sb.append(StringPool.OPEN_PARENTHESIS);
\r
579 sb.append(replaceJoinAndWhere(sql, params1));
\r
580 sb.append(StringPool.CLOSE_PARENTHESIS);
\r
583 sb.append(" UNION (");
\r
584 sb.append(replaceJoinAndWhere(sql, params2));
\r
585 sb.append(") UNION (");
\r
586 sb.append(replaceJoinAndWhere(sql, params3));
\r
587 sb.append(StringPool.CLOSE_PARENTHESIS);
\r
591 sb.append(" ORDER BY ");
\r
592 sb.append(obc.toString());
\r
595 sql = sb.toString();
\r
597 sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
\r
599 SQLQuery q = session.createSQLQuery(sql);
\r
601 QueryPos qPos = QueryPos.getInstance(q);
\r
603 setJoin(qPos, params1);
\r
605 qPos.add(companyId);
\r
607 qPos.add(firstNames, 2);
\r
608 qPos.add(middleNames, 2);
\r
609 qPos.add(lastNames, 2);
\r
610 qPos.add(screenNames, 2);
\r
611 qPos.add(emailAddresses, 2);
\r
613 if (status != WorkflowConstants.STATUS_ANY) {
\r
618 setJoin(qPos, params2);
\r
620 qPos.add(companyId);
\r
622 qPos.add(firstNames, 2);
\r
623 qPos.add(middleNames, 2);
\r
624 qPos.add(lastNames, 2);
\r
625 qPos.add(screenNames, 2);
\r
626 qPos.add(emailAddresses, 2);
\r
628 if (status != WorkflowConstants.STATUS_ANY) {
\r
632 setJoin(qPos, params3);
\r
634 qPos.add(companyId);
\r
636 qPos.add(firstNames, 2);
\r
637 qPos.add(middleNames, 2);
\r
638 qPos.add(lastNames, 2);
\r
639 qPos.add(screenNames, 2);
\r
640 qPos.add(emailAddresses, 2);
\r
642 if (status != WorkflowConstants.STATUS_ANY) {
\r
646 q.addEntity("User_", UserImpl.class);
\r
648 if (start == -1 && end == -1) {
\r
649 return q.list(true);
\r
652 return (List<User>)QueryUtil.list(q, getDialect(), start, end);
\r
655 catch (Exception e) {
\r
656 throw new SystemException(e);
\r
659 closeSession(session);
\r
663 protected List<Long> countByC_FN_MN_LN_SN_EA_S(
\r
664 Session session, long companyId, String[] firstNames,
\r
665 String[] middleNames, String[] lastNames, String[] screenNames,
\r
666 String[] emailAddresses, int status,
\r
667 LinkedHashMap<String, Object> params, boolean andOperator) {
\r
669 String sql = CustomSQLUtil.get(COUNT_BY_C_FN_MN_LN_SN_EA_S);
\r
671 sql = CustomSQLUtil.replaceKeywords(
\r
672 sql, "lower(User_.firstName)", StringPool.LIKE, false, firstNames);
\r
673 sql = CustomSQLUtil.replaceKeywords(
\r
674 sql, "lower(User_.middleName)", StringPool.LIKE, false,
\r
676 sql = CustomSQLUtil.replaceKeywords(
\r
677 sql, "lower(User_.lastName)", StringPool.LIKE, false, lastNames);
\r
678 sql = CustomSQLUtil.replaceKeywords(
\r
679 sql, "lower(User_.screenName)", StringPool.LIKE, false,
\r
681 sql = CustomSQLUtil.replaceKeywords(
\r
682 sql, "lower(User_.emailAddress)", StringPool.LIKE, true,
\r
685 if (status == WorkflowConstants.STATUS_ANY) {
\r
686 sql = StringUtil.replace(sql, _STATUS_SQL, StringPool.BLANK);
\r
689 sql = replaceJoinAndWhere(sql, params);
\r
690 sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
\r
692 SQLQuery q = session.createSQLQuery(sql);
\r
694 q.addScalar("userId", Type.LONG);
\r
696 QueryPos qPos = QueryPos.getInstance(q);
\r
698 setJoin(qPos, params);
\r
700 qPos.add(companyId);
\r
702 qPos.add(firstNames, 2);
\r
703 qPos.add(middleNames, 2);
\r
704 qPos.add(lastNames, 2);
\r
705 qPos.add(screenNames, 2);
\r
706 qPos.add(emailAddresses, 2);
\r
708 if (status != WorkflowConstants.STATUS_ANY) {
\r
712 return q.list(true);
\r
715 protected String getJoin(LinkedHashMap<String, Object> params) {
\r
716 if ((params == null) || params.isEmpty()) {
\r
717 return StringPool.BLANK;
\r
720 StringBundler sb = new StringBundler(params.size());
\r
722 Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
\r
724 while (itr.hasNext()) {
\r
725 Map.Entry<String, Object> entry = itr.next();
\r
727 String key = entry.getKey();
\r
729 if (key.equals("expandoAttributes")) {
\r
733 Object value = entry.getValue();
\r
735 if (Validator.isNotNull(value)) {
\r
736 sb.append(getJoin(key, value));
\r
740 return sb.toString();
\r
743 protected String getJoin(String key, Object value) {
\r
744 String join = StringPool.BLANK;
\r
746 if (key.equals("contactTwitterSn")) {
\r
747 join = CustomSQLUtil.get(JOIN_BY_CONTACT_TWITTER_SN);
\r
749 else if (key.equals("noOrganizations")) {
\r
750 join = CustomSQLUtil.get(JOIN_BY_NO_ORGANIZATIONS);
\r
752 else if (key.equals("permission")) {
\r
753 join = CustomSQLUtil.get(JOIN_BY_PERMISSION);
\r
755 else if (key.equals("userGroupRole")) {
\r
756 join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
\r
758 else if (key.equals("usersGroups")) {
\r
759 join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
\r
761 else if (key.equals("usersOrgs")) {
\r
762 join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
\r
764 else if (key.equals("usersOrgsTree")) {
\r
765 join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS_TREE);
\r
767 else if (key.equals("usersPasswordPolicies")) {
\r
768 join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
\r
770 else if (key.equals("usersRoles")) {
\r
771 join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
\r
773 else if (key.equals("usersTeams")) {
\r
774 join = CustomSQLUtil.get(JOIN_BY_USERS_TEAMS);
\r
776 else if (key.equals("usersUserGroups")) {
\r
777 join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
\r
779 else if (key.equals("announcementsDeliveryEmailOrSms")) {
\r
780 join = CustomSQLUtil.get(
\r
781 JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
\r
783 else if (key.equals("socialMutualRelation")) {
\r
784 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION);
\r
786 else if (key.equals("socialMutualRelationType")) {
\r
787 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE);
\r
789 else if (key.equals("socialRelation")) {
\r
790 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION);
\r
792 else if (key.equals("socialRelationType")) {
\r
793 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_TYPE);
\r
795 else if (key.equals("userEnseignement")) {
\r
796 join = CustomSQLUtil.get(JOIN_BY_USERS_ENSEIGNEMENT);
\r
798 else if (value instanceof CustomSQLParam) {
\r
799 CustomSQLParam customSQLParam = (CustomSQLParam)value;
\r
801 join = customSQLParam.getSQL();
\r
804 if (Validator.isNotNull(join)) {
\r
805 int pos = join.indexOf("WHERE");
\r
808 join = join.substring(0, pos);
\r
815 protected String getWhere(LinkedHashMap<String, Object> params) {
\r
816 if ((params == null) || params.isEmpty()) {
\r
817 return StringPool.BLANK;
\r
820 StringBundler sb = new StringBundler(params.size());
\r
822 Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
\r
824 while (itr.hasNext()) {
\r
825 Map.Entry<String, Object> entry = itr.next();
\r
827 String key = entry.getKey();
\r
829 if (key.equals("expandoAttributes")) {
\r
833 Object value = entry.getValue();
\r
835 if (Validator.isNotNull(value)) {
\r
836 sb.append(getWhere(key, value));
\r
840 return sb.toString();
\r
843 protected String getWhere(String key, Object value) {
\r
844 String join = StringPool.BLANK;
\r
846 if (key.equals("contactTwitterSn")) {
\r
847 join = CustomSQLUtil.get(JOIN_BY_CONTACT_TWITTER_SN);
\r
849 else if (key.equals("noOrganizations")) {
\r
850 join = CustomSQLUtil.get(JOIN_BY_NO_ORGANIZATIONS);
\r
852 else if (key.equals("permission")) {
\r
853 join = CustomSQLUtil.get(JOIN_BY_PERMISSION);
\r
855 else if (key.equals("userGroupRole")) {
\r
856 join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
\r
858 else if (key.equals("usersGroups")) {
\r
859 join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
\r
861 else if (key.equals("userEnseignement")) {
\r
862 join = CustomSQLUtil.get(JOIN_BY_USERS_ENSEIGNEMENT);
\r
864 else if (key.equals("usersOrgs")) {
\r
865 if (value instanceof Long) {
\r
866 join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
\r
868 else if (value instanceof Long[]) {
\r
869 Long[] organizationIds = (Long[])value;
\r
871 if (organizationIds.length == 0) {
\r
872 join = "WHERE (Users_Orgs.organizationId = -1)";
\r
875 StringBundler sb = new StringBundler(
\r
876 organizationIds.length * 2 + 1);
\r
878 sb.append("WHERE (");
\r
880 for (int i = 0; i < organizationIds.length; i++) {
\r
881 sb.append("(Users_Orgs.organizationId = ?) ");
\r
883 if ((i + 1) < organizationIds.length) {
\r
888 sb.append(StringPool.CLOSE_PARENTHESIS);
\r
890 join = sb.toString();
\r
894 else if (key.equals("usersOrgsTree")) {
\r
895 List<Organization> organizationsTree = (List<Organization>)value;
\r
897 int size = organizationsTree.size();
\r
900 StringBundler sb = new StringBundler(size * 2 + 1);
\r
902 sb.append("WHERE (");
\r
904 for (int i = 0; i < size; i++) {
\r
905 sb.append("(Organization_.treePath LIKE ?) ");
\r
907 if ((i + 1) < size) {
\r
912 sb.append(StringPool.CLOSE_PARENTHESIS);
\r
914 join = sb.toString();
\r
917 join = "WHERE (Organization_.treePath LIKE ?)";
\r
920 else if (key.equals("usersPasswordPolicies")) {
\r
921 join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
\r
923 else if (key.equals("usersRoles")) {
\r
924 join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
\r
926 else if (key.equals("usersTeams")) {
\r
927 join = CustomSQLUtil.get(JOIN_BY_USERS_TEAMS);
\r
929 else if (key.equals("usersUserGroups")) {
\r
930 if (value instanceof Long) {
\r
931 join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
\r
933 else if (value instanceof Long[]) {
\r
934 Long[] userGroupIds = (Long[])value;
\r
936 if (userGroupIds.length == 0) {
\r
937 join = "WHERE (Users_UserGroups.userGroupId = -1)";
\r
940 StringBundler sb = new StringBundler(
\r
941 userGroupIds.length * 2 + 1);
\r
943 sb.append("WHERE (");
\r
945 for (int i = 0; i < userGroupIds.length; i++) {
\r
946 sb.append("(Users_UserGroups.userGroupId = ?) ");
\r
948 if ((i + 1) < userGroupIds.length) {
\r
953 sb.append(StringPool.CLOSE_PARENTHESIS);
\r
955 join = sb.toString();
\r
959 else if (key.equals("announcementsDeliveryEmailOrSms")) {
\r
960 join = CustomSQLUtil.get(
\r
961 JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
\r
963 else if (key.equals("socialMutualRelation")) {
\r
964 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION);
\r
966 else if (key.equals("socialMutualRelationType")) {
\r
967 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE);
\r
969 else if (key.equals("socialRelation")) {
\r
970 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION);
\r
972 else if (key.equals("socialRelationType")) {
\r
973 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_TYPE);
\r
975 else if (value instanceof CustomSQLParam) {
\r
976 CustomSQLParam customSQLParam = (CustomSQLParam)value;
\r
978 join = customSQLParam.getSQL();
\r
981 if (Validator.isNotNull(join)) {
\r
982 int pos = join.indexOf("WHERE");
\r
985 join = join.substring(pos + 5, join.length()).concat(" AND ");
\r
988 join = StringPool.BLANK;
\r
995 protected String replaceJoinAndWhere(
\r
996 String sql, LinkedHashMap<String, Object> params) {
\r
998 sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
\r
999 sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
\r
1004 protected void setJoin(
\r
1005 QueryPos qPos, LinkedHashMap<String, Object> params) {
\r
1007 if (params == null) {
\r
1011 for (Map.Entry<String, Object> entry : params.entrySet()) {
\r
1012 String key = entry.getKey();
\r
1014 if (key.equals("expandoAttributes")) {
\r
1018 Object value = entry.getValue();
\r
1020 if (key.equals("usersOrgsTree")) {
\r
1021 List<Organization> organizationsTree =
\r
1022 (List<Organization>)value;
\r
1024 if (!organizationsTree.isEmpty()) {
\r
1025 for (Organization organization : organizationsTree) {
\r
1026 StringBundler treePath = new StringBundler(5);
\r
1028 treePath.append(StringPool.PERCENT);
\r
1029 treePath.append(StringPool.SLASH);
\r
1030 treePath.append(organization.getOrganizationId());
\r
1031 treePath.append(StringPool.SLASH);
\r
1032 treePath.append(StringPool.PERCENT);
\r
1034 qPos.add(treePath.toString());
\r
1038 qPos.add("%/ /%");
\r
1041 else if (value instanceof Long) {
\r
1042 Long valueLong = (Long)value;
\r
1044 if (Validator.isNotNull(valueLong)) {
\r
1045 qPos.add(valueLong);
\r
1048 else if (value instanceof Long[]) {
\r
1049 Long[] valueArray = (Long[])value;
\r
1051 for (Long element : valueArray) {
\r
1052 if (Validator.isNotNull(element)) {
\r
1053 qPos.add(element);
\r
1057 else if (value instanceof Long[][]) {
\r
1058 Long[][] valueDoubleArray = (Long[][])value;
\r
1060 for (Long[] valueArray : valueDoubleArray) {
\r
1061 for (Long valueLong : valueArray) {
\r
1062 qPos.add(valueLong);
\r
1066 else if (value instanceof String) {
\r
1067 String valueString = (String)value;
\r
1069 if (Validator.isNotNull(valueString)) {
\r
1070 qPos.add(valueString);
\r
1073 else if (value instanceof String[]) {
\r
1074 String[] valueArray = (String[])value;
\r
1076 for (String element : valueArray) {
\r
1077 if (Validator.isNotNull(element)) {
\r
1078 qPos.add(element);
\r
1082 else if (value instanceof CustomSQLParam) {
\r
1083 CustomSQLParam customSQLParam = (CustomSQLParam)value;
\r
1085 customSQLParam.process(qPos);
\r
1090 private static final String _STATUS_SQL = "AND (User_.status = ?)";
\r
1092 private LinkedHashMap<String, Object> _emptyLinkedHashMap =
\r
1093 new LinkedHashMap<String, Object>(0);
\r