Store excel formulae for calculated intervention data rows/cells

[bgsandan]

Whilst the definitive values for the intervention calculations should be the ones in the database (re-calculated on updates to the individual values in intervention_data via @andy-bevan's script, we should also encode the relationships in a manner that they can be sent to the front-end.

This will allow (for example), fields that contain totals to be updated in the UI on the fly when values are updated (multiplying no. of factories by number of inspections etc).

To facilitate this, the intention is to utilise jsonLogic (http://jsonlogic.com) to encode the relationships in a format that can be sent in the API response and parsed and calculated on the front-end. @bgsandan has already managed to demonstrate a proof-of-concept of automatically converting an Excel formula e.g. =(D57+D60+D63)*D73+D70*D74 into its jsonLogic representation which can then utilised in the front-end:

{  "+": [
    {
      "*": [
        {
          "+": [
            {
              "+": [
                {
                  "var": "row57.year0"
                },
                {
                  "var": "row60.year0"
                }
              ]
            },
            {
              "var": "row63.year0"
            }
          ]
        },
        {
          "var": "row73.year0"
        }
      ]
    },
    {
      "*": [
        {
          "var": "row70.year0"
        },
        {
          "var": "row74.year0"
        }
      ]
    }
  ]
}

If the database can store the input formulae for fields with field types of totals or calculatedvalues then these formulae can be exposed in the appropriate views allowing this conversion to jsonLogic to be handled at the API layer.

See https://kwvmxgit.ad.nerc.ac.uk/-/snippets/125 / https://kwvmxgit.ad.nerc.ac.uk/-/snippets/126 for more info

[bgsandan]

Also worth noting that the code used to go from Excel to JsonLogic goes via an Abstract Syntax Tree (AST) representation of the formula.  It may be possible to tap into that to go back from the AST to statements that are valid PLPGSql for automating some of the function generation

[bgsandan]

Ref #272

[rbroth]

I think this can be closed as completed

• We are extracting excel formulas from the spreadsheets and storing them in intervention_cell_formula
• We decided in a meeting at some point that it would be "easiest" to have all calculation logic encoded as JSON logic and use Javascript to do the calculation; that way, we can do calculations in the frontend and the API with the same code and won't have to maintain two codebases (JS for the frontend, PL/SQL for the db) to do the calculations