brokenproductdao.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.BrokenproductDTO;

public class BrokenproductDAO {
	private final int onepage=4;
	private int maxpage=1;
	private final String sql_getall="select bp.productID,p.productname,bp.brokenamount,bp.reason,bp.checkerID,bp.directorID,u.name,bp.breaktime from brokenproduct bp,product p,userinfor u where bp.productID=p.productID and bp.checkerID=u.userID";
	private final String sql_add="insert into brokenproduct values(?,?,?,?,?,?)";
	private final String sql_del="delete from brokenproduct where productID=?";
	private final String sql_update="update brokenproduct set brokenamount=?,reason=?,checkerID=?,directorID=?,breaktime=? where productID=?";
	private List<BrokenproductDTO> plist;
	public BrokenproductDAO(){
		plist=new ArrayList<BrokenproductDTO>();
	}
	public List<BrokenproductDTO> 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(8));
				int DirectorID=rs.getInt(6);
				if(lt>=lt1&&lt<=lt2&&DirectorID==0){
				BrokenproductDTO pd=new BrokenproductDTO();
				pd.setProductID(rs.getInt(1));
				pd.setProductname(rs.getString(2));
				pd.setBrokenamount(rs.getInt(3));
				pd.setReason(rs.getString(4));
				pd.setCheckerID(rs.getInt(5));			
				pd.setDirectorID(rs.getInt(6));
				pd.setChecker(rs.getString(7));
				pd.setBreaktime(rs.getString(8));
				plist.add(pd);
				}
			}
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return plist;
	}
	public List<BrokenproductDTO> 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.sql_getall);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				long lt=this.timeTolong(rs.getString(8));
				int DirectorID=rs.getInt(6);
				if(lt>=lt1&&lt<=lt2&&DirectorID!=0){
				BrokenproductDTO pd=new BrokenproductDTO();
				pd.setProductID(rs.getInt(1));
				pd.setProductname(rs.getString(2));
				pd.setBrokenamount(rs.getInt(3));
				pd.setReason(rs.getString(4));
				pd.setCheckerID(rs.getInt(5));			
				pd.setDirectorID(rs.getInt(6));
				pd.setChecker(rs.getString(7));
				pd.setBreaktime(rs.getString(8));
				plist.add(pd);
				}
			}
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return plist;
	}
	public List<BrokenproductDTO> gettimeBP(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(8));
				if(lt>=lt1&&lt<=lt2){
				BrokenproductDTO pd=new BrokenproductDTO();
				pd.setProductID(rs.getInt(1));
				pd.setProductname(rs.getString(2));
				pd.setBrokenamount(rs.getInt(3));
				pd.setReason(rs.getString(4));
				pd.setCheckerID(rs.getInt(5));			
				pd.setDirectorID(rs.getInt(6));
				pd.setChecker(rs.getString(7));
				pd.setBreaktime(rs.getString(8));
				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 brokenproduct");
			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 BrokenproductDTO getBrokenproductDTO(int productID,Connection con){
		BrokenproductDTO pd=new BrokenproductDTO();
		try {
			PreparedStatement ps=con.prepareStatement("select * from brokenproduct where productID=?");
			ps.setInt(1, productID);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				pd.setProductID(rs.getInt(1));
				pd.setBrokenamount(rs.getInt(2));
				pd.setReason(rs.getString(3));
				pd.setCheckerID(rs.getInt(4));			
				pd.setDirectorID(rs.getInt(5));
				pd.setBreaktime(rs.getString(6));
			}
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pd;
	}
	public List<BrokenproductDTO> getAlllist(int page,Connection con){
		try {
			PreparedStatement ps=con.prepareStatement(this.sql_getall,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){
				BrokenproductDTO pd=new BrokenproductDTO();
				pd.setProductID(rs.getInt(1));
				pd.setProductname(rs.getString(2));
				pd.setBrokenamount(rs.getInt(3));
				pd.setReason(rs.getString(4));
				pd.setCheckerID(rs.getInt(5));			
				pd.setDirectorID(rs.getInt(6));
				pd.setChecker(rs.getString(7));
				pd.setBreaktime(rs.getString(8));
				plist.add(pd);
				counter++;
			}
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return plist;
	}
	public boolean AddBrokenproduct(BrokenproductDTO pd,Connection con){
		try {
			PreparedStatement ps=con.prepareStatement(this.sql_add);
			ps.setInt(1,pd.getProductID());
			ps.setInt(2,pd.getBrokenamount());
			ps.setString(3,pd.getReason());
			ps.setInt(4,pd.getCheckerID());
			ps.setInt(5,pd.getDirectorID());
			ps.setString(6, pd.getBreaktime());	
			ps.execute();
			 
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	public boolean DelBrokenproduct(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 updBrokenproduct(BrokenproductDTO pd,Connection con){
		try {
			PreparedStatement ps=con.prepareStatement(sql_update);
			ps.setInt(6,pd.getProductID());
			ps.setInt(1,pd.getBrokenamount());
			ps.setString(2,pd.getReason());
			ps.setInt(3,pd.getCheckerID());
			ps.setInt(4,pd.getDirectorID());
			ps.setString(5, pd.getBreaktime());
			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 brokenproduct set directorID=? where productID=? and breaktime=?");
			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;
	}
	
}