/* $Id: AppKindDAO.java,v 1.6 2005/08/26 04:45:38 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.util.ArrayList;

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

/**
 * 申請種別テーブルに対するアクセスを行うクラス。
 * 
 * @author  ruchida(Argo 21, Corp.)
 * @version $Revision: 1.6 $
 * @since   Nautica Workflow 0.9
 */
public class AppKindDAO {
    
    private static final String APPKIND_ID      = "APPKIND_ID";
    private static final String APPKIND_NAME    = "APPKIND_NAME";
    private static final String ROLE_ID         = "ROLE_ID";
    private static final String APP_TABLE_NAME  = "APP_TABLE_NAME";
    private static final String APPROVE_ACCOUNT = "APPROVE_ACCOUNT";
    private static final String CONFIRM_ACCOUNT = "CONFIRM_ACCOUNT";    
    
    // リソースマネージャ
    private static ResourceManager locator = ResourceManager.getInstance();
    
    // 申請種別コードで承認ブロック数を取得するためのSQL
    private static final String SQL_SELECT_APPROVAL_ACCOUNT
        = "select APPROVE_ACCOUNT from APPKIND where APPKIND_ID = ?";
    
    // 申請種別コードで同報ブロック数を取得するためのSQL
    private static final String SQL_SELECT_CONFIRM_ACCOUNT
        = "select CONFIRM_ACCOUNT from APPKIND where APPKIND_ID = ?";

    // ロールで申請を取得するためのSQL
    private static final String SQL_SELECT_BY_ROLE 
        = "select * from APPKIND where ROLE_ID = ?";
   
    // 申請種別コードで申請を取得するためのSQL
    private static final String SQL_SELECT_BY_APPKIND_ID
        = "select * from APPKIND where APPKIND_ID = ?";
    
    /**
     * 申請種別コードで承認ブロック数を返す。
     * 
     * @param appKindID 申請種別コード
     * @return 承認ブロック数
     * @throws SQLException 任意のSQL例外
     */
    public int getApproveAccount(String appKindID) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_APPROVAL_ACCOUNT);
        try {            
            statement.setString(1, appKindID);
            ResultSet rs = statement.executeQuery();
            
            int approvalAccount = 0;
            while (rs.next()) {
                approvalAccount = rs.getInt(1);
            }
            return approvalAccount;
        } finally {
            locator.closeConnection(conn, statement);
        }        
    }
    
    /**
     * 申請種別コードで同報ブロック数を返す。
     * 
     * @param appKindID 申請種別コード
     * @return 同報ブロック数
     * @throws SQLException 任意のSQL例外
     */
    public int getConfirmAccount(String appKindID) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_CONFIRM_ACCOUNT);
        try {
            statement.setString(1, appKindID);
            ResultSet rs = statement.executeQuery();
            
            int confirmAccount = 0;
            while (rs.next()) {
                confirmAccount = rs.getInt(1);
            }
            return confirmAccount;
            
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
	 * 指定されたロールで申請可能な申請を返す。
     * 
	 * @param roleID ロールID
	 * @return 申請一覧
	 * @throws SQLException 任意のSQL例外
	 */
	public AppKind[] listAppKindByRole(String roleID) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_BY_ROLE);
        try {
            statement.setString(1, roleID);
            ResultSet rs = statement.executeQuery();
            ArrayList result = new ArrayList();
            while(rs.next()) {
                AppKind kind = createAppKindFromRecord(rs);
                if (kind!=null)
                    result.add(kind);
            }
            return (AppKind[]) result.toArray(new AppKind[result.size()]);
            
        } finally {
            locator.closeConnection(conn, statement);
        }        
    }
    
    /**
	 * 指定された申請種別コードで取得した申請を返す。
     * 
	 * @param appKindID 申請種別コード
	 * @return 申請種別
	 * @throws SQLException 任意のSQL例外
	 */
	public AppKind getAppKindFromID(String appKindID) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_BY_APPKIND_ID);
        AppKind appKind = null;
        try {
            statement.setString(1, appKindID);
            ResultSet rs = statement.executeQuery();
            while(rs.next()) {
                appKind=  createAppKindFromRecord(rs);
            }
            return appKind;
            
        } finally {
            locator.closeConnection(conn, statement);
        }        
    }
    
    /**
     * 申請種別を生成する。
     * 
     * @param rs リザルトセット
     * @return 申請種別
     * @throws SQLException 任意のSQL例外
     */
    private AppKind createAppKindFromRecord(ResultSet rs) throws SQLException {
        String appkindID = rs.getString(APPKIND_ID);
        String appkindName = rs.getString(APPKIND_NAME);
        String roleID = rs.getString(ROLE_ID);
        String appTableName = rs.getString(APP_TABLE_NAME);
        int approveAccount = rs.getInt(APPROVE_ACCOUNT);
        int confirmAccount = rs.getInt(CONFIRM_ACCOUNT);
        
        AppKind kind = new AppKind();
        kind.setAppkindID(appkindID);
        kind.setAppkindName(appkindName);
        kind.setRoleID(roleID);
        kind.setAppTableName(appTableName);
        kind.setApproveAccount(approveAccount);
        kind.setConfirmAccount(confirmAccount);
        
        return kind;
    }    
    
}
