package sms.bean;

import sms.db.DBAccess;
import java.sql.ResultSet;
import java.sql.*;
import java.io.Serializable;
import java.util.ArrayList;

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

    private String studentId;
    private String courseId;
    private String courseName;
    private String score;
    private String term;
    private String courseType;
    private String teachName;

    public void setStudentId(String studentId) {
        this.studentId = studentId;
    }

    public String getStudentId() {
        return studentId;
    }

    public void setCourseId(String courseId) {
        this.courseId = courseId;
    }

    public String getCourseId() {
        return courseId;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setScore(String score) {
        this.score = score;
    }

    public String getScore() {
        return score;
    }

    public void setTerm(String term) {
        this.term = term;
    }

    public String getTerm() {
        return term;
    }

    public void setCourseType(String courseType) {
        this.courseType = courseType;
    }

    public String getCourseType() {
        return courseType;
    }

    public void setTeachName(String teachName) {
        this.teachName = teachName;
    }

    public String getTeachName() {
        return teachName;
    }


    public ArrayList queryCourseScore(String courseId, String studentId,String term) {
        String sql = "select a.stu_id,a.course_id,a.score,a.remark,b.course_name,b.term,b.type,c.name teachname from stu_score a,course_info b,teach_info c where a.course_id=b.course_id and b.teach_id = c.id and a.stu_id=" +
                     studentId;
        if(term != null && term.length() > 0){
          sql += " and b.term='"+term+"'";
        }
        if(courseId != null && courseId.length() > 0){
          sql += " and a.course_id="+courseId;
        }
        DBAccess dba = new DBAccess();
        ArrayList aList = new ArrayList();
        try {
            dba.getConnection();
            ResultSet rs = null;
            rs = dba.query(sql);
            while (rs != null && rs.next()) {
                ScoreBean scoreBean = new ScoreBean();
                scoreBean.setCourseId(rs.getString("course_id"));
                scoreBean.setStudentId(rs.getString("stu_id"));
                scoreBean.setCourseName(rs.getString("course_name"));
                scoreBean.setScore(rs.getString("score"));
                scoreBean.setCourseType(rs.getString("type"));
                scoreBean.setTeachName(rs.getString("teachname"));
                scoreBean.setTerm(rs.getString("term"));
                aList.add(scoreBean);
            }
        } catch (SQLException ex) {
            return null;
        }finally{
            dba.closeConnection();
        }
        return aList;
    }

    public ArrayList queryTermScore(String term, String studentId) {
        String sql = "select a.stu_id,a.course_id,a.score,a.remark,b.course_name,b.term,b.type,c.name teachname from stu_score a,course_info b,teach_info c where a.course=b.course_id and b.teach_id = c.id and a.stu_id=" +
                     studentId + " and b.term = " + term;
        DBAccess dba = new DBAccess();

        ArrayList aList = new ArrayList();
        try {
            dba.getConnection();
            ResultSet rs = null;
            rs = dba.query(sql);
            if (rs != null && rs.next()) {
                ScoreBean scoreBean = new ScoreBean();
                scoreBean = new ScoreBean();
                scoreBean.setCourseId(rs.getString("course_id"));
                scoreBean.setStudentId(rs.getString("stu_id"));
                scoreBean.setCourseName(rs.getString("course_name"));
                scoreBean.setScore(rs.getString("score"));
                scoreBean.setCourseType(rs.getString("type"));
                scoreBean.setTeachName(rs.getString("teachname"));
                scoreBean.setTerm(rs.getString("term"));
                aList.add(scoreBean);
            }
        } catch (SQLException ex) {
            return null;
        }finally{
            dba.closeConnection();
        }
        return aList;
    }


    public static void main(String[] args) {
        ScoreBean scorebean = new ScoreBean();
    }
}