package com.csbook.restaurant.utility;

import java.sql.*;
import javax.swing.*;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: </p>
 * @author pengtao
 * @version 1.0
 */

public class CheckUser{
  //構造數據庫連接參數
    private String url="jdbc:sqlserver://localhost:1433;DatabaseName=Restaurant;user=user;password=user";
    public CheckUser() {
      try {
        //裝載數據庫驅動程序
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
      }
      catch (Exception ex) {
        ex.printStackTrace();
      }
    }

    //檢查指定用戶是否為合法用戶
    public boolean isValidUser(String operator,String password)
    {
      Connection con=null;
      PreparedStatement ps=null;
      ResultSet rs=null;
      boolean isValid=false;
      try{
        con = DriverManager.getConnection(url);
        String queryStr = "select * from operator WHERE id=? and password=?";
        ps = con.prepareStatement(queryStr);
        ps.setString(1, operator);
        ps.setString(2, password);
        rs = ps.executeQuery();
        if (rs.next())
          isValid = true;
      }
      catch(SQLException exc){
         exc.printStackTrace();
           }
      finally{
           if (rs != null) try {  rs.close();}catch (SQLException ignore) {}
           if (ps != null) try{  ps.close();}catch (SQLException ignore) {}
           if (con != null) try{con.close();}catch (SQLException ignore) {}
      }
      return isValid;
    }

    //更改用戶密碼
    public void updatePassword(String operator,String password)
    {
        Connection con=null;
        PreparedStatement ps=null;
        try{
          con = DriverManager.getConnection(url);
          String updateStr = "update operator set password=? WHERE id=?";
          ps = con.prepareStatement(updateStr);
          ps.setString(1, password);
          ps.setString(2, operator);
          ps.executeUpdate();
        }
        catch(SQLException exc){
           exc.printStackTrace();
             }
        finally{
         if (ps != null) try{  ps.close();}catch (SQLException ignore) {}
         if (con != null) try{con.close();}catch (SQLException ignore) {}
        }
    }

   //檢查指定用戶是否存在
    public boolean userExist(String userID)
    {
      Connection con = null;
      PreparedStatement ps = null;
      ResultSet rs=null;
      boolean exist=false;
        try{
          con = DriverManager.getConnection(url);
          String selectStr = "select id from operator where id=?";
          ps = con.prepareStatement(selectStr);
          ps.setString(1, userID);
          rs=ps.executeQuery();
          if(rs.next())
            exist=true;
        }
        catch(SQLException exc){
           exc.printStackTrace();
        }
        finally{
           if (rs != null) try{  rs.close();}catch (SQLException ignore) {}
           if (ps != null) try{  ps.close();}catch (SQLException ignore) {}
           if (con != null) try{con.close();}catch (SQLException ignore) {}
        }
        return exist;
      }

    //添加新用戶
    public void addOperator(String userID,String username,String userType,String password,String PICNo,String tel,String addr,String remark)
    {
        Connection con=null;
        PreparedStatement ps=null;
        try{
          con = DriverManager.getConnection(url);
          String updateStr = "insert into operator(id,password,name,type,tel,addr,PICNo,remark) values(?,?,?,?,?,?,?,?)";
          ps = con.prepareStatement(updateStr);
          ps.setString(1, userID);
          ps.setString(2, password);
          ps.setString(3,username);
          ps.setString(4,userType);
          ps.setString(5,tel);
          ps.setString(6,addr);
          ps.setString(7,PICNo);
          ps.setString(8,remark);
          ps.executeUpdate();
        }
        catch(SQLException exc){
           exc.printStackTrace();
             }
        finally{
         if (ps != null) try{  ps.close();}catch (SQLException ignore) {}
         if (con != null) try{con.close();}catch (SQLException ignore) {}
        }
    }

    //刪除用戶
    public boolean deleteOperator(String userID)
    {
       Connection con=null;
       PreparedStatement ps=null;
       boolean succeed=true;
       try{
         con = DriverManager.getConnection(url);
         String deleteStr="delete from operator where id=?";
         ps=con.prepareStatement(deleteStr);
         ps.setString(1,userID);
         ps.executeUpdate();
       }
       catch(SQLException exc){
           succeed=false;
       }
      finally{
         if (ps != null) try{  ps.close();}catch (SQLException ignore) {}
         if (con != null) try{con.close();}catch (SQLException ignore) {}
      }
      return succeed;
    }

    //獲得用戶類型
    public String getUserType(String user)
    {
       Connection con=null;
       PreparedStatement ps=null;
       ResultSet rs=null;
       String userType="";
       try{
         con = DriverManager.getConnection(url);
         String queryStr = "select * from operator WHERE id=?";
         ps = con.prepareStatement(queryStr);
         ps.setString(1, user);
         rs = ps.executeQuery();
         if (rs.next())
           userType = rs.getString("type");
       }
       catch(SQLException exc){
          exc.printStackTrace();
            }
       finally{
            if (rs != null) try {  rs.close();}catch (SQLException ignore) {}
            if (ps != null) try{  ps.close();}catch (SQLException ignore) {}
            if (con != null) try{con.close();}catch (SQLException ignore) {}
       }
       return userType;
    }

  }