/* $Id: AppTransactionDAO.java,v 1.2 2008/01/15 05:02:05 stokin 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.StateConstants;
import jp.co.argo21.nautica.workflow.soap.OrganizationManager;
import jp.co.argo21.nautica.workflow.soap.RoleManager;
import jp.co.argo21.nautica.workflow.soap.UserManager;
import jp.co.argo21.nautica.workflow.soap.message.Organization;
import jp.co.argo21.nautica.workflow.soap.message.Role;
import jp.co.argo21.nautica.workflow.soap.message.User;


/**
 * �\���g�����U�N�V�����e�[�u���ɑ΂���A�N�Z�X��s���N���X�B
 * 
 * @author  ruchida(Argo 21, Corp.)
 * @version $Revision: 1.2 $
 * @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";
    
    // ���\�[�X�}�l�[�W��
    private static ResourceManager locator = ResourceManager.getInstance();
    
    // ���ԊO�\���p�̃��R�[�h��ǉB��邽�߂�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 (?,?,?,?,?,?) ";
    
    // �\�����ԍ��Ń��R�[�h��擾���邽�߂�SQL
    private static final String SQL_SELECT_BY_APP_ID
        = "select * from APPTRANSACTION where APP_ID = ?";
    
    // �\�����ԍ��ƃ��[�UID�Ń��R�[�h��擾���邽�߂�SQL
    private static final String SQL_SELECT_BY_APPID_AND_USERID
        = "select * from APPTRANSACTION where APP_ID = ? and APP_USER_ID = ?";
    
    // �\�����ԍ��ƃ��[��ID�Ń��R�[�h��擾���邽�߂�SQL
    private static final String SQL_SELECT_BY_APPID_AND_ROLEID
        = "select * from APPTRANSACTION where APP_ID = ? and APP_ROLE_ID = ?";
    
    // �\�����ԍ��Ə��F�u���b�N�ԍ��Ń��R�[�h��擾���邽�߂�SQL
    private static final String SQL_SELECT_BY_APPID_AND_ACCOUNT_ID
        = "select * from APPTRANSACTION where APP_ID = ? and APPROVE_ACCOUNT_ID = ?";
    
    // ���[�UID�Ń��R�[�h��擾���邽�߂�SQL
    private static final String SQL_SELECT_BY_USER_ID
        = "select * from APPTRANSACTION where APP_USER_ID = ?";
    
    // ���[��ID�Ń��R�[�h��擾���邽�߂�SQL
    private static final String SQL_SELECT_BY_ROLE_ID
        = "select * from APPTRANSACTION where APP_ROLE_ID = ? AND APP_USER_ID = ''";
    
    // �\����e��͑҂��̃��R�[�h��擾���邽�߂�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 = '差し戻し'";
    
    // ���R�[�h��X�V���邽�߂�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 = ?";
    
    // �X�e�[�^�X��X�V���邽�߂�SQL
    private static final String SQL_UPDATE_STATUS
        = "update APPTRANSACTION set APP_STATUS = ?" +
                "where APP_ID = ? and APPROVE_ACCOUNT_ID = ?";
    
    // �\�����ԍ��Ń��R�[�h��폜���邽�߂�SQL
    private static final String SQL_DELETE
        = "delete from APPTRANSACTION where APP_ID = ?";
        
    /**
     * �w�肵���\���ԍ������R�[�h��폜����B
     * 
     * @param appID �\�����ԍ�
     * @throws SQLException �C�ӂ�SQL��O
     */
    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);
        }
    }
    
    /**
     * ���ԊO�J���\���p�̃��R�[�h��ǉB���B
     * 
     * @param appID �\�����ԍ�
     * @param appKindID �\����ʃR�[�h
     * @param userID ���[�UID
     * @throws Exception �C�ӂ̗�O
     */
    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, StateConstants.STATUS_TRANS_WAIT);
                    statement.setString(5, userID);
                    statement.setString(6, "");
                } else if (i == 1) {
                    statement.setString(4, StateConstants.STATUS_TRANS_NOT_REACH);
                    statement.setString(5, (String) approvers.get(0));
                    statement.setString(6, "");
                } else if (i == 2) {
                    statement.setString(4, StateConstants.STATUS_TRANS_NOT_REACH);
                    statement.setString(5, (String) approvers.get(1));
                    statement.setString(6, "");
                } else if (i == 3) {
                    statement.setString(4, StateConstants.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, StateConstants.STATUS_TRANS_CONFIRM);
                    statement.setString(5, (String) confirmers.get(0));
                    statement.setString(6, "");
                } else if (i == 5) {
                    statement.setString(4, StateConstants.STATUS_TRANS_CONFIRM);
                    statement.setString(5, (String) confirmers.get(1));
                    statement.setString(6, "");
                }
                statement.execute();
            }
        } finally {
            locator.closeConnection(conn, statement);
        }
    }
    
    /**
     * ���F�҃��[�UID�܂��̓��[��ID��Ԃ��B
     * 
     * @param userID ���[�UID
     * @return ���F�҃��[�UID�ꗗ
     * @throws Exception �C�ӂ̗�O
     */
    private List getApprovalID(String userID) throws Exception {
        UserManager umr = SingletonServices.getInstance().getUserManager();
        OrganizationManager omr = SingletonServices.getInstance().getOrganizationManager();
        RoleManager rmr = SingletonServices.getInstance().getRoleManager();
        User user = umr.getUser(userID);
        List<Organization> orgs = omr.getOrganizationByUser(user);
        
        // ������i�i�ے��j���[�UID��Z�b�g
        List approvers = new ArrayList();
        Role kachoRole = rmr.getRole(KACHO_ROLE);
        List<User> users = rmr.getUsersByRole(kachoRole);
        for (int i = 0; i < users.size(); i++) {
            List<Organization> kachoOrgs = omr.getOrganizationByUser(users.get(i));
            if (kachoOrgs.get(0).getId().equals(orgs.get(0).getId())) {
                approvers.add(users.get(i).getId());
                break;
            }
        }
        
        // �������[�UID��Z�b�g
        Role buchoRole = rmr.getRole(BUCHO_ROLE);
        List<User> buchoUsers = rmr.getUsersByRole(buchoRole);
        Organization higherOrg = omr.getHigherOrganization(orgs.get(0));
        for (int i = 0; i < buchoUsers.size(); i++) {
            List<User> higherUsers = omr.getAssignedUser(higherOrg);
            for (int j = 0; j < higherUsers.size(); j++) {
                if (buchoUsers.get(i).getId().equals(higherUsers.get(j).getId())) {
                    approvers.add(buchoUsers.get(i).getId());
                    break;
                }
            }
        }
        
        // �l������Z�b�g
        approvers.add(JINJI_ROLE);
        
        return approvers;
    }
    
    /**
     * ����҃��[�UID��Ԃ��B
     * 
     * @param userID ���[�UID
     * @return ����҃��[�UID�̈ꗗ
     * @throws Exception �C�ӂ̗�O
     */
    private List getConfirmID (String userID) throws Exception {
        List confirmers = new ArrayList();
        RoleManager rmr = SingletonServices.getInstance().getRoleManager();
                
        // ����҃��[�UID��Z�b�g
        Role jinjiKachoRole = rmr.getRole(JINJI_KACHO_ROLE);
        List<User> jinjiKacho = rmr.getUsersByRole(jinjiKachoRole);
        confirmers.add(jinjiKacho.get(0).getId());
        Role jinjiBuchoRole = rmr.getRole(JINJI_BUCHO_ROLE);
        List<User> jinjiBucho = rmr.getUsersByRole(jinjiBuchoRole);
        confirmers.add(jinjiBucho.get(0).getId());
        
        return confirmers;
    }
    
    /**
     * �\�����ԍ��Ŏ擾�������R�[�h��Ԃ��B
     * 
     * @param appID �\�����ԍ�
     * @return �\���g�����U�N�V�����I�u�W�F�N�g�ꗗ
     * @throws Exception �C�ӂ̗�O
     */
    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);
        }
    }
    
    /**
     * �w�肳�ꂽ���[�U���t���[�ɎQ�B��Ă��郌�R�[�h��Ԃ��B
     * 
     * @param userID ���[�UID
     * @return �\���g�����U�N�V�����I�u�W�F�N�g�ꗗ
     * @throws Exception �C�ӂ̗�O
     */
    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);
        }
    }
    
    /**
     * �w�肳�ꂽ���[�����t���[�ɎQ�B��Ă��郌�R�[�h��Ԃ��B
     * 
     * @param roleID ���[��ID
     * @return �\���g�����U�N�V�����I�u�W�F�N�g�ꗗ
     * @throws Exception �C�ӂ̗�O
     */
    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);
        }
    }
    
    /**
     * �\�����ԍ��ƃ��[�UID�Ŏ擾�������R�[�h��Ԃ��B
     * 
     * @param appID �\�����ԍ�
     * @param userID ���[�UID
     * @return �\���g�����U�N�V�����I�u�W�F�N�g
     * @throws Exception �C�ӂ̗�O
     */
    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�Ŏ擾�������R�[�h��Ԃ��B
     * 
     * @param appID �\�����ԍ�
     * @param roleID ���[��ID
     * @return �\���g�����U�N�V�����I�u�W�F�N�g
     * @throws SQLException �C�ӂ�SQL��O
     */
    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);
        }
    }
    
    /**
     * �\���g�����U�N�V�����𐶐�����B
     * 
     * @param rs ���U���g�Z�b�g
     * @return �\���g�����U�N�V����
     * @throws SQLException �C�ӂ�SQL��O
     */
    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;
    }
    
    /**
     * �\����e��͑҂��̐\���g�����U�N�V������Ԃ��B
     * 
     * @param appid �\�����ԍ�
     * @param userID ���[�UID
     * @return �\���g�����U�N�V����
     * @throws Exception �C�ӂ̗�O
     */
    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);
        }
    }

    /**
     * �\���g�����U�N�V������X�V����B
     * 
     * @param appTrans �\���g�����U�N�V����
     * @throws SQLException �C�ӂ�SQL��O
     */
    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);
        }
    }
    
    /**
     * �X�e�[�^�X��X�V����B
     * 
     * @param appTrans �\���g�����U�N�V����
     * @throws SQLException �C�ӂ�SQL��O
     */
    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);
        }
    }

	/**
	 * �\�����ԍ��Ə��F�u���b�N�ԍ��Ŏ擾�������R�[�h��Ԃ��B
     * 
	 * @param appID �\�����ԍ�
	 * @param blockID ���F�u���b�N�ԍ�
	 * @return �\���g�����U�N�V�����I�u�W�F�N�g
     * @throws Exception �C�ӂ̗�O
	 */
	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);
        }
	}
	
}
