How to develop Projects(OSMS) Using PHP and Mysql Part 11

Rashmi Mishra
0

 

How to develop Projects(OSMS) Using PHP and Mysql 

Part 11


7️ Online Service Management System


 Module 3: Assign Technician to Request

Purpose:
Allows admin to assign available technicians to customer service requests.

Functionalities:

  •  Assign technician to request
  •  View assigned tasks
  •  Reassign or update technician

Project Structure:

│── /assignments/

   ── assign_technician.php     # Assign a technician to request

   ── assignment_list.php       # List all assignments

   ── update_assignment.php     # Update/reassign technician

Database Table:

CREATE TABLE technician_assignments (

    id INT AUTO_INCREMENT PRIMARY KEY,

    request_id INT,

    technician_id INT,

    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (request_id) REFERENCES service_requests(id),

    FOREIGN KEY (technician_id) REFERENCES technicians(id)

);

1️ assign_technician.php

This page assigns a technician to a specific service request.

<?php

$conn = new mysqli("localhost", "root", "", "service_db");

 

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

 

if(isset($_POST['assign'])) {

    $request_id = $_POST['request_id'];

    $technician_id = $_POST['technician_id'];

 

    $sql = "INSERT INTO technician_assignments (request_id, technician_id) VALUES ('$request_id', '$technician_id')";

 

    if ($conn->query($sql) === TRUE) {

        echo "Technician Assigned Successfully!";

    } else {

        echo "Error: " . $conn->error;

    }

}

?>

 

<h2>Assign Technician to Request</h2>

<form method="POST">

    <label>Request ID:</label>

    <input type="number" name="request_id" required><br><br>

 

    <label>Select Technician:</label>

    <select name="technician_id" required>

        <?php

        $result = $conn->query("SELECT id, name FROM technicians");

        while ($row = $result->fetch_assoc()) {

            echo "<option value='{$row['id']}'>{$row['name']}</option>";

        }

        ?>

    </select><br><br>

 

    <button type="submit" name="assign">Assign Technician</button>

</form>


2️ assignment_list.php

This page lists all current technician assignments.

<?php

$conn = new mysqli("localhost", "root", "", "service_db");

 

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

 

$sql = "SELECT ta.id, sr.description as request_desc, t.name as technician_name, ta.assigned_at

        FROM technician_assignments ta

        JOIN service_requests sr ON ta.request_id = sr.id

        JOIN technicians t ON ta.technician_id = t.id";

 

$result = $conn->query($sql);

?>

 

<h2>Assigned Technicians</h2>

<table border="1" cellpadding="8">

    <tr>

        <th>ID</th>

        <th>Request</th>

        <th>Technician</th>

        <th>Assigned At</th>

    </tr>

    <?php while($row = $result->fetch_assoc()): ?>

    <tr>

        <td><?= $row['id'] ?></td>

        <td><?= $row['request_desc'] ?></td>

        <td><?= $row['technician_name'] ?></td>

        <td><?= $row['assigned_at'] ?></td>

    </tr>

    <?php endwhile; ?>

</table>


3️ update_assignment.php

This page allows you to reassign a technician to a request.

<?php

$conn = new mysqli("localhost", "root", "", "service_db");

 

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

 

if(isset($_POST['update'])) {

    $assignment_id = $_POST['assignment_id'];

    $technician_id = $_POST['technician_id'];

 

    $sql = "UPDATE technician_assignments SET technician_id = '$technician_id', assigned_at = CURRENT_TIMESTAMP WHERE id = '$assignment_id'";

 

    if ($conn->query($sql) === TRUE) {

        echo "Assignment Updated Successfully!";

    } else {

        echo "Error: " . $conn->error;

    }

}

?>

 

<h2>Update Technician Assignment</h2>

<form method="POST">

    <label>Assignment ID:</label>

    <input type="number" name="assignment_id" required><br><br>

 

    <label>Select New Technician:</label>

    <select name="technician_id" required>

        <?php

        $result = $conn->query("SELECT id, name FROM technicians");

        while ($row = $result->fetch_assoc()) {

            echo "<option value='{$row['id']}'>{$row['name']}</option>";

        }

        ?>

    </select><br><br>

 

    <button type="submit" name="update">Update Assignment</button>

</form>

 


Post a Comment

0Comments

Post a Comment (0)