YouTube, OpenLearn, BBC and Podcasts content related to Q64

Qualification Related Content

prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
prefix podcast: <http://data.open.ac.uk/podcast/ontology/>
prefix yt: <http://data.open.ac.uk/youtube/ontology/>
prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
prefix saou: <http://data.open.ac.uk/saou/ontology#>
prefix dbp: <http://dbpedia.org/property/>
prefix dbpedia: <http://dbpedia.org/property/>
prefix media: <http://purl.org/media#>
prefix video: <http://purl.org/media/video#>
prefix mediaont: <http://www.w3.org/TR/2010/WD-mediaont-10-20100608/>
prefix olearn: <http://data.open.ac.uk/openlearn/ontology/>
prefix bazaar: <http://digitalbazaar.com/media/>
prefix schema: <http://schema.org/>
prefix oubbc:  <http://data.open.ac.uk/bbc/ontology/>
prefix po: <http://purl.org/ontology/po/>
prefix aiiso: <http://purl.org/vocab/aiiso/schema#>
prefix dct: <http://purl.org/dc/terms/>
prefix ctx: <http://data.open.ac.uk/context/>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT
  (?t AS ?Type)
  (?r AS ?Id)
  (str(?l) AS ?Label)
  (str(?p) AS ?Published)
  (str(?d) AS ?Duration)
  (str(?po) AS ?Part_Of)
  (str(?it) AS ?ITunesU)
  (str(?e) AS ?Embed)
  (str(?lo) AS ?Url)
  (?courses AS ?Courses)
FROM ctx:qualification
FROM ctx:course
FROM ctx:youtube
FROM ctx:bbc
FROM ctx:podcast
FROM ctx:openlearn
WHERE {
 {
 	# YouTube related content
	SELECT ?r ?l
	  (GROUP_CONCAT(distinct ?seriesL; separator=", ") as ?po)
	  ?d ?t  (MIN(?p_) AS ?p)
	  (GROUP_CONCAT(distinct ?code; separator=", ") as ?courses)
	 WHERE
	  {
	    ?qual aiiso:code "Q64" .
	    ?qual saou:hasPathway ?pathway.
	    ?pathway saou:hasStage ?stage.
	    ?stage saou:includesCompulsoryCourse ?course .
		?course aiiso:code ?code .
	    ?r yt:relatesToCourse ?course .
	    ?r a schema:VideoObject .
	    optional{ 
			?r dct:isPart ?series . 
			?series a video:Series . 
			?series rdfs:label ?seriesL 
		} .
	    ?r rdfs:label ?l .
	    ?r dct:published ?p_ .
	    optional { ?r media:duration ?d }.
	    BIND( "YouTube" as ?t ) . 
	 }  GROUP BY ?r ?l ?d ?t
 
 } UNION {
 	# BBC related
	SELECT   ?r ?l ?po (?scheduled as ?p) ?lo  ?t 
	  (GROUP_CONCAT(?on ; separator = ",") as ?where) 
	  (GROUP_CONCAT(distinct ?code; separator=", ") as ?courses)
	WHERE
	  {
	    []  po:broadcast_on ?on ;
	        po:broadcast_of ?r ;
	        po:schedule_date ?scheduled .  
	     ?qual aiiso:code "Q64" .
	     ?qual saou:hasPathway/saou:hasStage/saou:includesCompulsoryCourse ?course .
	     ?course aiiso:code ?code .
             ?r oubbc:relatesToCourse ?course .
	    {
	      SELECT ?r (MAX(?date) as ?scheduled)
	             where {
	              ?qual aiiso:code "Q64" .
	              ?qual saou:hasPathway/saou:hasStage/saou:includesCompulsoryCourse ?course .
	              ?r oubbc:relatesToCourse ?course .
	              []  po:schedule_date ?date ;
	                  po:broadcast_on ?on ;
	                  po:broadcast_of ?r . 
	             } group by ?r 
	      }
	   ?r rdfs:label ?l .
	   optional { ?r mediaont:locator ?lo }
	   optional { 
	        { ?r po:series ?po } 
	          union 
	        { ?po po:episode ?r } 
	    }
	   BIND( "BBCCoproduction" as ?t ) . 
	} GROUP BY ?l ?r ?scheduled ?lo ?t ?po
 } UNION {
	# Podcasts
	{
	 SELECT ?r ?l ?e ?d ?po ?it ?t
	   (max(?podpublishedrawAny) as ?p) 
	  (GROUP_CONCAT(distinct ?code; separator=", ") as ?courses)
	   {
	        ?qual aiiso:code "Q64".
			?qual saou:hasPathway/saou:hasStage/saou:includesCompulsoryCourse ?course .
			?course aiiso:code ?code .
			?r podcast:relatesToCourse ?course;
		 			rdfs:label ?l;
		 			podcast:embedCode ?e ;
		 			dct:published ?podpublishedrawAny ;
		 			mediaont:duration ?d_ ;
		 			dct:isPartOf ?podalbumraw .
			?podalbumraw rdfs:label ?po .
	        FILTER(CONTAINS(STR(?e),"mediaplayer")) .
			OPTIONAL {
			 		?podalbumraw  podcast:hasiTunesU ?it.
			} .
                BIND((xsd:integer(substr(?d_,7,2) ) + xsd:integer(substr(?d_,4,2) )*60 + xsd:integer(substr(?d_,1,2) )*3600) AS ?d ) .
	        BIND("Podcast" as ?t)
		} GROUP BY ?r ?l ?e ?d ?po ?t ?it
	}
 } UNION {
	# OpenLearn
	{ 
		SELECT ?r ?t  ?l  ?lo  (max(?olpublishedrawAny) as ?p) 
	  		(GROUP_CONCAT(distinct ?code; separator=", ") as ?courses) 
			{
				?r olearn:relatesToCourse ?course ;
		 			rdfs:label ?l;
					dct:published ?olpublishedrawAny ;
		 		dbpedia:url ?lo .
				?course aiiso:code ?code .
	        	?qual aiiso:code "Q64" .
				?qual saou:hasPathway/saou:hasStage/saou:includesCompulsoryCourse ?course .
				BIND("OpenLearn" as ?t)
			} GROUP BY ?r ?t ?l ?lo
		}
	}
}