package imis_mate.DAO;

import imis_mate.bean.SupplyInfoBean;

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

import cmis.database.DBPoolManager;

public class SupplyInfoDAO {
	private boolean flag;
	private PreparedStatement pstmt;
	private DBPoolManager dbpool;

	public SupplyInfoDAO(){
		dbpool = new DBPoolManager();
	}
	
	public boolean save(SupplyInfoBean sib){
		flag = false;
		dbpool.getConnection();
		String sql ="insert into Supply(SupplyNo,SupplyName,Tele,Addr,AttPerson," +
				"Acccount,Bank,Remark) values(SupplyNo.nextval,?,?,?,?,?,?,?)";
		try {
			pstmt = dbpool.conn.prepareStatement(sql);
			pstmt.setString(1, sib.getSupplyName());
			pstmt.setString(2, sib.getTele());
			pstmt.setString(3, sib.getAddr());
			pstmt.setString(4, sib.getAttPerson());
			pstmt.setString(5, sib.getAcccount());
			pstmt.setString(6, sib.getBank());
			pstmt.setString(7, sib.getRemark());
			flag = pstmt.executeUpdate(sql)>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			dbpool.freeConnection();
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				pstmt=null;
			}
		}	
		return flag;
	}
	
	public boolean update(SupplyInfoBean sib){
		flag = false;
		dbpool.getConnection();
		String sql ="update Supply set SupplyName=?,Tele=?,Addr=?,AttPerson=?" +
				",Acccount=?,Bank=?,Remark=? where SupplyNo=?";
		try {
			pstmt = dbpool.conn.prepareStatement(sql);
			pstmt.setString(1, sib.getSupplyName());
			pstmt.setString(2, sib.getTele());
			pstmt.setString(3, sib.getAddr());
			pstmt.setString(4, sib.getAttPerson());
			pstmt.setString(5, sib.getAcccount());
			pstmt.setString(6, sib.getBank());
			pstmt.setString(7, sib.getRemark());
			pstmt.setInt(8, sib.getSupplyNo());
			flag = pstmt.executeUpdate(sql)>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			dbpool.freeConnection();
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				pstmt=null;
			}
		}	
		return flag;
	}
	
	public boolean delete(SupplyInfoBean sib){
		flag =false;
		dbpool.getConnection();
		String sql = "delete from Supply where SupplyNo=?";
		try {
			pstmt=dbpool.conn.prepareStatement(sql);
			pstmt.setInt(1, sib.getSupplyNo());
			flag= pstmt.executeUpdate(sql)>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			dbpool.freeConnection();
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				pstmt=null;
			}
		}	
		
		return flag;
	}
	
	public SupplyInfoBean getSupplyInfoOfSupplyNo(int supplyNo){
		SupplyInfoBean sib = new SupplyInfoBean();
		dbpool.getConnection();
		String sql = "select SupplyNo,SupplyName,Tele,Addr,Attperson,Acccount,Bank,Remark from Supply where SupplyNo="+supplyNo;
		try {
			Statement stmt=dbpool.conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				sib.setSupplyNo(rs.getInt("SupplyNo"));
				if(rs.getString("SupplyName")==null){
					sib.setSupplyName("");
				}else{
					sib.setSupplyName(rs.getString("SupplyName").trim());
				}
				if(rs.getString("Tele")==null){
					sib.setTele("");
				}else{
					sib.setTele(rs.getString("tele").trim());
				}
				if(rs.getString("addr")==null){
					sib.setAddr("");
				}else{
					sib.setAddr(rs.getString("Addr").trim());
				}
				if(rs.getString("AttPerson")==null){
					sib.setAttPerson("");
				}else{
					sib.setAttPerson(rs.getString("AttPerson").trim());
				}
				if(rs.getString("Acccount")==null){
					sib.setAcccount("");
				}else{
					sib.setAcccount(rs.getString("Acccount").trim());
				}
				if(rs.getString("bank")==null){
					sib.setBank("");
				}else{
					sib.setBank(rs.getString("Bank").trim());
				}
				if(rs.getString("Remark")==null){
					sib.setRemark("");
				}else{
					sib.setRemark(rs.getString("Remark").trim());
				}
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
		}
		return sib;
	}
	
	public ArrayList getSupplyInfo(){
		ArrayList list =new ArrayList();
		
		dbpool.getConnection();
		String sql = "select SupplyNo,SupplyName,Tele,Addr,Attperson,Acccount,Bank,Remark from Supply";
		try {
			Statement stmt=dbpool.conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				SupplyInfoBean sib = new SupplyInfoBean();
				sib.setSupplyNo(rs.getInt("SupplyNo"));
				if(rs.getString("SupplyName")==null){
					sib.setSupplyName("");
				}else{
					sib.setSupplyName(rs.getString("SupplyName").trim());
				}
				if(rs.getString("Tele")==null){
					sib.setTele("");
				}else{
					sib.setTele(rs.getString("tele").trim());
				}
				if(rs.getString("addr")==null){
					sib.setAddr("");
				}else{
					sib.setAddr(rs.getString("Addr").trim());
				}
				if(rs.getString("AttPerson")==null){
					sib.setAttPerson("");
				}else{
					sib.setAttPerson(rs.getString("AttPerson").trim());
				}
				if(rs.getString("Acccount")==null){
					sib.setAcccount("");
				}else{
					sib.setAcccount(rs.getString("Acccount").trim());
				}
				if(rs.getString("bank")==null){
					sib.setBank("");
				}else{
					sib.setBank(rs.getString("Bank").trim());
				}
				if(rs.getString("Remark")==null){
					sib.setRemark("");
				}else{
					sib.setRemark(rs.getString("Remark").trim());
				}
				list.add(sib);
				
				
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
		}
		return list;
	} 
	
	public ArrayList searchSupplyInfo(String supplyName,String addr,String attPerson){
		ArrayList list =new ArrayList();
		SupplyInfoBean sib = new SupplyInfoBean();
		dbpool.getConnection();
		String sql = "select SupplyNo,SupplyName,Tele,Addr,Attperson,Acccount,Bank,Remark from Supply where SupplyName like '%"+supplyName+"%' and Addr like '%"+addr+"%' and AttPerson like '%"+attPerson+"%'";
		try {
			Statement stmt=dbpool.conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				sib.setSupplyNo(rs.getInt("SupplyNo"));
				if(rs.getString("SupplyName")==null){
					sib.setSupplyName("");
				}else{
					sib.setSupplyName(rs.getString("SupplyName").trim());
				}
				if(rs.getString("Tele")==null){
					sib.setTele("");
				}else{
					sib.setTele(rs.getString("tele").trim());
				}
				if(rs.getString("addr")==null){
					sib.setAddr("");
				}else{
					sib.setAddr(rs.getString("Addr").trim());
				}
				if(rs.getString("AttPerson")==null){
					sib.setAttPerson("");
				}else{
					sib.setAttPerson(rs.getString("AttPerson").trim());
				}
				if(rs.getString("Acccount")==null){
					sib.setAcccount("");
				}else{
					sib.setAcccount(rs.getString("Acccount").trim());
				}
				if(rs.getString("bank")==null){
					sib.setBank("");
				}else{
					sib.setBank(rs.getString("Bank").trim());
				}
				if(rs.getString("Remark")==null){
					sib.setRemark("");
				}else{
					sib.setRemark(rs.getString("Remark").trim());
				}
				list.add(sib);
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
		}
		return list;
	} 
}