search

HSLdevcom / transitdata-cache-bootstrapper

Fill Redis with PubTrans ptDOI data
European Union Public License 1.2
0 stars 0 forks source link

feat/metro #11

Closed kenkus-futurice closed 5 years ago

kenkus-futurice commented 5 years ago

Changed queries:

SELECT
    CONVERT(CHAR(16), DVJ.Id) AS dvj_id, 
    KVV.StringValue AS [route], 
    SUBSTRING(CONVERT(CHAR(16), VJT.IsWorkedOnDirectionOfLineGid), 12, 1) AS direction, 
    CONVERT(CHAR(8), DVJ.OperatingDayDate, 112) AS operating_day, 
    RIGHT('0' + (CONVERT(VARCHAR(2), (DATEDIFF(HOUR, '1900-01-01', VJ.PlannedStartOffsetDateTime)))), 2) 
        + ':' + RIGHT('0' + CONVERT(VARCHAR(2), (DATEDIFF(MINUTE, '1900-01-01', VJ.PlannedStartOffsetDateTime)
        - (DATEDIFF(HOUR, '1900-01-01', VJ.PlannedStartOffsetDateTime) * 60))), 2) + ':00' AS start_time
FROM ptDOI4_Community.dbo.DatedVehicleJourney AS DVJ
LEFT JOIN ptDOI4_Community.dbo.VehicleJourney AS VJ ON (DVJ.IsBasedOnVehicleJourneyId = VJ.Id) 
LEFT JOIN ptDOI4_Community.dbo.VehicleJourneyTemplate AS VJT ON (DVJ.IsBasedOnVehicleJourneyTemplateId = VJT.Id)
LEFT JOIN ptDOI4_Community.T.KeyVariantValue AS KVV ON (KVV.IsForObjectId = VJ.Id)
LEFT JOIN ptDOI4_Community.dbo.KeyVariantType AS KVT ON (KVT.Id = KVV.IsOfKeyVariantTypeId)
LEFT JOIN ptDOI4_Community.dbo.KeyType AS KT ON (KT.Id = KVT.IsForKeyTypeId)
LEFT JOIN ptDOI4_Community.dbo.ObjectType AS OT ON (KT.ExtendsObjectTypeNumber = OT.Number)
WHERE
    (
        KT.Name = 'JoreIdentity'
        OR KT.Name = 'JoreRouteIdentity'
        OR KT.Name = 'RouteName'
    )
    AND OT.Name = 'VehicleJourney'
    AND VJT.IsWorkedOnDirectionOfLineGid IS NOT NULL
    AND DVJ.OperatingDayDate >= '2019-04-03'
    AND DVJ.OperatingDayDate < '2019-04-04'
    AND DVJ.IsReplacedById IS NULL
SELECT
    DISTINCT CONVERT(CHAR(16), DVJ.Id) AS dvj_id, 
    KVV.StringValue AS [route], 
    SUBSTRING(CONVERT(CHAR(16), VJT.IsWorkedOnDirectionOfLineGid), 12, 1) AS direction, 
    CONVERT(CHAR(8), DVJ.OperatingDayDate, 112) AS operating_day, 
    RIGHT('0' + (CONVERT(VARCHAR(2), (DATEDIFF(HOUR, '1900-01-01', VJ.PlannedStartOffsetDateTime)))), 2) 
        + ':' + RIGHT('0' + CONVERT(VARCHAR(2), (DATEDIFF(MINUTE, '1900-01-01', VJ.PlannedStartOffsetDateTime) -
        (DATEDIFF(HOUR, '1900-01-01', VJ.PlannedStartOffsetDateTime) * 60))), 2) + ':00' AS start_time,
    CONVERT(CHAR(7), JPP.Number) AS stop_number
FROM ptDOI4_Community.dbo.DatedVehicleJourney AS DVJ
LEFT JOIN ptDOI4_Community.dbo.VehicleJourney AS VJ ON (DVJ.IsBasedOnVehicleJourneyId = VJ.Id) 
LEFT JOIN ptDOI4_Community.dbo.VehicleJourneyTemplate AS VJT ON (DVJ.IsBasedOnVehicleJourneyTemplateId = VJT.Id)
LEFT JOIN ptDOI4_Community.T.KeyVariantValue AS KVV ON (KVV.IsForObjectId = VJ.Id)
LEFT JOIN ptDOI4_Community.dbo.KeyVariantType AS KVT ON (KVT.Id = KVV.IsOfKeyVariantTypeId)
LEFT JOIN ptDOI4_Community.dbo.KeyType AS KT ON (KT.Id = KVT.IsForKeyTypeId)
LEFT JOIN ptDOI4_Community.dbo.ObjectType AS OT ON (KT.ExtendsObjectTypeNumber = OT.Number)
LEFT JOIN ptDOI4_Community.dbo.JourneyPatternPoint AS JPP ON (VJT.StartsAtJourneyPatternPointGid = JPP.Gid)
WHERE
    (
        KT.Name = 'JoreIdentity'
        OR KT.Name = 'JoreRouteIdentity'
        OR KT.Name = 'RouteName'
    )
    AND OT.Name = 'VehicleJourney'
    AND VJT.IsWorkedOnDirectionOfLineGid IS NOT NULL
    AND DVJ.OperatingDayDate >= '2019-04-03'
    AND DVJ.OperatingDayDate < '2019-04-04'
    AND DVJ.IsReplacedById IS NULL
    AND VJT.TransportModeCode = 'METRO'