ollama pull embeddinggemma

CREATE TABLE support_tickets_ollama LIKE support_tickets;

INSERT INTO support_tickets_ollama
SELECT * FROM support_tickets;

ALTER TABLE support_tickets_ollama
    ADD COLUMN embedding_local VECTOR(768) NULL,
    ADD COLUMN embedding_hash_local CHAR(64) NULL;

SELECT
    support_tickets_ollama.*,
    '' AS semantic_query
FROM support_tickets_ollama

if (!function_exists("devclubOllamaEmbedding")) {
    function devclubOllamaEmbedding($text)
    {
        if (!function_exists("curl_init")) {
            throw new RuntimeException(
                "PHP cURL extension is required."
            );
        }

        $payload = json_encode(array(
            "model" => "embeddinggemma:latest",
            "input" => $text
        ), JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);

        if ($payload === false) {
            throw new RuntimeException("Cannot encode Ollama request.");
        }

        $ch = curl_init("http://127.0.0.1:11434/api/embed");

        if ($ch === false) {
            throw new RuntimeException("Cannot initialize cURL.");
        }

        curl_setopt_array($ch, array(
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => $payload,
            CURLOPT_HTTPHEADER => array(
                "Content-Type: application/json"
            ),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CONNECTTIMEOUT => 5,
            CURLOPT_TIMEOUT => 90
        ));

        $response = curl_exec($ch);
        $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $error = curl_error($ch);
        curl_close($ch);

        if ($response === false) {
            throw new RuntimeException(
                "Ollama connection failed: " . $error
            );
        }

        $data = json_decode($response, true);

        if ($httpCode !== 200) {
            $detail = is_array($data)
                ? ($data["error"] ?? "Unknown error")
                : "Unknown error";

            throw new RuntimeException(
                "Ollama HTTP " . $httpCode . ": " . $detail
            );
        }

        $vector = $data["embeddings"][0] ?? null;

        if (!is_array($vector) || count($vector) !== 768) {
            throw new RuntimeException(
                "Expected 768-dimensional embedding."
            );
        }

        foreach ($vector as $number) {
            if (!is_numeric($number) ||
                !is_finite((float)$number)) {
                throw new RuntimeException(
                    "Invalid number in embedding."
                );
            }
        }

        $json = json_encode(
            $vector,
            JSON_PRESERVE_ZERO_FRACTION
        );

        if ($json === false) {
            throw new RuntimeException(
                "Cannot encode embedding."
            );
        }

        return $json;
    }
}

try {
    $batchSize = 100;
    $tickets = array();
    $inputs = array();

    $rs = DB::Query(
        "SELECT ticket_id, subject, description " .
        "FROM support_tickets_ollama " .
        "WHERE embedding_local IS NULL " .
        "ORDER BY ticket_id LIMIT " . (int)$batchSize
    );

    if (!$rs) {
        throw new RuntimeException(DB::LastError());
    }

    while ($row = $rs->fetchAssoc()) {
        $text = "title: " . (string)$row["subject"] .
            " | text: " . (string)$row["description"];

        $tickets[] = array(
            "id" => (int)$row["ticket_id"],
            "text" => $text
        );

        $inputs[] = $text;
    }

    if (!$tickets) {
        $result["message"] =
            "All tickets already have local embeddings.";
    } else {
        if (!function_exists("curl_init")) {
            throw new RuntimeException(
                "PHP cURL extension is required."
            );
        }

        $payload = json_encode(array(
            "model" => "embeddinggemma:latest",
            "input" => $inputs
        ), JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);

        if ($payload === false) {
            throw new RuntimeException("Cannot encode request.");
        }

        $ch = curl_init("http://127.0.0.1:11434/api/embed");

        curl_setopt_array($ch, array(
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => $payload,
            CURLOPT_HTTPHEADER => array(
                "Content-Type: application/json"
            ),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CONNECTTIMEOUT => 5,
            CURLOPT_TIMEOUT => 120
        ));

        $response = curl_exec($ch);
        $httpCode = (int)curl_getinfo(
            $ch, CURLINFO_HTTP_CODE
        );
        $curlError = curl_error($ch);
        curl_close($ch);

        if ($response === false) {
            throw new RuntimeException(
                "Cannot connect to Ollama: " . $curlError
            );
        }

        $data = json_decode($response, true);

        if ($httpCode !== 200) {
            $detail = $data["error"] ??
                substr($response, 0, 200);

            throw new RuntimeException(
                "Ollama HTTP " . $httpCode . ": " . $detail
            );
        }

        $vectors = $data["embeddings"] ?? null;

        if (!is_array($vectors) ||
            count($vectors) !== count($tickets)) {
            throw new RuntimeException(
                "Unexpected number of embeddings returned."
            );
        }

        $updated = 0;

        foreach ($tickets as $i => $ticket) {
            $vector = $vectors[$i];

            if (!is_array($vector) || count($vector) !== 768) {
                throw new RuntimeException(
                    "Invalid vector for ticket " . $ticket["id"]
                );
            }

            foreach ($vector as $number) {
                if (!is_numeric($number) ||
                    !is_finite((float)$number)) {
                    throw new RuntimeException(
                        "Invalid vector number for ticket " .
                        $ticket["id"]
                    );
                }
            }

            $jsonVector = json_encode(
                $vector,
                JSON_PRESERVE_ZERO_FRACTION
            );

            if ($jsonVector === false) {
                throw new RuntimeException(
                    "Cannot encode vector for ticket " .
                    $ticket["id"]
                );
            }

            $hash = hash("sha256", $ticket["text"]);

            $sql = DB::PrepareSQL(
                "UPDATE support_tickets_ollama " .
                "SET embedding_local = VEC_FromText(':1'), " .
                "embedding_hash_local = ':2' " .
                "WHERE ticket_id = :3",
                $jsonVector,
                $hash,
                $ticket["id"]
            );

            if (!DB::Exec($sql)) {
                throw new RuntimeException(
                    "Saving ticket " . $ticket["id"] .
                    " failed: " . DB::LastError()
                );
            }

            $updated++;
        }

        $countRs = DB::Query(
            "SELECT COUNT(*) AS remaining " .
            "FROM support_tickets_ollama " .
            "WHERE embedding_local IS NULL"
        );

        if (!$countRs) {
            throw new RuntimeException(DB::LastError());
        }

        $countRow = $countRs->fetchAssoc();
        $remaining = (int)$countRow["remaining"];

        $result["message"] =
            "Generated " . $updated . " embeddings. " .
            $remaining . " tickets remaining.";
    }
} catch (Throwable $e) {
    $result["message"] =
        "Embedding generation failed: " . $e->getMessage();
}

ctrl.setMessage(result["message"]);

ALTER TABLE support_tickets_ollama
    MODIFY COLUMN embedding_local VECTOR(768) NOT NULL;

CREATE VECTOR INDEX idx_support_tickets_embedding
    ON support_tickets_ollama (embedding_local)
    M=8 DISTANCE=cosine;

$srch = SearchClause::getSearchObject("support_tickets_ollama");
$phrase = trim((string)$srch->getAllFieldsSearchValue());

if ($phrase !== "") {
    $key = hash("sha256", "embeddinggemma:latest\n" . $phrase);
    $cache = $_SESSION["DEVCLUB_LOCAL_SEARCH_CACHE"] ?? null;
    $ids = null;

    unset($_SESSION["DEVCLUB_LOCAL_SEARCH_ERROR"]);

    if (
        is_array($cache) &&
        isset($cache["key"], $cache["time"], $cache["ids"]) &&
        $cache["key"] === $key &&
        time() - (int)$cache["time"] < 300
    ) {
        $ids = $cache["ids"];
    }

    if ($ids === null) {
        try {
            if (strlen($phrase) > 1000) {
                throw new RuntimeException("Search text is too long.");
            }

            $payload = json_encode(array(
                "model" => "embeddinggemma:latest",
                "input" => "task: search result | query: " . $phrase
            ), JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);

            if ($payload === false) {
                throw new RuntimeException("Cannot encode Ollama request.");
            }

            $ch = curl_init("http://127.0.0.1:11434/api/embed");

            if ($ch === false) {
                throw new RuntimeException("Cannot initialize cURL.");
            }

            curl_setopt_array($ch, array(
                CURLOPT_POST => true,
                CURLOPT_POSTFIELDS => $payload,
                CURLOPT_HTTPHEADER => array(
                    "Content-Type: application/json"
                ),
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_CONNECTTIMEOUT => 5,
                CURLOPT_TIMEOUT => 60
            ));

            $response = curl_exec($ch);
            $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
            $curlError = curl_error($ch);
            curl_close($ch);

            if ($response === false) {
                throw new RuntimeException(
                    "Ollama connection failed: " . $curlError
                );
            }

            $data = json_decode($response, true);

            if ($httpCode !== 200) {
                $detail = is_array($data)
                    ? ($data["error"] ?? "Unknown error")
                    : "Unknown error";

                throw new RuntimeException(
                    "Ollama HTTP " . $httpCode . ": " . $detail
                );
            }

            $vector = $data["embeddings"][0] ?? null;

            if (!is_array($vector) || count($vector) !== 768) {
                throw new RuntimeException(
                    "Expected a 768-dimensional embedding."
                );
            }

            foreach ($vector as $number) {
                if (!is_numeric($number) ||
                    !is_finite((float)$number)) {
                    throw new RuntimeException(
                        "Invalid query embedding value."
                    );
                }
            }

            $vectorJson = json_encode(
                $vector,
                JSON_PRESERVE_ZERO_FRACTION
            );

            if ($vectorJson === false) {
                throw new RuntimeException("Cannot encode query vector.");
            }

            $sql = DB::PrepareSQL(
                "SELECT ticket_id FROM support_tickets_ollama " .
                "ORDER BY VEC_DISTANCE_COSINE(" .
                "embedding_local, VEC_FromText(':1')) " .
                "LIMIT 30",
                $vectorJson
            );

            $rs = DB::Query($sql);

            if (!$rs) {
                throw new RuntimeException(
                    "MariaDB query failed: " . DB::LastError()
                );
            }

            $ids = array();

            while ($row = $rs->fetchAssoc()) {
                $ids[] = (int)$row["ticket_id"];
            }

            $_SESSION["DEVCLUB_LOCAL_SEARCH_CACHE"] = array(
                "key" => $key,
                "time" => time(),
                "ids" => $ids
            );
        } catch (Throwable $e) {
            $_SESSION["DEVCLUB_LOCAL_SEARCH_ERROR"] =
                $e->getMessage();

            unset($_SESSION["DEVCLUB_LOCAL_SEARCH_CACHE"]);
            $ids = array();
        }
    }

    $condition = $ids
        ? "ticket_id IN (" .
            implode(",", array_map("intval", $ids)) . ")"
        : "1=0";

    $srch->setSearchSQL("semantic_query", $condition);
} else {
    unset($_SESSION["DEVCLUB_LOCAL_SEARCH_ERROR"]);
}

$srch = SearchClause::getSearchObject("support_tickets_ollama");
$phrase = trim((string)$srch->getAllFieldsSearchValue());

$cache = $_SESSION["DEVCLUB_LOCAL_SEARCH_CACHE"] ?? null;
$key = hash("sha256", "embeddinggemma:latest\n" . $phrase);

if (
    $phrase !== "" &&
    is_array($cache) &&
    isset($cache["key"], $cache["time"], $cache["ids"]) &&
    $cache["key"] === $key &&
    time() - (int)$cache["time"] < 300 &&
    !empty($cache["ids"])
) {
    $ids = array_map("intval", $cache["ids"]);

    $strOrderBy = " ORDER BY FIELD(ticket_id, " .
        implode(",", $ids) . ")";
}
[/php]

The search query filters the grid to the matching IDs. MariaDB's <strong>FIELD()</strong> function preserves the similarity order so the strongest match appears first.

<h3>7. Preventing possible duplicate tickets</h3>

Semantic similarity can also be checked before a record is added. Open the <strong>support_tickets_ollama Add page - Before record added</strong> event and add this code:

[php]
try {
    $subject = trim((string)($values["subject"] ?? ""));
    $description = trim((string)($values["description"] ?? ""));

    if ($subject === "" || $description === "") {
        $message = "Subject and description are required.";
        return false;
    }

    $text = "title: " . $subject .
        " | text: " . $description;

    $hash = hash("sha256", $text);
    $vectorJson = devclubOllamaEmbedding($text);

    $sql = DB::PrepareSQL(
        "SELECT ticket_id, subject, " .
        "VEC_DISTANCE_COSINE(" .
        "embedding_local, VEC_FromText(':1')) AS distance " .
        "FROM support_tickets_ollama " .
        "WHERE embedding_hash_local IS NOT NULL " .
        "ORDER BY VEC_DISTANCE_COSINE(" .
        "embedding_local, VEC_FromText(':2')) " .
        "LIMIT 3",
        $vectorJson,
        $vectorJson
    );

    $rs = DB::Query($sql);

    if (!$rs) {
        throw new RuntimeException(DB::LastError());
    }

    $duplicateThreshold = 0.93;

    while ($row = $rs->fetchAssoc()) {
        if ($row["distance"] === null) {
            continue;
        }

        $similarity = 1.0 - (float)$row["distance"];

        if ($similarity >= $duplicateThreshold) {
            $message =
                "Possible duplicate ticket found. " .
                "Ticket ID: " . $row["ticket_id"] .
                ". Subject: " . $row["subject"] .
                ". Similarity: " .
                number_format($similarity, 3) .
                ". Please review before adding.";

            return false;
        }
    }

    $sqlValues["embedding_local"] = DB::PrepareSQL(
        "VEC_FromText(':1')",
        $vectorJson
    );

    $sqlValues["embedding_hash_local"] = DB::PrepareSQL(
        "':1'",
        $hash
    );

    return true;
} catch (Throwable $e) {
    $message = "Cannot add ticket: " . $e->getMessage();
    return false;
}

try {
    $subject = array_key_exists("subject", $values)
        ? (string)$values["subject"]
        : (string)($oldvalues["subject"] ?? "");

    $description = array_key_exists("description", $values)
        ? (string)$values["description"]
        : (string)($oldvalues["description"] ?? "");

    $text = "title: " . trim($subject) .
        " | text: " . trim($description);

    $hash = hash("sha256", $text);
    $ticketId = (int)$keys["ticket_id"];

    $sql = DB::PrepareSQL(
        "SELECT embedding_hash_local " .
        "FROM support_tickets_ollama " .
        "WHERE ticket_id = :1",
        $ticketId
    );

    $rs = DB::Query($sql);

    if (!$rs) {
        throw new RuntimeException(DB::LastError());
    }

    $row = $rs->fetchAssoc();

    if (!$row) {
        throw new RuntimeException("Ticket not found.");
    }

    $existingHash = (string)(
        $row["embedding_hash_local"] ?? ""
    );

    if ($existingHash !== $hash) {
        $vectorJson = devclubOllamaEmbedding($text);

        $sqlValues["embedding_local"] = DB::PrepareSQL(
            "VEC_FromText(':1')",
            $vectorJson
        );

        $sqlValues["embedding_hash_local"] = DB::PrepareSQL(
            "':1'",
            $hash
        );
    }

    return true;
} catch (Throwable $e) {
    $message = "Cannot update ticket: " . $e->getMessage();
    return false;
}

CREATE TABLE `support_tickets` (
    `ticket_id` int NOT NULL AUTO_INCREMENT,
    `ticket_code` varchar(16) NOT NULL,
    `subject` varchar(255) NOT NULL,
    `description` longtext NOT NULL,
    `customer_name` varchar(100) NOT NULL,
    `customer_email` varchar(150) NOT NULL,
    `category` varchar(60) NOT NULL,
    `topic` varchar(80) NOT NULL,
    `status` varchar(30) NOT NULL DEFAULT 'Open',
    `priority` varchar(20) NOT NULL DEFAULT 'Normal',
    `channel` varchar(20) NOT NULL DEFAULT 'Web Portal',
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    `embedding_json` json NULL,
    `embedding_model` varchar(64) NULL,
    `embedding_sha256` varchar(64) NULL,
    `embedded_at` datetime NULL,
    PRIMARY KEY (`ticket_id`)
);

SELECT
    ticket_id,
    ticket_code,
    subject,
    description,
    customer_name,
    customer_email,
    category,
    topic,
    status,
    priority,
    channel,
    created_at,
    updated_at,
    embedding_json,
    embedding_model,
    embedding_sha256,
    embedded_at,
    '' AS semantic_query
FROM support_tickets

if (!defined("DC_EMBEDDING_MODEL")) {
    define("DC_EMBEDDING_MODEL", "text-embedding-3-small");
    define("DC_EMBEDDING_DIMENSIONS", 1536);
}

$_SESSION["DEVCLUB_OPENAI_API_KEY"] = "YOUR_OPENAI_API_KEY";

if (!function_exists("dcTicketText")) {
    function dcTicketText($subject, $description)
    {
        return "Subject: " . (string)$subject .
            "\nDescription: " . (string)$description;
    }
}

if (!function_exists("dcEmbeddingVector")) {
    function dcEmbeddingVector($text)
    {
        $apiKey = trim((string)($_SESSION["DEVCLUB_OPENAI_API_KEY"] ?? ""));

        if ($apiKey === "" || $apiKey === "YOUR_OPENAI_API_KEY") {
            throw new RuntimeException(
                "OpenAI API key is missing from the session."
            );
        }

        if (!function_exists("curl_init")) {
            throw new RuntimeException("The PHP cURL extension is required.");
        }

        $request = json_encode(array(
            "model" => DC_EMBEDDING_MODEL,
            "input" => (string)$text,
            "encoding_format" => "float"
        ), JSON_INVALID_UTF8_SUBSTITUTE);

        if ($request === false) {
            throw new RuntimeException("Cannot create the OpenAI request.");
        }

        $ch = curl_init("https://api.openai.com/v1/embeddings");

        if ($ch === false) {
            throw new RuntimeException("Cannot initialize the OpenAI request.");
        }

        curl_setopt_array($ch, array(
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => $request,
            CURLOPT_HTTPHEADER => array(
                "Authorization: Bearer " . $apiKey,
                "Content-Type: application/json",
                "Accept: application/json"
            ),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CONNECTTIMEOUT => 15,
            CURLOPT_TIMEOUT => 45,
            CURLOPT_SSL_VERIFYPEER => true
        ));

        $response = curl_exec($ch);
        $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $curlError = curl_error($ch);
        curl_close($ch);

        if ($response === false) {
            throw new RuntimeException(
                "OpenAI connection failed: " . $curlError
            );
        }

        $body = json_decode($response, true);

        if ($httpCode !== 200) {
            $detail = isset($body["error"]["message"])
                ? (string)$body["error"]["message"]
                : "Unexpected HTTP response";
            throw new RuntimeException(
                "OpenAI HTTP " . $httpCode . ": " . substr($detail, 0, 300)
            );
        }

        $vector = $body["data"][0]["embedding"] ?? null;

        if (!is_array($vector) || count($vector) !== DC_EMBEDDING_DIMENSIONS) {
            throw new RuntimeException(
                "OpenAI returned an embedding with an unexpected length."
            );
        }

        foreach ($vector as $value) {
            if (!is_numeric($value) || !is_finite((float)$value)) {
                throw new RuntimeException(
                    "OpenAI returned an invalid embedding value."
                );
            }
        }

        return $vector;
    }
}

if (!function_exists("dcTicketEmbeddingFields")) {
    function dcTicketEmbeddingFields($subject, $description)
    {
        $text = dcTicketText($subject, $description);
        $vector = dcEmbeddingVector($text);
        $vectorJson = json_encode($vector, JSON_PRESERVE_ZERO_FRACTION);

        if ($vectorJson === false) {
            throw new RuntimeException("Cannot save the returned embedding.");
        }

        return array(
            "embedding_json" => $vectorJson,
            "embedding_model" => DC_EMBEDDING_MODEL,
            "embedding_sha256" => hash("sha256", $text),
            "embedded_at" => gmdate("Y-m-d H:i:s")
        );
    }
}

try {
    $subject = trim((string)($values["subject"] ?? ""));
    $description = trim((string)($values["description"] ?? ""));

    if ($subject === "" || $description === "") {
        throw new RuntimeException("Subject and description are required.");
    }

    $embeddingFields = dcTicketEmbeddingFields($subject, $description);

    foreach ($embeddingFields as $field => $value) {
        $values[$field] = $value;
    }
} catch (Throwable $e) {
    $message = "Ticket was not saved: " . $e->getMessage();
    return false;
}

return true;

try {
    $subject = trim((string)($values["subject"] ?? $oldvalues["subject"]));
    $description = trim((string)(
        $values["description"] ?? $oldvalues["description"]
    ));

    if ($subject === "" || $description === "") {
        throw new RuntimeException("Subject and description are required.");
    }

    $oldText = dcTicketText(
        $oldvalues["subject"],
        $oldvalues["description"]
    );
    $newText = dcTicketText($subject, $description);

    if ($newText !== $oldText) {
        $embeddingFields = dcTicketEmbeddingFields($subject, $description);

        foreach ($embeddingFields as $field => $value) {
            $values[$field] = $value;
        }
    }
} catch (Throwable $e) {
    $message = "Ticket was not saved: " . $e->getMessage();
    return false;
}

return true;

$srch = SearchClause::getSearchObject("support_tickets");
$phrase = trim((string)$srch->getAllFieldsSearchValue());

if ($phrase !== "") {
    $cacheKey = hash("sha256", DC_EMBEDDING_MODEL . "\n" . $phrase);
    $cached = $_SESSION["DC_SEMANTIC_CACHE"] ?? null;
    $ids = null;

    unset($_SESSION["DC_SEMANTIC_ERROR"]);

    $cacheValid = is_array($cached)
        && isset($cached["key"], $cached["time"], $cached["ids"])
        && $cached["key"] === $cacheKey;

    if ($cacheValid) {
        $cacheAge = time() - (int)$cached["time"];
        $cacheValid = $cacheAge >= 0 && !($cacheAge > 300);
    }

    if ($cacheValid) {
        $ids = $cached["ids"];
    }

    if ($ids === null) {
        try {
            if (strlen($phrase) > 1000) {
                throw new RuntimeException(
                    "Search phrase is too long. The limit is 1000 bytes."
                );
            }

            $queryVector = dcEmbeddingVector($phrase);
            $queryNorm = 0.0;

            foreach ($queryVector as $value) {
                $number = (float)$value;
                $queryNorm += $number * $number;
            }

            if ($queryNorm === 0.0) {
                throw new RuntimeException("The query vector has zero length.");
            }

            $sql = DB::PrepareSQL(
                "SELECT ticket_id, subject, description, " .
                "embedding_json, embedding_sha256 " .
                "FROM support_tickets " .
                "WHERE embedding_json IS NOT NULL " .
                "AND embedding_model=':1'",
                DC_EMBEDDING_MODEL
            );
            $rs = DB::Query($sql);

            if (!$rs) {
                throw new RuntimeException(
                    "Database query failed: " . DB::LastError()
                );
            }

            $scores = array();

            while ($row = $rs->fetchAssoc()) {
                $ticketText = dcTicketText(
                    $row["subject"],
                    $row["description"]
                );
                $expectedHash = hash("sha256", $ticketText);

                if (!hash_equals(
                    (string)$row["embedding_sha256"],
                    $expectedHash
                )) {
                    continue;
                }

                $vector = json_decode(
                    (string)$row["embedding_json"],
                    true
                );

                if (!is_array($vector)
                    || count($vector) !== DC_EMBEDDING_DIMENSIONS) {
                    continue;
                }

                $dotProduct = 0.0;
                $vectorNorm = 0.0;

                foreach ($vector as $index => $value) {
                    if (!is_numeric($value)) {
                        continue 2;
                    }

                    $number = (float)$value;

                    if (!is_finite($number)) {
                        continue 2;
                    }

                    $dotProduct += (float)$queryVector[$index] * $number;
                    $vectorNorm += $number * $number;
                }

                if ($vectorNorm > 0.0) {
                    $scores[(int)$row["ticket_id"]] =
                        $dotProduct / sqrt($queryNorm * $vectorNorm);
                }
            }

            arsort($scores, SORT_NUMERIC);
            $ids = array_slice(array_keys($scores), 0, 30);

            $_SESSION["DC_SEMANTIC_CACHE"] = array(
                "key" => $cacheKey,
                "time" => time(),
                "ids" => $ids
            );
        } catch (Throwable $e) {
            $_SESSION["DC_SEMANTIC_ERROR"] = $e->getMessage();
            $ids = array();
            unset($_SESSION["DC_SEMANTIC_CACHE"]);
        }
    }

    $safeIds = array_map("intval", $ids);
    $condition = $safeIds
        ? "ticket_id IN (" . implode(",", $safeIds) . ")"
        : "1=0";

    $srch->setSearchSQL("semantic_query", $condition);
}

$srch = SearchClause::getSearchObject("support_tickets");
$phrase = trim((string)$srch->getAllFieldsSearchValue());
$cached = $_SESSION["DC_SEMANTIC_CACHE"] ?? null;
$cacheKey = hash("sha256", DC_EMBEDDING_MODEL . "\n" . $phrase);

$cacheValid = $phrase !== ""
    && is_array($cached)
    && isset($cached["key"], $cached["time"], $cached["ids"])
    && $cached["key"] === $cacheKey
    && !empty($cached["ids"]);

if ($cacheValid) {
    $cacheAge = time() - (int)$cached["time"];
    $cacheValid = $cacheAge >= 0 && !($cacheAge > 300);
}

if ($cacheValid) {
    $safeIds = array_map("intval", $cached["ids"]);
    $strOrderBy = " ORDER BY FIELD(ticket_id, " .
        implode(",", $safeIds) . ")";
}

if (!empty($_SESSION["DC_SEMANTIC_ERROR"])) {
    $message = "Semantic search failed: " .
        $_SESSION["DC_SEMANTIC_ERROR"];

    $xt->assign(
        "message",
        htmlspecialchars(
            $message,
            ENT_QUOTES | ENT_SUBSTITUTE,
            "UTF-8"
        )
    );
}

CREATE TABLE employees (
    EmployeeID INT NOT NULL AUTO_INCREMENT,
    LastName VARCHAR(20) NOT NULL,
    FirstName VARCHAR(10) NOT NULL,
    Title VARCHAR(30) NULL,
    TitleOfCourtesy VARCHAR(25) NULL,
    BirthDate DATETIME NULL,
    HireDate DATETIME NULL,
    Address VARCHAR(60) NULL,
    City VARCHAR(11) NULL,
    Region VARCHAR(15) NULL,
    PostalCode VARCHAR(10) NULL,
    Country VARCHAR(15) NULL,
    HomePhone VARCHAR(24) NULL,
    Extension VARCHAR(4) NULL,
    Photo LONGBLOB NULL,
    Notes LONGTEXT NULL,
    ReportsTo DATETIME NULL,
    ownerid INT NULL,
    email VARCHAR(250) NULL,
    PRIMARY KEY (EmployeeID)
);

CREATE TABLE videos (
    video_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    video_file LONGTEXT NULL,
    duration INT NULL,
    PRIMARY KEY (video_id)
);

CREATE TABLE video_watch (
    watch_id INT NOT NULL AUTO_INCREMENT,
    employee_id INT NOT NULL,
    video_id INT NOT NULL,
    last_position DECIMAL(10,2) NOT NULL DEFAULT 0,
    max_watched DECIMAL(10,2) NOT NULL DEFAULT 0,
    completed TINYINT NOT NULL DEFAULT 0,
    last_watched DATETIME NULL,
    completed_at DATETIME NULL,
    PRIMARY KEY (watch_id),
    UNIQUE KEY uq_video_watch (employee_id, video_id)
);

if (
    $_SERVER["REQUEST_METHOD"] === "POST" &&
    in_array(
        $_POST["action"] ?? "",
        array(
            "get_video_progress",
            "save_video_progress",
            "save_video_duration"
        ),
        true
    )
) {
    header("Content-Type: application/json");

    function videoResponse($data, $status = 200) {
        http_response_code($status);
        echo json_encode($data);
        exit;
    }

    if (!Security::isLoggedIn()) {
        videoResponse(array("success" => false), 401);
    }

    $user = Security::currentUserData();
    $employeeId = (int)($user["EmployeeID"] ?? 0);
    $videoId = filter_var(
        $_POST["video_id"] ?? null,
        FILTER_VALIDATE_INT
    );

    if ($employeeId <= 0 || !$videoId || $videoId <= 0) {
        videoResponse(array("success" => false), 400);
    }

    $video = DB::Select(
        "videos",
        array("video_id" => $videoId)
    )->fetchAssoc();

    if (!$video) {
        videoResponse(array("success" => false), 404);
    }

    $keys = array(
        "employee_id" => $employeeId,
        "video_id" => $videoId
    );

    $watch = DB::Select("video_watch", $keys)->fetchAssoc();

    if (!$watch) {
        DB::Insert("video_watch", array(
            "employee_id" => $employeeId,
            "video_id" => $videoId,
            "last_position" => 0,
            "max_watched" => 0,
            "completed" => 0
        ));

        $watch = DB::Select("video_watch", $keys)->fetchAssoc();
    }

    $action = $_POST["action"];

    if ($action === "get_video_progress") {
        if (empty($_SESSION["video_progress_token"])) {
            $_SESSION["video_progress_token"] =
                bin2hex(random_bytes(32));
        }

        videoResponse(array(
            "success" => true,
            "last_position" => (float)$watch["last_position"],
            "max_watched" => (float)$watch["max_watched"],
            "completed" => (int)$watch["completed"],
            "token" => $_SESSION["video_progress_token"]
        ));
    }

    $token = $_POST["token"] ?? "";

    if (
        !is_string($token) ||
        empty($_SESSION["video_progress_token"]) ||
        !hash_equals($_SESSION["video_progress_token"], $token)
    ) {
        videoResponse(array("success" => false), 403);
    }

    if ($action === "save_video_duration") {
        $duration = filter_var(
            $_POST["duration"] ?? null,
            FILTER_VALIDATE_INT
        );

        if (!$duration || $duration < 1 || $duration > 86400) {
            videoResponse(array("success" => false), 400);
        }

        if (empty($video["duration"])) {
            DB::Update(
                "videos",
                array("duration" => $duration),
                array("video_id" => $videoId)
            );
        }

        videoResponse(array("success" => true));
    }

    $position = filter_var(
        $_POST["position"] ?? null,
        FILTER_VALIDATE_FLOAT
    );
    $maxWatched = filter_var(
        $_POST["max_watched"] ?? null,
        FILTER_VALIDATE_FLOAT
    );

    if (
        $position === false || $position === null ||
        $maxWatched === false || $maxWatched === null ||
        $position < 0 || $maxWatched < 0 ||
        $position > $maxWatched + 0.5
    ) {
        videoResponse(array("success" => false), 400);
    }

    $duration = (float)($video["duration"] ?? 0);

    if (
        $duration > 0 &&
        ($position > $duration + 1 || $maxWatched > $duration + 1)
    ) {
        videoResponse(array("success" => false), 400);
    }

    $maxWatched = max(
        (float)$watch["max_watched"],
        $maxWatched
    );

    $values = array(
        "last_position" => $position,
        "max_watched" => $maxWatched,
        "last_watched" => date("Y-m-d H:i:s")
    );

    if (
        $duration > 0 &&
        $maxWatched >= $duration - 1.5 &&
        ($_POST["ended"] ?? "") === "1"
    ) {
        $values["completed"] = 1;

        if (empty($watch["completed"])) {
            $values["completed_at"] = date("Y-m-d H:i:s");
        }
    }

    DB::Update("video_watch", $values, $keys);
    videoResponse(array("success" => true));
}

string action = MVCFunctions.postvalue("action").ToString();

if (
    HttpContext.Current.Request.HttpMethod == "POST" &&
    (
        action == "get_video_progress" ||
        action == "save_video_progress" ||
        action == "save_video_duration"
    )
)
{
    Action<XVar, int> videoResponse = (payload, status) =>
    {
        HttpContext.Current.Response.StatusCode = status;
        HttpContext.Current.Response.ContentType = "application/json";
        MVCFunctions.Echo(MVCFunctions.runner_json_encode(payload));
        HttpContext.Current.Response.End();
        throw new RunnerInlineOutputException();
    };

    if (!XVar.Pack(Security.isLoggedIn()))
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 401);
    }

    XVar user = Security.currentUserData();
    int employeeId = 0;
    int videoId = 0;

    Int32.TryParse(user["EmployeeID"].ToString(), out employeeId);
    Int32.TryParse(
        MVCFunctions.postvalue("video_id").ToString(),
        out videoId
    );

    if (employeeId <= 0 || videoId <= 0)
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 400);
    }

    XVar videoKeys = XVar.Array();
    videoKeys["video_id"] = videoId;

    dynamic video = DB.Select("videos", videoKeys).fetchAssoc();

    if (!XVar.Pack(video))
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 404);
    }

    XVar keys = XVar.Array();
    keys["employee_id"] = employeeId;
    keys["video_id"] = videoId;

    dynamic watch = DB.Select("video_watch", keys).fetchAssoc();

    if (!XVar.Pack(watch))
    {
        XVar values = XVar.Array();
        values["employee_id"] = employeeId;
        values["video_id"] = videoId;
        values["last_position"] = 0;
        values["max_watched"] = 0;
        values["completed"] = 0;

        DB.Insert("video_watch", values);
        watch = DB.Select("video_watch", keys).fetchAssoc();
    }

    if (action == "get_video_progress")
    {
        string sessionToken =
            XSession.Session["video_progress_token"].ToString();

        if (String.IsNullOrEmpty(sessionToken))
        {
            sessionToken = Guid.NewGuid().ToString("N");
            XSession.Session["video_progress_token"] = sessionToken;
        }

        XVar response = XVar.Array();
        response["success"] = true;
        response["last_position"] =
            Convert.ToDouble(watch["last_position"]);
        response["max_watched"] =
            Convert.ToDouble(watch["max_watched"]);
        response["completed"] =
            Convert.ToInt32(watch["completed"]);
        response["token"] = sessionToken;

        videoResponse(response, 200);
    }

    string savedToken =
        XSession.Session["video_progress_token"].ToString();
    string suppliedToken =
        MVCFunctions.postvalue("token").ToString();

    if (
        String.IsNullOrEmpty(savedToken) ||
        !String.Equals(
            savedToken,
            suppliedToken,
            StringComparison.Ordinal
        )
    )
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 403);
    }

    if (action == "save_video_duration")
    {
        int durationValue = 0;
        Int32.TryParse(
            MVCFunctions.postvalue("duration").ToString(),
            out durationValue
        );

        if (durationValue < 1 || durationValue > 86400)
        {
            XVar response = XVar.Array();
            response["success"] = false;
            videoResponse(response, 400);
        }

        if (
            String.IsNullOrEmpty(video["duration"].ToString()) ||
            Convert.ToInt32(video["duration"]) == 0
        )
        {
            XVar values = XVar.Array();
            values["duration"] = durationValue;
            DB.Update("videos", values, videoKeys);
        }

        XVar response = XVar.Array();
        response["success"] = true;
        videoResponse(response, 200);
    }

    double position = 0;
    double maxWatched = 0;

    bool validPosition = Double.TryParse(
        MVCFunctions.postvalue("position").ToString(),
        System.Globalization.NumberStyles.Float,
        System.Globalization.CultureInfo.InvariantCulture,
        out position
    );
    bool validMaximum = Double.TryParse(
        MVCFunctions.postvalue("max_watched").ToString(),
        System.Globalization.NumberStyles.Float,
        System.Globalization.CultureInfo.InvariantCulture,
        out maxWatched
    );

    if (
        !validPosition || !validMaximum ||
        position < 0 || maxWatched < 0 ||
        position > maxWatched + 0.5
    )
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 400);
    }

    double duration = 0;
    Double.TryParse(video["duration"].ToString(), out duration);

    if (
        duration > 0 &&
        (position > duration + 1 || maxWatched > duration + 1)
    )
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 400);
    }

    maxWatched = Math.Max(
        Convert.ToDouble(watch["max_watched"]),
        maxWatched
    );

    XVar progress = XVar.Array();
    progress["last_position"] = position;
    progress["max_watched"] = maxWatched;
    progress["last_watched"] = DateTime.Now;

    if (
        duration > 0 &&
        maxWatched >= duration - 1.5 &&
        MVCFunctions.postvalue("ended").ToString() == "1"
    )
    {
        progress["completed"] = 1;

        if (Convert.ToInt32(watch["completed"]) == 0)
        {
            progress["completed_at"] = DateTime.Now;
        }
    }

    DB.Update("video_watch", progress, keys);

    XVar successResponse = XVar.Array();
    successResponse["success"] = true;
    videoResponse(successResponse, 200);
}

const video = document.querySelector("video");

if (video) {
    const params = new URLSearchParams(window.location.search);
    const videoId = params.get("editid1");
    const url = window.location.pathname + window.location.search;

    let durationSaved = false;
    let lastPosition = 0;
    let maxWatched = 0;
    let restoringPosition = false;
    let initialized = false;
    let completed = false;
    let token = "";
    let lastSave = 0;
    let saving = false;
    let pendingSave = false;

    function request(action, data = {}) {
        return $.ajax({
            url: url,
            type: "POST",
            dataType: "json",
            data: {
                action: action,
                video_id: videoId,
                ...data
            }
        });
    }

    function loadProgress() {
        video.pause();

        request("get_video_progress")
            .done(function(result) {
                if (!result.success) return;

                lastPosition = Number(result.last_position) || 0;
                maxWatched = Number(result.max_watched) || 0;
                completed = Number(result.completed) === 1;
                token = result.token || "";

                if (completed && Number.isFinite(video.duration)) {
                    maxWatched = video.duration;
                }

                restoringPosition = true;
                video.currentTime = Math.min(
                    lastPosition,
                    Math.max(0, video.duration - 0.25)
                );

                video.addEventListener(
                    "seeked",
                    function finishRestore() {
                        restoringPosition = false;
                        video.removeEventListener(
                            "seeked",
                            finishRestore
                        );
                    }
                );

                initialized = true;
            });
    }

    function saveVideoDuration() {
        if (durationSaved || !videoId || !token) return;

        const duration = Math.ceil(video.duration);

        if (!Number.isFinite(duration) || duration <= 0) return;

        durationSaved = true;

        request("save_video_duration", {
            duration: duration,
            token: token
        }).fail(function() {
            durationSaved = false;
        });
    }

    function saveProgress(ended = false) {
        if (!initialized || !token) return;

        if (saving) {
            pendingSave = true;
            return;
        }

        saving = true;

        const position = Math.max(
            0,
            Math.min(video.currentTime, maxWatched)
        );

        request("save_video_progress", {
            position: position.toFixed(2),
            max_watched: maxWatched.toFixed(2),
            ended: ended ? "1" : "0",
            token: token
        }).always(function() {
            saving = false;

            if (pendingSave) {
                pendingSave = false;
                saveProgress(video.ended);
            }
        });
    }

    function initialize() {
        if (initialized) return;
        if (!Number.isFinite(video.duration)) return;
        loadProgress();
    }

    video.addEventListener("loadedmetadata", initialize);

    if (video.readyState >= 1) {
        initialize();
    }

    video.addEventListener("play", function() {
        if (!initialized) {
            video.pause();
            return;
        }

        saveVideoDuration();
    });

    video.addEventListener("timeupdate", function() {
        if (!initialized || restoringPosition || video.seeking) {
            return;
        }

        const current = video.currentTime;

        if (
            !video.paused &&
            current > maxWatched &&
            current <= maxWatched + 2
        ) {
            maxWatched = current;
        }

        const now = Date.now();

        if (!video.paused && now - lastSave >= 5000) {
            lastSave = now;
            saveProgress();
        }
    });

    video.addEventListener("seeking", function() {
        if (!initialized || restoringPosition) return;

        if (video.currentTime > maxWatched + 0.5) {
            restoringPosition = true;
            video.currentTime = maxWatched;
        }
    });

    video.addEventListener("seeked", function() {
        if (restoringPosition) {
            restoringPosition = false;
        }
    });

    video.addEventListener("pause", function() {
        if (initialized && !video.ended) {
            saveProgress();
        }
    });

    video.addEventListener("ended", function() {
        maxWatched = Math.max(maxWatched, video.duration);
        completed = true;
        saveProgress(true);
    });
}

$user = Security::currentUserData();

$keys = array(
    "employee_id" => (int)$user["EmployeeID"],
    "video_id" => (int)$data["video_id"]
);

$watch = DB::Select("video_watch", $keys)->fetchAssoc();

if (!$watch || (float)$watch["max_watched"] <= 0) {
    $record["css"] = "background:#fce8e6;";
} elseif ((int)$watch["completed"] === 1) {
    $record["css"] = "background:#e6f4ea;";
} else {
    $record["css"] = "background:#fff4cc;";
}

XVar user = Security.currentUserData();

XVar keys = XVar.Array();
keys["employee_id"] = Convert.ToInt32(user["EmployeeID"]);
keys["video_id"] = Convert.ToInt32(data["video_id"]);

dynamic watch = DB.Select("video_watch", keys).fetchAssoc();

if (
    !XVar.Pack(watch) ||
    Convert.ToDouble(watch["max_watched"]) <= 0
)
{
    record["css"] = "background:#fce8e6;";
}
else if (Convert.ToInt32(watch["completed"]) == 1)
{
    record["css"] = "background:#e6f4ea;";
}
else
{
    record["css"] = "background:#fff4cc;";
}

CREATE TABLE assets (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    asset_tag VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(100),
    serial_number VARCHAR(100),
    location VARCHAR(150),
    status VARCHAR(30) NOT NULL DEFAULT 'Available',
    `condition` VARCHAR(30) DEFAULT 'Good',
    notes TEXT
);

CREATE TABLE employees (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(255),
    department VARCHAR(100),
    active TINYINT NOT NULL DEFAULT 1
);

CREATE TABLE asset_checkouts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    asset_id INT NOT NULL,
    employee_id INT NOT NULL,
    checked_out_at DATETIME,
    due_date DATE,
    returned_at DATETIME,
    condition_out VARCHAR(30),
    condition_in VARCHAR(30),
    notes TEXT
);

SELECT
    a.id,
    a.asset_tag,
    a.name,
    a.category,
    a.serial_number,
    a.location,
    a.status,
    a.`condition`,
    a.notes,
    ac.due_date,
    ac.employee_id
FROM assets a
LEFT JOIN asset_checkouts ac
    ON ac.id = (
        SELECT MAX(ac2.id)
        FROM asset_checkouts ac2
        WHERE ac2.asset_id = a.id
          AND ac2.returned_at IS NULL
    )

$record = $button->getCurrentRecord();

if (!$record || $record["status"] !== "Available") {
    $result["error"] = "This equipment is no longer available.";
    return;
}

$result["asset_id"] = $record["id"];

dynamic record = button.getCurrentRecord();

if (!XVar.Pack(record) || Convert.ToString(record["status"]) != "Available")
{
    result["error"] = "This equipment is no longer available.";
    return;
}

result["asset_id"] = record["id"];

if (result["error"]) {
    swal(result["error"]);
    return;
}

window.popup = Runner.displayPopup({
    url: Runner.pages.getUrl("asset_checkouts", "add")
        + "?mastertable=assets&masterkey1="
        + encodeURIComponent(result["asset_id"]),
    width: 650,
    height: 700,
    header: "Check Out Equipment"
});

$asset = $pageObject->getMasterRecord();

if (!$asset || $asset["status"] !== "Available") {
    $message = "This equipment is no longer available.";
    return false;
}

$values["asset_id"] = $asset["id"];
$values["checked_out_at"] = date("Y-m-d H:i:s");
$values["condition_out"] = $asset["condition"];
$values["returned_at"] = null;
$values["condition_in"] = null;

return true;

dynamic asset = pageObject.getMasterRecord();

if (!XVar.Pack(asset) || Convert.ToString(asset["status"]) != "Available")
{
    message = "This equipment is no longer available.";
    return false;
}

values["asset_id"] = asset["id"];
values["checked_out_at"] = DateTime.Now;
values["condition_out"] = asset["condition"];
values["returned_at"] = null;
values["condition_in"] = null;

return true;

DB::Update(
    "assets",
    array("status" => "Checked Out"),
    array("id" => $values["asset_id"])
);

$pageObject->stopPRG = true;
$pageObject->setProxyValue("checkoutSaved", true);

dynamic data = XVar.Array();
dynamic keyValues = XVar.Array();

data["status"] = "Checked Out";
keyValues["id"] = values["asset_id"];
DB.Update("assets", data, keyValues);

pageObject.stopPRG = true;
pageObject.setProxyValue("checkoutSaved", true);

if (proxy.checkoutSaved && window.parent && window.parent.popup) {
    window.parent.location.reload();
    window.parent.popup.close();
}

$asset = $button->getCurrentRecord();

if (!$asset || $asset["status"] !== "Checked Out") {
    $result["error"] = "This equipment is not checked out.";
    return;
}

$where = DB::PrepareSQL(
    "asset_id = :1 AND returned_at IS NULL",
    $asset["id"]
);
$rs = DB::Select("asset_checkouts", $where);
$checkout = $rs->fetchAssoc();

if (!$checkout) {
    $result["error"] = "The active checkout record was not found.";
    return;
}

$result["checkout_id"] = $checkout["id"];

dynamic asset = button.getCurrentRecord();

if (!XVar.Pack(asset) || Convert.ToString(asset["status"]) != "Checked Out")
{
    result["error"] = "This equipment is not checked out.";
    return;
}

string where = DB.PrepareSQL(
    "asset_id = :1 AND returned_at IS NULL",
    asset["id"]
);
dynamic rs = DB.Select("asset_checkouts", where);
dynamic checkout = rs.fetchAssoc();

if (!XVar.Pack(checkout))
{
    result["error"] = "The active checkout record was not found.";
    return;
}

result["checkout_id"] = checkout["id"];

if (result["error"]) {
    swal(result["error"]);
    return;
}

window.popup = Runner.displayPopup({
    url: Runner.pages.getUrl("asset_checkouts", "edit")
        + "?editid1="
        + encodeURIComponent(result["checkout_id"]),
    width: 650,
    height: 500,
    header: "Return Equipment"
});

if (!empty($oldvalues["returned_at"])) {
    $message = "This equipment has already been returned.";
    return false;
}

$values["returned_at"] = date("Y-m-d H:i:s");
return true;

if (XVar.Pack(oldvalues["returned_at"]))
{
    message = "This equipment has already been returned.";
    return false;
}

values["returned_at"] = DateTime.Now;
return true;

$status = $values["condition_in"] === "Damaged"
    ? "Maintenance"
    : "Available";

DB::Update(
    "assets",
    array(
        "status" => $status,
        "condition" => $values["condition_in"]
    ),
    array("id" => $oldvalues["asset_id"])
);

$pageObject->stopPRG = true;
$pageObject->setProxyValue("returnSaved", true);

string status = Convert.ToString(values["condition_in"]) == "Damaged"
    ? "Maintenance"
    : "Available";

dynamic data = XVar.Array();
dynamic keyValues = XVar.Array();
data["status"] = status;
data["condition"] = values["condition_in"];
keyValues["id"] = oldvalues["asset_id"];
DB.Update("assets", data, keyValues);

pageObject.stopPRG = true;
pageObject.setProxyValue("returnSaved", true);

if (proxy.returnSaved && window.parent && window.parent.popup) {
    window.parent.location.reload();
    window.parent.popup.close();
}

if ($data["status"] !== "Available") {
    $pageObject->hideItem("checkout_button", $recordId);
}

if ($data["status"] !== "Checked Out") {
    $pageObject->hideItem("return_button", $recordId);
}

if (
    !empty($data["due_date"])
    && strtotime($data["due_date"]) < strtotime(date("Y-m-d"))
) {
    $record["css"] = "background: #ffdddd;";
}
[/php]
<p><strong>C#</strong></p>
[code]
if (Convert.ToString(data["status"]) != "Available")
{
    pageObject.hideItem("checkout_button", recordId);
}

if (Convert.ToString(data["status"]) != "Checked Out")
{
    pageObject.hideItem("return_button", recordId);
}

DateTime dueDate;
if (
    DateTime.TryParse(Convert.ToString(data["due_date"]), out dueDate)
    && dueDate.Date < DateTime.Today
)
{
    record["css"] = "background: #ffdddd;";
}
[/code]
<p>On <strong>Assets View page - Before display</strong>, hide the buttons without a record ID.</p>
<p><strong>PHP</strong></p>
[php]
$asset = $pageObject->getCurrentRecord();

if ($asset["status"] !== "Available") {
    $pageObject->hideItem("checkout_button");
}

if ($asset["status"] !== "Checked Out") {
    $pageObject->hideItem("return_button");
}

dynamic asset = pageObject.getCurrentRecord();

if (Convert.ToString(asset["status"]) != "Available")
{
    pageObject.hideItem("checkout_button");
}

if (Convert.ToString(asset["status"]) != "Checked Out")
{
    pageObject.hideItem("return_button");
}

function openAssetScanner() {
    var scanner;
    var matched = false;
    var closing = false;

    Runner.displayPopup({
        html: '<div id="asset_qr_reader" style="width:100%;"></div>',
        width: 600,
        height: 650,
        header: "Scan Equipment Label",

        afterCreate: function(popup) {
            scanner = new Html5QrcodeScanner(
                "asset_qr_reader",
                {
                    fps: 10,
                    qrbox: { width: 250, height: 250 },
                    formatsToSupport: [
                        Html5QrcodeSupportedFormats.QR_CODE
                    ],
                    supportedScanTypes: [
                        Html5QrcodeScanType.SCAN_TYPE_CAMERA
                    ]
                },
                false
            );

            scanner.render(function(decodedText) {
                if (matched || closing) {
                    return;
                }

                matched = true;
                params["asset_tag"] = decodedText.trim();
                popup.close();
            });
        },

        beforeClose: function(popup) {
            if (closing) {
                return true;
            }

            closing = true;

            scanner.clear().then(function() {
                popup.close();

                if (matched) {
                    ajax.submit();
                }
            }).catch(function() {
                closing = false;
                matched = false;
                swal("Stop scanning before closing the window.");
            });

            return false;
        }
    });
}

if (window.Html5QrcodeScanner) {
    openAssetScanner();
} else {
    $.getScript(
        "https://unpkg.com/html5-qrcode@2.3.8/html5-qrcode.min.js"
    ).done(openAssetScanner).fail(function() {
        swal("The QR scanner library could not be loaded.");
    });
}

return false;

$tag = trim((string)$params["asset_tag"]);
$rs = DB::Select("assets", array("asset_tag" => $tag));
$asset = $rs->fetchAssoc();

if (!$asset) {
    $result["error"] = "No equipment matches this QR code.";
    return;
}

$result["asset_id"] = $asset["id"];

string tag = Convert.ToString(parameters["asset_tag"]).Trim();
dynamic values = XVar.Array();
values["asset_tag"] = tag;

dynamic rs = DB.Select("assets", values);
dynamic asset = rs.fetchAssoc();

if (!XVar.Pack(asset))
{
    result["error"] = "No equipment matches this QR code.";
    return;
}

result["asset_id"] = asset["id"];

if (result["error"]) {
    swal(result["error"]);
    return;
}

location.href = Runner.pages.getUrl("assets", "view")
    + "?editid1="
    + encodeURIComponent(result["asset_id"]);

CREATE TABLE exports (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME NOT NULL,
    date_from DATE NULL,
    date_to DATE NULL,
    status VARCHAR(20) NOT NULL,
    total_records INT NOT NULL DEFAULT 0,
    processed_records INT NOT NULL DEFAULT 0,
    filename VARCHAR(255) NULL,
    completed_at DATETIME NULL,
    task_type VARCHAR(50) NOT NULL
);

$allowedTypes = array("orders", "transactions");

if (!in_array($values["task_type"], $allowedTypes, true)) {
    $message = "Select a valid export type.";
    return false;
}

$values["created_at"] = date("Y-m-d H:i:s");
$values["status"] = "Pending";
$values["total_records"] = 0;
$values["processed_records"] = 0;
$values["filename"] = null;
$values["completed_at"] = null;

return true;

<?php

if (PHP_SAPI !== "cli") {
    http_response_code(403);
    exit("This worker must be run from the command line.");
}

require_once __DIR__ . "/include/dbcommon.php";

set_time_limit(300);

$recordsPerRun = 100000;
$progressUpdateInterval = 1000;

$exportTypes = array(
    "orders" => array(
        "table" => "orders",
        "dateField" => "order_date",
        "headers" => array(
            "Order ID",
            "Order Date",
            "Customer",
            "Amount"
        ),
        "fields" => array(
            "id",
            "order_date",
            "customer",
            "amount"
        )
    ),
    "transactions" => array(
        "table" => "transactions",
        "dateField" => "transaction_date",
        "headers" => array(
            "Transaction ID",
            "Transaction Date",
            "Customer Name",
            "Customer Email",
            "Transaction Type",
            "Amount",
            "Status",
            "Reference"
        ),
        "fields" => array(
            "id",
            "transaction_date",
            "customer_name",
            "customer_email",
            "transaction_type",
            "amount",
            "status",
            "reference"
        )
    )
);

$rs = DB::Query(
    "SELECT * FROM exports " .
    "WHERE status IN ('Pending', 'Processing') " .
    "ORDER BY id LIMIT 1"
);
$export = $rs->fetchAssoc();

if (!$export) {
    echo "No exports to process.\n";
    exit;
}

$exportId = (int)$export["id"];
$taskType = trim((string)$export["task_type"]);

if (!isset($exportTypes[$taskType])) {
    DB::Update(
        "exports",
        array("status" => "Failed"),
        array("id" => $exportId)
    );
    exit("Invalid task type.\n");
}

$definition = $exportTypes[$taskType];
$sourceTable = $definition["table"];
$dateField = $definition["dateField"];
$alreadyProcessed = (int)$export["processed_records"];

DB::Update(
    "exports",
    array("status" => "Processing"),
    array("id" => $exportId)
);

$where = array();

if (!empty($export["date_from"])) {
    $where[] = DB::PrepareSQL(
        $dateField . " >= ':1'",
        $export["date_from"] . " 00:00:00"
    );
}

if (!empty($export["date_to"])) {
    $where[] = DB::PrepareSQL(
        $dateField . " <= ':1'",
        $export["date_to"] . " 23:59:59"
    );
}

$whereSql = count($where)
    ? " WHERE " . implode(" AND ", $where)
    : "";

if ($alreadyProcessed === 0) {
    $countRs = DB::Query(
        "SELECT COUNT(*) AS cnt FROM " . $sourceTable . $whereSql
    );
    $countData = $countRs->fetchAssoc();
    $totalRecords = (int)$countData["cnt"];

    DB::Update(
        "exports",
        array("total_records" => $totalRecords),
        array("id" => $exportId)
    );
} else {
    $totalRecords = (int)$export["total_records"];
}

$exportFolder = getabspath("exports");

if (!is_dir($exportFolder) && !mkdir($exportFolder, 0755, true)) {
    DB::Update(
        "exports",
        array("status" => "Failed"),
        array("id" => $exportId)
    );
    exit("Cannot create the exports folder.\n");
}

$csvFilename = "export_" . $exportId . ".csv";
$csvPath = $exportFolder . DIRECTORY_SEPARATOR . $csvFilename;
$file = fopen($csvPath, $alreadyProcessed === 0 ? "w" : "a");

if (!$file) {
    DB::Update(
        "exports",
        array("status" => "Failed"),
        array("id" => $exportId)
    );
    exit("Cannot open the export file.\n");
}

if ($alreadyProcessed === 0) {
    fputcsv($file, $definition["headers"]);
}

$sql = "SELECT * FROM " . $sourceTable . $whereSql;
$sql .= " ORDER BY id";
$sql .= " LIMIT " . (int)$recordsPerRun;
$sql .= " OFFSET " . (int)$alreadyProcessed;

$rs = DB::Query($sql);
$processedThisRun = 0;

while ($row = $rs->fetchAssoc()) {
    $csvRow = array();

    foreach ($definition["fields"] as $field) {
        $csvRow[] = $row[$field];
    }

    fputcsv($file, $csvRow);
    $processedThisRun++;

    if ($processedThisRun % $progressUpdateInterval === 0) {
        DB::Update(
            "exports",
            array(
                "processed_records" =>
                    $alreadyProcessed + $processedThisRun
            ),
            array("id" => $exportId)
        );
    }
}

fclose($file);

$totalProcessed = $alreadyProcessed + $processedThisRun;
$update = array("processed_records" => $totalProcessed);

if ($totalProcessed >= $totalRecords) {
    $zipFilename = "export_" . $exportId . "_"
        . bin2hex(random_bytes(12)) . ".zip";
    $zipPath = $exportFolder . DIRECTORY_SEPARATOR . $zipFilename;
    $zip = new ZipArchive();

    if (
        $zip->open(
            $zipPath,
            ZipArchive::CREATE | ZipArchive::OVERWRITE
        ) !== true
    ) {
        $update["status"] = "Failed";
        DB::Update("exports", $update, array("id" => $exportId));
        exit("Cannot create the ZIP file.\n");
    }

    $zip->addFile($csvPath, $csvFilename);
    $zip->close();

    if (!file_exists($zipPath)) {
        $update["status"] = "Failed";
        DB::Update("exports", $update, array("id" => $exportId));
        exit("The ZIP file was not created.\n");
    }

    unlink($csvPath);
    $update["status"] = "Completed";
    $update["filename"] = $zipFilename;
    $update["completed_at"] = date("Y-m-d H:i:s");
} else {
    $update["status"] = "Processing";
}

DB::Update("exports", $update, array("id" => $exportId));

echo "Processed " . $processedThisRun . " records.\n";

* * * * * /usr/bin/php /path/to/application/cron.php

setInterval(function() {
    if ($("div.modal-dialog").length) {
        return;
    }

    location.reload();
}, 10000);

if ($data["status"] === "Completed" && $value) {
    $filename = basename($value);
    $url = "exports/" . rawurlencode($filename);
    $caption = htmlspecialchars(
        "Download",
        ENT_QUOTES,
        "UTF-8"
    );

    $value = '<a href="' . $url . '" download>'
        . $caption
        . '</a>';
} else {
    $value = "";
}

$(".phprunner-row-actions-menu").remove();

var btn = pageObj.getItemButton("row_actions", rowData.id);

var menu = $(
    '<div class="phprunner-row-actions-menu">' +
        '<div class="row-action" data-action="view">View</div>' +
        '<div class="row-action" data-action="edit">Edit</div>' +
        '<div class="row-action" data-action="orders">View orders</div>' +
        '<div class="row-action" data-action="delete">Delete</div>' +
        '<div class="row-action" data-action="copy">Create a copy</div>' +
        '<div class="row-action" data-action="archive">Archive</div>' +
    '</div>'
);

$("body").append(menu);

var pos = btn.offset();
var top = pos.top;
var left = pos.left + btn.outerWidth() + 4;

if (
    pos.top - $(window).scrollTop() + menu.outerHeight()
        > $(window).height()
) {
    top = pos.top - menu.outerHeight() + btn.outerHeight();
}

if (
    left + menu.outerWidth()
        > $(window).scrollLeft() + $(window).width()
) {
    left = pos.left - menu.outerWidth() - 4;
}

menu.css({
    top: top,
    left: left
});

menu.on("click", ".row-action", function(event) {
    event.stopPropagation();

    var action = $(this).data("action");
    menu.remove();

    switch (action) {
        case "view":
            Runner.displayPopup({
                url: Runner.pages.getUrl("customers", "view")
                    + "?editid1="
                    + encodeURIComponent(rowData.keys[0]),
                width: 800,
                height: 600,
                header: "View customer"
            });
            break;

        case "edit":
            pageObj.getItemButton(
                "grid_edit",
                rowData.id
            ).click();
            break;

        case "orders":
            var url = Runner.pages.getUrl("orders", "list");
            url += "?masterkey1="
                + encodeURIComponent(
                    row.getFieldValue("CustomerID")
                )
                + "&mastertable=customers";
            location.href = url;
            break;

        case "delete":
            swal("Would you like to delete this record?", {
                buttons: {
                    cancel: "No",
                    proceed: {
                        text: "Yes, delete it",
                        value: "proceed"
                    }
                }
            }).then(function(value) {
                if (value === "proceed") {
                    params["action"] = "delete";
                    submit();
                }
            });
            break;

        case "copy":
            location.href = Runner.pages.getUrl(
                "customers",
                "add"
            ) + "?copyid1="
                + encodeURIComponent(rowData.keys[0]);
            break;

        case "archive":
            // Add the code that archives the current record here.
            break;
    }
});

var closeTimer;

menu.on("mouseleave", function() {
    closeTimer = setTimeout(function() {
        menu.remove();
    }, 300);
});

menu.on("mouseenter", function() {
    clearTimeout(closeTimer);
});

return false;

$result["action"] = "";

if ($params["action"] === "delete") {
    $rights = Security::getPermissions("customers");

    if (empty($rights["D"])) {
        $result["message"] =
            "You do not have permission to delete this record.";
    } else {
        $record = $button->getCurrentRecord();

        if (!$record) {
            $result["message"] = "The record could not be found.";
        } else {
            $sql = DB::PrepareSQL(
                "DELETE FROM customers WHERE id=:1",
                $record["id"]
            );

            if (DB::Exec($sql)) {
                $result["action"] = "delete";
            } else {
                $result["message"] =
                    "The record could not be deleted.";
            }
        }
    }
}

result["action"] = "";

if (Convert.ToString(parameters["action"]) == "delete")
{
    dynamic rights = Security.getPermissions("customers");

    if (!XVar.Pack(rights["D"]))
    {
        result["message"] =
            "You do not have permission to delete this record.";
    }
    else
    {
        dynamic record = button.getCurrentRecord();

        if (!XVar.Pack(record))
        {
            result["message"] = "The record could not be found.";
        }
        else
        {
            dynamic sql = DB.PrepareSQL(
                "DELETE FROM customers WHERE id=:1",
                record["id"]
            );

            if (XVar.Pack(DB.Exec(sql)))
            {
                result["action"] = "delete";
            }
            else
            {
                result["message"] =
                    "The record could not be deleted.";
            }
        }
    }
}

if (result["message"]) {
    swal(result["message"]);
}

if (result["action"] === "delete") {
    pageObj.reload({
        a: "reload"
    });
}

.phprunner-row-actions-menu {
    position: absolute;
    z-index: 10000;
    min-width: 180px;
    padding: 6px 0;
    background: #fff;
    border: 1px solid #ddd;
    border-radius: 6px;
    box-shadow: 0 4px 14px rgba(0, 0, 0, 0.15);
}

.phprunner-row-actions-menu .row-action {
    padding: 8px 14px;
    cursor: pointer;
    white-space: nowrap;
}

.phprunner-row-actions-menu .row-action:hover {
    background: #f5f5f5;
}

function addDashboardGridSearch(dashboard, tableName) {
    var dashboardElement = dashboard.elements.find(function(element) {
        return element.table === tableName;
    });

    if (!dashboardElement) {
        console.warn("Dashboard table not found: " + tableName);
        return;
    }

    var containerId =
        "dashelement_" + dashboardElement.elementName + dashboard.id;
    var container = document.getElementById(containerId);

    if (!container) {
        console.warn("Dashboard container not found: " + containerId);
        return;
    }

    if (container.getAttribute("data-grid-search-initialized")) {
        return;
    }

    container.setAttribute("data-grid-search-initialized", "1");

    var searchValue = "";

    function search(input) {
        searchValue = input.value.trim();

        var parameters = {
            goto: 1
        };

        if (searchValue) {
            parameters.qs = searchValue;
        } else {
            parameters.a = "return";
            parameters.qs = "";
        }

        dashboard.loadPage(
            dashboardElement,
            {
                baseParams: parameters
            }
        );
    }

    function insertSearchBox() {
        var heading = container.querySelector(".panel-heading");
        var counter = container.querySelector(
            "[data-itemtype='details_found']"
        );

        if (!heading || !counter) {
            return;
        }

        if (heading.querySelector(".dashboard-grid-search")) {
            return;
        }

        var searchBox = document.createElement("div");
        var input = document.createElement("input");
        var clearButton = document.createElement("button");

        searchBox.className = "dashboard-grid-search";

        input.type = "search";
        input.className = "form-control input-sm";
        input.placeholder = "Search";
        input.value = searchValue;
        input.autocomplete = "off";
        input.setAttribute("aria-label", "Search " + tableName);

        clearButton.type = "button";
        clearButton.className = "btn btn-default btn-sm";
        clearButton.innerHTML = "×";
        clearButton.title = "Clear search";
        clearButton.setAttribute(
            "aria-label",
            "Clear " + tableName + " search"
        );

        searchBox.appendChild(input);
        searchBox.appendChild(clearButton);

        heading.classList.add("dashboard-grid-search-heading");
        counter.parentNode.classList.add("dashboard-grid-search-count");

        heading.insertBefore(searchBox, counter.parentNode);

        [
            "click",
            "mousedown",
            "mouseup",
            "touchstart"
        ].forEach(function(eventName) {
            searchBox.addEventListener(eventName, function(event) {
                event.stopPropagation();
            });
        });

        input.addEventListener("keydown", function(event) {
            if (event.key !== "Enter" && event.keyCode !== 13) {
                return;
            }

            event.preventDefault();
            event.stopPropagation();
            search(input);
        });

        clearButton.addEventListener("click", function(event) {
            event.preventDefault();
            event.stopPropagation();

            input.value = "";
            search(input);
        });
    }

    insertSearchBox();

    new MutationObserver(insertSearchBox).observe(
        container,
        {
            childList: true,
            subtree: true
        }
    );
}

addDashboardGridSearch(pageObj, "customers");
addDashboardGridSearch(pageObj, "products");
addDashboardGridSearch(pageObj, "employees");

.function-dashboard .panel-heading.dashboard-grid-search-heading {
    display: flex;
    align-items: center;
    gap: 8px;
    flex-wrap: nowrap;
}

.function-dashboard
.panel-heading.dashboard-grid-search-heading
> .rnr-dbebrick {
    float: none !important;
    white-space: nowrap;
}

.function-dashboard .dashboard-grid-search {
    display: flex;
    flex: 0 1 180px;
    min-width: 120px;
    margin-left: auto;
}

.function-dashboard .dashboard-grid-search .form-control {
    height: 30px;
    min-width: 0;
    padding: 4px 8px;
    font-size: 12px;
    border-radius: 4px 0 0 4px;
}

.function-dashboard .dashboard-grid-search .btn {
    height: 30px;
    padding: 4px 9px;
    font-size: 16px;
    line-height: 16px;
    border-radius: 0 4px 4px 0;
}

.function-dashboard .dashboard-grid-search-count {
    flex: 0 0 auto;
    margin-left: 0;
    white-space: nowrap;
    font-size: 12px;
}

@media (max-width: 900px) {
    .function-dashboard
    .panel-heading.dashboard-grid-search-heading {
        flex-wrap: wrap;
    }

    .function-dashboard .dashboard-grid-search {
        order: 3;
        flex-basis: 100%;
        margin-left: 28px;
    }

    .function-dashboard .dashboard-grid-search-count {
        margin-left: auto;
    }
}

CREATE TABLE ip_reputation_cache (
    ip_address VARCHAR(45) NOT NULL,
    abuse_score INT NOT NULL DEFAULT 0,
    total_reports INT NOT NULL DEFAULT 0,
    country_code VARCHAR(2) NULL,
    usage_type VARCHAR(100) NULL,
    checked_at DATETIME NOT NULL,
    PRIMARY KEY (ip_address)
);

<?php

function pwnedPasswordCount($password)
{
    $hash = strtoupper(sha1($password));
    $prefix = substr($hash, 0, 5);
    $suffix = substr($hash, 5);

    $ch = curl_init(
        "https://api.pwnedpasswords.com/range/" . $prefix
    );

    curl_setopt_array($ch, array(
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT => 5,
        CURLOPT_HTTPHEADER => array(
            "User-Agent: PHPRunner-PwnedPasswordCheck",
            "Add-Padding: true"
        )
    ));

    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    if ($response === false || $httpCode != 200) {
        return -1;
    }

    foreach (preg_split('/\r\n|\r|\n/', $response) as $line) {
        $parts = explode(':', trim($line), 2);

        if (
            count($parts) == 2
            && strtoupper($parts[0]) === $suffix
        ) {
            return (int)$parts[1];
        }
    }

    return 0;
}

function ipCheckFailure($message)
{
    return array(
        "success" => false,
        "error" => $message
    );
}

function checkIPReputation($ipAddress, $apiKey)
{
    if (!filter_var($ipAddress, FILTER_VALIDATE_IP)) {
        return ipCheckFailure("Invalid IP address.");
    }

    $sql = DB::PrepareSQL(
        "SELECT *
         FROM ip_reputation_cache
         WHERE ip_address=':1'
         AND checked_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)",
        $ipAddress
    );

    $rs = DB::Query($sql);
    $cached = $rs ? $rs->fetchAssoc() : false;

    if ($cached) {
        return array(
            "success" => true,
            "cached" => true,
            "abuseScore" => (int)$cached["abuse_score"],
            "totalReports" => (int)$cached["total_reports"],
            "countryCode" => $cached["country_code"],
            "usageType" => $cached["usage_type"]
        );
    }

    $url = "https://api.abuseipdb.com/api/v2/check?"
        . http_build_query(array(
            "ipAddress" => $ipAddress,
            "maxAgeInDays" => 90
        ));

    $ch = curl_init($url);

    curl_setopt_array($ch, array(
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT => 5,
        CURLOPT_HTTPHEADER => array(
            "Accept: application/json",
            "Key: " . $apiKey
        )
    ));

    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    if ($response === false || $httpCode != 200) {
        return ipCheckFailure("Unable to check IP reputation.");
    }

    $json = json_decode($response, true);

    if (!isset($json["data"])) {
        return ipCheckFailure("Unexpected AbuseIPDB response.");
    }

    $data = $json["data"];
    $abuseScore = (int)($data["abuseConfidenceScore"] ?? 0);
    $totalReports = (int)($data["totalReports"] ?? 0);
    $countryCode = $data["countryCode"] ?? "";
    $usageType = $data["usageType"] ?? "";

    DB::Exec(DB::PrepareSQL(
        "DELETE FROM ip_reputation_cache
         WHERE ip_address=':1'",
        $ipAddress
    ));

    DB::Exec(DB::PrepareSQL(
        "INSERT INTO ip_reputation_cache
         (ip_address, abuse_score, total_reports,
          country_code, usage_type, checked_at)
         VALUES (':1', :2, :3, ':4', ':5', NOW())",
        $ipAddress,
        $abuseScore,
        $totalReports,
        $countryCode,
        $usageType
    ));

    return array(
        "success" => true,
        "cached" => false,
        "abuseScore" => $abuseScore,
        "totalReports" => $totalReports,
        "countryCode" => $countryCode,
        "usageType" => $usageType
    );
}

require_once("security_checks.php");

using System;
using System.IO;
using System.Net;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using runnerDotNet;

namespace runnerDotNet
{
    public partial class CommonFunctions
    {
        public static int PwnedPasswordCount(string password)
        {
            byte[] bytes = Encoding.UTF8.GetBytes(password);
            byte[] digest;

            using (SHA1 sha1 = SHA1.Create())
            {
                digest = sha1.ComputeHash(bytes);
            }

            string hash = BitConverter.ToString(digest)
                .Replace("-", "")
                .ToUpperInvariant();

            string prefix = hash.Substring(0, 5);
            string suffix = hash.Substring(5);

            try
            {
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(
                    "https://api.pwnedpasswords.com/range/" + prefix
                );
                request.Method = "GET";
                request.Timeout = 5000;
                request.ReadWriteTimeout = 5000;
                request.UserAgent =
                    "ASPRunner.NET-PwnedPasswordCheck";
                request.Headers["Add-Padding"] = "true";

                using (HttpWebResponse response =
                    (HttpWebResponse)request.GetResponse())
                using (StreamReader reader = new StreamReader(
                    response.GetResponseStream()
                ))
                {
                    if (response.StatusCode != HttpStatusCode.OK)
                        return -1;

                    string body = reader.ReadToEnd();
                    string[] lines = body.Split(
                        new[] { "\r\n", "\n", "\r" },
                        StringSplitOptions.RemoveEmptyEntries
                    );

                    foreach (string line in lines)
                    {
                        string[] parts = line.Trim().Split(':');

                        if (
                            parts.Length == 2
                            && parts[0].ToUpperInvariant() == suffix
                        )
                        {
                            int count;
                            return Int32.TryParse(parts[1], out count)
                                ? count
                                : -1;
                        }
                    }
                }

                return 0;
            }
            catch
            {
                return -1;
            }
        }

        private static XVar IpCheckFailure(string message)
        {
            return new XVar(
                "success", false,
                "error", message
            );
        }

        public static XVar CheckIPReputation(
            string ipAddress,
            string apiKey
        )
        {
            if (String.IsNullOrWhiteSpace(ipAddress))
                return IpCheckFailure("Invalid IP address.");

            dynamic sql = DB.PrepareSQL(
                @"SELECT *
                  FROM ip_reputation_cache
                  WHERE ip_address=':1'
                  AND checked_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)",
                ipAddress
            );

            dynamic rs = DB.Query(sql);
            dynamic cached = XVar.Pack(rs) ? rs.fetchAssoc() : null;

            if (XVar.Pack(cached))
            {
                return new XVar(
                    "success", true,
                    "cached", true,
                    "abuseScore", cached["abuse_score"],
                    "totalReports", cached["total_reports"],
                    "countryCode", cached["country_code"],
                    "usageType", cached["usage_type"]
                );
            }

            string url =
                "https://api.abuseipdb.com/api/v2/check"
                + "?ipAddress=" + HttpUtility.UrlEncode(ipAddress)
                + "&maxAgeInDays=90";

            try
            {
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                request.Method = "GET";
                request.Timeout = 5000;
                request.ReadWriteTimeout = 5000;
                request.Accept = "application/json";
                request.Headers["Key"] = apiKey;

                string responseText;

                using (HttpWebResponse response =
                    (HttpWebResponse)request.GetResponse())
                using (StreamReader reader = new StreamReader(
                    response.GetResponseStream()
                ))
                {
                    if (response.StatusCode != HttpStatusCode.OK)
                        return IpCheckFailure(
                            "Unable to check IP reputation."
                        );

                    responseText = reader.ReadToEnd();
                }

                dynamic json = MVCFunctions.runner_json_decode(responseText);

                if (!XVar.Pack(json) || !XVar.Pack(json["data"]))
                    return IpCheckFailure(
                        "Unexpected AbuseIPDB response."
                    );

                dynamic data = json["data"];
                dynamic abuseScore = data["abuseConfidenceScore"];
                dynamic totalReports = data["totalReports"];
                dynamic countryCode = data["countryCode"];
                dynamic usageType = data["usageType"];

                DB.Exec(DB.PrepareSQL(
                    @"DELETE FROM ip_reputation_cache
                      WHERE ip_address=':1'",
                    ipAddress
                ));

                DB.Exec(DB.PrepareSQL(
                    @"INSERT INTO ip_reputation_cache
                      (ip_address, abuse_score, total_reports,
                       country_code, usage_type, checked_at)
                      VALUES (':1', :2, :3, ':4', ':5', NOW())",
                    ipAddress,
                    abuseScore,
                    totalReports,
                    countryCode,
                    usageType
                ));

                return new XVar(
                    "success", true,
                    "cached", false,
                    "abuseScore", abuseScore,
                    "totalReports", totalReports,
                    "countryCode", countryCode,
                    "usageType", usageType
                );
            }
            catch
            {
                return IpCheckFailure(
                    "Unable to check IP reputation."
                );
            }
        }
    }
}

pageObj.getItemButton("register_save").addClass("disabled");

params["password"] = this.getValue();

if (!params["password"]) {
    return false;
}

$result["count"] = pwnedPasswordCount($params["password"]);

result["count"] = CommonFunctions.PwnedPasswordCount(
    parameters["password"].ToString()
);

var count = Number(result.count);

if (count < 0) {
    swal("The password could not be checked. Please try again.");
    return;
}

if (count > 0) {
    swal(
        "This password has appeared in known data breaches "
        + count
        + " times. Please choose another password."
    );
    return;
}

pageObj.getItemButton("register_save").removeClass("disabled");

pageObj.getItemButton("register_save").addClass("disabled");

$count = pwnedPasswordCount($userdata["password"]);

if ($count < 0) {
    $message = "The password could not be checked. Please try again.";
    return false;
}

if ($count > 0) {
    $message = "This password has appeared in known data breaches. "
        . "Please choose another password.";
    return false;
}

return true;

int count = CommonFunctions.PwnedPasswordCount(
    userdata["password"].ToString()
);

if (count < 0)
{
    message = "The password could not be checked. Please try again.";
    return false;
}

if (count > 0)
{
    message = "This password has appeared in known data breaches. "
        + "Please choose another password.";
    return false;
}

return true;

$ipAddress = $_SERVER["REMOTE_ADDR"] ?? "";
$apiKey = "YOUR_ABUSEIPDB_API_KEY";

$ipResult = checkIPReputation($ipAddress, $apiKey);

if (
    $ipResult["success"]
    && $ipResult["abuseScore"] >= 90
) {
    http_response_code(403);
    exit("Access denied.");
}

string ipAddress = HttpContext.Current.Request.UserHostAddress;
string apiKey = "YOUR_ABUSEIPDB_API_KEY";

dynamic ipResult = CommonFunctions.CheckIPReputation(
    ipAddress,
    apiKey
);

if (
    XVar.Pack(ipResult["success"])
    && Convert.ToInt32(ipResult["abuseScore"].ToString()) >= 90
)
{
    HttpContext.Current.Response.StatusCode = 403;
    MVCFunctions.Echo("Access denied.");
    MVCFunctions.Exit();
}

if (
    Security::getUserGroup() != $_SESSION["AdminGroupName"]
    || $data["first_name"] == Security::getUserName()
) {
    $pageObject->hideItem("login_as_user", $recordId);
}

if (Security::getUserGroup() != $_SESSION["AdminGroupName"]) {
    $result["success"] = false;
    return;
}

$record = $button->getCurrentRecord();

$_SESSION["impersonation_admin"] = Security::getUserName();

Security::loginAs($record["first_name"]);

$result["success"] = true;

if (result["success"]) {
    window.location.href = "menu.php";
}

<?php
if (
    Security::isLoggedIn()
    && !empty($_SESSION["impersonation_admin"])
) {
    $adminUsername = htmlspecialchars(
        $_SESSION["impersonation_admin"],
        ENT_QUOTES,
        "UTF-8"
    );

    $currentUsername = htmlspecialchars(
        Security::getUserName(),
        ENT_QUOTES,
        "UTF-8"
    );
?>
<div id="impersonation-banner">
    <span>
        <strong>Impersonation mode:</strong>
        Admin <strong><?php echo $adminUsername; ?></strong>
        is browsing as
        <strong><?php echo $currentUsername; ?></strong>.
    </span>

    <a href="menu.php?return_admin=1">
        Return to admin session
    </a>
</div>

<script>
(function () {
    var banner = document.getElementById("impersonation-banner");

    if (!banner || !document.body) {
        return;
    }

    document.body.insertBefore(banner, document.body.firstChild);
    document.body.classList.add("impersonation-active");
})();
</script>
<?php
}
?>

:root {
    --impersonation-banner-height: 40px;
}

#impersonation-banner {
    position: sticky !important;
    top: 0 !important;
    z-index: 1000000 !important;
    width: 100% !important;
    max-width: none !important;
    height: var(--impersonation-banner-height);
    box-sizing: border-box;
    margin: 0 !important;
    padding: 0 16px;
    display: flex;
    align-items: center;
    justify-content: center;
    gap: 18px;
    background: #fff3cd;
    border-bottom: 1px solid #e0bd64;
    color: #664d03;
    font-size: 14px;
    line-height: 20px;
    text-align: center;
    white-space: nowrap;
}

#impersonation-banner a {
    color: #664d03;
    font-weight: 600;
    text-decoration: underline;
}

body.impersonation-active .r-topheader[data-fixed] {
    top: var(--impersonation-banner-height) !important;
}

body.impersonation-active .r-left[data-fixed] {
    top: var(--impersonation-banner-height) !important;
    height: calc(100vh - var(--impersonation-banner-height)) !important;
}

body.impersonation-active > .r-vbar-page,
body.impersonation-active > .r-topbar-page {
    min-height: calc(100vh - var(--impersonation-banner-height));
}

@media print {
    #impersonation-banner {
        display: none !important;
    }

    body.impersonation-active .r-topheader[data-fixed],
    body.impersonation-active .r-left[data-fixed] {
        top: 0 !important;
    }
}

$_SESSION["AdminGroupName"] = "Administrators";

if (
    isset($_GET["return_admin"])
    && !empty($_SESSION["impersonation_admin"])
) {
    $adminUsername = $_SESSION["impersonation_admin"];

    unset($_SESSION["impersonation_admin"]);

    Security::loginAs($adminUsername);

    header("Location: menu.php");
    exit();
}