3549825619

Build Type

github

Committed by GitHub

Commit Message

<a href="https://github.com/kobotoolbox/kpi/commit/<a class=hub.com/kobotoolbox/kpi/commit/541412021ebf15563e427d135bf64905fb7f2cc8">541412021<a href="https://github.com/kobotoolbox/kpi/commit/541412021ebf15563e427d135bf64905fb7f2cc8">">Merge </a><a class="double-link" href="https://github.com/kobotoolbox/kpi/commit/<a class="double-link" href="https://github.com/kobotoolbox/kpi/commit/bc2b4d32814a6bf10bf0b9f50c115c81d90a64c1">bc2b4d328</a>">bc2b4d328</a><a href="https://github.com/kobotoolbox/kpi/commit/541412021ebf15563e427d135bf64905fb7f2cc8"> into 5da8eddb6">5da8eddb6</a>

Pull Request Pull Request #4160: Fix 404 when date range parameters are omitted

Run Details

2490 of 4170 branches covered (59.71%)

36 of 36 new or added lines in 2 files covered. (100.0%)

10822 of 14322 relevant lines covered (75.56%)

1.51 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

11.89

/kobo/apps/superuser_stats/tasks.py

# coding: utf-8
from __future__ import annotations

import csv
from celery import shared_task
from collections import Counter
from datetime import datetime
from typing import Union
try:
    from zoneinfo import ZoneInfo
except ImportError:
    from backports.zoneinfo import ZoneInfo

from dateutil.relativedelta import relativedelta
from django.conf import settings
from django.contrib.auth.models import User
from django.core.files.storage import get_storage_class
from django.db.models import Sum, CharField, Count, F, Value, DateField, Q
from django.db.models.functions import Cast, Concat

from hub.models import ExtraUserDetail
from kobo.static_lists import COUNTRIES
from kpi.constants import ASSET_TYPE_SURVEY
from kpi.deployment_backends.kc_access.shadow_models import (
    KobocatXForm,
    KobocatUser,
    KobocatUserProfile,
    ReadOnlyKobocatInstance,
    ReadOnlyKobocatMonthlyXFormSubmissionCounter,
)
from kpi.models.asset import Asset


# Make sure this app is listed in `INSTALLED_APPS`; otherwise, Celery will
# complain that the task is unregistered


@shared_task
def generate_country_report(
    output_filename: str, start_date: str, end_date: str
):

    def get_row_for_country(code_: str, label_: str):
        row_ = []

        xform_ids = Asset.objects.values_list(
            '_deployment_data__backend_response__formid', flat=True
        ).filter(
            Q(settings__country__contains=[{'value': code_}])
            | Q(settings__country__value=code_),
            _deployment_data__active=True,
            _deployment_data__has_key='backend',
            asset_type=ASSET_TYPE_SURVEY,
        )
        # Doing it this way because this report is focused on crises in
        # very specific time frames
        instances_count = ReadOnlyKobocatInstance.objects.filter(
            xform_id__in=list(xform_ids),
            date_created__date__range=(start_date, end_date),
        ).count()

        row_.append(label_)
        row_.append(instances_count)

        return row_

    columns = [
        'Country',
        'Count',
    ]

    default_storage = get_storage_class()()
    with default_storage.open(output_filename, 'w') as output_file:
        writer = csv.writer(output_file)
        writer.writerow(columns)

        for code, label in COUNTRIES:

            try:
                row = get_row_for_country(code, label)
            except Exception as e:
                row = ['!FAILED!', 'Country: {}'.format(label), repr(e)]
            writer.writerow(row)


@shared_task
def generate_continued_usage_report(output_filename: str, end_date: str):
    data = []

    # We need to work with UTC timezone-aware datetime objects
    end_date_obj = datetime.strptime(end_date, '%Y-%m-%d').date()

    twelve_months_time = end_date_obj - relativedelta(years=1)
    six_months_time = end_date_obj - relativedelta(months=6)
    three_months_time = end_date_obj - relativedelta(months=3)

    users = User.objects.filter(
        last_login__date__range=(twelve_months_time, end_date),
    )

    for user in users:
        # twelve months
        assets = user.assets.values('pk', 'date_created').filter(
            date_created__date__range=(twelve_months_time, end_date),
        )
        submissions_count = (
            ReadOnlyKobocatMonthlyXFormSubmissionCounter.objects.annotate(
                date=Cast(
                    Concat(F('year'), Value('-'), F('month'), Value('-'), 1),
                    DateField(),
                )
            ).filter(
                user_id=user.id,
                date__range=(twelve_months_time, end_date),
            )
        )
        twelve_asset_count = assets.aggregate(asset_count=Count('pk'))
        twelve_submission_count = submissions_count.aggregate(
            submissions_count=Sum('counter'),
        )

        # six months
        assets = assets.filter(date_created__gte=six_months_time)
        submissions_count = submissions_count.filter(
            date__gte=six_months_time,
        )
        six_asset_count = assets.aggregate(asset_count=Count('pk'))
        six_submissions_count = submissions_count.aggregate(
            submissions_count=Sum('counter'),
        )

        # three months
        assets = assets.filter(date_created__gte=three_months_time)
        submissions_count = submissions_count.filter(
            date__gte=three_months_time,
        )
        three_asset_count = assets.aggregate(asset_count=Count('pk'))
        three_submissions_count = submissions_count.aggregate(
            submissions_count=Sum('counter'),
        )
        data.append([
            user.username,
            user.date_joined,
            user.last_login,
            three_asset_count['asset_count'] or 0,
            six_asset_count['asset_count'] or 0,
            twelve_asset_count['asset_count'] or 0,
            three_submissions_count['submissions_count'] or 0,
            six_submissions_count['submissions_count'] or 0,
            twelve_submission_count['submissions_count'] or 0,
        ])

    headers = [
        'Username',
        'Date Joined',
        'Last Login',
        'Assets 3m',
        'Assets 6m',
        'Assets 12m',
        'Submissions 3m',
        'Submissions 6m',
        'Submissions 12M',
    ]

    default_storage = get_storage_class()()
    with default_storage.open(output_filename, 'w') as output:
        writer = csv.writer(output)
        writer.writerow(headers)
        writer.writerows(data)


@shared_task
def generate_domain_report(output_filename: str, start_date: str, end_date: str):

    emails = User.objects.filter(
        date_joined__date__range=(start_date, end_date),
    ).values_list('email', flat=True)

    # get a list of the domains
    domains = [
        email.split('@')[1] if '@' in email else 'Invalid domain ' + email
        for email in emails
    ]
    domain_users = Counter(domains)

    # get a count of the assets
    domain_assets = {
        domain:
            Asset.objects.filter(
                owner__email__endswith='@' + domain,
                date_created__date__range=(start_date, end_date),
            ).count()
        for domain in domain_users.keys()
    }

    # get a count of the submissions
    domain_submissions = {
        domain: ReadOnlyKobocatMonthlyXFormSubmissionCounter.objects.annotate(
            date=Cast(
                Concat(F('year'), Value('-'), F('month'), Value('-'), 1),
                DateField(),
            )
        ).filter(
            user__email__endswith='@' + domain,
            date__range=(start_date, end_date),
        ).aggregate(
            Sum('counter')
        )['counter__sum']
        if domain_assets[domain] else 0
        for domain in domain_users.keys()
    }

    # create the CSV file
    columns = ['Email Domain', 'Users', 'Projects', 'Submissions']

    default_storage = get_storage_class()()
    with default_storage.open(output_filename, 'w') as output:
        writer = csv.writer(output)
        writer.writerow(columns)

        for domain, users in domain_users.most_common():
            row = [
                domain,
                users,
                domain_assets[domain],
                domain_submissions[domain]
            ]
            writer.writerow(row)


@shared_task
def generate_forms_count_by_submission_range(output_filename: str):
    # List of submissions count ranges
    ranges = [
        {
            'label': '0',
            'orm_criteria': {'num_of_submissions': 0}
        },
        {
            'label': '1 - 500',
            'orm_criteria': {'num_of_submissions__range': (1, 500)}
        },
        {
            'label': '501 - 1000',
            'orm_criteria': {'num_of_submissions__range': (501, 1000)}
        },
        {
            'label': '1001 - 10000',
            'orm_criteria': {'num_of_submissions__range': (1001, 10000)}
        },
        {
            'label': '10001 - 50000',
            'orm_criteria': {'num_of_submissions__range': (10001, 50000)}
        },
        {
            'label': '50001 and more',
            'orm_criteria': {'num_of_submissions__gte': 50001}
        },
    ]

    # store data for csv
    data = []

    today = datetime.today()
    date_ = today - relativedelta(years=1)
    queryset = KobocatXForm.objects.filter(date_created__gte=date_)

    for r in ranges:
        forms_count = queryset.filter(**r['orm_criteria']).count()
        data.append([r['label'], forms_count])

    headers = ['Range', 'Count']

    # Crate a csv with output filename
    default_storage = get_storage_class()()
    with default_storage.open(output_filename, 'w') as output:
        writer = csv.writer(output)
        writer.writerow(headers)
        writer.writerows(data)


@shared_task
def generate_media_storage_report(output_filename: str):
    attachments = KobocatUserProfile.objects.all().values(
        'user__username',
        'attachment_storage_bytes',
    )

    data = []

    for attachment_count in attachments:
        data.append([
            attachment_count['user__username'],
            attachment_count['attachment_storage_bytes'],
        ])

    headers = ['Username', 'Storage Used (Bytes)']

    default_storage = get_storage_class()()
    with default_storage.open(output_filename, 'w') as output:
        writer = csv.writer(output)
        writer.writerow(headers)
        writer.writerows(data)


@shared_task
def generate_user_count_by_organization(output_filename: str):
    # get users organizations
    organizations = (
        User.objects.filter(extra_details__data__has_key='organization')
        .values('extra_details__data__organization')
        .annotate(total=Count('extra_details__data__organization'))
    ).order_by('extra_details__data__organization')

    no_organizations_count = User.objects.exclude(
        Q(pk=settings.ANONYMOUS_USER_ID)
        | Q(extra_details__data__has_key='organization')
    ).count()

    has_no_organizations = False
    data = []
    for o in organizations:
        if not o['extra_details__data__organization']:
            has_no_organizations = True
            o['extra_details__data__organization'] = 'Unspecified'
            o['total'] += no_organizations_count

        data.append([o['extra_details__data__organization'], o['total']])

    if not has_no_organizations:
        data.insert(0, ['Unspecified', no_organizations_count])

    # write data to a csv file
    columns = ['Organization', 'Count']

    default_storage = get_storage_class()()
    with default_storage.open(output_filename, 'w') as output_file:
        writer = csv.writer(output_file)
        writer.writerow(columns)
        writer.writerows(data)


@shared_task
def generate_user_report(output_filename: str):
    def format_date(d):
        if hasattr(d, 'strftime'):
            return d.strftime('%F')
        else:
            return d

    def get_row_for_user(u: KobocatUser) -> list:
        row_ = []

        try:
            profile = KobocatUserProfile.objects.get(user=u)
        except KobocatUserProfile.DoesNotExist:
            profile = None

        try:
            extra_user_detail = ExtraUserDetail.objects.get(user_id=u.pk)
        except ExtraUserDetail.DoesNotExist:
            extra_details = None
        else:
            extra_details = extra_user_detail.data

        row_.append(u.username)
        row_.append(u.email)
        row_.append(u.pk)
        row_.append(u.first_name)
        row_.append(u.last_name)

        if extra_details:
            name = extra_details.get('name', '')
        else:
            name = ''
        if name:
            row_.append(name)
        elif profile:
            row_.append(profile.name)
        else:
            row_.append('')

        if extra_details:
            organization = extra_details.get('organization', '')
        else:
            organization = ''
        if organization:
            row_.append(organization)
        elif profile:
            row_.append(profile.organization)
        else:
            row_.append('')

        row_.append(KobocatXForm.objects.filter(user=u).count())

        if profile:
            row_.append(profile.num_of_submissions)
        else:
            row_.append(0)

        row_.append(format_date(u.date_joined))
        row_.append(format_date(u.last_login))

        return row_

    CHUNK_SIZE = 1000
    columns = [
        'username',
        'email',
        'pk',
        'first_name',
        'last_name',
        'name',
        'organization',
        'XForm count',
        'num_of_submissions',
        'date_joined',
        'last_login',
    ]

    default_storage = get_storage_class()()
    with default_storage.open(output_filename, 'w') as output_file:
        writer = csv.writer(output_file)
        writer.writerow(columns)
        kc_users = KobocatUser.objects.exclude(
            pk=settings.ANONYMOUS_USER_ID
        ).order_by('pk')
        for kc_user in kc_users.iterator(CHUNK_SIZE):
            try:
                row = get_row_for_user(kc_user)
            except Exception as e:
                row = ['!FAILED!', 'User PK: {}'.format(kc_user.pk), repr(e)]
            writer.writerow(row)


@shared_task
def generate_user_statistics_report(
    output_filename: str,
    start_date: str,
    end_date: str
):
    data = []

    asset_queryset = Asset.objects.values(
        'owner_id', 'owner__extra_details'
    ).filter(
        asset_type=ASSET_TYPE_SURVEY, date_created__date__range=(start_date, end_date)
    )
    records = asset_queryset.annotate(form_count=Count('pk')).order_by()
    forms_count = {
        record['owner_id']: record['form_count'] for record in records
    }

    # Filter the asset_queryset for active deployments
    asset_queryset = asset_queryset.filter(_deployment_data__active=True)
    records = asset_queryset.annotate(deployment_count=Count('pk')).order_by()
    deployment_count = {
        record['owner_id']: record['deployment_count']
        for record in records
    }

    # Get records from SubmissionCounter
    records = (
        ReadOnlyKobocatMonthlyXFormSubmissionCounter.objects.annotate(
            date=Cast(
                Concat(F('year'), Value('-'), F('month'), Value('-'), 1),
                DateField(),
            )
        ).filter(date__range=(start_date, end_date)).values(
            'user_id',
            'user__username',
            'user__date_joined',
        ).order_by('user__date_joined').annotate(count_sum=Sum('counter'))
    )

    def _get_country_value(value: Union[dict, list]) -> str:
        if isinstance(value, dict):
            return value['value']
        elif isinstance(value, list):
            return ', '.join([item['value'] for item in value])

        return value

    for record in records:
        user_details, created = ExtraUserDetail.objects.get_or_create(
            user_id=record['user_id']
        )
        data.append([
            record['user__username'],
            record['user__date_joined'],
            user_details.data.get('organization', ''),
            _get_country_value(user_details.data.get('country', '')),
            record['count_sum'],
            forms_count.get(record['user_id'], 0),
            deployment_count.get(record['user_id'], 0)
        ])

    columns = [
        'Username',
        'Date Joined',
        'Organization',
        'Country',
        'Submissions Count',
        'Forms Count',
        'Deployments Count'
    ]

    default_storage = get_storage_class()()
    with default_storage.open(output_filename, 'w') as output:
        writer = csv.writer(output)
        writer.writerow(columns)
        writer.writerows(data)


@shared_task
def generate_user_details_report(output_filename: str):
    USER_COLS = [
        'id',
        'username',
        'is_superuser',
        'is_staff',
        'date_joined_str',
        'last_login_str',
        'is_active',
        'email',
        'mfa_is_active',
        'asset_count',
    ]
    METADATA_COL = ['metadata']
    EXTRA_DETAILS_COLS = [
        'name',
        'gender',
        'sector',
        'country',
        'city',
        'bio',
        'organization',
        'require_auth',
        'primarySector',
        'organization_website',
        'twitter',
        'linkedin',
        'instagram',
        'metadata',
    ]

    def flatten_metadata_inplace(metadata: dict):
        for k, v in metadata.items():
            if isinstance(v, list) and v:
                metadata[k] = ', '.join([item['value'] for item in v])
            if isinstance(v, dict) and 'value' in v:
                metadata[k] = v['value']

    def get_row_value(
        row: dict, col: str
    ) -> Union[str, int, float, bool, None]:
        val = row.get(col, '')
        # remove any new lines from text
        if isinstance(val, str):
            val = val.replace('\n', '')
        return val

    values = USER_COLS + METADATA_COL
    data = (
        User.objects.exclude(pk=settings.ANONYMOUS_USER_ID)
        .annotate(
            mfa_is_active=F('mfa_methods__is_active'),
            metadata=F('extra_details__data'),
            date_joined_str=Cast('date_joined', CharField()),
            last_login_str=Cast('last_login', CharField()),
            asset_count=Count('assets'),
        )
        .values(*values)
        .order_by('id')
    )

    default_storage = get_storage_class()()
    with default_storage.open(output_filename, 'w') as f:
        columns = USER_COLS + EXTRA_DETAILS_COLS
        writer = csv.writer(f)
        writer.writerow(columns)
        for row in data:
            metadata = row.pop('metadata', {}) or {}
            flatten_metadata_inplace(metadata)
            row.update(metadata)
            flat_row = [get_row_value(row, col) for col in columns]
            writer.writerow(flat_row)

1	# coding: utf-8
2	from __future__ import annotations	2✔
3
4	import csv	2✔
5	from celery import shared_task	2✔
6	from collections import Counter	2✔
7	from datetime import datetime	2✔
8	from typing import Union	2✔
9	try:	2✔
10	from zoneinfo import ZoneInfo	2✔
11	except ImportError:	1✔
12	from backports.zoneinfo import ZoneInfo	1✔
13
14	from dateutil.relativedelta import relativedelta	2✔
15	from django.conf import settings	2✔
16	from django.contrib.auth.models import User	2✔
17	from django.core.files.storage import get_storage_class	2✔
18	from django.db.models import Sum, CharField, Count, F, Value, DateField, Q	2✔
19	from django.db.models.functions import Cast, Concat	2✔
20
21	from hub.models import ExtraUserDetail	2✔
22	from kobo.static_lists import COUNTRIES	2✔
23	from kpi.constants import ASSET_TYPE_SURVEY	2✔
24	from kpi.deployment_backends.kc_access.shadow_models import (	2✔
25	KobocatXForm,
26	KobocatUser,
27	KobocatUserProfile,
28	ReadOnlyKobocatInstance,
29	ReadOnlyKobocatMonthlyXFormSubmissionCounter,
30	)
31	from kpi.models.asset import Asset	2✔
32
33
34	# Make sure this app is listed in `INSTALLED_APPS`; otherwise, Celery will
35	# complain that the task is unregistered
36
37
38	@shared_task	2✔
39	def generate_country_report(	2✔
40	output_filename: str, start_date: str, end_date: str
41	):
42
43	def get_row_for_country(code_: str, label_: str):	×
44	row_ = []	×
45
46	xform_ids = Asset.objects.values_list(	×
47	'_deployment_data__backend_response__formid', flat=True
48	).filter(
49	Q(settings__country__contains=[{'value': code_}])
50	\| Q(settings__country__value=code_),
51	_deployment_data__active=True,
52	_deployment_data__has_key='backend',
53	asset_type=ASSET_TYPE_SURVEY,
54	)
55	# Doing it this way because this report is focused on crises in
56	# very specific time frames
57	instances_count = ReadOnlyKobocatInstance.objects.filter(	×
58	xform_id__in=list(xform_ids),
59	date_created__date__range=(start_date, end_date),
60	).count()
61
62	row_.append(label_)	×
63	row_.append(instances_count)	×
64
65	return row_	×
66
67	columns = [	×
68	'Country',
69	'Count',
70	]
71
72	default_storage = get_storage_class()()	×
73	with default_storage.open(output_filename, 'w') as output_file:	×
74	writer = csv.writer(output_file)	×
75	writer.writerow(columns)	×
76
77	for code, label in COUNTRIES:	×
78
79	try:	×
80	row = get_row_for_country(code, label)	×
81	except Exception as e:	×
82	row = ['!FAILED!', 'Country: {}'.format(label), repr(e)]	×
83	writer.writerow(row)	×
84
85
86	@shared_task	2✔
87	def generate_continued_usage_report(output_filename: str, end_date: str):	2✔
88	data = []	×
89
90	# We need to work with UTC timezone-aware datetime objects
91	end_date_obj = datetime.strptime(end_date, '%Y-%m-%d').date()	×
92
93	twelve_months_time = end_date_obj - relativedelta(years=1)	×
94	six_months_time = end_date_obj - relativedelta(months=6)	×
95	three_months_time = end_date_obj - relativedelta(months=3)	×
96
97	users = User.objects.filter(	×
98	last_login__date__range=(twelve_months_time, end_date),
99	)
100
101	for user in users:	×
102	# twelve months
103	assets = user.assets.values('pk', 'date_created').filter(	×
104	date_created__date__range=(twelve_months_time, end_date),
105	)
106	submissions_count = (	×
107	ReadOnlyKobocatMonthlyXFormSubmissionCounter.objects.annotate(
108	date=Cast(
109	Concat(F('year'), Value('-'), F('month'), Value('-'), 1),
110	DateField(),
111	)
112	).filter(
113	user_id=user.id,
114	date__range=(twelve_months_time, end_date),
115	)
116	)
117	twelve_asset_count = assets.aggregate(asset_count=Count('pk'))	×
118	twelve_submission_count = submissions_count.aggregate(	×
119	submissions_count=Sum('counter'),
120	)
121
122	# six months
123	assets = assets.filter(date_created__gte=six_months_time)	×
124	submissions_count = submissions_count.filter(	×
125	date__gte=six_months_time,
126	)
127	six_asset_count = assets.aggregate(asset_count=Count('pk'))	×
128	six_submissions_count = submissions_count.aggregate(	×
129	submissions_count=Sum('counter'),
130	)
131
132	# three months
133	assets = assets.filter(date_created__gte=three_months_time)	×
134	submissions_count = submissions_count.filter(	×
135	date__gte=three_months_time,
136	)
137	three_asset_count = assets.aggregate(asset_count=Count('pk'))	×
138	three_submissions_count = submissions_count.aggregate(	×
139	submissions_count=Sum('counter'),
140	)
141	data.append([	×
142	user.username,
143	user.date_joined,
144	user.last_login,
145	three_asset_count['asset_count'] or 0,
146	six_asset_count['asset_count'] or 0,
147	twelve_asset_count['asset_count'] or 0,
148	three_submissions_count['submissions_count'] or 0,
149	six_submissions_count['submissions_count'] or 0,
150	twelve_submission_count['submissions_count'] or 0,
151	])
152
153	headers = [	×
154	'Username',
155	'Date Joined',
156	'Last Login',
157	'Assets 3m',
158	'Assets 6m',
159	'Assets 12m',
160	'Submissions 3m',
161	'Submissions 6m',
162	'Submissions 12M',
163	]
164
165	default_storage = get_storage_class()()	×
166	with default_storage.open(output_filename, 'w') as output:	×
167	writer = csv.writer(output)	×
168	writer.writerow(headers)	×
169	writer.writerows(data)	×
170
171
172	@shared_task	2✔
173	def generate_domain_report(output_filename: str, start_date: str, end_date: str):	2✔
174
175	emails = User.objects.filter(	×
176	date_joined__date__range=(start_date, end_date),
177	).values_list('email', flat=True)
178
179	# get a list of the domains
180	domains = [	×
181	email.split('@')[1] if '@' in email else 'Invalid domain ' + email
182	for email in emails
183	]
184	domain_users = Counter(domains)	×
185
186	# get a count of the assets
187	domain_assets = {	×
188	domain:
189	Asset.objects.filter(
190	owner__email__endswith='@' + domain,
191	date_created__date__range=(start_date, end_date),
192	).count()
193	for domain in domain_users.keys()
194	}
195
196	# get a count of the submissions
197	domain_submissions = {	×
198	domain: ReadOnlyKobocatMonthlyXFormSubmissionCounter.objects.annotate(
199	date=Cast(
200	Concat(F('year'), Value('-'), F('month'), Value('-'), 1),
201	DateField(),
202	)
203	).filter(
204	user__email__endswith='@' + domain,
205	date__range=(start_date, end_date),
206	).aggregate(
207	Sum('counter')
208	)['counter__sum']
209	if domain_assets[domain] else 0
210	for domain in domain_users.keys()
211	}
212
213	# create the CSV file
214	columns = ['Email Domain', 'Users', 'Projects', 'Submissions']	×
215
216	default_storage = get_storage_class()()	×
217	with default_storage.open(output_filename, 'w') as output:	×
218	writer = csv.writer(output)	×
219	writer.writerow(columns)	×
220
221	for domain, users in domain_users.most_common():	×
222	row = [	×
223	domain,
224	users,
225	domain_assets[domain],
226	domain_submissions[domain]
227	]
228	writer.writerow(row)	×
229
230
231	@shared_task	2✔
232	def generate_forms_count_by_submission_range(output_filename: str):	2✔
233	# List of submissions count ranges
234	ranges = [	×
235	{
236	'label': '0',
237	'orm_criteria': {'num_of_submissions': 0}
238	},
239	{
240	'label': '1 - 500',
241	'orm_criteria': {'num_of_submissions__range': (1, 500)}
242	},
243	{
244	'label': '501 - 1000',
245	'orm_criteria': {'num_of_submissions__range': (501, 1000)}
246	},
247	{
248	'label': '1001 - 10000',
249	'orm_criteria': {'num_of_submissions__range': (1001, 10000)}
250	},
251	{
252	'label': '10001 - 50000',
253	'orm_criteria': {'num_of_submissions__range': (10001, 50000)}
254	},
255	{
256	'label': '50001 and more',
257	'orm_criteria': {'num_of_submissions__gte': 50001}
258	},
259	]
260
261	# store data for csv
262	data = []	×
263
264	today = datetime.today()	×
265	date_ = today - relativedelta(years=1)	×
266	queryset = KobocatXForm.objects.filter(date_created__gte=date_)	×
267
268	for r in ranges:	×
269	forms_count = queryset.filter(**r['orm_criteria']).count()	×
270	data.append([r['label'], forms_count])	×
271
272	headers = ['Range', 'Count']	×
273
274	# Crate a csv with output filename
275	default_storage = get_storage_class()()	×
276	with default_storage.open(output_filename, 'w') as output:	×
277	writer = csv.writer(output)	×
278	writer.writerow(headers)	×
279	writer.writerows(data)	×
280
281
282	@shared_task	2✔
283	def generate_media_storage_report(output_filename: str):	2✔
284	attachments = KobocatUserProfile.objects.all().values(	×
285	'user__username',
286	'attachment_storage_bytes',
287	)
288
289	data = []	×
290
291	for attachment_count in attachments:	×
292	data.append([	×
293	attachment_count['user__username'],
294	attachment_count['attachment_storage_bytes'],
295	])
296
297	headers = ['Username', 'Storage Used (Bytes)']	×
298
299	default_storage = get_storage_class()()	×
300	with default_storage.open(output_filename, 'w') as output:	×
301	writer = csv.writer(output)	×
302	writer.writerow(headers)	×
303	writer.writerows(data)	×
304
305
306	@shared_task	2✔
307	def generate_user_count_by_organization(output_filename: str):	2✔
308	# get users organizations
309	organizations = (	×
310	User.objects.filter(extra_details__data__has_key='organization')
311	.values('extra_details__data__organization')
312	.annotate(total=Count('extra_details__data__organization'))
313	).order_by('extra_details__data__organization')
314
315	no_organizations_count = User.objects.exclude(	×
316	Q(pk=settings.ANONYMOUS_USER_ID)
317	\| Q(extra_details__data__has_key='organization')
318	).count()
319
320	has_no_organizations = False	×
321	data = []	×
322	for o in organizations:	×
323	if not o['extra_details__data__organization']:	×
324	has_no_organizations = True	×
325	o['extra_details__data__organization'] = 'Unspecified'	×
326	o['total'] += no_organizations_count	×
327
328	data.append([o['extra_details__data__organization'], o['total']])	×
329
330	if not has_no_organizations:	×
331	data.insert(0, ['Unspecified', no_organizations_count])	×
332
333	# write data to a csv file
334	columns = ['Organization', 'Count']	×
335
336	default_storage = get_storage_class()()	×
337	with default_storage.open(output_filename, 'w') as output_file:	×
338	writer = csv.writer(output_file)	×
339	writer.writerow(columns)	×
340	writer.writerows(data)	×
341
342
343	@shared_task	2✔
344	def generate_user_report(output_filename: str):	2✔
345	def format_date(d):	×
346	if hasattr(d, 'strftime'):	×
347	return d.strftime('%F')	×
348	else:
349	return d	×
350
351	def get_row_for_user(u: KobocatUser) -> list:	×
352	row_ = []	×
353
354	try:	×
355	profile = KobocatUserProfile.objects.get(user=u)	×
356	except KobocatUserProfile.DoesNotExist:	×
357	profile = None	×
358
359	try:	×
360	extra_user_detail = ExtraUserDetail.objects.get(user_id=u.pk)	×
361	except ExtraUserDetail.DoesNotExist:	×
362	extra_details = None	×
363	else:
364	extra_details = extra_user_detail.data	×
365
366	row_.append(u.username)	×
367	row_.append(u.email)	×
368	row_.append(u.pk)	×
369	row_.append(u.first_name)	×
370	row_.append(u.last_name)	×
371
372	if extra_details:	×
373	name = extra_details.get('name', '')	×
374	else:
375	name = ''	×
376	if name:	×
377	row_.append(name)	×
378	elif profile:	×
379	row_.append(profile.name)	×
380	else:
381	row_.append('')	×
382
383	if extra_details:	×
384	organization = extra_details.get('organization', '')	×
385	else:
386	organization = ''	×
387	if organization:	×
388	row_.append(organization)	×
389	elif profile:	×
390	row_.append(profile.organization)	×
391	else:
392	row_.append('')	×
393
394	row_.append(KobocatXForm.objects.filter(user=u).count())	×
395
396	if profile:	×
397	row_.append(profile.num_of_submissions)	×
398	else:
399	row_.append(0)	×
400
401	row_.append(format_date(u.date_joined))	×
402	row_.append(format_date(u.last_login))	×
403
404	return row_	×
405
406	CHUNK_SIZE = 1000	×
407	columns = [	×
408	'username',
409	'email',
410	'pk',
411	'first_name',
412	'last_name',
413	'name',
414	'organization',
415	'XForm count',
416	'num_of_submissions',
417	'date_joined',
418	'last_login',
419	]
420
421	default_storage = get_storage_class()()	×
422	with default_storage.open(output_filename, 'w') as output_file:	×
423	writer = csv.writer(output_file)	×
424	writer.writerow(columns)	×
425	kc_users = KobocatUser.objects.exclude(	×
426	pk=settings.ANONYMOUS_USER_ID
427	).order_by('pk')
428	for kc_user in kc_users.iterator(CHUNK_SIZE):	×
429	try:	×
430	row = get_row_for_user(kc_user)	×
431	except Exception as e:	×
432	row = ['!FAILED!', 'User PK: {}'.format(kc_user.pk), repr(e)]	×
433	writer.writerow(row)	×
434
435
436	@shared_task	2✔
437	def generate_user_statistics_report(	2✔
438	output_filename: str,
439	start_date: str,
440	end_date: str
441	):
442	data = []	×
443
444	asset_queryset = Asset.objects.values(	×
445	'owner_id', 'owner__extra_details'
446	).filter(
447	asset_type=ASSET_TYPE_SURVEY, date_created__date__range=(start_date, end_date)
448	)
449	records = asset_queryset.annotate(form_count=Count('pk')).order_by()	×
450	forms_count = {	×
451	record['owner_id']: record['form_count'] for record in records
452	}
453
454	# Filter the asset_queryset for active deployments
455	asset_queryset = asset_queryset.filter(_deployment_data__active=True)	×
456	records = asset_queryset.annotate(deployment_count=Count('pk')).order_by()	×
457	deployment_count = {	×
458	record['owner_id']: record['deployment_count']
459	for record in records
460	}
461
462	# Get records from SubmissionCounter
463	records = (	×
464	ReadOnlyKobocatMonthlyXFormSubmissionCounter.objects.annotate(
465	date=Cast(
466	Concat(F('year'), Value('-'), F('month'), Value('-'), 1),
467	DateField(),
468	)
469	).filter(date__range=(start_date, end_date)).values(
470	'user_id',
471	'user__username',
472	'user__date_joined',
473	).order_by('user__date_joined').annotate(count_sum=Sum('counter'))
474	)
475
476	def _get_country_value(value: Union[dict, list]) -> str:	×
477	if isinstance(value, dict):	×
478	return value['value']	×
479	elif isinstance(value, list):	×
480	return ', '.join([item['value'] for item in value])	×
481
482	return value	×
483
484	for record in records:	×
485	user_details, created = ExtraUserDetail.objects.get_or_create(	×
486	user_id=record['user_id']
487	)
488	data.append([	×
489	record['user__username'],
490	record['user__date_joined'],
491	user_details.data.get('organization', ''),
492	_get_country_value(user_details.data.get('country', '')),
493	record['count_sum'],
494	forms_count.get(record['user_id'], 0),
495	deployment_count.get(record['user_id'], 0)
496	])
497
498	columns = [	×
499	'Username',
500	'Date Joined',
501	'Organization',
502	'Country',
503	'Submissions Count',
504	'Forms Count',
505	'Deployments Count'
506	]
507
508	default_storage = get_storage_class()()	×
509	with default_storage.open(output_filename, 'w') as output:	×
510	writer = csv.writer(output)	×
511	writer.writerow(columns)	×
512	writer.writerows(data)	×
513
514
515	@shared_task	2✔
516	def generate_user_details_report(output_filename: str):	2✔
517	USER_COLS = [	×
518	'id',
519	'username',
520	'is_superuser',
521	'is_staff',
522	'date_joined_str',
523	'last_login_str',
524	'is_active',
525	'email',
526	'mfa_is_active',
527	'asset_count',
528	]
529	METADATA_COL = ['metadata']	×
530	EXTRA_DETAILS_COLS = [	×
531	'name',
532	'gender',
533	'sector',
534	'country',
535	'city',
536	'bio',
537	'organization',
538	'require_auth',
539	'primarySector',
540	'organization_website',
541	'twitter',
542	'linkedin',
543	'instagram',
544	'metadata',
545	]
546
547	def flatten_metadata_inplace(metadata: dict):	×
548	for k, v in metadata.items():	×
549	if isinstance(v, list) and v:	×
550	metadata[k] = ', '.join([item['value'] for item in v])	×
551	if isinstance(v, dict) and 'value' in v:	×
552	metadata[k] = v['value']	×
553
554	def get_row_value(	×
555	row: dict, col: str
556	) -> Union[str, int, float, bool, None]:
557	val = row.get(col, '')	×
558	# remove any new lines from text
559	if isinstance(val, str):	×
560	val = val.replace('\n', '')	×
561	return val	×
562
563	values = USER_COLS + METADATA_COL	×
564	data = (	×
565	User.objects.exclude(pk=settings.ANONYMOUS_USER_ID)
566	.annotate(
567	mfa_is_active=F('mfa_methods__is_active'),
568	metadata=F('extra_details__data'),
569	date_joined_str=Cast('date_joined', CharField()),
570	last_login_str=Cast('last_login', CharField()),
571	asset_count=Count('assets'),
572	)
573	.values(*values)
574	.order_by('id')
575	)
576
577	default_storage = get_storage_class()()	×
578	with default_storage.open(output_filename, 'w') as f:	×
579	columns = USER_COLS + EXTRA_DETAILS_COLS	×
580	writer = csv.writer(f)	×
581	writer.writerow(columns)	×
582	for row in data:	×
583	metadata = row.pop('metadata', {}) or {}	×
584	flatten_metadata_inplace(metadata)	×
585	row.update(metadata)	×
586	flat_row = [get_row_value(row, col) for col in columns]	×
587	writer.writerow(flat_row)	×

kobotoolbox / kpi / 3549825619

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous