SELECT

  -- Client Details.
  CS.ClientID,
  CS.FinNumber,
  LEFT(REPLACE(REPLACE(CS.GroupName, ',', ''), '''', ''), 40) AS Client,
  CA.ClientBranchID,
  CB.BranchName AS ClientBranchName,

  -- Case Details.
  CA.CaseID,
  CA.ExternalCaseID,
  CA.OpposingParty,
  S1.OptionDescription AS ForumName,
  S2.OptionDescription AS CaseStatusName,

  -- Consultant Details.
  U1.FullName AS Consultant,
  BR.BranchName AS LabourNetBranchName,

  -- Appointment Details.
  AP.AppointmentID,
  AT.AppointmentType,
  (SELECT GROUP_CONCAT(DISTINCT
            TRIM(CONCAT(TRIM(CC1.FirstName), ' ', TRIM(CC1.Surname))),
            IF(TRIM(CC1.Position) = '', '', CONCAT(' (', TRIM(CC1.Position), ')'))
          SEPARATOR '<br />')
   FROM taClientContacts AS CC1 WHERE (CC1.ContactType = 'Appointment') AND (CC1.TypeID = AP.AppointmentID)) AS Parties,
  IF(AP.IRChargeType = '0', '', REPLACE(AP.IRChargeType, ',', '<br />')) AS IRChargeType,
  IF(AP.IRFinding = '0', '', REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(AP.IRFinding, '1', 'Guilty'), '2', 'No Finding Made'), '3', 'Period For Review'), '4', 'Insufficient Evidence'), '5', 'Matter Postponed'), '6', 'Not Guilty')
  , ',', '<br />')) AS IRFinding,
  S3.OptionDescription AS IRRecommendation,
  IF(LEFT(AP.InvoiceNumber, 1) <> 'I', '', AP.InvoiceNumber) AS InvoiceNumber,
  AP.AttendanceDate AS AttendanceDate,
  AP.BillingDate AS BillingDate,
  AP.ServiceValue AS TotalAmount
FROM
  tbAppointments AS AP
    LEFT JOIN taClients          AS CS ON (CS.ClientID         = AP.ClientID)
    LEFT JOIN taClientBranches   AS CB ON (CB.ClientBranchID   = AP.ClientBranchID)
    LEFT JOIN tbAppointmentTypes AS AT ON (AT.AttendanceTypeID = AP.AttendanceTypeID)
    LEFT JOIN taProducts         AS PR ON (PR.ProductID        = AP.ProductID)
    LEFT JOIN taUsers            AS U1 ON (U1.UserID           = AP.ConsultantID)
    LEFT JOIN taBranches         AS BR ON (BR.BranchID         = AP.ConsultantBranchID)
    LEFT JOIN taCase             AS CA ON (CA.CaseID           = AP.CaseID)
    LEFT JOIN taSystemOption     AS S1 ON ((S1.OptionType = 'CASE-FORUM')        AND (S1.OptionID = CA.CaseForumID))
    LEFT JOIN taSystemOption     AS S2 ON ((S2.OptionType = 'CASE-STATUS')       AND (S2.OptionID = CA.CaseStatusID))
    LEFT JOIN taSystemOption     AS S3 ON ((S3.OptionType = 'IR-RECOMMENDATION') AND (S3.OptionID = AP.IRRecommendationID))
WHERE
  (AP.AppointmentStage = 'Authorised') AND
  (AP.ServiceValue IS NOT NULL) AND (AP.ServiceValue > 0.0) AND
  (CS.ClientID IS NOT NULL) AND (CS.ClientID > 0) AND
  (CB.ClientBranchID IS NOT NULL) AND (CB.ClientBranchID > 0) AND
  ((PR.ProductCategory = 'IR') OR (AT.ProductCategory = 'IR') OR (PR.ProductCategory = 'LLAW (SUPPORTING)') OR (AT.ProductCategory = 'LLAW (SUPPORTING)')) AND
  (AT.IsClientMatter > 0)
  @pFromTo
  @pClientID
  @pCaseStatusID
  @pBranchID
ORDER BY
  CS.GroupName,
  AP.CaseID
