.. -
.. * #%L
.. * Tutti :: Persistence Adagio (impl)
.. * $Id: referential.rst 301 2013-02-02 14:24:01Z tchemit $
.. * $HeadURL: http://svn.forge.codelutin.com/svn/tutti/trunk/src/site/rst/referential.rst $
.. * %%
.. * Copyright (C) 2012 - 2013 Ifremer
.. * %%
.. * This program is free software: you can redistribute it and/or modify
.. * it under the terms of the GNU General Public License as
.. * published by the Free Software Foundation, either version 3 of the 
.. * License, or (at your option) any later version.
.. * 
.. * This program is distributed in the hope that it will be useful,
.. * but WITHOUT ANY WARRANTY; without even the implied warranty of
.. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
.. * GNU General Public License for more details.
.. * 
.. * You should have received a copy of the GNU General Public 
.. * License along with this program.  If not, see
.. * <http://www.gnu.org/licenses/gpl-3.0.html>.
.. * #L%
.. -

===================
Tutti - Référentiel
===================

Présentation
~~~~~~~~~~~~

Ce document décrit comment sont récupérées les données du référentiel.

Chaque requète a un nom du style **[REF-XXX]**.

Les requètes métier
~~~~~~~~~~~~~~~~~~~

+-----------------------------------------------------+-------+-----------------------------------------------------+
| Nom requète                                         | Etat  | Commentaire                                         |
+=====================================================+=======+=====================================================+
| [REF-01] Liste des zones d'une série de campagne    | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-02] Liste des pays                             | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-03] Liste des strates / sstrates/ localites    | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-04] Liste des navires (scientifique)           | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-05] Liste des navires (pro)                    | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-06] Liste des engins (scientifique)            | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-07] Liste des engins (pro)                     | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-08] Liste des utilisateurs                     | OK    | Doit-on utiliser limiter aux membres de l'Ifremer ? |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-09] Liste des espèces                          | OK    |                                                     s|
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-10] Catégorie de macro-déchets                 | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-11] Catégorie de taille macro-déchets          | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-12] Catégorie H/VC                             | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-13] Catégorie Classe. Tri                      | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-14] Catégorie Sexe                             | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-15] Catégorie Maturité                         | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-16] Conversion Taille-poids                    |TODO V2| A voir si vraiment à faire                          |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-17] Liste des ports                            | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+


[REF-01] Liste des zones d'une série de campagne
------------------------------------------------

::

  SELECT
    l.id,
    l.label,
    l.name,
    l.status
  FROM LocationImpl l
  WHERE
    l.locationLevel.id = :locationLevelId
    AND l.locationClassification.id = :locationClassificationId
    AND l.status.code IN (:statusValidCode, :statusTemporaryCode)

Paramètres :

- :locationLevelId = *LocationLevelId.PROGRAM*
- :locationClassificationId = *LocationClassificationId.SECTOR*
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*

*État:* À Valider et définir la valeur de la constante.

[REF-02] Liste des pays
-----------------------

::

  SELECT
    l.id,
    l.label,
    l.name,
    l.status
  FROM LocationImpl l
  WHERE
    l.locationLevel.id = :locationLevelId
    AND l.status.code IN (:statusValidCode, :statusTemporaryCode)


Paramètres :

- :locationLevelId = *LocationLevelId.PAYS_ISO3*
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*

*État:* Valide.

[REF-03] Liste des strates / sous-strates / localités
-----------------------------------------------------

::

  SELECT
    l.id as locationId,
    l.label as locationLabel,
    l.name as locationName,
    l.locationLevel.id as locationLevelId,
    l.status as status
  FROM
    LocationImpl l,
    LocationHierarchyImpl lh
  WHERE
    l.status.code IN (:statusValidCode, :statusTemporaryCode)
    AND l.locationLevel.id = :locationLevelId
    AND l.locationClassification.id = :locationClassificationId
    AND l.id = lh.locationHierarchyPk.location.id
    AND lh.locationHierarchyPk.parent.id = :parentId
    AND lh.locationHierarchyPk.parent.locationLevel.id = :parentLocationLevelId

Paramètres :

- :parentId = *l'id de la zone / strate / sous-strate*
- :parentLocationLevelId = *LocationLevelId.PROGRAM / LocationLevelId.STRATA / LocationLevelId.SUB_STRATA*
- :locationLevelId = *LocationLevelId.STRATA / LocationLevelId.SUB_STRATA / LocationLevelId.LOCALITE*
- :locationClassificationId = *LocationClassificationId.SECTOR*
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*

*État:* Valide.

[REF-04] Liste des navires (scientifique)
-----------------------------------------

::

  SELECT
    v.code AS vesselCode,
    vrp.registrationCode AS nationalRegistrationCode,
    vrp.internationalRegistrationCode as internationalRegistrationCode,
    vf.name AS vesselName,
    v.status AS status
  FROM
  VesselImpl v
  INNER JOIN v.vesselRegistrationPeriods AS vrp
  LEFT OUTER JOIN v.vesselFeatures AS vf
  WHERE v.vesselType.id = :vesselTypeId
  AND v.status.code IN (:statusValidCode, :statusTemporaryCode)
  AND vrp.vesselRegistrationPeriodPk.startDateTime =
  (
    SELECT MAX(vrp2.vesselRegistrationPeriodPk.startDateTime)
    FROM VesselRegistrationPeriodImpl vrp2 WHERE
    vrp2.vesselRegistrationPeriodPk.vessel.code = v.code
    AND (vrp2.vesselRegistrationPeriodPk.startDateTime <= :refDate OR :refDate IS NULL)
    GROUP BY vrp2.vesselRegistrationPeriodPk.vessel.code
  )
  AND vf.startDateTime =
  (
    SELECT MAX(vf2.startDateTime)
    FROM VesselFeaturesImpl vf2 WHERE
    vf2.vessel.code = v.code
    AND (vf2.startDateTime <= :refDate OR :refDate IS NULL)
    GROUP BY vf2.vessel.code
  )

Paramètres :

- :gearClassificiationId = *VesselTypeId.SCIENTIFIC_RESEARCH_VESSEL*
- :refDate = date (ou null)
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*

*État:* Valide mais à optimiser.

[REF-05] Liste des navires (pro)
--------------------------------

::

  SELECT
    v.code AS vesselCode,
    vrp.registrationCode AS nationalRegistrationCode,
    vrp.internationalRegistrationCode as internationalRegistrationCode,
    vf.name AS vesselName,
    v.status AS status
  FROM
  VesselImpl v
  INNER JOIN v.vesselRegistrationPeriods AS vrp
  LEFT OUTER JOIN v.vesselFeatures AS vf
  WHERE v.vesselType.id = :vesselTypeId
  AND v.status.code IN (:statusValidCode, :statusTemporaryCode)
  AND vrp.vesselRegistrationPeriodPk.startDateTime =
  (
    SELECT MAX(vrp2.vesselRegistrationPeriodPk.startDateTime)
    FROM VesselRegistrationPeriodImpl vrp2 WHERE
    vrp2.vesselRegistrationPeriodPk.vessel.code = v.code
    AND (vrp2.vesselRegistrationPeriodPk.startDateTime <= :refDate OR :refDate IS NULL)
    GROUP BY vrp2.vesselRegistrationPeriodPk.vessel.code
  )
  AND vf.startDateTime =
  (
    SELECT MAX(vf2.startDateTime)
    FROM VesselFeaturesImpl vf2 WHERE
    vf2.vessel.code = v.code
    AND (vf2.startDateTime <= :refDate OR :refDate IS NULL)
    GROUP BY vf2.vessel.code
  )


Paramètres :

- :gearClassificiationId = *VesselTypeId.FISHING_VESSEL*
- :refDate = date (ou null)
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*

*État:* Valide mais à optimiser.

[REF-06] Liste des engins (scientifique)
----------------------------------------

::

  SELECT
    g.id,
    g.label,
    g.name,
    g.status AS status
  FROM GearImpl g
  WHERE
    g.gearClassification.id= :gearClassificiationId
    AND g.status.code IN (:statusValidCode, :statusTemporaryCode)


Paramètres :

- :gearClassificiationId = *GearClassificationId.SCIENTIFIC_CRUISE*
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*

*État:* Valide.

[REF-07] Liste des engins (pro)
-------------------------------

::

  SELECT
    g.id,
    g.label,
    g.name,
    g.status AS status
  FROM GearImpl g
  WHERE
    g.gearClassification.id= :gearClassificiationId
    AND g.status.code IN (:statusValidCode, :statusTemporaryCode)

Paramètres :

- :gearClassificiationId = *GearClassificationId.FAO*
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*

*État:* Valide.


[REF-08] Liste des utilisateurs
-------------------------------

::

  SELECT DISTINCT
    p.id,
    p.lastname,
    p.firstname,
    p.department.code,
    p.status
  FROM
    PersonImpl p
    LEFT OUTER JOIN p.profils pp
  WHERE
    p.status.code IN (:statusValidCode, :statusTemporaryCode)
    AND pp.id IN (
        :observerProfilId,
        :projectMemberProfilId,
        :userProfilId
    )
    AND p.department.code LIKE 'PDG-%'

Paramètres :

- :observerProfilId = *UserProfilId.OBSERVER*
- :projectMemberProfilId = *UserProfilId.PROJECT_MEMBER*
- :userProfilId = *UserProfilId.USER*
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*

*État:* Valide.

[REF-09] Liste des espèces
--------------------------

::

  SELECT
    t.id,
    t.reference_taxon_fk,
    t.complete_name,
    ti.external_code as "code rubin",
    t.is_referent
  FROM 
    taxon_name t
    LEFT JOIN transcribing_item ti on t.reference_taxon_fk = ti.object_id
  and ti.transcribing_item_type_fk  = 52

NB :
- mettre transcribing_item_type_fk en paramètre car ça changera (on est en train de la mettre en place)
- les tables transcribing dans la version actuelle du modèle HSQLDB ne sont pour l'instant pas celles indiquées dans la requête (Benoit travaille sur la mise à jour du modèle)

*État:* A valider (transformer les requêtes SQL, de plus ).

[REF-10] Catégorie de macro-déchets
-----------------------------------

[REF-T01] + [REF-T02] avec :pmfmId = *PmfmId.MARINE_LITTER_TYPE*

*État:* Valide.

[REF-11] Catégorie de taille macro-déchets
-------------------------------------------

[REF-T01] + [REF-T02] avec :pmfmId = *PmfmId.MARINE_LITTER_SIZE_CATEGORY*

*État:* Valide.

[REF-12] Catégorie H/VC
-----------------------

[REF-T01] + [REF-T02] avec :pmfmId = *PmfmId.SORTED_UNSORTED*

*État:* Valide.

[REF-13] Catégorie Classe. Tri
------------------------------

[REF-T01] + [REF-T02] avec :pmfmId = *PmfmId.SIZE_CATEGORY*

*État:* Valide.

[REF-14] Catégorie Sexe
-----------------------

[REF-T01] + [REF-T02] avec :pmfmId = *PmfmId.SEX*

*État:* Valide.

[REF-15] Catégorie Maturité
---------------------------

[REF-T01] + [REF-T02] avec :pmfmId = *PmfmId.MATURITY*

*État:* Valide.

[REF-16] Conversion Taille-poids
--------------------------------

*État:* A FAIRE.

[REF-17] Liste des ports
------------------------------------------------

::

  SELECT
    l.id,
    l.label,
    l.name,
    l.status
  FROM location l
  WHERE l.location_level_fk = :locationLevelId)
    AND l.status.code IN (:statusValidCode, :statusTemporaryCode)

Paramètres :

- :locationLevelId = *LocationLevelId.PORT*
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*

*État:* À Valider et définir la variable LocationLevelId.PORT.

Les requètes techniques
~~~~~~~~~~~~~~~~~~~~~~~

+-----------------------------------------------------+-------+-----------------------------------------------------+
| Nom requète                                         | Etat  | Commentaire                                         |
+=====================================================+=======+=====================================================+
| [REF-T01] Détail d'un psfm par son id               | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-T02] Valeurs qualitatives d'un psfm            | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-T03] Liste de tous les psfm                    | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+
| [REF-T04] Lieu statistique à partir de lat/long     | OK    |                                                     |
+-----------------------------------------------------+-------+-----------------------------------------------------+

[REF-T01] Détail d'un psfm par son id
-------------------------------------

::

  SELECT
    p.id AS pmfmId,
    p.parameter.name AS parameterName,
    p.matrix.name AS matrixName,
    p.fraction.name AS fractionName,
    p.method.name AS methodName,
    p.parameter.isAlphanumeric AS isAlphanumeric,
    p.parameter.isQualitative AS isQualitative,
    p.signifFiguresNumber,
    p.maximumNumberDecimals,
    p.precision,
    case when (p.unit.id = :unitIdNone) then '' else p.unit.symbol end AS symbol,
    p.status AS status
  FROM PmfmImpl p
  WHERE
    p.status.code IN (1, 2)
    AND p.id= :pmfmId

Paramètres :

- :pmfmId = id du psfm
- :unitIdNone = *UnitId.NONE*

*État:* Valide.

[REF-T02] Valeurs qualitatives d'un psfm
----------------------------------------

::

  SELECT
    qv.id AS id,
    case when (qv.name = qv.description) then qv.name else concat(qv.name, ' - ', qv.description) end AS name,
    qv.status AS status
  FROM
    PmfmImpl p JOIN p.qualitativeValues qv
  WHERE
    p.id= :pmfmId
    AND qv.status.code IN (1, 2)

Paramètres :

- :pmfmId = id du psfm

*État:* Valide.

[REF-T03] Liste de tous les psfm
--------------------------------

::

  SELECT
    p.id AS pmfmId,
    p.parameter.name AS parameterName,
    p.matrix.name AS matrixName,
    p.fraction.name AS fractionName,
    p.method.name AS methodName,
    p.parameter.isAlphanumeric AS isAlphanumeric,
    p.parameter.isQualitative AS isQualitative,
    p.signifFiguresNumber,
    p.maximumNumberDecimals,
    p.precision,
    case when (p.unit.id = :unitIdNone) then '' else p.unit.symbol end AS symbol,
    p.status AS status
  FROM PmfmImpl p
  WHERE
    p.status.code IN (:statusValidCode, :statusTemporaryCode)
    AND p.parameter.isCalculated = false

Paramètres :

- :unitIdNone = *UnitId.NONE*
- :statusValidCode = *StatusCode.ENABLE*
- :statusTemporaryCode = *StatusCode.TEMPORARY*


*État:* Valide.

[REF-T04] Lieu statistique (rectangle statistique) à partir de lat/long
-----------------------------------------------------------------------

Utiliser *LocationService.getLocationIdByLatLong()* (adagio-core)

*État:* Valide.