stockdao.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.RefundDTO;
import com.bean.DTO.StockDTO;

public class StockDAO {
	private Connection con;
	private static final String sql_getall="select * from stock";
	private static final String sql_getall2="select * from stock where stateID in (5,6,11)";
	private static final String sql_update1="update stock set budgetprice=?,provider=?,buytime=?,checkerID=?,directorID=? where projectID=? and productID=?";
	private static final String sql_update2="update stock set realprice=?,realamount=?,provider=?,buytime=?,checkerID=?,directorID=?,stateID=11 where projectID=? and productID=?";
	private static final String sql_getByID="select * from stock where projectID=? and productID=? and stateID=13";
	private static final String sql_del="delete from stock where projectID=? and productID=?";
	private final String sql_update="update stock set stateID=? where projectID=? and productID=?";
	private List<StockDTO> list=new ArrayList<StockDTO>();
	private static final String sql_add = "insert into " +
			"stock(productID,budgetprice,provider,buytime,checkerID,directorID,projectID)" +
			"  values(?,?,?,?,?,?,?)";
	private PreparedStatement ps;
	
	public StockDAO(Connection con){
		this.con=con;
	}
	public void add(StockDTO dto){
		try {
			ps = con.prepareStatement(sql_add);
			ps.setInt(1, dto.getProductID());
			ps.setFloat(2, dto.getBudgetprice());
			ps.setString(3, dto.getProvider());
			ps.setString(4, dto.getBuytime());
			ps.setInt(5, dto.getCheckerID());
			ps.setInt(6, dto.getDirectorID());
			ps.setInt(7, dto.getProjectID());
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public List<StockDTO> getAll1(){
		list.clear();
		try {
			ps = con.prepareStatement("select * from stock");
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				StockDTO pd=new StockDTO();
				pd.setProjectID(rs.getInt(1));
				pd.setProductID(rs.getInt(2));
				pd.setRealamount(rs.getInt(3));
				pd.setBudgetprice(rs.getFloat(4));
				pd.setRealprice(rs.getFloat(5));
				pd.setProvider(rs.getString(6));
				pd.setTotalmoney(rs.getFloat(7));
				pd.setBuytime(rs.getString(8));
				pd.setCheckerID(rs.getInt(9));
				pd.setDirectorID(rs.getInt(10));
				pd.setStateID(rs.getInt(11));
				list.add(pd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}
	public List<StockDTO> getAll(int curpage){
		list.clear();
		try {
			ps=con.prepareStatement(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){
				StockDTO pd=new StockDTO();
				pd.setProjectID(rs.getInt(1));
				pd.setProductID(rs.getInt(2));
				pd.setBudgetprice(rs.getFloat(4));
				pd.setRealprice(rs.getFloat(5));
				pd.setProvider(rs.getString(6));
				pd.setRealamount(rs.getInt(3));
				pd.setTotalmoney(rs.getFloat(7));
				pd.setBuytime(rs.getString(8));
				pd.setCheckerID(rs.getInt(9));
				pd.setDirectorID(rs.getInt(10));
				pd.setStateID(rs.getInt(11));
				list.add(pd);
				counter++;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public List<StockDTO> getAll2(int curpage){
		list.clear();
		try {
			ps=con.prepareStatement(sql_getall2,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){
				StockDTO pd=new StockDTO();
				pd.setProjectID(rs.getInt(1));
				pd.setProductID(rs.getInt(2));
				pd.setBudgetprice(rs.getFloat(4));
				pd.setRealprice(rs.getFloat(5));
				pd.setProvider(rs.getString(6));
				pd.setRealamount(rs.getInt(3));
				pd.setTotalmoney(rs.getFloat(7));
				pd.setBuytime(rs.getString(8));
				pd.setCheckerID(rs.getInt(9));
				pd.setDirectorID(rs.getInt(10));
				pd.setStateID(rs.getInt(11));
				list.add(pd);
				counter++;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public List<StockDTO> getAll3(int curpage,int num){
		list.clear();
		try {
			ps=con.prepareStatement("select * from stock where projectID=? and stateID in (5,6,11)",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ps.setInt(1, num);
			ResultSet rs=ps.executeQuery();
			rs.absolute((curpage-1)*5+1);
			rs.previous();
			int counter=0;
			while(rs.next()&&counter<5){
				StockDTO pd=new StockDTO();
				pd.setProjectID(rs.getInt(1));
				pd.setProductID(rs.getInt(2));
				pd.setBudgetprice(rs.getFloat(4));
				pd.setRealprice(rs.getFloat(5));
				pd.setProvider(rs.getString(6));
				pd.setRealamount(rs.getInt(3));
				pd.setTotalmoney(rs.getFloat(7));
				pd.setBuytime(rs.getString(8));
				pd.setCheckerID(rs.getInt(9));
				pd.setDirectorID(rs.getInt(10));
				pd.setStateID(rs.getInt(11));
				list.add(pd);
				counter++;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public List<StockDTO> getAll4(int curpage,int num){
		list.clear();
		try {
			ps=con.prepareStatement("select * from stock where productID=? and  stateID in (5,6,11)",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ps.setInt(1, num);
			ResultSet rs=ps.executeQuery();
			rs.absolute((curpage-1)*5+1);
			rs.previous();
			int counter=0;
			while(rs.next()&&counter<5){
				StockDTO pd=new StockDTO();
				pd.setProjectID(rs.getInt(1));
				pd.setProductID(rs.getInt(2));
				pd.setBudgetprice(rs.getFloat(4));
				pd.setRealprice(rs.getFloat(5));
				pd.setProvider(rs.getString(6));
				pd.setRealamount(rs.getInt(3));
				pd.setTotalmoney(rs.getFloat(7));
				pd.setBuytime(rs.getString(8));
				pd.setCheckerID(rs.getInt(9));
				pd.setDirectorID(rs.getInt(10));
				pd.setStateID(rs.getInt(11));
				list.add(pd);
				counter++;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public int getTotalPage() {
		int totalpage=1;
		try {
			ps = con.prepareStatement("select count(*) from stock");
			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 int getTotalPage3(int num) {
		int totalpage=1;
		try {
			ps = con.prepareStatement("select count(*) from stock where projectID=? and stateID in (5,6,11)");
			ps.setInt(1, num);
			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 int getTotalPage4(int num) {
		int totalpage=1;
		try {
			ps = con.prepareStatement("select count(*) from stock where productID=? and stateID in (5,6,11)");
			ps.setInt(1, num);
			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 updproduct(int projectID,int productID){
		try {
			ps=con.prepareStatement(sql_update);
			ps.setInt(1, 13);
			ps.setInt(2, projectID);
			ps.setInt(3, productID);
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void delstock(int projectID, int productID) {
		try {
			ps=con.prepareStatement(sql_del);
			ps.setInt(1, projectID);
			ps.setInt(2, productID);
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public StockDTO getByID(int projectID, int productID){
		StockDTO dto = new StockDTO();
		try {
			ps=con.prepareStatement("select * from stock where projectID=? and productID=?" );
			ps.setInt(1, projectID);
			ps.setInt(2, productID);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				dto.setProjectID(rs.getInt(1));
				dto.setProductID(rs.getInt(2));
				dto.setBudgetprice(rs.getFloat(4));
				dto.setRealprice(rs.getFloat(5));
				dto.setProvider(rs.getString(6));
				dto.setRealamount(rs.getInt(3));
				dto.setTotalmoney(rs.getFloat(7));
				dto.setBuytime(rs.getString(8));
				dto.setCheckerID(rs.getInt(9));
				dto.setDirectorID(rs.getInt(10));
				dto.setStateID(rs.getInt(11));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dto;
	}
	public String getByID2(int projectID, int productID){
		String dto ="";
		try {
			ps=con.prepareStatement(sql_getByID);
			ps.setInt(1, projectID);
			ps.setInt(2, productID);
			ResultSet rs = ps.executeQuery();
			if(!rs.next()){
				dto="hdj";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dto;
	}
	public void update(StockDTO dto) {
		try {
			ps = con.prepareStatement(sql_update1);
			ps.setInt(7, dto.getProductID());
			ps.setFloat(1, dto.getBudgetprice());
			ps.setString(2, dto.getProvider());
			ps.setString(3, dto.getBuytime());
			ps.setInt(4, dto.getCheckerID());
			ps.setInt(5, dto.getDirectorID());
			ps.setInt(6, dto.getProjectID());
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	public void update2(StockDTO dto) {
		try {
			ps = con.prepareStatement(sql_update2);
			ps.setInt(8, dto.getProductID());
			ps.setFloat(1, dto.getRealprice());
			ps.setFloat(2, dto.getRealamount());
			ps.setString(3, dto.getProvider());
			ps.setString(4, dto.getBuytime());
			ps.setInt(5, dto.getCheckerID());
			ps.setInt(6, dto.getDirectorID());
			ps.setInt(7, dto.getProjectID());
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	public void update3(int projectID, int productID) {
		try {
			ps = con.prepareStatement("update stock set stateID=6 where projectID=? and productID=?");
			ps.setFloat(1, projectID);
			ps.setFloat(2, productID);
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void update4(int projectID, int productID) {
		try {
			ps = con.prepareStatement("update stock set stateID=5 where projectID=? and productID=?");
			ps.setFloat(1, projectID);
			ps.setFloat(2, productID);
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public List<StockDTO> getAll5(String begin, String end) {
		list.clear();
		try {
			long lt1=this.timeTolong(begin);
			long lt2=this.timeTolong(end);
			ps=con.prepareStatement("select * from stock");
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				long lt=this.timeTolong(rs.getString(8));
				if(lt>=lt1&&lt<=lt2){
					StockDTO pd=new StockDTO();
					pd.setProjectID(rs.getInt(1));
					pd.setProductID(rs.getInt(2));
					pd.setBudgetprice(rs.getFloat(4));
					pd.setRealprice(rs.getFloat(5));
					pd.setProvider(rs.getString(6));
					pd.setRealamount(rs.getInt(3));
					pd.setTotalmoney(rs.getFloat(7));
					pd.setBuytime(rs.getString(8));
					pd.setCheckerID(rs.getInt(9));
					pd.setDirectorID(rs.getInt(10));
					pd.setStateID(rs.getInt(11));
					list.add(pd);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	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 String getByID3(int projectID) {
		String forward="xx";
		try {
			ps=con.prepareStatement("select * from stock where projectID=? and stateID=5");
			ps.setInt(1, projectID);
			ResultSet rs = ps.executeQuery();
			if(!rs.next()){
				forward = "hdj";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return forward;
	}
}