pggeodb.nancy.inra.fr/db_cefs - db_cefs on pggeodb.nancy.inra.fr

Function: ps_maj_indic_cpos_finish

Schema

public

Owner

postgres

Descriptions

Fonction de positionnnement final (après un premier passage) des indicateurs de suivi des campagnes de positionnement

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_maj_indic_cpos_finish (
 annee_suivi integer,
 nb_mini_camp10min integer = 45,
 nb_mini_camp1heure integer = 20
)
RETURNS integer AS
$span$
DECLARE
 curs_campagne refcursor; -- Curseur recevant les informations des campagnes
 rec_campagne  record; -- Enregistrement contenant la campagne en cours de traitement
 i INTEGER;
 camp_nb_camp_jour INTEGER; -- variable recevant le nombre de campagne dans la journée
 camp_ani_id int4; -- variable recevant l'identifiant animal permettant de détecter les changements d'animaux
 camp_id INTEGER[3]; -- Tableau recevant les identifiants des dernières campagnes analysées
 delta_nb_minute INTEGER[3]; -- Tableau recevant les delta des dernières campagnes analysées (nombre de minutes entre une campagne et la précédente
 nbcamp INTEGER; -- Variable contenant le nombre de campagnes analysées et retourné en fin de traitement
 camp_id_tmp INTEGER; -- Variable contenant l'identifiant de la campagne à modifier, les commandes SQL UPDATE ne pouvant accepter une variable issue d'un tableau
  -- Par exemple UPDATE ... WHERE cpos_ID = camp_id[2] est en erreur.
 nb_mini_camp1heure_dernier_jour integer; -- Variable définissant le nombre minimum de campagnes positionnées avec cpos_prog1heure à vrai lors du dernier jour de mesure
-- pour que cet indicateur ne soit pas réinitialisé à faux.
 nb_mini_camp10min_dernier_jour integer; -- Variable définissant le nombre minimum de campagnes positionnées avec cpos_prog10minutes à vrai lors du dernier jour de mesure
-- pour que cet indicateur ne soit pas réinitialisé à faux.                                
BEGIN
-- Initialisation des variables
   camp_nb_camp_jour :=0;
   camp_ani_id := 0;
nbcamp :=0;
nb_mini_camp1heure_dernier_jour := 7;
nb_mini_camp10min_dernier_jour := 20;
-- Campagnes 2003 - Des campagnes à 4 heures uniquement. Pas de correction à apporter.
-- if (rec_campagne.date_capture > '01/11/2002') and (rec_campagne.date_capture < '01/04/2003') THEN
-- end if;
   
-- Campagne 2004 - Des campagnes à 3, 4 ou 6 heures. Corrections :
-- Pour les campagnes à 4 heures (delta = 240 - NB relevés dans la journée >= 6) les relevés à 0 et 12 heures ne doivent pas indiquer de suivi à 3 ou 6 heures.
-- Pour les campagnes à 3 heures (delta = 180 - NB relevés dans la journée >= 8) les relevés à 0 et 12 heures ne doivent pas indiquer de suivi à 4 heures.
-- Pour les campagnes à 6 heures (delta = 360 - NB relevés dans la journée >= 4) les relevés ne doivent pas indiquer de suivi à 3 ou 4 heures.

-- Execute la requete et initialise la variable curseur concernée.  
open curs_campagne FOR
 SELECT
public.t_campagne_pos_cpos.cpos_ani_id,
public.t_animal_ani.ani_etiq,
public.t_campagne_pos_cpos.cpos_id,
public.t_campagne_pos_cpos.cpos_date,
public.t_campagne_pos_cpos.cpos_heure,
public.t_campagne_pos_cpos.cpos_delta,        
public.t_campagne_pos_cpos.cpos_prog6heure,
public.t_campagne_pos_cpos.cpos_prog4heure,
public.t_campagne_pos_cpos.cpos_prog3heure,
MAX(public.t_capture_cap.cap_date) AS date_capture,
count(*)OVER(partition BY t_campagne_pos_cpos.cpos_date, t_campagne_pos_cpos.cpos_ani_id) AS nb_camp_jour
 FROM
public.t_campagne_pos_cpos
INNER JOIN public.t_animal_ani ON (public.t_campagne_pos_cpos.cpos_ani_id = public.t_animal_ani.ani_id)
INNER JOIN public.t_capture_cap ON (public.t_animal_ani.ani_id = public.t_capture_cap.cap_ani_id)
 WHERE
public.t_capture_cap.cap_date <= public.t_campagne_pos_cpos.cpos_date and
        public.t_campagne_pos_cpos.cpos_annee_suivi = annee_suivi
 GROUP BY
public.t_campagne_pos_cpos.cpos_ani_id,
public.t_animal_ani.ani_etiq,
public.t_campagne_pos_cpos.cpos_id,
public.t_campagne_pos_cpos.cpos_date,
public.t_campagne_pos_cpos.cpos_heure,
public.t_campagne_pos_cpos.cpos_delta,
public.t_campagne_pos_cpos.cpos_prog6heure,
public.t_campagne_pos_cpos.cpos_prog4heure,
public.t_campagne_pos_cpos.cpos_prog3heure
 HAVING MAX(public.t_capture_cap.cap_date) BETWEEN '01/11/2003' and '31/03/2004'  
 ORDER BY
public.t_animal_ani.ani_etiq,
public.t_campagne_pos_cpos.cpos_date,
public.t_campagne_pos_cpos.cpos_heure;

LOOP
FETCH curs_campagne into rec_campagne;
nbcamp := nbcamp +1;
if found then
camp_id[3] := rec_campagne.cpos_id;
delta_nb_minute[3] := rec_campagne.cpos_delta;         

-- Analyse s'il y a changement d'animal.
if (camp_ani_id <> rec_campagne.cpos_ani_id) THEN -- Changement d'animal
raise notice 'animal : %', rec_campagne.cpos_ani_id; -- Debug info
camp_ani_id := rec_campagne.cpos_ani_id;
camp_id[1] := 0;
camp_id[2] := 0;
delta_nb_minute[1] := 0;
delta_nb_minute[2] := 0;
i:=0;
end if;

if (rec_campagne.cpos_prog4heure = true) and (rec_campagne.cpos_delta = 240) then -- Campagne à 4 heures certifiée.
UPDATE t_campagne_pos_cpos set cpos_prog6heure = false , cpos_prog3heure = false where cpos_id = rec_campagne.cpos_id;
if i=1 then -- Revient sur la première mesure d'une série
camp_id_tmp:= camp_id[2];
UPDATE t_campagne_pos_cpos set cpos_prog6heure = false , cpos_prog3heure = false where cpos_id = camp_id_tmp;
delta_nb_minute[2] := 240;
end if;
elsif (rec_campagne.cpos_prog3heure = true) and (rec_campagne.cpos_delta = 180) then -- Campagne à 3 heures certifiée.
UPDATE t_campagne_pos_cpos set cpos_prog4heure = false where cpos_id = rec_campagne.cpos_id;
if i=1 then -- Revient sur la première mesure d'une série
camp_id_tmp:= camp_id[2];
UPDATE t_campagne_pos_cpos set cpos_prog4heure = false where cpos_id = camp_id_tmp;
delta_nb_minute[2] := 180;
end if;

elsif (rec_campagne.cpos_prog6heure = true) and (rec_campagne.cpos_delta = 360) then -- Campagne à 6 heures certifiée.
UPDATE t_campagne_pos_cpos set cpos_prog4heure = false, cpos_prog3heure = false where cpos_id = rec_campagne.cpos_id;
if i=1 then -- Revient sur la première mesure d'une série
camp_id_tmp:= camp_id[2];
UPDATE t_campagne_pos_cpos set cpos_prog4heure = false, cpos_prog3heure = false where cpos_id = camp_id_tmp;
delta_nb_minute[2] := 360;
end if;

else -- Pas de certitude sur la programmation prévue de la campagne.
-- Analyse des 2 campagnes précédentes
if (rec_campagne.cpos_prog4heure = true) and ((delta_nb_minute[1] = 240) or (delta_nb_minute[2] = 240)) then  -- Au moins une des 2 campagnes précédentes est sur un pas de 4 heures
UPDATE t_campagne_pos_cpos set cpos_prog6heure = false , cpos_prog3heure = false where cpos_id = rec_campagne.cpos_id;
delta_nb_minute[3] := 240;
elsif  (rec_campagne.cpos_prog3heure = true) and ((delta_nb_minute[1] = 180) or (delta_nb_minute[2] = 180)) then  -- Au moins une des 2 campagnes précédentes est sur un pas de 3 heures
UPDATE t_campagne_pos_cpos set cpos_prog4heure = false where cpos_id = rec_campagne.cpos_id;
delta_nb_minute[3] := 180;
elsif  (rec_campagne.cpos_prog6heure = true) and ((delta_nb_minute[1] = 360) or (delta_nb_minute[2] = 360)) then -- Au moins une des 2 campagnes précédentes est sur un pas de 6 heures
UPDATE t_campagne_pos_cpos set cpos_prog4heure = false, cpos_prog3heure = false where cpos_id = rec_campagne.cpos_id;
delta_nb_minute[3] := 360;
                       else -- Trop d'incertitude donc on supprime les indicateurs éventuellement posés.
                        -- if i > 0 then -- Non validé. L'animal 21/130 démarre avec une campagne à 20H00 qui laisserai un indicateur de suivi à 4 heures
                           -- alors que cet animal est suivi toutes les 3 heures par la suite.
                            if (rec_campagne.ani_etiq = '130') or (rec_campagne.ani_etiq = '132') or (rec_campagne.ani_etiq = '134') then -- Animaux dont le suivi est à 3 heures
                        UPDATE t_campagne_pos_cpos set cpos_prog4heure = false where cpos_id = rec_campagne.cpos_id;                                
                                   ELSE -- Tous les autres animaux sont sur un suivi à 4 heures
                                   UPDATE t_campagne_pos_cpos set cpos_prog6heure = false, cpos_prog3heure = false where cpos_id = rec_campagne.cpos_id;
                               end if;

-- end if;                                
end if;
end if;

 -- Glissement des valeurs dans les tableaux
 camp_id[1] := camp_id[2];
 camp_id[2] := camp_id[3];
 delta_nb_minute[1] = delta_nb_minute[2];
 delta_nb_minute[2] = delta_nb_minute[3];

 i:= i+1;
else
 exit;
end if;
END LOOP;  
close curs_campagne;

-- Vérification des campagnes à 10 minutes (peu importe la date). Au moins N (paramètre) campagnes dans la journée avec cet indicateur pour le conserver.
-- Pour la dernière journée de mesure la valeur appliquée à N est réduite au contenu de la variable nb_mini_camp10min_dernier_jour
open curs_campagne FOR
        select cpos_ani_id,
            cpos_id,
            cpos_date,
               cpos_heure,
               cpos_prog10minutes,
               nb_camp_jour,
               date_capture,
               max (cpos_date) over (partition by cpos_ani_id, date_capture) AS Fin_Campagne
from
           (SELECT
public.t_campagne_pos_cpos.cpos_ani_id,
public.t_animal_ani.ani_etiq,
public.t_campagne_pos_cpos.cpos_id,
public.t_campagne_pos_cpos.cpos_date,
public.t_campagne_pos_cpos.cpos_heure,
public.t_campagne_pos_cpos.cpos_delta,        
public.t_campagne_pos_cpos.cpos_prog10minutes,
MAX(public.t_capture_cap.cap_date) AS date_capture,
count(*)OVER(partition BY t_campagne_pos_cpos.cpos_date, t_campagne_pos_cpos.cpos_ani_id) AS nb_camp_jour
 FROM
public.t_campagne_pos_cpos
INNER JOIN public.t_animal_ani ON (public.t_campagne_pos_cpos.cpos_ani_id = public.t_animal_ani.ani_id)
INNER JOIN public.t_capture_cap ON (public.t_animal_ani.ani_id = public.t_capture_cap.cap_ani_id)
 WHERE
public.t_capture_cap.cap_date <= public.t_campagne_pos_cpos.cpos_date and public.t_campagne_pos_cpos.cpos_prog10minutes = true and
        public.t_campagne_pos_cpos.cpos_annee_suivi = annee_suivi
 GROUP BY
public.t_campagne_pos_cpos.cpos_ani_id,
public.t_animal_ani.ani_etiq,
public.t_campagne_pos_cpos.cpos_id,
public.t_campagne_pos_cpos.cpos_date,
public.t_campagne_pos_cpos.cpos_heure,
public.t_campagne_pos_cpos.cpos_delta,
public.t_campagne_pos_cpos.cpos_prog10minutes
 -- HAVING MAX(public.t_capture_cap.cap_date) BETWEEN '01/11/2002' and '31/03/2003'  
 ORDER BY
public.t_animal_ani.ani_etiq,
public.t_campagne_pos_cpos.cpos_date,
public.t_campagne_pos_cpos.cpos_heure) sous_requete;

LOOP
FETCH curs_campagne into rec_campagne;
nbcamp := nbcamp +1;
if found then
if ((rec_campagne.nb_camp_jour < nb_mini_camp10min ) and ( rec_campagne.cpos_date <> rec_campagne.Fin_Campagne))
or ((rec_campagne.nb_camp_jour < nb_mini_camp10min_dernier_jour ) and ( rec_campagne.cpos_date = rec_campagne.Fin_Campagne))
then -- Nb de campagne à 10 minutes de la journée en cours inférieur au seuil. Sauf pour la dernière journée où le seuil est plus bas.
UPDATE t_campagne_pos_cpos set cpos_prog10minutes = false where cpos_id = rec_campagne.cpos_id;
end if;

-- Glissement des valeurs dans les tableaux
camp_id[1] := camp_id[2];
camp_id[2] := camp_id[3];
delta_nb_minute[1] = delta_nb_minute[2];
delta_nb_minute[2] = delta_nb_minute[3];

i:= i+1;
else
 exit;
end if;
END LOOP;
close curs_campagne;

-- Vérification des campagnes à 1 heure (peu importe la date). Au moins N (paramètre) campagnes dans la journée avec cet indicateur pour le conserver.
-- Pour la dernière journée de mesure la valeur appliquée à N est réduite au contenu de la variable nb_mini_camp1heure_dernier_jour
open curs_campagne FOR
        select cpos_ani_id,
            cpos_id,
            cpos_date,
               cpos_heure,
               cpos_prog1heure,
               nb_camp_jour,
               date_capture,
               max (cpos_date) over (partition by cpos_ani_id, date_capture) AS Fin_Campagne
from        
(SELECT
public.t_campagne_pos_cpos.cpos_ani_id,
public.t_animal_ani.ani_etiq,
public.t_campagne_pos_cpos.cpos_id,
public.t_campagne_pos_cpos.cpos_date,
public.t_campagne_pos_cpos.cpos_heure,
public.t_campagne_pos_cpos.cpos_delta,        
public.t_campagne_pos_cpos.cpos_prog1heure,
MAX(public.t_capture_cap.cap_date) AS date_capture,
count(*)OVER(partition BY t_campagne_pos_cpos.cpos_date, t_campagne_pos_cpos.cpos_ani_id) AS nb_camp_jour
 FROM
public.t_campagne_pos_cpos
INNER JOIN public.t_animal_ani ON (public.t_campagne_pos_cpos.cpos_ani_id = public.t_animal_ani.ani_id)
INNER JOIN public.t_capture_cap ON (public.t_animal_ani.ani_id = public.t_capture_cap.cap_ani_id)
 WHERE
public.t_capture_cap.cap_date <= public.t_campagne_pos_cpos.cpos_date and public.t_campagne_pos_cpos.cpos_prog1heure = true and
        public.t_campagne_pos_cpos.cpos_annee_suivi = annee_suivi
 GROUP BY
public.t_campagne_pos_cpos.cpos_ani_id,
public.t_animal_ani.ani_etiq,
public.t_campagne_pos_cpos.cpos_id,
public.t_campagne_pos_cpos.cpos_date,
public.t_campagne_pos_cpos.cpos_heure,
public.t_campagne_pos_cpos.cpos_delta,
public.t_campagne_pos_cpos.cpos_prog1heure
 -- HAVING MAX(public.t_capture_cap.cap_date) BETWEEN '01/11/2002' and '31/03/2003'  
 ORDER BY
public.t_animal_ani.ani_etiq,
public.t_campagne_pos_cpos.cpos_date,
public.t_campagne_pos_cpos.cpos_heure) sous_requete;

LOOP
FETCH curs_campagne into rec_campagne;
nbcamp := nbcamp +1;
if found then
if ((rec_campagne.nb_camp_jour < nb_mini_camp1heure ) and ( rec_campagne.cpos_date <> rec_campagne.Fin_Campagne))
or ((rec_campagne.nb_camp_jour < nb_mini_camp1heure_dernier_jour ) and ( rec_campagne.cpos_date = rec_campagne.Fin_Campagne))
then -- Nb de campagne à 1 heure de la journée en cours inférieur au seuil. Sauf pour la dernière journée où le seuil est plus bas.
UPDATE t_campagne_pos_cpos set cpos_prog1heure = false where cpos_id = rec_campagne.cpos_id;
end if;

-- Glissement des valeurs dans les tableaux
camp_id[1] := camp_id[2];
camp_id[2] := camp_id[3];
delta_nb_minute[1] = delta_nb_minute[2];
delta_nb_minute[2] = delta_nb_minute[3];

i:= i+1;
else
 exit;
end if;
END LOOP;  

return nbcamp ;
EXCEPTION
WHEN OTHERS THEN
raise notice 'animal : %', rec_campagne.cpos_ani_id;
   raise notice 'Erreur : % %', sqlstate, sqlerrm;
 return 0;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION public.ps_maj_indic_cpos_finish(annee_suivi integer, nb_mini_camp10min integer, nb_mini_camp1heure integer)
IS 'Fonction de positionnnement final (après un premier passage) des indicateurs de suivi des campagnes de positionnement';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 13/03/2014 13:23