billadddao.java

分为经理和出纳2个权限

package com.yiboit.cfss.bill;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.yiboit.cfss.db.ManageConnection;
import com.yiboit.cfss.vendor.VendorBean;

public class BillAddDAO {
	private static SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

	public boolean addBill(BillBean bb) throws SQLException {
		boolean result = false;

		Connection conn = null;

		try {

			conn = ManageConnection.getConnection();
			conn.setAutoCommit(false);

			int billId = 0;
			String sql = "select SEQ_BILL_ID.nextval from dual";
			PreparedStatement pstmt1 = conn.prepareStatement(sql);
			ResultSet rs = pstmt1.executeQuery();

			if (rs.next()) {
				billId = rs.getInt(1);
			}
			rs.close();
			pstmt1.close();

			sql = "insert into shop_bill(bill_id, vendor_id"
					+ ", bill_date, bill_due_date, bill_amount) values"
					+ "(?, ?, ?, ?, ?)";

			PreparedStatement pstmt2 = conn.prepareStatement(sql);

			pstmt2.setInt(1, billId);
			pstmt2.setInt(2, bb.getVendorId());
			pstmt2.setDate(3, new java.sql.Date(sdf.parse(bb.getBillDate())
					.getTime()));
			pstmt2.setDate(4, new java.sql.Date(sdf.parse(bb.getBillDueDate())
					.getTime()));
			pstmt2.setDouble(5, bb.getAmount());

			int res = pstmt2.executeUpdate();
			if (res == 1) {
				sql = "insert into shop_bill_item(bill_id, dept_id"
						+ ", bill_item_id, bill_item_expense) values"
						+ " (?, ?, SEQ_BILL_ITEM_ID.nextval, ?)";

				List<BillItemBean> deptList = bb.getDeptList();
				PreparedStatement pstmt3 = conn.prepareStatement(sql);

				for (BillItemBean bean : deptList) {
					if (bean.getConfirm() != null) {
						pstmt3.setInt(1, billId);
						pstmt3.setInt(2, bean.getDeptId());
						pstmt3.setDouble(3, Double.parseDouble(bean.getAmount()));
						res = pstmt3.executeUpdate();
						if (res != 1) {
							conn.rollback();
							break;
						}
					}
				}

				conn.commit();
				result = true;
			} else {
				conn.rollback();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new SQLException(e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.setAutoCommit(true);
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return result;
	}

	public List getDeptList() throws SQLException {
		List<BillItemBean> result = null;
		Connection conn = null;

		try {
			conn = ManageConnection.getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("select * from shop_dept");
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				result = new ArrayList<BillItemBean>();
				while (rs.next()) {
					result.add(new BillItemBean(rs.getInt("dept_id"), rs
							.getString("dept_name")));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return result;
	}

	public List getVendorList() throws SQLException {
		List<VendorBean> result = null;

		Connection conn = null;

		try {
			conn = ManageConnection.getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("select vendor_id, vendor_name from shop_vendor");
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				result = new ArrayList<VendorBean>();
				while (rs.next()) {
					result.add(new VendorBean(rs.getInt("vendor_id"), rs
							.getString("vendor_name")));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return result;
	}
}