SELECT

  -- Client Data.
  CS.ClientID,
  CS.ClientStatus,
  CS.FinNumber,
  CS.GroupName,
  X.Batches AS Batch,
  CS.DateJoined,
  CS.SuspensionDate,
  SM.ClientRetainer,
  SM.ProductCategory AS ClientProductCategories,
  SM.NoOfEmployees,
  SM.BranchCount,
  SM.OverdueBalance,
  SM.AccountBalance,

  -- Termination Data.
  CS.NoticeReceivedDate,
  CS.NoticeDocumentID,
  CS.TerminationDate AS ClientTerminationDate,
  CS.TerminationDate,
  CS.TerminationReason,
  CS.TerminationNotes,
  CS.InitialAuditNotes,
  CS.TwelveMonthReviewNotes,
  CS.WorkDoneNotes,
  IF(CS.CompetitorName IS NULL, 0, IF(TRIM(CS.CompetitorName) <> '', 1, 0)) AS GoneToCompetitor,
  CS.CompetitorName,

  -- Client Product Data.
  X.ProductCount,
  X.Products,
  X.ClientProducts,

  -- Client products that have not had cancellation initiated.
  Y.UnInitProductCount,
  Y.UnInitProducts,
  Y.UnInitClientProducts,

  -- Allocated Consultants
  ( SELECT GROUP_CONCAT(DISTINCT BP.ConsultantID)
    FROM
      taClientBranchProducts AS BP
        LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    WHERE
      (BP.Status = 1) AND
      (CP.ClientID = CS.ClientID)
  ) AS AllocatedConsultantID,

  -- Branch Administrators
  ( SELECT GROUP_CONCAT(DISTINCT BS.ConsultantID)
    FROM taBranchStructure AS BS
    WHERE
      (BS.Description LIKE '%Branch Administrator%') AND
      (BS.BranchID = HO.LabourNetBranchID)
  ) AS BranchAdministratorID,

  -- Regional Product Managers
  ( SELECT GROUP_CONCAT(DISTINCT BS.ConsultantID)
    FROM
      taClientProducts AS CP
        LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID),
      taBranchStructure AS BS
    WHERE
      (CP.Status = 1) AND
      (BS.Description LIKE CONCAT('%Regional ', PR.ProductCategory, ' Manager%')) AND
      (BS.BranchID = HO.LabourNetBranchID)
  ) AS RegionalProductManagerID,

  -- Branch Manager
  BR.BranchManagerID,

  -- System Administrators
  (SELECT GROUP_CONCAT(UserID) FROM taUserRoleMember WHERE RoleName = 'cmsSystemAdmin') AS SystemAdministratorID
FROM
  taClients AS CS
    LEFT JOIN taClientSummary AS SM ON (SM.ClientID = CS.ClientID)
    LEFT JOIN taClientBranches AS HO ON ((HO.ClientID = CS.ClientID) AND (HO.IsHeadOffice = 1) AND (HO.BranchStatus = CS.ClientStatus))
    LEFT JOIN taBranches AS BR ON (BR.BranchID = HO.LabourNetBranchID)
    LEFT JOIN (
      SELECT
        M.ClientID,
        COUNT(*) AS ProductCount,
        GROUP_CONCAT(DISTINCT M.Batch SEPARATOR ',') AS Batches,
        GROUP_CONCAT(DISTINCT M.ProductID SEPARATOR ',') AS Products,
        GROUP_CONCAT(DISTINCT M.ClientProductID SEPARATOR ',') AS ClientProducts
      FROM
        taClientProducts AS M
          LEFT JOIN taClients AS N ON (N.ClientID = M.ClientID)
      WHERE
        (M.Status = N.ClientStatus)
      GROUP BY
        ClientID
    ) AS X ON (X.ClientID = CS.ClientID)
    LEFT JOIN (
      SELECT
        ClientID,
        COUNT(*) AS UnInitProductCount,
        GROUP_CONCAT(DISTINCT ProductID SEPARATOR ',') AS UnInitProducts,
        GROUP_CONCAT(DISTINCT ClientProductID SEPARATOR ',') AS UnInitClientProducts
      FROM
        taClientProducts
      WHERE
        (Status = 1) AND ((TerminationDate IS NULL) OR (TerminationDate = '0000-00-00'))
      GROUP BY
        ClientID
    ) AS Y ON (Y.ClientID = CS.ClientID)
WHERE
  (CS.ClientID = @nClientID)
