Coverage for cc_modules/cc_taskschedulereports.py: 36%
132 statements
« prev ^ index » next coverage.py v7.9.2, created at 2025-07-15 14:23 +0100
1"""
2camcops_server/cc_modules/cc_taskschedulereports.py
4===============================================================================
6 Copyright (C) 2012, University of Cambridge, Department of Psychiatry.
7 Created by Rudolf Cardinal (rnc1001@cam.ac.uk).
9 This file is part of CamCOPS.
11 CamCOPS is free software: you can redistribute it and/or modify
12 it under the terms of the GNU General Public License as published by
13 the Free Software Foundation, either version 3 of the License, or
14 (at your option) any later version.
16 CamCOPS is distributed in the hope that it will be useful,
17 but WITHOUT ANY WARRANTY; without even the implied warranty of
18 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 GNU General Public License for more details.
21 You should have received a copy of the GNU General Public License
22 along with CamCOPS. If not, see <https://www.gnu.org/licenses/>.
24===============================================================================
26**Server reports on CamCOPS scheduled tasks.**
28"""
30from typing import List, Type, TYPE_CHECKING, Union
32from cardinal_pythonlib.classes import classproperty
33from cardinal_pythonlib.sqlalchemy.core_query import (
34 get_rows_fieldnames_from_select,
35)
36from cardinal_pythonlib.sqlalchemy.sqlfunc import extract_month, extract_year
37from sqlalchemy import cast, Integer
38from sqlalchemy.sql.elements import ColumnElement
39from sqlalchemy.sql.expression import (
40 FromClause,
41 Select,
42 desc,
43 func,
44 literal,
45 select,
46 union_all,
47)
48from sqlalchemy.sql.functions import FunctionElement
50from camcops_server.cc_modules.cc_device import Device
51from camcops_server.cc_modules.cc_sqla_coltypes import (
52 isotzdatetime_to_utcdatetime,
53)
54from camcops_server.cc_modules.cc_email import Email
55from camcops_server.cc_modules.cc_forms import ReportParamSchema
56from camcops_server.cc_modules.cc_group import Group
57from camcops_server.cc_modules.cc_patient import Patient
58from camcops_server.cc_modules.cc_pyramid import ViewParam
59from camcops_server.cc_modules.cc_report import Report, PlainReportType
60from camcops_server.cc_modules.cc_reportschema import (
61 ByYearSelector,
62 ByMonthSelector,
63 DEFAULT_BY_MONTH,
64 DEFAULT_BY_YEAR,
65)
66from camcops_server.cc_modules.cc_taskschedule import (
67 PatientTaskSchedule,
68 PatientTaskScheduleEmail,
69 TaskSchedule,
70 TaskScheduleItem,
71)
73if TYPE_CHECKING:
74 from typing import Any
76 # noinspection PyProtectedMember
77 from sqlalchemy.sql.expression import Visitable
78 from camcops_server.cc_modules.cc_request import CamcopsRequest
81class TaskAssignmentReportSchema(ReportParamSchema):
82 by_year = ByYearSelector() # must match ViewParam.BY_YEAR
83 by_month = ByMonthSelector() # must match ViewParam.BY_MONTH
86# =============================================================================
87# Reports
88# =============================================================================
91class TaskAssignmentReport(Report):
92 """
93 Report to count server-side patients and their assigned tasks.
95 We don't currently record when a patient was assigned to a task schedule;
96 we only record when the patient registered themselves on the app, along
97 with any tasks they completed. This report provides:
99 - Number of server-side patients created (by month or year)
100 - Number of tasks assigned to registered patients (by month or year)
101 - Number of tasks assigned to unregistered patients (all time)
102 - Number of emails sent to patients (by month or year)
104 This along with the task count report should give good data on completed
105 and outstanding tasks.
107 """
109 template_name = "task_assignment_report.mako"
111 label_year = "year"
112 label_month = "month"
113 label_group_id = "group_id"
114 label_group_name = "group_name"
115 label_schedule_id = "schedule_id"
116 label_schedule_name = "schedule_name"
117 label_tasks = "tasks_assigned"
118 label_patients_created = "patients_created"
119 label_emails_sent = "emails_sent"
121 # noinspection PyMethodParameters
122 @classproperty
123 def report_id(cls) -> str:
124 return "taskassignment"
126 @classmethod
127 def title(cls, req: "CamcopsRequest") -> str:
128 _ = req.gettext
129 return _(
130 "(Server) Count server-created patients and their assigned tasks"
131 )
133 # noinspection PyMethodParameters
134 @classproperty
135 def superuser_only(cls) -> bool:
136 return False
138 @staticmethod
139 def get_paramform_schema_class() -> Type["ReportParamSchema"]:
140 return TaskAssignmentReportSchema
142 @classmethod
143 def get_specific_http_query_keys(cls) -> List[str]:
144 return [
145 ViewParam.BY_YEAR,
146 ViewParam.BY_MONTH,
147 ]
149 def get_rows_colnames(self, req: "CamcopsRequest") -> PlainReportType:
150 by_year = req.get_bool_param(ViewParam.BY_YEAR, DEFAULT_BY_YEAR)
151 by_month = req.get_bool_param(ViewParam.BY_MONTH, DEFAULT_BY_MONTH)
153 tasks_query = self._get_tasks_query(req, by_year, by_month)
154 tasks_query.alias("tasks_data")
155 patients_query = self._get_created_patients_query(
156 req, by_year, by_month
157 )
158 patients_query.alias("patients_data")
159 emails_query = self._get_emails_sent_query(req, by_year, by_month)
160 emails_query.alias("emails_data")
162 selectors = (
163 []
164 ) # type: List[Union[ColumnElement[Any], FromClause, int]]
165 sorters = [] # type: List[Union[str, bool, Visitable, None]]
166 groupers = [
167 self.label_group_id,
168 self.label_schedule_id,
169 self.label_group_name,
170 self.label_schedule_name,
171 ] # type: List[Union[str, bool, Visitable, None]]
173 # Merge the three queries.
174 all_data = union_all(tasks_query, patients_query, emails_query).alias(
175 "all_data"
176 )
178 if by_year:
179 selectors.append(all_data.c.year)
180 groupers.append(all_data.c.year)
181 sorters.append(desc(all_data.c.year))
183 if by_month:
184 selectors.append(all_data.c.month)
185 groupers.append(all_data.c.month)
186 sorters.append(desc(all_data.c.month))
188 sorters += [all_data.c.group_id, all_data.c.schedule_id]
189 selectors += [
190 all_data.c.group_name,
191 all_data.c.schedule_name,
192 func.sum(all_data.c.patients_created).label(
193 self.label_patients_created
194 ),
195 func.sum(all_data.c.tasks_assigned).label(self.label_tasks),
196 func.sum(all_data.c.emails_sent).label(self.label_emails_sent),
197 ]
198 query = (
199 select(*selectors) # type: ignore[arg-type]
200 .select_from(all_data)
201 .group_by(*groupers) # type: ignore[arg-type]
202 .order_by(*sorters) # type: ignore[arg-type]
203 )
205 rows, colnames = get_rows_fieldnames_from_select(req.dbsession, query)
207 return PlainReportType(rows=rows, column_names=colnames)
209 def _get_tasks_query(
210 self, req: "CamcopsRequest", by_year: bool, by_month: bool
211 ) -> Select:
212 """
213 Returns a query of the number of tasks assigned to (scheduled for)
214 patients created on the server (in a way compatible with being merged
215 with other queries in this report).
216 """
218 pts = PatientTaskSchedule.__table__
219 ts = TaskSchedule.__table__
220 tsi = TaskScheduleItem.__table__
221 group = Group.__table__
223 tables = (
224 pts.join(ts, pts.c.schedule_id == ts.c.id)
225 .join(tsi, tsi.c.schedule_id == ts.c.id)
226 .join(group, ts.c.group_id == group.c.id)
227 )
229 date_column = isotzdatetime_to_utcdatetime(pts.c.start_datetime)
230 # Order must be consistent across queries
231 count_selectors = [
232 literal(0).label(self.label_patients_created),
233 func.count().label(self.label_tasks),
234 literal(0).label(self.label_emails_sent),
235 ]
237 query = self._build_query(
238 req, tables, by_year, by_month, date_column, count_selectors # type: ignore[arg-type] # noqa: E501
239 )
241 return query
243 def _get_created_patients_query(
244 self, req: "CamcopsRequest", by_year: bool, by_month: bool
245 ) -> Select:
246 """
247 Returns a query of the number of patients created on the server (in a
248 way compatible with being merged with other queries in this report).
249 """
250 server_device = Device.get_server_device(req.dbsession)
252 pts = PatientTaskSchedule.__table__
253 ts = TaskSchedule.__table__
254 group = Group.__table__
255 patient = Patient.__table__
257 # noinspection PyProtectedMember
258 tables = (
259 pts.join(ts, pts.c.schedule_id == ts.c.id)
260 .join(group, ts.c.group_id == group.c.id)
261 .join(patient, pts.c.patient_pk == patient.c._pk)
262 )
264 # noinspection PyProtectedMember
265 date_column = isotzdatetime_to_utcdatetime(patient.c._when_added_exact)
266 # Order must be consistent across queries
267 count_selectors = [
268 func.count().label(self.label_patients_created),
269 literal(0).label(self.label_tasks),
270 literal(0).label(self.label_emails_sent),
271 ]
273 # noinspection PyProtectedMember,PyTypeChecker
274 return self._build_query(
275 req, tables, by_year, by_month, date_column, count_selectors # type: ignore[arg-type] # noqa: E501
276 ).where(patient.c._device_id == server_device.id)
278 def _get_emails_sent_query(
279 self, req: "CamcopsRequest", by_year: bool, by_month: bool
280 ) -> Select:
281 """
282 Returns a query of the number of e-mails sent to patients created on
283 the server (in a way compatible with being merged with other queries in
284 this report).
285 """
287 pts = PatientTaskSchedule.__table__
288 ts = TaskSchedule.__table__
289 group = Group.__table__
290 patient = Patient.__table__
291 ptse = PatientTaskScheduleEmail.__table__
292 email = Email.__table__
294 # noinspection PyProtectedMember
295 tables = (
296 ptse.join(pts, ptse.c.patient_task_schedule_id == pts.c.id)
297 .join(ts, pts.c.schedule_id == ts.c.id)
298 .join(group, ts.c.group_id == group.c.id)
299 .join(patient, pts.c.patient_pk == patient.c._pk)
300 .join(email, ptse.c.email_id == email.c.id)
301 )
303 date_column = email.c.sent_at_utc
304 # Order must be consistent across queries
305 count_selectors = [
306 literal(0).label(self.label_patients_created),
307 literal(0).label(self.label_tasks),
308 func.count().label(self.label_emails_sent),
309 ]
311 # noinspection PyTypeChecker
312 return self._build_query(
313 req, tables, by_year, by_month, date_column, count_selectors # type: ignore[arg-type] # noqa: E501
314 ).where(
315 email.c.sent == True # noqa: E712
316 )
318 def _build_query(
319 self,
320 req: "CamcopsRequest",
321 tables: FromClause,
322 by_year: bool,
323 by_month: bool,
324 date_column: FunctionElement,
325 count_selectors: List[FunctionElement],
326 ) -> Select:
327 assert req.user is not None # For type checker
329 group_ids = req.user.ids_of_groups_user_may_report_on
330 superuser = req.user.superuser
332 ts = TaskSchedule.__table__
333 group = Group.__table__
335 groupers = [
336 group.c.id,
337 ts.c.id,
338 ] # type: List[Union[str, bool, Visitable, None]]
340 selectors = (
341 []
342 ) # type: List[Union[ColumnElement[Any], FromClause, int]]
344 if by_year:
345 selectors.append(
346 cast( # Necessary for SQLite tests
347 extract_year(date_column),
348 Integer(),
349 ).label(self.label_year)
350 )
351 groupers.append(self.label_year)
353 if by_month:
354 selectors.append(
355 cast( # Necessary for SQLite tests
356 extract_month(date_column),
357 Integer(),
358 ).label(self.label_month)
359 )
360 groupers.append(self.label_month)
361 # Regardless:
362 selectors.append(group.c.id.label(self.label_group_id))
363 selectors.append(group.c.name.label(self.label_group_name))
364 selectors.append(ts.c.id.label(self.label_schedule_id))
365 selectors.append(ts.c.name.label(self.label_schedule_name))
366 selectors += count_selectors
367 # noinspection PyUnresolvedReferences
368 query = select(*selectors).select_from(tables).group_by(*groupers) # type: ignore[arg-type] # noqa: E501
369 if not superuser:
370 # Restrict to accessible groups
371 # noinspection PyProtectedMember
372 query = query.where(group.c.id.in_(group_ids))
374 return query