citywebshop_cms/application/models/userstory_model.php

<?php

class userstory_model extends Model {

  public function getAllStories() {
    $rows = $this->query("SELECT
      uuid,
      previous_uuid,
      MIN(created_at) as first_event,
      MAX(created_at) as last_event,
      COUNT(*) as event_count,
      MAX(hotel) as hotel,
      MAX(order_id) as order_id,
      MAX(ip_address) as ip_address
    FROM azonics_user_events
    GROUP BY uuid
    ORDER BY last_event DESC;");

    // UUID change láncolatok feloldása: previous_uuid -> uuid mapping
    $uuidChanges = $this->query("SELECT uuid, previous_uuid FROM azonics_user_events
      WHERE event_type = 'uuid_change' AND previous_uuid != ''
      ORDER BY event_id ASC;");

    // Mapping: new_uuid => original_uuid (a lánc elejére mutat)
    $childToParent = [];
    foreach ($uuidChanges as $change) {
      $parent = $change->previous_uuid;
      // Ha a parent maga is egy child, kövessük a láncot
      if (isset($childToParent[$parent])) {
        $parent = $childToParent[$parent];
      }
      $childToParent[$change->uuid] = $parent;
    }

    // Sorok csoportosítása az eredeti (parent) UUID alapján
    $grouped = [];
    $order = [];
    foreach ($rows as $row) {
      $originalUUID = isset($childToParent[$row->uuid]) ? $childToParent[$row->uuid] : $row->uuid;

      if (!isset($grouped[$originalUUID])) {
        $grouped[$originalUUID] = (object)[
          'uuid' => $originalUUID,
          'new_uuids' => [],
          'first_event' => $row->first_event,
          'last_event' => $row->last_event,
          'event_count' => 0,
          'hotel' => '',
          'order_id' => '',
          'ip_address' => ''
        ];
        $order[] = $originalUUID;
      }

      $g = $grouped[$originalUUID];
      $g->event_count += (int)$row->event_count;
      if ($row->first_event < $g->first_event) $g->first_event = $row->first_event;
      if ($row->last_event > $g->last_event) $g->last_event = $row->last_event;
      if (!empty($row->hotel)) $g->hotel = $row->hotel;
      if (!empty($row->order_id)) $g->order_id = $row->order_id;
      if (!empty($row->ip_address)) $g->ip_address = $row->ip_address;

      if ($row->uuid !== $originalUUID) {
        $g->new_uuids[] = $row->uuid;
      }
    }

    // Rendezés last_event desc szerint
    usort($order, function($a, $b) use ($grouped) {
      return strcmp($grouped[$b]->last_event, $grouped[$a]->last_event);
    });

    $result = [];
    foreach ($order as $key) {
      $result[] = $grouped[$key];
    }
    return $result;
  }

  public function getEventsByUUID($uuid) {
    $uuid = $this->escapeString($uuid);
    return $this->query("SELECT * FROM azonics_user_events
      WHERE uuid = '".$uuid."' OR previous_uuid = '".$uuid."' OR uuid IN (
        SELECT uuid FROM azonics_user_events WHERE previous_uuid = '".$uuid."'
      )
      ORDER BY created_at ASC, event_id ASC;");
  }

}