Report Grid: support last attestations

[davidwatkins73]

Description

Would need to select the attestation sub types (i.e. flow, cateory:FT etc)

Resourcing

We intend to contribute this feature

Breakdown

• [x] Add dll
• [x] Tweak unique index
• [x] Grid def support
• [x] Data grid support
• [x] Filter support
• [ ] Col editor support
• [ ] API support

[davidwatkins73]

Bumping to next release as can't be done without ddl changes. Not enough fields on the report_grid_col_def table to specify what kind of attestation is needed.

May need the creation of an AttestationKind table (which may simplify other aspects of Waltz)

@JWoodland-Scott - thoughts ?

[davidwatkins73]

Adding the qualifier_kind and qualifier_id cols to the grid_col_def table looks like it should be okay.

However due to the nature of the unique index on the col def table we can't add more than one attestation to a grid at a time.

This should be fixable by adding more cols to the unique index, allowing it to discriminate between attestations cols.

[davidwatkins73]

Some sample grid data:

INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 1, 'APP_GROUP', 5, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 1, 'APPLICATION', null, null, 'NONE', 'NONE', 1, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 13, 'ASSESSMENT_DEFINITION', 14, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 14, 'ASSESSMENT_DEFINITION', 4, null, 'SUMMARY', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 12, 'ASSESSMENT_DEFINITION', 3, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 11, 'COST_KIND', 1, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 8, 'COST_KIND', 3, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 9, 'COST_KIND', 2, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 10, 'COST_KIND', 5, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 1, 'DATA_TYPE', 1000, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 1, 'DATA_TYPE', 6000, null, 'SUMMARY', 'PICK_HIGHEST', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 7, 'INVOLVEMENT_KIND', 6, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 6, 'INVOLVEMENT_KIND', 3, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 4, 'INVOLVEMENT_KIND', 11, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 5, 'INVOLVEMENT_KIND', 4, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 1, 'MEASURABLE', 86, null, 'SUMMARY', 'PICK_LOWEST', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 1, 'MEASURABLE', 88, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 0, 'MEASURABLE', 89, null, 'SUMMARY', 'PICK_HIGHEST', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 3, 'SURVEY_QUESTION', 1, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 2, 'SURVEY_QUESTION', 4, null, 'NONE', 'NONE', null, null, null);
INSERT INTO report_grid_column_definition (report_grid_id, position, column_entity_kind, column_entity_id, display_name, column_usage_kind, rating_rollup_rule, entity_field_reference_id, column_qualifier_kind, column_qualifier_id) VALUES (:gridId, 3, 'SURVEY_TEMPLATE', 3, null, 'NONE', 'NONE', 5, null, null);

[davidwatkins73]

Sample data query:

with required as (
        select *
        from (
            values
                (1, 'APPLICATION', 79, 'MEASURABLE_CATEGORY', 1),
                (2, 'APPLICATION', 79, 'LOGICAL_DATA_FLOW', null),
                (3, 'APPLICATION', 79, 'PHYSICAL_FLOW', null),
                (1, 'APPLICATION', 19, 'MEASURABLE_CATEGORY', 1),
                (2, 'APPLICATION', 19, 'LOGICAL_DATA_FLOW', null),
                (3, 'APPLICATION', 19, 'PHYSICAL_FLOW', null))
        as required (col_id, ref_k, ref_i, att_k, att_i)
    ),
    attestations as (
        select required.col_id,
            ai.parent_entity_kind,
            ai.parent_entity_id,
            ai.attested_at,
            ai.attested_by,
            max(ai.attested_at)
                    over (
                        partition by
                            ai.parent_entity_kind,
                            ai.parent_entity_id,
                            ai.attested_entity_kind,
                            ar.attested_entity_id
                        order by ai.attested_at desc) latest
        from attestation_instance ai
             inner join attestation_run ar on ar.id = ai.attestation_run_id
             inner join required on
                    required.ref_i = ai.parent_entity_id and
                    required.ref_k = ai.parent_entity_kind and
                    required.att_k = ai.attested_entity_kind and
                    (required.att_i = ar.attested_entity_id or required.att_i is null)
    )
select col_id,
       parent_entity_kind,
       parent_entity_id,
       attested_by,
       attested_at
from attestations
where attestations.latest = attestations.attested_at;

[davidwatkins73]

Alternative without the need for creating DSL.row(...) entries:

with required as (
        select rgcd.id col_id,
            'APPLICATION' ref_k,
            age.application_id ref_i,
            rgcd.column_qualifier_kind att_k,
            rgcd.column_qualifier_id att_i
        from report_grid_column_definition rgcd
        cross join application_group_entry age -- selector
        where rgcd.column_entity_kind = 'ATTESTATION'
          and age.group_id = 1
          and rgcd.report_grid_id = 13
    ),
    attestations as (
        select required.col_id,
            ai.parent_entity_kind,
            ai.parent_entity_id,
            ai.attested_at,
            ai.attested_by,
            max(ai.attested_at)
                over (
                    partition by
                        ai.parent_entity_kind,
                        ai.parent_entity_id,
                        ai.attested_entity_kind,
                        ar.attested_entity_id
                    order by ai.attested_at desc) latest
        from attestation_instance ai
             inner join attestation_run ar on ar.id = ai.attestation_run_id
             inner join required on
                    required.ref_i = ai.parent_entity_id and
                    required.ref_k = ai.parent_entity_kind and
                    required.att_k = ai.attested_entity_kind and
                    (required.att_i = ar.attested_entity_id or required.att_i is null)
    )
select col_id,
       parent_entity_kind,
       parent_entity_id,
       attested_by,
       attested_at
from attestations
where attestations.latest = attestations.attested_at;

[davidwatkins73]

API Tested with:

### report grid tabular extractor
POST http://localhost:8000/data-extract/report-grid/external-id/5a4845aa-7fae-425f-92be-b52caafa78e9?format=XLSX
Content-Type: application/json

{
   "entityReference": {"kind": "ORG_UNIT", "id": 10},
   "scope": "CHILDREN"
}

### report grid tabular extractor
POST http://localhost:8000/data-extract/report-grid/external-id/5a4845aa-7fae-425f-92be-b52caafa78e9?format=JSON
Content-Type: application/json

{
   "entityReference": {"kind": "ORG_UNIT", "id": 10},
   "scope": "CHILDREN"
}

[davidwatkins73]