USE [Trains]
GO
/****** Object:  StoredProcedure [dbo].[spGetFaresFrom]    Script Date: 18/12/2013 00:13:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER proc
[dbo].[spGetFaresFrom]
    @OriginCRS CHAR(3) = NULL,
    @OriginNLC CHAR(4) = NULL,
    @TicketCode CHAR(3) = NULL,
    @Seasons bit = 0,
    @IncludeAdvances bit = 0,
    @RouteCode INT = NULL,
    @TicketClass INT = NULL,
    @TOCId CHAR(3) = NULL,
    @Reversable bit = 0,
    @CrossLondon bit = NULL
AS
IF @OriginNLC IS NULL
BEGIN
    IF @OriginCRS IS NULL
    BEGIN
        raiserror('You must specify either @OriginNLC or @OriginCRS',11,1)
        RETURN
    END
    ELSE
    BEGIN
        /* Get the code for the specified station */
        SELECT
            @OriginNlc = NLCCode
        FROM
            Locations
        WHERE
            CRSCODE = @OriginCRS
    END
END

/* Where a clustered flow exists between two destinations, and a specific one, the clustered flow is completely suppressed */

DECLARE @OriginNLCs TABLE
(
    NLC CHAR(4)
)


DECLARE @DestinationNLCs TABLE
(
    NLC CHAR(4)
)

INSERT
    @OriginNLCs
SELECT
    @OriginNlc

INSERT
    @OriginNLCs
SELECT
    GroupLocationNLCCode
FROM
    LocationGroupsLocationMembers
WHERE
    MemberLocationNLCCode = @OriginNlc

CREATE TABLE #Flows
(
FlowId INT NOT NULL,
OriginNLCCode CHAR(4) NOT NULL,
DestinationNLCCode CHAR(4) NOT NULL,
RouteCode INT,
Cluster bit,
Overridden bit,
TOC CHAR(3) NULL,
CrossLondon tinyint,
Reversable bit)


INSERT INTO
    #Flows
SELECT
    Flows.FlowId,
    OriginFlowCodes.NLC,
    isnull(Flows.DestinationLocation, Dest.LocationNLCCode) DestinationNLCCode,
     Flows.RouteCode,
     1 AS Cluster,
     0 Overriden,
     Flows.TicketTOCId,
     Flows.CrossLondon,
     Flows.Reversable
FROM
    @OriginNLCs AS OriginFlowCodes
    JOIN ClusterLocations OriginCluster ON OriginCluster.LocationNLCCode = OriginFlowCodes.NLC
    JOIN Flows ON Flows.OriginCluster = OriginCluster.ClusterId
    LEFT JOIN ClusterLocations Dest ON Dest.ClusterId = Flows.DestinationCluster
UNION
SELECT
    Flows.FlowId,
    CASE WHEN Flows.Reversable = 1 THEN DestFlowCodes.NLC ELSE isnull(Flows.OriginLocation, Origin.LocationNLCCode) END OriginNLCCode,
    CASE WHEN Flows.Reversable = 1 THEN isnull(Flows.OriginLocation, Origin.LocationNLCCode) ELSE DestFlowCodes.NLC END DestinationNLCCode,
     Flows.RouteCode,
     1 AS Cluster,
     0 Overriden,
     Flows.TicketTOCId,
     Flows.CrossLondon,
     Flows.Reversable
FROM
    @OriginNLCs AS DestFlowCodes
    JOIN ClusterLocations DestCluster ON DestCluster.LocationNLCCode = DestFlowCodes.NLC
    JOIN Flows ON Flows.DestinationCluster = DestCluster.ClusterId
    LEFT JOIN ClusterLocations Origin ON Origin.ClusterId = Flows.OriginCluster
WHERE
    (@Seasons = 1 OR Flows.Reversable = 1 OR @Reversable = 1)

INSERT INTO #Flows
SELECT
    Flows.FlowId,
    OriginFlowCodes.NLC,
    isnull(Flows.DestinationLocation, Dest.LocationNLCCode) DestinationNLCCode,
     Flows.RouteCode,
     0 AS Cluster,
     0 Overriden,
     Flows.TicketTOCId,
     Flows.CrossLondon,
     Flows.Reversable
FROM
    @OriginNLCs AS OriginFlowCodes
    JOIN Flows ON Flows.OriginLocation = OriginFlowCodes.NLC
    LEFT JOIN ClusterLocations Dest ON Dest.ClusterId = Flows.DestinationCluster
UNION
SELECT
    Flows.FlowId,
    CASE WHEN Flows.Reversable = 1 THEN DestFlowCodes.NLC ELSE isnull(Flows.OriginLocation, Origin.LocationNLCCode) END OriginNLCCode,
    CASE WHEN Flows.Reversable = 1 THEN isnull(Flows.OriginLocation, Origin.LocationNLCCode) ELSE DestFlowCodes.NLC END DestinationNLCCode,
     Flows.RouteCode,
     0 AS Cluster,
     0 Overriden,
     Flows.TicketTOCId,
     Flows.CrossLondon,
     Flows.Reversable
FROM
    @OriginNLCs AS DestFlowCodes
    JOIN Flows ON Flows.DestinationLocation = DestFlowCodes.NLC
    LEFT JOIN ClusterLocations Origin ON Origin.ClusterId = Flows.OriginCluster
WHERE
    (@Seasons = 1 OR Flows.Reversable = 1 OR @Reversable = 1)


UPDATE
    F1
SET
    Overridden = 1
FROM
    #Flows F1 JOIN
    #Flows F2 ON F2.OriginNLCCode = F1.OriginNLCCode AND f2.DestinationNLCCode = f1.DestinationNLCCode AND f1.RouteCode = f2.RouteCode
WHERE
    f1.Cluster = 1 AND f2.Cluster = 0

CREATE TABLE #Fares
(
OriginNLCCode CHAR(4) NOT NULL,
DestinationNLCCode CHAR(4) NOT NULL,
Fare INT NULL,
RouteCode INT,
TicketCode CHAR(3),
RestrictionCode CHAR(2),
Override bit,
Overridden bit,
TOC CHAR(3) NULL,
CrossLondon tinyint,
Reversable bit
)

INSERT INTO #Fares
SELECT
OriginNLCCode,
DestinationNLCCode,
Fare,
RouteCode,
FlowFares.TicketType,
FlowFares.RestrictionCode,
0,
Flows.Overridden,
Flows.TOC,
Flows.CrossLondon,
Flows.Reversable
FROM #Flows Flows
JOIN FlowFares ON FlowFares.FlowId = Flows.FlowId

INSERT INTO
#Fares
SELECT Origin.NLC, NonDerivableFares.DestinationNLCCode, AdultFare, RouteCode, TicketCode, NonDerivableFares.RestrictionCode,
1, 0, NULL, NonDerivableFares.CrossLondon, 0
FROM
    NonDerivableFares
    JOIN @OriginNLCs Origin ON Origin.NLC = NonDerivableFares.OriginNLCCode
WHERE
    NonDerivableFares.RailcardCode =''

UPDATE
F1
    SET
    Overridden = 1
FROM
#Fares f1
JOIN #Fares f2 ON
    f1.OriginNLCCode = f2.OriginNLCCode AND
    f1.DestinationNLCCode = f2.DestinationNLCCode AND
    f1.TicketCode = f2.TicketCode AND
    f1.RouteCode = f2.RouteCode
WHERE
    f2.Override = 1 AND f1.override = 0

SELECT
    Origin.Description Origin,
    Fares.OriginNLCCode,  Destination.Description Destination,
    Fares.DestinationNLCCode,
    TicketTypes.Description Ticket, TicketCode,  '£'+CAST(CAST(Fares.fare/100.0 AS DECIMAL(8,2)) AS VARCHAR(10)) Fare,
    Routes.Description Route,
    Routes.Code RouteCode,
    Fares.RestrictionCode,
    TOCs.name TOC,
    TOCs.ID TOCid,
    TicketValidity.BreakOfJourneyOutbound,
    TicketValidity.BreakOfJourneyReturn,
    CASE CrossLondon WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' WHEN 2 THEN 'Not Underground' END AS CrossLondon,
    Reversable
FROM
    #Fares Fares
    JOIN Routes ON Routes.Code = Fares.RouteCode
    JOIN TicketTypes ON TicketTypes.code = Fares.TicketCode
    JOIN TicketValidity ON TicketTypes.ValidityCode = TicketValidity.Code
    JOIN Locations Origin ON Origin.NLCCode = fares.OriginNLCCode
    JOIN Locations Destination ON Destination.NLCCode = Fares.DestinationNLCCode
    LEFT JOIN @DestinationNLCs DestNLCs ON DestNLCs.NLC = Fares.DestinationNLCCode
    LEFT JOIN ticketTOCs tocs ON TOCS.Id = Fares.TOC
WHERE
    Fares.fare % 10 = 0 AND fare != 999900 AND Overridden = 0 AND
    ((@Seasons = 0 AND TicketTypes.TicketType != 'N') OR (@Seasons = 1 AND TicketTypes.TicketType = 'N')) AND
    (@IncludeAdvances = 1 OR TicketTypes.RestrictedByTrain = 0) AND
    (@TicketCode IS NULL OR TicketTypes.Code = @TicketCode) AND
    (@TicketClass IS NULL OR TicketTypes.TicketClass = @TicketClass) AND
    (@RouteCode IS NULL OR Routes.Code = @RouteCode) AND
    (@Tocid IS NULL OR Fares.TOC = @Tocid) AND
    (@CrossLondon IS NULL OR CrossLondon = @CrossLondon)
ORDER BY Fares.Fare