applicationdao.java

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

package com.bean.DAO;

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.bean.DTO.AppbuyDTO;
import com.bean.DTO.ApplicationDTO;

public class ApplicationDAO {
	private final String SELECT_ALL="select a.*,s.statename from application a, state s where a.stateID=s.stateID order by apptime desc  ";
	private String ADD_APP="insert into application(productID, applicantID, amount, stateID, apptime) values(?,?,?,10,?)";
        
	 private final String sql_getall="select ap.productID,p.productname,ap.amount,ap.applicantID,u.name,ap.directorID,ap.apptime from application ap,product p,userinfor u where ap.productID=p.productID and ap.applicantID=u.userID";
     private final String sql_add="insert into application values(?,?,?,?,?)";
     private final String sql_del="delete from application where productID=?";
     private final String sql_update="update application set amount=?,applicationID=?,directorID=?,stateID=? where productID=?";
    
     private String QUERY_ADDAPP="select * from application where stateID=10 and directorID is NULL";
     private String APPROVE_APP="update application set directorID=? ,stateID=9 where productID=? and apptime=?";
	
	
	private List<ApplicationDTO> plist;
	public ApplicationDAO(){
		plist=new ArrayList<ApplicationDTO>();
	}
	
	public List<ApplicationDTO> gettimeAll(String time1,String time2,Connection con){
		long lt1;long lt2;
		if(time1==null)
			time1="1000-01-01";
		if(time2==null)
			time2="2080-01-01";
		lt1=this.timeTolong(time1);
		lt2=this.timeTolong(time2);
		try {
			PreparedStatement ps=con.prepareStatement(this.sql_getall);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				long lt=this.timeTolong(rs.getString(7));
				int DirectorID=rs.getInt(6);
				if(lt>=lt1&&lt<=lt2&&DirectorID==0){
				ApplicationDTO pd=new ApplicationDTO();
				pd.setProductID(rs.getInt(1));
				pd.setProductname(rs.getString(2));
				pd.setAmount(rs.getInt(3));
				pd.setApplicantID(rs.getInt(4));
				pd.setApplicant(rs.getString(5));
				pd.setDirectorID(rs.getInt(6));
				pd.setApptime(rs.getString(7));
				plist.add(pd);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return plist;
	}
	public List<ApplicationDTO> gettimeSL(String time1,String time2,Connection con){
		long lt1;long lt2;
		if(time1==null)
			time1="1000-01-01";
		if(time2==null)
			time2="2080-01-01";
		lt1=this.timeTolong(time1);
		lt2=this.timeTolong(time2);
		try {
			PreparedStatement ps=con.prepareStatement(this.sql_getall);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				long lt=this.timeTolong(rs.getString(7));
				if(lt>=lt1&&lt<=lt2){
					ApplicationDTO pd=new ApplicationDTO();
					pd.setProductID(rs.getInt(1));
					pd.setProductname(rs.getString(2));
					pd.setAmount(rs.getInt(3));
					pd.setApplicantID(rs.getInt(4));
					pd.setApplicant(rs.getString(5));
					pd.setDirectorID(rs.getInt(6));
					pd.setApptime(rs.getString(7));
				plist.add(pd);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return plist;
	}
	
//仓库部门的方法
    public ApplicationDTO getApplicationDTO(int productID,String apptime,Connection con){
    	ApplicationDTO pd=new ApplicationDTO();
	try {
		PreparedStatement ps=con.prepareStatement("select * from application where productID=? and apptime=?");
		ps.setInt(1, productID);
		ps.setString(2, apptime);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){		
			pd.setProductID(rs.getInt(1));
			pd.setAmount(rs.getInt(2));
			pd.setApplicantID(rs.getInt(3));
			pd.setDirectorID(rs.getInt(4));
			pd.setApptime(rs.getString(6));		
		}
		 
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return pd;
}
public boolean Addproduct(ApplicationDTO pd,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_add);
		ps.setInt(1,pd.getProductID());
		ps.setInt(2,pd.getAmount());
		ps.setInt(3,pd.getApplicantID());
		ps.setInt(4,pd.getDirectorID());
		ps.setInt(5,pd.getStateID());
	
		ps.execute();
		 
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean Delproduct(int productID,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_del);
		ps.setInt(1,productID);
		ps.execute();
		 
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean updproduct(ApplicationDTO pd,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(sql_update);
		
		
		ps.setInt(5,pd.getProductID());
		ps.setInt(1,pd.getAmount());
		ps.setInt(2,pd.getApplicantID());
		ps.setInt(3,pd.getDirectorID());
		ps.setInt(4,pd.getStateID());
		
		
		ps.execute();
		 
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean upddirectorID(int productID,String time,int directorID,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement("update application set directorID=? where productID=? and apptime=?");
		ps.setInt(2, productID);
		ps.setString(3, time);
		ps.setInt(1, directorID);
		ps.execute();
		 
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public long timeTolong(String time){
	long ltime=0;
	String times=time.trim();
	if(times.length()==10){
		ltime+=Integer.parseInt(time.substring(0, 4))*360;
		ltime+=Integer.parseInt(time.substring(4, 7))*30;
		ltime+=Integer.parseInt(time.substring(8, 10))*1;
	}	
	return ltime;
}
	
	
//生产部门用到的方法
	
	
public List<ApplicationDTO> getAll(Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(SELECT_ALL);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			ApplicationDTO pd=new ApplicationDTO();
			pd.setProductID(rs.getInt(1));
			pd.setAmount(rs.getInt(2));
			pd.setApplicantID(rs.getInt(3));
			pd.setDirectorID(rs.getInt(4));
			pd.setStateID(rs.getInt(5));
			pd.setApptime(rs.getString(6));
			pd.setState(rs.getString(7));
			 
			plist.add(pd);
		}
	
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}
public void appProduct(ApplicationDTO dto,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(ADD_APP);
		System.out.println("以下是填写申领表的信息显示：");
		System.out.println(dto.getProductID());
		System.out.println(dto.getApplicantID());
		System.out.println(dto.getAmount());
		System.out.println(dto.getApptime());
		
		ps.setInt(1,dto.getProductID());
		ps.setInt(2,dto.getApplicantID());
		ps.setInt(3,dto.getAmount());
		ps.setString(4,dto.getApptime());
		ps.execute(); 
                     
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public int getTotalPage(Connection con) {
	int totalpage=1;
	try {
		PreparedStatement ps = con.prepareStatement("select count(*) from application");
		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 List<ApplicationDTO> queryAddapp(int curpage,Connection con){
	plist.clear();
	try {
		PreparedStatement ps=con.prepareStatement(QUERY_ADDAPP,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){
			ApplicationDTO pd=new ApplicationDTO();
			pd.setProductID(rs.getInt(1));
			pd.setAmount(rs.getInt(2));
			pd.setApplicantID(rs.getInt(3));
			
			pd.setStateID(rs.getInt(5));
			
			pd.setApptime(rs.getString(6));// 获取填写的时间
			plist.add(pd);
			counter++;
		}
		
	} catch (SQLException e) {
	
		e.printStackTrace();
	}
	return plist;
}
public void approveApp(ApplicationDTO dto,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(APPROVE_APP);
		ps.setInt(1,dto.getDirectorID());
		ps.setInt(2,dto.getProductID());
		ps.setString(3,dto.getApptime());
		ps.execute(); 
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
}