assetusingdaoimpl.java

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

package com.shenlong.assetmanage.daoImplSQLserver;

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.shenlong.assetmanage.dao.AssetUsingDao;
import com.shenlong.assetmanage.dbhelp.ConnectionFactory;
import com.shenlong.assetmanage.domain.AssetUsing;



public class AssetUsingDaoImpl implements AssetUsingDao {
	//－－－－－－－借出固定资产－－－－－－－－－－－－－
	public boolean borrowAsset(AssetUsing using) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "INSERT INTO asset_using " +
					"(asset_id, emp_id, borrow_date, borrow_admin_name, " +
					"asset_purpose, use_memo) " +
					"VALUES (?,?,?,?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setInt(1, using.getAssetId());
			ps.setInt(2, using.getEmpId());
			ps.setString(3, using.getBorrowDate());
			ps.setString(4, using.getBorrowAdmin());
			ps.setString(5, using.getPurpose());
			ps.setString(6, using.getMemo());
			ps.executeUpdate();
			ps = null;
			
			sql = "UPDATE assets SET asset_emp_id=? WHERE asset_id=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, using.getEmpId());
			ps.setInt(2, using.getAssetId());
			ps.executeUpdate();
			return true;
		} catch(SQLException e){
			con.rollback();
			e.printStackTrace();
			return false;
		} finally {
			ps.close();
			con.close();
		}	
	}
	//－－－－－－－归还固定资产－－－－－－－－－－－－－
	public boolean returnAsset(AssetUsing using) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "UPDATE asset_using SET use_memo=?,return_date = ?, return_admin_name = ? " +
					"WHERE using_id = ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, using.getMemo());
			ps.setString(2, using.getReturnDate());
			ps.setString(3, using.getReturnAdmin());
			ps.setInt(4, using.getId());

			ps.executeUpdate();
			ps = null;
			
			sql = "UPDATE assets SET asset_emp_id=NULL WHERE asset_id=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, using.getAssetId());
			ps.executeUpdate();
			return true;
		} catch(SQLException e){
			con.rollback();
			e.printStackTrace();
			return false;
		} finally {
			ps.close();
			con.close();
		}	
	}
	//－－－－－－查询员工使用固定资产的数量－－－－－－－－－－－
	public int getUsingCountByEmpName(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.using_id) " +
					"FROM asset_using a " +
					"INNER JOIN assets b ON a.asset_id=b.asset_id " +
					"INNER JOIN employee c ON a.emp_id= c.emp_id ";			
			if(!empName.equalsIgnoreCase("*")){
				sql += "WHERE c.emp_name LIKE ?";
			}			
			ps = con.prepareStatement(sql);			
			if(!empName.equalsIgnoreCase("*")) {		
				ps.setString(1, "%" + empName + "%");
			}			
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}			
		} finally {
			
		
			ps.close();
			con.close();
		}
		return count;
	}
	//－－－－－－－根据员工姓名查询固定资产使用的信息，并分页显示－－－－－－－
	public List<AssetUsing> queryUsingByEmpName(String empName, int page, int total) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<AssetUsing> usings = new ArrayList<AssetUsing>();
		int rowStart = (page - 1) * 5 + 1;
		int rowEnd = page *5;
		rowEnd = (rowEnd >= total) ? 0 : total - rowEnd;

		try {
			con = ConnectionFactory.getConnection();
			
			String sql1 = "SELECT a.using_id, b.asset_name, c.emp_name, a.borrow_date, a.borrow_admin_name, " +
							"a.return_date, a.return_admin_name, a.asset_purpose, a.use_memo, " +
							"b.asset_id, c.emp_id " +
					"FROM asset_using a " +
					"INNER JOIN assets b ON a.asset_id=b.asset_id " +
					"INNER JOIN employee c ON a.emp_id= c.emp_id " +
				"WHERE a.using_id NOT IN (SELECT TOP " + (rowStart - 1) + " a.using_id FROM asset_using a " +
						"INNER JOIN assets b ON a.asset_id=b.asset_id " +
						"INNER JOIN employee c ON a.emp_id= c.emp_id ";

			String sql2 = "ORDER BY c.emp_name + cast(a.using_id as varchar(6))) " +
						"AND a.using_id NOT IN (SELECT TOP " + rowEnd + " a.using_id FROM asset_using a " +
						"INNER JOIN assets b ON a.asset_id=b.asset_id " +
						"INNER JOIN employee c ON a.emp_id= c.emp_id ";
			String sql3 = "ORDER BY c.emp_name + cast(a.using_id as varchar(6)) DESC) ";
												
			String sql4 = "ORDER BY c.emp_name + cast(a.using_id as varchar(6)) "; 
			String sql = null;
			if(!empName.equalsIgnoreCase("*")) {
				sql = sql1 + "WHERE c.emp_name LIKE ? " +
					sql2 + "WHERE c.emp_name LIKE ? " +
					sql3 + "AND c.emp_name LIKE ? " +
					sql4;
			} else {
				sql = sql1 + sql2 + sql3 + 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()) {				
				AssetUsing using = new AssetUsing(
						rs.getString("asset_name"), rs.getString("emp_name"),
						rs.getString("borrow_date").substring(0, 10), rs.getString("borrow_admin_name"),
						rs.getString("return_date"), rs.getString("return_admin_name"),
						rs.getString("asset_purpose"), rs.getString("use_memo")
					);
				if(using.getReturnDate()!=null){
					using.setReturnDate(using.getReturnDate().substring(0, 10));
				}
				using.setAssetId(rs.getInt("asset_id"));
				using.setEmpId(rs.getInt("emp_id"));
				using.setId(rs.getInt("using_id"));
				usings.add(using);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return usings;
	}
	
}