| ... |
... |
@@ -157,8 +157,8 @@ report.psObservationFobUsageMinimal.request.3=2,0|row|\ |
|
157
|
157
|
Where \
|
|
158
|
158
|
m.id In :tripId \
|
|
159
|
159
|
and dcp.computedWhenArrivingSimplifiedObjectType = :typeObjetId \
|
|
160
|
|
- and a.vesselActivity.id != 'fr.ird.referential.ps.common.VesselActivity#1239832675369#0.12552908048322586'
|
|
161
|
|
-and (dcp.objectOperation.id = 'fr.ird.referential.ps.common.ObjectOperation#0#2' \
|
|
|
160
|
+ and a.vesselActivity.id != 'fr.ird.referential.ps.common.VesselActivity#1239832675369#0.12552908048322586' \
|
|
|
161
|
+ and (dcp.objectOperation.id = 'fr.ird.referential.ps.common.ObjectOperation#0#2' \
|
|
162
|
162
|
or dcp.objectOperation.id = 'fr.ird.referential.ps.common.ObjectOperation#0#8' \
|
|
163
|
163
|
or dcp.objectOperation.id = 'fr.ird.referential.ps.common.ObjectOperation#0#4' \
|
|
164
|
164
|
or dcp.objectOperation.id = 'fr.ird.referential.ps.common.ObjectOperation#0#5' \
|
| ... |
... |
@@ -567,6 +567,140 @@ report.psObservationSetByAssociation.request.8=3,1|column|\ |
|
567
|
567
|
and os.topiaId = 'fr.ird.referential.ps.common.ObservedSystem#1239832686428#0.9217864901728908' \
|
|
568
|
568
|
and os not in elements(a.observedSystem)
|
|
569
|
569
|
################################################################################
|
|
|
570
|
+## Captures thons selon le type d'association
|
|
|
571
|
+################################################################################
|
|
|
572
|
+report.psObservationTargetCatchByAssociation.modelType=PS
|
|
|
573
|
+report.psObservationTargetCatchByAssociation.name=Observations - Captures de thons selon le type d’association (en t)
|
|
|
574
|
+report.psObservationTargetCatchByAssociation.description=Afficher la répartitions des captures de thons selon le type d'association
|
|
|
575
|
+report.psObservationTargetCatchByAssociation.columns=YFT, SKJ, BET, LTA, FRI, Autres, Total
|
|
|
576
|
+report.psObservationTargetCatchByAssociation.rows=BL sans baleine, BL avec baleine, BO avec requin-baleine, BO sans requin-baleine, Total
|
|
|
577
|
+report.psObservationTargetCatchByAssociation.operations.1=SumRow
|
|
|
578
|
+report.psObservationTargetCatchByAssociation.operations.2=SumColumn
|
|
|
579
|
+# ligne 1
|
|
|
580
|
+report.psObservationTargetCatchByAssociation.request.1=0,0|row|\
|
|
|
581
|
+ Select \
|
|
|
582
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \
|
|
|
583
|
+ Else 0.0 End), \
|
|
|
584
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.975344121171992' Then ct.catchWeight \
|
|
|
585
|
+ Else 0.0 End), \
|
|
|
586
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \
|
|
|
587
|
+ Else 0.0 End), \
|
|
|
588
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \
|
|
|
589
|
+ Else 0.0 End), \
|
|
|
590
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \
|
|
|
591
|
+ Else 0.0 End), \
|
|
|
592
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.8943253454598569' Then 0.0 \
|
|
|
593
|
+ When 'fr.ird.referential.common.Species#1239832685474#0.975344121171992' Then 0.0 \
|
|
|
594
|
+ When 'fr.ird.referential.common.Species#1239832685475#0.13349466123905152' Then 0.0 \
|
|
|
595
|
+ When 'fr.ird.referential.common.Species#1239832685477#0.8024257002747615' Then 0.0 \
|
|
|
596
|
+ When 'fr.ird.referential.common.Species#1239832685477#0.3846921632590058' Then 0.0 \
|
|
|
597
|
+ Else ct.catchWeight End) \
|
|
|
598
|
+ From TripImpl m \
|
|
|
599
|
+ Join m.routeObs r \
|
|
|
600
|
+ Join r.activity a \
|
|
|
601
|
+ Join a.set c \
|
|
|
602
|
+ with c.schoolType.id = 'fr.ird.referential.ps.common.SchoolType#0#2' \
|
|
|
603
|
+ Join c.catches ct \
|
|
|
604
|
+ Join ct.speciesFate sf with sf.discard = FALSE, \
|
|
|
605
|
+ ObservedSystemImpl os \
|
|
|
606
|
+ Where \
|
|
|
607
|
+ m.id In :tripId \
|
|
|
608
|
+ and os.topiaId = 'fr.ird.referential.ps.common.ObservedSystem#1239832686428#0.9395222812356602' \
|
|
|
609
|
+ and os not in elements(a.observedSystem)
|
|
|
610
|
+# ligne 2
|
|
|
611
|
+report.psObservationTargetCatchByAssociation.request.7=0,1|row|\
|
|
|
612
|
+ Select \
|
|
|
613
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \
|
|
|
614
|
+ Else 0.0 End), \
|
|
|
615
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.975344121171992' Then ct.catchWeight \
|
|
|
616
|
+ Else 0.0 End), \
|
|
|
617
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \
|
|
|
618
|
+ Else 0.0 End), \
|
|
|
619
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \
|
|
|
620
|
+ Else 0.0 End), \
|
|
|
621
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \
|
|
|
622
|
+ Else 0.0 End), \
|
|
|
623
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.8943253454598569' Then 0.0 \
|
|
|
624
|
+ When 'fr.ird.referential.common.Species#1239832685474#0.975344121171992' Then 0.0 \
|
|
|
625
|
+ When 'fr.ird.referential.common.Species#1239832685475#0.13349466123905152' Then 0.0 \
|
|
|
626
|
+ When 'fr.ird.referential.common.Species#1239832685477#0.8024257002747615' Then 0.0 \
|
|
|
627
|
+ When 'fr.ird.referential.common.Species#1239832685477#0.3846921632590058' Then 0.0 \
|
|
|
628
|
+ Else ct.catchWeight End) \
|
|
|
629
|
+ From TripImpl m \
|
|
|
630
|
+ Join m.routeObs r \
|
|
|
631
|
+ Join r.activity a \
|
|
|
632
|
+ Join a.set c \
|
|
|
633
|
+ with c.schoolType.id = 'fr.ird.referential.ps.common.SchoolType#0#2' \
|
|
|
634
|
+ Join c.catches ct \
|
|
|
635
|
+ Join ct.speciesFate sf with sf.discard = FALSE, \
|
|
|
636
|
+ ObservedSystemImpl os \
|
|
|
637
|
+ Where \
|
|
|
638
|
+ m.id In :tripId \
|
|
|
639
|
+ and os.topiaId = 'fr.ird.referential.ps.common.ObservedSystem#1239832686428#0.9395222812356602' \
|
|
|
640
|
+ and os in elements(a.observedSystem)
|
|
|
641
|
+# ligne 3
|
|
|
642
|
+report.psObservationTargetCatchByAssociation.request.13=0,2|row|\
|
|
|
643
|
+ Select \
|
|
|
644
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \
|
|
|
645
|
+ Else 0.0 End), \
|
|
|
646
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.975344121171992' Then ct.catchWeight \
|
|
|
647
|
+ Else 0.0 End), \
|
|
|
648
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \
|
|
|
649
|
+ Else 0.0 End), \
|
|
|
650
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \
|
|
|
651
|
+ Else 0.0 End), \
|
|
|
652
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \
|
|
|
653
|
+ Else 0.0 End), \
|
|
|
654
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.8943253454598569' Then 0.0 \
|
|
|
655
|
+ When 'fr.ird.referential.common.Species#1239832685474#0.975344121171992' Then 0.0 \
|
|
|
656
|
+ When 'fr.ird.referential.common.Species#1239832685475#0.13349466123905152' Then 0.0 \
|
|
|
657
|
+ When 'fr.ird.referential.common.Species#1239832685477#0.8024257002747615' Then 0.0 \
|
|
|
658
|
+ When 'fr.ird.referential.common.Species#1239832685477#0.3846921632590058' Then 0.0 \
|
|
|
659
|
+ Else ct.catchWeight End) \
|
|
|
660
|
+ From TripImpl m \
|
|
|
661
|
+ Join m.routeObs r \
|
|
|
662
|
+ Join r.activity a \
|
|
|
663
|
+ Join a.set c \
|
|
|
664
|
+ with c.schoolType.id = 'fr.ird.referential.ps.common.SchoolType#0#1' \
|
|
|
665
|
+ Join c.catches ct \
|
|
|
666
|
+ Join ct.speciesFate sf with sf.discard = FALSE, \
|
|
|
667
|
+ ObservedSystemImpl os \
|
|
|
668
|
+ Where \
|
|
|
669
|
+ m.id In :tripId \
|
|
|
670
|
+ and os.topiaId = 'fr.ird.referential.ps.common.ObservedSystem#1239832686428#0.9217864901728908' \
|
|
|
671
|
+ and os in elements(a.observedSystem)
|
|
|
672
|
+# ligne 4
|
|
|
673
|
+report.psObservationTargetCatchByAssociation.request.19=0,3|row|\
|
|
|
674
|
+ Select \
|
|
|
675
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.8943253454598569' Then ct.catchWeight \
|
|
|
676
|
+ Else 0.0 End), \
|
|
|
677
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.975344121171992' Then ct.catchWeight \
|
|
|
678
|
+ Else 0.0 End), \
|
|
|
679
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685475#0.13349466123905152' Then ct.catchWeight \
|
|
|
680
|
+ Else 0.0 End), \
|
|
|
681
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685477#0.8024257002747615' Then ct.catchWeight \
|
|
|
682
|
+ Else 0.0 End), \
|
|
|
683
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685477#0.3846921632590058' Then ct.catchWeight \
|
|
|
684
|
+ Else 0.0 End), \
|
|
|
685
|
+ Sum(CASE ct.species.topiaId When 'fr.ird.referential.common.Species#1239832685474#0.8943253454598569' Then 0.0 \
|
|
|
686
|
+ When 'fr.ird.referential.common.Species#1239832685474#0.975344121171992' Then 0.0 \
|
|
|
687
|
+ When 'fr.ird.referential.common.Species#1239832685475#0.13349466123905152' Then 0.0 \
|
|
|
688
|
+ When 'fr.ird.referential.common.Species#1239832685477#0.8024257002747615' Then 0.0 \
|
|
|
689
|
+ When 'fr.ird.referential.common.Species#1239832685477#0.3846921632590058' Then 0.0 \
|
|
|
690
|
+ Else ct.catchWeight End) \
|
|
|
691
|
+ From TripImpl m \
|
|
|
692
|
+ Join m.routeObs r \
|
|
|
693
|
+ Join r.activity a \
|
|
|
694
|
+ Join a.set c \
|
|
|
695
|
+ with c.schoolType.id = 'fr.ird.referential.ps.common.SchoolType#0#1' \
|
|
|
696
|
+ Join c.catches ct \
|
|
|
697
|
+ Join ct.speciesFate sf with sf.discard = FALSE, \
|
|
|
698
|
+ ObservedSystemImpl os \
|
|
|
699
|
+ Where \
|
|
|
700
|
+ m.id In :tripId \
|
|
|
701
|
+ and os.topiaId = 'fr.ird.referential.ps.common.ObservedSystem#1239832686428#0.9217864901728908' \
|
|
|
702
|
+ and os not in elements(a.observedSystem)
|
|
|
703
|
+################################################################################
|
|
570
|
704
|
## Rejets thons selon le type d'association
|
|
571
|
705
|
################################################################################
|
|
572
|
706
|
report.psObservationTargetDiscardedByAssociation.modelType=PS
|
| ... |
... |
@@ -600,7 +734,8 @@ report.psObservationTargetDiscardedByAssociation.request.1=0,0|row|\ |
|
600
|
734
|
Join r.activity a \
|
|
601
|
735
|
Join a.set c \
|
|
602
|
736
|
with c.schoolType.id = 'fr.ird.referential.ps.common.SchoolType#0#2' \
|
|
603
|
|
- Join c.catches ct, \
|
|
|
737
|
+ Join c.catches ct \
|
|
|
738
|
+ Join ct.speciesFate sf with sf.discard = TRUE, \
|
|
604
|
739
|
ObservedSystemImpl os \
|
|
605
|
740
|
Where \
|
|
606
|
741
|
m.id In :tripId \
|
| ... |
... |
@@ -630,7 +765,8 @@ report.psObservationTargetDiscardedByAssociation.request.7=0,1|row|\ |
|
630
|
765
|
Join r.activity a \
|
|
631
|
766
|
Join a.set c \
|
|
632
|
767
|
with c.schoolType.id = 'fr.ird.referential.ps.common.SchoolType#0#2' \
|
|
633
|
|
- Join c.catches ct, \
|
|
|
768
|
+ Join c.catches ct \
|
|
|
769
|
+ Join ct.speciesFate sf with sf.discard = TRUE, \
|
|
634
|
770
|
ObservedSystemImpl os \
|
|
635
|
771
|
Where \
|
|
636
|
772
|
m.id In :tripId \
|
| ... |
... |
@@ -660,7 +796,8 @@ report.psObservationTargetDiscardedByAssociation.request.13=0,2|row|\ |
|
660
|
796
|
Join r.activity a \
|
|
661
|
797
|
Join a.set c \
|
|
662
|
798
|
with c.schoolType.id = 'fr.ird.referential.ps.common.SchoolType#0#1' \
|
|
663
|
|
- Join c.catches ct, \
|
|
|
799
|
+ Join c.catches ct \
|
|
|
800
|
+ Join ct.speciesFate sf with sf.discard = TRUE, \
|
|
664
|
801
|
ObservedSystemImpl os \
|
|
665
|
802
|
Where \
|
|
666
|
803
|
m.id In :tripId \
|
| ... |
... |
@@ -690,7 +827,8 @@ report.psObservationTargetDiscardedByAssociation.request.19=0,3|row|\ |
|
690
|
827
|
Join r.activity a \
|
|
691
|
828
|
Join a.set c \
|
|
692
|
829
|
with c.schoolType.id = 'fr.ird.referential.ps.common.SchoolType#0#1' \
|
|
693
|
|
- Join c.catches ct, \
|
|
|
830
|
+ Join c.catches ct \
|
|
|
831
|
+ Join ct.speciesFate sf with sf.discard = TRUE, \
|
|
694
|
832
|
ObservedSystemImpl os \
|
|
695
|
833
|
Where \
|
|
696
|
834
|
m.id In :tripId \
|
| ... |
... |
@@ -749,64 +887,81 @@ report.psObservationCatch.request.3=2,0|row|\ |
|
749
|
887
|
Where m.id In :tripId
|
|
750
|
888
|
report.psObservationCatch.request.3.repeat=speciesId|column
|
|
751
|
889
|
################################################################################
|
|
752
|
|
-## Captures accessoires par speciesGroup d'espèces
|
|
|
890
|
+## Captures par groupe d'espèces
|
|
753
|
891
|
################################################################################
|
|
754
|
|
-report.psObservationAccessoryCatchByGroup.modelType=PS
|
|
755
|
|
-report.psObservationAccessoryCatchByGroup.name=Observations - Dénombrement des captures accessoires et devenir, filtrés par groupe
|
|
756
|
|
-report.psObservationAccessoryCatchByGroup.description=Afficher les nombres de captures accessoires par groupe d'espèce selon le type de banc et le devenir
|
|
757
|
|
-report.psObservationAccessoryCatchByGroup.columns=Espèce, Total BL, Total BO, Sorti vivant/échappé, Sorti mort, Rejeté vivant, Rejeté mort, Partiellement conservé, Cuve, Cuisine, Autre
|
|
758
|
|
-report.psObservationAccessoryCatchByGroup.variable.speciesGroup=fr.ird.observe.dto.referential.common.SpeciesGroupDto|From SpeciesGroupImpl ge Order By ge.code
|
|
759
|
|
-report.psObservationAccessoryCatchByGroup.request.1=0,0|row|\
|
|
|
892
|
+report.psObservationCatchByGroup.modelType=PS
|
|
|
893
|
+report.psObservationCatchByGroup.name=Observations - Dénombrement des captures par devenir, filtrés par groupe
|
|
|
894
|
+report.psObservationCatchByGroup.description=Afficher les nombres de captures par groupe d'espèce selon le type de banc et le devenir
|
|
|
895
|
+report.psObservationCatchByGroup.variable.speciesGroup=fr.ird.observe.dto.referential.common.SpeciesGroupDto|\
|
|
|
896
|
+Select distinct sg \
|
|
|
897
|
+From TripImpl t \
|
|
|
898
|
+Join t.routeObs r \
|
|
|
899
|
+Join r.activity a \
|
|
|
900
|
+Join a.set c \
|
|
|
901
|
+Join c.catches ca with ca.totalCount Is Not Null \
|
|
|
902
|
+Join ca.species e \
|
|
|
903
|
+Join e.speciesGroup sg \
|
|
|
904
|
+Where t.id In :tripId \
|
|
|
905
|
+Order By sg.code
|
|
|
906
|
+report.psObservationCatchByGroup.repeatVariable.1.species=fr.ird.observe.entities.referential.common.Species|\
|
|
|
907
|
+Select distinct e \
|
|
|
908
|
+From TripImpl t \
|
|
|
909
|
+Join t.routeObs r \
|
|
|
910
|
+Join r.activity a \
|
|
|
911
|
+Join a.set c \
|
|
|
912
|
+Join c.catches ca with ca.totalCount Is Not Null \
|
|
|
913
|
+Join ca.species e with e.speciesGroup.id = :speciesGroup \
|
|
|
914
|
+Where t.id In :tripId \
|
|
|
915
|
+Order By e.homeId
|
|
|
916
|
+report.psObservationCatchByGroup.repeatVariable.2.speciesFate=fr.ird.observe.entities.referential.ps.common.SpeciesFate|\
|
|
|
917
|
+Select distinct sf \
|
|
|
918
|
+From TripImpl t \
|
|
|
919
|
+Join t.routeObs r \
|
|
|
920
|
+Join r.activity a \
|
|
|
921
|
+Join a.set c \
|
|
|
922
|
+Join c.catches ca with ca.totalCount Is Not Null \
|
|
|
923
|
+Join ca.species e with e.id In :species \
|
|
|
924
|
+Join ca.speciesFate sf \
|
|
|
925
|
+Where t.id In :tripId \
|
|
|
926
|
+Order by sf.code
|
|
|
927
|
+report.psObservationCatchByGroup.request.1=0,0|row|\
|
|
760
|
928
|
Select \
|
|
761
|
929
|
concat(\
|
|
762
|
930
|
'[FAO]', (case when e.faoCode is not null then e.faoCode else '-' end), \
|
|
763
|
931
|
' [sc]', (case when e.scientificLabel is not null then e.scientificLabel else '-' end), \
|
|
764
|
932
|
' [fr]', (case when e.label2 is not null then e.label2 else '-' end)), \
|
|
765
|
|
- Sum(case c.schoolType.id when 'fr.ird.referential.ps.common.SchoolType#0#2' then ca.totalCount else 0.0 end), \
|
|
766
|
|
- Sum(case c.schoolType.id when 'fr.ird.referential.ps.common.SchoolType#0#1' then ca.totalCount else 0.0 end), \
|
|
767
|
|
- Sum(case ca.speciesFate.id when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.9931091059863436' then ca.totalCount \
|
|
768
|
|
- when 'fr.ird.referential.ps.common.SpeciesFate#1239832683618#0.06155887805368032' then ca.totalCount \
|
|
769
|
|
- else 0.0 end), \
|
|
770
|
|
- Sum(case ca.speciesFate.id when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.11883784875534997' then ca.totalCount \
|
|
771
|
|
- else 0.0 end), \
|
|
772
|
|
- Sum(case ca.speciesFate.id when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.5308862132841506' then ca.totalCount \
|
|
773
|
|
- else 0.0 end), \
|
|
774
|
|
- Sum(case ca.speciesFate.id when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.6250731662108877' then ca.totalCount \
|
|
775
|
|
- else 0.0 end), \
|
|
776
|
|
- Sum(case ca.speciesFate.id when 'fr.ird.referential.ps.common.SpeciesFate#1239832683620#0.46609703818634485' then ca.totalCount \
|
|
777
|
|
- else 0.0 end), \
|
|
778
|
|
- Sum(case ca.speciesFate.id when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.5722739932065866' then ca.totalCount \
|
|
779
|
|
- else 0.0 end), \
|
|
780
|
|
- Sum(case ca.speciesFate.id when 'fr.ird.referential.ps.common.SpeciesFate#1239832683621#0.6728026426066158' then ca.totalCount \
|
|
781
|
|
- else 0.0 end), \
|
|
782
|
|
- Sum(case ca.speciesFate.id when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.9931091059863436' then 0.0 \
|
|
783
|
|
- when 'fr.ird.referential.ps.common.SpeciesFate#1239832683618#0.06155887805368032' then 0.0 \
|
|
784
|
|
- when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.11883784875534997' then 0.0 \
|
|
785
|
|
- when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.5308862132841506' then 0.0 \
|
|
786
|
|
- when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.6250731662108877' then 0.0 \
|
|
787
|
|
- when 'fr.ird.referential.ps.common.SpeciesFate#1239832683620#0.46609703818634485' then 0.0 \
|
|
788
|
|
- when 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.5722739932065866' then 0.0 \
|
|
789
|
|
- when 'fr.ird.referential.ps.common.SpeciesFate#1239832683621#0.6728026426066158' then 0.0 \
|
|
790
|
|
- else ca.totalCount end) \
|
|
|
933
|
+ Sum(case c.schoolType.id when 'fr.ird.referential.ps.common.SchoolType#0#2' then ca.totalCount else 0 end), \
|
|
|
934
|
+ Sum(case c.schoolType.id when 'fr.ird.referential.ps.common.SchoolType#0#1' then ca.totalCount else 0 end) \
|
|
791
|
935
|
From TripImpl m \
|
|
792
|
936
|
Join m.routeObs r \
|
|
793
|
937
|
Join r.activity a \
|
|
794
|
938
|
Join a.set c \
|
|
795
|
|
- Join c.catches ca \
|
|
796
|
|
- Join ca.species e \
|
|
797
|
|
- with e.speciesGroup.id = :speciesGroup \
|
|
|
939
|
+ Join c.catches ca with ca.totalCount Is Not Null \
|
|
|
940
|
+ Join ca.species e with e.id In :species \
|
|
798
|
941
|
Where m.id In :tripId \
|
|
799
|
942
|
Group by e \
|
|
800
|
943
|
Order By e.homeId
|
|
|
944
|
+report.psObservationCatchByGroup.operations.1=ExecuteRequestAndReorganizeCountByRepeatVariable:species|speciesFate|\
|
|
|
945
|
+Select \
|
|
|
946
|
+ca.species.id, ca.speciesFate.id, Sum(ca.totalCount) \
|
|
|
947
|
+From TripImpl m \
|
|
|
948
|
+Join m.routeObs r \
|
|
|
949
|
+Join r.activity a \
|
|
|
950
|
+Join a.set c \
|
|
|
951
|
+Join c.catches ca with ca.speciesFate.id In :speciesFate And ca.species.id In :species \
|
|
|
952
|
+Where m.id In :tripId \
|
|
|
953
|
+Group by ca.species, ca.speciesFate \
|
|
|
954
|
+Order By ca.species.homeId, ca.speciesFate.code
|
|
|
955
|
+report.psObservationCatchByGroup.operations.2=ComputeDynamicHeader:Espèce,Total BL,Total BO|speciesFate
|
|
|
956
|
+report.psObservationCatchByGroup.operations.3=SumIntRow:1|3
|
|
|
957
|
+report.psObservationCatchByGroup.operations.4=SumIntColumn:1|1
|
|
801
|
958
|
###########################################################
|
|
802
|
|
-## Distribution des tailles par espèces non ciblées
|
|
|
959
|
+## Distribution des tailles par espèces et type de mesure
|
|
803
|
960
|
###########################################################
|
|
804
|
|
-report.psObservationNonTargetDistributionLengths.modelType=PS
|
|
805
|
|
-report.psObservationNonTargetDistributionLengths.name=Observations - Distribution des tailles par espèces non ciblées
|
|
806
|
|
-report.psObservationNonTargetDistributionLengths.description=Afficher la nombre d'individus mesurés par classe de taille pour les espèces non ciblées
|
|
807
|
|
-report.psObservationNonTargetDistributionLengths.columns=Classe de taille,Effectif
|
|
808
|
|
-report.psObservationNonTargetDistributionLengths.operations.1=GroupByLength
|
|
809
|
|
-report.psObservationNonTargetDistributionLengths.variable.speciesId=fr.ird.observe.dto.referential.common.SpeciesDto|\
|
|
|
961
|
+report.psObservationLengthsDistribution.modelType=PS
|
|
|
962
|
+report.psObservationLengthsDistribution.name=Observations - Distribution des tailles par espèces et type de mesure
|
|
|
963
|
+report.psObservationLengthsDistribution.description=Afficher la nombre d'individus mesurés par classe de taille pour les espèces et le type de mesure
|
|
|
964
|
+report.psObservationLengthsDistribution.variable.1.species=fr.ird.observe.dto.referential.common.SpeciesDto|\
|
|
810
|
965
|
Select distinct ntl.species \
|
|
811
|
966
|
From TripImpl t \
|
|
812
|
967
|
Join t.routeObs r \
|
| ... |
... |
@@ -814,133 +969,47 @@ Join r.activity a \ |
|
814
|
969
|
Join a.set.sample nts \
|
|
815
|
970
|
Join nts.sampleMeasure ntl \
|
|
816
|
971
|
Where t.id In :tripId
|
|
817
|
|
-report.psObservationNonTargetDistributionLengths.request.1=0,0|row| \
|
|
818
|
|
-Select ntl.length, sum(ntl.count) \
|
|
|
972
|
+report.psObservationLengthsDistribution.variable.2.sizeMeasureType=fr.ird.observe.dto.referential.common.SizeMeasureTypeDto|\
|
|
|
973
|
+Select distinct ntl.sizeMeasureType \
|
|
819
|
974
|
From TripImpl t \
|
|
820
|
975
|
Join t.routeObs r \
|
|
821
|
976
|
Join r.activity a \
|
|
822
|
977
|
Join a.set.sample nts \
|
|
823
|
978
|
Join nts.sampleMeasure ntl \
|
|
824
|
|
-Where t.id In :tripId \
|
|
825
|
|
-And ntl.species.id = :speciesId \
|
|
826
|
|
-Group By ntl.length \
|
|
827
|
|
-Order By ntl.length
|
|
828
|
|
-####################################################################
|
|
829
|
|
-## Distribution des tailles des conservés par espèces ciblées (LD1)
|
|
830
|
|
-####################################################################
|
|
831
|
|
-report.psObservationTargetStoredDistributionLengthsLD1.modelType=PS
|
|
832
|
|
-report.psObservationTargetStoredDistributionLengthsLD1.name=Observations - Distribution des tailles des captures par espèces ciblées (mesure LD1)
|
|
833
|
|
-report.psObservationTargetStoredDistributionLengthsLD1.description=Afficher le nombre d'individus capturés, mesurés par classe de taille LD1 pour les espèces ciblées
|
|
834
|
|
-report.psObservationTargetStoredDistributionLengthsLD1.columns=Classe de taille,Effectif
|
|
835
|
|
-report.psObservationTargetStoredDistributionLengthsLD1.operations.1=GroupByLength
|
|
836
|
|
-report.psObservationTargetStoredDistributionLengthsLD1.variable.speciesId=fr.ird.observe.dto.referential.common.SpeciesDto|\
|
|
837
|
|
-Select distinct tl.species \
|
|
838
|
|
-From TripImpl t \
|
|
839
|
|
-Join t.routeObs r \
|
|
840
|
|
-Join r.activity a \
|
|
841
|
|
-Join a.set.sample ts \
|
|
842
|
|
-Join ts.sampleMeasure tl \
|
|
843
|
|
-Where t.id In :tripId \
|
|
844
|
|
-And tl.sizeMeasureType = 'fr.ird.referential.common.SizeMeasureType#1433499466774#0.529249255312607'
|
|
845
|
|
-report.psObservationTargetStoredDistributionLengthsLD1.request.1=0,0|row| \
|
|
846
|
|
-Select tl.length , Sum(tl.count) \
|
|
847
|
|
-From TripImpl t \
|
|
848
|
|
-Join t.routeObs r \
|
|
849
|
|
-Join r.activity a \
|
|
850
|
|
-Join a.set.sample ts \
|
|
851
|
|
-Join ts.sampleMeasure tl \
|
|
852
|
|
-Where t.id In :tripId \
|
|
853
|
|
- And tl.species.id = :speciesId \
|
|
854
|
|
- And tl.sizeMeasureType = 'fr.ird.referential.common.SizeMeasureType#1433499466774#0.529249255312607' \
|
|
855
|
|
-Group By tl.length \
|
|
856
|
|
-Order By tl.length
|
|
857
|
|
-################################################################
|
|
858
|
|
-## Distribution des tailles des rejets par espèces ciblées (LD1)
|
|
859
|
|
-################################################################
|
|
860
|
|
-report.psObservationTargetDiscardedDistributionLengthsLD1.modelType=PS
|
|
861
|
|
-report.psObservationTargetDiscardedDistributionLengthsLD1.name=Observations - Distribution des tailles des rejets par espèces ciblées (mesure LD1)
|
|
862
|
|
-report.psObservationTargetDiscardedDistributionLengthsLD1.description=Afficher la nombre d'individus rejetés, mesurés par classe de taille LD1 pour les espèces ciblées
|
|
863
|
|
-report.psObservationTargetDiscardedDistributionLengthsLD1.columns=Classe de taille,Effectif
|
|
864
|
|
-report.psObservationTargetDiscardedDistributionLengthsLD1.operations.1=GroupByLength
|
|
865
|
|
-report.psObservationTargetDiscardedDistributionLengthsLD1.variable.speciesId=fr.ird.observe.dto.referential.common.SpeciesDto|\
|
|
866
|
|
-Select distinct tl.species \
|
|
867
|
|
-From TripImpl t \
|
|
868
|
|
-Join t.routeObs r \
|
|
869
|
|
-Join r.activity a \
|
|
870
|
|
-Join a.set.sample ts \
|
|
871
|
|
-Join ts.sampleMeasure tl \
|
|
872
|
|
-Where t.id In :tripId \
|
|
873
|
|
-And tl.sizeMeasureType = 'fr.ird.referential.common.SizeMeasureType#1433499466774#0.529249255312607'
|
|
874
|
|
-report.psObservationTargetDiscardedDistributionLengthsLD1.request.1=0,0|row| \
|
|
875
|
|
-Select tl.length , Sum(tl.count) \
|
|
876
|
|
-From TripImpl t \
|
|
877
|
|
-Join t.routeObs r \
|
|
878
|
|
-Join r.activity a \
|
|
879
|
|
-Join a.set.sample ts \
|
|
880
|
|
-Join ts.sampleMeasure tl \
|
|
881
|
|
-Where t.id In :tripId \
|
|
882
|
|
- And tl.species.id = :speciesId \
|
|
883
|
|
- And tl.sizeMeasureType = 'fr.ird.referential.common.SizeMeasureType#1433499466774#0.529249255312607'\
|
|
884
|
|
-Group By tl.length \
|
|
885
|
|
-Order By tl.length
|
|
886
|
|
-###############################################################
|
|
887
|
|
-## Distribution des tailles des conservés par espèces ciblées (LF)
|
|
888
|
|
-###############################################################
|
|
889
|
|
-report.psObservationTargetStoredDistributionLengthsLF.modelType=PS
|
|
890
|
|
-report.psObservationTargetStoredDistributionLengthsLF.name=Observations - Distribution des tailles des captures par espèces ciblées (mesure LF)
|
|
891
|
|
-report.psObservationTargetStoredDistributionLengthsLF.description=Afficher le nombre d'individus capturés, mesurés par classe de taille LF pour les espèces ciblées
|
|
892
|
|
-report.psObservationTargetStoredDistributionLengthsLF.columns=Classe de taille,Effectif
|
|
893
|
|
-report.psObservationTargetStoredDistributionLengthsLF.operations.1=GroupByLength
|
|
894
|
|
-report.psObservationTargetStoredDistributionLengthsLF.variable.speciesId=fr.ird.observe.dto.referential.common.SpeciesDto|\
|
|
895
|
|
-Select distinct tl.species \
|
|
896
|
|
-From TripImpl t \
|
|
897
|
|
-Join t.routeObs r \
|
|
898
|
|
-Join r.activity a \
|
|
899
|
|
-Join a.set.sample ts \
|
|
900
|
|
-Join ts.sampleMeasure tl \
|
|
901
|
|
-Where t.id In :tripId \
|
|
902
|
|
-And tl.sizeMeasureType = 'fr.ird.referential.common.SizeMeasureType#1433499465700#0.0902433863375336'
|
|
903
|
|
-report.psObservationTargetStoredDistributionLengthsLF.request.1=0,0|row| \
|
|
904
|
|
-Select tl.length , Sum(tl.count) \
|
|
|
979
|
+Where t.id In :tripId
|
|
|
980
|
+report.psObservationLengthsDistribution.repeatVariable.speciesFate=fr.ird.observe.entities.referential.ps.common.SpeciesFate|\
|
|
|
981
|
+Select distinct sf \
|
|
905
|
982
|
From TripImpl t \
|
|
906
|
983
|
Join t.routeObs r \
|
|
907
|
984
|
Join r.activity a \
|
|
908
|
|
-Join a.set.sample ts \
|
|
909
|
|
-Join ts.sampleMeasure tl \
|
|
|
985
|
+Join a.set.sample nts \
|
|
|
986
|
+Join nts.sampleMeasure ntl with ntl.species.id = :species And ntl.sizeMeasureType.id = :sizeMeasureType \
|
|
|
987
|
+Left Join ntl.speciesFate sf \
|
|
910
|
988
|
Where t.id In :tripId \
|
|
911
|
|
- And tl.species.id = :speciesId \
|
|
912
|
|
- And tl.sizeMeasureType = 'fr.ird.referential.common.SizeMeasureType#1433499465700#0.0902433863375336' \
|
|
913
|
|
-Group By tl.length \
|
|
914
|
|
-Order By tl.length
|
|
915
|
|
-###############################################################
|
|
916
|
|
-## Distribution des tailles des rejets par espèces ciblées (LF)
|
|
917
|
|
-###############################################################
|
|
918
|
|
-report.psObservationTargetDiscardedDistributionLengthsLF.modelType=PS
|
|
919
|
|
-report.psObservationTargetDiscardedDistributionLengthsLF.name=Observations - Distribution des tailles des rejets par espèces ciblées (mesure LF)
|
|
920
|
|
-report.psObservationTargetDiscardedDistributionLengthsLF.description=Afficher le nombre d'individus rejetés, mesurés par classe de taille LF pour les espèces ciblées
|
|
921
|
|
-report.psObservationTargetDiscardedDistributionLengthsLF.columns=Classe de taille,Effectif
|
|
922
|
|
-report.psObservationTargetDiscardedDistributionLengthsLF.operations.1=GroupByLength
|
|
923
|
|
-report.psObservationTargetDiscardedDistributionLengthsLF.variable.speciesId=fr.ird.observe.dto.referential.common.SpeciesDto|\
|
|
924
|
|
-Select distinct tl.species \
|
|
|
989
|
+Order By sf.code
|
|
|
990
|
+report.psObservationLengthsDistribution.repeatVariable.length=java.lang.Float|\
|
|
|
991
|
+Select distinct ntl.length \
|
|
925
|
992
|
From TripImpl t \
|
|
926
|
993
|
Join t.routeObs r \
|
|
927
|
994
|
Join r.activity a \
|
|
928
|
|
-Join a.set.sample ts \
|
|
929
|
|
-Join ts.sampleMeasure tl \
|
|
|
995
|
+Join a.set.sample nts \
|
|
|
996
|
+Join nts.sampleMeasure ntl with ntl.species.id = :species And ntl.sizeMeasureType.id = :sizeMeasureType \
|
|
930
|
997
|
Where t.id In :tripId \
|
|
931
|
|
-And tl.sizeMeasureType = 'fr.ird.referential.common.SizeMeasureType#1433499465700#0.0902433863375336'
|
|
932
|
|
-report.psObservationTargetDiscardedDistributionLengthsLF.request.1=0,0|row| \
|
|
933
|
|
-Select tl.length , Sum(tl.count) \
|
|
|
998
|
+Order By ntl.length
|
|
|
999
|
+report.psObservationLengthsDistribution.operations.1=CopyRepeatVariableToFirstColumn:length
|
|
|
1000
|
+report.psObservationLengthsDistribution.operations.2=ExecuteRequestAndReorganizeCountByRepeatVariable:length|speciesFate|\
|
|
|
1001
|
+Select ntl.length, sf.id, sum(ntl.count) \
|
|
934
|
1002
|
From TripImpl t \
|
|
935
|
1003
|
Join t.routeObs r \
|
|
936
|
1004
|
Join r.activity a \
|
|
937
|
|
-Join a.set.sample ts \
|
|
938
|
|
-Join ts.sampleMeasure tl \
|
|
|
1005
|
+Join a.set.sample nts \
|
|
|
1006
|
+Join nts.sampleMeasure ntl With ntl.species.id = :species And ntl.sizeMeasureType.id = :sizeMeasureType \
|
|
|
1007
|
+Left Join ntl.speciesFate sf with sf.id In :speciesFate \
|
|
939
|
1008
|
Where t.id In :tripId \
|
|
940
|
|
- And tl.species.id = :speciesId \
|
|
941
|
|
- And tl.sizeMeasureType = 'fr.ird.referential.common.SizeMeasureType#1433499465700#0.0902433863375336' \
|
|
942
|
|
-Group By tl.length \
|
|
943
|
|
-Order By tl.length
|
|
|
1009
|
+Group By ntl.length, sf \
|
|
|
1010
|
+Order By ntl.length
|
|
|
1011
|
+report.psObservationLengthsDistribution.operations.3=ComputeDynamicHeader:Classe de taille (cm)|speciesFate
|
|
|
1012
|
+report.psObservationLengthsDistribution.operations.4=SumIntRow:1|1
|
|
944
|
1013
|
############################################
|
|
945
|
1014
|
## Répartition des calées par cuves
|
|
946
|
1015
|
############################################
|