This is an automated email from the git hooks/post-receive script. New commit to branch feature/7146 in repository observe. See http://git.codelutin.com/observe.git commit 0bb8c14e875aae680f121c0c77cf11aa4663bbb9 Author: Sylvain Bavencoff <bavencoff@codelutin.com> Date: Wed May 27 11:23:41 2015 +0200 refs #7146 : Captures thon par type d'association (578.546 ms ==> 235.227 ms) --- .../src/main/filters/observe-reports.properties | 498 +++++---------------- 1 file changed, 112 insertions(+), 386 deletions(-) diff --git a/observe-business/src/main/filters/observe-reports.properties b/observe-business/src/main/filters/observe-reports.properties index 0aaa672..638a396 100644 --- a/observe-business/src/main/filters/observe-reports.properties +++ b/observe-business/src/main/filters/observe-reports.properties @@ -300,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 -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@list.forge.codelutin.com>.