|pypi| |actions| |codecov| |downloads|
edc-qareports
-------------
This module helps you represent SQL VIEWS as QA Reports using Django Admin.
In clinicedc/edc projects, QA reports are in the ``<my_app>_reports`` module.
Installation
============
Add to settings.INSTALLED_APPS:
.. code-block:: python
INSTALLED_APPS = [
...
"edc_qareports.apps.AppConfig",
...
]
Add to project URLS:
.. code-block:: python
# urls.py
urlpatterns = [
...
*paths_for_urlpatterns("edc_qareports"),
...
]
Custom QA / data management reports using SQL VIEWS
===================================================
Although not absolutely necessary, it is convenient to base a QA report on an SQL VIEW. As
data issues are resolved, the SQL VIEW reflects the current data.
A QA report based on an SQL VIEW can be represented by a model class. By registering the model class in Admin, all the functionality of the ModelAdmin class is available to show the report.
First, within your EDC project, create a `<myapp>_reports` app. For example `Meta Reports`.
.. code-block:: bash
meta_edc
meta_edc/meta_reports
meta_edc/meta_reports/admin
meta_edc/meta_reports/admin/dbviews
meta_edc/meta_reports/admin/dbviews/my_view_in_sql_admin.py
meta_edc/meta_reports/migrations
meta_edc/meta_reports/migrations/0001_myviewinsql.py
meta_edc/meta_reports/models
meta_edc/meta_reports/models/dbviews
meta_edc/meta_reports/models/dbviews/mymodel/unmanaged_model.py
meta_edc/meta_reports/models/dbviews/mymodel/view_definition.py
meta_edc/meta_reports/admin_site.py
meta_edc/meta_reports/apps.py
meta_edc/ ...
the ``apps.py`` might look like this:
.. code-block:: python
from django.apps import AppConfig as DjangoAppConfig
class AppConfig(DjangoAppConfig):
name = "meta_reports"
verbose_name = "META Reports"
include_in_administration_section = True
QA Report as an SQL VIEW
++++++++++++++++++++++++
Now that you have created the basic structure for the Reports App, create an SQL VIEW. Some rules apply:
* To show the model class in Admin, the SQL VIEW needs at least an ID column.
* To use the EDC ModelAdmin classes, include ``id``, ``subject_identifier``, ``site_id``, ``created`` and ``report_model``.
* Columns ``id``, ``created`` and ``report_model`` are generated columns from the SQL VIEW, not values coming from the underlying SQL statement / data tables.
* Column ``report_model`` is in label_lower format.
* Suffix the view name with ``_view``.
To manage SQL view code, we use ``django_dbviews``. This module helps by using migrations to manage changes to the SQL view code.
The ``view_defintion.py`` might look like this:
.. code-block:: sql
from edc_qareports.sql_generator import SqlViewGenerator
def get_view_definition() -> dict:
subquery = """
select subject_identifier, site_id, appt_datetime, `first_value`,
`second_value`, `third_value`,
datediff(`third_date`, `first_date`) as `interval_days`,
datediff(now(), `first_date`) as `from_now_days`
from (
select subject_identifier, site_id, appt_datetime,
FIRST_VALUE(visit_code) OVER w as `first_value`,
NTH_VALUE(visit_code, 2) OVER w as `second_value`,
NTH_VALUE(visit_code, 3) OVER w as `third_value`,
FIRST_VALUE(appt_datetime) OVER w as `first_date`,
NTH_VALUE(appt_datetime, 3) OVER w as `third_date`
from edc_appointment_appointment where visit_code_sequence=0 and appt_status="New"
and appt_datetime <= now()
WINDOW w as (PARTITION BY subject_identifier order by appt_datetime ROWS UNBOUNDED PRECEDING)
) as B
where `second_value` is not null and `third_value` is not null
""" # noqa
sql_view = SqlViewGenerator(
report_model="meta_reports.unattendedthreeinrow",
ordering=["subject_identifier", "site_id"],
)
return {
"django.db.backends.mysql": sql_view.as_mysql(subquery),
"django.db.backends.postgresql": sql_view.as_postgres(subquery),
"django.db.backends.sqlite3": sql_view.as_sqlite(subquery),
}
Using a model class to represent your QA Report
+++++++++++++++++++++++++++++++++++++++++++++++
An SQL VIEW is not a table so configure an unmanaged model class by setting ``managed=False``. ``makemigrations`` creates migrations for unmanaged models but never calls ``CreateModel``.
The unmanaged model class would be something like this:
.. code-block:: python
class MyViewInSql(QaReportModelMixin, models.Model):
col1 = models.CharField(max_length=25)
col2 = models.IntegerField()
col3 = models.DateTimeField()
class Meta:
managed = False
db_table = "my_view_in_sql_view"
verbose_name = "blah blah"
verbose_name_plural = "blah blah"
You can store the SQL statement anywhere but we put it in the same folder as
the model class using the same file name as the model class but with file extension ``.sql``
Using a migration to read the SQL statement
+++++++++++++++++++++++++++++++++++++++++++
Create an empty migration in the reports app and read the SQL file in the migration
.. code-block:: python
...
operations = [
migrations.RunSQL(
read_unmanaged_model_sql("my_view_in_sql.sql", app_name="meta_reports")
),
]
IMPORTANT: If you change the SQL VIEW, update the ``.sql`` file and create a new migration
that drops and re-creates the SQL VIEW.
.. code-block:: python
...
operations = [
migrations.RunSQL("drop view my_view_in_sql_view"),
migrations.RunSQL(
read_unmanaged_model_sql("my_view_in_sql.sql", app_name="meta_reports")
),
]
Linking ``QaReportNote`` with your QA Report
++++++++++++++++++++++++++++++++++++++++++++
You can link your QA Report in Admin to model ``QaReportNote``. The ``QaReportNote``
model class is used to track the ``status`` of the report item and provide a space for any
notes.
To use ``QaReportNote`` with your QA report, declare the QA Report admin class with ``QaReportWithNoteModelAdminMixin``.
.. code-block:: python
from django.contrib import admin
from edc_model_admin.dashboard import ModelAdminDashboardMixin
from edc_model_admin.mixins import TemplatesModelAdminMixin
from edc_qareports.admin import QaReportWithNoteModelAdminMixin
from edc_sites.admin import SiteModelAdminMixin
from edc_visit_schedule.admin import ScheduleStatusListFilter
from ...admin_site import meta_reports_admin
from ...models import MyViewInSql
@admin.register(MyViewInSql, site=meta_reports_admin)
class MyViewInSqlAdmin(
QaReportWithNoteModelAdminMixin,
SiteModelAdminMixin,
ModelAdminDashboardMixin,
TemplatesModelAdminMixin,
admin.ModelAdmin,
):
ordering = ["site", "subject_identifier"]
list_display = [
"dashboard",
"subject",
"col1",
"col2",
"col3",
"created",
]
list_filter = [ScheduleStatusListFilter, "col1", "col3"]
search_fields = ["id", "subject_identifier"]
@admin.display(description="Subject", ordering="subject_identifier")
def subject(self, obj):
return obj.subject_identifier
Granting access to your QA Report
+++++++++++++++++++++++++++++++++
Add the QA report codenames to your local app, create a group and add the group to the QA_REPORTS_ROLE.
In this example the app is called ``meta_reports`` and the group is ``META_REPORTS``.
(Note: If your app has an ``auth`` module (e.g. ``meta_auth``) put these lines there.)
.. code-block:: python
# meta_reports/auth_objects.py
reports_codenames = [c for c in get_app_codenames("meta_reports")]
.. code-block:: python
# meta_reports/auths.py
site_auths.add_group(*reports_codenames, name=META_REPORTS)
# add the group to the QA_REPORTS role
site_auths.update_role(META_REPORTS, name=QA_REPORTS_ROLE)
.. |pypi| image:: https://img.shields.io/pypi/v/edc-qareports.svg
:target: https://pypi.python.org/pypi/edc-qareports
.. |actions| image:: https://github.com/clinicedc/edc-qareports/actions/workflows/build.yml/badge.svg
:target: https://github.com/clinicedc/edc-qareports/actions/workflows/build.yml
.. |codecov| image:: https://codecov.io/gh/clinicedc/edc-qareports/branch/develop/graph/badge.svg
:target: https://codecov.io/gh/clinicedc/edc-qareports
.. |downloads| image:: https://pepy.tech/badge/edc-qareports
:target: https://pepy.tech/project/edc-qareports
Raw data
{
"_id": null,
"home_page": "https://github.com/clinicedc/edc-qareports",
"name": "edc-qareports",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.12",
"maintainer_email": null,
"keywords": "django Edc QA, SQL VIEWS, clinicedc, clinical trials",
"author": "Erik van Widenfelt",
"author_email": "ew2789@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/40/f0/be8d3628f7e88bf6f07dd0ec0c9b5804b882caf231001133c3756e9102fd/edc_qareports-0.1.20.tar.gz",
"platform": null,
"description": "|pypi| |actions| |codecov| |downloads|\n\nedc-qareports\n-------------\n\nThis module helps you represent SQL VIEWS as QA Reports using Django Admin.\n\nIn clinicedc/edc projects, QA reports are in the ``<my_app>_reports`` module.\n\nInstallation\n============\n\nAdd to settings.INSTALLED_APPS:\n\n.. code-block:: python\n\n INSTALLED_APPS = [\n ...\n \"edc_qareports.apps.AppConfig\",\n ...\n ]\n\nAdd to project URLS:\n\n.. code-block:: python\n\n # urls.py\n urlpatterns = [\n ...\n *paths_for_urlpatterns(\"edc_qareports\"),\n ...\n ]\n\nCustom QA / data management reports using SQL VIEWS\n===================================================\n\nAlthough not absolutely necessary, it is convenient to base a QA report on an SQL VIEW. As\ndata issues are resolved, the SQL VIEW reflects the current data.\n\nA QA report based on an SQL VIEW can be represented by a model class. By registering the model class in Admin, all the functionality of the ModelAdmin class is available to show the report.\n\nFirst, within your EDC project, create a `<myapp>_reports` app. For example `Meta Reports`.\n\n.. code-block:: bash\n\n meta_edc\n meta_edc/meta_reports\n meta_edc/meta_reports/admin\n meta_edc/meta_reports/admin/dbviews\n meta_edc/meta_reports/admin/dbviews/my_view_in_sql_admin.py\n meta_edc/meta_reports/migrations\n meta_edc/meta_reports/migrations/0001_myviewinsql.py\n meta_edc/meta_reports/models\n meta_edc/meta_reports/models/dbviews\n meta_edc/meta_reports/models/dbviews/mymodel/unmanaged_model.py\n meta_edc/meta_reports/models/dbviews/mymodel/view_definition.py\n meta_edc/meta_reports/admin_site.py\n meta_edc/meta_reports/apps.py\n meta_edc/ ...\n\nthe ``apps.py`` might look like this:\n\n.. code-block:: python\n\n from django.apps import AppConfig as DjangoAppConfig\n\n class AppConfig(DjangoAppConfig):\n name = \"meta_reports\"\n verbose_name = \"META Reports\"\n include_in_administration_section = True\n\n\nQA Report as an SQL VIEW\n++++++++++++++++++++++++\nNow that you have created the basic structure for the Reports App, create an SQL VIEW. Some rules apply:\n\n* To show the model class in Admin, the SQL VIEW needs at least an ID column.\n* To use the EDC ModelAdmin classes, include ``id``, ``subject_identifier``, ``site_id``, ``created`` and ``report_model``.\n* Columns ``id``, ``created`` and ``report_model`` are generated columns from the SQL VIEW, not values coming from the underlying SQL statement / data tables.\n* Column ``report_model`` is in label_lower format.\n* Suffix the view name with ``_view``.\n\nTo manage SQL view code, we use ``django_dbviews``. This module helps by using migrations to manage changes to the SQL view code.\n\n\nThe ``view_defintion.py`` might look like this:\n\n.. code-block:: sql\n\n from edc_qareports.sql_generator import SqlViewGenerator\n\n def get_view_definition() -> dict:\n subquery = \"\"\"\n select subject_identifier, site_id, appt_datetime, `first_value`,\n `second_value`, `third_value`,\n datediff(`third_date`, `first_date`) as `interval_days`,\n datediff(now(), `first_date`) as `from_now_days`\n from (\n select subject_identifier, site_id, appt_datetime,\n FIRST_VALUE(visit_code) OVER w as `first_value`,\n NTH_VALUE(visit_code, 2) OVER w as `second_value`,\n NTH_VALUE(visit_code, 3) OVER w as `third_value`,\n FIRST_VALUE(appt_datetime) OVER w as `first_date`,\n NTH_VALUE(appt_datetime, 3) OVER w as `third_date`\n from edc_appointment_appointment where visit_code_sequence=0 and appt_status=\"New\"\n and appt_datetime <= now()\n WINDOW w as (PARTITION BY subject_identifier order by appt_datetime ROWS UNBOUNDED PRECEDING)\n ) as B\n where `second_value` is not null and `third_value` is not null\n \"\"\" # noqa\n sql_view = SqlViewGenerator(\n report_model=\"meta_reports.unattendedthreeinrow\",\n ordering=[\"subject_identifier\", \"site_id\"],\n )\n return {\n \"django.db.backends.mysql\": sql_view.as_mysql(subquery),\n \"django.db.backends.postgresql\": sql_view.as_postgres(subquery),\n \"django.db.backends.sqlite3\": sql_view.as_sqlite(subquery),\n }\n\nUsing a model class to represent your QA Report\n+++++++++++++++++++++++++++++++++++++++++++++++\n\nAn SQL VIEW is not a table so configure an unmanaged model class by setting ``managed=False``. ``makemigrations`` creates migrations for unmanaged models but never calls ``CreateModel``.\n\nThe unmanaged model class would be something like this:\n\n.. code-block:: python\n\n class MyViewInSql(QaReportModelMixin, models.Model):\n\n col1 = models.CharField(max_length=25)\n\n col2 = models.IntegerField()\n\n col3 = models.DateTimeField()\n\n class Meta:\n managed = False\n db_table = \"my_view_in_sql_view\"\n verbose_name = \"blah blah\"\n verbose_name_plural = \"blah blah\"\n\nYou can store the SQL statement anywhere but we put it in the same folder as\nthe model class using the same file name as the model class but with file extension ``.sql``\n\nUsing a migration to read the SQL statement\n+++++++++++++++++++++++++++++++++++++++++++\n\nCreate an empty migration in the reports app and read the SQL file in the migration\n\n.. code-block:: python\n\n ...\n\n operations = [\n migrations.RunSQL(\n read_unmanaged_model_sql(\"my_view_in_sql.sql\", app_name=\"meta_reports\")\n ),\n ]\n\n\nIMPORTANT: If you change the SQL VIEW, update the ``.sql`` file and create a new migration\nthat drops and re-creates the SQL VIEW.\n\n.. code-block:: python\n\n ...\n\n operations = [\n migrations.RunSQL(\"drop view my_view_in_sql_view\"),\n migrations.RunSQL(\n read_unmanaged_model_sql(\"my_view_in_sql.sql\", app_name=\"meta_reports\")\n ),\n ]\n\n\nLinking ``QaReportNote`` with your QA Report\n++++++++++++++++++++++++++++++++++++++++++++\n\nYou can link your QA Report in Admin to model ``QaReportNote``. The ``QaReportNote``\nmodel class is used to track the ``status`` of the report item and provide a space for any\nnotes.\n\nTo use ``QaReportNote`` with your QA report, declare the QA Report admin class with ``QaReportWithNoteModelAdminMixin``.\n\n.. code-block:: python\n\n from django.contrib import admin\n from edc_model_admin.dashboard import ModelAdminDashboardMixin\n from edc_model_admin.mixins import TemplatesModelAdminMixin\n from edc_qareports.admin import QaReportWithNoteModelAdminMixin\n from edc_sites.admin import SiteModelAdminMixin\n from edc_visit_schedule.admin import ScheduleStatusListFilter\n\n from ...admin_site import meta_reports_admin\n from ...models import MyViewInSql\n\n\n @admin.register(MyViewInSql, site=meta_reports_admin)\n class MyViewInSqlAdmin(\n QaReportWithNoteModelAdminMixin,\n SiteModelAdminMixin,\n ModelAdminDashboardMixin,\n TemplatesModelAdminMixin,\n admin.ModelAdmin,\n ):\n ordering = [\"site\", \"subject_identifier\"]\n\n list_display = [\n \"dashboard\",\n \"subject\",\n \"col1\",\n \"col2\",\n \"col3\",\n \"created\",\n ]\n\n list_filter = [ScheduleStatusListFilter, \"col1\", \"col3\"]\n\n search_fields = [\"id\", \"subject_identifier\"]\n\n @admin.display(description=\"Subject\", ordering=\"subject_identifier\")\n def subject(self, obj):\n return obj.subject_identifier\n\nGranting access to your QA Report\n+++++++++++++++++++++++++++++++++\n\nAdd the QA report codenames to your local app, create a group and add the group to the QA_REPORTS_ROLE.\n\nIn this example the app is called ``meta_reports`` and the group is ``META_REPORTS``.\n\n(Note: If your app has an ``auth`` module (e.g. ``meta_auth``) put these lines there.)\n\n.. code-block:: python\n\n # meta_reports/auth_objects.py\n\n reports_codenames = [c for c in get_app_codenames(\"meta_reports\")]\n\n.. code-block:: python\n\n # meta_reports/auths.py\n\n site_auths.add_group(*reports_codenames, name=META_REPORTS)\n # add the group to the QA_REPORTS role\n site_auths.update_role(META_REPORTS, name=QA_REPORTS_ROLE)\n\n\n\n.. |pypi| image:: https://img.shields.io/pypi/v/edc-qareports.svg\n :target: https://pypi.python.org/pypi/edc-qareports\n\n.. |actions| image:: https://github.com/clinicedc/edc-qareports/actions/workflows/build.yml/badge.svg\n :target: https://github.com/clinicedc/edc-qareports/actions/workflows/build.yml\n\n.. |codecov| image:: https://codecov.io/gh/clinicedc/edc-qareports/branch/develop/graph/badge.svg\n :target: https://codecov.io/gh/clinicedc/edc-qareports\n\n.. |downloads| image:: https://pepy.tech/badge/edc-qareports\n :target: https://pepy.tech/project/edc-qareports\n",
"bugtrack_url": null,
"license": "GPL license, see LICENSE",
"summary": "Classes for QA Reports in clinicedc/edc projects",
"version": "0.1.20",
"project_urls": {
"Homepage": "https://github.com/clinicedc/edc-qareports"
},
"split_keywords": [
"django edc qa",
" sql views",
" clinicedc",
" clinical trials"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "e1055caa1cb0efa3037c54c6092cd6edaa2dc1937ca9ba98e69c0cf781b8aec1",
"md5": "60e919ef99996c7f5fcad380112db4d0",
"sha256": "6d1f303847bf5aaf7bd7b482bafbd2437d23611443b08c78a2ab7aa4f253f9f8"
},
"downloads": -1,
"filename": "edc_qareports-0.1.20-py3-none-any.whl",
"has_sig": false,
"md5_digest": "60e919ef99996c7f5fcad380112db4d0",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.12",
"size": 62792,
"upload_time": "2024-11-20T22:34:01",
"upload_time_iso_8601": "2024-11-20T22:34:01.718051Z",
"url": "https://files.pythonhosted.org/packages/e1/05/5caa1cb0efa3037c54c6092cd6edaa2dc1937ca9ba98e69c0cf781b8aec1/edc_qareports-0.1.20-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "40f0be8d3628f7e88bf6f07dd0ec0c9b5804b882caf231001133c3756e9102fd",
"md5": "b936bcdbb1769c215998cedb405f30a7",
"sha256": "37b6131fad2c884a32292fc23488edd46278b6de3898209eed16aa4a9e29dd60"
},
"downloads": -1,
"filename": "edc_qareports-0.1.20.tar.gz",
"has_sig": false,
"md5_digest": "b936bcdbb1769c215998cedb405f30a7",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.12",
"size": 48632,
"upload_time": "2024-11-20T22:34:03",
"upload_time_iso_8601": "2024-11-20T22:34:03.007116Z",
"url": "https://files.pythonhosted.org/packages/40/f0/be8d3628f7e88bf6f07dd0ec0c9b5804b882caf231001133c3756e9102fd/edc_qareports-0.1.20.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-11-20 22:34:03",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "clinicedc",
"github_project": "edc-qareports",
"travis_ci": false,
"coveralls": true,
"github_actions": true,
"lcname": "edc-qareports"
}
JSON
