package imis_mate.DAO;

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 database.DBPoolManager;
import imis_mate.bean.MaterialBuyBean;
import imis_mate.bean.MaterialBuyDetailBean;
import imis_mate.bean.MaterialBuySearchBean;
import imis_mate.bean.MaterialChangeBean;

public class MateBuyDAO {
	private DBPoolManager dbpool;
	private PreparedStatement pstmt;
	private Statement st;
	
	
	public MateBuyDAO(){
		dbpool = new DBPoolManager();

	}

	public boolean save(MaterialBuyBean mbb) {
		// TODO Auto-generated method stub
		boolean flag=false;
		dbpool.getConnection();
		try{
			//String sql ="insert into Material_Buy_Table(BuyNo,PayType,RePay,AcPay,InDate,BuyDep,BuyBy,ApplyDep,ApplyBy,UseDep,UseBy,BuyFor,SupplyNo,PerBy,ReMark) values(?,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?,?,?,?,?,?,?,?)";
			String sql ="insert into Material_Buy_Table(BuyNo,PayType,RePay,AcPay,InDate,BuyDep,BuyBy,ApplyDep,ApplyBy,UseDep,UseBy,BuyFor,SupplyNo,PerBy,ReMark) values('";
			sql = sql + mbb.getBuyNo()+"','";
			sql = sql + mbb.getPayType() + "',";
			sql = sql + mbb.getRePay() +",";
			sql = sql + mbb.getAcPay() +",to_date('";
			sql = sql + mbb.getInDate() +"','yyyy-mm-dd'),";
			sql = sql + mbb.getBuyDep() +",'";
			sql = sql + mbb.getBuyBy() +"',";
			sql = sql + mbb.getApplyDep() +",'";
			sql = sql + mbb.getApplyBy() +"',";
			sql = sql + mbb.getUseDep()+",'";
			sql = sql + mbb.getUseBy() +"','";
			sql = sql + mbb.getBuyFor() +"',";
			sql = sql + mbb.getSupplyNo() +",'";
			sql = sql + mbb.getPerBy() +"','";
			sql = sql + mbb.getReMark()+"')";
	
			
			
			st = dbpool.conn.createStatement();
		
	
			
		
			int i =st.executeUpdate(sql);
			flag = i>0?true:false;		
		}catch(Exception e){
			flag = false;
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				st=null;
			}
		}	
		return flag;
	}
	
	public boolean update(MaterialBuyBean mbb){
		boolean flag = false;
		dbpool.getConnection();
		try{
			String sql ="update Material_Buy_Table set PayType='";
			sql = sql+mbb.getPayType()+"',RePay=";
			sql = sql+mbb.getRePay()+",AcPay=";
			sql = sql+mbb.getAcPay()+",InDate=to_date('"+mbb.getInDate()+"','yyyy-mm-dd'),BuyDep=";
			sql = sql+mbb.getBuyDep()+",BuyBy='"+mbb.getBuyBy()+"',ApplyDep="+mbb.getApplyDep()+"," +
					"ApplyBy='"+mbb.getApplyBy()+"',UseDep=";
			sql = sql+mbb.getUseDep()+",UseBy='"+mbb.getUseBy()+"',BuyFor='"+mbb.getBuyFor()+"'," +
					"SupplyNo='"+mbb.getSupplyNo()+"',PerBy='";
			sql = sql+mbb.getPerBy()+"',ReMark='"+mbb.getReMark()+"' where BuyNo='"+mbb.getBuyNo()+"'";
			
			st=dbpool.conn.createStatement();
			flag = st.executeUpdate(sql)>0?true:false;		
		}catch(Exception e){
			flag = false;
			e.printStackTrace();
		}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(String buyNo){
		boolean flag = false;
		dbpool.getConnection();
		String sql = "delete from Material_Buy_Table where BuyNo='"+buyNo+"'";
	
		try {
			st = dbpool.conn.createStatement();

			flag = st.executeUpdate(sql)>0?true:false;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			dbpool.freeConnection();
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				st=null;
			}
		}
		return flag;
	}

	
	public ArrayList getMateBuy(){
		ArrayList list =new ArrayList();
		dbpool.getConnection();
		MaterialBuyBean mbb = new MaterialBuyBean();
		String sql ="select buyNo,payType,rePay,acPay,inDate,buyDep,buyBy,applyDep,applyBy,useDep,useBy,buyFor,supplyNo,perBy,reMark from Material_Buy_Table";
		try {
			st = dbpool.conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				mbb.setBuyNo(rs.getString("buyNo"));
				if(rs.getString("payType")==null){
					mbb.setPayType("");
				}else{
					mbb.setPayType(rs.getString("payType").trim());
				}
				mbb.setRePay(rs.getDouble("rePay"));
				mbb.setAcPay(rs.getDouble("inDate"));
				if(rs.getString("inDate")==null){
					mbb.setInDate("");
				}else{
					mbb.setInDate(rs.getString("inDate").trim());
				}
				mbb.setBuyDep(rs.getInt("buyDep"));
				if(rs.getString("buyBy")==null){
					mbb.setBuyBy("");
				}else{
					mbb.setBuyBy(rs.getString("buyBy"));
				}
				mbb.setApplyDep(rs.getInt("applyDep"));
				if(rs.getString("applyBy")==null){
					mbb.setApplyBy("");
				}else{
					mbb.setApplyBy(rs.getString("applyBy").trim());
				}
				mbb.setUseDep(rs.getInt("useDep"));
				if(rs.getString("useBy")==null){
					mbb.setUseBy("");
				}else{
					mbb.setUseBy(rs.getString("useBy").trim());
				}
				if(rs.getString("buyFor")==null){
					mbb.setBuyFor("");
				}else{
					mbb.setBuyFor(rs.getString("buyFor"));
				}
				mbb.setSupplyNo(rs.getInt("supplyNo"));
				if(rs.getString("perBy")==null){
					mbb.setPerBy("");
				}else{
					mbb.setPerBy(rs.getString("perBy").trim());
				}
				if(rs.getString("reMark")==null){
					mbb.setReMark("");
				}else{
					mbb.setReMark(rs.getString("reMark").trim());
				}
				list.add(mbb);
			}
			st.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
		}
		return list;	
	}
	
	public MaterialBuyBean getMateBuyByBuyNo(String buyNo){
		MaterialBuyBean mbb = new MaterialBuyBean();
		String sql ="select buyNo,payType,rePay,acPay,inDate,buyDep,buyBy,applyDep,applyBy,useDep,useBy,buyFor,supplyNo,perBy,reMark from Material_Buy_Table where buyNo = '"+buyNo+"'";
		dbpool.getConnection();
		
		
		try {
			
			st = dbpool.conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				mbb.setBuyNo(rs.getString("buyNo"));
				if(rs.getString("payType")==null){
					mbb.setPayType("");
				}else{
					mbb.setPayType(rs.getString("payType").trim());
				}
				mbb.setRePay(rs.getDouble("rePay"));
				mbb.setAcPay(rs.getDouble("acPay"));
				if(rs.getDate("inDate")==null){
					mbb.setInDate("");
				}else{
					mbb.setInDate(rs.getDate("inDate").toString());
				}
				mbb.setBuyDep(rs.getInt("buyDep"));
				if(rs.getString("buyBy")==null){
					mbb.setBuyBy("");
				}else{
					mbb.setBuyBy(rs.getString("buyBy"));
				}
				mbb.setApplyDep(rs.getInt("applyDep"));
				if(rs.getString("applyBy")==null){
					mbb.setApplyBy("");
				}else{
					mbb.setApplyBy(rs.getString("applyBy").trim());
				}
				mbb.setUseDep(rs.getInt("useDep"));
				if(rs.getString("useBy")==null){
					mbb.setUseBy("");
				}else{
					mbb.setUseBy(rs.getString("useBy").trim());
				}
				if(rs.getString("buyFor")==null){
					mbb.setBuyFor("");
				}else{
					mbb.setBuyFor(rs.getString("buyFor"));
				}
				mbb.setSupplyNo(rs.getInt("supplyNo"));
				if(rs.getString("perBy")==null){
					mbb.setPerBy("");
				}else{
					mbb.setPerBy(rs.getString("perBy").trim());
				}
				if(rs.getString("reMark")==null){
					mbb.setReMark("");
				}else{
					mbb.setReMark(rs.getString("reMark").trim());
				}
			
			}
			st.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
			
		}
		if(mbb.getBuyNo()==null)
		{
			return null;
		}
		else
		
			return mbb;
	}

	public ArrayList search(MaterialBuyBean mate, MaterialBuyDetailBean mateD, String startDate, String endDate) throws Exception {
		//多条件查询
		
		ArrayList array = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		MaterialTypeDAO mateTypeDAO = new MaterialTypeDAO();
		OrganDAO organDAO = new OrganDAO();
		
		//Build the search criterias
		StringBuffer sql = new StringBuffer(512);
		sql.append(	"SELECT Material_Buy_Table.BuyNo BuyNo, Material_Buy_Detail.MaterialTypeName, Material_Buy_Detail.MaModel, Material_Buy_Detail.PriceUnit, Material_Buy_Detail.InCount, Material_Buy_Table.ApplyDep, Material_Buy_Table.BuyFor " +
					"FROM Material_Buy_Table,Material_Buy_Detail " +
					"WHERE Material_Buy_Table.BuyNo = Material_Buy_Detail.BuyNo AND " );
//		if(mate.getApplyDep() != 0) {
//			sql.append("ApplyDep = '" +
//					DBUtil.fixSqlFieldValue(String.valueOf(mate.getApplyDep())) + "' AND ");
//		}
		if(mate.getApplyDep() != 0) {
			sql.append("(ApplyDep IN " +organDAO.getOrganSQL(mate.getApplyDep(),"ApplyDep")+") AND ");
		}
//		if(mateD.getMaterialTypeName() != null && mateD.getMaterialTypeName().equals("") != true) {
//			sql.append("MaterialTypeName LIKE '%" +
//					DBUtil.fixSqlFieldValue(mateD.getMaterialTypeName()) + "%' AND ");
//		}
//		if (mateD.getMaterialTypeNo() != null && mateD.getMaterialTypeNo().equals("") != true) {
//			sql.append("MaterialTypeNo = '" + 
//					DBUtil.fixSqlFieldValue(mateD.getMaterialTypeNo()) + "' AND ");
//		}
		if (mateD.getMaterialTypeNo() != null && mateD.getMaterialTypeNo().equals("") != true) {
			sql.append("(MaterialTypeNo IN "+mateTypeDAO.getMaterialSQL(mateD.getMaterialTypeNo())+") AND ");
		}
		if (mateD.getFlag() != null && mateD.getFlag().equals("") != true && !mateD.getFlag().equals("-1")) {
			//入库条件 不选择 的情况
			sql.append("Flag = '" + 
					DBUtil.fixSqlFieldValue(mateD.getFlag()) + "' AND ");
		} else if (mateD.getFlag() != null && mateD.getFlag().trim().equals("-1")) {
			//入库标志 条件为 否 的情况
			sql.append("Flag IS NULL AND ");
		}
//		if (mateD.getFlag() != null && mateD.getFlag().trim().equals("*")) {
//			//入库标志 条件为 是 的情况
//			sql.append("Flag = '" + 
//					DBUtil.fixSqlFieldValue(mateD.getFlag()) + "' AND ");
//		} 

		
		if (mateD.getReFlag() != null && mateD.getReFlag().equals("") != true && !mateD.getReFlag().equals("-1")) {
			//退货标志 条件不选择的情况
			sql.append("ReFlag = '" + 
					DBUtil.fixSqlFieldValue(mateD.getReFlag()) + "' AND ");
		} else if (mateD.getReFlag() != null && mateD.getReFlag().trim().equals("-1")) {
			//退货标志 条件 否 的情况
			sql.append("ReFlag IS NULL AND ");
		}
//		if (mateD.getReFlag() != null && mateD.getReFlag().trim().equals("*")) {
//			//退货标志 条件 是 的情况
//			sql.append("ReFlag = '" + 
//					DBUtil.fixSqlFieldValue(mateD.getReFlag()) + "' AND ");
//		} 

		if((startDate != null && startDate.equals("") != true) && (endDate != null && endDate.equals("") != true)) {
			sql.append("inDate BETWEEN TO_DATE('" +
					DBUtil.fixSqlFieldValue(startDate.trim()) + "','YYYY-MM-DD') AND TO_DATE('"+endDate.trim()+"','YYYY-MM-DD')");
		}
		
		
		//Remove unused 'AND' & 'WHERE'
		if(sql.substring(sql.length()-5).equals(" AND ")) {
			sql.delete(sql.length()-5, sql.length()-1);
		}
		if(sql.substring(sql.length()-7).equals(" WHERE ")) {
			sql.delete(sql.length()-7, sql.length()-1);
		}
		sql.append(" order by Material_Buy_Table.BuyNo");
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql.toString());
			MaterialBuySearchBean mate1 = null;

			while(rs.next()) {
				mate1 = new MaterialBuySearchBean();
				mate1.setBuyNo(rs.getString("BuyNo"));
				mate1.setApplyDep(rs.getInt("ApplyDep"));
				mate1.setBuyFor(rs.getString("BuyFor"));
				mate1.setMaterialTypeName(rs.getString("MaterialTypeName"));
				mate1.setMaModel(rs.getString("MaModel"));
				mate1.setPriceUnit(rs.getDouble("PriceUnit"));
				mate1.setInCount(rs.getInt("InCount"));
				array.add(mate1);	
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(stmt);
			DBConnection.close(conn);
		}
		return array;	
	}
//修改采购表所有信息
public boolean update(String buyNo,MaterialBuyBean mbb){
		boolean flag = false;
		dbpool.getConnection();
		try{
			String sql ="update Material_Buy_Table set BuyNo='"+mbb.getBuyNo()+"',PayType='";
			sql = sql+mbb.getPayType()+"',RePay=";
			sql = sql+mbb.getRePay()+",AcPay=";
			sql = sql+mbb.getAcPay()+",InDate=to_date('"+mbb.getInDate()+"','yyyy-mm-dd'),BuyDep=";
			sql = sql+mbb.getBuyDep()+",BuyBy='"+mbb.getBuyBy()+"',ApplyDep="+mbb.getApplyDep()+"," +
					"ApplyBy='"+mbb.getApplyBy()+"',UseDep=";
			sql = sql+mbb.getUseDep()+",UseBy='"+mbb.getUseBy()+"',BuyFor='"+mbb.getBuyFor()+"'," +
					"SupplyNo='"+mbb.getSupplyNo()+"',PerBy='";
			sql = sql+mbb.getPerBy()+"',ReMark='"+mbb.getReMark()+"' where BuyNo='"+buyNo+"'";
			
			st=dbpool.conn.createStatement();
			flag = st.executeUpdate(sql)>0?true:false;		
		}catch(Exception e){
			flag = false;
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				pstmt=null;
			}
		}	
		return flag;
	}
}