SELECT
  B.Batch,
  IF(B.CountProcessed <= 0, 'Not Started Yet', IF(B.CountProcessed >= B.CountTotal, 'Complete', 'In-Progress')) AS Status,
  B.CountTotal AS `Count`,
  B.CountProcessed AS Processed,
  (B.CountTotal - B.CountProcessed) AS Remaining,
  B.RetainerFeesTotal AS Value,
  B.RetainerFeesProcessed AS Invoiced,
  (B.RetainerFeesTotal - B.RetainerFeesProcessed) AS Outstanding,
  '' AS Process,
  '' AS `View`,
  C.ItemCount AS JournalItemCount,
  C.InvoiceDates
FROM (

  SELECT
    X.Batch,
    SUM(X.CountTotal) AS CountTotal,
    SUM(IF(X.InvoiceCount >= X.CountTotal, 1, 0)) AS CountProcessed,
    SUM(X.RetainerFeesTotal) AS RetainerFeesTotal,
    SUM(X.RetainerFeesProcessed / X.CountTotal) AS RetainerFeesProcessed
  FROM (

    SELECT
      MD.Batch,
      MD.ClientID,
      COUNT(*) AS CountTotal,
      SUM(COALESCE(Y.InvoiceCount, 0)) AS InvoiceCount,
      SUM(COALESCE(MD.RetainerFees, 0.0)) AS RetainerFeesTotal,
      SUM(COALESCE(Y.RetainerFeesProcessed, 0.0)) AS RetainerFeesProcessed
    FROM
      ttRetainerBatchMembersDue AS MD
        LEFT JOIN taClients AS BE USING (ClientID)
        LEFT JOIN (

          SELECT M.Batch, M.ClientID, M.AccountBookType, M.AccountBookID, COUNT(*) AS InvoiceCount, SUM(M.UnitPriceExcl) AS RetainerFeesProcessed FROM (
            SELECT A.Batch, A.ClientID, A.AccountBookType, A.AccountBookID, A.UnitPriceExcl FROM (
              SELECT
                CAST(TRIM(SUBSTR(Batch, LOCATE(' ', IH.Batch))) AS SIGNED) AS Batch,
                IH.ClientID,
                IH.AccountBookType,
                IH.AccountBookID,
                ROUND(IL.UnitPriceExcl, 2) AS UnitPriceExcl
              FROM
                taInvoiceHeader AS IH
                  INNER JOIN taInvoiceLine AS IL ON (IL.CMSInvNo = IH.CMSInvNo)
                  INNER JOIN taClients AS BE ON (BE.ClientID = IH.ClientID)
              WHERE
                (IH.InvoiceType = 'R') AND
                (IH.DocumentNumber <> 'VOID') AND
                (IH.AppointmentID = @nYearMonth) AND
                (BE.ClientStatus = 1)
            ) AS A
          ) AS M GROUP BY M.Batch, M.ClientID, M.AccountBookType, M.AccountBookID

        ) AS Y USING (Batch, ClientID, AccountBookType, AccountBookID)
    GROUP BY 1,2
  ) AS X
  GROUP BY 1
) AS B
LEFT JOIN (

  SELECT
    Batch,
    COUNT(*) AS ItemCount,
    GROUP_CONCAT(DISTINCT DocumentDate) AS InvoiceDates
  FROM
    taRetainerReallocation
  WHERE
    (YearMonth = @nYearMonth) AND
    (CMSJournalNo IS NULL)
  GROUP BY 1

) AS C ON (C.Batch = B.Batch)
ORDER BY
  B.Batch
