SELECT

  --
  -- Consultant Fields.
  --
  U1.FullName AS ClientConsultant,
  GROUP_CONCAT(DISTINCT BR.BranchRegion ORDER BY BR.BranchRegion SEPARATOR ' + ') AS Regions,

  --
  -- taClient Fields.
  --
  CS.ClientID AS ClientID,
  CS.FinNumber AS FinNumber,
  CS.GroupName AS GroupName,
  IF((CS.ClientStatus = 1), 'Active', 'Cancelled') AS ClientStatus,
  S1.OptionDescription AS Industry,
  S2.OptionDescription AS Seta,
  CS.EstTurnOver AS EstTurnOver,
  BP.JoinDate AS DateJoined,
  SM.ClientGroup,
  IF((CS.EmployersOrganisation IS NULL) OR (TRIM(CS.EmployersOrganisation) = ''), "No", CS.EmployersOrganisation) AS EmployersOrganisation,
  IF(CB.IsHeadOffice > 0, '<b>HO</b>', 'branch') AS HeadOffice,
  IF(fnClientLabourNetRegionCount(CS.ClientID) > 1, 'National', 'Local') AS National,
  (SELECT COUNT(*) FROM taClients AS CS1 WHERE CS1.ReferredByClientID = CS.ClientID) AS ReferralCount,
  CONCAT(CS2.GroupName, ' [', CS2.FinNumber, ']') AS ReferredBy,
  COUNT(0) AS BranchProductCount,
  SUM(CB.NoOfEmployees) AS EmployeeCount,
  X.ClientDocCount,
  X.SLADocCount,

  --
  -- Retainer.
  --
  ROUND(SUM(fnClientBranchProductRetainer(BP.ClientBranchProductID, IF((BP.Status = 1), NOW(), BP.TerminationDate))) * 1.0, 2) AS Retainer
  @pCancellationFields
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 taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
    LEFT JOIN taUsers          AS U1 ON (U1.UserID = BP.ConsultantID)
    LEFT JOIN taClients        AS CS ON (CS.ClientID = CB.ClientID)
    LEFT JOIN taClientSummary  AS SM ON (SM.ClientID = CB.ClientID)
    LEFT JOIN taClients        AS CS2 ON (CS2.ClientID = CS.ReferredByClientID)
    LEFT JOIN taSystemOption   AS S1 ON ((S1.OptionType = 'INDUSTRY') AND (S1.OptionID = CS.IndustryID))
    LEFT JOIN taSystemOption   AS S2 ON ((S2.OptionType = 'SETA')     AND (S2.OptionID = CS.SetaID))
    LEFT JOIN (

      SELECT CD.TypeID AS ClientID, COUNT(0) ClientDocCount, SUM(IF(CD.Folder = 'ServiceLevelAgreement', 1, 0)) AS SLADocCount
      FROM taDocument AS CD WHERE (CD.DocType = 'Client')
      GROUP BY 1

    ) AS X ON (X.ClientID = CS.ClientID)
WHERE
  ((TRIM(@aDateJoinedFrom) = '') OR (CS.DateJoined >= @aDateJoinedFrom)) AND
  (
    (@nHasClientDocs < 0) OR
    ((@nHasClientDocs = 0) AND (X.SLADocCount <= 0)) OR
    ((@nHasClientDocs > 0) AND (X.SLADocCount > 0))
  )
  @pProductID
  @pBranchID
  @pConsultantID
  @pClientStatus
GROUP BY
  BP.ConsultantID,
  CS.ClientID
ORDER BY
  U1.FullName
