billdao.java

含有 销售商、进货商

package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import po.*;
import vo.BillVO;

public class BillDAO {
	
	Connection conn = null;
	Statement state = null;
	ResultSet rs = null;
	
	//根据vendorid查询出table_billt表中相应的记录
	public BillPO FindBillByID(int vendorid)
	{
		BillPO billpo = null;
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery("select * from table_bill where vendor_id = " + vendorid);
			
			if(rs.next())
			{
				billpo = new BillPO();
				billpo.setBill_id(rs.getInt("bill_id"));
				billpo.setVendor_id(rs.getInt("vendor_id"));
				billpo.setBill_date(rs.getString("bill_date"));
				billpo.setBill_due_date(rs.getString("bill_due_date"));
				billpo.setBill_paid_flag(rs.getString("bill_paid_flag"));
				billpo.setBill_amount(rs.getInt("bill_amount"));
			}
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				rs.close();
				state.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return billpo;
	}
	
	//根据部门名查询table_vendor表和table_bill的所有记录
	public ArrayList FindVendorByname(String vendorname)
	{  
		ArrayList array = new ArrayList();
		BillVO billvo = null;
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery("select * from table_vendor vendor,table_bill bill where vendor.vendor_id = bill.vendor_id and vendor.vendor_name = '"+vendorname+"'" );
			
			while(rs.next())
			{
				billvo = new BillVO();
				billvo.setId(rs.getInt("bill_id"));
				billvo.setName(rs.getString("vendor_name"));
				billvo.setDate(rs.getString("bill_date"));
				billvo.setDue_date(rs.getString("bill_due_date"));
				billvo.setFlag(rs.getString("bill_paid_flag"));
				
				array.add(billvo);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				rs.close();
				state.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return array;
	}
	
	//修改table_bill表
	public void ModifyBill(BillVO vo)
	{
		
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			state.executeUpdate("update table_bill set bill_paid_flag = '1' where bill_id = " + vo.getId());
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				state.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	//将SUM插入到table_bill表中
	public void AddBillSum(BillPO po)
	{
		
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			state.executeUpdate("insert into table_bill values("+po.getBill_id()+","+po.getVendor_id()+",to_date('"+po.getBill_date()+"','yyyy-mm-dd'),to_date('"+po.getBill_due_date()+"','yyyy-mm-dd'),'0',"+po.getBill_amount()+")");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				state.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
    //自动累加ID
	public int addBillID()
	{
		int id = 0;
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery("select max(bill_id) Mid from table_bill  ");
			
			if(rs.next())
			{
				id = rs.getInt("Mid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				rs.close();
				state.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ++id;
	}
	
	//
	public int FindByVendorID(String name)
	{
		int vendor_id = 0;
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery("select * from table_vendor where vendor_name = '"+name+"'");
			
			if(rs.next())
			{
				vendor_id = rs.getInt("vendor_id");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				rs.close();
				state.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return vendor_id;
	}
	
	 //自动累加ID
	public int addBill_ItemID()
	{
		int id = 0;
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery("select max(bill_item_id) Mid from table_bill_item  ");
			
			if(rs.next())
			{
				id = rs.getInt("Mid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				rs.close();
				state.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ++id;
	}
	
	//
	public void AddBill_ItemSum(BillItemPO po)
	{
		
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			state.executeUpdate("insert into table_bill_item values("+po.getBill_id()+","+po.getDept_id()+","+po.getBill_item_id()+","+po.getBill_item_expense()+")");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				state.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

}