SELECT
  CP.ClientID,
  CP.ProductID,
  CP.ClientProductID,
  PR.ProductCategory,
  PR.ProductName,
  IF(CP1.ClientProductID IS NULL, IF(X.ChildCount > 0, 'parent', 'n/a'), 'child') AS Children,
  CP.Batch,
  COALESCE(IF(CP.AccountBookType = 'Xero', XE.XeroEntityName, IF(CP.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(CP.AccountBookType, '_', CP.AccountBookID)) AS AccountBookName,
  CP.DedicatedRetainerInvoice,
  IF(CP.Status = 1, 'ACTIVE', '<font class="emphasize">CANCELLED</font>') AS ProductStatus,
  CP.JoinDate,
  CP.NextIncreaseDate,
  IF(CP.Status = 1, '', CP.TerminationDate) AS TerminationDate,
  fnClientProductRetainer(CP.ClientProductID, NOW()) AS Retainer
FROM
  taClientProducts AS CP
    LEFT JOIN taProducts        AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taClients         AS CS ON (CS.ClientID = CP.ClientID)
    LEFT JOIN taSageEntity      AS SE ON ((SE.SageEntityID = CP.AccountBookID) AND (CP.AccountBookType = 'Sage'))
    LEFT JOIN taXeroEntity      AS XE ON ((XE.XeroEntityID = CP.AccountBookID) AND (CP.AccountBookType = 'Xero'))
    LEFT JOIN taClientProducts  AS CP1 ON (CP1.ClientProductID = CP.ParentClientProductID)
    LEFT JOIN taProducts        AS PR1 ON (PR1.ProductID = CP1.ProductID)
    LEFT JOIN (
      SELECT ParentClientProductID, COUNT(*) AS ChildCount FROM taClientProducts WHERE (Status = 1) AND (ParentClientProductID IS NOT NULL) GROUP BY 1
    ) AS X ON (X.ParentClientProductID = CP.ClientProductID)
WHERE
  (CP.ClientID = @nClientID)
  @pStatus
ORDER BY
  CP.Status,
  IF(PR1.ProductName IS NULL, PR.ProductName, CONCAT(PR1.ProductName, '|', PR.ProductName))
