package teachlessionman;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import db.dbconn;

public class teachlession_operation {
    /**
     * 功能:往教師授課表增加一條記錄
     * 輸入?yún)?shù):lession_id為課程ID,teacher_id為教師ID
     * 輸出:返回1表成功，返回2表數(shù)據(jù)連接參數(shù)配置不正確或連接數(shù)據(jù)庫(kù)失敗或數(shù)據(jù)庫(kù)操作失敗，
     * 返回3表此教師授課信息已存在，返回4表輸入?yún)?shù)lession_id或teacher_id為空;
     */
    public int teachlession_add_one(int lession_id,int teacher_id){
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫(kù)連接對(duì)象
        String sqlString=null;//SQL語(yǔ)句字符串
        ResultSet rs=null;//結(jié)果記錄集
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫(kù)連接
        if(dbconn==null) return 2;//連接失敗
        //--------輸入?yún)?shù)不正確---------
        if(lession_id==0||teacher_id==0)
           return 4;
        //--------插入記錄，先判斷是否已存在------
        sqlString="select * from teachlession where lession_id=? and teacher_id=?";
        try{   
           PreparedStatement preSQLSelect=dbconn.prepareStatement(sqlString);
           preSQLSelect.setInt(1,lession_id);
           preSQLSelect.setInt(2,teacher_id);
           rs=preSQLSelect.executeQuery();
           if(rs.next()) return 3;//已存在此教師授課記錄
           sqlString="insert into teachlession(lession_id,teacher_id) values(?,?)";
           PreparedStatement preSQLInsert=dbconn.prepareStatement(sqlString);
           preSQLInsert.setInt(1,lession_id);
           preSQLInsert.setInt(2,teacher_id);  
           preSQLInsert.executeUpdate();
           return 1;
         }catch(Exception e){
           System.out.print(e);
           return 2;
         }             
    }
    /**
     * 功能:查詢出所有的教師授課信息
     * 輸入?yún)?shù)：無(wú)
     * 輸出:所有教師授課的記錄集,如果沒(méi)有記錄或操作失敗返回null
     */
    public ResultSet teachlession_select_all(){
        String sqlString=null;//SQL語(yǔ)句字符串
        Statement sql=null;//SQL語(yǔ)句對(duì)象
        ResultSet rs=null;//結(jié)果記錄集
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫(kù)連接對(duì)象
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫(kù)連接
        if(dbconn==null) return null;//連接失敗
        try{
           //-------查詢出數(shù)據(jù)------------
           sqlString="select distinct teachlession.teachlession_id as teachlession_id," +
           		"teachlession.teacher_id as teacher_id," +
           		"teachlession.lession_id as lession_id," +
           		"teacher.teacher_name as teacher_name," +
           		"lession.lession_name as lession_name"+
           		" from teachlession,teacher,lession " +
           		"where lession.lession_id=teachlession.lession_id" +
           		" and teacher.teacher_id=teachlession.teacher_id"; 
           sql=dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
           rs=sql.executeQuery(sqlString);
           return rs;
        }catch(Exception e){
           System.out.print(e);
           return null;
        }  
    }
    /**
     * 功能:根據(jù)條件查詢出教師授課的信息
     * 輸入?yún)?shù)：lession_id為課程ID號(hào)，teacher_id為教師ID號(hào)
     * 輸出:查詢需要的教師授課的記錄集,如果沒(méi)有記錄或操作失敗返回null
     */
    public ResultSet teachlession_select_part(int lession_id,int teacher_id){
        String sqlString=null;//SQL語(yǔ)句字符串
        Statement sql=null;//SQL語(yǔ)句對(duì)象
        ResultSet rs=null;//結(jié)果記錄集
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫(kù)連接對(duì)象
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫(kù)連接
        if(dbconn==null) return null;//連接失敗
        try{
           //-------查詢出數(shù)據(jù)------------
            sqlString="select distinct teachlession.teachlession_id as teachlession_id," +
       			"teachlession.teacher_id as teacher_id," +
       			"teachlession.lession_id as lession_id," +
       			"teacher.teacher_name as teacher_name," +
       			"lession.lession_name as lession_name"+
       			" from teachlession,teacher,lession " +
       			"where lession.lession_id=teachlession.lession_id" +
       			" and teacher.teacher_id=teachlession.teacher_id"; 
           if(lession_id!=0)
               sqlString=sqlString+" and lession.lession_id="+lession_id+" and " +
               		"teachlession.lession_id="+lession_id;
           if(teacher_id!=0)
               sqlString=sqlString+" and teacher.teacher_id="+teacher_id+" and " +
               		"teachlession.teacher_id="+teacher_id;   
           sql=dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
           rs=sql.executeQuery(sqlString);
           return rs;
        }catch(Exception e){
           System.out.print(e);
           return null;
        }  
    }
    /**
     * 功能：刪除一個(gè)教師授課表中的信息
     * 輸入?yún)?shù)：教師授課表中的ID號(hào)
     * 輸出：返回1表成功，返回2表數(shù)據(jù)連接參數(shù)配置不正確或連接數(shù)據(jù)庫(kù)失敗，
     * 返回4表輸入?yún)?shù)teachlession_id為0，即輸入?yún)?shù)不正確
     */
    public int teachlession_delete(long teachlession_id){
        String sqlString=null;//SQL語(yǔ)句字符串
        ResultSet rs=null;//結(jié)果記錄集
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫(kù)連接對(duì)象
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫(kù)連接
        if(dbconn==null) return 2;//連接失敗
        if(teachlession_id==0) return 4;//輸入?yún)?shù)不正確
        //---------刪除數(shù)據(jù)--------------
        sqlString="delete from teachlession where teachlession_id=?";
        try{
            PreparedStatement preSQLDelete=dbconn.prepareStatement(sqlString);
            preSQLDelete.setLong(1,teachlession_id);
            preSQLDelete.executeUpdate();
            return 1;
          }catch(Exception e){
            System.out.print(e);
            return 2;
          }   
    }
    /**
     * 功能:查詢出某一條教師授課表中的信息
     * 輸入?yún)?shù):教師授課表中的ID號(hào)
     * 輸出:此條教師授課表中的記錄,如果沒(méi)有記錄或操作失敗返回null
     */
    public ResultSet teachlession_select_one(long teachlession_id){
        String sqlString=null;//SQL語(yǔ)句字符串
        ResultSet rs=null;//結(jié)果記錄集
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫(kù)連接對(duì)象
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫(kù)連接
        if(dbconn==null) return null;//連接失敗
        if(teachlession_id==0) return null;//輸入?yún)?shù)不正確
        try{
           //-------查詢出數(shù)據(jù)------------
           sqlString="select * from teachlession where teachlession_id=?"; 
           PreparedStatement preSQLSelect=dbconn.prepareStatement(sqlString);
           preSQLSelect.setLong(1,teachlession_id);
           rs=preSQLSelect.executeQuery();
           return rs;
        }catch(Exception e){
           System.out.print(e);
           return null;
        }  
    }
    /**
     * 功能:更新教師授課表的一條記錄
     * 輸入?yún)?shù):teachlession_id為教師授課序號(hào)，lession_id為課程ID,teacher_id為老師ID
     * 輸出:返回1表成功，返回2表數(shù)據(jù)連接參數(shù)配置不正確或連接數(shù)據(jù)庫(kù)失敗或數(shù)據(jù)庫(kù)操作失敗，
     * 返回3表記錄已存在，返回4表輸入?yún)?shù)為空或不正確;
     */
    public int teachlession_update(long teachlession_id,int lession_id,int teacher_id){
        String sqlString=null;//SQL語(yǔ)句字符串
        dbconn dbconnOBject=new dbconn();//數(shù)據(jù)庫(kù)連接對(duì)象
        ResultSet rs=null;//結(jié)果記錄集
        Connection dbconn=dbconnOBject.getDBConn();//得到數(shù)據(jù)庫(kù)連接
        if(dbconn==null) return 2;//連接失敗
        if(teachlession_id==0||lession_id==0||teacher_id==0){//輸入?yún)?shù)不正確
            return 4;
        }
        //--------更新記錄，先判斷是否已存在------
        sqlString="select * from teachlession where lession_id=? and teacher_id=? and teachlession_id<>"+teachlession_id;
        try{
            PreparedStatement preSQLSelect=dbconn.prepareStatement(sqlString);
            preSQLSelect.setInt(1,lession_id);
            preSQLSelect.setInt(2,teacher_id);
            rs=preSQLSelect.executeQuery();
            if(rs.next()) return 3;//已存在此教師授課記錄
            //--------更新記錄-----------
            sqlString="update teachlession set lession_id=?,teacher_id=? where teachlession_id=?";
            PreparedStatement preSQLUpdate=dbconn.prepareStatement(sqlString);
            preSQLUpdate.setInt(1,lession_id);
            preSQLUpdate.setInt(2,teacher_id);
            preSQLUpdate.setLong(3,teachlession_id);
            preSQLUpdate.executeUpdate();
            return 1;
        }catch(Exception e){
            System.out.print(e);
            return 2;
        }     
    }  
}