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.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import database.DBPoolManager;

public class ElecUseInfoDAOImpl implements ElecUseInfoDAO {

    private static final String GET_CUSTOMER_SQL =
        "SELECT * FROM Elec_Use_Info WHERE ElecMeterRegNo = ? and readdate between to_date(?,'yyyy-mm') and to_date(?,'yyyy-mm') order by readdate desc";
    
    private static final String CREATE_CUSTOMER_SQL =
        "INSERT INTO Elec_Use_Info (UseId,ElecMeterRegNo,NowReading,ElecMeterReading,ReadOn,XianLoss,BianLoss,TuiBu,SumElec,PriceUnit,ReadDate,ReMark,Memo) VALUES (UseId.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, to_date(?, 'yy-mm-dd'), ?, ?)";
    
    private static final String DELETE_CUSTOMER_SQL =
        "DELETE FROM Elec_Use_Info WHERE UseId = ?";

    private static final String UPDATE_CUSTOMER_SQL =
        "UPDATE Elec_Use_Info SET NowReading=?, ElecMeterReading=?, ReadOn=?, XianLoss=?, BianLoss=?, TuiBu=?,SumElec=?, PriceUnit=?, ReadDate=to_date(?, 'yy-mm-dd'), ReMark=?, Memo=? WHERE UseId = ?";

    private static final String SEARCH_CUSTOMER_SQL =
//        "SELECT * FROM Elec_Use_Info WHERE ";	
    	 "SELECT e1.UserId,e2.ElecMeterRegNo,e2.NowReading,e2.ElecMeterReading,e2.ReadOn," +
		 "e2.XianLoss,e2.BianLoss,e2.TuiBu,e2.SumElec,e2.PriceUnit,e2.ReadDate,e2.ReMark,e2.Memo " + 
		 "FROM ElecMeter_info e1,Elec_Use_Info e2 " +
		 "WHERE e1.ElecMeterRegNo=e2.ElecMeterRegNo and ";
	
	public boolean deleteElecUseInfo(int useId) throws Exception {
		
		boolean flag = false;
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
	    try {
	        connection = db.conn;
	        pStatement = connection.prepareStatement(DELETE_CUSTOMER_SQL);
	        pStatement.setInt(1, useId);
			int i = pStatement.executeUpdate();
			flag = i > 0 ? true : false;
			
	    	pStatement.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return flag;
	    
	}

	public ElecUseInfoTO getElecUseInfo(String elecMeterRegNo, boolean modifyOrDelete) throws Exception {
		
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    ResultSet rs = null;
	    ElecUseInfoTO elecUseInfo = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
		Date date = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
		Calendar calendar = new GregorianCalendar();
		calendar.setTime(date);
		String thisMonth = format.format(date);
		calendar.add(Calendar.MONTH, -1);
		String lastMonth = format.format(calendar.getTime());
	    
	    try {
	        connection = db.conn;
	        // Prepare a statement to insert a record
	       
	        /*
	        if(modifyOrDelete) {
	        	String sql = "SELECT * FROM Elec_Use_Info WHERE ElecMeterRegNo=? order by readdate desc";
		        pStatement = connection.prepareStatement(sql);
		        pStatement.setString(1,elecMeterRegNo);
	        } else {
		        pStatement = connection.prepareStatement(GET_CUSTOMER_SQL);
		        pStatement.setString(1,elecMeterRegNo);
		        pStatement.setString(2, lastMonth);
		        pStatement.setString(3, thisMonth);
	        }
	        */
	        
        	String sql = "SELECT * FROM Elec_Use_Info WHERE ElecMeterRegNo=? order by readdate desc";
	        pStatement = connection.prepareStatement(sql);
	        pStatement.setString(1,elecMeterRegNo);
        
	        
	        
	        rs = pStatement.executeQuery();
	        
	        
	       
	        
	        if (rs.next()) {
	        	elecUseInfo = new ElecUseInfoTO();
	        	elecUseInfo.setUseId(rs.getInt("UseId"));
	        	elecUseInfo.setElecMeterRegNo(rs.getString("ElecMeterRegNo"));
	        	elecUseInfo.setNowReading(rs.getDouble("NowReading"));
	        	elecUseInfo.setElecMeterReading(rs.getDouble("ElecMeterReading"));
	        	elecUseInfo.setReadOn(rs.getString("ReadOn"));
	        	elecUseInfo.setXianLoss(rs.getDouble("XianLoss"));
	        	elecUseInfo.setBianLoss(rs.getDouble("BianLoss"));
	        	elecUseInfo.setTuiBu(rs.getDouble("TuiBu"));
	        	elecUseInfo.setSumElec(rs.getDouble("SumElec"));
	        	elecUseInfo.setPriceUnit(rs.getDouble("PriceUnit"));
	        	elecUseInfo.setReadDate(rs.getDate("ReadDate").toString());
	        	elecUseInfo.setReMark(rs.getInt("ReMark"));
	        	elecUseInfo.setMemo(rs.getString("Memo"));
	        }
	        
	    	rs.close();
	    	pStatement.close();
	    } catch (SQLException e) {
	    	e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return elecUseInfo;
	    
	}
	
	public ElecUseInfoTO getElecUseInfoByDate(String elecMeterRegNo, String date) throws Exception {
		
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    ResultSet rs = null;
	    ElecUseInfoTO elecUseInfo = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();	    
	    
	    try {
	        connection = db.conn;
	        // Prepare a statement to insert a record
        	String sql = "SELECT * FROM Elec_Use_Info WHERE ElecMeterRegNo=?";
	        pStatement = connection.prepareStatement(sql);
	        pStatement.setString(1,elecMeterRegNo);
	        rs = pStatement.executeQuery();	        

	        while (rs.next()) {
	        	String[] arr = rs.getString("ReadDate").split("-");	        	
	        	String sDate = arr[0] + "-" + Integer.parseInt(arr[1]);
	        	
	        	if(sDate.equals(date)) {
		        	elecUseInfo = new ElecUseInfoTO();
		        	elecUseInfo.setUseId(rs.getInt("UseId"));
		        	elecUseInfo.setElecMeterRegNo(rs.getString("ElecMeterRegNo"));
		        	elecUseInfo.setNowReading(rs.getDouble("NowReading"));
		        	elecUseInfo.setElecMeterReading(rs.getDouble("ElecMeterReading"));
		        	elecUseInfo.setReadOn(rs.getString("ReadOn"));
		        	elecUseInfo.setXianLoss(rs.getDouble("XianLoss"));
		        	elecUseInfo.setBianLoss(rs.getDouble("BianLoss"));
		        	elecUseInfo.setTuiBu(rs.getDouble("TuiBu"));
		        	elecUseInfo.setSumElec(rs.getDouble("SumElec"));
		        	elecUseInfo.setPriceUnit(rs.getDouble("PriceUnit"));
		        	elecUseInfo.setReadDate(rs.getDate("ReadDate").toString());
		        	elecUseInfo.setReMark(rs.getInt("ReMark"));
		        	elecUseInfo.setMemo(rs.getString("Memo"));
		        	
		        	break;
	        	}
	        }
	        
	    	rs.close();
	    	pStatement.close();
	    } catch (SQLException e) {
	    	e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return elecUseInfo;
	    
	}
	
	public boolean getElecUseInfo(String elecMeterRegNo, String date) throws Exception {
		
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    ResultSet rs = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    String[] dataArray = date.split("-");
	    String sDate = dataArray[0] + "-" + dataArray[1];
	    
	    boolean flag = false;
	    
	    try {
	        connection = db.conn;
	        // Prepare a statement to insert a record
	        
        	String sql = "SELECT to_char(readdate,'yyyy-mm') as readdate FROM Elec_Use_Info WHERE ElecMeterRegNo=? order by useid desc";
	        pStatement = connection.prepareStatement(sql);
	        pStatement.setString(1,elecMeterRegNo);
	        rs = pStatement.executeQuery();
	       
	        while (rs.next()) {
	        	if(rs.getString("readdate").equals(sDate)) {
	        		flag = true;