3 files changed, 34 insertions, 7 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?
 
diff --git a/spec/models/referential_spec.rb b/spec/models/referential_spec.rb
index 539af4db4..7cf428eb0 100644
--- a/spec/models/referential_spec.rb
+++ b/spec/models/referential_spec.rb
@@ -4,13 +4,27 @@ describe Referential, :type => :model do
   let(:ref) { create :referential, metadatas: [create(:referential_metadata)] }
 
   # it "create a rule_parameter_set" do
-    # referential = create(:referential)
-    #expect(referential.rule_parameter_sets.size).to eq(1)
+  #   referential = create(:referential)
+  #   expect(referential.rule_parameter_sets.size).to eq(1)
   # end
 
   it { should have_many(:metadatas) }
   it { should belong_to(:workbench) }
 
+  context ".referential_ids_in_periode" do
+    it 'should retrieve referential id in periode range' do
+      range = ref.metadatas.first.periodes.sample
+      refs  = Referential.referential_ids_in_periode(range)
+      expect(refs).to include(ref.id)
+    end
+
+    it 'should not retrieve referential id not in periode range' do
+      range = Date.today - 2.year..Date.today - 1.year
+      refs  = Referential.referential_ids_in_periode(range)
+      expect(refs).to_not include(ref.id)
+    end
+  end
+
   context "Cloning referential" do
     let(:clone) do
       Referential.new_from(ref)
@@ -102,15 +116,10 @@ describe Referential, :type => :model do
   end
 
   context "lines" do
-
     describe "search" do
-
       it "should support Ransack search method" do
         expect(ref.lines.search.result.to_a).to eq(ref.lines.to_a)
       end
-
     end
-
   end
-
 end