SELECT * FROM (SELECT
	(array_agg(DISTINCT o.countryclass))[1] AS country,
	(array_agg(DISTINCT o.legalname))[1]    AS organization,
	d.id                                    AS repoid,
	d.officialname                          AS reponame,
	d.namespaceprefix                       AS repoprefix,
	d.websiteurl                            AS repourl,
	a.id                                    AS id,
	a.active                                AS active,
	a.protocolclass                         AS protocol,
	a.typologyclass                         AS type,
	CASE 
		WHEN (accomp.edited IS NOT NULL) THEN accomp.edited 
		WHEN (accomp.original IS NOT NULL) THEN accomp.original
		ELSE a.compatibilityclass
	END AS compliance,
	CASE 
		WHEN (acaggrdate.edited   IS NOT NULL) THEN acaggrdate.edited 
		WHEN (acaggrdate.original IS NOT NULL) THEN acaggrdate.original
		WHEN (acdowndate.edited   IS NOT NULL) THEN acdowndate.edited 
		WHEN (acdowndate.original IS NOT NULL) THEN acdowndate.original
		ELSE ''
	END AS aggrdate,
	CASE 
		WHEN (acaggrtotal.edited   IS NOT NULL) THEN acaggrtotal.edited 
		WHEN (acaggrtotal.original IS NOT NULL) THEN acaggrtotal.original
		WHEN (acdowntotal.edited   IS NOT NULL) THEN acdowntotal.edited 
		WHEN (acdowntotal.original IS NOT NULL) THEN acdowntotal.original
		ELSE '0'
	END AS aggrtotal
FROM
	api a 
	LEFT OUTER JOIN datasources d               ON (a.datasource = d.id)
	LEFT OUTER JOIN datasource_organization dao ON (d.id = dao.datasource)
	LEFT OUTER JOIN organizations o             ON (dao.organization = o.id)
	LEFT OUTER JOIN apicollections accomp       ON (a.id = accomp.api      AND accomp.param = 'overriding_compliance')
	LEFT OUTER JOIN apicollections acaggrdate   ON (a.id = acaggrdate.api  AND acaggrdate.param = 'last_aggregation_date')
	LEFT OUTER JOIN apicollections acaggrtotal  ON (a.id = acaggrtotal.api AND acaggrtotal.param = 'last_aggregation_total')
	LEFT OUTER JOIN apicollections acdowndate   ON (a.id = acdowndate.api  AND acdowndate.param = 'last_download_date')
	LEFT OUTER JOIN apicollections acdowntotal  ON (a.id = acdowntotal.api AND acdowntotal.param = 'last_download_total')
	GROUP BY
		d.id, d.officialname, d.namespaceprefix, d.websiteurl,
		a.id, a.active, a.protocolclass, a.typologyclass, a.compatibilityclass,
		accomp.edited, accomp.original,
		acaggrdate.edited, acaggrdate.original, 
		acdowndate.edited, acdowndate.original,
		acaggrtotal.edited, acaggrtotal.original,
		acdowntotal.edited, acdowntotal.original
) AS t WHERE
	$if(field)$
		$field$ = $delimeter$$value$$delimeter$
	$else$
		repoid ILIKE '%$value$%' 
		OR reponame ILIKE '%$value$%' 
		OR repoprefix ILIKE '%$value$%' 
		OR repourl ILIKE '%$value$%' 
		OR organization ILIKE '%$value$%' 
	$endif$


