<?php
require_login();
require_once __DIR__ . '/../includes/email_phpmailer_manual.php';
$wfId = intval($_POST['wf']);
$stateId = intval($_POST['state']); // This is the initial state (state 1)
$userId = $_SESSION['user_id'];
// Get requester name for email notifications
$requesterSql = "SELECT firstName, lastName FROM TICKET_User WHERE pk_User = ?";
$requesterStmt = mysqli_prepare($conn, $requesterSql);
mysqli_stmt_bind_param($requesterStmt, 'i', $userId);
mysqli_stmt_execute($requesterStmt);
mysqli_stmt_bind_result($requesterStmt, $requesterFirstName, $requesterLastName);
mysqli_stmt_fetch($requesterStmt);
mysqli_stmt_close($requesterStmt);
$requesterName = $requesterFirstName . ' ' . $requesterLastName;
// Get workflow title for email notifications
$wfSql = "SELECT title FROM TICKET_Workflow WHERE pk_Workflow = ?";
$wfStmt = mysqli_prepare($conn, $wfSql);
mysqli_stmt_bind_param($wfStmt, 'i', $wfId);
mysqli_stmt_execute($wfStmt);
mysqli_stmt_bind_result($wfStmt, $wfTitle);
mysqli_stmt_fetch($wfStmt);
mysqli_stmt_close($wfStmt);
// Start a transaction for atomicity
mysqli_begin_transaction($conn);
$success = true;
$errorMessage = '';
// 1) Create the request in the first state
$stmt = mysqli_prepare(
$conn,
'INSERT INTO TICKET_Request (fk_Workflow, fk_State, fk_User) VALUES (?, ?, ?)'
);
if (!$stmt) {
$success = false;
$errorMessage = "Prepare statement for TICKET_Request failed: " . mysqli_error($conn);
error_log($errorMessage);
}
if ($success) {
mysqli_stmt_bind_param($stmt, 'iii', $wfId, $stateId, $userId);
if (!mysqli_stmt_execute($stmt)) {
$success = false;
$errorMessage = "Execute statement for TICKET_Request failed: " . mysqli_stmt_error($stmt);
error_log($errorMessage);
} else {
$reqId = mysqli_insert_id($conn);
}
mysqli_stmt_close($stmt);
}
// 2) Log that initial state in history
if ($success) {
$stmt = mysqli_prepare(
$conn,
'INSERT INTO TICKET_hasState (fk_Request, fk_State, iam_inserted) VALUES (?, ?, ?)'
);
if (!$stmt) {
$success = false;
$errorMessage = "Prepare statement for TICKET_hasState (initial) failed: " . mysqli_error($conn);
error_log($errorMessage);
}
}
if ($success) {
mysqli_stmt_bind_param($stmt, 'iii', $reqId, $stateId, $userId);
if (!mysqli_stmt_execute($stmt)) {
$success = false;
$errorMessage = "Execute statement for TICKET_hasState (initial) failed: " . mysqli_stmt_error($stmt);
error_log($errorMessage);
}
mysqli_stmt_close($stmt);
}
// 3) Save form data for the initial state
if ($success) {
foreach ($_POST as $k => $v) {
if (strpos($k, 'field_') === 0) {
$fieldId = intval(substr($k, 6));
$value = is_array($v) ? '1' : trim($v);
$dStmt = mysqli_prepare(
$conn,
'INSERT INTO TICKET_Data (fk_Request, fk_Field, value) VALUES (?, ?, ?)'
);
if (!$dStmt) {
$success = false;
$errorMessage = "Prepare statement for TICKET_Data failed: " . mysqli_error($conn);
error_log($errorMessage);
break; // Exit loop on error
}
mysqli_stmt_bind_param($dStmt, 'iis', $reqId, $fieldId, $value);
if (!mysqli_stmt_execute($dStmt)) {
$success = false;
$errorMessage = "Execute statement for TICKET_Data failed: " . mysqli_stmt_error($dStmt);
error_log($errorMessage);
mysqli_stmt_close($dStmt); // Close statement before breaking
break; // Exit loop on error
}
mysqli_stmt_close($dStmt);
}
}
}
// 4) Lookup the *second* state for this workflow
$nextState = null;
$nextStateTitle = null;
if ($success) {
$query = "
SELECT pk_State, title
FROM TICKET_State
WHERE fk_Workflow = ?
ORDER BY `no` ASC
LIMIT 1 OFFSET 1
";
$stmt = mysqli_prepare($conn, $query);
if (!$stmt) {
$success = false;
$errorMessage = "Prepare statement for next state lookup failed: " . mysqli_error($conn);
error_log($errorMessage);
}
}
if ($success) {
mysqli_stmt_bind_param($stmt, 'i', $wfId);
if (!mysqli_stmt_execute($stmt)) {
$success = false;
$errorMessage = "Execute statement for next state lookup failed: " . mysqli_stmt_error($stmt);
error_log($errorMessage);
} else {
$res = mysqli_stmt_get_result($stmt);
if ($row = mysqli_fetch_assoc($res)) {
$nextState = $row['pk_State'];
$nextStateTitle = $row['title'];
}
mysqli_free_result($res);
}
mysqli_stmt_close($stmt);
}
if ($success && $nextState !== null) {
// 5) Advance the request to that next state
$upd = mysqli_prepare(
$conn,
'UPDATE TICKET_Request SET fk_State = ? WHERE pk_Request = ?'
);
if (!$upd) {
$success = false;
$errorMessage = "Prepare statement for TICKET_Request update failed: " . mysqli_error($conn);
error_log($errorMessage);
}
if ($success) {
mysqli_stmt_bind_param($upd, 'ii', $nextState, $reqId);
if (!mysqli_stmt_execute($upd)) {
$success = false;
$errorMessage = "Execute statement for TICKET_Request update failed: " . mysqli_stmt_error($upd);
error_log($errorMessage);
} else {
// Check if any rows were actually affected by the update
if (mysqli_stmt_affected_rows($upd) === 0) {
error_log("Warning: TICKET_Request update affected 0 rows for reqId: $reqId, nextState: $nextState");
}
}
mysqli_stmt_close($upd);
}
// 6) Log the transition in history
if ($success) {
$h2 = mysqli_prepare(
$conn,
'INSERT INTO TICKET_hasState (fk_Request, fk_State, iam_inserted) VALUES (?, ?, ?)'
);
if (!$h2) {
$success = false;
$errorMessage = "Prepare statement for TICKET_hasState (next) failed: " . mysqli_error($conn);
error_log($errorMessage);
}
}
if ($success) {
mysqli_stmt_bind_param($h2, 'iii', $reqId, $nextState, $userId);
if (!mysqli_stmt_execute($h2)) {
$success = false;
$errorMessage = "Execute statement for TICKET_hasState (next) failed: " . mysqli_stmt_error($h2);
error_log($errorMessage);
}
mysqli_stmt_close($h2);
}
// 7) Create notifications for the next state's actors
if ($success && $nextState !== null) {
// Get actors for the next state
$actorSql = "
SELECT sa.actorType, sa.fk_User, sa.fk_Group
FROM TICKET_StateActor sa
WHERE sa.fk_State = ?
";
$actorStmt = mysqli_prepare($conn, $actorSql);
if ($actorStmt) {
mysqli_stmt_bind_param($actorStmt, 'i', $nextState);
mysqli_stmt_execute($actorStmt);
$actorResult = mysqli_stmt_get_result($actorStmt);
while ($actor = mysqli_fetch_assoc($actorResult)) {
if ($actor['actorType'] === 'USER' && $actor['fk_User']) {
// Direct user notification
$notifStmt = mysqli_prepare($conn,
'INSERT INTO TICKET_Notifications (fk_State, fk_User, fk_Request) VALUES (?, ?, ?)'
);
mysqli_stmt_bind_param($notifStmt, 'iii', $nextState, $actor['fk_User'], $reqId);
mysqli_stmt_execute($notifStmt);
mysqli_stmt_close($notifStmt);
} elseif ($actor['actorType'] === 'GROUP' && $actor['fk_Group']) {
// Group notification - notify all users in the group
$groupUsersSql = "SELECT pk_User FROM TICKET_User WHERE fk_Group = ?";
$groupUsersStmt = mysqli_prepare($conn, $groupUsersSql);
mysqli_stmt_bind_param($groupUsersStmt, 'i', $actor['fk_Group']);
mysqli_stmt_execute($groupUsersStmt);
$groupUsersResult = mysqli_stmt_get_result($groupUsersStmt);
while ($groupUser = mysqli_fetch_assoc($groupUsersResult)) {
$notifStmt = mysqli_prepare($conn,
'INSERT INTO TICKET_Notifications (fk_State, fk_User, fk_Request) VALUES (?, ?, ?)'
);
mysqli_stmt_bind_param($notifStmt, 'iii', $nextState, $groupUser['pk_User'], $reqId);
mysqli_stmt_execute($notifStmt);
mysqli_stmt_close($notifStmt);
}
mysqli_stmt_close($groupUsersStmt);
}
}
mysqli_stmt_close($actorStmt);
}
// Send email notifications to next state actors using PHPMailer
send_actor_notification_emails_phpmailer($conn, $nextState, $reqId, $wfTitle, $nextStateTitle, $requesterName);
}
} else if ($success && $nextState === null) {
// No next state found (e.g., single-stage workflow)
// This is not an error, just a different outcome
$errorMessage = "Request submitted successfully. No further stages found for automatic advancement.";
}
// Final transaction commit or rollback
if ($success) {
mysqli_commit($conn);
echo '<p>Request submitted and automatically moved to the next stage!</p>';
} else {
mysqli_rollback($conn);
echo '<p style="color:red;">An error occurred during request submission: ' . htmlspecialchars($errorMessage) . '</p>';
echo '<p>Please try again or contact support.</p>';
}
echo '<p><a href="index.php?page=my_requests">View My Requests</a></p>';
?>