materialchangedao.java

JSP移动商品管理平台源代码.........

package imis_mate.DAO;

import imis_mate.bean.MateOutBaseBean;
import imis_mate.bean.MateOutDetailBean;
import imis_mate.bean.MaterialBean;
import imis_mate.bean.MaterialChangeBean;
import imis_mate.bean.MaterialChangeSearchBean;
import imis_mate.bean.MaterialOutSearchBean;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/*
 * 物品使用变更表 对应表 Material_Change_Table
 * */
public class MaterialChangeDAO {
	//SQL 语句
	private static final String INSERT_MATERIAL_SQL = 
		"INSERT INTO " +
		"Material_Change_Table(changeId, changeType, outNoId, outCount, outDate, userCode, oraDep, useDep, managerBy, useBy, roomNu, buildingNu, changeCause, remark)" +
		"VALUES (ChangeID.Nextval,?,?,?,to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),?,?,?,?,?,?,?,?,?)";
	private static final String UPDATE_MATERIAL_SQL = 
		"UPDATE Material_Change_Table " +
		"SET changeType = ?, outNoId = ?, outCount = ?, outDate = ?, userCode = ?, oraDep = ?, useDep = ?, managerBy = ?, useBy = ?, roomNu = ?, buildingNu = ?, changeCause = ?, remark = ? " +
		"WHERE ChangeID = ?";
	private static final String DELETE_MATERIAL_SQL = 
		"DELETE FROM Material_Change_Table " +
		"WHERE ChangeID = ?";
	private static final String SELECT_MATERIAL_SQL = 
		"SELECT changeId, changeType, outNoId, outCount, outDate, userCode, oraDep, useDep, managerBy, useBy, roomNu, buildingNu, changeCause, remark " +
		"FROM Material_Change_Table " +
		"WHERE ChangeID = ?";
	private static final String SELECT_OUT_CHANGE_MATERIAL_SQL = 
		"SELECT changeId, changeType, outNoId, outCount, outDate, userCode, oraDep, useDep, managerBy, useBy, roomNu, buildingNu, changeCause, remark " +
		"FROM Material_Change_Table " +
		"WHERE outNoId = ?";
//	private static final String SEARCH_MATERIAL_SQL = "" +
//		"SELECT changeId, changeType, outNoId, outCount, outDate, userCode, oraDep, useDep, managerBy, useBy, roomNu, buildingNu, changeCause, reMark " +
//		"FROM Material_Change_Table " +
//		"WHERE ";
	private static final String SEARCH_MATERIAL_SQL = "" +
			"SELECT MaterialName,MaterialTypeNo,MaBrand,ChangeType,Material_Change_Table.OutCount,oraDep,useDep,outDate,ChangeCause,ReMark "+ 
			"FROM Material_Change_Table,Material_Out_Detail "+
			"WHERE Material_Change_Table.Outnoid = Material_Out_Detail.Outnoid " +
			"AND "; 
	
	private static final String SELECT_OUT_MATERIAL_SQL = "" +
			"SELECT Material_Out_Detail.OutNoId,Material_Out_Table.Outno,Material_Out_Table.Usedep,Material_Out_Table.Useby,Material_Out_Detail.Materialname,Material_Out_Detail.Mamodel,Material_Out_Detail.Mabrand,Material_Out_Detail.Priceunit,Material_Out_Detail.Outcount,Material_Out_Detail.Chaflag "+  
			"FROM Material_Out_Table,Material_Out_Detail " + 
			"WHERE Material_Out_Table.OutNo = Material_Out_Detail.OutNo AND Material_Out_Detail.OutNoId = ?";
	//方法
	public boolean insertMaterialChange(MaterialChangeBean mate) throws Exception {
		//插入变更信息
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(INSERT_MATERIAL_SQL);
			
			ps.setInt(1, mate.getChangeType());
			ps.setInt(2, mate.getOutNoId());
			ps.setInt(3, mate.getOutCount());
//			ps.setDate(4, mate.getOutDate());
			ps.setString(4, mate.getUserCode());
			ps.setInt(5, mate.getOraDep());
			ps.setInt(6, mate.getUseDep());
			ps.setString(7, mate.getManagerBy());
			ps.setString(8, mate.getUseBy());
			ps.setString(9, mate.getRoomNu());
			ps.setString(10, mate.getBuildingNu());
			ps.setString(11, mate.getChangeCause());
			ps.setString(12, mate.getReMark());
			
			int i = ps.executeUpdate();
			flag = i > 0 ? true : false;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(ps);
			DBConnection.close(conn);
		}
		return flag;
	}
	public boolean updateMaterialChange(MaterialChangeBean mate) throws Exception {
		//更新变更信息
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(UPDATE_MATERIAL_SQL);
			
			ps.setInt(1, mate.getChangeType());
			ps.setInt(2, mate.getOutNoId());
			ps.setInt(3, mate.getOutCount());
			ps.setDate(4, mate.getOutDate());
			ps.setString(5, mate.getUserCode());
			ps.setInt(6, mate.getOraDep());
			ps.setInt(7, mate.getUseDep());
			ps.setString(8, mate.getManagerBy());
			ps.setString(9, mate.getUseBy());
			ps.setString(10, mate.getRoomNu());
			ps.setString(11, mate.getBuildingNu());
			ps.setString(12, mate.getChangeCause());
			ps.setString(13, mate.getReMark());
			ps.setInt(14, mate.getChangeID());
			
			int i = ps.executeUpdate();
			flag = i > 0 ? true : false;
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(ps);
			DBConnection.close(conn);
		}
		return flag;
	}
	public boolean deleteMaterialChange(int ChangeId) throws Exception {
		//删除变更信息
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(DELETE_MATERIAL_SQL);
			ps.setInt(1, ChangeId);
			int i = ps.executeUpdate();
			flag = i > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception(); 
		} finally {
			DBConnection.close(ps);
			DBConnection.close(conn);
		}
		return flag;
	}
	public MaterialChangeBean selectMaterialChange(int ChangeId) throws Exception {
		//查询变更信息
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		MaterialChangeBean mate = null;
		
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(SELECT_MATERIAL_SQL);
			ps.setInt(1, ChangeId);
			rs = ps.executeQuery();
			if(rs.next()) {
				mate = new MaterialChangeBean();
				mate.setChangeID(rs.getInt("changeId"));
				mate.setChangeType(rs.getInt("changeType"));
				mate.setOutNoId(rs.getInt("outNoId"));
				mate.setOutCount(rs.getInt("outCount"));
				mate.setOutDate(rs.getDate("outDate"));
				mate.setUserCode(rs.getString("userCode"));
				mate.setOraDep(rs.getInt("oraDep"));
				mate.setUseDep(rs.getInt("useDep"));
				mate.setManagerBy(rs.getString("managerBy"));
				mate.setUseBy(rs.getString("useBy"));
				mate.setBuildingNu(rs.getString("buildingNu"));
				mate.setChangeCause(rs.getString("changeCause"));
				mate.setRoomNu(rs.getString("roomNu"));
				mate.setReMark(rs.getString("remark"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(ps);
			DBConnection.close(conn);
		}
		return mate;
	}
	
	public ArrayList searchMaterialChange(MaterialChangeBean mate,String materialName,String startDate,String endDate) throws Exception {
		//多条件查询
		
		ArrayList array = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		OrganDAO organDAO = new OrganDAO();
		
		//Build the search criterias
		StringBuffer sql = new StringBuffer(512);
		sql.append(SEARCH_MATERIAL_SQL);
		if(mate.getChangeType() != 0) {
			sql.append("ChangeType LIKE '%" +
					DBUtil.fixSqlFieldValue(String.valueOf(mate.getChangeType())) + "%' AND ");
		}
//		if(mate.getOraDep() != 0) {
//			sql.append("OraDep = " +
//					DBUtil.fixSqlFieldValue(String.valueOf(mate.getOraDep())) + " AND ");
//		}
//		if(mate.getUseDep() != 0) {
//			sql.append("UseDep = " +
//					DBUtil.fixSqlFieldValue(String.valueOf(mate.getUseDep())) + " AND ");
//		}
		if(mate.getOraDep() != 0) {
			sql.append("(OraDep IN " +organDAO.getOrganSQL(mate.getOraDep(),"OraDep")+") AND ");
		}
		if(mate.getUseDep() != 0) {
			sql.append("(UseDep IN " +organDAO.getOrganSQL(mate.getUseDep(),"UseDep")+") AND ");
		}