package xscjgl;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
public class Grade_CountInformation{
    /*
     //CountSum:求總分
     //CountAvg：平均分
     //subjectOrder:單科排名
     //SubjectCount:科目統計
     //CountStudent:單科學生數目
     //sumOrder:總分排名
     */
    public String sumString,avgString;
    public int SubjectCount,CountStudent,SubjectOrder,SumOrder;
    public float CountSum,CountAvg;

    public Grade_CountInformation(){
       this.CountAvg=0;
       this.CountStudent=0;
       this.CountSum=0;
       this.SubjectCount=0;
       this.SubjectOrder=0;
       this.SumOrder=0;
       this.sumString="";
       this.avgString="";

    }


    //根據學號求統計信息
    public void setCountInformation(String Snumber){
        try {
            Jdbcconn Count = new Jdbcconn();
            Count.OpenConn("course_design", "sa", "zjxy2006");
            String subjectSQL = "select * from SC where Sno='"+Snumber+"'";
            //*******統計學生科目*************
            ResultSet crs = Count.getResults(subjectSQL);
            crs.last();
            this.SubjectCount=crs.getRow();
            //*******統計學生科目*************

             //******統計總分*************
            String sumSQL = "select sum(Cgrade)as countSum from sc where sno='"+Snumber+"'";
            ResultSet srs = Count.getResults(sumSQL);
            srs.first();
            this.CountSum=srs.getFloat(1);
            //JOptionPane.showMessageDialog(null,"統計總分："+srs.getFloat(1));
            //******統計總分*************
             //******求平均分*************
            this.CountAvg=CountSum/CountStudent;
             //JOptionPane.showMessageDialog(null,"平均分："+CountAvg);
            //******求平均分*************
             Count.closeStmt();
             Count.closeConn();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
    }

    //根據課程號得到排名，由當前CNO得到排名
    public void setSubjectOrder(String CNO,String Sno) {
        try {
              Jdbcconn Order = new Jdbcconn();
              Order.OpenConn("course_design", "sa", "zjxy2006");
              String OrderSQL="select * from SC where Cno='"+CNO+"'order by Cgrade desc";
              ResultSet ors=Order.getResults(OrderSQL);
              ors.last();
              this.CountStudent=ors.getRow();//單科學生數目
              ors.beforeFirst();

              //****************單科排名****************
              while (ors.next()) {
                  String Snumber = ors.getString("Sno");
                  int OrderNumber = ors.getRow();
                  if (Snumber.equals(Sno)) {
                      //將當前的行號（即名次）賦給subjectOrder
                      this.SubjectOrder = OrderNumber;

                     // JOptionPane.showMessageDialog(null,"排名in:"+SubjectOrder);
                       break;
                  } else {
                      this.SubjectOrder =0;
                  }//end if else
              }//end while
              //****************單科排名****************

               Order.closeStmt();
               Order.closeConn();
          } catch (Exception ex) {
              System.out.println(ex.getMessage());
          }
    }



    public void setSumOrder(String Snumber){
       /*
        try {
            Jdbcconn Order = new Jdbcconn();
            Order.OpenConn("course_design", "sa", "zjxy2006");
            String OrderSQL = "select distinct Sno from sc";
            ResultSet ors = Order.getResults(OrderSQL);
            ors.beforeFirst();
            //一個循環讀取學號,二個循環根據學號統計總分
            while(ors.next()){
                String Sno=ors.getString("Sno");



            }
            Order.closeStmt();
            Order.closeConn();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        */

    }



    //CountSum:求總分
    public float getCountSum(){
        return CountSum;
    }


    //CountAvg：平均分
    public float getCountAvg() {
        return CountAvg;
    }

    //subjectOrder:單科排名
    public int getsubjectOrder() {
        return SubjectOrder;
    }

    //SubjectCount:科目統計
    public int getSubjectCount() {
        return SubjectCount;
    }

    //CountStudent:單科學生數目
    public int getCountStudent() {
        return CountStudent;
    }


    //SumOrder:單科排名
    public int getSumOrder() {
        return SumOrder;
    }

    public static void main(String args[]){
    Grade_CountInformation lzquan=new Grade_CountInformation();
    lzquan.setCountInformation("200440914115");
    JOptionPane.showMessageDialog(null,"平均分："+lzquan.getCountAvg());
    JOptionPane.showMessageDialog(null,"總分："+lzquan.getCountSum());
    JOptionPane.showMessageDialog(null,"科目數"+lzquan.getSubjectCount());
    lzquan.setSubjectOrder("10007B3","200440914115");
    JOptionPane.showMessageDialog(null,"排名out："+lzquan.getsubjectOrder());
    JOptionPane.showMessageDialog(null,"排名out："+lzquan.getCountStudent());
    }



}//end class