SELECT

  -- Client Detail.
  CS.ClientID,
  CS.GroupName,
  CS.FinNumber,
  fnClientRetainer(CS.ClientID, DATE_SUB(IF(CS.TerminationDate IS NULL, NOW(), CS.TerminationDate), INTERVAL 1 DAY)) AS ClientRetainer,
  SM.AccountBalance,
  SM.OverdueBalance,
  SM.BranchCount,
  SM.NoOfEmployees,
  BR.BranchName AS LabourNetBranchName,

  -- Client Termination Details.
  IF(CS.ClientStatus <> 1, 'Cancelled', IF(CS.TerminationDate IS NOT NULL, 'Cancellation Pending', 'Active')) AS ClientStatus,
  CS.TerminationDate AS ClientTerminationDate,
  CS.TerminationReason AS ClientTerminationReason,
  CS.TerminationNotes AS ClientTerminationNotes,

  -- Client Product Details.
  CONCAT('(', PR.ProductCategory, ') ', PR.ProductName) AS ProductName,
  IF(CP.CancelApproveDateExecutive IS NOT NULL, '3 - Approved',
    IF(CP.CancelApproveDateManager IS NOT NULL, '2 - Requested', '1 - Initiated')) AS CancellationStage,
  CP.TerminationDate,
  CP.CancellationInitiatedDate,
  IF(CP.CancelApproveDateExecutive IS NOT NULL, DATEDIFF(CP.CancelApproveDateExecutive, CP.CancellationInitiatedDate), DATEDIFF(NOW(), CP.CancellationInitiatedDate)) AS DaysInCancellation,
  fnClientProductRetainer(CP.ClientProductID, DATE_SUB(CP.TerminationDate, INTERVAL 1 DAY)) AS ClientProductRetainer,
  X.FirstRetainerInvoiceDate,
  X.LastRetainerInvoiceDate,
  X.RetainerInvoiceCount,
  IF(X.RetainerInvoiceCount < SM.InvoicesDueCount, 0, (X.RetainerInvoiceCount - SM.InvoicesDueCount)) AS PaidRetainerInvoiceCount,
  SM.InvoicesDueCount,
  ( SELECT GROUP_CONCAT(DISTINCT U1.FullName SEPARATOR ',<br>')
    FROM
      taBranchStructure AS BS
        LEFT JOIN taUsers AS U1 ON (U1.UserID = BS.ConsultantID)
    WHERE
      (BranchID = HO.LabourNetBranchID) AND
      (Description LIKE CONCAT('%Regional ', PR.ProductCategory, ' Manager%'))
  ) AS RegionalProductManagers,
  IF(CP.CancelApproveDateManager IS NOT NULL, DATEDIFF(CP.CancelApproveDateManager, CP.CancellationInitiatedDate), NULL) AS DaysInManagerStage,
  CP.CancelApproveDateManager,
  U1.FullName AS ProductExecutive,
  IF(CP.CancelApproveDateExecutive IS NOT NULL, DATEDIFF(CP.CancelApproveDateExecutive, CP.CancelApproveDateManager), NULL) AS DaysInExecutiveStage,
  CP.CancelApproveDateExecutive
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 taFinCategory     AS FC ON (FC.FinCategoryID   = CS.FinCategory)
    LEFT JOIN (
      SELECT
        X.ClientID,
        ( SELECT Y.LabourNetBranchID
          FROM taClientBranches AS Y
          WHERE (Y.IsHeadOffice = 1) AND (Y.ClientID = X.ClientID)
          ORDER BY Y.BranchStatus DESC
          LIMIT 1
        ) AS LabourNetBranchID
      FROM taClients AS X
    ) AS HO ON (HO.ClientID = CS.ClientID)
    LEFT JOIN taBranches        AS BR ON (BR.BranchID        = HO.LabourNetBranchID)
    LEFT JOIN taProducts        AS PR ON (PR.ProductID       = CP.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
    LEFT JOIN taUsers           AS U1 ON (U1.UserID          = PC.ProductManagerID)
    LEFT JOIN (
      SELECT
        IH.ClientID,
        MIN(IH.DocumentDate) AS FirstRetainerInvoiceDate,
        MAX(IH.DocumentDate) AS LastRetainerInvoiceDate,
        COUNT(*) AS RetainerInvoiceCount
      FROM
        taInvoiceHeader AS IH
      WHERE
        (IH.InvoiceType = 'R') AND
        (IH.DocumentNumber <> 'VOID') AND
        (IH.ExternalID IS NOT NULL)
      GROUP BY
        IH.ClientID
    ) AS X ON (X.ClientID = CS.ClientID)
    LEFT JOIN taSystemOption AS SO ON ((SO.OptionType = 'TERMINATION') AND (SO.OptionDescription = CP.TerminationReason))
WHERE
  (
    (
      (CP.TerminationDate IS NOT NULL) AND (CP.TerminationDate <> '0000-00-00')
      @pFromTo
    ) OR (
      (CS.TerminationDate IS NOT NULL) AND (CS.TerminationDate <> '0000-00-00') AND
      (CP.Status = 1) AND
      ((CP.TerminationDate IS NULL) OR (CP.TerminationDate = '0000-00-00'))
      @pClientFromTo
    )
  ) AND (
    (@nCancelStage <= 0) OR (@nCancelStage > 4) OR
    ((@nCancelStage = 1) AND (CP.CancelApproveDateManager IS NULL) AND (CP.CancelApproveDateExecutive IS NULL)) OR
    ((@nCancelStage = 2) AND (CP.CancelApproveDateManager IS NOT NULL) AND (CP.CancelApproveDateExecutive IS NULL)) OR
    ((@nCancelStage = 3) AND (CP.CancelApproveDateManager IS NOT NULL) AND (CP.CancelApproveDateExecutive IS NOT NULL)) OR
    ((@nCancelStage = 4) AND (CP.CancelApproveDateExecutive IS NULL))
  )
  @pTerminationReason
  @pProductID
  @pBranchID
ORDER BY
  CS.GroupName,
  CP.TerminationDate