USE [Trains]
GO
/****** Object:  StoredProcedure [dbo].[spGetFaresFrom]    Script Date: 25/03/2013 00:20:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc
[dbo].[spGetFaresFrom]
    @OriginStation CHAR(3),
    @TicketCode CHAR(3) = NULL,
    @Seasons bit = 0,
    @IncludeAdvances bit = 0,
    @RouteCode INT = NULL,
    @TicketClass INT = NULL,
    @TOCId CHAR(3) = NULL
AS
DECLARE @OriginNlc CHAR(4)
/* Get the code for the specified station */
SELECT
    @OriginNlc = NLCCode
FROM
    Locations
WHERE
    CRSCODE = @OriginStation

/* 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)

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
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,
    DestFlowCodes.NLC OriginNLCCode,
    isnull(Flows.OriginLocation, Origin.LocationNLCCode) DestinationNLCCode,
     Flows.RouteCode,
     1 AS Cluster,
     0 Overriden,
     Flows.TicketTOCId,
     Flows.CrossLondon
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)

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
FROM
    @OriginNLCs AS OriginFlowCodes
    JOIN Flows ON Flows.OriginLocation = OriginFlowCodes.NLC
    LEFT JOIN ClusterLocations Dest ON Dest.ClusterId = Flows.DestinationCluster
UNION
SELECT
    Flows.FlowId,
    Flows.DestinationLocation OriginNLCCode,
    isnull(Flows.OriginLocation, Origin.LocationNLCCode) DestinationNLCCode,
     Flows.RouteCode,
     0 AS Cluster,
     0 Overriden,
     Flows.TicketTOCId,
     Flows.CrossLondon
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)


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
)

INSERT INTO #Fares
SELECT
OriginNLCCode,
DestinationNLCCode,
Fare,
RouteCode,
FlowFares.TicketType,
FlowFares.RestrictionCode,
0,
Flows.Overridden,
Flows.TOC,
Flows.CrossLondon
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
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
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)
ORDER BY Fares.Fare