package tools;
/*
* 創建日期 2006-5-24
* 作者： 褚廷軍
* 項目名稱：學生考試系統；
* 項目要求：畢業設計；
* 模塊名稱：服務器端用戶編輯模塊
* 模塊功能：考試系統的服務端用戶編輯界面
*          完成對用戶的查詢，修改，添加等高級操作
*                    
* 更改所生成文件模板為
* 窗口 > 首選項 > Java > 代碼生成 > 代碼和注釋
*/
import java.awt.*;
import java.sql.*;
import java.util.Properties;
import java.awt.event.*;
import javax.swing.*;

public class EditUser extends JFrame implements ActionListener
{
	/**
	 * 
	 */
	private static final long serialVersionUID = -4532970044952124748L;
	Container c;
	JTextField Equery,Cquery,
	          Eupdate,Cupdate,
	          Eappend,Cappend;
	JButton Query,Update,Append,ok;
	JPanel p1,p2,p3,p4;
	int list = 0;
	Connection con = null;
	Statement stmt = null;
	
	String driver;
	String user;
	String password;
	String url;
private JTextField Bquery;
private JTextField Bupdate;
private JTextField Bappend;
	EditUser()
	{
		super("學生信息編輯");
	    c=getContentPane();
		Toolkit tool=getToolkit();
        Image ima=tool.getImage("tubiao.jpg");
        setIconImage(ima);
		setSize(840,180);
		setLocationRelativeTo(null);
		setVisible(true);
		c.setLayout(new GridLayout(4,1));
		
		try {
			Properties ps = new Properties();
			ps.load(getClass().getResourceAsStream("/db.properties"));
			this.driver = ps.getProperty("driver");
			this.url = ps.getProperty("url");
			this.user = ps.getProperty("user");
			this.password = ps.getProperty("password");
			System.out.println(driver);
			System.out.println(url);
			System.out.println(user);
			System.out.println(password);

		} catch (Exception e) {
			System.out.println(e);
		}
		
		
		try{
			  Class.forName(driver);
		   }
		catch(ClassNotFoundException e){}
		try{
			  con = DriverManager.getConnection(url,user,password);
			  stmt = con.createStatement();
		   }
		catch(SQLException ee){}
		Equery = new JTextField(12);    //輸入查詢學號
		Cquery = new JTextField(12);    //輸出查詢姓名
		Bquery = new JTextField(12);    //輸出查詢班級
		Cquery.setForeground(Color.red);
		Bquery.setForeground(Color.RED);
		Eupdate = new JTextField(12);//輸入更新學號
		Cupdate = new JTextField(12);//輸出更新姓名
		Bupdate =new JTextField(12);  //輸出更新班級
		Eappend = new JTextField(12);//輸入添加學號
		Cappend = new JTextField(12);//輸入添加姓名
		Bappend = new JTextField(12);//輸入添加班級
		
		Query = new JButton("查詢");
		Update = new JButton("修改");
		Append = new JButton("添加");
		ok=new JButton("完成");
		p1 = new JPanel();
		p2 = new JPanel();
		p3 = new JPanel();
		p4 = new JPanel();
		p1.add(new JLabel("輸入準考證號:"));
		p1.add(Equery);
		p1.add(new JLabel("顯示考生姓名:"));
		p1.add(Cquery);
		p1.add(new JLabel("顯示考生班級:"));
		p1.add(Bquery);
		p1.add(Query);
		p2.add(new JLabel("輸入準考證號:"));
		p2.add(Eupdate);
		p2.add(new JLabel("修改考生姓名:"));
		p2.add(Cupdate);
		p2.add(new JLabel("修改考生班級:"));
	    p2.add(Bupdate);
		p2.add(Update);
		p3.add(new JLabel("輸入準考證號:"));
		p3.add(Eappend);
		p3.add(new JLabel("輸入考生姓名:"));
		p3.add(Cappend);
		p3.add(new JLabel("輸入考生班級:"));
		p3.add(Bappend);
		p3.add(Append);
		p4.add(ok);
		
		c.add(p1);
		c.add(p2);
		c.add(p3);
		c.add(p4);
		
		Query.addActionListener(this);
		Update.addActionListener(this);
		Append.addActionListener(this);
		ok.addActionListener(this);
		addWindowListener(new WindowAdapter()
		{
			public void windowClosing(WindowEvent e)
			{
				setVisible(false);
			}
		});
	}
	
	public void actionPerformed(ActionEvent e)
	{
		if(e.getSource()==Query)
		{
			list = 0;
			try{
				  query();
			   }
			catch(SQLException ee){}
		}
		else if(e.getSource()==Update)
		{
			list = 0;
			try{
				  update();
			   }
			catch(SQLException ee){}
		}
		else if(e.getSource()==Append)
		{
			try{
				  append();
			   }
			catch(SQLException ee){}
		}
		else if(e.getSource()==ok)
		{
			setVisible(false);
		}
	}
	
	public void query() throws SQLException
	{
		String number,name,sclass;
		con = DriverManager.getConnection(url,user,password);
		ResultSet rs = stmt.executeQuery("select * from stuinfo");
		while(rs.next())
		{
			number = rs.getString(1);
			name = rs.getString(2);
			sclass=rs.getString(4);
			if(number.equals(Equery.getText().trim()))
			{
				Cquery.setText(name);
				Bquery.setText(sclass);
				list = 1;
				break;
			}
		}
		con.close();
		if(list==0)
		{
			Cquery.setText("");
			JOptionPane.showMessageDialog(this,"不存在該準考證號!","錯誤提示", JOptionPane.WARNING_MESSAGE);
		}
	}
	
	public void update() throws SQLException
	{
		String number;
		con = DriverManager.getConnection(url,user,password);
		ResultSet rs = stmt.executeQuery("select * from stuinfo");
		while(rs.next())
		{
			number = rs.getString(1);
			
			if(number.equals(Eupdate.getText().trim()))
			{
				list = 1;
				break;
			}
		}
		if(Cupdate.getText().equals(""))
		{
			list=2;
			JOptionPane.showMessageDialog(this,"考生姓名不能為空!","錯誤提示", JOptionPane.WARNING_MESSAGE);
		}
		if(list==1)
		{
			String s1 = "'"+Eupdate.getText().trim()+"'";
			String s2 = "'"+Cupdate.getText().trim()+"'";
			String s3 = "'"+Bupdate.getText().trim()+"'";
			String temp = "update stuinfo set sname="+s2+",sclass="+s3+"where sno="+s1;
			con = DriverManager.getConnection(url,user,password);
			stmt.executeUpdate(temp);
			con.close();
			Eupdate.setText("");
			Cupdate.setText("");
			Bupdate.setText("");
			JOptionPane.showMessageDialog(this,"考生信息修改成功!請查詢確認!","成功提示",JOptionPane.INFORMATION_MESSAGE);
		}

		else if(list==0)
		{
			Eupdate.setText("");
			Cupdate.setText("");
			Bupdate.setText("");
			JOptionPane.showMessageDialog(this,"修改信息不完全!修改無效!","錯誤提示", JOptionPane.WARNING_MESSAGE);
		}
	}
	
	public void append() throws SQLException
	{
		String number,name,sclass;
		con = DriverManager.getConnection(url,user,password);
		ResultSet rs = stmt.executeQuery("select * from stuinfo");
		while(rs.next())
		{
			number = rs.getString(1);
			name = rs.getString(2);
			sclass=rs.getString(4);
			if(number.equals(Eappend.getText().trim()))
			{
				Eappend.setText("");
				Cappend.setText("");
				Bappend.setText("");
				JOptionPane.showMessageDialog(this,"不能存在重復的準考證號!","錯誤提示", JOptionPane.WARNING_MESSAGE);
				break;
			}
		}
		if(Eappend.getText().equals("")||Cappend.getText().equals(""))
		{
			JOptionPane.showMessageDialog(this,"添加信息不完整，添加無效!","錯誤提示", JOptionPane.WARNING_MESSAGE);
		}
		else
		{
			String s1 = "'"+Eappend.getText().trim()+"'";
			String s2 = "'"+Cappend.getText().trim()+"'";
			String s3 = "'"+Bappend.getText().trim()+"'";
			String s4=s1.substring(2,s1.length()-1);
			
			con = DriverManager.getConnection(url,user,password);
			con.setAutoCommit(false);
			
			String temp = "insert into stuinfo(sno,sname,spass,sclass) values("+s1+","+s2+","+s4+","+s3+")";
		
			stmt.executeUpdate(temp);
			con.commit();
			
			Eappend.setText("");
			Cappend.setText("");
			con.close();
			JOptionPane.showMessageDialog(this,"考生信息添加成功!請查詢確認!","成功提示",JOptionPane.INFORMATION_MESSAGE);
		}
	}
}