edc-qareports


Nameedc-qareports JSON
Version 0.1.19 PyPI version JSON
download
home_pagehttps://github.com/clinicedc/edc-qareports
SummaryClasses for QA Reports in clinicedc/edc projects
upload_time2024-10-09 16:31:21
maintainerNone
docs_urlNone
authorErik van Widenfelt
requires_python>=3.12
licenseGPL license, see LICENSE
keywords django edc qa sql views clinicedc clinical trials
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage
            |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/61/64/f1b5532f51c8ccc9c1d5ce34c6b64f86947c5f3fb36affd05d1cf9acb68b/edc_qareports-0.1.19.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.19",
    "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": "9a31a3300f83a62704648071063f55f6671d813d0622c30c820d2adf5c122a96",
                "md5": "3ce3559c98d21b84eab15b1def548ee3",
                "sha256": "bb4c6da3c1168f4252ad490eaf9c18083ca804134ae9457f22f97178ac42ec22"
            },
            "downloads": -1,
            "filename": "edc_qareports-0.1.19-py3-none-any.whl",
            "has_sig": false,
            "md5_digest": "3ce3559c98d21b84eab15b1def548ee3",
            "packagetype": "bdist_wheel",
            "python_version": "py3",
            "requires_python": ">=3.12",
            "size": 62774,
            "upload_time": "2024-10-09T16:31:19",
            "upload_time_iso_8601": "2024-10-09T16:31:19.320691Z",
            "url": "https://files.pythonhosted.org/packages/9a/31/a3300f83a62704648071063f55f6671d813d0622c30c820d2adf5c122a96/edc_qareports-0.1.19-py3-none-any.whl",
            "yanked": false,
            "yanked_reason": null
        },
        {
            "comment_text": "",
            "digests": {
                "blake2b_256": "6164f1b5532f51c8ccc9c1d5ce34c6b64f86947c5f3fb36affd05d1cf9acb68b",
                "md5": "3f6e5182f1181f113b8967a8b1c49596",
                "sha256": "8edcbc7afb90ce2c2e2afbdfaabd3a304fdf92e80e51087e83eb0959541d66e6"
            },
            "downloads": -1,
            "filename": "edc_qareports-0.1.19.tar.gz",
            "has_sig": false,
            "md5_digest": "3f6e5182f1181f113b8967a8b1c49596",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": ">=3.12",
            "size": 48526,
            "upload_time": "2024-10-09T16:31:21",
            "upload_time_iso_8601": "2024-10-09T16:31:21.836507Z",
            "url": "https://files.pythonhosted.org/packages/61/64/f1b5532f51c8ccc9c1d5ce34c6b64f86947c5f3fb36affd05d1cf9acb68b/edc_qareports-0.1.19.tar.gz",
            "yanked": false,
            "yanked_reason": null
        }
    ],
    "upload_time": "2024-10-09 16:31:21",
    "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"
}
        
Elapsed time: 0.35888s