Eric-ontology/queries/customer-transaction-records.rq

PREFIX gesli: <https://gesli.example/ontology#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?transactionNo ?inquiryNo ?dealStatus ?isWon ?customerName ?counterpartyName
       ?orderNo ?routeName ?brandName ?productName ?supplierName ?offeringChannel
       ?transportMode ?transportSubMode ?serviceForm ?routeType ?supplierCostAmount
       ?aiRecommendedAmount ?customerPremiumAmount ?salespersonAdjustmentAmount ?finalQuoteAmount
       ?quotedAmount ?marketAmount ?dealAmount ?currency ?lossReason
       ?pickupLocation ?deliveryLocation ?cargoName ?dangerousGoodsClass
       ?cargoVolume ?portToPortTransitDays ?doorToDoorTransitDays
       ?grossMarginRate ?grossMarginAmount
WHERE {
  ?record a gesli:CustomerTransactionRecord ;
          gesli:transactionNo ?transactionNo ;
          gesli:transactionCustomer ?customer ;
          gesli:transactionCounterparty ?counterparty ;
          gesli:routeName ?routeName .

  ?customer rdfs:label ?customerName .
  ?counterparty rdfs:label ?counterpartyName .

  OPTIONAL { ?record gesli:inquiryNo ?inquiryNo . }
  OPTIONAL { ?record gesli:dealStatus ?dealStatus . }
  OPTIONAL { ?record gesli:isWon ?isWon . }
  OPTIONAL { ?record gesli:orderNo ?orderNo . }
  OPTIONAL { ?record gesli:quotedAmount ?quotedAmount . }
  OPTIONAL { ?record gesli:marketAmount ?marketAmount . }
  OPTIONAL { ?record gesli:dealAmount ?dealAmount . }
  OPTIONAL { ?record gesli:currency ?currency . }
  OPTIONAL { ?record gesli:lossReason ?lossReason . }
  OPTIONAL {
    ?record gesli:selectedServiceProductRef ?product .
    ?product gesli:productName ?productName ;
             gesli:transportMode ?transportMode ;
             gesli:transportSubMode ?transportSubMode ;
             gesli:serviceForm ?serviceForm ;
             gesli:routeType ?routeType .
    OPTIONAL {
      ?product gesli:hasServiceBrand ?brand .
      ?brand gesli:brandName ?brandName .
    }
  }
  OPTIONAL {
    ?record gesli:selectedSupplierOffering ?offering .
    ?offering gesli:offeredBySupplier ?supplier .
    ?supplier rdfs:label ?supplierName .
    OPTIONAL { ?offering gesli:offeringChannel ?offeringChannel . }
  }
  OPTIONAL {
    ?record gesli:usesSupplierQuote ?supplierQuote .
    ?supplierQuote gesli:supplierCostAmount ?supplierCostAmount .
  }
  OPTIONAL {
    ?record gesli:hasCustomerQuote ?customerQuote .
    ?customerQuote gesli:usesRecommendedPrice ?recommendedPrice ;
                   gesli:finalQuoteAmount ?finalQuoteAmount .
    ?recommendedPrice gesli:aiRecommendedAmount ?aiRecommendedAmount .
    OPTIONAL { ?customerQuote gesli:salespersonAdjustmentAmount ?salespersonAdjustmentAmount . }
    OPTIONAL {
      ?customerQuote gesli:usesPremiumDecision ?premiumDecision .
      ?premiumDecision gesli:customerPremiumAmount ?customerPremiumAmount .
    }
  }
  OPTIONAL { ?record gesli:pickupLocation ?pickupLocation . }
  OPTIONAL { ?record gesli:deliveryLocation ?deliveryLocation . }
  OPTIONAL { ?record gesli:cargoName ?cargoName . }
  OPTIONAL { ?record gesli:dangerousGoodsClass ?dangerousGoodsClass . }
  OPTIONAL { ?record gesli:cargoVolume ?cargoVolume . }
  OPTIONAL { ?record gesli:portToPortTransitDays ?portToPortTransitDays . }
  OPTIONAL { ?record gesli:doorToDoorTransitDays ?doorToDoorTransitDays . }
  OPTIONAL { ?record gesli:grossMarginRate ?grossMarginRate . }
  OPTIONAL { ?record gesli:grossMarginAmount ?grossMarginAmount . }
}
ORDER BY ?transactionNo