Save and Share Source Code

@prefix : 		<http://www.knoodl.com/EIW/RDBMappingOntology/instances/DITPR#> .
@prefix rr: 	<http://www.w3.org/ns/r2rml#> .
@prefix irb: 	<https://www.commonvocabulary.army.mil/ui/groups/IRBAnalytics/vocab/IRB_Domain_Ontology/entry/> .
@prefix rdf: 	<http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix rdfs: 	<http://www.w3.org/2000/01/rdf-schema#> .
@prefix map: 	<http://revelytix.com/spyder/vocab/mapping/> .
@prefix xsd: 	<http://www.w3.org/2001/XMLSchema#> .

## Main System Map - Everything contained in the SYSTEM Table.  Untested

:SystemMap
	a rr:TriplesMapClass
	; rr:SQLQuery """
	SELECT APPLIC_ID, ACRONYM, APPLIC_NAME, BCMPNT_CD, 
	CASE 
	WHEN ISYS_BUS_FUNC = 'F' THEN 'Financial Management'
	WHEN ISYS_BUS_FUNC = 'A' THEN 'Acquisition'
	WHEN ISYS_BUS_FUNC = 'S' THEN 'Industrial Based Security'
	WHEN ISYS_BUS_FUNC = 'L' THEN 'Logistics and Materiel Readiness'
	WHEN ISYS_BUS_FUNC = 'I' THEN 'Installations and Environment'
	WHEN ISYS_BUS_FUNC = 'H' THEN 'Human Resources Management'
	WHEN ISYS_BUS_FUNC = 'M' THEN 'Foreign Military Sales'
	WHEN ISYS_BUS_FUNC = 'E' THEN 'Enterprise IT Infrastructure'
	ELSE 'Unknown'
	END AS BUSINESS_FUNCTION,
	CASE 
	WHEN ISYS_MOD_COTS = 'Y' THEN 'Yes'
	WHEN ISYS_MOD_COTS = 'N' THEN 'No'
	ELSE 'Unknown'
	END AS COTS_MOD
	FROM SYSTEM"""
	; rr:subjectMap [
		rr:class irb:System
		; rr:template "https://www.commonvocabulary.army.mil/ui/groups/IRBAnalytics/vocab/IRB_Domain_Ontology/entry/System-{APPLIC_ID}"
		; rr:termtype "IRI"
	]

## Predicate Object Maps contained in the SYSTEM Table

; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasSystemID ]
		; rr:objectMap 	[ rr:column "APPLIC_ID" ; rr:datatype xsd:string ]
	]
	
	; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasSystemAcronym ]
		; rr:objectMap 	[ rr:column "ACRONYM" ; rr:datatype xsd:string ]
	]

; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasSystemName ]
		; rr:objectMap 	[ rr:column "APPLIC_NAME" ; rr:datatype xsd:string ]
	]
	
	; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasBusinessFunction ]
		; rr:objectMap 	[ rr:column "BUSINESS_FUNCTION" ; rr:datatype xsd:string ]
	]
	
	; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasModifiedCOTSStatus ]
		; rr:objectMap 	[ rr:column "COTS_MOD" ; rr:datatype xsd:string ]
	]
	
## Referenced Predicate Object Maps Contained Elsewhere

; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasComponent ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :ComponentMap
			; rr:joinCondition "child.{BCMPNT_CD} = parent.{BCMPNT_CD}"
		]
	]
		
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasFunctionalDomain ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :FunctionalDomainMap
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
	
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasMissionArea ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :MissionAreaMap
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	] ;

; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasBEAComplianceStatus ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :BEAComplianceMap
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
	
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasBPRPerformedStatus ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :BPRPerformedMap
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
	
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasLatestBEAVersion ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :BEAVersionMap
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
	
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasOV5Activity ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :OV5_Map
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
	
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasOV6CProcess ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :OV6C_Map
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
	
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasNDAAExemption ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :NDAAExemptionMap
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
	
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasCertificationYear ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :CertificationYearMap
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
	
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasCertificationID ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :CertificationIDMap
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
	
	; rr:refPredicateObjectMap [
		rr:refPredicateMap [ rr:predicate irb:hasBEAComplianceBoolean ]
		; rr:refObjectMap  [
			rr:parentTriplesMap :BEAComplianceBooleanMap
			; rr:joinCondition "child.{APPLIC_ID} = parent.{APPLIC_ID}"
		]
	]
.

## Ref Predicate Object Maps for Properties

:CERTREQ_Query
	a map:SQLPattern
	; rdfs:label "CERT_REQ"
	; map:opaque "true"
	; map:pattern
		"""SELECT APPLIC_ID, CERTRQ_REF_YR, CERTRQ_REF_ID, CERTRQ_BEA_VRSN, CERTRQ_NDAA_BEA, CERTRQ_NDAA_BPR, 
			CASE
			WHEN cert.CERTRQ_NDAA_CATBC = 'B' THEN 'Critical Capability'
			WHEN cert.CERTRQ_NDAA_CATBC = 'C' THEN 'Adverse Impact'
			WHEN cert.CERTRQ_NDAA_CATBC = 'N' THEN 'Neither'
			ELSE 'Unknown'
			END AS NDAA_EXEMPT_CAT
			FROM SYS_CERT_REQ cert , 
			(SELECT MAX(CERTRQ_ID) AS maxID, APPLIC_ID AS ID 
			FROM SYS_CERT_REQ GROUP BY APPLIC_ID ) maxCert  
			WHERE CERT.CERTRQ_ID = maxCERT.maxID 
			AND CERT.APPLIC_ID = maxCert.ID"""
	; map:columns "APPLIC_ID CERTRQ_REF_YR CERTRQ_REF_ID CERTRQ_BEA_VRSN CERTRQ_NDAA_BEA CERTRQ_NDAA_BPR NDAA_EXEMPT_CAT"
	.

:BEAComplianceBooleanMap
	a rr:TriplesMapClass
	; map:source :CERTREQ_Query
	; rr:subjectMap [ rr:column "CERTRQ_NDAA_BEA" ; rr:datatype xsd:string ]
	.
	
:BPRPerformedMap
	a rr:TriplesMapClass
	; map:source :CERTREQ_Query
	; rr:subjectMap [ rr:column "CERTRQ_NDAA_BPR" ; rr:datatype xsd:string ]
	.
	
:BEAVersionMap
	a rr:TriplesMapClass
	; map:source :CERTREQ_Query
	; rr:subjectMap [ rr:column "CERTRQ_BEA_VRSN" ; rr:datatype xsd:string ]
	.
	
:NDAAExemptionMap
	a rr:TriplesMapClass
	; map:source :CERTREQ_Query
	; rr:subjectMap [ rr:column "NDAA_EXEMPT_CAT" ; rr:datatype xsd:string ]
	.
	
:CertificationYearMap
	a rr:TriplesMapClass
	; map:source :CERTREQ_Query
	; rr:subjectMap [ rr:column "CERTRQ_REF_YR" ; rr:datatype xsd:string ]
	.
	
:CertificationIDMap
	a rr:TriplesMapClass
	; map:source :CERTREQ_Query
	; rr:subjectMap [ rr:column "CERTRQ_REF_ID" ; rr:datatype xsd:string ]
	.

## Other subjectMaps used by refPredicateObjectMaps.
	
:ComponentMap
	a rr:TriplesMapClass
	; rr:SQLQuery "SELECT BCMPNT_CD, BCMPNT_NAME, BCMPNT_ACRONYM,  REPLACE( REPLACE( REPLACE( REPLACE(REPLACE(BCMPNT_ACRONYM, ' ', '%20'), '(', '%28'), ')', '%29'), '/', '%2F'), '&', '%26') AS URI_CD  FROM REF_COMPONENT"
	; rr:subjectMap [ rr:class irb:Component 
		; rr:template "https://www.commonvocabulary.army.mil/ui/groups/IRBAnalytics/vocab/IRB_Domain_Ontology/entry/Component-{URI_CD}"
		;  rr:termtype "IRI" ]
		
	; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasComponentName ]
		; rr:objectMap  [ rr:column "BCMPNT_NAME" ; rr:datatype xsd:string ]
	]
	
	; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasComponentAcronym ]
		; rr:objectMap  [ rr:column "BCMPNT_ACRONYM" ; rr:datatype xsd:string ]
	]
	.

:FunctionalDomainMap
	a rr:TriplesMapClass
	; rr:SQLQuery """SELECT SYS.APPLIC_ID, REF.DOMAIN_ACRONYM, REF.DOMAIN_FORMAL_NAME
		FROM SYS_DOMAIN SYS
		JOIN REF_DOMAIN REF
		ON SYS.DOMAIN_ID = REF.DOMAIN_ID
		WHERE SYS.DOMAIN_ROLE = 'PRIMARY'"""
	;  rr:subjectMap [ rr:class irb:FunctionalDomain
		; rr:template "https://www.commonvocabulary.army.mil/ui/groups/IRBAnalytics/vocab/IRB_Domain_Ontology/entry/FunctionalDomain-{DOMAIN_ACRONYM}"
		; rr:termtype "IRI" ] 
		
	; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasFunctionalDomainAcronym ]
		; rr:objectMap  [ rr:column "DOMAIN_ACRONYM" ; rr:datatype xsd:string ]
	]
	
	; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasFunctionalDomainName ]
		; rr:objectMap  [ rr:column "DOMAIN_FORMAL_NAME" ; rr:datatype xsd:string ]
	]
	.

## Does this need to return REF.DOMAIN_FORMAL_NAME?
:MissionAreaMap
	a rr:TriplesMapClass
	; rr:SQLQuery """SELECT SYS.APPLIC_ID, REF.DOMAIN_MSN_AREA_CD, REF.DOMAIN_FORMAL_NAME
		FROM SYS_DOMAIN SYS
		JOIN REF_DOMAIN REF
		ON SYS.DOMAIN_ID = REF.DOMAIN_ID
		WHERE SYS.DOMAIN_ROLE = 'PRIMARY'"""
	; rr:subjectMap [ rr:class irb:MissionArea
		; rr:template "https://www.commonvocabulary.army.mil/ui/groups/IRBAnalytics/vocab/IRB_Domain_Ontology/entry/MissionArea-{DOMAIN_MSN_AREA_CD}"
		; rr:termtype "IRI" ] 
		
	; rr:predicateObjectMap [
		rr:predicateMap [ rr:predicate irb:hasMissionAreaAcronym ]
		; rr:objectMap  [ rr:column "DOMAIN_MSN_AREA_CD" ; rr:datatype xsd:string ]
	]
	.

## BEA Compliance Status

:BEAComplianceQuery
	a map:SQLPattern
		; rdfs:label "CERT_REQ"
		; map:opaque "true"
		; map:pattern """SELECT cert.APPLIC_ID AS APPLIC_ID, r.BEA_CMPLNC_TITLE AS BEA_COMPLIANCE
			FROM SYS_CERT_STATUS cert, REF_BEA_COMPLIANCE r, 
			(SELECT MAX(CERTRQ_ID) AS maxID, APPLIC_ID AS AP_ID
			FROM SYS_CERT_STATUS
			GROUP BY APPLIC_ID) maxCert
			WHERE cert.CERTRQ_ID = maxCert.maxID
			AND cert.APPLIC_ID = maxCert.AP_ID
			AND cert.CERTST_BEA_CMPLNC = r.BEA_CMPLNC_CD"""
		; map:columns "APPLIC_ID BEA_COMPLIANCE"
.

:BEAComplianceMap
	a rr:TriplesMapClass
	; map:source :BEAComplianceQuery
	; rr:subjectMap [ rr:column "BEA_COMPLIANCE" ; rr:datatype xsd:string ]
	.

## OV-5 Activity
:OV5_Map
	a rr:TriplesMapClass ;
	rr:SQLQuery """SELECT DISTINCT s.APPLIC_ID, SYSTEM_OA
					FROM REF_OPERATIONAL_ACTIVITY r , SYS_OPERATIONAL_ACTIVITY s 
					WHERE s.OA_ID = r.OA_ID 
					AND ((s.OA_TYPE = 'AS IS') OR (s.OA_TYPE = 'BOTH'))
					AND r.BEA_VERSION > '6.9'
					AND r.OA_LVL_ID < 2""" ;
	rr:subjectMap [ rr:column "SYSTEM_OA" ; rr:datatype xsd:string ]
.

## OV-6c Process 
:OV6C_Map
	a rr:TriplesMapClass ;
	rr:SQLQuery """SELECT DISTINCT s.APPLIC_ID AS APPLIC_ID, r.SYSTEM_PROCESS AS SYSTEM_PROCESS
					FROM SYS_OV6C s, REF_OV6C_PROCESS r 
					WHERE s.OV_ID = r.OV_ID 
					AND r.BEA_VERSION > '6.9'""" ;
	rr:subjectMap [ rr:column "SYSTEM_PROCESS" ; rr:datatype xsd:string ]
.