All queries



Query vesselTypesByIds

SQL query HQL query
select vesseltype0_.ID as col_0_0_, vesseltype0_.NAME as col_1_0_ from PUBLIC.VESSEL_TYPE vesseltype0_ where ( vesseltype0_.ID in ( ? ) ) and ( vesseltype0_.STATUS_FK in ( ? , ? ) ); SELECT vt.id, vt.name FROM VesselTypeImpl vt WHERE vt.id IN(:vesselTypeIds) AND vt.status.code IN (:statusValidCode, :statusTemporaryCode)
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
vesselTypeIds java.lang.String  


Query vesselOwnerByVesselCode

SQL query HQL query
select vesselowne1_.ID as col_0_0_, vesselowne1_.REGISTRATION_CODE as col_1_0_, vesselowne1_.LASTNAME as col_2_0_, vesselowne1_.FIRSTNAME as col_3_0_, vesselowne1_.STREET as col_4_0_, vesselowne1_.ZIP_CODE as col_5_0_, vesselowne1_.CITY as col_6_0_, vesselowne0_.START_DATE_TIME as col_7_0_ from PUBLIC.VESSEL_OWNER_PERIOD vesselowne0_ inner join PUBLIC.VESSEL_OWNER vesselowne1_ on vesselowne0_.VESSEL_OWNER_FK=vesselowne1_.ID where vesselowne1_.PROGRAM_FK=? and vesselowne0_.VESSEL_FK=? and vesselowne0_.START_DATE_TIME<=? and coalesce(vesselowne0_.END_DATE_TIME, ?)>=? order by vesselowne0_.START_DATE_TIME DESC; SELECT vo.id, vo.registrationCode, vo.lastname, vo.firstname, vo.street, vo.zipCode, vo.city, vop.id.startDateTime as startDateTime FROM VesselOwnerPeriodImpl vop INNER JOIN vop.id.vesselOwner vo WHERE vo.program.code = :programCode AND vop.id.vessel.code = :vesselCode AND vop.id.startDateTime <= :refDate AND coalesce(vop.endDateTime, :refDate) >= :refDate ORDER BY vop.id.startDateTime DESC
Parameters
Name Type Description
programCode java.lang.String  
vesselCode java.lang.String  
refDate java.util.Date  


Query gearsByIds

SQL query HQL query
select gearimpl0_.ID as col_0_0_, gearimpl0_.LABEL as col_1_0_, gearimpl0_.NAME as col_2_0_, gearimpl0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.GEAR gearimpl0_ inner join PUBLIC.STATUS statusimpl1_ on gearimpl0_.STATUS_FK=statusimpl1_.CODE where gearimpl0_.GEAR_CLASSIFICATION_FK=? and ( ( gearimpl0_.STATUS_FK in ( ? , ? ) ) and ( gearimpl0_.ID in ( ? ) ) or gearimpl0_.STATUS_FK=? ); SELECT g.id AS id, g.label AS label, g.name AS name, g.status AS status FROM GearImpl g WHERE g.gearClassification.id=:gearClassificationId AND ( ( g.status.code IN (:statusValidCode, :statusTemporaryCode) AND g.id IN (:ids) ) OR g.status.code = :statusTemporaryCode )
Parameters
Name Type Description
statusValidCode java.lang.String  
gearClassificationId java.lang.Integer  
statusTemporaryCode java.lang.String  
ids java.lang.String  


Query regionalizedItemsByParentLocations

SQL query HQL query
select distinct spatialite0_.OBJECT_ID as col_0_0_, spatialite1_.LOCALIZED_NAME as col_1_0_ from PUBLIC.SPATIAL_ITEM spatialite0_ inner join PUBLIC.SPATIAL_ITEM2LOCATION spatialite1_ on spatialite0_.ID=spatialite1_.SPATIAL_ITEM_FK cross join PUBLIC.LOCATION_HIERARCHY locationhi2_ where spatialite0_.SPATIAL_ITEM_TYPE_FK=? and ( locationhi2_.PARENT_LOCATION_FK in ( ? ) ) and spatialite1_.LOCATION_FK=locationhi2_.CHILD_LOCATION_FK; SELECT DISTINCT si.objectId, i2l.localizedName FROM SpatialItemImpl si INNER JOIN si.spatialItem2Locations i2l, LocationHierarchyImpl lh WHERE si.spatialItemType.id = :spatialItemType AND lh.id.parent.id IN (:parentLocationIds) AND i2l.id.location.id = lh.id.location.id
Parameters
Name Type Description
parentLocationIds java.lang.String  
spatialItemType java.lang.Integer  


Query produceEntitiesByFishingTrip

Get produces (as Hibernate entities) by fishing trip

SQL query HQL query
select produces1_.ID as ID1_118_, produces1_.IS_DISCARD as IS_DISCA2_118_, produces1_.SUBGROUP_COUNT as SUBGROUP3_118_, produces1_.INDIVIDUAL_COUNT as INDIVIDU4_118_, produces1_.TAXON_GROUP_OTHER_INFORMATION as TAXON_GR5_118_, produces1_.CONTROL_DATE as CONTROL_6_118_, produces1_.VALIDATION_DATE as VALIDATI7_118_, produces1_.QUALIFICATION_DATE as QUALIFIC8_118_, produces1_.QUALIFICATION_COMMENTS as QUALIFIC9_118_, produces1_.WEIGHT as WEIGHT10_118_, produces1_.COST as COST11_118_, produces1_.REMOTE_ID as REMOTE_12_118_, produces1_.OTHER_TAXON_GROUP_FK as OTHER_T13_118_, produces1_.TAXON_GROUP_FK as TAXON_G14_118_, produces1_.SALE_TYPE_FK as SALE_TY15_118_, produces1_.OTHER_GEAR_FK as OTHER_G16_118_, produces1_.DRESSING_FK as DRESSIN17_118_, produces1_.PRESERVATION_FK as PRESERV18_118_, produces1_.TRANSACTION_FK as TRANSAC19_118_, produces1_.FRESHNESS_CATEGORY_FK as FRESHNE20_118_, produces1_.SIZE_CATEGORY_FK as SIZE_CA21_118_, produces1_.WEIGHT_METHOD_FK as WEIGHT_22_118_, produces1_.FISHING_OPERATION_FK as FISHING23_118_, produces1_.EXPECTED_SALE_FK as EXPECTE24_118_, produces1_.BATCH_FK as BATCH_F25_118_, produces1_.BUYER_FK as BUYER_F26_118_, produces1_.SALE_FK as SALE_FK27_118_, produces1_.GEAR_FK as GEAR_FK28_118_, produces1_.LANDING_FK as LANDING29_118_, produces1_.TAKE_OVER_FK as TAKE_OV30_118_, produces1_.QUALITY_FLAG_FK as QUALITY31_118_, produces1_.TRANSSHIPMENT_FK as TRANSSH32_118_ from PUBLIC.OPERATION fishingope0_ inner join PUBLIC.PRODUCE produces1_ on fishingope0_.ID=produces1_.FISHING_OPERATION_FK where fishingope0_.IS_FISHING_OPERATION=1 and fishingope0_.FISHING_TRIP_FK=? and ( produces1_.EXPECTED_SALE_FK is null ); SELECT p FROM FishingOperationImpl o inner join o.produces p WHERE o.fishingTrip.id = :fishingTripId AND p.expectedSale is null
Parameters
Name Type Description
fishingTripId java.lang.Integer  


Query allNearbySpecificAreas

SQL query HQL query
select nearbyspec0_.ID as col_0_0_, nearbyspec0_.NAME as col_1_0_, nearbyspec0_.STATUS_FK as col_2_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.NEARBY_SPECIFIC_AREA nearbyspec0_ inner join PUBLIC.STATUS statusimpl1_ on nearbyspec0_.STATUS_FK=statusimpl1_.CODE where nearbyspec0_.STATUS_FK in ( ? , ? ); SELECT g.id AS id, g.name AS name, g.status AS status FROM NearbySpecificAreaImpl g WHERE g.status.code IN (:statusValidCode, :statusTemporaryCode)
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  


Query dailyActivityCalendarByObservedLocationAndVessel

Get a daily activity calendar by observed location and vessel

SQL query HQL query
select dailyactiv0_.ID as col_0_0_, dailyactiv0_.VESSEL_FK as col_1_0_, dailyactiv0_.START_DATE as col_2_0_, dailyactiv0_.END_DATE as col_3_0_, dailyactiv0_.SYNCHRONIZATION_STATUS as col_4_0_, (select vesselusem4_.NUMERICAL_VALUE from PUBLIC.VESSEL_USE_MEASUREMENT vesselusem4_ inner join PUBLIC.VESSEL_USE_FEATURES vesselusef5_ on vesselusem4_.VESSEL_USE_FEATURES_FK=vesselusef5_.ID, PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv6_ where vesselusef5_.DAILY_ACTIVITY_CALENDAR_FK=dailyactiv6_.ID and vesselusef5_.DAILY_ACTIVITY_CALENDAR_FK=dailyactiv0_.ID and vesselusef5_.START_DATE=dailyactiv0_.START_DATE and vesselusef5_.END_DATE=dailyactiv0_.END_DATE and vesselusem4_.PMFM_FK=?) as col_5_0_, gearusefea1_.START_DATE as col_6_0_, gearusefea1_.END_DATE as col_7_0_, (select vesselusem7_.NUMERICAL_VALUE from PUBLIC.VESSEL_USE_MEASUREMENT vesselusem7_ inner join PUBLIC.VESSEL_USE_FEATURES vesselusef8_ on vesselusem7_.VESSEL_USE_FEATURES_FK=vesselusef8_.ID, PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv9_ where vesselusef8_.DAILY_ACTIVITY_CALENDAR_FK=dailyactiv9_.ID and vesselusef8_.DAILY_ACTIVITY_CALENDAR_FK=dailyactiv0_.ID and vesselusef8_.START_DATE=gearusefea1_.START_DATE and vesselusef8_.END_DATE=gearusefea1_.END_DATE and vesselusem7_.PMFM_FK=?) as col_8_0_, (select gearusemea10_.NUMERICAL_VALUE from PUBLIC.GEAR_USE_MEASUREMENT gearusemea10_, PUBLIC.GEAR_USE_FEATURES gearusefea11_ where gearusemea10_.GEAR_USE_FEATURES_FK=gearusefea11_.ID and gearusemea10_.GEAR_USE_FEATURES_FK=gearusefea1_.ID and gearusemea10_.PMFM_FK=?) as col_9_0_, metierimpl2_.ID as col_10_0_, metierimpl2_.LABEL as col_11_0_, metierimpl2_.NAME as col_12_0_, statusimpl3_.CODE as col_13_0_, statusimpl3_.CODE as CODE1_159_, statusimpl3_.NAME as NAME2_159_, statusimpl3_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv0_ left outer join PUBLIC.GEAR_USE_FEATURES gearusefea1_ on dailyactiv0_.ID=gearusefea1_.DAILY_ACTIVITY_CALENDAR_FK left outer join PUBLIC.METIER metierimpl2_ on gearusefea1_.METIER_FK=metierimpl2_.ID left outer join PUBLIC.STATUS statusimpl3_ on metierimpl2_.STATUS_FK=statusimpl3_.CODE where dailyactiv0_.VESSEL_FK=? and dailyactiv0_.OBSERVED_LOCATION_FK=? and ( dailyactiv0_.SYNCHRONIZATION_STATUS in ( ? ) ) order by dailyactiv0_.ID ASC, gearusefea1_.START_DATE ASC, gearusefea1_.END_DATE DESC, metierimpl2_.LABEL ASC; SELECT ac.id AS dailyACtivityCalendarId, ac.vessel.code AS vesselCode, ac.startDate AS startDate, ac.endDate AS endDate, ac.synchronizationStatus, (select vum.numericalValue from VesselUseMeasurementImpl vum inner join vum.vesselUseFeatures vuf where vuf.dailyActivityCalendar=ac and vuf.startDate=ac.startDate and vuf.endDate=ac.endDate and vum.pmfm.id=:pmfmIdActiveDaysNumber) AS activeDaysNumber, guf.startDate AS dayStartDate, guf.endDate AS dayEndDate, (select vum.numericalValue from VesselUseMeasurementImpl vum inner join vum.vesselUseFeatures vuf where vuf.dailyActivityCalendar=ac and vuf.startDate=guf.startDate and vuf.endDate=guf.endDate and vum.pmfm.id=:pmfmIdTripNumber) AS tripNumber, (select gum.numericalValue from GearUseMeasurementImpl gum WHERE gum.gearUseFeatures = guf and gum.pmfm.id=:pmfmIdActiveDaysNumber) AS aggregatedMetierActiveDaysNumber, m.id AS metierId, m.label AS metierLabel, m.name AS metierName, metierStatus AS metierStatus FROM DailyActivityCalendarImpl ac left join ac.gearUseFeatures guf left join guf.metier m left join m.status as metierStatus WHERE ac.vessel.code = :vesselCode AND ac.observedLocation.id = :observedLocationId AND ac.synchronizationStatus IN (:synchronizationStatus) ORDER BY ac.id ASC, guf.startDate ASC, guf.endDate DESC, m.label ASC
Parameters
Name Type Description
observedLocationId java.lang.Integer  
pmfmIdTripNumber java.lang.Integer  
pmfmIdActiveDaysNumber java.lang.Integer  
synchronizationStatus java.lang.String  
vesselCode java.lang.String  


Query deletePersonSessionItemsByPersonSessionId

SQL query HQL query
delete from PUBLIC.PERSON_SESSION_ITEM where PERSON_SESSION_FK=?; DELETE FROM PersonSessionItemImpl psi WHERE psi.personSession.id = :personSessionId
Parameters
Name Type Description
personSessionId java.lang.Integer  


Query deleteTemporaryPersonSessionItem

SQL query HQL query
delete from PUBLIC.PERSON_SESSION_ITEM where ID<0 and ( OBJECT_TYPE_FK in ( ? ) ); DELETE FROM PersonSessionItemImpl WHERE id < 0 AND objectType.code in (:objectTypes)
Parameters
Name Type Description
objectTypes java.lang.String  


Query observedLocationById

Get an observed location by Id

SQL query HQL query
select observedlo0_.ID as col_0_0_, observedlo0_.START_DATE_TIME as col_1_0_, observedlo0_.END_DATE_TIME as col_2_0_, observedlo0_.SAMPLING_STRATA_REFERENCE as col_3_0_, observedlo0_.COMMENTS as col_4_0_, observedlo0_.LOCATION_FK as col_5_0_, locationim1_.LABEL as col_6_0_, locationim1_.NAME as col_7_0_, observedlo0_.SYNCHRONIZATION_STATUS as col_8_0_, observedlo0_.PROGRAM_FK as col_9_0_, observedlo0_.RECORDER_PERSON_FK as col_10_0_ from PUBLIC.OBSERVED_LOCATION observedlo0_, PUBLIC.LOCATION locationim1_ where observedlo0_.LOCATION_FK=locationim1_.ID and observedlo0_.ID=?; SELECT ol.id, ol.startDateTime, ol.endDateTime, ol.samplingStrataReference, ol.comments, ol.location.id AS locationId, ol.location.label AS locationLabel, ol.location.name AS locationName, ol.synchronizationStatus AS synchronizationStatus, ol.program.code AS programCode, ol.recorderPerson.id AS recorderPersonId FROM ObservedLocationImpl ol WHERE ol.id = :observedLocationId
Parameters
Name Type Description
observedLocationId java.lang.Integer  


Query regionalizedPreservingsByTaxonGroupId

SQL query HQL query
select distinct roundweigh0_.PRESERVING_FK as col_0_0_ from PUBLIC.ROUND_WEIGHT_CONVERSION roundweigh0_ where ( roundweigh0_.TAXON_GROUP_FK in ( select taxongroup1_.PARENT_TAXON_GROUP_FK from PUBLIC.TAXON_GROUP_HIERARCHY taxongroup1_ where taxongroup1_.CHILD_TAXON_GROUP_FK=? ) ) and roundweigh0_.LOCATION_FK=? and roundweigh0_.START_DATE<=? and coalesce(roundweigh0_.END_DATE, ?)>=?; SELECT DISTINCT c.preserving.id FROM RoundWeightConversionImpl c WHERE c.taxonGroup.id IN ( SELECT tgh.taxonGroupHierarchyPk.parentTaxonGroup.id FROM TaxonGroupHierarchyImpl tgh WHERE tgh.taxonGroupHierarchyPk.childTaxonGroup.id = :taxonGroupId) AND c.location.id = :locationId AND NOT( c.startDate > :endDate OR coalesce(c.endDate, :startDate) < :startDate )
Parameters
Name Type Description
endDate java.util.Date  
locationId java.lang.String  
taxonGroupId java.lang.Integer  
startDate java.util.Date  


Query fishingMetiersByIds

SQL query HQL query
select metierimpl0_.ID as col_0_0_, metierimpl0_.LABEL as col_1_0_, metierimpl0_.NAME as col_2_0_, metierimpl0_.STATUS_FK as col_3_0_, metierimpl0_.GEAR_FK as col_4_0_, metierimpl0_.TAXON_GROUP_FK as col_5_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.METIER metierimpl0_ inner join PUBLIC.STATUS statusimpl1_ on metierimpl0_.STATUS_FK=statusimpl1_.CODE where ( metierimpl0_.TAXON_GROUP_FK is not null ) and ( metierimpl0_.GEAR_FK is not null ) and ( ( metierimpl0_.STATUS_FK in ( ? , ? ) ) and ( metierimpl0_.ID in ( ? ) ) or metierimpl0_.STATUS_FK=? ) order by metierimpl0_.LABEL; SELECT m.id, m.label, m.name, m.status, m.gear.id, m.taxonGroup.id FROM MetierImpl m WHERE m.taxonGroup.id is not null AND m.gear.id is not null AND ( ( m.status.code IN (:statusValidCode, :statusTemporaryCode) AND m.id IN (:ids) ) OR m.status.code = :statusTemporaryCode ) ORDER BY m.label
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
ids java.lang.String  


Query landingById

Get a landings by Id

SQL query HQL query
select landingimp0_.ID as col_0_0_, landingimp0_.LANDING_DATE_TIME as col_1_0_, landingimp0_.SYNCHRONIZATION_STATUS as col_2_0_, landingimp0_.VESSEL_FK as col_3_0_, vesselimpl3_.VESSEL_TYPE_FK as col_4_0_, denormaliz2_.NAME as col_5_0_, denormaliz2_.REGISTRATION_CODE as col_6_0_, denormaliz2_.INT_REGISTRATION_CODE as col_7_0_, denormaliz2_.REGISTRATION_LOCATION_FK as col_8_0_, locationim4_.LABEL as col_9_0_, locationim4_.NAME as col_10_0_, vesselimpl3_.STATUS_FK as col_11_0_, (select landingmea8_.QUALITATIVE_VALUE_FK from PUBLIC.LANDING_MEASUREMENT landingmea8_ where landingmea8_.LANDING_FK=landingimp0_.ID and landingmea8_.PMFM_FK=?) as col_12_0_, fishingtri1_.ID as col_13_0_, fishingtri1_.SYNCHRONIZATION_STATUS as col_14_0_, landingimp0_.OBSERVED_LOCATION_FK as col_15_0_, statusimpl7_.CODE as CODE1_159_, statusimpl7_.NAME as NAME2_159_, statusimpl7_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LANDING landingimp0_ left outer join PUBLIC.FISHING_TRIP fishingtri1_ on landingimp0_.FISHING_TRIP_FK=fishingtri1_.ID, PUBLIC.VESSEL vesselimpl3_ inner join PUBLIC.STATUS statusimpl7_ on vesselimpl3_.STATUS_FK=statusimpl7_.CODE cross join PUBLIC.DENORMALIZED_VESSEL denormaliz2_, PUBLIC.LOCATION locationim4_ where landingimp0_.VESSEL_FK=vesselimpl3_.CODE and denormaliz2_.REGISTRATION_LOCATION_FK=locationim4_.ID and landingimp0_.ID=? and denormaliz2_.VESSEL_FK=landingimp0_.VESSEL_FK; SELECT la.id AS id, la.landingDateTime AS landingDateTime, la.synchronizationStatus AS synchronizationStatus, la.vessel.code AS vesselCode, la.vessel.vesselType.id AS vesselTypeId, dv.name as name, dv.registrationCode as registrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationLocation.id as registrationLocationId, dv.registrationLocation.label as registrationLocationLabel, dv.registrationLocation.name as registrationLocationName, la.vessel.status AS vesselStatus, (select lm.qualitativeValue.id from LandingMeasurementImpl lm where lm.landing.id=la.id and lm.pmfm.id=:pmfmIdVesselPortState) AS vesselPortStateQvId, ft.id AS fishingTripId, ft.synchronizationStatus AS fishingTripSynchronizationStatus, la.observedLocation.id FROM LandingImpl la left join la.fishingTrip ft, DenormalizedVesselImpl dv WHERE la.id = :landingId AND dv.vessel = la.vessel
Parameters
Name Type Description
pmfmIdVesselPortState java.lang.Integer  
landingId java.lang.Integer  


Query findDuplicateLandingsById

Get duplicate landing corresponding to a natural key

SQL query HQL query
select landingimp0_.ID as col_0_0_ from PUBLIC.LANDING landingimp0_ where landingimp0_.ID<>? and landingimp0_.PROGRAM_FK=( select landingimp1_.PROGRAM_FK from PUBLIC.LANDING landingimp1_ where landingimp1_.ID=? ) and landingimp0_.VESSEL_FK=( select landingimp2_.VESSEL_FK from PUBLIC.LANDING landingimp2_ where landingimp2_.ID=? ) and landingimp0_.LANDING_DATE_TIME=( select landingimp3_.LANDING_DATE_TIME from PUBLIC.LANDING landingimp3_ where landingimp3_.ID=? ) order by landingimp0_.ID; SELECT la.id AS id FROM LandingImpl la WHERE la.id != :landingId AND la.program.id = (SELECT program.id FROM LandingImpl WHERE id = :landingId) AND la.vessel.code = (SELECT vessel.code FROM LandingImpl WHERE id = :landingId) AND la.landingDateTime = (SELECT landingDateTime FROM LandingImpl WHERE id = :landingId) ORDER BY la.id
Parameters
Name Type Description
landingId java.lang.Integer  


Query userRightsOnVesselAndPeriod

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.PERSON_SESSION_VESSEL personsess0_ cross join PUBLIC.PERSON_SESSION personsess1_ where personsess0_.PERSON_SESSION_FK=personsess1_.ID and personsess1_.PERSON_FK=? and personsess0_.VESSEL_FK=? and ( personsess0_.END_DATE_TIME is null or personsess0_.END_DATE_TIME>=? ) and personsess0_.START_DATE_TIME<=?; SELECT COUNT(*) FROM PersonSessionVesselImpl pvs WHERE pvs.personSession.person.id = :personId AND pvs.vessel.code = :vesselCode AND (pvs.endDateTime is null OR pvs.endDateTime >= :startDate) AND pvs.startDateTime <= :endDate
Parameters
Name Type Description
endDate java.sql.Timestamp  
personId java.lang.Integer  
vesselCode java.lang.String  
startDate java.sql.Timestamp  


Query metierById

SQL query HQL query
select metierimpl0_.ID as col_0_0_, metierimpl0_.LABEL as col_1_0_, metierimpl0_.NAME as col_2_0_, metierimpl0_.STATUS_FK as col_3_0_, metierimpl0_.GEAR_FK as col_4_0_, metierimpl0_.TAXON_GROUP_FK as col_5_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.METIER metierimpl0_ inner join PUBLIC.STATUS statusimpl1_ on metierimpl0_.STATUS_FK=statusimpl1_.CODE where metierimpl0_.ID=?; SELECT m.id, m.label, m.name, m.status, m.gear.id, m.taxonGroup.id FROM MetierImpl m WHERE m.id=:metierId
Parameters
Name Type Description
metierId java.lang.Integer  


Query observedLocationByFishingTripId

Get an observed location by FishingTripId

SQL query HQL query
select observedlo2_.ID as col_0_0_, observedlo2_.START_DATE_TIME as col_1_0_, observedlo2_.END_DATE_TIME as col_2_0_, observedlo2_.SAMPLING_STRATA_REFERENCE as col_3_0_, observedlo2_.COMMENTS as col_4_0_, observedlo2_.LOCATION_FK as col_5_0_, locationim3_.LABEL as col_6_0_, locationim3_.NAME as col_7_0_, observedlo2_.SYNCHRONIZATION_STATUS as col_8_0_, observedlo2_.PROGRAM_FK as col_9_0_, observedlo2_.RECORDER_PERSON_FK as col_10_0_ from PUBLIC.FISHING_TRIP fishingtri0_ inner join PUBLIC.LANDING landings1_ on fishingtri0_.ID=landings1_.FISHING_TRIP_FK inner join PUBLIC.OBSERVED_LOCATION observedlo2_ on landings1_.OBSERVED_LOCATION_FK=observedlo2_.ID, PUBLIC.LOCATION locationim3_ where observedlo2_.LOCATION_FK=locationim3_.ID and fishingtri0_.ID=?; SELECT ol.id, ol.startDateTime, ol.endDateTime, ol.samplingStrataReference, ol.comments, ol.location.id AS locationId, ol.location.label AS locationLabel, ol.location.name AS locationName, ol.synchronizationStatus AS synchronizationStatus, ol.program.code AS programCode, ol.recorderPerson.id AS recorderPersonId FROM FishingTripImpl ft inner join ft.landings l inner join l.observedLocation ol WHERE ft.id = :fishingTripId
Parameters
Name Type Description
fishingTripId java.lang.Integer  


Query selectLocationIdByLabelAndLocationLevel

SQL query HQL query
select locationim0_.ID as col_0_0_ from PUBLIC.LOCATION locationim0_ where locationim0_.LABEL=? and ( locationim0_.LOCATION_LEVEL_FK in ( ? ) ); SELECT l.id FROM LocationImpl l WHERE l.label=:locationLabel AND l.locationLevel.id IN (:locationLevelIds)
Parameters
Name Type Description
locationLevelIds java.lang.String  
locationLabel java.lang.String  


Query observedLocationByDailyActivityCalendarId

Get an observed location by DailyActivityCalendarId

SQL query HQL query
select observedlo1_.ID as col_0_0_, observedlo1_.START_DATE_TIME as col_1_0_, observedlo1_.END_DATE_TIME as col_2_0_, observedlo1_.SAMPLING_STRATA_REFERENCE as col_3_0_, observedlo1_.COMMENTS as col_4_0_, observedlo1_.LOCATION_FK as col_5_0_, locationim2_.LABEL as col_6_0_, locationim2_.NAME as col_7_0_, observedlo1_.SYNCHRONIZATION_STATUS as col_8_0_, observedlo1_.PROGRAM_FK as col_9_0_, observedlo1_.RECORDER_PERSON_FK as col_10_0_ from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv0_ inner join PUBLIC.OBSERVED_LOCATION observedlo1_ on dailyactiv0_.OBSERVED_LOCATION_FK=observedlo1_.ID, PUBLIC.LOCATION locationim2_ where observedlo1_.LOCATION_FK=locationim2_.ID and dailyactiv0_.ID=?; SELECT ol.id, ol.startDateTime, ol.endDateTime, ol.samplingStrataReference, ol.comments, ol.location.id AS locationId, ol.location.label AS locationLabel, ol.location.name AS locationName, ol.synchronizationStatus AS synchronizationStatus, ol.program.code AS programCode, ol.recorderPerson.id AS recorderPersonId FROM DailyActivityCalendarImpl ac inner join ac.observedLocation ol WHERE ac.id = :dailyActivityCalendarId
Parameters
Name Type Description
dailyActivityCalendarId java.lang.Integer  


Query fishingTripsByObservedLocationAndVessel

Get all fishing trips by observedLocation and vessel

SQL query HQL query
select fishingtri0_.ID as col_0_0_, fishingtri0_.DEPARTURE_DATE_TIME as col_1_0_, fishingtri0_.RETURN_DATE_TIME as col_2_0_, fishingtri0_.SYNCHRONIZATION_STATUS as col_3_0_, metierimpl5_.ID as col_4_0_, metierimpl5_.LABEL as col_5_0_, metierimpl5_.NAME as col_6_0_, statusimpl6_.CODE as col_7_0_, statusimpl6_.CODE as CODE1_159_, statusimpl6_.NAME as NAME2_159_, statusimpl6_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.FISHING_TRIP fishingtri0_ inner join PUBLIC.LANDING landings1_ on fishingtri0_.ID=landings1_.FISHING_TRIP_FK inner join PUBLIC.OBSERVED_LOCATION observedlo2_ on landings1_.OBSERVED_LOCATION_FK=observedlo2_.ID left outer join PUBLIC.OPERATION fishingope3_ on fishingtri0_.ID=fishingope3_.FISHING_TRIP_FK and fishingope3_.IS_FISHING_OPERATION=1 left outer join PUBLIC.GEAR_USE_FEATURES gearusefea4_ on fishingope3_.ID=gearusefea4_.OPERATION_FK left outer join PUBLIC.METIER metierimpl5_ on gearusefea4_.METIER_FK=metierimpl5_.ID left outer join PUBLIC.STATUS statusimpl6_ on metierimpl5_.STATUS_FK=statusimpl6_.CODE where observedlo2_.ID=? and fishingtri0_.VESSEL_FK=? and ( fishingope3_.ID is null or fishingope3_.START_DATE_TIME=fishingtri0_.DEPARTURE_DATE_TIME and fishingope3_.END_DATE_TIME=fishingtri0_.RETURN_DATE_TIME ) and ( fishingtri0_.SYNCHRONIZATION_STATUS in ( ? ) ) order by fishingtri0_.RETURN_DATE_TIME ASC, fishingtri0_.ID, fishingope3_.IS_MAIN_OPERATION DESC, fishingope3_.RANK_ORDER_ON_PERIOD ASC; SELECT ft.id AS id, ft.departureDateTime AS startDateTime, ft.returnDateTime AS endDateTime, ft.synchronizationStatus AS synchronizationStatus, m.id AS metierId, m.label AS metierLabel, m.name AS metierName, m_status AS metierStatus FROM FishingTripImpl ft join ft.landings la join la.observedLocation ol left join ft.fishingOperations o left join o.gearUseFeatures guf left join guf.metier m left join m.status m_status WHERE ol.id = :observedLocationId and ft.vessel.code = :vesselCode and (o.id is null OR (o.startDateTime = ft.departureDateTime AND o.endDateTime = ft.returnDateTime)) and ft.synchronizationStatus IN (:synchronizationStatus) ORDER BY ft.returnDateTime ASC, ft.id, o.isMainOperation DESC, o.rankOrderOnPeriod ASC
Parameters
Name Type Description
observedLocationId java.lang.Integer  
synchronizationStatus java.lang.String  
vesselCode java.lang.String  


Query updateFishingtripsTemporaryVessel

Replace temporary vessel in fishing trips

SQL query HQL query
update PUBLIC.FISHING_TRIP set VESSEL_FK=? where VESSEL_FK=?; UPDATE FishingTripImpl ft SET ft.vessel.code = :validVesselCode WHERE ft.vessel.code = :tempVesselCode
Parameters
Name Type Description
tempVesselCode java.lang.String  
validVesselCode java.lang.String  


Query lightLandingsByObservedLocationId

get list of vessels on an observed location (used by combo)

SQL query HQL query
select distinct landingimp0_.ID as col_0_0_, fishingtri1_.RETURN_DATE_TIME as col_1_0_, landingimp0_.SYNCHRONIZATION_STATUS as col_2_0_, landingimp0_.VESSEL_FK as col_3_0_, vesselimpl3_.VESSEL_TYPE_FK as col_4_0_, denormaliz2_.NAME as col_5_0_, denormaliz2_.REGISTRATION_CODE as col_6_0_, denormaliz2_.INT_REGISTRATION_CODE as col_7_0_, denormaliz2_.REGISTRATION_LOCATION_FK as col_8_0_, locationim4_.LABEL as col_9_0_, locationim4_.NAME as col_10_0_, vesselimpl3_.STATUS_FK as col_11_0_, statusimpl7_.CODE as CODE1_159_, statusimpl7_.NAME as NAME2_159_, statusimpl7_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LANDING landingimp0_ left outer join PUBLIC.FISHING_TRIP fishingtri1_ on landingimp0_.FISHING_TRIP_FK=fishingtri1_.ID, PUBLIC.VESSEL vesselimpl3_ inner join PUBLIC.STATUS statusimpl7_ on vesselimpl3_.STATUS_FK=statusimpl7_.CODE cross join PUBLIC.DENORMALIZED_VESSEL denormaliz2_, PUBLIC.LOCATION locationim4_ where landingimp0_.VESSEL_FK=vesselimpl3_.CODE and denormaliz2_.REGISTRATION_LOCATION_FK=locationim4_.ID and 1=1 and landingimp0_.OBSERVED_LOCATION_FK=? and denormaliz2_.VESSEL_FK=landingimp0_.VESSEL_FK order by landingimp0_.VESSEL_FK, fishingtri1_.RETURN_DATE_TIME ASC; SELECT DISTINCT la.id AS id, ft.returnDateTime AS landingDateTime, la.synchronizationStatus AS synchronizationStatus, la.vessel.code AS vesselCode, la.vessel.vesselType.id AS vesselTypeId, dv.name as name, dv.registrationCode as registrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationLocation.id as registrationLocationId, dv.registrationLocation.label as registrationLocationLabel, dv.registrationLocation.name as registrationLocationName, la.vessel.status AS vesselStatus FROM LandingImpl la left join la.fishingTrip ft, DenormalizedVesselImpl dv WHERE 1=1 AND la.observedLocation.id = :observedLocationId AND dv.vessel = la.vessel ORDER BY la.vessel.code, ft.returnDateTime ASC
Parameters
Name Type Description
observedLocationId java.lang.Integer  


Query fishingOperationCatchBatchId

SQL query HQL query
select fishingope0_.CATCH_BATCH_FK as col_0_0_ from PUBLIC.OPERATION fishingope0_ where fishingope0_.IS_FISHING_OPERATION=1 and fishingope0_.ID=?; SELECT o.catchBatch.id as catchBatchId FROM FishingOperationImpl o WHERE o.id=:fishingOperationId
Parameters
Name Type Description
fishingOperationId java.lang.Integer  


Query landingIdsByVesselCode

Get all landing id bys vessel code

SQL query HQL query
select distinct landingimp0_.ID as col_0_0_ from PUBLIC.LANDING landingimp0_ where 1=1 and landingimp0_.VESSEL_FK=? order by landingimp0_.ID; SELECT DISTINCT la.id AS id FROM LandingImpl la WHERE 1=1 AND la.vessel.code = :vesselCode ORDER BY la.id
Parameters
Name Type Description
vesselCode java.lang.String  


Query allTaxonNamesIsReferent

SQL query HQL query
select taxonnamei0_.REFERENCE_TAXON_FK as col_0_0_, taxonnamei0_.ID as col_1_0_, taxonnamei0_.IS_REFERENT as col_2_0_, taxonnamei0_.NAME as col_3_0_, taxonnamei0_.IS_TEMPORARY as col_4_0_ from PUBLIC.TAXON_NAME taxonnamei0_ where taxonnamei0_.IS_OBSOLETE=false and taxonnamei0_.IS_REFERENT=true order by taxonnamei0_.NAME; SELECT tn.referenceTaxon.id, tn.id, tn.isReferent, tn.name, tn.isTemporary FROM TaxonNameImpl tn WHERE tn.isObsolete = false and tn.isReferent = true ORDER BY tn.name


Query fishingTripCountByVesselCode

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.FISHING_TRIP fishingtri0_ where fishingtri0_.VESSEL_FK=?; SELECT COUNT(*) FROM FishingTripImpl ft WHERE ft.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query deleteVesselRegistrationPeriods

Delete vessel registration periods for a specific vessel

SQL query HQL query
delete from PUBLIC.VESSEL_REGISTRATION_PERIOD where VESSEL_FK=?; DELETE FROM VesselRegistrationPeriodImpl vrp WHERE vrp.id.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query notExportableObservedLocationsWithVessel

Return observed locations :
- with the given synchronization status
- with at least one landing and vessel
- not exportable by user, user has NO rights on one vessel/program, during the observation period

SQL query HQL query
select distinct observedlo1_.ID as col_0_0_, observedlo1_.START_DATE_TIME as col_1_0_, observedlo1_.END_DATE_TIME as col_2_0_, observedlo1_.SAMPLING_STRATA_REFERENCE as col_3_0_, observedlo1_.COMMENTS as col_4_0_, observedlo1_.LOCATION_FK as col_5_0_, locationim2_.LABEL as col_6_0_, locationim2_.NAME as col_7_0_, observedlo1_.SYNCHRONIZATION_STATUS as col_8_0_, observedlo1_.PROGRAM_FK as col_9_0_, observedlo1_.RECORDER_PERSON_FK as col_10_0_ from PUBLIC.PERSON_SESSION personsess0_ cross join PUBLIC.OBSERVED_LOCATION observedlo1_, PUBLIC.LOCATION locationim2_ where observedlo1_.LOCATION_FK=locationim2_.ID and 1=1 and observedlo1_.SYNCHRONIZATION_STATUS=? and personsess0_.PERSON_FK=? and ( exists ( select landingimp4_.ID from PUBLIC.LANDING landingimp4_ where landingimp4_.OBSERVED_LOCATION_FK=observedlo1_.ID ) ) and not (exists (select landingimp5_.ID from PUBLIC.LANDING landingimp5_ cross join PUBLIC.PERSON_SESSION_VESSEL personsess6_ where landingimp5_.OBSERVED_LOCATION_FK=observedlo1_.ID and personsess6_.PERSON_SESSION_FK=personsess0_.ID and personsess6_.VESSEL_FK=landingimp5_.VESSEL_FK and (personsess6_.OBJECT_TYPE_FK in (?)) and personsess6_.PROGRAM_FK=observedlo1_.PROGRAM_FK and observedlo1_.START_DATE_TIME<=personsess6_.END_DATE_TIME and observedlo1_.END_DATE_TIME>=personsess6_.START_DATE_TIME)) order by observedlo1_.START_DATE_TIME desc; SELECT DISTINCT ol.id, ol.startDateTime AS startDateTime, ol.endDateTime AS endDateTime, ol.samplingStrataReference AS samplingStrataReference, ol.comments AS comments, ol.location.id AS locationId, ol.location.label AS locationLabel, ol.location.name AS locationName, ol.synchronizationStatus AS synchronizationStatus, ol.program.code AS programCode, ol.recorderPerson.id AS recorderPersonId FROM PersonSessionImpl ps, ObservedLocationImpl ol WHERE 1=1 AND ol.synchronizationStatus = :synchronizationStatus AND ps.person.id = :personId AND EXISTS ( from LandingImpl l1 where l1.observedLocation.id = ol.id ) AND NOT EXISTS ( select l2.id from LandingImpl l2, PersonSessionVesselImpl psv where l2.observedLocation.id = ol.id and psv.personSession.id = ps.id AND psv.vessel.code = l2.vessel.code AND psv.objectType.code IN (:objectTypes) AND psv.program.code = ol.program.code AND NOT(ol.startDateTime > psv.endDateTime OR ol.endDateTime < psv.startDateTime) ) ORDER BY ol.startDateTime desc
Parameters
Name Type Description
synchronizationStatus java.lang.String  
personId java.lang.Integer  
objectTypes java.lang.String  


Query taxonNameReferentWithTranscribing

SQL query HQL query
select taxonnamei0_.REFERENCE_TAXON_FK as col_0_0_, taxonnamei0_.ID as col_1_0_, taxonnamei0_.IS_REFERENT as col_2_0_, taxonnamei0_.NAME as col_3_0_, taxonnamei0_.IS_TEMPORARY as col_4_0_, (select transcribi1_.EXTERNAL_CODE from PUBLIC.TRANSCRIBING_ITEM transcribi1_, PUBLIC.TRANSCRIBING_ITEM_TYPE transcribi3_ cross join PUBLIC.TRANSCRIBING_ITEM_TYPE transcribi2_ where transcribi1_.TRANSCRIBING_ITEM_TYPE_FK=transcribi3_.ID and transcribi1_.TRANSCRIBING_ITEM_TYPE_FK=transcribi2_.ID and transcribi2_.ID=? and transcribi1_.OBJECT_ID=taxonnamei0_.REFERENCE_TAXON_FK) as col_5_0_ from PUBLIC.TAXON_NAME taxonnamei0_ where taxonnamei0_.REFERENCE_TAXON_FK=? and taxonnamei0_.IS_REFERENT=true; SELECT tn.referenceTaxon.id, tn.id, tn.isReferent, tn.name, tn.isTemporary, ( SELECT ti.externalCode FROM TranscribingItemImpl ti, TranscribingItemTypeImpl tt WHERE ti.transcribingItemType = tt.id AND tt.id = :transcribingTypeId AND ti.objectId = tn.referenceTaxon.id ) AS externalCode FROM TaxonNameImpl tn WHERE tn.referenceTaxon.id = :referenceTaxonId AND tn.isReferent = true
Parameters
Name Type Description
referenceTaxonId java.lang.Integer  
transcribingTypeId java.lang.Integer  


Query qualitativeValueIdFromSurveyMeasurementByLandingId

get qualitative value, by landing and pmfm

SQL query HQL query
select surveymeas0_.QUALITATIVE_VALUE_FK as col_0_0_ from PUBLIC.SURVEY_MEASUREMENT surveymeas0_ where surveymeas0_.LANDING_FK=? and surveymeas0_.PMFM_FK=?; SELECT qualitativeValue.id from SurveyMeasurementImpl where landing.id=:landingId and pmfm.id=:pmfmId
Parameters
Name Type Description
pmfmId java.lang.Integer  
landingId java.lang.Integer  


Query pmfmQualitativeValues

Get a pmfm qualitative values from his id

SQL query HQL query
select qualitativ2_.ID as col_0_0_, qualitativ2_.NAME as col_1_0_, case when qualitativ2_.DESCRIPTION is null then qualitativ2_.NAME else qualitativ2_.DESCRIPTION end as col_2_0_, qualitativ2_.STATUS_FK as col_3_0_, statusimpl3_.CODE as CODE1_159_, statusimpl3_.NAME as NAME2_159_, statusimpl3_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.PMFM pmfmimpl0_ inner join PUBLIC.PMFM2QUALITATIVE_VALUE qualitativ1_ on pmfmimpl0_.ID=qualitativ1_.PMFM_FK inner join PUBLIC.QUALITATIVE_VALUE qualitativ2_ on qualitativ1_.QUALITATIVE_VALUE_FK=qualitativ2_.ID inner join PUBLIC.STATUS statusimpl3_ on qualitativ2_.STATUS_FK=statusimpl3_.CODE where pmfmimpl0_.ID=? and ( qualitativ2_.STATUS_FK in ( ? , ? ) ); SELECT qv.id AS id, qv.name, case when (qv.description is null) then qv.name else qv.description end AS description, qv.status AS status FROM PmfmImpl p JOIN p.qualitativeValues qv WHERE p.id= :pmfmId AND qv.status.code IN (:statusValidCode, :statusTemporaryCode)
Parameters
Name Type Description
pmfmId java.lang.Integer  
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  


Query updateSortingBatchReferenceTaxon

SQL query HQL query
update PUBLIC.BATCH set REFERENCE_TAXON_FK=? where IS_CATCH_BATCH=0 and ID=?; UPDATE SortingBatchImpl b SET b.referenceTaxon.id=:referenceTaxonId WHERE b.id=:sortingBatchId
Parameters
Name Type Description
sortingBatchId java.lang.Integer  
referenceTaxonId java.lang.Integer  


Query lastSystemVersion

SQL query HQL query
select systemvers0_.LABEL as col_0_0_ from PUBLIC.SYSTEM_VERSION systemvers0_ where systemvers0_.ID=( select max(systemvers1_.ID) from PUBLIC.SYSTEM_VERSION systemvers1_ where systemvers1_.LABEL not in ( '3.8.6.1' , '3.8.6.2' , '3.8.6.3' ) ); SELECT label as schemaVersion FROM SystemVersionImpl WHERE id = (select max(id) from SystemVersionImpl where label NOT IN ('3.8.6.1', '3.8.6.2', '3.8.6.3'))


Query operationCountByVesselCode

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.OPERATION fishingope0_ where fishingope0_.IS_FISHING_OPERATION=1 and fishingope0_.VESSEL_FK=?; SELECT COUNT(*) FROM FishingOperationImpl o WHERE o.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query locationLevelById

Get a location level by id

SQL query HQL query
select locationle0_.ID as col_0_0_, locationle0_.NAME as col_1_0_ from PUBLIC.LOCATION_LEVEL locationle0_ where locationle0_.ID=?; SELECT l.id AS id, l.name AS name FROM LocationLevelImpl l WHERE l.id= :locationLevelId
Parameters
Name Type Description
locationLevelId java.lang.Integer  


Query distanceToCoastGradientsByIds

SQL query HQL query
select distanceto0_.ID as col_0_0_, distanceto0_.NAME as col_1_0_, distanceto0_.STATUS_FK as col_2_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.DISTANCE_TO_COAST_GRADIENT distanceto0_ inner join PUBLIC.STATUS statusimpl1_ on distanceto0_.STATUS_FK=statusimpl1_.CODE where ( distanceto0_.STATUS_FK in ( ? , ? ) ) and ( distanceto0_.ID in ( ? ) ) order by distanceto0_.RANK_ORDER ASC; SELECT g.id AS id, g.name AS name, g.status AS status FROM DistanceToCoastGradientImpl g WHERE g.status.code IN (:statusValidCode, :statusTemporaryCode) AND g.id IN (:ids) ORDER BY g.rankOrder ASC
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
ids java.lang.String  


Query deletePersonSessionVesselByPersonSessionId

SQL query HQL query
delete from PUBLIC.PERSON_SESSION_VESSEL where PERSON_SESSION_FK=?; DELETE FROM PersonSessionVesselImpl psv WHERE psv.personSession.id = :personSessionId
Parameters
Name Type Description
personSessionId java.lang.Integer  


Query countDailyActivityCalendarByObservedLocationAndVessel

Get the number of dailyActivity calendar by observed location and vessel

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv0_ where dailyactiv0_.VESSEL_FK=? and dailyactiv0_.OBSERVED_LOCATION_FK=? and ( ? is null or dailyactiv0_.ID<>? ) and ( dailyactiv0_.SYNCHRONIZATION_STATUS in ( ? ) ); SELECT count(*) AS nbCalendar FROM DailyActivityCalendarImpl ac WHERE ac.vessel.code = :vesselCode AND ac.observedLocation.id = :observedLocationId AND (:calendarId is null OR ac.id != :calendarId) AND ac.synchronizationStatus IN (:synchronizationStatus)
Parameters
Name Type Description
observedLocationId java.lang.Integer  
calendarId java.lang.Integer  
synchronizationStatus java.lang.String  
vesselCode java.lang.String  


Query qualitativeValueById

SQL query HQL query
select qualitativ0_.ID as col_0_0_, qualitativ0_.NAME as col_1_0_, case when qualitativ0_.DESCRIPTION is null then qualitativ0_.NAME else qualitativ0_.DESCRIPTION end as col_2_0_, qualitativ0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.QUALITATIVE_VALUE qualitativ0_ inner join PUBLIC.STATUS statusimpl1_ on qualitativ0_.STATUS_FK=statusimpl1_.CODE where qualitativ0_.ID=?; SELECT qv.id AS id, qv.name, case when (qv.description is null) then qv.name else qv.description end AS description, qv.status AS status FROM QualitativeValueImpl qv WHERE qv.id = :qualitativeValueId
Parameters
Name Type Description
qualitativeValueId java.lang.Integer  


Query deleteAllTempDenormalizedVessel

SQL query HQL query
delete from PUBLIC.TEMP_DENORMALIZED_VESSEL; DELETE FROM TempDenormalizedVesselImpl


Query observedLocationByLandingId

Get an observed location by LandingId

SQL query HQL query
select observedlo1_.ID as col_0_0_, observedlo1_.START_DATE_TIME as col_1_0_, observedlo1_.END_DATE_TIME as col_2_0_, observedlo1_.SAMPLING_STRATA_REFERENCE as col_3_0_, observedlo1_.COMMENTS as col_4_0_, observedlo1_.LOCATION_FK as col_5_0_, locationim2_.LABEL as col_6_0_, locationim2_.NAME as col_7_0_, observedlo1_.SYNCHRONIZATION_STATUS as col_8_0_, observedlo1_.PROGRAM_FK as col_9_0_, observedlo1_.RECORDER_PERSON_FK as col_10_0_ from PUBLIC.LANDING landingimp0_ inner join PUBLIC.OBSERVED_LOCATION observedlo1_ on landingimp0_.OBSERVED_LOCATION_FK=observedlo1_.ID, PUBLIC.LOCATION locationim2_ where observedlo1_.LOCATION_FK=locationim2_.ID and landingimp0_.ID=?; SELECT ol.id, ol.startDateTime, ol.endDateTime, ol.samplingStrataReference, ol.comments, ol.location.id AS locationId, ol.location.label AS locationLabel, ol.location.name AS locationName, ol.synchronizationStatus AS synchronizationStatus, ol.program.code AS programCode, ol.recorderPerson.id AS recorderPersonId FROM LandingImpl l inner join l.observedLocation ol WHERE l.id = :landingId
Parameters
Name Type Description
landingId java.lang.Integer  


Query taxonGroupIdByLabel

SQL query HQL query
select taxongroup0_.ID as col_0_0_ from PUBLIC.TAXON_GROUP taxongroup0_ where taxongroup0_.LABEL=? and ( taxongroup0_.STATUS_FK in ( ? , ? ) ) and taxongroup0_.TAXON_GROUP_TYPE_FK=?; SELECT t.id AS id FROM TaxonGroupImpl t WHERE t.label = :taxonGroupLabel AND t.status.code IN (:statusValidCode, :statusTemporaryCode) AND t.taxonGroupType.code = :taxonGroupTypeCode
Parameters
Name Type Description
statusValidCode java.lang.String  
taxonGroupLabel java.lang.String  
statusTemporaryCode java.lang.String  
taxonGroupTypeCode java.lang.String  


Query deleteGearPhysicalFeatures

SQL query HQL query
delete from PUBLIC.GEAR_PHYSICAL_FEATURES where ID in ( ? ); DELETE FROM GearPhysicalFeaturesImpl gpf WHERE gpf.id IN (:gearPhysicalFeaturesIds)
Parameters
Name Type Description
gearPhysicalFeaturesIds java.lang.String  


Query updateFishingTripFixMantis30905

SQL query HQL query
update PUBLIC.FISHING_TRIP set SYNCHRONIZATION_STATUS=?, UPDATE_DATE=? where SYNCHRONIZATION_STATUS=? and ( exists ( select landingimp1_.ID from PUBLIC.LANDING landingimp1_ where landingimp1_.OBSERVED_LOCATION_FK=? and landingimp1_.FISHING_TRIP_FK=PUBLIC.FISHING_TRIP.ID ) ); UPDATE FishingTripImpl ft SET ft.synchronizationStatus = :synchronizationStatusSync, ft.updateDate = :updateDate WHERE ft.synchronizationStatus = :synchronizationStatusReadyToSync AND EXISTS( FROM LandingImpl l WHERE l.observedLocation.id = :observedLocationId AND l.fishingTrip.id = ft.id )
Parameters
Name Type Description
synchronizationStatusReadyToSync java.lang.String  
updateDate java.sql.Timestamp  
observedLocationId java.lang.Integer  
synchronizationStatusSync java.lang.String  


Query landingsForDeclaredPredocumentation

Get all landings need for declared predocumentation (vessels whose declared port is landing location)

SQL query HQL query
select distinct 'DIRTY' as col_0_0_, denormaliz0_.VESSEL_FK as col_1_0_, vesselimpl1_.VESSEL_TYPE_FK as col_2_0_, denormaliz0_.NAME as col_3_0_, denormaliz0_.REGISTRATION_CODE as col_4_0_, denormaliz0_.INT_REGISTRATION_CODE as col_5_0_, denormaliz0_.REGISTRATION_LOCATION_FK as col_6_0_, locationim2_.LABEL as col_7_0_, locationim2_.NAME as col_8_0_, vesselimpl1_.STATUS_FK as col_9_0_, statusimpl5_.CODE as CODE1_159_, statusimpl5_.NAME as NAME2_159_, statusimpl5_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.DENORMALIZED_VESSEL denormaliz0_, PUBLIC.VESSEL vesselimpl1_ inner join PUBLIC.STATUS statusimpl5_ on vesselimpl1_.STATUS_FK=statusimpl5_.CODE, PUBLIC.LOCATION locationim2_ where denormaliz0_.VESSEL_FK=vesselimpl1_.CODE and denormaliz0_.REGISTRATION_LOCATION_FK=locationim2_.ID and 1=1 and denormaliz0_.BASE_PORT_LOCATION_FK=? order by denormaliz0_.VESSEL_FK ASC; SELECT DISTINCT 'DIRTY' AS synchronizationStatus, dv.vessel.code AS vesselCode, dv.vessel.vesselType.id AS vesselTypeId, dv.name as name, dv.registrationCode as registrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationLocation.id as registrationLocationId, dv.registrationLocation.label as registrationLocationLabel, dv.registrationLocation.name as registrationLocationName, dv.vessel.status AS vesselStatus FROM DenormalizedVesselImpl dv WHERE 1=1 AND dv.basePortLocation.id = :landingLocationId ORDER BY dv.vessel.code ASC
Parameters
Name Type Description
landingLocationId java.lang.Integer  


Query checkVesselConcurrencyInLandings

SQL query HQL query
select distinct observedla0_.OBSERVED_LOCATION_FK as col_0_0_, observedla0_.VESSEL_FK as col_1_0_ from PUBLIC.LANDING observedla0_ where observedla0_.IS_OBSERVED=1 and ( observedla0_.VESSEL_FK=? or observedla0_.VESSEL_FK=? ) order by observedla0_.OBSERVED_LOCATION_FK; select distinct la.observedLocation.id, la.vessel.code from ObservedLandingImpl la where la.vessel.code = :tempVesselCode or la.vessel.code = :validVesselCode order by la.observedLocation.id
Parameters
Name Type Description
tempVesselCode java.lang.String  
validVesselCode java.lang.String  


Query deleteVessel

Delete vessel

SQL query HQL query
delete from PUBLIC.VESSEL where CODE=?; DELETE FROM VesselImpl v WHERE v.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query observersByFishingTripId

Get observers by fishingtripId

SQL query HQL query
select personimpl2_.ID as col_0_0_, personimpl2_.LASTNAME as col_1_0_, personimpl2_.FIRSTNAME as col_2_0_, department3_.CODE as col_3_0_, personimpl2_.STATUS_FK as col_4_0_, statusimpl4_.CODE as CODE1_159_, statusimpl4_.NAME as NAME2_159_, statusimpl4_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.FISHING_TRIP observedfi0_ inner join PUBLIC.FISHING_TRIP2OBSERVER_PERSON observerpe1_ on observedfi0_.ID=observerpe1_.FISHING_TRIP_FK inner join PUBLIC.PERSON personimpl2_ on observerpe1_.PERSON_FK=personimpl2_.ID, PUBLIC.DEPARTMENT department3_ inner join PUBLIC.STATUS statusimpl4_ on personimpl2_.STATUS_FK=statusimpl4_.CODE where observedfi0_.IS_OBSERVED=1 and personimpl2_.DEPARTMENT_FK=department3_.ID and observedfi0_.ID=?; SELECT p.id, p.lastname AS lastname, p.firstname AS firstname, p.department.code AS departmentCode, p.status FROM ObservedFishingTripImpl ft join ft.observerPersons p WHERE ft.id = :fishingTripId
Parameters
Name Type Description
fishingTripId java.lang.Integer  


Query resetCrypPasswordByExtranetLogin

Reset the user cached password (encoded with SHA).

SQL query HQL query
update PUBLIC.PERSON set CRYPT_PASSWORD=null where USERNAME_EXTRANET=?; UPDATE PersonImpl p set p.cryptPassword = null WHERE p.usernameExtranet = :personLogin
Parameters
Name Type Description
personLogin java.lang.String  


Query denormalizedVesselByRegistrationCodeOrNameOrType

Search vessels, if no registration location as been choose for filter.

SQL query HQL query
select distinct denormaliz0_.VESSEL_FK as col_0_0_, denormaliz0_.REGISTRATION_CODE as col_1_0_, denormaliz0_.INT_REGISTRATION_CODE as col_2_0_, denormaliz0_.REGISTRATION_START_DATE as col_3_0_, denormaliz0_.REGISTRATION_END_DATE as col_4_0_, denormaliz0_.NAME as col_5_0_, vesselimpl3_.VESSEL_TYPE_FK as col_6_0_, vesseltype5_.NAME as col_7_0_, denormaliz0_.REGISTRATION_COUNTRY_FK as col_8_0_, locationim6_.LABEL as col_9_0_, locationim6_.NAME as col_10_0_, locationim6_.STATUS_FK as col_11_0_, denormaliz0_.REGISTRATION_LOCATION_FK as col_12_0_, locationim10_.LABEL as col_13_0_, locationim10_.NAME as col_14_0_, locationim10_.STATUS_FK as col_15_0_, locationim1_.ID as col_16_0_, locationim1_.LABEL as col_17_0_, locationim1_.NAME as col_18_0_, statusimpl2_.CODE as col_19_0_, vesselimpl3_.STATUS_FK as col_20_0_, statusimpl9_.CODE as CODE1_159_0_, statusimpl13_.CODE as CODE1_159_1_, statusimpl2_.CODE as CODE1_159_2_, statusimpl15_.CODE as CODE1_159_3_, statusimpl9_.NAME as NAME2_159_0_, statusimpl9_.UPDATE_DATE as UPDATE_D3_159_0_, statusimpl13_.NAME as NAME2_159_1_, statusimpl13_.UPDATE_DATE as UPDATE_D3_159_1_, statusimpl2_.NAME as NAME2_159_2_, statusimpl2_.UPDATE_DATE as UPDATE_D3_159_2_, statusimpl15_.NAME as NAME2_159_3_, statusimpl15_.UPDATE_DATE as UPDATE_D3_159_3_ from PUBLIC.DENORMALIZED_VESSEL denormaliz0_ left outer join PUBLIC.LOCATION locationim1_ on denormaliz0_.BASE_PORT_LOCATION_FK=locationim1_.ID left outer join PUBLIC.STATUS statusimpl2_ on locationim1_.STATUS_FK=statusimpl2_.CODE, PUBLIC.VESSEL vesselimpl3_, PUBLIC.VESSEL_TYPE vesseltype5_ inner join PUBLIC.STATUS statusimpl15_ on vesselimpl3_.STATUS_FK=statusimpl15_.CODE, PUBLIC.LOCATION locationim6_ inner join PUBLIC.STATUS statusimpl9_ on locationim6_.STATUS_FK=statusimpl9_.CODE, PUBLIC.LOCATION locationim10_ inner join PUBLIC.STATUS statusimpl13_ on locationim10_.STATUS_FK=statusimpl13_.CODE where denormaliz0_.VESSEL_FK=vesselimpl3_.CODE and vesselimpl3_.VESSEL_TYPE_FK=vesseltype5_.ID and denormaliz0_.REGISTRATION_COUNTRY_FK=locationim6_.ID and denormaliz0_.REGISTRATION_LOCATION_FK=locationim10_.ID and ( ? is null or upper(denormaliz0_.NAME) like ('%'||?||'%') ) and ( ? is null or upper(denormaliz0_.REGISTRATION_CODE) like ('%'||?||'%') or upper(denormaliz0_.INT_REGISTRATION_CODE) like ('%'||?||'%') ) and ( vesselimpl3_.VESSEL_TYPE_FK in ( ? ) ) and ( vesselimpl3_.STATUS_FK in ( ? ) ) and ( ? is null or denormaliz0_.BASE_PORT_LOCATION_FK=? ) order by denormaliz0_.VESSEL_FK; SELECT DISTINCT dv.vessel.code AS vesselCode, dv.registrationCode AS nationalRegistrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationStartDate AS registrationStartDate, dv.registrationEndDate AS registrationEndDate, dv.name AS vesselName, dv.vessel.vesselType.id AS vesselTypeId, dv.vessel.vesselType.name AS vesselTypeName, dv.registrationCountry.id AS countryLocationId, dv.registrationCountry.label AS countryLocationLabel, dv.registrationCountry.name AS countryLocationName, dv.registrationCountry.status AS countryLocationStatus, dv.registrationLocation.id AS registrationLocationId, dv.registrationLocation.label AS registrationLocationLabel, dv.registrationLocation.name AS registrationLocationName, dv.registrationLocation.status AS registrationLocationStatus, port.id AS basePortLocationId, port.label AS basePortLocationLabel, port.name AS basePortLocationName, portStatus AS basePortLocationStatus, dv.vessel.status AS status FROM DenormalizedVesselImpl dv left join dv.basePortLocation as port left join port.status as portStatus WHERE ( :name is null OR (upper(dv.name) like '%' || :name || '%') ) AND ( :registrationCode is null OR (upper(dv.registrationCode) like '%' || :registrationCode || '%') OR (upper(dv.internationalRegistrationCode) like '%' || :registrationCode || '%') ) AND dv.vessel.vesselType.id IN (:vesselTypeIds) AND dv.vessel.status.code IN (:statusCodes) AND ( :basePortLocationId is null OR dv.basePortLocation.id = :basePortLocationId) ORDER BY dv.vessel.code
Parameters
Name Type Description
statusValidCode java.lang.String  
basePortLocationId java.lang.Integer  
statusTemporaryCode java.lang.String  
name java.lang.String  
registrationCode java.lang.String  
vesselTypeIds java.lang.String  


Query countTemporaryGearUsedInGPF

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.GEAR_PHYSICAL_FEATURES gearphysic0_ where gearphysic0_.GEAR_FK=?; SELECT count(*) FROM GearPhysicalFeaturesImpl gpf WHERE gpf.gear.id = :gearId
Parameters
Name Type Description
gearId java.lang.Integer  


Query updateMetiersTemporaryGear

SQL query HQL query
update PUBLIC.METIER set GEAR_FK=? where GEAR_FK=?; UPDATE MetierImpl m SET m.gear.id = :targetId WHERE m.gear.id = :sourceId
Parameters
Name Type Description
sourceId java.lang.Integer  
targetId java.lang.Integer  


Query countObservedLocationBySynchronizationStatus

Count observed locations, recorder by given person, by synchronisation status

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.OBSERVED_LOCATION observedlo0_ where observedlo0_.RECORDER_PERSON_FK=? and observedlo0_.SYNCHRONIZATION_STATUS=?; SELECT count(*) FROM ObservedLocationImpl ol WHERE ol.recorderPerson.id = :recorderPersonId AND ol.synchronizationStatus = :synchronizationStatus
Parameters
Name Type Description
synchronizationStatus java.lang.String  
recorderPersonId java.lang.Integer  


Query allSaleTypes

Get all sale types

SQL query HQL query
select saletypeim0_.ID as col_0_0_, saletypeim0_.NAME as col_1_0_, saletypeim0_.STATUS_FK as col_2_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.SALE_TYPE saletypeim0_ inner join PUBLIC.STATUS statusimpl1_ on saletypeim0_.STATUS_FK=statusimpl1_.CODE where saletypeim0_.STATUS_FK in ( ? , ? ); SELECT st.id AS saleTypeId, st.name AS saleTypeName, st.status AS saleTypeStatus FROM SaleTypeImpl st WHERE st.status.code IN (:statusValidCode, :statusTemporaryCode)
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  


Query deleteVesselUseFeatures

Delete vessel features for a specific vessel

SQL query HQL query
delete from PUBLIC.VESSEL_USE_FEATURES where VESSEL_FK=?; DELETE FROM VesselUseFeaturesImpl vuf WHERE vuf.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query updateGearUseFeaturesTemporaryVessel

Replace temporary vessel in gear use features

SQL query HQL query
update PUBLIC.GEAR_USE_FEATURES set VESSEL_FK=? where VESSEL_FK=?; UPDATE GearUseFeaturesImpl guf SET guf.vessel.code = :validVesselCode WHERE guf.vessel.code = :tempVesselCode
Parameters
Name Type Description
tempVesselCode java.lang.String  
validVesselCode java.lang.String  


Query vesselTypeById

SQL query HQL query
select vesseltype0_.ID as col_0_0_, vesseltype0_.NAME as col_1_0_ from PUBLIC.VESSEL_TYPE vesseltype0_ where vesseltype0_.ID=?; SELECT vt.id, vt.name FROM VesselTypeImpl vt WHERE vt.id = :vesselTypeId
Parameters
Name Type Description
vesselTypeId java.lang.integer  


Query allDistanceToCoastGradients

SQL query HQL query
select distanceto0_.ID as col_0_0_, distanceto0_.NAME as col_1_0_, distanceto0_.STATUS_FK as col_2_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.DISTANCE_TO_COAST_GRADIENT distanceto0_ inner join PUBLIC.STATUS statusimpl1_ on distanceto0_.STATUS_FK=statusimpl1_.CODE where distanceto0_.STATUS_FK in ( ? , ? ) order by distanceto0_.RANK_ORDER ASC; SELECT g.id AS id, g.name AS name, g.status AS status FROM DistanceToCoastGradientImpl g WHERE g.status.code IN (:statusValidCode, :statusTemporaryCode) ORDER BY g.rankOrder ASC
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  


Query updateGearUseFeaturesTemporaryGear

SQL query HQL query
update PUBLIC.GEAR_USE_FEATURES set GEAR_FK=? where GEAR_FK=?; UPDATE GearUseFeaturesImpl guf SET guf.gear.id = :targetId WHERE guf.gear.id = :sourceId
Parameters
Name Type Description
sourceId java.lang.Integer  
targetId java.lang.Integer  


Query updateBatchesTemporaryTaxonGroup

SQL query HQL query
update PUBLIC.BATCH set TAXON_GROUP_FK=? where IS_CATCH_BATCH=0 and TAXON_GROUP_FK=?; UPDATE SortingBatchImpl b SET b.taxonGroup.id = :targetId WHERE b.taxonGroup.id = :sourceId
Parameters
Name Type Description
sourceId java.lang.Integer  
targetId java.lang.Integer  


Query allPmfm

SQL query HQL query
select pmfmimpl0_.ID as col_0_0_, pmfmimpl0_.PARAMETER_FK as col_1_0_, parameteri1_.NAME as col_2_0_, matriximpl2_.NAME as col_3_0_, fractionim3_.NAME as col_4_0_, methodimpl4_.NAME as col_5_0_, parameteri1_.IS_ALPHANUMERIC as col_6_0_, parameteri1_.IS_QUALITATIVE as col_7_0_, pmfmimpl0_.SIGNIF_FIGURES_NUMBER as col_8_0_, pmfmimpl0_.MAXIMUM_NUMBER_DECIMALS as col_9_0_, pmfmimpl0_.PRECISION as col_10_0_, case when pmfmimpl0_.UNIT_FK=? then '' else unitimpl7_.SYMBOL end as col_11_0_, pmfmimpl0_.STATUS_FK as col_12_0_, statusimpl8_.CODE as CODE1_159_, statusimpl8_.NAME as NAME2_159_, statusimpl8_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.PMFM pmfmimpl0_, PUBLIC.PARAMETER parameteri1_, PUBLIC.MATRIX matriximpl2_, PUBLIC.FRACTION fractionim3_, PUBLIC.METHOD methodimpl4_, PUBLIC.UNIT unitimpl7_ inner join PUBLIC.STATUS statusimpl8_ on pmfmimpl0_.STATUS_FK=statusimpl8_.CODE where pmfmimpl0_.PARAMETER_FK=parameteri1_.CODE and pmfmimpl0_.MATRIX_FK=matriximpl2_.ID and pmfmimpl0_.FRACTION_FK=fractionim3_.ID and pmfmimpl0_.METHOD_FK=methodimpl4_.ID and pmfmimpl0_.UNIT_FK=unitimpl7_.ID and ( pmfmimpl0_.STATUS_FK in ( ? , ? ) ) and parameteri1_.IS_CALCULATED=false; SELECT p.id AS pmfmId, p.parameter.code AS parameterCode, p.parameter.name AS parameterName, p.matrix.name AS matrixName, p.fraction.name AS fractionName, p.method.name AS methodName, p.parameter.isAlphanumeric AS isAlphanumeric, p.parameter.isQualitative AS isQualitative, p.signifFiguresNumber, p.maximumNumberDecimals, p.precision, case when (p.unit.id = :unitIdNone) then '' else p.unit.symbol end AS symbol, p.status AS status FROM PmfmImpl p WHERE p.status.code IN (:statusValidCode, :statusTemporaryCode) AND p.parameter.isCalculated = false
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
unitIdNone java.lang.Integer  


Query gearById

SQL query HQL query
select gearimpl0_.ID as col_0_0_, gearimpl0_.LABEL as col_1_0_, gearimpl0_.NAME as col_2_0_, gearimpl0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.GEAR gearimpl0_ inner join PUBLIC.STATUS statusimpl1_ on gearimpl0_.STATUS_FK=statusimpl1_.CODE where gearimpl0_.ID=?; SELECT g.id AS id, g.label AS label, g.name AS name, g.status AS status FROM GearImpl g WHERE g.id = :gearId
Parameters
Name Type Description
gearId java.lang.Integer  


Query metiersByDailyActivityCalendarForPredocumentation

Get metiers used by vessel, but excluded metier found in the current calendar

SQL query HQL query
select distinct metierimpl1_.ID as col_0_0_, metierimpl1_.LABEL as col_1_0_, metierimpl1_.NAME as col_2_0_, metierimpl1_.STATUS_FK as col_3_0_, statusimpl2_.CODE as CODE1_159_, statusimpl2_.NAME as NAME2_159_, statusimpl2_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.GEAR_USE_FEATURES gearusefea0_ inner join PUBLIC.METIER metierimpl1_ on gearusefea0_.METIER_FK=metierimpl1_.ID inner join PUBLIC.STATUS statusimpl2_ on metierimpl1_.STATUS_FK=statusimpl2_.CODE where ( metierimpl1_.STATUS_FK in ( ? , ? ) ) and gearusefea0_.VESSEL_FK=? and ( gearusefea0_.PROGRAM_FK in ( ? ) ) and ( ? is null or gearusefea0_.DAILY_ACTIVITY_CALENDAR_FK<>? ) and gearusefea0_.START_DATE<=? and coalesce(gearusefea0_.END_DATE, ?)>=? order by metierimpl1_.LABEL ASC; SELECT DISTINCT m.id, m.label, m.name, m.status FROM GearUseFeaturesImpl guf inner join guf.metier m WHERE m.status.code IN (:statusValidCode, :statusTemporaryCode) AND guf.vessel.code = :vesselCode AND guf.program.code IN (:programCodes) AND (:dailyActivityCalendarId is null OR guf.dailyActivityCalendar.id != :dailyActivityCalendarId) AND NOT( guf.startDate > :endDate OR coalesce(guf.endDate, :startDate) < :startDate ) ORDER BY m.label ASC
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
programCodes java.lang.String  
endDate java.util.Date  
dailyActivityCalendarId java.lang.Integer  
vesselCode java.lang.String  
startDate java.util.Date  


Query childBatchIds

SQL query HQL query
select sortingbat0_.ID as col_0_0_, sortingbat0_.ROOT_BATCH_FK as col_1_0_ from PUBLIC.BATCH sortingbat0_ where sortingbat0_.IS_CATCH_BATCH=0 and sortingbat0_.PARENT_BATCH_FK=?; SELECT b.id as childBatchId, b.rootBatch.id as rootBatchId FROM SortingBatchImpl b WHERE b.parentBatch.id = :parentBatchId
Parameters
Name Type Description
parentBatchId java.lang.Integer  


Query allMeasurementFilesFromObject

SQL query HQL query
select measuremen0_.OBJECT_TYPE_FK as col_0_0_, measuremen0_.OBJECT_ID as col_1_0_, measuremen0_.ID as col_2_0_, measuremen0_.PATH as col_3_0_, measuremen0_.NAME as col_4_0_, measuremen0_.COMMENTS as col_5_0_ from PUBLIC.MEASUREMENT_FILE measuremen0_ where measuremen0_.OBJECT_ID=? and measuremen0_.OBJECT_TYPE_FK=? order by measuremen0_.ID; SELECT m.objectType.code as attachmentObjectType, m.objectId AS attachmentObjectId, m.id AS attachmentId, m.path AS attachmentPath, m.name AS attachmentName, m.comments as attachmentComment FROM MeasurementFileImpl m WHERE m.objectId = :objectId AND m.objectType.code = :objectTypeCode ORDER BY m.id
Parameters
Name Type Description
objectTypeCode java.lang.Integer  
objectId java.lang.Integer  


Query saleTypeById

get a sale type by id

SQL query HQL query
select saletypeim0_.ID as col_0_0_, saletypeim0_.NAME as col_1_0_, saletypeim0_.STATUS_FK as col_2_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.SALE_TYPE saletypeim0_ inner join PUBLIC.STATUS statusimpl1_ on saletypeim0_.STATUS_FK=statusimpl1_.CODE where saletypeim0_.ID=?; SELECT st.id AS saleTypeId, st.name AS saleTypeName, st.status AS saleTypeStatus FROM SaleTypeImpl st WHERE st.id = :saleTypeId
Parameters
Name Type Description
id java.lang.Integer  


Query vesselsUsedWithLandingDate

Get old used vessels and their first landing date

SQL query HQL query
select landingimp0_.VESSEL_FK as col_0_0_, min(landingimp0_.LANDING_DATE_TIME) as col_1_0_ from PUBLIC.LANDING landingimp0_ where not (exists (select 1 from PUBLIC.DENORMALIZED_VESSEL denormaliz1_ where denormaliz1_.VESSEL_FK=landingimp0_.VESSEL_FK)) group by landingimp0_.VESSEL_FK; SELECT la.vessel.code AS vesselCode, min(la.landingDateTime) AS landingDateTime FROM LandingImpl la WHERE NOT EXISTS (select 1 from DenormalizedVesselImpl dv where dv.vessel.code = la.vessel.code) GROUP BY la.vessel.code


Query profilsByPersonId

SQL query HQL query
select distinct userprofil2_.ID as col_0_0_ from PUBLIC.PERSON personimpl0_ left outer join PUBLIC.PERSON2USER_PROFIL profils1_ on personimpl0_.ID=profils1_.PERSON_FK left outer join PUBLIC.USER_PROFIL userprofil2_ on profils1_.USER_PROFIL_FK=userprofil2_.ID where personimpl0_.ID=?; SELECT DISTINCT pp.id FROM PersonImpl p LEFT OUTER JOIN p.profils pp WHERE p.id = :personId
Parameters
Name Type Description
personId java.lang.Integer  


Query gearPhysicalFeatureCountByVesselCode

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.GEAR_PHYSICAL_FEATURES gearphysic0_ where gearphysic0_.VESSEL_FK=?; SELECT COUNT(*) FROM GearPhysicalFeaturesImpl gpf WHERE gpf.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query updateDailyActivityCalendarFixMantis30905

SQL query HQL query
update PUBLIC.DAILY_ACTIVITY_CALENDAR set SYNCHRONIZATION_STATUS=?, UPDATE_DATE=? where OBSERVED_LOCATION_FK=? and SYNCHRONIZATION_STATUS=?; UPDATE DailyActivityCalendarImpl ac SET ac.synchronizationStatus = :synchronizationStatusSync, ac.updateDate = :updateDate WHERE ac.observedLocation.id = :observedLocationId AND ac.synchronizationStatus = :synchronizationStatusReadyToSync
Parameters
Name Type Description
synchronizationStatusReadyToSync java.lang.String  
updateDate java.sql.Timestamp  
observedLocationId java.lang.Integer  
synchronizationStatusSync java.lang.String  


Query taxonNameReferent

SQL query HQL query
select taxonnamei0_.REFERENCE_TAXON_FK as col_0_0_, taxonnamei0_.ID as col_1_0_, taxonnamei0_.IS_REFERENT as col_2_0_, taxonnamei0_.NAME as col_3_0_, taxonnamei0_.IS_TEMPORARY as col_4_0_ from PUBLIC.TAXON_NAME taxonnamei0_ where taxonnamei0_.REFERENCE_TAXON_FK=? and taxonnamei0_.IS_REFERENT=true; SELECT tn.referenceTaxon.id, tn.id, tn.isReferent, tn.name, tn.isTemporary FROM TaxonNameImpl tn WHERE tn.referenceTaxon.id = :referenceTaxonId AND tn.isReferent = true
Parameters
Name Type Description
referenceTaxonId java.lang.Integer  


Query removeLandingLinkToFishingTrip

Remove link between landing (vessel on site) and fishing trip (LANDING.FISHING_TRIP_FK).
Use before delete a fishing trip, when landing must be kept.

SQL query HQL query
update PUBLIC.LANDING set FISHING_TRIP_FK=null, SYNCHRONIZATION_STATUS=? where ID=?; UPDATE LandingImpl la SET la.fishingTrip = null, la.synchronizationStatus = :dirtySynchronizationStatus WHERE la.id = :landingId
Parameters
Name Type Description
dirtySynchronizationStatus java.lang.String  
landingId java.lang.Integer  


Query vesselUseFeatureCountByVesselCode

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.VESSEL_USE_FEATURES vesselusef0_ where vesselusef0_.VESSEL_FK=?; SELECT COUNT(*) FROM VesselUseFeaturesImpl vuf WHERE vuf.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query rootBatchId

SQL query HQL query
select sortingbat0_.ROOT_BATCH_FK as col_0_0_ from PUBLIC.BATCH sortingbat0_ where sortingbat0_.IS_CATCH_BATCH=0 and sortingbat0_.ID=?; SELECT b.rootBatch.id as rootBatchId FROM SortingBatchImpl b WHERE b.id = :sortingBatchId
Parameters
Name Type Description
sortingBatchId java.lang.Integer  


Query producesByLanding

Get produces (as Hibernate entities) by landing

SQL query HQL query
select produces1_.ID as col_0_0_, produces1_.INDIVIDUAL_COUNT as col_1_0_, (select producesor3_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor3_ where producesor3_.PRODUCE_FK=produces1_.ID and producesor3_.PMFM_FK=?) as col_2_0_, produces1_.WEIGHT as col_3_0_, (select producequa4_.NUMERICAL_VALUE from PUBLIC.QUANTIFICATION_MEASUREMENT_P producequa4_ where producequa4_.PRODUCE_FK=produces1_.ID and producequa4_.PMFM_FK=?) as col_4_0_, (select producequa5_.NUMERICAL_VALUE from PUBLIC.QUANTIFICATION_MEASUREMENT_P producequa5_ where producequa5_.PRODUCE_FK=produces1_.ID and producequa5_.PMFM_FK=?) as col_5_0_, coalesce(produces1_.DRESSING_FK, (select producesor6_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor6_ where producesor6_.PRODUCE_FK=produces1_.ID and producesor6_.PMFM_FK=?)) as col_6_0_, coalesce(produces1_.PRESERVATION_FK, (select producesor7_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor7_ where producesor7_.PRODUCE_FK=produces1_.ID and producesor7_.PMFM_FK=?)) as col_7_0_, coalesce(produces1_.SIZE_CATEGORY_FK, (select producesor8_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor8_ where producesor8_.PRODUCE_FK=produces1_.ID and producesor8_.PMFM_FK=?)) as col_8_0_, taxongroup2_.ID as col_9_0_, taxongroup2_.LABEL as col_10_0_, taxongroup2_.NAME as col_11_0_, taxongroup2_.STATUS_FK as col_12_0_, statusimpl9_.CODE as CODE1_159_, statusimpl9_.NAME as NAME2_159_, statusimpl9_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LANDING landingimp0_ inner join PUBLIC.PRODUCE produces1_ on landingimp0_.ID=produces1_.LANDING_FK left outer join PUBLIC.TAXON_GROUP taxongroup2_ on produces1_.TAXON_GROUP_FK=taxongroup2_.ID inner join PUBLIC.STATUS statusimpl9_ on taxongroup2_.STATUS_FK=statusimpl9_.CODE where landingimp0_.FISHING_TRIP_FK=? and ( produces1_.EXPECTED_SALE_FK is null ) order by taxongroup2_.LABEL ASC; SELECT p.id AS produceId, p.individualCount, (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdProducePackaging) AS packaging, p.weight AS weight, (select m.numericalValue from ProduceQuantificationMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdMeasuredWeight) AS measuredWeight, (select m.numericalValue from ProduceQuantificationMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdEstimatedWeight) AS estimatedWeight, coalesce(p.dressing.id, (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdDressing)) AS qvDressing, coalesce(p.preservation.id, (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdPreservation)) AS qvPreservation, coalesce(p.sizeCategory.id, (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdSizeCategory)) AS qvSizeCategory, tg.id as taxonGroupId, tg.label as taxonGroupLabel, tg.name as taxonGroupLabel, tg.status as taxonGroupStatus FROM LandingImpl la inner join la.produces p left join p.taxonGroup tg WHERE la.fishingTrip.id = :fishingTripId AND p.expectedSale is null ORDER BY tg.label ASC
Parameters
Name Type Description
pmfmIdProducePackaging java.lang.Integer  
pmfmIdEstimatedWeight java.lang.Integer  
pmfmIdMeasuredWeight java.lang.Integer  
pmfmIdDressing java.lang.Integer  
pmfmIdPreservation java.lang.Integer  
fishingTripId java.lang.Integer  
pmfmIdSizeCategory java.lang.Integer  


Query minNegativeRemoteIdFromObservedLocation

SQL query HQL query
select min(observedlo0_.REMOTE_ID) as col_0_0_ from PUBLIC.OBSERVED_LOCATION observedlo0_ where observedlo0_.REMOTE_ID<0; SELECT min(remoteId) FROM ObservedLocationImpl WHERE remoteId < 0


Query selectParentLocationByLevel

SQL query HQL query
select locationim0_.ID as ID1_70_, locationim0_.LABEL as LABEL2_70_, locationim0_.NAME as NAME3_70_, locationim0_.UPDATE_DATE as UPDATE_D4_70_, locationim0_.BATHYMETRY as BATHYMET5_70_, locationim0_.UT_FORMAT as UT_FORMA6_70_, locationim0_.DAYLIGHT_SAVING_TIME as DAYLIGHT7_70_, locationim0_.LOCATION_LEVEL_FK as LOCATION8_70_, locationim0_.LOCATION_CLASSIFICATION_FK as LOCATION9_70_, locationim0_.STATUS_FK as STATUS_10_70_, locationim0_1_.UPDATE_DATE as UPDATE_D2_78_, locationim0_2_.UPDATE_DATE as UPDATE_D2_71_, locationim0_3_.UPDATE_DATE as UPDATE_D2_77_, case when locationim0_1_.ID is not null then 1 when locationim0_2_.ID is not null then 2 when locationim0_3_.ID is not null then 3 when locationim0_.ID is not null then 0 end as clazz_ from PUBLIC.LOCATION locationim0_ left outer join PUBLIC.LOCATION_POINT locationim0_1_ on locationim0_.ID=locationim0_1_.ID left outer join PUBLIC.LOCATION_AREA locationim0_2_ on locationim0_.ID=locationim0_2_.ID left outer join PUBLIC.LOCATION_LINE locationim0_3_ on locationim0_.ID=locationim0_3_.ID cross join PUBLIC.LOCATION_HIERARCHY locationhi1_ where locationim0_.LOCATION_LEVEL_FK=? and locationim0_.ID=locationhi1_.PARENT_LOCATION_FK and locationhi1_.CHILD_LOCATION_FK=?; SELECT l FROM LocationImpl l, LocationHierarchyImpl lh WHERE l.locationLevel.id = :locationLevelId AND l.id = lh.locationHierarchyPk.parent.id AND lh.locationHierarchyPk.location.id = :locationId
Parameters
Name Type Description
locationId java.lang.Integer  
locationLevelId java.lang.Integer  


Query countTaxonGroupByLabel

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.TAXON_GROUP taxongroup0_ where taxongroup0_.LABEL=?; SELECT COUNT(*) FROM TaxonGroupImpl t WHERE t.label = :taxonGroupLabel
Parameters
Name Type Description
taxonGroupLabel java.lang.String  


Query findDuplicateObservedLocationsById

Get duplicate observed location corresponding to a natural key

SQL query HQL query
select observedlo0_.ID as col_0_0_ from PUBLIC.OBSERVED_LOCATION observedlo0_ where observedlo0_.ID<>? and observedlo0_.PROGRAM_FK=( select observedlo1_.PROGRAM_FK from PUBLIC.OBSERVED_LOCATION observedlo1_ where observedlo1_.ID=? ) and observedlo0_.LOCATION_FK=( select observedlo2_.LOCATION_FK from PUBLIC.OBSERVED_LOCATION observedlo2_ where observedlo2_.ID=? ) and observedlo0_.START_DATE_TIME=( select observedlo3_.START_DATE_TIME from PUBLIC.OBSERVED_LOCATION observedlo3_ where observedlo3_.ID=? ) and observedlo0_.RECORDER_PERSON_FK=( select observedlo4_.RECORDER_PERSON_FK from PUBLIC.OBSERVED_LOCATION observedlo4_ where observedlo4_.ID=? ) order by observedlo0_.ID; SELECT ol.id AS id FROM ObservedLocationImpl ol WHERE ol.id != :observedLocationId AND ol.program.id = (SELECT program.id FROM ObservedLocationImpl WHERE id = :observedLocationId) AND ol.location.id = (SELECT location.id FROM ObservedLocationImpl WHERE id = :observedLocationId) AND ol.startDateTime = (SELECT startDateTime FROM ObservedLocationImpl WHERE id = :observedLocationId) AND ol.recorderPerson.id = (SELECT recorderPerson.id FROM ObservedLocationImpl WHERE id = :observedLocationId) ORDER BY ol.id
Parameters
Name Type Description
observedLocationId java.lang.Integer  


Query removeDeletedItemHistory

SQL query HQL query
delete from PUBLIC.DELETED_ITEM_HISTORY where RECORDER_PERSON_FK=? and OBJECT_TYPE_FK=? and OBJECT_ID=? and ( ? is null or VESSEL_FK=? ); DELETE FROM DeletedItemHistoryImpl dih WHERE dih.recorderPerson.id = :recorderPersonId AND dih.objectType.code = :objectTypeCode AND dih.objectId = :objectId AND (:vesselCode IS NULL OR dih.vesselCode = :vesselCode)
Parameters
Name Type Description
objectTypeCode java.lang.String  
vesselCode java.lang.String  
recorderPersonId java.lang.Integer  
objectId java.lang.Long  


Query fishingTripById

Get a fishing trip by id

SQL query HQL query
select fishingtri0_.ID as col_0_0_, fishingtri0_.DEPARTURE_DATE_TIME as col_1_0_, fishingtri0_.RETURN_DATE_TIME as col_2_0_, fishingtri0_.DEPARTURE_LOCATION_FK as col_3_0_, locationim11_.LABEL as col_4_0_, locationim11_.NAME as col_5_0_, locationim11_.STATUS_FK as col_6_0_, fishingtri0_.RETURN_LOCATION_FK as col_7_0_, locationim15_.LABEL as col_8_0_, locationim15_.NAME as col_9_0_, locationim15_.STATUS_FK as col_10_0_, fishingtri0_.VESSEL_FK as col_11_0_, fishingtri0_.COMMENTS as col_12_0_, fishingtri0_.SYNCHRONIZATION_STATUS as col_13_0_, (select vesselusem20_.NUMERICAL_VALUE from PUBLIC.VESSEL_USE_FEATURES vesselusef19_ inner join PUBLIC.VESSEL_USE_MEASUREMENT vesselusem20_ on vesselusef19_.ID=vesselusem20_.VESSEL_USE_FEATURES_FK where vesselusef19_.FISHING_TRIP_FK=? and vesselusem20_.PMFM_FK=?) as col_14_0_, (select vesselusem21_.NUMERICAL_VALUE from PUBLIC.VESSEL_USE_MEASUREMENT vesselusem21_, PUBLIC.VESSEL_USE_FEATURES vesselusef22_ where vesselusem21_.VESSEL_USE_FEATURES_FK=vesselusef22_.ID and vesselusem21_.VESSEL_USE_FEATURES_FK=vesselusef2_.ID and vesselusem21_.PMFM_FK=?) as col_15_0_, fishingope1_.HAS_CATCH as col_16_0_, locationim5_.ID as col_17_0_, locationim5_.LABEL as col_18_0_, locationim5_.NAME as col_19_0_, statusimpl6_.CODE as col_20_0_, distanceto7_.ID as col_21_0_, distanceto7_.NAME as col_22_0_, statusimpl8_.CODE as col_23_0_, metierimpl9_.ID as col_24_0_, metierimpl9_.LABEL as col_25_0_, metierimpl9_.NAME as col_26_0_, statusimpl10_.CODE as col_27_0_, statusimpl14_.CODE as CODE1_159_0_, statusimpl18_.CODE as CODE1_159_1_, statusimpl6_.CODE as CODE1_159_2_, statusimpl8_.CODE as CODE1_159_3_, statusimpl10_.CODE as CODE1_159_4_, statusimpl14_.NAME as NAME2_159_0_, statusimpl14_.UPDATE_DATE as UPDATE_D3_159_0_, statusimpl18_.NAME as NAME2_159_1_, statusimpl18_.UPDATE_DATE as UPDATE_D3_159_1_, statusimpl6_.NAME as NAME2_159_2_, statusimpl6_.UPDATE_DATE as UPDATE_D3_159_2_, statusimpl8_.NAME as NAME2_159_3_, statusimpl8_.UPDATE_DATE as UPDATE_D3_159_3_, statusimpl10_.NAME as NAME2_159_4_, statusimpl10_.UPDATE_DATE as UPDATE_D3_159_4_ from PUBLIC.FISHING_TRIP fishingtri0_ left outer join PUBLIC.OPERATION fishingope1_ on fishingtri0_.ID=fishingope1_.FISHING_TRIP_FK and fishingope1_.IS_FISHING_OPERATION=1 left outer join PUBLIC.VESSEL_USE_FEATURES vesselusef2_ on fishingope1_.ID=vesselusef2_.OPERATION_FK left outer join PUBLIC.GEAR_USE_FEATURES gearusefea3_ on fishingope1_.ID=gearusefea3_.OPERATION_FK left outer join PUBLIC.FISHING_AREA fishingare4_ on gearusefea3_.ID=fishingare4_.GEAR_USE_FEATURES_FK left outer join PUBLIC.LOCATION locationim5_ on fishingare4_.LOCATION_FK=locationim5_.ID left outer join PUBLIC.STATUS statusimpl6_ on locationim5_.STATUS_FK=statusimpl6_.CODE left outer join PUBLIC.DISTANCE_TO_COAST_GRADIENT distanceto7_ on fishingare4_.DISTANCE_TO_COAST_GRADIENT_FK=distanceto7_.ID left outer join PUBLIC.STATUS statusimpl8_ on distanceto7_.STATUS_FK=statusimpl8_.CODE left outer join PUBLIC.METIER metierimpl9_ on gearusefea3_.METIER_FK=metierimpl9_.ID left outer join PUBLIC.STATUS statusimpl10_ on metierimpl9_.STATUS_FK=statusimpl10_.CODE, PUBLIC.LOCATION locationim11_ inner join PUBLIC.STATUS statusimpl14_ on locationim11_.STATUS_FK=statusimpl14_.CODE, PUBLIC.LOCATION locationim15_ inner join PUBLIC.STATUS statusimpl18_ on locationim15_.STATUS_FK=statusimpl18_.CODE where fishingtri0_.DEPARTURE_LOCATION_FK=locationim11_.ID and fishingtri0_.RETURN_LOCATION_FK=locationim15_.ID and fishingtri0_.ID=? and ( fishingope1_.ID is null or fishingope1_.START_DATE_TIME=fishingtri0_.DEPARTURE_DATE_TIME and fishingope1_.END_DATE_TIME=fishingtri0_.RETURN_DATE_TIME ) order by fishingope1_.IS_MAIN_OPERATION DESC, fishingope1_.RANK_ORDER_ON_PERIOD ASC; SELECT ft.id AS id, ft.departureDateTime AS startDateTime, ft.returnDateTime AS endDateTime, ft.departureLocation.id AS departureLocationId, ft.departureLocation.label AS departureLocationLabel, ft.departureLocation.name AS departureLocationName, ft.departureLocation.status AS departureLocationStatus, ft.returnLocation.id AS returnLocationId, ft.returnLocation.label AS returnLocationLabel, ft.returnLocation.name AS returnLocationName, ft.returnLocation.status AS returnLocationStatus, ft.vessel.code AS vesselCode, ft.comments AS comments, ft.synchronizationStatus AS synchronizationStatus, (select vum.numericalValue from VesselUseFeaturesImpl vuf inner join vuf.vesselUseMeasurements vum where vuf.fishingTrip.id = :fishingTripId and vum.pmfm.id=:pmfmIdCrewSize) AS crewSize, (select vum.numericalValue from VesselUseMeasurementImpl vum where vum.vesselUseFeatures=vuf and vum.pmfm.id=:pmfmIdVesselFishingDuration) AS vesselFishingDuration, o.hasCatch AS hasCatch, fal.id AS fishingAreaId, fal.label AS fishingAreaLabel, fal.name AS fishingAreaName, fal_status AS fishingAreaStatus, dg.id AS distanceGradientId, dg.name AS distanceGradientName, dg_status AS distanceGradientStatus, m.id AS metierId, m.label AS metierLabel, m.name AS metierName, m_status AS metierStatus FROM FishingTripImpl ft left join ft.fishingOperations o left join o.vesselUseFeatures vuf left join o.gearUseFeatures guf left join guf.fishingAreas fa left join fa.location fal left join fal.status fal_status left join fa.distanceToCoastGradient dg left join dg.status dg_status left join guf.metier m left join m.status m_status WHERE ft.id = :fishingTripId and (o.id is null OR (o.startDateTime = ft.departureDateTime AND o.endDateTime = ft.returnDateTime)) ORDER BY o.isMainOperation DESC, o.rankOrderOnPeriod ASC
Parameters
Name Type Description
pmfmIdCrewSize java.lang.Integer  
fishingTripId java.lang.Integer  
pmfmIdVesselFishingDuration java.lang.Integer  


Query taxonGroupById

SQL query HQL query
select taxongroup0_.ID as col_0_0_, taxongroup0_.LABEL as col_1_0_, taxongroup0_.NAME as col_2_0_, taxongroup0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.TAXON_GROUP taxongroup0_ inner join PUBLIC.STATUS statusimpl1_ on taxongroup0_.STATUS_FK=statusimpl1_.CODE where taxongroup0_.ID=?; SELECT t.id AS id, t.label AS label, t.name AS name, t.status AS status FROM TaxonGroupImpl t WHERE t.id = :taxonGroupId
Parameters
Name Type Description
taxonGroupId java.lang.Integer  


Query updateOperationsTemporaryVessel

Replace temporary vessel in operations

SQL query HQL query
update PUBLIC.OPERATION set VESSEL_FK=? where IS_FISHING_OPERATION=1 and VESSEL_FK=?; UPDATE FishingOperationImpl o SET o.vessel.code = :validVesselCode WHERE o.vessel.code = :tempVesselCode
Parameters
Name Type Description
tempVesselCode java.lang.String  
validVesselCode java.lang.String  


Query updateGearUseFeaturesTemporaryMetier

SQL query HQL query
update PUBLIC.GEAR_USE_FEATURES set METIER_FK=? where METIER_FK=?; UPDATE GearUseFeaturesImpl guf SET guf.metier.id = :targetId WHERE guf.metier.id = :sourceId
Parameters
Name Type Description
sourceId java.lang.Integer  
targetId java.lang.Integer  


Query depthGradientsByIds

SQL query HQL query
select depthgradi0_.ID as col_0_0_, depthgradi0_.NAME as col_1_0_, depthgradi0_.STATUS_FK as col_2_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.DEPTH_GRADIENT depthgradi0_ inner join PUBLIC.STATUS statusimpl1_ on depthgradi0_.STATUS_FK=statusimpl1_.CODE where ( depthgradi0_.STATUS_FK in ( ? , ? ) ) and ( depthgradi0_.ID in ( ? ) ) order by depthgradi0_.RANK_ORDER ASC; SELECT g.id AS id, g.name AS name, g.status AS status FROM DepthGradientImpl g WHERE g.status.code IN (:statusValidCode, :statusTemporaryCode) AND g.id IN (:ids) ORDER BY g.rankOrder ASC
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
ids java.lang.String  


Query allGears

SQL query HQL query
select gearimpl0_.ID as col_0_0_, gearimpl0_.LABEL as col_1_0_, gearimpl0_.NAME as col_2_0_, gearimpl0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.GEAR gearimpl0_ inner join PUBLIC.STATUS statusimpl1_ on gearimpl0_.STATUS_FK=statusimpl1_.CODE where ( gearimpl0_.STATUS_FK in ( ? , ? ) ) and gearimpl0_.GEAR_CLASSIFICATION_FK=?; SELECT g.id AS id, g.label AS label, g.name AS name, g.status AS status FROM GearImpl g WHERE g.status.code IN (:statusValidCode, :statusTemporaryCode) and g.gearClassification.id=:gearClassificationId
Parameters
Name Type Description
statusValidCode java.lang.String  
gearClassificationId java.lang.Integer  
statusTemporaryCode java.lang.String  


Query departmentIdByPersonId

Get a department of a person

SQL query HQL query
select personimpl0_.DEPARTMENT_FK as col_0_0_ from PUBLIC.PERSON personimpl0_ where personimpl0_.ID=?; SELECT p.department.id FROM PersonImpl p WHERE p.id = :personId
Parameters
Name Type Description
personId java.lang.Integer  


Query cleanableObservedLocationIds

Return observed locations :
- synchronized (SYNC)
- no access right for ANY user in local DB
* empty observed location (no landings), and user has NO right from PersonSessionItem
* observedLocation with landing, and user has NO rights on one [vessel,program] during the observation period

SQL query HQL query
select distinct observedlo0_.ID as col_0_0_ from PUBLIC.OBSERVED_LOCATION observedlo0_ left outer join PUBLIC.LANDING landings1_ on observedlo0_.ID=landings1_.OBSERVED_LOCATION_FK where ( landings1_.ID is null ) and observedlo0_.SYNCHRONIZATION_STATUS=? and not (exists (select personsess2_.ID from PUBLIC.PERSON_SESSION_ITEM personsess2_ where personsess2_.OBJECT_ID=observedlo0_.REMOTE_ID and personsess2_.PROGRAM_FK=observedlo0_.PROGRAM_FK and (personsess2_.OBJECT_TYPE_FK in (?)))) or ( landings1_.ID is not null ) and observedlo0_.SYNCHRONIZATION_STATUS=? and not (exists (select personsess3_.ID from PUBLIC.PERSON_SESSION_VESSEL personsess3_ where personsess3_.VESSEL_FK=landings1_.VESSEL_FK and personsess3_.PROGRAM_FK=observedlo0_.PROGRAM_FK and (personsess3_.OBJECT_TYPE_FK in (?)) and observedlo0_.START_DATE_TIME<=personsess3_.END_DATE_TIME and observedlo0_.END_DATE_TIME>=personsess3_.START_DATE_TIME)); SELECT DISTINCT ol.id FROM ObservedLocationImpl ol LEFT OUTER JOIN ol.landings l WHERE ( l.id is null AND ol.synchronizationStatus = :syncSynchronizationStatus AND NOT EXISTS ( FROM PersonSessionItemImpl psi WHERE psi.objectId = ol.remoteId and psi.program.code = ol.program.code and psi.objectType.code in (:objectTypes) ) ) OR (l.id is not null AND ol.synchronizationStatus = :syncSynchronizationStatus AND NOT EXISTS ( FROM PersonSessionVesselImpl psv WHERE psv.vessel.code = l.vessel.code and psv.program.code = ol.program.code and psv.objectType.code in (:objectTypes) AND NOT(ol.startDateTime > psv.endDateTime OR ol.endDateTime < psv.startDateTime) ) )
Parameters
Name Type Description
syncSynchronizationStatus java.lang.String  
objectTypes java.lang.String  


Query locationsByLevelAndParent

Get a location by a level and a parent

SQL query HQL query
select locationim0_.ID as col_0_0_, locationim0_.LABEL as col_1_0_, locationim0_.NAME as col_2_0_, locationim0_.STATUS_FK as col_3_0_, statusimpl2_.CODE as CODE1_159_, statusimpl2_.NAME as NAME2_159_, statusimpl2_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LOCATION locationim0_ inner join PUBLIC.STATUS statusimpl2_ on locationim0_.STATUS_FK=statusimpl2_.CODE cross join PUBLIC.LOCATION_HIERARCHY locationhi1_ where ( locationim0_.STATUS_FK in ( ? , ? ) ) and locationim0_.LOCATION_LEVEL_FK=? and locationim0_.ID=locationhi1_.CHILD_LOCATION_FK and locationhi1_.PARENT_LOCATION_FK=?; SELECT l.id as locationId, l.label as locationLabel, l.name as locationName, l.status as status FROM LocationImpl l, LocationHierarchyImpl lh WHERE l.status.code IN (:statusValidCode, :statusTemporaryCode) AND l.locationLevel.id = :locationLevelId AND l.id = lh.locationHierarchyPk.location.id AND lh.locationHierarchyPk.parent.id = :parentLocationId
Parameters
Name Type Description
statusValidCode java.lang.String  
parentLocationId java.lang.Integer  
statusTemporaryCode java.lang.String  
locationLevelId java.lang.Integer  


Query landingCountByVesselCode

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.LANDING landingimp0_ where landingimp0_.VESSEL_FK=?; SELECT COUNT(*) FROM LandingImpl la WHERE la.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query findMetiersByGearId

SQL query HQL query
select distinct metierimpl0_.ID as col_0_0_, metierimpl0_.LABEL as col_1_0_, metierimpl0_.NAME as col_2_0_, metierimpl0_.STATUS_FK as col_3_0_, metierimpl0_.GEAR_FK as col_4_0_, metierimpl0_.TAXON_GROUP_FK as col_5_0_, statusimpl3_.CODE as CODE1_159_, statusimpl3_.NAME as NAME2_159_, statusimpl3_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.METIER metierimpl0_ left outer join PUBLIC.GEAR gearimpl1_ on metierimpl0_.GEAR_FK=gearimpl1_.ID left outer join PUBLIC.GEAR childgears2_ on gearimpl1_.ID=childgears2_.PARENT_GEAR_FK inner join PUBLIC.STATUS statusimpl3_ on metierimpl0_.STATUS_FK=statusimpl3_.CODE where ( metierimpl0_.STATUS_FK in ( ? , ? ) ) and ( metierimpl0_.GEAR_FK=? or childgears2_.ID=? ) order by metierimpl0_.LABEL; SELECT DISTINCT m.id, m.label, m.name, m.status, m.gear.id, m.taxonGroup.id FROM MetierImpl m left join m.gear.childGears child WHERE m.status.code IN (:statusValidCode, :statusTemporaryCode) AND (m.gear.id=:gearId OR child.id=:gearId) ORDER BY m.label
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
gearId java.lang.String  


Query unusedFishingOperationGroups

Get operation groups, unused (should be deleted) by fishing trip

SQL query HQL query
select operations1_.ID as ID1_96_, operations1_.NAME as NAME3_96_, operations1_.RANK_ORDER_ON_PERIOD as RANK_ORD4_96_, operations1_.START_DATE_TIME as START_DA5_96_, operations1_.END_DATE_TIME as END_DATE6_96_, operations1_.IS_MAIN_OPERATION as IS_MAIN_7_96_, operations1_.COMMENTS as COMMENTS8_96_, operations1_.CONTROL_DATE as CONTROL_9_96_, operations1_.VALIDATION_DATE as VALIDAT10_96_, operations1_.QUALIFICATION_DATE as QUALIFI11_96_, operations1_.QUALIFICATION_COMMENTS as QUALIFI12_96_, operations1_.REMOTE_ID as REMOTE_13_96_, operations1_.QUALITY_FLAG_FK as QUALITY14_96_, operations1_.VESSEL_FK as VESSEL_15_96_, operations1_.FISHING_TRIP_FK as FISHING16_96_, operations1_.GEAR_PHYSICAL_FEATURES_FK as GEAR_PH17_96_, operations1_.FISHING_START_DATE_TIME as FISHING18_96_, operations1_.FISHING_END_DATE_TIME as FISHING19_96_, operations1_.HAS_CATCH as HAS_CAT20_96_, operations1_.CATCH_BATCH_FK as CATCH_B21_96_, operations1_.IS_FISHING_OPERATION as IS_FISHI2_96_ from PUBLIC.FISHING_TRIP fishingtri0_ inner join PUBLIC.OPERATION operations1_ on fishingtri0_.ID=operations1_.FISHING_TRIP_FK left outer join PUBLIC.GEAR_USE_FEATURES gearusefea2_ on operations1_.ID=gearusefea2_.OPERATION_FK left outer join PUBLIC.METIER metierimpl3_ on gearusefea2_.METIER_FK=metierimpl3_.ID where fishingtri0_.ID=? and operations1_.START_DATE_TIME<>fishingtri0_.DEPARTURE_DATE_TIME and operations1_.END_DATE_TIME<>fishingtri0_.RETURN_DATE_TIME and not (exists (select operations5_.ID from PUBLIC.FISHING_TRIP fishingtri4_ inner join PUBLIC.OPERATION operations5_ on fishingtri4_.ID=operations5_.FISHING_TRIP_FK left outer join PUBLIC.GEAR_USE_FEATURES gearusefea6_ on operations5_.ID=gearusefea6_.OPERATION_FK left outer join PUBLIC.METIER metierimpl7_ on gearusefea6_.METIER_FK=metierimpl7_.ID where fishingtri4_.ID=? and operations5_.START_DATE_TIME=fishingtri4_.DEPARTURE_DATE_TIME and operations5_.END_DATE_TIME=fishingtri4_.RETURN_DATE_TIME and metierimpl3_.ID=metierimpl7_.ID)); SELECT o FROM FishingTripImpl ft inner join ft.operations o left join o.gearUseFeatures guf left join guf.metier m WHERE ft.id = :fishingTripId AND o.startDateTime != ft.departureDateTime AND o.endDateTime != ft.returnDateTime AND NOT EXISTS ( SELECT o2.id FROM FishingTripImpl ft2 inner join ft2.operations o2 left join o2.gearUseFeatures guf2 left join guf2.metier m2 WHERE ft2.id = :fishingTripId AND o2.startDateTime = ft2.departureDateTime AND o2.endDateTime = ft2.returnDateTime AND m.id = m2.id )
Parameters
Name Type Description
fishingTripId java.lang.Integer  


Query resetCrypPasswordByLogin

SQL query HQL query
update PUBLIC.PERSON set CRYPT_PASSWORD=null where USERNAME=?; UPDATE PersonImpl p set p.cryptPassword = null WHERE p.username = :personLogin
Parameters
Name Type Description
personLogin java.lang.String  


Query oldPersonSessions

SQL query HQL query
select personsess0_.ID as ID1_104_, personsess0_.UPDATE_DATE as UPDATE_D2_104_, personsess0_.PERSON_FK as PERSON_F3_104_ from PUBLIC.PERSON_SESSION personsess0_ where ( ? is null or personsess0_.PERSON_FK<>? ) and personsess0_.UPDATE_DATE FROM PersonSessionImpl ps WHERE (:excludedPersonId IS NULL OR ps.person.id <> :excludedPersonId) AND ps.updateDate < :nbYearOldDate
Parameters
Name Type Description
nbYearOldDate java.util.Date  
excludedPersonId java.lang.Integer  


Query vesselByRegistrationCodes

SQL query HQL query
select distinct vesselimpl0_.CODE as col_0_0_, vesselregi1_.REGISTRATION_CODE as col_1_0_, vesselregi1_.INT_REGISTRATION_CODE as col_2_0_, vesselfeat2_.NAME as col_3_0_, vesselimpl0_.VESSEL_TYPE_FK as col_4_0_, vesseltype3_.NAME as col_5_0_, vesselimpl0_.STATUS_FK as col_6_0_, statusimpl4_.CODE as CODE1_159_, statusimpl4_.NAME as NAME2_159_, statusimpl4_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.VESSEL vesselimpl0_ inner join PUBLIC.VESSEL_REGISTRATION_PERIOD vesselregi1_ on vesselimpl0_.CODE=vesselregi1_.VESSEL_FK left outer join PUBLIC.VESSEL_FEATURES vesselfeat2_ on vesselimpl0_.CODE=vesselfeat2_.VESSEL_FK, PUBLIC.VESSEL_TYPE vesseltype3_ inner join PUBLIC.STATUS statusimpl4_ on vesselimpl0_.STATUS_FK=statusimpl4_.CODE where vesselimpl0_.VESSEL_TYPE_FK=vesseltype3_.ID and ( upper(vesselregi1_.REGISTRATION_CODE) in ( ? ) or upper(vesselregi1_.INT_REGISTRATION_CODE) in ( ? ) ) and ( vesselimpl0_.VESSEL_TYPE_FK in ( ? ) ) and ( vesselimpl0_.STATUS_FK in ( ? , ? ) ) and coalesce(vesselregi1_.END_DATE_TIME, '2999-12-31 00:00:00')>=coalesce(?, sysdate) and vesselregi1_.START_DATE_TIME<=coalesce(?, sysdate); SELECT DISTINCT v.code AS vesselCode, vrp.registrationCode AS nationalRegistrationCode, vrp.internationalRegistrationCode as internationalRegistrationCode, vf.name AS vesselName, v.vesselType.id AS vesselTypeId, v.vesselType.name AS vesselTypeName, v.status AS status FROM VesselImpl v INNER JOIN v.vesselRegistrationPeriods AS vrp LEFT OUTER JOIN v.vesselFeatures AS vf WHERE ( upper(vrp.registrationCode) IN (:registrationCodes) OR upper(vrp.internationalRegistrationCode) IN (:registrationCodes) ) AND v.vesselType.id IN (:vesselTypeIds) AND v.status.code IN (:statusValidCode, :statusTemporaryCode) AND NOT(coalesce(vrp.endDateTime, '2999-12-31 00:00:00') < coalesce(:refDate,sysdate) OR vrp.vesselRegistrationPeriodPk.startDateTime > coalesce(:refDate,sysdate) )
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
name java.lang.String  
registrationCode java.lang.String  
vesselTypeIds java.lang.String  
refDate java.util.Date  


Query parentLocationByLevel

Get the parent location (from a fixed level), from a child location

SQL query HQL query
select locationim0_.ID as col_0_0_, locationim0_.LABEL as col_1_0_, locationim0_.NAME as col_2_0_, locationim0_.STATUS_FK as col_3_0_, statusimpl2_.CODE as CODE1_159_, statusimpl2_.NAME as NAME2_159_, statusimpl2_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LOCATION locationim0_ inner join PUBLIC.STATUS statusimpl2_ on locationim0_.STATUS_FK=statusimpl2_.CODE cross join PUBLIC.LOCATION_HIERARCHY locationhi1_ where locationim0_.LOCATION_LEVEL_FK=? and locationim0_.ID=locationhi1_.PARENT_LOCATION_FK and locationhi1_.CHILD_LOCATION_FK=?; SELECT l.id as locationId, l.label as locationLabel, l.name as locationName, l.status as status FROM LocationImpl l, LocationHierarchyImpl lh WHERE l.locationLevel.id = :locationLevelId AND l.id = lh.locationHierarchyPk.parent.id AND lh.locationHierarchyPk.location.id = :locationId
Parameters
Name Type Description
locationId java.lang.Integer  
locationLevelId java.lang.Integer  


Query metiersByIds

SQL query HQL query
select metierimpl0_.ID as col_0_0_, metierimpl0_.LABEL as col_1_0_, metierimpl0_.NAME as col_2_0_, metierimpl0_.STATUS_FK as col_3_0_, metierimpl0_.GEAR_FK as col_4_0_, metierimpl0_.TAXON_GROUP_FK as col_5_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.METIER metierimpl0_ inner join PUBLIC.STATUS statusimpl1_ on metierimpl0_.STATUS_FK=statusimpl1_.CODE where ( metierimpl0_.STATUS_FK in ( ? , ? ) ) and ( metierimpl0_.ID in ( ? ) ) or metierimpl0_.STATUS_FK=? order by metierimpl0_.LABEL; SELECT m.id, m.label, m.name, m.status, m.gear.id, m.taxonGroup.id FROM MetierImpl m WHERE ( m.status.code IN (:statusValidCode, :statusTemporaryCode) AND m.id IN (:ids) ) OR m.status.code = :statusTemporaryCode ORDER BY m.label
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
ids java.lang.String  


Query updateProducesTemporaryTaxonGroup

SQL query HQL query
update PUBLIC.PRODUCE set TAXON_GROUP_FK=? where TAXON_GROUP_FK=?; UPDATE ProduceImpl p SET p.taxonGroup.id = :targetId WHERE p.taxonGroup.id = :sourceId
Parameters
Name Type Description
sourceId java.lang.Integer  
targetId java.lang.Integer  


Query countTemporaryGearUsedInMetier

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.METIER metierimpl0_ where metierimpl0_.GEAR_FK=?; SELECT count(*) FROM MetierImpl m WHERE m.gear.id = :gearId
Parameters
Name Type Description
gearId java.lang.Integer  


Query allMetiers

SQL query HQL query
select metierimpl0_.ID as col_0_0_, metierimpl0_.LABEL as col_1_0_, metierimpl0_.NAME as col_2_0_, metierimpl0_.STATUS_FK as col_3_0_, metierimpl0_.GEAR_FK as col_4_0_, metierimpl0_.TAXON_GROUP_FK as col_5_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.METIER metierimpl0_ inner join PUBLIC.STATUS statusimpl1_ on metierimpl0_.STATUS_FK=statusimpl1_.CODE where metierimpl0_.STATUS_FK in ( ? , ? ) order by metierimpl0_.LABEL; SELECT m.id, m.label, m.name, m.status, m.gear.id, m.taxonGroup.id FROM MetierImpl m WHERE m.status.code IN (:statusValidCode, :statusTemporaryCode) ORDER BY m.label
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  


Query updateDailyActivityCalendarSynchronizationStatus

Update synchronization status of a list of calendar ids

SQL query HQL query
update PUBLIC.DAILY_ACTIVITY_CALENDAR set SYNCHRONIZATION_STATUS=? where ( ID in ( ? ) ) and SYNCHRONIZATION_STATUS<>?; UPDATE DailyActivityCalendarImpl ac SET ac.synchronizationStatus = :synchronizationStatus WHERE ac.id IN (:calendarIds) AND ac.synchronizationStatus != :synchronizationStatus
Parameters
Name Type Description
calendarIds java.lang.String  
synchronizationStatus java.lang.String  


Query undefinedFishingOperationGroupsByFishingTripId

Get undefined operation groups, by fishing trip

SQL query HQL query
select operations1_.ID as col_0_0_, operations1_.START_DATE_TIME as col_1_0_, operations1_.END_DATE_TIME as col_2_0_, operations1_.FISHING_START_DATE_TIME as col_3_0_, operations1_.FISHING_END_DATE_TIME as col_4_0_, fishingtri0_.DEPARTURE_DATE_TIME as col_5_0_, fishingtri0_.RETURN_DATE_TIME as col_6_0_, metierimpl4_.ID as col_7_0_, metierimpl4_.LABEL as col_8_0_, metierimpl4_.NAME as col_9_0_, metierimpl4_.STATUS_FK as col_10_0_, gearimpl5_.ID as col_11_0_, gearimpl5_.LABEL as col_12_0_, gearimpl5_.NAME as col_13_0_, gearimpl5_.STATUS_FK as col_14_0_, locationim7_.ID as col_15_0_, locationim7_.LABEL as col_16_0_, locationim7_.NAME as col_17_0_, locationim7_.STATUS_FK as col_18_0_, distanceto8_.ID as col_19_0_, distanceto8_.NAME as col_20_0_, distanceto8_.STATUS_FK as col_21_0_, operations1_.HAS_CATCH as col_22_0_, operations1_.COMMENTS as col_23_0_, count(produces2_.ID) as col_24_0_ from PUBLIC.FISHING_TRIP fishingtri0_ inner join PUBLIC.OPERATION operations1_ on fishingtri0_.ID=operations1_.FISHING_TRIP_FK left outer join PUBLIC.PRODUCE produces2_ on operations1_.ID=produces2_.FISHING_OPERATION_FK left outer join PUBLIC.GEAR_USE_FEATURES gearusefea3_ on operations1_.ID=gearusefea3_.OPERATION_FK left outer join PUBLIC.METIER metierimpl4_ on gearusefea3_.METIER_FK=metierimpl4_.ID left outer join PUBLIC.GEAR gearimpl5_ on gearusefea3_.GEAR_FK=gearimpl5_.ID left outer join PUBLIC.FISHING_AREA fishingare6_ on gearusefea3_.ID=fishingare6_.GEAR_USE_FEATURES_FK left outer join PUBLIC.LOCATION locationim7_ on fishingare6_.LOCATION_FK=locationim7_.ID left outer join PUBLIC.DISTANCE_TO_COAST_GRADIENT distanceto8_ on fishingare6_.DISTANCE_TO_COAST_GRADIENT_FK=distanceto8_.ID where fishingtri0_.ID=? and operations1_.START_DATE_TIME=fishingtri0_.DEPARTURE_DATE_TIME and operations1_.END_DATE_TIME=fishingtri0_.RETURN_DATE_TIME group by operations1_.ID , operations1_.START_DATE_TIME , operations1_.END_DATE_TIME , operations1_.FISHING_START_DATE_TIME , operations1_.FISHING_END_DATE_TIME , fishingtri0_.DEPARTURE_DATE_TIME , fishingtri0_.RETURN_DATE_TIME , metierimpl4_.ID , metierimpl4_.LABEL , metierimpl4_.NAME , metierimpl4_.STATUS_FK , gearimpl5_.ID , gearimpl5_.LABEL , gearimpl5_.NAME , gearimpl5_.STATUS_FK , locationim7_.ID , locationim7_.LABEL , locationim7_.NAME , locationim7_.STATUS_FK , distanceto8_.ID , distanceto8_.NAME , distanceto8_.STATUS_FK , operations1_.HAS_CATCH , operations1_.COMMENTS , operations1_.RANK_ORDER_ON_PERIOD order by operations1_.START_DATE_TIME, coalesce(operations1_.RANK_ORDER_ON_PERIOD, -1) ASC; SELECT o.id AS id, o.startDateTime AS startDate, o.endDateTime AS endDate, o.fishingStartDateTime AS fishingStartDate, o.fishingEndDateTime AS fishingEndDate, ft.departureDateTime AS ftStartDate, ft.returnDateTime AS ftEndDate, m.id AS metierId, m.label AS metierLabel, m.name AS metierName, m.status.code AS metierStatusCode, g.id AS gearId, g.label AS gearLabel, g.name AS gearName, g.status.code AS gearStatusCode, fal.id AS fishingAreaLocationId, fal.label AS fishingAreaLocationLabel, fal.name AS fishingAreaLocationname, fal.status.code AS fishingAreaLocationStatusCode, dcg.id AS distanceToCoastId, dcg.name AS distanceToCoastLabel, dcg.status.code AS distanceToCoastStatusCode, o.hasCatch AS hasCatch, o.comments AS comments, count(p) AS produceCount FROM FishingTripImpl ft inner join ft.operations o left join o.produces p left join o.gearUseFeatures guf left join guf.metier m left join guf.gear g left join guf.fishingAreas fa left join fa.location fal left join fa.distanceToCoastGradient dcg WHERE ft.id = :fishingTripId AND o.startDateTime = ft.departureDateTime AND o.endDateTime = ft.returnDateTime GROUP BY o.id, o.startDateTime, o.endDateTime, o.fishingStartDateTime, o.fishingEndDateTime, ft.departureDateTime, ft.returnDateTime, m.id, m.label, m.name, m.status.code, g.id, g.label, g.name, g.status.code, fal.id, fal.label, fal.name, fal.status.code, dcg.id, dcg.name, dcg.status.code, o.hasCatch, o.comments, o.rankOrderOnPeriod ORDER BY o.startDateTime, coalesce(o.rankOrderOnPeriod,-1) ASC
Parameters
Name Type Description
fishingOperationGroupId java.lang.Integer  


Query producesByFishingTrip

Get produces by fishing trip

SQL query HQL query
select produces1_.ID as col_0_0_, produces1_.INDIVIDUAL_COUNT as col_1_0_, (select producesor3_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor3_ where producesor3_.PRODUCE_FK=produces1_.ID and producesor3_.PMFM_FK=?) as col_2_0_, produces1_.WEIGHT as col_3_0_, (select producequa4_.NUMERICAL_VALUE from PUBLIC.QUANTIFICATION_MEASUREMENT_P producequa4_ where producequa4_.PRODUCE_FK=produces1_.ID and producequa4_.PMFM_FK=?) as col_4_0_, (select producequa5_.NUMERICAL_VALUE from PUBLIC.QUANTIFICATION_MEASUREMENT_P producequa5_ where producequa5_.PRODUCE_FK=produces1_.ID and producequa5_.PMFM_FK=?) as col_5_0_, coalesce(produces1_.DRESSING_FK, (select producesor6_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor6_ where producesor6_.PRODUCE_FK=produces1_.ID and producesor6_.PMFM_FK=?)) as col_6_0_, coalesce(produces1_.PRESERVATION_FK, (select producesor7_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor7_ where producesor7_.PRODUCE_FK=produces1_.ID and producesor7_.PMFM_FK=?)) as col_7_0_, coalesce(produces1_.SIZE_CATEGORY_FK, (select producesor8_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor8_ where producesor8_.PRODUCE_FK=produces1_.ID and producesor8_.PMFM_FK=?)) as col_8_0_, taxongroup2_.ID as col_9_0_, taxongroup2_.LABEL as col_10_0_, taxongroup2_.NAME as col_11_0_, taxongroup2_.STATUS_FK as col_12_0_, fishingope0_.ID as col_13_0_, statusimpl9_.CODE as CODE1_159_, statusimpl9_.NAME as NAME2_159_, statusimpl9_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.OPERATION fishingope0_ inner join PUBLIC.PRODUCE produces1_ on fishingope0_.ID=produces1_.FISHING_OPERATION_FK left outer join PUBLIC.TAXON_GROUP taxongroup2_ on produces1_.TAXON_GROUP_FK=taxongroup2_.ID inner join PUBLIC.STATUS statusimpl9_ on taxongroup2_.STATUS_FK=statusimpl9_.CODE where fishingope0_.IS_FISHING_OPERATION=1 and fishingope0_.FISHING_TRIP_FK=? and ( produces1_.EXPECTED_SALE_FK is null ) order by taxongroup2_.LABEL ASC; SELECT p.id AS produceId, p.individualCount, (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdProducePackaging) AS packaging, p.weight AS weight, (select m.numericalValue from ProduceQuantificationMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdMeasuredWeight) AS measuredWeight, (select m.numericalValue from ProduceQuantificationMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdEstimatedWeight) AS estimatedWeight, coalesce(p.dressing.id, (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdDressing)) AS qvDressing, coalesce(p.preservation.id, (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdPreservation)) AS qvPreservation, coalesce(p.sizeCategory.id, (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdSizeCategory)) AS qvSizeCategory, tg.id as taxonGroupId, tg.label as taxonGroupLabel, tg.name as taxonGroupLabel, tg.status as taxonGroupStatus, o.id as operationId FROM FishingOperationImpl o inner join o.produces p left join p.taxonGroup tg WHERE o.fishingTrip.id = :fishingTripId AND p.expectedSale is null ORDER BY tg.label ASC
Parameters
Name Type Description
pmfmIdProducePackaging java.lang.Integer  
pmfmIdEstimatedWeight java.lang.Integer  
pmfmIdMeasuredWeight java.lang.Integer  
pmfmIdDressing java.lang.Integer  
pmfmIdPreservation java.lang.Integer  
fishingTripId java.lang.Integer  
pmfmIdSizeCategory java.lang.Integer  


Query updateFishingTripsSynchronizationStatus

Update synchronization status of a list of fishing trips

SQL query HQL query
update PUBLIC.FISHING_TRIP set SYNCHRONIZATION_STATUS=? where ( ID in ( ? ) ) and SYNCHRONIZATION_STATUS<>?; UPDATE FishingTripImpl ft SET ft.synchronizationStatus = :synchronizationStatus WHERE ft.id IN (:fishingTripIds) AND ft.synchronizationStatus != :synchronizationStatus
Parameters
Name Type Description
synchronizationStatus java.lang.String  
fishingTripIds java.lang.String  


Query denormalizedVesselByRegistrationLocation

Search for vessels, if a registration location as been set (see mantis #)

SQL query HQL query
select distinct denormaliz0_.VESSEL_FK as col_0_0_, denormaliz0_.REGISTRATION_CODE as col_1_0_, denormaliz0_.INT_REGISTRATION_CODE as col_2_0_, denormaliz0_.REGISTRATION_START_DATE as col_3_0_, denormaliz0_.REGISTRATION_END_DATE as col_4_0_, denormaliz0_.NAME as col_5_0_, vesselimpl1_.VESSEL_TYPE_FK as col_6_0_, vesseltype9_.NAME as col_7_0_, denormaliz0_.REGISTRATION_COUNTRY_FK as col_8_0_, locationim10_.LABEL as col_9_0_, locationim10_.NAME as col_10_0_, locationim10_.STATUS_FK as col_11_0_, denormaliz0_.REGISTRATION_LOCATION_FK as col_12_0_, locationim14_.LABEL as col_13_0_, locationim14_.NAME as col_14_0_, locationim14_.STATUS_FK as col_15_0_, locationim4_.ID as col_16_0_, locationim4_.LABEL as col_17_0_, locationim4_.NAME as col_18_0_, statusimpl5_.CODE as col_19_0_, vesselimpl1_.STATUS_FK as col_20_0_, statusimpl13_.CODE as CODE1_159_0_, statusimpl17_.CODE as CODE1_159_1_, statusimpl5_.CODE as CODE1_159_2_, statusimpl19_.CODE as CODE1_159_3_, statusimpl13_.NAME as NAME2_159_0_, statusimpl13_.UPDATE_DATE as UPDATE_D3_159_0_, statusimpl17_.NAME as NAME2_159_1_, statusimpl17_.UPDATE_DATE as UPDATE_D3_159_1_, statusimpl5_.NAME as NAME2_159_2_, statusimpl5_.UPDATE_DATE as UPDATE_D3_159_2_, statusimpl19_.NAME as NAME2_159_3_, statusimpl19_.UPDATE_DATE as UPDATE_D3_159_3_ from PUBLIC.DENORMALIZED_VESSEL denormaliz0_ inner join PUBLIC.VESSEL vesselimpl1_ on denormaliz0_.VESSEL_FK=vesselimpl1_.CODE inner join PUBLIC.VESSEL_REGISTRATION_PERIOD vesselregi2_ on vesselimpl1_.CODE=vesselregi2_.VESSEL_FK inner join PUBLIC.LOCATION locationim3_ on vesselregi2_.REGISTRATION_LOCATION_FK=locationim3_.ID, PUBLIC.VESSEL_TYPE vesseltype9_ inner join PUBLIC.STATUS statusimpl19_ on vesselimpl1_.STATUS_FK=statusimpl19_.CODE left outer join PUBLIC.LOCATION locationim4_ on denormaliz0_.BASE_PORT_LOCATION_FK=locationim4_.ID left outer join PUBLIC.STATUS statusimpl5_ on locationim4_.STATUS_FK=statusimpl5_.CODE, PUBLIC.LOCATION locationim10_ inner join PUBLIC.STATUS statusimpl13_ on locationim10_.STATUS_FK=statusimpl13_.CODE, PUBLIC.LOCATION locationim14_ inner join PUBLIC.STATUS statusimpl17_ on locationim14_.STATUS_FK=statusimpl17_.CODE cross join PUBLIC.LOCATION_HIERARCHY locationhi6_ cross join PUBLIC.LOCATION locationim22_ where vesselimpl1_.VESSEL_TYPE_FK=vesseltype9_.ID and denormaliz0_.REGISTRATION_COUNTRY_FK=locationim10_.ID and denormaliz0_.REGISTRATION_LOCATION_FK=locationim14_.ID and locationhi6_.PARENT_LOCATION_FK=locationim22_.ID and ( ? is null or upper(denormaliz0_.NAME) like ('%'||?||'%') ) and ( ? is null or upper(denormaliz0_.REGISTRATION_CODE) like ('%'||?||'%') or upper(denormaliz0_.INT_REGISTRATION_CODE) like ('%'||?||'%') ) and ( vesselimpl1_.VESSEL_TYPE_FK in ( ? ) ) and ( vesselimpl1_.STATUS_FK in ( ? ) ) and ( ? is null or denormaliz0_.BASE_PORT_LOCATION_FK=? ) and coalesce(vesselregi2_.END_DATE_TIME, ?)>=? and locationhi6_.CHILD_LOCATION_FK=vesselregi2_.REGISTRATION_LOCATION_FK and locationim22_.LOCATION_LEVEL_FK=? and ( ? is null or locationhi6_.PARENT_LOCATION_FK=? ) and ( ? is null or vesselregi2_.REGISTRATION_LOCATION_FK=? ) order by denormaliz0_.VESSEL_FK; SELECT DISTINCT dv.vessel.code AS vesselCode, dv.registrationCode AS nationalRegistrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationStartDate AS registrationStartDate, dv.registrationEndDate AS registrationEndDate, dv.name AS vesselName, dv.vessel.vesselType.id AS vesselTypeId, dv.vessel.vesselType.name AS vesselTypeName, dv.registrationCountry.id AS countryLocationId, dv.registrationCountry.label AS countryLocationLabel, dv.registrationCountry.name AS countryLocationName, dv.registrationCountry.status AS countryLocationStatus, dv.registrationLocation.id AS registrationLocationId, dv.registrationLocation.label AS registrationLocationLabel, dv.registrationLocation.name AS registrationLocationName, dv.registrationLocation.status AS registrationLocationStatus, port.id AS basePortLocationId, port.label AS basePortLocationLabel, port.name AS basePortLocationName, portStatus AS basePortLocationStatus, dv.vessel.status AS status FROM DenormalizedVesselImpl dv INNER JOIN dv.vessel.vesselRegistrationPeriods vrp INNER JOIN vrp.id.registrationLocation l LEFT OUTER JOIN dv.basePortLocation as port LEFT OUTER JOIN port.status as portStatus, LocationHierarchyImpl lh WHERE ( :name is null OR (upper(dv.name) like '%' || :name || '%') ) AND ( :registrationCode is null OR (upper(dv.registrationCode) like '%' || :registrationCode || '%') OR (upper(dv.internationalRegistrationCode) like '%' || :registrationCode || '%') ) AND dv.vessel.vesselType.id IN (:vesselTypeIds) AND dv.vessel.status.code IN (:statusCodes) AND ( :basePortLocationId is null OR dv.basePortLocation.id = :basePortLocationId) AND NOT(coalesce(vrp.endDateTime, :vrpStartDate) < :vrpStartDate) AND lh.id.location.id = vrp.id.registrationLocation.id AND lh.id.parent.locationLevel.id = :countryLocationLevelId AND ( :registrationCountryId is null OR lh.id.parent.id = :registrationCountryId) AND ( :registrationLocationId is null OR vrp.id.registrationLocation.id = :registrationLocationId) ORDER BY dv.vessel.code
Parameters
Name Type Description
statusValidCode java.lang.String  
basePortLocationId java.lang.Integer  
countryLocationLevelId java.lang.Integer  
statusTemporaryCode java.lang.String  
registrationLocationId java.lang.Integer  
vrpStartDate java.util.Date  
name java.lang.String  
registrationCode java.lang.String  
vesselTypeIds java.lang.String  
registrationCountryId java.lang.Integer  


Query denormalizedVesselByCode

SQL query HQL query
select distinct denormaliz0_.VESSEL_FK as col_0_0_, denormaliz0_.REGISTRATION_CODE as col_1_0_, denormaliz0_.INT_REGISTRATION_CODE as col_2_0_, denormaliz0_.REGISTRATION_START_DATE as col_3_0_, denormaliz0_.REGISTRATION_END_DATE as col_4_0_, denormaliz0_.NAME as col_5_0_, vesselimpl3_.VESSEL_TYPE_FK as col_6_0_, vesseltype5_.NAME as col_7_0_, denormaliz0_.REGISTRATION_COUNTRY_FK as col_8_0_, locationim6_.LABEL as col_9_0_, locationim6_.NAME as col_10_0_, locationim6_.STATUS_FK as col_11_0_, denormaliz0_.REGISTRATION_LOCATION_FK as col_12_0_, locationim10_.LABEL as col_13_0_, locationim10_.NAME as col_14_0_, locationim10_.STATUS_FK as col_15_0_, locationim1_.ID as col_16_0_, locationim1_.LABEL as col_17_0_, locationim1_.NAME as col_18_0_, statusimpl2_.CODE as col_19_0_, vesselimpl3_.STATUS_FK as col_20_0_, statusimpl9_.CODE as CODE1_159_0_, statusimpl13_.CODE as CODE1_159_1_, statusimpl2_.CODE as CODE1_159_2_, statusimpl15_.CODE as CODE1_159_3_, statusimpl9_.NAME as NAME2_159_0_, statusimpl9_.UPDATE_DATE as UPDATE_D3_159_0_, statusimpl13_.NAME as NAME2_159_1_, statusimpl13_.UPDATE_DATE as UPDATE_D3_159_1_, statusimpl2_.NAME as NAME2_159_2_, statusimpl2_.UPDATE_DATE as UPDATE_D3_159_2_, statusimpl15_.NAME as NAME2_159_3_, statusimpl15_.UPDATE_DATE as UPDATE_D3_159_3_ from PUBLIC.DENORMALIZED_VESSEL denormaliz0_ left outer join PUBLIC.LOCATION locationim1_ on denormaliz0_.BASE_PORT_LOCATION_FK=locationim1_.ID left outer join PUBLIC.STATUS statusimpl2_ on locationim1_.STATUS_FK=statusimpl2_.CODE, PUBLIC.VESSEL vesselimpl3_, PUBLIC.VESSEL_TYPE vesseltype5_ inner join PUBLIC.STATUS statusimpl15_ on vesselimpl3_.STATUS_FK=statusimpl15_.CODE, PUBLIC.LOCATION locationim6_ inner join PUBLIC.STATUS statusimpl9_ on locationim6_.STATUS_FK=statusimpl9_.CODE, PUBLIC.LOCATION locationim10_ inner join PUBLIC.STATUS statusimpl13_ on locationim10_.STATUS_FK=statusimpl13_.CODE where denormaliz0_.VESSEL_FK=vesselimpl3_.CODE and vesselimpl3_.VESSEL_TYPE_FK=vesseltype5_.ID and denormaliz0_.REGISTRATION_COUNTRY_FK=locationim6_.ID and denormaliz0_.REGISTRATION_LOCATION_FK=locationim10_.ID and denormaliz0_.VESSEL_FK=?; SELECT DISTINCT dv.vessel.code AS vesselCode, dv.registrationCode AS nationalRegistrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationStartDate AS registrationStartDate, dv.registrationEndDate AS registrationEndDate, dv.name AS vesselName, dv.vessel.vesselType.id AS vesselTypeId, dv.vessel.vesselType.name AS vesselTypeName, dv.registrationCountry.id AS countryLocationId, dv.registrationCountry.label AS countryLocationLabel, dv.registrationCountry.name AS countryLocationName, dv.registrationCountry.status AS countryLocationStatus, dv.registrationLocation.id AS registrationLocationId, dv.registrationLocation.label AS registrationLocationLabel, dv.registrationLocation.name AS registrationLocationName, dv.registrationLocation.status AS registrationLocationStatus, port.id AS basePortLocationId, port.label AS basePortLocationLabel, port.name AS basePortLocationName, portStatus AS basePortLocationStatus, dv.vessel.status AS status FROM DenormalizedVesselImpl dv left join dv.basePortLocation as port left join port.status as portStatus WHERE dv.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query allFishingMetiers

SQL query HQL query
select metierimpl0_.ID as col_0_0_, metierimpl0_.LABEL as col_1_0_, metierimpl0_.NAME as col_2_0_, metierimpl0_.STATUS_FK as col_3_0_, metierimpl0_.GEAR_FK as col_4_0_, metierimpl0_.TAXON_GROUP_FK as col_5_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.METIER metierimpl0_ inner join PUBLIC.STATUS statusimpl1_ on metierimpl0_.STATUS_FK=statusimpl1_.CODE where ( metierimpl0_.STATUS_FK in ( ? , ? ) ) and ( metierimpl0_.TAXON_GROUP_FK is not null ) and ( metierimpl0_.GEAR_FK is not null ) order by metierimpl0_.LABEL; SELECT m.id, m.label, m.name, m.status, m.gear.id, m.taxonGroup.id FROM MetierImpl m WHERE m.status.code IN (:statusValidCode, :statusTemporaryCode) AND m.taxonGroup.id is not null AND m.gear.id is not null ORDER BY m.label
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  


Query countVesselByCode

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.VESSEL vesselimpl0_ where vesselimpl0_.CODE=?; SELECT COUNT(*) FROM VesselImpl v WHERE v.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query updateLandingsFromObservedLocation

Update landing linked with a observed location (copy some attributes)

SQL query HQL query
update PUBLIC.LANDING set LANDING_DATE_TIME=?, SYNCHRONIZATION_STATUS=? where ( FISHING_TRIP_FK is null ) and OBSERVED_LOCATION_FK=? and LANDING_DATE_TIME<>?; UPDATE LandingImpl la SET la.landingDateTime = :landingDateTime, la.synchronizationStatus = :synchronizationStatus WHERE la.fishingTrip is null AND la.observedLocation.id = :observedLocationId AND la.landingDateTime != :landingDateTime
Parameters
Name Type Description
observedLocationId java.lang.Integer  
landingDateTime java.util.Date  
synchronizationStatus java.lang.String  


Query hasCryptPasswordByLogin

SQL query HQL query
select count(personimpl0_.ID) as col_0_0_ from PUBLIC.PERSON personimpl0_ where ( personimpl0_.STATUS_FK in ( ? , ? ) ) and ( personimpl0_.USERNAME=? or personimpl0_.USERNAME_EXTRANET=? ) and ( personimpl0_.CRYPT_PASSWORD is not null ); SELECT COUNT(p) FROM PersonImpl p WHERE p.status.code IN (:statusValidCode, :statusTemporaryCode) AND (p.username = :login OR p.usernameExtranet = :login) AND p.cryptPassword is not null
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
login java.lang.String  


Query updateUndefinedOperationDates

Update undefined operation dates

SQL query HQL query
update PUBLIC.OPERATION set START_DATE_TIME=?, FISHING_START_DATE_TIME=?, END_DATE_TIME=?, FISHING_END_DATE_TIME=? where IS_FISHING_OPERATION=1 and ( ID in ( select fishingope1_.ID from PUBLIC.OPERATION fishingope1_ inner join PUBLIC.FISHING_TRIP fishingtri2_ on fishingope1_.FISHING_TRIP_FK=fishingtri2_.ID where fishingope1_.IS_FISHING_OPERATION=1 and fishingtri2_.ID=? and fishingope1_.START_DATE_TIME=fishingtri2_.DEPARTURE_DATE_TIME and fishingope1_.END_DATE_TIME=fishingtri2_.RETURN_DATE_TIME and ( fishingope1_.START_DATE_TIME<>? or fishingope1_.FISHING_START_DATE_TIME<>? or fishingope1_.END_DATE_TIME<>? or fishingope1_.FISHING_END_DATE_TIME<>? ) ) ); UPDATE FishingOperationImpl o SET o.startDateTime = :startDateTime, o.fishingStartDateTime = :startDateTime, o.endDateTime = :endDateTime, o.fishingEndDateTime = :endDateTime WHERE o.id IN ( SELECT o2.id FROM FishingOperationImpl o2 INNER JOIN o2.fishingTrip ft WHERE ft.id = :fishingTripId AND o2.startDateTime = ft.departureDateTime AND o2.endDateTime = ft.returnDateTime AND ( o2.startDateTime != :startDateTime OR o2.fishingStartDateTime != :startDateTime OR o2.endDateTime != :endDateTime OR o2.fishingEndDateTime != :endDateTime ) )
Parameters
Name Type Description
startDateTime java.util.Date  
endDateTime java.util.Date  
fishingTripId java.lang.Integer  


Query insertTempDenormalizedVessel

SQL query HQL query
insert into PUBLIC.TEMP_DENORMALIZED_VESSEL ( CODE, PROGRAM_FK, VRP_START_DATE_TIME, VOP_START_DATE_TIME, VF_START_DATE_TIME ) select vesselimpl0_.CODE as col_0_0_, max(vesselimpl0_.PROGRAM_FK) as col_1_0_, max(vesselregi1_.START_DATE_TIME) as col_2_0_, max(vesselowne3_.START_DATE_TIME) as col_3_0_, max(vesselfeat2_.START_DATE_TIME) as col_4_0_ from PUBLIC.VESSEL vesselimpl0_ inner join PUBLIC.VESSEL_REGISTRATION_PERIOD vesselregi1_ on vesselimpl0_.CODE=vesselregi1_.VESSEL_FK left outer join PUBLIC.VESSEL_FEATURES vesselfeat2_ on vesselimpl0_.CODE=vesselfeat2_.VESSEL_FK left outer join PUBLIC.VESSEL_OWNER_PERIOD vesselowne3_ on vesselimpl0_.CODE=vesselowne3_.VESSEL_FK where 1=1 and ( vesselimpl0_.PROGRAM_FK in ( ? ) ) and ( ? is null or vesselimpl0_.CODE in ( ? ) ) and ( vesselimpl0_.STATUS_FK in ( ? , ? ) ) and coalesce(vesselregi1_.END_DATE_TIME, ?)>=? and coalesce(vesselowne3_.END_DATE_TIME, ?)>=? and coalesce(vesselfeat2_.END_DATE_TIME, ?)>=? group by vesselimpl0_.CODE; INSERT INTO TempDenormalizedVesselImpl (code, program, vrpStartDateTime, vopStartDateTime, vfStartDateTime) SELECT v.code, max(v.program), max(vrp.id.startDateTime), max(vop.id.startDateTime), max(vf.startDateTime) FROM VesselImpl v INNER JOIN v.vesselRegistrationPeriods AS vrp LEFT OUTER JOIN v.vesselFeatures AS vf LEFT OUTER JOIN v.vesselOwnerPeriods AS vop WHERE 1=1 AND v.program.code IN (:programCodes) AND (:vesselCodes IS NULL OR v.code IN (:vesselCodes)) AND v.status.code IN (:statusValidCode, :statusTemporaryCode) AND NOT(coalesce(vrp.endDateTime, :startDate) < :startDate) AND NOT(coalesce(vop.endDateTime, :startDate) < :startDate) AND NOT(coalesce(vf.endDateTime, :startDate) < :startDate) GROUP BY v.code
Parameters
Name Type Description
statusValidCode java.lang.String  
programCodes java.lang.String  
statusTemporaryCode java.lang.String  
vesselCodes java.lang.String  
startDate java.util.Date  


Query gearsByMetierId

SQL query HQL query
select gearimpl0_.ID as col_0_0_, gearimpl0_.LABEL as col_1_0_, gearimpl0_.NAME as col_2_0_, gearimpl0_.STATUS_FK as col_3_0_, statusimpl3_.CODE as CODE1_159_, statusimpl3_.NAME as NAME2_159_, statusimpl3_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.GEAR gearimpl0_ left outer join PUBLIC.GEAR gearimpl1_ on gearimpl0_.PARENT_GEAR_FK=gearimpl1_.ID inner join PUBLIC.STATUS statusimpl3_ on gearimpl0_.STATUS_FK=statusimpl3_.CODE cross join PUBLIC.METIER metierimpl2_ where ( gearimpl0_.STATUS_FK in ( ? , ? ) ) and ( ? is null or gearimpl0_.GEAR_CLASSIFICATION_FK=? ) and ( metierimpl2_.GEAR_FK=gearimpl0_.ID or metierimpl2_.GEAR_FK=gearimpl1_.ID ) and metierimpl2_.ID=?; SELECT g.id AS id, g.label AS label, g.name AS name, g.status AS status FROM GearImpl g left join g.parentGear parent, MetierImpl m WHERE g.status.code IN (:statusValidCode, :statusTemporaryCode) AND (:gearClassificationId is null OR g.gearClassification.id=:gearClassificationId) AND (m.gear=g OR m.gear=parent) AND m.id=:metierId
Parameters
Name Type Description
statusValidCode java.lang.String  
gearClassificationId java.lang.Integer  
statusTemporaryCode java.lang.String  
metierId java.lang.Integer  


Query allBatch

SQL query HQL query
select sortingbat0_.ID as ID1_12_0_, sortingmea1_.ID as ID1_151_1_, quantifica2_.ID as ID1_130_2_, sortingbat0_.RANK_ORDER as RANK_ORD3_12_0_, sortingbat0_.SUBGROUP_COUNT as SUBGROUP4_12_0_, sortingbat0_.INDIVIDUAL_COUNT as INDIVIDU5_12_0_, sortingbat0_.CHILD_BATCHS_REPLICATION as CHILD_BA6_12_0_, sortingbat0_.EXHAUSTIVE_INVENTORY as EXHAUSTI7_12_0_, sortingbat0_.COMMENTS as COMMENTS8_12_0_, sortingbat0_.LABEL as LABEL9_12_0_, sortingbat0_.REMOTE_ID as REMOTE_10_12_0_, sortingbat0_.LOCATION_FK as LOCATIO11_12_0_, sortingbat0_.PARENT_BATCH_FK as PARENT_12_12_0_, sortingbat0_.QUALITY_FLAG_FK as QUALITY13_12_0_, sortingbat0_.SAMPLING_RATIO as SAMPLIN20_12_0_, sortingbat0_.SAMPLING_RATIO_TEXT as SAMPLIN21_12_0_, sortingbat0_.REFERENCE_TAXON_FK as REFEREN22_12_0_, sortingbat0_.ROOT_BATCH_FK as ROOT_BA23_12_0_, sortingbat0_.TAXON_GROUP_FK as TAXON_G24_12_0_, sortingmea1_.NUMERICAL_VALUE as NUMERICA2_151_1_, sortingmea1_.ALPHANUMERICAL_VALUE as ALPHANUM3_151_1_, sortingmea1_.DIGIT_COUNT as DIGIT_CO4_151_1_, sortingmea1_.PRECISION_VALUE as PRECISIO5_151_1_, sortingmea1_.CONTROL_DATE as CONTROL_6_151_1_, sortingmea1_.VALIDATION_DATE as VALIDATI7_151_1_, sortingmea1_.QUALIFICATION_DATE as QUALIFIC8_151_1_, sortingmea1_.QUALIFICATION_COMMENTS as QUALIFIC9_151_1_, sortingmea1_.REMOTE_ID as REMOTE_10_151_1_, sortingmea1_.AGGREGATION_LEVEL_FK as AGGREGA11_151_1_, sortingmea1_.QUALITY_FLAG_FK as QUALITY12_151_1_, sortingmea1_.PRECISION_TYPE_FK as PRECISI13_151_1_, sortingmea1_.ANALYSIS_INSTRUMENT_FK as ANALYSI14_151_1_, sortingmea1_.NUMERICAL_PRECISION_FK as NUMERIC15_151_1_, sortingmea1_.DEPARTMENT_FK as DEPARTM16_151_1_, sortingmea1_.PMFM_FK as PMFM_FK17_151_1_, sortingmea1_.QUALITATIVE_VALUE_FK as QUALITA18_151_1_, sortingmea1_.RANK_ORDER as RANK_OR19_151_1_, sortingmea1_.SORTING_BATCH_FK as SORTING20_151_1_, sortingmea1_.SORTING_BATCH_FK as SORTING20_151_0__, sortingmea1_.ID as ID1_151_0__, quantifica2_.NUMERICAL_VALUE as NUMERICA2_130_2_, quantifica2_.ALPHANUMERICAL_VALUE as ALPHANUM3_130_2_, quantifica2_.DIGIT_COUNT as DIGIT_CO4_130_2_, quantifica2_.PRECISION_VALUE as PRECISIO5_130_2_, quantifica2_.CONTROL_DATE as CONTROL_6_130_2_, quantifica2_.VALIDATION_DATE as VALIDATI7_130_2_, quantifica2_.QUALIFICATION_DATE as QUALIFIC8_130_2_, quantifica2_.QUALIFICATION_COMMENTS as QUALIFIC9_130_2_, quantifica2_.REMOTE_ID as REMOTE_10_130_2_, quantifica2_.AGGREGATION_LEVEL_FK as AGGREGA11_130_2_, quantifica2_.QUALITY_FLAG_FK as QUALITY12_130_2_, quantifica2_.PRECISION_TYPE_FK as PRECISI13_130_2_, quantifica2_.ANALYSIS_INSTRUMENT_FK as ANALYSI14_130_2_, quantifica2_.NUMERICAL_PRECISION_FK as NUMERIC15_130_2_, quantifica2_.DEPARTMENT_FK as DEPARTM16_130_2_, quantifica2_.PMFM_FK as PMFM_FK17_130_2_, quantifica2_.QUALITATIVE_VALUE_FK as QUALITA18_130_2_, quantifica2_.SUBGROUP_NUMBER as SUBGROU19_130_2_, quantifica2_.IS_REFERENCE_QUANTIFICATION as IS_REFE20_130_2_, quantifica2_.BATCH_FK as BATCH_F21_130_2_, quantifica2_.BATCH_FK as BATCH_F21_130_1__, quantifica2_.ID as ID1_130_1__ from PUBLIC.BATCH sortingbat0_ left outer join PUBLIC.SORTING_MEASUREMENT sortingmea1_ on sortingbat0_.ID=sortingmea1_.SORTING_BATCH_FK left outer join PUBLIC.QUANTIFICATION_MEASUREMENT quantifica2_ on sortingbat0_.ID=quantifica2_.BATCH_FK where sortingbat0_.IS_CATCH_BATCH=0 and sortingbat0_.ROOT_BATCH_FK=? and ( quantifica2_.ID is null or quantifica2_.IS_REFERENCE_QUANTIFICATION=true or quantifica2_.IS_REFERENCE_QUANTIFICATION=false and quantifica2_.PMFM_FK=? ) order by sortingbat0_.ID, sortingmea1_.RANK_ORDER, sortingmea1_.SORTING_BATCH_FK, quantifica2_.BATCH_FK; SELECT b FROM SortingBatchImpl b left join fetch b.sortingMeasurements sm left join fetch b.quantificationMeasurements qm WHERE b.rootBatch.id=:rootBatchId AND ( qm is null OR qm.isReferenceQuantification=true OR (qm.isReferenceQuantification=false AND qm.pmfm.id = :pmfmId) ) ORDER BY b.id, sm.rankOrder
Parameters
Name Type Description
pmfmId java.lang.Integer  
rootBatchId java.lang.Integer  


Query landingIdsByObservedLocationIdAndVesselCode

Get all landing Ids corresponding to a observed location

SQL query HQL query
select distinct landingimp0_.ID as col_0_0_ from PUBLIC.LANDING landingimp0_ where 1=1 and landingimp0_.OBSERVED_LOCATION_FK=? and landingimp0_.VESSEL_FK=? order by landingimp0_.ID; SELECT DISTINCT la.id AS id FROM LandingImpl la WHERE 1=1 AND la.observedLocation.id = :observedLocationId AND la.vessel.code = :vesselCode ORDER BY la.id
Parameters
Name Type Description
observedLocationId java.lang.Integer  
vesselCode java.lang.String  


Query fishingOperationGroupById

Get one operation group by id

SQL query HQL query
select fishingope0_.ID as col_0_0_, fishingope0_.START_DATE_TIME as col_1_0_, fishingope0_.END_DATE_TIME as col_2_0_, fishingope0_.FISHING_START_DATE_TIME as col_3_0_, fishingope0_.FISHING_END_DATE_TIME as col_4_0_, fishingtri15_.DEPARTURE_DATE_TIME as col_5_0_, fishingtri15_.RETURN_DATE_TIME as col_6_0_, metierimpl8_.ID as col_7_0_, metierimpl8_.LABEL as col_8_0_, metierimpl8_.NAME as col_9_0_, metierimpl8_.STATUS_FK as col_10_0_, gearimpl9_.ID as col_11_0_, gearimpl9_.LABEL as col_12_0_, gearimpl9_.NAME as col_13_0_, gearimpl9_.STATUS_FK as col_14_0_, locationim11_.ID as col_15_0_, locationim11_.LABEL as col_16_0_, locationim11_.NAME as col_17_0_, locationim11_.STATUS_FK as col_18_0_, distanceto12_.ID as col_19_0_, distanceto12_.NAME as col_20_0_, distanceto12_.STATUS_FK as col_21_0_, fishingope0_.HAS_CATCH as col_22_0_, fishingope0_.COMMENTS as col_23_0_, count(produces1_.ID) as col_24_0_, depthgradi13_.ID as col_25_0_, depthgradi13_.NAME as col_26_0_, depthgradi13_.STATUS_FK as col_27_0_, nearbyspec14_.ID as col_28_0_, nearbyspec14_.NAME as col_29_0_, nearbyspec14_.STATUS_FK as col_30_0_, max(case vesselusem3_.PMFM_FK when ? then vesselusem3_.NUMERICAL_VALUE end) as col_31_0_, max(case gearusemea5_.PMFM_FK when ? then gearusemea5_.NUMERICAL_VALUE end) as col_32_0_, max(case vesselusem3_.PMFM_FK when ? then vesselusem3_.NUMERICAL_VALUE end) as col_33_0_, max(case gearphysic7_.PMFM_FK when ? then gearphysic7_.NUMERICAL_VALUE end) as col_34_0_, max(case gearphysic7_.PMFM_FK when ? then gearphysic7_.NUMERICAL_VALUE end) as col_35_0_ from PUBLIC.OPERATION fishingope0_ left outer join PUBLIC.PRODUCE produces1_ on fishingope0_.ID=produces1_.FISHING_OPERATION_FK left outer join PUBLIC.VESSEL_USE_FEATURES vesselusef2_ on fishingope0_.ID=vesselusef2_.OPERATION_FK left outer join PUBLIC.VESSEL_USE_MEASUREMENT vesselusem3_ on vesselusef2_.ID=vesselusem3_.VESSEL_USE_FEATURES_FK left outer join PUBLIC.GEAR_USE_FEATURES gearusefea4_ on fishingope0_.ID=gearusefea4_.OPERATION_FK left outer join PUBLIC.GEAR_USE_MEASUREMENT gearusemea5_ on gearusefea4_.ID=gearusemea5_.GEAR_USE_FEATURES_FK left outer join PUBLIC.METIER metierimpl8_ on gearusefea4_.METIER_FK=metierimpl8_.ID left outer join PUBLIC.GEAR gearimpl9_ on gearusefea4_.GEAR_FK=gearimpl9_.ID left outer join PUBLIC.FISHING_AREA fishingare10_ on gearusefea4_.ID=fishingare10_.GEAR_USE_FEATURES_FK left outer join PUBLIC.LOCATION locationim11_ on fishingare10_.LOCATION_FK=locationim11_.ID left outer join PUBLIC.DISTANCE_TO_COAST_GRADIENT distanceto12_ on fishingare10_.DISTANCE_TO_COAST_GRADIENT_FK=distanceto12_.ID left outer join PUBLIC.DEPTH_GRADIENT depthgradi13_ on fishingare10_.DEPTH_GRADIENT_FK=depthgradi13_.ID left outer join PUBLIC.NEARBY_SPECIFIC_AREA nearbyspec14_ on fishingare10_.NEARBY_SPECIFIC_AREA_FK=nearbyspec14_.ID left outer join PUBLIC.GEAR_PHYSICAL_FEATURES gearphysic6_ on fishingope0_.GEAR_PHYSICAL_FEATURES_FK=gearphysic6_.ID left outer join PUBLIC.GEAR_PHYSICAL_MEASUREMENT gearphysic7_ on gearphysic6_.ID=gearphysic7_.GEAR_PHYSICAL_FEATURES_FK, PUBLIC.FISHING_TRIP fishingtri15_ where fishingope0_.IS_FISHING_OPERATION=1 and fishingope0_.FISHING_TRIP_FK=fishingtri15_.ID and fishingope0_.ID=? group by fishingope0_.ID , fishingope0_.START_DATE_TIME , fishingope0_.END_DATE_TIME , fishingope0_.FISHING_START_DATE_TIME , fishingope0_.FISHING_END_DATE_TIME , fishingtri15_.DEPARTURE_DATE_TIME , fishingtri15_.RETURN_DATE_TIME , metierimpl8_.ID , metierimpl8_.LABEL , metierimpl8_.NAME , metierimpl8_.STATUS_FK , gearimpl9_.ID , gearimpl9_.LABEL , gearimpl9_.NAME , gearimpl9_.STATUS_FK , locationim11_.ID , locationim11_.LABEL , locationim11_.NAME , locationim11_.STATUS_FK , distanceto12_.ID , distanceto12_.NAME , distanceto12_.STATUS_FK , fishingope0_.HAS_CATCH , fishingope0_.COMMENTS , depthgradi13_.ID , depthgradi13_.NAME , depthgradi13_.STATUS_FK , nearbyspec14_.ID , nearbyspec14_.NAME , nearbyspec14_.STATUS_FK; SELECT o.id AS id, o.startDateTime AS startDate, o.endDateTime AS endDate, o.fishingStartDateTime AS fishingStartDate, o.fishingEndDateTime AS fishingEndDate, o.fishingTrip.departureDateTime AS ftStartDate, o.fishingTrip.returnDateTime AS ftEndDate, m.id AS metierId, m.label AS metierLabel, m.name AS metierName, m.status.code AS metierStatusCode, g.id AS gearId, g.label AS gearLabel, g.name AS gearName, g.status.code AS gearStatusCode, fal.id AS fishingAreaLocationId, fal.label AS fishingAreaLocationLabel, fal.name AS fishingAreaLocationname, fal.status.code AS fishingAreaLocationStatusCode, dcg.id AS distanceToCoastId, dcg.name AS distanceToCoastLabel, dcg.status.code AS distanceToCoastStatusCode, o.hasCatch AS hasCatch, o.comments AS comments, count(p) AS produceCount, dg.id AS depthGradientId, dg.name AS depthGradientLabel, dg.status.code AS depthGradientStatusCode, nsa.id AS nearbySpecificAreaId, nsa.name AS nearbySpecificAreaLabel, nsa.status.code AS nearbySpecificAreaStatusCode, max(CASE vum.pmfm.id WHEN :pmfmIdVesselFishingDuration THEN vum.numericalValue END) AS vesselFishingDuration, max(CASE gum.pmfm.id WHEN :pmfmIdGearFishingDuration THEN gum.numericalValue END) AS gearFishingDuration, max(CASE vum.pmfm.id WHEN :pmfmIdFishingOperationNumber THEN vum.numericalValue END) AS fishingOperationNumber, max(CASE gpm.pmfm.id WHEN :pmfmIdGearMeshSize THEN gpm.numericalValue END) AS gearMeshSize, max(CASE gpm.pmfm.id WHEN :pmfmIdGearDimension THEN gpm.numericalValue END) AS gearDimension FROM FishingOperationImpl o left join o.produces p left join o.vesselUseFeatures vuf left join vuf.vesselUseMeasurements vum left join o.gearUseFeatures guf left join guf.gearUseMeasurements gum left join o.gearPhysicalFeatures gpf left join gpf.gearPhysicalMeasurements gpm left join guf.metier m left join guf.gear g left join guf.fishingAreas fa left join fa.location fal left join fa.distanceToCoastGradient dcg left join fa.depthGradient dg left join fa.nearbySpecificArea nsa WHERE o.id = :fishingOperationGroupId GROUP BY o.id, o.startDateTime, o.endDateTime, o.fishingStartDateTime, o.fishingEndDateTime, o.fishingTrip.departureDateTime, o.fishingTrip.returnDateTime, m.id, m.label, m.name, m.status.code, g.id, g.label, g.name, g.status.code, fal.id, fal.label, fal.name, fal.status.code, dcg.id, dcg.name, dcg.status.code, o.hasCatch, o.comments, dg.id, dg.name, dg.status.code, nsa.id, nsa.name, nsa.status.code
Parameters
Name Type Description
pmfmIdFishingOperationNumber java.lang.Integer  
pmfmIdGearMeshSize java.lang.Integer  
pmfmIdGearDimension java.lang.Integer  
fishingOperationGroupId java.lang.Integer  
pmfmIdGearFishingDuration java.lang.Integer  
pmfmIdVesselFishingDuration java.lang.Integer  


Query produceEntitiesByLanding

Get produces (as Hibernate entities) by landing (vessel on site) - used by SFA only

SQL query HQL query
select produces1_.ID as ID1_118_, produces1_.IS_DISCARD as IS_DISCA2_118_, produces1_.SUBGROUP_COUNT as SUBGROUP3_118_, produces1_.INDIVIDUAL_COUNT as INDIVIDU4_118_, produces1_.TAXON_GROUP_OTHER_INFORMATION as TAXON_GR5_118_, produces1_.CONTROL_DATE as CONTROL_6_118_, produces1_.VALIDATION_DATE as VALIDATI7_118_, produces1_.QUALIFICATION_DATE as QUALIFIC8_118_, produces1_.QUALIFICATION_COMMENTS as QUALIFIC9_118_, produces1_.WEIGHT as WEIGHT10_118_, produces1_.COST as COST11_118_, produces1_.REMOTE_ID as REMOTE_12_118_, produces1_.OTHER_TAXON_GROUP_FK as OTHER_T13_118_, produces1_.TAXON_GROUP_FK as TAXON_G14_118_, produces1_.SALE_TYPE_FK as SALE_TY15_118_, produces1_.OTHER_GEAR_FK as OTHER_G16_118_, produces1_.DRESSING_FK as DRESSIN17_118_, produces1_.PRESERVATION_FK as PRESERV18_118_, produces1_.TRANSACTION_FK as TRANSAC19_118_, produces1_.FRESHNESS_CATEGORY_FK as FRESHNE20_118_, produces1_.SIZE_CATEGORY_FK as SIZE_CA21_118_, produces1_.WEIGHT_METHOD_FK as WEIGHT_22_118_, produces1_.FISHING_OPERATION_FK as FISHING23_118_, produces1_.EXPECTED_SALE_FK as EXPECTE24_118_, produces1_.BATCH_FK as BATCH_F25_118_, produces1_.BUYER_FK as BUYER_F26_118_, produces1_.SALE_FK as SALE_FK27_118_, produces1_.GEAR_FK as GEAR_FK28_118_, produces1_.LANDING_FK as LANDING29_118_, produces1_.TAKE_OVER_FK as TAKE_OV30_118_, produces1_.QUALITY_FLAG_FK as QUALITY31_118_, produces1_.TRANSSHIPMENT_FK as TRANSSH32_118_ from PUBLIC.LANDING landingimp0_ inner join PUBLIC.PRODUCE produces1_ on landingimp0_.ID=produces1_.LANDING_FK where landingimp0_.FISHING_TRIP_FK=? and ( produces1_.EXPECTED_SALE_FK is null ); SELECT p FROM LandingImpl la inner join la.produces p WHERE la.fishingTrip.id = :fishingTripId AND p.expectedSale is null
Parameters
Name Type Description
fishingTripId java.lang.Integer  


Query locationsByIds

Get locations by ids

SQL query HQL query
select locationim0_.ID as col_0_0_, locationim0_.LABEL as col_1_0_, locationim0_.NAME as col_2_0_, locationim0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LOCATION locationim0_ inner join PUBLIC.STATUS statusimpl1_ on locationim0_.STATUS_FK=statusimpl1_.CODE where ( locationim0_.STATUS_FK in ( ? , ? ) ) and ( locationim0_.ID in ( ? ) ); SELECT l.id as locationId, l.label as locationLabel, l.name as locationName, l.status as status FROM LocationImpl l WHERE l.status.code IN (:statusValidCode, :statusTemporaryCode) AND l.id IN (:locationIds)
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
locationIds java.lang.String  


Query saleProducesByFishingTrip

Get sale by fishing trip

SQL query HQL query
select produces1_.ID as col_0_0_, produces1_.INDIVIDUAL_COUNT as col_1_0_, max(case when producequa2_.PMFM_FK=? then producequa2_.NUMERICAL_VALUE end) as col_2_0_, max(case when producequa2_.PMFM_FK=? then producequa2_.NUMERICAL_VALUE end) as col_3_0_, max(case when producesor3_.PMFM_FK=? then producesor3_.QUALITATIVE_VALUE_FK end) as col_4_0_, produces1_.WEIGHT as col_5_0_, max(case when producequa2_.PMFM_FK=? then producequa2_.NUMERICAL_VALUE end) as col_6_0_, max(case when producequa2_.PMFM_FK=? then producequa2_.NUMERICAL_VALUE end) as col_7_0_, max(coalesce(produces1_.SIZE_CATEGORY_FK, case when producesor3_.PMFM_FK=? then producesor3_.QUALITATIVE_VALUE_FK end)) as col_8_0_, max(case when producesor3_.PMFM_FK=? then producesor3_.NUMERICAL_VALUE end) as col_9_0_, max(case when producesor3_.PMFM_FK=? then producesor3_.NUMERICAL_VALUE end) as col_10_0_, max(case when producesor3_.PMFM_FK=? then producesor3_.NUMERICAL_VALUE end) as col_11_0_, max(case when producesor3_.PMFM_FK=? then producesor3_.NUMERICAL_VALUE end) as col_12_0_, max(case when producesor3_.PMFM_FK=? then producesor3_.NUMERICAL_VALUE end) as col_13_0_, max(coalesce(produces1_.COST, case when producesor3_.PMFM_FK=? then producesor3_.NUMERICAL_VALUE end, case when producequa2_.PMFM_FK=? then producequa2_.NUMERICAL_VALUE end)) as col_14_0_, max(case when producesor3_.PMFM_FK=? then producesor3_.NUMERICAL_VALUE end) as col_15_0_, produces1_.WEIGHT_METHOD_FK as col_16_0_, saletypeim4_.ID as col_17_0_, saletypeim4_.NAME as col_18_0_, statusimpl5_.CODE as col_19_0_, taxongroup6_.ID as col_20_0_, taxongroup6_.LABEL as col_21_0_, taxongroup6_.NAME as col_22_0_, taxongroup6_.STATUS_FK as col_23_0_, statusimpl5_.CODE as CODE1_159_0_, statusimpl7_.CODE as CODE1_159_1_, statusimpl5_.NAME as NAME2_159_0_, statusimpl5_.UPDATE_DATE as UPDATE_D3_159_0_, statusimpl7_.NAME as NAME2_159_1_, statusimpl7_.UPDATE_DATE as UPDATE_D3_159_1_ from PUBLIC.LANDING landingimp0_ inner join PUBLIC.PRODUCE produces1_ on landingimp0_.ID=produces1_.LANDING_FK left outer join PUBLIC.QUANTIFICATION_MEASUREMENT_P producequa2_ on produces1_.ID=producequa2_.PRODUCE_FK left outer join PUBLIC.SORTING_MEASUREMENT_P producesor3_ on produces1_.ID=producesor3_.PRODUCE_FK left outer join PUBLIC.SALE_TYPE saletypeim4_ on produces1_.SALE_TYPE_FK=saletypeim4_.ID left outer join PUBLIC.STATUS statusimpl5_ on saletypeim4_.STATUS_FK=statusimpl5_.CODE left outer join PUBLIC.TAXON_GROUP taxongroup6_ on produces1_.TAXON_GROUP_FK=taxongroup6_.ID inner join PUBLIC.STATUS statusimpl7_ on taxongroup6_.STATUS_FK=statusimpl7_.CODE where landingimp0_.FISHING_TRIP_FK=? and ( produces1_.EXPECTED_SALE_FK is not null ) group by landingimp0_.FISHING_TRIP_FK , produces1_.ID , produces1_.INDIVIDUAL_COUNT , produces1_.WEIGHT , produces1_.WEIGHT_METHOD_FK , saletypeim4_.ID , saletypeim4_.NAME , statusimpl5_.CODE , statusimpl5_.NAME , statusimpl5_.UPDATE_DATE , taxongroup6_.ID , taxongroup6_.LABEL , taxongroup6_.NAME , taxongroup6_.STATUS_FK , statusimpl7_.NAME , statusimpl7_.UPDATE_DATE order by taxongroup6_.LABEL ASC; SELECT p.id AS produceId, p.individualCount AS individualCount, max(case when pqm.pmfm.id = :pmfmIdProduceEstimatedIndividualCount then pqm.numericalValue end) AS estimatedIndividualCount, max(case when pqm.pmfm.id = :pmfmIdProduceMeasuredIndividualCount then pqm.numericalValue end) AS measuredIndividualCount, max(case when psm.pmfm.id = :pmfmIdProducePackaging then psm.qualitativeValue.id end) AS packaging, p.weight AS weight, max(case when pqm.pmfm.id= :pmfmIdMeasuredWeight then pqm.numericalValue end) AS measuredWeight, max(case when pqm.pmfm.id= :pmfmIdEstimatedWeight then pqm.numericalValue end) AS estimatedWeight, max(coalesce(p.sizeCategory.id, case when psm.pmfm.id= :pmfmIdSizeCategory then psm.qualitativeValue.id end)) AS qvSizeCategory, max(case when psm.pmfm.id= :pmfmIdProduceAveragePrice then psm.numericalValue end) AS averagePrice, max(case when psm.pmfm.id= :pmfmIdProduceAveragePricePerUnit then psm.numericalValue end) AS averagePricePerUnit, max(case when psm.pmfm.id= :pmfmIdProduceAveragePricePerDozen then psm.numericalValue end) AS averagePricePerDozen, max(case when psm.pmfm.id= :pmfmIdProduceAveragePricePerHundred then psm.numericalValue end) AS averagePricePerHundred, max(case when psm.pmfm.id= :pmfmIdProduceAveragePricePerVolume then psm.numericalValue end) AS averagePricePerVolume, max(coalesce(p.cost, case when psm.pmfm.id= :pmfmIdProduceTotalPrice then psm.numericalValue end, case when pqm.pmfm.id= :pmfmIdProduceTotalPrice then pqm.numericalValue end)) AS totalPrice, max(case when psm.pmfm.id= :pmfmIdProduceSamplingRatio then psm.numericalValue end) AS samplingRatio, p.weightMethod.id AS weightMethodId, st.id AS saleTypeId, st.name AS saleTypeName, sts AS saleTypeStatus, tg.id as taxonGroupId, tg.label as taxonGroupLabel, tg.name as taxonGroupName, tg.status as taxonGroupStatus FROM LandingImpl la inner join la.produces p left join p.produceQuantificationMeasurements pqm left join p.produceSortingMeasurements psm left join p.saleType st left join st.status sts left join p.taxonGroup tg WHERE la.fishingTrip.id = :fishingTripId AND p.expectedSale is not null GROUP BY la.fishingTrip.id, p.id , p.individualCount, p.weight, p.weightMethod.id , st.id, st.name, sts.code, sts.name, sts.updateDate, tg.id , tg.label, tg.name , tg.status.code, tg.status.name, tg.status.updateDate ORDER BY tg.label ASC
Parameters
Name Type Description
pmfmIdProduceAveragePrice java.lang.Integer  
pmfmIdProduceAveragePricePerUnit java.lang.Integer  
pmfmIdProduceAveragePricePerHundred java.lang.Integer  
pmfmIdProduceSamplingRatio java.lang.Integer  
pmfmIdProduceMeasuredIndividualCount java.lang.Integer  
pmfmIdEstimatedWeight java.lang.Integer  
pmfmIdProduceTotalPrice java.lang.Integer  
pmfmIdMeasuredWeight java.lang.Integer  
fishingTripId java.lang.Integer  
pmfmIdProduceEstimatedIndividualCount java.lang.Integer  
pmfmIdProducePackaging java.lang.Integer  
pmfmIdProduceAveragePricePerVolume java.lang.Integer  
pmfmIdSizeCategory java.lang.Integer  
pmfmIdProduceAveragePricePerDozen java.lang.Integer  


Query countDailyActivityCalendarBySynchronizationStatus

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv0_ where dailyactiv0_.RECORDER_PERSON_FK=? and dailyactiv0_.SYNCHRONIZATION_STATUS=?; SELECT count(*) FROM DailyActivityCalendarImpl ac WHERE ac.recorderPerson.id = :recorderPersonId AND ac.synchronizationStatus = :synchronizationStatus
Parameters
Name Type Description
synchronizationStatus java.lang.String  
recorderPersonId java.lang.Integer  


Query allDepthGradients

SQL query HQL query
select depthgradi0_.ID as col_0_0_, depthgradi0_.NAME as col_1_0_, depthgradi0_.STATUS_FK as col_2_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.DEPTH_GRADIENT depthgradi0_ inner join PUBLIC.STATUS statusimpl1_ on depthgradi0_.STATUS_FK=statusimpl1_.CODE where depthgradi0_.STATUS_FK in ( ? , ? ) order by depthgradi0_.RANK_ORDER ASC; SELECT g.id AS id, g.name AS name, g.status AS status FROM DepthGradientImpl g WHERE g.status.code IN (:statusValidCode, :statusTemporaryCode) ORDER BY g.rankOrder ASC
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  


Query vesselByRegistrationCodeOrNameOrType

SQL query HQL query
select distinct vesselimpl0_.CODE as col_0_0_, vesselregi1_.REGISTRATION_CODE as col_1_0_, vesselregi1_.INT_REGISTRATION_CODE as col_2_0_, vesselfeat2_.NAME as col_3_0_, vesselimpl0_.VESSEL_TYPE_FK as col_4_0_, vesseltype3_.NAME as col_5_0_, vesselimpl0_.STATUS_FK as col_6_0_, statusimpl4_.CODE as CODE1_159_, statusimpl4_.NAME as NAME2_159_, statusimpl4_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.VESSEL vesselimpl0_ inner join PUBLIC.VESSEL_REGISTRATION_PERIOD vesselregi1_ on vesselimpl0_.CODE=vesselregi1_.VESSEL_FK left outer join PUBLIC.VESSEL_FEATURES vesselfeat2_ on vesselimpl0_.CODE=vesselfeat2_.VESSEL_FK, PUBLIC.VESSEL_TYPE vesseltype3_ inner join PUBLIC.STATUS statusimpl4_ on vesselimpl0_.STATUS_FK=statusimpl4_.CODE where vesselimpl0_.VESSEL_TYPE_FK=vesseltype3_.ID and ( ? is null or upper(vesselfeat2_.NAME) like ('%'||?||'%') ) and ( ? is null or upper(vesselregi1_.REGISTRATION_CODE) like ('%'||?||'%') or upper(vesselregi1_.INT_REGISTRATION_CODE) like ('%'||?||'%') ) and ( vesselimpl0_.VESSEL_TYPE_FK in ( ? ) ) and ( vesselimpl0_.STATUS_FK in ( ? ) ) and coalesce(vesselregi1_.END_DATE_TIME, '2999-12-31 00:00:00')>=coalesce(?, sysdate) and vesselregi1_.START_DATE_TIME<=coalesce(?, sysdate); SELECT DISTINCT v.code AS vesselCode, vrp.registrationCode AS nationalRegistrationCode, vrp.internationalRegistrationCode as internationalRegistrationCode, vf.name AS vesselName, v.vesselType.id AS vesselTypeId, v.vesselType.name AS vesselTypeName, v.status AS status FROM VesselImpl v INNER JOIN v.vesselRegistrationPeriods AS vrp LEFT OUTER JOIN v.vesselFeatures AS vf WHERE ( :name is null OR (upper(vf.name) like '%' || :name || '%') ) AND ( :registrationCode is null OR (upper(vrp.registrationCode) like '%' || :registrationCode || '%') OR (upper(vrp.internationalRegistrationCode) like '%' || :registrationCode || '%') ) AND v.vesselType.id IN (:vesselTypeIds) AND v.status.code IN (:statusCodes) AND NOT(coalesce(vrp.endDateTime, '2999-12-31 00:00:00') < coalesce(:refDate,sysdate) OR vrp.vesselRegistrationPeriodPk.startDateTime > coalesce(:refDate,sysdate) )
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
name java.lang.String  
registrationCode java.lang.String  
vesselTypeIds java.lang.String  
refDate java.util.Date  


Query observedLocationFeaturesByObservedLocationId

Get a observed location features by observed location Id

SQL query HQL query
select observedlo0_.ID as col_0_0_, observedlo0_.VESSEL_TYPE_FK as col_1_0_, (select observedlo1_.NUMERICAL_VALUE from PUBLIC.OBSERVED_LOCATION_MEASUREMENT observedlo1_ where observedlo1_.OBSERVED_LOCATION_FEATURES_FK=observedlo0_.ID and observedlo1_.PMFM_FK=?) as col_2_0_, (select observedlo2_.QUALITATIVE_VALUE_FK from PUBLIC.OBSERVED_LOCATION_MEASUREMENT observedlo2_ where observedlo2_.OBSERVED_LOCATION_FEATURES_FK=observedlo0_.ID and observedlo2_.PMFM_FK=?) as col_3_0_, observedlo0_.METIER_FK as col_4_0_ from PUBLIC.OBSERVED_LOCATION_FEATURES observedlo0_ where observedlo0_.OBSERVED_LOCATION_FK=?; SELECT f.id, f.vesselType.id AS vesselTypeId, (select fm.numericalValue from ObservedLocationMeasurementImpl fm where fm.observedLocationFeatures.id = f.id and fm.pmfm.id=:pmfmIdVesselCount) AS vesselCount, (select fm.qualitativeValue.id from ObservedLocationMeasurementImpl fm where fm.observedLocationFeatures.id= f.id and fm.pmfm.id=:pmfmIdVesselPortState) AS vesselPortStateQvId, f.metier.id AS metierId FROM ObservedLocationFeaturesImpl f WHERE f.observedLocation.id = :observedLocationId
Parameters
Name Type Description
pmfmIdVesselPortState java.lang.Integer  
observedLocationId java.lang.Integer  
pmfmIdVesselCount java.lang.Integer  


Query expensesByFishingTripId

Get a fishing trip by id

SQL query HQL query
select fishingtri0_.ID as col_0_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_1_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.QUALITATIVE_VALUE_FK else null end) as col_2_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_3_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_4_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_5_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_6_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_7_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_8_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_9_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_10_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_11_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_12_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_13_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_14_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_15_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_16_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_17_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_18_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_19_0_, max(case vesselusem2_.PMFM_FK when ? then vesselusem2_.NUMERICAL_VALUE else null end) as col_20_0_, '' as col_21_0_ from PUBLIC.FISHING_TRIP fishingtri0_ inner join PUBLIC.VESSEL_USE_FEATURES vesselusef1_ on fishingtri0_.ID=vesselusef1_.FISHING_TRIP_FK inner join PUBLIC.VESSEL_USE_MEASUREMENT vesselusem2_ on vesselusef1_.ID=vesselusem2_.VESSEL_USE_FEATURES_FK where fishingtri0_.ID=? and ( vesselusem2_.PMFM_FK in ( ? ) ) group by fishingtri0_.ID; SELECT ft.id AS id, max(case vum.pmfm.id when :pmfmIdEstimatedTotalCost then vum.numericalValue else null end) AS estimatedTotalCost, max(case vum.pmfm.id when :pmfmIdFuelType then vum.qualitativeValue.id else null end) AS fuelTypeQv, max(case vum.pmfm.id when :pmfmIdFuelVolume then vum.numericalValue else null end) AS fuelVolume, max(case vum.pmfm.id when :pmfmIdFuelUnitPrice then vum.numericalValue else null end) AS fuelUnitPrice, max(case vum.pmfm.id when :pmfmIdFuelCost then vum.numericalValue else null end) AS fuelCost, max(case vum.pmfm.id when :pmfmIdEngineOilVolume then vum.numericalValue else null end) AS engineOilVolume, max(case vum.pmfm.id when :pmfmIdEngineOilUnitPrice then vum.numericalValue else null end) AS engineOilUnitPrice, max(case vum.pmfm.id when :pmfmIdEngineOilCost then vum.numericalValue else null end) AS engineOilCost, max(case vum.pmfm.id when :pmfmIdHydraulicOilVolume then vum.numericalValue else null end) AS hydraulicOilVolume, max(case vum.pmfm.id when :pmfmIdHydraulicOilUnitPrice then vum.numericalValue else null end) AS hydraulicOilUnitPrice, max(case vum.pmfm.id when :pmfmIdHydraulicOilCost then vum.numericalValue else null end) AS hydraulicOilCost, max(case vum.pmfm.id when :pmfmIdLandingCost then vum.numericalValue else null end) AS landingCost, max(case vum.pmfm.id when :pmfmIdIceCost then vum.numericalValue else null end) AS iceCost, max(case vum.pmfm.id when :pmfmIdIceKg then vum.numericalValue else null end) AS iceKg, max(case vum.pmfm.id when :pmfmIdIceBags then vum.numericalValue else null end) AS iceBags, max(case vum.pmfm.id when :pmfmIdBaitCost then vum.numericalValue else null end) AS baitCost, max(case vum.pmfm.id when :pmfmIdBaitKg then vum.numericalValue else null end) AS baitKg, max(case vum.pmfm.id when :pmfmIdFoodCost then vum.numericalValue else null end) AS foodCost, max(case vum.pmfm.id when :pmfmIdLostCost then vum.numericalValue else null end) AS lostCost, max(case vum.pmfm.id when :pmfmIdOtherCost then vum.numericalValue else null end) AS otherCost, '' as comments FROM FishingTripImpl ft inner join ft.vesselUseFeatures vuf inner join vuf.vesselUseMeasurements vum WHERE ft.id = :fishingTripId AND vum.pmfm.id IN (:pmfmIdsUsedForExpenses) GROUP BY ft.id
Parameters
Name Type Description
pmfmIdIceCost java.lang.Integer  
pmfmIdLostCost java.lang.Integer  
pmfmIdOtherCost java.lang.Integer  
pmfmIdEngineOilCost java.lang.Integer  
pmfmIdFuelUnitPrice java.lang.Integer  
pmfmIdEngineOilVolume java.lang.Integer  
pmfmIdHydraulicOilUnitPrice java.lang.Integer  
pmfmIdFuelVolume java.lang.Integer  
pmfmIdFuelType java.lang.Integer  
pmfmIdLandingCost java.lang.Integer  
pmfmIdEngineOilUnitPrice java.lang.Integer  
fishingTripId java.lang.Integer  
pmfmIdIceBags java.lang.Integer  
pmfmIdBaitKg java.lang.Integer  
pmfmIdEstimatedTotalCost java.lang.Integer  
pmfmIdBaitCost java.lang.Integer  
pmfmIdHydraulicOilCost java.lang.Integer  
pmfmIdFoodCost java.lang.Integer  
pmfmIds java.lang.String  
pmfmIdIceKg java.lang.Integer  
pmfmIdFuelCost java.lang.Integer  
pmfmIdHydraulicOilVolume java.lang.Integer  


Query taxonGroupsByIds

SQL query HQL query
select taxongroup0_.ID as col_0_0_, taxongroup0_.LABEL as col_1_0_, taxongroup0_.NAME as col_2_0_, taxongroup0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.TAXON_GROUP taxongroup0_ inner join PUBLIC.STATUS statusimpl1_ on taxongroup0_.STATUS_FK=statusimpl1_.CODE where taxongroup0_.TAXON_GROUP_TYPE_FK=? and ( ( taxongroup0_.STATUS_FK in ( ? , ? ) ) and ( taxongroup0_.ID in ( ? ) ) or taxongroup0_.STATUS_FK=? ); SELECT t.id AS id, t.label AS label, t.name AS name, t.status AS status FROM TaxonGroupImpl t WHERE t.taxonGroupType.code = :taxonGroupTypeCode AND ( ( t.status.code IN (:statusValidCode, :statusTemporaryCode) AND t.id IN (:ids) ) OR t.status.code = :statusTemporaryCode )
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
ids java.lang.String  
taxonGroupTypeCode java.lang.String  


Query countTemporaryMetierUsedInOLF

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.OBSERVED_LOCATION_FEATURES observedlo0_ where observedlo0_.METIER_FK=?; SELECT count(*) FROM ObservedLocationFeaturesImpl olf WHERE olf.metier.id = :metierId
Parameters
Name Type Description
metierId java.lang.Integer  


Query gearIdByLabel

SQL query HQL query
select gearimpl0_.ID as col_0_0_ from PUBLIC.GEAR gearimpl0_ where ( gearimpl0_.STATUS_FK in ( ? , ? ) ) and gearimpl0_.GEAR_CLASSIFICATION_FK=? and gearimpl0_.LABEL=?; SELECT g.id AS id FROM GearImpl g WHERE g.status.code IN (:statusValidCode, :statusTemporaryCode) AND g.gearClassification.id=:gearClassificationId AND g.label = :gearLabel
Parameters
Name Type Description
statusValidCode java.lang.String  
gearClassificationId java.lang.Integer  
gearLabel java.lang.String  
statusTemporaryCode java.lang.String  


Query fishingOperationGroupsByFishingTripId

Get operation groups by fishing trip

SQL query HQL query
select operations1_.ID as col_0_0_, operations1_.START_DATE_TIME as col_1_0_, operations1_.END_DATE_TIME as col_2_0_, operations1_.FISHING_START_DATE_TIME as col_3_0_, operations1_.FISHING_END_DATE_TIME as col_4_0_, fishingtri0_.DEPARTURE_DATE_TIME as col_5_0_, fishingtri0_.RETURN_DATE_TIME as col_6_0_, metierimpl9_.ID as col_7_0_, metierimpl9_.LABEL as col_8_0_, metierimpl9_.NAME as col_9_0_, metierimpl9_.STATUS_FK as col_10_0_, gearimpl10_.ID as col_11_0_, gearimpl10_.LABEL as col_12_0_, gearimpl10_.NAME as col_13_0_, gearimpl10_.STATUS_FK as col_14_0_, locationim12_.ID as col_15_0_, locationim12_.LABEL as col_16_0_, locationim12_.NAME as col_17_0_, locationim12_.STATUS_FK as col_18_0_, distanceto13_.ID as col_19_0_, distanceto13_.NAME as col_20_0_, distanceto13_.STATUS_FK as col_21_0_, operations1_.HAS_CATCH as col_22_0_, operations1_.COMMENTS as col_23_0_, count(produces2_.ID) as col_24_0_, depthgradi14_.ID as col_25_0_, depthgradi14_.NAME as col_26_0_, depthgradi14_.STATUS_FK as col_27_0_, nearbyspec15_.ID as col_28_0_, nearbyspec15_.NAME as col_29_0_, nearbyspec15_.STATUS_FK as col_30_0_, max(case vesselusem4_.PMFM_FK when ? then vesselusem4_.NUMERICAL_VALUE end) as col_31_0_, max(case gearusemea6_.PMFM_FK when ? then gearusemea6_.NUMERICAL_VALUE end) as col_32_0_, max(case vesselusem4_.PMFM_FK when ? then vesselusem4_.NUMERICAL_VALUE end) as col_33_0_, max(case gearphysic8_.PMFM_FK when ? then gearphysic8_.NUMERICAL_VALUE end) as col_34_0_, max(case gearphysic8_.PMFM_FK when ? then gearphysic8_.NUMERICAL_VALUE end) as col_35_0_ from PUBLIC.FISHING_TRIP fishingtri0_ inner join PUBLIC.OPERATION operations1_ on fishingtri0_.ID=operations1_.FISHING_TRIP_FK left outer join PUBLIC.PRODUCE produces2_ on operations1_.ID=produces2_.FISHING_OPERATION_FK left outer join PUBLIC.VESSEL_USE_FEATURES vesselusef3_ on operations1_.ID=vesselusef3_.OPERATION_FK left outer join PUBLIC.VESSEL_USE_MEASUREMENT vesselusem4_ on vesselusef3_.ID=vesselusem4_.VESSEL_USE_FEATURES_FK left outer join PUBLIC.GEAR_USE_FEATURES gearusefea5_ on operations1_.ID=gearusefea5_.OPERATION_FK left outer join PUBLIC.GEAR_USE_MEASUREMENT gearusemea6_ on gearusefea5_.ID=gearusemea6_.GEAR_USE_FEATURES_FK left outer join PUBLIC.METIER metierimpl9_ on gearusefea5_.METIER_FK=metierimpl9_.ID left outer join PUBLIC.GEAR gearimpl10_ on gearusefea5_.GEAR_FK=gearimpl10_.ID left outer join PUBLIC.FISHING_AREA fishingare11_ on gearusefea5_.ID=fishingare11_.GEAR_USE_FEATURES_FK left outer join PUBLIC.LOCATION locationim12_ on fishingare11_.LOCATION_FK=locationim12_.ID left outer join PUBLIC.DISTANCE_TO_COAST_GRADIENT distanceto13_ on fishingare11_.DISTANCE_TO_COAST_GRADIENT_FK=distanceto13_.ID left outer join PUBLIC.DEPTH_GRADIENT depthgradi14_ on fishingare11_.DEPTH_GRADIENT_FK=depthgradi14_.ID left outer join PUBLIC.NEARBY_SPECIFIC_AREA nearbyspec15_ on fishingare11_.NEARBY_SPECIFIC_AREA_FK=nearbyspec15_.ID left outer join PUBLIC.GEAR_PHYSICAL_FEATURES gearphysic7_ on operations1_.GEAR_PHYSICAL_FEATURES_FK=gearphysic7_.ID left outer join PUBLIC.GEAR_PHYSICAL_MEASUREMENT gearphysic8_ on gearphysic7_.ID=gearphysic8_.GEAR_PHYSICAL_FEATURES_FK where fishingtri0_.ID=? group by operations1_.ID , operations1_.START_DATE_TIME , operations1_.END_DATE_TIME , operations1_.FISHING_START_DATE_TIME , operations1_.FISHING_END_DATE_TIME , fishingtri0_.DEPARTURE_DATE_TIME , fishingtri0_.RETURN_DATE_TIME , metierimpl9_.ID , metierimpl9_.LABEL , metierimpl9_.NAME , metierimpl9_.STATUS_FK , gearimpl10_.ID , gearimpl10_.LABEL , gearimpl10_.NAME , gearimpl10_.STATUS_FK , locationim12_.ID , locationim12_.LABEL , locationim12_.NAME , locationim12_.STATUS_FK , distanceto13_.ID , distanceto13_.NAME , distanceto13_.STATUS_FK , operations1_.HAS_CATCH , operations1_.COMMENTS , operations1_.RANK_ORDER_ON_PERIOD , depthgradi14_.ID , depthgradi14_.NAME , depthgradi14_.STATUS_FK , nearbyspec15_.ID , nearbyspec15_.NAME , nearbyspec15_.STATUS_FK order by operations1_.START_DATE_TIME ASC, coalesce(operations1_.RANK_ORDER_ON_PERIOD, -1) ASC; SELECT o.id AS id, o.startDateTime AS startDate, o.endDateTime AS endDate, o.fishingStartDateTime AS fishingStartDate, o.fishingEndDateTime AS fishingEndDate, ft.departureDateTime AS ftStartDate, ft.returnDateTime AS ftEndDate, m.id AS metierId, m.label AS metierLabel, m.name AS metierName, m.status.code AS metierStatusCode, g.id AS gearId, g.label AS gearLabel, g.name AS gearName, g.status.code AS gearStatusCode, fal.id AS fishingAreaLocationId, fal.label AS fishingAreaLocationLabel, fal.name AS fishingAreaLocationname, fal.status.code AS fishingAreaLocationStatusCode, dcg.id AS distanceToCoastId, dcg.name AS distanceToCoastLabel, dcg.status.code AS distanceToCoastStatusCode, o.hasCatch AS hasCatch, o.comments AS comments, count(p) AS produceCount, dg.id AS depthGradientId, dg.name AS depthGradientLabel, dg.status.code AS depthGradientStatusCode, nsa.id AS nearbySpecificAreaId, nsa.name AS nearbySpecificAreaLabel, nsa.status.code AS nearbySpecificAreaStatusCode, max(CASE vum.pmfm.id WHEN :pmfmIdVesselFishingDuration THEN vum.numericalValue END) AS vesselFishingDuration, max(CASE gum.pmfm.id WHEN :pmfmIdGearFishingDuration THEN gum.numericalValue END) AS gearFishingDuration, max(CASE vum.pmfm.id WHEN :pmfmIdFishingOperationNumber THEN vum.numericalValue END) AS fishingOperationNumber, max(CASE gpm.pmfm.id WHEN :pmfmIdGearMeshSize THEN gpm.numericalValue END) AS gearMeshSize, max(CASE gpm.pmfm.id WHEN :pmfmIdGearDimension THEN gpm.numericalValue END) AS gearDimension FROM FishingTripImpl ft inner join ft.operations o left join o.produces p left join o.vesselUseFeatures vuf left join vuf.vesselUseMeasurements vum left join o.gearUseFeatures guf left join guf.gearUseMeasurements gum left join o.gearPhysicalFeatures gpf left join gpf.gearPhysicalMeasurements gpm left join guf.metier m left join guf.gear g left join guf.fishingAreas fa left join fa.location fal left join fa.distanceToCoastGradient dcg left join fa.depthGradient dg left join fa.nearbySpecificArea nsa WHERE ft.id = :fishingTripId GROUP BY o.id, o.startDateTime, o.endDateTime, o.fishingStartDateTime, o.fishingEndDateTime, ft.departureDateTime, ft.returnDateTime, m.id, m.label, m.name, m.status.code, g.id, g.label, g.name, g.status.code, fal.id, fal.label, fal.name, fal.status.code, dcg.id, dcg.name, dcg.status.code, o.hasCatch, o.comments, o.rankOrderOnPeriod, dg.id, dg.name, dg.status.code, nsa.id, nsa.name, nsa.status.code ORDER BY o.startDateTime ASC, coalesce(o.rankOrderOnPeriod, -1) ASC
Parameters
Name Type Description
pmfmIdFishingOperationNumber java.lang.Integer  
pmfmIdGearMeshSize java.lang.Integer  
pmfmIdGearDimension java.lang.Integer  
pmfmIdGearFishingDuration java.lang.Integer  
fishingTripId java.lang.Integer  
pmfmIdVesselFishingDuration java.lang.Integer  


Query allPersons

Get all persons

SQL query HQL query
select distinct personimpl0_.ID as col_0_0_, personimpl0_.LASTNAME as col_1_0_, personimpl0_.FIRSTNAME as col_2_0_, department3_.CODE as col_3_0_, personimpl0_.STATUS_FK as col_4_0_, statusimpl4_.CODE as CODE1_159_, statusimpl4_.NAME as NAME2_159_, statusimpl4_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.PERSON personimpl0_ left outer join PUBLIC.PERSON2USER_PROFIL profils1_ on personimpl0_.ID=profils1_.PERSON_FK left outer join PUBLIC.USER_PROFIL userprofil2_ on profils1_.USER_PROFIL_FK=userprofil2_.ID, PUBLIC.DEPARTMENT department3_ inner join PUBLIC.STATUS statusimpl4_ on personimpl0_.STATUS_FK=statusimpl4_.CODE where personimpl0_.DEPARTMENT_FK=department3_.ID and ( personimpl0_.STATUS_FK in ( ? , ? ) ) and ( userprofil2_.ID in ( ? , ? , ? ) ) and ( ? is null or department3_.CODE like ? ); SELECT DISTINCT p.id, p.lastname, p.firstname, p.department.code, p.status FROM PersonImpl p LEFT OUTER JOIN p.profils pp WHERE p.status.code IN (:statusValidCode, :statusTemporaryCode) AND pp.id IN ( :observerProfilId, :projectMemberProfilId, :userProfilId ) AND (:departmentCodeFilter is null OR (p.department.code LIKE :departmentCodeFilter) )
Parameters
Name Type Description
statusValidCode java.lang.String  
departmentCodeFilter java.lang.String  
statusTemporaryCode java.lang.String  
observerProfilId java.lang.Integer  
userProfilId java.lang.Integer  
projectMemberProfilId java.lang.Integer  


Query allTranscribingForAType

SQL query HQL query
select transcribi0_.OBJECT_ID as col_0_0_, transcribi0_.EXTERNAL_CODE as col_1_0_ from PUBLIC.TRANSCRIBING_ITEM transcribi0_ where transcribi0_.TRANSCRIBING_ITEM_TYPE_FK=?; SELECT ti.objectId, ti.externalCode FROM TranscribingItemImpl ti WHERE ti.transcribingItemType.id = :transcribingTypeId )
Parameters
Name Type Description
transcribingTypeId java.lang.Integer  


Query locationByLabelAndLevel

Get location by level and label. Used to retrieve the default country, from a label in the configuration file

SQL query HQL query
select locationim0_.ID as col_0_0_, locationim0_.LABEL as col_1_0_, locationim0_.NAME as col_2_0_, locationim0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LOCATION locationim0_ inner join PUBLIC.STATUS statusimpl1_ on locationim0_.STATUS_FK=statusimpl1_.CODE where locationim0_.LABEL=? and ( locationim0_.LOCATION_LEVEL_FK in ( ? ) ); SELECT l.id as locationId, l.label as locationLabel, l.name as locationName, l.status as status FROM LocationImpl l WHERE l.label=:locationLabel AND l.locationLevel.id IN (:locationLevelIds)
Parameters
Name Type Description
locationLevelIds java.lang.String  
locationLabel java.lang.String  


Query calendarCountByVesselCode

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv0_ where dailyactiv0_.VESSEL_FK=?; SELECT COUNT(*) FROM DailyActivityCalendarImpl ca WHERE ca.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query updateCalendarsTemporaryVessel

Replace temporary vessel in daily activity calendars

SQL query HQL query
update PUBLIC.DAILY_ACTIVITY_CALENDAR set VESSEL_FK=? where VESSEL_FK=?; UPDATE DailyActivityCalendarImpl ac SET ac.vessel.code = :validVesselCode WHERE ac.vessel.code = :tempVesselCode
Parameters
Name Type Description
tempVesselCode java.lang.String  
validVesselCode java.lang.String  


Query personById

Get one person

SQL query HQL query
select distinct personimpl0_.ID as col_0_0_, personimpl0_.LASTNAME as col_1_0_, personimpl0_.FIRSTNAME as col_2_0_, department1_.CODE as col_3_0_, personimpl0_.STATUS_FK as col_4_0_, statusimpl2_.CODE as CODE1_159_, statusimpl2_.NAME as NAME2_159_, statusimpl2_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.PERSON personimpl0_, PUBLIC.DEPARTMENT department1_ inner join PUBLIC.STATUS statusimpl2_ on personimpl0_.STATUS_FK=statusimpl2_.CODE where personimpl0_.DEPARTMENT_FK=department1_.ID and personimpl0_.ID=?; SELECT DISTINCT p.id, p.lastname, p.firstname, p.department.code, p.status FROM PersonImpl p WHERE p.id = :personId
Parameters
Name Type Description
personId java.lang.Integer  


Query personSessionIdByPerson

SQL query HQL query
select max(personsess0_.ID) as col_0_0_ from PUBLIC.PERSON_SESSION personsess0_ where personsess0_.PERSON_FK=?; SELECT max(ps.id) FROM PersonSessionImpl ps WHERE ps.person.id = :personId
Parameters
Name Type Description
personId java.lang.Integer  


Query updateLandingsTemporaryVessel

Replace temporary vessel in landings

SQL query HQL query
update PUBLIC.LANDING set VESSEL_FK=? where VESSEL_FK=?; UPDATE LandingImpl l SET l.vessel.code = :validVesselCode WHERE l.vessel.code = :tempVesselCode
Parameters
Name Type Description
tempVesselCode java.lang.String  
validVesselCode java.lang.String  


Query packetCatchBatchByLandingId

SQL query HQL query
select catchbatch0_.ID as col_0_0_ from PUBLIC.BATCH catchbatch0_ cross join PUBLIC.LANDING landingimp1_ where catchbatch0_.IS_CATCH_BATCH=1 and catchbatch0_.ID=landingimp1_.CATCH_BATCH_FK and landingimp1_.ID=?; SELECT cb.id FROM CatchBatchImpl cb WHERE cb.landing.id = :landingId
Parameters
Name Type Description
landingId java.lang.Integer  


Query landingsWithMainMetierByObservedLocationId

Get vessels on an observed locations, with main metier

SQL query HQL query
select distinct landingimp0_.ID as col_0_0_, fishingtri1_.RETURN_DATE_TIME as col_1_0_, landingimp0_.SYNCHRONIZATION_STATUS as col_2_0_, landingimp0_.VESSEL_FK as col_3_0_, vesselimpl6_.VESSEL_TYPE_FK as col_4_0_, metierimpl4_.ID as col_5_0_, denormaliz5_.NAME as col_6_0_, denormaliz5_.REGISTRATION_CODE as col_7_0_, denormaliz5_.INT_REGISTRATION_CODE as col_8_0_, denormaliz5_.REGISTRATION_LOCATION_FK as col_9_0_, locationim7_.LABEL as col_10_0_, locationim7_.NAME as col_11_0_, vesselimpl6_.STATUS_FK as col_12_0_, (select landingmea11_.QUALITATIVE_VALUE_FK from PUBLIC.LANDING_MEASUREMENT landingmea11_ where landingmea11_.LANDING_FK=landingimp0_.ID and landingmea11_.PMFM_FK=?) as col_13_0_, fishingtri1_.ID as col_14_0_, fishingtri1_.SYNCHRONIZATION_STATUS as col_15_0_, landingimp0_.OBSERVED_LOCATION_FK as col_16_0_, statusimpl10_.CODE as CODE1_159_, statusimpl10_.NAME as NAME2_159_, statusimpl10_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LANDING landingimp0_ left outer join PUBLIC.FISHING_TRIP fishingtri1_ on landingimp0_.FISHING_TRIP_FK=fishingtri1_.ID left outer join PUBLIC.OPERATION operations2_ on fishingtri1_.ID=operations2_.FISHING_TRIP_FK left outer join PUBLIC.GEAR_USE_FEATURES gearusefea3_ on operations2_.ID=gearusefea3_.OPERATION_FK left outer join PUBLIC.METIER metierimpl4_ on gearusefea3_.METIER_FK=metierimpl4_.ID, PUBLIC.VESSEL vesselimpl6_ inner join PUBLIC.STATUS statusimpl10_ on vesselimpl6_.STATUS_FK=statusimpl10_.CODE cross join PUBLIC.DENORMALIZED_VESSEL denormaliz5_, PUBLIC.LOCATION locationim7_ where landingimp0_.VESSEL_FK=vesselimpl6_.CODE and denormaliz5_.REGISTRATION_LOCATION_FK=locationim7_.ID and 1=1 and landingimp0_.OBSERVED_LOCATION_FK=? and ( operations2_.ID is null or operations2_.IS_MAIN_OPERATION=true and operations2_.START_DATE_TIME=fishingtri1_.DEPARTURE_DATE_TIME and operations2_.END_DATE_TIME=fishingtri1_.RETURN_DATE_TIME ) and denormaliz5_.VESSEL_FK=landingimp0_.VESSEL_FK order by landingimp0_.VESSEL_FK, fishingtri1_.RETURN_DATE_TIME ASC; SELECT DISTINCT la.id AS id, ft.returnDateTime AS landingDateTime, la.synchronizationStatus AS synchronizationStatus, la.vessel.code AS vesselCode, la.vessel.vesselType.id AS vesselTypeId, m.id AS metierId, dv.name as name, dv.registrationCode as registrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationLocation.id as registrationLocationId, dv.registrationLocation.label as registrationLocationLabel, dv.registrationLocation.name as registrationLocationName, la.vessel.status AS vesselStatus, (select lm.qualitativeValue.id from LandingMeasurementImpl lm where lm.landing.id=la.id and lm.pmfm.id=:pmfmIdVesselPortState) AS vesselPortStateQvId, ft.id AS fishingTripId, ft.synchronizationStatus AS fishingTripSynchronizationStatus, la.observedLocation.id FROM LandingImpl la left join la.fishingTrip ft left join ft.operations o left join o.gearUseFeatures guf left join guf.metier m, DenormalizedVesselImpl dv WHERE 1=1 AND la.observedLocation.id = :observedLocationId and (o.id is null OR (o.isMainOperation = true AND o.startDateTime = ft.departureDateTime AND o.endDateTime = ft.returnDateTime)) AND dv.vessel = la.vessel ORDER BY la.vessel.code, ft.returnDateTime ASC
Parameters
Name Type Description
pmfmIdVesselPortState java.lang.Integer  
observedLocationId java.lang.Integer  


Query updateVesselUseFeaturesTemporaryVessel

Replace temporary vessel in vessel use features

SQL query HQL query
update PUBLIC.VESSEL_USE_FEATURES set VESSEL_FK=? where VESSEL_FK=?; UPDATE VesselUseFeaturesImpl vuf SET vuf.vessel.code = :validVesselCode WHERE vuf.vessel.code = :tempVesselCode
Parameters
Name Type Description
tempVesselCode java.lang.String  
validVesselCode java.lang.String  


Query countTemporaryTaxonGroupUsedInProduce

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.PRODUCE produceimp0_ where produceimp0_.TAXON_GROUP_FK=?; SELECT count(*) FROM ProduceImpl p WHERE p.taxonGroup.id = :taxonGroupId
Parameters
Name Type Description
taxonGroupId java.lang.Integer  


Query unusedSaleProducesByFishingTrip

Get unused sales (that should be deleted) by fishing trip

SQL query HQL query
select produceimp0_.ID as ID1_118_, produceimp0_.IS_DISCARD as IS_DISCA2_118_, produceimp0_.SUBGROUP_COUNT as SUBGROUP3_118_, produceimp0_.INDIVIDUAL_COUNT as INDIVIDU4_118_, produceimp0_.TAXON_GROUP_OTHER_INFORMATION as TAXON_GR5_118_, produceimp0_.CONTROL_DATE as CONTROL_6_118_, produceimp0_.VALIDATION_DATE as VALIDATI7_118_, produceimp0_.QUALIFICATION_DATE as QUALIFIC8_118_, produceimp0_.QUALIFICATION_COMMENTS as QUALIFIC9_118_, produceimp0_.WEIGHT as WEIGHT10_118_, produceimp0_.COST as COST11_118_, produceimp0_.REMOTE_ID as REMOTE_12_118_, produceimp0_.OTHER_TAXON_GROUP_FK as OTHER_T13_118_, produceimp0_.TAXON_GROUP_FK as TAXON_G14_118_, produceimp0_.SALE_TYPE_FK as SALE_TY15_118_, produceimp0_.OTHER_GEAR_FK as OTHER_G16_118_, produceimp0_.DRESSING_FK as DRESSIN17_118_, produceimp0_.PRESERVATION_FK as PRESERV18_118_, produceimp0_.TRANSACTION_FK as TRANSAC19_118_, produceimp0_.FRESHNESS_CATEGORY_FK as FRESHNE20_118_, produceimp0_.SIZE_CATEGORY_FK as SIZE_CA21_118_, produceimp0_.WEIGHT_METHOD_FK as WEIGHT_22_118_, produceimp0_.FISHING_OPERATION_FK as FISHING23_118_, produceimp0_.EXPECTED_SALE_FK as EXPECTE24_118_, produceimp0_.BATCH_FK as BATCH_F25_118_, produceimp0_.BUYER_FK as BUYER_F26_118_, produceimp0_.SALE_FK as SALE_FK27_118_, produceimp0_.GEAR_FK as GEAR_FK28_118_, produceimp0_.LANDING_FK as LANDING29_118_, produceimp0_.TAKE_OVER_FK as TAKE_OV30_118_, produceimp0_.QUALITY_FLAG_FK as QUALITY31_118_, produceimp0_.TRANSSHIPMENT_FK as TRANSSH32_118_ from PUBLIC.PRODUCE produceimp0_ inner join PUBLIC.LANDING landingimp1_ on produceimp0_.LANDING_FK=landingimp1_.ID inner join PUBLIC.FISHING_TRIP fishingtri2_ on landingimp1_.FISHING_TRIP_FK=fishingtri2_.ID left outer join PUBLIC.TAXON_GROUP taxongroup3_ on produceimp0_.TAXON_GROUP_FK=taxongroup3_.ID where fishingtri2_.ID=? and ( produceimp0_.EXPECTED_SALE_FK is not null ) and not (exists (select produceimp4_.ID from PUBLIC.PRODUCE produceimp4_ left outer join PUBLIC.OPERATION fishingope5_ on produceimp4_.FISHING_OPERATION_FK=fishingope5_.ID left outer join PUBLIC.FISHING_TRIP fishingtri6_ on fishingope5_.FISHING_TRIP_FK=fishingtri6_.ID left outer join PUBLIC.LANDING landingimp7_ on produceimp4_.LANDING_FK=landingimp7_.ID left outer join PUBLIC.FISHING_TRIP fishingtri8_ on landingimp7_.FISHING_TRIP_FK=fishingtri8_.ID left outer join PUBLIC.TAXON_GROUP taxongroup9_ on produceimp4_.TAXON_GROUP_FK=taxongroup9_.ID where (fishingtri6_.ID=? or fishingtri8_.ID=?) and (produceimp4_.EXPECTED_SALE_FK is null) and taxongroup3_.ID=taxongroup9_.ID and coalesce((select producesor10_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor10_ where producesor10_.PRODUCE_FK=produceimp0_.ID and producesor10_.PMFM_FK=?), 0)=coalesce((select producesor11_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor11_ where producesor11_.PRODUCE_FK=produceimp4_.ID and producesor11_.PMFM_FK=?), 0) and coalesce(cast(produceimp0_.SIZE_CATEGORY_FK as integer), (select producesor12_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor12_ where producesor12_.PRODUCE_FK=produceimp0_.ID and producesor12_.PMFM_FK=?))=coalesce(cast(produceimp4_.SIZE_CATEGORY_FK as integer), (select producesor13_.QUALITATIVE_VALUE_FK from PUBLIC.SORTING_MEASUREMENT_P producesor13_ where producesor13_.PRODUCE_FK=produceimp4_.ID and producesor13_.PMFM_FK=?)))); SELECT p FROM ProduceImpl p inner join p.landing la inner join la.fishingTrip ft left join p.taxonGroup tg WHERE ft.id = :fishingTripId AND p.expectedSale is not null AND NOT exists ( SELECT p1.id FROM ProduceImpl p1 left join p1.fishingOperation o1 left join o1.fishingTrip o1_ft left join p1.landing l1 left join l1.fishingTrip l1_ft left join p1.taxonGroup tg1 WHERE (o1_ft.id = :fishingTripId OR l1_ft.id = :fishingTripId) AND p1.expectedSale is null AND tg.id = tg1.id AND coalesce((select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdProducePackaging), 0) = coalesce((select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p1.id and m.pmfm.id=:pmfmIdProducePackaging), 0) AND coalesce(cast(p.sizeCategory.id as int), (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p.id and m.pmfm.id=:pmfmIdSizeCategory)) = coalesce(cast(p1.sizeCategory.id as int), (select m.qualitativeValue.id from ProduceSortingMeasurementImpl m where m.produce.id=p1.id and m.pmfm.id=:pmfmIdSizeCategory)) )
Parameters
Name Type Description
pmfmIdProducePackaging java.lang.Integer  
fishingTripId java.lang.Integer  
pmfmIdSizeCategory java.lang.Integer  


Query oldDirtyObservedLocationsByPersonId

Return observed locations :
- older than : today - [NB year old]
- dirty
- user has access because :
* observed location without landing, but user is the recorder
* OR observed location without landing, but right from PersonSessionItem
* OR observedLocation with landings, and user has rights on one [vessel, program] during the observation period

SQL query HQL query
select distinct observedlo0_.ID as col_0_0_, observedlo0_.START_DATE_TIME as col_1_0_, observedlo0_.END_DATE_TIME as col_2_0_, observedlo0_.SAMPLING_STRATA_REFERENCE as col_3_0_, observedlo0_.COMMENTS as col_4_0_, observedlo0_.LOCATION_FK as col_5_0_, locationim2_.LABEL as col_6_0_, locationim2_.NAME as col_7_0_, observedlo0_.SYNCHRONIZATION_STATUS as col_8_0_, observedlo0_.PROGRAM_FK as col_9_0_, observedlo0_.RECORDER_PERSON_FK as col_10_0_ from PUBLIC.OBSERVED_LOCATION observedlo0_ left outer join PUBLIC.LANDING landings1_ on observedlo0_.ID=landings1_.OBSERVED_LOCATION_FK, PUBLIC.LOCATION locationim2_ where observedlo0_.LOCATION_FK=locationim2_.ID and observedlo0_.SYNCHRONIZATION_STATUS=? and observedlo0_.START_DATE_TIME=personsess6_.START_DATE_TIME ) ) ) order by observedlo0_.START_DATE_TIME desc; SELECT DISTINCT ol.id, ol.startDateTime, ol.endDateTime, ol.samplingStrataReference, ol.comments, ol.location.id AS locationId, ol.location.label AS locationLabel, ol.location.name AS locationName, ol.synchronizationStatus AS synchronizationStatus, ol.program.code AS programCode, ol.recorderPerson.id AS recorderPersonId FROM ObservedLocationImpl ol LEFT OUTER JOIN ol.landings l WHERE ol.synchronizationStatus = :dirtySynchronizationStatus AND ol.startDateTime < :nbYearOldDate AND ( (l.id is null AND ( (ol.remoteId is null AND ol.recorderPerson.id = :personId) OR EXISTS ( FROM PersonSessionItemImpl psi WHERE psi.objectId = ol.remoteId and psi.personSession.person.id = :personId and psi.program.code = ol.program.code and psi.objectType.code in (:objectTypes) ) ) ) OR (l.id is not null AND EXISTS ( FROM PersonSessionVesselImpl psv WHERE psv.vessel.code = l.vessel.code and psv.personSession.person.id = :personId and psv.program.code = ol.program.code and psv.objectType.code in (:objectTypes) AND NOT(ol.startDateTime > psv.endDateTime OR ol.endDateTime < psv.startDateTime) ) ) ) ORDER BY ol.startDateTime desc
Parameters
Name Type Description
nbYearOldDate java.util.Date  
personId java.lang.Integer  
dirtySynchronizationStatus java.lang.String  
objectTypes java.lang.String  


Query allTaxonNames

SQL query HQL query
select taxonnamei0_.REFERENCE_TAXON_FK as col_0_0_, taxonnamei0_.ID as col_1_0_, taxonnamei0_.IS_REFERENT as col_2_0_, taxonnamei0_.NAME as col_3_0_, taxonnamei0_.IS_TEMPORARY as col_4_0_ from PUBLIC.TAXON_NAME taxonnamei0_ where taxonnamei0_.IS_OBSOLETE=false order by taxonnamei0_.NAME; SELECT tn.referenceTaxon.id, tn.id, tn.isReferent, tn.name, tn.isTemporary FROM TaxonNameImpl tn WHERE tn.isObsolete = false ORDER BY tn.name


Query denormalizedVessels

SQL query HQL query
select vesselimpl0_.CODE as col_0_0_, vesselimpl0_.PROGRAM_FK as col_1_0_, vesselfeat3_.NAME as col_2_0_, vesselregi1_.INT_REGISTRATION_CODE as col_3_0_, vesselregi1_.REGISTRATION_CODE as col_4_0_, vesselregi1_.START_DATE_TIME as col_5_0_, vesselregi1_.END_DATE_TIME as col_6_0_, vesselregi1_.RANK_ORDER as col_7_0_, vesselfeat3_.EXTERIOR_MARKING as col_8_0_, locationim2_.ID as col_9_0_, locationhi6_.PARENT_LOCATION_FK as col_10_0_, vesselowne5_.VESSEL_OWNER_FK as col_11_0_, locationim4_.ID as col_12_0_ from PUBLIC.VESSEL vesselimpl0_ inner join PUBLIC.VESSEL_REGISTRATION_PERIOD vesselregi1_ on vesselimpl0_.CODE=vesselregi1_.VESSEL_FK inner join PUBLIC.LOCATION locationim2_ on vesselregi1_.REGISTRATION_LOCATION_FK=locationim2_.ID left outer join PUBLIC.VESSEL_FEATURES vesselfeat3_ on vesselimpl0_.CODE=vesselfeat3_.VESSEL_FK and ( vesselfeat3_.START_DATE_TIME<=? and ( vesselfeat3_.END_DATE_TIME is null or vesselfeat3_.END_DATE_TIME>=? ) ) left outer join PUBLIC.LOCATION locationim4_ on vesselfeat3_.BASE_PORT_LOCATION_FK=locationim4_.ID left outer join PUBLIC.VESSEL_OWNER_PERIOD vesselowne5_ on vesselimpl0_.CODE=vesselowne5_.VESSEL_FK and ( vesselowne5_.START_DATE_TIME<=? and ( vesselowne5_.END_DATE_TIME is null or vesselowne5_.END_DATE_TIME>=? ) ) cross join PUBLIC.LOCATION_HIERARCHY locationhi6_ cross join PUBLIC.LOCATION locationim7_ where locationhi6_.PARENT_LOCATION_FK=locationim7_.ID and 1=1 and ( vesselimpl0_.PROGRAM_FK in ( ? ) ) and ( ? is null or vesselimpl0_.CODE in ( ? ) ) and ( vesselimpl0_.STATUS_FK in ( ? , ? ) ) and vesselregi1_.START_DATE_TIME<=? and coalesce(vesselregi1_.END_DATE_TIME, ?)>=? and locationhi6_.CHILD_LOCATION_FK=locationim2_.ID and locationim7_.LOCATION_LEVEL_FK=?; SELECT v.code AS vesselCode, v.program.code AS programCode, vf.name AS name, vrp.internationalRegistrationCode as internationalRegistrationCode, vrp.registrationCode AS registrationCode, vrp.id.startDateTime AS registrationStartDateTime, vrp.endDateTime AS registrationEndDateTime, vrp.rankOrder AS rankOrder, vf.exteriorMarking AS exteriorMarking, l.id AS registrationLocationId, lh.id.parent.id AS registrationCountryId, vop.id.vesselOwner.id AS vesselOwnerId, bpl.id AS basePortLocationId FROM VesselImpl v INNER JOIN v.vesselRegistrationPeriods AS vrp INNER JOIN vrp.vesselRegistrationPeriodPk.registrationLocation l LEFT OUTER JOIN v.vesselFeatures AS vf with vf.startDateTime <= :refDate AND (vf.endDateTime is null OR vf.endDateTime >= :refDate) LEFT OUTER JOIN vf.basePortLocation AS bpl LEFT OUTER JOIN v.vesselOwnerPeriods AS vop with vop.id.startDateTime <= :refDate AND (vop.endDateTime is null OR vop.endDateTime >= :refDate), LocationHierarchyImpl lh WHERE 1=1 AND v.program.code IN (:programCodes) AND (:vesselCodes IS NULL OR v.code IN (:vesselCodes)) AND v.status.code IN (:statusValidCode, :statusTemporaryCode) AND vrp.id.startDateTime <= :refDate AND coalesce(vrp.endDateTime, :refDate) >= :refDate AND lh.id.location.id = l.id AND lh.id.parent.locationLevel.id = :countryLocationLevelId
Parameters
Name Type Description
statusValidCode java.lang.String  
countryLocationLevelId java.lang.Integer  
programCodes java.lang.String  
statusTemporaryCode java.lang.String  
refDate java.util.Date  
vesselCodes java.lang.String  


Query batchEntitiesByOperationIds

SQL query HQL query
select catchbatch0_.ID as ID1_12_, catchbatch0_.RANK_ORDER as RANK_ORD3_12_, catchbatch0_.SUBGROUP_COUNT as SUBGROUP4_12_, catchbatch0_.INDIVIDUAL_COUNT as INDIVIDU5_12_, catchbatch0_.CHILD_BATCHS_REPLICATION as CHILD_BA6_12_, catchbatch0_.EXHAUSTIVE_INVENTORY as EXHAUSTI7_12_, catchbatch0_.COMMENTS as COMMENTS8_12_, catchbatch0_.LABEL as LABEL9_12_, catchbatch0_.REMOTE_ID as REMOTE_10_12_, catchbatch0_.LOCATION_FK as LOCATIO11_12_, catchbatch0_.PARENT_BATCH_FK as PARENT_12_12_, catchbatch0_.QUALITY_FLAG_FK as QUALITY13_12_, catchbatch0_.SYNCHRONIZATION_STATUS as SYNCHRO14_12_, catchbatch0_.CONTROL_DATE as CONTROL15_12_, catchbatch0_.VALIDATION_DATE as VALIDAT16_12_, catchbatch0_.QUALIFICATION_DATE as QUALIFI17_12_, catchbatch0_.QUALIFICATION_COMMENTS as QUALIFI18_12_, catchbatch0_.UPDATE_DATE as UPDATE_19_12_ from PUBLIC.BATCH catchbatch0_ cross join PUBLIC.OPERATION fishingope1_ where catchbatch0_.IS_CATCH_BATCH=1 and catchbatch0_.ID=fishingope1_.CATCH_BATCH_FK and fishingope1_.IS_FISHING_OPERATION=1 and ( fishingope1_.ID in ( ? ) ); SELECT cb FROM CatchBatchImpl cb WHERE cb.fishingOperation.id IN (:operationIds)
Parameters
Name Type Description
operationIds java.lang.Integer  


Query countTemporaryMetierUsedInGUF

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.GEAR_USE_FEATURES gearusefea0_ where gearusefea0_.METIER_FK=?; SELECT count(*) FROM GearUseFeaturesImpl guf WHERE guf.metier.id = :metierId
Parameters
Name Type Description
metierId java.lang.Integer  


Query gearUseFeatureCountByVesselCode

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.GEAR_USE_FEATURES gearusefea0_ where gearusefea0_.VESSEL_FK=?; SELECT COUNT(*) FROM GearUseFeaturesImpl guf WHERE guf.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query allTaxonGroups

SQL query HQL query
select taxongroup0_.ID as col_0_0_, taxongroup0_.LABEL as col_1_0_, taxongroup0_.NAME as col_2_0_, taxongroup0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.TAXON_GROUP taxongroup0_ inner join PUBLIC.STATUS statusimpl1_ on taxongroup0_.STATUS_FK=statusimpl1_.CODE where ( taxongroup0_.STATUS_FK in ( ? , ? ) ) and taxongroup0_.TAXON_GROUP_TYPE_FK=?; SELECT t.id AS id, t.label AS label, t.name AS name, t.status AS status FROM TaxonGroupImpl t WHERE t.status.code IN (:statusValidCode, :statusTemporaryCode) AND t.taxonGroupType.code = :taxonGroupTypeCode
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
taxonGroupTypeCode java.lang.String  


Query metiersByFishingTripForPredocumentation

Get metiers used by vessel

SQL query HQL query
select distinct metierimpl1_.ID as col_0_0_, metierimpl1_.LABEL as col_1_0_, metierimpl1_.NAME as col_2_0_, metierimpl1_.STATUS_FK as col_3_0_, statusimpl2_.CODE as CODE1_159_, statusimpl2_.NAME as NAME2_159_, statusimpl2_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.GEAR_USE_FEATURES gearusefea0_ inner join PUBLIC.METIER metierimpl1_ on gearusefea0_.METIER_FK=metierimpl1_.ID inner join PUBLIC.STATUS statusimpl2_ on metierimpl1_.STATUS_FK=statusimpl2_.CODE cross join PUBLIC.OPERATION operationi3_ where gearusefea0_.OPERATION_FK=operationi3_.ID and ( metierimpl1_.STATUS_FK in ( ? , ? ) ) and gearusefea0_.VESSEL_FK=? and ( gearusefea0_.PROGRAM_FK in ( ? ) ) and ( ? is null or operationi3_.FISHING_TRIP_FK<>? ) and gearusefea0_.START_DATE<=? and coalesce(gearusefea0_.END_DATE, ?)>=? order by metierimpl1_.LABEL ASC; SELECT DISTINCT m.id, m.label, m.name, m.status FROM GearUseFeaturesImpl guf inner join guf.metier m WHERE m.status.code IN (:statusValidCode, :statusTemporaryCode) AND guf.vessel.code = :vesselCode AND guf.program.code IN (:programCodes) AND (:fishingTripId is null OR guf.operation.fishingTrip.id != :fishingTripId) AND NOT( guf.startDate > :endDate OR coalesce(guf.endDate, :startDate) < :startDate ) ORDER BY m.label ASC
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
programCodes java.lang.String  
endDate java.util.Date  
vesselCode java.lang.String  
fishingTripId java.lang.Integer  
startDate java.util.Date  


Query observedLocationsByProgramCodes

Get all observed location

SQL query HQL query
select observedlo0_.ID as col_0_0_, observedlo0_.START_DATE_TIME as col_1_0_, observedlo0_.END_DATE_TIME as col_2_0_, observedlo0_.SAMPLING_STRATA_REFERENCE as col_3_0_, observedlo0_.COMMENTS as col_4_0_, observedlo0_.LOCATION_FK as col_5_0_, locationim1_.LABEL as col_6_0_, locationim1_.NAME as col_7_0_, observedlo0_.SYNCHRONIZATION_STATUS as col_8_0_, observedlo0_.PROGRAM_FK as col_9_0_, observedlo0_.RECORDER_PERSON_FK as col_10_0_ from PUBLIC.OBSERVED_LOCATION observedlo0_, PUBLIC.LOCATION locationim1_ where observedlo0_.LOCATION_FK=locationim1_.ID and ( observedlo0_.PROGRAM_FK in ( ? ) ) and ( ?=true or observedlo0_.SYNCHRONIZATION_STATUS in ( ? ) ) order by observedlo0_.START_DATE_TIME desc; SELECT ol.id, ol.startDateTime, ol.endDateTime, ol.samplingStrataReference, ol.comments, ol.location.id AS locationId, ol.location.label AS locationLabel, ol.location.name AS locationName, ol.synchronizationStatus AS synchronizationStatus, ol.program.code AS programCode, ol.recorderPerson.id AS recorderPersonId FROM ObservedLocationImpl ol WHERE ol.program.code IN (:programCodes) AND ( :ignoreSynchronizationStatus = true OR ol.synchronizationStatus IN (:synchronizationStatusCodes) ) ORDER BY ol.startDateTime desc
Parameters
Name Type Description
synchronizationStatusCodes java.lang.String  
programCodes java.lang.String  
ignoreSynchronizationStatus java.lang.Boolean  


Query updateLandingsSynchronizationStatus

Update synchronization status of a list of observed location

SQL query HQL query
update PUBLIC.LANDING set SYNCHRONIZATION_STATUS=? where ( ID in ( ? ) ) and SYNCHRONIZATION_STATUS<>? and ( ? is null or SYNCHRONIZATION_STATUS=? ); UPDATE LandingImpl la SET la.synchronizationStatus = :newSynchronizationStatus WHERE la.id IN (:landingIds) AND la.synchronizationStatus != :newSynchronizationStatus AND (:oldSynchronizationStatusFilter is null OR la.synchronizationStatus = :oldSynchronizationStatusFilter)
Parameters
Name Type Description
landingIds java.lang.String  
newSynchronizationStatus java.lang.String  
oldSynchronizationStatusFilter java.lang.String  


Query catchBatchOnly

SQL query HQL query
select catchbatch0_.ID as ID1_12_0_, quantifica1_.ID as ID1_130_1_, catchbatch0_.RANK_ORDER as RANK_ORD3_12_0_, catchbatch0_.SUBGROUP_COUNT as SUBGROUP4_12_0_, catchbatch0_.INDIVIDUAL_COUNT as INDIVIDU5_12_0_, catchbatch0_.CHILD_BATCHS_REPLICATION as CHILD_BA6_12_0_, catchbatch0_.EXHAUSTIVE_INVENTORY as EXHAUSTI7_12_0_, catchbatch0_.COMMENTS as COMMENTS8_12_0_, catchbatch0_.LABEL as LABEL9_12_0_, catchbatch0_.REMOTE_ID as REMOTE_10_12_0_, catchbatch0_.LOCATION_FK as LOCATIO11_12_0_, catchbatch0_.PARENT_BATCH_FK as PARENT_12_12_0_, catchbatch0_.QUALITY_FLAG_FK as QUALITY13_12_0_, catchbatch0_.SYNCHRONIZATION_STATUS as SYNCHRO14_12_0_, catchbatch0_.CONTROL_DATE as CONTROL15_12_0_, catchbatch0_.VALIDATION_DATE as VALIDAT16_12_0_, catchbatch0_.QUALIFICATION_DATE as QUALIFI17_12_0_, catchbatch0_.QUALIFICATION_COMMENTS as QUALIFI18_12_0_, catchbatch0_.UPDATE_DATE as UPDATE_19_12_0_, quantifica1_.NUMERICAL_VALUE as NUMERICA2_130_1_, quantifica1_.ALPHANUMERICAL_VALUE as ALPHANUM3_130_1_, quantifica1_.DIGIT_COUNT as DIGIT_CO4_130_1_, quantifica1_.PRECISION_VALUE as PRECISIO5_130_1_, quantifica1_.CONTROL_DATE as CONTROL_6_130_1_, quantifica1_.VALIDATION_DATE as VALIDATI7_130_1_, quantifica1_.QUALIFICATION_DATE as QUALIFIC8_130_1_, quantifica1_.QUALIFICATION_COMMENTS as QUALIFIC9_130_1_, quantifica1_.REMOTE_ID as REMOTE_10_130_1_, quantifica1_.AGGREGATION_LEVEL_FK as AGGREGA11_130_1_, quantifica1_.QUALITY_FLAG_FK as QUALITY12_130_1_, quantifica1_.PRECISION_TYPE_FK as PRECISI13_130_1_, quantifica1_.ANALYSIS_INSTRUMENT_FK as ANALYSI14_130_1_, quantifica1_.NUMERICAL_PRECISION_FK as NUMERIC15_130_1_, quantifica1_.DEPARTMENT_FK as DEPARTM16_130_1_, quantifica1_.PMFM_FK as PMFM_FK17_130_1_, quantifica1_.QUALITATIVE_VALUE_FK as QUALITA18_130_1_, quantifica1_.SUBGROUP_NUMBER as SUBGROU19_130_1_, quantifica1_.IS_REFERENCE_QUANTIFICATION as IS_REFE20_130_1_, quantifica1_.BATCH_FK as BATCH_F21_130_1_, quantifica1_.BATCH_FK as BATCH_F21_130_0__, quantifica1_.ID as ID1_130_0__ from PUBLIC.BATCH catchbatch0_ left outer join PUBLIC.QUANTIFICATION_MEASUREMENT quantifica1_ on catchbatch0_.ID=quantifica1_.BATCH_FK where catchbatch0_.IS_CATCH_BATCH=1 and catchbatch0_.ID=? and ( quantifica1_.ID is null or quantifica1_.IS_REFERENCE_QUANTIFICATION=true ) order by quantifica1_.BATCH_FK; SELECT cb FROM CatchBatchImpl cb left join fetch cb.quantificationMeasurements qm WHERE cb.id=:catchBatchId AND ( qm is null OR qm.isReferenceQuantification=true )
Parameters
Name Type Description
catchBatchId java.lang.Integer  


Query pmfmById

Get a pmfm caracteristics by this id

SQL query HQL query
select pmfmimpl0_.ID as col_0_0_, pmfmimpl0_.PARAMETER_FK as col_1_0_, parameteri1_.NAME as col_2_0_, matriximpl2_.NAME as col_3_0_, fractionim3_.NAME as col_4_0_, methodimpl4_.NAME as col_5_0_, parameteri1_.IS_ALPHANUMERIC as col_6_0_, parameteri1_.IS_QUALITATIVE as col_7_0_, pmfmimpl0_.SIGNIF_FIGURES_NUMBER as col_8_0_, pmfmimpl0_.MAXIMUM_NUMBER_DECIMALS as col_9_0_, pmfmimpl0_.PRECISION as col_10_0_, case when pmfmimpl0_.UNIT_FK=? then '' else unitimpl7_.SYMBOL end as col_11_0_, pmfmimpl0_.STATUS_FK as col_12_0_, statusimpl8_.CODE as CODE1_159_, statusimpl8_.NAME as NAME2_159_, statusimpl8_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.PMFM pmfmimpl0_, PUBLIC.PARAMETER parameteri1_, PUBLIC.MATRIX matriximpl2_, PUBLIC.FRACTION fractionim3_, PUBLIC.METHOD methodimpl4_, PUBLIC.UNIT unitimpl7_ inner join PUBLIC.STATUS statusimpl8_ on pmfmimpl0_.STATUS_FK=statusimpl8_.CODE where pmfmimpl0_.PARAMETER_FK=parameteri1_.CODE and pmfmimpl0_.MATRIX_FK=matriximpl2_.ID and pmfmimpl0_.FRACTION_FK=fractionim3_.ID and pmfmimpl0_.METHOD_FK=methodimpl4_.ID and pmfmimpl0_.UNIT_FK=unitimpl7_.ID and pmfmimpl0_.ID=?; SELECT p.id AS pmfmId, p.parameter.code AS parameterCode, p.parameter.name AS parameterName, p.matrix.name AS matrixName, p.fraction.name AS fractionName, p.method.name AS methodName, p.parameter.isAlphanumeric AS isAlphanumeric, p.parameter.isQualitative AS isQualitative, p.signifFiguresNumber, p.maximumNumberDecimals, p.precision, case when (p.unit.id = :unitIdNone) then '' else p.unit.symbol end AS symbol, p.status AS status FROM PmfmImpl p WHERE p.id= :pmfmId
Parameters
Name Type Description
pmfmId java.lang.Integer  
unitIdNone java.lang.Integer  


Query measurementFile

SQL query HQL query
select measuremen0_.OBJECT_TYPE_FK as col_0_0_, measuremen0_.OBJECT_ID as col_1_0_, measuremen0_.ID as col_2_0_, measuremen0_.PATH as col_3_0_, measuremen0_.NAME as col_4_0_, measuremen0_.COMMENTS as col_5_0_ from PUBLIC.MEASUREMENT_FILE measuremen0_ where measuremen0_.ID=?; SELECT m.objectType.code as attachmentObjectType, m.objectId AS attachmentObjectId, m.id AS attachmentId, m.path AS attachmentPath, m.name AS attachmentName, m.comments as attachmentComment FROM MeasurementFileImpl m WHERE m.id = :attachmentId
Parameters
Name Type Description
measurementFileId java.lang.Integer  


Query locationsByLevel

Get a location by a level

SQL query HQL query
select locationim0_.ID as col_0_0_, locationim0_.LABEL as col_1_0_, locationim0_.NAME as col_2_0_, locationim0_.STATUS_FK as col_3_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LOCATION locationim0_ inner join PUBLIC.STATUS statusimpl1_ on locationim0_.STATUS_FK=statusimpl1_.CODE where ( locationim0_.STATUS_FK in ( ? , ? ) ) and locationim0_.LOCATION_LEVEL_FK=?; SELECT l.id as locationId, l.label as locationLabel, l.name as locationName, l.status as status FROM LocationImpl l WHERE l.status.code IN (:statusValidCode, :statusTemporaryCode) AND l.locationLevel.id = :locationLevelId
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
locationLevelId java.lang.Integer  


Query regionalizedItemsByChildLocations

SQL query HQL query
select distinct spatialite0_.OBJECT_ID as col_0_0_, spatialite1_.LOCALIZED_NAME as col_1_0_ from PUBLIC.SPATIAL_ITEM spatialite0_ inner join PUBLIC.SPATIAL_ITEM2LOCATION spatialite1_ on spatialite0_.ID=spatialite1_.SPATIAL_ITEM_FK cross join PUBLIC.LOCATION_HIERARCHY locationhi2_ where spatialite0_.SPATIAL_ITEM_TYPE_FK=? and ( locationhi2_.CHILD_LOCATION_FK in ( ? ) ) and spatialite1_.LOCATION_FK=locationhi2_.PARENT_LOCATION_FK; SELECT DISTINCT si.objectId, i2l.localizedName FROM SpatialItemImpl si INNER JOIN si.spatialItem2Locations i2l, LocationHierarchyImpl lh WHERE si.spatialItemType.id = :spatialItemType AND lh.id.location.id IN (:childLocationIds) AND i2l.id.location.id = lh.id.parent.id
Parameters
Name Type Description
childLocationIds java.lang.String  
spatialItemType java.lang.Integer  


Query availableVesselCodesByPersonId

SQL query HQL query
select distinct denormaliz0_.VESSEL_FK as col_0_0_ from PUBLIC.DENORMALIZED_VESSEL denormaliz0_ cross join PUBLIC.PERSON_SESSION_VESSEL personsess1_ inner join PUBLIC.PERSON_SESSION personsess2_ on personsess1_.PERSON_SESSION_FK=personsess2_.ID where personsess1_.VESSEL_FK=denormaliz0_.VESSEL_FK and personsess2_.PERSON_FK=? and personsess1_.PROGRAM_FK=? and personsess1_.WRITE_DATA=true; SELECT DISTINCT dv.vessel.code FROM DenormalizedVesselImpl dv, PersonSessionVesselImpl psv INNER JOIN psv.personSession ps WHERE psv.vessel.code = dv.vessel.code AND ps.person.id = :personId AND psv.program.code = :programCode AND psv.writeData = true
Parameters
Name Type Description
programCode java.lang.String  
personId java.lang.Integer  


Query countPersonSessionByPersonId

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.PERSON_SESSION personsess0_ where personsess0_.PERSON_FK=?; SELECT COUNT(*) FROM PersonSessionImpl ps WHERE ps.person.id = :personId
Parameters
Name Type Description
personId java.lang.Integer  


Query findDuplicateCalendarsById

Get duplicate calendar corresponding to a natural key

SQL query HQL query
select dailyactiv0_.ID as col_0_0_ from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv0_ where dailyactiv0_.ID<>? and dailyactiv0_.PROGRAM_FK=( select dailyactiv1_.PROGRAM_FK from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv1_ where dailyactiv1_.ID=? ) and dailyactiv0_.VESSEL_FK=( select dailyactiv2_.VESSEL_FK from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv2_ where dailyactiv2_.ID=? ) and dailyactiv0_.START_DATE=( select dailyactiv3_.START_DATE from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv3_ where dailyactiv3_.ID=? ) and dailyactiv0_.END_DATE=( select dailyactiv4_.END_DATE from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv4_ where dailyactiv4_.ID=? ) order by dailyactiv0_.ID; SELECT ac.id AS id FROM DailyActivityCalendarImpl ac WHERE ac.id != :calendarId AND ac.program.id = (SELECT program.id FROM DailyActivityCalendarImpl WHERE id = :calendarId) AND ac.vessel.code = (SELECT vessel.code FROM DailyActivityCalendarImpl WHERE id = :calendarId) AND ac.startDate = (SELECT startDate FROM DailyActivityCalendarImpl WHERE id = :calendarId) AND ac.endDate = (SELECT endDate FROM DailyActivityCalendarImpl WHERE id = :calendarId) ORDER BY ac.id
Parameters
Name Type Description
calendarId java.lang.Integer  


Query deleteVesselFeatures

Delete vessel features for a specific vessel

SQL query HQL query
delete from PUBLIC.VESSEL_FEATURES where VESSEL_FK=?; DELETE FROM VesselFeaturesImpl vf WHERE vf.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query personByLogin

Get a person (with a temporary or valid status) corresponding to the given login (Intranet or Extranet)

SQL query HQL query
select distinct personimpl0_.ID as ID1_102_, personimpl0_.LASTNAME as LASTNAME2_102_, personimpl0_.FIRSTNAME as FIRSTNAM3_102_, personimpl0_.ADDRESS as ADDRESS4_102_, personimpl0_.CREATION_DATE as CREATION5_102_, personimpl0_.PHONE_NUMBER as PHONE_NU6_102_, personimpl0_.MOBILE_NUMBER as MOBILE_N7_102_, personimpl0_.FAX_NUMBER as FAX_NUMB8_102_, personimpl0_.EMAIL as EMAIL9_102_, personimpl0_.UPDATE_DATE as UPDATE_10_102_, personimpl0_.CRYPT_PASSWORD as CRYPT_P11_102_, personimpl0_.EMPLOYEE_NUMBER as EMPLOYE12_102_, personimpl0_.USERNAME as USERNAM13_102_, personimpl0_.USERNAME_EXTRANET as USERNAM14_102_, personimpl0_.STATUS_FK as STATUS_15_102_, personimpl0_.DEPARTMENT_FK as DEPARTM16_102_ from PUBLIC.PERSON personimpl0_ where ( personimpl0_.STATUS_FK in ( ? , ? ) ) and ( personimpl0_.USERNAME=? or personimpl0_.USERNAME_EXTRANET=? ); SELECT DISTINCT p FROM PersonImpl p WHERE p.status.code IN (:statusValidCode, :statusTemporaryCode) AND (p.username = :personLogin OR p.usernameExtranet = :personLogin)
Parameters
Name Type Description
personLogin java.lang.String  
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  


Query cleanNegativeRemoteIdFromObservedLocation

SQL query HQL query
update PUBLIC.OBSERVED_LOCATION set REMOTE_ID=null where REMOTE_ID<0; UPDATE ObservedLocationImpl SET remoteId = null WHERE remoteId < 0


Query observersByObservedLocationId

Get observers location by ObservedLocationId

SQL query HQL query
select personimpl2_.ID as col_0_0_, personimpl2_.LASTNAME as col_1_0_, personimpl2_.FIRSTNAME as col_2_0_, department3_.CODE as col_3_0_, personimpl2_.STATUS_FK as col_4_0_, statusimpl4_.CODE as CODE1_159_, statusimpl4_.NAME as NAME2_159_, statusimpl4_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.OBSERVED_LOCATION observedlo0_ inner join PUBLIC.OBSERVED_LOCATION2PERSON observers1_ on observedlo0_.ID=observers1_.OBSERVED_LOCATION_FK inner join PUBLIC.PERSON personimpl2_ on observers1_.OBSERVER_PERSON_FK=personimpl2_.ID, PUBLIC.DEPARTMENT department3_ inner join PUBLIC.STATUS statusimpl4_ on personimpl2_.STATUS_FK=statusimpl4_.CODE where personimpl2_.DEPARTMENT_FK=department3_.ID and observedlo0_.ID=?; SELECT p.id, p.lastname AS lastname, p.firstname AS firstname, p.department.code AS departmentCode, p.status FROM ObservedLocationImpl ol join ol.observers p WHERE ol.id = :observedLocationId
Parameters
Name Type Description
observedLocationId java.lang.Integer  


Query landingsForPredocumentation

Get vessels previously observed on site (predocumentation on 12 months) on all ObsDeb data collecting programs.
- endDate: last second of day, of the the observation end date
- startDate: endDate - 12 months
- observedLocationId: the current observation identifier
- programCodes: all ObsDeb program codes

SQL query HQL query
select distinct landingimp0_.SYNCHRONIZATION_STATUS as col_0_0_, landingimp0_.VESSEL_FK as col_1_0_, vesselimpl2_.VESSEL_TYPE_FK as col_2_0_, denormaliz1_.NAME as col_3_0_, denormaliz1_.REGISTRATION_CODE as col_4_0_, denormaliz1_.INT_REGISTRATION_CODE as col_5_0_, denormaliz1_.REGISTRATION_LOCATION_FK as col_6_0_, locationim3_.LABEL as col_7_0_, locationim3_.NAME as col_8_0_, vesselimpl2_.STATUS_FK as col_9_0_, statusimpl6_.CODE as CODE1_159_, statusimpl6_.NAME as NAME2_159_, statusimpl6_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LANDING landingimp0_, PUBLIC.VESSEL vesselimpl2_ inner join PUBLIC.STATUS statusimpl6_ on vesselimpl2_.STATUS_FK=statusimpl6_.CODE cross join PUBLIC.DENORMALIZED_VESSEL denormaliz1_, PUBLIC.LOCATION locationim3_ where landingimp0_.VESSEL_FK=vesselimpl2_.CODE and denormaliz1_.REGISTRATION_LOCATION_FK=locationim3_.ID and 1=1 and landingimp0_.OBSERVED_LOCATION_FK<>? and landingimp0_.LANDING_LOCATION_FK=? and ( landingimp0_.PROGRAM_FK in ( ? ) ) and landingimp0_.LANDING_DATE_TIME>=? and landingimp0_.LANDING_DATE_TIME<=? and denormaliz1_.VESSEL_FK=landingimp0_.VESSEL_FK order by landingimp0_.VESSEL_FK ASC; SELECT DISTINCT la.synchronizationStatus AS synchronizationStatus, la.vessel.code AS vesselCode, la.vessel.vesselType.id AS vesselTypeId, dv.name as name, dv.registrationCode as registrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationLocation.id as registrationLocationId, dv.registrationLocation.label as registrationLocationLabel, dv.registrationLocation.name as registrationLocationName, la.vessel.status AS vesselStatus FROM LandingImpl la, DenormalizedVesselImpl dv WHERE 1=1 AND la.observedLocation.id != :observedLocationId AND la.landingLocation.id = :landingLocationId AND la.program.code IN (:programCodes) AND la.landingDateTime >= :startDate AND la.landingDateTime <= :endDate AND dv.vessel = la.vessel ORDER BY la.vessel.code ASC
Parameters
Name Type Description
observedLocationId java.lang.Integer  
programCodes java.lang.String  
endDate java.util.Date  
startDate java.util.Date  
landingLocationId java.lang.Integer  


Query deleteGearPhysicalMeasurementByFeatures

SQL query HQL query
delete from PUBLIC.GEAR_PHYSICAL_MEASUREMENT where GEAR_PHYSICAL_FEATURES_FK in ( ? ); DELETE FROM GearPhysicalMeasurementImpl m WHERE m.gearPhysicalFeatures.id IN (:gearPhysicalFeaturesIds)
Parameters
Name Type Description
gearPhysicalFeaturesIds java.lang.String  


Query lightDailyActivityCalendarById

Get a daily activity calendar by its id

SQL query HQL query
select dailyactiv0_.ID as col_0_0_, dailyactiv0_.VESSEL_FK as col_1_0_, dailyactiv0_.START_DATE as col_2_0_, dailyactiv0_.END_DATE as col_3_0_, dailyactiv0_.SYNCHRONIZATION_STATUS as col_4_0_ from PUBLIC.DAILY_ACTIVITY_CALENDAR dailyactiv0_ where dailyactiv0_.ID=?; SELECT ac.id AS dailyACtivityCalendarId, ac.vessel.code AS vesselCode, ac.startDate AS startDate, ac.endDate AS endDate, ac.synchronizationStatus FROM DailyActivityCalendarImpl ac WHERE ac.id = :calendarId
Parameters
Name Type Description
calendarId java.lang.Integer  


Query deleteAllDenormalizedVessels

SQL query HQL query
delete from PUBLIC.DENORMALIZED_VESSEL where PROGRAM_FK in ( ? ); DELETE FROM DenormalizedVesselImpl dv WHERE dv.program.code IN (:programCodes)
Parameters
Name Type Description
programCodes java.lang.String  


Query removeOperationGearPhysicalFeatures

SQL query HQL query
update PUBLIC.OPERATION set GEAR_PHYSICAL_FEATURES_FK=null where GEAR_PHYSICAL_FEATURES_FK in ( ? ); UPDATE OperationImpl o SET o.gearPhysicalFeatures = null WHERE o.gearPhysicalFeatures.id IN (:gearPhysicalFeaturesIds)
Parameters
Name Type Description
gearPhysicalFeaturesIds java.lang.String  


Query updateObservedLocationSynchronizationStatus

Update synchronization status of a list of observed location

SQL query HQL query
update PUBLIC.OBSERVED_LOCATION set SYNCHRONIZATION_STATUS=? where ( ID in ( ? ) ) and SYNCHRONIZATION_STATUS<>?; UPDATE ObservedLocationImpl ol SET ol.synchronizationStatus = :synchronizationStatus WHERE ol.id IN (:observedLocationIds) AND ol.synchronizationStatus != :synchronizationStatus
Parameters
Name Type Description
observedLocationIds java.lang.String  
synchronizationStatus java.lang.String  


Query deleteDenormalizedVessels

SQL query HQL query
delete from PUBLIC.DENORMALIZED_VESSEL where ( PROGRAM_FK in ( ? ) ) and ( VESSEL_FK in ( ? ) ); DELETE FROM DenormalizedVesselImpl dv WHERE dv.program.code IN (:programCodes) AND dv.vessel.code IN (:vesselCodes)
Parameters
Name Type Description
programCodes java.lang.String  
vesselCodes java.lang.String  


Query denormalizedVesselByRegistrationCodeOrNameOrTypeAndParentLocation

SQL query HQL query
select distinct denormaliz1_.VESSEL_FK as col_0_0_, denormaliz1_.REGISTRATION_CODE as col_1_0_, denormaliz1_.INT_REGISTRATION_CODE as col_2_0_, denormaliz1_.REGISTRATION_START_DATE as col_3_0_, denormaliz1_.REGISTRATION_END_DATE as col_4_0_, denormaliz1_.NAME as col_5_0_, vesselimpl2_.VESSEL_TYPE_FK as col_6_0_, vesseltype4_.NAME as col_7_0_, denormaliz1_.REGISTRATION_COUNTRY_FK as col_8_0_, locationim5_.LABEL as col_9_0_, locationim5_.NAME as col_10_0_, locationim5_.STATUS_FK as col_11_0_, denormaliz1_.REGISTRATION_LOCATION_FK as col_12_0_, locationim9_.LABEL as col_13_0_, locationim9_.NAME as col_14_0_, locationim9_.STATUS_FK as col_15_0_, denormaliz1_.BASE_PORT_LOCATION_FK as col_16_0_, locationim13_.LABEL as col_17_0_, locationim13_.NAME as col_18_0_, locationim13_.STATUS_FK as col_19_0_, vesselimpl2_.STATUS_FK as col_20_0_, statusimpl8_.CODE as CODE1_159_0_, statusimpl12_.CODE as CODE1_159_1_, statusimpl16_.CODE as CODE1_159_2_, statusimpl18_.CODE as CODE1_159_3_, statusimpl8_.NAME as NAME2_159_0_, statusimpl8_.UPDATE_DATE as UPDATE_D3_159_0_, statusimpl12_.NAME as NAME2_159_1_, statusimpl12_.UPDATE_DATE as UPDATE_D3_159_1_, statusimpl16_.NAME as NAME2_159_2_, statusimpl16_.UPDATE_DATE as UPDATE_D3_159_2_, statusimpl18_.NAME as NAME2_159_3_, statusimpl18_.UPDATE_DATE as UPDATE_D3_159_3_ from PUBLIC.LOCATION_HIERARCHY locationhi0_ cross join PUBLIC.DENORMALIZED_VESSEL denormaliz1_, PUBLIC.VESSEL vesselimpl2_, PUBLIC.VESSEL_TYPE vesseltype4_ inner join PUBLIC.STATUS statusimpl18_ on vesselimpl2_.STATUS_FK=statusimpl18_.CODE, PUBLIC.LOCATION locationim5_ inner join PUBLIC.STATUS statusimpl8_ on locationim5_.STATUS_FK=statusimpl8_.CODE, PUBLIC.LOCATION locationim9_ inner join PUBLIC.STATUS statusimpl12_ on locationim9_.STATUS_FK=statusimpl12_.CODE, PUBLIC.LOCATION locationim13_ inner join PUBLIC.STATUS statusimpl16_ on locationim13_.STATUS_FK=statusimpl16_.CODE where denormaliz1_.VESSEL_FK=vesselimpl2_.CODE and vesselimpl2_.VESSEL_TYPE_FK=vesseltype4_.ID and denormaliz1_.REGISTRATION_COUNTRY_FK=locationim5_.ID and denormaliz1_.REGISTRATION_LOCATION_FK=locationim9_.ID and denormaliz1_.BASE_PORT_LOCATION_FK=locationim13_.ID and denormaliz1_.BASE_PORT_LOCATION_FK=locationhi0_.CHILD_LOCATION_FK and locationhi0_.PARENT_LOCATION_FK=? and ( ? is null or upper(denormaliz1_.NAME) like ('%'||?||'%') ) and ( ? is null or upper(denormaliz1_.REGISTRATION_CODE) like ('%'||?||'%') or upper(denormaliz1_.INT_REGISTRATION_CODE) like ('%'||?||'%') ) and ( vesselimpl2_.VESSEL_TYPE_FK in ( ? ) ) and ( vesselimpl2_.STATUS_FK in ( ? ) ) order by denormaliz1_.VESSEL_FK; SELECT DISTINCT dv.vessel.code AS vesselCode, dv.registrationCode AS nationalRegistrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationStartDate AS registrationStartDate, dv.registrationEndDate AS registrationEndDate, dv.name AS vesselName, dv.vessel.vesselType.id AS vesselTypeId, dv.vessel.vesselType.name AS vesselTypeName, dv.registrationCountry.id AS countryLocationId, dv.registrationCountry.label AS countryLocationLabel, dv.registrationCountry.name AS countryLocationName, dv.registrationCountry.status AS countryLocationStatus, dv.registrationLocation.id AS registrationLocationId, dv.registrationLocation.label AS registrationLocationLabel, dv.registrationLocation.name AS registrationLocationName, dv.registrationLocation.status AS registrationLocationStatus, dv.basePortLocation.id AS basePortLocationId, dv.basePortLocation.label AS basePortLocationLabel, dv.basePortLocation.name AS basePortLocationName, dv.basePortLocation.status AS basePortLocationStatus, dv.vessel.status AS status FROM LocationHierarchyImpl lh, DenormalizedVesselImpl dv WHERE dv.basePortLocation.id = lh.id.location.id AND lh.id.parent.id = :portParentLocationId AND ( :name is null OR (upper(dv.name) like '%' || :name || '%') ) AND ( :registrationCode is null OR (upper(dv.registrationCode) like '%' || :registrationCode || '%') OR (upper(dv.internationalRegistrationCode) like '%' || :registrationCode || '%') ) AND dv.vessel.vesselType.id IN (:vesselTypeIds) AND dv.vessel.status.code IN (:statusCodes) ORDER BY dv.vessel.code
Parameters
Name Type Description
statusValidCode java.lang.String  
portParentLocationId java.lang.Integer  
statusTemporaryCode java.lang.String  
name java.lang.String  
registrationCode java.lang.String  
vesselTypeIds java.lang.String  


Query denormalizedVesselByRegistrationCodes

SQL query HQL query
select distinct denormaliz0_.VESSEL_FK as col_0_0_, denormaliz0_.REGISTRATION_CODE as col_1_0_, denormaliz0_.INT_REGISTRATION_CODE as col_2_0_, denormaliz0_.REGISTRATION_START_DATE as col_3_0_, denormaliz0_.REGISTRATION_END_DATE as col_4_0_, denormaliz0_.NAME as col_5_0_, vesselimpl3_.VESSEL_TYPE_FK as col_6_0_, vesseltype5_.NAME as col_7_0_, denormaliz0_.REGISTRATION_COUNTRY_FK as col_8_0_, locationim6_.LABEL as col_9_0_, locationim6_.NAME as col_10_0_, locationim6_.STATUS_FK as col_11_0_, denormaliz0_.REGISTRATION_LOCATION_FK as col_12_0_, locationim10_.LABEL as col_13_0_, locationim10_.NAME as col_14_0_, locationim10_.STATUS_FK as col_15_0_, locationim1_.ID as col_16_0_, locationim1_.LABEL as col_17_0_, locationim1_.NAME as col_18_0_, statusimpl2_.CODE as col_19_0_, vesselimpl3_.STATUS_FK as col_20_0_, statusimpl9_.CODE as CODE1_159_0_, statusimpl13_.CODE as CODE1_159_1_, statusimpl2_.CODE as CODE1_159_2_, statusimpl15_.CODE as CODE1_159_3_, statusimpl9_.NAME as NAME2_159_0_, statusimpl9_.UPDATE_DATE as UPDATE_D3_159_0_, statusimpl13_.NAME as NAME2_159_1_, statusimpl13_.UPDATE_DATE as UPDATE_D3_159_1_, statusimpl2_.NAME as NAME2_159_2_, statusimpl2_.UPDATE_DATE as UPDATE_D3_159_2_, statusimpl15_.NAME as NAME2_159_3_, statusimpl15_.UPDATE_DATE as UPDATE_D3_159_3_ from PUBLIC.DENORMALIZED_VESSEL denormaliz0_ left outer join PUBLIC.LOCATION locationim1_ on denormaliz0_.BASE_PORT_LOCATION_FK=locationim1_.ID left outer join PUBLIC.STATUS statusimpl2_ on locationim1_.STATUS_FK=statusimpl2_.CODE, PUBLIC.VESSEL vesselimpl3_, PUBLIC.VESSEL_TYPE vesseltype5_ inner join PUBLIC.STATUS statusimpl15_ on vesselimpl3_.STATUS_FK=statusimpl15_.CODE, PUBLIC.LOCATION locationim6_ inner join PUBLIC.STATUS statusimpl9_ on locationim6_.STATUS_FK=statusimpl9_.CODE, PUBLIC.LOCATION locationim10_ inner join PUBLIC.STATUS statusimpl13_ on locationim10_.STATUS_FK=statusimpl13_.CODE where denormaliz0_.VESSEL_FK=vesselimpl3_.CODE and vesselimpl3_.VESSEL_TYPE_FK=vesseltype5_.ID and denormaliz0_.REGISTRATION_COUNTRY_FK=locationim6_.ID and denormaliz0_.REGISTRATION_LOCATION_FK=locationim10_.ID and ( upper(denormaliz0_.REGISTRATION_CODE) in ( ? ) or upper(denormaliz0_.INT_REGISTRATION_CODE) in ( ? ) ) and ( vesselimpl3_.VESSEL_TYPE_FK in ( ? ) ) and ( vesselimpl3_.STATUS_FK in ( ? , ? ) ) order by denormaliz0_.VESSEL_FK; SELECT DISTINCT dv.vessel.code AS vesselCode, dv.registrationCode AS nationalRegistrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationStartDate AS registrationStartDate, dv.registrationEndDate AS registrationEndDate, dv.name AS vesselName, dv.vessel.vesselType.id AS vesselTypeId, dv.vessel.vesselType.name AS vesselTypeName, dv.registrationCountry.id AS countryLocationId, dv.registrationCountry.label AS countryLocationLabel, dv.registrationCountry.name AS countryLocationName, dv.registrationCountry.status AS countryLocationStatus, dv.registrationLocation.id AS registrationLocationId, dv.registrationLocation.label AS registrationLocationLabel, dv.registrationLocation.name AS registrationLocationName, dv.registrationLocation.status AS registrationLocationStatus, port.id AS basePortLocationId, port.label AS basePortLocationLabel, port.name AS basePortLocationName, portStatus AS basePortLocationStatus, dv.vessel.status AS status FROM DenormalizedVesselImpl dv left join dv.basePortLocation as port left join port.status as portStatus WHERE ( upper(dv.registrationCode) IN (:registrationCodes) OR upper(dv.internationalRegistrationCode) IN (:registrationCodes) ) AND dv.vessel.vesselType.id IN (:vesselTypeIds) AND dv.vessel.status.code IN (:statusValidCode, :statusTemporaryCode) ORDER BY dv.vessel.code
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
vesselTypeIds java.lang.String  
registrationCodes java.lang.String  


Query nearbySpecificAreasByIds

SQL query HQL query
select nearbyspec0_.ID as col_0_0_, nearbyspec0_.NAME as col_1_0_, nearbyspec0_.STATUS_FK as col_2_0_, statusimpl1_.CODE as CODE1_159_, statusimpl1_.NAME as NAME2_159_, statusimpl1_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.NEARBY_SPECIFIC_AREA nearbyspec0_ inner join PUBLIC.STATUS statusimpl1_ on nearbyspec0_.STATUS_FK=statusimpl1_.CODE where ( nearbyspec0_.STATUS_FK in ( ? , ? ) ) and ( nearbyspec0_.ID in ( ? ) ); SELECT g.id AS id, g.name AS name, g.status AS status FROM NearbySpecificAreaImpl g WHERE g.status.code IN (:statusValidCode, :statusTemporaryCode) AND g.id IN (:ids)
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
ids java.lang.String  


Query countTemporaryTaxonGroupUsedInBatch

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.BATCH sortingbat0_ where sortingbat0_.IS_CATCH_BATCH=0 and sortingbat0_.TAXON_GROUP_FK=?; SELECT count(*) FROM SortingBatchImpl b WHERE b.taxonGroup.id = :taxonGroupId
Parameters
Name Type Description
taxonGroupId java.lang.Integer  


Query metiersByObservedLocationForPredocumentation

Get metiers used by vessel in a observed location

SQL query HQL query
select distinct metierimpl4_.ID as col_0_0_, metierimpl4_.LABEL as col_1_0_, metierimpl4_.NAME as col_2_0_, metierimpl4_.STATUS_FK as col_3_0_, statusimpl5_.CODE as CODE1_159_, statusimpl5_.NAME as NAME2_159_, statusimpl5_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LANDING landingimp0_ inner join PUBLIC.FISHING_TRIP fishingtri1_ on landingimp0_.FISHING_TRIP_FK=fishingtri1_.ID inner join PUBLIC.OPERATION operations2_ on fishingtri1_.ID=operations2_.FISHING_TRIP_FK inner join PUBLIC.GEAR_USE_FEATURES gearusefea3_ on operations2_.ID=gearusefea3_.OPERATION_FK inner join PUBLIC.METIER metierimpl4_ on gearusefea3_.METIER_FK=metierimpl4_.ID inner join PUBLIC.STATUS statusimpl5_ on metierimpl4_.STATUS_FK=statusimpl5_.CODE where landingimp0_.VESSEL_FK=? and landingimp0_.OBSERVED_LOCATION_FK=? order by metierimpl4_.LABEL ASC; SELECT DISTINCT m.id, m.label, m.name, m.status FROM LandingImpl la inner join la.fishingTrip.operations o inner join o.gearUseFeatures guf inner join guf.metier m WHERE la.vessel.code = :vesselCode AND la.observedLocation.id = :observedLocationId ORDER BY m.label ASC
Parameters
Name Type Description
observedLocationId java.lang.Integer  
vesselCode java.lang.String  


Query regionalizedItemsByLocations

SQL query HQL query
select distinct spatialite0_.OBJECT_ID as col_0_0_, spatialite1_.LOCALIZED_NAME as col_1_0_ from PUBLIC.SPATIAL_ITEM spatialite0_ inner join PUBLIC.SPATIAL_ITEM2LOCATION spatialite1_ on spatialite0_.ID=spatialite1_.SPATIAL_ITEM_FK where spatialite0_.SPATIAL_ITEM_TYPE_FK=? and ( spatialite1_.LOCATION_FK in ( ? ) ); SELECT DISTINCT si.objectId, i2l.localizedName FROM SpatialItemImpl si INNER JOIN si.spatialItem2Locations i2l WHERE si.spatialItemType.id = :spatialItemType AND i2l.id.location.id IN (:locationIds)
Parameters
Name Type Description
spatialItemType java.lang.Integer  
locationIds java.lang.String  


Query countTemporaryGearUsedInGUF

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.GEAR_USE_FEATURES gearusefea0_ where gearusefea0_.GEAR_FK=?; SELECT count(*) FROM GearUseFeaturesImpl guf WHERE guf.gear.id = :gearId
Parameters
Name Type Description
gearId java.lang.Integer  


Query deleteDenormalizedVessel

SQL query HQL query
delete from PUBLIC.DENORMALIZED_VESSEL where VESSEL_FK=?; DELETE FROM DenormalizedVesselImpl dv WHERE dv.vessel.code = :vesselCode
Parameters
Name Type Description
vesselCode java.lang.String  


Query updateGearPhysicalFeaturesTemporaryGear

SQL query HQL query
update PUBLIC.GEAR_PHYSICAL_FEATURES set GEAR_FK=? where GEAR_FK=?; UPDATE GearPhysicalFeaturesImpl guf SET guf.gear.id = :targetId WHERE guf.gear.id = :sourceId
Parameters
Name Type Description
sourceId java.lang.Integer  
targetId java.lang.Integer  


Query countFishingTripBySynchronizationStatus

SQL query HQL query
select count(*) as col_0_0_ from PUBLIC.FISHING_TRIP fishingtri0_ where fishingtri0_.RECORDER_PERSON_FK=? and fishingtri0_.SYNCHRONIZATION_STATUS=?; SELECT count(*) FROM FishingTripImpl ft WHERE ft.recorderPerson.id = :recorderPersonId AND ft.synchronizationStatus = :synchronizationStatus
Parameters
Name Type Description
synchronizationStatus java.lang.String  
recorderPersonId java.lang.Integer  


Query locationsByLevels

Get all locations by level

SQL query HQL query
select distinct locationim0_.ID as col_0_0_, locationim0_.LABEL as col_1_0_, locationim0_.NAME as col_2_0_, locationim0_.STATUS_FK as col_3_0_, statusimpl2_.CODE as CODE1_159_, statusimpl2_.NAME as NAME2_159_, statusimpl2_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LOCATION locationim0_ inner join PUBLIC.STATUS statusimpl2_ on locationim0_.STATUS_FK=statusimpl2_.CODE cross join PUBLIC.LOCATION_HIERARCHY locationhi1_ cross join PUBLIC.LOCATION locationim3_ cross join PUBLIC.LOCATION locationim4_ where locationhi1_.PARENT_LOCATION_FK=locationim3_.ID and locationhi1_.CHILD_LOCATION_FK=locationim4_.ID and ( locationim0_.STATUS_FK in ( ? , ? ) ) and locationim0_.ID=locationhi1_.CHILD_LOCATION_FK and ( locationim3_.LOCATION_LEVEL_FK in ( ? ) or locationim4_.LOCATION_LEVEL_FK in ( ? ) ); SELECT DISTINCT l.id as locationId, l.label as locationLabel, l.name as locationName, l.status as status FROM LocationImpl l, LocationHierarchyImpl lh WHERE l.status.code IN (:statusValidCode, :statusTemporaryCode) AND l.id = lh.id.location.id AND (lh.id.parent.locationLevel.id IN (:locationLevelIds) OR lh.id.location.locationLevel.id IN (:locationLevelIds))
Parameters
Name Type Description
statusValidCode java.lang.String  
statusTemporaryCode java.lang.String  
locationLevelIds java.lang.String  


Query updateLandingFixMantis30905

SQL query HQL query
update PUBLIC.LANDING set SYNCHRONIZATION_STATUS=?, UPDATE_DATE=? where OBSERVED_LOCATION_FK=? and SYNCHRONIZATION_STATUS=?; UPDATE LandingImpl l SET l.synchronizationStatus = :synchronizationStatusSync, l.updateDate = :updateDate WHERE l.observedLocation.id = :observedLocationId AND l.synchronizationStatus = :synchronizationStatusReadyToSync
Parameters
Name Type Description
synchronizationStatusReadyToSync java.lang.String  
updateDate java.sql.Timestamp  
observedLocationId java.lang.Integer  
synchronizationStatusSync java.lang.String  


Query findDuplicateFishingTripsById

Get duplicate fishing trip corresponding to a natural key

SQL query HQL query
select fishingtri0_.ID as col_0_0_ from PUBLIC.FISHING_TRIP fishingtri0_ where fishingtri0_.ID<>? and fishingtri0_.PROGRAM_FK=( select fishingtri1_.PROGRAM_FK from PUBLIC.FISHING_TRIP fishingtri1_ where fishingtri1_.ID=? ) and fishingtri0_.VESSEL_FK=( select fishingtri2_.VESSEL_FK from PUBLIC.FISHING_TRIP fishingtri2_ where fishingtri2_.ID=? ) and fishingtri0_.DEPARTURE_DATE_TIME=( select fishingtri3_.DEPARTURE_DATE_TIME from PUBLIC.FISHING_TRIP fishingtri3_ where fishingtri3_.ID=? ) order by fishingtri0_.ID; SELECT ft.id AS id FROM FishingTripImpl ft WHERE ft.id != :fishingTripId AND ft.program.id = (SELECT program.id FROM FishingTripImpl WHERE id = :fishingTripId) AND ft.vessel.code = (SELECT vessel.code FROM FishingTripImpl WHERE id = :fishingTripId) AND ft.departureDateTime = (SELECT departureDateTime FROM FishingTripImpl WHERE id = :fishingTripId) ORDER BY ft.id
Parameters
Name Type Description
fishingTripId java.lang.Integer  


Query updateGearPhysicalFeaturesTemporaryVessel

Replace temporary vessel in gear physical features

SQL query HQL query
update PUBLIC.GEAR_PHYSICAL_FEATURES set VESSEL_FK=? where VESSEL_FK=?; UPDATE GearPhysicalFeaturesImpl gpf SET gpf.vessel.code = :validVesselCode WHERE gpf.vessel.code = :tempVesselCode
Parameters
Name Type Description
tempVesselCode java.lang.String  
validVesselCode java.lang.String  


Query expectedSaleByFishingTrip

Get expected sales by fishing trip

SQL query HQL query
select expectedsa0_.ID as col_0_0_, (select salemeasur1_.NUMERICAL_VALUE from PUBLIC.SALE_MEASUREMENT salemeasur1_ where salemeasur1_.EXPECTED_SALE_FK=expectedsa0_.ID and salemeasur1_.PMFM_FK=?) as col_1_0_, (select salemeasur2_.QUALITATIVE_VALUE_FK from PUBLIC.SALE_MEASUREMENT salemeasur2_ where salemeasur2_.EXPECTED_SALE_FK=expectedsa0_.ID and salemeasur2_.PMFM_FK=?) as col_2_0_ from PUBLIC.EXPECTED_SALE expectedsa0_ cross join PUBLIC.LANDING observedla3_ where expectedsa0_.OBSERVED_LANDING_FK=observedla3_.ID and observedla3_.FISHING_TRIP_FK=?; SELECT s.id AS id, (select sm.numericalValue from SaleMeasurementImpl sm where sm.expectedSale.id=s.id and sm.pmfm.id=:pmfmIdExpectedTotalPrice) as expectedTotalPrice, (select sm.qualitativeValue.id from SaleMeasurementImpl sm where sm.expectedSale.id=s.id and sm.pmfm.id=:pmfmIdMarketing) as qvMarketing FROM ExpectedSaleImpl s WHERE s.observedLanding.fishingTrip.id=:fishingTripId
Parameters
Name Type Description
pmfmIdMarketing java.lang.Integer  
pmfmIdExpectedTotalPrice java.lang.Integer  
fishingTripId java.lang.Integer  


Query landingIdsByObservedLocationId

Get all landing Ids corresponding to a observed location

SQL query HQL query
select distinct landingimp0_.ID as col_0_0_ from PUBLIC.LANDING landingimp0_ where 1=1 and landingimp0_.OBSERVED_LOCATION_FK=? order by landingimp0_.ID; SELECT DISTINCT la.id AS id FROM LandingImpl la WHERE 1=1 AND la.observedLocation.id = :observedLocationId ORDER BY la.id
Parameters
Name Type Description
observedLocationId java.lang.Integer  


Query checkExtranetLogin

Check if a login is an Extranet login or not

SQL query HQL query
select distinct case when personimpl0_.USERNAME_EXTRANET=? then true else false end as col_0_0_ from PUBLIC.PERSON personimpl0_ where personimpl0_.USERNAME=? or personimpl0_.USERNAME_EXTRANET=?; SELECT DISTINCT case when p.usernameExtranet = :personLogin then true else false end FROM PersonImpl p WHERE p.username = :personLogin OR p.usernameExtranet = :personLogin
Parameters
Name Type Description
personLogin java.lang.String  


Query regionalizedDressingsByTaxonGroupId

SQL query HQL query
select distinct roundweigh0_.DRESSING_FK as col_0_0_ from PUBLIC.ROUND_WEIGHT_CONVERSION roundweigh0_ where ( roundweigh0_.TAXON_GROUP_FK in ( select taxongroup1_.PARENT_TAXON_GROUP_FK from PUBLIC.TAXON_GROUP_HIERARCHY taxongroup1_ where taxongroup1_.CHILD_TAXON_GROUP_FK=? ) ) and roundweigh0_.LOCATION_FK=? and roundweigh0_.START_DATE<=? and coalesce(roundweigh0_.END_DATE, ?)>=?; SELECT DISTINCT c.dressing.id FROM RoundWeightConversionImpl c WHERE c.taxonGroup.id IN ( SELECT tgh.taxonGroupHierarchyPk.parentTaxonGroup.id FROM TaxonGroupHierarchyImpl tgh WHERE tgh.taxonGroupHierarchyPk.childTaxonGroup.id = :taxonGroupId) AND c.location.id = :locationId AND NOT( c.startDate > :endDate OR coalesce(c.endDate, :startDate) < :startDate )
Parameters
Name Type Description
endDate java.util.Date  
locationId java.lang.String  
taxonGroupId java.lang.Integer  
startDate java.util.Date  


Query updateObservedLocationFeaturesTemporaryMetier

SQL query HQL query
update PUBLIC.OBSERVED_LOCATION_FEATURES set METIER_FK=? where METIER_FK=?; UPDATE ObservedLocationFeaturesImpl olf SET olf.metier.id = :targetId WHERE olf.metier.id = :sourceId
Parameters
Name Type Description
sourceId java.lang.Integer  
targetId java.lang.Integer  


Query notExportableObservedLocationsNoVessel

Return observed locations :
- with the given synchronization status
- without any landing and vessel
- not exportable by user, because NO rights from PersonSessionItem (mantis #28889)

SQL query HQL query
select distinct observedlo1_.ID as col_0_0_, observedlo1_.START_DATE_TIME as col_1_0_, observedlo1_.END_DATE_TIME as col_2_0_, observedlo1_.SAMPLING_STRATA_REFERENCE as col_3_0_, observedlo1_.COMMENTS as col_4_0_, observedlo1_.LOCATION_FK as col_5_0_, locationim3_.LABEL as col_6_0_, locationim3_.NAME as col_7_0_, observedlo1_.SYNCHRONIZATION_STATUS as col_8_0_, observedlo1_.PROGRAM_FK as col_9_0_, observedlo1_.RECORDER_PERSON_FK as col_10_0_ from PUBLIC.PERSON_SESSION personsess0_ cross join PUBLIC.OBSERVED_LOCATION observedlo1_ left outer join PUBLIC.LANDING landings2_ on observedlo1_.ID=landings2_.OBSERVED_LOCATION_FK, PUBLIC.LOCATION locationim3_ where observedlo1_.LOCATION_FK=locationim3_.ID and observedlo1_.SYNCHRONIZATION_STATUS=? and ( landings2_.ID is null ) and personsess0_.PERSON_FK=? and ( observedlo1_.REMOTE_ID is null or ( observedlo1_.REMOTE_ID is not null ) and not (exists (select personsess5_.ID from PUBLIC.PERSON_SESSION_ITEM personsess5_ where personsess5_.OBJECT_ID=observedlo1_.REMOTE_ID and personsess5_.PERSON_SESSION_FK=personsess0_.ID and personsess5_.PROGRAM_FK=observedlo1_.PROGRAM_FK and (personsess5_.OBJECT_TYPE_FK in (?)))) ) order by observedlo1_.START_DATE_TIME desc; SELECT DISTINCT ol.id, ol.startDateTime AS startDateTime, ol.endDateTime AS endDateTime, ol.samplingStrataReference AS samplingStrataReference, ol.comments AS comments, ol.location.id AS locationId, ol.location.label AS locationLabel, ol.location.name AS locationName, ol.synchronizationStatus AS synchronizationStatus, ol.program.code AS programCode, ol.recorderPerson.id AS recorderPersonId FROM PersonSessionImpl ps, ObservedLocationImpl ol LEFT JOIN ol.landings l WHERE ol.synchronizationStatus = :synchronizationStatus AND l.id IS NULL AND ps.person.id = :personId AND ( ol.remoteId IS NULL OR ( ol.remoteId IS NOT NULL AND NOT EXISTS ( FROM PersonSessionItemImpl psi WHERE psi.objectId = ol.remoteId and psi.personSession.id = ps.id and psi.program.code = ol.program.code and psi.objectType.code IN (:objectTypes) ) ) ) ORDER BY ol.startDateTime desc
Parameters
Name Type Description
synchronizationStatus java.lang.String  
personId java.lang.Integer  
objectTypes java.lang.String  


Query landingsByObservedLocationId

Get all landings corresponding to a observed location

SQL query HQL query
select distinct landingimp0_.ID as col_0_0_, fishingtri1_.RETURN_DATE_TIME as col_1_0_, landingimp0_.SYNCHRONIZATION_STATUS as col_2_0_, landingimp0_.VESSEL_FK as col_3_0_, vesselimpl3_.VESSEL_TYPE_FK as col_4_0_, denormaliz2_.NAME as col_5_0_, denormaliz2_.REGISTRATION_CODE as col_6_0_, denormaliz2_.INT_REGISTRATION_CODE as col_7_0_, denormaliz2_.REGISTRATION_LOCATION_FK as col_8_0_, locationim4_.LABEL as col_9_0_, locationim4_.NAME as col_10_0_, vesselimpl3_.STATUS_FK as col_11_0_, (select landingmea8_.QUALITATIVE_VALUE_FK from PUBLIC.LANDING_MEASUREMENT landingmea8_ where landingmea8_.LANDING_FK=landingimp0_.ID and landingmea8_.PMFM_FK=?) as col_12_0_, fishingtri1_.ID as col_13_0_, fishingtri1_.SYNCHRONIZATION_STATUS as col_14_0_, landingimp0_.OBSERVED_LOCATION_FK as col_15_0_, statusimpl7_.CODE as CODE1_159_, statusimpl7_.NAME as NAME2_159_, statusimpl7_.UPDATE_DATE as UPDATE_D3_159_ from PUBLIC.LANDING landingimp0_ left outer join PUBLIC.FISHING_TRIP fishingtri1_ on landingimp0_.FISHING_TRIP_FK=fishingtri1_.ID, PUBLIC.VESSEL vesselimpl3_ inner join PUBLIC.STATUS statusimpl7_ on vesselimpl3_.STATUS_FK=statusimpl7_.CODE cross join PUBLIC.DENORMALIZED_VESSEL denormaliz2_, PUBLIC.LOCATION locationim4_ where landingimp0_.VESSEL_FK=vesselimpl3_.CODE and denormaliz2_.REGISTRATION_LOCATION_FK=locationim4_.ID and 1=1 and landingimp0_.OBSERVED_LOCATION_FK=? and denormaliz2_.VESSEL_FK=landingimp0_.VESSEL_FK order by landingimp0_.VESSEL_FK, fishingtri1_.RETURN_DATE_TIME ASC; SELECT DISTINCT la.id AS id, ft.returnDateTime AS landingDateTime, la.synchronizationStatus AS synchronizationStatus, la.vessel.code AS vesselCode, la.vessel.vesselType.id AS vesselTypeId, dv.name as name, dv.registrationCode as registrationCode, dv.internationalRegistrationCode as internationalRegistrationCode, dv.registrationLocation.id as registrationLocationId, dv.registrationLocation.label as registrationLocationLabel, dv.registrationLocation.name as registrationLocationName, la.vessel.status AS vesselStatus, (select lm.qualitativeValue.id from LandingMeasurementImpl lm where lm.landing.id=la.id and lm.pmfm.id=:pmfmIdVesselPortState) AS vesselPortStateQvId, ft.id AS fishingTripId, ft.synchronizationStatus AS fishingTripSynchronizationStatus, la.observedLocation.id FROM LandingImpl la left join la.fishingTrip ft, DenormalizedVesselImpl dv WHERE 1=1 AND la.observedLocation.id = :observedLocationId AND dv.vessel = la.vessel ORDER BY la.vessel.code, ft.returnDateTime ASC
Parameters
Name Type Description
pmfmIdVesselPortState java.lang.Integer  
observedLocationId java.lang.Integer  


Query updateObservedLocationTemporaryPerson

SQL query HQL query
UPDATE OBSERVED_LOCATION2PERSON ol2p SET ol2p.OBSERVER_PERSON_FK = :targetId WHERE ol2p.OBSERVER_PERSON_FK = :sourceId;


Query insertDenormalizedVesselsFromTemp

SQL query HQL query
INSERT INTO DENORMALIZED_VESSEL (id, vessel_fk, program_fk, name, int_Registration_Code, registration_Code, registration_Start_Date, registration_end_date, rank_Order, exterior_Marking, registration_Location_fk, Registration_country_fk, vessel_Owner_fk, base_port_location_fk) SELECT NEXT VALUE FOR DENORMALIZED_VESSEL_SEQ, tmp.code AS vesselCode, tmp.program_fk AS programCode, vf.name AS name, vrp.INT_REGISTRATION_CODE as internationalRegistrationCode, vrp.REGISTRATION_CODE AS registrationCode, vrp.START_DATE_TIME AS registrationStartDateTime, vrp.END_DATE_TIME AS registrationEndDateTime, vrp.RANK_ORDER AS rankOrder, vf.EXTERIOR_MARKING AS exteriorMarking, vrp.registration_location_fk AS registrationLocationId, lh.parent_location_fk AS registrationCountryId, vop.vessel_owner_fk AS vesselOwnerId, vf.base_port_location_fk AS basePortLocationId FROM temp_denormalized_vessel AS tmp INNER JOIN vessel_registration_period AS vrp ON vrp.vessel_fk = tmp.code AND vrp.start_date_time = tmp.vrp_start_date_time LEFT OUTER JOIN vessel_features AS vf ON vf.vessel_fk = tmp.code AND vf.start_date_time = tmp.vf_start_date_time LEFT OUTER JOIN vessel_owner_period AS vop ON vop.vessel_fk = tmp.code AND vop.start_date_time = tmp.vf_start_date_time, location_hierarchy lh, location l_country WHERE 1=1 AND lh.child_location_fk = vrp.registration_location_fk AND lh.parent_location_fk = l_country.id AND l_country.location_level_fk = :countryLocationLevelId;
Parameters
Name Type Description
countryLocationLevelId java.lang.Integer  


Query updateObservedFishingTripTemporaryPerson

SQL query HQL query
UPDATE FISHING_TRIP2OBSERVER_PERSON ft2op SET ft2op.PERSON_FK = :targetId WHERE ft2op.PERSON_FK = :sourceId;


Query countTemporaryPersonUsedInFishingTrip

SQL query HQL query
SELECT count(*) FROM FISHING_TRIP2OBSERVER_PERSON ft2op WHERE ft2op.PERSON_FK = :personId;
Parameters
Name Type Description
personId java.lang.Integer  


Query countTemporaryPersonUsedInObservedLocation

SQL query HQL query
SELECT count(*) FROM OBSERVED_LOCATION2PERSON ol2p WHERE ol2p.OBSERVER_PERSON_FK = :personId;
Parameters
Name Type Description
personId java.lang.Integer  


Query insertDenormalizedVessel

SQL query HQL query
insert into DENORMALIZED_VESSEL (id, vessel_fk, program_fk, name, int_Registration_Code, registration_Code, registration_Start_Date, registration_End_Date, rank_Order, exterior_Marking, registration_Location_fk, Registration_country_fk, vessel_Owner_fk, base_port_location_fk) VALUES (NEXT VALUE FOR DENORMALIZED_VESSEL_SEQ, :vesselCode,:programCode,:name,:internationalRegistrationCode,:registrationCode,:registrationStartDateTime,:registrationEndDateTime, :rankOrder, :exteriorMarking, :registrationLocationId, :registrationCountryId, :vesselOwnerId, :basePortLocationId);
Parameters
Name Type Description
registrationEndDateTime java.util.Date  
exteriorMarking java.lang.String  
basePortLocationId java.lang.Integer  
programCode java.lang.String  
internationalRegistrationCode java.lang.String  
registrationLocationId java.lang.Integer  
vesselOwnerId java.lang.Integer  
registrationCode java.lang.String  
vesselCode java.lang.String  
rankOrder java.lang.String  
registrationStartDateTime java.util.Date  
registrationCountryId java.lang.Integer  


Query updateObservedLandingTemporaryPerson

SQL query HQL query
UPDATE LANDING2OBSERVER_PERSON l2p SET l2p.PERSON_FK = :targetId WHERE l2p.PERSON_FK = :sourceId;