billdco.java

海鲜超市管理 登录 修改 增加 等功能jsp+servlet

/*
 * 创建日期 2006-12-18
 * TODO 
 */
package module.bill.dco;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import module.bill.dto.AddBillDto;
import module.vendor.dto.VendorDto;
import conn.ConnSky;
import dto.DeptDto;


public class BillDco {
	
	public static int sumNote;
	
	public void selectVendor(List list){	
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		VendorDto dto = null;
		String sql = "select vendor_id, vendor_name from table_vendor";
	
		try{
			conn = ConnSky.getConn();
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next()){
				dto = new VendorDto();
				dto.setName(rs.getString("vendor_name"));
				dto.setVendorId(rs.getString("vendor_id"));
				list.add(dto);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				rs.close();
				pstmt.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		
	}
	
	public void selectDept(List list){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DeptDto dto = null;
		String sql = "select dept_name, dept_id from table_dept";
		
		try{
			conn = ConnSky.getConn();
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				dto = new DeptDto();
				dto.setName(rs.getString("dept_name"));
				dto.setId(rs.getString("dept_id"));
				list.add(dto);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				rs.close();
				pstmt.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
	
	
	public void addBill(AddBillDto dto){
		Connection conn = null;
		PreparedStatement pstmt = null;
		int flag = 0;
		String seq =null; 


		
		try{
			conn = ConnSky.getConn();
			seq = selectBillSeq(conn);
			System.out.println("seq");
			String sqlBill = "insert into table_bill(bill_id, vendor_id, bill_date, " +
			"bill_due_date, bill_amount) values " +
				"("+seq+",?,to_date(?,'yyyy-mm-dd'),to_date(?,'yyyy-mm-dd'),?)";

			String sqlItem = "insert into table_bill_item(bill_id, dept_id, bill_item_id, bill_item_expense) " +
				" values("+seq+",?,item_seq.nextval,?)";
			
			pstmt = conn.prepareStatement(sqlBill);
			pstmt.setString(1,dto.getVendorId());
			pstmt.setString(2,dto.getBillDate());
			pstmt.setString(3,dto.getDueDate());
			pstmt.setString(4,dto.getSum());

			conn.commit();
			flag = pstmt.executeUpdate();
			for (int i = 0; i < dto.getDeptId().length; i++){
			pstmt = conn.prepareStatement(sqlItem);
			pstmt.setString(1,dto.getDeptId()[i]);
			pstmt.setString(2,dto.getExpense()[i]);
			pstmt.executeUpdate();
			
			}
			conn.commit();

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				pstmt.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		
		
	}

	public void updateDept(DeptDto dto){
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int flag = 0;
		String sql = "update table_dept set dept_name =? ,dept_description=? where dept_id =? ";
		
		try{
			conn = ConnSky.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,dto.getName());
			pstmt.setString(2,dto.getDescription());
			pstmt.setInt(3,Integer.parseInt(dto.getId()));
			flag = pstmt.executeUpdate();

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				pstmt.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	
	}	

	private String selectBillSeq (Connection conn){
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String seq = null;
		String sql = "select bill_seq.nextval seq from dual";
		
		try{
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			if(rs.next()){
				seq = rs.getString("seq");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				rs.close();
				pstmt.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return seq;
		
	}
	
	
}