|
1
|
SELECT * FROM (SELECT
|
|
2
|
(array_agg(DISTINCT o.countryclass))[1] AS country,
|
|
3
|
(array_agg(DISTINCT o.legalname))[1] AS organization,
|
|
4
|
d.id AS repoid,
|
|
5
|
d.officialname AS reponame,
|
|
6
|
d.englishname AS othername,
|
|
7
|
d.namespaceprefix AS repoprefix,
|
|
8
|
d.websiteurl AS repourl,
|
|
9
|
a.id AS id,
|
|
10
|
a.active AS active,
|
|
11
|
a.protocolclass AS protocol,
|
|
12
|
a.typologyclass AS type,
|
|
13
|
CASE
|
|
14
|
WHEN (accomp.edited IS NOT NULL) THEN accomp.edited
|
|
15
|
WHEN (accomp.original IS NOT NULL) THEN accomp.original
|
|
16
|
ELSE a.compatibilityclass
|
|
17
|
END AS compliance,
|
|
18
|
CASE
|
|
19
|
WHEN (acaggrdate.edited IS NOT NULL) THEN acaggrdate.edited
|
|
20
|
WHEN (acaggrdate.original IS NOT NULL) THEN acaggrdate.original
|
|
21
|
WHEN (acdowndate.edited IS NOT NULL) THEN acdowndate.edited
|
|
22
|
WHEN (acdowndate.original IS NOT NULL) THEN acdowndate.original
|
|
23
|
ELSE ''
|
|
24
|
END AS aggrdate,
|
|
25
|
CASE
|
|
26
|
WHEN (acaggrtotal.edited IS NOT NULL) THEN acaggrtotal.edited
|
|
27
|
WHEN (acaggrtotal.original IS NOT NULL) THEN acaggrtotal.original
|
|
28
|
WHEN (acdowntotal.edited IS NOT NULL) THEN acdowntotal.edited
|
|
29
|
WHEN (acdowntotal.original IS NOT NULL) THEN acdowntotal.original
|
|
30
|
ELSE '0'
|
|
31
|
END AS aggrtotal
|
|
32
|
FROM
|
|
33
|
api a
|
|
34
|
LEFT OUTER JOIN datasources d ON (a.datasource = d.id)
|
|
35
|
LEFT OUTER JOIN datasource_organization dao ON (d.id = dao.datasource)
|
|
36
|
LEFT OUTER JOIN organizations o ON (dao.organization = o.id)
|
|
37
|
LEFT OUTER JOIN apicollections accomp ON (a.id = accomp.api AND accomp.param = 'overriding_compliance')
|
|
38
|
LEFT OUTER JOIN apicollections acaggrdate ON (a.id = acaggrdate.api AND acaggrdate.param = 'last_aggregation_date')
|
|
39
|
LEFT OUTER JOIN apicollections acaggrtotal ON (a.id = acaggrtotal.api AND acaggrtotal.param = 'last_aggregation_total')
|
|
40
|
LEFT OUTER JOIN apicollections acdowndate ON (a.id = acdowndate.api AND acdowndate.param = 'last_download_date')
|
|
41
|
LEFT OUTER JOIN apicollections acdowntotal ON (a.id = acdowntotal.api AND acdowntotal.param = 'last_download_total')
|
|
42
|
GROUP BY
|
|
43
|
d.id, d.officialname, d.englishname, d.namespaceprefix, d.websiteurl,
|
|
44
|
a.id, a.active, a.protocolclass, a.typologyclass, a.compatibilityclass,
|
|
45
|
accomp.edited, accomp.original,
|
|
46
|
acaggrdate.edited, acaggrdate.original,
|
|
47
|
acdowndate.edited, acdowndate.original,
|
|
48
|
acaggrtotal.edited, acaggrtotal.original,
|
|
49
|
acdowntotal.edited, acdowntotal.original
|
|
50
|
) AS t WHERE
|
|
51
|
$if(field)$
|
|
52
|
$field$ = $delimeter$$value$$delimeter$
|
|
53
|
$else$
|
|
54
|
repoid ILIKE '%$value$%'
|
|
55
|
OR reponame ILIKE '%$value$%'
|
|
56
|
OR othername ILIKE '%$value$%'
|
|
57
|
OR repoprefix ILIKE '%$value$%'
|
|
58
|
OR repourl ILIKE '%$value$%'
|
|
59
|
OR organization ILIKE '%$value$%'
|
|
60
|
$endif$
|
|
61
|
|
|
62
|
|