package sms.bean;

import sms.db.*;
import java.io.Serializable;
import java.sql.ResultSet;

public class CourseSchemeBean implements Serializable {
    public CourseSchemeBean() {
    }

    /**
     * 退選課程時調用的方法
     * @param studentId 學生編號，courseId 課程編號
     * @return 成功返回ture，失敗false
     */
    public boolean undoChooseCourse(String studentId, String courseId) {
        String sql = "delete from selective_course_scheme where stu_id=" +
                     studentId + " and course_id=" + courseId; ;
        DBAccess dba = new DBAccess();
        dba.getConnection();
        int flag = dba.executeSql(sql);
        if (flag > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 選課時調用的方法
     * @param studentId 學生編號，courseId 課程編號
     * @return 成功返回1，異常時返回－2，已經選擇該課程時返回0，失敗返回－1,該時間有必修課時返回-3；
     */
    public int chooseCourse(String courseId, String studentId, String classId) {
        String sql =
                "select * from selective_course_scheme where stu_id=" +
                studentId + " and course_id=" + courseId;
        String sql2 = "select a.course_id,b.class_id from course_info a,required_course_scheme b,course_info";
        sql2 += ",(select term,week,period from course_info where course_id='" +
                courseId + "') c";
        sql2 += " where a.course_id=b.course_id and b.class_id='" + classId +
                "' and a.week=c.week";
        sql2 += " and a.period=c.period and a.term=c.term";
        String sql3 =
                "insert into selective_course_scheme (stu_id,course_id) values('" +
                studentId + "','" + courseId + "')";
        DBAccess dba = new DBAccess();
        try {
            dba.getConnection();
            ResultSet rs = null;
            rs = dba.query(sql2); //查詢該時間段內是否有必修課
            if (rs != null && rs.next()) {
                return -3;
            }
            rs = null;
            rs = dba.query(sql); //查詢是否已經選擇了此課程
            if (rs != null && rs.next()) {
                return 0; //已經選擇了該課程，直接返回
            }
            int flag = dba.executeSql(sql3); //將此課程添加到選課表中
            if (flag > 0) {
                return 1; //選課成功返回1
            } else {
                return -1; //選課失敗返回－1
            }
        } catch (Exception ex) {
            return -2;
        } finally {
            dba.closeConnection();
        }
    }

    public static void main(String[] args) {
        CourseSchemeBean courseschemebean = new CourseSchemeBean();
    }
}