package chatweb;
import java.sql.*;
import java.io.*;
import java.util.Vector;

public class conn {
  String sDBDriver = "org.gjt.mm.mysql.Driver";
  String sConnStr = "jdbc:mysql://localhost:3306/hoodb";
  String user = "root";
  String pass = "root";
  Connection connect = null;
  ResultSet rs = null;

  public conn() {
    try {
      Class.forName(sDBDriver).newInstance();
    }
    catch(Exception e) {
      System.err.println( "forName:"+e.getMessage());
    }
  }

  public ResultSet executeQuery(String sql) {//主要用于執(zhí)行查詢語(yǔ)句
    rs = null;
    try {
      connect = DriverManager.getConnection(sConnStr,user,pass);
      Statement stmt = connect.createStatement();
      rs = stmt.executeQuery(sql);
    }
    catch(SQLException ex) {
      System.err.println("EXECUTEQUERY:"+ex.getMessage());
    }
    return rs;
  }

  public boolean executeOrder(String sql) {//執(zhí)行一條SQL語(yǔ)句，插入異常時(shí)返回FALSE
    try {
      connect = DriverManager.getConnection(sConnStr,user,pass);
      Statement stmt = connect.createStatement();
  //    stmt.executeQuery(sql);
      stmt.executeUpdate(sql);
    }
    catch(SQLException ex) {
      System.err.println("EXECUTEORDER:"+ex.getMessage());
      return false;
    }
    return true;
  }


  public int register(String regname,String password){//返回1表示注冊(cè)成功，返回2表示用戶名已存在，返回3表示插入異常
    String strSQL="select * from user where username='"+regname+"'";
    ResultSet rs=executeQuery(strSQL);
    try{
      if (rs.next())
        return 2;
    }
    catch(Exception e){
      System.err.println("REGISTER:"+e.getMessage());
      return 3;
    }
    String strIns="insert into  user (username,password,lasttime) Values('"+regname+"','"+password+"',now())";
    try {
      connect = DriverManager.getConnection(sConnStr,user,pass);
      Statement stmt = connect.createStatement();
    //  stmt.executeQuery(strIns);
      stmt.executeUpdate(strIns);
    }
    catch(SQLException ex) {
      System.err.println("REGISTER::"+ex.getMessage());
      return 3;
    }

    return 1;
  }

  public int login(String logname,String password){//返回用戶等級(jí)表示注冊(cè)成功;返回-1表示密碼錯(cuò)誤;返回-2表示用戶名錯(cuò)誤;返回-3表示數(shù)據(jù)庫(kù)操作異常
    try{
      String strSQL="select * from user where username='"+logname+"'";
      ResultSet rs=executeQuery(strSQL);
      if(rs.next()){
        if (!password.equals(rs.getString("password")))
          return -1;
        else{
          executeOrder("update user set lasttime=now() where username='"+logname+"';");
          return rs.getInt("grade");
        }
      }
        else
                return -2;

    }
    catch(Exception e){
      System.err.println("LOGIN:"+e.getMessage());
      return -3;
    }
  }

//Option for message
  public int checkNewMsg(String name){//檢查新短信息，返回新短信息的數(shù)量
    int newMsgCount=0;
    try{
      String strSQL="select * from message where `to`='"+name+"' and isnew=1";
      ResultSet rs=executeQuery(strSQL);
      while(rs.next()){
        newMsgCount+=1;
      }
    }
    catch(Exception e){
      }
    return newMsgCount;
  }

  public Vector getMsg(String name){//獲取短信息
    String strSQL="select * from message where `to`='"+name+"' order by id";
    ResultSet rs=null;
    Vector msgs=new Vector();
    try{
      rs = executeQuery(strSQL);
      while (rs.next()) {
        msg ms=new msg();
        ms.setId(rs.getInt("id"));
        ms.setFromUser(rs.getString("from"));
        ms.setToUser(rs.getString("to"));
        ms.setTitle(rs.getString("title"));
        ms.setContent(rs.getString("content"));
        ms.setIsnew(rs.getBoolean("isnew"));
        ms.setSendTime(rs.getTime("sendtime"));
        ms.setSendDate(rs.getDate("sendtime"));
        msgs.addElement(ms);
      }
    }
    catch(Exception e){

    }
    String strMod="update message set isnew=0 where `to`='"+name+"'";
    executeOrder(strMod);
    return msgs;
  }

  public boolean addMsg(String strFrom,String strTo,String strContent,String strTitle){//添加短信息
    String strCheck="select * from user where username='"+strTo+"'";
    ResultSet rs=executeQuery(strCheck);
    try{
      if(!rs.next())
        return false;
    }
    catch(Exception e){
    }
    String strSQL="INSERT INTO message (`from`, `to`, content, title, sendtime) VALUES ('"+strFrom+"', '"+strTo+"', '"+strContent+"', '"+strTitle+"', now())";
    return executeOrder(strSQL);
  }

  public boolean deleteMsg(int id,String toUser){//刪除短信息
    String StrCheck="select * from message where id="+id;
    ResultSet rs=executeQuery(StrCheck);
    try{
      if(rs.next()){
        if(!toUser.equals(rs.getString("to")))
          return false;
      }
    }
    catch(Exception e){
      return false;
    }
    String strSQL="delete from message where id="+id;
    return executeOrder(strSQL);
  }

  public boolean modify(String username,String oldPassword,String newPassword){//修改密碼
    String strSQL="update user set password='"+newPassword+"' where username='"+username+"' and password='"+oldPassword+"' and not grade=1";//不允許修改管理員密碼
    try {
      connect = DriverManager.getConnection(sConnStr,user,pass);
      Statement stmt = connect.createStatement();
      int updateCount=stmt.executeUpdate(strSQL);
      if(updateCount>0)
        return true;
    }
    catch(SQLException ex) {
      System.err.println("EXECUTEORDER:"+ex.getMessage());
    }
    return false;
  }
}