Ticketing system

1 files changed, 75 insertions, 103 deletions

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>