billdao.java

cantain海鲜超市管理系统

package com.j1132.dao.bill;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.j1132.bean.BillBean;
import com.j1132.bean.BillItemBean;
import com.j1132.dao.AbstractDao;

public class BillDao extends AbstractDao implements IBillDao {

	public long insertBill(BillBean bb) {

		
		long bill_id = bb.getBill_ID();

		long vendor_ID = bb.getVendor_ID();

		Date bill_date = bb.getBill_date();

		Date bill_due_date = bb.getBill_due_date();

		long bill_paid_flag = bb.getBill_paid_flag();

		long bill_amount = bb.getBill_amount();

		String sql = "insert into bill(bill_id,vendor_id,bill_date,bill_due_date,bill_paid_flag,bill_amount) values(?,?,?,?,?,?)";

		Connection conn = getConnection();

		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);

			ps.setLong(1, bill_id);

			ps.setLong(2, vendor_ID);

			ps.setDate(3, new java.sql.Date(bill_date.getTime()));

			ps.setDate(4, new java.sql.Date(bill_due_date.getTime()));

			ps.setLong(5, bill_paid_flag);

			ps.setLong(6, bill_amount);

			ps.executeUpdate();
		} catch (Exception e) {

			bill_id = -1;

			System.out.println("添加账单信息出错!");

			e.printStackTrace();
		} finally {
			close(null, ps, conn);
		}
		return bill_id;
	}

	public boolean insertBillItem(List<BillItemBean> list) {

		boolean b = true;

		String sql = "insert into bill_item(bill_id,dept_id,bill_item_id,bill_item_expense) values(?,?,?,?)";

		Connection conn = getConnection();

		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			for (BillItemBean bib : list) {
				ps.setLong(1, bib.getBill_ID());

				ps.setLong(2, bib.getDept_ID());

				ps.setLong(3, bib.getBill_item_ID());

				ps.setLong(4, bib.getBill_item_expense());

				ps.executeUpdate();
			}
		} catch (Exception e) {

			b = false;

			System.out.println("添加账单项出错");

			e.printStackTrace();
		} finally {

			close(null, ps, conn);
		}
		return b;
	}

	public List<BillBean> getBillsByVendorName(String vendorName) {
		
		List<BillBean> list = new ArrayList<BillBean>();
		
		
		String sql = "select * from bill b left join vendor v on b.vendor_id = v.vendor_id where v.vendor_name = ?";
		
		Connection conn = getConnection();
		
		PreparedStatement ps = null;
		
		ResultSet rs = null;
		
		try{
		ps = conn.prepareStatement(sql);
		
		ps.setString(1,vendorName);
		
		rs = ps.executeQuery();
		
		while(rs.next()){
			
			long bill_id = rs.getLong(1);
			
			long vendor_id = rs.getLong(2);
			
			java.sql.Date bill_date = rs.getDate(3);
			
			java.sql.Date bill_due_date = rs.getDate(4);
			
			long bill_paid_flag = rs.getLong(5);
			
			long bill_amount = rs.getLong(6);
			
			BillBean bb = new BillBean();
			
			bb.setBill_ID(bill_id);
			
			bb.setVendor_ID(vendor_id);
			
			bb.setBill_date(bill_date);
			
			bb.setBill_due_date(bill_due_date);
			
			bb.setBill_paid_flag(bill_paid_flag);
			
			bb.setBill_amount(bill_amount);
			
			list.add(bb);
		}
		}catch(Exception e){
			System.out.println("查询账单出错!");
			e.printStackTrace();
		}finally{
			close(rs, ps, conn);
		}
		return list;
	}

	public boolean payBill(long bill_id) {
		
		boolean b = true ;
		
		String sql = "update bill SET bill_paid_flag =1 WHERE bill_id  = ?";
		
		Connection conn = getConnection();
		
		PreparedStatement ps = null;
		
		try{
		ps = conn.prepareStatement(sql);
		
		ps.setLong(1, bill_id);
		
		ps.executeUpdate();
		}catch(Exception e){
			
			b = false;
			
			System.out.println("支付账单出错!");
			
			e.printStackTrace();
		}
		return b;
	}

}