def user_es_query(cls, domain, request):
user_ids = cls.selected_user_ids(request)
user_types = cls.selected_user_types(request)
group_ids = cls.selected_group_ids(request)
user_type_filters = []
if HQUserType.ADMIN in user_types:
user_type_filters.append(user_es.admin_users())
if HQUserType.UNKNOWN in user_types:
user_type_filters.append(user_es.unknown_users())
user_type_filters.append(user_es.web_users())
if HQUserType.DEMO_USER in user_types:
user_type_filters.append(user_es.demo_users())
q = user_es.UserES().domain(domain)
if HQUserType.REGISTERED in user_types:
# return all users with selected user_types
user_type_filters.append(user_es.mobile_users())
return q.OR(*user_type_filters)
else:
# return matching user types and exact matches
id_filter = filters.OR(
filters.term("_id", user_ids),
filters.term("__group_ids", group_ids),
)
if user_type_filters:
return q.OR(
id_filter,
filters.OR(*user_type_filters),
)
else:
return q.filter(id_filter)
def to_es_filter(self):
if self.show_all:
return None
if self.is_null:
return filters.OR(
*[filters.missing(field) for field in self.filter.fields])
terms = [v.value for v in self.value]
return filters.OR(*[filters.term(self.filter.field, terms)])
def user_es_query(cls, domain, mobile_user_and_group_slugs, request_user):
# The queryset returned by this method is location-safe
user_ids = cls.selected_user_ids(mobile_user_and_group_slugs)
user_types = cls.selected_user_types(mobile_user_and_group_slugs)
group_ids = cls.selected_group_ids(mobile_user_and_group_slugs)
location_ids = cls.selected_location_ids(mobile_user_and_group_slugs)
user_type_filters = []
if HQUserType.ADMIN in user_types:
user_type_filters.append(user_es.admin_users())
if HQUserType.UNKNOWN in user_types:
user_type_filters.append(user_es.unknown_users())
user_type_filters.append(user_es.web_users())
if HQUserType.DEMO_USER in user_types:
user_type_filters.append(user_es.demo_users())
q = user_es.UserES().domain(domain)
if HQUserType.ACTIVE in user_types and HQUserType.DEACTIVATED in user_types:
q = q.show_inactive()
elif HQUserType.DEACTIVATED in user_types:
q = q.show_only_inactive()
if not request_user.has_permission(domain, 'access_all_locations'):
cls._verify_users_are_accessible(domain, request_user, user_ids)
return q.OR(
filters.term("_id", user_ids),
user_es.location(list(SQLLocation.active_objects
.get_locations_and_children(location_ids)
.accessible_to_user(domain, request_user)
.location_ids())),
)
elif HQUserType.ACTIVE in user_types or HQUserType.DEACTIVATED in user_types:
# return all users with selected user_types
user_type_filters.append(user_es.mobile_users())
return q.OR(*user_type_filters)
else:
# return matching user types and exact matches
location_ids = list(SQLLocation.active_objects
.get_locations_and_children(location_ids)
.location_ids())
id_filter = filters.OR(
filters.term("_id", user_ids),
filters.term("__group_ids", group_ids),
user_es.location(location_ids),
)
if user_type_filters:
return q.OR(
id_filter,
filters.OR(*user_type_filters),
)
else:
return q.filter(id_filter)
def get_user_ids_for_user_types(self, admin, unknown, demo, commtrack):
"""
referenced from CaseListMixin to fetch user_ids for selected user type
:param admin: if admin users to be included
:param unknown: if unknown users to be included
:param demo: if demo users to be included
:param commtrack: if commtrack users to be included
:return: user_ids for selected user types
"""
from corehq.apps.es import filters, users as user_es
if not any([admin, unknown, demo]):
return []
user_filters = [filter_ for include, filter_ in [
(admin, user_es.admin_users()),
(unknown, filters.OR(user_es.unknown_users(), user_es.web_users())),
(demo, user_es.demo_users()),
] if include]
query = (user_es.UserES()
.domain(self.domain_object.name)
.OR(*user_filters)
.show_inactive()
.fields([]))
user_ids = query.run().doc_ids
if commtrack:
user_ids.append("commtrack-system")
if demo:
user_ids.append("demo_user_group_id")
user_ids.append("demo_user")
return user_ids
def login_as_user_query(domain, couch_user, search_string, limit, offset):
'''
Takes in various parameters to determine which users to populate the login as screen.
:param domain: String domain
:param couch_user: The CouchUser that is using the Login As feature
:param search_string: The query that filters the users returned. Filters based on the
`search_fields` as well as any fields defined in `user_data_fields`.
:param limit: The max amount of users returned.
:param offset: From where to start the query.
:returns: An EsQuery instance.
'''
search_fields = [
"base_username", "last_name", "first_name", "phone_numbers"
]
user_es = (UserES().domain(domain).start(offset).size(limit).sort(
'username.exact').search_string_query(search_string, search_fields))
if not couch_user.has_permission(domain, 'access_all_locations'):
loc_ids = SQLLocation.objects.accessible_to_user(
domain, couch_user).location_ids()
user_es = user_es.location(list(loc_ids))
if _limit_login_as(couch_user, domain):
user_filters = [login_as_user_filter(couch_user.username)]
if couch_user.has_permission(domain, 'access_default_login_as_user'):
user_filters.append(login_as_user_filter('default'))
user_es = user_es.filter(
queries.nested('user_data_es', filters.OR(*user_filters)))
return user_es.mobile_users()
def get_by_case_id_form_es_query(start, size, case_id):
base_query = get_base_form_es_query(start, size)
return (base_query.filter(
filters.nested(
'form.case',
filters.OR(filters.term('form.case.@case_id', case_id),
filters.term('form.case.case_id', case_id)))))
def to_es_filter(self):
location_id = self.value[0].value
fs = [
filters.term(x.column, x.filter_value)
for x in self.get_hierarchy(location_id)
]
return filters.OR(fs)
def login_as_user_query(domain,
couch_user,
search_string,
limit,
offset,
user_data_fields=None):
'''
Takes in various parameters to determine which users to populate the login as screen.
:param domain: String domain
:param couch_user: The CouchUser that is using the Login As feature
:param search_string: The query that filters the users returned. Filters based on the
`search_fields` as well as any fields defined in `user_data_fields`.
:param limit: The max amount of users returned.
:param offset: From where to start the query.
:param user_data_fields: A list of custom user data fields that should also be searched
by the `search_string`
:returns: An EsQuery instance.
'''
search_fields = [
"base_username", "last_name", "first_name", "phone_numbers"
]
should_criteria_query = [
queries.search_string_query(search_string, search_fields),
]
if user_data_fields:
or_criteria = []
for field in user_data_fields:
or_criteria.append(
filters.AND(
filters.term('user_data_es.key', field),
filters.term('user_data_es.value', search_string),
), )
should_criteria_query.append(
queries.nested_filter('user_data_es', filters.OR(*or_criteria)))
user_es = (
UserES().domain(domain).start(offset).size(limit).
sort('username.exact').set_query(
queries.BOOL_CLAUSE(
queries.SHOULD_CLAUSE(
should_criteria_query,
# It should either match on the search fields like username or it
# should match on the custom user data fields. If this were 2, then
# it would require the search string to match both on the search fields and
# the custom user data fields.
minimum_should_match=1,
), )))
if not couch_user.has_permission(domain, 'access_all_locations'):
loc_ids = SQLLocation.objects.accessible_to_user(
domain, couch_user).location_ids()
user_es = user_es.location(list(loc_ids))
return user_es.mobile_users()
def consume_params(self, raw_params):
value = raw_params.pop(self.param, None)
if value:
return filters.OR(
filters.AND(filters.NOT(filters.missing(self.param)),
filters.range_filter(self.param, **value)),
filters.AND(filters.missing(self.param),
filters.range_filter("received_on", **value)))
def _get_assigned_cases(checkin_case):
query = (CaseSearchES().domain(checkin_case.domain).filter(
filters.OR(case_type("patient"),
case_type("contact"))).case_property_query(
"assigned_to_primary_checkin_case_id",
checkin_case.case_id))
return [CommCareCase.wrap(flatten_result(hit)) for hit in query.run().hits]
def test_not_filter_edge_case(self):
self._setup_data()
query = FormES().remove_default_filters().filter(
filters.NOT(filters.OR(
filters.term('domain', 'd'),
filters.term('app_id', 'a')
))
)
self.assertEqual(query.run().doc_ids, ['doc3'])
def _get_case_property_or_missing_query(self, criteria):
if criteria.is_empty:
return case_property_missing(criteria.key)
if criteria.is_ancestor_query:
missing_filter = build_filter_from_xpath(self.query_domains,
f'{criteria.key} = ""')
else:
missing_filter = case_property_missing(criteria.key)
return filters.OR(self._get_query(criteria), missing_filter)
def _get_cursor_query(domain, params, last_date, last_id):
query = _get_query(domain, params)
return query.filter(
filters.OR(
filters.AND(
filters.term('@indexed_on', last_date),
filters.range_filter('_id', gt=last_id),
),
case_search.indexed_on(gt=last_date),
)
)
def get_nested_terms_filter(prop, terms):
filters = []
def make_filter(term):
return es_filters.term(prop, term)
for term in _get_terms_list(terms):
if len(term) == 1:
filters.append(make_filter(term[0]))
elif len(term) > 1:
filters.append(es_filters.AND(*(make_filter(t) for t in term)))
return es_filters.OR(*filters)
def es_query_from_get_params(search_params,
domain,
reserved_query_params=None,
doc_type='form'):
# doc_type can be form or case
assert doc_type in ['form', 'case']
es = FormES() if doc_type == 'form' else CaseES()
query = es.remove_default_filters().domain(domain)
if doc_type == 'form':
if 'include_archived' in search_params:
query = query.filter(
filters.OR(filters.term('doc_type', 'xforminstance'),
filters.term('doc_type', 'xformarchived')))
else:
query = query.filter(filters.term('doc_type', 'xforminstance'))
if '_search' in search_params:
# This is undocumented usecase by Data export tool and one custom project
# Validate that the passed in param is one of these two expected
_filter = _validate_and_get_es_filter(
json.loads(search_params['_search']))
query = query.filter(_filter)
# filters are actually going to be a more common case
reserved_query_params = RESERVED_QUERY_PARAMS | set(reserved_query_params
or [])
query_params = {
param: value
for param, value in search_params.items()
if param not in reserved_query_params and not param.endswith('__full')
}
for consumer in query_param_consumers:
try:
payload_filter = consumer.consume_params(query_params)
except DateTimeError as e:
raise Http400("Bad query parameter: {}".format(str(e)))
if payload_filter:
query = query.filter(payload_filter)
# add unconsumed filters
for param, value in query_params.items():
# assume these fields are analyzed in ES so convert to lowercase
# Any fields that are not analyzed in ES should be in the ``query_param_consumers`` above
value = value.lower()
query = query.filter(filters.term(param, value))
return query.raw_query
def user_es_query(cls, domain, mobile_user_and_group_slugs):
user_ids = cls.selected_user_ids(mobile_user_and_group_slugs)
user_types = cls.selected_user_types(mobile_user_and_group_slugs)
group_ids = cls.selected_group_ids(mobile_user_and_group_slugs)
location_ids = cls.selected_location_ids(mobile_user_and_group_slugs)
user_type_filters = []
if HQUserType.ADMIN in user_types:
user_type_filters.append(user_es.admin_users())
if HQUserType.UNKNOWN in user_types:
user_type_filters.append(user_es.unknown_users())
user_type_filters.append(user_es.web_users())
if HQUserType.DEMO_USER in user_types:
user_type_filters.append(user_es.demo_users())
q = user_es.UserES().domain(domain)
if HQUserType.REGISTERED in user_types:
# return all users with selected user_types
user_type_filters.append(user_es.mobile_users())
return q.OR(*user_type_filters)
else:
# return matching user types and exact matches
location_ids = list(SQLLocation.active_objects
.get_locations_and_children(location_ids)
.location_ids())
id_filter = filters.OR(
filters.term("_id", user_ids),
filters.term("__group_ids", group_ids),
user_es.location(location_ids),
)
if user_type_filters:
return q.OR(
id_filter,
filters.OR(*user_type_filters),
)
else:
return q.filter(id_filter)
def deactivate_mobile_workers_by_inactivity(self, domain):
date_of_inactivity = datetime.datetime.utcnow() - datetime.timedelta(
days=self.inactivity_period)
user_query = (UserES().domain(domain).mobile_users().is_active(
).created(lte=date_of_inactivity).filter(
filters.OR(
filters.date_range(
"reporting_metadata.last_submission_for_user.submission_date",
lte=date_of_inactivity),
filters.missing(
"reporting_metadata.last_submission_for_user.submission_date"
))).source(['_id']))
user_ids = [u['_id'] for u in user_query.run().hits]
for chunked_ids in chunked(user_ids, 100):
bulk_auto_deactivate_commcare_users(chunked_ids, domain)
def _get_assigned_cases(checkin_case):
"""
An assigned case is a case for which all of the following are true
Case type patient or contact
Exists in the same domain as the user case
The case property assigned_to_primary_checkin_case_id equals an associated checkin case's case_id
"""
query = (CaseSearchES().domain(checkin_case.domain).filter(
filters.OR(case_type("patient"),
case_type("contact"))).case_property_query(
"assigned_to_primary_checkin_case_id",
checkin_case.case_id))
return [CommCareCase.wrap(flatten_result(hit)) for hit in query.run().hits]
def options(self):
query = CaseES("report_cases").domain(
self.domain).case_type("participant")
user = self.request.couch_user
if user.is_web_user():
owner_ids = [o.lower() for o in user.get_group_ids() if o]
if owner_ids:
query = query.filter(
filters.OR(owner(owner_ids),
user_filter(user._id.lower())))
else:
query = query.user(user._id.lower())
results = query.values("full_name.#value")
return [(case['_id'], case['full_name']['#value']) for case in results]
def get_user_ids_for_user_types(self,
admin,
unknown,
web,
demo,
commtrack,
active=False,
deactivated=False):
"""
referenced from CaseListMixin to fetch user_ids for selected user type
:param admin: if admin users to be included
:param unknown: if unknown users to be included
:param demo: if demo users to be included
:param commtrack: if commtrack users to be included
:return: user_ids for selected user types
"""
from corehq.apps.es import filters, users as user_es
if not any([admin, unknown, web, demo, commtrack, active, deactivated
]):
return []
user_filters = [
filter_ for include, filter_ in [
(admin, user_es.admin_users()),
(unknown, filters.OR(user_es.unknown_users())),
(web, user_es.web_users()),
(demo, user_es.demo_users()),
# Sets the is_active filter status correctly for if either active or deactivated users are selected
(active ^ deactivated, user_es.is_active(active)),
] if include
]
if not user_filters:
return []
query = (user_es.UserES().domain(
self.domain_object.name).OR(*user_filters).remove_default_filter(
'not_deleted').remove_default_filter('active').fields([]))
user_ids = query.run().doc_ids
if commtrack:
user_ids.append("commtrack-system")
if demo:
user_ids.append("demo_user_group_id")
user_ids.append("demo_user")
return user_ids
def get_domains_to_update_es_filter():
"""
Returns ES filter to filter domains that are never updated or
domains that haven't been updated since a week or domains that
have been updated within last week but have new form submissions
in the last day.
"""
last_week = datetime.utcnow() - timedelta(days=7)
more_than_a_week_ago = filters.date_range('cp_last_updated', lt=last_week)
less_than_a_week_ago = filters.date_range('cp_last_updated', gte=last_week)
not_updated = filters.missing('cp_last_updated')
domains_submitted_today = (FormES().submitted(
gte=datetime.utcnow() - timedelta(days=1)).terms_aggregation(
'domain', 'domain').size(0).run().aggregations.domain.keys)
return filters.OR(
not_updated, more_than_a_week_ago,
filters.AND(less_than_a_week_ago, domains_submitted_today))
def test_not_or_rewrite(self):
json_output = {
"query": {
"bool": {
"filter": [
{
"bool": {
"must_not": {
"bool": {
"should": [
{
"term": {
"type": "A"
}
},
{
"term": {
"type": "B"
}
}
]
}
}
}
},
{
"match_all": {}
}
],
"must": {
"match_all": {}
}
}
},
"size": SIZE_LIMIT
}
query = HQESQuery('cases').filter(
filters.NOT(
filters.OR(filters.term('type', 'A'), filters.term('type', 'B'))
)
)
self.checkQuery(query, json_output)
def case_filter(self):
now = datetime.datetime.utcnow()
fromdate = now - timedelta(days=42)
_filters = BaseHNBCReport.base_filters(self)
_filters.append(filters.term('pp_case_filter.#value', '1'))
_filters.append(filters.range(gte=json_format_date(fromdate)))
status = self.request_params.get('PNC_status', '')
if status:
if status == 'On Time':
for i in range(1, 8):
_filters.append(filters.term('case_pp_%s_done.#value' % i, 'yes'))
else:
or_stmt = []
for i in range(1, 8):
or_stmt.append(filters.not_term('case_pp_%s_done.#value' % i, 'yes'))
if or_stmt:
_filters.append(filters.OR(*or_stmt))
return filters.AND(*_filters)
def _get_special_owner_ids(domain, admin, unknown, web, demo, commtrack):
if not any([admin, unknown, web, demo, commtrack]):
return []
user_filters = [
filter_ for include, filter_ in [
(admin, user_es.admin_users()),
(unknown, filters.OR(user_es.unknown_users())),
(web, user_es.web_users()),
(demo, user_es.demo_users()),
] if include
]
owner_ids = (user_es.UserES().domain(domain).OR(*user_filters).get_ids())
if commtrack:
owner_ids.append("commtrack-system")
if demo:
owner_ids.append("demo_user_group_id")
owner_ids.append("demo_user")
return owner_ids
def get_rows(self):
query = case_es.CaseES().domain(self.domain)\
.fields([])\
.opened_range(lte=self.datespan.enddate_utc)\
.case_type(self.default_case_type)
query.index = 'report_cases'
if self.case_status == 'open':
query = query.filter(es_filters.OR(
case_es.is_closed(False),
case_es.closed_range(gte=self.datespan.enddate_utc)
))
elif self.case_status == 'closed':
query = query.filter(case_es.closed_range(lte=self.datespan.enddate_utc))
query = query.owner([user['doc_id'] for user in self.users_matching_filter])
result = query.run()
return [
CommCareCase.wrap(doc)
for doc in iter_docs(CommCareCase.get_db(), result.ids)
]
def get_special_owner_ids(self, admin, unknown, demo, commtrack):
if not any([admin, unknown, demo]):
return []
user_filters = [
filter_ for include, filter_ in [
(admin, user_es.admin_users()),
(unknown,
filters.OR(user_es.unknown_users(), user_es.web_users())),
(demo, user_es.demo_users()),
] if include
]
query = (user_es.UserES().domain(
self.domain).OR(*user_filters).show_inactive().fields([]))
owner_ids = query.run().doc_ids
if commtrack:
owner_ids.append("commtrack-system")
if demo:
owner_ids.append("demo_user_group_id")
owner_ids.append("demo_user")
return owner_ids
def get_rows(self, datespan):
def get_awc_filter(awcs):
return get_nested_terms_filter("awc_name.#value", awcs)
def get_gp_filter(gp):
owner_ids = [
user._id for user in self.users
if getattr(user, 'user_data', {}).get('gp') in self.gp
]
return es_filters.term("owner_id", owner_ids)
def get_block_filter(block):
return es_filters.term("block_name.#value", block.lower())
query = case_es.CaseES().domain(self.domain)\
.fields([])\
.opened_range(lte=self.datespan.enddate_utc)\
.term("type.exact", self.default_case_type)
query.index = 'report_cases'
if self.display_open_cases_only:
query = query.filter(
es_filters.OR(
case_es.is_closed(False),
case_es.closed_range(gte=self.datespan.enddate_utc)))
elif self.display_closed_cases_only:
query = query.filter(
case_es.closed_range(lte=self.datespan.enddate_utc))
if self.awcs:
query = query.filter(get_awc_filter(self.awcs))
elif self.gp:
query = query.filter(get_gp_filter(self.gp))
elif self.block:
query = query.filter(get_block_filter(self.block))
result = query.run()
return map(CommCareCase, iter_docs(CommCareCase.get_db(), result.ids))
if 'debug_case' in self.request.GET:
case = CommCareCase.get(self.request.GET['debug_case'])
if case.domain != DOMAIN:
raise Http404()
return [case]
query = case_es.CaseES().domain(self.domain)\
.exclude_source()\
.opened_range(lte=self.datespan.enddate_utc)\
.case_type(self.default_case_type)
query.index = 'report_cases'
if self.case_status == 'open':
query = query.filter(
es_filters.OR(
case_es.is_closed(False),
case_es.closed_range(gte=self.datespan.enddate_utc)))
elif self.case_status == 'closed':
query = query.filter(
case_es.closed_range(lte=self.datespan.enddate_utc))
query = query.owner(
[user['doc_id'] for user in self.users_matching_filter])
result = query.run()
return [
CommCareCase.wrap(doc)
for doc in iter_docs(CommCareCase.get_db(), result.doc_ids)
]
def to_es_filter(self):
return esfilters.OR(*[f.to_es_filter() for f in self.operand_filters])
def user_es_query(cls, domain, mobile_user_and_group_slugs, request_user):
# The queryset returned by this method is location-safe
q = user_es.UserES().domain(domain, allow_mirroring=True)
if ExpandedMobileWorkerFilter.no_filters_selected(
mobile_user_and_group_slugs):
return q.show_inactive()
user_ids = cls.selected_user_ids(mobile_user_and_group_slugs)
user_types = cls.selected_user_types(mobile_user_and_group_slugs)
group_ids = cls.selected_group_ids(mobile_user_and_group_slugs)
location_ids = cls.selected_location_ids(mobile_user_and_group_slugs)
user_type_filters = []
if HQUserType.ADMIN in user_types:
user_type_filters.append(user_es.admin_users())
if HQUserType.UNKNOWN in user_types:
user_type_filters.append(user_es.unknown_users())
if HQUserType.WEB in user_types:
user_type_filters.append(user_es.web_users())
if HQUserType.DEMO_USER in user_types:
user_type_filters.append(user_es.demo_users())
if HQUserType.ACTIVE in user_types and HQUserType.DEACTIVATED in user_types:
q = q.show_inactive()
elif HQUserType.DEACTIVATED in user_types:
q = q.show_only_inactive()
if not request_user.has_permission(domain, 'access_all_locations'):
cls._verify_users_are_accessible(domain, request_user, user_ids)
return q.OR(
filters.term("_id", user_ids),
user_es.location(
list(
SQLLocation.active_objects.get_locations_and_children(
location_ids).accessible_to_user(
domain, request_user).location_ids())),
)
if HQUserType.ACTIVE in user_types or HQUserType.DEACTIVATED in user_types:
# return all users with selected user_types
user_type_filters.append(user_es.mobile_users())
return q.OR(*user_type_filters)
# return matching user types and exact matches
location_ids = list(
SQLLocation.active_objects.get_locations_and_children(
location_ids).location_ids())
group_id_filter = filters.term("__group_ids", group_ids)
if FILTER_ON_GROUPS_AND_LOCATIONS.enabled(
domain) and group_ids and location_ids:
group_and_location_filter = filters.AND(
group_id_filter,
user_es.location(location_ids),
)
else:
group_and_location_filter = filters.OR(
group_id_filter,
user_es.location(location_ids),
)
id_filter = filters.OR(
filters.term("_id", user_ids),
group_and_location_filter,
)
if user_type_filters:
return q.OR(
id_filter,
group_and_location_filter,
filters.OR(*user_type_filters),
)
return q.filter(id_filter)
