package grad.util.user;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */
import grad.util.DataBase;
import grad.util.course.*;
import java.sql.*;
import java.util.*;

public class op_user {
  public op_user() {
  }
  /**
   * 通過用戶編號和用戶類型，返回用戶的詳細信息
   * @param user_code
   * @param type
   * @return
   */
  public user getUserByUserCode(String user_code,String type){
   Connection conn=null;
     Statement st=null;
     ResultSet rs=null;
     String sql ="select * from t_user where account='"+user_code+"' and type='"+type+"'";
     try{
       DataBase ds = new DataBase();
       conn = ds.conn;
       st = conn.createStatement();
       user us=new user();
       rs = st.executeQuery(sql);
       if (rs.next()) {
          us.setId(rs.getInt(1));
          System.out.println(rs.getString(2));
          us.setAccount(rs.getString(2));
          us.setPassword(rs.getString(3));
          us.setType(rs.getInt(4));
          us.setName(rs.getString(5));
          us.setSex(rs.getString(6));
          us.setCollege(rs.getString(7));
          us.setTeacher(rs.getString(8));
       }
       return us;
     }catch(Exception e){System.out.println(e.getMessage());return null;}
  }
  /**
   * 往數據庫添加一條用戶記錄
   * @param us －用戶對象
   * @return
   */
public boolean addUser(user us)
{
  Connection conn = null;
  Statement st = null;
  String sql =
      "insert into t_user(id,account,password,type,name,sex,college,teacher)values(" +
      " '"+us.getId()+"',"+
      " '"+us.getAccount()+"',"+
      " '"+us.getAccount()+"',"+
      " '"+us.getType()+"',"+
      " '"+grad.util.DataFormate.toGb(us.getName())+"',"+
      " '"+grad.util.DataFormate.toGb(us.getSex())+"',"+
      " '"+grad.util.DataFormate.toGb(us.getCollege())+"',"+
      " '"+grad.util.DataFormate.toGb(us.getTeacher())+"')";
  System.out.println(sql);
  try {
    DataBase ds = new DataBase();
    conn = ds.conn;
    st = conn.createStatement();
    st.executeUpdate(sql);
     return true;
  }
  catch (Exception e) {
    System.out.println(e.getMessage());
    return false;
  }
}
/**
 * 更新用戶信息
 * @param us －用戶對象
 * @return
 */
public boolean updateUser(user us)
{
 Connection conn = null;
 Statement st = null;
 String sql =
     "update t_user "+
     "  name='"+(us.getName())+"',"+
     "  sex='"+(us.getSex())+"',"+
     "  college='"+(us.getCollege())+"',"+
     "  teacher='"+(us.getTeacher())+"'"+
     " where account='"+us.getAccount()+"'";
 System.out.println(sql);
 try {
   DataBase ds = new DataBase();
   conn = ds.conn;
   st = conn.createStatement();
   st.executeUpdate(sql);
    return true;
 }
 catch (Exception e) {
   System.out.println(e.getMessage());
   return false;
 }
}
  /**
   * 查詢選擇了指定course_id課程的全部學生
   * @param course_id－課程編號
   * @return
   */
 public ArrayList getAllStudent(String course_id)
 {
    Connection conn=null;
    Statement st=null;
    ResultSet rs=null;

    String sql ="select a.* from t_user a,xk_record b "+
               " where a.account=b.stu_code "+
               " and a.type=1 "+
              " and b.course_code='"+course_id+"'" +
              " and b.select_year >getdate()-180 ";
    ArrayList al=new ArrayList();
    try{
      DataBase ds = new DataBase();
      conn = ds.conn;
      st = conn.createStatement();
      rs = st.executeQuery(sql);
      while (rs.next()) {
        user us=new user();
         us.setId(rs.getInt(1));
         us.setAccount(rs.getString(2));
         us.setPassword(rs.getString(3));
         us.setType(rs.getInt(4));
         us.setName(rs.getString(5));
         us.setSex(rs.getString(6));
         us.setCollege(rs.getString(7));
         us.setTeacher(rs.getString(8));
         al.add(us);
      }
      return al;
    }catch(Exception e){System.out.println(e.getMessage());return null;}
 }
 /**
  * 供管理員查詢用戶信息，管理員可以按帳號，姓名，院系查詢用戶信息
  * @param account
  * @param name
  * @param college
  * @param type
  * @return
  */
 public ArrayList getAllUserQuery(String account,String name,String college,String type)
{
   Connection conn=null;
   Statement st=null;
   ResultSet rs=null;
   String sql ="select * from t_user where type='"+type+"'";
   if(account!=null&&!account.equals(""))
     sql+=" and account='"+account+"'";
   if(name!=null&&!name.equals(""))
     sql+=" and name ='"+name+"'";
   if(college!=null&&!college.equals(""))
     sql+=" and college like '%"+college+"%'";
   System.out.println(sql);
   ArrayList al=new ArrayList();
   try{
     DataBase ds = new DataBase();
     conn = ds.conn;
     st = conn.createStatement();
     rs = st.executeQuery(sql);
     while (rs.next()) {
       user us=new user();
        us.setId(rs.getInt(1));
        us.setAccount(rs.getString(2));
        us.setPassword(rs.getString(3));
        us.setType(rs.getInt(4));
        us.setName(rs.getString(5));
        us.setSex(rs.getString(6));
        us.setCollege(rs.getString(7));
        us.setTeacher(rs.getString(8));
        al.add(us);
      }
     return al;
   }catch(Exception e){System.out.println(e.getMessage());return null;}
}
 /**
  * 判斷是否允許用戶登錄
  * @param user_code
  * @param password
  * @param type
  * @return
  */
 public boolean isLogin(String user_code,String password,String type)
 {
   user a = this.getUserByUserCode(user_code,type);
   if(a.getPassword().equals(password))
     return true;
   else
     return false;

 }
 /**
  * 修改密碼
  * @param user_code
  * @param password
  * @return
  */
 public boolean modPass(String user_code,String password)
{
   Connection conn=null;
     Statement st=null;
     ResultSet rs=null;
     user us=new user();
     String sql ="update t_user set password='"+password+"' where account='"+user_code+"'";
     try{
       DataBase ds = new DataBase();
       conn = ds.conn;
       st = conn.createStatement();
       st.executeUpdate(sql);
       return true;
     }catch(Exception e){System.out.println(e.getMessage());return false;}

}
   /**
    * 查詢指定user_code學生選擇的全部課程
    * @param user_code-學生編號
    * @return
    */
public ArrayList getUserCourse(String user_code)
   {
  Connection conn=null;
   Statement st=null;
   ResultSet rs=null;
   String sql ="select a.* from t_course a,xk_record b where a.course_code=b.course_code and b.stu_code='"+user_code+"'";
   ArrayList al=new ArrayList();
   try{
     DataBase ds = new DataBase();
     conn = ds.conn;
     st = conn.createStatement();
     rs = st.executeQuery(sql);
     while (rs.next()) {
       course cs=new course();
        cs.setId(rs.getInt(1));
        cs.setCourse_code(rs.getString(2));
        cs.setcourse_name(rs.getString(3));
        cs.setcollege(rs.getString(4));
        cs.setcourse_time(rs.getString(5));
        cs.setstart_end(rs.getString(6));
        cs.setcourse_tech(rs.getString(7));
        cs.setcourse_credit(rs.getString(8));
        cs.setcourse_site(rs.getString(9));
        cs.setcourse_note(rs.getString(10));
        al.add(cs);
     }
     return al;
   }catch(Exception e){
     System.out.println(e.getMessage());
     return null;
   }
  }
  /**
   * 查詢指定課程編號的課程信息
   * @param cid－課程編號
   * @return
   */
  public course getCourseByid(String cid)
   {
     Connection conn=null;
   Statement st=null;
   ResultSet rs=null;
   String sql ="select * from t_course where course_code='"+cid+"'";
   try{
     DataBase ds = new DataBase();
     conn = ds.conn;
     course cs=new course();
     st = conn.createStatement();
     rs = st.executeQuery(sql);
     if (rs.next()) {
        cs.setId(rs.getInt(1));
        cs.setCourse_code(rs.getString(2));
        cs.setcourse_name(rs.getString(3));
        cs.setcollege(rs.getString(4));
        cs.setcourse_time(rs.getString(5));
        cs.setstart_end(rs.getString(6));
        cs.setcourse_tech(rs.getString(7));
        cs.setcourse_credit(rs.getString(8));
        cs.setcourse_site(rs.getString(9));
        cs.setcourse_note(rs.getString(10));
     }
     return cs;
   }catch(Exception e){System.out.println(e.getMessage());return null;}

   }
   /**
    * 從數據庫中取出全部課程
    * @return
    */
   public ArrayList getAllCourse()
     {
       Connection conn=null;
     Statement st=null;
     ResultSet rs=null;
     ArrayList al=new ArrayList();
     String sql ="select * from t_course ";
     try{
       DataBase ds = new DataBase();
       conn = ds.conn;
       st = conn.createStatement();
       rs = st.executeQuery(sql);
       while (rs.next()) {
         course cs=new course();
          cs.setId(rs.getInt(1));
          cs.setCourse_code(rs.getString(2));
          cs.setcourse_name(rs.getString(3));
          cs.setcollege(rs.getString(4));
          cs.setcourse_time(rs.getString(5));