mapping.sparql

PREFIX adms:    <http://www.w3.org/ns/adms#>
PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX gr:      <http://purl.org/goodrelations/v1#>
PREFIX org:     <http://www.w3.org/ns/org#>
PREFIX ov:      <http://open.vocab.org/terms/>
PREFIX payment: <http://reference.data.gov.uk/def/payment#>
PREFIX pc:      <http://purl.org/procurement/public-contracts#>
PREFIX rdf:     <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX schema:  <http://schema.org/>
PREFIX skos:    <http://www.w3.org/2004/02/skos/core#>
PREFIX xsd:     <http://www.w3.org/2001/XMLSchema#>

CONSTRUCT {
  ?payment a payment:Payment ;
    payment:reference ?unique_transaction_id ;
    payment:contract ?contract ;
    ov:versionnumber ?transactionnumber ;
    payment:payer ?contractingAuthority ;
    payment:payee ?awardedSupplier
    .
  ?contract a ?class ;
    pc:numberOfTenders ?numberOfTenders ;
    adms:identifier ?contractIdentifier, ?solicitationIdentifier ;
    dcterms:description ?description ;
    pc:awardedTender ?awardedTender ;
    pc:awardDate ?awardDate ;
    pc:estimatedEndDate ?estimatedEndDate ;
    pc:actualEndDate ?actualEndDate ;
    pc:estimatedPrice ?estimatedPrice ;
    pc:location ?contractLocation ;
    pc:item ?item ;
    pc:mainObject ?mainObject ;
    pc:contractingAuthority ?contractingAuthority ;
    ov:versionnumber ?modnumber ;
    dcterms:type ?contractActionType ;
    skos:changeNote ?changeNote
    .
  ?contractIdentifier a adms:Identifier ;
    skos:notation ?piid ;
    dcterms:type <http://linked.opendata.cz/resource/identifier-types/contract>
    .
  ?solicitationIdentifier a adms:Identifier ;
    skos:notation ?solicitationid ;
    dcterms:type <http://linked.opendata.cz/resource/identifier-types/solicitation>
    .
  ?contractLocation a schema:Place ;
    schema:address ?contractAddress
    .
  ?contractAddress a schema:PostalAddress ;
    schema:addressCountry ?contractCountry ;
    schema:addressRegion ?pop_state_code ;
    schema:addressLocality ?contractAddressLocality ;
    schema:postalCode ?placeofperformancezipcode
    .
  ?contractCountry a schema:Country ;
    schema:name ?contractCountryName
    .
  ?item a gr:Offering ;
    gr:includes ?includedProductOrService
    .
  ?includedProductOrService a gr:SomeItems ;
    gr:name ?includedProductOrServiceName
    .
  ?mainObject a skos:Concept ;
    skos:inScheme <http://purl.org/weso/pscs/naics/2012/resource/ds> ;
    skos:prefLabel ?naicsLabel
    .
  ?contractingAuthority a gr:BusinessEntity ;
    gr:legalName ?contractingAuthorityLegalName
    .
  ?awardedTender a pc:Tender ;
    pc:supplier ?awardedSupplier
    .
  ?awardedSupplier a gr:BusinessEntity ;
    gr:legalName ?vendorname ;
    skos:altLabel ?vendoralternatename ;
    adms:identifier ?awardedSupplierIdentifier ;
    schema:location ?awardedSupplierLocation ;
    schema:contactPoint ?awardedSupplierContact ;
    org:classification ?awardedSupplierClassification
    .
  ?awardedSupplierIdentifier a adms:Identifier ;
    skos:notation ?dunsnumber ;
    adms:schemaAgency <http://dbpedia.org/resource/Dun_&_Bradstreet>
    .
  ?awardedSupplierLocation a schema:Place ;
    schema:address ?awardedSupplierAddress
    .
  ?awardedSupplierAddress a schema:PostalAddress ;
    schema:addressCountry ?awardedSupplierCountry ;
    schema:addressRegion ?state ;
    schema:addressLocality ?awardedSupplierAddressCity ;
    schema:streetAddress ?streetaddress ;
    schema:postalCode ?zipcode
    .
  ?awardedSupplierContact a schema:ContactPoint ;
    schema:telephone ?phoneno ;
    schema:faxNumber ?faxno
    .
  ?awardedSupplierCountry a schema:Country ;
    schema:name ?awardedSupplierCountryName
    .
  ?awardedSupplierClassification a skos:Concept ;
    skos:prefLabel ?classification
    .
  ?estimatedPrice a gr:PriceSpecification ;
    gr:hasCurrency "USD" ;
    gr:hasCurrencyValue ?estimatedPriceValue
    .
}
WHERE {
  # Mapping contract types
  VALUES (?contractactiontype ?class) {
    ("DO Delivery Order" pc:FrameworkAgreement)
    #("PO Purchase Order" )
    ("DCA Definitive Contract" pc:Contract)
    #("BPA Call Blanket Purchase Agreement" )
  }
  BIND("http://linked.opendata.cz/resource/domain/usa-spending/" AS ?baseURI)

  # Mint URIs
  BIND(URI(CONCAT(?baseURI, "payment/", ?unique_transaction_id)) AS ?payment)
  BIND(URI(CONCAT(?baseURI, "contract/", ENCODE_FOR_URI(?piid))) AS ?contract)
  BIND(URI(CONCAT(?baseURI, "identifier/", ENCODE_FOR_URI(?piid))) AS ?contractIdentifier)
  BIND(URI(CONCAT(?baseURI, "tender/", SHA1(?vendorname))) AS ?awardedTender)
  BIND(URI(CONCAT(?baseURI, "business-entity/", ENCODE_FOR_URI(?dunsnumber))) AS ?awardedSupplier)
  BIND(URI(CONCAT(?baseURI, "identifier/", ENCODE_FOR_URI(?dunsnumber))) AS ?awardedSupplierIdentifier)
  BIND(SHA1(CONCAT(?streetaddress, ?city, ?state, ?zipcode, ?vendorcountrycode)) AS ?awardedSupplierSha)
  BIND(URI(CONCAT(
    ?baseURI,
    "place/",
    ?awardedSupplierSha
  )) AS ?awardedSupplierLocation)
  BIND(URI(CONCAT(
    ?baseURI,
    "postal-address/",
    ?awardedSupplierSha
  )) AS ?awardedSupplierAddress)
  BIND(URI(CONCAT(?baseURI, "country/", SHA1(?vendorcountrycode))) AS ?awardedSupplierCountry)
  BIND(URI(CONCAT(?baseURI, "price-specification/", ?unique_transaction_id)) AS ?estimatedPrice) 
  BIND(URI(CONCAT(?baseURI, "contact-point/", SHA1(CONCAT(?phoneno, ?faxno)))) AS ?awardedSupplierContact)
  BIND(STRLANG(?PlaceOfPerformanceCity, "en-US") AS ?contractAddressLocality)
  BIND(STRLANG(?city, "en-US") AS ?awardedSupplierAddressCity)
  BIND(STRLANG(?vendorcountrycode, "en-US") AS ?awardedSupplierCountryName)
  BIND(STRLANG(?productorservicecode, "en-US") AS ?includedProductOrServiceName)
  BIND(SHA1(CONCAT(
    IF(BOUND(?PlaceOfPerformanceCity),?PlaceOfPerformanceCity, ''),
    ?pop_state_code,
    ?placeofperformancecountrycode,
    ?placeofperformancezipcode
  )) AS ?contractLocationSha)
  BIND(URI(CONCAT(
    ?baseURI,
    "place/",
    ?contractLocationSha
  )) AS ?contractLocation)
  BIND(URI(CONCAT(
    ?baseURI,
    "postal-address/",
    ?contractLocationSha
  )) AS ?contractAddress) 
  BIND(URI(CONCAT(?baseURI, "country/", SHA1(?placeofperformancecountrycode))) AS ?contractCountry)
  BIND(URI(CONCAT(?baseURI, "offering/",   SHA1(?productorservicecode))) AS ?item)
  BIND(URI(CONCAT(?baseURI, "some-items/", SHA1(?productorservicecode))) AS ?includedProductOrService)
  BIND(URI(CONCAT(
    "http://purl.org/weso/pscs/naics/2012/resource/",
    IF(
      REGEX(?principalnaicscode, "(\\d+).*"),
      REPLACE(?principalnaicscode, "(\\d+).*", "$1"),
      SHA1(?principalnaicscode)
    )
  )) AS ?mainObject)
  BIND(URI(CONCAT(?baseURI, "business-entity/", SHA1(?contractingofficeagencyid))) AS ?contractingAuthority)
  BIND(REPLACE(?solicitationid, "^(\\s)?(\\S+)(\\s+$)?", "$2") AS ?solicitationIdentifierTemp)
  BIND(IF(
    (STRLEN(?solicitationIdentifierTemp) = 0) || (?solicitationIdentifierTemp = "NOT APPLICABLE"),
    BNODE(),
    URI(CONCAT(?baseURI, "identifier/", ENCODE_FOR_URI(?solicitationIdentifierTemp)))
  ) AS ?solicitationIdentifier)
  BIND(URI(CONCAT(?baseURI, "concept/", SHA1(?organizationaltype))) AS ?awardedSupplierClassification)
   
  # Process literals
  BIND(STRDT(?numberofoffersreceived, xsd:nonNegativeInteger) AS ?numberOfTenders)
  BIND(STRLANG(?descriptionofcontractrequirement, "en-US") AS ?description)
  BIND(STRLANG(?contractactiontype, "en-US") AS ?contractActionType)
  BIND(STRLANG(IF(
    REGEX(?placeofperformancecountrycode, "(\\w+):\\s*(\\w+)"),
    REPLACE(?placeofperformancecountrycode, "(\\w+):\\s*(\\w+)", "$2"),
    ?placeofperformancecountrycode
  ), "en-US") AS ?contractCountryName)
  BIND(STRDT(REPLACE(
    ?currentcompletiondate,
    "(\\d{2})/(\\d{2})/(\\d{4})",
    "$3-$1-$2"
  ), xsd:date) AS ?estimatedEndDate)
  BIND(STRDT(REPLACE(
    ?ultimatecompletiondate,
    "(\\d{2})/(\\d{2})/(\\d{4})",
    "$3-$1-$2"
  ), xsd:date) AS ?actualEndDateTemp)
  BIND(IF(?actualEndDateTemp < xsd:date(now()), ?actualEndDateTemp, BNODE()) AS ?actualEndDate)
  BIND(STRDT(REPLACE(
    ?signeddate,
    "(\\d{2})/(\\d{2})/(\\d{4})",
    "$3-$1-$2"
  ), xsd:date) AS ?awardDate)
  BIND(STRDT(?obligatedamount, xsd:decimal) AS ?estimatedPriceValue)
  BIND(STRLANG(IF(
    REGEX(?principalnaicscode, "(\\d+):\\s(\\w+)"),
    REPLACE(?principalnaicscode, "(\\d+):\\s(\\w+)", "$2"),
    ?principalnaicscode
  ), "en-US") AS ?naicsLabel)
  BIND(STRLANG(IF(
    REGEX(?contractingofficeagencyid, "(\\d+):\\s(\\w+)"),
    REPLACE(?contractingofficeagencyid, "(\\d+):\\s(\\w+.*?)(, (DEPARTMENT OF( THE)?)|)$", "$4 $2"),
    ?contractingofficeagencyid
  ), "en-US") AS ?contractingAuthorityLegalName)
  BIND(STRLANG(?organizationaltype, "en-US") AS ?classification)
  BIND(STRLANG(?reasonformodification, "en-US") AS ?changeNote) FILTER REGEX(?changeNote, "\\w")
}
OFFSET 1