ReportnetDataflows.sparql

#####################################
# Reportnet: Data flows             #
# http://cr.eionet.europa.eu/sparql #
#####################################

PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX rod: <http://rod.eionet.europa.eu/schema.rdf#>
PREFIX obl: <http://rod.eionet.europa.eu/obligations/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?instrument ?subj AS ?obluri ?obltitle ?lastUpdate ?reportingFrequencyMonths AS ?freqMonths 
       ?nextdeadline ?responsibleRole AS ?nationalReportingContact ?isEEAPrimary AS ?coreDataFlow ?isTerminated ?requester 
       SUM(?formalReporters) AS ?count1 sql:GROUP_CONCAT(?list1, ", ") AS ?formalReporters 
       SUM(?voluntaryReporters) AS ?count2 sql:GROUP_CONCAT(?list2, ", ") AS ?voluntaryReporters 
  #    str(?primaryRepository) AS ?primaryRepository  
  #    str(?dataUsedFor) AS ?dataUsedFor 
WHERE {

    ?subj a rod:Obligation ;
        rod:isTerminated ?isTerminated ;
        rod:isEEAPrimary ?isEEAPrimary ;
        rod:requester ?reportTo ;
        rod:instrument ?insturi .

    ?insturi dcterms:title ?insttitle .
    OPTIONAL { ?insturi rdfs:label ?instrument }

    ?reportTo rdfs:label ?requester .

    OPTIONAL { ?subj rod:responsibleRole ?responsibleRole }
    OPTIONAL { ?subj rod:dataUsedFor ?dataUsedFor }
    OPTIONAL { ?subj rod:lastUpdate ?lastUpdate }

    OPTIONAL { ?subj rod:reportingFrequencyMonths ?reportingFrequencyMonths }
    OPTIONAL { ?subj rod:primaryRepository ?primaryRepository }
    OPTIONAL { ?subj rod:nextdeadline  ?nextdeadline }
    OPTIONAL { ?subj dcterms:title ?obltitle }

  # Operational data flows
    FILTER ( ?isEEAPrimary = True OR ?subj in (obl:9, obl:106, obl:141, obl:156, obl:158, obl:171, obl:213, obl:268, obl:276, obl:367, obl:371, obl:387, obl:524, obl:540, obl:556, obl:559, obl:572, obl:601, obl:602, obl:603, obl:607, obl:608, obl:611, obl:612, obl:614, obl:632, obl:655, obl:656, obl:661, obl:665, obl:670, obl:671, obl:672, obl:674, obl:679, obl:680, obl:681, obl:682, obl:683, obl:690, obl:692, obl:693, obl:694, obl:696, obl:698, obl:700, obl:703, obl:704, obl:705, obl:706, obl:713, obl:716, obl:717, obl:718, obl:719, obl:720))

  # Core data flows
  FILTER ( ?isEEAPrimary = True )

  # Instrument filter
  # FILTER ( ?insturi = <http://rod.eionet.europa.eu/instruments/652> )

  # FILTER ( ?isTerminated = False )

  {
    SELECT ?subj count(?formalReporter) AS ?formalReporters 0 AS ?voluntaryReporters sql:GROUP_CONCAT(?countryCode, ", ") AS ?list1
    WHERE {

        ?subj a rod:Obligation 
        OPTIONAL { 
                   ?subj rod:formalReporter ?formalReporter .
                   ?formalReporter rod:loccode ?countryCode .
        }

     } GROUP BY ?subj

  } UNION {

    SELECT ?subj 0 AS ?formalReporters count(?voluntaryReporter) AS ?voluntaryReporters sql:GROUP_CONCAT(?countryCode, ", ") AS ?list2
    WHERE {

        ?subj a rod:Obligation 
        OPTIONAL { 
                   ?subj rod:voluntaryReporter ?voluntaryReporter .
                   ?voluntaryReporter rod:loccode ?countryCode .
        }

    } GROUP BY ?subj

  }
} GROUP BY ?instrument ?subj ?obltitle ?isTerminated ?nextdeadline ?reportingFrequencyMonths ?isTerminated ?responsibleRole ?lastUpdate ?primaryRepository ?isEEAPrimary ?requester ?dataUsedFor ORDER BY ?instrument ?obltitle