Coverage for cc_modules/cc_taskreports.py : 40%

Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1#!/usr/bin/env python
3"""
4camcops_server/cc_modules/cc_taskreports.py
6===============================================================================
8 Copyright (C) 2012-2020 Rudolf Cardinal (rudolf@pobox.com).
10 This file is part of CamCOPS.
12 CamCOPS is free software: you can redistribute it and/or modify
13 it under the terms of the GNU General Public License as published by
14 the Free Software Foundation, either version 3 of the License, or
15 (at your option) any later version.
17 CamCOPS is distributed in the hope that it will be useful,
18 but WITHOUT ANY WARRANTY; without even the implied warranty of
19 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 GNU General Public License for more details.
22 You should have received a copy of the GNU General Public License
23 along with CamCOPS. If not, see <https://www.gnu.org/licenses/>.
25===============================================================================
27**Server reports on CamCOPS tasks.**
29"""
31from collections import Counter
32from typing import Any, List, Sequence, Type, TYPE_CHECKING
34from cardinal_pythonlib.classes import classproperty
35from cardinal_pythonlib.sqlalchemy.orm_query import get_rows_fieldnames_from_query # noqa
36from cardinal_pythonlib.sqlalchemy.sqlfunc import extract_month, extract_year
37from sqlalchemy.sql.expression import and_, desc, func, literal, select
39from camcops_server.cc_modules.cc_sqla_coltypes import (
40 isotzdatetime_to_utcdatetime,
41)
42from camcops_server.cc_modules.cc_forms import (
43 ReportParamSchema,
44 ViaIndexSelector,
45)
46from camcops_server.cc_modules.cc_pyramid import ViewParam
47from camcops_server.cc_modules.cc_report import Report, PlainReportType
48from camcops_server.cc_modules.cc_task import Task
49from camcops_server.cc_modules.cc_taskindex import TaskIndexEntry
50from camcops_server.cc_modules.cc_user import User
52if TYPE_CHECKING:
53 from camcops_server.cc_modules.cc_request import CamcopsRequest
56# =============================================================================
57# Reports
58# =============================================================================
60class TaskCountReportSchema(ReportParamSchema):
61 via_index = ViaIndexSelector() # must match ViewParam.VIA_INDEX
64class TaskCountReport(Report):
65 """
66 Report to count task instances.
67 """
69 # noinspection PyMethodParameters
70 @classproperty
71 def report_id(cls) -> str:
72 return "taskcount"
74 @classmethod
75 def title(cls, req: "CamcopsRequest") -> str:
76 _ = req.gettext
77 return _(
78 "(Server) Count current task instances, by creation month and "
79 "task type"
80 )
82 # noinspection PyMethodParameters
83 @classproperty
84 def superuser_only(cls) -> bool:
85 return False
87 @staticmethod
88 def get_paramform_schema_class() -> Type["ReportParamSchema"]:
89 return TaskCountReportSchema
91 @classmethod
92 def get_specific_http_query_keys(cls) -> List[str]:
93 return [
94 ViewParam.VIA_INDEX
95 ]
97 def get_rows_colnames(self, req: "CamcopsRequest") -> PlainReportType:
98 final_rows = [] # type: List[Sequence[Sequence[Any]]]
99 colnames = [] # type: List[str]
100 dbsession = req.dbsession
101 group_ids = req.user.ids_of_groups_user_may_report_on
102 superuser = req.user.superuser
103 via_index = req.get_bool_param(ViewParam.VIA_INDEX, True)
104 if via_index:
105 # noinspection PyUnresolvedReferences
106 query = (
107 select([
108 extract_year(TaskIndexEntry.when_created_utc).label("year"), # noqa
109 extract_month(TaskIndexEntry.when_created_utc).label("month"), # noqa
110 TaskIndexEntry.task_table_name.label("task"),
111 func.count().label("num_tasks_added"),
112 ])
113 .select_from(TaskIndexEntry.__table__)
114 .group_by("year", "month", "task")
115 .order_by(desc("year"), desc("month"), "task")
116 # ... http://docs.sqlalchemy.org/en/latest/core/tutorial.html#ordering-or-grouping-by-a-label # noqa
117 )
118 if not superuser:
119 # Restrict to accessible groups
120 # noinspection PyProtectedMember
121 query = query.where(TaskIndexEntry.group_id.in_(group_ids))
122 rows, colnames = get_rows_fieldnames_from_query(dbsession, query)
123 # noinspection PyTypeChecker
124 final_rows = rows
125 else:
126 classes = Task.all_subclasses_by_tablename()
127 for cls in classes:
128 # noinspection PyUnresolvedReferences
129 query = (
130 select([
131 # func.year() is specific to some DBs, e.g. MySQL
132 # so is func.extract();
133 # http://modern-sql.com/feature/extract
134 extract_year(isotzdatetime_to_utcdatetime(
135 cls.when_created)).label("year"),
136 extract_month(isotzdatetime_to_utcdatetime(
137 cls.when_created)).label("month"),
138 literal(cls.__tablename__).label("task"),
139 func.count().label("num_tasks_added"),
140 ])
141 .select_from(cls.__table__)
142 .where(cls._current == True) # noqa: E712
143 .group_by("year", "month")
144 )
145 if not superuser:
146 # Restrict to accessible groups
147 # noinspection PyProtectedMember
148 query = query.where(cls._group_id.in_(group_ids))
149 rows, colnames = get_rows_fieldnames_from_query(
150 dbsession, query)
151 final_rows.extend(rows)
152 final_rows.sort(key=lambda r: (-r[0], -r[1], r[2]))
153 # ... sort by: year (descending), month (descending), task
154 return PlainReportType(rows=final_rows, column_names=colnames)
157class TaskCountByUserReport(Report):
158 """
159 Report to count task instances.
160 """
162 # noinspection PyMethodParameters
163 @classproperty
164 def report_id(cls) -> str:
165 return "taskcount_by_user"
167 @classmethod
168 def title(cls, req: "CamcopsRequest") -> str:
169 _ = req.gettext
170 return _(
171 "(Server) Count current task instances, by creation month and user"
172 )
174 # noinspection PyMethodParameters
175 @classproperty
176 def superuser_only(cls) -> bool:
177 return False
179 @staticmethod
180 def get_paramform_schema_class() -> Type["ReportParamSchema"]:
181 return TaskCountReportSchema
183 @classmethod
184 def get_specific_http_query_keys(cls) -> List[str]:
185 return [
186 ViewParam.VIA_INDEX
187 ]
189 def get_rows_colnames(self, req: "CamcopsRequest") -> PlainReportType:
190 final_rows = [] # type: List[Sequence[Sequence[Any]]]
191 colnames = [] # type: List[str]
192 dbsession = req.dbsession
193 group_ids = req.user.ids_of_groups_user_may_report_on
194 superuser = req.user.superuser
195 via_index = req.get_bool_param(ViewParam.VIA_INDEX, True)
196 if via_index:
197 # noinspection PyUnresolvedReferences
198 query = (
199 select([
200 extract_year(TaskIndexEntry.when_created_utc).label("year"), # noqa
201 extract_month(TaskIndexEntry.when_created_utc).label("month"), # noqa
202 User.username.label("adding_user_name"),
203 func.count().label("num_tasks_added"),
204 ])
205 .select_from(TaskIndexEntry.__table__)
206 .select_from(User.__table__)
207 .where(TaskIndexEntry.adding_user_id == User.id)
208 .group_by("year", "month", "adding_user_name")
209 .order_by(desc("year"), desc("month"), "adding_user_name")
210 )
211 if not superuser:
212 # Restrict to accessible groups
213 # noinspection PyProtectedMember
214 query = query.where(TaskIndexEntry.group_id.in_(group_ids))
215 rows, colnames = get_rows_fieldnames_from_query(dbsession, query)
216 # noinspection PyTypeChecker
217 final_rows = rows
218 else:
219 classes = Task.all_subclasses_by_tablename()
220 counter = Counter()
221 for cls in classes:
222 # noinspection PyUnresolvedReferences
223 query = (
224 select([
225 # func.year() is specific to some DBs, e.g. MySQL
226 # so is func.extract();
227 # http://modern-sql.com/feature/extract
228 extract_year(isotzdatetime_to_utcdatetime(
229 cls.when_created)).label("year"),
230 extract_month(isotzdatetime_to_utcdatetime(
231 cls.when_created)).label("month"),
232 User.username.label("adding_user_name"),
233 func.count().label("num_tasks_added"),
234 ])
235 .select_from(cls.__table__)
236 .select_from(User.__table__)
237 .where(and_(cls._current == True, # noqa: E712
238 cls._adding_user_id == User.id))
239 .group_by("year", "month", "adding_user_name")
240 )
241 if not superuser:
242 # Restrict to accessible groups
243 # noinspection PyProtectedMember
244 query = query.where(cls._group_id.in_(group_ids))
245 rows, colnames = get_rows_fieldnames_from_query(
246 dbsession, query)
247 for row in rows:
248 year, month, username = row[0], row[1], row[2]
249 count = row[3]
250 counter.update({(year, month, username): count})
251 for (year, month, username), total in counter.items():
252 final_rows.append([year, month, username, total])
253 final_rows.sort(key=lambda r: (-r[0], -r[1], r[2]))
254 # ... sort by: year (descending), month (descending), username
255 return PlainReportType(rows=final_rows, column_names=colnames)