-- View: public.report_liniove_komunikace_static_map

DROP VIEW public.report_liniove_komunikace_static_map;
CREATE OR REPLACE VIEW public.report_liniove_komunikace_static_map AS 
 WITH static_map AS (
         SELECT (((((((((((((((((((poloha.base_url::text || 'size='::text) || poloha.map_width::text) || ','::text) || poloha.map_height::text) || '&type='::text) || poloha.map_type::text) || '&bestfit='::text) || poloha.bounding_box) || '&center='::text) || poloha.centroid) || '&imagetype='::text) || poloha.image_type::text) || '&projection=sm&key='::text) || poloha.provider_key::text) || '&polyline=color:'::text) || poloha.stroke_color::text) || '|width:'::text) || poloha.stroke_width::text) || '|'::text) || poloha.latlons AS map_url, poloha.entity_id
           FROM ( SELECT field_data_field_poloha_komunikace_wgs.field_poloha_komunikace_wgs_geometry AS geo, pg_catalog.concat_ws(','::text, st_ymin(st_buffer(field_data_field_poloha_komunikace_wgs.field_poloha_komunikace_wgs_geometry, 0.005::double precision)::box3d), st_xmin(st_buffer(field_data_field_poloha_komunikace_wgs.field_poloha_komunikace_wgs_geometry, 0.005::double precision)::box3d), st_ymax(st_buffer(field_data_field_poloha_komunikace_wgs.field_poloha_komunikace_wgs_geometry, 0.005::double precision)::box3d), st_xmax(st_buffer(field_data_field_poloha_komunikace_wgs.field_poloha_komunikace_wgs_geometry, 0.005::double precision)::box3d)) AS bounding_box, 'somestringsomestring' AS base_url, 'keykeykeykey' AS provider_key, '400' AS map_width, '400' AS map_height, 'map' AS map_type, pg_catalog.concat(st_y(st_centroid(field_data_field_poloha_komunikace_wgs.field_poloha_komunikace_wgs_geometry)), ',', st_x(st_centroid(field_data_field_poloha_komunikace_wgs.field_poloha_komunikace_wgs_geometry))) AS centroid, 'PNG' AS image_type, '0x77ff0000' AS stroke_color, '5' AS stroke_width, regexp_replace(regexp_replace(st_astext(st_flipcoordinates(field_data_field_poloha_komunikace_wgs.field_poloha_komunikace_wgs_geometry)), '.*\(\((.*)\)\)'::text, '\1'::text), '\s'::text, ','::text, 'g'::text) AS latlons, field_data_field_poloha_komunikace_wgs.entity_id
                   FROM field_data_field_poloha_komunikace_wgs) poloha
        )
 SELECT string_agg(DISTINCT static_map.map_url, ''::text) AS string_agg, 
 node.nid AS id, 
 node.nid, 
 node.title, 
 to_char(to_timestamp(node.created::double precision), 
  'DD.MM. YYYY HH24:MI:SS'::text) AS "vytvořeno", 
 to_char(to_timestamp(node.changed::double precision), 
  'DD.MM. YYYY HH24:MI:SS'::text) AS upraveno, 
 node.uid AS upravil, 
 string_agg(DISTINCT cislo_komunikace.field_cislo_komunikace_value::text, ''::text) AS field_cislo_komunikace_value, 
 string_agg(DISTINCT evidencni_cislo.field_evidencni_cislo_value::text, ''::text) AS field_evidencni_cislo_value, 
 string_agg(DISTINCT nazev_ulice.field_nazev_ulice_value::text, ''::text) AS field_nazev_ulice_value, 
 max(nosnost.field_nosnost_komunikace_value) AS field_nosnost_komunikace_value, 
 max(pocet_pruhu.field_pocet_pruhu_value) AS field_pocet_pruhu_value, 
 unnest(array_agg(DISTINCT poloha.field_poloha_komunikace_wgs_geometry)) AS field_poloha_komunikace_wgs_geometry, 
 array_agg(DISTINCT povrch.field_povrch_target_id) AS povrchy_ids, 
 string_agg(DISTINCT povrch_tax.name::text, ','::text) AS povrchy, 
 string_agg(DISTINCT poznamka.field_poznamka_value, ''::text) AS field_poznamka_value, 
 max(provoz.field_provoz_na_kom_target_id) AS field_provoz_na_kom_target_id, 
 string_agg(DISTINCT provoz_tax.name::text, ''::text) AS provoz_na_komunikaci, 
 max(sirka.field_sirka_komunikace_value) AS field_sirka_komunikace_value, 
 st_length(st_transform(unnest(array_agg(DISTINCT poloha.field_poloha_komunikace_wgs_geometry)), 32633)) AS delka, 
 max(stav.field_stav_target_id) AS field_stav_target_id, 
 string_agg(DISTINCT stav_tax.name::text, ''::text) AS stav_komunikace,
 max(stupen_dopr_vytizeni.field_stupen_dopr_vytizeni_target_id) AS field_stupen_dopr_vytizeni_target_id,
 string_agg(DISTINCT stupen_vytizeni_tax.name,'') AS stupen_vytizeni,
 max(trida.field_trida_komunikace_target_id) AS field_trida_komunikace_target_id, 
 string_agg(DISTINCT trida_tax.name::text, ''::text) AS trida_komunikace, 
 max(obec.field_obec_target_id) AS field_obec_target_id, 
 string_agg(DISTINCT obec_node.title::text, ''::text) AS obec_komunikace, 
 array_agg(DISTINCT cast_obce.field_cast_obce_target_id) AS casti_obce_ids, 
 string_agg(DISTINCT cast_obce_node.title::text, ','::text) AS casti_obce_komunikace, 
 array_agg(DISTINCT vlastnik.field_vlastnik_target_id) AS vlastnici_ids, 
 string_agg(DISTINCT vlastnik_node.title::text, ','::text) AS vlastnici_komunikace, 
 array_agg(DISTINCT spravce.field_spravce_target_id) AS spravci_ids, 
 string_agg(DISTINCT spravce_node.title::text, ','::text) AS spravci_komunikace, 
 max(krajnice.field_krajnice_target_id) AS field_krajnice_target_id, 
 string_agg(DISTINCT krajnice_tax.name::text, ''::text) AS krajnice_komunikace, 
 max(tloustka_povrchu.field_tloustka_povrchu_value) AS field_tloustka_povrchu_value, 
 unnest(array_agg(DISTINCT rok_vystavby.field_rok_vystavby_value)) AS field_rok_vystavby_value, 
 max(pocet_mostu.field_pocet_mostu_value) AS field_pocet_mostu_value, 
 max(delka_mostu.field_delka_mostu_value) AS field_delka_mostu_value, 
 unnest(array_agg(DISTINCT zimni_udrzba.field_zimni_udrzba_value)) AS field_zimni_udrzba_value, 
 array_agg(DISTINCT katastralni_uzemi.field_katastralni_uzemi_target_id) AS katastralni_uzemi_ids,
 string_agg(DISTINCT katastralni_uzemi_node.title::text, ','::text) AS katastralni_uzemi_komunikace 
   FROM static_map
   LEFT JOIN node ON static_map.entity_id = node.nid
   LEFT JOIN field_data_field_cislo_komunikace cislo_komunikace ON node.nid = cislo_komunikace.entity_id
   LEFT JOIN field_data_field_evidencni_cislo evidencni_cislo ON node.nid = evidencni_cislo.entity_id
   LEFT JOIN field_data_field_nazev_ulice nazev_ulice ON node.nid = nazev_ulice.entity_id
   LEFT JOIN field_data_field_nosnost_komunikace nosnost ON node.nid = nosnost.entity_id
   LEFT JOIN field_data_field_pocet_pruhu pocet_pruhu ON node.nid = pocet_pruhu.entity_id
   -- LEFT JOIN field_revision_field_poloha_komunikace_wgs poloha ON node.nid = poloha.entity_id
   LEFT JOIN (SELECT distinct entity_id, max(revision_id) OVER(partition by entity_id), max(field_poloha_komunikace_wgs_geometry) OVER(partition by entity_id)::geometry AS field_poloha_komunikace_wgs_geometry FROM field_revision_field_poloha_komunikace_wgs) poloha ON node.nid =  poloha.entity_id
   LEFT JOIN field_data_field_povrch povrch ON node.nid = povrch.entity_id
   LEFT JOIN field_data_field_poznamka poznamka ON node.nid = poznamka.entity_id
   LEFT JOIN field_data_field_provoz_na_kom provoz ON node.nid = provoz.entity_id
   LEFT JOIN field_data_field_sirka_komunikace sirka ON node.nid = sirka.entity_id
   LEFT JOIN field_data_field_stav stav ON node.nid = stav.entity_id
   LEFT JOIN field_data_field_stupen_dopr_vytizeni stupen_dopr_vytizeni ON node.nid = stupen_dopr_vytizeni.entity_id
   LEFT JOIN field_data_field_trida_komunikace trida ON node.nid = trida.entity_id
   LEFT JOIN field_data_field_obec obec ON node.nid = obec.entity_id
   LEFT JOIN field_data_field_cast_obce cast_obce ON node.nid = cast_obce.entity_id
   LEFT JOIN field_data_field_vlastnik vlastnik ON node.nid = vlastnik.entity_id
   LEFT JOIN field_data_field_tloustka_povrchu tloustka_povrchu ON node.nid = tloustka_povrchu.entity_id
   LEFT JOIN field_data_field_rok_vystavby rok_vystavby ON node.nid = rok_vystavby.entity_id
   LEFT JOIN field_data_field_spravce spravce ON node.nid = spravce.entity_id
   LEFT JOIN field_data_field_krajnice krajnice ON node.nid = krajnice.entity_id
   LEFT JOIN field_data_field_pocet_mostu pocet_mostu ON node.nid = pocet_mostu.entity_id
   LEFT JOIN field_data_field_delka_mostu delka_mostu ON node.nid = delka_mostu.entity_id
   LEFT JOIN field_data_field_zimni_udrzba zimni_udrzba ON node.nid = zimni_udrzba.entity_id
   LEFT JOIN field_data_field_katastralni_uzemi katastralni_uzemi ON node.nid = katastralni_uzemi.entity_id
   LEFT JOIN taxonomy_term_data stav_tax ON stav.field_stav_target_id = stav_tax.tid
   LEFT JOIN taxonomy_term_data stupen_vytizeni_tax ON stupen_dopr_vytizeni.field_stupen_dopr_vytizeni_target_id = stupen_vytizeni_tax.tid
   LEFT JOIN taxonomy_term_data trida_tax ON trida.field_trida_komunikace_target_id = trida_tax.tid
   LEFT JOIN taxonomy_term_data provoz_tax ON provoz.field_provoz_na_kom_target_id = provoz_tax.tid
   LEFT JOIN taxonomy_term_data povrch_tax ON povrch.field_povrch_target_id = povrch_tax.tid
   LEFT JOIN node cast_obce_node ON cast_obce.field_cast_obce_target_id = cast_obce_node.nid
   LEFT JOIN node katastralni_uzemi_node ON katastralni_uzemi.field_katastralni_uzemi_target_id = katastralni_uzemi_node.nid
   LEFT JOIN taxonomy_term_data krajnice_tax ON krajnice.field_krajnice_target_id = krajnice_tax.tid
   LEFT JOIN node obec_node ON obec.field_obec_target_id = obec_node.nid
   LEFT JOIN node vlastnik_node ON vlastnik.field_vlastnik_target_id = vlastnik_node.nid
   LEFT JOIN node spravce_node ON spravce.field_spravce_target_id = spravce_node.nid
  WHERE node.type::text = 'komunikace_linie'::text AND node.status = 1
  GROUP BY node.nid;

ALTER TABLE public.report_liniove_komunikace_static_map
  OWNER TO ess;
GRANT ALL ON TABLE public.report_liniove_komunikace_static_map TO ess;
GRANT ALL ON TABLE public.report_liniove_komunikace_static_map TO atomicon;
