Fix workbench#show filter referential by periode range

Refs #2665

2 files changed, 18 insertions, 0 deletions

diff --git a/app/controllers/workbenches_controller.rb b/app/controllers/workbenches_controller.rb
index 3818ea866..f3dc0c9b3 100644
--- a/app/controllers/workbenches_controller.rb
+++ b/app/controllers/workbenches_controller.rb
@@ -5,6 +5,7 @@ class WorkbenchesController < BreadcrumbController
   def show
     scope = Workbench.find(params[:id])
     scope = params[:q] ? scope.all_referentials : scope.referentials.ready
+    scope = scope.in_periode(ransack_periode) if ransack_periode
     @q = scope.ransack(params[:q])
     @q.organisation_name_eq_any ||= current_organisation.name unless params[:q]
 
@@ -27,8 +28,17 @@ class WorkbenchesController < BreadcrumbController
   def sort_column
     Workbench.find(params[:id]).referentials.include?(params[:sort]) ? params[:sort] : 'name'
   end
+
   def sort_direction
     %w[asc desc].include?(params[:direction]) ?  params[:direction] : 'asc'
   end
 
+  def ransack_periode
+    return unless params[:q] && params[:q]['validity_period']
+
+    periode     = params[:q]['validity_period']
+    start_range = Date.civil(periode["begin_gteq(1i)"].to_i, periode["begin_gteq(2i)"].to_i, periode["begin_gteq(3i)"].to_i)
+    end_range   = Date.civil(periode["end_lteq(1i)"].to_i, periode["end_lteq(2i)"].to_i, periode["end_lteq(3i)"].to_i)
+    start_range..end_range
+  end
 end
diff --git a/app/models/referential.rb b/app/models/referential.rb
index dbc7b76e7..85d581c33 100644
--- a/app/models/referential.rb
+++ b/app/models/referential.rb
@@ -43,6 +43,7 @@ class Referential < ActiveRecord::Base
   belongs_to :workbench
 
   scope :ready, -> { where(ready: true) }
+  scope :in_periode, ->(periode) { where(id: referential_ids_in_periode(periode)) }
 
   def lines
     if metadatas.blank?
@@ -229,6 +230,13 @@ class Referential < ActiveRecord::Base
     end
   end
 
+  def self.referential_ids_in_periode(range)
+    subquery = "SELECT DISTINCT(public.referential_metadata.referential_id) FROM public.referential_metadata, LATERAL unnest(periodes) period "
+    subquery << "WHERE period && '#{ActiveRecord::ConnectionAdapters::PostgreSQLColumn.range_to_string(range)}'"
+    query = "SELECT * FROM public.referentials WHERE referentials.id IN (#{subquery})"
+    self.connection.select_values(query).map(&:to_i)
+  end
+
   def overlapped_referential_ids
     return [] unless metadatas.present?