<?php
// Database connection
$LINK = mysqli_connect('127.0.0.1', 'pisjo950', 'vjPRjFTxDVIoG7)t', 'pisjo950');
if (!$LINK) {
die('DB connect error: ' . mysqli_connect_error());
}
// Get current user ID
$userId = isset($_SESSION['id']) ? (int)$_SESSION['id'] : 0;
$userRole=$_SESSION['group'];
// 1) Fetch all tickets created by this user
$stmtTickets = $LINK->prepare(
"SELECT id_request, fi_workflow, date_inserted
FROM TicketRequest
WHERE fi_user = ?
ORDER BY date_inserted DESC"
);
$stmtTickets->bind_param('i', $userId);
$stmtTickets->execute();
$tickets = $stmtTickets->get_result()->fetch_all(MYSQLI_ASSOC);
$stmtTickets->close();
// 2) For each ticket, fetch its latest state
foreach ($tickets as &$ticket) {
$stmtState = $LINK->prepare(
"SELECT s.no, s.responsable
FROM TicketHasState hs
JOIN TicketStates s ON hs.fi_state = s.id_state
WHERE hs.fi_request = ?
ORDER BY hs.id_has_state DESC
LIMIT 1"
);
$stmtState->bind_param('i', $ticket['id_request']);
$stmtState->execute();
$stateInfo = $stmtState->get_result()->fetch_assoc();
$stmtState->close();
// attach to each ticket
$ticket['step_no'] = $stateInfo['no'] ?? null;
$ticket['step_title'] = $stateInfo['responsable'] ?? 'Unknown';
}
$itTickets = [];
if ($userRole === 'It' || $userRole==='Admin') {
// A) Get all request IDs whose latest state is assigned to IT
$reqIds = [];
$stmt1 = $LINK->prepare(
"SELECT hs.fi_request
FROM TicketHasState hs
JOIN TicketStates s ON hs.fi_state = s.id_state
WHERE s.responsable = 'It'
GROUP BY hs.fi_request"
);
$stmt1->execute();
$res1 = $stmt1->get_result();
while ($row = $res1->fetch_assoc()) {
$reqIds[] = $row['fi_request'];
}
//var_dump($reqIds);
$stmt1->close();
foreach($reqIds as $reqId)
{
//B) For each request with It Id receive relevant data
$statementTicket=$LINK->prepare(
"SELECT r.id_request,w.title,d.value,r.date_inserted
FROM TicketRequest r
LEFT JOIN TicketWorkflows w ON w.id_workflow=r.fi_workflow
LEFT JOIN TicketData d ON r.id_request=d.fi_request
WHERE id_request=?"
);
$statementTicket->bind_param('i',$reqId);
$statementTicket->execute();
$ItTicket=$statementTicket->get_result()->fetch_assoc();
$statementTicket->close();
if($ItTicket)
{
$itTickets[]=$ItTicket;
}
}
//pressing advance button
if(isset($_POST['ticketId']))
{
$ticketId=$_POST['ticketId'];
// var_dump($ticketId);
//find current state and workflow
$stmtCur = $LINK->prepare(
"SELECT s.no AS current_no, r.fi_workflow
FROM TicketHasState hs
JOIN TicketStates s ON hs.fi_state = s.id_state
JOIN TicketRequest r ON hs.fi_request = r.id_request
WHERE hs.fi_request = ?
ORDER BY hs.id_has_state DESC
LIMIT 1"
);
$stmtCur->bind_param('i', $ticketId);
$stmtCur->execute();
$cur = $stmtCur->get_result()->fetch_assoc();
$stmtCur->close();
//var_dump($cur);
//if we get the right one
if($cur)
{
//get his current step number
$currentNo = $cur['current_no'];
$workflow = $cur['fi_workflow'];
// 2) find next state id
$stmtNext = $LINK->prepare(
"SELECT id_state
FROM TicketStates
WHERE fi_workflow = ?
AND no > ?
ORDER BY no ASC
LIMIT 1"
);
$stmtNext->bind_param('ii', $workflow, $currentNo);
$stmtNext->execute();
$nextRow = $stmtNext->get_result()->fetch_assoc();
$stmtNext->close();
//var_dump($nextRow);
//If there is a nest step
if ($nextRow) {
$nextState = $nextRow['id_state'];
// 3) insert new history entry
$stmtIns = $LINK->prepare(
"INSERT INTO TicketHasState
(fi_request, fi_state, iam_inserted, date_inserted)
VALUES (?, ?, ?, ?)"
);
$now = date('Y-m-d H:i:s');
$stmtIns->bind_param('iiss', $ticketId, $nextState, $_SESSION['username'], $now);
$stmtIns->execute();
$stmtIns->close();
// refresh to avoid repost
// header("Location: index.php?page=myTickets");
exit;
}
else{
$errorMsg = 'Already at final state';
}
}
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>My Tickets</title>
<link rel="stylesheet" href="Styles/styles.css">
</head>
<body>
<?php if (!empty($errorMsg)): ?>
<p style="color:red;">Error: <?= htmlspecialchars($errorMsg) ?></p>
<?php endif; ?>
<h1>My Tickets</h1>
<?php if (empty($tickets)): ?>
<p>You have not created any tickets yet.</p>
<?php else: ?>
<table>
<thead>
<tr>
<th>ID</th>
<th>Workflow</th>
<th>Created At</th>
<th>Current Step</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php foreach ($tickets as $t): ?>
<tr>
<td><?= $t['id_request'] ?></td>
<td><?= $t['fi_workflow'] ?></td>
<td><?= $t['date_inserted'] ?></td>
<td><?= $t['step_no'] ?> – <?= $t['step_title'] ?></td>
<td>
<a href="index.php?page=showTickets&ticketId=<?= $t['id_request'] ?>">View</a>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php endif;
if($_SESSION['group']=='It' || $_SESSION['group']=='Admin')
{
?>
<h2>Tickets Assigned to IT</h2>
<?php if (empty($itTickets)): ?>
<p>No tickets currently assigned to IT.</p>
<?php else: ?>
<form method="POST">
<table>
<thead><tr>
<th>ID</th><th>Workflow</th><th>Value</th><th>Created At</th><th>Actions</th>
</tr></thead>
<tbody>
<?php foreach ($itTickets as $t): ?>
<tr>
<td><?= $t['id_request'] ?></td>
<td><?= $t['title'] ?></td>
<td><?= $t['value'] ?></td>
<td><?= $t['date_inserted'] ?></td>
<td>
<button type="submit" name="ticketId" value="<?= $t['id_request'] ?>">Advance</button>
<input type="hidden" name="advance" value="1">
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</form>
<?php endif; ?>
<?php
}
?>
<p><a href="index.php">Back to Home</a></p>
</body>
</html>