package student.dao;

import java.sql.*;
import java.util.ArrayList;

import commons.PageInation;

import student.dao.StudentInfo;

public class StudentMan {
	
	public ArrayList getDataList() {	 
	 	Connection con=null;
	    Statement sql; 
	    ResultSet rs;	
	    ArrayList list=new ArrayList();
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        sql=con.createStatement();
	        rs=sql.executeQuery("SELECT * FROM score ");
  	        while(rs.next()){  
  	        	StudentInfo student=new StudentInfo();
  	        	student.setId(rs.getString("學(xué)號"));
  	        	student.setName(rs.getString("姓名"));
  	        	student.setMath(rs.getString("數(shù)學(xué)成績"));
  	        	student.setEnglish(rs.getString("英語成績"));
  	        	student.setPhysic(rs.getString("物理成績"));
	        	student.setPhoto(rs.getString(6));
	        	student.setResume(rs.getString(7));
  	        	list.add(student);
 	        }
 	     	rs.close();
	     	sql.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}
		return list;
	}
	public void delData(String id){
	 	Connection con=null;
	    Statement sql; 
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        sql=con.createStatement();
	        sql.executeUpdate("delete FROM score  where 學(xué)號='"+id+"'");
 	     	sql.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}
	}
	public StudentInfo getDataByID(String id){
	 	Connection con=null;
	    Statement sql;
	    ResultSet rs;	
	    StudentInfo student=null;
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        sql=con.createStatement();
	        rs=sql.executeQuery("select * FROM score  where 學(xué)號='"+id+"'");
	        if(rs.next()){
	        	student=new StudentInfo();
	        	student.setId(rs.getString(1));
	        	student.setName(rs.getString(2));
	        	student.setMath(rs.getString(3));
	        	student.setEnglish(rs.getString(4));
	        	student.setPhysic(rs.getString(5));
	        	student.setPhoto(rs.getString(6));
	        	student.setResume(rs.getString(7));
	        } 
	        rs.close();
 	     	sql.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}
		return student;
	}
	public void updateData(StudentInfo student){
	 	Connection con=null;
	 	PreparedStatement stmt;
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        stmt=con.prepareStatement("update score set 姓名=?,數(shù)學(xué)成績=?," +
	        		"英語成績=?,物理成績=? where 學(xué)號=?");
	        stmt.setObject(1, student.getName());
	        stmt.setObject(2, new Float(student.getMath()));
	        stmt.setObject(3, new Float(student.getEnglish()));
	        stmt.setObject(4, new Float(student.getPhysic()));
	        stmt.setObject(5, student.getId());
	        stmt.executeUpdate();
	        stmt.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}		
	}	
	public void insertData(StudentInfo student){
	 	Connection con=null;
	 	PreparedStatement stmt;
        System.out.println(student.getId());
        System.out.println(student.getName());
        System.out.println(student.getMath());
        System.out.println(student.getEnglish());
        System.out.println(student.getPhysic());
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        stmt=con.prepareStatement("insert into score values (?,?,?,?,?)");	
	        stmt.setObject(1, student.getId());
	        stmt.setObject(2, student.getName());
	        stmt.setObject(3, new Float(student.getMath()));
	        stmt.setObject(4, new Float(student.getEnglish()));
	        stmt.setObject(5, new Float(student.getPhysic()));
	       
	        stmt.executeUpdate();
	        stmt.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}		
	}	
	public void addFile(String id,String fileName,String fileType){
	 	Connection con=null;
	 	PreparedStatement stmt;
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
        System.out.println("id="+id);
        System.out.println("fileName="+fileName);
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        if(fileType.equals("0")){
	        	stmt=con.prepareStatement("update score set 照片文件=? where 學(xué)號=?");
	        }else{
	        	stmt=con.prepareStatement("update score set 簡歷文件=? where 學(xué)號=?");
	        }
	        stmt.setObject(1, fileName);
	        stmt.setObject(2, id);
	        stmt.executeUpdate();
	        stmt.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}		
	}	
	public int getDataCount(StudentInfo student){
	 	Connection con=null;
	    Statement stmt;
	    ResultSet rs;
	    int count=0;
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        stmt=con.createStatement();
	        String sql="";
	        if(student.getId()!=null){
	        	sql+=" where 學(xué)號 like '"+student.getId()+"%'";
	        }
	        if(student.getName()!=null){
	        	if(sql.equals("")){
	        		sql+=" where 姓名 like '%"+student.getName()+"%'";
	        	}else{
	        		sql+="and 姓名 like '%"+student.getName()+"%'";
	        	}
	        }
	        rs=stmt.executeQuery("select count(*) FROM score "+sql);
	        if(rs.next()){
	        	count=rs.getInt(1);
	        } 
	        rs.close();
	        stmt.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}
		return count;
	}
	public ArrayList getDataList(PageInation page,StudentInfo student) {	 
	 	Connection con=null;
	    Statement stmt;
	    ResultSet rs;
	    ArrayList list=new ArrayList();
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        stmt=con.createStatement();
	        String sql="";
	        String condition="";
	        if(student.getId()!=null&&!student.getId().equals("")){
	        	condition+=" 學(xué)號 like '"+student.getId()+"%'";
	        }
	        if(student.getName()!=null&&!student.getName().equals("")){
	        	if(condition.equals("")){
	        		condition+=" 姓名 like '%"+student.getName()+"%'";
	        	}else{
	        		condition+="and 姓名 like '%"+student.getName()+"%'";
	        	}
	        }
	        if(page.getCurrentPage()==1){
	        	sql="SELECT TOP "+page.getPageSize()+" * FROM score "+
	        	(condition.equals("")?"":"where "+condition)+
	        	" ORDER BY 學(xué)號";
	        }else{
		        sql="SELECT TOP "+page.getPageSize()+" * FROM score "+
		        " where "+(condition.equals("")?"":condition+" and")+" 學(xué)號 not in "+
	        	" (select top "+(page.getPageSize()*(page.getCurrentPage()-1))+" 學(xué)號"+
	        	" from score order by 學(xué)號 "+(condition.equals("")?"":"where "+condition)+" )"+
	        	" ORDER BY 學(xué)號";
	        	
	        }
	        System.out.println(sql);
	        rs=stmt.executeQuery(sql);
  	        while(rs.next()){  
  	        	student=new StudentInfo();
  	        	student.setId(rs.getString("學(xué)號"));
  	        	student.setName(rs.getString("姓名"));
  	        	student.setMath(rs.getString("數(shù)學(xué)成績"));
  	        	student.setEnglish(rs.getString("英語成績"));
  	        	student.setPhysic(rs.getString("物理成績"));
	        	student.setPhoto(rs.getString(6));
	        	student.setResume(rs.getString(7));
  	        	list.add(student);
 	        }
 	     	rs.close();
	     	stmt.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}
		return list;
	}
	public int getDataCount(){
	 	Connection con=null;
	    Statement stmt;
	    ResultSet rs;
	    int count=0;
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        stmt=con.createStatement();
	        rs=stmt.executeQuery("select count(*) FROM score ");
	        if(rs.next()){
	        	count=rs.getInt(1);
	        } 
	        rs.close();
	        stmt.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}
		return count;
	}
	public ArrayList getDataList(PageInation page) {	 
	 	Connection con=null;
	    Statement stmt;
	    ResultSet rs;
	    ArrayList list=new ArrayList();
	    try { 
	    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	    }catch(Exception e){ 
	        System.out.print(e);
	    }
	    try { 
	        con=DriverManager.getConnection("jdbc:odbc:student","","");
	        stmt=con.createStatement();
	        String sql="";
	        if(page.getCurrentPage()==1){
	        	sql="SELECT TOP "+page.getPageSize()+" * FROM score ORDER BY 學(xué)號";
	        }else{
		        sql="SELECT TOP "+page.getPageSize()+" * FROM score "+
		        " where  學(xué)號 not in "+
	        	" (select top "+(page.getPageSize()*(page.getCurrentPage()-1))+" 學(xué)號"+
	        	" from score order by 學(xué)號 "+
	        	" ORDER BY 學(xué)號";
	        	
	        }
	        System.out.println(sql);
	        rs=stmt.executeQuery(sql);
	        StudentInfo student=null;
  	        while(rs.next()){  
  	        	student=new StudentInfo();
  	        	student.setId(rs.getString("學(xué)號"));
  	        	student.setName(rs.getString("姓名"));
  	        	student.setMath(rs.getString("數(shù)學(xué)成績"));
  	        	student.setEnglish(rs.getString("英語成績"));
  	        	student.setPhysic(rs.getString("物理成績"));
	        	student.setPhoto(rs.getString(6));
	        	student.setResume(rs.getString(7));
  	        	list.add(student);
 	        }
 	     	rs.close();
	     	stmt.close();      
		}catch(SQLException e1){  
			System.out.print(e1);
		}finally{
			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception ee) {}	
		}
		return list;
	}

}