#encoding: utf-8

class TimelineController < ApplicationController
  before_filter :login_required
  layout "application"



  def index
    big_session[:timeline]={}
    redirect_to :action => :years
  end

  def years #erste Seite einer Abfrage
    if big_session[:timeline][:years]
      @preselected_years = big_session[:timeline][:years].collect { |jahr| jahr.to_i }
    else
      @preselected_years = (2007..session[:reporting_period].to_i).to_a
    end
  end
  
  def bula
    if params[:origin] == 'years'
      big_session[:timeline][:years] = params[:jahr]._?([])
    end
    @bulaender = ActiveRecord::Base.connection.query("SELECT id,ltext FROM l_bundeslaender WHERE id > 0 ORDER BY sortier")
    if big_session[:timeline][:bulaender]
      @preselected_bulas = big_session[:timeline][:bulaender].collect { |bula| bula.to_i }
    else
      @preselected_bulas = (1..16).to_a
    end
  end

  def filter
    if params[:origin] == 'bula'
      big_session[:timeline][:bulaender] = params[:bula]
    end
  end

  def name
    if params[:origin] == 'filter'
      big_session[:timeline][:krit1] = (params[:fa].to_f != 0.0 || params[:fs].to_f != 0.0)
      big_session[:timeline][:fa] = params[:fa].sub(',','.').to_f
      big_session[:timeline][:fs] = params[:fs].sub(',','.').to_f
      big_session[:timeline][:krit2] = !params[:krit2].nil?
      big_session[:timeline][:filter_krit1] = !params[:filter_krit1].nil?
      big_session[:timeline][:filter_krit2] = !params[:filter_krit2].nil?
    end
  end

  def req_categories
    if params[:origin] == 'name'
      big_session[:timeline][:name] = params[:name]
    end
  end

  def categories
    if params[:origin] == 'req_categories'
      big_session[:timeline][:required_categories] = params[:required_categories]
    end
  end

  def result
    if params[:origin] == 'categories'
      big_session[:timeline][:categories] = params[:attributes]._?(('1'..'7').to_a)
    end
    @result = []
    @result += fetch_freis('"Freisetzung von #{min[3]} in die Luft"','1') {|jahr| fetch_freis_pa(jahr, 2, 'luft')} if big_session[:timeline][:categories].include?('1')
    @result += fetch_freis('"Freisetzung von #{min[3]} in das Wasser"','2') {|jahr| fetch_freis_pa(jahr, 3, 'wasser')} if big_session[:timeline][:categories].include?('2')
    @result += fetch_freis('"Freisetzung von #{min[3]} in den Boden"','3') {|jahr| fetch_freis_pa(jahr, 1, 'boden')} if big_session[:timeline][:categories].include?('3')
    @result += fetch_freis('"Verbringung von #{min[3]} mit dem Abwasser"','4') {|jahr| fetch_abw_pa(jahr)} if big_session[:timeline][:categories].include?('4')
    @result += fetch_freis('"Verbringung von gefährlichem Abfall im Inland"','5') {|jahr| fetch_abf_pa(jahr,2,1)} if big_session[:timeline][:categories].include?('5')
    @result += fetch_freis('"Verbringung von nicht gefährlichem Abfall im Ausland"','6') {|jahr| fetch_abf_pa(jahr,1,2)} if big_session[:timeline][:categories].include?('6')
    @result += fetch_freis('"Verbringung von gefährlichem Abfall im Ausland"','7') {|jahr| fetch_abf_pa(jahr,1,1)} if big_session[:timeline][:categories].include?('7')
    @result.sort_by! do |row|
      [row[:buland],row[:betriebs_kennnr],row[:kat],row[:stoff]] 
    end
    # filter for extreme values and values not reported every year
    if big_session[:timeline][:krit1] || big_session[:timeline][:krit2]
      @result.each do |line|
        if big_session[:timeline][:krit1]
          data = []
          sum = 0.0
          line[:daten].each do |i|
            unless i.nil?
              i = i.to_f
              sum+=i
              data << i
            end
          end
          avg = sum / data.length
          dev_sum = 0.0
          data.each do |val|
            dev_sum+=(val-avg)**2
          end
          dev_avg = Math.sqrt(dev_sum / (data.length-1))
          dev_max = dev_avg * big_session[:timeline][:fs]
          schww = line[:schww].to_i * big_session[:timeline][:fa]
          limit = dev_max > schww ? dev_max : schww
          range = (avg-limit)..(avg+limit)
          #puts "#{line[:betriebs_kennnr]}: avg=#{avg} dev_avg=#{dev_avg} range=#{range}"
          line[:krit1] = !data.all?{|val| range === val }
        end
        if big_session[:timeline][:krit2]
          line[:krit2] = line[:daten].any?{|val| val.nil? }
        end
      end
      if big_session[:timeline][:filter_krit1] || big_session[:timeline][:filter_krit2]
        @result.delete_if { |line| (big_session[:timeline][:filter_krit1] && !line[:krit1]) ||
                                      (big_session[:timeline][:filter_krit2] && !line[:krit2]) }
      end
    end
    # filter for required categories
    unless big_session[:timeline][:required_categories].blank?
      tmp = {:betriebs_kennnr => nil}
      to_be_deleted = []
      for e in @result
        if e[:betriebs_kennnr] != tmp[:betriebs_kennnr]
          unless tmp[:betriebs_kennnr].nil? || tmp[:found_categories]
            to_be_deleted.push(tmp[:betriebs_kennnr])
          end
          tmp = {:betriebs_kennnr => e[:betriebs_kennnr], :found_categories => big_session[:timeline][:required_categories].include?(e[:kat])}
        else
          tmp[:found_categories] = tmp[:found_categories] || big_session[:timeline][:required_categories].include?(e[:kat])
        end
      end
      unless tmp[:betriebs_kennnr].nil? || tmp[:found_categories]
        to_be_deleted.push(tmp[:betriebs_kennnr])
      end
      to_be_deleted.each do |bid|
        @result.delete_if { |el| el[:betriebs_kennnr]==bid }
      end
    end
  end

  def fetch_freis(beschreibung,kat)
    tmp_frei = []
    result = []
    big_session[:timeline][:years].each do |jahr|
      tmp_frei.push yield(jahr) #fetch_freis_pa(jahr, medium_id, medium_wort)
    end
    weitermachen = true
    while weitermachen
      tmp_frei_filled = tmp_frei.select { |jahresdaten| !jahresdaten.empty? }
      min = tmp_frei_filled.min_by { |jahresdaten| cmp_hash(jahresdaten[0]) }
      break if min.nil?
      min=min[0]
      col = 0
      tmp_daten=[]
      tmp_frei.each do |jahresdaten|
        if cmp_hash(jahresdaten[0]) == cmp_hash(min)
          tmp_daten[col] = jahresdaten[0][4]
          jahresdaten.shift
        else
          tmp_daten[col] = nil
        end
        col += 1
      end
      result.push({:betriebs_id => min[0], :kategorie => eval(beschreibung), :kat => kat, :betriebs_kennnr => min[2], :buland => min[7], :stoff => min[3], :daten => tmp_daten, :unit => min[5], :schww => min[6], :name => min[8]})
      weitermachen = !tmp_frei.all? { |jahresdaten| jahresdaten.empty? }
    end
    result
  end

  def fetch_freis_pa(jahr, medium_id, medium_wort)
    sql = "SELECT b.id, s.id, b.kennnr, s.ktext, a.jfracht, s.einheit_#{medium_wort}, schwellenwert_#{medium_wort}, l.ktext, COALESCE(b.name1,'')||' '||COALESCE(b.name1,'')
    FROM p_freis a
    JOIN p_betrieb b ON a.p_betrieb_id = b.id
    JOIN l_bundeslaender l ON b.land_id = l.id
    JOIN l_stoffe s  ON a.stoffnr_id = s.id
    WHERE a.jahr = '#{jahr}'
      AND a.jfracht IS NOT NULL
      AND a.medium_id    = #{medium_id}"
    sql << " AND b.land_id IN (#{big_session[:timeline][:bulaender].join(',')})" #unless big_session[:timeline][:bulaender].length == 16
    sql << " AND (b.name1 LIKE '%#{big_session[:timeline][:name]}%' OR b.name2 LIKE '%#{big_session[:timeline][:name]}%')" unless big_session[:timeline][:name].blank?
    sql << " ORDER BY l.ktext,b.kennnr,s.id"
    ActiveRecord::Base.connection.query(sql)
  end

  def fetch_abw_pa(jahr)
    sql = "SELECT b.id, s.id, b.kennnr, s.ktext, a.jfracht, s.einheit_wasser, schwellenwert_wasser, l.ktext, COALESCE(b.name1,'')||' '||COALESCE(b.name1,'')
    FROM p_vabw a
    JOIN p_betrieb b ON a.p_betrieb_id = b.id
    JOIN l_bundeslaender l ON b.land_id = l.id
    JOIN l_stoffe s  ON a.stoffnr_id = s.id
    WHERE a.jahr = '#{jahr}'
      AND a.jfracht IS NOT NULL"
    sql << " AND b.land_id IN (#{big_session[:timeline][:bulaender].join(',')})"
    sql << " AND (b.name1 LIKE '%#{big_session[:timeline][:name]}%' OR b.name2 LIKE '%#{big_session[:timeline][:name]}%')" unless big_session[:timeline][:name].blank?
    sql << " ORDER BY l.ktext,b.kennnr,s.id"
    ActiveRecord::Base.connection.query(sql)
  end

  def fetch_abf_pa(jahr,ia,typ)
    sql = "SELECT b.id, '0', b.kennnr, 'abfall', SUM(a.menge), 'T/Jahr', '#{(ia==1)?'2':'2000'}',l.ktext, COALESCE(b.name1,'')||' '||COALESCE(b.name1,'')
    FROM p_vabf a
    JOIN p_betrieb b ON a.p_betrieb_id = b.id
    JOIN l_bundeslaender l ON b.land_id = l.id
    WHERE a.jahr = '#{jahr}'
      AND a.menge IS NOT NULL
      AND a.abfart_id = #{typ}
      AND a.aland_id= #{ia}"
    sql << " AND b.land_id IN (#{big_session[:timeline][:bulaender].join(',')})"
    sql << " AND (b.name1 LIKE '%#{big_session[:timeline][:name]}%' OR b.name2 LIKE '%#{big_session[:timeline][:name]}%')" unless big_session[:timeline][:name].blank?
    sql << " GROUP BY b.id,l.ktext,b.kennnr,b.name1,b.name2"
    sql << " ORDER BY l.ktext,b.kennnr"
    puts sql
    ActiveRecord::Base.connection.query(sql)
  end

  def cmp_hash(value)
    return [value[7],value[2], value[1].to_i] rescue [-1] # ensures never is equal to real value
  end
  
end



