def get_commconnect_domain_stats_data(domains, datespan, interval, datefield="date_created", additional_params_es={}):
"""
Returns domains that have used SMS.
Returned based on date domain is created
"""
sms = SMSES().domain(domains).terms_aggregation("domain", "domains").size(0)
if additional_params_es:
sms = add_params_to_query(sms, additional_params_es)
sms_domains = set(sms.run().aggregations.domains.keys)
domains_after_date = (
DomainES()
.in_domains(sms_domains)
.created(gte=datespan.startdate, lte=datespan.enddate)
.date_histogram("date", datefield, interval)
.size(0)
)
histo_data = domains_after_date.run().aggregations.date.as_facet_result()
domains_before_date = DomainES().in_domains(sms_domains).created(lt=datespan.startdate).size(0)
domains_before_date = domains_before_date.run().total
return format_return_data(histo_data, domains_before_date, datespan)
def sms_in_last(domain, days=None):
query = SMSES().domain(domain).size(0)
if days:
query = query.received(date.today() - relativedelta(days=30))
return query.run().total
def get_sms_only_domain_stats_data(domains, datespan, interval, datefield="date_created"):
"""
Returns domains that have only used SMS and not forms.
Returned based on date domain is created
"""
histo_data = []
sms = SMSES().domain(domains).terms_aggregation("domain", "domains").size(0)
forms = FormES().domain(domains).terms_aggregation("domain", "domains").size(0)
sms_domains = set(sms.run().aggregations.domains.keys)
form_domains = set(forms.run().aggregations.domains.keys)
sms_only_domains = sms_domains - form_domains
domains_after_date = (
DomainES()
.in_domains(sms_only_domains)
.created(gte=datespan.startdate, lte=datespan.enddate)
.date_histogram("date", datefield, interval)
.size(0)
)
histo_data = domains_after_date.run().aggregations.date.as_facet_result()
domains_before_date = DomainES().in_domains(sms_only_domains).created(lt=datespan.startdate).size(0)
domains_before_date = domains_before_date.run().total
return format_return_data(histo_data, domains_before_date, datespan)
def get_sms_only_domain_stats_data(domains, datespan, interval, datefield="date_created"):
"""
Returns domains that have only used SMS and not forms.
Returned based on date domain is created
"""
histo_data = []
sms = SMSES().domain(domains).terms_facet("domain", "domains", size=DOMAIN_COUNT_UPPER_BOUND).size(0)
forms = FormES().domain(domains).terms_facet("domain", "domains", size=DOMAIN_COUNT_UPPER_BOUND).size(0)
sms_domains = {x["term"] for x in sms.run().facet("domains", "terms")}
form_domains = {x["term"] for x in forms.run().facet("domains", "terms")}
sms_only_domains = sms_domains - form_domains
domains_after_date = (
DomainES()
.in_domains(sms_only_domains)
.created(gte=datespan.startdate, lte=datespan.enddate)
.date_histogram("date", datefield, interval)
.size(0)
)
histo_data = domains_after_date.run().facet("date", "entries")
domains_before_date = DomainES().in_domains(sms_only_domains).created(lt=datespan.startdate).size(0)
domains_before_date = domains_before_date.run().total
return format_return_data(histo_data, domains_before_date, datespan)
def get_total_clients_data(domains, datespan, interval, datefield="opened_on"):
"""
Returns cases that have used SMS.
Returned based on date case is opened
"""
sms_cases = SMSES().to_commcare_case().domain(domains).terms_aggregation("couch_recipient", "cases").size(0)
cases = sms_cases.run().aggregations.cases.keys
cases_after_date = (
CaseES()
.domain(domains)
.filter({"ids": {"values": cases}})
.opened_range(gte=datespan.startdate, lte=datespan.enddate)
.date_histogram("date", datefield, interval)
.size(0)
)
histo_data = cases_after_date.run().aggregations.date.as_facet_result()
cases_before_date = (
CaseES().domain(domains).filter({"ids": {"values": cases}}).opened_range(lt=datespan.startdate).count()
)
return format_return_data(histo_data, cases_before_date, datespan)
def sms_in_last(domain, days=None):
query = SMSES().domain(domain).size(0)
if days:
query = query.received(date.today() - relativedelta(days=30))
return query.run().total
def get_mobile_workers_data(domains, datespan, interval, datefield="created_on"):
"""
Returns mobile workers that have used SMS.
Returned based on date mobile worker is created
"""
sms_users = SMSES().to_commcare_user().domain(domains).terms_aggregation("couch_recipient", "users").size(0)
users = sms_users.run().aggregations.users.keys
users_after_date = (
UserES()
.domain(domains)
.filter({"ids": {"values": users}})
.mobile_users()
.show_inactive()
.created(gte=datespan.startdate, lte=datespan.enddate)
.date_histogram("date", datefield, interval)
.size(0)
)
histo_data = users_after_date.run().aggregations.date.as_facet_result()
users_before_date = (
UserES()
.domain(domains)
.filter({"ids": {"values": users}})
.mobile_users()
.show_inactive()
.created(lt=datespan.startdate)
.count()
)
return format_return_data(histo_data, users_before_date, datespan)
def get_real_sms_messages_data(domains,
datespan,
interval,
datefield='date',
is_commtrack=False,
additional_params_es={}):
"""
Returns SMS sent in timespan.
Returned based on date SMS was sent
"""
sms_after_date = (SMSES().domain(domains).received(
gte=datespan.startdate,
lte=datespan.enddate).date_histogram('date', datefield,
interval).size(0))
if additional_params_es:
sms_after_date = add_params_to_query(sms_after_date,
additional_params_es)
if is_commtrack:
sms_after_date = sms_after_date.to_commcare_user_or_case()
histo_data = sms_after_date.run().aggregations.date.as_facet_result()
sms_before_date = (SMSES().domain(domains).received(
lt=datespan.startdate).size(0))
if additional_params_es:
sms_before_date = add_params_to_query(sms_before_date,
additional_params_es)
if is_commtrack:
sms_before_date = sms_before_date.to_commcare_user_or_case()
sms_before_date = sms_before_date.run().total
return format_return_data(histo_data, sms_before_date, datespan)
def get_commconnect_domain_stats_data(domains, datespan, interval, datefield="date_created", additional_params_es={}):
"""
Returns domains that have used SMS.
Returned based on date domain is created
"""
sms = SMSES().domain(domains).terms_facet("domain", "domains", size=DOMAIN_COUNT_UPPER_BOUND).size(0)
if additional_params_es:
sms = add_params_to_query(sms, additional_params_es)
sms_domains = {x["term"] for x in sms.run().facet("domains", "terms")}
domains_after_date = (
DomainES()
.in_domains(sms_domains)
.created(gte=datespan.startdate, lte=datespan.enddate)
.date_histogram("date", datefield, interval)
.size(0)
)
histo_data = domains_after_date.run().facet("date", "entries")
domains_before_date = DomainES().in_domains(sms_domains).created(lt=datespan.startdate).size(0)
domains_before_date = domains_before_date.run().total
return format_return_data(histo_data, domains_before_date, datespan)
def get_real_sms_messages_data(
domains, datespan, interval, datefield="date", is_commtrack=False, additional_params_es={}
):
"""
Returns SMS sent in timespan.
Returned based on date SMS was sent
"""
sms_after_date = (
SMSES()
.domain(domains)
.received(gte=datespan.startdate, lte=datespan.enddate)
.date_histogram("date", datefield, interval)
.size(0)
)
if additional_params_es:
sms_after_date = add_params_to_query(sms_after_date, additional_params_es)
if is_commtrack:
sms_after_date = sms_after_date.to_commcare_user_or_case()
histo_data = sms_after_date.run().facet("date", "entries")
sms_before_date = SMSES().domain(domains).received(lt=datespan.startdate).size(0)
if additional_params_es:
sms_before_date = add_params_to_query(sms_before_date, additional_params_es)
if is_commtrack:
sms_before_date = sms_before_date.to_commcare_user_or_case()
sms_before_date = sms_before_date.run().total
return format_return_data(histo_data, sms_before_date, datespan)
def _sms_helper(domain, direction=None, days=None):
query = SMSES().domain(domain).size(0)
if direction:
query = query.direction(direction)
if days:
query = query.received(date.today() - relativedelta(days=30))
return query.run().total
def _sms_helper(domain, direction=None, days=None):
query = SMSES().domain(domain).size(0)
if direction:
query = query.direction(direction)
if days:
query = query.received(date.today() - relativedelta(days=30))
return query.run().total
def active_mobile_users(domain, *args):
"""
Returns the number of mobile users who have submitted a form or SMS in the
last 30 days
"""
now = datetime.utcnow()
then = (now - timedelta(days=30))
user_ids = get_mobile_users(domain)
form_users = set(
FormES()
.domain(domain)
.user_aggregation()
.submitted(gte=then)
.user_id(user_ids)
.size(0)
.run()
.aggregations.user.keys
)
sms_users = set(
SMSES()
.incoming_messages()
.user_aggregation()
.to_commcare_user()
.domain(domain)
.received(gte=then)
.size(0)
.run()
.aggregations.user.keys
)
num_users = len(form_users | sms_users)
return num_users if 'inactive' not in args else len(user_ids) - num_users
def get_submitted_users():
real_form_users = {
user_count['term'] for user_count in (
FormES()
.user_facet(size=USER_COUNT_UPPER_BOUND)
.size(0)
.run()
.facets.user.result
)
}
real_sms_users = {
user_count['term'] for user_count in (
SMSES()
.terms_facet(
'couch_recipient', 'user', USER_COUNT_UPPER_BOUND
)
.incoming_messages()
.size(0)
.run()
.facets.user.result
)
}
return real_form_users | real_sms_users
def get_mobile_workers_data(domains, datespan, interval,
datefield='created_on'):
"""
Returns mobile workers that have used SMS.
Returned based on date mobile worker is created
"""
sms_users = (SMSES()
.to_commcare_user()
.domain(domains)
.terms_facet('couch_recipient', 'users', USER_COUNT_UPPER_BOUND)
.size(0))
users = [u['term'] for u in sms_users.run().facet('users', 'terms')]
users_after_date = (UserES()
.domain(domains)
.filter({"ids": {"values": users}})
.mobile_users()
.show_inactive()
.created(gte=datespan.startdate, lte=datespan.enddate)
.date_histogram('date', datefield, interval)
.size(0))
histo_data = users_after_date.run().facet('date', 'entries')
users_before_date = (UserES()
.domain(domains)
.filter({"ids": {"values": users}})
.mobile_users()
.show_inactive()
.created(lt=datespan.startdate)
.size(0)).run().total
return format_return_data(histo_data, users_before_date, datespan)
def test_processed_or_incoming(self):
json_output = {
"query": {
"filtered": {
"filter": {
"and": [
{"term": {"domain.exact": "demo"}},
{
"not": {
"and": (
{"term": {"direction": "o"}},
{"term": {"processed": False}},
)
}
},
{"match_all": {}},
]
},
"query": {"match_all": {}}
}
},
"size": SIZE_LIMIT
}
query = SMSES().domain('demo').processed_or_incoming_messages()
self.checkQuery(query, json_output)
def active_mobile_users(domain, *args):
"""
Returns the number of mobile users who have submitted a form or SMS in the
last 30 days
"""
now = datetime.utcnow()
then = (now - timedelta(days=30))
user_ids = get_mobile_users(domain)
form_users = {
q['term']
for q in (FormES().domain(domain).user_facet(
size=USER_COUNT_UPPER_BOUND).submitted(
gte=then).user_id(user_ids).size(0).run().facets.user.result)
}
sms_users = {
q['term']
for q in (SMSES().user_facet(
size=USER_COUNT_UPPER_BOUND).to_commcare_user().domain(
domain).received(gte=then).size(0).run().facets.user.result)
}
num_users = len(form_users | sms_users)
return num_users if 'inactive' not in args else len(user_ids) - num_users
def test_sql_sms_pillow(self, mock_do_publish):
mock_do_publish.return_value = True
consumer = get_test_kafka_consumer(topics.SMS)
# get the seq id before the change is published
kafka_seq = get_topic_offset(topics.SMS)
# create an sms
sms_and_dict = create_fake_sms(self.domain)
self.sms = sms_and_dict.sms
sms_json = self._to_json(sms_and_dict.sms_dict, self.sms)
# test serialization
self.assertEqual(self.sms.to_json(), sms_json)
# publish the change and confirm it gets to kafka
self.sms.publish_change()
message = next(consumer)
change_meta = change_meta_from_kafka_message(message.value)
self.assertEqual(self.sms.couch_id, change_meta.document_id)
self.assertEqual(self.domain, change_meta.domain)
# send to elasticsearch
sms_pillow = get_sql_sms_pillow('SqlSMSPillow')
sms_pillow.process_changes(since=kafka_seq, forever=False)
self.elasticsearch.indices.refresh(SMS_INDEX_INFO.index)
# confirm change made it to elasticserach
results = SMSES().run()
self.assertEqual(1, results.total)
sms_doc = results.hits[0]
self.assertEqual(sms_doc, sms_json)
def get_commconnect_domain_stats_data(domains,
datespan,
interval,
datefield='date_created',
additional_params_es={}):
"""
Returns domains that have used SMS.
Returned based on date domain is created
"""
sms = (SMSES().domain(domains).terms_aggregation('domain',
'domains').size(0))
if additional_params_es:
sms = add_params_to_query(sms, additional_params_es)
sms_domains = set(sms.run().aggregations.domains.keys)
domains_after_date = (DomainES().in_domains(sms_domains).created(
gte=datespan.startdate,
lte=datespan.enddate).date_histogram('date', datefield,
interval).size(0))
histo_data = domains_after_date.run().aggregations.date.as_facet_result()
domains_before_date = (DomainES().in_domains(sms_domains).created(
lt=datespan.startdate).size(0))
domains_before_date = domains_before_date.run().total
return format_return_data(histo_data, domains_before_date, datespan)
def get_user_type_filters(histo_type, user_type_mobile, require_submissions):
result = {'terms': {}}
if histo_type == 'forms':
result['terms']["form.meta.userID"] = list(
get_user_ids(user_type_mobile))
elif histo_type == 'users_all':
existing_users = get_user_ids(user_type_mobile)
if require_submissions:
LARGE_NUMBER = 1000 * 1000 * 10
real_form_users = {
user_count['term']
for user_count in (
FormES().user_facet().size(0).run().facets.user.result)
}
real_sms_users = {
user_count['term']
for user_count in (SMSES().terms_facet(
'couch_recipient', 'user', LARGE_NUMBER).incoming_messages(
).size(0).run().facets.user.result)
}
filtered_real_users = (existing_users &
(real_form_users | real_sms_users))
else:
filtered_real_users = existing_users
result['terms']['_id'] = list(filtered_real_users)
return result
def get_mobile_workers_data(domains,
datespan,
interval,
datefield='created_on'):
"""
Returns mobile workers that have used SMS.
Returned based on date mobile worker is created
"""
sms_users = (SMSES().to_commcare_user().domain(domains).terms_aggregation(
'couch_recipient', 'users').size(0))
users = sms_users.run().aggregations.users.keys
users_after_date = (UserES().domain(domains).filter({
"ids": {
"values": users
}
}).mobile_users().show_inactive().created(
gte=datespan.startdate,
lte=datespan.enddate).date_histogram('date', datefield,
interval).size(0))
histo_data = users_after_date.run().aggregations.date.as_facet_result()
users_before_date = (UserES().domain(domains).filter({
"ids": {
"values": users
}
}).mobile_users().show_inactive().created(
lt=datespan.startdate).size(0)).run().total
return format_return_data(histo_data, users_before_date, datespan)
def get_sms_only_domain_stats_data(domains,
datespan,
interval,
datefield='date_created'):
"""
Returns domains that have only used SMS and not forms.
Returned based on date domain is created
"""
histo_data = []
sms = (SMSES().domain(domains).terms_facet('domain',
'domains',
size=LARGE_ES_NUMBER).size(0))
forms = (FormES().domain(domains).terms_facet(
'domain', 'domains', size=LARGE_ES_NUMBER).size(0))
sms_domains = {x['term'] for x in sms.run().facet('domains', 'terms')}
form_domains = {x['term'] for x in forms.run().facet('domains', 'terms')}
sms_only_domains = sms_domains - form_domains
domains_after_date = (DomainES().in_domains(sms_only_domains).created(
gte=datespan.startdate,
lte=datespan.enddate).date_histogram('date', datefield,
interval).size(0))
histo_data = domains_after_date.run().facet('date', 'entries')
domains_before_date = (DomainES().in_domains(sms_only_domains).created(
lt=datespan.startdate).size(0))
domains_before_date = domains_before_date.run().total
return format_return_data(histo_data, domains_before_date, datespan)
def get_commconnect_domain_stats_data(domains,
datespan,
interval,
datefield='date_created',
additional_params_es={}):
"""
Returns domains that have used SMS.
Returned based on date domain is created
"""
sms = (SMSES().domain(domains).terms_facet('domain',
'domains',
size=LARGE_ES_NUMBER).size(0))
if additional_params_es:
sms = add_params_to_query(sms, additional_params_es)
sms_domains = {x['term'] for x in sms.run().facet('domains', 'terms')}
domains_after_date = (DomainES().in_domains(sms_domains).created(
gte=datespan.startdate,
lte=datespan.enddate).date_histogram('date', datefield,
interval).size(0))
histo_data = domains_after_date.run().facet('date', 'entries')
domains_before_date = (DomainES().in_domains(sms_domains).created(
lt=datespan.startdate).size(0))
domains_before_date = domains_before_date.run().total
return format_return_data(histo_data, domains_before_date, datespan)
def get_total_clients_data(domains, datespan, interval, datefield='opened_on'):
"""
Returns cases that have used SMS.
Returned based on date case is opened
"""
sms_cases = (SMSES().to_commcare_case().domain(domains).terms_facet(
'couch_recipient', 'cases', size=LARGE_ES_NUMBER).size(0))
cases = [u['term'] for u in sms_cases.run().facet('cases', 'terms')]
cases_after_date = (CaseES().domain(domains).filter({
"ids": {
"values": cases
}
}).opened_range(gte=datespan.startdate,
lte=datespan.enddate).date_histogram(
'date', datefield, interval).size(0))
histo_data = cases_after_date.run().facet('date', 'entries')
cases_before_date = (CaseES().domain(domains).filter({
"ids": {
"values": cases
}
}).opened_range(lt=datespan.startdate).size(0)).run().total
return format_return_data(histo_data, cases_before_date, datespan)
def active_mobile_users(domain, start, end, *args):
"""
Returns the number of mobile users who have submitted a form or SMS in the
time specified
"""
user_ids = get_mobile_users(domain.name)
form_users = (FormES(es_instance_alias=ES_EXPORT_INSTANCE)
.domain(domain.name)
.user_aggregation()
.submitted(gte=start, lt=end)
.user_id(user_ids)
.size(0)
.run()
.aggregations.user.counts_by_bucket())
sms_users = set(
SMSES(es_instance_alias=ES_EXPORT_INSTANCE)
.incoming_messages()
.user_aggregation()
.to_commcare_user()
.domain(domain.name)
.received(gte=start, lt=end)
.size(0)
.run()
.aggregations.user.keys
)
return set(user_ids), form_users, sms_users
def get_sms_only_domain_stats_data(domains,
datespan,
interval,
datefield='date_created'):
"""
Returns domains that have only used SMS and not forms.
Returned based on date domain is created
"""
histo_data = []
sms = (SMSES().domain(domains).terms_aggregation('domain',
'domains').size(0))
forms = (FormES().domain(domains).terms_aggregation('domain',
'domains').size(0))
sms_domains = set(sms.run().aggregations.domains.keys)
form_domains = set(forms.run().aggregations.domains.keys)
sms_only_domains = sms_domains - form_domains
domains_after_date = (DomainES().in_domains(sms_only_domains).created(
gte=datespan.startdate,
lte=datespan.enddate).date_histogram('date', datefield,
interval).size(0))
histo_data = domains_after_date.run().aggregations.date.as_facet_result()
domains_before_date = (DomainES().in_domains(sms_only_domains).created(
lt=datespan.startdate).size(0))
domains_before_date = domains_before_date.run().total
return format_return_data(histo_data, domains_before_date, datespan)
def get_total_clients_data(domains, datespan, interval, datefield='opened_on'):
"""
Returns cases that have used SMS.
Returned based on date case is opened
"""
sms_cases = (SMSES().to_commcare_case().domain(domains).terms_aggregation(
'couch_recipient', 'cases').size(0))
cases = sms_cases.run().aggregations.cases.keys
cases_after_date = (CaseES().domain(domains).filter({
"ids": {
"values": cases
}
}).opened_range(gte=datespan.startdate,
lte=datespan.enddate).date_histogram(
'date', datefield, interval).size(0))
histo_data = cases_after_date.run().aggregations.date.as_facet_result()
cases_before_date = (CaseES().domain(domains).filter({
"ids": {
"values": cases
}
}).opened_range(lt=datespan.startdate).size(0)).run().total
return format_return_data(histo_data, cases_before_date, datespan)
def get_sms_query(begin,
end,
facet_name,
facet_terms,
domains,
size=LARGE_ES_NUMBER):
return (SMSES().domain(domains).received(gte=begin, lte=end).terms_facet(
facet_terms, facet_name, size).size(0))
def get_submitted_users():
real_form_users = set(
FormES().user_aggregation().size(0).run().aggregations.user.keys)
real_sms_users = set(SMSES().terms_aggregation(
'couch_recipient',
'user').incoming_messages().size(0).run().aggregations.user.keys)
return real_form_users | real_sms_users
def _sms_helper(domain, direction=None, days=None):
assert direction in (INCOMING, OUTGOING, None), repr(direction)
query = SMSES().domain(domain).size(0)
if direction == INCOMING:
query = query.incoming_messages()
elif direction == OUTGOING:
query = query.outgoing_messages()
if days:
query = query.received(date.today() - relativedelta(days=days))
return query.run().total
def get_sms_count(domain, direction=None, days=None):
"""
:param domain: domain name
:param direction: can specify INCOMING or OUTGOING, or None to retrieve both
:param days: only return count of sms docs from the past N days
:return: number of sms docs fetched based on query parameters specified
"""
assert direction in (INCOMING, OUTGOING, None), repr(direction)
query = SMSES().domain(domain).size(0)
if direction == INCOMING:
query = query.incoming_messages()
elif direction == OUTGOING:
query = query.outgoing_messages()
if days:
days = int(days) if isinstance(days, str) else days
query = query.received(date.today() - relativedelta(days=days))
return query.run().total
def test_sms_count(self):
sms_doc = self.create_sms_in_es(self.domain.name, INCOMING)
self.addCleanup(self.delete_sms_in_es, sms_doc)
self.assertEqual(SMSES().count(), 1)
self.assertEqual(sms(self.domain.name, INCOMING), 1)
self.assertEqual(sms(self.domain.name, OUTGOING), 0)
def test_sms(self):
self.assertEqual(SMSES().count(), 1)
self.assertEqual(sms(self.domain.name, INCOMING), 1)
self.assertEqual(sms(self.domain.name, OUTGOING), 0)
def get_sms_export_base_query(domain):
return (SMSES(es_instance_alias=ES_EXPORT_INSTANCE).domain(
domain).processed_or_incoming_messages().sort("date"))
def get_sms_query(begin, end, facet_name, facet_terms, domains):
return (SMSES().domain(domains).received(
gte=begin, lte=end).terms_aggregation(facet_terms, facet_name).size(0))
def get_sms_export_base_query(domain):
return (SMSES(for_export=True).domain(
domain).processed_or_incoming_messages().sort("date"))
