Pseudo API

[shddcit]

Good day, Klemen.

I need some consultation regarding the Hesk database.

Which tables, column names, and row names contain comments for client tickets, and which tables, column names, and row names contain staff notes?

I can send you my code, which I implemented for a mobile phone, where you can mark tickets that need to be addressed and mark completed tickets. I also have the ability to close requests, but due to the lack of authorization and category-based permission distribution, requests are currently being closed by an unknown person. The problem is that I would like to record a comment or note before sending it to the server. However, an error occurs, as if it cannot determine which ticket I am submitting a request to add comments or notes to, referring to the ticket ID. Can I send you the code via email, perhaps you might find something useful for your work.

[shddcit]

I am retrieving this from the database:

Code: Select all

$sql = "SELECT t.id, t.name, t.subject, t.message, t.dt, c.name AS category, t.custom1, t.custom2, t.custom3, t.custom6, t.custom7
        FROM " . $dbPrefix . "tickets t
        JOIN " . $dbPrefix . "categories c ON t.category = c.id
        WHERE t.status != 3
        ORDER BY t.dt DESC";
$result = $conn->query($sql);

For comments, I am sending this:

Code: Select all

<?php

$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$data = json_decode(file_get_contents('php://input'), true);
if (json_last_error() !== JSON_ERROR_NONE) {
    die(json_encode(array('success' => false, 'message' => 'Invalid JSON data')));
}

if (!isset($data['ticketId']) || !isset($data['comment'])) {
    die(json_encode(array('success' => false, 'message' => 'Missing ticketId or comment')));
}

$ticketId = $data['ticketId'];
$comment = $data['comment'];

$comment = strip_tags($comment);

$sql = "INSERT INTO " . $dbPrefix . "replies (ticket_id, message, message_html) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);

if ($stmt === false) {
    die(json_encode(array('success' => false, 'message' => 'Failed to prepare statement: ' . $conn->error)));
}

$stmt->bind_param('iss', $ticketId, $comment, $comment);

if ($stmt->execute()) {
    echo json_encode(array('success' => true));
} else {
    echo json_encode(array('success' => false, 'message' => $stmt->error));
}

$stmt->close();
$conn->close();
?>

For notes, this is:

Code: Select all

<?php

$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$data = json_decode(file_get_contents('php://input'), true);
if (json_last_error() !== JSON_ERROR_NONE) {
    die(json_encode(array('success' => false, 'message' => 'Invalid JSON data')));
}

if (!isset($data['ticketId']) || !isset($data['note'])) {
    die(json_encode(array('success' => false, 'message' => 'Missing ticketId or note')));
}

$ticketId = $data['ticketId'];
$note = $data['note'];

$note = strip_tags($note);

$sql = "INSERT INTO " . $dbPrefix . "notes (ticket_id, message) VALUES (?, ?)";
$stmt = $conn->prepare($sql);

if ($stmt === false) {
    die(json_encode(array('success' => false, 'message' => 'Failed to prepare statement: ' . $conn->error)));
}

$stmt->bind_param('is', $ticketId, $note);

if ($stmt->execute()) {
    echo json_encode(array('success' => true));
} else {
    echo json_encode(array('success' => false, 'message' => $stmt->error));
}

$stmt->close();
$conn->close();
?>

[shddcit]

The authorization service with access rights request is currently in development.

[Klemen]

hesk_tickets contains the basic ticket data and initial message
hesk_replies contains replies to a ticket (staff and customer)
hesk_notes contains private staff notes for a ticket
hesk_users contains all the user (staff) data

Please study those tables (and others) in phpMyAdmin for example, and you should find all the information you are looking for. The column names are named in a way that should provide clues as to what data they contain.

If you have questions about a specific dataset/column contents, feel free to ask.

[ztank]

Hello @Klemen,

I am trying to add new tickets using a script that basically pipes an sql INSERT statement in to a mysql command.
Although it works and it seems it populates the majority of heskik_tickets fields (trackid, u_name, u_email, subject, message, message_html, custom1, dt just to mention a few) correctly, when I pull the tickets list from the HESK gui the CUSTOMER and LAST REPLIER fields contain the string "[Customer]" instead of what I would expect: u_name. No matter if I have the customer available and/or pre-populated in the manage_customers.php page.
I am really scratching my head around it and I am sure you might know exactly where or what I am doing wrong.

Can you please take a look?
Thanks.

[Klemen]

u_name is there for legacy reasons only (to prevent data loss during updates) and for possible future use.

What you need to do is:

1. create a new customer (or use an existing one) in the hesk_customers table
2. add an entry into the hesk_ticket_to_customer table