This is an automated email from the git hooks/post-receive script. New commit to branch feature/7498 in repository observe. See http://git.codelutin.com/observe.git commit ee473651328a530d1b070929b7d71557b73fa375 Author: Sylvain Bavencoff <bavencoff@codelutin.com> Date: Thu Sep 3 10:12:20 2015 +0200 implantation du service de generation des rapport + test (refs #7498) --- .../src/main/filters/observe-reports.properties | 854 +++++++++++++++++++++ 1 file changed, 854 insertions(+) diff --git a/observe-services-api/src/main/filters/observe-reports.properties b/observe-services-api/src/main/filters/observe-reports.properties new file mode 100644 index 0000000..732b367 --- /dev/null +++ b/observe-services-api/src/main/filters/observe-reports.properties @@ -0,0 +1,854 @@ +### +# #%L +# ObServe :: Business +# %% +# Copyright (C) 2008 - 2010 IRD, Codelutin, Tony Chemit +# %% +# 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% +### + +################################################################################ +## Liste des activit\u00e9s avec des comments +################################################################################ + +report.activityWithComment.name=Activit\u00e9s avec comment et leurs positions +report.activityWithComment.description=Afficher les activit\u00e9s avec comment et leurs positions g\u00e9ographiques +report.activityWithComment.columns=Jour - Heure observation, Latitude, Longitude, Activit\u00e9, Commentaire +report.activityWithComment.request.1=0,0|row|\ + Select concat(day(r.date), '/', month(r.date), '/', year(r.date), ' ', hour(a.time), ':', minute(a.time)), a.latitude, a.longitude, ab.label2, a.comment \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.vesselActivitySeine ab \ + Where \ + m.id = :tripId \ + and a.comment is not null \ + Order By r.date, a.time + +################################################################################ +## Liste de toutes les activit\u00e9s et leurs positions +################################################################################ + +report.allActivitys.name=Toutes les activit\u00e9s et leurs positions +report.allActivitys.description=Afficher toutes les activit\u00e9s et leurs positions g\u00e9ographiques +report.allActivitys.columns=Jour - Heure observation, Latitude, Longitude, Activit\u00e9, Commentaire +report.allActivitys.request.1=0,0|row|\ + Select concat(day(r.date), '/', month(r.date), '/', year(r.date), ' ', hour(a.time), ':', minute(a.time)), a.latitude, a.longitude, ab.label2, a.comment \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.vesselActivitySeine ab \ + Where \ + m.id = :tripId \ + Order By r.date, a.time + +################################################################################ +## Nombre de cal\u00e9es et captures journali\u00e8res d'une mar\u00e9e +################################################################################ + +report.dailySetAndCatch.name=Nombre de cal\u00e9es et captures journali\u00e8res d'une mar\u00e9e +report.dailySetAndCatch.description=Afficher le nombre de cal\u00e9es et les captures journali\u00e8res d\u2019une cal\u00e9e +report.dailySetAndCatch.columns=Jour observation,Nombre de cal\u00e9es ,Captures thon +report.dailySetAndCatch.request.1=0,0|row|\ + Select concat(day(r.date), '/', month(r.date), '/', year(r.date)), Count(distinct a), Sum(ct.catchWeight) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine s \ + left join s.targetCatch as ct \ + Where \ + m.id = :tripId \ + Group By r.date \ + Order By r.date + +################################################################################ +## Utilisation des DCP +################################################################################ + +report.dcpUsage.name=Utilisation des DCP +report.dcpUsage.description=Tableau r\u00e9capitulatif du nombre de DCP visit\u00e9s selon l\u2019action effectu\u00e9e (visites avec et sans p\u00eache, mis \u00e0 l\u2019eau, r\u00e9cuperation), et tortues observ\u00e9es +report.dcpUsage.columns=Type de DCP (Tableau 8),Nombre visit\u00e9s,Nombre p\u00each\u00e9s,Nombre mis \u00e0 l\u2019eau seuls,Nombre renforc\u00e9s par radeau balis\u00e9,R\u00e9cup\u00e9r\u00e9 sans p\u00eache,Nombre de tortues associ\u00e9es +report.dcpUsage.repeatVariable.typeObjetId=java.lang.String|\ + Select to.id \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ + Join dcp.objectType to \ + Where \ + m.id = :tripId \ + Order by to.code + +report.dcpUsage.request.1=0,0|row|\ + Select concat(str(to.code) , ' - ', to.label2) \ + From ObjectTypeImpl to \ + Where \ + to.id = :typeObjetId +report.dcpUsage.request.1.repeat=typeObjetId|column + +report.dcpUsage.request.2=1,0|row|\ + Select Count(dcp) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ + Where \ + m.id = :tripId \ + and dcp.objectType.id = :typeObjetId \ + and dcp.objectOperation.id = 'fr.ird.observe.entities.referentiel.seine.ObjectOperation#1239832686249#0.8268884472438458' +report.dcpUsage.request.2.repeat=typeObjetId|column + +report.dcpUsage.request.3=2,0|row|\ + Select Count(dcp) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ + Where \ + m.id = :tripId \ + and dcp.objectType.id = :typeObjetId \ + and dcp.objectOperation.id = 'fr.ird.observe.entities.referentiel.seine.ObjectOperation#1239832686249#0.8431519556575698' +report.dcpUsage.request.3.repeat=typeObjetId|column + +report.dcpUsage.request.4=3,0|row|\ + Select Count(dcp) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ + Where \ + m.id = :tripId \ + and dcp.objectType.id = :typeObjetId \ + and dcp.objectOperation.id = 'fr.ird.observe.entities.referentiel.seine.ObjectOperation#1239832686248#0.8669327599318251' +report.dcpUsage.request.4.repeat=typeObjetId|column + +report.dcpUsage.request.5=4,0|row|\ + Select Count(dcp) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ + Where \ + m.id = :tripId \ + and dcp.objectType.id = :typeObjetId \ + and dcp.objectFate.id = 'fr.ird.observe.entities.referentiel.seine.ObjectFate#1396860761530#0.8869464242156488' \ + and dcp.objectOperation.id != 'fr.ird.observe.entities.referentiel.seine.ObjectOperation#1239832686248#0.8669327599318251' +report.dcpUsage.request.5.repeat=typeObjetId|column + +report.dcpUsage.request.6=5,0|row|\ + Select Count(dcp) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ + Where \ + m.id = :tripId \ + and dcp.objectType.id = :typeObjetId \ + and dcp.objectOperation.code = 'fr.ird.observe.entities.referentiel.seine.ObjectOperation#1239832686249#0.7838704130950722' +report.dcpUsage.request.6.repeat=typeObjetId|column + +report.dcpUsage.request.7=6,0|row| \ + Select Sum(efo.count) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ + Join dcp.objectObservedSpecies efo \ + Where \ + m.id = :tripId \ + and dcp.objectType.id = :typeObjetId \ + and efo.species.speciesGroup.id = 'fr.ird.observe.entities.referentiel.SpeciesGroup#1239832683690#0.24333033683679461' +report.dcpUsage.request.7.repeat=typeObjetId|column + +################################################################################ +## Nombre des cal\u00e9es selon le type d'association +################################################################################ + +report.setByAssociation.name=Nombre de cal\u00e9es selon le type d\u2019association +report.setByAssociation.description=Afficher la r\u00e9partitions des cal\u00e9es selon le type d\u2019association l\u2019issue du coup. Les coups sur BL baleine et BO requin-baleine sont sp\u00e9cifi\u00e9s. +report.setByAssociation.columns=BL sans baleine, BL avec baleine, BO avec requin-baleine, BO sans requin-baleine, Total +report.setByAssociation.rows=Coups positifs, Coups nuls, Total +report.setByAssociation.operations.1=SumIntRow +report.setByAssociation.operations.2=SumIntColumn +report.setByAssociation.request.1=0,0|column|\ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and a.setSeine.schoolType = 2 \ + and a.setSeine.reasonForNullSet is null \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' \ + and os not in elements(a.observedSystem) + +report.setByAssociation.request.2=0,1|column|\ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and a.setSeine.schoolType = 2 \ + and a.setSeine.reasonForNullSet is not null \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' \ + and os not in elements(a.observedSystem) + +report.setByAssociation.request.3=1,0|column|\ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and a.setSeine.schoolType = 2 \ + and a.setSeine.reasonForNullSet is null \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' \ + and os in elements(a.observedSystem) + +report.setByAssociation.request.4=1,1|column|\ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and a.setSeine.schoolType = 2 \ + and a.setSeine.reasonForNullSet is not null \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' \ + and os in elements(a.observedSystem) + +report.setByAssociation.request.5=2,0|column|\ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and a.setSeine.schoolType = 1 \ + and a.setSeine.reasonForNullSet is null \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' \ + and os in elements(a.observedSystem) + +report.setByAssociation.request.6=2,1|column|\ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and a.setSeine.schoolType = 1 \ + and a.setSeine.reasonForNullSet is not null \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' \ + and os in elements(a.observedSystem) + +report.setByAssociation.request.7=3,0|column|\ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and a.setSeine.schoolType = 1 \ + and a.setSeine.reasonForNullSet is null \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' \ + and os not in elements(a.observedSystem) + +report.setByAssociation.request.8=3,1|column|\ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and a.setSeine.schoolType = 1 \ + and a.setSeine.reasonForNullSet is not null \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' \ + and os not in elements(a.observedSystem) + +################################################################################ +## Captures thon par type d'association +################################################################################ + +report.targetCatchByAssociation.name=Capture de thon selon le type d'association (en t) +report.targetCatchByAssociation.description=Afficher la r\u00e9partitions des captures de thons selon le type d'association +report.targetCatchByAssociation.columns=YFT, SKJ, BET, LTA, FRI, Autres, Total +report.targetCatchByAssociation.rows=BL sans baleine, BL avec baleine, BO avec requin-baleine, BO sans requin-baleine, Total +report.targetCatchByAssociation.operations.1=SumRow +report.targetCatchByAssociation.operations.2=SumColumn + +# ligne 1 +report.targetCatchByAssociation.request.1=0,0|row|\ + Select \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then 0.0 \ + Else ct.catchWeight End) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 2 \ + Join c.targetCatch ct \ + with ct.discarded = false, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' \ + and os not in elements(a.observedSystem) + +# ligne 2 +report.targetCatchByAssociation.request.7=0,1|row|\ + Select \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then 0.0 \ + Else ct.catchWeight End) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 2 \ + Join c.targetCatch ct \ + with ct.discarded = false, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' \ + and os in elements(a.observedSystem) + +# ligne 3 +report.targetCatchByAssociation.request.13=0,2|row|\ + Select \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then 0.0 \ + Else ct.catchWeight End) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 1 \ + Join c.targetCatch ct \ + with ct.discarded = false, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' \ + and os in elements(a.observedSystem) + +# ligne 4 +report.targetCatchByAssociation.request.19=0,3|row|\ + Select \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then 0.0 \ + Else ct.catchWeight End) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 1 \ + Join c.targetCatch ct \ + with ct.discarded = false, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' \ + and os not in elements(a.observedSystem) + +################################################################################ +## Rejets thons selon le type d'association +################################################################################ + +report.targetDiscardedByAssociation.name=Rejets de thons selon le type d\u2019association (en t) +report.targetDiscardedByAssociation.description=Afficher la r\u00e9partitions des rejets de thons selon le type d'association +report.targetDiscardedByAssociation.columns=YFT, SKJ, BET, LTA, FRI, Autres, Total +report.targetDiscardedByAssociation.rows=BL sans baleine, BL avec baleine, BO avec requin-baleine, BO sans requin-baleine, Total +report.targetDiscardedByAssociation.operations.1=SumRow +report.targetDiscardedByAssociation.operations.2=SumColumn + +# ligne 1 +report.targetDiscardedByAssociation.request.1=0,0|row|\ + Select \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then 0.0 \ + Else ct.catchWeight End) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 2 \ + Join c.targetCatch ct \ + with ct.discarded = true, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' \ + and os not in elements(a.observedSystem) + +# ligne 2 +report.targetDiscardedByAssociation.request.7=0,1|row|\ + Select \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then 0.0 \ + Else ct.catchWeight End) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 2 \ + Join c.targetCatch ct \ + with ct.discarded = true, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' \ + and os in elements(a.observedSystem) + +# ligne 3 +report.targetDiscardedByAssociation.request.13=0,2|row|\ + Select \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then 0.0 \ + Else ct.catchWeight End) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 1 \ + Join c.targetCatch ct \ + with ct.discarded = true, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' \ + and os in elements(a.observedSystem) + +# ligne 4 +report.targetDiscardedByAssociation.request.19=0,3|row|\ + Select \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \ + Else 0.0 End), \ + Sum(CASE ct.weightCategory.species.topiaId When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.8943253454598569' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685474#0.975344121171992' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685475#0.13349466123905152' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.8024257002747615' Then 0.0 \ + When 'fr.ird.observe.entities.referentiel.Species#1239832685477#0.3846921632590058' Then 0.0 \ + Else ct.catchWeight End) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 1 \ + Join c.targetCatch ct \ + with ct.discarded = true, \ + ObservedSystemImpl os \ + Where \ + m.id = :tripId \ + and os.topiaId = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' \ + and os not in elements(a.observedSystem) + +################################################################################ +## Captures accessoires observ\u00e9es +################################################################################ + + +report.accessoryCatch.name=Liste des captures accessoires selon le type de banc, filtr\u00e9es par groupe +report.accessoryCatch.description=Afficher les captures accessoires par groupe d'esp\u00e8ce\nLes poids sont exprim\u00e9s en tonnes. +report.accessoryCatch.columns=Esp\u00e8ce, Banc libre, Banc objet +report.accessoryCatch.variable.speciesGroup=fr.ird.observe.services.dto.referential.SpeciesGroupDto|From SpeciesGroupImpl ge Order By ge.code +report.accessoryCatch.repeatVariable.speciesId=java.lang.String|\ + Select e.id From SpeciesImpl e \ + Where e.speciesGroup.id = :speciesGroup \ + Order By e.homeId + +report.accessoryCatch.request.1=0,0|row|\ + Select \ + concat('[FAO]', (case when e.faoCode is not null then e.faoCode else '-' end), \ + ' [sc]', (case when e.scientificLabel is not null then e.scientificLabel else '-' end), \ + ' [fr]', (case when e.label2 is not null then e.label2 else '-' end)) \ + From SpeciesImpl e \ + Where e.id = :speciesId +report.accessoryCatch.request.1.repeat=speciesId|column + +report.accessoryCatch.request.2=1,0|row|\ + Select \ + case when Count(ca) > 0 then \ + concat('+', \ + case when Sum(ca.totalCount) is not null then concat(' Effectif : ', str(Sum(ca.totalCount))) else '' end , \ + case when Sum(ca.catchWeight) is not null then concat(' Poids total (t) : ' , str(Sum(ca.catchWeight))) else '' end ) \ + else '-' end \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 2 \ + Join c.nonTargetCatch ca \ + with ca.species.id = :speciesId \ + Where m.id = :tripId +report.accessoryCatch.request.2.repeat=speciesId|column + +report.accessoryCatch.request.3=2,0|row|\ + Select \ + case when Count(ca) > 0 then \ + concat('+', \ + case when Sum(ca.totalCount) is not null then concat(' Effectif : ', str(Sum(ca.totalCount))) else '' end , \ + case when Sum(ca.catchWeight) is not null then concat(' Poids total (t) : ' , str(Sum(ca.catchWeight))) else '' end ) \ + else '-' end \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + with c.schoolType = 1 \ + Join c.nonTargetCatch ca \ + with ca.species.id = :speciesId \ + Where m.id = :tripId +report.accessoryCatch.request.3.repeat=speciesId|column + +################################################################################ +## Captures accessoires par speciesGroup d'esp\u00e8ces +################################################################################ + +report.accessoryCatchByGroup.name=D\u00e9nombrement des captures accessoires et devenir, filtr\u00e9s par groupe +report.accessoryCatchByGroup.description=Afficher les nombres de captures accessoires par groupe d'esp\u00e8ce selon le type de banc et le devenir +report.accessoryCatchByGroup.columns=Esp\u00e8ce, Total BL, Total BO, Sorti vivant/\u00e9chapp\u00e9, Sorti mort, Rejet\u00e9 vivant, Rejet\u00e9 mort, Partiellement conserv\u00e9, Cuve, Cuisine, Autre +report.accessoryCatchByGroup.variable.speciesGroup=fr.ird.observe.services.dto.referential.SpeciesGroupDto|From SpeciesGroupImpl ge Order By ge.code + +report.accessoryCatchByGroup.request.1=0,0|row|\ + Select \ + concat(\ + '[FAO]', (case when e.faoCode is not null then e.faoCode else '-' end), \ + ' [sc]', (case when e.scientificLabel is not null then e.scientificLabel else '-' end), \ + ' [fr]', (case when e.label2 is not null then e.label2 else '-' end)), \ + Sum(case c.schoolType when 2 then ca.totalCount else 0.0 end), \ + Sum(case c.schoolType when 1 then ca.totalCount else 0.0 end), \ + Sum(case ca.speciesFate.id when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.9931091059863436' then ca.totalCount \ + when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683618#0.06155887805368032' then ca.totalCount \ + else 0.0 end), \ + Sum(case ca.speciesFate.id when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.11883784875534997' then ca.totalCount \ + else 0.0 end), \ + Sum(case ca.speciesFate.id when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.5308862132841506' then ca.totalCount \ + else 0.0 end), \ + Sum(case ca.speciesFate.id when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.6250731662108877' then ca.totalCount \ + else 0.0 end), \ + Sum(case ca.speciesFate.id when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683620#0.46609703818634485' then ca.totalCount \ + else 0.0 end), \ + Sum(case ca.speciesFate.id when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.5722739932065866' then ca.totalCount \ + else 0.0 end), \ + Sum(case ca.speciesFate.id when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683621#0.6728026426066158' then ca.totalCount \ + else 0.0 end), \ + Sum(case ca.speciesFate.id when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.9931091059863436' then 0.0 \ + when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683618#0.06155887805368032' then 0.0 \ + when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.11883784875534997' then 0.0 \ + when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.5308862132841506' then 0.0 \ + when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.6250731662108877' then 0.0 \ + when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683620#0.46609703818634485' then 0.0 \ + when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.5722739932065866' then 0.0 \ + when 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683621#0.6728026426066158' then 0.0 \ + else ca.totalCount end) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.setSeine c \ + Join c.nonTargetCatch ca \ + Join ca.species e \ + with e.speciesGroup.id = :speciesGroup \ + Where m.id = :tripId \ + Group by e \ + Order By e.homeId + +########################################################### +## Distribution des tailles par espèces non ciblées +########################################################### + +report.nonTargetDistributionLengths.name=Distribution des tailles par esp\u00e8ces non cibl\u00e9es +report.nonTargetDistributionLengths.description=Afficher la nombre d'individus mesur\u00e9s par classe de taille pour les esp\u00e8ces non cibl\u00e9es +report.nonTargetDistributionLengths.columns=Classe de taille,Effectif +report.nonTargetDistributionLengths.operations.1=GroupByLength +report.nonTargetDistributionLengths.variable.speciesId=fr.ird.observe.entities.referentiel.Species|\ +Select distinct ntl.species \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.nonTargetSample nts \ +Join nts.nonTargetLength ntl \ +Where t.id = :tripId +report.nonTargetDistributionLengths.request.1=0,0|row| \ +Select ntl.length, sum(ntl.count) \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.nonTargetSample nts \ +Join nts.nonTargetLength ntl \ +Where t.id = :tripId \ +And ntl.species.id = :speciesId \ +Group By ntl.length \ +Order By ntl.length + +#################################################################### +## Distribution des tailles des conservés par espèces ciblées (LD1) +#################################################################### + +report.targetStoredDistributionLengthsLD1.name=Distribution des tailles des captures par esp\u00e8ces cibl\u00e9es (mesure LD1) +report.targetStoredDistributionLengthsLD1.description=Afficher le nombre d'individus captur\u00e9s, mesur\u00e9s par classe de taille LD1 pour les esp\u00e8ces cibl\u00e9es +report.targetStoredDistributionLengthsLD1.columns=Classe de taille,Effectif +report.targetStoredDistributionLengthsLD1.operations.1=GroupByLength +report.targetStoredDistributionLengthsLD1.variable.speciesId=fr.ird.observe.entities.referentiel.Species|\ +Select distinct tl.species \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.targetSample ts \ +Join ts.targetLength tl \ +Where t.id = :tripId \ +And tl.measureType = 1 \ +And ts.discarded = false +report.targetStoredDistributionLengthsLD1.request.1=0,0|row| \ +Select tl.length , Sum(tl.count) \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.targetSample ts \ +Join ts.targetLength tl \ +Where t.id = :tripId \ + And tl.species.id = :speciesId \ + And tl.measureType = 1 \ + And ts.discarded = false \ +Group By tl.length \ +Order By tl.length + +################################################################ +## Distribution des tailles des rejets par espèces ciblées (LD1) +################################################################ + +report.targetDiscardedDistributionLengthsLD1.name=Distribution des tailles des rejets par esp\u00e8ces cibl\u00e9es (mesure LD1) +report.targetDiscardedDistributionLengthsLD1.description=Afficher la nombre d'individus rejet\u00e9s, mesur\u00e9s par classe de taille LD1 pour les esp\u00e8ces cibl\u00e9es +report.targetDiscardedDistributionLengthsLD1.columns=Classe de taille,Effectif +report.targetDiscardedDistributionLengthsLD1.operations.1=GroupByLength +report.targetDiscardedDistributionLengthsLD1.variable.speciesId=fr.ird.observe.entities.referentiel.Species|\ +Select distinct tl.species \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.targetSample ts \ +Join ts.targetLength tl \ +Where t.id = :tripId \ +And tl.measureType = 1 \ +And ts.discarded = true +report.targetDiscardedDistributionLengthsLD1.request.1=0,0|row| \ +Select tl.length , Sum(tl.count) \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.targetSample ts \ +Join ts.targetLength tl \ +Where t.id = :tripId \ + And tl.species.id = :speciesId \ + And tl.measureType = 1\ + And ts.discarded = true \ +Group By tl.length \ +Order By tl.length + +############################################################### +## Distribution des tailles des conservés par espèces ciblées (LF) +############################################################### + +report.targetStoredDistributionLengthsLF.name=Distribution des tailles des captures par esp\u00e8ces cibl\u00e9es (mesure LF) +report.targetStoredDistributionLengthsLF.description=Afficher le nombre d'individus captur\u00e9s, mesur\u00e9s par classe de taille LF pour les esp\u00e8ces cibl\u00e9es +report.targetStoredDistributionLengthsLF.columns=Classe de taille,Effectif +report.targetStoredDistributionLengthsLF.operations.1=GroupByLength +report.targetStoredDistributionLengthsLF.variable.speciesId=fr.ird.observe.entities.referentiel.Species|\ +Select distinct tl.species \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.targetSample ts \ +Join ts.targetLength tl \ +Where t.id = :tripId \ +And tl.measureType = 2 \ +And ts.discarded = false +report.targetStoredDistributionLengthsLF.request.1=0,0|row| \ +Select tl.length , Sum(tl.count) \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.targetSample ts \ +Join ts.targetLength tl \ +Where t.id = :tripId \ + And tl.species.id = :speciesId \ + And tl.measureType = 2 \ + And ts.discarded = false \ +Group By tl.length \ +Order By tl.length + +############################################################### +## Distribution des tailles des rejets par espèces ciblées (LF) +############################################################### + +report.targetDiscardedDistributionLengthsLF.name=Distribution des tailles des rejets par esp\u00e8ces cibl\u00e9es (mesure LF) +report.targetDiscardedDistributionLengthsLF.description=Afficher le nombre d'individus rejet\u00e9s, mesur\u00e9s par classe de taille LF pour les esp\u00e8ces cibl\u00e9es +report.targetDiscardedDistributionLengthsLF.columns=Classe de taille,Effectif +report.targetDiscardedDistributionLengthsLF.operations.1=GroupByLength +report.targetDiscardedDistributionLengthsLF.variable.speciesId=fr.ird.observe.entities.referentiel.Species|\ +Select distinct tl.species \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.targetSample ts \ +Join ts.targetLength tl \ +Where t.id = :tripId \ +And tl.measureType = 2 \ +And ts.discarded = true +report.targetDiscardedDistributionLengthsLF.request.1=0,0|row| \ +Select tl.length , Sum(tl.count) \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.targetSample ts \ +Join ts.targetLength tl \ +Where t.id = :tripId \ + And tl.species.id = :speciesId \ + And tl.measureType = 2 \ + And ts.discarded = true \ +Group By tl.length \ +Order By tl.length + +############################################ +## Répartition des calées par cuves +############################################ + +report.repartionCaleeParCuve.name=R\u00e9partition des cal\u00e9es par cuves +report.repartionCaleeParCuve.description=R\u00e9partition des cal\u00e9es par cuves, type de banc et esp\u00e8ce +report.repartionCaleeParCuve.columns=Jour - Heure observation,Latitude,Longitude,Type de banc,Esp\u00e8ce,Tonnage,Cuve +report.repartionCaleeParCuve.request.1=0,0|row| \ +Select concat(day(r.date), '/', month(r.date), '/', year(r.date), ' ', hour(a.time), ':', minute(a.time)), \ + a.latitude, \ + a.longitude, \ + case when a.setSeine.schoolType = 1 then 'BO' else 'BL' end, \ + tc.weightCategory.species.faoCode, \ + Sum(tc.catchWeight), \ + case when tc.discarded = true then 'Rejet\u00e9' else tc.well end \ +From TripSeineImpl t \ +Join t.route r \ +Join r.activitySeine a \ +Join a.setSeine.targetCatch tc \ +Where t.id = :tripId \ +Group By r.date, a.time, a.latitude, a.longitude, tc.weightCategory.species, tc.weightCategory.species.faoCode, tc.well, a.setSeine.schoolType \ +Order By r.date, a.time, tc.weightCategory.species, tc.well -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@list.forge.codelutin.com>.