SELECT

  -- Client Data.
  CS.ClientID,
  CS.ClientStatus,
  CS.FinNumber,
  CS.GroupName,
  CP.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.TerminationReason AS ConsultantTerminationReason,
  CS.TerminationNotes AS ClientTerminationNotes,
  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.
  CP.ClientProductID,
  CP.CancelApproveDateManager,
  CP.Status,
  CP.Status AS ClientProductStatus,
  CP.JoinDate,
  COALESCE(CP.TerminationDate, CS.TerminationDate) AS TerminationDate,
  COALESCE(CP.TerminationReason, CS.TerminationReason) AS TerminationReason,
  COALESCE(CP.TerminationNotes, CS.TerminationNotes) AS TerminationNotes,
  CP.ClientTerminationReason,
  COALESCE(CP.InitialAuditNotes, CS.InitialAuditNotes) AS InitialAuditNotes,
  COALESCE(CP.TwelveMonthReviewNotes, CS.TwelveMonthReviewNotes) AS TwelveMonthReviewNotes,
  COALESCE(CP.WorkDoneNotes, CS.WorkDoneNotes) AS WorkDoneNotes,
  COALESCE(CP.CompetitorName, CS.CompetitorName) AS CompetitorName,
  CP.CancelCommentsManager,
  CP.RemedialActionRequired,
  COALESCE(CP.RemedialActionTaken, 0) AS RemedialActionTaken,
  COALESCE(CP.AuditReviewed, 0) AS AuditReviewed,
  COALESCE(CP.ClientSurveyCompleted, 0) AS ClientSurveyCompleted,
  COALESCE(CP.AcceptPrematureCancellation, 0) AS AcceptPrematureCancellation,
  COALESCE(CP.HandOverRequired, 0) AS HandOverRequired,

  -- Product Data.
  PR.ProductCategory,
  PR.ProductID,
  PR.ProductName,
  PR.ProductID AS Products,
  CP.ClientProductID AS ClientProducts,

  -- Child Client Product Data.
  Y.ChildClientProductCount,
  Y.ChildClientProducts,
  Y.ChildClientProductStage1Count,
  Y.ChildClientProductStage2Count,

  -- Client Branch Product Data.
  X.ClientBranchProductCount,
  X.ClientBranchProducts,

  -- Allocated Consultants
  ( SELECT GROUP_CONCAT(DISTINCT BP.ConsultantID)
    FROM taClientBranchProducts AS BP
    WHERE
      (BP.Status = 1) AND
      (BP.ClientProductID = CP.ClientProductID)
  ) 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 taBranchStructure AS BS
    WHERE
      (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,

  -- Product Executive Data.
  CP.CancelApproveDateExecutive,
  COALESCE(CP.AllFieldsFilledIn, 0) AS AllFieldsFilledIn,
  COALESCE(CP.LetterFromClientChecked, 0) AS LetterFromClientChecked,
  COALESCE(CP.ReportsReviewed, 0) AS ReportsReviewed
FROM
  taClientProducts AS CP
    LEFT JOIN taClients         AS CS ON (CS.ClientID  = CP.ClientID)
    LEFT JOIN taClientSummary   AS SM ON (SM.ClientID  = CP.ClientID)
    LEFT JOIN taProducts        AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
    LEFT JOIN taClientBranches  AS HO ON ((HO.ClientID = CS.ClientID) AND (HO.IsHeadOffice = 1) AND (HO.BranchStatus = 1))
    LEFT JOIN taBranches        AS BR ON (BR.BranchID = HO.LabourNetBranchID)
    LEFT JOIN (
      SELECT
        M.ClientProductID,
        COUNT(*) AS ClientBranchProductCount,
        GROUP_CONCAT(DISTINCT M.ClientBranchProductID SEPARATOR ',') AS ClientBranchProducts
      FROM
        taClientBranchProducts AS M
          LEFT JOIN taClientProducts AS N ON (N.ClientProductID = M.ClientProductID)
      WHERE
        (M.Status = N.Status)
      GROUP BY
        ClientProductID
    ) AS X ON (X.ClientProductID = CP.ClientProductID)
    LEFT JOIN (
      SELECT
        ParentClientProductID AS ClientProductID,
        COUNT(*) AS ChildClientProductCount,
        GROUP_CONCAT(DISTINCT ClientProductID SEPARATOR ',') AS ChildClientProducts,
        SUM(IF((CancelApproveDateManager IS NOT NULL) AND (CancelApproveDateManager <> '0000-00-00'), 1, 0)) AS ChildClientProductStage1Count,
        SUM(IF((CancelApproveDateExecutive IS NOT NULL) AND (CancelApproveDateExecutive <> '0000-00-00'), 1, 0)) AS ChildClientProductStage2Count
      FROM
        taClientProducts
      WHERE
        (Status = 1)
      GROUP BY
        ParentClientProductID
    ) AS Y ON (Y.ClientProductID = CP.ClientProductID)
WHERE
  (CP.ClientProductID = @nClientProductID)
