package com.CsmsCode.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.CsmsCode.DB.DBConnection;
import com.CsmsCode.VO.UserVo;

public class UserDao {
	
	
	public int login(String userLogin,String userPwd) throws ClassNotFoundException, SQLException
	{
		
		
		StringBuffer strSql=new StringBuffer();
		strSql.append("select count(*) from users where userLogin='"+userLogin+"' and userPwd='"+userPwd+"'");
		
		int r=0;
		DBConnection db=new DBConnection();
		Connection conn=db.getConnection();
		
		PreparedStatement pst=conn.prepareStatement(strSql.toString());
		
		ResultSet rs= pst.executeQuery();
		
		while (rs.next())
		{
			r=rs.getInt("count(*)");
		}
		
		return r;
		
	}
	
	public List<UserVo> getUsers(String userLogin,String userName) throws ClassNotFoundException, SQLException
	{
		StringBuffer strSql=new StringBuffer();
		
		strSql.append("select * from users where 1=1 ");
		
		if (!userLogin.equals(""))
		{
			
			strSql.append(" and userLogin='"+userLogin+"' ");
		}
		
		if (!userName.equals(""))
		{
			
			strSql.append(" and userName='"+userName+"'");
		}
		
		DBConnection db=new DBConnection();
		Connection conn=db.getConnection();
		PreparedStatement pst=conn.prepareStatement(strSql.toString());
		ResultSet rs= pst.executeQuery();
		
	    List<UserVo> users=new ArrayList<UserVo>();
	    
	    while (rs.next())
	    {
	    	UserVo myUser=new UserVo();
	    	myUser.setFlag(rs.getInt("flag"));
	    	myUser.setUserLogin(rs.getString("userLogin"));
	    	myUser.setUserName(rs.getString("userName"));
	    	myUser.setUserNote(rs.getString("userNote"));
	    	myUser.setUserPwd(rs.getString("userPwd"));
	    	users.add(myUser);
	    	
	    }
	    
	    return users;
		 
		
	}
	
	
	public void addUser(UserVo uservo) throws ClassNotFoundException, SQLException
	{
		StringBuffer strSql=new StringBuffer();
		strSql.append("insert into users values ('"+uservo.getUserLogin()+"','"+uservo.getUserName()+"','"+uservo.getUserNote()+"','"+uservo.getUserPwd()+"','0)");
		
		DBConnection db=new DBConnection();
		Connection conn=db.getConnection();
		
		PreparedStatement pst=conn.prepareStatement(strSql.toString());
		pst.executeUpdate();
		
	}
	
	public void updateUser(UserVo uservo) throws ClassNotFoundException, SQLException
	{
		StringBuffer strSql=new StringBuffer();
		strSql.append("update users set userName='"+uservo.getUserName()+"',userPwd='"+uservo.getUserPwd()+"',userNote='"+uservo.getUserNote()+"'where userLogin='"+uservo.getUserLogin()+"'");
		
		DBConnection db=new DBConnection();
		Connection conn=db.getConnection();
		
		PreparedStatement pst=conn.prepareStatement(strSql.toString());
		pst.executeUpdate();
		
	}
	
	public void delUser(String userLogin) throws ClassNotFoundException, SQLException
	{
		StringBuffer strSql=new StringBuffer();
		strSql.append("delete from users where userLogin='"+userLogin+"' ");
		
		DBConnection db=new DBConnection();
		Connection conn=db.getConnection();
		
		PreparedStatement pst=conn.prepareStatement(strSql.toString());
		pst.executeUpdate();
		
	}

	

}