mateoutbasedao.java

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

package imis_mate.DAO;

import imis_mate.bean.MateOutBaseBean;
import imis_mate.bean.MateOutDetailBean;
import imis_mate.bean.MaterialBuySearchBean;
import imis_mate.bean.MaterialOutSearchBean;

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

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import cmis.database.DBPoolManager;

public class MateOutBaseDAO {
	boolean flag;
	private DBPoolManager dbpool;
	private Statement pstmt;
	
	public MateOutBaseDAO(){
		dbpool = new DBPoolManager();

	}
	public boolean save(MateOutBaseBean ob) {
		// TODO Auto-generated method stub
		flag = false;
		dbpool.getConnection();
		try {
			String sql = "insert into Material_Out_Table(OutNo,OutDate,UserCode,UseDep,ManagerBy," +
					"UseBy,RoomNu,BuildingNu,OutCause,ReMark) values('"+ob.getOutNo()+"',to_date('"+ob.getOutDate()
					+"','yyyy-mm-dd'),'"+ob.getUserCode()+"',"+ob.getUseDep()+",'"+ob.getManagerBy()+"','"
					+ob.getUseBy()+"','"+ob.getRoomNu()+"','"+ob.getBuildingNu()+"','"+ob.getOutCause()+"','"+ob.getReMark()+"')";
					
			pstmt = dbpool.conn.createStatement();
			flag = pstmt.executeUpdate(sql)>0? true : false;		
			
			dbpool.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			flag = false;
		} finally {
			dbpool.freeConnection();
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				pstmt=null;
			}
		}
		return flag;
	}

	public boolean update(MateOutBaseBean ob){
		flag=false;
		dbpool.getConnection();
		String sql  = "update Material_Out_Table set OutDate=to_date('"+ob.getOutDateToString()+"','yyyy-mm-dd')," +
				"UserCode='"+ob.getUserCode()+"',UseDep="+ob.getUseDep()+",ManagerBy='"+ob.getManagerBy()
				+"',UseBy='"+ob.getUseBy()+"',RoomNu='"+ob.getRoomNu()+"',BuildingNu='"+ob.getBuildingNu()+"'" +
						",OutCause="+ob.getOutCause()+",ReMark='"+ob.getReMark()+"' where OutNo='"+ob.getOutNo()+"'";
		try {
			
			pstmt = dbpool.conn.createStatement();
			flag = pstmt.executeUpdate(sql)>0?true:false;
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				pstmt=null;
			}
		}
		
		return flag;		
	}
	
	public boolean delete(MateOutBaseBean mob){
		flag = false;
		dbpool.getConnection();
		String sql = "delete from Material_Out_Table where where OutNo='"+mob.getOutNo()+"'";
		try {
			pstmt = dbpool.conn.createStatement();
			flag = pstmt.executeUpdate(sql)>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				pstmt=null;
			}
		}
		return flag;
		
	}
	public boolean delete(String outNo){
		flag = false;
		dbpool.getConnection();
		String sql = "delete from Material_Out_Table  where OutNo='"+outNo+"'";
		try {
		
			pstmt = dbpool.conn.createStatement();
			flag = pstmt.executeUpdate(sql)>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				pstmt=null;
			}
		}
		return flag;
		
	}
	
	public ArrayList getMateOutBaseBean(){
		ArrayList list = new ArrayList();
		MateOutBaseBean mobb = new MateOutBaseBean();
		String sql = "select OutNo,OutDate,UserCode,UseDep,ManagerBy," +
					"UseBy,RoomNu,BuildingNu,OutCause,ReMark from Material_Out_Table";
		try {
			pstmt = dbpool.conn.createStatement();
		
			ResultSet rs = pstmt.executeQuery(sql);
			while(rs.next()){
				mobb.setOutNo(rs.getString("OutNo").trim());
				mobb.setOutDate(rs.getDate("outDate"));
				if(rs.getString("UserCode")==null){
					mobb.setUserCode("");
				}else{
					mobb.setUserCode(rs.getString("userCode").trim());
				}
				mobb.setUseDep(rs.getInt("useDep"));
				if(rs.getString("ManagerBy")==null){
					mobb.setManagerBy("");
				}else{
					mobb.setManagerBy(rs.getString("managerBy").trim());
				}
				if(rs.getString("UseBy")==null){
					mobb.setUseBy("");
				}else{
					mobb.setUseBy(rs.getString("useBy").trim());
				}
				if(rs.getString("RoomNu")==null){
					mobb.setRoomNu("");
				}else{
					mobb.setRoomNu(rs.getString("roomNu").trim());
				}
				if(rs.getString("BuildingNu")==null){
					mobb.setBuildingNu("");
				}else{
					mobb.setBuildingNu(rs.getString("buildingNu").trim());
				}
				mobb.setOutCause(rs.getInt("outCause"));
				if(rs.getString("ReMark")==null){
					mobb.setReMark("");
				}else{
					mobb.setReMark(rs.getString("ReMark").trim());
				}
				list.add(mobb);
			}
			pstmt.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
		}
		return list;
	}
	
	public MateOutBaseBean getMateOutBaseBeanByOutNu(String outNu){
		MateOutBaseBean mobb = new MateOutBaseBean();
		dbpool.getConnection();
		String sql = "select OutNo,OutDate,UserCode,UseDep,ManagerBy," +
					"UseBy,RoomNu,BuildingNu,OutCause,ReMark from Material_Out_Table where OutNo='" +outNu+"'";
		try {
			
			pstmt = dbpool.conn.createStatement();
		
			ResultSet rs = pstmt.executeQuery(sql);
			while(rs.next()){
				mobb.setOutNo(rs.getString("OutNo").trim());
				mobb.setOutDate(rs.getDate("outDate"));
				if(rs.getString("UserCode")==null){
					mobb.setUserCode("");
				}else{
					mobb.setUserCode(rs.getString("userCode").trim());
				}
				mobb.setUseDep(rs.getInt("useDep"));
				if(rs.getString("ManagerBy")==null){
					mobb.setManagerBy("");
				}else{
					mobb.setManagerBy(rs.getString("managerBy").trim());
				}
				if(rs.getString("UseBy")==null){
					mobb.setUseBy("");
				}else{
					mobb.setUseBy(rs.getString("useBy").trim());
				}
				if(rs.getString("RoomNu")==null){
					mobb.setRoomNu("");
				}else{
					mobb.setRoomNu(rs.getString("roomNu").trim());
				}
				if(rs.getString("BuildingNu")==null){
					mobb.setBuildingNu("");
				}else{
					mobb.setBuildingNu(rs.getString("buildingNu").trim());
				}
				mobb.setOutCause(rs.getInt("outCause"));
				if(rs.getString("ReMark")==null){
					mobb.setReMark("");
				}else{
					mobb.setReMark(rs.getString("ReMark").trim());
				}
			}
			pstmt.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
		}
		if(mobb.getOutNo()==null)
		{
			return null;
		}
		else
		
			
	
		return mobb;
	}
	public ArrayList search(MateOutBaseBean mate, MateOutDetailBean mateD, String startDate, String endDate) throws Exception {
		// 多条件查询
		
		ArrayList array = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		MaterialTypeDAO mateTypeDAO = new MaterialTypeDAO();
		OrganDAO organDAO = new OrganDAO();
		
		StringBuffer sql = new StringBuffer(512);
		sql.append(	"SELECT Material_Out_Detail.Outno, Usedep, Useby, Materialname, Mamodel, Mabrand, Priceunit, Outcount, Chaflag, outNoId "+  
					"FROM Material_Out_Table,Material_Out_Detail " + 
					"WHERE Material_Out_Table.OutNo = Material_Out_Detail.OutNo AND ");
//		if(mate.getUseDep() != 0) {
//			sql.append("UseDep = " +
//					DBUtil.fixSqlFieldValue(String.valueOf(mate.getUseDep())) + " AND ");
//		}
		if(mate.getUseDep() != 0) {
			sql.append("(UseDep IN " +organDAO.getOrganSQL(mate.getUseDep(),"UseDep")+") AND ");
		}
//		if(mateD.getMaterialTypeNo() != null && mateD.getMaterialTypeNo().equals("") != true) {
//			sql.append("MaterialTypeNo LIKE '%" +
//					DBUtil.fixSqlFieldValue(mateD.getMaterialTypeNo()) + "%' AND ");
//		}
		if (mateD.getMaterialTypeNo() != null && mateD.getMaterialTypeNo().equals("") != true) {
			sql.append("(MaterialTypeNo IN "+mateTypeDAO.getMaterialSQL(mateD.getMaterialTypeNo())+") AND ");
		}
		if((startDate != null && startDate.equals("") != true) && (endDate != null && endDate.equals("") != true)) {
			sql.append("OutDate BETWEEN TO_DATE('" +
					DBUtil.fixSqlFieldValue(startDate.trim()) + "','YYYY-MM-DD') AND TO_DATE('"+endDate.trim()+"','YYYY-MM-DD')");
		}
		
		
		//Remove unused 'AND' & 'WHERE'
		if(sql.substring(sql.length()-5).equals(" AND ")) {
			sql.delete(sql.length()-5, sql.length()-1);
		}
		if(sql.substring(sql.length()-7).equals(" WHERE ")) {
			sql.delete(sql.length()-7, sql.length()-1);
		}
		sql.append(" ORDER BY OutNo");
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql.toString());
			MaterialOutSearchBean mate1 = null;

			while(rs.next()) {
				mate1 = new MaterialOutSearchBean();
				mate1.setOutNo(rs.getString("outNo"));
				mate1.setMaterialName(rs.getString("materialName"));
				mate1.setMaModel(rs.getString("maModel"));
				mate1.setMaBrand(rs.getString("maBrand"));
				mate1.setPriceUnit(rs.getDouble("priceUnit"));
				mate1.setOutCount(rs.getInt("outCount"));
				mate1.setUseDep(rs.getInt("useDep"));
				mate1.setUseBy(rs.getString("useBy"));
				mate1.setChaFlag(rs.getString("chaFlag"));
				mate1.setOutNoId(rs.getInt("outNoId"));
				array.add(mate1);	
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(stmt);
			DBConnection.close(conn);
		}
		return array;	
	}
}