appbuydao.java

仓库管理系统,适合各种行业的仓库管理系统

package com.bean.DAO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.bean.DTO.AppbuyDTO;
import com.database.DataSourceFactory;

public class AppbuyDAO {
	
	private Connection con;
	private final String sql_getall="select * from appbuy";
	private final String QUERY="select a.*,s.statename from appbuy a,state s where a.stateID=s.stateID order by apptime desc";
	
	
    private final String sql_update="update appbuy set stateID=? where projectID=? and productID=?";
	private List<AppbuyDTO> plist;
	private PreparedStatement ps;



        private ResultSet rs;
        private  final String ADD_BUY="insert into appbuy(projectID ,productID, applicantID, buyamount, stateID,apptime) values(?,?,?,?,10,?)";
        private final String QUERY_ADDBUY="select * from appbuy where stateID=10 and appdirctorID is NULL";
        private final String APPROVE_ADD_BUY="update appbuy set appdirctorID=? ,stateID=9 where productID=? and apptime=?";
        private final String ISHAVE="select * from appbuy where projectID=? and productID=?";
      
         
    


public AppbuyDAO(Connection con){
	this.con=con;
	plist=new ArrayList<AppbuyDTO>();
}









public List<AppbuyDTO> getAll(int curpage){
	plist.clear();
	try {
		ps=con.prepareStatement(this.sql_getall,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
		ResultSet rs=ps.executeQuery();
		rs.absolute((curpage-1)*5+1);
		rs.previous();
		int counter=0;
		while(rs.next()&&counter<5){
			AppbuyDTO pd=new AppbuyDTO();
			pd.setProductID(rs.getInt(1));
			pd.setBuyamount(rs.getInt(2));
			pd.setApplicantID(rs.getInt(3));
			pd.setStordirctorID(rs.getInt(4));
			pd.setAppdirctorID(rs.getInt(5));
			pd.setStateID(rs.getInt(6));
			pd.setProjectID(rs.getInt(7));
			pd.setApptime(rs.getString(8));
			plist.add(pd);
			counter++;
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}

public void updproduct(int projectID,int productID){
	try {
		ps=con.prepareStatement(sql_update);
		ps.setInt(1, 6);
		ps.setInt(2, projectID);
		ps.setInt(3, productID);
		ps.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public void updproduct2(int projectID,int productID){
	try {
		ps=con.prepareStatement(sql_update);
		ps.setInt(1, 5);
		ps.setInt(2, projectID);
		ps.setInt(3, productID);
		ps.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public int getTotalPage() {
	int totalpage=1;
	try {
		ps = con.prepareStatement("select count(*) from appbuy");
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			totalpage = (rs.getInt(1)+4)/5;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return totalpage;
}




//生产部门增加申购单
public void addBuy(AppbuyDTO dto){
	try {
		PreparedStatement ps=con.prepareStatement(ADD_BUY);
		ps.setInt(1,dto.getProjectID());
		ps.setInt(2,dto.getProductID());
		ps.setInt(3,dto.getApplicantID());
		ps.setInt(4,dto.getBuyamount());
		ps.setString(5,dto.getApptime());
		ps.execute(); 
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public void approveAppbuy(AppbuyDTO dto){
	try {
		PreparedStatement ps=con.prepareStatement(APPROVE_ADD_BUY);
		ps.setInt(1,dto.getAppdirctorID());
		ps.setInt(2,dto.getProductID());
		ps.setString(3,dto.getApptime());
		ps.execute(); 
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public List<AppbuyDTO> queryAddbuy(int curpage){
	plist.clear();
	try {
		ps=con.prepareStatement(QUERY_ADDBUY,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
		ResultSet rs=ps.executeQuery();
		rs.absolute((curpage-1)*5+1);
		rs.previous();
		int counter=0;
		while(rs.next()&&counter<5){
			AppbuyDTO pd=new AppbuyDTO();
			pd.setProductID(rs.getInt(1));
			pd.setBuyamount(rs.getInt(2));
			pd.setApplicantID(rs.getInt(3));
			pd.setStordirctorID(rs.getInt(4));
			pd.setAppdirctorID(rs.getInt(5));
			pd.setStateID(rs.getInt(6));
			pd.setProjectID(rs.getInt(7));
			pd.setApptime(rs.getString(8));// 获取填写的时间
			plist.add(pd);
			counter++;
		}
		
	} catch (SQLException e) {
	
		e.printStackTrace();
	}
	return plist;
}
//仓库部门的所有方法
public List<AppbuyDTO> getAll(){
	try {
		Connection con=DataSourceFactory.getDataSource().getConnection();
		PreparedStatement ps=con.prepareStatement(QUERY);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			AppbuyDTO pd=new AppbuyDTO();
			pd.setProductID(rs.getInt(1));
			pd.setBuyamount(rs.getInt(2));
			pd.setApplicantID(rs.getInt(3));
			pd.setStordirctorID(rs.getInt(4));
			pd.setAppdirctorID(rs.getInt(5));
			pd.setStateID(rs.getInt(6));
			pd.setApptime(rs.getString(8));
			pd.setState(rs.getString(9));
			plist.add(pd);
		}
		con.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}

public boolean isHave(int projectID,int productID){
	boolean flag=false;
	try {
		PreparedStatement ps=con.prepareStatement(ISHAVE);
		ps.setInt(1,projectID);
		ps.setInt(2,productID);
		rs=ps.executeQuery();
		if(!rs.next()){
			flag=true;
			
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return flag;
}









public String  getByID(int projectID, int productID) {
	String forward = "xx";
	PreparedStatement ps;
	try {
		ps = con.prepareStatement("select * from appbuy where projectID=? and productID=? " );
		ps.setInt(1,projectID);
		ps.setInt(2,productID);
		ResultSet rs = ps.executeQuery();
		if(!rs.next()){
			forward="hdj";
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return forward;
	
}

}