package hospital.Controller.Actions.UserRole;

import org.apache.struts.action.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.*;

public class DispatchRoleAction extends Action {
    String userNo = "";
    String[] checkedRoleItems = null;
    String successFlag = "0";

    public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {

        String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
        String dbURL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=hospital";
        String userName = "sa";
        String userPwd = "";
        Connection dbConn;

        ArrayList listRole1 = new ArrayList(20);
        ArrayList listRole2 = new ArrayList(20);

        try {
            Class.forName(driverName);
            dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
            Statement stmt = dbConn.createStatement();

            //初始化頁面的所有參數
            initPageParameters(form, request);

            if (checkedRoleItems != null) {
                saveRoleItems(stmt);
            }

            createRoleItems(stmt, listRole1, listRole2);

            stmt.close();
            dbConn.close();

        } catch (Exception ex) {
            ex.printStackTrace();
        }

        returnPageParameters(request, listRole1, listRole2);
        return mapping.findForward("dispatchrole");
    }

    private void setPropertyToOrginate() {
        userNo = "";
        checkedRoleItems = null;
        successFlag = "0";
    }

    private void initPageParameters(ActionForm form, HttpServletRequest request) {
        setPropertyToOrginate();
        userNo = request.getParameter("userNo");
        checkedRoleItems = request.getParameterValues("list2");
    }

    private void createRoleItems(Statement stmt, ArrayList listRole1, ArrayList listRole2) throws SQLException {
        String sql = "";
        ResultSet rs = null;

        sql = "select * from sys角色表 where 角色編號 not in (select 角色編號 from sys用戶角色表 where 用戶編號='" + userNo + "')";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String[] contentRow = new String[2];
            contentRow[0] = rs.getString("角色編號");
            contentRow[1] = rs.getString("角色名稱");
            listRole1.add(contentRow);
        }

        sql = "select sys角色表.角色編號,sys角色表.角色名稱 from sys用戶角色表,sys角色表 where sys用戶角色表.角色編號=sys角色表.角色編號 and sys用戶角色表.用戶編號='" + userNo + "'";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String[] contentRow = new String[2];
            contentRow[0] = rs.getString("角色編號");
            contentRow[1] = rs.getString("角色名稱");
            listRole2.add(contentRow);
        }
        rs.close();
    }

    private void saveRoleItems(Statement stmt) {
        try {
            System.out.println(checkedRoleItems.length);
            String sql = "delete from sys用戶角色表 where 用戶編號='" + userNo + "'";
            stmt.executeUpdate(sql);
            for (int i = 0; i < checkedRoleItems.length; i++) {
                sql = "insert into sys用戶角色表(角色編號,用戶編號) values('" + checkedRoleItems[i] + "','" + userNo + "')";
                stmt.executeUpdate(sql);
            }
            successFlag = "1";
        } catch (SQLException ex) {
            ex.printStackTrace();
            successFlag = "-1";
        }
    }

    private void returnPageParameters(HttpServletRequest request, ArrayList listRole1, ArrayList listRole2) {
        request.setAttribute("userNo", userNo);
        request.setAttribute("listRole1", listRole1);
        request.setAttribute("listRole2", listRole2);
        request.setAttribute("successFlag", successFlag);
    }
}