#encoding: utf-8
class StatistikController < ApplicationController
  before_action :login_required
  layout "application"

  GENERELLE_INFORMATIONEN = [
    ["Anzahl der Betriebe",
     "select count(distinct p.id)
from p_betrieb p
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231);"
    ],

    ["Anzahl der Betriebe mit angegebener Haupttätigkeit (aHT)",
      "select count(distinct p.id)
from p_betrieb p
  left join p_taet t
    on p.id = t.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and t.htprtr = 1;"
    ],

    [
      "Anzahl der Betriebe mit Freisetzungen",
      "select count( distinct p.id)
from p_betrieb p
  left join p_freis f
    on p.id = f.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and f.p_betrieb_id is not null;"
    ],

    ["Anzahl der Betriebe mit Verbringung von Abwasser (VAbw)",
     "select count(distinct p.id)
from p_betrieb p
  left join p_vabw vaw
    on p.id = vaw.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and vaw.p_betrieb_id is not null;"
    ],

    ["Anzahl der Betriebe mit Verbringung von Abfall (VAbf)",
      "select count(distinct p.id)
from p_betrieb p
  left join p_vabf vaf
    on p.id = vaf.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and vaf.p_betrieb_id is not null;"
    ],

    ["Anzahl der Betriebe mit aHT, Freis., VAbw und VAbf",
      "select count(distinct p.id)
from p_betrieb p
  left join p_taet t
    on p.id = t.p_betrieb_id
  left join p_freis f
    on p.id = f.p_betrieb_id
  left join p_vabw vaw
    on p.id = vaw.p_betrieb_id
  left join p_vabf vaf
    on p.id = vaf.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and t.htprtr = 1
  and f.p_betrieb_id is not null
  and vaw.p_betrieb_id is not null
  and vaf.p_betrieb_id is not null;"
    ],

    ["Anzahl der Tätigkeiten",
      "select count(distinct p.id)
from p_betrieb p
  left join p_taet t
    on p.id = t.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and t.id is not null;"
    ],

    ["Anzahl der Freisetzungen",
      "select count(p.id)
from p_betrieb p
  left join p_freis f
    on p.id = f.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and f.id is not null;"
    ],

    ["Anzahl der Verbringungen von Abwasser",
      "select count(p.id)
from p_betrieb p
  left join p_vabw vaw
    on p.id = vaw.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and vaw.id is not null;"
    ],

    ["Anzahl der Verbringungen von Abfall",
      "select count(p.id)
from p_betrieb p
  left join p_vabf vaf
    on p.id = vaf.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and vaf.id is not null;"
    ],

    ["Anzahl der Freisetzungen von Betrieben mit aHT",
      "select count(distinct f.id)
from p_betrieb p
  inner join p_freis f
    on p.id = f.p_betrieb_id
  inner join p_taet t
    on p.id = t.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and t.htprtr = 1;"
    ],

    ["Anzahl der VAwb von Betrieben mit aHT",
      "select count(distinct vaw.id)
from p_betrieb p
  inner join p_vabw vaw
    on p.id = vaw.p_betrieb_id
  inner join p_taet t
    on p.id = t.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and t.htprtr = 1;"
    ],

    ["Anzahl der VAwf von Betrieben mit aHT",
      "select count(vaf.id)
from p_betrieb p
  inner join p_vabf vaf
    on p.id = vaf.p_betrieb_id
  inner join p_taet t
    on p.id = t.p_betrieb_id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and t.htprtr = 1;"
    ]
  ]

  $SPEZIELLE_INFORMATIONEN = {
    "Branchenhäufigkeiten nach Anzahl mit Haupttätigkeit Anh. I und Branchengruppe" =>
      %q/select    count(lt.*) as "Anzahl Meldungen",
      -- coalesce (p.name1,'')||' '|| coalesce(p.name2,'') as "betriebseinrichtung",
      lt.schl as "Schlüssel",
      lt.ktext as "Tätigkeit Anh. I",
      lbg.name as "Branchengruppe" from p_betrieb p
      left join p_taet t
        on p.id = t.p_betrieb_id
      left join l_taetigkeiten_anhang1_eprtr lt
        on t.nrprtr_id = lt.id
      left join l_prtr_branchengruppen lbg
        on lt.prtr_branchengruppe_id = lbg.id
      where p.state = 0
         and p.date_from <= (2007*10000+101)
         and p.date_to   >= (2007*10000+1231)
         and t.htprtr = 1 group by lt.schl, lt.ktext, lbg.name
      order by count(lt.*) desc;/,

    "Branchenhäufigkeiten nach Anzahl mit Tätigkeit Anh. I und Branchengruppe" =>
      %q/
select
  count(lt.*) as "Anzahl Meldungen",
  -- coalesce (p.name1,'')||' '|| coalesce(p.name2,'') as "betriebseinrichtung",
  lt.schl as "Schlüssel",
  lt.ktext as "Tätigkeit Anh. I",
  lbg.name as "Branchengruppe"
from p_betrieb p
  left join p_taet t
    on p.id = t.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr lt
    on t.nrprtr_id = lt.id
  left join l_prtr_branchengruppen lbg
    on lt.prtr_branchengruppe_id = lbg.id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
group by lt.schl, lt.ktext, lbg.name
order by count(lt.*) desc
;/,

    "Branchengruppenhäufigkeiten nach Anzahl (Nur Haupttätigkeiten)" =>
      %q/select
  count(lbg.name) as "Anzahl Meldungen",
  lbg.name as "Branchengruppe"
from p_betrieb p
  left join p_taet t
    on p.id = t.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr lt
    on t.nrprtr_id = lt.id
  left join l_prtr_branchengruppen lbg
    on lt.prtr_branchengruppe_id = lbg.id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and t.htprtr = 1
group by lbg.name
order by count(lt.*) desc;/,

    "Branchengruppenhäufigkeiten nach Anzahl" =>
      %q/select
  count(lbg.name) as "Anzahl Meldungen",
  lbg.name as "Branchengruppe"
from p_betrieb p
  left join p_taet t
    on p.id = t.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr lt
    on t.nrprtr_id = lt.id
  left join l_prtr_branchengruppen lbg
    on lt.prtr_branchengruppe_id = lbg.id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
group by lbg.name
order by count(lt.*) desc
;/,

    "Summarische Frachten aus Freisetzungen je Stoff und Kompartiment" =>
      %q$select
  replace(sum(f.jfracht)::varchar, '.', ',') as "Summenfracht [kg/a]",
  ls.ktext as "Stoffname",
  lk.ktext as "Kompartiment"
from p_betrieb p
  left join p_freis f
    on p.id = f.p_betrieb_id
  left join l_stoffe ls
    on f.stoffnr_id = ls.id
  left join l_kompartimente lk
    on f.medium_id = lk.id
where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and stoffnr_id is not null
group by ls.ktext, lk.ktext
order by sum(f.jfracht) desc
;$,

    "Summarische Frachten aus Freisetzungen je Stoff und Branchengruppe" =>
      %q$select 
  replace(sum(f.jfracht)::varchar, '.', ',') as "Summenfracht [kg/a]",
  ls.ktext as "Stoffname",
  lbg.name as "Branchengruppe"

from p_betrieb p
  left join p_freis f
    on p.id = f.p_betrieb_id
  left join l_stoffe ls
    on f.stoffnr_id = ls.id
  left join l_kompartimente lk
    on f.medium_id = lk.id

  left join p_taet t
    on p.id = t.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr lt
    on t.nrprtr_id = lt.id
  left join l_prtr_branchengruppen lbg
    on lt.prtr_branchengruppe_id = lbg.id

where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and stoffnr_id is not null
  and t.htprtr = 1
group by ls.ktext, lbg.name
order by sum(f.jfracht) desc
;$,

    "Summarische Frachten aus Freisetzungen je Stoff und Tätigkeit Anh. I" =>
      %q$select
  -- f.jfracht,
  replace(sum(f.jfracht)::varchar, '.', ',') as "Summenfracht [kg/a]",
  ls.ktext as "Stoffname",
  -- lk.ktext as "Kompartiment",
  lt.schl as "Schlüssel",
  lt.ktext as "Tätigkeit Anh. I",
  lbg.name as "Branchengruppe"

from p_betrieb p
  left join p_freis f
    on p.id = f.p_betrieb_id
  left join l_stoffe ls
    on f.stoffnr_id = ls.id
  left join l_kompartimente lk
    on f.medium_id = lk.id

  left join p_taet t
    on p.id = t.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr lt
    on t.nrprtr_id = lt.id
  left join l_prtr_branchengruppen lbg
    on lt.prtr_branchengruppe_id = lbg.id

where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and stoffnr_id is not null
  and t.htprtr = 1
group by ls.ktext, lt.schl, lt.ktext, lbg.name
order by sum(f.jfracht) desc
;$,


    "Alle Frachten aus Freisetzungen" =>
      %q$select
  coalesce(p.kennnr,'') || ' ' || coalesce (p.name1,'') || ' ' || coalesce(p.name2,'') as "Betriebseinrichtung",
  replace(f.jfracht::varchar, '.', ',') as "Fracht [kg/a]",
  ls.ktext as "Stoffname",
  lk.ktext as "Kompartiment",
  lt.schl as "Schlüssel",
  lt.ktext as "Tätigkeit Anh. I",
  lbg.name as "Branchengruppe"

from p_betrieb p
  left join p_freis f
    on p.id = f.p_betrieb_id
  left join l_stoffe ls
    on f.stoffnr_id = ls.id
  left join l_kompartimente lk
    on f.medium_id = lk.id

  left join p_taet t
    on p.id = t.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr lt
    on t.nrprtr_id = lt.id
  left join l_prtr_branchengruppen lbg
    on lt.prtr_branchengruppe_id = lbg.id

where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and stoffnr_id is not null
  and t.htprtr = 1
--  and t.nrprtr_id <> 3 -- nicht 1.c (GFA)
--  and lbg.id = 1 -- Energiesektor
-- group by ls.ktext, lk.ktext
order by p.kennnr, f.jfracht desc;$,


    "Alle Verbringungen von Schadstoffen mit dem Abwasser" =>
      %q$select
  coalesce(p.kennnr,'') as "Kennnummer",
  coalesce (p.name1,'') || ' ' || coalesce(p.name2,'') as "Betriebseinrichtung",
  replace(vaw.jfracht::varchar, '.', ',') as "Fracht [kg/a]",
  ls.ktext as "Stoffname",
  lt.schl as "Schlüssel",
  lt.ktext as "Tätigkeit Anh. I",
  lbg.name as "Branchengruppe"

from p_betrieb p
  left join p_vabw vaw
    on p.id = vaw.p_betrieb_id
  left join l_stoffe ls
    on vaw.stoffnr_id = ls.id

  left join p_taet t
    on p.id = t.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr lt
    on t.nrprtr_id = lt.id
  left join l_prtr_branchengruppen lbg
    on lt.prtr_branchengruppe_id = lbg.id

where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and stoffnr_id is not null
  and t.htprtr = 1
--  and t.nrprtr_id <> 3 -- nicht 1.c (GFA)
--  and lbg.id = 1 -- Energiesektor
-- group by ls.ktext, lk.ktext
order by p.kennnr, vaw.jfracht desc;$,

    "Alle Verbringungen von Abfällen" =>
      %q$select
  coalesce(p.kennnr,'') as "Kennnummer",
  coalesce (p.name1,'') || ' ' || coalesce(p.name2,'') as "Betriebseinrichtung",
  replace(vabf.menge::varchar, '.', ',') as "Menge [t/a]",
  ls.ktext as "Abfalltyp",
  lvb.ktext as "Disposition",
  lt.ktext as "Tätigkeit Anh. I",
  lbg.name as "Branchengruppe"

from p_betrieb p
  left join p_vabf vabf
    on p.id = vabf.p_betrieb_id
  left join l_abfalltypen ls
    on vabf.abfart_id = ls.id
  left join l_ia lia
    on vabf.aland_id = lia.id
  left join l_disposition_abfall lvb
    on vabf.verbes_id = lvb.id

  left join p_taet t
    on p.id = t.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr lt
    on t.nrprtr_id = lt.id
  left join l_prtr_branchengruppen lbg
    on lt.prtr_branchengruppe_id = lbg.id

where p.state = 0
  and p.date_from <= (2007*10000+101)
  and p.date_to   >= (2007*10000+1231)
  and vabf.abfart_id is not null
  and t.htprtr = 1
--  and t.nrprtr_id <> 3 -- nicht 1.c (GFA)
--  and lbg.id = 1 -- Energiesektor
-- group by ls.ktext, lk.ktext
order by p.kennnr, vabf.menge desc;$
    }

  def index

  end

  def gen_info
    @gen_info = []
    for row in GENERELLE_INFORMATIONEN do
      @gen_info.push([row.first, ActiveRecord::Base.connection.execute(row.last)])
    end
  end

  def single
    @values = ActiveRecord::Base.connection.execute($SPEZIELLE_INFORMATIONEN[params[:page]])
  end

  def all
    gen_info
    @tables = []
    for table in $SPEZIELLE_INFORMATIONEN.keys do
      @tables.push([table, ActiveRecord::Base.connection.execute($SPEZIELLE_INFORMATIONEN[table])])
    end
  end
  
end
