package user;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import util.stringUtil;
import db.dbconn;
/
public class user_operation {
    /**
     * 功能:判斷自動(dòng)生成的角色是否已經(jīng)生成用戶
     * 輸入:foreign_id為教師表或?qū)W生表中的ID號(hào),sysuser_role為用戶角色
     * 輸出:返回0表未生成,返回1表已生成,返回3表未知
     * 說明:只要有sysuser表中可查到記錄,即表示已經(jīng)生成
     */
    public int isAutoGenOK(long foreign_id,int sysuser_role){
        if(foreign_id==0||sysuser_role==0||sysuser_role==1||sysuser_role==2)
        //用戶角色為1表系統(tǒng)管理員,為2表教務(wù)人員,這兩種角色手工生成,所以不為自動(dòng)生成
            return 3;
        String sqlString=null;//SQL語句字符串
        ResultSet rs=null;//結(jié)果記錄集
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫連接對(duì)象
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫連接
        if(dbconn==null) return 3;//連接失敗
        sqlString="select * from sysuser where foreign_id=? and sysuser_role=?";
        try{
            PreparedStatement preSQLSelect=dbconn.prepareStatement(sqlString);
            preSQLSelect.setLong(1,foreign_id);
            preSQLSelect.setInt(2,sysuser_role);
            rs=preSQLSelect.executeQuery();
            if(rs.next()) return 1;
            else return 0;
          }catch(Exception e){
            System.out.print(e);
            return 3;
          }   
    }
    public int isAdminGenOK(long foreign_id,int sysuser_role){
        if(foreign_id==0||sysuser_role==0)
            return 3;
        String sqlString=null;//SQL語句字符串
        ResultSet rs=null;//結(jié)果記錄集
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫連接對(duì)象
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫連接
        if(dbconn==null) return 3;//連接失敗
        sqlString="select * from sysuser where foreign_id=? and sysuser_role=?";
        try{
            PreparedStatement preSQLSelect=dbconn.prepareStatement(sqlString);
            preSQLSelect.setLong(1,foreign_id);
            preSQLSelect.setInt(2,sysuser_role);
            rs=preSQLSelect.executeQuery();
            if(rs.next()) return 1;
            else return 0;
          }catch(Exception e){
            System.out.print(e);
            return 3;
          }   
    }
    /**
     * 功能：生成老師用戶
     */
    public String genTeacherUser(String teacher_id[]){
        String returnString=new String("");
        String sqlString=null;//SQL語句字符串
        ResultSet rs=null;//結(jié)果記錄集
        ResultSet rs1=null;//另一結(jié)果記錄集
        Statement sql=null;//SQL語句對(duì)象
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫連接對(duì)象
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫連接
        if(dbconn==null) return returnString;//連接失敗
        try{
            sqlString="select * from teacher";
            System.out.println(teacher_id.length);
            int j=0;//加長(zhǎng)SQL語句的次數(shù)
            if(teacher_id.length!=0){//生成部分教師用戶
                for(int i=0;i<teacher_id.length;i++)
                    if(teacher_id[i]!=null&&teacher_id[i].length()!=0&&!teacher_id[i].equalsIgnoreCase("null")){
                        if(j==0)
                            {sqlString=sqlString+" where teacher_id="+teacher_id[i];j++;}
                        else
                            sqlString=sqlString+" or teacher_id="+teacher_id[i];
                    }
                
            }
            sql=dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs=sql.executeQuery(sqlString);
            while(rs.next()){
                sqlString="select * from sysuser where foreign_id=" +
                		rs.getLong("teacher_id")+" and sysuser_role=3";
                sql=dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs1=sql.executeQuery(sqlString);
                if(!rs1.next()){//不存在插入，存在則不做事
                    sqlString="insert into sysuser(sysuser_name,sysuser_password,sysuser_role" +
                    		",foreign_id) values(?,?,3,?)";
                    PreparedStatement preSQLUpdate=dbconn.prepareStatement(sqlString);
                    preSQLUpdate.setString(1,rs.getString("teacher_name"));
                    preSQLUpdate.setString(2,"111111");
                    preSQLUpdate.setLong(3,rs.getLong("teacher_id"));
                    preSQLUpdate.executeUpdate();
                    returnString=returnString+"生成教師"+rs.getString("teacher_name")+"用戶成功！<BR>";
                }else
                    returnString=returnString+"教師"+rs.getString("teacher_name")+"用戶已經(jīng)生成，不必再生成！<BR>";
                rs1.close();
            }
        }catch(Exception e){
            System.out.print(e);
        }
        return returnString;
    }
    
    /**
     * 功能：生成學(xué)生用戶
     */
    public String genStudentUser(String student_id[]){
        String returnString=new String("");
        String sqlString=null;//SQL語句字符串
        ResultSet rs=null;//結(jié)果記錄集
        ResultSet rs1=null;//另一結(jié)果記錄集
        Statement sql=null;//SQL語句對(duì)象
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫連接對(duì)象
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫連接
        if(dbconn==null) return returnString;//連接失敗
        try{
            sqlString="select * from student";
            System.out.println(student_id.length);
            int j=0;//加長(zhǎng)SQL語句的次數(shù)
            if(student_id.length!=0){//生成部分教師用戶
                for(int i=0;i<student_id.length;i++)
                    if(student_id[i]!=null&&student_id[i].length()!=0&&!student_id[i].equalsIgnoreCase("null")){
                        if(j==0)
                            {sqlString=sqlString+" where student_id="+student_id[i];j++;}
                        else
                            sqlString=sqlString+" or student_id="+student_id[i];
                    }
                
            }
            sql=dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs=sql.executeQuery(sqlString);
            while(rs.next()){
                sqlString="select * from sysuser where foreign_id=" +
                		rs.getLong("student_id")+" and sysuser_role=4";
                sql=dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs1=sql.executeQuery(sqlString);
                if(!rs1.next()){//不存在插入，存在則不做事
                    sqlString="insert into sysuser(sysuser_name,sysuser_password,sysuser_role" +
                    		",foreign_id) values(?,?,4,?)";
                    PreparedStatement preSQLUpdate=dbconn.prepareStatement(sqlString);
                    preSQLUpdate.setString(1,rs.getString("student_name"));
                    preSQLUpdate.setString(2,"111111");
                    preSQLUpdate.setLong(3,rs.getLong("student_id"));
                    preSQLUpdate.executeUpdate();
                    returnString=returnString+"生成學(xué)生"+rs.getString("student_name")+"用戶成功！<BR>";
                }else
                    returnString=returnString+"學(xué)生"+rs.getString("student_name")+"用戶已經(jīng)生成，不必再生成！<BR>";
                rs1.close();
            }
        }catch(Exception e){
            System.out.print(e);
        }
        return returnString;
    }
    /**
     * 得到管理用戶記錄集,系統(tǒng)管理員和教務(wù)管理員是管理用戶
     */
    public ResultSet getAdminUsers(){
        String sqlString=null;//SQL語句字符串
        Statement sql=null;//SQL語句對(duì)象
        ResultSet rs=null;//結(jié)果記錄集
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫連接對(duì)象
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫連接
        if(dbconn==null) return null;//連接失敗
        try{
           //-------查詢出數(shù)據(jù)------------
           sqlString="select * from sysuser where sysuser_role=1 or sysuser_role=2"; 
           sql=dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);