assetdaoimpl.java

固定资产管理系统包括一个基本的部分

			
			String sql = "SELECT a.asset_id, e.asset_class1_name, a.asset_class2_id, " + 
						"b.asset_class2_name,a.asset_name, a.asset_type, a.asset_value, " + 
						"a.asset_purchase_date, a.asset_state_id, c.asset_state_name, a.asset_emp_id, " + 
						"d.emp_name, a.asset_memo " +
					"FROM assets a " +
					"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " + 
					"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
					"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " + 
					"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id " +
					"WHERE a.asset_id NOT IN (SELECT TOP " + (rowStart - 1) + " a.asset_id FROM assets a " +
							"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " + 
							"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
							"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " +
							"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id " +
							"WHERE a.asset_class2_id = ? " +
							"ORDER BY a.asset_id) " +
						"AND a.asset_id NOT IN (SELECT TOP " + rowEnd + " a.asset_id FROM assets a " +
							"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " + 
							"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
							"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " + 
							"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id " +
							"WHERE a.asset_class2_id = ? " +							
							"ORDER BY a.asset_id DESC) " +
						"AND a.asset_class2_id = ? " +
						"AND a.tag = 1";
			ps = con.prepareStatement(sql);
			ps.setInt(1, class2Id);
			ps.setInt(2, class2Id);
			ps.setInt(3, class2Id);
			rs = ps.executeQuery();
			while (rs.next()) {				
				Asset asset = new Asset(rs.getInt("asset_id"), rs.getInt("asset_class2_id"), 
						rs.getString("asset_name"),	rs.getString("asset_type"),
						rs.getString("asset_value"), rs.getString("asset_purchase_date").substring(0,10),
						rs.getInt("asset_state_id"), rs.getInt("asset_emp_id"),
						(rs.getString("asset_memo"))
				);
				asset.setClassName(rs.getString("asset_class1_name") + " - " + rs.getString("asset_class2_name"));
				asset.setState(rs.getString("asset_state_name"));
				if(rs.getString("emp_name")==null){
					asset.setEmpName("----");
				}else{
					asset.setEmpName(rs.getString("emp_name"));
				}
				assets.add(asset);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return assets;
	}
	//－－－－－根据次类别查询固定资产的数量－－－－－－－－－－－－
	public int getAssetCountByClass(int class2Id) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		try {
			con = ConnectionFactory.getConnection();			
			
			String sql = "SELECT count(a.asset_id) " +
					"FROM assets a " +
					"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " +
					"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
					"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " +
					"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id " +
					"WHERE a.asset_class2_id = ? and a.tag=1";
			ps = con.prepareStatement(sql);
			ps.setInt(1, class2Id);			
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return count;
	}
	//－－－－根据使用者查询固定资产的数量－－－－－－－－－－－－
	public int getAssetCountByEmpName(String empName) throws SQLException {		
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		try {
			con = ConnectionFactory.getConnection();			
			
			String sql = "SELECT count(a.asset_id) " +
					"FROM assets a " +
					"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " +
					"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
					"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " +
					"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id ";			
			if(!empName.equalsIgnoreCase("*")){
				sql += "WHERE d.emp_name LIKE ?";
			}else{
				sql += "WHERE NOT d.emp_name IS NULL";
			}			
			ps = con.prepareStatement(sql);			
			if(!empName.equalsIgnoreCase("*")) {		
				ps.setString(1, "%" + empName + "%");
			}			
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}			
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return count;
	}
	//－－－－－根据使用者查询固定资产，分页显示－－－－－－－－
	public List<Asset> queryAssetByEmpName(String empName, int page, int total) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Asset> assets = new ArrayList<Asset>();
		int rowStart = (page - 1) * 5 + 1;
		int rowEnd = page * 5;
		rowEnd = (rowEnd >= total) ? 0 : total - rowEnd;
		try {
			con = ConnectionFactory.getConnection();
			
			String sql1 = "SELECT a.asset_id, e.asset_class1_name, a.asset_class2_id, " + 
						"b.asset_class2_name,a.asset_name, a.asset_type, a.asset_value, " + 
						"a.asset_purchase_date, a.asset_state_id, c.asset_state_name, a.asset_emp_id, " + 
						"d.emp_name, a.asset_memo " +
					"FROM assets a " +
					"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " + 
					"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
					"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " + 
					"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id " +
				"WHERE a.asset_id NOT IN (SELECT TOP " + (rowStart - 1) + " a.asset_id FROM assets a " +
							"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " + 
							"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
							"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " +
							"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id ";

			String sql2 = "ORDER BY d.emp_name) " +
						"AND a.asset_id NOT IN (SELECT TOP " + rowEnd + " a.asset_id FROM assets a " +
							"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " + 
							"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
							"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " + 
							"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id ";
			String sql3 = "ORDER BY d.emp_name DESC) ";
												
			String sql4 = "ORDER BY d.emp_name "; 
			String sql = null;
			if(!empName.equalsIgnoreCase("*")) {
				sql = sql1 + "WHERE d.emp_name LIKE ? " +
					sql2 + "WHERE d.emp_name LIKE ? " +
					sql3 + "AND d.emp_name LIKE ? " +
					sql4;
			} else {
				sql = sql1 + "WHERE NOT d.emp_name IS NULL " +
					sql2 + "WHERE NOT d.emp_name IS NULL " +
					sql3 + "AND NOT d.emp_name IS NULL " +
					sql4;
			}
			ps = con.prepareStatement(sql);
			if(!empName.equalsIgnoreCase("*")) {	
				ps.setString(1, "%" + empName + "%");
				ps.setString(2, "%" + empName + "%");
				ps.setString(3, "%" + empName + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {				
				Asset asset = new Asset(rs.getInt("asset_id"), rs.getInt("asset_class2_id"), 
						rs.getString("asset_name"),	rs.getString("asset_type"),
						rs.getString("asset_value"), rs.getString("asset_purchase_date").substring(0,10),
						rs.getInt("asset_state_id"), rs.getInt("asset_emp_id"),
					   (rs.getString("asset_memo"))
				);
				asset.setEmpName(rs.getString("emp_name"));
				asset.setClassName(rs.getString("asset_class1_name") + " - " + rs.getString("asset_class2_name"));
				asset.setState(rs.getString("asset_state_name"));
				assets.add(asset);
			}		
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return assets;
	}
//－－－－－修改固定资产的信息－－－－－－－－－－－－－－
	public int modifyAsset(Asset asset) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try{
			con = ConnectionFactory.getConnection();
			String sql = "UPDATE assets SET asset_class2_id = ?, asset_name = ?," +
					"asset_type = ?, asset_value = ?, asset_state_id = ?," +
					"asset_memo = ? WHERE asset_id = ?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, asset.getClass2Id());
			ps.setString(2, asset.getName());
			ps.setString(3, asset.getType());
			ps.setString(4, asset.getValue());
			ps.setInt(5, asset.getStateId());
			ps.setString(6, asset.getMemo());
			ps.setInt(7, asset.getId());
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();			
		}	
		return row;
	}
	//－－－－－－根据ＩＤ删除固定资产－－－－－－－－－－－
	public int deleteAssetById(int id) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "UPDATE assets SET tag=0 WHERE asset_id=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();
		}	
		return row;
	}
	//－－－－根据固定资产的ＩＤ修改使用者的ＩＤ－－－－－－－－－
	public int updateEmpId(int assetId, int empId) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "UPDATE asset SET asset_emp_id=? WHERE asset_id=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, empId);
			ps.setInt(2, assetId);
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();
		}	
		return row;
	}
}