SELECT 
	ds.id,
	ds.officialname,
	ds.englishname,
	ds.websiteurl,
	ds.logourl,
	ds.contactemail,
	ds.latitude,
	ds.longitude,
	ds.timezone,
	ds.namespaceprefix,
	ds.od_numberofitems, 
	ds.od_numberofitemsdate,
	ds.od_policies,
	ds.od_languages, 
	ds.od_contenttypes, 
	ds.collectedfrom,
	ds.inferred,
	ds.deletedbyinference,
	ds.trust,
	ds.inferenceprovenance,
	ds.dateofvalidation,
	ds.registeredby,
	ds.optional1,
	ds.optional2,
	ds.datasourceclass,
	ds.provenanceactionclass,
	ds.dateofcollection,
	ds.typology,
	ds.activationid, 
	ds.mergehomonyms,
	ds.description,
	ds.releasestartdate,
	ds.releaseenddate,
	ds.missionstatementurl,
	ds.dataprovider,
	ds.serviceprovider,
	ds.databaseaccesstype,
	ds.datauploadtype,
	ds.databaseaccessrestriction,
	ds.datauploadrestriction,
	ds.versioning,
	ds.citationguidelineurl,
	ds.qualitymanagementkind, 
	ds.pidsystems,
	ds.certificates, 
	ds.aggregator,
	ds.issn,
	ds.eissn,
	ds.lissn,
	(array_agg(o.legalname))[1] as organization,
	(array_agg(ccl.code))[1] as countrycode,
	(array_agg(ccl.name))[1] as countryname,
	array_agg(distinct ag.accessinfopackage) as accessinfopackage,
	to_char(now(), 'YYYY-MM-DDThh24:mi:ssZ') AS now
	
FROM datasources ds
	LEFT OUTER JOIN datasource_organization dso ON ds.id = dso.datasource
	LEFT OUTER JOIN organizations o ON dso.organization = o.id
	LEFT OUTER JOIN class ccl ON ccl.code = o.countryclass
	LEFT OUTER JOIN (
			SELECT 	api.datasource, api.contentdescriptionclass, api.compatibilityclass,
					COALESCE(api.id, '')||'<==1==>'||COALESCE(api.typologyclass, '')||'<==2==>'||COALESCE(api.compatibilityclass, '')||'<==3==>'||COALESCE(api.contentdescriptionclass,'')||'<==4==>'||COALESCE(api.protocolclass,'')||'<==5==>'||COALESCE(api.active,false)||'<==6==>'||COALESCE(api.removable,false)||'<==7==>'||array_to_string(array_agg(distinct COALESCE(ac.accessparam, true)||'###'||COALESCE(ac.param,'')||'###'||COALESCE(ac.value,'')||'###'), '@@@') AS accessinfopackage 
			FROM api 
			LEFT OUTER JOIN (
				SELECT api, param, accessparam, CASE WHEN edited IS NULL THEN original ELSE edited END as value FROM apicollections
			) AS ac ON (ac.api = api.id) 
			GROUP BY 
				api.id,
				api.datasource,
				api.contentdescriptionclass,
				api.typologyclass,
				api.compatibilityclass,
				api.protocolclass,
				api.active,
				api.removable
		) AS ag on (ag.datasource = ds.id)

$if(condition)$
	WHERE $condition$
$endif$

GROUP BY 
	ds.id,
	ds.officialname,
	ds.englishname,
	ds.websiteurl,
	ds.logourl,
	ds.contactemail,
	ds.latitude,
	ds.longitude,
	ds.timezone,
	ds.namespaceprefix,
	ds.od_numberofitems, 
	ds.od_numberofitemsdate,
	ds.od_policies,
	ds.od_languages, 
	ds.od_contenttypes, 
	ds.collectedfrom,
	ds.inferred,
	ds.deletedbyinference,
	ds.trust,
	ds.inferenceprovenance,
	ds.dateofvalidation,
	ds.registeredby,
	ds.optional1,
	ds.optional2,
	ds.datasourceclass,
	ds.provenanceactionclass,
	ds.dateofcollection,
	ds.typology,
	ds.activationid, 
	ds.mergehomonyms,
	ds.description,
	ds.releasestartdate,
	ds.releaseenddate,
	ds.missionstatementurl,
	ds.dataprovider,
	ds.serviceprovider,
	ds.databaseaccesstype,
	ds.datauploadtype,
	ds.databaseaccessrestriction,
	ds.datauploadrestriction,
	ds.versioning,
	ds.citationguidelineurl,
	ds.qualitymanagementkind, 
	ds.pidsystems,
	ds.certificates, 
	ds.aggregator,
	ds.issn,
	ds.eissn,
	ds.lissn
