Currently there are 2 School Age (PT) and one should be School Age (FT). This is correct in the Administration panel but not being reflected on the actual data entry screens.
```sql
WITH second_occurrence_lines AS (
SELECT
funding_submission_line_jsons.id,
funding_submission_line_jsons.[values],
funding_submission_line.[key] AS json_array_index,
ROW_NUMBER() OVER (
PARTITION BY funding_submission_line_jsons.id,
JSON_VALUE(funding_submission_line.[value], '$.sectionName'),
JSON_VALUE(funding_submission_line.[value], '$.lineName')
ORDER BY
funding_submission_line.[key]
) AS occurrence_number
FROM
funding_submission_line_jsons
CROSS APPLY OPENJSON(funding_submission_line_jsons.[values]) AS funding_submission_line
WHERE
JSON_VALUE(funding_submission_line.[value], '$.lineName') = 'School Age (PT)'
)
UPDATE
funding_submission_line_jsons
SET
[values] = JSON_MODIFY(
funding_submission_line_jsons.[values],
'$[' + CAST(second_occurrence_lines.json_array_index AS nvarchar(10)) + '].lineName',
'School Age (FT)'
)
FROM
funding_submission_line_jsons
INNER JOIN second_occurrence_lines ON funding_submission_line_jsons.id = second_occurrence_lines.id
WHERE
second_occurrence_lines.occurrence_number = 2;
```
script-2-clean-up-funding-submission-lines.sql
```sql
WITH duplicate_lines AS (
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY fiscal_year,
section_name,
line_name
ORDER BY
created_at
) AS row_num
FROM
funding_submission_lines
WHERE
line_name = 'School Age (PT)'
)
UPDATE
funding_submission_lines
SET
line_name = 'School Age (FT)'
WHERE
id IN (
SELECT
id
FROM
duplicate_lines
WHERE
row_num = 2
);
```
Concerns
Since having typoes in names is pretty common, we might need to provide a UI for fixing these kinds of things.
The current UI only supports fixing the issue into the future, not fixing past typos.
Relates to:
Context
Currently there are 2 School Age (PT) and one should be School Age (FT). This is correct in the Administration panel but not being reflected on the actual data entry screens.
The Admin UI located at https://elcc.ynet.gov.yk.ca/administration/submission-lines, lets us change the data for new worksheets, but it doesn't update the labeling of existing worksheets.
Implementation
script-1-clean-up-funding-submision-line-jsons-values-column.sql```sql WITH second_occurrence_lines AS ( SELECT funding_submission_line_jsons.id, funding_submission_line_jsons.[values], funding_submission_line.[key] AS json_array_index, ROW_NUMBER() OVER ( PARTITION BY funding_submission_line_jsons.id, JSON_VALUE(funding_submission_line.[value], '$.sectionName'), JSON_VALUE(funding_submission_line.[value], '$.lineName') ORDER BY funding_submission_line.[key] ) AS occurrence_number FROM funding_submission_line_jsons CROSS APPLY OPENJSON(funding_submission_line_jsons.[values]) AS funding_submission_line WHERE JSON_VALUE(funding_submission_line.[value], '$.lineName') = 'School Age (PT)' ) UPDATE funding_submission_line_jsons SET [values] = JSON_MODIFY( funding_submission_line_jsons.[values], '$[' + CAST(second_occurrence_lines.json_array_index AS nvarchar(10)) + '].lineName', 'School Age (FT)' ) FROM funding_submission_line_jsons INNER JOIN second_occurrence_lines ON funding_submission_line_jsons.id = second_occurrence_lines.id WHERE second_occurrence_lines.occurrence_number = 2; ```
script-2-clean-up-funding-submission-lines.sql```sql WITH duplicate_lines AS ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY fiscal_year, section_name, line_name ORDER BY created_at ) AS row_num FROM funding_submission_lines WHERE line_name = 'School Age (PT)' ) UPDATE funding_submission_lines SET line_name = 'School Age (FT)' WHERE id IN ( SELECT id FROM duplicate_lines WHERE row_num = 2 ); ```
Concerns
Since having typoes in names is pretty common, we might need to provide a UI for fixing these kinds of things. The current UI only supports fixing the issue into the future, not fixing past typos.
Screenshots
Testing Instructions
dev down -vdev upProduction Fixup instructions.