[Mapbender-commits] r7427 - in trunk/mapbender: http/geoportal resources/db/pgsql/UTF-8/update

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2011-01-07 04:28:59 -0800 (Fri, 07 Jan 2011)
New Revision: 7427

Modified:
   trunk/mapbender/http/geoportal/mod_initialStartWmc.php
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7rc1_to_2.7rc2_pgsql_UTF-8.sql
Log:
Updates for new logging of public wmc function

Modified: trunk/mapbender/http/geoportal/mod_initialStartWmc.php
===================================================================
--- trunk/mapbender/http/geoportal/mod_initialStartWmc.php	2011-01-07 10:36:42 UTC (rev 7426)
+++ trunk/mapbender/http/geoportal/mod_initialStartWmc.php	2011-01-07 12:28:59 UTC (rev 7427)
@@ -61,11 +61,11 @@
 }
 //define sql for selecting informations from database:
 $sql = "";
-$sql .= "SELECT search_wmc_view.wmc_serial_id,search_wmc_view.wmc_title,search_wmc_view.wmc_abstract, custom_category.custom_category_code_".$languageCode. " ";
+$sql .= "SELECT search_wmc_view.wmc_serial_id,search_wmc_view.wmc_title,search_wmc_view.wmc_abstract, custom_category.custom_category_code_".$languageCode. ", search_wmc_view.load_count ";
 $sql .= "FROM search_wmc_view INNER JOIN wmc_custom_category ON "; 
 $sql .= "(wmc_custom_category.fkey_wmc_serial_id=search_wmc_view.wmc_serial_id) INNER JOIN custom_category ON ";
 $sql .= "(custom_category.custom_category_id=wmc_custom_category.fkey_custom_category_id) WHERE ";
-$sql .= "custom_category.custom_category_key = 'mbc1' LIMIT $1";
+$sql .= "custom_category.custom_category_key = 'mbc1' ORDER BY search_wmc_view.load_count DESC LIMIT $1 ";
 
 $v = array($maxObjects);
 $t = array('i');

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7rc1_to_2.7rc2_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7rc1_to_2.7rc2_pgsql_UTF-8.sql	2011-01-07 10:36:42 UTC (rev 7426)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7rc1_to_2.7rc2_pgsql_UTF-8.sql	2011-01-07 12:28:59 UTC (rev 7427)
@@ -2,3 +2,78 @@
 --bugfix for template_openlayers #759
 --
 UPDATE gui_element set e_pos='2' WHERE e_id='jq_ui_position';
+--
+--new function to get the load_count of wmc which are stored in the mapbender database
+--
+-- Function: f_wmc_load_count(integer)
+
+-- DROP FUNCTION f_wmc_load_count(integer);
+
+CREATE OR REPLACE FUNCTION f_wmc_load_count(integer)
+  RETURNS integer AS
+$BODY$
+DECLARE
+   wmc_rel int8;
+BEGIN
+wmc_rel := load_count from wmc_load_count where wmc_load_count.fkey_wmc_serial_id=$1; 
+IF wmc_rel IS NULL THEN
+	RETURN 0;
+ELSE
+	RETURN wmc_rel;
+END IF;
+END;
+$BODY$
+  LANGUAGE 'plpgsql' VOLATILE
+  COST 100;
+ALTER FUNCTION f_wmc_load_count(integer) OWNER TO postgres;
+--
+-- change view for search wmc to include the load_count
+--
+-- View: search_wmc_view
+
+-- DROP VIEW search_wmc_view;
+
+CREATE OR REPLACE VIEW search_wmc_view AS 
+ SELECT wmc_dep.fkey_user_id AS user_id, wmc_dep.wmc_id, wmc_dep.srs AS wmc_srs, wmc_dep.wmc_title, wmc_dep.abstract AS wmc_abstract, f_collect_searchtext_wmc(wmc_dep.wmc_id) AS searchtext, wmc_dep.wmc_timestamp, wmc_dep.department, wmc_dep.mb_group_name, wmc_dep.mb_group_title, wmc_dep.mb_group_country, wmc_dep.wmc_serial_id, f_wmc_load_count(wmc_dep.wmc_serial_id) as load_count, wmc_dep.mb_group_stateorprovince, f_collect_inspire_cat_wmc(wmc_dep.wmc_serial_id) AS md_inspire_cats, f_collect_custom_cat_wmc(wmc_dep.wmc_serial_id) AS md_custom_cats, f_collect_topic_cat_wmc(wmc_dep.wmc_id) AS md_topic_cats, transform(geometryfromtext(((((((((((((((((((('POLYGON(('::text || wmc_dep.minx::text) || ' '::text) || wmc_dep.miny::text) || ','::text) || wmc_dep.minx::text) || ' '::text) || wmc_dep.maxy::text) || ','::text) || wmc_dep.maxx::text) || ' '::text) || wmc_dep.maxy::text) || ','::text) || wmc_dep.maxx::text) || ' '::text) || wmc_dep.miny::text) || ','::text) || wmc_dep.minx::
 text) || ' '::text) || wmc_dep.miny::text) || '))'::text, regexp_replace(upper(wmc_dep.srs::text), 'EPSG:'::text, ''::text)::integer), 4326) AS the_geom, (((((wmc_dep.minx::text || ','::text) || wmc_dep.miny::text) || ','::text) || wmc_dep.maxx::text) || ','::text) || wmc_dep.maxy::text AS bbox, wmc_dep.mb_group_logo_path
+   FROM ( SELECT mb_user_wmc.wmc_public, mb_user_wmc.maxy, mb_user_wmc.maxx, mb_user_wmc.miny, mb_user_wmc.minx, mb_user_wmc.srs, mb_user_wmc.wmc_serial_id AS wmc_id, mb_user_wmc.wmc_serial_id, mb_user_wmc.wmc_title, mb_user_wmc.abstract, mb_user_wmc.wmc_timestamp, mb_user_wmc.fkey_user_id, user_dep.mb_group_id AS department, user_dep.mb_group_name, user_dep.mb_group_title, user_dep.mb_group_country, user_dep.mb_group_stateorprovince, user_dep.mb_group_logo_path
+           FROM ( SELECT registrating_groups.fkey_mb_user_id AS mb_user_id, mb_group.mb_group_id, mb_group.mb_group_name, mb_group.mb_group_title, mb_group.mb_group_country, mb_group.mb_group_stateorprovince, mb_group.mb_group_logo_path
+                   FROM registrating_groups, mb_group
+                  WHERE registrating_groups.fkey_mb_group_id = mb_group.mb_group_id) user_dep, mb_user_wmc
+          WHERE user_dep.mb_user_id = mb_user_wmc.fkey_user_id) wmc_dep
+  WHERE wmc_dep.wmc_public = 1
+  ORDER BY wmc_dep.wmc_id;
+
+ALTER TABLE search_wmc_view OWNER TO postgres;
+--
+-- Bugfix for normalize searchtext of wmc docs
+--
+-- Function: f_collect_searchtext_wmc(integer)
+
+-- DROP FUNCTION f_collect_searchtext_wmc(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_searchtext_wmc(integer)
+  RETURNS text AS
+$BODY$
+DECLARE
+    p_wmc_id ALIAS FOR $1;
+    
+    r_keywords RECORD;
+    l_result TEXT;
+BEGIN
+    l_result := '';
+    l_result := l_result || (SELECT COALESCE(wmc_title, '') || ' ' || COALESCE(abstract, '') FROM mb_user_wmc WHERE wmc_serial_id = p_wmc_id);
+    FOR r_keywords IN SELECT DISTINCT keyword FROM
+        (SELECT keyword FROM wmc_keyword L JOIN keyword K ON (K.keyword_id = L.fkey_keyword_id )
+        ) AS __keywords__ LOOP
+        l_result := l_result || ' ' || COALESCE(r_keywords.keyword, '');
+    END LOOP;
+   l_result := UPPER(l_result);
+   l_result := replace(replace(replace(replace(replace(replace(replace(l_result,'Ä','AE'),'ß','SS'),'Ö','OE'),'Ü','UE'),'ä','AE'),'ü','UE'),'ö','OE');
+
+    RETURN l_result;
+END;
+$BODY$
+  LANGUAGE 'plpgsql' VOLATILE
+  COST 100;
+ALTER FUNCTION f_collect_searchtext_wmc(integer) OWNER TO postgres;
+