refunddao.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.ProductDTO;
import com.bean.DTO.RefundDTO;
import com.database.DataSourceFactory;

public class RefundDAO {
	private final String REFUND="select r.*,s.statename,u.name,ui.name from refund r,state s,userinfor ui,userinfor u "+
	" where r.stateID=s.stateID and r.stateID=5 "+  
	" and u.userID=r.checkerID and ui.userID=r.directorID";
	
	private final String GET_REFUND="select r.*,s.statename,st.realamount,st.realprice " +
			                        " from refund r, state s,stock st " +
			                        " where r.stateID=s.stateID and r.stateID=5 " +
			                        " and r.projectID=st.projectID and r.projectID=?";
    
	private final String UPDATE_REFUND="update refund set othfee=?,totalfee=?,operateID=?,stateID=?, refundtime=? where projectID=?";
	
	private final String FIND_DEP="select d.departmentID from department d,userinfor u where u.userID=?";
  
	private final String ADD_MONEY="update balance set balance=balance+? where depID=?";
	
	private final String QUERY_ALL="select r.*,s.statename from refund r,state s " +
			" where r.stateID=s.stateID and r.stateID=7 ";
	
	private final String INSERT="insert into refund values(?,?,?,?,?,?,?)";
	
	
	
	
	
	
	private PreparedStatement ps;
	private Connection con;
    private ResultSet rs;
    private List<RefundDTO> list;
    
	public RefundDAO(Connection con) {
		list=new ArrayList<RefundDTO>();
		this.con = con;
	}
	public List<RefundDTO> display(){
		
		try {
			PreparedStatement ps=con.prepareStatement(REFUND);
			rs=ps.executeQuery();
			while(rs.next()){
				RefundDTO dto=new RefundDTO();
		        dto.setProjectID(rs.getInt(1));
		        dto.setOthfee(rs.getFloat(2));
		        dto.setTotalfee(rs.getFloat(3));
		        //dto.setRefundtime(rs.getDate(4).toString());
		        dto.setOperateID(rs.getInt(5));
		        dto.setCheckerID(rs.getInt(6));
		        dto.setDirectorID(rs.getInt(7));
		        dto.setStateID(rs.getInt(8));
		        dto.setState(rs.getString(9));
		        dto.setChecker(rs.getString(10));
		        dto.setDirector(rs.getString(11));
		        
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}
public RefundDTO getRefund(int projectID){
	RefundDTO dto=new RefundDTO();
		try {
			PreparedStatement ps=con.prepareStatement(GET_REFUND);
			ps.setInt(1,projectID);
			rs=ps.executeQuery();
			if(rs.next()){
		        dto.setProjectID(rs.getInt(1));
		        dto.setOthfee(rs.getFloat(2));
		        dto.setTotalfee(rs.getFloat(3));
		        //dto.setRefundtime(rs.getDate(4).toString());
		        dto.setOperateID(rs.getInt(5));
		        dto.setCheckerID(rs.getInt(6));
		        dto.setDirectorID(rs.getInt(7));
		        dto.setStateID(rs.getInt(8));
		        dto.setState(rs.getString(9));
		        dto.setRealamount(rs.getInt(10));
		        dto.setRealprice(rs.getFloat(11));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return dto;
	}
public boolean updateRefund(RefundDTO dto){
	boolean flag=false;
	try {
		PreparedStatement ps=con.prepareStatement(UPDATE_REFUND);
		ps.setFloat(1,dto.getOthfee());
		ps.setFloat(2,dto.getTotalfee());
		ps.setInt(3,dto.getOperateID());
		ps.setInt(4,dto.getStateID());
		ps.setString(5,dto.getRefundtime());
		ps.setInt(6,dto.getProjectID());
		
		flag=ps.execute();
		updateFee(dto.getDirectorID(),dto.getOperateID(),dto.getTotalfee());
		return !flag;
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return flag;
}
public void updateFee(int directorID,int operateID,float totalfee){
	
	if(operateID==2){
		 int depID=findDepartment(directorID);
		 if(depID!=0){
		 addMoney(4,totalfee);
		 }else{
			 System.out.println("没有找到主管所在的部门");
		 }
		 
	}
}
public int findDepartment(int userID){
	int id=0;
	try {
		PreparedStatement ps=con.prepareStatement(FIND_DEP);
		ps.setInt(1,userID);
		rs=ps.executeQuery();
		if(rs.next()){
			id=rs.getInt(1);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return id;
}
public void addMoney(int depID,float totalfee){
	try {
		PreparedStatement ps=con.prepareStatement(ADD_MONEY);
		ps.setFloat(1,totalfee);
		ps.setInt(2,depID);
		ps.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
	
		e.printStackTrace();
	}
	
}

public List<RefundDTO> queryInformation(){
	
	try {
		PreparedStatement ps=con.prepareStatement(QUERY_ALL);
		rs=ps.executeQuery();
		while(rs.next()){
			RefundDTO dto=new RefundDTO();
		
			    dto.setProjectID(rs.getInt(1));
		        dto.setOthfee(rs.getFloat(2));
		        dto.setTotalfee(rs.getFloat(3));
		        dto.setRefundtime(rs.getString(4));
		        dto.setOperateID(rs.getInt(5));
		        dto.setCheckerID(rs.getInt(6));
		        dto.setDirectorID(rs.getInt(7));
		        dto.setStateID(rs.getInt(8));
		        dto.setState(rs.getString(9));
		        
				list.add(dto);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return list;
}

public List<RefundDTO> queryTime(String starttime,String overtime){
	
	long st;long ot;
	if(starttime==null)
		starttime="1000-01-01";
	if(overtime==null)
		overtime="2080-01-01";
	st=this.timeTolong(starttime);
	ot=this.timeTolong(overtime);
	try {
		
		PreparedStatement ps=con.prepareStatement(QUERY_ALL);
		
		 rs=ps.executeQuery();
		 
		while(rs.next()){
			long lt=this.timeTolong(rs.getString(4));
			System.out.println(rs.getString(4)+  "   "+lt +"   "+st+"   "+ot);
			if(lt>=st&&lt<=ot){
				System.out.println(rs.getString(4));
				RefundDTO dto=new RefundDTO();
				dto.setProjectID(rs.getInt(1));
		        dto.setOthfee(rs.getFloat(2));
		        dto.setTotalfee(rs.getFloat(3));
		        dto.setRefundtime(rs.getString(4));
		        dto.setOperateID(rs.getInt(5));
		        dto.setCheckerID(rs.getInt(6));
		        dto.setDirectorID(rs.getInt(7));
		        dto.setStateID(rs.getInt(8));
		        dto.setState(rs.getString(9));
			
			list.add(dto);
			
			}
		}
	
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return list;
}

public void insertData(RefundDTO dto){
	try {
		PreparedStatement ps=con.prepareStatement(INSERT);
		ps.setInt(1, dto.getProjectID());
		ps.setFloat(2,dto.getOthfee());
		ps.setFloat(3,dto.getTotalfee());
		ps.setString(4,dto.getRefundtime());
		ps.setInt(5,dto.getOperateID());
		ps.setInt(6,dto.getCheckerID());
		ps.setInt(7,dto.getDirectorID());
	    ps.setInt(8,dto.getStateID());
	    ps.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}
public long timeTolong(String time){
	long ltime=0;
	String times=time.trim();
	String[] str=new String[3];
	str=times.split("-");
	
		ltime+=Integer.parseInt(str[0])*360;
		ltime+=Integer.parseInt(str[1])*30;
		ltime+=Integer.parseInt(str[2])*1;
	
	return ltime;
}


//采购的方法
public List<RefundDTO> getAll(int curpage){
	list.clear();
	try {
		ps=con.prepareStatement("select * from refund",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){
			RefundDTO pd=new RefundDTO();
			pd.setProjectID(rs.getInt(1));
			pd.setOthfee(rs.getFloat(2));
			pd.setTotalfee(rs.getFloat(3));
			pd.setRefundtime(rs.getString(4));
			pd.setOperateID(rs.getInt(5));
			pd.setCheckerID(rs.getInt(6));
			pd.setDirectorID(rs.getInt(7));
			pd.setStateID(rs.getInt(8));
			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 refund");
		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 update(int projectID) {
	try {
		ps = con.prepareStatement("update refund set stateId=6 where projectID=?");
		ps.setInt(1, projectID);
		ps.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

public void update2(int projectID) {
	try {
		ps = con.prepareStatement("update refund set stateId=5 where projectID=?");
		ps.setInt(1, projectID);
		ps.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

public void del(int projectID) {
	try {
		ps = con.prepareStatement("delete from refund where projectID=?");
		ps.setInt(1, projectID);
		ps.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public RefundDTO getByID(int projectID){
	RefundDTO dto =new RefundDTO();
	try {
		ps = con.prepareStatement("select * from refund where projectID=?");
		ps.setInt(1, projectID);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
		dto.setProjectID(rs.getInt(1));
		dto.setOthfee(rs.getFloat(2));
		dto.setTotalfee(rs.getFloat(3));
		dto.setRefundtime(rs.getString(4));
		dto.setCheckerID(rs.getInt(6));
		dto.setDirectorID(rs.getInt(7));
		dto.setOperateID(rs.getInt(5));
		dto.setStateID(rs.getInt(8));
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return dto;
}

public void update1(RefundDTO dto) {
	try {
		ps = con.prepareStatement("update refund set othfee=?,totalfee=?,refundtime=?,checkerID=?,directorID=?,operateID=? where projectID=?");
		ps.setFloat(1, dto.getOthfee());
		ps.setFloat(2, dto.getTotalfee());
		ps.setString(3, dto.getRefundtime());
		ps.setInt(4, dto.getCheckerID());
		ps.setInt(5, dto.getDirectorID());
		ps.setInt(6, dto.getOperateID());
		ps.setInt(7, dto.getProjectID());
		ps.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

public String getByID2(int projectID) {
	String dto ="xx";
	try {
		ps=con.prepareStatement("select * from refund where projectID=?");
		ps.setInt(1, projectID);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			dto="hdj";
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return dto;
}

public void add(RefundDTO dto) {
	try {
		ps = con.prepareStatement("insert into refund values(?,?,?,?,?,?,?,?)");
		ps.setFloat(2, 0);
		ps.setFloat(3, dto.getTotalfee());
		ps.setString(4, "0");
		ps.setInt(5, 0);
		ps.setInt(6, dto.getCheckerID());
		ps.setInt(7, dto.getDirectorID());
		ps.setInt(8, 0);
		ps.setInt(1, dto.getProjectID());
		ps.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

public List<RefundDTO> getAll2(int curpage, String begin, String end) {
	list.clear();
	try {
		long lt1=this.timeTolong(begin);
		long lt2=this.timeTolong(end);
		ps=con.prepareStatement("select * from refund",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){
			long lt=this.timeTolong(rs.getString(4));
			if(lt>=lt1&&lt<=lt2){
			RefundDTO pd=new RefundDTO();
			pd.setProjectID(rs.getInt(1));
			pd.setOthfee(rs.getFloat(2));
			pd.setTotalfee(rs.getFloat(3));
			pd.setRefundtime(rs.getString(4));
			pd.setOperateID(rs.getInt(5));
			pd.setCheckerID(rs.getInt(6));
			pd.setDirectorID(rs.getInt(7));
			pd.setStateID(rs.getInt(8));
			list.add(pd);
			counter++;
			}
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return list;
}





	}