package imis_elec;
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;




public class Elec_Fee_infoDAOImpl implements Elec_Fee_infoDAO{
	
	private static final String GET_CUSTOMER_SQL =
		"SELECT * FROM Elec_Fee_info WHERE ElecUseType = ?";
	private static final String CREATE_CUSTOMER_SQL =
		 "INSERT INTO Elec_Fee_info (FeeId,ElecUseType,PriceUnit,Memo) VALUES (?,?,?,?)";
	private static final String DELETE_CUSTOMER_SQL =
        "DELETE FROM Elec_Fee_info WHERE ElecUseType = ?";
	private static final String UPDATE_CUSTOMER_SQL =
		"UPDATE Elec_Fee_info SET PriceUnit=?,Memo=? where ElecUseType=?";
	private static final String SEARCH_CUSTOMER_SQL =
        "SELECT * FROM Elec_Fee_info WHERE ";

	public boolean deleteElecDev_Type(int FeeId) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	public Elec_Fee_infoTO getElec_Fee_info(int ElecUseType) throws Exception {
		//Connection connection = null;
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        Elec_Fee_infoTO elec_Fee_info = null;
        DBPoolManager db = new DBPoolManager();
        db.getConnection();
        try {
            //connection = DBConnection.getConnection();
            // Prepare a statement to insert a record
            pStatement = db.conn.prepareStatement(GET_CUSTOMER_SQL);
            pStatement.setInt(1,ElecUseType);
            rs = pStatement.executeQuery();
            if (rs.next()) {
            	elec_Fee_info = new Elec_Fee_infoTO();
            	elec_Fee_info.setFeeId(rs.getInt("FeeId"));
            	elec_Fee_info.setElecUseType(rs.getInt("ElecUseType"));
            	elec_Fee_info.setPriceUnit(rs.getDouble("PriceUnit"));
            	elec_Fee_info.setMemo(rs.getString("Memo"));
            }
            
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			db.freeConnection();
        }
        
        return elec_Fee_info;
	}

	public boolean insertElec_Fee_info(Elec_Fee_infoTO Elec_Fee_info) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	public ArrayList searchElecDev_Type(Elec_Fee_infoTO Elec_Fee_info) throws Exception {
		ArrayList arrayList = new ArrayList();
        //Connection connection = null;
		 DBPoolManager db = new DBPoolManager();
	      db.getConnection();
        Statement statement = null;
        ResultSet rs = null;
        StringBuffer criteriaSql = new StringBuffer(512);
        criteriaSql.append(SEARCH_CUSTOMER_SQL);
        
        if (Elec_Fee_info.getElecUseType() !=-1) {
            criteriaSql.append("ElecUseType=" +
            		Elec_Fee_info.getElecUseType());
        }
        
       
       
        
         
       
        if (criteriaSql.substring(criteriaSql.length()-5).
	            equals(" AND "))
	        criteriaSql.delete(criteriaSql.length()-5,
	                criteriaSql.length()-1);
	    if (criteriaSql.substring(criteriaSql.length()-7).
	            equals(" WHERE "))
	        criteriaSql.delete(criteriaSql.length()-7,
	                criteriaSql.length()-1);
	    try {
	        //connection = DBConnection.getConnection();
	    	
	        statement = db.conn.createStatement();
	        rs = statement.executeQuery(criteriaSql.toString());
	        
	        while (rs.next()) {
	        	Elec_Fee_infoTO elec_Fee_infoTO = new Elec_Fee_infoTO();
	        	elec_Fee_infoTO.setFeeId(rs.getInt("FeeId"));
	        	elec_Fee_infoTO.setElecUseType(rs.getInt("ElecUseType"));
	        	elec_Fee_infoTO.setPriceUnit(rs.getDouble("priceUnit"));
	        	elec_Fee_infoTO.setMemo(rs.getString("Memo"));
	        	
	
	            arrayList.add(elec_Fee_infoTO);
	        }
	
	    } catch (SQLException e) {
	        e.printStackTrace();
	        throw new Exception();
	    } finally {
			//DBConnection.close(rs);
			//DBConnection.close(statement);
	    	db.freeConnection();
	    }
	    
	    return arrayList;
	}

	public boolean updateElec_Fee_info(Elec_Fee_infoTO Elec_Fee_info) throws Exception {
		boolean flag = false;
        //Connection connection = null;
		 DBPoolManager db = new DBPoolManager();
	     db.getConnection();
        PreparedStatement pStatement = null;
        try {
            //connection = DBConnection.getConnection();
            // Prepare a statement to insert a record
            pStatement = db.conn.prepareStatement(UPDATE_CUSTOMER_SQL);  
           
            pStatement.setDouble(1,Elec_Fee_info.getPriceUnit());
            pStatement.setString(2, Elec_Fee_info.getMemo());
            pStatement.setInt(3, Elec_Fee_info.getElecUseType());
            
            
            int i = pStatement.executeUpdate();
            flag = i > 0 ? true : false;
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			//DBConnection.close(pStatement);
			//DBConnection.close(connection);
        	db.freeConnection();
        }
        
        return flag;
	}

}