/* $Id: OvertimeAppDAO.java,v 1.9 2005/08/26 04:45:39 ysahara Exp $
*
* Copyright (c)ARGO 21, Corporation. 2005.  All rights reserved.
 * 
 * This file is part of Nautica Workflow.
 * 
 *  Nautica Workflow is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 * 
 *  Nautica Workflow is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 * 
 *  You should have received a copy of the GNU General Public License
 *  along with Nautica Workflow; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
 *  
*/
package jp.co.argo21.nautica.workflow.sample.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.Calendar;

import jp.co.argo21.nautica.workflow.sample.model.Application;
import jp.co.argo21.nautica.workflow.sample.model.OvertimeApp;
import jp.co.argo21.nautica.workflow.sample.util.ResourceManager;

/**
 * 時間外労働申請テーブルに対するアクセスを行うクラス。
 * 
 * @author  ruchida(Argo 21, Corp.)
 * @version $Revision: 1.9 $
 * @since   Nautica Workflow 0.9
 */
public class OvertimeAppDAO extends ApplicationDAO {
    
    private static final String WORKDAY = "WORKDAY";
    private static final String START_TIME = "START_TIME";
    private static final String END_TIME = "END_TIME";
    private static final String OVERTIME_HOURS = "OVERTIME_HOURS";
    private static final String REASON = "REASON";
    private static final String COMP_DAY = "COMP_DAY";
    private static final String COMP_DAY_DATE = "COMP_DAY_DATE";
    private static final String REMARKS = "REMARKS";
    
    // リソースマネージャ
    private static ResourceManager locator = ResourceManager.getInstance();
    
    // 新しい申請書を追加するためのSQL
    private static final String SQL_INSERT_APPNO
        = "insert into OVERTIMEAPP (APP_ID) values (?) ";
    
    // 申請書番号で申請書を取得するためのSQL
    private static final String SQL_SELECT_BY_ID
        = "select * from OVERTIMEAPP where APP_ID = ?";
    
    // 申請書番号でレコードを削除するためのSQL
    private static final String SQL_DELETE
        = "delete from OVERTIMEAPP where APP_ID = ?";
    
    // レコードを更新するためのSQL
    private static final String SQL_UPDATE
        = "update OVERTIMEAPP SET WORKDAY = ?, START_TIME = ?, END_TIME = ?, OVERTIME_HOURS = ?, " +
                "COMP_DAY = ?, COMP_DAY_DATE = ?, REMARKS = ?, REASON = ? " +
                "where APP_ID = ?";    
     
    /**
     * レコードを更新する。
     * 
     * @param app 申請
     * @throws SQLException 任意のSQL例外
     */
    public void updateApplication(Application app) throws SQLException {
        OvertimeApp overtimeApp = (OvertimeApp) app;
        
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_UPDATE);
        try {
            statement.setTimestamp(1, overtimeApp.getWorkday());
            statement.setTime(2, overtimeApp.getStartTime());
            statement.setTime(3, overtimeApp.getEndTime());
            statement.setFloat(4, overtimeApp.getOvertimeHours());
            if (overtimeApp.isComp()) {
                statement.setInt(5, 0);
                statement.setTimestamp(6, overtimeApp.getCompDay());
            } else {
                statement.setInt(5, 1);
                statement.setTimestamp(6, null);
            }
            statement.setString(7, overtimeApp.getRemark());
            statement.setString(8, overtimeApp.getReason());
            statement.setString(9, overtimeApp.getId());
            
            statement.executeUpdate();
            
        } finally {
            locator.closeConnection(conn, statement);
        }        
    }
    
    /**
     * 申請書番号の空レコードを追加する
     * 
     * @param appID 申請書番号
     * @throws SQLException 任意のSQL例外
     */
    public void insertAppNo(String appID) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_INSERT_APPNO);
        try {
            statement.setString(1, appID);
            statement.execute();
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * 申請書番号で申請書を生成する。
     * 
     * @param appID 申請書番号
     * @throws SQLException 任意のSQL例外
     */
    public Application getApplicationByID(String appID) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_BY_ID);
        OvertimeApp overtimeApp = new OvertimeApp();
        try {
            statement.setString(1, appID);
            ResultSet rs = statement.executeQuery();
            
            while (rs.next()) {
                overtimeApp.setId(appID);
                overtimeApp.setWorkday(rs.getTimestamp(WORKDAY));
                
                Calendar calendar = Calendar.getInstance();
                Time startTime = rs.getTime(START_TIME);
                if (startTime != null) {
                    calendar.setTime(startTime);
                    overtimeApp.setStartHour(calendar.get(Calendar.HOUR_OF_DAY));
                    overtimeApp.setStartMin(calendar.get(Calendar.MINUTE));
                }
                
                Time endTime = rs.getTime(END_TIME);
                if (endTime != null) {
                    calendar.setTime(endTime);
                    overtimeApp.setEndHour(calendar.get(Calendar.HOUR_OF_DAY));
                    overtimeApp.setEndMin(calendar.get(Calendar.MINUTE));
                }
                
                overtimeApp.setOvertimeHours(rs.getFloat(OVERTIME_HOURS));
                if (rs.getInt(COMP_DAY) == 0) {
                    overtimeApp.setComp(true);
                } else {
                    overtimeApp.setComp(false);
                }                  
                overtimeApp.setCompDay(rs.getTimestamp(COMP_DAY_DATE));
                overtimeApp.setReason(rs.getString(REASON));
                overtimeApp.setRemark(rs.getString(REMARKS));
            }            
            return overtimeApp;
            
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * 指定した申請番号をもつレコードを削除する。
     * 
     * @param appID 申請書番号
     * @throws SQLException 任意のSQL例外
     */
    public void remove(String appID) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_DELETE);
        try {
            statement.setString(1, appID);
            statement.execute();
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
}
