productregisterdao.java

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

package com.bean.DAO;

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

import com.bean.DTO.ProductregisterDTO;
import com.bean.xls.GetListfile;
import com.database.DataSourceFactory;
import com.dom4j.SMSConfig;


public class ProductregisterDAO {
private final int onepage=4;
private int maxpage=1;
private final String getGItable="select pr.productID,p.productname,pr.amount,pr.price,pr.provider,pr.inputtime,pr.checkerID,pr.directorID,u.name from productregister pr,product p,userinfor u where pr.productID=p.productID and pr.checkerID=u.userID";
private final String sql_add="insert into productregister values(?,?,?,?,?,?,?)";
private final String sql_del="delete from productregister where productID=?";
private final String sql_update="update productregister set amount=?,price=?,provider=?,inputtime=?,checkerID=?,directorID=? where productID=?";
private List<ProductregisterDTO> plist;
public ProductregisterDAO(){
	plist=new ArrayList<ProductregisterDTO>();
}
public List<ProductregisterDTO> 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.getGItable);
		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){
			ProductregisterDTO pd=new ProductregisterDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setAmount(rs.getInt(3));
			pd.setPrice(rs.getInt(4));
			pd.setProvider(rs.getString(5));
			pd.setInputtime(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<ProductregisterDTO> 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.getGItable);
		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){
			ProductregisterDTO pd=new ProductregisterDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setAmount(rs.getInt(3));
			pd.setPrice(rs.getInt(4));
			pd.setProvider(rs.getString(5));
			pd.setInputtime(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<ProductregisterDTO> gettimeGI(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.getGItable);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			long lt=this.timeTolong(rs.getString(6));
			if(lt>=lt1&&lt<=lt2){
			ProductregisterDTO pd=new ProductregisterDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setAmount(rs.getInt(3));
			pd.setPrice(rs.getInt(4));
			pd.setProvider(rs.getString(5));
			pd.setInputtime(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 productregister");
		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 ProductregisterDTO getProductregisterDTO(int productID,Connection con){
	ProductregisterDTO pd=new ProductregisterDTO();
	try {
		PreparedStatement ps=con.prepareStatement("select * from productregister where productID=?");
		ps.setInt(1, productID);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			pd.setProductID(rs.getInt(1));
			pd.setAmount(rs.getInt(2));
			pd.setPrice(rs.getFloat(3));
			pd.setProvider(rs.getString(4));
			pd.setInputtime(rs.getString(5));
			pd.setCheckerID(rs.getInt(6));
			pd.setDirectorID(rs.getInt(7));
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return pd;
}
public List<ProductregisterDTO> getAlllist(int page,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.getGItable,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){
			ProductregisterDTO pd=new ProductregisterDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setAmount(rs.getInt(3));
			pd.setPrice(rs.getInt(4));
			pd.setProvider(rs.getString(5));
			pd.setInputtime(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 Addproduct(ProductregisterDTO pd,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_add);
		ps.setInt(1,pd.getProductID());
		ps.setInt(2,pd.getAmount());
		ps.setFloat(3,pd.getPrice());
		ps.setString(4,pd.getProvider());
		ps.setString(5,pd.getInputtime());
		ps.setInt(6,pd.getCheckerID());
		ps.setInt(7,pd.getDirectorID());
		ps.execute();
		
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean Delproductregister(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(ProductregisterDTO pd,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(sql_update);
		ps.setInt(7,pd.getProductID());
		ps.setInt(1,pd.getAmount());
		ps.setFloat(2,pd.getPrice());
		ps.setString(3,pd.getProvider());
		ps.setString(4,pd.getInputtime());
		ps.setInt(5,pd.getCheckerID());
		ps.setInt(6,pd.getDirectorID());
		ps.execute();
		
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean Delpr(int productID,String time,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 upddirectorID(int productID,String time,int directorID,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement("update productregister set directorID=? where productID=? and inputtime=?");
		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;
}
public static void main(String[] args) {
	// TODO Auto-generated method stub
	ProductregisterDAO prd = new ProductregisterDAO();
	Connection con;
	SMSConfig.getSMSConfig().readXML("WebRoot/WEB-INF/SMSConfig.xml");
	try {
		con = DataSourceFactory.getDataSource().getConnection();
		if(prd.upddirectorID(10, "2009-12-10", 3, con))
			System.out.println("ok");
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}
}