SELECT
  1 AS MainOrder,
  IF(PR1.ProductName IS NULL, PR.ProductName, CONCAT(PR1.ProductName, '|', PR.ProductName)) AS ProductOrder,
  CP.ProductID,
  CP.ClientProductID,
  PR.ProductName,
  CP1.ProductID AS ParentProductID,
  PR1.ProductName AS ParentProductName,
  Y.AllowedBranches,
  X.IsService,
  X.AttendanceTypeID,
  X.AppointmentType
FROM
  taClientProducts AS CP
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
    LEFT JOIN taClientProducts AS CP1 ON (CP1.ClientProductID = CP.ParentClientProductID)
    LEFT JOIN taProducts AS PR1 ON (PR1.ProductID = CP1.ProductID)
    LEFT JOIN (

      SELECT
        PR.ProductID,
        AT.IsService,
        AT.AttendanceTypeID,
        AT.AppointmentType
      FROM
        taProducts AS PR
          LEFT JOIN tbAppointmentTypes AS AT ON (AT.ProductCategory = PR.ProductCategory)
          LEFT JOIN taAttendanceExclusion AS AE ON ((AE.ProductID = PR.ProductID) AND (AE.AttendanceTypeID = AT.AttendanceTypeID))
      WHERE
        (AT.IsActive = 1) AND
        (AE.AttendanceTypeID IS NULL) AND
        (TRIM(AT.AppointmentType) <> '')

    ) AS X ON (X.ProductID = PR.ProductID)
    LEFT JOIN (

      SELECT
        BP.ClientProductID,
        GROUP_CONCAT(DISTINCT BP.ClientBranchID) AS AllowedBranches
      FROM
        taClientBranchProducts AS BP
          INNER JOIN taClientProducts AS CP2 ON (CP2.ClientProductID = BP.ClientProductID)
      WHERE
        (BP.Status = 1) AND
        (CP2.Status = 1) AND
        (CP2.ClientID = @nClientID)
      GROUP BY
        BP.ClientProductID

    ) AS Y ON (Y.ClientProductID = CP.ClientProductID)
WHERE
  (CP.ClientID = @nClientID) AND
  (CP.Status = 1) AND
  (X.AttendanceTypeID IS NOT NULL)

UNION

SELECT
  2 AS MainOrder,
  AT.ProductCategory AS ProductOrder,
  14 AS ProductID,
  -1 AS ClientProductID,
  'ADHOC' AS ProductName,
  -1 AS ParentProductID,
  AT.ProductCategory AS ParentProductName,
  Y.AllowedBranches,
  AT.IsService,
  AT.AttendanceTypeID,
  AT.AppointmentType
FROM
  tbAppointmentTypes AS AT
    LEFT JOIN taAttendanceExclusion AS AE ON ((AE.ProductID = 14) AND (AE.AttendanceTypeID = AT.AttendanceTypeID)),
  (SELECT GROUP_CONCAT(ClientBranchID) AS AllowedBranches FROM taClientBranches WHERE (BranchStatus = 1) AND (ClientID = @nClientID)) AS Y,
  (SELECT AdhocAllowed FROM taClients WHERE (ClientID = @nClientID)) AS Z
WHERE
  (Z.AdhocAllowed = 1) AND
  (AT.IsActive = 1) AND
  (TRIM(AT.AppointmentType) <> '') AND
  ((AE.ProductID IS NULL) OR (AT.AlwaysAllowed = 1)) AND
  (AT.ProductCategory NOT IN ((
     SELECT
       PR1.ProductCategory
     FROM
       taClientProducts AS CP1
         INNER JOIN taProducts AS PR1 USING (ProductID)
         INNER JOIN taProductCategory AS PC1 USING (ProductCategory)
     WHERE
       (CP1.ClientID = @nClientID) AND
       (CP1.Status = 1) AND
       (PC1.CoverAllowed = 1)
  )))

ORDER BY 1,2,9,11
