[Observe-commits] 01/01: Merge branch 'feature/7146' into develop

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>.