package txl.tools;

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

public class DbBean
{
	public Connection con = null;
	public Statement stmt = null;
	public ResultSet sqlrs = null;
	
	public DbBean()
	{
		try
		{
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
			String url="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=jsp";
			con = DriverManager.getConnection(url,"sa","sa");
			stmt = con.createStatement();
			con.setAutoCommit(true);
		}
		catch(Exception e)
		{
			System.out.println("數據庫錯誤");
		}
	}
	public void delUser(String id){
		String sql = "delete from txl where id="+id;
		try{
			stmt.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public void addUser(String username,String password){
		try
		{
			String addsql="insert into userinfo(username,password) values(?,?)";
			PreparedStatement pstm=con.prepareStatement(addsql);
			pstm.setString(1,new String(username.trim().getBytes("ISO8859_1"),"gb2312"));
			pstm.setString(2,new MD5().getMD5ofStr(new String(password.trim().getBytes("ISO8859_1"),"gb2312")));
			pstm.executeUpdate();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public int userFind(String username,String password){
		password = new MD5().getMD5ofStr(password);
		String sql = "select * from userinfo where username='"+username+"' and password='"+password+"'";
		int uid = 0;	
		    try{
				sqlrs=stmt.executeQuery(sql);
				while(sqlrs.next()){
					uid = sqlrs.getInt(1);
				}
	 	    }
	     	catch(Exception e)
	     	{
	     		e.printStackTrace();
	     	}
	       return uid;
	}
	public void addnew(TxlUserBean txlinfo) 
	{
		try
		{
			String addsql="insert into txl(name,dz,dh,yb,oicq,lrsj,uid) values(?,?,?,?,?,getdate(),?)";
			PreparedStatement pstm=con.prepareStatement(addsql);
			pstm.setString(1,new String(txlinfo.getName().trim().getBytes("ISO8859_1"),"gb2312"));
			pstm.setString(2,new String(txlinfo.getDz().trim().getBytes("ISO8859_1"),"gb2312"));
			pstm.setString(3,txlinfo.getDh());
			pstm.setString(4,txlinfo.getYb());
			pstm.setString(5,txlinfo.getOicq());
			pstm.setString(6,txlinfo.getUid());
			pstm.executeUpdate();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

	}
public void close(){
	try{
		if(sqlrs != null){
			sqlrs.close();
		}
		if(stmt != null){
		    stmt.close();
		}
		if(con != null){
			con.close();
		}
	}catch(Exception e){
		e.printStackTrace();
	}
}

public int ShowInfoCount(){
	String sql = "select count(*) from txl";
	int maxRowCount = 0;
		try
		{
			sqlrs=stmt.executeQuery(sql);
			while(sqlrs.next()){
				maxRowCount=sqlrs.getInt(1);
			}
 	    }
     	catch(Exception e)
     	{
     		e.printStackTrace();
     	}
        return maxRowCount;
}
public PageBean ShowInfo(PageBean pagebean) 
	{
	int maxRowCount = ShowInfoCount();
	int rowsPerPage = pagebean.getRowsPerPage();
	int maxPage = 0;
	if(maxRowCount%rowsPerPage==0){
		maxPage = maxRowCount/rowsPerPage;
	}else{
		maxPage = maxRowCount/rowsPerPage+1;
	}
	pagebean.setMaxPage(maxPage);
	pagebean.setMaxRowCount(maxRowCount);
	
	int listpage = pagebean.getCurPage()*pagebean.getRowsPerPage();
	String sql = "select top "+listpage+" * from txl order by lrsj desc";
	List list = new ArrayList();
		try
		{
			sqlrs=stmt.executeQuery(sql);
			int i = 1;
			while(sqlrs.next()){
				if(i>(pagebean.getCurPage()-1)*pagebean.getRowsPerPage()){
				TxlUserBean userbean = new TxlUserBean();
				userbean.setId(sqlrs.getString(1));
				userbean.setName(sqlrs.getString(2));
				userbean.setDz(sqlrs.getString(3));
				userbean.setDh(sqlrs.getString(4));
				userbean.setYb(sqlrs.getString(5));
				userbean.setOicq(sqlrs.getString(6));
				userbean.setDate(sqlrs.getString(7));
				userbean.setUid(sqlrs.getString(8));
				list.add(userbean);
				}
				i++;
			}
 	    }
     	catch(Exception e)
     	{
     		e.printStackTrace();
     	}
     	pagebean.setList(list);
        return pagebean;
	}
public TxlUserBean listUser(String id){
	String sql = "select * from txl where id="+id;
	TxlUserBean txl = new TxlUserBean();
		try
		{
			sqlrs=stmt.executeQuery(sql);
			while(sqlrs.next()){
				txl.setId(sqlrs.getString(1));
				txl.setName(sqlrs.getString(2));
				txl.setDz(sqlrs.getString(3));
				txl.setDh(sqlrs.getString(4));
				txl.setYb(sqlrs.getString(5));
				txl.setOicq(sqlrs.getString(6));
				txl.setDate(sqlrs.getString(7));
			}
 	    }
     	catch(Exception e)
     	{
     		e.printStackTrace();
     	}
        return txl;
}
public void txlMod(TxlUserBean txlinfo){
		try
		{
			String updatesql="update txl set name=?,dz=?,dh=?,yb=?,oicq=?,lrsj=getdate() where id=?";
			PreparedStatement pstm=con.prepareStatement(updatesql);
			pstm.setString(1,new String(txlinfo.getName().trim().getBytes("ISO8859_1"),"gb2312"));
			pstm.setString(2,new String(txlinfo.getDz().trim().getBytes("ISO8859_1"),"gb2312"));
			pstm.setString(3,txlinfo.getDh());
			pstm.setString(4,txlinfo.getYb());
			pstm.setString(5,txlinfo.getOicq());
			pstm.setString(6,txlinfo.getId());
			pstm.executeUpdate();
 	    }
     	catch(Exception e)
     	{
     		e.printStackTrace();
     	}
}
}