package Cstudent;

import java.util.*;
import java.sql.*;
import javax.swing.*;

/**
 * 用來(lái)保存成績(jī)查詢方面的類
 */
public class csBean {
	String sql;
	ResultSet rs;
	Vector tempvector=new Vector(1,1);

	String Cno;
	String Sno;
	float Grade;

	int colNum;
	

	/**
	 * 添加學(xué)生的選課信息
	 */
	public void csAdd(String cno, String sno){
		
		Database DB = new Database();

		this.Cno = cno;
		this.Sno = sno;		
		sql = "insert into SC(Cno,Sno) values ("+Integer.parseInt(Cno)+","+Integer.parseInt(Sno)+")";
		
		try{			
			DB.OpenConn();			
			DB.executeUpdate(sql);			
			JOptionPane.showMessageDialog(null,"成功添加一條新的紀(jì)錄！");

		}
		catch(Exception e){
			JOptionPane.showMessageDialog(null, "保存失敗", "錯(cuò)誤", JOptionPane.ERROR_MESSAGE); 
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
	}

	/**
	 * 修改學(xué)生的選課成績(jī)
	 */
	public void csModify(String cno, String sno, String grade){
		Database DB = new Database();
		this.Cno = cno;
		this.Sno = sno;

		try{
			this.Grade = Float.parseFloat(grade);
		}
		catch(Exception e){
			JOptionPane.showMessageDialog(null, "成績(jī)輸入錯(cuò)誤", "錯(cuò)誤", JOptionPane.ERROR_MESSAGE);
			return;
		}

		sql = "update SC set Grade = "+Grade+" where Sno = "+Integer.parseInt(Sno)+" and Cno = "+Integer.parseInt(Cno)+" ";
		try{
			DB.OpenConn();
			DB.executeUpdate(sql);
			JOptionPane.showMessageDialog(null,"成功登記成績(jī)！");
		}
		catch(Exception e){
			System.out.println(e);
			JOptionPane.showMessageDialog(null, "登記失敗", "錯(cuò)誤", JOptionPane.ERROR_MESSAGE); 
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
	}

	/**
	 * 根據(jù)學(xué)號(hào)搜索其所選的課程名稱
	 */
	public String[] cNameSearch(String sno){
		Database DB = new Database();
		this.Sno = sno;

		sql = "select * from SC,Course where SC.Sno = "+Integer.parseInt(Sno)+" and SC.Cno = Course.Cno";
		String[] cn = null;
		int row = 0;
		int i = 0;
		
		try{
			DB.OpenConn();
			rs = DB.executeQuery(sql);
			if(rs.last()){
				row = rs.getRow();
			}
			if(row == 0){
				cn = null;
			}
			else{
				cn = new String[row];
				rs.first();
				rs.previous();
				while(rs.next()){
					cn[i] = rs.getString(5);
					i++;
				}
			}
		}
		catch(Exception e){
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
		return cn;
	}

	/**
	 * 成績(jī)信息綜合查詢
	 */
	public String[][] csAllSearch(String sno, int colnum){
		this.Sno = sno;
		this.colNum = colnum;
        
		Database DB = new Database();
        String[][] cn = null;
		int row = 0;
		int i = 0;
		sql = "select * from SC,Course,Student where SC.Sno = "+Integer.parseInt(Sno)+" and SC.Cno = Course.Cno and SC.Sno = Student.Sno";
		try{
			DB.OpenConn();
			rs = DB.executeQuery(sql);

			if(rs.last()){
				row = rs.getRow();
			}
			if(row == 0){
				cn = null;
			}
			else{
				cn = new String[row][8];
				rs.first();
				rs.previous();
				while(rs.next()){
					cn[i][0] = rs.getString(1);
					cn[i][2] = rs.getString(2);
					cn[i][4] = rs.getString(3);
					cn[i][3] = rs.getString(5);
					cn[i][1] = rs.getString(11);
					i++;
				}
			}
		}
		catch(Exception e){
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
		return cn;
	}
}