This is an automated email from the git hooks/post-receive script. New commit to branch develop in repository observe. See http://git.codelutin.com/observe.git commit 24fb5d4e28552d59a6d3a45b1813f0292cd13c0a Author: Tony CHEMIT <chemit@codelutin.com> Date: Mon Aug 10 16:45:10 2015 +0200 Remplacement d'un fichier sql fournit par l'IRD (refs #7440) --- .../extra/function_enhanced_sets_school_types.sql | 132 ++++++++++++--------- 1 file changed, 78 insertions(+), 54 deletions(-) diff --git a/observe-swing/src/main/assembly/dist/obstuna-admin/extra/function_enhanced_sets_school_types.sql b/observe-swing/src/main/assembly/dist/obstuna-admin/extra/function_enhanced_sets_school_types.sql index 9129dea..fe1d14b 100644 --- a/observe-swing/src/main/assembly/dist/obstuna-admin/extra/function_enhanced_sets_school_types.sql +++ b/observe-swing/src/main/assembly/dist/obstuna-admin/extra/function_enhanced_sets_school_types.sql @@ -1,6 +1,8 @@ --- -- #%L -- ObServe :: Swing +-- $Id: function_enhanced_sets_school_types.sql 1617 2013-07-03 10:24:21Z chemit $ +-- $HeadURL: https://svn.mpl.ird.fr/osiris/observe/tags/observe-3.1.2/observe-swing/src/m... $ -- %% -- Copyright (C) 2008 - 2013 IRD, Codelutin, Tony Chemit -- %% @@ -23,21 +25,26 @@ -- Organisme: IRD (UMR EME - OT), IEO, AZTI -- Database: ObServe -- Author: Pascal CAUQUIL --- Creation date: 18/01/2013 +-- -- Description: Given a set's topiaid (main source field: c.topiaid) and a separator character, this function returns 1..n FAD types, separated by the separator, -- where possible FAD types were decided during the 2012 Sukkarieta meeting (which brought together RFMOs and observation programs actors). -- Possible codes are : --- * Anchored FAD (PAY): equivalent to 'objetflottant in (7)' --- * Drifting FAD (FAD): equivalent to 'objetflottant in (6, 15, 16, 18)' --- * Natural log (LOG): equivalent to 'objetflottant in (1,2,3,4,5,10,11,12,13,14,17) OR systemeobserve in (5,6,7,8)' --- * Whale shark (WHS): equivalent to 'systemeobserve in (12)' --- * Dolphin (DOL): equivalent to 'systemeobserve in (9)' --- * Whale (MAM): equivalent to 'systemeobserve in (10,11)' --- * Seamount (SMT): equivalent to 'systemeobserve in (18,19)' +-- * Anchored FAD (PAY): equivalent to 'objecttype in (7)' +-- * Drifting FAD (FAD): equivalent to 'objecttype in (6,15,16,18,19,20,21,22) OR observedsystem in (5,6)' +-- * Natural log (LOG): equivalent to 'objecttype in (1,2,3,4,5,10,11,12,13,14,17) OR observedsystem in (7,8)' +-- * Whale shark (WHS): equivalent to 'observedsystem in (12)' +-- * Dolphin (DOL): equivalent to 'observedsystem in (9)' +-- * Whale (MAM): equivalent to 'observedsystem in (10,11)' +-- * Seamount (SMT): equivalent to 'observedsystem in (18,19)' -- * Free school (FSC): equivalent to 'all sets satisfying none of the previous criterias' +-- +-- Creation date: 18/01/2013 +-- Modification : 05/08/2015 - Corrected some codes classification between FAD & LOG; included new codes from objecttype (19 to 22) +-- Modification : 05/08/2015 - Renamed from enhanced_school_type to ot_enhanced_school_type +-- Modification : 05/08/2015 - Translation V4 database model -- ------------------------------------------------------------------------------------------------------- -CREATE OR REPLACE FUNCTION enhanced_school_type(set_topiaid varchar, separator char(1)) RETURNS varchar AS $$ +CREATE OR REPLACE FUNCTION ot_enhanced_school_type(set_topiaid varchar, separator char(1)) RETURNS varchar AS $$ DECLARE r1 RECORD; r2 RECORD; @@ -46,29 +53,38 @@ DECLARE BEGIN -- Working on table 'objetflottant' - FOR r1 IN SELECT ARRAY[ofl.type] AS ofl FROM activite a INNER JOIN objetflottant ofl ON (ofl.activite=a.topiaid) WHERE a.calee = $1 LOOP - IF r1.ofl[1] IN ( 'fr.ird.observe.entities.referentiel.ObjectType#1239832686564#0.44929275294648374') --PAY:12 + FOR r1 IN SELECT ARRAY[ofl.objecttype] AS ofl FROM observe_seine.activity a INNER JOIN observe_seine.floatingobject ofl ON (ofl.activity=a.topiaid) WHERE a.set = $1 LOOP + IF r1.ofl[1] IN ( 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686564#0.44929275294648374') --PAY:12 THEN IF (fads @> '{PAY}') THEN ELSE - RAISE NOTICE 'adding PAY, %', fads; + --RAISE NOTICE 'adding PAY, %', fads; fads := array_append(fads, 'PAY'); END IF; - ELSE IF r1.ofl[1] IN ( 'fr.ird.observe.entities.referentiel.ObjectType#1239832686564#0.08078169174465666', --FAD:6 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686565#0.2649661094338329', --FAD:15 - 'fr.ird.observe.entities.referentiel.ObjectType#1303917875154#0.48066228062107885') --FAD:18 + ELSE IF r1.ofl[1] IN ( + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686564#0.08078169174465666', --FAD:6 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686565#0.2649661094338329', --FAD:15 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1303917875154#0.48066228062107885', --FAD:18 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1303917219716#0.5212308393273027', --FAD:16 + + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1339680183257#0.5748716212194649', --FAD:19 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1339680260257#0.6852768899908745', --FAD:20 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1339680288164#0.00913474217675625', --FAD:21 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1389776562472#0.1755770199408313' --FAD:22 + ) THEN IF (fads @> '{FAD}') THEN ELSE fads := array_append(fads, 'FAD'); END IF; - ELSE IF r1.ofl[1] IN ( 'fr.ird.observe.entities.referentiel.ObjectType#1239832686563#0.688534215381033', --LOG:1 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686564#0.13889047716567404', --LOG:2 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686564#0.23119123436653222', --LOG:3 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686564#0.002691776291490311', --LOG:4 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686564#0.8004309360361301', --LOG:5 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686565#0.28689628152638935', --LOG:10 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686565#0.8517078464341938', --LOG:11 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686565#0.05946979309573974', --LOG:12 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686565#0.07397538492536193', --LOG:13 - 'fr.ird.observe.entities.referentiel.ObjectType#1239832686565#0.13571777026332488', --LOG:14 - 'fr.ird.observe.entities.referentiel.ObjectType#1303917503831#0.051798951857539355') --LOG:17 + ELSE IF r1.ofl[1] IN ( + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686563#0.688534215381033', --LOG:1 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686564#0.13889047716567404', --LOG:2 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686564#0.23119123436653222', --LOG:3 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686564#0.002691776291490311', --LOG:4 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686564#0.8004309360361301', --LOG:5 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686565#0.28689628152638935', --LOG:10 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686565#0.8517078464341938', --LOG:11 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686565#0.05946979309573974', --LOG:12 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686565#0.07397538492536193', --LOG:13 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1239832686565#0.13571777026332488', --LOG:14 + 'fr.ird.observe.entities.referentiel.seine.ObjectType#1303917503831#0.051798951857539355') --LOG:17 THEN IF (fads @> '{LOG}') THEN ELSE fads := array_append(fads, 'LOG'); END IF; @@ -79,36 +95,44 @@ BEGIN END LOOP; -- Working on table 'activite_systemeobserve' - FOR r2 IN SELECT ARRAY[aso.systemeobserve] AS aso FROM activite a INNER JOIN activite_systemeobserve aso ON (aso.activite=a.topiaid) WHERE a.calee = $1 LOOP + FOR r2 IN SELECT ARRAY[aso.observedsystem] AS aso FROM observe_seine.activity a INNER JOIN observe_seine.activity_observedsystem aso ON (aso.activity=a.topiaid) WHERE a.set = $1 LOOP --RAISE NOTICE 'r2.aso[1] vaut %', r2.aso[1]; - IF r2.aso[1] IN ( --'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686427#0.5015912524161882', --LOG:1 - --'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686427#0.4875966267297659', --LOG:2 - --'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686427#0.7251934343058472', --LOG:3 - --'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686427#0.6223326827132584', --LOG:4 - 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686427#0.15391195068515717', --LOG:5 - 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686427#0.725007998980404', --LOG:6 - 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686427#0.33083751936947536', --LOG:7 - 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686428#0.31736040820946176' ) --LOG:8 - THEN IF (fads @> '{LOG}') THEN ELSE - fads := array_append(fads, 'LOG'); + IF r2.aso[1] IN ( + 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686427#0.15391195068515717', --LOG:5 + 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686427#0.725007998980404' --LOG:6 + ) + THEN IF (fads @> '{FAD}') THEN ELSE + fads := array_append(fads, 'FAD'); END IF; - ELSE IF r2.aso[1] IN ( 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686428#0.9217864901728908') --WHS:12 - THEN IF (fads @> '{WHS}') THEN ELSE - fads := array_append(fads, 'WHS'); - END IF; - ELSE IF r2.aso[1] IN ( 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686428#0.6854047537933218') --DOL:9 - THEN IF (fads @> '{DOL}') THEN ELSE - fads := array_append(fads, 'DOL'); - END IF; - ELSE IF r2.aso[1] IN ( 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686428#0.9425305842216437', --MAM:10 - 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686428#0.9395222812356602') --MAM:11 - THEN IF (fads @> '{MAM}') THEN ELSE - fads := array_append(fads, 'MAM'); + ELSE IF r2.aso[1] IN ( + 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686427#0.33083751936947536', --LOG:7 + 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.31736040820946176' --LOG:8 + ) + THEN IF (fads @> '{LOG}') THEN ELSE + fads := array_append(fads, 'LOG'); + END IF; + ELSE IF r2.aso[1] IN ( 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908') --WHS:12 + THEN IF (fads @> '{WHS}') THEN ELSE + fads := array_append(fads, 'WHS'); + END IF; + ELSE IF r2.aso[1] IN ( 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.6854047537933218') --DOL:9 + THEN IF (fads @> '{DOL}') THEN ELSE + fads := array_append(fads, 'DOL'); END IF; - ELSE IF r2.aso[1] IN ( 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686430#0.0707482498533355', --SMT:18 - 'fr.ird.observe.entities.referentiel.ObservedSystem#1239832686430#0.9306046432586412') --SMT:19 - THEN IF (fads @> '{SMT}') THEN ELSE - fads := array_append(fads, 'SMT'); + ELSE IF r2.aso[1] IN ( + 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9425305842216437', --MAM:10 + 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' --MAM:11 + ) + THEN IF (fads @> '{MAM}') THEN ELSE + fads := array_append(fads, 'MAM'); + END IF; + ELSE IF r2.aso[1] IN ( + 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686430#0.0707482498533355', --SMT:18 + 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686430#0.9306046432586412' --SMT:19 + ) + THEN IF (fads @> '{SMT}') THEN ELSE + fads := array_append(fads, 'SMT'); + END IF; END IF; END IF; END IF; @@ -130,4 +154,4 @@ BEGIN END; $$ LANGUAGE plpgsql; ---DROP FUNCTION enhanced_school_type(varchar, char(1)); +--DROP FUNCTION ot_enhanced_school_type(varchar, char(1)); -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@list.forge.codelutin.com>.