package classfile;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import java.io.*;
public class Student_user extends JFrame 
{
	JMenuBar mb=new JMenuBar();
	JMenu m1=new JMenu("查詢");
	JMenu m2=new JMenu("排名");
	JMenu m3=new JMenu("統計");
	JMenu m4=new JMenu("退出");
	JMenuItem mbb=new JMenuItem("退出");
	JMenuItem mb1=new JMenuItem("所有課程成績查詢");
	JMenuItem mb2=new JMenuItem("單科成績查詢");
	JMenuItem mb3=new JMenuItem("所有課程成績排名");
	JMenuItem mb4=new JMenuItem("單科成績排名");
	JMenuItem mb5=new JMenuItem("平均成績");
	JMenuItem mb6=new JMenuItem("總成績");
	JMenuItem mb7=new JMenuItem("總修學分");
	JTextArea txt=new JTextArea(20,10);
	JDialog g1=new JDialog(this,"課程成績查詢");
	JDialog g2=new JDialog(this,"課程成績排序");
	JTextField txt1=new JTextField("請在此處輸入要查詢的課程名");
	JTextField txt2=new JTextField("請在此處輸入要排序的課程名");
	JButton b1=new JButton("查詢");
	JButton b2=new JButton("取消");
	JButton b3=new JButton("排序");
	JButton b4=new JButton("取消");
	public Student_user()
	{
		m1.add(mb1);
		m1.add(mb2);
		m2.add(mb3);
		m2.add(mb4);
		m3.add(mb5);
		m3.add(mb6);
		m3.add(mb7);
		m4.add(mbb);
		mb.add(m1);
		mb.add(m2);
		mb.add(m3);
		mb.add(m4);
		mb.setBackground(Color.red);
		txt.setBackground(Color.yellow);
		txt.setFont(new Font("Dialog",Font.BOLD,24));
		txt.setEditable(false);
		this.setJMenuBar(mb);
		this.add(txt);
		
		g1.setLayout(new FlowLayout());
		g1.setBounds(90,90,500,100);
		g1.setBackground(Color.blue);
		g1.add(txt1);
		g1.add(b1);
		g1.add(b2);
	
		g2.setLayout(new FlowLayout());
		g2.setBounds(90,90,500,100);
		g2.setBackground(Color.BLUE);
		g2.add(txt2);
		g2.add(b3);
		g2.add(b4);
		b1.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				query_actionPerformed(e);
			}
		});
		b2.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				cancel1_actionPerformed(e);
			}
		});
		b3.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				String s=txt2.getText().trim();
				String cno=cno(s);
				order_actionPerformed(cno);
			}
		});
		
		b4.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				cancel2_actionPerformed(e);
			}
		});
		mb1.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				queryAll_actionPerformed(e);
			}
		});
		mb2.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				g1.setVisible(true);
			}
		});
		mb3.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				orderAll_actionPerformed(e);
			}
		});
		mb4.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				g2.setVisible(true);
			}
		});
		
		mb5.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				average_actionPerformed(e);
			}
		});
		mb6.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				sum_actionPerformed(e);
			}
		});
		mb7.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				credit_actionPerformed(e);
			}
		});
		mbb.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				Exit();
			}
		});
		this.setBounds(200,200,1000,600);
		this.setVisible(true);
	}
	void Exit()
	{
		this.setVisible(false);
	}
	String cno(String cname)//根據課程名查出課程號
	{
		dataBase db=new dataBase();
		String cno=" ";
		String s="select cno from course where cname='"+cname+"'";
		ResultSet r=db.getResult(s);
		try
		{
			while(r.next())
			{
				cno=r.getString(1);
				return cno;
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
			ex.printStackTrace();
			return null;
		}
		return null;
	}
	void query_actionPerformed(ActionEvent e)
	{
		
		String s=txt1.getText();
		String sql="select grade from sc where cno=(select cno from course where cname='"+s+"')and sno='"+Login.name+"'";
		try
		{
			dataBase db=new dataBase();
			ResultSet rs=db.getResult(sql);
		//	if(rs.first())
			//{
				//JOptionPane.showMessageDialog(null,"您沒有選該課程");
			//}
			//else
			//{
				txt.append("\n學號	姓名	課程名	成績\n");
				while(rs.next())
				{
					float grade=rs.getFloat(1);
					txt.append("\n"+Login.name+"	"+Login.name1+"	  "+s+"	  "+grade+"\n");	
				}
			//}
				
		}
		catch(SQLException ew)
		{
			ew.printStackTrace();
		}
	}
	void order_actionPerformed(String cno)
	{
		
		
		String sql="select sno from sc where cno='"+cno+"'order by grade desc";
		dataBase db=new dataBase();
		ResultSet rs=db.getResult(sql);
		try
		{
				int number=1;
				String n=" ";
				while(rs.next())
				{
					n=rs.getString(1);
					if(!n.equals(Login.name))
					{
						number++;
					}
					else
					{
						txt.append("\n"+Login.name1+"	"+cno+"	第"+number+"名");
						break;
					}
						
				}		
		}
		catch(SQLException ew)
		{
			ew.printStackTrace();
		}
	}
	void cancel1_actionPerformed(ActionEvent e)
	{
		g1.setVisible(false);
	}
	void cancel2_actionPerformed(ActionEvent e)
	{
		g2.setVisible(false);
	}
	void queryAll_actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		dataBase db1=new dataBase();
    	String course[]=new String[10];
    	Float grade[]=new Float[10];
    	Float maxgrade[]=new Float[10];
    	Float mingrade[]=new Float[10];
    	int count[]=new int[10];
    	String sql1,sql2,sql3,sql4;
     	int i=0;
     	sql1="select count(*) from sc where sno="+Login.name;//得出該學生選課的總數
     	try
    	{
    		ResultSet rs=db.getResult(sql1);
    		while(rs.next())
    		{
    			i=rs.getInt(1);
    		}
    		course =new String[i];
    		grade=new Float[i];
			maxgrade=new Float[i];
			mingrade=new Float[i];
			count=new int[i];
    	}
    	catch(SQLException ew)
    	{
    		ew.printStackTrace();
    	}
   		sql2="select cno,cname from course where cno in(select cno from sc where sno="+Login.name+")";	
    	try
    	{
    		ResultSet r=db.getResult(sql2);
    		txt.setText(Login.name1+"您的成績信息如下");
    		txt.append("\n	課程號	成績	最高分	最低分	選課人數");
    		while(r.next())
    		{
    			for(int j=0;j<course.length;j++)
    			{
    				course[j]=new String(r.getString(1));
    				txt.append("\n	"+r.getString(2));
    				sql3="select grade from sc where sno='"+Login.name+"'and cno='"+course[j]+"'";
    				sql4="select max(grade),min(grade),count(*) from sc where cno='"+course[j]+"'";
    				ResultSet a=db.getResult(sql3);	
    				ResultSet b=db1.getResult(sql4);	
    				while((a.next())|(b.next()))
    				{
    					grade[j]=new Float(a.getFloat(1));
    					txt.append("	"+grade[j]);
    					maxgrade[j]=new Float(b.getFloat(1));
    					txt.append("	"+maxgrade[j]);
    					mingrade[j]=new Float(b.getFloat(2));
    					txt.append("	"+mingrade[j]);
    					count[j]=new Integer(b.getInt(3));
    					txt.append("	"+count[j]);
    				}	
    				r.next();
    			}
    			txt.append("\n");	
    		}	
    	}
    	catch(SQLException ew)
    	{
    		ew.printStackTrace();
    	}	
    }
    void orderAll_actionPerformed(ActionEvent e)
	{
		String sql="select cno from sc where sno='"+Login.name+"'";
		dataBase db=new dataBase();
		ResultSet rs=db.getResult(sql);
		try
		{
			
			String n=" ";
			while(rs.next())
			{
					n=rs.getString(1);
					sql="select sno from sc where cno='"+n+"'order by grade desc";
					order_actionPerformed(n);
			}
				
		}
		catch(SQLException ew)
		{
			ew.printStackTrace();
		}
	}
	void average_actionPerformed(ActionEvent e)
	{
		String sql="select avg(grade) from sc where sno='"+Login.name+"'";
		dataBase db=new dataBase();
		ResultSet rs=db.getResult(sql);
		try
		{
			while(rs.next())
			{
				txt.append(Login.name1+"的平均成績是"+rs.getString(1)+"\n");	
			}	
		}
		catch(SQLException ew)
		{
			ew.printStackTrace();
		}
	}
	void sum_actionPerformed(ActionEvent e)
	{
		String sql="select sum(grade) from sc where sno='"+Login.name+"'";
		dataBase db=new dataBase();
		ResultSet rs=db.getResult(sql);
		try
		{
			while(rs.next())
			{
				txt.append(Login.name1+"的總成績是"+rs.getString(1)+"\n");	
			}	
		}
		catch(SQLException ew)
		{
			ew.printStackTrace();
		}
	}
	void credit_actionPerformed(ActionEvent e)
	{
		String sql="select sum(credit) from sc,course where sno='"+Login.name+"'and sc.cno=course.cno";
		dataBase db=new dataBase();
		ResultSet rs=db.getResult(sql);
		try
		{
			while(rs.next())
			{
				txt.append(Login.name1+"的總修學分是"+rs.getString(1)+"\n");	
			}	
		}
		catch(SQLException ew)
		{
			ew.printStackTrace();
		}
	}

}