package kr.co.udapsoft.ebid.buyer.common.lib.biz;

import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.util.ValueObject;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

import kr.co.hsnc.common.base.WAFLogger;

public class SearchSpotDeptBiz extends Biz {

	public SearchSpotDeptBiz(HandlerStorage storage) {
		super(storage);
	}
 
    public void doBiz() throws Exception {
    	
    	if(storage.compareEvent("")){
    		RowSet getDivisionRowSet = getDivisionRowSet();
    		storage.addRowSet("DivisionRowSet", getDivisionRowSet);
    	} else if (storage.compareEvent("GET_DEPT_LIST")) {
    		params.set("sheet1", getSpotDept2(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
    	} else{
    		params.set("sheet1", getSpotDept(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
    	}
    }

    /**
     * 현장코드 검색을 처리한다.(전체)
     * @param params
     * @return <code>RowSet</code>
     */
    public RowSet getSpotDept(ValueObject Params) {

        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();

        try {

			ValueObject user = storage.getUser();

			String dept_cd    		= user.get("DEPT_CD");
			String comon_dcr    	= user.get("COMON_DCR");
			String dept_cnt    		= user.get("DEPT_CNT");
			String deadflg_value			= params.get("deadflg_value");
			String mudept_yn		= user.get("MUDEPT_YN");
			
			if(!params.get("prp_key").equals("")){
				
				comon_dcr = "1";
				
			}
			
			//본사이고 계정타입이 관리자거나 경영지원(01)일때
			if( comon_dcr.equals("1") ) {
				sql.append(" SELECT  A.DEPT_CD      																		\n");
				sql.append("         ,A.DEPT_NM     																		\n");
				sql.append("         ,A.AC_UNIT_CD                                 										\n");
				sql.append("         ,F_GET_CODE_NAME('101', A.AC_UNIT_CD) AS AC_UNIT_NM                     				\n");
				sql.append("         ,A.STD_CURRENCY_CD                    												\n");
				sql.append("         ,F_GET_CURRENCY_DIGIT(A.STD_CURRENCY_CD) AS STD_DIGIT								\n");
				sql.append(" FROM	ACZ10100V A																			\n");
				sql.append(" WHERE	1 = 1   																			\n");
			//	sql.append("   AND A.AC_UNIT_CD != '50'                           										\n");
				sql.append(" AND	A.DEPT_CD != '0000'																	\n");
				
				if( !params.get("ac_unit_cd").equals("") ) {
					sql.append(" AND      A.AC_UNIT_CD = '" + params.get("ac_unit_cd") + "'          \n");
				}
				
			//현장일때
			} else { 
				if( dept_cnt.equals("1") ){
					if( mudept_yn.equals("Y") ){
						sql.append(" SELECT  A.DEPT_CD      																	\n");
						sql.append("         ,A.DEPT_NM     																	\n");
						sql.append("         ,A.AC_UNIT_CD                                 										\n");
						sql.append("         ,F_GET_CODE_NAME('101', A.AC_UNIT_CD) AS AC_UNIT_NM                     			\n");
						sql.append("         ,A.STD_CURRENCY_CD                    												\n");
						sql.append("         ,F_GET_CURRENCY_DIGIT(A.STD_CURRENCY_CD) AS STD_DIGIT								\n");
						sql.append(" FROM	ACZ10100V A, PUB0130T_V B															\n");
						sql.append(" WHERE	SUBSTR(A.DEPT_CD,1,4) = SUBSTR(B.DEPT_CD,1,4)						\n");
						sql.append(" AND	B.ACCT_ID = "+user.get("ACCT_ID")+"													\n");
						sql.append("   AND A.AC_UNIT_CD != '50'                           										\n");
						sql.append(" AND	A.DEPT_CD != '0000'																	\n");
					}else {
						sql.append(" SELECT  DEPT_CD      																		\n");
						sql.append("         ,A.DEPT_NM     																		\n");
						sql.append("         ,A.AC_UNIT_CD                                 										\n");
						sql.append("         ,F_GET_CODE_NAME('101', A.AC_UNIT_CD) AS AC_UNIT_NM                     				\n");
						sql.append("         ,A.STD_CURRENCY_CD                    												\n");
						sql.append("         ,F_GET_CURRENCY_DIGIT(A.STD_CURRENCY_CD) AS STD_DIGIT								\n");
						sql.append(" FROM	ACZ10100V A																			\n");
						sql.append(" WHERE	SUBSTR(DEPT_CD,1,4) = '"+dept_cd+"'													\n");
						sql.append("   AND A.AC_UNIT_CD != '50'                           										\n");
						sql.append(" AND	DEPT_CD != '0000'																	\n");
					}
				}
			}
			

			if( !deadflg_value.equals("") )
				sql.append(" AND      A.PRJ_OK = '"+deadflg_value+"'										\n");

			if( !params.get("DEPT_NM").equals("") )
				sql.append(" AND      UPPER(A.DEPT_NM) LIKE UPPER('%" + params.get("dept_nm") + "%')	\n");

			sql.append(" ORDER BY A.DEPT_CD                   										\n");
			WAFLogger.debug(sql.toString());
        	search.setStatement(sql.toString());
            rowSet = search.execute();

        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    public RowSet getSpotDeptForeign(ValueObject Params) {

        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();

        try {

			ValueObject user = storage.getUser();

			String dept_cd    		= user.get("DEPT_CD");
			String comon_dcr    	= user.get("COMON_DCR");
			String dept_cnt    		= user.get("DEPT_CNT");
			String deadflg			= params.get("deadflg");
			String mudept_yn		= user.get("MUDEPT_YN");
			//본사이고 계정타입이 관리자거나 경영지원(01)일때
			if( comon_dcr.equals("1") ) {
				sql.append(" SELECT  A.DEPT_CD      																		\n");
				sql.append("         ,A.DEPT_NM     																		\n");
				sql.append("         ,A.AC_UNIT_CD                                 										\n");
				sql.append("         ,F_GET_CODE_NAME('101', A.AC_UNIT_CD) AS AC_UNIT_NM                     				\n");
				sql.append("         ,A.STD_CURRENCY_CD                    												\n");
				sql.append("         ,A.LOCAL_CURRENCY_CD                    												\n");
				sql.append("         ,F_GET_CURRENCY_DIGIT(A.STD_CURRENCY_CD) AS STD_DIGIT								\n");
				sql.append("         ,F_GET_CURRENCY_DIGIT(A.LOCAL_CURRENCY_CD) AS LOCAL_DIGIT							\n");
				sql.append(" FROM	ETEC_ERPIF.ACZ10100 A																			\n");
				sql.append(" WHERE	1 = 1   																			\n");
				sql.append(" AND	A.DEPT_CD != '0000'																	\n");
				
				if( !params.get("ac_unit_cd").equals("") ) {
					sql.append(" AND      A.AC_UNIT_CD = '" + params.get("ac_unit_cd") + "'          \n");
				}
				
			//현장일때
			} else { 
				if( dept_cnt.equals("1") ){
					if( mudept_yn.equals("Y") ){
						sql.append(" SELECT  A.DEPT_CD      																	\n");
						sql.append("         ,A.DEPT_NM     																	\n");
						sql.append("         ,A.AC_UNIT_CD                                 										\n");
						sql.append("         ,F_GET_CODE_NAME('101', A.AC_UNIT_CD) AS AC_UNIT_NM                     			\n");
						sql.append("         ,A.STD_CURRENCY_CD                    												\n");
						sql.append("         ,A.LOCAL_CURRENCY_CD                    											\n");
						sql.append("         ,F_GET_CURRENCY_DIGIT(A.STD_CURRENCY_CD) AS STD_DIGIT								\n");
						sql.append("         ,F_GET_CURRENCY_DIGIT(A.LOCAL_CURRENCY_CD) AS LOCAL_DIGIT							\n");
						sql.append(" FROM	ETEC_ERPIF.ACZ10100 A, PUB0130T_V B															\n");
						sql.append(" WHERE	SUBSTR(A.DEPT_CD,1,4) = SUBSTR(B.DEPT_CD,1,4)						\n");
						sql.append(" AND	B.ACCT_ID = "+user.get("ACCT_ID")+"													\n");
						sql.append(" AND	A.DEPT_CD != '0000'																	\n");
					}else {
						sql.append(" SELECT  DEPT_CD      																		\n");
						sql.append("         ,A.DEPT_NM     																		\n");
						sql.append("         ,A.AC_UNIT_CD                                 										\n");
						sql.append("         ,F_GET_CODE_NAME('101', A.AC_UNIT_CD) AS AC_UNIT_NM                     				\n");
						sql.append("         ,A.STD_CURRENCY_CD                    												\n");
						sql.append("         ,A.LOCAL_CURRENCY_CD                    												\n");
						sql.append("         ,F_GET_CURRENCY_DIGIT(A.STD_CURRENCY_CD) AS STD_DIGIT								\n");
						sql.append("         ,F_GET_CURRENCY_DIGIT(A.LOCAL_CURRENCY_CD) AS LOCAL_DIGIT							\n");
						sql.append(" FROM	ETEC_ERPIF.ACZ10100 A																			\n");
						sql.append(" WHERE	SUBSTR(DEPT_CD,1,4) = '"+dept_cd+"'													\n");
						sql.append("   AND A.AC_UNIT_CD != '50'                           										\n");
						sql.append(" AND	DEPT_CD != '0000'																	\n");
					}
				}
			}
			

			if( !deadflg.equals("") )
				sql.append(" AND      A.PRJ_OK = '"+deadflg+"'										\n");

			if( !params.get("DEPT_NM").equals("") )
				sql.append(" AND      UPPER(A.DEPT_NM) LIKE UPPER('%" + params.get("dept_nm") + "%')	\n");

			sql.append(" ORDER BY A.DEPT_CD                   										\n");
			
        	search.setStatement(sql.toString());
        	WAFLogger.debug(sql.toString());
            rowSet = search.execute();

        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    private RowSet getSpotDeptAll(ValueObject Params) {
    	
    	Search search = new SearchImpl();
    	RowSet rowSet = new RowSetImpl();
    	StringBuffer sql = new StringBuffer();
    	
    	try {
    		
    		ValueObject user = storage.getUser();
    		
    		String dept_cd    		= user.get("DEPT_CD");
    		String comon_dcr    	= user.get("COMON_DCR");
    		String dept_cnt    		= user.get("DEPT_CNT");
    		String deadflg			= params.get("deadflg");
    		
    		//본사이고 계정타입이 관리자거나 경영지원(01)일때
    		if( comon_dcr.equals("1") ) {
    			sql.append(" SELECT  DEPT_CD      																		\n");
    			sql.append("         ,DEPT_NM     																		\n");
    			sql.append("         ,AC_UNIT_CD                                 										\n");
    			sql.append("         ,F_GET_CODE_NAME('101', AC_UNIT_CD) AS AC_UNIT_NM                     				\n");
    			sql.append("         ,STD_CURRENCY_CD                    												\n");
    			sql.append("         ,LOCAL_CURRENCY_CD                    												\n");
    			sql.append("         ,F_GET_CURRENCY_DIGIT(STD_CURRENCY_CD) AS STD_DIGIT								\n");
    			sql.append("         ,F_GET_CURRENCY_DIGIT(LOCAL_CURRENCY_CD) AS LOCAL_DIGIT							\n");
    			sql.append(" FROM	ETEC_ERPIF.ACZ10100																			\n");
    			sql.append(" WHERE	1 = 1   																			\n");
				sql.append("   AND AC_UNIT_CD != '50'                           										\n");
    			//sql.append(" AND	DEPT_CD != '0000'																	\n");
    			
    			if( !params.get("ac_unit_cd").equals("") ) {
    				sql.append(" AND      AC_UNIT_CD = '" + params.get("ac_unit_cd") + "'          \n");
    			}
    			
    			//현장일때
    		} else { 
    			if( dept_cnt.equals("1") ){
    				sql.append(" SELECT  DEPT_CD      																		\n");
    				sql.append("         ,DEPT_NM     																		\n");
    				sql.append("         ,AC_UNIT_CD                                 										\n");
    				sql.append("         ,F_GET_CODE_NAME('101', AC_UNIT_CD) AS AC_UNIT_NM                     				\n");
    				sql.append("         ,STD_CURRENCY_CD                    												\n");
    				sql.append("         ,LOCAL_CURRENCY_CD                    												\n");
    				sql.append("         ,F_GET_CURRENCY_DIGIT(STD_CURRENCY_CD) AS STD_DIGIT								\n");
    				sql.append("         ,F_GET_CURRENCY_DIGIT(LOCAL_CURRENCY_CD) AS LOCAL_DIGIT							\n");
    				sql.append(" FROM	ETEC_ERPIF.ACZ10100																			\n");
    				sql.append(" WHERE	DEPT_CD = '"+dept_cd+"'																\n");
    				sql.append("   AND AC_UNIT_CD != '50'                           										\n");
    				//sql.append(" AND	DEPT_CD != '0000'																	\n");
    			}
    		}
    		
    		
    		if( !deadflg.equals("") )
    			sql.append(" AND      PRJ_OK = '"+deadflg+"'										\n");
    		
    		if( !params.get("DEPT_NM").equals("") )
    			sql.append(" AND      UPPER(DEPT_NM) LIKE UPPER('%" + params.get("dept_nm") + "%')	\n");
    		
    		sql.append(" ORDER BY DEPT_CD                   										\n");
    		
    		search.setStatement(sql.toString());
    		WAFLogger.debug(sql.toString());
    		rowSet = search.execute();
    		
    	}
    	catch (WAFSQLException se) {
    		super.doException(se);
    		this.storage.setSuccess(false);
    		this.storage.setMessage(se.getMessage());
    		storage.setDetailMessage(se);
    	}
    	finally {    
    		if(rowSet == null) rowSet = new RowSetImpl();
    		return rowSet;
    	}
    }
    
    public RowSet getDivisionRowSet() throws Exception {
		  
		  Search search = new SearchImpl();
		  RowSet listRowSet = null;
		  StringBuffer sql = new StringBuffer();
		  
		  ValueObject user = storage.getUser();
		  String head_dept_cd = user.get("HEAD_DEPT_CD");
		  String acc_dept_cd = user.get("ACC_DEPT_CD");
		  String comon_dcr = user.get("COMON_DCR");
		  
		  try{
				  sql.append("	SELECT '' AS DETAILCD, '전체' AS DETAILNM FROM DUAL							\n");
				  sql.append("	UNION ALL																	\n");
				  sql.append("	SELECT DETAILCD, DETAILNM FROM EBID_COM911T     				\n");
				  sql.append("		WHERE BASECD = '101'							\n");
				  if(head_dept_cd.equals("30")){
					  sql.append("			AND DETAILCD <> '10'     											\n");
					  sql.append("			AND DETAILCD <> '20'												\n");
					  sql.append("			AND DETAILCD <> '40'     											\n");
					  sql.append("			AND DETAILCD <> '50'												\n");
					  sql.append("			AND DETAILCD <> '60'												\n");
					  sql.append("			AND DETAILCD <> '99'       										\n");
				  }else{
					  sql.append("			AND DETAILCD <> '30'     											\n");
					  sql.append("			AND DETAILCD <> '40'     											\n");
					  sql.append("			AND DETAILCD <> '99'       										\n");
					  
				  }

				  WAFLogger.debug(sql.toString());
			  search.setStatement(sql.toString());
	          listRowSet = search.execute();
		  }
		  catch(WAFSQLException e) {
	          super.doException(e);
	          return null;
	      }
	      finally {
	          if( listRowSet == null ){
	        	  listRowSet = new RowSetImpl();
	          }
	          
	      }
	      return listRowSet;
		}
    
    /**
     * 현장코드 검색을 처리한다.(전체)
     * @param params
     * @return <code>RowSet</code>
     */
    public RowSet getSpotDept2(ValueObject Params) {

    	Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();

        try {			
			sql.append(" SELECT   DEPT_CD, DEPT_NM, AC_UNIT_CD, REP_DEPT_CD, PRJ_OK     \n");
			sql.append(" FROM     ACZ10100V                                        \n");
			sql.append(" WHERE    1 = 1                                         		\n");
			sql.append(" AND	  DEPT_CD != '0000'                                     \n");
			
			//사업부
			if( !params.get("ac_unit_cd").equals("") )
				sql.append(" AND      AC_UNIT_CD = '" + params.get("ac_unit_cd") + "' \n");

			if( !params.get("DEADFLG").equals("") )
				sql.append(" AND      PRJ_OK = '" + params.get("deadflg") + "' \n");

			//부서명
			if( !params.get("DEPT_NM").equals("") )
				sql.append(" AND      UPPER(DEPT_NM) LIKE UPPER('%" + params.get("dept_nm") + "%') \n");

			sql.append(" ORDER BY DEPT_CD                   \n");
			
        	search.setStatement(sql.toString());
            rowSet = search.execute();

        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
}
