search

msupply-foundation / open-msupply

Open mSupply represents our most recent advancement in the Logistics Management Information System (LMIS), expanding on more than two decades of development inherited from the well-established legacy of the original mSupply.
https://msupply.foundation/open-msupply/
Other
23 stars 14 forks source link

Reports Phase 3 #4668

Open roxy-dao opened 3 months ago

roxy-dao commented 3 months ago

In order of priority:

---- Future works ---

untriaged issues:

Apart from this we would want to add more internal docs about report creation (how to make report compliant with our excel export logic, etc..)

andreievg commented 2 months ago

Versioning, Testing and Easy of Deployment

Problem Space

First of all, report are configurable and sync out to sites, and sites can be on different versions of omSupply (updating all sites is not an easy task). Consider this in the context of the following

For deployment

Solution

Standard reports will be moved to omSupply repository and cli can generate one json file containing all reports in a directory, this file is bundled into omSupply application and can also be uploaded to mSupply central to update all reports at once

When omSupply starts (or via cli command), these reports are upserted by the following rules:

found_report = find report with this code

if not found_report {
    upsert report
    return
}

if found_report.version >= this report.version {
    return
}

upsert report

code is a new field that identifies the same report for different versions

version follows omSupply major and minor version, for omSupply to select correct report version for current omSupply instance, and patch identifies any updates to current report version

When omSupply generates report it will select report based on version with the follow rules

After narrowing down on report by context and sub context, and then by code

report_versions = all reports for code

if report_versions have custom reports {
    report_versions = only custom reports
}

// Report selection logic will be such to satisfy the following use cases (path version is ignored)
report_version = [2.3.0, 2.3.5, 2.8.2, 2.8.3, 3.0.1, 3.5.1]
if omSupply.version = 2.3 selected report = 2.3.5
if omSupply.version = 2.4 selected report = 2.3.5
if omSupply.version = 2.8 selected report = 2.8.3
if omSupply.version = 3.2 selected report = 3.0.1
if omSupply.version = 4.5 selected report = 3.5.1

This will allow us to update existing report through mSupply without upgrading omSupply, for current and previous versions of omSupply. When omSupply is deployed are remote sites, it's not that easy to update them to later versions, but patching existing versions can be done through mSupply report configurations. Also custom reports would still be used instead of standard reports, where needed.

omSupply report builder cli will be changes to crawl a directory and find all matching manifest.json files, and then generate one json file containing all reports and extra metadata (version, arguments schemas etc), this file will be used to:

Custom report will still live in separate repository (can specify report builder arguments for folder to crawl)

Manifest File

Manifest file updated to:

{
    "//": "optional",
    "//": "default - false",
    "//": "Is it a custom report, custom reports will override non custom reports when report is selected",
    "isCustom": false,
    "//": "Major and minor should follow omSupply versions, only the latest version will be bundled with omSupply, but previous or future versions may exist in database",
    "//": "omSupply will match the version best suited to current omSup version",
    "version": "2.1.1",
    "//": "Report identifier, will be unique between reports but the same for different report versions",
    "code": "item_usage",
    "//": "Report context, see server/repository/src/db_diesel/report_row.rs",
    "context": "Report",
        "//": "Report name as it appears in omSupply UI",
    "name": "Report",
    "//": "optional",
    "//": "Sub context for reports, applicable to Dispensary and Report context, see client/packages/reports/src/ListView/ListView.tsx",
    "subContext": "StockAndItems",
    "//": "All location/paths relative to manifest.json",
    "//": "Location of template file",
    "template": "template.html",
    "queries": {
        "//": "optional",
        "//": "Location of gql query file file",
        "gql": "query.gql",
        "//": "optional",
        "//": "Location of sql queries, without extension",
        "//": "Each query will consist of one .sql file or two files .sqlite.sql and .postgres.sql",
        "//": "In below example 'one' query works for both sqlite and postgres and 'two' has db specific syntax",
        "//": "thus three sql files would exist in manifest.json folder 'one.sql', 'two.sqlite.sql' and 'two.postgres.sql'",
        "sql": [
            "one",
            "two"
        ]
    },
    "//": "optional",
    "arguments": {
        "//": "Location of arguments json schema",
        "schema": "arguments.json",
        "//": "Location of Arguments json forms",
        "ui": "arguments_ui.json",
    },
    "//": "optional",
    "//": "Location of header",
    "header": "header.html",
    "//": "optional",
    "//": "Location of footer",
    "footer": "footer.html",
    "test": {
        "//": "optional",
        "//": "Location of test arguments",
        "arguments": "test_arguments.json",
        "//": "Location of test reference data",
        "referenceData": "data/reference1",
        "//": "optional",
        "//": "For report that have dataId, for example requisition or outbound shipment reports",
        "dataId": "c92b740c-f271-4af0-be37-fa3068bdb0b2",
    }
}

Reports Folder Structure

reports
  item_usage
    shared
        one.sql
        arguments.json
        test_arguments.json
        arguments_ui.json
    2_3_1
        manifest.json
        two.postgres.sql
        two.sqlite.sql
        query.gql
        template.html
    2_4_3
        manifest.json
        two.postgres.sql
        two.sqlite.sql
        query.gql
        template.html

With above structure, in manifest.json, locations of arguments and sql one query would have ../shared/ prefix

When report is patched the directory name for the version is updated (as well as version in manifest)

Testing

It's quite important to make sure existing reports are not broken by schema or api changes (we are trying to avoid these changes, however there is a small chance this could happen, for example when I did performance improvements for program work I moved some nodes to loaders, and from UI perspective everything was fine but it did break custom reports)

Currently we tests reports against a running omSupply version, for report unit tests we would need to have a data set that checks all of the query and template paths, thus using reference data and combining report builder and remote service cli (which allows us to upsert referenceData) would help with automating report tests for standard and custom reports (referenceData for custom reports will be located in custom report repo).

Unit tests should be created in such a way as to combine report tests that use the same reference file.

Updating and Creating new reports

It's quite convenient to have print, show and upsert scripts when working with existing reports, these can be built into cli, we would need to specify the location of the report or report code to run with the cli. Cli can connect to current database and access service methods to print/show report. There would already exist a method to upsert report in case it needs to be checked via omSupply app UI.

When updating existing reports via patch, we would need to let support team know, generated reports would be committed to the repo (to omSupply repo for standard reports, and client specific json files in custom report repository), so they can be used by the build process and by support team to update existing reports in mSupply. We need to flag a support task for updating these reports.

Patches to previous versions should be done and tested on previous versions of omSupply (not in develop, but develop updated after)

mSupply Import Script

We would need a way to import all reports to mSupply at once (here is mSupply issue)

This scripts should not touch or update reports that have not changed, and delete previous patch versions (to reduce sync traffic)

Existing omSupply versions

Since versioning is a foreign concept to existing omSupply, we would need to differentiate between versioned report and not versioned. This would require adding a new report 'type' (editor field in mSupply). We would also need to make sure that custom reports are re imported into mSupply for countries that will be updating to versioned report version of omSupply

Schema Changes

mSupply - report table

in mSupply we would need to update the report UI to have this new type (maybe have one import script that populates all fields in that UI, we can import json with one report via this UI)

omSupply

In version upgrade we would remove existing reports, and mark reports in sync buffer to be re-integrated Report translator will only integrate "omsupply_versioned" type/editor reports

Extra

We might not want to bundle all the previous report version, but probably want to supply one json file to support with all of the versions, may need a parameter for bundle json (with latest) and for all report version json, and have a way to share the former with support

jmbrunskill commented 2 months ago

@andreievg as discussed, I think we could consider moving these versioned reports to open-mSupply Central.

Advantages

  1. No mSupply Changes needed
  2. Upgrading central server could allow it to automatically update and remote site reports (if they're comptible)
  3. Less dependance on Legacy mSupply

Disadvantages

  1. Need to build some Central Server UI to manage the upload of customer reports.
jmbrunskill commented 2 months ago

I think the general plan for versioning looks good though! I like tying the compatibility to OMS Version.

There is an issue when a change is made that breaks an older version, e.g. removing a field in graphql (similar to loaders one) We can't tag the old version as incompatible and we'll have to be vigilant to upgrade. I think for standard reports, we should be ok as we'll be testing, but I think there's risk of breaking custom reports unexpectedly.

I haven't thought of any magic solutions for this, I guess we could try to add a test function to production for all custom reports?

mark-prins commented 1 month ago

That all looks great - thanks for the thought gone into the versioning and management of reports, that's super helpful

I agree with @jmbrunskill - would be good to take this opportunity and remove the OG dependency, and shift report admin to oms central server.

Custom reports are still a little tricky to manage compatibility and versioning; happy to keep that as a manual task. there's low likelihood of it happening and we can isolate a manual test task to specific clients prior to upgrading. It's a little tedious so it's worth investigating later if we can automate the testing of custom reports.

andreievg commented 3 weeks ago

Add header with logo to all standard/general reports and make sure existing standard report (like requisition) works with the logo set on store

andreievg commented 3 weeks ago

Make sure all standard reports and printouts are included in the bundling process (not just general reports)