package com.xdf.supermarket.service;

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

import com.xdf.supermarket.db.DBConnection;
import com.xdf.supermarket.dto.BillDTO;

public class BillBean extends BaseBean{
	
	/**
	 * 得到未支付帳單
	 */
	public ArrayList getUnpaidBillByVendorid(String vendor_id){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList list = new ArrayList(); 
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = 
			"select b.*,to_char(bill_date,'yyyy-mm-dd') bd," +
			"to_char(bill_due_date,'yyyy-mm-dd') bdd from table_bill b where vendor_id="+vendor_id
			+" and bill_paid_flag=0 order by bill_id";
			rs = stmt.executeQuery(sql);
			while (rs.next()){
				BillDTO v = new BillDTO(); 
				v.setBill_id(rs.getString("bill_id"));
				v.setVendor_id(rs.getString("vendor_id"));
				v.setBill_date(rs.getString("bd"));
				v.setBill_due_date(rs.getString("bdd"));
				v.setBill_amount(rs.getString("bill_amount"));
				v.setBill_paid_flag(rs.getString("bill_paid_flag"));				
				list.add(v);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return list;		
	}
	/**
	 * 支付帳單
	 */
	public boolean payBill(String bill_id){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		boolean flag = false;		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql ="update table_bill set bill_paid_flag=1 where bill_id="+bill_id;
			int t = stmt.executeUpdate(sql);
			if (t==1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return flag;		
	}
	/**
	 * 增加帳單
	 */
	public boolean addBill(String[] dept_id,String[] money,
			String total,String bill_date,
			String bill_due_date,String vendor_id){

		Connection conn = null;
		Statement stmt = null;
		PreparedStatement stmt2 = null;
		PreparedStatement stmt3 = null;
		ResultSet rs = null;
		boolean flag = false;		
		try {
			conn = DBConnection.getConnection();
			conn.setAutoCommit(false);//設置不自動提交
			stmt = conn.createStatement();			
			String sql ="select seq_bill_id.nextval from dual";
			rs = stmt.executeQuery(sql);
			rs.next();
			int bill_id = rs.getInt(1);
			sql = "insert into table_bill values(?,?," +
					"to_date(?,'yyyy-mm-dd'),to_date(?,'yyyy-mm-dd'),0,?)";
			stmt2 = conn.prepareStatement(sql);
			stmt2.setInt(1,bill_id);
			stmt2.setString(2,vendor_id);
			stmt2.setString(3,bill_date);
			stmt2.setString(4,bill_due_date);
			stmt2.setString(5,total);
			stmt2.executeUpdate();
			sql = "insert into table_bill_item values(?,?,seq_bill_item_id.nextval,?)";
			stmt3 = conn.prepareStatement(sql);
			for (int i=0;i<dept_id.length;i++){
				stmt3.setInt(1,bill_id);
				stmt3.setString(2,dept_id[i]);
				stmt3.setString(3,money[i]);
				stmt3.executeUpdate();
			}
			conn.commit();//提交
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (conn!=null)
					conn.rollback();	//回滾 
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			close(rs);
			close(stmt);
			close(stmt2);
			close(stmt3);
			try {
				if (conn!=null)
					conn.setAutoCommit(true);	//恢復自動提交
			} catch (SQLException e1) {
				e1.printStackTrace();
			}			
			close(conn);
		}
		return flag;			
	}	
}