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

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

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

  public ResultSet executeQuery(String sql) {//主要用于执行查询语句
  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) {//执行一条SQL语句,执行异常时返回FALSE
    try {
      connect = DriverManager.getConnection(sConnStr,user,pass);
      Statement stmt = connect.createStatement();
      stmt.executeQuery(sql);
    }
    catch(SQLException ex) {
      System.err.println("EXECUTEORDER:"+ex.getMessage());
      return false;
    }
    return true;
  }


public long getBlocked(String username){//0和负数表示没被禁言，正数表示被禁言
  ResultSet rs1=null;
  String strSQL="select * from block where username='"+username+"'";
  rs1=executeQuery(strSQL);
  try{
    if(rs1.next()){
      String strSQL2="select period_diff(freetime,now()) as res from block where username='"+username+"';";
      ResultSet rs2=executeQuery(strSQL2);
      if(rs2.next()){
        long lefttime = rs2.getLong("res");
        if(lefttime<=0){
          free(username);
          lefttime=0;
        }
        return lefttime;
      }
    }
  }
  catch(Exception e){
    System.err.println(e.toString());
    return -1;
  }
  return 0;
}

public boolean block(String username,int blocktime){//禁止说话
  String strSQL="insert into block (username,freetime) values('"+username+"',date_add(now(),interval "+blocktime+" minute));";
  if(getBlocked(username)>0){
    strSQL=new String("update block set freetime=date_add(now(),interval "+blocktime+" minute) where username='"+username+"';");
  }
  return executeOrder(strSQL);
}

public boolean free(String username){
  String strSQL="delete from block where username='"+username+"'";
  return executeOrder(strSQL);
}

public boolean deleteUser(String username){//删除成员
  if(getBlocked(username)>0){
    free(username);
  }
  String strSQL="delete from user where username='"+username+"' and not grade=1";//不能删除管理员
  return executeOrder(strSQL);
}

public int[] getCount(String table,int pagesize){//对信息进行分页，i[0]表示记录数，i[1]表示页数
  rs=null;
  int i[];
  i=new int[2];
  String strSQL="select count(*) from `"+table+"`";
  rs=executeQuery(strSQL);
  try{
    if (rs.next()) {
      i[0]=rs.getInt(1);
    }
  }
  catch(Exception e){
  }
  i[1]=(i[0]/pagesize)+(i[0]%pagesize==0?0:1);
  return i;
}

public ResultSet getPage(String table,int currentpage,int pagesize){//到到指定页内指定数目的记录
  rs=null;
  String strSQL="SELECT * FROM `"+table+"` order by id desc LIMIT "+((currentpage-1)*pagesize)+","+pagesize;
  rs=executeQuery(strSQL);
  return rs;
}

}