SELECT
  X.ClientRelationshipManager,
  CS.ClientID,
  CS.FinNumber,
  CS.GroupName,
  Y.ProductCategory,
  Y.ProductName,
  N.LastAttendanceDate,
  S1.OptionDescription AS Industry,
  IF((CS.EmployersOrganisation IS NULL) OR (TRIM(CS.EmployersOrganisation) = ''), "No", CS.EmployersOrganisation) AS EmployersOrganisation,
  SM.National,
  M.BranchCount,
  Y.ProductCount,
  SM.NoOfEmployees,
  CS.EstTurnOver,
  SM.OverdueBalance,
  SM.ClientRetainer
FROM
  taClients AS CS
    LEFT JOIN taClientSummary AS SM ON (SM.ClientID = CS.ClientID)
    LEFT JOIN taSystemOption  AS S1 ON ((S1.OptionType = 'INDUSTRY') AND (S1.OptionID = CS.IndustryID))
    LEFT JOIN (

      SELECT
        Z.ClientID,
        Z.ProductCount,
        Z.ProductCategory AS ProductCategory,
        Z.ProductName AS ProductName
      FROM (
        SELECT
          COUNT(*) AS ProductCount,
          CP.ClientID,
          GROUP_CONCAT(DISTINCT PR.ProductCategory SEPARATOR ' + ') AS ProductCategory,
          GROUP_CONCAT(PR.ProductName SEPARATOR ' + ') AS ProductName
        FROM
          taClientProducts AS CP
            INNER JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
            INNER JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
        WHERE
          (CP.Status = 1) AND
          (COALESCE(PC.MustHaveParent, 0) = 0) AND
          (PR.ProductCategory <> 'CORPORATE ACC MAN')
        GROUP BY CP.ClientID
      ) AS Z

    ) AS Y ON (Y.ClientID = CS.ClientID)

    LEFT JOIN (

      SELECT
        Z.ClientID,
        COUNT(*) AS BranchCount
      FROM (
        SELECT N.ClientID, N.ClientBranchID FROM (
          SELECT
            CB.ClientID,
            CB.ClientBranchID,
            fnClientBranchRetainer(CB.ClientBranchID, NOW()) AS BranchRetainer
          FROM
            taClientBranches AS CB
              INNER JOIN (
                SELECT CP.ClientID FROM taClientProducts AS CP WHERE (CP.ProductID = 175) AND (CP.Status = 1)
              ) AS B ON (B.ClientID = CB.ClientID)
          WHERE
            (CB.BranchStatus = 1)
        ) AS N WHERE (N.BranchRetainer > 0.0)
      ) AS Z
      GROUP BY
        Z.ClientID

    ) AS M ON (M.ClientID = CS.ClientID)

    LEFT JOIN (

      SELECT
        AP.ClientID,
        MAX(AP.AttendanceDate) AS LastAttendanceDate
      FROM
        tbAppointments AS AP
          LEFT JOIN taProducts AS PR ON (PR.ProductID = AP.ProductID)
      WHERE
        (AP.AppointmentStage = 'Authorised') AND
        (PR.ProductCategory = 'CORPORATE ACC MAN')
      GROUP BY
        AP.ClientID

    ) AS N ON (N.ClientID = CS.ClientID)

    INNER JOIN (

      SELECT DISTINCT
        CP.ClientID,
        CB.LabourNetBranchID,
        BR.BranchName AS LabourNetBranch,
        U1.FullName AS ClientRelationshipManager
      FROM
        taClientBranchProducts AS BP
          INNER JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
          INNER JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
          INNER JOIN taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
          INNER JOIN taClients        AS BE ON (BE.ClientID = CP.ClientID)
          LEFT JOIN taUsers          AS U1 ON (U1.UserID = BP.ConsultantID)
      WHERE
        (CP.ProductID = 175) AND
        (BE.ClientStatus = 1)
        @pBranchID
        @pConsultantID

    ) AS X ON (X.ClientID = CS.ClientID)
  WHERE
    (SM.ClientRetainer > 0.0)
ORDER BY
  X.ClientRelationshipManager,
  CS.GroupName
