branch develop updated (90d271a -> f7c7a24)
This is an automated email from the git hooks/post-receive script. New change to branch develop in repository observe. See http://git.codelutin.com/observe.git from 90d271a fixes #7147: Mise à jour librairies Merge branch 'feature/7147' into develop new f7c7a24 Merge branch 'feature/7146' into develop The 1 revisions listed above as "new" are entirely new to this repository and will be described in separate emails. The revisions listed as "adds" were already present in the repository and have only been added to this reference. Detailed log of new commits: commit f7c7a24427bb637ef56491662089420e6c4b270d Author: Sylvain Bavencoff <bavencoff@codelutin.com> Date: Wed May 27 16:55:57 2015 +0200 Merge branch 'feature/7146' into develop Summary of changes: .../src/main/filters/observe-reports.properties | 1544 ++++++-------------- .../observe/ui/admin/report/ReportUIHandler.java | 5 + 2 files changed, 414 insertions(+), 1135 deletions(-) -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@list.forge.codelutin.com>.
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 f7c7a24427bb637ef56491662089420e6c4b270d Author: Sylvain Bavencoff <bavencoff@codelutin.com> Date: Wed May 27 16:55:57 2015 +0200 Merge branch 'feature/7146' into develop --- .../src/main/filters/observe-reports.properties | 1544 ++++++-------------- .../observe/ui/admin/report/ReportUIHandler.java | 5 + 2 files changed, 414 insertions(+), 1135 deletions(-) diff --git a/observe-business/src/main/filters/observe-reports.properties b/observe-business/src/main/filters/observe-reports.properties index 434dda1..3b21725 100644 --- a/observe-business/src/main/filters/observe-reports.properties +++ b/observe-business/src/main/filters/observe-reports.properties @@ -29,13 +29,13 @@ report.activityWithComment.description=Afficher les activit\u00e9s avec comment 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, RouteImpl r, ActivitySeineImpl a, VesselActivitySeineImpl ab \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.vesselActivitySeine ab \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ and a.comment is not null \ - and ab = a.vesselActivitySeine \ Order By r.date, a.time ################################################################################ @@ -47,12 +47,12 @@ report.allActivitys.description=Afficher toutes les activit\u00e9s et leurs posi 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, RouteImpl r, ActivitySeineImpl a, VesselActivitySeineImpl ab \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.vesselActivitySeine ab \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and ab = a.vesselActivitySeine \ Order By r.date, a.time ################################################################################ @@ -64,13 +64,13 @@ report.dailySetAndCatch.description=Afficher le nombre de cal\u00e9es et les cap 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, RouteImpl r, ActivitySeineImpl a \ - left join a.setSeine.targetCatch as ct \ + 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 \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ Group By r.date \ Order By r.date @@ -81,13 +81,15 @@ report.dailySetAndCatch.request.1=0,0|row|\ 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 ObjectTypeImpl to Where to In (Select to From ObjectTypeImpl to, TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, FloatingObjectImpl dcp \ +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 \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and dcp in elements(a.floatingObject) \ - and to = dcp.objectType) \ Order by to.code report.dcpUsage.request.1=0,0|row|\ @@ -99,79 +101,76 @@ report.dcpUsage.request.1.repeat=typeObjetId|column report.dcpUsage.request.2=1,0|row|\ Select Count(dcp) \ - From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, FloatingObjectImpl dcp \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and dcp in elements(a.floatingObject) \ and dcp.objectType.id = :typeObjetId \ - and dcp.objectOperation.code = 2 + 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, RouteImpl r, ActivitySeineImpl a, FloatingObjectImpl dcp \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and dcp in elements(a.floatingObject) \ and dcp.objectType.id = :typeObjetId \ - and dcp.objectOperation.code = 3 + 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, RouteImpl r, ActivitySeineImpl a, FloatingObjectImpl dcp \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and dcp in elements(a.floatingObject) \ and dcp.objectType.id = :typeObjetId \ - and dcp.objectOperation.code = 1 + 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, RouteImpl r, ActivitySeineImpl a, FloatingObjectImpl dcp \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and dcp in elements(a.floatingObject) \ and dcp.objectType.id = :typeObjetId \ - and ( dcp.objectFate.code = 8 and dcp.objectOperation.code != 1 ) + 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, RouteImpl r, ActivitySeineImpl a, FloatingObjectImpl dcp \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and dcp in elements(a.floatingObject) \ and dcp.objectType.id = :typeObjetId \ - and dcp.objectOperation.code = 4 + 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, RouteImpl r, ActivitySeineImpl a, FloatingObjectImpl dcp, ObjectObservedSpeciesImpl efo \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a \ + Join a.floatingObject dcp \ + Join dcp.objectObservedSpecies efo \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and dcp in elements(a.floatingObject) \ and dcp.objectType.id = :typeObjetId \ - and efo in elements(dcp.objectObservedSpecies) \ - and efo.species in ( \ - Select distinct ef2 From SpeciesImpl ef2, SpeciesGroupImpl gef2 \ - Where ef2.speciesGroup = gef2 \ - and gef2.id = 'fr.ird.observe.entities.referentiel.SpeciesGroup#1239832683690#0.24333033683679461' \ - ) + and efo.species.speciesGroup.id = 'fr.ird.observe.entities.referentiel.SpeciesGroup#1239832683690#0.24333033683679461' report.dcpUsage.request.7.repeat=typeObjetId|column ################################################################################ @@ -185,102 +184,108 @@ 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(distinct a) \ - From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - left join a.observedSystem as so \ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ and a.setSeine.schoolType = 2 \ and a.setSeine.reasonForNullSet is null \ - and ((so is null and size(a.observedSystem) = 0) or (so.id != 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem)) ) + 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(distinct a) \ - From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - left join a.observedSystem as so \ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ and a.setSeine.schoolType = 2 \ and a.setSeine.reasonForNullSet is not null \ - and ((so is null and size(a.observedSystem) = 0) or (so.id != 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem)) ) + 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(distinct a) \ - From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ and a.setSeine.schoolType = 2 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem) \ - and a.setSeine.reasonForNullSet is null + 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(distinct a) \ - From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ and a.setSeine.schoolType = 2 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem) \ - and a.setSeine.reasonForNullSet is not null + 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(distinct a) \ - From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - and a.setSeine.reasonForNullSet is null + 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(distinct a) \ - From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - and a.setSeine.reasonForNullSet is not null + 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(distinct a) \ - From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - and a.setSeine.reasonForNullSet is null + 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(distinct a) \ - From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ + Select Count(a) \ + From TripSeineImpl m \ + Join m.route r \ + Join r.activitySeine a, \ + ObservedSystemImpl os \ Where \ m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - and a.setSeine.reasonForNullSet is not null + 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 @@ -295,405 +300,131 @@ report.targetCatchByAssociation.operations.2=SumColumn # ligne 1 report.targetCatchByAssociation.request.1=0,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and ((so is null and size(a.observedSystem) = 0) or (so.id != 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'YFT' - -##Select Sum(ct.catchWeight) From fr.ird.observe.entities.seine.SetSeineImpl c, fr.ird.observe.entities.seine.TargetCatchImpl ct Where c in ( Select distinct a.setSeine From fr.ird.observe.entities.seine.TripSeineImpl m, fr.ird.observe.entities.seine.RouteImpl r, fr.ird.observe.entities.seine.ActivitySeineImpl a, fr.ird.observe.entities.referentiel.seine.ObservedSystemImpl so left join a.observedSystem as so Where m.id = :tripId and r in elements(m.route) and a in elements(r.activitySei [...] - -report.targetCatchByAssociation.request.2=1,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and ((so is null and size(a.observedSystem) = 0) or (so.id != 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'SKJ' - -report.targetCatchByAssociation.request.3=2,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and ((so is null and size(a.observedSystem) = 0) or (so.id != 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'BET' - -report.targetCatchByAssociation.request.4=3,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and ((so is null and size(a.observedSystem) = 0) or (so.id != 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'LTA' - -report.targetCatchByAssociation.request.5=4,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and ((so is null and size(a.observedSystem) = 0) or (so.id != 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'FRI' - -report.targetCatchByAssociation.request.6=5,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ + 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 \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and ((so is null and size(a.observedSystem) = 0) or (so.id != 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode NOT IN ( 'YFT', 'SKJ', 'BET', 'LTA', 'FRI' ) + 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(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'YFT' - -report.targetCatchByAssociation.request.8=1,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'SKJ' - -report.targetCatchByAssociation.request.9=2,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'BET' - -report.targetCatchByAssociation.request.10=3,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'LTA' - -report.targetCatchByAssociation.request.11=4,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'FRI' - -report.targetCatchByAssociation.request.12=5,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ + 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 \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9395222812356602' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode NOT IN ( 'YFT', 'SKJ', 'BET', 'LTA', 'FRI' ) + 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(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'YFT' - -report.targetCatchByAssociation.request.14=1,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'SKJ' - -report.targetCatchByAssociation.request.15=2,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'BET' - -report.targetCatchByAssociation.request.16=3,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'LTA' - -report.targetCatchByAssociation.request.17=4,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'FRI' - -report.targetCatchByAssociation.request.18=5,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ + 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 \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode NOT IN ( 'YFT', 'SKJ', 'BET', 'LTA', 'FRI' ) + 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(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'YFT' - -report.targetCatchByAssociation.request.20=1,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'SKJ' - -report.targetCatchByAssociation.request.21=2,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'BET' - -report.targetCatchByAssociation.request.22=3,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'LTA' - -report.targetCatchByAssociation.request.23=4,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode = 'FRI' - -report.targetCatchByAssociation.request.24=5,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ + 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 \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = false and ct.weightCategory.species.faoCode NOT IN ( 'YFT', 'SKJ', 'BET', 'LTA', 'FRI' ) + 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 @@ -708,426 +439,131 @@ report.targetDiscardedByAssociation.operations.2=SumColumn # ligne 1 report.targetDiscardedByAssociation.request.1=0,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and ( (so is null and size(a.observedSystem) = 0) or (so.code != 11 and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'YFT' - -report.targetDiscardedByAssociation.request.2=1,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and ( (so is null and size(a.observedSystem) = 0) or (so.code != 11 and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'SKJ' - -report.targetDiscardedByAssociation.request.3=2,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and ( (so is null and size(a.observedSystem) = 0) or (so.code != 11 and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'BET' - -report.targetDiscardedByAssociation.request.4=3,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and ( (so is null and size(a.observedSystem) = 0) or (so.code != 11 and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'LTA' - -report.targetDiscardedByAssociation.request.5=4,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and ( (so is null and size(a.observedSystem) = 0) or (so.code != 11 and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'FRI' - -report.targetDiscardedByAssociation.request.6=5,0|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ + 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 \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and ( (so is null and size(a.observedSystem) = 0) or (so.code != 11 and so in elements(a.observedSystem)) ) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode NOT IN ( 'YFT', 'SKJ', 'BET', 'LTA', 'FRI' ) + 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(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and so in elements(a.observedSystem) and so.code = 11 \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'YFT' - -report.targetDiscardedByAssociation.request.8=1,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and so in elements(a.observedSystem) and so.code = 11 \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'SKJ' - -report.targetDiscardedByAssociation.request.9=2,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and so in elements(a.observedSystem) and so.code = 11 \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'BET' - -report.targetDiscardedByAssociation.request.10=3,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and so in elements(a.observedSystem) and so.code = 11 \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'LTA' - -report.targetDiscardedByAssociation.request.11=4,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and so in elements(a.observedSystem) and so.code = 11 \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'FRI' - -report.targetDiscardedByAssociation.request.12=5,1|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ + 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 \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - left join a.observedSystem as so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 2 \ - and so in elements(a.observedSystem) and so.code = 11 \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode NOT IN ( 'YFT', 'SKJ', 'BET', 'LTA', 'FRI' ) + 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(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'YFT' - -report.targetDiscardedByAssociation.request.14=1,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'SKJ' - -report.targetDiscardedByAssociation.request.15=2,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'BET' - -report.targetDiscardedByAssociation.request.16=3,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'LTA' - -report.targetDiscardedByAssociation.request.17=4,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'FRI' - -report.targetDiscardedByAssociation.request.18=5,2|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ + 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 \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode NOT IN ( 'YFT', 'SKJ', 'BET', 'LTA', 'FRI' ) + 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(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'YFT' - -report.targetDiscardedByAssociation.request.20=1,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'SKJ' - -report.targetDiscardedByAssociation.request.21=2,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'BET' - -report.targetDiscardedByAssociation.request.22=3,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'LTA' - -report.targetDiscardedByAssociation.request.23=4,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode = 'FRI' - -report.targetDiscardedByAssociation.request.24=5,3|row|\ - Select Sum(ct.catchWeight) \ - From SetSeineImpl c, TargetCatchImpl ct \ + 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 \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, ObservedSystemImpl so \ - Where \ - m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.reasonForNullSet is null \ - and a.setSeine.schoolType = 1 \ - and so.id = 'fr.ird.observe.entities.referentiel.seine.ObservedSystem#1239832686428#0.9217864901728908' and so not in elements(a.observedSystem) \ - ) \ - and ct in elements(c.targetCatch) and ct.discarded = true and ct.weightCategory.species.faoCode NOT IN ( 'YFT', 'SKJ', 'BET', 'LTA', 'FRI' ) + 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 @@ -1145,56 +581,45 @@ report.accessoryCatch.repeatVariable.speciesId=java.lang.String|\ 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)) \ + 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(distinct 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 SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct aa.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl aa \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and aa in elements(r.activitySeine) \ - and aa.setSeine is not null \ - and aa.setSeine.schoolType = 2 \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId + 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 -##Select case when Count(distinct 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 fr.ird.observe.entities.seine.SetSeineImpl c, fr.ird.observe.entities.seine.NonTargetCatchImpl ca Where c in ( Select distinct aa.setSeine From fr.ird.observe.entities.seine.TripSeineImpl [...] - report.accessoryCatch.request.3=2,0|row|\ Select \ - case when Count(distinct 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 SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct aa.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl aa \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and aa in elements(r.activitySeine) \ - and aa.setSeine is not null \ - and aa.setSeine.schoolType = 1 \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId + 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 ################################################################################ @@ -1205,200 +630,49 @@ report.accessoryCatchByGroup.name=D\u00e9nombrement des captures accessoires et 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.speciesGroupId=fr.ird.observe.entities.referentiel.SpeciesGroup|From SpeciesGroupImpl ge Order By ge.code -report.accessoryCatchByGroup.repeatVariable.speciesId=java.lang.String|\ - Select e.id From SpeciesImpl e \ - Where e.speciesGroup.id = :speciesGroupId \ - and e in ( \ - Select distinct e1 From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a, NonTargetCatchImpl cf, SpeciesImpl e1 \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and cf in elements(a.setSeine.nonTargetCatch) \ - and cf.species.id = e1.id \ - ) \ - Order By e.homeId - -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)) \ - From SpeciesImpl e \ - Where e.id = :speciesId -report.accessoryCatchByGroup.request.1.repeat=speciesId|column - -report.accessoryCatchByGroup.request.2=1,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 2 \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId -report.accessoryCatchByGroup.request.2.repeat=speciesId|column - -report.accessoryCatchByGroup.request.3=2,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - and a.setSeine.schoolType = 1 \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId -report.accessoryCatchByGroup.request.3.repeat=speciesId|column - -report.accessoryCatchByGroup.request.4=3,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId \ - and ca.speciesFate.id in ( \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.9931091059863436', \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683618#0.06155887805368032' \ - ) -report.accessoryCatchByGroup.request.4.repeat=speciesId|column -report.accessoryCatchByGroup.request.5=4,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId \ - and ca.speciesFate.id in ('fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.11883784875534997') -report.accessoryCatchByGroup.request.5.repeat=speciesId|column - -report.accessoryCatchByGroup.request.6=5,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId \ - and ca.speciesFate.id = 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.5308862132841506' -report.accessoryCatchByGroup.request.6.repeat=speciesId|column - -report.accessoryCatchByGroup.request.7=6,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId \ - and ca.speciesFate.id = 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.6250731662108877' -report.accessoryCatchByGroup.request.7.repeat=speciesId|column - -report.accessoryCatchByGroup.request.8=7,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId \ - and ca.speciesFate.id = 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683620#0.46609703818634485' -report.accessoryCatchByGroup.request.8.repeat=speciesId|column - -report.accessoryCatchByGroup.request.9=8,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId \ - and ca.speciesFate.id in ('fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.5722739932065866') -report.accessoryCatchByGroup.request.9.repeat=speciesId|column - -report.accessoryCatchByGroup.request.10=9,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId \ - and ca.speciesFate.id in ('fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683621#0.6728026426066158') -report.accessoryCatchByGroup.request.10.repeat=speciesId|column - -report.accessoryCatchByGroup.request.11=10,0|row|\ - Select Sum(ca.totalCount) \ - From SetSeineImpl c, NonTargetCatchImpl ca \ - Where \ - c in ( \ - Select distinct a.setSeine From TripSeineImpl m, RouteImpl r, ActivitySeineImpl a \ - Where m.id = :tripId \ - and r in elements(m.route) \ - and a in elements(r.activitySeine) \ - and a.setSeine is not null \ - ) \ - and ca in elements(c.nonTargetCatch) \ - and ca.species.id = :speciesId \ - and ca.speciesFate.id not in ( \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.9931091059863436', \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683618#0.06155887805368032', \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.11883784875534997', \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.5308862132841506', \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.6250731662108877', \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683620#0.46609703818634485', \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683619#0.5722739932065866', \ - 'fr.ird.observe.entities.referentiel.seine.SpeciesFate#1239832683621#0.6728026426066158' \ - ) -report.accessoryCatchByGroup.request.11.repeat=speciesId|column +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 = :speciesGroupId \ + Where m.id = :tripId \ + Group by e \ + Order By e.homeId ########################################################### ## Distribution des tailles par esp\u00e8ce non cibl\u00e8s diff --git a/observe-swing/src/main/java/fr/ird/observe/ui/admin/report/ReportUIHandler.java b/observe-swing/src/main/java/fr/ird/observe/ui/admin/report/ReportUIHandler.java index 250850f..770d58c 100644 --- a/observe-swing/src/main/java/fr/ird/observe/ui/admin/report/ReportUIHandler.java +++ b/observe-swing/src/main/java/fr/ird/observe/ui/admin/report/ReportUIHandler.java @@ -45,6 +45,7 @@ import org.apache.commons.logging.LogFactory; import org.nuiton.decorator.Decorator; import org.nuiton.decorator.JXPathDecorator; import org.nuiton.topia.persistence.TopiaEntity; +import org.nuiton.util.TimeLog; import javax.swing.DefaultListCellRenderer; import javax.swing.JComboBox; @@ -77,6 +78,7 @@ public class ReportUIHandler extends AdminTabUIHandler { /** Logger */ private static final Log log = LogFactory.getLog(ReportUIHandler.class); + private static final TimeLog timeLog = new TimeLog(ReportUIHandler.class, 0, 1000); final Runnable revalidateTabUI = new Runnable() { @Override @@ -437,10 +439,13 @@ public class ReportUIHandler extends AdminTabUIHandler { Map<String, Object> variables = model.getVariables(); variables.put(ReportExecute.TRIP_ID_VARIABLE, mareeId); + long startTime = TimeLog.getTime(); data = execute.execute(variables); + timeLog.log(startTime, "execute", report.getName()); if (log.isInfoEnabled()) { log.info("Result to dispaly:\n" + data.getClipbordContent(true, true)); } + } catch (Exception e) { if (log.isErrorEnabled()) { log.error("Could not obtain report data", e); -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@list.forge.codelutin.com>.
participants (1)
-
codelutin.com scm