loginda.java

医药供应链管理系统

	/**
	 * 员工管理 按部门查询
	 * @return
	 */
	public ArrayList selectUsersBysid(int s_id){
		ArrayList arry = new ArrayList();
		UsersBean bean = null;
		String sql = "SELECT login.l_id, login.l_no, login.l_name, login.l_sex, sector.s_name FROM login INNER JOIN sector ON login.l_s_id = sector.s_id WHERE login.l_s_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, s_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new UsersBean(rs.getInt("l_id"), rs.getString("l_no"), rs.getString("l_name"), rs.getString("l_sex"), rs.getString("s_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过ID返回用户详细信息
	 * @param l_id
	 * @return
	 */
	public UsersAllBean userShow(int l_id){
		UsersAllBean bean = null;
		String sql = "SELECT login.l_id, sector.s_name, login.l_no, login.l_name, login.l_username, " +
				"login.l_password, login.l_sex, login.l_tel, login.l_qq, " +
				"login.l_mail, login.l_addr, login.l_sfz, login.l_type FROM login INNER JOIN sector ON " +
				"login.l_s_id = sector.s_id WHERE login.l_id = ? ";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, l_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new UsersAllBean(rs.getInt("l_id"), rs.getString("l_no"), rs.getString("l_name"),
										rs.getString("l_sex"), rs.getString("l_username"), rs.getString("l_password"),
										rs.getString("l_tel"), rs.getString("l_qq"),
										rs.getString("l_mail"), rs.getString("l_addr"), rs.getString("l_sfz"), rs.getBoolean("l_type"), rs.getString("s_name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
	/**
	 * 通过用户登录名得到用户所在部门
	 * @param l_username
	 * @return
	 */
	public int showSector(String l_username){
		int tmp = 0;
		String sql = "select l_s_id from login where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, l_username);
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getInt("l_s_id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 帐户开通
	 */
	public void userOpenType(String l_username){
		String sql = "update login set l_type = ? where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, true);
			pstmt.setString(2, l_username);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 帐户禁用
	 */
	public void userCloseType(String l_username){
		String sql = "update login set l_type = ? where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, false);
			pstmt.setString(2, l_username);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过session("users")返回用户ID和姓名
	 * @param form
	 * @return
	 */
	public String showUserName(LoginForm form){
		String tmp = "";
		//UsersBean bean = null;
		String sql = "select l_id, l_name from login where l_username = ? and l_password = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getL_username());
			pstmt.setString(2, form.getL_password());
			rs = pstmt.executeQuery();
			if(rs.next()){
				//bean = new UsersBean(rs.getInt("l_id"), rs.getString("l_no"), rs.getString("l_name"), rs.getString("l_sex"), rs.getString("s_name"));
				tmp = rs.getInt("l_id")+","+rs.getString("l_name");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 员工添加
	 * @param bean
	 * @param l_s_id
	 */
	public void addUsers(UsersAllBean bean, int l_s_id){
		String sql = "insert into login values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getL_no());
			pstmt.setString(2, bean.getL_username());
			pstmt.setString(3, bean.getL_password());
			pstmt.setString(4, bean.getL_name());
			pstmt.setString(5, bean.getL_sex());
			pstmt.setString(6, bean.getL_tel());
			pstmt.setString(7, bean.getL_addr());
			pstmt.setString(8, bean.getL_sfz());
			pstmt.setString(9, bean.getL_qq());
			pstmt.setString(10, bean.getL_mail());
			pstmt.setBoolean(11, false);
			pstmt.setBoolean(12, bean.isL_type());
			pstmt.setInt(13, l_s_id);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 返回当前员工编号最大的一个
	 * @return
	 */
	public String showMaxL_no(){
		String sql = "select max(l_no) l_no from login";
		String tmp = "";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				tmp = rs.getString("l_no");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 修改员工资料
	 * @param bean
	 * @param l_s_id
	 * @param l_id
	 */
	public void updateUsers(UsersAllBean bean, int l_s_id, int l_id){
		String sql = "update login set l_password = ?, l_name = ?, l_sex = ?, l_tel = ?, l_addr = ?, l_sfz = ?, l_qq = ?, l_mail = ?, l_s_id = ? where l_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getL_password());
			pstmt.setString(2, bean.getL_name());
			pstmt.setString(3, bean.getL_sex());
			pstmt.setString(4, bean.getL_tel());
			pstmt.setString(5, bean.getL_addr());
			pstmt.setString(6, bean.getL_sfz());
			pstmt.setString(7, bean.getL_qq());
			pstmt.setString(8, bean.getL_mail());
			pstmt.setInt(9, l_s_id);
			pstmt.setInt(10, l_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	/**
	 * 返回有入库审核权限的人员
	 * @return
	 */
	public ArrayList showVerMan(){
		ArrayList arry = new ArrayList();
		VerManBean bean = null;
		String sql = "SELECT login.l_id, login.l_name, vercom.v_aa FROM login INNER JOIN vercom ON login.l_username = vercom.v_username WHERE vercom.v_aa = 1";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new VerManBean();
				bean.setL_id(rs.getInt("l_id"));
				bean.setL_name(rs.getString("l_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过登录者信息返回登录者ID
	 * @param form
	 * @return
	 */
	public int showL_id(LoginForm form){
		int tmp = 0;
		String sql = "select l_id from login where l_username = ? and l_password = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getL_username());
			pstmt.setString(2, form.getL_password());
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getInt("l_id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 返回有出库审核权限的人员
	 * @return
	 */
	public ArrayList showVerSellsMan(){
		ArrayList arry = new ArrayList();
		VerManBean bean = null;
		String sql = "SELECT login.l_id, login.l_name, vercom.v_aa FROM login INNER JOIN vercom ON login.l_username = vercom.v_username WHERE vercom.v_ba = 1";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new VerManBean();
				bean.setL_id(rs.getInt("l_id"));
				bean.setL_name(rs.getString("l_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
}