package app.servlets;

import app.bean.DeptBean;
import app.bean.EmpBean;
import app.bean.SalgradeBean;
import app.entity.Dept;
import app.entity.Emp;
import app.entity.Salgrade;

import javax.ejb.EJB;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;

//@WebServlet("/search")
public class Select1Servlet extends HttpServlet {

    @EJB
    private EmpBean empBean;
    private DeptBean deptBean;
    private SalgradeBean salgradeBean;

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html");
        req.setCharacterEncoding("UTF-8");
        req.getRequestDispatcher("/searchview.jsp").forward(req, resp);
    }


    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
//        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/users";
        String userName = "root";
        String password = "7777";


        Statement st;
        try {
//            Class.forName("com.mysql.jdbc.Driver");
//            conn = DriverManager.getConnection(url , userName, password);
            System.out.println("Connected!");
            String numb = request.getParameter("numb");

            ArrayList al = null;
            ArrayList pid_list = new ArrayList();

//            String query = "SELECT emp.ename, emp.job, emp.sal, dept.dname, salgrade.grade FROM dept,emp,salgrade WHERE (emp.empno = '" + numb + "' ) AND (emp.deptno = dept.deptno) and (emp.sal BETWEEN losal and hisal)  ";

//            System.out.println("query " + query);
//            st = conn.createStatement();
//            ResultSet rs = st.executeQuery(query);
            Emp emp = empBean.get(Integer.valueOf(numb));
            String ename= emp.getEname();
            String job = emp.getJob();
            Integer sal = emp.getSal();
            Integer deptnoEmp= emp.getDeptno();
            Dept dept = deptBean.get(Integer.valueOf(deptnoEmp));
            String dname = dept.getDname();
            Salgrade salgrade = salgradeBean.get(sal);
            Integer grade = salgrade.getGrade();

//            while (rs.next()) {
                al = new ArrayList();
//                out.println(rs.getString(1));
//                out.println(rs.getString(2));
//                out.println(rs.getString(3));
//                out.println(rs.getString(4));
                al.add(ename);
                al.add(job);
                al.add(sal);
                al.add(dname);
                al.add(grade);

                System.out.println("al :: " + al);
                pid_list.add(al);
//            }

            request.setAttribute("piList", pid_list);
            RequestDispatcher view = request.getRequestDispatcher("/searchview.jsp");
            view.forward(request, response);
            conn.close();
            System.out.println("Disconnected!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}