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

Function: ps_maj_emplacement

Schema

public

Owner

postgres

Descriptions

Cette fonction analyse tous les mouvements de plants (pour un projet donné) et reconstruit les différents emplacements (associant un plant à une palette d'un robot pour une période donnée).
Elle met à null le champ dat_plt_id de toutes les données issues des fichiers du projet puis tente pour chacune de ces données de trouver le plant à rattacher et effectue la mise à jour.

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_maj_emplacement (
 nom_projet varchar
)
RETURNS boolean AS
$span$
DECLARE
 nom_table_mvt VARCHAR; -- Nom de la table des mouvements
 curs_mvt refcursor; -- Curseur recevant les informations de mouvements
 rec_mvt   record; -- Enregistrement contenant le mouvement en cours de traitement
 curs_mvt_tmp refcursor; -- Curseur recevant les informations de mouvements temporaires
 rec_mvt_tmp   record; -- Enregistrement contenant le mouvement temporaire en cours de traitement

 requete text; -- Variable contenant le texte de requête à exécuter
 curs_emp refcursor; -- Curseur recevant les informations d'emplacement
 rec_emp   record; -- Enregistrement contenant l'emplacement en cours de traitement

 date_en_cours date; -- Date du mouvement en cours de traitement
 heure_en_cours TIME; -- Heure du mouvement en cours de traitement
 
 id_robot INTEGER; -- ID du robot concerné par le traitement
 id_projet INTEGER; -- ID du projet concerné par le traitement
 id_plant INTEGER; -- ID du plant concerné par le traitement
 date_fin_projet DATE; -- Date de dernier fichier du projet
 plusrecent TIMESTAMP; -- Plus recent emplacement occupé par le plant en cours de traitement
 
BEGIN
   -- Récupération du nom de la table des mouvements
   nom_table_mvt = 't_mvt_' || lower(nom_projet);

   -- Récupération ID du projet
SELECT pjt_id FROM t_projet_pjt WHERE lower(pjt_nom) = lower(nom_projet) INTO id_projet;
IF NOT found THEN
     raise notice 'Erreur - Nom projet introuvable : % ', nom_projet;
 return FALSE;
END IF;
   -- Récupération date_dernier_fichier du projet
SELECT pjt_date_dernier_fichier FROM t_projet_pjt WHERE lower(pjt_nom) = lower(nom_projet) INTO date_fin_projet;
IF NOT found THEN
     raise notice 'Erreur - Nom projet introuvable : % ', nom_projet;
 return FALSE;
END IF;    

   -- Pour les plants du projet sélectionne les emplacements qui ne sont pas les plus anciens. Ces enregistrements seront supprimés
   -- Car la reconstrcution est complète, certains mouvements ayant pu être annulés.
   requete = 'with plusancien as (SELECT
  public.tj_emplacement_plt_rob_emp.emp_plt_id,
  min(lower(tj_emplacement_plt_rob_emp.emp_periode)) AS plusvieux
FROM
  public.tj_emplacement_plt_rob_emp
  INNER JOIN public.t_plant_plt ON (public.tj_emplacement_plt_rob_emp.emp_plt_id = public.t_plant_plt.plt_id)
WHERE public.t_plant_plt.plt_pjt_id = ' || id_projet ||
' GROUP BY
  public.tj_emplacement_plt_rob_emp.emp_plt_id)
       select tj_emplacement_plt_rob_emp.emp_plt_id, lower(tj_emplacement_plt_rob_emp.emp_periode) as debut from tj_emplacement_plt_rob_emp,plusancien where (tj_emplacement_plt_rob_emp.emp_plt_id = plusancien.emp_plt_id) and (lower(tj_emplacement_plt_rob_emp.emp_periode)<> plusvieux);
       ;';
OPEN curs_emp FOR -- Récupération des emplacements à supprimer
  EXECUTE requete ;
    LOOP
       FETCH curs_emp into rec_emp;
           IF found THEN -- Supprime chaque emplacement qui n'est pas le premier utilisé par un plant du projet.
               delete from public.tj_emplacement_plt_rob_emp WHERE (tj_emplacement_plt_rob_emp.emp_plt_id = rec_emp.emp_plt_id) and (lower(tj_emplacement_plt_rob_emp.emp_periode) = rec_emp.debut) ;
ELSE
        exit;                
           END IF;
END LOOP;    

   -- Pour tous les plants du projet on réinitialise le booléen emp_sortie_definitive et la date de fin d'affectation de l'emplacement qui reste (date de fin de projet à 23H59).
update public.tj_emplacement_plt_rob_emp
    SET emp_sortie_definitive = false,
           emp_periode = tsrange(lower(tj_emplacement_plt_rob_emp.emp_periode)::TIMESTAMP, "timestamp"(date(date_fin_projet),"time"('23:59:00')))
       where emp_plt_id in (select plt_id from t_plant_plt WHERE plt_pjt_id = id_projet);
   
   -- Création de la table temporaire des mouvements
   CREATE temporary TABLE t_mvt_tmp (
tmp_plt_id INTEGER,
       tmp_rob_id INTEGER,
       tmp_palette INTEGER,
       tmp_demarrage TIMESTAMP
   );
   
   -- Initialisation des variables de rupture
   date_en_cours = '01/01/1900';
   heure_en_cours = '12:00:00';
   
   -- Algo général :
   -- Les mouvements sont triés par couple DATE/HEURE
   -- Pour un même couple DATE/HEURE on effectue les mouvements de sortie qui surviennent pour un mouvement de sortie définitive mais aussi pour un déplacement
   -- (Sortie de l'emplacement actuel puis entrée dans le nouvel emplacement) - le mouvement d'entrée dans le nouvel emplacement est mémorisé dans une table
   -- temporaire car cet emplacement n'est peut-être pas encore libre (en attente de la sortie de son occupant).
   -- Au changement de DATE/HEURE on créé les mouvements de déplacement (la seconde partie précedemment mémorisée dans la table temporaire) et on vide la table temporaire
   -- Ce mécanisme fonctionne si les permutations de plants pouvant être en conflit (par exemple inversion de 2 plants) sont rigoureusement effectuées au même horodatage;
   -- A défaut une erreur arrêtera le traitement dès qu'un conflit sera détecté.
   
   -- Tri de la table des mouvements
requete = 'SELECT * FROM ' || nom_table_mvt || ' order by mvt_date,mvt_heure;';
OPEN curs_mvt FOR -- Récupération des mouvements
  EXECUTE requete ;
    LOOP    
   FETCH curs_mvt into rec_mvt;
           IF found THEN -- Pour chaque enregistrement trouvé

               IF (rec_mvt.mvt_date <> date_en_cours) OR (rec_mvt.mvt_heure <> heure_en_cours) THEN -- on change d'horodatage
                -- Mémorisation des nouvelles valeurs pour les variables de rupture
                   date_en_cours = rec_mvt.mvt_date;
                   heure_en_cours = rec_mvt.mvt_heure;
                -- On Traite les mouvements précédemment sauvegardés dans la table temporaire
-- Attention ce traitement devra être répété en dehors de la boucle de lecture des mouvements faute
-- de quoi les mouvements de plants effectués au dernier horodatage (exceptés les sorties définitives) ne seront pas pris en compte
                   OPEN curs_mvt_tmp FOR
                    SELECT * FROM t_mvt_tmp;
                       LOOP
                       FETCH curs_mvt_tmp into rec_mvt_tmp;
                           IF found THEN -- Encore des mouvements temporaires à traiter
                            raise notice 'plant : %  -- robot %  -- palette % -- Horo % ', rec_mvt_tmp.tmp_plt_id , rec_mvt_tmp.tmp_rob_id,rec_mvt_tmp.tmp_palette,rec_mvt_tmp.tmp_demarrage;
                            INSERT INTO tj_emplacement_plt_rob_emp (emp_plt_id,emp_rob_id,emp_palette,emp_periode,emp_sortie_definitive)
                                    VALUES (rec_mvt_tmp.tmp_plt_id,rec_mvt_tmp.tmp_rob_id,rec_mvt_tmp.tmp_palette,tsrange(rec_mvt_tmp.tmp_demarrage,"timestamp"(date(date_fin_projet),"time"('23:59:00'))),FALSE);
                            ELSE -- Plus de mouvements temporaires à traiter
                                exit;                                
                           END IF;
                       END LOOP;
                    CLOSE curs_mvt_tmp; -- Ferme le curseur pour pouvoir vider la table
                       TRUNCATE t_mvt_tmp;
               END IF;
               
               raise notice 'plant : % - robot : % - palette : % - horo % % ' , rec_mvt.mvt_plt_numero, rec_mvt.mvt_nouveaurobot_prefix, rec_mvt.mvt_emp_palette, rec_mvt.mvt_date, rec_mvt.mvt_heure;
               SELECT rob_id FROM tr_robot_rob WHERE lower(rob_prefix) = lower(rec_mvt.mvt_nouveaurobot_prefix) INTO id_robot;
               SELECT plt_id FROM t_plant_plt WHERE plt_numero = rec_mvt.mvt_plt_numero and plt_pjt_id = id_projet INTO id_plant;
               SELECT max(lower(tj_emplacement_plt_rob_emp.emp_periode)) FROM  public.tj_emplacement_plt_rob_emp
                   WHERE public.tj_emplacement_plt_rob_emp.emp_plt_id = id_plant INTO plusrecent;
               -- On met à jour le plus récent emplacement concernant le plant
               -- On retire une minute à l'horodatage pour éviter les conflits avec un autre plant qui viendrai en remplacement
               UPDATE public.tj_emplacement_plt_rob_emp SET
                   emp_sortie_definitive = rec_mvt.mvt_sortie_definitive,
                   emp_periode = tsrange(lower(tj_emplacement_plt_rob_emp.emp_periode)::TIMESTAMP, "timestamp"(date(rec_mvt.mvt_date),"time"(rec_mvt.mvt_heure)) - interval ' 1 minute')         
                   WHERE (tj_emplacement_plt_rob_emp.emp_plt_id = id_plant) AND (lower(tj_emplacement_plt_rob_emp.emp_periode) = plusrecent) ;  
 
               IF (rec_mvt.mvt_sortie_definitive = FALSE)THEN -- Le mouvement n'est pas une sortie définitive => Déplacement                       
               -- On place dans la table temporaire les informations concernant le nouvel emplacement qui sera créé plus tard
               -- car n'étant peut-être pas encore libre.
                 INSERT INTO t_mvt_tmp (tmp_plt_id,tmp_rob_id,tmp_palette,tmp_demarrage)
                   VALUES (id_plant, id_robot,rec_mvt.mvt_emp_palette,"timestamp"(date(rec_mvt.mvt_date),"time"(rec_mvt.mvt_heure)));                        
               END IF;
               
 ELSE -- Plus aucun mouvement à traiter
        exit;                
           END IF; -- plus de mouvements à analyser dans la table de mouvements du projet

    END LOOP;
-- Dernier passage pour permettre de traiter des déplacements effectués au dernier horodatage
OPEN curs_mvt_tmp FOR
SELECT * FROM t_mvt_tmp;
LOOP
FETCH curs_mvt_tmp into rec_mvt_tmp;
IF found THEN -- Encore des mouvements temporaires à traiter
raise notice 'plant : %  -- robot %  -- palette % -- Horo % ', rec_mvt_tmp.tmp_plt_id , rec_mvt_tmp.tmp_rob_id,rec_mvt_tmp.tmp_palette,rec_mvt_tmp.tmp_demarrage;
INSERT INTO tj_emplacement_plt_rob_emp (emp_plt_id,emp_rob_id,emp_palette,emp_periode,emp_sortie_definitive)
VALUES (rec_mvt_tmp.tmp_plt_id,rec_mvt_tmp.tmp_rob_id,rec_mvt_tmp.tmp_palette,tsrange(rec_mvt_tmp.tmp_demarrage,"timestamp"(date(date_fin_projet),"time"('23:59:00'))),FALSE);
ELSE -- Plus de mouvements temporaires à traiter
exit;                                
END IF;
END LOOP;
CLOSE curs_mvt_tmp; -- Ferme le curseur pour pouvoir vider la table
DROP TABLE t_mvt_tmp;
   close curs_mvt ;
   
   -- Réinitialise le champ dat_plt_id de toutes les données du projet (provenant des fichiers affectés au projet
   -- avec une date de fichier incluse dans la période d'affectation robot/projet
   UPDATE t_datarobot_dat SET dat_plt_id = NULL where dat_id IN
    (
       SELECT
           public.t_datarobot_dat.dat_id
         FROM
           public.t_fichier_fic
           INNER JOIN public.tr_robot_rob ON (public.t_fichier_fic.fic_rob_id = public.tr_robot_rob.rob_id)
           INNER JOIN public.tj_affectation_rob_pjt_aff ON (public.tr_robot_rob.rob_id = public.tj_affectation_rob_pjt_aff.aff_rob_id)
           INNER JOIN public.t_projet_pjt ON (public.t_projet_pjt.pjt_id = public.tj_affectation_rob_pjt_aff.aff_pjt_id)
           INNER JOIN public.t_datarobot_dat ON (public.t_fichier_fic.fic_id = public.t_datarobot_dat.dat_fic_id)
         WHERE
           (public.t_fichier_fic.fic_date BETWEEN public.tj_affectation_rob_pjt_aff.aff_date_debut AND public.tj_affectation_rob_pjt_aff.aff_date_fin) AND
           (lower(public.t_projet_pjt.pjt_nom) = lower(nom_projet))
           );
           
-- Met à jour le champ dat_plt_id des données du projet avec l'identifiant du plant positionné à l'emplacement de la donnée si ce plant est trouvé.
   -- Les autres données resteront avec une valeur nulle signifiant qu'aucun plant ne correspond à cette donnée.
   WITH plant_trouve as ( -- Les emplacements trouvés pour les données s'il y a un plant trouvé
    SELECT
         public.t_datarobot_dat.dat_id,
         public.tj_emplacement_plt_rob_emp.emp_plt_id as new_plt
       FROM
         public.t_datarobot_dat
         INNER JOIN public.t_fichier_fic ON (public.t_datarobot_dat.dat_fic_id = public.t_fichier_fic.fic_id)
         INNER JOIN public.tj_emplacement_plt_rob_emp ON (public.t_fichier_fic.fic_rob_id = public.tj_emplacement_plt_rob_emp.emp_rob_id)
       WHERE
         public.t_datarobot_dat.dat_palette = public.tj_emplacement_plt_rob_emp.emp_palette AND
         public.t_datarobot_dat.dat_horodatage BETWEEN lower(public.tj_emplacement_plt_rob_emp.emp_periode) AND upper(public.tj_emplacement_plt_rob_emp.emp_periode) AND
         dat_id IN
             ( -- Les données du projet
             SELECT
                 public.t_datarobot_dat.dat_id
               FROM
                 public.t_fichier_fic
                 INNER JOIN public.tr_robot_rob ON (public.t_fichier_fic.fic_rob_id = public.tr_robot_rob.rob_id)
                 INNER JOIN public.tj_affectation_rob_pjt_aff ON (public.tr_robot_rob.rob_id = public.tj_affectation_rob_pjt_aff.aff_rob_id)
                 INNER JOIN public.t_projet_pjt ON (public.t_projet_pjt.pjt_id = public.tj_affectation_rob_pjt_aff.aff_pjt_id)
                 INNER JOIN public.t_datarobot_dat ON (public.t_fichier_fic.fic_id = public.t_datarobot_dat.dat_fic_id)
               WHERE
                 (public.t_fichier_fic.fic_date BETWEEN public.tj_affectation_rob_pjt_aff.aff_date_debut AND public.tj_affectation_rob_pjt_aff.aff_date_fin) AND
                 (lower(public.t_projet_pjt.pjt_nom) = lower(nom_projet))
             )
     )
     UPDATE t_datarobot_dat SET dat_plt_id = (SELECT plant_trouve.new_plt FROM plant_trouve where plant_trouve.dat_id = t_datarobot_dat.dat_id) where dat_id IN
     ( -- Les données du projet
             SELECT
                 public.t_datarobot_dat.dat_id
               FROM
                 public.t_fichier_fic
                 INNER JOIN public.tr_robot_rob ON (public.t_fichier_fic.fic_rob_id = public.tr_robot_rob.rob_id)
                 INNER JOIN public.tj_affectation_rob_pjt_aff ON (public.tr_robot_rob.rob_id = public.tj_affectation_rob_pjt_aff.aff_rob_id)
                 INNER JOIN public.t_projet_pjt ON (public.t_projet_pjt.pjt_id = public.tj_affectation_rob_pjt_aff.aff_pjt_id)
                 INNER JOIN public.t_datarobot_dat ON (public.t_fichier_fic.fic_id = public.t_datarobot_dat.dat_fic_id)
               WHERE
                 (public.t_fichier_fic.fic_date BETWEEN public.tj_affectation_rob_pjt_aff.aff_date_debut AND public.tj_affectation_rob_pjt_aff.aff_date_fin) AND
                 (lower(public.t_projet_pjt.pjt_nom) = lower(nom_projet))
             );

  return true;
  
EXCEPTION
WHEN OTHERS THEN
     raise notice 'Erreur : % %', sqlstate, sqlerrm;
     return false;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION public.ps_maj_emplacement(nom_projet varchar)
IS 'Cette fonction analyse tous les mouvements de plants (pour un projet donné) et reconstruit les différents emplacements (associant un plant à une palette d''un robot pour une période donnée).
Elle met à null le champ dat_plt_id de toutes les données issues des fichiers du projet puis tente pour chacune de ces données de trouver le plant à rattacher et effectue la mise à jour.';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 26/02/2014 11:51