/* $Id: AppTransactionDAO.java,v 1.16 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.Timestamp;
import java.util.ArrayList;
import java.util.List;

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.util.ResourceManager;
import jp.co.argo21.nautica.workflow.sample.util.SingletonServices;
import jp.co.argo21.nautica.workflow.sample.util.StatusConstants;
import jp.co.argo21.nautica.workflow.security.Organization;
import jp.co.argo21.nautica.workflow.security.Role;
import jp.co.argo21.nautica.workflow.security.User;
import jp.co.argo21.nautica.workflow.security.simple.OrganizationManagerRemote;
import jp.co.argo21.nautica.workflow.security.simple.RoleManagerRemote;
import jp.co.argo21.nautica.workflow.security.simple.UserManagerRemote;

/**
 * 申請トランザクションテーブルに対するアクセスを行うクラス。
 * 
 * @author  ruchida(Argo 21, Corp.)
 * @version $Revision: 1.16 $
 * @since   Nautica Workflow 0.9
 */
public class AppTransactionDAO {
    
    public static final String APP_ID             = "APP_ID";
    public static final String APPROVE_ACCOUNT_ID = "APPROVE_ACCOUNT_ID";
    public static final String APPKIND_ID         = "APPKIND_ID";
    public static final String APP_STATUS         = "APP_STATUS";
    public static final String APP_USER_ID        = "APP_USER_ID";
    public static final String APP_ROLE_ID        = "APP_ROLE_ID";
    public static final String COMMENT            = "COMMENT";
    public static final String DATE               = "DATE";
    
    // ロール
    private static final String KACHO_ROLE        = "KACHO_ROLE";
    private static final String BUCHO_ROLE        = "BUCHO_ROLE";
    public static final String JINJI_KACHO_ROLE  = "JINJI_KACHO_ROLE";
    public static final String JINJI_BUCHO_ROLE  = "JINJI_BUCHO_ROLE";
    public static final String JINJI_ROLE         = "JINJI_ROLE";
    
    // リソースマネージャ
    private static ResourceManager locator = ResourceManager.getInstance();
    
    // 時間外申請用のレコードを追加するためのSQL
    private static final String SQL_INSERT_FOR_OVERTIME
        = "insert into APPTRANSACTION (" +
                "APP_ID, APPROVE_ACCOUNT_ID, APPKIND_ID, APP_STATUS, " +
                "APP_USER_ID, APP_ROLE_ID) values (?,?,?,?,?,?) ";
    
    // 申請書番号でレコードを取得するためのSQL
    private static final String SQL_SELECT_BY_APP_ID
        = "select * from APPTRANSACTION where APP_ID = ?";
    
    // 申請書番号とユーザIDでレコードを取得するためのSQL
    private static final String SQL_SELECT_BY_APPID_AND_USERID
        = "select * from APPTRANSACTION where APP_ID = ? and APP_USER_ID = ?";
    
    // 申請書番号とロールIDでレコードを取得するためのSQL
    private static final String SQL_SELECT_BY_APPID_AND_ROLEID
        = "select * from APPTRANSACTION where APP_ID = ? and APP_ROLE_ID = ?";
    
    // 申請書番号と承認ブロック番号でレコードを取得するためのSQL
    private static final String SQL_SELECT_BY_APPID_AND_ACCOUNT_ID
        = "select * from APPTRANSACTION where APP_ID = ? and APPROVE_ACCOUNT_ID = ?";
    
    // ユーザIDでレコードを取得するためのSQL
    private static final String SQL_SELECT_BY_USER_ID
        = "select * from APPTRANSACTION where APP_USER_ID = ?";
    
    // ロールIDでレコードを取得するためのSQL
    private static final String SQL_SELECT_BY_ROLE_ID
        = "select * from APPTRANSACTION where APP_ROLE_ID = ? AND APP_USER_ID = ''";
    
    // 申請内容入力待ちのレコードを取得するためのSQL
    private static final String SQL_SELECT_FOR_CONTINUE
        = "select * from APPTRANSACTION A  " +
                "where A.APP_ID = ? " +
                "and A.APPROVE_ACCOUNT_ID = 0 " +
                "and A.APP_USER_ID = ? " +
                "and A.APP_STATUS = '待ち' " +
                "union " +
                "select B.* from APPTRANSACTION A join APPTRANSACTION B on A.APP_ID = B.APP_ID " +
                "where A.APP_ID = ? " +
                "and A.APPROVE_ACCOUNT_ID = 0 " +
                "and A.APP_USER_ID = ? " +
                "and B.APP_ID = ? " +
                "and B.APP_STATUS = '差し戻し'";
    
    // レコードを更新するためのSQL
    private static final String SQL_UPDATE
        = "update APPTRANSACTION set APP_STATUS = ?, APP_USER_ID = ?, " +
                "APP_ROLE_ID = ?, COMMENT = ?, DATE = ?" +
                "where APP_ID = ? and APPROVE_ACCOUNT_ID = ?";
    
    // ステータスを更新するためのSQL
    private static final String SQL_UPDATE_STATUS
        = "update APPTRANSACTION set APP_STATUS = ?" +
                "where APP_ID = ? and APPROVE_ACCOUNT_ID = ?";
    
    // 申請書番号でレコードを削除するためのSQL
    private static final String SQL_DELETE
        = "delete from APPTRANSACTION where APP_ID = ?";
        
    /**
     * 指定した申請番号をもつレコードを削除する。
     * 
     * @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);
        }
    }
    
    /**
     * 時間外労働申請用のレコードを追加する。
     * 
     * @param appID 申請書番号
     * @param appKindID 申請種別コード
     * @param userID ユーザID
     * @throws Exception 任意の例外
     */
    public void insertForOverTime(String appID, String appKindID, String userID) throws Exception {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_INSERT_FOR_OVERTIME);
        try {
            AppKindDAO appKindDao = DAOFactory.getAppKindDAO();
            int approveAccount = appKindDao.getApproveAccount(appKindID);
            int confirmAccount = appKindDao.getConfirmAccount(appKindID);
            
            List approvers = new ArrayList();
            approvers = getApprovalID(userID);
            for (int i=0; i < approveAccount; i++) {
                statement.setString(1, appID);
                statement.setInt(2, i);
                statement.setString(3, appKindID);
                
                if (i == 0) {
                    statement.setString(4, StatusConstants.STATUS_TRANS_WAIT);
                    statement.setString(5, userID);
                    statement.setString(6, "");
                } else if (i == 1) {
                    statement.setString(4, StatusConstants.STATUS_TRANS_NOT_REACH);
                    statement.setString(5, (String) approvers.get(0));
                    statement.setString(6, "");
                } else if (i == 2) {
                    statement.setString(4, StatusConstants.STATUS_TRANS_NOT_REACH);
                    statement.setString(5, (String) approvers.get(1));
                    statement.setString(6, "");
                } else if (i == 3) {
                    statement.setString(4, StatusConstants.STATUS_TRANS_NOT_REACH);
                    statement.setString(5, "");
                    statement.setString(6, (String) approvers.get(2));
                }
                statement.execute();
            }
            
            List confirmers = new ArrayList();
            confirmers = getConfirmID(userID);
            for (int i = approveAccount; i < approveAccount+confirmAccount; i++) {
                statement.setString(1, appID);
                statement.setInt(2, i);
                statement.setString(3, appKindID);
                
                if (i == 4) {
                    statement.setString(4, StatusConstants.STATUS_TRANS_CONFIRM);
                    statement.setString(5, (String) confirmers.get(0));
                    statement.setString(6, "");
                } else if (i == 5) {
                    statement.setString(4, StatusConstants.STATUS_TRANS_CONFIRM);
                    statement.setString(5, (String) confirmers.get(1));
                    statement.setString(6, "");
                }
                statement.execute();
            }
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * 承認者ユーザIDまたはロールIDを返す。
     * 
     * @param userID ユーザID
     * @return 承認者ユーザID一覧
     * @throws Exception 任意の例外
     */
    private List getApprovalID(String userID) throws Exception {
        UserManagerRemote umr = SingletonServices.getInstance().getUserManager();
        OrganizationManagerRemote omr = SingletonServices.getInstance().getOrganizationManager();
        RoleManagerRemote rmr = SingletonServices.getInstance().getRoleManager();
        User user = umr.getUser(userID);
        Organization[] orgs = omr.getOrganizationByUser(user);
        
        // 直属上司（課長）ユーザIDをセット
        List approvers = new ArrayList();
        Role kachoRole = rmr.getRole(KACHO_ROLE);
        User[] users = rmr.getUsersByRole(kachoRole);
        for (int i = 0; i < users.length; i++) {
            Organization[] kachoOrgs = omr.getOrganizationByUser(users[i]);
            if (kachoOrgs[0].getID().equals(orgs[0].getID())) {
                approvers.add(users[i].getID());
                break;
            }
        }
        
        // 部長ユーザIDをセット
        Role buchoRole = rmr.getRole(BUCHO_ROLE);
        User[] buchoUsers = rmr.getUsersByRole(buchoRole);
        Organization higherOrg = omr.getHigherOrganization(orgs[0]);
        for (int i = 0; i < buchoUsers.length; i++) {
            User[] higherUsers = omr.getAssignedUser(higherOrg);
            for (int j = 0; j < higherUsers.length; j++) {
                if (buchoUsers[i].getID().equals(higherUsers[j].getID())) {
                    approvers.add(buchoUsers[i].getID());
                    break;
                }
            }
        }
        
        // 人事部をセット
        approvers.add(JINJI_ROLE);
        
        return approvers;
    }
    
    /**
     * 同報者ユーザIDを返す。
     * 
     * @param userID ユーザID
     * @return 同報者ユーザIDの一覧
     * @throws Exception 任意の例外
     */
    private List getConfirmID (String userID) throws Exception {
        List confirmers = new ArrayList();
        RoleManagerRemote rmr = SingletonServices.getInstance().getRoleManager();
                
        // 同報者ユーザIDをセット
        Role jinjiKachoRole = rmr.getRole(JINJI_KACHO_ROLE);
        User[] jinjiKacho = rmr.getUsersByRole(jinjiKachoRole);
        confirmers.add(jinjiKacho[0].getID());
        Role jinjiBuchoRole = rmr.getRole(JINJI_BUCHO_ROLE);
        User[] jinjiBucho = rmr.getUsersByRole(jinjiBuchoRole);
        confirmers.add(jinjiBucho[0].getID());
        
        return confirmers;
    }
    
    /**
     * 申請書番号で取得したレコードを返す。
     * 
     * @param appID 申請書番号
     * @return 申請トランザクションオブジェクト一覧
     * @throws Exception 任意の例外
     */
    public AppTransaction[] getAppTransactionsByAppID (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();
            
            ArrayList result = new ArrayList();
            while(rs.next()) {
                AppTransaction kind = createAppTransFromRecord(rs);
                if (kind!=null)
                    result.add(kind);
            }
            return (AppTransaction[]) result.toArray(new AppTransaction[result.size()]);
            
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * 指定されたユーザがフローに参加しているレコードを返す。
     * 
     * @param userID ユーザID
     * @return 申請トランザクションオブジェクト一覧
     * @throws Exception 任意の例外
     */
    public AppTransaction[] getAppTransactionsByUserID (String userID) throws Exception {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_BY_USER_ID);
        try {
            statement.setString(1, userID);
            ResultSet rs = statement.executeQuery();
            
            ArrayList result = new ArrayList();
            while(rs.next()) {
                AppTransaction kind = createAppTransFromRecord(rs);
                if (kind!=null)
                    result.add(kind);
            }
            return (AppTransaction[]) result.toArray(new AppTransaction[result.size()]);
            
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * 指定されたロールがフローに参加しているレコードを返す。
     * 
     * @param roleID ロールID
     * @return 申請トランザクションオブジェクト一覧
     * @throws Exception 任意の例外
     */
    public AppTransaction[] getAppTransactionsByRoleID (String roleID) throws Exception {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_BY_ROLE_ID);
        try {
            statement.setString(1, roleID);
            ResultSet rs = statement.executeQuery();
            
            ArrayList result = new ArrayList();
            while(rs.next()) {
                AppTransaction kind = createAppTransFromRecord(rs);
                if (kind!=null)
                    result.add(kind);
            }
            return (AppTransaction[]) result.toArray(new AppTransaction[result.size()]);
            
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * 申請書番号とユーザIDで取得したレコードを返す。
     * 
     * @param appID 申請書番号
     * @param userID ユーザID
     * @return 申請トランザクションオブジェクト
     * @throws Exception 任意の例外
     */
    public AppTransaction getAppTransactionByAppIDUserID (String appID, String userID) throws Exception {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_BY_APPID_AND_USERID);
        try {
            statement.setString(1, appID);
            statement.setString(2, userID);
            ResultSet rs = statement.executeQuery();
            AppTransaction appt = null;
            while (rs.next()) {
                appt = createAppTransFromRecord(rs);
            }
            return appt;
            
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * 申請書番号とロールIDで取得したレコードを返す。
     * 
     * @param appID 申請書番号
     * @param roleID ロールID
     * @return 申請トランザクションオブジェクト
     * @throws SQLException 任意のSQL例外
     */
    public AppTransaction getAppTransactionByAppIDRoleID (String appID, String roleID) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_BY_APPID_AND_ROLEID);
        try {
            statement.setString(1, appID);
            statement.setString(2, roleID);
            ResultSet rs = statement.executeQuery();
            AppTransaction appt = null;
            while (rs.next()) {
                appt = createAppTransFromRecord(rs);
            }
            return appt;
            
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * 申請トランザクションを生成する。
     * 
     * @param rs リザルトセット
     * @return 申請トランザクション
     * @throws SQLException 任意のSQL例外
     */
    private AppTransaction createAppTransFromRecord(ResultSet rs) throws SQLException {
        String appID = rs.getString(APP_ID);
        int approveAccountID = rs.getInt(APPROVE_ACCOUNT_ID);
        String appKindID = rs.getString(APPKIND_ID);
        String appStatus = rs.getString(APP_STATUS);
        String appUserID = rs.getString(APP_USER_ID);
        String appRoleID = rs.getString(APP_ROLE_ID);
        String comment = rs.getString(COMMENT);
        Timestamp date = rs.getTimestamp(DATE);
        
        AppTransaction appt = new AppTransaction();
        appt.setAppID(appID);
        appt.setApproveAccountID(approveAccountID);
        appt.setAppKindID(appKindID);
        appt.setAppUserID(appUserID);
        appt.setAppRoleID(appRoleID);
        appt.setAppStatus(appStatus);
        appt.setComment(comment);
        appt.setDate(date);
        
        AppKind appKindFromID = DAOFactory.getAppKindDAO().getAppKindFromID(appKindID);
        appt.setAppkind(appKindFromID);
        
        return appt;
    }
    
    /**
     * 申請内容入力待ちの申請トランザクションを返す。
     * 
     * @param appid 申請書番号
     * @param userID ユーザID
     * @return 申請トランザクション
     * @throws Exception 任意の例外
     */
    public AppTransaction getAppTransactionForContinue(String appID, String userID) throws Exception {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_FOR_CONTINUE);
        try {
            statement.setString(1, appID);
            statement.setString(2, userID);
            statement.setString(3, appID);
            statement.setString(4, userID);
            statement.setString(5, appID);
            ResultSet rs = statement.executeQuery();
            
            AppTransaction appTrans = null;
            while (rs.next()) {
                appTrans = createAppTransFromRecord(rs);
            }            
            return appTrans;
        } finally {
            locator.closeConnection(conn, statement);
        }
    }

    /**
     * 申請トランザクションを更新する。
     * 
     * @param appTrans 申請トランザクション
     * @throws SQLException 任意のSQL例外
     */
    public void updateAppTransaction(AppTransaction appTrans) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_UPDATE);
        try {
            statement.setString(1, appTrans.getAppStatus());
            statement.setString(2, appTrans.getAppUserID());
            statement.setString(3, appTrans.getAppRoleID());
            statement.setString(4, appTrans.getComment());
            statement.setTimestamp(5, appTrans.getDate());
            statement.setString(6, appTrans.getAppID());
            statement.setInt(7, appTrans.getApproveAccountID());
            statement.executeUpdate();
            
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * ステータスを更新する。
     * 
     * @param appTrans 申請トランザクション
     * @throws SQLException 任意のSQL例外
     */
    public void updateStatus(AppTransaction appTrans) throws SQLException {
        Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_UPDATE_STATUS);
        try {
            statement.setString(1, appTrans.getAppStatus());
            statement.setString(2, appTrans.getAppID());
            statement.setInt(3, appTrans.getApproveAccountID());
            statement.executeUpdate();
            
        } finally {
            locator.closeConnection(conn, statement);
        }
    }

	/**
	 * 申請書番号と承認ブロック番号で取得したレコードを返す。
     * 
	 * @param appID 申請書番号
	 * @param blockID 承認ブロック番号
	 * @return 申請トランザクションオブジェクト
     * @throws Exception 任意の例外
	 */
	public AppTransaction getAppTransactionByAppIDBlockID(String appID, int blockID) 
        throws Exception
    {
	    Connection conn = locator.getConnection();
        PreparedStatement statement = conn.prepareStatement(SQL_SELECT_BY_APPID_AND_ACCOUNT_ID);
        try {
            statement.setString(1, appID);
            statement.setInt(2, blockID);
            ResultSet rs = statement.executeQuery();
            AppTransaction appTras= null;
            while(rs.next()) {
                appTras = createAppTransFromRecord(rs);
            }
            return appTras;
        } finally {
            locator.closeConnection(conn, statement);
        }
	}
	
}
