#encoding: utf-8
class InkonsistenzenController < ApplicationController
  before_action :login_required
  layout "application"
  PLURAL = "Inkonsistenzen"

  include ApplicationHelper

  # QS-Konstanten setzen
  # Hier: Abstände, bei deren Überschreitung Fehler bzw. Warnung ausgelöst werden
  @@error_distance = 1000
  @@warning_distance = 500
  @@warning_vorperidenaenderung = 0.3   # 30% mehr oder 30% weniger
  @@warning_vorperidenfaktor_klein = 1.3   # 30% mehr oder 23% weniger
  @@warning_vorperidenfaktor_mittel = 1.6   # 60% mehr oder 37.5% weniger
  @@warning_vorperidenfaktor_gross  = 2.0   # 100% mehr oder 50% weniger

  # qs-errors
  QS_ERROR=[
    {
      :id => 'uvag',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Unvollständige aktive Agglomerationen (1.v.1.a)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unvollst.C3.A4ndige_aktive_Objekte_.281.v.1.a.2C_1.v.6.2C_1.p.5.iii.2C_2.29',
      :count_on => 'a.id',
      :where => %q^" AND agglomerations.rep_code = '#{rep_code}'
    AND agglomerations.ignore_all_qs_checks IS DISTINCT FROM 1
    AND agg_state_active IS DISTINCT FROM 0
    AND agglomerations.ag_com_report IS DISTINCT FROM 0
    AND (
      #{get_keys('agglomerations')}
      OR agglomerations.ag_com_report IS NULL
      OR #{get_wrong_values('agglomerations')}
      OR (ag_to_be_reported IS DISTINCT FROM 1 AND agg_state_active IS DISTINCT FROM 1)
      OR (
        ( #{get_required('agglomerations')}
          OR (ag_change_nload = 1 AND ag_remark_change IS NULL)
          OR (ag_nload > 100000
            AND agg_c2*ag_nload > 200000
            AND (agg_c2_pt IS NULL
              OR agg_c2_st IS NULL
              OR agg_c2_ft IS NULL
            )
          )
          OR (ag_city = 1 AND pl_city_id IS NULL)
          OR (agg_not_collected > 0 AND agg_date_coll IS NULL)
        )
        AND (ag_to_be_reported IS DISTINCT FROM 0
          OR ag_nload IS NULL
        )
      )
    )
    AND (agglomerations.federal_state_id IN (#{states}) OR agglomerations.handled_by_federal_state_id IN (#{states}))"^,
      :datensatz => 'agglomerations'
    },
    {
        :id => 'uviag',
        :group => 'gr__unvollstaendig',
        :heading => 'Vollständigkeit',
        :title => 'Unvollständige inaktive Agglomerationen (1.v.1.b)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unvollst.C3.A4ndige_inaktive_Objekte_.281.v.1.b.29',
        :count_on => 'a.id',
        :where => %q^" AND agglomerations.rep_code = '#{rep_code}'
    AND agglomerations.ignore_all_qs_checks IS DISTINCT FROM 1
    AND agg_state_active = 0
    AND agglomerations.ag_com_report IS DISTINCT FROM 0
    AND (
      #{get_keys('agglomerations')}
      OR agglomerations.ag_com_report IS NULL
      OR #{get_wrong_values('agglomerations')}
      OR (ag_to_be_reported IS DISTINCT FROM 1 AND agg_state_active IS DISTINCT FROM 1)
      OR (
        (#{get_required('agglomerations')}
          OR (ag_change_nload = 1 AND ag_remark_change IS NULL)
          OR (ag_nload > 100000
            AND agg_c2*ag_nload > 200000
            AND (agg_c2_pt IS NULL
              OR agg_c2_st IS NULL
              OR agg_c2_ft IS NULL))
          OR (ag_city = 1 AND pl_city_id IS NULL)
          OR (agg_not_collected > 0 AND agg_date_coll IS NULL))
        AND (ag_to_be_reported IS DISTINCT FROM 0
          OR ag_nload IS NULL)
      )
    )
    AND (agglomerations.federal_state_id IN (#{states}) OR agglomerations.handled_by_federal_state_id IN (#{states}))"^,
        :datensatz => 'agglomerations'
    },
    {
      :id => 'uvpl',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Unvollständige aktive Anlagen (1.v.1.a)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unvollst.C3.A4ndige_aktive_Objekte_.281.v.1.a.2C_1.v.6.2C_1.p.5.iii.2C_2.29',
      :count_on => 'p.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
    AND ignore_all_qs_checks IS DISTINCT FROM 1
    AND plants.pl_com_report IS DISTINCT FROM 0
    AND (
      #{get_keys('plants')}
      OR #{get_wrong_values('plants')}
      OR plants.pl_com_report IS NULL
      OR (pl_to_be_reported IS DISTINCT FROM 1 AND pl_state_active IS DISTINCT FROM 1)
      OR (
        (#{get_required('plants')}
          OR (pl_odc > 10000 AND pl_n_anh1_id IS NULL)
          OR (pl_odc > 10000 AND pl_ncom_id IS NULL)
          OR (pl_odc > 10000 AND pl_pcom_id IS NULL)
          OR (pl_odc > 10000 AND pl_p_anh1_id IS NULL)
          OR ((pl_cod IS NULL OR pl_cod <= 0) AND (pl_cod100 IS NULL OR pl_cod100 <= 0))
          OR ((pl_bod IS NULL OR pl_bod <= 0) AND (pl_bod100 IS NULL OR pl_bod100 <= 0))
          OR (pl_odc > 10000 AND (pl_n IS NULL OR pl_n <= 0) AND (pl_n100 IS NULL OR pl_n100 <= 0))
          OR (pl_odc > 10000 AND (pl_p IS NULL OR pl_p <= 0) AND (pl_p100 IS NULL OR pl_p100 <= 0))
          OR (pl_other = 1 AND pl_other_comment IS NULL))
        AND (ignore_completeness IS DISTINCT FROM 1)
        AND (pl_to_be_reported IS DISTINCT FROM 0)
        AND (pl_state_active IS DISTINCT FROM 0
          OR pl_cd IS NULL)))
    AND (plants.federal_state_id IN (#{states}) OR plants.handled_by_federal_state_id IN (#{states}))"^,
      :datensatz => 'plants'
    },
    {
        :id => 'uvipl',
        :group => 'gr__unvollstaendig',
        :heading => 'Vollständigkeit',
        :title => 'Unvollständige inaktive Anlagen (1.v.1.a)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unvollst.C3.A4ndige_inaktive_Objekte_.281.v.1.b.29',
        :count_on => 'p.id',
        :where => %q^" AND plants.rep_code = '#{rep_code}'
    AND ignore_all_qs_checks IS DISTINCT FROM 1
    AND plants.pl_com_report IS DISTINCT FROM 0
    AND pl_state_active = 0
    AND (
      #{get_keys('plants')}
      OR #{get_wrong_values('plants')}
      OR plants.pl_com_report IS NULL
      OR pl_to_be_reported IS DISTINCT FROM 1
      OR pl_cd IS NULL
      OR (
        (#{get_required('plants')}
          OR (pl_odc > 10000 AND pl_n_anh1_id IS NULL)
          OR (pl_odc > 10000 AND pl_ncom_id IS NULL)
          OR (pl_odc > 10000 AND pl_pcom_id IS NULL)
          OR (pl_odc > 10000 AND pl_p_anh1_id IS NULL)
          OR ((pl_cod IS NULL OR pl_cod <= 0) AND (pl_cod100 IS NULL OR pl_cod100 <= 0))
          OR ((pl_bod IS NULL OR pl_bod <= 0) AND (pl_bod100 IS NULL OR pl_bod100 <= 0))
          OR (pl_odc > 10000 AND (pl_n IS NULL OR pl_n <= 0) AND (pl_n100 IS NULL OR pl_n100 <= 0))
          OR (pl_odc > 10000 AND (pl_p IS NULL OR pl_p <= 0) AND (pl_p100 IS NULL OR pl_p100 <= 0))
          OR (pl_other = 1 AND pl_other_comment IS NULL)
        )
        AND ignore_completeness IS DISTINCT FROM 1
      )
    )
    AND (plants.federal_state_id IN (#{states}) OR plants.handled_by_federal_state_id IN (#{states}))"^,
        :datensatz => 'plants'
    },
    {
      :id => 'uvdp',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Unvollständige aktive Einleitstellen (1.v.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unvollst.C3.A4ndige_aktive_Objekte_.281.v.1.a.2C_1.v.6.2C_1.p.5.iii.2C_2.29',
      :count_on => 'dp.id',
      :where => %q^" AND discharge_points.rep_code = '#{rep_code}'
    AND discharge_points.ignore_all_qs_checks IS DISTINCT FROM 1
    AND discharge_points.dp_com_report IS DISTINCT FROM 0
    AND dp_state_active IS DISTINCT FROM 0
    AND (
      #{get_keys('discharge_points')}
      OR #{get_wrong_values('discharge_points')}
      OR discharge_points.dp_com_report IS NULL
      OR (dp_to_be_reported IS DISTINCT FROM 1 AND dp_state_active IS DISTINCT FROM 1)
      OR (
        ( #{get_required('discharge_points')}
          OR dp_waterbody_id IS NULL AND dp_gwbody_id IS NULL
          OR dp_surface_sp_id IN (1, 5) AND dp_gwbody_id IS NULL
          OR dp_surface_sp_id IN (1, 5) AND dp_surface_land_id IS NULL
          OR dp_surface_sp_id IN (2, 3, 4) AND dp_waterbody_id IS NULL
        )
        AND (discharge_points.ignore_completeness IS DISTINCT FROM 1)
        AND (dp_to_be_reported IS DISTINCT FROM 0
          OR plant_id IS NULL
        )
      )
    )
    AND discharge_points.dp_com_report IS DISTINCT FROM 0
    AND (discharge_points.federal_state_id IN (#{states}) OR discharge_points.handled_by_federal_state_id IN (#{states}))"^,
      :datensatz => 'discharge_points'
    },
    {
        :id => 'uvidp',
        :group => 'gr__unvollstaendig',
        :heading => 'Vollständigkeit',
        :title => 'Unvollständige inaktive Einleitstellen (1.v.1)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unvollst.C3.A4ndige_inaktive_Objekte_.281.v.1.b.29',
        :count_on => 'dp.id',
        :where => %q^" AND discharge_points.rep_code = '#{rep_code}'
    AND discharge_points.ignore_all_qs_checks IS DISTINCT FROM 1
    AND discharge_points.dp_com_report IS DISTINCT FROM 0
    AND dp_state_active = 0
    AND (
      #{get_keys('discharge_points')}
      OR #{get_wrong_values('discharge_points')}
      OR discharge_points.dp_com_report IS NULL
      OR (dp_to_be_reported IS DISTINCT FROM 1 AND dp_state_active IS DISTINCT FROM 1)
      OR (
        (#{get_required('discharge_points')}
          OR dp_waterbody_id IS NULL AND dp_gwbody_id IS NULL
          OR dp_surface_sp_id IN (1, 5) AND dp_gwbody_id IS NULL
          OR dp_surface_sp_id IN (1, 5) AND dp_surface_land_id IS NULL
          OR dp_surface_sp_id IN (2, 3, 4) AND dp_waterbody_id IS NULL
        )
        AND (discharge_points.ignore_completeness IS DISTINCT FROM 1)
        AND (dp_to_be_reported IS DISTINCT FROM 0
          OR plant_id IS NULL
        )
      )
    )
    AND (discharge_points.federal_state_id IN (#{states}) OR discharge_points.handled_by_federal_state_id IN (#{states}))"^,
        :datensatz => 'discharge_points'
    },
    {
      :id => 'uvfs',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Unvollständige Länderdaten (1.v.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unvollst.C3.A4ndige_Eintr.C3.A4ge_.281.v.1.2C_1.v.6.2C_1.p.5.iii.2C_2.29',
      :count_on => 'fs.id',
      :where => %q^" AND federal_state_info.rep_code = '#{rep_code}'
    AND (
      #{get_keys('federal_state_info')}
      OR (#{get_required('federal_state_info')}))"^,
      :datensatz => 'federal_state_info'
    },
    {
      :id => 'fatt',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Fehlende Angaben zur weitergehenden Behandlung in der Kläranlage (1.v.4)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Fehlende_Angaben_zur_weitergehenden_Behandlung_in_der_Kl.C3.A4ranlage_.281.v.4.29',
      :count_on => 'p.id',
      :statement => '" FROM plants p
  JOIN model.reporting_periods rp
    ON p.rep_code = rp.key_report
  LEFT JOIN l_fac_type ft on pl_tt_id = ft.id
  WHERE rp.state = 1
    AND p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND p.pl_com_report IS DISTINCT FROM 0
    AND federal_state_id in (#{states})
    AND pl_state_active = 1
    AND pl_to_be_reported = 1
    AND (pl_tt_id = 3
      AND pl_n_tech = 0
      AND pl_p_tech = 0
      AND pl_uv = 0
      AND pl_chlorination = 0
      AND pl_ozonation = 0
      AND pl_sand_filtration = 0
      AND pl_micro_filtration = 0
      AND pl_other = 0)"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'pl_tt', :spezial => 'ft.key', :human_name => 'Art der Behandlung'},
        {:col_name => 'p.pl_n_tech', :human_name => 'N-Eliminierung', :type => 3},
        {:col_name => 'p.pl_p_tech', :human_name => 'P-Eliminierung', :type => 3},
        {:col_name => 'p.pl_uv', :human_name => 'UV-Desinfektion', :type => 3},
        {:col_name => 'p.pl_chlorination', :human_name => 'Chlorung', :type => 3},
        {:col_name => 'p.pl_ozonation', :human_name => 'Ozonierung', :type => 3},
        {:col_name => 'p.pl_sand_filtration', :human_name => 'Sandfiltration', :type => 3},
        {:col_name => 'p.pl_micro_filtration', :human_name => 'Mikro-/Ultrafiltration', :type => 3},
        {:col_name => 'p.pl_other', :human_name => 'andere Verfahren', :type => 3},


      ],
      :datensatz => 'plants'
    },
    {
      :id => 'agoetrs',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Agglomerationen ohne verwertbare Koordinaten (1.v.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Objekte_ohne_verwertbare_Koordinaten_.281.v.1.29',
      :count_on => 'a.id',
      :where => %q^" AND agglomerations.rep_code = '#{rep_code}'
        AND agglomerations.ignore_all_qs_checks IS DISTINCT FROM 1
        AND agglomerations.ag_com_report IS DISTINCT FROM 0
        AND agglomerations.federal_state_id in (#{states})
        AND agglomerations.ag_to_be_reported IS DISTINCT FROM 0
        AND agglomerations.agg_state_active IS DISTINCT FROM 0
        AND agglomerations.geo_etrs89_utm32 IS NULL"^,
      :datensatz => 'agglomerations'
    },
    {
      :id => 'ploetrs',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Anlagen ohne verwertbare Koordinaten (1.v.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Objekte_ohne_verwertbare_Koordinaten_.281.v.1.29',
      :count_on => 'p.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.pl_com_report IS DISTINCT FROM 0
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND plants.geo_etrs89_utm32 IS NULL
        AND plants.federal_state_id IN (#{states})"^,
      :datensatz => 'plants'
    },
    {
      :id => 'dpoetrs',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Einleitstellen ohne verwertbare Koordinaten (1.v.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Objekte_ohne_verwertbare_Koordinaten_.281.v.1.29',
      :count_on => 'dp.id',
      :where => %q^" AND discharge_points.rep_code = '#{rep_code}'
        AND discharge_points.ignore_all_qs_checks IS DISTINCT FROM 1
        AND discharge_points.dp_com_report IS DISTINCT FROM 0
        AND discharge_points.dp_to_be_reported IS DISTINCT FROM 0
        AND discharge_points.dp_state_active IS DISTINCT FROM 0
        AND discharge_points.geo_etrs89_utm32 IS NULL
        AND discharge_points.federal_state_id IN (#{states})"^,
      :datensatz => 'discharge_points'
    },
    {
        :id => 'oiagg',
        :group => 'gr__unvollstaendig',
        :heading => 'Vollständigkeit',
        :title => 'Bereits als inaktiv gemeldete Agglomerationen (1.p.2iii)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Bereits_als_inaktiv_gemeldete_Objekte_.281.p.2iii.29',
        :count_on => 'a.id',
        :statement => '" FROM agglomerations a
          JOIN model.reporting_periods rp ON a.rep_code = rp.key_report
          WHERE rp.state = 1
            AND a.federal_state_id in (#{states})
            AND a.ignore_all_qs_checks IS DISTINCT FROM 1
            AND (
        SELECT count(id) FROM agglomerations
        JOIN model.reporting_periods rp2 ON rep_code = rp2.key_report
        WHERE agg_state_active = 0 AND agg_eu_key = a.agg_eu_key AND rp2.date_from = (
                SELECT max(date_from) FROM model.reporting_periods
                WHERE state = 0 AND date_from < rp.date_from
                )
        ) != 0"',
        :show_columns => [
            {:row_id => 'a.id', :type => :link},
            {:col_name => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
            {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
            {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
            {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
            {:spezial => %Q^ARRAY_TO_STRING( ARRAY( SELECT id || '|' || rep_code||': '||agg_eu_key||' - '||ag_name FROM agglomerations
    JOIN model.reporting_periods rp2 ON rep_code = rp2.key_report WHERE agg_state_active = 0 AND agg_eu_key = a.agg_eu_key AND rp2.date_from = (
                SELECT max(date_from) FROM model.reporting_periods
                WHERE state = 0 AND date_from < rp.date_from
                )), '\n')^,
          :col_name => 'inactive_previous', :human_name => 'Bereits als inaktiv gemeldete Agglomeration',
          :url_options => {:controller => 'agglomerations', :action => 'edit'},
          :type => :link_list
            }
        ],
        :datensatz => 'agglomerations'
    },
    {
        :id => 'oipl',
        :group => 'gr__unvollstaendig',
        :heading => 'Vollständigkeit',
        :title => 'Bereits als inaktiv gemeldete Anlagen (1.p.2iii)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Bereits_als_inaktiv_gemeldete_Objekte_.281.p.2iii.29',
        :count_on => 'p.id',
        :statement => '" FROM plants p
          JOIN model.reporting_periods rp ON p.rep_code = rp.key_report
          WHERE rp.state = 1
            AND p.federal_state_id in (#{states})
            AND p.ignore_all_qs_checks IS DISTINCT FROM 1
            AND (
        SELECT count(id) FROM plants
        JOIN model.reporting_periods rp2 ON rep_code = rp2.key_report
        WHERE pl_state_active = 0 AND pl_eu_key = p.pl_eu_key AND rp2.date_from = (
                SELECT max(date_from) FROM model.reporting_periods
                WHERE state = 0 AND date_from < rp.date_from
                )
        ) != 0"',
        :show_columns => [
            {:row_id => 'p.id', :type => :link},
            {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
            {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
            {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
            {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
            {:spezial => %Q^array_to_string(ARRAY(SELECT id || '|' || rep_code||': '||pl_eu_key||' - '||pl_name FROM plants
    JOIN model.reporting_periods rp2 ON rep_code = rp2.key_report WHERE pl_state_active = 0 AND pl_eu_key = p.pl_eu_key AND rp2.date_from = (
                SELECT max(date_from) FROM model.reporting_periods
                WHERE state = 0 AND date_from < rp.date_from
                )), '\n')^,
          :col_name => 'inactive_previous', :human_name => 'Bereits als inaktiv gemeldete Anlage',
          :url_options => {:controller => 'plants', :action => 'edit'},
          :type => :link_list
            }
        ],
        :datensatz => 'plants'
    },
    {
        :id => 'oidp',
        :group => 'gr__unvollstaendig',
        :heading => 'Vollständigkeit',
        :title => 'Bereits als inaktiv gemeldete Einleitstellen (1.p.2iii)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Bereits_als_inaktiv_gemeldete_Objekte_.281.p.2iii.29',
        :count_on => 'dp.id',
        :statement => '" FROM discharge_points dp
          JOIN model.reporting_periods rp ON dp.rep_code = rp.key_report
          WHERE rp.state = 1
            AND dp.federal_state_id in (#{states})
            AND dp.ignore_all_qs_checks IS DISTINCT FROM 1
            AND (
        SELECT count(id) FROM discharge_points
        JOIN model.reporting_periods rp2 ON rep_code = rp2.key_report
        WHERE dp_state_active = 0 AND dp_eu_key = dp.dp_eu_key AND rp2.date_from = (
                SELECT max(date_from) FROM model.reporting_periods
                WHERE state = 0 AND date_from < rp.date_from
                )
        ) != 0"',
        :show_columns => [
            {:row_id => 'dp.id', :type => :link},
            {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
            {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
            {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
            {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
            {:spezial => %Q^array_to_string(ARRAY(SELECT id || '|' || rep_code||': '||dp_eu_key||' - '||dp_name FROM discharge_points
    JOIN model.reporting_periods rp2 ON rep_code = rp2.key_report WHERE dp_state_active = 0 AND dp_eu_key = dp.dp_eu_key AND rp2.date_from = (
                SELECT max(date_from) FROM model.reporting_periods
                WHERE state = 0 AND date_from < rp.date_from
                )), '\n')^,
          :col_name => 'inactive_previous', :human_name => 'Bereits als inaktiv gemeldete Einleitstelle',
          :url_options => {:controller => 'discharge_points', :action => 'edit'},
          :type => :link_list
            }
        ],
        :datensatz => 'discharge_points'
    },
    {
      :id => 'neag',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Nicht mehr vorhandene EU-Schlüssel für Agglomerationen (1.v.2)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Nicht_mehr_vorhandene_EU-Schl.C3.BCssel_f.C3.BCr_Agglomerationen_.281.v.2.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
  left join agglomerations a2
    on a.agg_eu_key = a2.agg_eu_key
    AND a2.rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 0)
  where a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND a.agg_state_active = 1
    AND a.ag_to_be_reported = 1
    AND a.rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 1)
    AND a2.id IS NULL
    AND a.federal_state_id in (#{states})"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.rep_code', :human_name => 'Report ID'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'successors', :spezial => %Q^array_to_string(ARRAY(SELECT a.id || '|' || agg_eu_key ||' - '|| ag_name FROM agglomerations_successors ap
    LEFT JOIN agglomerations a ON ap.agglomeration_succ_id = a.id
    where ap.agglomeration_pre_id = a.id), '\n')^, :human_name => 'Nachfolger'},
        {:col_name => 'candidates', :spezial => %Q^array_to_string(ARRAY(SELECT id || '|' || rep_code||': '||agg_eu_key||' - '||ag_name FROM agglomerations
    WHERE rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 0)
                AND (ag_name ILIKE '%'||a.ag_name||'%'
                OR (ag_lat > a.ag_lat - 100
                AND ag_lat < a.ag_lat + 100
                AND ag_long > a.ag_long - 100
                AND ag_long < a.ag_long + 100))), '\n')^, :human_name => 'Kandidaten',
          :url_options => {:controller => 'agglomerations', :action => 'edit'},
          :type => :link_list}
      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'neap',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Nicht mehr vorhandene EU-Schlüssel für Anlagen (1.v.2)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Nicht_mehr_vorhandene_EU-Schl.C3.BCssel_f.C3.BCr_Anlagen_.281.v.2.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  left join plants p2
    on p.pl_eu_key = p2.pl_eu_key
    AND p2.rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 0)
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND p.pl_state_active = 1
    AND p.pl_to_be_reported = 1
    AND p.rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 1)
    AND p2.id IS NULL
    AND p.federal_state_id in (#{states})"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'successors', :spezial => %Q^array_to_string(ARRAY(SELECT a.id || '|' || pl_eu_key ||' - '|| pl_name FROM plants_successors ap
    LEFT JOIN plants a ON ap.plant_succ_id = a.id
    where ap.plant_pre_id = p.id), '\n')^, :human_name => 'Nachfolger',
          :url_options => {:controller => 'plants', :action => 'edit'},
          :type => :link_list},
        {:col_name => 'candidates', :spezial => %Q^array_to_string(ARRAY(SELECT id || '|' || rep_code||': '||pl_eu_key||' - '||pl_name FROM plants
    WHERE rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 0)
                AND (pl_name ILIKE '%'||p.pl_name||'%'
                OR (pl_lat > p.pl_lat - 100
                AND pl_lat < p.pl_lat + 100
                AND pl_long > p.pl_long - 100
                AND pl_long < p.pl_long + 100))), '\n')^, :human_name => 'Kandidaten',
          :url_options => {:controller => 'plants', :action => 'edit'},
          :type => :link_list
          }
      ],
      :datensatz => 'plants'
    },
    {
      :id => 'nedp',
      :group => 'gr__unvollstaendig',
      :heading => 'Vollständigkeit',
      :title => 'Nicht mehr vorhandene EU-Schlüssel für Einleitstellen (1.v.2)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Nicht_mehr_vorhandene_EU-Schl.C3.BCssel_f.C3.BCr_Einleitstellen_.281.v.2.29',
      :count_on => 'dp.id',
      :statement => '" from discharge_points dp
  left join discharge_points dp2
    on dp.dp_eu_key = dp2.dp_eu_key
    AND dp2.rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 0)
  where dp.ignore_all_qs_checks IS DISTINCT FROM 1
    AND dp.dp_state_active = 1
    AND dp.dp_to_be_reported = 1
    AND dp.rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 1)
    AND dp2.id IS NULL
    AND dp.federal_state_id in (#{states})"',
      :show_columns => [
        {:row_id => 'dp.id', :type => :link},
        {:col_name => 'dp.rep_code', :human_name => 'Report ID'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'successors', :spezial => %Q^array_to_string(ARRAY(SELECT a.id || '|' || dp_eu_key ||' - '|| dp_name FROM discharge_points_successors ap
    LEFT JOIN discharge_points a ON ap.discharge_point_succ_id = a.id
    where ap.discharge_point_pre_id = dp.id), \'\n\')^, :human_name => 'Nachfolger',
          :url_options => {:controller => 'discharge_points', :action => 'edit'},},
        {:col_name => 'candidates', :spezial => %Q^array_to_string(ARRAY(SELECT id || '|' || rep_code||': '||dp_eu_key||' - '||dp_name FROM discharge_points
    WHERE rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 0)
                AND (dp_name ILIKE '%'||dp.dp_name||'%'
                OR (dp_lat > dp.dp_lat - 100
                AND dp_lat < dp.dp_lat + 100
                AND dp_long > dp.dp_long - 100
                AND dp_long < dp.dp_long + 100))), '\n')^, :human_name => 'Kandidaten',
          :url_options => {:controller => 'discharge_points', :action => 'edit'},
          :type => :link_list
          }
      ],
      :datensatz => 'discharge_points'
    },
    {
        :id => 'nasw',
        :group => 'gr__unvollstaendig',
        :heading => 'Vollständigkeit',
        :title => 'Nicht mehr aktuelle Wasserkörper (1.p.8i)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Nicht_mehr_aktuelle_Wasserk.C3.B6rper_.281.p.8i.29',
        :count_on => 'dp.id',
        :statement => '" from discharge_points dp
            LEFT JOIN l_water_body_code lw
              ON dp.dp_waterbody_id = lw.id
            LEFT JOIN model.reporting_periods rp
              ON dp.rep_code = rp.key_report
            WHERE dp.ignore_all_qs_checks IS DISTINCT FROM 1
            AND rp.state = 1
            AND dp.federal_state_id in (#{states})
            AND ( rp.date_from < lw.date_from OR rp.date_to > lw.date_to )"',
        :show_columns => [
            {:row_id => 'dp.id', :type => :link},
            {:col_name => 'dp.rep_code', :human_name => 'Report ID'},
            {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
            {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
            {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
            {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'rp.date_from', :human_name => 'Berichtsperiode gültig von'},
            {:col_name => 'rp.date_to', :human_name => 'Berichtsperiode gültig bis'},
            {:spezial  => 'lw.date_from', :col_name=> 'lw_from', :human_name => 'Gewässer gültig von'}, # da der Tabellenpräfix weggeschnitten wird, ->
            {:spezial  => 'lw.date_to', :col_name=> 'lw_to', :human_name => 'Gewässer gültig bis'},     # muss hier ein alternativer name angegeben werden
            {:col_name => 'lw.description', :human_name => 'Name des Gewässers'}
        ],
        :datensatz => 'discharge_points'
    },

    {
        :id => 'nagw',
        :group => 'gr__unvollstaendig',
        :heading => 'Vollständigkeit',
        :title => 'Nicht mehr aktuelle Grundwasserkörper (1.p.8ii)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Nicht_mehr_aktuelle_Grundwasserk.C3.B6rper_.281.p.8ii.29',
        :count_on => 'dp.id',
        :statement => '" from discharge_points dp
            LEFT JOIN l_groundwater_body gb
              ON dp.dp_gwbody_id = gb.id
            LEFT JOIN model.reporting_periods rp
              ON dp.rep_code = rp.key_report
            WHERE dp.ignore_all_qs_checks IS DISTINCT FROM 1
              AND dp.dp_gwbody_id NOTNULL
              AND dp.federal_state_id in (#{states})
              AND rp.state = 1
            AND ( rp.date_from < gb.date_from OR rp.date_to > gb.date_to )"',
        :show_columns => [
            {:row_id => 'dp.id', :type => :link},
            {:col_name => 'dp.rep_code', :human_name => 'Report ID'},
            {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
            {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
            {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
            {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'rp.date_from', :human_name => 'Berichtsperiode gültig von'},
            {:col_name => 'rp.date_to', :human_name => 'Berichtsperiode gültig bis'},
            {:spezial  => 'gb.date_from', :col_name=> 'gb_from', :human_name => 'Grundwasserkörper gültig von'}, # da der Tabellenpräfix weggeschnitten wird, ->
            {:spezial  => 'gb.date_to', :col_name=> 'gb_to', :human_name => 'Grundwasserkörper gültig bis'},     # muss hier ein alternativer name angegeben werden
            {:col_name => 'gb.description', :human_name => 'Name des Grundwasserkörpers'}
        ],
        :datensatz => 'discharge_points'
    },

    {
      :id => 'aoag',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Anlagen ohne Agglomerationen (1.v.5.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_ohne_Agglomerationen_.281.v.5.1.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  left join agglomerations_plants ap
    on  p.id = ap.plant_id
  left join agglomerations a
    on ap.agglomeration_id = a.id
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (ap.id IS NULL)
    AND rp.state = 1
    AND p.pl_to_be_reported IS DISTINCT FROM 0
    AND p.pl_state_active IS DISTINCT FROM 0
    AND p.pl_com_report IS DISTINCT FROM 0
    AND p.federal_state_id in (#{states})"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel der Agglomeration'},
      ],
      :datensatz => 'plants'
    },
    {
      :id => 'npl2k',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Anlagen ohne Agglomerationen >= 2000 EW (1.v.5.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_ohne_Agglomerationen_.3E.3D_2000_EW',
      :count_on => 'pl.id',
      :statement => '" FROM plants pl
          LEFT JOIN model.reporting_periods rp ON pl.rep_code = rp.key_report
          WHERE pl.federal_state_id IN (#{states})
          AND rp.state=1 AND pl.pl_to_be_reported = 1 AND pl_state_active = 1
          AND ignore_all_qs_checks IS DISTINCT FROM 1
          AND pl.pl_com_report IS DISTINCT FROM 0
          AND (
                 SELECT COUNT(*) FROM agglomerations_plants ap
                 LEFT JOIN agglomerations agg ON agg.id = ap.agglomeration_id
                 WHERE ap.plant_id = pl.id
                 AND agg.ag_nload >= 2000
                 AND agg.agg_state_active = 1
          ) = 0
          "',
      :show_columns => [
        {:row_id   => 'pl.id', :type => :link },
        {:col_name => 'pl.rep_code', :human_name => 'Report ID'},
        {:col_name => 'pl.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'pl.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'pl.pl_eu_key', :human_name => 'EU Schlüssel'},
      ],
      :datensatz => 'plants'
    },
    {
      :id => 'aiag',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Anlagen mit inaktiven Agglomerationen (1.v.5.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_mit_inaktiven_Agglomerationen_.281.v.5.1.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  left join agglomerations_plants ap
    on  p.id = ap.plant_id
  left join agglomerations a
    on ap.agglomeration_id = a.id
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (a.agg_state_active = 0)
    AND rp.state = 1
    AND p.pl_to_be_reported IS DISTINCT FROM 0
    AND p.pl_state_active IS DISTINCT FROM 0
    AND p.federal_state_id in (#{states})"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:row_id => 'a.id', :type => :link, :col_name => 'agg_id', :controller => 'agglomerations'},
        {:col_name => 'agg_rep_code', :spezial => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'plants'
    },
    {
      :id => 'aaag',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Anlagen mit alten Agglomerationen (interne Prüfung)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_mit_alten_Agglomerationen_.28interne_Pr.C3.BCfung.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  left join agglomerations_plants ap
    on  p.id = ap.plant_id
  left join agglomerations a
    on ap.agglomeration_id = a.id
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (not a.rep_code = p.rep_code)
    AND rp.state = 1
    AND (p.federal_state_id in (#{states}) OR p.handled_by_federal_state_id in (#{states}))"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:row_id => 'a.id', :type => :link, :col_name => 'agg_id', :controller => 'agglomerations'},
        {:col_name => 'agg_rep_code', :spezial => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'plants'
    },
    {
      :id => 'pl2k',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Anlagen mit Agglomeration >= 2000 EW, die nicht berichtet werden (1.v.5.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_mit_Agglomeration_.3E.3D_2000_EW.2C_die_nicht_berichtet_werden_.281.v.5.1.29',
      :count_on => 'pl.id',
      :statement => '" from agglomerations agg
          left join model.reporting_periods rp on agg.rep_code = rp.key_report
          left join public.agglomerations_plants ap on agg.id = ap.agglomeration_id
          left join plants pl on ap.plant_id = pl.id
          where agg.ignore_all_qs_checks IS DISTINCT FROM 1
            AND agg.ag_nload >= 2000
            AND agg.agg_state_active = 1
            AND rp.state=1
            AND pl.pl_to_be_reported IS DISTINCT FROM 1
            AND pl.federal_state_id in (#{states})
          "',
      :show_columns => [
        {:row_id   => 'pl.id', :type => :link },
        {:col_name => 'agg.rep_code', :human_name => 'Report ID'},
        {:col_name => 'pl.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'pl.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'pl.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'pl.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'agg.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'agg.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'agg.ag_nload', :human_name => 'Nominalbelastung'},
        {:col_name => 'agg.agg_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'plants'

    },
    {
      :id => 'agoa',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Agglomerationen ohne Anlagen (1.v.5.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomerationen_ohne_Anlagen_.281.v.5.1.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
  left join agglomerations_plants ap
    on a.id = ap.agglomeration_id
  left join plants p
    on ap.plant_id = p.id
  join model.reporting_periods rp
    on a.rep_code = rp.key_report
  where a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (ap.id IS NULL)
    AND rp.state = 1
    --AND a.ag_to_be_reported IS DISTINCT FROM 0
    AND a.agg_state_active IS DISTINCT FROM 0
    AND a.federal_state_id in (#{states})"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel der Anlage'},
      ],
      :datensatz => 'agglomerations'
  },
    {
      :id => 'agia',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Agglomerationen mit inaktiven Anlagen (1.v.5.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomerationen_mit_inaktiven_Anlagen_.281.v.5.1.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
  left join agglomerations_plants ap
    on a.id = ap.agglomeration_id
  left join plants p
    on ap.plant_id = p.id
  join model.reporting_periods rp
    on a.rep_code = rp.key_report
  where a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (p.pl_state_active = 0)
    AND rp.state = 1
    AND a.ag_com_report IS DISTINCT FROM 0
    --AND a.ag_to_be_reported IS DISTINCT FROM 0
    AND a.agg_state_active IS DISTINCT FROM 0
    AND a.federal_state_id in (#{states})"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:row_id => 'p.id', :type => :link, :col_name => 'pl_id', :controller => 'plants'},
        {:col_name => 'pl_rep_code', :spezial => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'agaa',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Agglomerationen mit alten Anlagen (interne Prüfung)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomerationen_mit_alten_Anlagen_.28interne_Pr.C3.BCfung.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
  left join agglomerations_plants ap
    on a.id = ap.agglomeration_id
  left join plants p
    on ap.plant_id = p.id
  join model.reporting_periods rp
    on a.rep_code = rp.key_report
  where a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (not p.rep_code = a.rep_code)
    AND rp.state = 1
    AND (a.federal_state_id in (#{states}) OR a.handled_by_federal_state_id in (#{states}))"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:row_id => 'p.id', :type => :link, :col_name => 'pl_id', :controller => 'plants'},
        {:col_name => 'pl_rep_code', :spezial => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'nagg2k',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Agglomerationen mit < 2000 EW, die berichtet werden (1.ü.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomerationen_mit_.3C_2000_EW.2C_die_berichtet_werden_.281..C3.BC.1.29',
      :count_on => 'agg.id',
      :statement => '" from public.agglomerations agg
          left join model.reporting_periods rp on agg.rep_code = rp.key_report
          where agg.ignore_all_qs_checks IS DISTINCT FROM 1
            AND agg.ag_com_report IS DISTINCT FROM 0
            AND agg.ag_nload < 2000
            AND agg.agg_state_active = 1
            AND rp.state=1
            AND agg.ag_to_be_reported = 1
            AND agg.federal_state_id in (#{states})
          "',
      :show_columns => [
         { :row_id   => 'agg.id', :type => :link },
        {:col_name => 'agg.rep_code', :human_name => 'Report ID'},
        {:col_name => 'agg.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'agg.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'agg.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'agg.ag_nload', :human_name => 'Nominalbelastung'},
        {:col_name => 'agg.ag_to_be_reported', :human_name => 'Berichtet?', :type => 3},
        {:col_name => 'agg.agg_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'agglomerations'

    },
    {
      :id => 'agg2k',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Agglomerationen mit >= 2000 EW, die nicht berichtet werden (1.v.7)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomerationen_mit_.3E.3D_2000_EW.2C_die_nicht_berichtet_werden_.281.v.7.29',
      :count_on => 'agg.id',
      :statement => '" from public.agglomerations agg
          left join model.reporting_periods rp on agg.rep_code = rp.key_report
          where agg.ignore_all_qs_checks IS DISTINCT FROM 1
            AND agg.ag_nload >= 2000
            AND agg.agg_state_active = 1
            AND rp.state=1
            AND agg.ag_to_be_reported IS DISTINCT FROM 1
            AND agg.federal_state_id in (#{states})
          "',
      :show_columns => [
         { :row_id   => 'agg.id', :type => :link },
        {:col_name => 'agg.rep_code', :human_name => 'Report ID'},
        {:col_name => 'agg.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'agg.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'agg.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'agg.ag_nload', :human_name => 'Nominalbelastung'},
        {:col_name => 'agg.ag_to_be_reported', :human_name => 'Berichtet?', :type => 3},
        {:col_name => 'agg.agg_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'agglomerations'

    },
        {
      :id => 'aodp',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Anlagen ohne Einleitstellen (1.v.5.2)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_ohne_Einleitstellen_.281.v.5.2.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  left join discharge_points dp
    on dp.plant_id = p.id
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (dp.id IS NULL)
    AND rp.state = 1
    AND p.pl_to_be_reported IS DISTINCT FROM 0
    AND p.pl_state_active IS DISTINCT FROM 0
    AND p.federal_state_id in (#{states})
    AND p.federal_state_id not in (17, 18)"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel der Einleitstelle'},
      ],
      :datensatz => 'plants'
    },
    {
      :id => 'aidp',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Anlagen mit inaktiven Einleitstellen (1.v.5.2)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_mit_inaktiven_Einleitstellen_.281.v.5.2.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  left join discharge_points dp
    on dp.plant_id = p.id
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (dp.dp_state_active = 0)
    AND rp.state = 1
    AND p.pl_to_be_reported IS DISTINCT FROM 0
    AND p.pl_state_active IS DISTINCT FROM 0
    AND p.federal_state_id in (#{states})
    AND p.federal_state_id not in (17, 18)"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:row_id => 'dp.id', :type => :link, :col_name => 'dp_id', :controller => 'discharge_points'},
        {:col_name => 'dp_rep_code', :spezial => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Anlage'},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Anlage'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'plants'
    },
    {
      :id => 'aadp',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Anlagen mit alten Einleitstellen (interne Prüfung)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_mit_alten_Einleitstellen_.281.v.5.2.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  left join discharge_points dp
    on dp.plant_id = p.id
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (not p.rep_code = dp.rep_code)
    AND rp.state = 1
    AND (p.federal_state_id in (#{states}) OR p.handled_by_federal_state_id in (#{states}))
    AND p.federal_state_id not in (17, 18)"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:row_id => 'dp.id', :type => :link, :col_name => 'dp_id', :controller => 'discharge_points'},
        {:col_name => 'dp_rep_code', :spezial => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Anlage'},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Anlage'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'plants'
    },
  {
    :id => 'dpip',
    :group => 'gr__inkonsistent',
    :heading => 'Konsistenz',
    :title => 'Einleitstellen mit inaktiven Anlagen (1.v.5.2)',
    :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Einleitstellen_mit_inaktiven_Anlagen_.281.v.5.2.29',
    :count_on => 'dp.id',
    :statement => '" from discharge_points dp
  left join plants p
    on dp.plant_id = p.id
  join model.reporting_periods rp
    on dp.rep_code = rp.key_report
  where dp.ignore_all_qs_checks IS DISTINCT FROM 1
    AND p.pl_state_active = 0
    AND dp.dp_state_active IS DISTINCT FROM 0
    AND dp.dp_com_report IS DISTINCT FROM 0
    AND rp.state = 1
    AND dp.federal_state_id in (#{states})
    AND dp.federal_state_id not in (17, 18)"',
      :show_columns => [
        {:row_id => 'dp.id', :type => :link},
        {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel der Einleitstelle'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Einleitstelle Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:row_id => 'p.id', :type => :link, :col_name => 'pl_id', :controller => 'plants'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel der Anlage'},
        {:col_name => 'p.pl_state_active', :human_name => 'Anlage Aktiv?', :type => 3},
      ],
     :datensatz => 'discharge_points'
    },
  {
    :id => 'dpap',
    :group => 'gr__inkonsistent',
    :heading => 'Konsistenz',
    :title => 'Einleitstellen mit alten Anlagen (interne Prüfung)',
    :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Einleitstellen_mit_alten_Anlagen_.28interne_Pr.C3.BCfung.29',
    :count_on => 'dp.id',
    :statement => '" from discharge_points dp
  left join plants p
    on dp.plant_id = p.id
  join model.reporting_periods rp
    ON dp.rep_code = rp.key_report
      AND rp.state = 1
  where dp.ignore_all_qs_checks IS DISTINCT FROM 1
    AND dp.dp_com_report IS DISTINCT FROM 0
    AND dp.rep_code IS DISTINCT FROM p.rep_code
    AND (dp.federal_state_id in (#{states}) OR dp.handled_by_federal_state_id in (#{states}))
    AND dp.federal_state_id not in (17, 18)"',
      :show_columns => [
        {:row_id => 'dp.id', :type => :link},
        {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel der Einleitstelle'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Einleitstelle Aktiv?', :type => 3},

        {:spezial => "'&nbsp;&nbsp;&nbsp;'", :col_name => 'space'},
        {:row_id => 'p.id', :type => :link, :col_name => 'pl_id', :controller => 'plants'},
        {:col_name => 'pl_rep_code', :spezial => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel der Anlage'},
        {:col_name => 'p.pl_state_active', :human_name => 'Anlage Aktiv?', :type => 3},
      ],
     :datensatz => 'discharge_points'
    },
    {
      :id => 'dpoa',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Einleitstellen ohne Anlagen (1.v.5.2)',
      # TODO
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Einleistellen_ohne_Anlagen_.281.v.5.2.29',
      :count_on => 'dp.id',
      :statement => '" from discharge_points dp
  JOIN model.reporting_periods rp
    ON dp.rep_code = rp.key_report
      AND rp.state = 1
  where dp.ignore_all_qs_checks IS DISTINCT FROM 1
    AND (dp.plant_id IS NULL)
    AND dp.dp_to_be_reported IS DISTINCT FROM 0
    AND dp.dp_state_active IS DISTINCT FROM 0
    AND (dp.federal_state_id in (#{states}) OR dp.handled_by_federal_state_id in (#{states}) ) "',
      :show_columns => [
        {:row_id => 'dp.id', :type => :link},
        {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstellen'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstellen'},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstellen'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'discharge_points'
    },
    {
      :id => 'eukag',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Agglomerationen mit Syntaxfehler im EU-Schlüssel (6.1.a)',
      # TODO
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomerationen_mit_Syntaxfehler_im_EU-Schl.C3.BCssel_.286.1.a.29',
      :count_on => 'agg.id',
      :statement => '" from agglomerations agg
  join model.reporting_periods rp
    on agg.rep_code = rp.key_report
  where rp.state = 1
    AND federal_state_id in (#{states})
    AND agg_state_active = 1
    AND ag_to_be_reported = 1
    AND ignore_all_qs_checks IS DISTINCT FROM 1
    AND agg_eu_key not like \'DEAG_%\'"',
      :show_columns => [
        {:row_id => 'agg.id', :type => :link},
        {:col_name => 'agg.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'agg.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'agg.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'agg.agg_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'agglomerations'
    },
    {
        :id => 'eukpl',
        :group => 'gr__inkonsistent',
        :heading => 'Konsistenz',
        :title => 'Anlagen mit Syntaxfehler im EU-Schlüssel (6.1.b)',
        # TODO
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_mit_Syntaxfehler_im_EU-Schl.C3.BCssel_.286.1.b.29',
        :count_on => 'p.id',
        :statement => '" from plants p
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND rp.state = 1
    AND federal_state_id in (#{states})
    AND pl_state_active = 1
    AND pl_to_be_reported = 1
    AND pl_eu_key not like \'DETP_%\'"',
        :show_columns => [
            {:row_id => 'p.id', :type => :link},
            {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
            {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
            {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
            {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        ],
        :datensatz => 'plants'
    },
    {
      :id => 'eukdp',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Einleitstellen mit Syntaxfehler im EU-Schlüssel (6.1.c)',
      # TODO
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Einleitstellen_mit_Syntaxfehler_im_EU-Schl.C3.BCssel_.286.1.c.29',
      :count_on => 'dp.id',
      :statement => '" from discharge_points dp
  join model.reporting_periods rp
    on dp.rep_code = rp.key_report
  where rp.state = 1
    AND federal_state_id in (#{states})
    AND dp_state_active = 1
    AND dp_to_be_reported = 1
    AND dp.ignore_all_qs_checks IS DISTINCT FROM 1
    AND dp_eu_key not like \'DEDP_%\'"',
      :show_columns => [
        {:row_id => 'dp.id', :type => :link},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Anlage'},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Anlage'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
      ],
      :datensatz => 'discharge_points'
    },
    {
        :id => 'nv_bigcities',
        :group => 'gr__inkonsistent',
        :heading => 'Konsistenz',
        :title => 'Alle Großstädte (Big Cities) müssen Verwendung finden (1.p.7.a)',
        # TODO
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Alle_Gro.C3.9Fst.C3.A4dte_.28Big_Cities.29_m.C3.BCssen_Verwendung_finden_.281.p.7.a.29',
        :count_on => 'lc.id',
        :statement => '" from l_cities lc
  JOIN model.reporting_periods mrp
    ON mrp.state = 1
      AND lc.date_from <= mrp.date_to
      AND lc.date_to >= mrp.date_to
  LEFT JOIN public.agglomerations agg
    ON agg.pl_city_id = lc.id
      AND agg.state = 0
      AND agg.agg_state_active = 1
      AND agg.ag_to_be_reported IS DISTINCT FROM 0
      AND agg.federal_state_id = lc.federal_state_id
  WHERE lc.state = 0
    AND lc.federal_state_id in (#{states})
    AND lc.ignore_all_qs_checks IS DISTINCT FROM 1
    AND agg.id IS NULL"',
        :show_columns => [
            # {:row_id => 'lc.id', :type => :link},
            {:col_name => 'lc.key', :human_name => 'BigCity Schlüssel'},
            {:col_name => 'lc.description', :human_name => 'BigCity Name'}
        ],
        :datensatz => 'cities'
    },
    {
        :id => 'pl_tbr_dp_ntbr',
        :group => 'gr__inkonsistent',
        :heading => 'Konsistenz',
        :title => 'Anlage wird berichtet, Einleitstelle wird jedoch nicht berichtet (6.3.a)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlage_wird_berichtet.2C_Einleitstelle_wird_jedoch_nicht_berichtet_.286.3.a.29',
        :count_on => 'p.id',
        :statement => %q^" FROM plants p
JOIN model.reporting_periods rp
  ON rp.key_report = p.rep_code
    AND rp.state = 1
JOIN discharge_points d
  ON d.plant_id = p.id
LEFT JOIN l_federal_state lfs1
  ON p.federal_state_id = lfs1.id
LEFT JOIN l_federal_state lfs2
  ON d.federal_state_id = lfs2.id
WHERE ( p.federal_state_id in (#{states}) OR p.handled_by_federal_state_id in (#{states})
    OR d.federal_state_id in (#{states}) OR d.handled_by_federal_state_id in (#{states}) )
  AND p.pl_to_be_reported IS DISTINCT FROM d.dp_to_be_reported
  AND p.pl_to_be_reported IS DISTINCT FROM 0
      "^,
        :show_columns => [
            {:row_id => 'p.id', :type => :link},
            {:col_name => 'p.rep_code', :human_name => 'Report ID'},
            {:spezial => 'lfs1.key', :col_name => 'lfs1_key', :human_name => 'BULA Anl.'},
            {:col_name => 'p.pl_key', :human_name => 'ID Anlage'},
            {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'p.pl_to_be_reported', :human_name => 'Anl. berichtet?', :type => 3},
            {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
            {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
            {:spezial => 'lfs2.key', :col_name => 'lfs2_key', :human_name => 'BULA Einl.'},
            {:col_name => 'd.dp_key', :human_name => 'ID Einl.'},
            {:col_name => 'd.dp_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'd.dp_to_be_reported', :human_name => 'Einl. berichtet?', :type => 3},
            {:col_name => 'd.dp_name', :human_name => 'Name Einleitstelle'},
            {:col_name => 'd.dp_eu_key', :human_name => 'EU Schlüssel'},
        ],
        :datensatz => 'plants'
    },
    {
        :id => 'pl_ntbr_dp_tbr',
        :group => 'gr__inkonsistent',
        :heading => 'Konsistenz',
        :title => 'Anlage wird nicht berichtet, Einleitstelle wird jedoch berichtet (6.3.b)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlage_wird_nicht_berichtet.2C_Einleitstelle_wird_jedoch_berichtet_.286.3.b.29',
        :count_on => 'p.id',
        :statement => %q^" FROM plants p
JOIN model.reporting_periods rp
  ON rp.key_report = p.rep_code
    AND rp.state = 1
JOIN discharge_points d
  ON d.plant_id = p.id
LEFT JOIN l_federal_state lfs1
  ON p.federal_state_id = lfs1.id
LEFT JOIN l_federal_state lfs2
  ON d.federal_state_id = lfs2.id
WHERE ( p.federal_state_id in (#{states}) OR p.handled_by_federal_state_id in (#{states})
    OR d.federal_state_id in (#{states}) OR d.handled_by_federal_state_id in (#{states}) )
  AND p.pl_to_be_reported IS DISTINCT FROM d.dp_to_be_reported
  AND p.pl_to_be_reported = 0
  AND p.pl_com_report IS DISTINCT FROM 0
      "^,
        :show_columns => [
            {:row_id => 'p.id', :type => :link},
            {:col_name => 'p.rep_code', :human_name => 'Report ID'},
            {:spezial => 'lfs1.key', :col_name => 'lfs1_key', :human_name => 'BULA Anl.'},
            {:col_name => 'p.pl_key', :human_name => 'ID Anlage'},
            {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'p.pl_to_be_reported', :human_name => 'Anl. berichtet?', :type => 3},
            {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
            {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
            {:spezial => 'lfs2.key', :col_name => 'lfs2_key', :human_name => 'BULA Einl.'},
            {:col_name => 'd.dp_key', :human_name => 'ID Einl.'},
            {:col_name => 'd.dp_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'd.dp_to_be_reported', :human_name => 'Einl. berichtet?', :type => 3},
            {:col_name => 'd.dp_name', :human_name => 'Name Einleitstelle'},
            {:col_name => 'd.dp_eu_key', :human_name => 'EU Schlüssel'},
        ],
        :datensatz => 'plants'
    },
    {
      :id => 'qsm1',
      :group => 'gr__unstimmig',
      :heading => 'Fracht- und Anschlussstimmigkeit',
      :title => 'Unstimmigkeiten der Anschlussgrade innerhalb der Agglomeration - Summe der Hauptfrachten (1.p.5.a)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unstimmigkeiten_der_Anschlussgrade_innerhalb_der_Agglomeration_-_Summe_der_Hauptfrachten_.281.p.5.a.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
  join model.reporting_periods rp
    on a.rep_code = rp.key_report
  where a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND ag_nload >= 2000
    AND NOT agg_c1 + agg_c2 + agg_not_collected BETWEEN 99.9 AND 100.1
    AND agg_state_active = 1
    AND rp.state = 1
    AND federal_state_id in (#{states})
    AND federal_state_id not in (17, 18)
    -- AND a.state = 0"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'a.agg_c1', :human_name => 'Angeschlossene EW an Kanalisation [%]', :type => 9},
        {:col_name => 'a.agg_c2', :human_name => 'Angeschlossene EW an IAS [%]', :type => 9},
        {:col_name => 'a.agg_not_collected', :human_name => 'Nicht angeschlossene EW [%]', :type => 9}
      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'qsm2',
      :group => 'gr__unstimmig',
      :heading => 'Fracht- und Anschlussstimmigkeit',
      :title => 'Unstimmigkeiten der angeschlossenen Frachten - Kanalisationsbilanz (1.p.5.b)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unstimmigkeiten_der_angeschlossenen_Frachten_-_Kanalisationsbilanz_.281.p.5.b.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
  join model.reporting_periods rp
    on a.rep_code = rp.key_report
  where a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND ag_nload >= 2000
    AND NOT round((agg_coll_nott + agg_c1_ka - agg_c1)::numeric, 5) BETWEEN -0.1 AND 0.1
    AND agg_state_active = 1
    AND rp.state = 1
    AND federal_state_id in (#{states})
    AND federal_state_id not in (17, 18)
    -- AND a.state = 0"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'a.agg_c1_ka', :human_name => 'Angeschlossene EW an Kläranlage [%]', :type => 9},
        {:col_name => 'a.agg_coll_nott', :human_name => 'Angeschlossene EW an Kanalisation ohne Behandlung [%]', :type => 9},
        {:col_name => 'a.agg_c1', :human_name => 'Angeschlossene EW an Kanalisation [%]', :type => 9}
      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'qsm3',
      :group => 'gr__unstimmig',
      :heading => 'Fracht- und Anschlussstimmigkeit',
      :title => 'Unstimmigkeiten der individuell gesammelten Frachten - IAS-Bilanz (1.p.5.d)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unstimmigkeiten_der_individuell_gesammelten_Frachten_-_IAS-Bilanz_.281.p.5.d.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
  join model.reporting_periods rp
    on a.rep_code = rp.key_report
  where a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND ag_nload >= 2000
    AND agg_c2*1.001 < agg_auc_c2t
    AND agg_state_active = 1
    AND rp.state = 1
    AND federal_state_id in (#{states})
    AND federal_state_id not in (17, 18)
    -- AND a.state = 0"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'a.agg_c2', :human_name => 'Angeschlossene EW an IAS [%]', :type => 9},
        {:col_name => 'a.agg_auc_c2t', :human_name => 'Angeschlossene EW an Kläranlage via LKW [%]', :type => 9}
      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'qsias',
      :group => 'gr__unstimmig',
      :heading => 'Fracht- und Anschlussstimmigkeit',
      :title => 'Unstimmigkeiten der Anschlussgrade innerhalb der weiteren Behandlung von Schmutzfracht aus IAS (1.p.5.e)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unstimmigkeiten_der_Anschlussgrade_innerhalb_der_weiteren_Behandlung_von_Schmutzfracht_aus_IAS_.281.p.5.e.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
  join model.reporting_periods rp
    on a.rep_code = rp.key_report
  where a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND ag_nload >= 100000
    AND ag_nload*agg_c2 >= 200000
    AND (NOT coalesce(agg_c2_pt,0) + coalesce(agg_c2_st,0) + coalesce(agg_c2_ft,0) BETWEEN coalesce(agg_c2,0)-0.1 AND coalesce(agg_c2,0)+0.1)
    AND agg_state_active = 1
    AND rp.state = 1
    AND federal_state_id in (#{states})
    AND federal_state_id not in (17, 18)
    -- AND a.state = 0"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'a.ag_nload', :human_name => 'Nominallast [EW]'},
        {:col_name => 'a.agg_c2', :human_name => 'Anteil der Nominallast an IAS [%]', :type => 9},
        {:col_name => 'c2nload', :spezial => 'a.agg_c2*a.ag_nload/100', :human_name => 'Nominallast an IAS [EW]', :type => 9},
        {:col_name => 'a.agg_c2_pt', :human_name => 'IAS Anteil [%] mit Primärbehandlung', :type => 9},
        {:col_name => 'a.agg_c2_st', :human_name => 'IAS Anteil [%] mit Sekundärbehandlung', :type => 9},
        {:col_name => 'a.agg_c2_ft', :human_name => 'IAS Anteil [%] mit weitergehende Behandlung', :type => 9},
      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'upmn',
      :group => 'gr__unstimmig',
      :heading => 'Fracht- und Anschlussstimmigkeit',
      :title => 'Unstimmigkeiten der Nominalbelastungen einer Anlage und denen der zugeordneten Agglomerationen (1.p.4)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unstimmigkeiten_der_Nominalbelastungen_einer_Anlage_und_denen_der_zugeordneten_Agglomerationen_.281.p.4.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND rp.state = 1
    AND p.federal_state_id in (#{states})
    AND p.pl_state_active = 1
    AND p.pl_to_be_reported = 1
    AND  (Select Sum(sa.ag_nload * sap.agg_percent_plant)
          From agglomerations_plants sap
          Join agglomerations sa
            on sap.agglomeration_id = sa.id
          Where sap.plant_id = p.id
            AND sa.agg_state_active = 1
            AND sa.ag_to_be_reported = 1) > (p.pl_nload*110)"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:spezial => %Q^array_to_string(ARRAY(Select sa.id || '|' || sa.agg_eu_key||': '||sa.ag_nload||'EW * '||replace(sap.agg_percent_plant::varchar,'.',',')||'%'
          From agglomerations_plants sap
          Join agglomerations sa
            on sap.agglomeration_id = sa.id
          Where sap.plant_id = p.id
            AND sa.agg_state_active = 1
            AND sa.ag_to_be_reported = 1), '\n')^,
          :col_name => 'connected_agg', :human_name => 'Angeschlossene Agglomerationen',
          :url_options => {:controller => 'agglomerations', :action => 'edit'},
          :type => :link_list},
        {:spezial => '(Select sum(sa.ag_nload * sap.agg_percent_plant)/100
          From agglomerations_plants sap
          Join agglomerations sa
            on sap.agglomeration_id = sa.id
          Where sap.plant_id = p.id
            AND sa.agg_state_active = 1
            AND sa.ag_to_be_reported = 1)',
          :col_name => 'aggregated_nload', :human_name => 'Summe der angeschlossenen Nominallasten', :type => 9},
        {:col_name => 'p.pl_nload', :human_name => 'Nominallast der Kläranlage'}
      ],
      :datensatz => 'plants'
    },
    {
      :id => 'uamn',
      :group => 'gr__unstimmig',
      :heading => 'Fracht- und Anschlussstimmigkeit',
      :title => 'Unstimmigkeiten der Belastungsanteile von einer Agglomeration an Anlagen (1.v.5.3i)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unstimmigkeiten_der_Belastungsanteile_von_einer_Agglomeration_an_Anlagen_.281.v.5.3i.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
  join model.reporting_periods rp
    on a.rep_code = rp.key_report
  where a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND rp.state = 1
    AND a.federal_state_id in (#{states})
    AND a.agg_state_active = 1
    AND a.ag_to_be_reported = 1
    AND not (SELECT SUM(COALESCE(agg_percent_plant,0))
          From agglomerations_plants ap
          Join plants p
            on ap.plant_id = p.id
          Where ap.agglomeration_id = a.id
            AND p.pl_state_active = 1
            AND (p.pl_to_be_reported = 1 OR p.federal_state_id = 17)) between a.agg_c1_ka-0.1 AND a.agg_c1_ka+0.1"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:spezial => %Q^array_to_string(ARRAY(Select sp.id || '|' || sp.pl_eu_key||': '||sap.agg_percent_plant||'%'
          From agglomerations_plants sap
          Join plants sp
            on sap.plant_id = sp.id
          Where sap.agglomeration_id = a.id
            AND sp.pl_state_active = 1
            AND sp.pl_to_be_reported = 1), '\n')^,
          :col_name => 'connected_agg', :human_name => 'Zugeordnete Kläranlagen',
          :url_options => {:controller => 'plants', :action => 'edit'},
          :type => :link_list},
        {:spezial => '(SELECT SUM(COALESCE(agg_percent_plant,0))
          From agglomerations_plants ap
          Join plants p
            on ap.plant_id = p.id
          Where ap.agglomeration_id = a.id
            AND p.pl_state_active = 1
            AND p.pl_to_be_reported = 1)',
          :col_name => 'aggregated_percent_plant', :human_name => 'Summe der Klärwerksanteile des Agglomerationsabwassers', :type => 9},
        {:col_name => 'a.agg_c1_ka', :human_name => 'Angeschlossene EW an Kläranlage [%]', :type => 9}
      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'undp',
      :group => 'gr__unstimmig',
      :heading => 'Fracht- und Anschlussstimmigkeit',
      :title => 'Anlagen mit Unstimmigkeiten der Nährstofffrachten an den Einleitstellen (1.p.3)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Anlagen_mit_Unstimmigkeiten_der_N.C3.A4hrstofffrachten_an_den_Einleitstellen_.281.p.3.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND rp.state = 1
    AND federal_state_id in (#{states})
    AND p.pl_com_report IS DISTINCT FROM 0
    AND pl_state_active = 1
    AND pl_to_be_reported = 1
    AND (dp_load_n_incoming*1.001 < dp_load_n
      OR dp_load_p_incoming*1.001 < dp_load_p)"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'p.dp_load_n_incoming', :human_name => 'N-Zulauffracht [kg/a]', :type => 9},
        {:col_name => 'p.dp_load_n', :human_name => 'N-Ablauffracht [kg/a]', :type => 9},
        {:col_name => 'p.dp_load_p_incoming', :human_name => 'P-Zulauffracht [kg/a]', :type => 9},
        {:col_name => 'p.dp_load_p', :human_name => 'P-Ablauffracht [kg/a]', :type => 9}
      ],
      :datensatz => 'plants'
    },
    {
      :id => 'kba1',
      :group => 'gr__konfklassen',
      :heading => 'Konformitätsklassen / Anhang 1',
      :title => 'Konformität des Bescheids mit Anhang 1',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Konformit.C3.A4t_des_Bescheids_mit_Anhang_1',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_kk_check_1(plants.id) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'kkea1p',
      :group => 'gr__konfklassen',
      :heading => 'Konformitätsklassen / Anhang 1',
      :title => 'Konformitätsklasse im Einklang mit Anhang 1-Einhaltung der Parameter',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Konformit.C3.A4tsklasse_im_Einklang_mit_Anhang_1-Einhaltung_der_Parameter',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_kk_check_3(plants.id) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'nkkenea1p',
      :group => 'gr__konfklassen',
      :heading => 'Konformitätsklassen / Anhang 1',
      :title => '(Non-)Konformitätsklasse im Einklang mit Anhang 1-nicht-Einhaltung der Parameter',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#.28Non-.29Konformit.C3.A4tsklasse_im_Einklang_mit_Anhang_1-nicht-Einhaltung_der_Parameter',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_kk_check_2(plants.id) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'gkea1p',
      :group => 'gr__konfklassen',
      :heading => 'Konformitätsklassen / Anhang 1',
      :title => 'Gesamtkonformität im Einklang mit Anhang 1-Einhaltung der Parameter',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Gesamtkonformit.C3.A4t_im_Einklang_mit_Anhang_1-Einhaltung_der_Parameter',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_kk_check_4(plants.id) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'gnkea1nep',
      :group => 'gr__konfklassen',
      :heading => 'Konformitätsklassen / Anhang 1',
      :title => 'Gesamt-Nonkonformität im Einklang mit Anhang 1-Teil-Nicht-Einhaltung der Parameter',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Gesamt-Nonkonformit.C3.A4t_im_Einklang_mit_Anhang_1-Teil-Nicht-Einhaltung_der_Parameter',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_kk_check_5(plants.id) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'kkegk',
      :group => 'gr__konfklassen',
      :heading => 'Konformitätsklassen / Anhang 1',
      :title => 'Konformitätsklasse im Einklang mit Angabe zur Gesamtkonformität',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Konformit.C3.A4tsklasse_im_Einklang_mit_Angabe_zur_Gesamtkonformit.C3.A4t',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_kk_check_6(plants.id) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'dgkkwv',
      :group => 'gr__konfklassen',
      :heading => 'Konformitätsklassen / Anhang 1',
      :title => 'Derzeit gültige Konformitätsklasse wird verwendet',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Derzeit_g.C3.BCltige_Konformit.C3.A4tsklasse_wird_verwendet',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_kk_check_7(plants.id) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'vpverabw',
      :group => 'gr__vorperiodenvergleiche',
      :heading => 'Vorperiodenvergleich',
      :title => 'Verschlechterung der Abwasserbehandlung seit dem Vorjahreszeitraum (5.1.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Verschlechterung_der_Abwasserbehandlung_seit_dem_Vorjahreszeitraum_.285.1.1.29',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_pvjd_check_1(plants.id) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'vpverdp',
      :group => 'gr__vorperiodenvergleiche',
      :heading => 'Vorperiodenvergleich',
      :title => 'Veränderung des Einleitgewässers gegenüber dem Vorjahreszeitraum (5.2.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Ver.C3.A4nderung_des_Einleitgew.C3.A4ssers_gegen.C3.BCber_dem_Vorjahreszeitraum_.285.2.1.29',
      :count_on => 'pl.id',
      :where => %q^" AND discharge_points.rep_code = '#{rep_code}'
        AND discharge_points.ignore_all_qs_checks IS DISTINCT FROM 1
        AND discharge_points.federal_state_id in (#{states})
        AND discharge_points.dp_to_be_reported IS DISTINCT FROM 0
        AND discharge_points.dp_state_active IS DISTINCT FROM 0
        AND enda_pvjd_check_2(discharge_points.id) IS DISTINCT FROM 0"^,
      :datensatz => 'discharge_points'
    },
    {
      :id => 'agaddf',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Agglomerationen, deren Koordinaten mehr als #{@@error_distance} m außerhalb des deutschen Festlands liegen (4.1.1)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Agglomeration_vom_deutschen_Festland_.284.1.1.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
          JOIN model.reporting_periods rp
            ON rp.key_report = a.rep_code
              AND rp.state = 1
          JOIN geo.germany_shapes g
            ON a.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(g.geom, a.geo_etrs89_utm32) > #{@@error_distance}
          WHERE a.ignore_all_qs_checks IS DISTINCT FROM 1
              AND a.federal_state_id in (#{states})
              AND a.ag_to_be_reported IS DISTINCT FROM 0
              AND a.agg_state_active IS DISTINCT FROM 0
              AND g.gf = 4"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls( st_transform(a.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(g.geom, a.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},

      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'agadb',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Agglomerationen, deren Koordinaten mehr als #{@@error_distance} m außerhalb des beherbergenden Bundeslandes (Festland) liegen (4.1.3)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Agglomeration_vom_beherbergenden_Bundesland_.28Festland.29_.284.1.3.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
          JOIN model.reporting_periods rp
            ON rp.key_report = a.rep_code
              AND rp.state = 1
          JOIN geo.federal_states_shapes fss
            ON a.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(fss.geom, a.geo_etrs89_utm32) > #{@@error_distance}
          WHERE a.ignore_all_qs_checks IS DISTINCT FROM 1
              AND a.federal_state_id in (#{states})
              AND a.ag_to_be_reported IS DISTINCT FROM 0
              AND a.agg_state_active IS DISTINCT FROM 0
              AND fss.gf = 4
              AND fss.federal_state_id = a.federal_state_id
              "',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls(st_transform(a.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(fss.geom, a.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},

      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'pladdf',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Anlagen, deren Koordinaten mehr als #{@@error_distance} m außerhalb des deutschen Festlands liegen (4.2.1)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Anlage_vom_deutschen_Festland_.284.2.1.29',
      :count_on => 'p.id',
      :statement => '" FROM plants p
          JOIN model.reporting_periods rp
            ON rp.key_report = p.rep_code
              AND rp.state = 1
          JOIN geo.germany_shapes g
            ON p.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(g.geom, p.geo_etrs89_utm32) > #{@@error_distance}
          WHERE p.ignore_all_qs_checks IS DISTINCT FROM 1
              AND p.federal_state_id in (#{states})
              AND p.pl_to_be_reported IS DISTINCT FROM 0
              AND p.pl_state_active IS DISTINCT FROM 0
              AND g.gf = 4"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls( st_transform(p.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(g.geom, p.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},

      ],
      :datensatz => 'plants'
    },
    {
      :id => 'pladb',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Anlagen, deren Koordinaten mehr als #{@@error_distance} m außerhalb des beherbergenden Bundeslandes (Festland) liegen (4.2.3)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Anlage_vom_beherbergenden_Bundesland_.28Festland.29_.284.2.3.29',
      :count_on => 'p.id',
      :statement => '" FROM plants p
          JOIN model.reporting_periods rp
            ON rp.key_report = p.rep_code
              AND rp.state = 1
          JOIN geo.federal_states_shapes fss
            ON p.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(fss.geom, p.geo_etrs89_utm32) > #{@@error_distance}
          WHERE p.ignore_all_qs_checks IS DISTINCT FROM 1
              AND p.federal_state_id in (#{states})
              AND p.pl_to_be_reported IS DISTINCT FROM 0
              AND p.pl_state_active IS DISTINCT FROM 0
              AND fss.gf = 4
              AND fss.federal_state_id = p.federal_state_id
              "',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls(st_transform(p.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(fss.geom, p.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},

      ],
      :datensatz => 'plants'
    },
    {
      :id => 'dpaddf',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Einleitstellen, deren Koordinaten mehr als #{@@error_distance} m außerhalb des deutschen Festlands liegen (4.3.1)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Einleitstelle_vom_deutschen_Festland_.284.3.1.29',
      :count_on => 'dp.id',
      :statement => '" FROM discharge_points dp
          JOIN model.reporting_periods rp
            ON rp.key_report = dp.rep_code
              AND rp.state = 1
          JOIN geo.germany_shapes g
            ON dp.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(g.geom, dp.geo_etrs89_utm32) > #{@@error_distance}
          WHERE dp.federal_state_id in (#{states})
              AND dp.dp_to_be_reported IS DISTINCT FROM 0
              AND dp.dp_state_active IS DISTINCT FROM 0
              AND g.gf = 4
              AND dp.ignore_all_qs_checks IS DISTINCT FROM 1"',
      :show_columns => [
        {:row_id => 'dp.id', :type => :link},
        {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls( st_transform(dp.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(g.geom, dp.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},
      ],
      :datensatz => 'discharge_points'
    },
    {
      :id => 'dpadb',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Einleitstellen, deren Koordinaten mehr als #{@@error_distance} m außerhalb des beherbergenden Bundeslandes (Festland) liegen (4.3.3)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Einleitstelle_vom_beherbergenden_Bundesland_.28Festland.29_.284.3.3.29',
      :count_on => 'dp.id',
      :statement => '" FROM discharge_points dp
          JOIN model.reporting_periods rp
            ON rp.key_report = dp.rep_code
              AND rp.state = 1
          JOIN geo.federal_states_shapes fss
            ON dp.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(fss.geom, dp.geo_etrs89_utm32) > #{@@error_distance}
          WHERE dp.federal_state_id in (#{states})
              AND dp.dp_to_be_reported IS DISTINCT FROM 0
              AND dp.dp_state_active IS DISTINCT FROM 0
              AND fss.gf = 4
              AND fss.federal_state_id = dp.federal_state_id
              AND ignore_all_qs_checks IS DISTINCT FROM 1
       "',
      :show_columns => [
        {:row_id => 'dp.id', :type => :link},
        {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls(st_transform(dp.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(fss.geom, dp.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},

      ],
      :datensatz => 'discharge_points'
    },
    {
        :id => 'err_dp_oo_rbd',
        :group => 'gr__koordinaten',
        :heading => 'Koordinaten',
        :title => "Einleitstellen, deren Koordinaten mehr als #{@@error_distance} m außerhalb des beherbergenden Flusseinzugsgebiets liegen (4.3.5)",
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Einleitstelle_vom_beherbergenden_Flusseinzugsgebiet_.284.3.5.29',
        :count_on => 'dp.id',
        :statement => '" FROM discharge_points dp
      JOIN model.reporting_periods rp
        ON rp.key_report = dp.rep_code
          AND rp.state = 1
      JOIN public.l_river_basin_districts lrbd
        ON lrbd.id = dp.rb_rbd_id
      JOIN geo.riverbasindistricts_25832 rbds
        ON dp.geo_etrs89_utm32 IS DISTINCT FROM NULL
          AND rbds.thematicid = lrbd.river_basin_district_eu_key
          AND ST_Distance(rbds.geom, dp.geo_etrs89_utm32) > #{@@error_distance}
      WHERE dp.ignore_all_qs_checks IS DISTINCT FROM 1
          AND dp.federal_state_id in (#{states})
          AND dp.dp_to_be_reported IS DISTINCT FROM 0
          AND dp.dp_state_active IS DISTINCT FROM 0
          "',
        :show_columns => [
            {:row_id => 'dp.id', :type => :link},
            {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
            {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
            {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
            {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
            {:col_name => 'lrbd.key', :human_name => 'Flussgebietseinheit'},
            {:col_name => 'lrbd.river_basin_district_eu_key', :human_name => 'RBD-EU-Key'},
            {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
               SELECT enda_map_urls(dp.geo_wgs84)), '\n')^, :human_name => 'OSM oder Google'},
            {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
               ROUND(ST_Distance(rbds.geom, dp.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'}
        ],
        :datensatz => 'discharge_points'
    }

  ]


  # qs-warnings
  QS_WARNING = [
      {
          :id => 'agg_gt100kew_o_bigcity',
          :group => 'gr__vollstaendigkeit',
          :heading => 'Vollständigkeit',
          :title => 'Alle Agglomerationen ab 100.000 EW Nominalbelastung sollten eine Großstadt (Big City) haben (1.v.8)',
          # TODO
          :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomerationen.2C_deren_Nominalbelastung_.5BEW.5D_.3E.3D_100.000_EW.2C_sollten_eine_Gro.C3.9Fstadt_haben_.281.v.8.29',
          :count_on => 'a.id',
          :statement => '" from agglomerations a
  JOIN model.reporting_periods mrp
    ON mrp.state = 1
      AND a.date_from <= mrp.date_to
      AND a.date_to >= mrp.date_to
  WHERE a.state = 0
    AND a.agg_state_active = 1
    AND a.ag_to_be_reported IS DISTINCT FROM 0
    AND a.federal_state_id in (#{states})
    AND a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND a.ag_nload >= 100000
    AND a.pl_city_id IS NULL
          "',
          :show_columns => [
              {:row_id => 'a.id', :type => :link},
              {:col_name => 'a.rep_code', :human_name => 'Report ID'},
              {:col_name => 'a.agg_key', :human_name => 'ID'},
              {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
              {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
              {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
              {:col_name => 'a.ag_nload', :human_name => 'Nominalbelastung [EW]', :type => 8},
              {:col_name => 'a.pl_city_id', :human_name => 'Großstadt'}
          ],
          :datensatz => 'agglomerations'
      },
    {
      :id => 'ua11',
      :group => 'gr__unstimmig',
      :heading => 'Fracht- und Anschlussstimmigkeit',
      :title => 'Unstimmigkeiten zwischen der Nominallast zugeordneter Agglomeration und der Ausbaugröße einer Anlage (1.v.5.3ii)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Unstimmigkeiten_zwischen_der_Nominallast_zugeordneter_Agglomeration_und_der_Ausbaugr.C3.B6.C3.9Fe_einer_Anlage_.281.v.5.3ii.29',
      :count_on => 'p.id',
      :statement => '" from plants p
  join model.reporting_periods rp
    on p.rep_code = rp.key_report
  where p.ignore_all_qs_checks IS DISTINCT FROM 1
    AND rp.state = 1
    AND p.federal_state_id in (#{states})
    AND p.pl_state_active = 1
    AND p.pl_to_be_reported = 1
    AND  (Select Sum(sa.ag_nload * sap.agg_percent_plant)
          From agglomerations_plants sap
          Join agglomerations sa
            on sap.agglomeration_id = sa.id
          Where sap.plant_id = p.id
            AND sa.agg_state_active = 1
            AND sa.ag_to_be_reported = 1) > (p.pl_odc*110)"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:spezial => %Q^array_to_string(ARRAY(Select sa.id || '|' || sa.agg_eu_key||': '||sa.ag_nload||'EW * '||sap.agg_percent_plant||'%'
          From agglomerations_plants sap
          Join agglomerations sa
            on sap.agglomeration_id = sa.id
          Where sap.plant_id = p.id
            AND sa.agg_state_active = 1
            AND sa.ag_to_be_reported = 1), '\n')^,
          :col_name => 'connected_agg',
          :human_name => 'Angeschlossene Agglomerationen',
          :url_options => {:controller => 'agglomerations', :action => 'edit'},
          :type => :link_list},
        {:spezial => '(Select sum(sa.ag_nload * sap.agg_percent_plant)/100
          From agglomerations_plants sap
          Join agglomerations sa
            on sap.agglomeration_id = sa.id
          Where sap.plant_id = p.id
            AND sa.agg_state_active = 1
            AND sa.ag_to_be_reported = 1)',
          :col_name => 'aggregated_nload', :human_name => 'Summe der angeschlossenen Nominallasten', :type => 9},
        {:col_name => 'p.pl_odc', :human_name => 'Ausbaugröße der Anlage'}
      ],
      :datensatz => 'plants'
    },
    #{
    #    :id => 'wapa1ke',
    #  :group => 'gr__konfklassen',
    #  :heading => 'Konformitätsklassen / Anhang 1',
    #  :title => 'Parameterspezifische Anhang 1-Konformitätsangabe vs. Erlaubniswert',
    #  :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung',
    #  :count_on => 'pl.id',
    #  :where => %q^" AND plants.rep_code = '#{rep_code}'
    #    AND plants.pl_to_be_reported IS DISTINCT FROM 0
    #    AND plants.pl_state_active IS DISTINCT FROM 0
    #    AND enda_kk_check_8(plants.id) IS DISTINCT FROM 0"^,
    #  :datensatz => 'plants'
    #},
    {
      :id => 'wpvjdabgr',
      :group => 'gr__vorperiodenvergleiche',
      :heading => 'Vorperiodenvergleich',
      :title => 'Signifikante Veränderung der Ausbaugröße (5.3.1)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Signifikante_Ver.C3.A4nderung_der_Ausbaugr.C3.B6.C3.9Fe_.285.3.1.29',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_pvjd_check_3(plants.id, #{@@warning_vorperidenfaktor_klein}) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'wpvjdnomlast',
      :group => 'gr__vorperiodenvergleiche',
      :heading => 'Vorperiodenvergleich',
      :title => 'Signifikante Veränderung der Nominalbelastung (5.3.2)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Signifikante_Ver.C3.A4nderung_der_Nominalbelastung_.285.3.2.29',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_pvjd_check_4(plants.id, #{@@warning_vorperidenfaktor_klein}) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'wpvjdloadn',
      :group => 'gr__vorperiodenvergleiche',
      :heading => 'Vorperiodenvergleich',
      :title => 'Signifikante Veränderung der Zu- oder Ablauffracht N (5.3.3)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Signifikante_Ver.C3.A4nderung_der_Zu-_oder_Ablauffracht_N_.285.3.3.29',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_pvjd_check_5(plants.id, #{@@warning_vorperidenfaktor_mittel}) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'wpvjdloadp',
      :group => 'gr__vorperiodenvergleiche',
      :heading => 'Vorperiodenvergleich',
      :title => 'Signifikante Veränderung der Zu- oder Ablauffracht P (5.3.4)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Signifikante_Ver.C3.A4nderung_der_Zu-_oder_Ablauffracht_P_.285.3.4.29',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_pvjd_check_6(plants.id, #{@@warning_vorperidenfaktor_mittel}) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'wpvjdvolume',
      :group => 'gr__vorperiodenvergleiche',
      :heading => 'Vorperiodenvergleich',
      :title => 'Signifikante Veränderung der Jahresabwassermenge (5.3.5)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Signifikante_Ver.C3.A4nderung_der_Jahresabwassermenge_.285.3.5.29',
      :count_on => 'pl.id',
      :where => %q^" AND plants.rep_code = '#{rep_code}'
        AND plants.ignore_all_qs_checks IS DISTINCT FROM 1
        AND plants.federal_state_id in (#{states})
        AND plants.pl_to_be_reported IS DISTINCT FROM 0
        AND plants.pl_state_active IS DISTINCT FROM 0
        AND enda_pvjd_check_7(plants.id, #{@@warning_vorperidenfaktor_mittel}) IS DISTINCT FROM 0"^,
      :datensatz => 'plants'
    },
    {
      :id => 'wpvjdanschluss',
      :group => 'gr__vorperiodenvergleiche',
      :heading => 'Vorperiodenvergleich',
      :title => 'Verringerung der angeschlossenen EW an Kanalisation oder Kläranlage(n) (5.3.6)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Verringerung_der_angeschlossenen_EW_an_Kanalisation_oder_Kl.C3.A4ranlage.28n.29_.285.3.6.29',
      :count_on => 'agg.id',
      :where => %q^" AND agglomerations.rep_code = '#{rep_code}'
        AND agglomerations.ignore_all_qs_checks IS DISTINCT FROM 1
        AND agglomerations.federal_state_id in (#{states})
        AND agglomerations.ag_to_be_reported IS DISTINCT FROM 0
        AND agglomerations.agg_state_active IS DISTINCT FROM 0
        AND enda_pvjd_check_8(agglomerations.id) IS DISTINCT FROM 0"^,
      :datensatz => 'agglomerations'
    },
    {
      :id => 'wagaddf',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Agglomerationen, deren Koordinaten mehr als #{@@warning_distance} m, nicht jedoch mehr als #{@@error_distance} m  außerhalb des deutschen Festlands liegen (4.1.2)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Agglomeration_vom_deutschen_Festland_.284.1.2.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
          JOIN model.reporting_periods rp
            ON rp.key_report = a.rep_code
              AND rp.state = 1
          JOIN geo.germany_shapes g
            ON a.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(g.geom, a.geo_etrs89_utm32) > #{@@warning_distance}
              AND ST_Distance(g.geom, a.geo_etrs89_utm32) <= #{@@error_distance}
          WHERE a.ignore_all_qs_checks IS DISTINCT FROM 1
              AND a.federal_state_id in (#{states})
              AND a.ag_to_be_reported IS DISTINCT FROM 0
              AND a.agg_state_active IS DISTINCT FROM 0
              AND g.gf = 4"',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls( st_transform(a.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(g.geom, a.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},

      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'wagadb',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Agglomerationen, deren Koordinaten mehr als #{@@warning_distance} m, nicht jedoch mehr als #{@@error_distance} m außerhalb des beherbergenden Bundeslandes (Festland) liegen (4.1.4)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Agglomeration_vom_beherbergenden_Bundesland_.28Festland.29_.284.1.4.29',
      :count_on => 'a.id',
      :statement => '" from agglomerations a
          JOIN model.reporting_periods rp
            ON rp.key_report = a.rep_code
              AND rp.state = 1
          JOIN geo.federal_states_shapes fss
            ON a.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(fss.geom, a.geo_etrs89_utm32) > #{@@warning_distance}
              AND ST_Distance(fss.geom, a.geo_etrs89_utm32) <= #{@@error_distance}
          WHERE a.ignore_all_qs_checks IS DISTINCT FROM 1
              AND a.federal_state_id in (#{states})
              AND a.ag_to_be_reported IS DISTINCT FROM 0
              AND a.agg_state_active IS DISTINCT FROM 0
              AND fss.gf = 4
              AND fss.federal_state_id = a.federal_state_id
              "',
      :show_columns => [
        {:row_id => 'a.id', :type => :link},
        {:col_name => 'a.rep_code', :human_name => 'Report ID Agglomeration'},
        {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
        {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
        {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls(st_transform(a.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(fss.geom, a.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},

      ],
      :datensatz => 'agglomerations'
    },
    {
      :id => 'wpladdf',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Anlagen, deren Koordinaten mehr als #{@@warning_distance} m, nicht jedoch mehr als #{@@error_distance} m außerhalb des deutschen Festlands liegen (4.2.2)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Anlage_vom_deutschen_Festland_.284.2.2.29',
      :count_on => 'p.id',
      :statement => '" FROM plants p
          JOIN model.reporting_periods rp
            ON rp.key_report = p.rep_code
              AND rp.state = 1
          JOIN geo.germany_shapes g
            ON p.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(g.geom, p.geo_etrs89_utm32) > #{@@warning_distance}
              AND ST_Distance(g.geom, p.geo_etrs89_utm32) <= #{@@error_distance}
          WHERE p.ignore_all_qs_checks IS DISTINCT FROM 1
              AND p.federal_state_id in (#{states})
              AND p.pl_to_be_reported IS DISTINCT FROM 0
              AND p.pl_state_active IS DISTINCT FROM 0
              AND g.gf = 4"',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls( st_transform(p.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(g.geom, p.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},

      ],
      :datensatz => 'plants'
    },
    {
      :id => 'wpladb',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Anlagen, deren Koordinaten mehr als #{@@warning_distance} m, nicht jedoch mehr als #{@@error_distance} m außerhalb des beherbergenden Bundeslandes (Festland) liegen (4.2.4)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Anlage_vom_beherbergenden_Bundesland_.28Festland.29_.284.2.4.29',
      :count_on => 'p.id',
      :statement => '" FROM plants p
          JOIN model.reporting_periods rp
            ON rp.key_report = p.rep_code
              AND rp.state = 1
          JOIN geo.federal_states_shapes fss
            ON p.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(fss.geom, p.geo_etrs89_utm32) > #{@@warning_distance}
              AND ST_Distance(fss.geom, p.geo_etrs89_utm32) <= #{@@error_distance}
          WHERE p.ignore_all_qs_checks IS DISTINCT FROM 1
              AND p.federal_state_id in (#{states})
              AND p.pl_to_be_reported IS DISTINCT FROM 0
              AND p.pl_state_active IS DISTINCT FROM 0
              AND fss.gf = 4
              AND fss.federal_state_id = p.federal_state_id
              "',
      :show_columns => [
        {:row_id => 'p.id', :type => :link},
        {:col_name => 'p.rep_code', :human_name => 'Report ID Anlage'},
        {:col_name => 'p.pl_key', :human_name => 'ID der Anlage'},
        {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'p.pl_name', :human_name => 'Name der Anlage'},
        {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls(st_transform(p.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(fss.geom, p.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},

      ],
      :datensatz => 'plants'
    },
    {
      :id => 'wdpaddf',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Einleitstellen, deren Koordinaten mehr als #{@@warning_distance} m, nicht jedoch mehr als #{@@error_distance} m außerhalb des deutschen Festlands liegen (4.3.2)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Einleitstelle_vom_deutschen_Festland_.284.3.2.29',
      :count_on => 'dp.id',
      :statement => '" FROM discharge_points dp
          JOIN model.reporting_periods rp
            ON rp.key_report = dp.rep_code
              AND rp.state = 1
          JOIN geo.germany_shapes g
            ON dp.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(g.geom, dp.geo_etrs89_utm32) > #{@@warning_distance}
              AND ST_Distance(g.geom, dp.geo_etrs89_utm32) <= #{@@error_distance}
          WHERE dp.ignore_all_qs_checks IS DISTINCT FROM 1
              AND dp.federal_state_id in (#{states})
              AND dp.dp_to_be_reported IS DISTINCT FROM 0
              AND dp.dp_state_active IS DISTINCT FROM 0
              AND g.gf = 4"',
      :show_columns => [
        {:row_id => 'dp.id', :type => :link},
        {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls( st_transform(dp.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(g.geom, dp.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'},
      ],
      :datensatz => 'discharge_points'
    },
    {
      :id => 'wdpadb',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Einleitstellen, deren Koordinaten mehr als #{@@warning_distance} m, nicht jedoch mehr als #{@@error_distance} m außerhalb des beherbergenden Bundeslandes (Festland) liegen (4.3.4)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Einleitstelle_vom_beherbergenden_Bundesland_.28Festland.29_.284.3.4.29',
      :count_on => 'dp.id',
      :statement => '" FROM discharge_points dp
          JOIN model.reporting_periods rp
            ON rp.key_report = dp.rep_code
              AND rp.state = 1
          JOIN geo.federal_states_shapes fss
            ON dp.geo_etrs89_utm32 IS DISTINCT FROM NULL
              AND ST_Distance(fss.geom, dp.geo_etrs89_utm32) > #{@@warning_distance}
              AND ST_Distance(fss.geom, dp.geo_etrs89_utm32) <= #{@@error_distance}
          WHERE dp.ignore_all_qs_checks IS DISTINCT FROM 1
              AND dp.federal_state_id in (#{states})
              AND dp.dp_to_be_reported IS DISTINCT FROM 0
              AND dp.dp_state_active IS DISTINCT FROM 0
              AND fss.gf = 4
              AND fss.federal_state_id = dp.federal_state_id
              "',
      :show_columns => [
        {:row_id => 'dp.id', :type => :link},
        {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
        {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
        {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
        {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
        {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
        {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
                   SELECT enda_map_urls(st_transform(dp.geo_etrs89_utm32, 4326))), '\n')^, :human_name => 'OSM oder Google'},
        {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
                   ROUND(ST_Distance(fss.geom, dp.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'}
      ],
      :datensatz => 'discharge_points'
    },
    {
      :id => 'wrn_dp_oo_rbd',
      :group => 'gr__koordinaten',
      :heading => 'Koordinaten',
      :title => "Einleitstellen, deren Koordinaten mehr als #{@@warning_distance} m, nicht jedoch mehr als #{@@error_distance} m außerhalb des beherbergenden Flusseinzugsgebiets liegen (4.3.6)",
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Entfernung_der_Einleitstelle_vom_beherbergenden_Flusseinzugsgebiet_.284.3.6.29',
      :count_on => 'dp.id',
      :statement => '" FROM discharge_points dp
      JOIN model.reporting_periods rp
        ON rp.key_report = dp.rep_code
          AND rp.state = 1
      JOIN public.l_river_basin_districts lrbd
        ON lrbd.id = dp.rb_rbd_id
      JOIN geo.riverbasindistricts_25832 rbds
        ON dp.geo_etrs89_utm32 IS DISTINCT FROM NULL
          AND rbds.thematicid = lrbd.river_basin_district_eu_key
          AND ST_Distance(rbds.geom, dp.geo_etrs89_utm32) > #{@@warning_distance}
          AND ST_Distance(rbds.geom, dp.geo_etrs89_utm32) <= #{@@error_distance}
      WHERE dp.ignore_all_qs_checks IS DISTINCT FROM 1
          AND dp.federal_state_id in (#{states})
          AND dp.dp_to_be_reported IS DISTINCT FROM 0
          AND dp.dp_state_active IS DISTINCT FROM 0
          "',
      :show_columns => [
          {:row_id => 'dp.id', :type => :link},
          {:col_name => 'dp.rep_code', :human_name => 'Report ID Einleitstelle'},
          {:col_name => 'dp.dp_key', :human_name => 'ID der Einleitstelle'},
          {:col_name => 'dp.dp_state_active', :human_name => 'Aktiv?', :type => 3},
          {:col_name => 'dp.dp_name', :human_name => 'Name der Einleitstelle'},
          {:col_name => 'dp.dp_eu_key', :human_name => 'EU Schlüssel'},
          {:col_name => 'dp.rb_rbd_id', :human_name => 'Flussgebietseinheit'},
          {:col_name => 'osm_or_google', :spezial => %Q^ARRAY_TO_STRING( ARRAY(
               SELECT enda_map_urls(dp.geo_wgs84)), '\n')^, :human_name => 'OSM oder Google'},
          {:col_name => 'distance', :spezial => %Q^array_to_string(ARRAY(SELECT
               ROUND(ST_Distance(rbds.geom, dp.geo_etrs89_utm32)::NUMERIC, 2)), '\n')^, :human_name => 'Abstand in m'}
      ],
      :datensatz => 'discharge_points'
    },
    {
        :id => 'agg_bula_ne_bigcity_bula',
        :group => 'gr__inkonsistent',
        :heading => 'Konsistenz',
        :title => 'Agglomerationen, deren verknüpfte Großstadt (BigCity) in einem anderen Bundesland liegt, als die Agglomeration selbst (1.p.7.b)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomerationen.2C_deren_verkn.C3.BCpfte_Gro.C3.9Fstadt_.28BigCity.29_in_einem_anderen_Bundesland_liegt.2C_als_die_Agglomeration_selbst_.281.p.7.b.29',
        :count_on => 'a.id',
        :statement => %q^" FROM agglomerations a
  INNER JOIN model.reporting_periods mrp
    ON mrp.state = 1
      AND a.date_from <= mrp.date_to
      AND a.date_to >= mrp.date_to
  INNER JOIN l_federal_state lf7
    ON lf7.id = a.federal_state_id
  LEFT JOIN l_cities lc
    ON a.pl_city_id = lc.id
      AND lc.state = 0
      AND lc.federal_state_id <> a.federal_state_id
  LEFT JOIN l_federal_state lf2
    ON lf2.id = lc.federal_state_id
  WHERE a.state = 0
    AND a.ignore_all_qs_checks IS DISTINCT FROM 1
    AND a.federal_state_id in (#{states})
    AND a.ag_to_be_reported IS DISTINCT FROM 0
    AND a.agg_state_active IS DISTINCT FROM 0
    AND lc.id IS NOT NULL"^,
        :show_columns => [
            {:row_id => 'a.id', :type => :link},
            {:col_name => 'a.rep_code', :human_name => 'Report ID'},
            # {:col_name => 'a.agg_key', :human_name => 'ID der Agglomeration'},
            {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
            {:spezial => 'lf7.key', :col_name => 'lf7_key', :human_name => 'BULA Agglomeration'},
            {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
            {:col_name => 'lc.description', :human_name => 'EIONET-Großstadt'},
            {:spezial => 'lf2.key', :col_name => 'lf2_key', :human_name => 'BULA Großstadt'},
            {:col_name => 'lc.key', :human_name => 'Großstadt-Schlüssel'},
            {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
    #         {:col_name => 'successors', :spezial => %Q^array_to_string(ARRAY(SELECT a.id || '|' || agg_eu_key ||' - '|| ag_name FROM agglomerations_successors ap
    # LEFT JOIN agglomerations a ON ap.agglomeration_succ_id = a.id
    # where ap.agglomeration_pre_id = a.id), '\n')^, :human_name => 'Nachfolger'},
    #         {:col_name => 'candidates', :spezial => %Q^array_to_string(ARRAY(SELECT id || '|' || rep_code||': '||agg_eu_key||' - '||ag_name FROM agglomerations
    # WHERE rep_code IN (SELECT key_report FROM model.reporting_periods ORDER BY key_report desc LIMIT 1 OFFSET 0)
    #             AND (ag_name ILIKE '%'||a.ag_name||'%'
    #             OR (ag_lat > a.ag_lat - 100
    #             AND ag_lat < a.ag_lat + 100
    #             AND ag_long > a.ag_long - 100
    #             AND ag_long < a.ag_long + 100))), '\n')^, :human_name => 'Kandidaten',
    #          :url_options => {:controller => 'agglomerations', :action => 'edit'},
    #          :type => :link_list}
        ],
        # :show_columns => [
        #     {:row_id => 'a.id', :type => :link},
        #     {:col_name => 'lc.key', :human_name => 'BigCity Schlüssel'},
        #     {:col_name => 'lc.description', :human_name => 'BigCity Name'}
        # ],
        :datensatz => 'agglomerations'
    },
    {
      :id => 'agg_tbr_pl_ntbr',
      :group => 'gr__inkonsistent',
      :heading => 'Konsistenz',
      :title => 'Agglomerationen und Anlagen mit abweichendem "wird berichtet"-Status: Agglomeration wird berichtet, Anlage nicht (6.2.a)',
      :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomeration_wird_berichtet.2C_Anlage_wird_jedoch_nicht_berichtet_.286.2.a.29',
      :count_on => 'a.id',
      :statement => %q^" FROM agglomerations a
JOIN model.reporting_periods rp
  ON rp.key_report = a.rep_code
    AND rp.state = 1
FULL OUTER JOIN agglomerations_plants ap
  ON a.id = ap.agglomeration_id
FULL OUTER JOIN plants p
  ON ap.plant_id = p.id
INNER JOIN l_federal_state lfs1
  ON a.federal_state_id = lfs1.id
INNER JOIN l_federal_state lfs2
  ON p.federal_state_id = lfs2.id
WHERE ( a.federal_state_id in (#{states}) OR a.handled_by_federal_state_id in (#{states})
    OR p.federal_state_id in (#{states}) OR p.handled_by_federal_state_id in (#{states}) )
  AND a.ag_to_be_reported IS DISTINCT FROM p.pl_to_be_reported
  AND a.ag_to_be_reported IS DISTINCT FROM 0
      "^,
      :show_columns => [
          {:row_id => 'a.id', :type => :link},
          {:col_name => 'a.rep_code', :human_name => 'Report ID'},
          {:spezial => 'lfs1.key', :col_name => 'lfs1_key', :human_name => 'BULA Agg.'},
          {:col_name => 'a.agg_key', :human_name => 'ID Agglo.'},
          {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
          {:col_name => 'a.ag_to_be_reported', :human_name => 'Agg. berichtet?', :type => 3},
          {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
          {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
          {:spezial => 'lfs2.key', :col_name => 'lfs2_key', :human_name => 'BULA Anl.'},
          {:col_name => 'p.pl_key', :human_name => 'ID Anlage'},
          {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
          {:col_name => 'p.pl_to_be_reported', :human_name => 'Anlage berichtet?', :type => 3},
          {:col_name => 'p.pl_name', :human_name => 'Name Anlage'},
          {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
      ],
      :datensatz => 'agglomerations'
    },
    {
        :id => 'agg_ntbr_pl_tbr',
        :group => 'gr__inkonsistent',
        :heading => 'Konsistenz',
        :title => 'Agglomerationen und Anlagen mit abweichendem "wird berichtet"-Status: Agglomeration wird nicht, Anlage wird berichtet (6.2.b)',
        :wiki_link => 'https://wiki.e-kommu.de/index.php/Realisierung_der_Pr%C3%BCfungen_in_der_Anwendung#Agglomeration_wird_nicht_berichtet.2C_Anlage_wird_jedoch_berichtet_.286.2.b.29',
        :count_on => 'a.id',
        :statement => %q^" FROM agglomerations a
  JOIN model.reporting_periods rp
    ON rp.key_report = a.rep_code
      AND rp.state = 1
  FULL OUTER JOIN agglomerations_plants ap
    ON a.id = ap.agglomeration_id
  FULL OUTER JOIN plants p
    ON ap.plant_id = p.id
  INNER JOIN l_federal_state lfs1
    ON a.federal_state_id = lfs1.id
  INNER JOIN l_federal_state lfs2
    ON p.federal_state_id = lfs2.id
  WHERE ( a.federal_state_id in (#{states}) OR a.handled_by_federal_state_id in (#{states})
      OR p.federal_state_id in (#{states}) OR p.handled_by_federal_state_id in (#{states}) )
    AND a.ag_to_be_reported IS DISTINCT FROM p.pl_to_be_reported
    AND p.pl_to_be_reported IS DISTINCT FROM 0
        "^,
        :show_columns => [
            {:row_id => 'a.id', :type => :link},
            {:col_name => 'a.rep_code', :human_name => 'Report ID'},
            {:spezial => 'lfs1.key', :col_name => 'lfs1_key', :human_name => 'BULA Agg.'},
            {:col_name => 'a.agg_key', :human_name => 'ID Agglo.'},
            {:col_name => 'a.agg_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'a.ag_to_be_reported', :human_name => 'Agg. berichtet?', :type => 3},
            {:col_name => 'a.ag_name', :human_name => 'Name der Agglomeration'},
            {:col_name => 'a.agg_eu_key', :human_name => 'EU Schlüssel'},
            {:spezial => 'lfs2.key', :col_name => 'lfs2_key', :human_name => 'BULA Anl.'},
            {:col_name => 'p.pl_key', :human_name => 'ID Anlage'},
            {:col_name => 'p.pl_state_active', :human_name => 'Aktiv?', :type => 3},
            {:col_name => 'p.pl_to_be_reported', :human_name => 'Anlage berichtet?', :type => 3},
            {:col_name => 'p.pl_name', :human_name => 'Name Anlage'},
            {:col_name => 'p.pl_eu_key', :human_name => 'EU Schlüssel'},
        ],
        :datensatz => 'agglomerations'
    }
  ]

  def get_keys(datensatz, alia = nil)
    cgen = Generica.new(datensatz, session[:user]) #Übergabe sind Tabellenname und User-id
    columns = cgen.get_keys
    return 'false' if columns.empty?
    tabellen = columns.collect {|c| "#{alia.nil? ? datensatz : alia}.#{c['col_name']}"}
    where = tabellen.join(" IS NULL OR ")
    where += " IS NULL"
    return where
  end

  def get_required(datensatz, alia = nil)
    cgen = Generica.new(datensatz, session[:user]) #Übergabe sind Tabellenname und User-id
    columns = cgen.get_required
    return 'false' if columns.empty?
    tabellen = columns.collect {|c| "#{alia.nil? ? datensatz : alia}.#{c['col_name']}"}
    where = tabellen.join(" IS NULL OR ")
    where += " IS NULL"
    return where
  end

  def get_wrong_values(datensatz, alia = nil)
    cgen = Generica.new(datensatz, session[:user]) #Übergabe sind Tabellenname und User-id
    columns = cgen.get_ints_and_floats #gibt ref 8,9,81,91
    return 'false' if columns.empty?
    tabellen = columns.collect {|c| "#{alia.nil? ? datensatz : alia}.#{c['col_name']}"}
    where = tabellen.inject('') { |w,c| w+' '+c+([8,9].include?(c['reference_type'].to_i) ? '<=0 or':'<0 or') }
    where += " false"
    return where
  end

  def index_hash
    @user = theUser(session)
    @list_error,@error_count = prepare_count_for_index InkonsistenzenController::QS_ERROR
    @list_warning,@warning_count = prepare_count_for_index InkonsistenzenController::QS_WARNING
  end

  def prepare_count_for_index(arr)
    total_count = 0
    states = @user.get_states_for_read.join(", ") #is used in eval below
    rep_code = ActiveRecord::Base.connection.execute("SELECT key_report FROM reporting_periods WHERE state = 1")[0]['key_report'] #is used in eval below
    list = arr.collect do |sql|
      count = nil
      unless sql[:show_columns].nil?
        count = ActiveRecord::Base.connection.execute("select count(#{sql[:count_on]}) as num"+eval(sql[:statement]))[0]['num']
      else
        @gen = gen_for(sql[:datensatz])
        @data, @order = @gen.all(eval(sql[:where]), [],nil,@user.rows_per_page,params['offset'].to_i || 0, true)
        count = @data.rec_count
      end
      total_count += count.to_i
      puts "***#{sql[:id]}: #{count}***"
      sql[:count] = count
      sql
    end
    puts "***TOTAL_COUNT: #{total_count}"
    return list, total_count
  end

  def links_hash
  end

  def self.where(session)
    tmp = self.new

    id = session[:qs_sql]
    cache = session[:qs_cache][id]

    datensatz = cache[:datensatz]
    pkis = cache[:pkis]

    return [" AND "+datensatz+".id IN (" + pkis.join(",") + ")", nil]

  end

  def detail_hash
    @user = theUser(session)
    if params['amount'].to_i > 0
      @user.update_attributes(:rows_per_page => params['amount'].to_i)
    end

    session[:qs_sql] = params[:qs_sql] if params[:qs_sql]
    sql = InkonsistenzenController::QS_ERROR.detect{ |s| s[:id] == session[:qs_sql] }
    sql ||= InkonsistenzenController::QS_WARNING.detect { |s| s[:id] == session[:qs_sql] }
    @datensatz = sql[:datensatz]
    @heading = sql[:title]
    @wiki_link = sql[:wiki_link]
    @gen = gen
    @pruefung = sql[:id]

    @where = 'qs_query'

    unless session.key?(:qs_cache)
      session[:qs_cache] = Hash.new();
    end

    states = @user.get_states_for_read.join(", ") #is used in eval 1 line below
    #  puts "***#{"select #{sql[2]}"+eval(sql[3])}***
    unless sql[:show_columns].nil?
      stmt = "select #{sql[:show_columns].collect do |e|
        if !e[:row_id].nil?
#          e[:url_options] = {
#            :controller => e[:controller].nil? ? @datensatz : e[:controller],
#            :action => e[:action].nil? ? 'edit' : e[:action],
#          }
          %Q^#{e[:row_id]} as "#{e[:col_name].nil? ? 'row_id' : e[:col_name]}"^
        elsif !e[:spezial].nil?
          %Q^#{e[:spezial]} as "#{e[:col_name]}"^
        else
          e[:col_name]
        end
      end.join(', ')}"+eval(sql[:statement])
      stmt << "\nLIMIT ? OFFSET ?"

      if params['offset'].to_i < 0
        params['offset'] = 0
      end

      @columns = sql[:show_columns]

      @data = ActiveRecord::Base.execute_sql(stmt,@user.rows_per_page,params['offset'].to_i || 0).collect do |row|
        Generica::Record.new(row, nil, sql[:show_columns])
      end

      count = ActiveRecord::Base.connection.execute("select count(#{sql[:count_on]}) as num"+eval(sql[:statement]))[0]['num']
      Generica.add_count_custom(@data, count)
      @order = []
      render :template => 'inkonsistenzen/list'
    else

      rep_code = ActiveRecord::Base.connection.execute("SELECT key_report FROM reporting_periods WHERE state = 1")[0]['key_report']
      @data, @order = @gen.all(eval(sql[:where]), [], nil,@user.rows_per_page,params['offset'].to_i || 0)

      pkis = []

      # save pkis for csv export
      @data.each do |e|
	      pkis.push(e['id'])
      end

      session[:qs_cache][session[:qs_sql]] = Hash.new()
      session[:qs_cache][session[:qs_sql]][:datensatz] = @datensatz
      session[:qs_cache][session[:qs_sql]][:pkis] = pkis

      render :template => 'layouts/list'
    end
  end

  def heading
    str = 'Konsistenz'
    str << ' - '+@heading if !@heading.nil?
  end

  def freigabe
    #benutzt die "lock-Methode" aus Generika um in den Tabellen das "locked-Feld" des Bundeslandes des Users auf 1 zu setzen
    @user = theUser(session)
    unless @user.can_write?
      flash[:notice] = "Sie haben keine Berechtigung die Daten freizugeben!"
      redirect_to :controller => :start and return false
    end

    @list_error,@error_count = prepare_count_for_index InkonsistenzenController::QS_ERROR
    if @error_count == 0
      gen_for("agglomerations").lock
      gen_for("plants").lock
      gen_for("discharge_points").lock
      gen_for("federal_state_info").lock

      bula_reported = @user.get_states_for_write
      bula_txt = bula_reported.count == 1 ? "das Bundesland" : "die Bundesländer"
      bula_long = bula_reported.map{ |b| FederalState.find(b).name rescue b }
      bula_keys = bula_reported.map{ |b| FederalState.find(b).federal_key rescue b }

      message = <<EOM
Sehr geehrte Damen und Herren,

die Freigabe für #{bula_txt} #{bula_long.join(", ")} ist erfolgt.

Es grüßt die e-Kommunalabwasser-Anwendung.
EOM
      subject = "e-Kommunalabwasser: #{bula_keys.join(", ")}: Freigabe erfolgt"

      begin
        ActionMailer::Base.mail(
            :from => APPROVAL_SENDER,
            :cc => APPROVAL_SENDER,
            :to => APPROVAL_RECIPIENT,
            :subject => subject,
            :body => message
        ).deliver
      rescue Exception => e
        flash[:notice] = "Die Daten wurden erfolgreich freigegeben, es konnte jedoch keine E-Mail-Benachrichtigung an das Umweltbundesamt gesendet werden. Bitte versuchen Sie es später erneut."
        puts e.message
        puts e.backtrace.inspect
      else
        flash[:notice] = "Die Daten wurden erfolgreich freigegeben, es kann nun nichts mehr geändert werden. Das Umweltbundesamt wurde über die Freigabe der Daten informiert."
      end

    else
      flash[:notice] = "Eine Freigabe ist erst möglich, wenn die Qualitätssicherung keine Fehler mehr meldet."
      redirect_to :action => :index and return false
    end
    redirect_to :controller => :start
  end

  protected
  def gen
    return Generica.new(@datensatz, session[:user])
  end

  def gen_for(datensatz)
    return Generica.new(datensatz, session[:user])
  end
end
