temp_deleted_20180316.txt

{
        externalLayers: {
            bingLayer: {
                label: 'Bing Layer',
                key: 'AuhiCJHlGzhg93IqUH_oCpl_-ZUrIE6SPftlyGYUvr9Amx5nzA-WqGcPquyFZl4L',
            }
        },
        withUrl: {
            mapbox: {
                label: 'MapBox',
                mapOpts: {
                    url: 'https://api.mapbox.com/v4/mapbox.streets/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoicmFrc2hhayIsImEiOiJ5cHhqeHlRIn0.Vi87VjI1cKbl1lhOn95Lpw',
                    options: {
                        attribution: '© <a href="https://www.mapbox.com/feedback/">Mapbox</a> © <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a>'
                    }
                }
            },
            googleSatLayer: {
                label: 'Google Satellite',
                mapOpts: {
                    url: 'http://{s}.google.com/vt/lyrs=s&x={x}&y={y}&z={z}',
                    options: {
                        maxZoom: 20,
                        subdomains: ['mt0', 'mt1', 'mt2', 'mt3']
                    }
                }

            }
        }
    }










             /*   $('body').off('click', this.options.submitBtnSelector);

            $('body').on('click', this.options.submitBtnSelector, function (e) {
                e.preventDefault();
                var formData = parseAttributesForm(self.formDomObj);

                if (self.options.updateCb && self.options.updateCb instanceof Function) {
                    self.options.updateCb(formData, self)
                }
            });*/















    /**
     * Create leaflet Markers from  marker definitions - markersData
     *
     * @param markersData       - marker definitions {lat:, lng: , draggable: .... other}
     * @param leafletMap        - leaflet map instance
     * @param layerGroup        - if specified markers will be added to this layer group L.layerGroup([])
     * @param addToMap boolean  - if true will add marker layer to leaflet map
     * @param clearLayer boolean - if true will clear provided layer
     * @returns {layerGroup} featureMarkers
     */
    function _createMarkersOnLayer(options) {
        var markersData = options.markersData;
        var addToMap = options.addToMap;
        var clearLayer = options.clearLayer;
        var iconIdentifierKey = options.iconIdentifierKey;

        _handleLayers(clearLayer, addToMap);

        var i = 0, marker;

        var dataCnt = markersData.length;

        for (i; i < dataCnt; i += 1) {
            marker = createDashBoardMarker({
                marker: markersData[i],
                iconIdentifierKey: iconIdentifierKey
            });
            marker.addTo(featureMarkers);

        }

        return featureMarkers;
    }



.wb-actions-row {
    width: 100%;
    display: block;
    position: relative;
    padding: 0.1em 0 0.3em;
}


.wb-actions-row .wb-actions {
    padding: 0 5px;
}

.wb-actions-row .geo-search-wrap {
    position: relative;
    display: block;
    float: right;
    width: 100%;
    min-width: 150px;
    max-width: 350px;
}












<div class="navbar navbar-fixed-top">
    <div class="container-fluid">
        <div class="navbar-header">
            <button type="button" class="navbar-toggle collapsed" data-toggle="collapse"
                    data-target="#wb-navbar-collapse" aria-expanded="false">
                <span class="sr-only">Toggle navigation</span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
            </button>
            <a class="navbar-brand" href="/" title="Waterboard"></a>
        </div>
        <div class="collapse navbar-collapse" id="wb-navbar-collapse">
            <ul class="nav navbar-nav">
                <li class="active">
                    <a href="/table-report">Back <span class="sr-only">(current)</span></a>
                </li>
{#                <li><a href="#">Link</a></li>#}
            </ul>
            <ul class="nav navbar-nav navbar-right">
                {% if not user.is_authenticated %}
                    <li id="login_id">
                        <a href="/login">

                            Login
                        </a>
                    </li>
                {% else %}
                    <li>
                        <a href="/logout">
                            Logout
                        </a>
                    </li>
                {% endif %}
            </ul>
        </div>
    </div>
</div>
</nav>





/**
 * Get chart filter keys (filter field names) from chart config
 * @param chartConf
 * @returns {*}
 */
DashboardController.getFilterableChartKeys = function (chartConf) {
    return Object.keys(chartConf).reduce(function (acc, val, i) {
        if (chartConf[val].isFilter === true) {
            acc[acc.length] = chartConf[val].name;
        }
        return acc;
    }, []);
};






/**
 * Get chart keys for specified chart type
 * @param chartConf
 * @param chartType
 * @returns {*}
 */
DashboardController.getChartKeysByChartType = function (chartConf, chartType) {
    return Object.keys(chartConf).reduce(function (acc, val, i) {
        if (chartConf[val].chartType === chartType) {
            acc[acc.length] = val;
        }
        return acc;
    }, []);
};










function SimpleStorage(storage) {
    this.storage = storage || {};
}


// TODO unneded - remoev prolly
SimpleStorage.prototype = {
    setItem: function (key, val) {
        this.storage[key] = val;
        return this.storage[key];
    },
    getItem: function (key) {
        if (key !== undefined && key !== null) {
            return _.get(this.storage, key);
        }
        return this.storage;
    },
    getItems: function (keys) {
        var key, i = 0, items = {};

        var keysCnt = (keys || []).length;

        if (keysCnt > 1) {
            for (i; i < keysCnt; i += i) {
                key = keys[i];

                if (key !== undefined && key !== null) {
                    items[key] = _.get(this.storage, key);
                }
            }
            return items;
        }
        return this.storage;
    },

    removeItem: function (key) {
        delete this.storage[key];
    },
    setStorage: function (storage) {
        this.storage = storage || {};
    }
};

WB.Storage = new SimpleStorage(globalVars);


















    /** TODO REMOVE
     * After resize / debounce
     * @param func
     * @param wait
     * @param endCheck
     * @returns {Function}
     */
    function _debounce(func, wait, endCheck) {
        var timeout;

        return function () {
            const that = this;
            const args = arguments;

            const later = function () {
                timeout = null;
                if (!endCheck) {
                    func.apply(that, args);
                }
            };

            const callNow = endCheck && !timeout;

            clearTimeout(timeout);

            timeout = setTimeout(later, wait);

            if (callNow) {
                func.apply(that, args);
            }
        };
    }











    /** TODO remove
     * taken  from: https://github.com/cosmosio/nested-property
     * Get the property of an object nested in one or more objects
     * given an object such as a.b.c.d = 5, getNestedProperty(a, "b.c.d") will return 5.
     * @param {Object} object the object to get the property from
     * @param {String} property the path to the property as a string
     * @returns the object or the the property value if found
     */
    function _getNestedProperty(object, property) {
        if (object && typeof object == "object") {
            if (typeof property == "string" && property !== "") {
                var split = property.split(".");
                return split.reduce(function (obj, prop) {
                    return obj && obj[prop];
                }, object);
            } else if (typeof property == "number") {
                return object[property];
            } else {
                return object;
            }
        } else {
            return object;
        }
    }

















        WB.utils.ax({
            method: 'POST',
            url: '/data/',
            data: opts.data,
            successCb: opts.successCb || function (data) {
                WB.controller.updateDashboards(data);
            },
            errorCb: opts.errorCb || function (request, error) {

                WB.notif.options({
                    message: 'Could not Fetch Dashboard data.',
                    type: 'danger'
                }).show();

            }
        });




{#    <script src="https://cdnjs.cloudflare.com/ajax/libs/html5shiv/3.7.3/html5shiv.min.js" integrity="sha256-3Jy/GbSLrg0o9y5Z5n1uw0qxZECH7C6OQpVBgNFYa0g=" crossorigin="anonymous"></script>#}




      var dynamicColumns = reportTableDataAttributes.map(function (attribute) {
        return {
          data: attribute.key,
          title: '<div>' + attribute.label + '</div>',
          searchable: attribute.searchable,
          orderable: attribute.orderable
        };
      });

      var TABLE_REPORT_COLUMNS = [{
        data: '_last_update',
        title: 'Last Update',
        searchable: false,
        render: timestampColumnRenderer,
        orderable: true
      }, {
        data: '_webuser',
        title: 'User',
        searchable: false,
        orderable: true
      }].concat(dynamicColumns);

      var options = {
        dataTable: {
          "dom": 'l<"wb-export-toolbar">frtip',
          scrollX: true,
          fixedHeader: true,
          columns: TABLE_REPORT_COLUMNS,
          order: [[0, 'desc']],
          lengthMenu: TABLE_ROWS_PER_PAGE,
          rowClickCb: tableRowClickHandlerFn,
          serverSide: true,
          // this is only throttling and not debouncing, for debouncing we need to fully control search input events
          searchDelay: 400,
          ajax: {
            url: '/table-data',
            type: 'POST'
          }
        }
      };



















    // TODO combine all init functions
 /*     var WB = (function (module) {
        if (!module.controller) {
          module.controller = {};
        }

        // init module and set options
        WB.notif = WB.SimpleNotification()
          .options({
            message: null,
            type: 'success',
            fadeOut: {
              delay: Math.floor(Math.random() * 500) + 2500,
              enabled: true
            }
          });

      })(WB || {});*/
    WB.notif = WB.SimpleNotification()
      .options({
        message: null,
        type: 'success',
        fadeOut: {
          delay: Math.floor(Math.random() * 500) + 2500,
          enabled: true
        }
      });
    WB.notif();

    WB.FeatureForm = new SimpleForm({
      formId: 'feature-create-form',
      parentId: 'formWrap',
      isEnabled: true,
      isBtnVisible: true,
      submitBtnSelector: '#create_button',
      onSubmit: function (formData, formInstance) {
        WB.loadingModal.show();
        formInstance.formDomObj.submit();
      },
      accordionConf: {
        selector: '#data-accordion',
        opts: {
          heightStyle: "content",
          header: "div > h3"
        }
      }
    });

    // Leaflet Map

    var coords = WB.FeatureForm.getFormFieldValues(['_latitude', '_longitude']);

    var markerGeometry = {lon: 38.3, lat: 14.3};

    if (coords._longitude && coords._latitude) {
      markerGeometry = {
        lon: coords._longitude,
        lat: coords._latitude
      };
    } else {
      WB.FeatureForm.setFormFieldValues({
        _longitude: markerGeometry.lon,
        _latitude: markerGeometry.lat
      });
    }


    // setup
    WB.mapInstance = wbMap()
      .layerConf(TILELAYER_DEFINITIONS)
      .leafletConf({
        zoom: 12,
        editable: true
      }, 'MapBox')
      .markerRenderer(createFeatureByUUidMarker)
      .initMapSearch({
        parentId: 'geo-search-wrap'
      })
      .markerData([{
        geometry: markerGeometry,
        data: {},
        draggable: true,
        zoomToMarker: true
      }]);

    // init
    WB.mapInstance('featureMapWrap');

    // add markers
    WB.mapInstance.renderMarkers({});













create or replace function core_utils.prepare_filtered_dashboard_data(i_webuser_id integer, i_min_x double precision, i_min_y double precision, i_max_x double precision, i_max_y double precision, i_filters json default '{}'::json)
returns void
LANGUAGE plpgsql
AS $BODY$
declare
    l_query text;
    l_filter_query text;
    l_filter text;
    l_is_staff boolean;
    l_geofence geometry;
    l_woreda_predicate text;
    l_geofence_predicate text;
begin
    -- TODO handle ranges
-- {"tabiya":"Egub","fencing_exists":"No","funded_by":"FoodSecurity","water_committe_exist":"Unknown","static_water_level":4,"amount_of_deposited":4,"yield":5,"should_not_appeat":null}
l_filter_query:= format($WHERE_FILTER$
SELECT
    string_agg('and (' || same_filter_values || ')', ' ')
from
  (
    SELECT distinct
      filter_key,
       string_agg(
           filter_key || '=' || quote_literal(filter_value) , ' or '
       ) over (partition by filter_key) as same_filter_values
    FROM (
      SELECT
        "key" as filter_key,
        json_array_elements_text("value"::json) as filter_value
      FROM
          json_each_text(
              '%s'::JSON
          )
    ) a
  where a.filter_value is not null
    group by
      filter_key,
      filter_value
) k
$WHERE_FILTER$, i_filters);

    execute l_filter_query into l_filter;
                -- point_geometry && ST_SetSRID(ST_MakeBox2D(ST_Point(-180, -90), ST_Point(180, 90)), 4326)

    -- check if user has is_staff
    l_query := format('select is_staff OR is_readonly, geofence FROM webusers_webuser where id = %L', i_webuser_id);

    EXECUTE l_query INTO l_is_staff, l_geofence;

    IF l_is_staff = FALSE
    THEN
        l_woreda_predicate := format(' AND woreda IN (SELECT unnest(values) FROM webusers_grant WHERE webuser_id = %L)',
                                     i_webuser_id);
    ELSE
        l_woreda_predicate := NULL;
    END IF;

    -- geofence predicate
    IF l_geofence IS NOT NULL THEN
        l_geofence_predicate := format(' AND st_within(point_geometry, %L)', l_geofence);
    ELSE
        l_geofence_predicate := NULL;
    END IF;

    -- create temporary table so the core_utils.get_core_dashboard_data is called only once
    -- filtering / aggregation / statistics should be taken from tmp_dashboard_chart_data
    l_query :=  format($TEMP_TABLE_QUERY$create temporary table if not exists tmp_dashboard_chart_data on commit drop
        as
        select *
        from (
          select *,
            CASE
                WHEN static_water_level::FLOAT >= 100
                  THEN 5
                WHEN static_water_level::FLOAT >= 50 AND static_water_level::FLOAT < 100
                  THEN 4
                WHEN static_water_level::FLOAT >= 20 AND static_water_level::FLOAT < 50
                  THEN 3
                WHEN static_water_level::FLOAT > 10 AND static_water_level::FLOAT < 20
                  THEN 2
                ELSE 1
                END AS static_water_level_group_id,
                CASE
                      WHEN amount_of_deposited::FLOAT >= 5000
                          THEN 5
                      WHEN amount_of_deposited::FLOAT >= 3000 AND amount_of_deposited::FLOAT < 5000
                          THEN 4
                      WHEN amount_of_deposited::FLOAT >= 500 AND amount_of_deposited::FLOAT < 3000
                          THEN 3
                      WHEN amount_of_deposited::FLOAT > 1 AND amount_of_deposited::FLOAT < 500
                          THEN 2
                      ELSE 1
                  END AS amount_of_deposited_group_id,
            CASE
                WHEN yield::FLOAT >= 6
                  THEN 5
                WHEN yield::FLOAT >= 3 AND yield::FLOAT < 6
                  THEN 4
                WHEN yield::FLOAT >= 1 AND yield::FLOAT < 3
                  THEN 3
                WHEN yield::FLOAT > 0 AND yield::FLOAT < 1
                  THEN 2
                ELSE 1
                END        AS yield_group_id
            FROM
            features.active_data
        ) core_data
        WHERE
            point_geometry && ST_SetSRID(ST_MakeBox2D(ST_Point(%s, %s), ST_Point(%s, %s)), 4326)
          %s %s %s
    $TEMP_TABLE_QUERY$, i_min_x, i_min_y, i_max_x, i_max_y, l_filter, l_woreda_predicate, l_geofence_predicate);

    execute l_query;
END;
$BODY$;























-- *
-- core_utils.create_feature
-- *


CREATE or replace FUNCTION core_utils.create_feature(i_feature_changeset integer, i_feature_point_geometry geometry, i_feature_attributes text)
  RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
    v_new_attributes JSONB;
    v_key            TEXT;

    v_attr_id        INTEGER;
    v_result_type    TEXT;
    v_allowed_values TEXT [];

    v_feature_uuid   uuid;

    v_int_value      INTEGER;
    v_decimal_value  DECIMAL(9, 2);
    v_text_value     text;

BEGIN

    -- insert a new feature

    INSERT INTO features.feature (feature_uuid, changeset_id, point_geometry, is_active)
    VALUES (
        uuid_generate_v4(), i_feature_changeset, i_feature_point_geometry,
        TRUE
    ) RETURNING feature_uuid INTO v_feature_uuid;

    -- v_new_attributes := jsonb_strip_nulls(i_feature_attributes::jsonb);
    v_new_attributes := i_feature_attributes::jsonb;
    -- cast(i_feature_attributes AS JSONB);

    -- collect type definitions
    CREATE TEMPORARY TABLE tmp_attribute_types ON COMMIT DROP AS
        SELECT
            aa.id,
            aa.key AS key,
            aa.result_type,
            array_agg(ao.value) AS allowed_values
        FROM attributes_attribute aa
            JOIN attributes_attributegroup ag ON aa.attribute_group_id = ag.id
            LEFT JOIN attributes_attributeoption ao ON ao.attribute_id = aa.id
        GROUP BY aa.id, aa.key, aa.result_type;

    FOR v_key IN SELECT * FROM jsonb_object_keys(v_new_attributes) LOOP
        -- check attributes
        SELECT
            id,
            result_type,
            allowed_values
        INTO
            v_attr_id,
            v_result_type,
            v_allowed_values
        FROM tmp_attribute_types
        WHERE key = v_key;

        IF NOT FOUND
        THEN
            RAISE NOTICE 'Attribute="%" is not defined, skipping', v_key;
            CONTINUE;
        END IF;

        -- check attribute type
        IF v_result_type = 'Integer'
        THEN
            v_int_value := v_new_attributes ->> v_key;


            -- only insert new data if the value has changed
            -- insert new data
            INSERT INTO features.feature_attribute_value (feature_uuid, changeset_id, attribute_id, val_int)
            VALUES (
                v_feature_uuid, i_feature_changeset, v_attr_id, v_int_value
            );

        ELSEIF v_result_type = 'Decimal'
            THEN
                v_decimal_value := v_new_attributes ->> v_key;

            -- only insert new data if the value has changed
                -- insert new data
                INSERT INTO features.feature_attribute_value (feature_uuid, changeset_id, attribute_id, val_real)
                VALUES (
                    v_feature_uuid, i_feature_changeset, v_attr_id, v_decimal_value
                );

        ELSEIF v_result_type = 'Text'
            THEN
                -- for whatever reason text values must be extracted as text (oprerator ->>)
                v_text_value := v_new_attributes ->> v_key;

                -- only insert new data if the value has changed

                -- insert new data
                INSERT INTO features.feature_attribute_value (feature_uuid, changeset_id, attribute_id, val_text)
                VALUES (
                    v_feature_uuid, i_feature_changeset, v_attr_id, nullif(v_text_value::text, '')
                );

        ELSEIF v_result_type = 'DropDown'
            THEN
                v_int_value := v_new_attributes ->> v_key;

                -- only insert new data if the value has changed
                IF NOT (v_allowed_values @> ARRAY [v_int_value :: TEXT])
                THEN
                    RAISE 'Attribute "%" value "%" is not allowed: %', v_key, v_int_value, v_allowed_values;
                END IF;

                -- insert new data
                INSERT INTO features.feature_attribute_value (feature_uuid, changeset_id, attribute_id, val_int)
                VALUES (
                    v_feature_uuid, i_feature_changeset, v_attr_id, v_int_value
                );

        END IF;

    END LOOP;

    -- we need to refresh the materialized view
   -- execute core_utils.refresh_active_data();

    RETURN v_feature_uuid::text;
END;
$$;











-- *
-- * create materialized view active_data and refresh
-- *

CREATE OR REPLACE FUNCTION core_utils.refresh_active_data()
    RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_query TEXT;
    l_args TEXT;
    l_field_def TEXT;
BEGIN
    v_query := $attributes$
    select
        string_agg(quote_literal(key), ',' ORDER BY key) as args,
        string_agg(key || ' text', ', ' ORDER BY key) as field_def
    from (
        SELECT key
        FROM
            attributes_attribute
        ORDER BY
            key
    )d;
    $attributes$;

    EXECUTE v_query
    INTO l_args, l_field_def;

    v_query := format($q$
    CREATE MATERIALIZED VIEW IF NOT EXISTS features.active_data AS (
        select * from core_utils.get_core_dashboard_data(
            %s
        ) as (
            point_geometry geometry, email varchar, ts timestamp with time zone, feature_uuid uuid,
            %s
        )
    );$q$, l_args, l_field_def);

    EXECUTE v_query;

    -- unique index is required for concurrent updates
    CREATE UNIQUE INDEX IF NOT EXISTS features_active_data_feature_uuid ON features.active_data (feature_uuid);

    REFRESH MATERIALIZED VIEW CONCURRENTLY features.active_data;

END;
$$;











-- *
-- core_utils.get_feature_by_changeset_uuid
-- *

CREATE OR REPLACE FUNCTION
		core_utils.get_feature_by_changeset_uuid(i_uuid UUID, i_changeset_id int)
RETURNS TEXT AS
$BODY$
SELECT coalesce(jsonb_agg(d.row) :: TEXT, '[]') AS data
FROM (
       SELECT jsonb_build_object(
                  '_feature_uuid', ft.feature_uuid :: TEXT,
                  '_created_date', chg.ts_created,
                  '_data_captor', wu.email,
                  '_geometry', ARRAY [ST_Y(ft.point_geometry), ST_X(ft.point_geometry)]
              ) || coalesce(attributes.row, '{}' :: JSONB) AS row
       FROM
         features.feature ft

			 LEFT JOIN LATERAL (
					 SELECT
							 fav.feature_uuid,
							 jsonb_object_agg(
									 dg.key || '/' || da.key,
										row_to_json(fav) -> CASE
													WHEN da.result_type = 'Integer'
															THEN 'val_int'
													WHEN da.result_type = 'Decimal'
															THEN 'val_real'
													WHEN da.result_type = 'Text'
															THEN 'val_text'
													WHEN da.result_type = 'DropDown'
															THEN 'val_int'
													WHEN da.result_type = 'MultipleChoice'
															THEN 'val_text'
													ELSE NULL
													END
								) AS row
					 FROM
							 features.feature_attribute_value fav
							 JOIN public.attributes_attribute da ON da.id = fav.attribute_id
							 JOIN public.attributes_attributegroup dg ON dg.id = da.attribute_group_id
					 WHERE
							 fav.feature_uuid = ft.feature_uuid
					and fav.changeset_id = i_changeset_id
					 GROUP BY fav.feature_uuid
			 ) attributes ON TRUE
			JOIN
					features.changeset chg ON ft.changeset_id = chg.id
			JOIN
				webusers_webuser wu ON chg.webuser_id = wu.id

       WHERE
         ft.feature_uuid = $1
			 AND
					 ft.changeset_id = i_changeset_id
       GROUP BY ft.feature_uuid, chg.ts_created, wu.email, ft.point_geometry,
         attributes.row
       ORDER BY ft.feature_uuid) d;
$BODY$
LANGUAGE SQL;














-- *
-- core_utils.get_event_by_uuid
-- *

CREATE OR REPLACE FUNCTION core_utils.get_event_by_uuid(i_uuid UUID)
    RETURNS TEXT AS
$BODY$
SELECT coalesce(jsonb_agg(d.row) :: TEXT, '[]') AS data
FROM (
       SELECT jsonb_build_object(
                  '_feature_uuid', ft.feature_uuid :: TEXT,
                  '_created_date', chg.ts_created,
                  '_data_captor', wu.email,
                  '_geometry', ARRAY [ST_X(ft.point_geometry), ST_Y(ft.point_geometry)]
              ) || coalesce(attributes.row, '{}' :: JSONB) AS row
       FROM
         features.feature ft

             LEFT JOIN LATERAL (
                       SELECT
                           fav.feature_uuid,
                           jsonb_object_agg(
                               dg.key || '/' || da.key,
                                row_to_json(fav) -> CASE
                                    WHEN da.result_type = 'Integer'
                                        THEN 'val_int'
                                    WHEN da.result_type = 'Decimal'
                                        THEN 'val_real'
                                    WHEN da.result_type = 'Text'
                                        THEN 'val_text'
                                    WHEN da.result_type = 'DropDown'
                                        THEN 'val_int'
                                    WHEN da.result_type = 'MultipleChoice'
                                        THEN 'val_text'
                                    ELSE NULL
                                    END
                            ) AS row
                       FROM
                           features.feature_attribute_value fav
                           JOIN public.attributes_attribute da ON da.id = fav.attribute_id
                           JOIN public.attributes_attributegroup dg ON dg.id = da.attribute_group_id
                       WHERE
                           fav.feature_uuid = ft.feature_uuid
                           AND fav.is_active = TRUE
                       GROUP BY fav.feature_uuid
                       ) attributes ON TRUE

                 JOIN features.changeset chg ON ft.changeset_id = chg.id
             JOIN webusers_webuser wu ON chg.webuser_id = wu.id

       WHERE
         ft.feature_uuid = $1
         AND ft.is_active = TRUE
       GROUP BY ft.feature_uuid, chg.ts_created, wu.email, ft.point_geometry,
         attributes.row
       ORDER BY ft.feature_uuid) d;
$BODY$
LANGUAGE SQL
STABLE
COST 100;



















-- *
-- core_utils.create_changeset, used in attributes / features
-- *

CREATE OR REPLACE FUNCTION core_utils.create_changeset(i_webuser_id integer)
  RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  v_new_changeset_id INTEGER;
BEGIN

  INSERT INTO features.changeset (webuser_id) VALUES (i_webuser_id) RETURNING id INTO v_new_changeset_id;

  RETURN v_new_changeset_id;
END;

$$;
-- drop function core_utils.create_feature(i_feature_changeset integer, i_feature_point_geometry geometry, i_feature_attributes text)
























-- *
-- * core_utils.get_typed_core_dashboard_row
-- *
-- * TODO is same as core_utils.get_typed_core_dashboard_data(), returns single row by feature uuid
-- * TODO will be removed or merged later

CREATE OR REPLACE FUNCTION core_utils.get_typed_core_dashboard_row(i_feature_uuid uuid)
  RETURNS SETOF record
STABLE
LANGUAGE plpgsql
AS $$
DECLARE l_query text;
        l_field_list text;
        l_field_vals text;
    l_field_def TEXT;
		l_field_cast TEXT;
BEGIN

-- TODO refactor this..
	 l_query := $attributes$
    select
        '{' || string_agg(key, ',' ORDER BY key) || '}' as l_field_list,
        string_agg('(' || quote_literal(key) || ')', ',' ORDER BY key) as field_vals,
        string_agg(key || ' ' || field_type, ', ' ORDER BY key) as field_def,
	      string_agg('attrs.' ||key || '::'|| field_type , ' ,' ORDER BY key) as  field_cast
    from (
        SELECT key,
					 case
						when result_type = 'Integer' THEN 'int'
						when result_type = 'Decimal' THEN 'float'
						ELSE 'text'
					 end as field_type
        FROM
            attributes_attribute aa
        ORDER BY
            key
    )d;
    $attributes$;

    EXECUTE l_query
    INTO l_field_list, l_field_vals,l_field_def, l_field_cast;

l_query := format($OUTER_QUERY$
SELECT
	  ff.point_geometry
    , wu.email
		, chg.ts_created as ts
		, ff.feature_uuid
		, %s
FROM
			crosstab(
				$INNER_QUERY$select
						ff.feature_uuid,
						aa.key as attribute_key,
					 case
						when aa.result_type = 'Integer' THEN fav.val_int::text
						when aa.result_type = 'Decimal' THEN fav.val_real::text
						when aa.result_type = 'Text' THEN fav.val_text::text
						when aa.result_type = 'DropDown' THEN ao.option
						ELSE null
					 end as val
				from
					features.feature ff
				JOIN
					features.feature_attribute_value fav
				ON
					ff.feature_uuid = fav.feature_uuid
				join
					attributes_attribute aa
				on
					fav.attribute_id = aa.id
				left JOIN
					 attributes_attributeoption ao
				ON
					fav.attribute_id = ao.attribute_id
				AND
						ao.value = val_int
				where
				 fav.is_active = True
             and
					 ff.feature_uuid = %L

					 fav.is_active = True
				and
					 ff.is_active = True
			    and
			        aa.key = any(%L)
				order by 1,2
				$INNER_QUERY$, $VALUES$ VALUES %s $VALUES$
			) as attrs (
                    feature_uuid uuid, %s
			 )
JOIN
  features.feature ff
ON
	attrs.feature_uuid = ff.feature_uuid
JOIN
    features.changeset chg
ON
    ff.changeset_id = chg.id
JOIN
      webusers_webuser wu
ON
    chg.webuser_id = wu.id
where
		 ff.is_active = True
		 and ff.feature_uuid =%L
$OUTER_QUERY$, l_field_cast, i_feature_uuid, l_field_list, l_field_vals, l_field_def, i_feature_uuid

);
raise notice '%s', l_query;
  return Query execute l_query;
END;

$$;















> Nominatim search example


     curl "https://nominatim.openstreetmap.org/search?q=zagreb&format=json"

     [
      {
        "place_id": "178856888",
        "licence": "Data © OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
        "osm_type": "relation",
        "osm_id": "3168167",
        "boundingbox": [
          "45.7473017",
          "45.938004",
          "15.8217504",
          "16.1034054"
        ],
        "lat": "45.813177",
        "lon": "15.977048",
        "display_name": "Zagreb, City of Zagreb, Croatia",
        "class": "place",
        "type": "city",
        "importance": 0.27562188954477,
        "icon": "https://nominatim.openstreetmap.org/images/mapicons/poi_place_city.p.20.png"
      }]

> MapBox search sample

    wget https://api.mapbox.com/geocoding/v5/mapbox.places/zagreb.json?access_token=pk.map_box_key

    {
      "type": "FeatureCollection",
      "query": [
        "zagreb"
      ],
      "features": [
        {
          "id": "place.11788683325166430",
          "type": "Feature",
          "place_type": [
            "place"
          ],
          "relevance": 1,
          "properties": {
            "wikidata": "Q1435"
          },
          "text": "City of Zagreb",
          "place_name": "City of Zagreb, Zagrebacka županija, Croatia",
          "matching_text": "Zagreb",
          "matching_place_name": "Zagreb, Zagrebacka županija, Croatia",
          "bbox": [
            15.774355,
            45.614282,
            16.243531,
            45.969044
          ],
          "center": [
            15.95,
            45.8
          ],
          "geometry": {
            "type": "Point",
            "coordinates": [
              15.95,
              45.8
            ]
          },
          "context": [
            {
              "id": "region.229834",
              "short_code": "HR-01",
              "wikidata": "Q27038",
              "text": "Zagrebacka županija"
            },
            {
              "id": "country.362",
              "short_code": "hr",
              "wikidata": "Q224",
              "text": "Croatia"
            }
          ]
        }]