FEEHN
Fix transposed effort numbers for lining methods on CELR forms
On CELR forms, events which use a lining methods (BLL,SLL,DL and TL) are mean to record the “Number of sets hauled in a day” into the effort_num field and the “Total number of hooks hauled in the day” in the total_hook_num field. These can be transposed. In this check for CELR forms with these methods, where effort_num is geater or equal to 100 and total_hook_num is less than 100, these fields are transposed.
-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('FEEHN', 'edw', 'effort', 'Fix', 'Fix transposed effort numbers for lining methods on CELR forms');
CREATE OR REPLACE FUNCTION FEEHN()
RETURNS VOID AS $$
with _feehn AS (
SELECT id, effort_num, total_hook_num
FROM groomed.effort
WHERE primary_method IN ('BLL', 'SLL', 'DL', 'TL')
AND effort_num >= 100
AND total_hook_num < 100
)
INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'FEEHN', 'effort', 'effort_num', id, effort_num::TEXT, total_hook_num::TEXT
FROM groomed.effort
WHERE primary_method IN ('BLL', 'SLL', 'DL', 'TL')
AND effort_num >= 100
AND total_hook_num < 100;
-- Swap orig and new around for total_hook_num update
INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'FEEHN', 'effort', 'total_hook_num', id, new, orig
FROM groomed.checks
WHERE code = 'FEEHN'
AND "column"= 'effort_num';
UPDATE groomed.effort SET checks = checks || '{FEEHN}', effort_num = u.new::INT
FROM groomed.checks u
WHERE u.code = 'FEEHN'
AND u.column = 'effort_num'
AND groomed.effort.id = u.id
AND groomed.effort.effort_num = u.orig::INT;
UPDATE groomed.effort SET checks = checks || '{FEEHN}', total_hook_num = u.new::INT
FROM groomed.checks u
WHERE u.code = 'FEEHN'
AND u.column = 'total_hook_num'
AND groomed.effort.id = u.id
AND groomed.effort.total_hook_num = u.orig::INT;
$$ LANGUAGE SQL;
SELECT FEEHN();
-- WITH _feehn AS (
-- SELECT id, effort_num, total_hook_num
-- FROM groomed.effort
-- WHERE form_type = 'CEL' AND primary_method IN ('BLL', 'SLL', 'DL', 'TL') AND effort_num >= 100 AND total_hook_num < 100
-- )Effects of grooming
