require("./global.php");
$primaryTableName = "tickets";
if (isset($_POST['create_package'])) {
$id = generateRandomString();
$name = escape($_POST['name']);
$company_name = escape($_POST['company_name']);
$department = escape($_POST['department']);
$site_name = escape($_POST['site_name']);
$email = escape($_POST['email']);
$status = escape($_POST['status']);
$phone_number = escape($_POST['phone_number']);
$primary_contact = escape($_POST['primary_contact']);
$home_number = escape($_POST['home_number']);
// $location_name = escape($_POST['location_name']);
$start_date = escape($_POST['start_date']);
$end_date = escape($_POST['end_date']);
$priority = escape($_POST['priority']);
// $repeat_every = escape($_POST['repeat_every']);
// $assign = escape($_POST['assign']);
$assign = isset($_POST['assign']) ? implode(',', $_POST['assign']) : '';
// $assign = implode(',', $_POST['assign']);
$description = escape($_POST['description']);
$timeAdded = date('Y-m-d H:i:s');
// $session_userId = $_SESSION['userId'];
$actionId = $_POST['actionId'];
if ($actionId == "") {
$id = generateRandomString();
$actionId = $id;
$query = "INSERT INTO jeoXillityCrm_tickets (id, name, company_name, department, site_name, email, status, phone_number, primary_contact, home_number, location_name, start_date, end_date, priority, assign, description, timeAdded, userId) VALUES
('$id', '$name', '$company_name', '$department', '$site_name', '$email', '$status', '$phone_number', '$primary_contact', '$home_number', '','$start_date', '$end_date', '$priority', '$assign', '$description', '$timeAdded', '$session_userId')";
} else {
$query = "UPDATE jeoXillityCrm_tickets SET notif_read = 1 ,name='$name', company_name='$company_name', department='$department', site_name='$site_name', email='$email', status='$status', phone_number='$phone_number', primary_contact='$primary_contact', home_number='$home_number', start_date='$start_date', end_date='$end_date', priority='$priority', assign='$assign', description='$description' WHERE id='$actionId'";
}
runQuery($query);
$file = storeFile($_FILES['file']);
if ($file != "") {
$query = "update jeoXillityCrm_tickets set file='$file' where id='$actionId'";
runQuery($query);
}
error_log($assign);
if (!empty($assign)) {
$assignedUsers = explode(',', $assign);
$ticketTitle = $name;
$ticketId = $actionId;
$protocol = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? "https://" : "http://";
$host = $_SERVER['HTTP_HOST']; // e.g., portal.xillity-crm.online
$baseURL = $protocol . $host . '/';
$pageLink = $baseURL . "tickets_view.php?id=" . $ticketId;
$message = "You have been assigned a new ticket: $ticketTitle";
$time = time();
foreach ($assignedUsers as $user_id) {
// 1. Notification Bell
$notif_query = "INSERT INTO jeoXillityCrm_notifications (user_id, message, link, is_read, created_at)
VALUES ('$user_id', '$message', '$pageLink', 0, '$time')";
runQuery($notif_query);
// 2. Email Alert
$userInfo = getRow($con, "SELECT email, name FROM jeoXillityCrm_users WHERE name='$user_id'");
// echo $userInfo; die();
if (!empty($userInfo['email'])) {
$to = $userInfo['email'];
$subject = "New Ticket Assigned: $ticketTitle";
$body = "Hello {$userInfo['name']},
You have been assigned to a new ticket titled $ticketTitle.
Click here to view
Thank you,
Support Team";
$headers = "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
$headers .= "From: noreply@xillity-crm.online\r\n";
if (!mail($to, $subject, $body, $headers)) {
error_log("Email to $to failed to send.");
} else {
error_log("Email to $to sent successfully.");
}
}
}
}
header("Location: ?m=Data was saved successfully!&type=success");
exit();
}
// if (isset($_GET['delete-record'])) {
// $id = escape($_GET['delete-record']);
// $query = "delete from jeoXillityCrm_tickets where id='$id'";
// runQuery($query);
// }
if (isset($_GET['delete-record'])) {
$id = escape($_GET['delete-record']);
// Check if there are tickets associated with the company
$chk = "SELECT * FROM jeoXillityCrm_tickets WHERE id='$id' AND is_deleted = 0";
$chk_run = mysqli_query($con, $chk);
if (!$chk_run) {
die("Database error: " . mysqli_error($con));
}
if (mysqli_num_rows($chk_run) > 0) {
$moveToTrash = "UPDATE jeoXillityCrm_tickets SET is_deleted = 1 WHERE id='$id'";
mysqli_query($con, $moveToTrash);
header("Location: ?m=Tickets moved to trash. You can recover them from Trash.!&type=success");
}
}
if (isset($_POST['filter'])) {
$status = isset($_POST['status']) ? escape($_POST['status']) : '';
$priority = isset($_POST['priority']) ? escape($_POST['priority']) : '';
// Construct the query with filters
$query = "SELECT * FROM jeoXillityCrm_tickets WHERE 1=1";
if ($status != '') {
$query .= " AND status = '$status'";
}
if ($priority != '') {
$query .= " AND priority = '$priority'";
}
$query .= " AND userId='$session_userId' AND is_deleted=0";
// echo $query;die();
// Execute the query
$results = getAll($con, $query);
} else {
// Default query to display all records
$session_userName = $_SESSION['username'] ?? null;
$query = "select * from jeoXillityCrm_tickets t where (t.userId='$session_userId' or t.assign like '%$session_userName%') AND t.is_deleted=0 order by t.timeAdded desc";
// echo($query);die();
$results = getAll($con, $query);
}
if (isset($_POST['site_names'])) {
$siteName = $_POST['site_names'];
$query = "SELECT * FROM jeoXillityCrm_sites WHERE site_name = '$siteName' and userId='$session_userId'";
// print_r($query);die();
$results = getall($con, $query);
$output = '';
foreach ($results as $row) {
$output .= '';
}
echo $output;
}
if (isset($_POST['companyId'])) {
$companyId = $_POST['companyId'];
$sql = "SELECT * FROM jeoXillityCrm_sites WHERE company_id = $companyId";
$stmt = $con->prepare($sql);
$stmt->bind_param("i", $companyId);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
echo '';
while ($row = $result->fetch_assoc()) {
echo '';
}
} else {
echo '';
}
$stmt->close();
}
?>