SELECT
  Z.LabourNetBranchName,
  Z.Consultant,
  Z.ClientGroup,
  Z.ClientID,
  Z.ClientName,
  Z.ClientBranchID,
  CONCAT(Z.ClientBranchName, ' (', Z.LabourNetBranchName, ')') AS ClientBranchName,
  Z.ProductCategory,
  fnClientRetainer(Z.ClientID, NOW()) AS TotalRetainer,
  fnClientBranchProductRetainer(Z.ClientBranchProductID, NOW()) * 1.0 AS AllocatedRetainer,
  Z.JoinDate,
  Z.LastAttendanceDate,
  IF(Z.IsHeadOffice = 1, 'Head Office', 'Branch') AS IsHeadOffice
FROM (

  SELECT
    Y.*
  FROM (

    SELECT
      X.*,
      (
        SELECT
          MAX(A.AttendanceDate) AS LastAttendanceDate
        FROM
          tbAppointments AS A
        WHERE
          (A.AppointmentStage = 'Authorised') AND
          (A.ClientBranchProductID = X.ClientBranchProductID) AND
          CASE @nAttendedByID
            WHEN 1 THEN (A.ConsultantID = X.ConsultantID)
            WHEN 2 THEN (A.ConsultantID = (SELECT BranchManagerID FROM taBranches WHERE (BranchID = fnUserBranch(X.ConsultantID, NOW()))))
            ELSE TRUE
          END AND
          CASE @nMustVisitSite
            WHEN 1 THEN ((A.TimeTravelled > 0) OR (A.DistanceTravelled > 0))
            ELSE TRUE
          END
      ) AS LastAttendanceDate
    FROM (

      SELECT
        SM.ClientGroup,
        CB.LabourNetBranchID,
        BR.BranchManagerID,
        BR.BranchName AS LabourNetBranchName,
        BP.ConsultantID,
        U1.FullName AS Consultant,
        CB.ClientID,
        CS.GroupName AS ClientName,
        BP.ClientBranchProductID,
        CB.ClientBranchID,
        BP.ClientProductID,
        BP.JoinDate,
        CB.BranchName AS ClientBranchName,
        PR.ProductCategory,
        CB.IsHeadOffice
      FROM
        taClientBranchProducts AS BP
          LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
          LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
          LEFT JOIN taClients        AS CS ON (CS.ClientID = CB.ClientID)
          LEFT JOIN taClientSummary  AS SM ON (SM.ClientID = CB.ClientID)
          LEFT JOIN taProducts       AS PR ON (PR.ProductID = CP.ProductID)
          LEFT JOIN taUsers          AS U1 ON (U1.UserID = BP.ConsultantID)
          LEFT JOIN taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
      WHERE
        (BP.ConsultantID IS NOT NULL) AND (BP.ConsultantID > 0) AND
        (CS.ClientID IS NOT NULL) AND (CS.ClientID > 0) AND
        (PR.ProductID IS NOT NULL) AND (PR.ProductID > 0) AND
        (BR.BranchID IS NOT NULL) AND (BR.BranchID > 0) AND
        (CS.ClientStatus = 1) AND
        (CP.Status = 1) AND
        (BP.Status = 1) AND
        (SM.ClientGroup IN ('A','B','C','D')) AND
        ((@nBranchID = 0) OR (BR.BranchID = @nBranchID)) AND
        ((@aClientGroup = '') OR (SM.ClientGroup = @aClientGroup))
        @pConsultantID
        @pProductID
        @pClientID
      ORDER BY
        BR.BranchName,
        U1.FullName,
        SM.ClientGroup,
        CB.ClientID

    ) AS X

  ) AS Y
  WHERE
    1
    @pShowAllClients

) AS Z
ORDER BY
  Z.LabourNetBranchName,
  Z.Consultant,
  Z.ClientGroup,
  Z.ClientName
