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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2018-03-26 02:19:21 -0700 (Mon, 26 Mar 2018)
New Revision: 9887

Modified:
   trunk/mapbender/http/php/mod_showOpenDataOrganizations.php
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
Add orga filter for exporting opendata information to ckan instances

Modified: trunk/mapbender/http/php/mod_showOpenDataOrganizations.php
===================================================================
--- trunk/mapbender/http/php/mod_showOpenDataOrganizations.php	2018-03-20 08:40:33 UTC (rev 9886)
+++ trunk/mapbender/http/php/mod_showOpenDataOrganizations.php	2018-03-26 09:19:21 UTC (rev 9887)
@@ -20,13 +20,13 @@
 }
 $sql = <<<SQL
 
-select opendata.*, mb_group.uuid, mb_group_id, mb_group_title, mb_group_description, mb_group_homepage, mb_group_logo_path, mb_group_address, mb_group_postcode, mb_group_city, mb_group_email, timestamp from mb_group, (select (sum(count_wms_layer)+sum(count_metadata)) as number_of_opendata_ressources, group_id,mb_group_name from (
+select opendata.*, mb_group.uuid, mb_group_id, mb_group_title, mb_group_description, mb_group_homepage, mb_group_logo_path, mb_group_address, mb_group_postcode, mb_group_city, mb_group_email, timestamp, mb_group.mb_group_ckan_uuid from mb_group, (select (sum(count_wms_layer)+sum(count_metadata)) as number_of_opendata_ressources, group_id,mb_group_name from (
 
-select count(open_published_layers.layer_id) as count_wms_layer, 0 as count_metadata,open_published_layers.group_id, mb_group_name from (select layer_id, group_id, mb_group_name from layer , (select wms_group.count, mb_group_name,group_id, wms_id from mb_group, (select count(wms_id) ,f_get_responsible_organization_for_ressource(wms_id, 'wms') as group_id,wms_id  from wms where wms_id in (select fkey_wms_id from wms_termsofuse where fkey_termsofuse_id in (select termsofuse_id from termsofuse where isopen = 1)) group by group_id, wms_id) as wms_group where mb_group.mb_group_id = wms_group.group_id) as open_published_wms where layer.fkey_wms_id = open_published_wms.wms_id and layer_searchable = 1 and export2csw is true ) as open_published_layers left join ows_relation_metadata on open_published_layers.layer_id = ows_relation_metadata.fkey_layer_id where fkey_layer_id is null group by open_published_layers.group_id, open_published_layers.mb_group_name 
+select count(open_published_layers.layer_id) as count_wms_layer, 0 as count_metadata,open_published_layers.group_id, mb_group_name from (select layer_id, group_id, mb_group_name from layer , (select wms_group.count, mb_group_name,group_id, wms_id from mb_group, (select count(wms_id) ,f_get_responsible_organization_for_ressource(wms_id, 'wms') as group_id,wms_id  from wms where wms_id in (select fkey_wms_id from wms_termsofuse where fkey_termsofuse_id in (select termsofuse_id from termsofuse where isopen = 1)) group by group_id, wms_id) as wms_group where mb_group.mb_group_id = wms_group.group_id AND mb_group.export2ckan = TRUE) as open_published_wms where layer.fkey_wms_id = open_published_wms.wms_id and layer_searchable = 1 and export2csw is true ) as open_published_layers left join ows_relation_metadata on open_published_layers.layer_id = ows_relation_metadata.fkey_layer_id where fkey_layer_id is null group by open_published_layers.group_id, open_published_layers.mb_group_name 
 
 union
 
-select count_wms_layer, metadata_group.count_metadata, group_id,mb_group_name from mb_group, (select count(metadata_id) as count_metadata, 0 as count_wms_layer , f_get_responsible_organization_for_ressource(metadata_id, 'metadata') as group_id from mb_metadata where searchable is true and export2csw is true and metadata_id in (select fkey_metadata_id from md_termsofuse where fkey_termsofuse_id in (select termsofuse_id from termsofuse where isopen = 1)) group by group_id) as metadata_group where mb_group.mb_group_id = metadata_group.group_id
+select count_wms_layer, metadata_group.count_metadata, group_id,mb_group_name from mb_group, (select count(metadata_id) as count_metadata, 0 as count_wms_layer , f_get_responsible_organization_for_ressource(metadata_id, 'metadata') as group_id from mb_metadata where searchable is true and export2csw is true and metadata_id in (select fkey_metadata_id from md_termsofuse where fkey_termsofuse_id in (select termsofuse_id from termsofuse where isopen = 1)) group by group_id) as metadata_group where mb_group.mb_group_id = metadata_group.group_id AND mb_group.export2ckan = TRUE
 
 ) as opendata_ressources group by group_id, mb_group_name) as opendata 
 where opendata.group_id = mb_group.mb_group_id
@@ -36,9 +36,9 @@
 
 $sql2 = <<<SQL
 
-select opendata.*, mb_group.uuid, mb_group_id, mb_group_title, mb_group_description, mb_group_homepage, mb_group_logo_path, mb_group_address, mb_group_postcode, mb_group_city, mb_group_email, timestamp from mb_group, (select (sum(count_wms_layer)+sum(count_metadata)) as number_of_opendata_ressources, group_id,mb_group_name from (
+select opendata.*, mb_group.uuid, mb_group_id, mb_group_title, mb_group_description, mb_group_homepage, mb_group_logo_path, mb_group_address, mb_group_postcode, mb_group_city, mb_group_email, timestamp, mb_group.mb_group_ckan_uuid from mb_group, (select (sum(count_wms_layer)+sum(count_metadata)) as number_of_opendata_ressources, group_id,mb_group_name from (
 
-select count_wms_layer, metadata_group.count_metadata, group_id,mb_group_name from mb_group, (select count(metadata_id) as count_metadata, 0 as count_wms_layer , f_get_responsible_organization_for_ressource(metadata_id, 'metadata') as group_id from mb_metadata where searchable is true and export2csw is true and metadata_id in (select fkey_metadata_id from md_termsofuse where fkey_termsofuse_id in (select termsofuse_id from termsofuse where isopen = 1)) group by group_id) as metadata_group where mb_group.mb_group_id = metadata_group.group_id
+select count_wms_layer, metadata_group.count_metadata, group_id,mb_group_name from mb_group, (select count(metadata_id) as count_metadata, 0 as count_wms_layer , f_get_responsible_organization_for_ressource(metadata_id, 'metadata') as group_id from mb_metadata where searchable is true and export2csw is true and metadata_id in (select fkey_metadata_id from md_termsofuse where fkey_termsofuse_id in (select termsofuse_id from termsofuse where isopen = 1)) group by group_id) as metadata_group where mb_group.mb_group_id = metadata_group.group_id AND mb_group.export2ckan = TRUE
 
 ) as opendata_ressources group by group_id, mb_group_name) as opendata 
 where opendata.group_id = mb_group.mb_group_id
@@ -61,7 +61,7 @@
 while ($row = db_fetch_array($result)) {
         unset($orga);
 	$orga->serialId = $row['mb_group_id'];
-	$orga->id = $row['uuid'];
+	$orga->id = $row['mb_group_ckan_uuid'];
 	$orga->department_address = $row['mb_group_address'];
 	$orga->department_city = $row['mb_group_city'];
 	$orga->department_postcode = $row['mb_group_postcode'];

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2018-03-20 08:40:33 UTC (rev 9886)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2018-03-26 09:19:21 UTC (rev 9887)
@@ -2335,3 +2335,15 @@
 ALTER FUNCTION f_get_responsible_organization_for_ressource(integer, varchar)
   OWNER TO postgres;
 
+-- Column: export2ckan
+
+-- ALTER TABLE mb_group DROP COLUMN export2ckan;
+
+ALTER TABLE mb_group ADD COLUMN export2ckan boolean;
+ALTER TABLE mb_group ALTER COLUMN export2ckan SET DEFAULT true;
+
+UPDATE mb_group SET export2ckan = TRUE WHERE export2ckan IS NULL;
+
+
+
+