productoutputdao.java

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

package com.bean.DAO;

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

import com.bean.DTO.ProductoutputDTO;

public class ProductoutputDAO {
private final int onepage=4;
private int maxpage=1;
private final String getGOtable="select po.productID,p.productname,po.outamount,po.consumer,po.tradeprice,po.outtime,po.checkerID,po.directorID,u.name from productoutput po,product p,userinfor u where po.productID=p.productID and po.checkerID=u.userID";
private final String sql_add="insert into productoutput values(?,?,?,?,?,?,?,?,?)";
private final String sql_del="delete from productoutput where productID=?";
private final String sql_update="update productoutput set outamount=?,consumer=?,tradeprice=?,retailprice=?,outtime=?,checkerID=?,directorID=?,appID=? where productID=?";
private List<ProductoutputDTO> plist;
public ProductoutputDAO(){
	plist=new ArrayList<ProductoutputDTO>();
}
public List<ProductoutputDTO> 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.getGOtable);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			long lt=this.timeTolong(rs.getString(6));
			int DirectorID=rs.getInt(8);
			if(lt>=lt1&&lt<=lt2&&DirectorID==0){
			ProductoutputDTO pd=new ProductoutputDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setOutamount(rs.getInt(3));
			pd.setConsumer(rs.getString(4));
			pd.setTradeprice(rs.getFloat(5));			
			pd.setOuttime(rs.getString(6));
			pd.setCheckerID(rs.getInt(7));
			pd.setDirectorID(rs.getInt(8));
			pd.setChecker(rs.getString(9));
			this.plist.add(pd);
			}
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}
public List<ProductoutputDTO> gettimedAll(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.getGOtable);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			long lt=this.timeTolong(rs.getString(6));
			int DirectorID=rs.getInt(8);
			if(lt>=lt1&&lt<=lt2&&DirectorID!=0){
			ProductoutputDTO pd=new ProductoutputDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setOutamount(rs.getInt(3));
			pd.setConsumer(rs.getString(4));
			pd.setTradeprice(rs.getFloat(5));			
			pd.setOuttime(rs.getString(6));
			pd.setCheckerID(rs.getInt(7));
			pd.setDirectorID(rs.getInt(8));
			pd.setChecker(rs.getString(9));
			this.plist.add(pd);
			}
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}
public List<ProductoutputDTO> gettimeGO(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.getGOtable);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			long lt=this.timeTolong(rs.getString(6));
			if(lt>=lt1&&lt<=lt2){
			ProductoutputDTO pd=new ProductoutputDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setOutamount(rs.getInt(3));
			pd.setConsumer(rs.getString(4));
			pd.setTradeprice(rs.getFloat(5));			
			pd.setOuttime(rs.getString(6));
			pd.setCheckerID(rs.getInt(7));
			pd.setDirectorID(rs.getInt(8));
			pd.setChecker(rs.getString(9));
			this.plist.add(pd);
			}
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}
public int getmaxpage(Connection con){
	try {
		PreparedStatement ps=con.prepareStatement("select count(*) from productoutput");
		ResultSet rs=ps.executeQuery();
		if(rs.next())
			this.maxpage=(rs.getInt(1)+onepage-1)/onepage;
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return maxpage;
}
public ProductoutputDTO getProductoutputDTO(int productID,Connection con){
	ProductoutputDTO pd=new ProductoutputDTO();
	try {
		PreparedStatement ps=con.prepareStatement("select * from productoutput where productID=?");
		ps.setInt(1, productID);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			pd.setProductID(rs.getInt(1));
			pd.setOutamount(rs.getInt(2));
			pd.setConsumer(rs.getString(3));
			pd.setTradeprice(rs.getFloat(4));			
			pd.setOuttime(rs.getString(6));
			pd.setCheckerID(rs.getInt(7));
			pd.setDirectorID(rs.getInt(8));
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return pd;
}
public List<ProductoutputDTO> getAlllist(int page,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.getGOtable,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
		ResultSet rs=ps.executeQuery();
		rs.absolute((page-1)*onepage+1);
		rs.previous();
		int counter=0;
		while(rs.next()&&counter<onepage){
			ProductoutputDTO pd=new ProductoutputDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setOutamount(rs.getInt(3));
			pd.setConsumer(rs.getString(4));
			pd.setTradeprice(rs.getFloat(5));			
			pd.setOuttime(rs.getString(6));
			pd.setCheckerID(rs.getInt(7));
			pd.setDirectorID(rs.getInt(8));
			pd.setChecker(rs.getString(9));
			this.plist.add(pd);
			counter++;
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}
public boolean Addproductoutput(ProductoutputDTO pd,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_add);
		ps.setInt(1,pd.getProductID());
		ps.setInt(2,pd.getOutamount());
		ps.setString(3,pd.getConsumer());
		ps.setFloat(4,pd.getTradeprice());
		ps.setFloat(5,pd.getRetailprice());
		ps.setString(6, pd.getOuttime());
		ps.setInt(7, pd.getCheckerID());
		ps.setInt(8, pd.getDirectorID());
		ps.setInt(9, pd.getAppID());
	
		ps.execute();
		
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean Delproductoutput(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 updproductoutput(ProductoutputDTO pd,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(sql_update);
		ps.setInt(9,pd.getProductID());
		ps.setInt(1,pd.getOutamount());
		ps.setString(2,pd.getConsumer());
		ps.setFloat(3,pd.getTradeprice());
		ps.setFloat(4,pd.getRetailprice());
		ps.setString(5,pd.getOuttime());
		ps.setInt(6, pd.getCheckerID());
		ps.setInt(7, pd.getDirectorID());
		ps.setInt(8, pd.getAppID());
		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 productoutput set directorID=? where productID=? and outtime=?");
		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))*365;
		ltime+=Integer.parseInt(time.substring(4, 7))*30;
		ltime+=Integer.parseInt(time.substring(8, 10))*1;
	}	
	return ltime;
}
}