Ticketing system

author: arf20 <aruizfernandez05@gmail.com> 2024-03-20 20:00:31 +0100
committer: arf20 <aruizfernandez05@gmail.com> 2024-03-20 20:00:31 +0100
commit: a8f2bace985ee0419492a09e1d5a3879f2f5b96b (patch)
tree: acbcf11cb4ba4652a07077dffd46792e454bf8bc
parent: ec7700456c646b9089f63a272cf15ff4ea5895d6 (diff)
download: arfnet2-cstims-a8f2bace985ee0419492a09e1d5a3879f2f5b96b.tar.gz
arfnet2-cstims-a8f2bace985ee0419492a09e1d5a3879f2f5b96b.zip
3 files changed, 104 insertions, 107 deletions
diff --git a/client.php b/client.php
index dbab9e8..2a9721d 100644
--- a/client.php
+++ b/client.php
@@ -29,6 +29,15 @@ mysqli_stmt_execute($stmt);
 $result = mysqli_stmt_get_result($stmt);
 $services = $result->fetch_all(MYSQLI_ASSOC);
 
+// Get tickets
+$sql = "SELECT id, `order`, subject, body, date, status, asignee FROM tickets WHERE `order` IN (SELECT id FROM orders WHERE client = ?)";
+$stmt = mysqli_prepare($link, $sql);
+mysqli_stmt_bind_param($stmt, "s", $param_client);
+$param_client = $clientid;
+mysqli_stmt_execute($stmt);
+$result = mysqli_stmt_get_result($stmt);
+$tickets = $result->fetch_all(MYSQLI_ASSOC);
+
 function getservicebyid($id) {
     global $services;
     foreach ($services as $service) {
@@ -38,6 +47,15 @@ function getservicebyid($id) {
     }
 }
 
+function getorderbyid($id) {
+    global $orders;
+    foreach ($orders as $order) {
+        if ($order["id"] == $id) {
+            return $order;
+        }
+    }
+}
+
 ?>
 
 <!doctype html>
@@ -61,17 +79,24 @@ function getservicebyid($id) {
                         <div class="col5">
                             <h3>Orders</h3>
                             <table>
-                                <tr><th>service</th><th>instance</th><th>billing</th><th>comments</th><th>status</th></tr>
+                                <tr><th>instance</th><th>service</th><th>billing</th><th>comments</th><th>status</th></tr>
                                 <?php
                                 foreach ($orders as $order) {
-                                    echo "<tr><td>".getservicebyid($order["service"])["name"]."</td><td>".$order["name"]."</td><td>".$order["billing"]."</td><td><pre>".$order["comments"]."</pre></td><td>".$order["status"]."</tr>\n";
+                                    echo "<tr><td>".$order["name"]."</td><td>".getservicebyid($order["service"])["name"]."</td><td>".$order["billing"]."</td><td><details><summary></summary><pre>".$order["comments"]."</pre></details></td><td>".$order["status"]."</td></tr>\n";
                                 }
                                 ?>
                             </table>
                         </div>
                         <div class="col5">
                             <h3>Tickets</h3>
-                            <!-- TODO PHP list of services -->
+                            <table>
+                                <tr><th>order</th><th>subject</th><th>body</th><th>date</th><th>status</th></tr>
+                                <?php
+                                foreach ($tickets as $ticket) {
+                                    echo "<tr><td>".getorderbyid($ticket["order"])["name"]."</td><td>".$ticket["subject"]."</td><td><details><summary></summary><pre>".$ticket["body"]."</pre></details></td><td>".$ticket["date"]."</td><td>".$ticket["status"]."</td></tr>\n";
+                                }
+                                ?>
+                            </table>
                         </div>
                     </div>
                 </div>
diff --git a/managetickets.php b/managetickets.php
index 02c2871..752f7f7 100644
--- a/managetickets.php
+++ b/managetickets.php
@@ -192,7 +192,7 @@ function getuserbyid($id) {
                             ."<td>".getservicebyid($order["service"])["name"]."</td>"
                             ."<td>".getuserbyid(getorderbyid($ticket["order"])["client"])["username"]."</td>"
                             ."<td>".$ticket["subject"]."</td>"
-                            ."<td><pre>".$ticket["body"]."</pre></td>"
+                            ."<td><details><summary></summary><pre>".$ticket["body"]."</pre></details></td>"
                             ."<td>".$ticket["status"]."</td>"
                             ."<td>".getuserbyid($ticket["asignee"])["username"]."</td>"
                             ."<td><a href=\"?del=".$ticket["id"]."\">del</a> <a href=\"?edit=".$ticket["id"]."\">edit</a></td></tr>\n";
diff --git a/openticket.php b/openticket.php
index 4448f7b..6269120 100644
--- a/openticket.php
+++ b/openticket.php
@@ -14,48 +14,71 @@ $type = $_SESSION["type"];
 require_once "config.php";
 
 // Get orders
-$sql = "SELECT id, name FROM orders WHERE client = ?";
+$sql = "SELECT id, name, service FROM orders WHERE client = ?";
 $stmt = mysqli_prepare($link, $sql);
 mysqli_stmt_bind_param($stmt, "s", $param_client);
 $param_client = $clientid;
 mysqli_stmt_execute($stmt);
 $result = mysqli_stmt_get_result($stmt);
+$orders = $result->fetch_all(MYSQLI_ASSOC);
+
+// Get services
+$sql = "SELECT id, name, type, billing, description FROM services";
+$stmt = mysqli_prepare($link, $sql);
+mysqli_stmt_execute($stmt);
+$result = mysqli_stmt_get_result($stmt);
 $services = $result->fetch_all(MYSQLI_ASSOC);
 
+// Get users
+$sql = "SELECT id, username, type, email FROM users";
+$stmt = mysqli_prepare($link, $sql);
+mysqli_stmt_execute($stmt);
+$result = mysqli_stmt_get_result($stmt);
+$users = $result->fetch_all(MYSQLI_ASSOC);
+
 // POST actions
 if ($_SERVER["REQUEST_METHOD"] == "POST") {
     // add entry
-    $sql = "INSERT INTO orders (service, name, client, billing, comments) VALUES (?, ?, ?, ?, ?)";
+    $sql = "INSERT INTO tickets (`order`, subject, body, status, asignee) VALUES (?, ?, ?, ?, ?)";
     $stmt = mysqli_prepare($link, $sql);
-    mysqli_stmt_bind_param($stmt, "sssss", $param_service, $param_name, $param_client, $param_billing, $param_comments);
-    $param_service = $_POST["service"];
-    $param_name = $_POST["name"];
-    $param_client = $clientid;
-    $param_billing = $_POST["billing"];
-    $param_comments = $_POST["comments"];
+    mysqli_stmt_bind_param($stmt, "sssss", $param_order, $param_subject, $param_body, $param_status, $param_asignee);
+    $param_order = $_POST["order"];
+    $param_subject = $_POST["subject"];
+    $param_body = $_POST["body"];
+    $param_status = "open";
+    // choose asignee automatically
+    $helpdesk = array_filter($users, function ($t) { return $t["type"] == "helpdesk"; });
+    $admins = array_filter($users, function ($t) { return $t["type"] == "admin"; });
+    $asignee = null;
+    if (!empty($helpdesk))
+        $asignee = $helpdesk[array_rand($helpdesk)];
+    else
+        $asignee = $admins[array_rand($admins)];
+    $param_asignee = $asignee["id"];
 
     if (!mysqli_stmt_execute($stmt) || (mysqli_stmt_affected_rows($stmt) != 1)) {
         echo "SQL error.";
     } else {
-        // send admin mail
-        // Get admin mails
-        $sql = "SELECT email FROM users WHERE type = 'admin'";
+        // send ticket notification
+        // get id
+        // Get users
+        $sql = "SELECT id FROM tickets ORDER BY id DESC LIMIT 0, 1";
         $stmt = mysqli_prepare($link, $sql);
         mysqli_stmt_execute($stmt);
         $result = mysqli_stmt_get_result($stmt);
-        $admins = $result->fetch_all(MYSQLI_ASSOC);
+        $id = $result->fetch_all(MYSQLI_ASSOC);
 
-        foreach ($admins as $admin) {
-            $mailer->addAddress($admin["email"]);
-        }
-        
-        $mailer->Subject = "New service order request";
-        $mailer->Body = "Admins,\n\nUser $username requested service ".getservicebyid($_POST["service"])["name"]."\n\n"
-            ."Instance name: ".$_POST["name"]."\n"
-            ."Calculated billing: ".$_POST["billing"]."\n"
-            ."Comments:\n"
-            .$_POST["comments"]
+        $lines = explode("\n", $_POST["body"]);
+        $body = "";
+        foreach ($lines as $line) $body .= ">".$line;
+
+        $mailer->addAddress($asignee["email"]);
+        $mailer->addReplyTo(getuserbyid($clientid)["email"]);
+        $mailer->Subject = "[Ticket ID: ".$id[0]["id"]."] ".$_POST["subject"];
+        $mailer->Body = "Helpdesk,\n\nUser $username opened new ticket for ".getorderbyid($_POST["order"])["name"]." (".getservicebyid(getorderbyid($_POST["order"])["service"])["name"]."):\n"
+            .$body
             ."\n\n--\nARFNET Client, Service, Ticket and Invoice Management System\nhttps://arf20.com";
+
         if (!$mailer->send()) {
             echo 'Mailer Error [ask arf20]: ' . $mailer->ErrorInfo;
         } else header("location: ".$_SERVER['SCRIPT_NAME']);
@@ -71,9 +94,22 @@ function getservicebyid($id) {
     }
 }
 
-function genoption($id, $name) {
-    return "<input type=\"radio\" name=\"service\" id=\"$id\" onclick=\"selectservice($id)\" value=\"$id\">"
-        ."<label for=\"$id\">$name</label><br>\n";
+function getuserbyid($id) {
+    global $users;
+    foreach ($users as $user) {
+        if ($user["id"] == $id) {
+            return $user;
+        }
+    }
+}
+
+function getorderbyid($id) {
+    global $orders;
+    foreach ($orders as $order) {
+        if ($order["id"] == $id) {
+            return $order;
+        }
+    }
 }
 
 ?>
@@ -84,39 +120,6 @@ function genoption($id, $name) {
         <meta charset="UTF-8">
         <link rel="stylesheet" type="text/css" href="/style.css">
         <title>ARFNET CSTIMS</title>
-        <script type="text/javascript">
-            var services = <?php echo json_encode($services); ?>;
-            var service;
-            function selectservice(id) {
-                service = services.find((element) => element["id"] == id);
-                document.getElementById("pricelabel").innerHTML = "Price: " + service["billing"];
-                document.getElementById("description").innerHTML = service["description"];
-                if (service["name"] == "vps") {
-                    document.getElementById("extraform").innerHTML
-                        = `<label><b>Options</b></label><br><label>Cores</label><br><select id=\"cpus\" onclick=\"update()\"><option value=\"1\">1</option><option value=\"2\">2</option><option value=\"3\">3</option><option value=\"4\">4</option></select><br>
-                        <label>Memory</label><br><select id=\"mem\" onclick=\"update()\"><option value=\"1\">1GB</option><option value=\"2\">2GB</option><option value=\"4\">4GB</option><option value=\"8\">8GB</option></select><br>
-                        <label>SSD</label><br><select id=\"ssd\" onclick=\"update()\"><option value=\"5\">5GB</option><option value=\"10\">10GB</option><option value=\"15\">15GB</option><option value=\"20\">20GB</option><option value=\"30\">30GB</option></select><br>
-                        <br><label id=\"calculated\">Calculated price: </label>`;
-                } else document.getElementById("extraform").innerHTML = "";
-                update();
-            }
-
-            function update() {
-                var comment = document.getElementById("commentbox").value;
-                if (service["name"] == "vps") {
-                    var cpus = document.getElementById("cpus").value;
-                    var mem = document.getElementById("mem").value;
-                    var ssd = document.getElementById("ssd").value;
-                    document.getElementById("comments").value = "Options:\ncpus: " + cpus + "\nmem: " + mem + "GB\nssd: " + ssd + "GB\n\nClient comment:\n" + comment;
-                    var price = (1*Number(cpus)**2) + (0.5*Number(mem)**2) + (0.02*Number(ssd)**2);
-                    document.getElementById("calculated").innerHTML = "Calculated price: " + price + " €/mo";
-                    document.getElementById("billing").value = price + " €/mo";
-                } else {
-                    document.getElementById("comments").value = "Client comment:\n" + comment;
-                    document.getElementById("billing").value = service["billing"];
-                }
-            }
-        </script>
     </head>
     <body>
         <header><a href="https://arf20.com/">
@@ -129,52 +132,21 @@ function genoption($id, $name) {
                     <h2>ARFNET Client Service Ticket and Invoice Management System</h2>
                     <h3><?php echo strtoupper($type[0]).substr($type, 1); ?> panel</h3>
                     <div class="form">
-                        <h3>Order a new service</h3>
+                        <h3>Open ticket</h3>
                         <form action="<?php echo $_SERVER['SCRIPT_NAME']; ?>" method="post">
-                            <div class="border">
-                                <label><b>Service</b></label><br>
-                                <div class="row">
-                                    <div class="col">
-                                        <label>Premium</dev><br>
-                                        <?php
-                                        foreach ($services as $service) {
-                                            if ($service["type"] != "premium") continue;
-                                            echo genoption($service["id"], $service["name"]);
-                                        }
-                                        ?>
-                                    </div>
-                                    <div class="col">
-                                        <label>Standard</dev><br>
-                                        <?php
-                                        foreach ($services as $service) {
-                                            if ($service["type"] != "standard") continue;
-                                            echo genoption($service["id"], $service["name"]);
-                                        }
-                                        ?>
-                                    </div>
-                                    <div class="col">
-                                        <label>Free</dev><br>
-                                        <?php
-                                        foreach ($services as $service) {
-                                            if ($service["type"] != "free") continue;
-                                            echo genoption($service["id"], $service["name"]);
-                                        }
-                                        ?>
-                                    </div>
-                                </div>
-                            </div>
-                            <br><label>Description</label><pre id="description"></pre>
-                            <label id="pricelabel">Price: </label><br>
-                            <br><div class="border" id="extraform"></div>
-                            <br><label>Instance name</label><br>
-                            <input type=text name="name"><br>
-                            <div id="commentcontainer">
-                                <br><label>Comments (describe use case and requirements)</label><br>
-                                <textarea id="commentbox" rows="10" cols="80" onchange="update()"></textarea><br>
-                            </div>
-                            <input type="hidden" name="billing" id="billing">
-                            <input type="hidden" name="comments" id="comments">
-                            <br><input type="submit" value="Place order">
+                            <label><b>Service</b></label><br>
+                            <select name="order">
+                                <?php
+                                foreach ($orders as $order) {
+                                    echo "<option value=\"".$order["id"]."\">".$order["name"]." (".getservicebyid($order["service"])["name"].")</option>\n";
+                                }
+                                ?>
+                            </select><br>
+                            <br><label><b>Subject</b></label><br>
+                            <input type="text" name="subject"><br>
+                            <br><label><b>Body</b></label><br>
+                            <textarea name="body" rows="10" cols="80"></textarea><br>
+                            <br><input type="submit" value="Open ticket">
                         </form>
                     </div>
                 </div>
author	arf20 <aruizfernandez05@gmail.com>	2024-03-20 20:00:31 +0100
committer	arf20 <aruizfernandez05@gmail.com>	2024-03-20 20:00:31 +0100
commit	a8f2bace985ee0419492a09e1d5a3879f2f5b96b (patch)
tree	acbcf11cb4ba4652a07077dffd46792e454bf8bc
parent	ec7700456c646b9089f63a272cf15ff4ea5895d6 (diff)
download	arfnet2-cstims-a8f2bace985ee0419492a09e1d5a3879f2f5b96b.tar.gz arfnet2-cstims-a8f2bace985ee0419492a09e1d5a3879f2f5b96b.zip