appointmentregister.jsp

<%--
    Document   : appointmentregister
    Created on : Jul 1, 2018, 10:56:02 PM
    Author     : hp
--%>

<%
    String UserID = "";
    if (((String) session.getAttribute("UserID")) != null) {
        UserID = (String) session.getAttribute("UserID");
    }
    String Group = "";
    if (((String) session.getAttribute("Group")) != null) {
        Group = (String) session.getAttribute("Group");
    }

    String Name = "";
    if (((String) session.getAttribute("Name")) != null) {
        Name = (String) session.getAttribute("Name");
    }
%>
<%@page import = "java.sql.ResultSet"%>
<%@page import = "java.sql.Connection"%>
<%@page import = "java.sql.Statement"%>
<%@page import = "java.sql.DriverManager"%>
<%@page contentType = "text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>VSMS</title>
    </head>
    <body>
        <%
            //TO CONNECT WITH dms DATABASE
            Connection connection = null;

            try {
                Class.forName("org.gjt.mm.mysql.Driver");
                String url = "jdbc:mysql://localhost/dbcarservices";
                String user = "root";
                String pwd = "";
                connection = DriverManager.getConnection(url, user, pwd);
                Statement statement = connection.createStatement();

                //GET DATA FROM STAFF FORM
                String platno = request.getParameter("platno");
                String appointmentdate = request.getParameter("appointmentdate");
                String appointmentstatus = "In Progress";
                String id = "";
                int min = 100;
                int count = 0;

                String select = "select * from working where workingdate = '" + appointmentdate + "'";
                ResultSet rows = statement.executeQuery(select);

                while (rows.next()) {
                    if (min > rows.getInt("totalwork")){
                        min = rows.getInt("totalwork");
                        id = rows.getString("staffID");
                        count++;
                    }
                }
                 if (count==0){
                     out.println("<script>alert('Mechanic not available ! ');window.history.back();</script>");
                 }

                int total = min + 1;
                     String update = "UPDATE working "
                        + "SET "
                        + "totalwork = '"+total+"' "
                        + "WHERE staffID = '" + id + "'";

                int j = statement.executeUpdate(update);


                //UPDATE STAFF TABLE
                String sql = "UPDATE appointment "
                        + "SET "
                        + "appointmentstatus = '" + appointmentstatus + "', staffID =  '" + id + "'"
                        + "WHERE platno = '" + platno + "' AND appointmentdate = '" + appointmentdate + "'";

                int i = statement.executeUpdate(sql);


                if (i== 1) {
                    out.println("<script>alert('Appointment for "+platno+" on "+appointmentdate+" Successfully Registered!  ');document.location.href='appointmentlist.jsp';</script>");
                } else {
                    out.println("<script>alert('Failed to Register an Appointment for "+platno+" on "+appointmentdate+". Try Again ! ');window.history.back();</script>");
                }


                if (connection != null) {
                    connection.close();
                }

            } catch (Exception e) {
                out.println("Error" + e);
                System.out.println("Error" + e);
                //It is a good practice to use System.out.println()
                //for printing the error messages in the server console
            } finally {
                if (null != connection) {
                    connection.close();
                }
            }

        %>
    </body>
</html>