SELECT
  BR.BranchName AS LabourNetBranchName,
  B3.BranchName AS OnBehalfOfBranchName,
  Y.ConsultantID,
  Y.Consultant,
  U1.EmployeeCode,
  B4.BranchName AS ConsultantBranchName,
  U1.JobTitle,
  TRIM(CONCAT(U1.VehicleMake, ' ', TRIM(CONCAT(U1.VehicleModel, ' ', U1.VehicleRegYear)))) AS Vehicle,
  U1.VehiclePurchaseDate,
  U1.VehiclePurchasePrice,
  U1.VehicleMake,
  U1.VehicleModel,
  U1.VehicleRegYear,
  U1.VehicleNumberPlate,
  Y.AppointmentType,
  Y.AttendanceDate,
  Y.AppointmentID,
  Y.ClientName,
  Y.ProductCategory,
  Y.TravelMode,
  Y.Trip,
  Y.DistanceTravelled,
  Y.TravelParkingCost AS TravelParkingCost,
  Y.TravelTollgatesCost AS TravelTollgatesCost,
  Y.ConsultantTravelRate,
  (Y.ConsultantTravelRate * Y.DistanceTravelled) AS DistanceAmount,
  (Y.ConsultantTravelRate * Y.DistanceTravelled + Y.TravelParkingCost + Y.TravelTollgatesCost) AS ClaimAmount
FROM (

  SELECT
    X.*,
    fnUserBranch(X.ConsultantID, NOW()) AS ConsultantBranchID,
    (
      SELECT M.TravelRate
      FROM taUsersTravelRates AS M
      WHERE (M.TravelRateID = (
        SELECT MAX(N.TravelRateID)
        FROM taUsersTravelRates AS N
        WHERE (N.UserID = X.ConsultantID) AND (N.DateChanged = (
          SELECT MAX(O.DateChanged)
          FROM taUsersTravelRates AS O
          WHERE (O.UserID = X.ConsultantID) AND (O.DateChanged <= X.AttendanceDate)
        ))
      ))
    ) AS ConsultantTravelRate
  FROM (

    SELECT
      'Client' AS AppointmentType,
      A.ConsultantBranchID AS LabourNetBranchID,
      CB.LabourNetBranchID AS OnBehalfOfBranchID,
      A.ConsultantID,
      U1.FullName AS Consultant,
      1 AS Trip,
      A.AttendanceDate,
      A.AppointmentID,
      CB.ClientID,
      CB.BranchName AS ClientName,
      COALESCE(PR.ProductCategory, 'ADHOC') AS ProductCategory,
      (
        CASE A.TravelMode
          WHEN 0 THEN 'Other'
          WHEN 1 THEN 'Own Car'
          WHEN 2 THEN 'Company Car'
          WHEN 3 THEN 'With Client'
          ELSE CONCAT('Unknown Travel Mode [', A.TravelMode, ']')
        END
      ) AS TravelMode,
      IF(A.TravelMode > 1, 0.0, A.TravelParkingCost) AS TravelParkingCost,
      IF(A.TravelMode > 1, 0.0, A.TravelTollgatesCost) AS TravelTollgatesCost,
      IF(A.TravelMode > 1, 0.0, A.DistanceTravelled) AS DistanceTravelled
    FROM
      tbAppointments AS A
        LEFT JOIN tbAppointmentTypes AS D ON (D.AttendanceTypeID = A.AttendanceTypeID)
        LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = A.ClientBranchID)
        LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = A.ClientBranchProductID)
        LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
        LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
        LEFT JOIN taUsers AS U1 ON (U1.UserID = A.ConsultantID)
    WHERE
      (A.AttendanceDate BETWEEN @aFromDate AND @aToDate) AND
      (A.AppointmentStage = 'Authorised') AND
      (D.IsService = 0)
      @pProductID
      @pBranchID
      @pConsultantID
      @pTravelMode
      @pIncludeClients
      @pClientID

    UNION

    SELECT
      'Prospect' AS AppointmentType,
      BM.BranchID AS LabourNetBranchID,
      B.LabourNetBranchID AS OnBehalfOfBranchID,
      A.ConsultantID,
      C.FullName AS Consultant,
      1 AS Trip,
      A.AttendanceDate,
      A.PresentationID AS AppointmentID,
      B.ProspectID AS ClientID,
      B.ProspectName AS ClientName,
      'All' AS ProductCategory,
      (
        CASE A.TravelMode
          WHEN 0 THEN 'Other'
          WHEN 1 THEN 'Own Car'
          WHEN 2 THEN 'Company Car'
          WHEN 3 THEN 'With Client'
          ELSE CONCAT('Unknown Travel Mode [', A.TravelMode, ']')
        END
      ) AS TravelMode,
      IF(A.TravelMode > 1, 0.0, A.TravelParkingCost) AS TravelParkingCost,
      IF(A.TravelMode > 1, 0.0, A.TravelTollgatesCost) AS TravelTollgatesCost,
      IF(A.TravelMode > 1, 0.0, A.DistanceTravelled) AS DistanceTravelled
    FROM
      taPresentation A
        LEFT JOIN taProspect AS B ON (B.ProspectID = A.ProspectID)
        LEFT JOIN taUsers AS C ON (C.UserID = A.ConsultantID)
        LEFT JOIN taProspectProduct AS D ON (D.ProspectID = A.ProspectID),
      taUserBranchMember AS BM
    WHERE
      (BM.UserID = A.ConsultantID) AND
      (BM.FromDate = (SELECT MAX(N.FromDate) FROM taUserBranchMember AS N WHERE (N.UserID = A.ConsultantID) AND (N.FromDate <= A.AttendanceDate))) AND
      (LEFT(AttendanceDate, 10) BETWEEN @aFromDate AND @aToDate) AND
      (A.PresentationStage = 'Closed')
      @pProspectBranchID
      @pSalesPersonID
      @pTravelMode
      @pIncludeProspects
      @pProspectID

  ) AS X

) AS Y
  LEFT JOIN taUsers AS U1 ON (U1.UserID = Y.ConsultantID)
  LEFT JOIN taBranches AS BR ON (BR.BranchID = Y.LabourNetBranchID)
  LEFT JOIN taBranches AS B3 ON (B3.BranchID = Y.OnBehalfOfBranchID)
  LEFT JOIN taBranches AS B4 ON (B4.BranchID = Y.ConsultantBranchID)
ORDER BY
  Y.AttendanceDate,
  Y.AppointmentID
