/* $Id: ApplicationDAO.java,v 1.10 2005/08/26 04:45:40 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.Timestamp;

import jp.co.argo21.nautica.workflow.sample.model.AppKind;
import jp.co.argo21.nautica.workflow.sample.model.AppTransaction;
import jp.co.argo21.nautica.workflow.sample.model.Application;
import jp.co.argo21.nautica.workflow.sample.util.ResourceManager;
import jp.co.argo21.nautica.workflow.sample.util.SingletonServices;
import jp.co.argo21.nautica.workflow.security.simple.UserManagerRemote;

/**
 * 申請テーブルに対するアクセスを行うクラス。
 * すべての個別申請テーブルのスーパークラス。
 * 
 * @author  ruchida(Argo 21, Corp.)
 * @version $Revision: 1.10 $
 * @since   Nautica Workflow 0.9
 */
public class ApplicationDAO {

    private static final String APPKIND_ID = "APPKIND_ID";
    private static final String USER_ID = "USER_ID";
    private static final String APP_DATE = "APP_DATE";

    // リソースマネージャ
    private static ResourceManager locator = ResourceManager.getInstance();
    
    // 新しい申請書番号を追加するためのSQL
    private static final String SQL_INSERT
        = "insert into APPLICATION (APP_ID, APPKIND_ID, USER_ID) values (?,?,?) ";
    
    // 申請書番号でレコードを取得するためのSQL
    private static final String SQL_SELECT_BY_APP_ID
        = "select * from APPLICATION where APP_ID = ?";
    
    // 申請書番号でレコードを削除するためのSQL
    private static final String SQL_DELETE
        = "delete from APPLICATION where APP_ID = ?";
    
    // レコードを更新するためのSQL
    private static final String SQL_UPDATE
        = "update APPLICATION set APP_DATE = ? where APP_ID = ?";
    
    /**
     * 申請書番号で取得した申請を返す。
     * 
     * @param appID 申請書番号
     * @return 申請
     * @throws Exception 任意の例外
     */
	public Application getApplicationByID( String appID ) throws Exception {        
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_BY_APP_ID);
        try {
            statement.setString(1, appID);
            ResultSet rs = statement.executeQuery();
            String appKindID = "";
            String userID = "";
            Timestamp appDate = null;
            while (rs.next()) {
                appKindID = rs.getString(APPKIND_ID);
                userID = rs.getString(USER_ID);
                appDate = rs.getTimestamp(APP_DATE);
            }
            
            ApplicationDAO appKindDao = DAOFactory.getApplicationDAOFromAppKindID(appKindID);
            Application app = appKindDao.getApplicationByID(appID);
            
            AppKind appKind = DAOFactory.getAppKindDAO().getAppKindFromID(appKindID);
            app.setKind(appKind);
            app.setUserid(userID);
            app.setDate(appDate);
            
            UserManagerRemote umr = SingletonServices.getInstance().getUserManager();
            app.setUser(umr.getUser(userID));
            
            return app;
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * レコードを更新する。
     * 
     * @param app 申請
     * @throws SQLException 任意のSQL例外
     */
    public void updateApplication(Application app) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_UPDATE);
        try {
            statement.setTimestamp(1, app.getDate());
            statement.setString(2, app.getId());
            statement.executeUpdate();
        } finally {
            locator.closeConnection(conn, statement);
        }
    }

    /**
     * 申請書番号、申請種別コード、ユーザIDを追加する。
     * 
     * @param appID     申請書番号
     * @param appKindID 申請種別コード
     * @param userID    ユーザID
     * @throws SQLException 任意のSQL例外
     */
    public void insert (String appID, String appKindID, String userID) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_INSERT);
        try {
            statement.setString(1, appID);
            statement.setString(2, appKindID);
            statement.setString(3, userID);
            statement.execute();
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * 指定した申請番号をもつレコードを削除する。
     * 
     * @param appID 申請書番号
     * @throws Exception 任意の例外
     */
    public void remove (String appID) throws Exception {        
        AppTransactionDAO appTransDao = DAOFactory.getAppTransactionDAO();
        AppTransaction appTrans = appTransDao.getAppTransactionByAppIDBlockID(appID, 0);
        String appKindID = appTrans.getAppKindID();
        
        ApplicationDAO appDao = DAOFactory.getApplicationDAOFromAppKindID(appKindID);
        appDao.remove(appID);
        
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_DELETE);
        try {
            statement.setString(1, appID);
            statement.execute();
        } finally {
            locator.closeConnection(conn, statement);
        }
    }

}