REPLACE INTO taClientSummary

SELECT
  Y.ClientID,
  Y.FinNumber,
  Y.ClientName,
  Y.ProductCategory,
  Y.Batches,
  Y.AccountBooks,
  Y.BranchCount,
  Y.NoOfEmployees,
  Y.ClientDocCount,
  Y.ClientRetainer,
  (CASE
     WHEN Y.ClientRetainer >= 2000.0 THEN 'A'
     WHEN Y.ClientRetainer >= 1000.0 THEN 'B'
     WHEN Y.ClientRetainer >=  500.0 THEN 'C'
     ELSE 'D'
   END) AS ClientGroup,
  Y.OverdueBalance,
  Y.AccountBalance,
  Y.CurrentBalance,
  Y.ThirtyDayBalance,
  Y.SixtyDayBalance,
  Y.NinetyDayBalance,
  Y.OneTwentyDayBalance,
  Y.InvoicesDueCount,
  Y.National,
  Y.LabourNetBranch,
  Y.ClientConsultants,
  Y.ClientContactFirstName,
  Y.ClientContactSurname,
  LEFT(CONCAT(Y.GroupName, ' &nbsp;&nbsp;&nbsp; [', Y.FinNumber, ']', ' &nbsp;&nbsp;&nbsp; (', Y.ProductCategory, ')'),1024) AS GroupNameExt,
  Y.ClientContactsJson,
  Y.ClientProductsJson,
  Y.ClientBranchesJson
FROM (

  SELECT
    CS.ClientID,
    TRIM(CS.FinNumber) AS FinNumber,
    TRIM(CS.GroupName) AS GroupName,
    LEFT((SELECT GROUP_CONCAT(DISTINCT X1.ClientName) FROM (
       SELECT CB1.ClientID, TRIM(CB1.TradeName) AS ClientName FROM taClientBranches AS CB1 WHERE (TRIM(CB1.TradeName) <> '')
       UNION
       SELECT CB2.ClientID, TRIM(CB2.BranchName) AS ClientName FROM taClientBranches AS CB2 WHERE (TRIM(CB2.BranchName) <> '')
     ) AS X1 WHERE (X1.ClientID = CS.ClientID)),1024) AS ClientName,
    LEFT(COALESCE(
      (SELECT
        GROUP_CONCAT(DISTINCT PR1.ProductCategory SEPARATOR '<b> + </b>')
      FROM
        taClients AS CS1
          LEFT JOIN taClientProducts AS CP1 ON (CP1.ClientID = CS1.ClientID)
          LEFT JOIN taProducts AS PR1 ON (PR1.ProductID = CP1.ProductID)
      WHERE
        (CS1.ClientID = CS.ClientID) AND
        (CP1.Status = 1)
      GROUP BY CS1.ClientID),
     'ad-hoc'),1024) AS ProductCategory,
    LEFT((SELECT GROUP_CONCAT(DISTINCT X1.Batch SEPARATOR ',') FROM taClientProducts AS X1 WHERE (X1.ClientID = CS.ClientID)), 100) AS Batches,
    LEFT(
      (
        SELECT GROUP_CONCAT(DISTINCT X1.AccountBook SEPARATOR ',')
        FROM (
          SELECT CP1.ClientID, PB.PastelBookName AS AccountBook FROM taClientProducts AS CP1
          LEFT JOIN taPastelBook AS PB ON ((PB.PastelBookID = CP1.AccountBookID) AND (CP1.AccountBookType = 'Pastel'))
          UNION
          SELECT CP1.ClientID, SE.SageEntityName AS AccountBook FROM taClientProducts AS CP1
          LEFT JOIN taSageEntity AS SE ON ((SE.SageEntityID = CP1.AccountBookID) AND (CP1.AccountBookType = 'Sage'))
          UNION
          SELECT CP1.ClientID, XE.XeroEntityName AS AccountBook FROM taClientProducts AS CP1
          LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = CP1.AccountBookID) AND (CP1.AccountBookType = 'Xero'))
        ) AS X1 WHERE (X1.ClientID = CS.ClientID)
      )
    ,1024) AS AccountBooks,
    (SELECT COUNT(*) FROM taClientBranches AS CB1 WHERE (CB1.ClientID = CS.ClientID) AND (CB1.BranchStatus = 1)) AS BranchCount,
    (SELECT SUM(NoOfEmployees) FROM taClientBranches AS CB WHERE CB.ClientID = CS.ClientID AND CB.BranchStatus = 1) AS NoOfEmployees,
    (SELECT COUNT(*) FROM taDocument AS CD WHERE ((CD.DocType = 'Client') AND (CD.TypeID = CS.ClientID))) AS ClientDocCount,
    ROUND(fnClientRetainer(CS.ClientID, NOW()), 2) AS ClientRetainer,
    COALESCE(SM.OverdueBalance, 0.0) AS OverdueBalance,
    COALESCE(SM.AccountBalance, 0.0) AS AccountBalance,
    COALESCE(SM.CurrentBalance, 0.0) AS CurrentBalance,
    COALESCE(SM.ThirtyDayBalance, 0.0) AS ThirtyDayBalance,
    COALESCE(SM.SixtyDayBalance, 0.0) AS SixtyDayBalance,
    COALESCE(SM.NinetyDayBalance, 0.0) AS NinetyDayBalance,
    COALESCE(SM.OneTwentyDayBalance, 0.0) AS OneTwentyDayBalance,
    COALESCE(SM.InvoicesDueCount, 0) AS InvoicesDueCount,
    IF(fnClientLabourNetRegionCount(CS.ClientID) > 1, 'National', 'Local') AS National,
    LEFT((SELECT
       BR1.BranchName AS LabourNetBranchName
     FROM
       taClients AS CS1
         LEFT JOIN taClientBranches CB1 ON (CB1.ClientID = CS1.ClientID)
         LEFT JOIN taBranches AS BR1 ON (BR1.BranchID = CB1.LabourNetBranchID)
     WHERE
       (CS1.ClientID = CS.ClientID) AND
       (CB1.IsHeadOffice = 1) AND
       ((CB1.BranchStatus = 1) OR (CS1.ClientStatus = 0))
     LIMIT 1
    ), 20) AS LabourNetBranch,
    CONCAT(',',LEFT((
      SELECT GROUP_CONCAT(DISTINCT X1.ConsultantID) FROM (
        SELECT CB1.ClientID, BP1.ConsultantID
        FROM taClientBranchProducts AS BP1 LEFT JOIN taClientBranches AS CB1 ON (CB1.ClientBranchID = BP1.ClientBranchID)
        UNION
        SELECT CB2.ClientID, BP2.SalesCloserID
        FROM taClientBranchProducts AS BP2 LEFT JOIN taClientBranches AS CB2 ON (CB2.ClientBranchID = BP2.ClientBranchID)
        UNION
        SELECT CB3.ClientID, BP3.SalesReferrerID
        FROM taClientBranchProducts AS BP3 LEFT JOIN taClientBranches AS CB3 ON (CB3.ClientBranchID = BP3.ClientBranchID)
      ) AS X1 WHERE (X1.ClientID = CS.ClientID)
    ),1024),',') AS ClientConsultants,
    LEFT((SELECT
       GROUP_CONCAT(DISTINCT FirstName)
     FROM
       taClientContacts AS CC1
         LEFT JOIN taClientBranches AS CB2 ON ((CB2.ClientBranchID = CC1.TypeID) AND (CC1.ContactType = 'ClientBranch'))
     WHERE
       (CB2.ClientID = CS.ClientID)
    ),1024) AS ClientContactFirstName,
    LEFT((SELECT
       GROUP_CONCAT(DISTINCT Surname)
     FROM
       taClientContacts AS CC2
         LEFT JOIN taClientBranches AS CB3 ON ((CB3.ClientBranchID = CC2.TypeID) AND (CC2.ContactType = 'ClientBranch'))
     WHERE
       (CB3.ClientID = CS.ClientID)
    ),1024) AS ClientContactSurname,
    C1.ClientContactsJson,
    P1.ClientProductsJson,
    B1.ClientBranchesJson
  FROM
    taClients AS CS
      LEFT JOIN taClientSummary AS SM ON (SM.ClientID = CS.ClientID)
      LEFT JOIN (

        SELECT
          CASE CC.ContactType
            WHEN 'Client' THEN CC.TypeID
            WHEN 'ClientBranch' THEN CB.ClientID
            ELSE 0
          END AS ClientID,
          CONCAT('[', GROUP_CONCAT(DISTINCT CONCAT('{',
                      '"ContactID":', CC.ContactID, ',',
                      '"Name":"', TRIM(CONCAT(TRIM(COALESCE(CC.Title, '')), ' ', TRIM(COALESCE(CC.FirstName, '')), ' ', TRIM(COALESCE(CC.Surname, '')))), '",',
                      '"Position":"', TRIM(COALESCE(CC.Position, '')), '",',
                      '"Number":"', CONCAT(TRIM(COALESCE(CC.TelephoneAreaCode, '')), TRIM(COALESCE(CC.TelephoneNo, ''))), '",',
                      '"Email":"', TRIM(COALESCE(CC.Email, '')), '"',
                '}') SEPARATOR ','), ']') AS ClientContactsJson
        FROM
          taClientContacts AS CC
            LEFT JOIN taClients AS CS ON ((CS.ClientID = CC.TypeID) AND (CC.ContactType = 'Client'))
            LEFT JOIN taClientBranches AS CB ON ((CB.ClientBranchID = CC.TypeID) AND (CC.ContactType = 'ClientBranch'))
            LEFT JOIN taClients AS C2 ON (C2.ClientID = CB.ClientID)
        WHERE
          (CC.ContactType IN ('Client','ClientBranch')) AND
          (
            CASE CC.ContactType
              WHEN 'Client' THEN CS.ClientStatus
              WHEN 'ClientBranch' THEN C2.ClientStatus
              ELSE ''
            END = 1
          )
        GROUP BY
          1

      ) AS C1 ON (C1.ClientID = CS.ClientID)
      LEFT JOIN (

        SELECT
          M.ClientID,
          CONCAT('[', GROUP_CONCAT(DISTINCT CONCAT('{"ClientProductID":', M.ClientProductID, ',"ProductName":"', N.ProductName, '","ProductID":', N.ProductID, ',"ClientBranches":', P.ClientBranchProductsJson, '}') SEPARATOR ','), ']') AS ClientProductsJson
        FROM
          taClientProducts AS M
            LEFT JOIN taProducts AS N ON (N.ProductID = M.ProductID)
            LEFT JOIN (

              SELECT
                Q.ClientProductID,
                CONCAT('[', GROUP_CONCAT(DISTINCT CONCAT('{"ClientBranchProductID":', Q.ClientBranchProductID, ',"ClientBranchName":"',
                               REPLACE(REPLACE(REPLACE(COALESCE(R.BranchName, ''), '"', ''), '''', ''), '\\', ''),
                               '","ClientBranchID":', R.ClientBranchID, ',"LabourNetBranchName":"', S.BranchName, '"}') SEPARATOR ','), ']') AS ClientBranchProductsJson
              FROM
                taClientBranchProducts AS Q
                  LEFT JOIN taClientBranches AS R ON (R.ClientBranchID = Q.ClientBranchID)
                  LEFT JOIN taBranches AS S ON (S.BranchID = R.LabourNetBranchID)
              WHERE
                (Q.Status = 1)
              GROUP BY
                Q.ClientProductID

            ) AS P ON (P.ClientProductID = M.ClientProductID)
        WHERE
          (M.Status = 1)
        GROUP BY
          M.ClientID

      ) AS P1 ON (P1.ClientID = CS.ClientID)
      LEFT JOIN (

        SELECT
          T.ClientID,
          CONCAT('[', GROUP_CONCAT(DISTINCT CONCAT('{"ClientBranchProductID":0,"ClientBranchName":"',
                     REPLACE(REPLACE(REPLACE(COALESCE(T.BranchName, ''), '"', ''), '''', ''), '\\', ''),
                     '","ClientBranchID":', T.ClientBranchID, ',"LabourNetBranchName":"', COALESCE(U.BranchName, ''), '"}') SEPARATOR ','), ']') AS ClientBranchesJson
        FROM
          taClientBranches AS T
            LEFT JOIN taBranches AS U ON (U.BranchID = T.LabourNetBranchID)
        WHERE
          (T.BranchStatus = 1)
        GROUP BY
          T.ClientID

      ) AS B1 ON (B1.ClientID = CS.ClientID)
  WHERE
    (CS.FinNumber IS NOT NULL) AND (TRIM(CS.FinNumber) <> '')
    @pClientID

) AS Y