package com.xdf.supermarket.service;

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

import com.xdf.supermarket.db.DBConnection;
import com.xdf.supermarket.dto.VendorDTO;
import com.xdf.supermarket.util.Constant;

public class VendorBean extends BaseBean{
	
	/**
	 * 得到所有的供應商信息 
	 */
	public ArrayList getAllVendor(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList list = new ArrayList();
		 
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "select * from table_vendor order by vendor_id";
			rs = stmt.executeQuery(sql);
			while (rs.next()){
				VendorDTO v = new VendorDTO(); 
				v.setVendor_id(rs.getString("vendor_id"));
				v.setVendor_name(rs.getString("vendor_name"));
				v.setVendor_fax(rs.getString("vendor_fax"));
				v.setVendor_phone(rs.getString("vendor_phone"));
				v.setVendor_address(rs.getString("vendor_address"));
				v.setVendor_contact_person(rs.getString("vendor_contact_person"));
				list.add(v);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return list;		
	}
	/**
	 * 增加供應商
	 */
	public boolean addVendor(VendorDTO dd){
		Connection conn = null;
		PreparedStatement stmt = null;
		boolean flag = false;
		try {
			conn = DBConnection.getConnection();
			String sql = 
				"insert into table_vendor values(seq_vendor_id.nextval,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,dd.getVendor_name());
			stmt.setString(2,dd.getVendor_address());
			stmt.setString(3,dd.getVendor_phone());
			stmt.setString(4,dd.getVendor_fax());
			stmt.setString(5,dd.getVendor_contact_person());
			int t = stmt.executeUpdate();
			if (t==1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(stmt);
			close(conn);
		}
		return flag;
	} 
	
	/**
	 * 修改供應商
	 */
	public boolean updateVendor(VendorDTO dd){
		Connection conn = null;
		PreparedStatement stmt = null;
		boolean flag = false;
		try {
			conn = DBConnection.getConnection();
			String sql = "update table_vendor set vendor_name=?," +
					"vendor_address=?,vendor_phone=?,vendor_fax=?," +
					"vendor_contact_person=? where vendor_id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,dd.getVendor_name());
			stmt.setString(2,dd.getVendor_address());
			stmt.setString(3,dd.getVendor_phone());
			stmt.setString(4,dd.getVendor_fax());
			stmt.setString(5,dd.getVendor_contact_person());
			stmt.setString(6,dd.getVendor_id());
			int t = stmt.executeUpdate();
			if (t==1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(stmt);
			close(conn);
		}
		return flag;
	}
	
	/**
	 * 得到總頁數
	 */
	public int getVendorTotalPage(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		int total = 0;
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(*) c from table_vendor");
			rs.next();
			int num = rs.getInt("c");
			total = (num+Constant.VENDOR_PAGE_SIZE-1)/Constant.VENDOR_PAGE_SIZE;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);			
		}
		return total;
	}
	/**
	 * 得到一頁部供應商
	 */
	public ArrayList getOnePageVendor(int pageno){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList list = new ArrayList();
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			int pagesize = Constant.VENDOR_PAGE_SIZE;
			int first = (pageno-1)*pagesize+1;
			int last = pageno*pagesize;
			String sql = 
				"select * from " + 
				"(select s.*,rownum r from " +
				"(select * from table_vendor order by vendor_id) s " +
				"where rownum<="+last+") " +
				"where r>="+first;

			rs = stmt.executeQuery(sql);
			while (rs.next()){
				VendorDTO v = new VendorDTO(); 
				v.setVendor_id(rs.getString("vendor_id"));
				v.setVendor_name(rs.getString("vendor_name"));
				v.setVendor_fax(rs.getString("vendor_fax"));
				v.setVendor_phone(rs.getString("vendor_phone"));
				v.setVendor_address(rs.getString("vendor_address"));
				v.setVendor_contact_person(rs.getString("vendor_contact_person"));
				list.add(v);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return list;		
	}	
	/**
	 * 根據id得到一個供應商
	 */
	public VendorDTO getOneVendor(String vendor_id){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		VendorDTO v = new VendorDTO();
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "select * from table_vendor where vendor_id="+vendor_id; 
			
			rs = stmt.executeQuery(sql);
			if (rs.next()){
				v.setVendor_id(rs.getString("vendor_id"));
				v.setVendor_name(rs.getString("vendor_name"));
				v.setVendor_fax(rs.getString("vendor_fax"));
				v.setVendor_phone(rs.getString("vendor_phone"));
				v.setVendor_address(rs.getString("vendor_address"));
				v.setVendor_contact_person(rs.getString("vendor_contact_person"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return v;		
	}
}