Préavis – Récupérer données manquantes

[VincentAntoine]

No description provided.

[AdelineCelier]

Données à récupérer pour l'affichage des préavis dans la vue liste:

• heure prévue de débarque (vérifier qu'elle n'est jamais nulle, cf. mention dans l'annexe de l'arrêté des ports désignés)

• creuser la manière de récupérer les segments au moment des préavis : se baser sur les FAR ou sur le PNO ? sachant qu'il y a des engins par capture dans le PNO.
  => regarder s'il est possible qu'il y ait des PNO sans FAR. Si non, autant reprendre les segments calculés avec les FAR.

[VincentAntoine]

Autre point à récupérer : les zones CGPM. Ne fait pas partie du standard ERS, mais parfois ajouté dans une balise ERAS à l'intérieur d'un RAS:

<ers:RAS FA="37" SA="1" ID="2" EZ="FRA" SR="26C2"> <ers:ERAS Type="nat" ZC="37.07" RM="26C2"/> </ers:RAS>

Dans l'exemple ci-dessus, ZC="37.07" indique la zone CGPM n°7. RM="26C2" est une redite du rectangle statistique qui est déjà mentionné dans le RAS, inutile de l'extraire.

[VincentAntoine]

Pour la question de savoir comment déterminer les engins et donc les segments associés à un PNO :
Analyse sur un mois (09/01/2024 - 09/02/2024)

• seulement environ un tiers des PNO, correspondant à environ deux tiers des volumes débarqués, ont des infos d'engins renseignés dans le PNO, et ce uniquement pour les navires français, les PNOs des navires battant pavillon tiers ne comportant jamais d'information d'engin (en tout cas pour ceux qui ne sont pas sous FLUX, les données FLUX n'étant pas analysées ici):

WITH t AS (
    SELECT  
        flag_state,
        report_id,
        xpath('//ers:SPE/@GE', rw.xml_message::xml, ARRAY[ARRAY['ers', 'http://ec.europa.eu/fisheries/schema/ers/v3']])::VARCHAR[] AS gears,
        xpath('//ers:SPE/@SN', rw.xml_message::xml, ARRAY[ARRAY['ers', 'http://ec.europa.eu/fisheries/schema/ers/v3']])::VARCHAR[] AS species,
        xpath('//ers:SPE/@WL', rw.xml_message::xml, ARRAY[ARRAY['ers', 'http://ec.europa.eu/fisheries/schema/ers/v3']])::VARCHAR[]::DOUBLE PRECISION[] AS weights
    FROM logbook_reports r
    JOIN logbook_raw_messages rw
    ON rw.operation_number = r.operation_number
    WHERE
        log_type = 'PNO'
        AND operation_datetime_utc >= CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '1 month'
),

t2 AS (
    SELECT
        report_id,
        flag_state,
        COALESCE(array_length(gears, 1) > 0, false) AS has_gear_info,
        COALESCE((SELECT SUM(s) FROM unnest(weights) s), 0) AS weight
    FROM t
)

SELECT
    flag_state,
    has_gear_info AS pno_has_gear_info,
    SUM(weight) AS weight,
    COUNT(DISTINCT report_id) AS nb_pnos
FROM t2
GROUP BY flag_state, has_gear_info

Résultat :

• Option 2 : les infos d'engin sont disponibles pour > 99% des PNO dans les FAR de la marée, hormis pour les partenaires ne transmettant par leurs FAR (ESP, NLD...) :

WITH pnos_and_fars AS (
    SELECT
        pnos.report_id AS pno_report_id,
        pnos.flag_state,
        pnos.cfr,
        pnos.value->'catchOnboard' IS NOT NULL AS pno_has_catches,
        COUNT(DISTINCT fars.report_id) AS number_of_far_reports,
        ARRAY_AGG(DISTINCT haul->>'gear') AS far_gears,
        COUNT(DISTINCT haul->>'gear') AS number_of_far_gears
    FROM logbook_reports pnos
    LEFT JOIN logbook_reports fars
    LEFT JOIN jsonb_array_elements(fars.value->'hauls') haul ON true
    ON
        pnos.cfr = fars.cfr
        AND pnos.trip_number = fars.trip_number
        AND fars.log_type = 'FAR'
        AND fars.operation_datetime_utc > CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '1 month' - INTERVAL '2 weeks'
    WHERE
        pnos.operation_datetime_utc > CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '1 month'
        AND pnos.log_type = 'PNO'
        AND pnos.trip_number IS NOT NULL
    GROUP BY
        pnos.flag_state,
        pnos.cfr,
        pnos.report_id,
        pnos.value->'catchOnboard' IS NOT NULL
    ORDER BY COUNT(DISTINCT fars.report_id), COUNT(DISTINCT haul->>'gear')
),

t AS (
    SELECT 
        flag_state,
        COUNT(DISTINCT pno_report_id) AS nb_pnos,
        COUNT(DISTINCT CASE WHEN pno_has_catches THEN pno_report_id END) AS nb_pnos_with_catches,
        COUNT(DISTINCT CASE WHEN pno_has_catches AND number_of_far_gears >=1 THEN pno_report_id END) AS nb_pnos_with_catches_and_far_gears
    FROM pnos_and_fars
    GROUP BY flag_state
)

SELECT
    *,
    nb_pnos_with_catches_and_far_gears / nb_pnos_with_catches::DOUBLE PRECISION AS share_of_pno_with_catches_having_gear_info_in_fars
FROM t

Résultat :

Donc sans aucun doute on se basera sur les engins des FAR de la marée.

[VincentAntoine]

Créé #3012 pour les histoires d'espèces à bord VS à débarquer sur lesquelles il reste des questions non tranchées.
Ce ticket reste donc pour les autres points :

• heure prévue d'arrivée du PNO
• engins de la marée du PNO pour les segments et les types

[AdelineCelier]

@VincentAntoine où en est-on de ce ticket ? c'est reviewable ?

[AdelineCelier]

Vu avec Vincent, les dates de préavis et les engins de la marée pour le calcul des segments ont bien été récupérés, je ferme ce ticket.