/**
 * @(#)file    PuSupplierListBiz.java
 * @(#)author  이성민
 * @(#)version 1.0
 * @(#)date    2014.06.25
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of udapsoft Inc.
 */
package kr.co.udapsoft.ebid.buyer.supplier.purchase.biz;



import java.io.Reader;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
import kr.co.hsnc.common.sql.Row;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFConnectionImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.common.sender.MessageSend;
import kr.co.udapsoft.ebid.buyer.bid.outside.biz.InterfaceOutERPiU;
import kr.co.udapsoft.ebid.buyer.bid.purchase.biz.InterfacePurERPiU;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;

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

import blowfishj.*;

import java.text.SimpleDateFormat;
import java.util.*;

import oracle.toplink.internal.ox.StrBuffer;
import weblogic.xml.security.specs.User;


public class PuSupplierListBiz extends Biz {

	public PuSupplierListBiz(HandlerStorage storage) {
	    
	    
		super(storage);
	}

	/* (non-Javadoc)
	 * @see kr.co.hwenc.system.Biz#doBiz()
	 */
	public void doBiz() throws Exception {

		
		
		
		if(storage.compareEvent(""))
		{
//	        RowSet acctTypeRowSet = common.getAcctTypeUseRowSet(storage.getUser().get("ACCT_TYPE_CODE"));
//	        storage.addRowSet("AcctTypeRowSet", acctTypeRowSet);
			ValueObject auth = new ValueObject();
			auth = getAuthReqeustValueObject(params);
            storage.addValueObject("Auth", auth);
            
		    RowSet suppStatusRowSet = getSuppStatusRowSet(params);
            storage.addRowSet("SuppStatusRowSet", suppStatusRowSet);
            
		    
		    RowSet materialKindCodeLv1 = getLv1MaterialKindCode();
            storage.addRowSet("materialKindCodeLv1", materialKindCodeLv1);
            
		}
		else if(storage.compareEvent("GET_LIST"))
        {

		    params.set("sheet1", getList(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
            
        }else if(storage.compareEvent("EMAIL_SEND")){
        	boolean success = sendData(params);
        	storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }else if(storage.compareEvent("ERP_SEND")){
        	boolean success = erpsendData(params);
        	storage.setSuccess(success);
        	//storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }else if(storage.compareEvent("TEST_ALL")){
        	boolean success = test_all(params);
        	super.storage.setSuccess(success);
             if(success){
             	storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
             }
        }else if(storage.compareEvent("GET_MATL_KIND_CODE_LV2"))
        {
            //ValueObject vo = getMaterialKindCode();
            //storage.addValueObject("VALUEOBJECT", vo);
            
            RowSet materialKindCodeLv2 = getLv2MaterialKindCode(params.get("material_Kind_Code_Lv1"));
            storage.addRowSet( "ROWSET", materialKindCodeLv2 );
            storage.setSuccess(true);
        }
        else if(storage.compareEvent("GET_MATL_KIND_CODE_LV3"))
        {
            /*
            ValueObject vo = getMaterialKindCode();
            
            storage.addValueObject("VALUEOBJECT", vo);
            */
            
            RowSet materialKindCodeLv3 = getLv3MaterialKindCode(params.get("material_Kind_Code_Lv2"));
            storage.addRowSet( "ROWSET", materialKindCodeLv3 );
            storage.setSuccess(true);
        }
        else if(storage.compareEvent("GET_CREADIT_REG_CHK"))
        {
            final String AUTHKEY = "596e317a4d46557462317071506c6b"; //암호화키
            final String BZ_INS_NO = params.get("p_corp_reg_no"); //사업자번호
            
            AesUtil aesUtil = new AesUtil();
            
            String e_bz_ins_no = aesUtil.encrypt(AUTHKEY, BZ_INS_NO); //암호화 사업자번호
            String url = "https://xlink.nicednb.com/weblink/toServer.do?clp_cd=411&bz_ins_no=" + e_bz_ins_no;
            
            ValueObject getUrl = new ValueObject();
            
            getUrl.set("returnUrl", url);
            
            storage.addValueObject("VALUEOBJECT", getUrl);
            storage.setSuccess(true);
        }
        else if(storage.compareEvent("GET_CREADIT_REG_CHK2"))
        {  
            String bzno = params.get("p_corp_reg_no"); //사업자번호
            
            SimpleDateFormat encdDateFormat = new SimpleDateFormat("ddHHmmss");                                                                                                                                                                                  
            Calendar encdCalendar = Calendar.getInstance();                                                                                                                                                                                                      
            String encdtoday  =  "";     //fKey구성에쓰일 시간값                                                                                                                                                                                                 
            encdtoday =  encdDateFormat.format(encdCalendar.getTime());     
            
            String KEY = "kzCur4ryVY";      
            
            BlowfishEasy bfes = new BlowfishEasy(KEY.toCharArray());                                                                                                                                                                                             
            String finalkey = bfes.encryptString(encdtoday);                                                                                                                                                                                                     
            String autolink_url = "https://www.k-srm.co.kr/refbfautologin.do?acctp=J1&isSSL=&ID=ETEC&siteKey="+finalkey+"=&BZNO="+bzno;
            
            
            ValueObject return_url = new ValueObject();
            return_url.set("return_url", autolink_url);
            
            storage.addValueObject("VALUEOBJECT", return_url);
            storage.setSuccess(true);
        }
        else if(storage.compareEvent("SUPP_TEST"))
        {
            boolean supp_test = false;
            supp_test = sp_reg_appr_set(params);
            
            if(supp_test == true)
            {
            	WAFLogger.debug("supp_test=================>" + supp_test);
                
                super.storage.setSuccess(supp_test);
                storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
            }
        }
        else if(storage.compareEvent("SUPP_TEST2"))
		{
			suppApprSet2();
		}
        else if(storage.compareEvent("GET_NATION"))
        {
            RowSet nationRowSet = getNationList(params);
            storage.addRowSet("ROWSET", nationRowSet );
            storage.setSuccess(true);
        }
        else if(storage.compareEvent("GET_ATTACHFILE"))
        {
        	ValueObject AttachFile = new ValueObject();
			//첨부파일 2016.8.5
        	if (params.get("job_div").equals("P")){
        		AttachFile = getfilelist("TERMS_AGREE_INFO", params.get("p_corp_id"),"AttachFile");
        	}
        	else{
        		AttachFile = getfilelist("TERMS_AGREE_INFO_"+params.get("p_corp_id"),"","AttachFile");
        	}
	        storage.addValueObject("VALUEOBJECT", AttachFile);
        }
	}
	
	private RowSet getNationList(ValueObject Params) {
        
        
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("    SELECT DETAILCD, DETAILNM                          \n")
                   .append("        FROM EBID_COM911T                              \n")
                   .append("    WHERE BASECD = 'NATION_CD'                         \n")
                   .append("    ORDER BY DETAILNM ASC                                   \n");
            
            
            
            WAFLogger.debug(sqlStr.toString());

            search.setStatement(sqlStr);
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
	
	private void suppApprSet2(){
		
		CallableStatement cs = null;
		ValueObject rtnVo = new ValueObject();
		conn.begin();
		try 
		{
			Connection con = conn.getConnection();
			
            String corp_id   = params.get("p_corp_id");
            
            String sqlStr = "{call SP_REG_APPR_SET2(?, ?, ?)}";
            
            cs = con.prepareCall(sqlStr);
            cs.setString(1, corp_id);
            cs.registerOutParameter(2, java.sql.Types.VARCHAR);
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.execute();
            
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(2));
            rtnVo.set("OUT_MESSAGE",cs.getString(3));
            
			
			storage.setSuccess(true);
			//storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		} catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
            try {
                if(cs !=null ) cs.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
			conn.end();
		} 
		
	}
	
	public boolean sp_reg_appr_set(ValueObject Params) throws Exception { 
        
        CallableStatement cs = null;
        ValueObject rtnVo = new ValueObject();
        
        try {
            conn.begin();                       
            String corp_id   = params.get("p_corp_id");
            
            System.out.println("corp_id["+corp_id+"]");
            
            String sqlStr = "{call SP_REG_APPR_SET(?, ?, ?)}";
            
            cs = super.conn.getConnection().prepareCall(sqlStr);
            cs.setString(1, corp_id);
            cs.registerOutParameter(2, java.sql.Types.VARCHAR);
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.execute();
            
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(2));
            rtnVo.set("OUT_MESSAGE",cs.getString(3));
            
            WAFLogger.debug(cs.getString(3));
            
            return true;
        }
        catch(Exception e) {
            super.doException(e);
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(24));
            rtnVo.set("errMsg",cs.getString(3));
            super.conn.setRollback();
            this.storage.setSuccess(false);
            this.storage.setMessage(e.getMessage());
            return false;
        }
        finally {
            super.conn.end();
        }
    }
	
	
	/**
     * 목록을 검색하여 리턴
     * @param Params
     * @return
     */
    private RowSet getList(ValueObject Params) throws Exception {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
				sqlStr.append("    SELECT                                                       																								\n")                                                                                                                                  
					  .append("           A.CORP_ID                                                     																						\n")                                                                                                                                                                     
					  .append("           ,B.USER_ID                                                																							\n")
					  .append("           ,A.CORP_REG_NO                                           																								\n")
					  .append("           ,A.CORP_KOR                                                																							\n")     
					  .append("           ,A.IN_OUT_DIV AS IN_OUT_DIV_CODE                                                																		\n")     
					  .append("           ,DECODE(A.IN_OUT_DIV, 'I' , '국내','O',  '해외', '' ) IN_OUT_DIV                                                    									\n")
					  .append("           ,A.REP_NAME                                                     																						\n")                                                                                                                          
					  .append("           ,DECODE(A.CREDIT_GRADE, '', '', A.CREDIT_GRADE || '(' || CREDIT_CORP_CD || ')' ) CREDIT_GRADE                											\n")                                                                                                 
					  .append("           ,A.CREDIT_CORP_CD                                                     																				\n")                                                                                                                          
					  .append("           ,DECODE(A.CORP_DIV_CD, '1', '법인', '개인') CORP_DIV_CD         																						\n")                                                                             
					  .append("           ,TO_CHAR(A.REG_REQ_DT, 'YYYY-MM-DD') AS REG_REQ_DT                                                                     								\n")                                                                                
					  .append("           ,TO_CHAR(A.CRE_DATE, 'YYYY-MM-DD') AS CRE_DATE                      																					\n")                                                                                                                               
					  //                    .append("                , DECODE(A.STATUS, '00', '가입요청', '01', '대기', '02', '가등록', '03', '등록', '91', '거래중지', '99', '거래취소') CORP_STATUS              \n")
					  .append("           ,CASE  WHEN A.STATUS = '00' THEN  '가입요청'                    																						\n")
					  .append("                  WHEN A.STATUS = '01' THEN  '대기'                   																							\n")
					  .append("                  WHEN A.STATUS = '02' AND TO_CHAR(TO_DATE(USE_END_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD')  <  TO_CHAR(SYSDATE, 'YYYY-MM-DD')  THEN  '등록기간 만료'	\n")
					  .append("                  WHEN A.STATUS = '02' THEN  '가등록'                      																						\n")
					  .append("                  WHEN A.STATUS = '03' AND TO_CHAR(TO_DATE(USE_END_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD')  <  TO_CHAR(SYSDATE, 'YYYY-MM-DD')  THEN  '등록기간 만료'    \n")
					  .append("                  WHEN A.STATUS = '03' THEN  '등록'                   																							\n")
					  .append("                  WHEN A.STATUS = '91' THEN  '거래중지'                     																						\n")
					  .append("                  WHEN A.STATUS = '99' THEN  '거래취소'                     																						\n")
					  .append("             END  AS CORP_STATUS              																													\n")           
					  .append("           ,A.STATUS STATUS_CODE                          																										\n")
					  .append("           ,A.JOB_DIV                                            																								\n")
					  .append("           ,D.GUBUN_1                                   																											\n")
					  .append("           ,D.GUBUN_2                                   																											\n")
					  .append("           ,D.GUBUN_3                                   																											\n")
					  //.append("           ,CHK_CREDIT(C.CORP_ID) EVAL_CMP                                   																					\n")
					  .append("           ,F.DOC_CD                                   																											\n")
					  .append("           ,F.APPR_TOT_RATE                                   																									\n")
					  .append("           ,F.ARR_ID                                   																											\n")
					  .append("           ,F.APPR_DY                                   																											\n")
					  .append("           ,F.ADM_YEAR                                 																											\n")
					  .append("           ,TO_CHAR(A.REG_DT, 'YYYY-MM-DD') REG_DT                                   																			\n")
					  .append("           ,TO_CHAR(APPR_DT, 'YYYY-MM-DD') || TO_CHAR(CAN_DT, 'YYYY-MM-DD') AS APPR_CAN_DT                                   									\n")
					  .append("           , TO_CHAR(TO_DATE(A.CREDIT_STA_DY), 'YYYY-MM-DD') CREDIT_STA_DY    \n")
                      .append("           , TO_CHAR(TO_DATE(A.CREDIT_END_DY), 'YYYY-MM-DD') CREDIT_END_DY    \n")
                      .append("           ,\"COMMENT\"													     \n")
                      .append("      FROM SUPP_INFO A																																			\n")
					  .append("			  ,SUPP_CHARGE B																																		\n")
					  .append("			  ,SUPP_PUR_ITEM C																																		\n")
					  .append("			  ,PMM_MATERIALGROUP_V D																																\n")
					  .append("			  ,PMM_MATERIALGROUP E																																	\n")
					  .append("			  ,(SELECT  DOC_CD																																		\n")
					  .append("					   ,CORP_ID																																		\n")
					  .append("					   ,APPR_TOT_RATE																																\n")
					  .append("					   ,ARR_ID																																		\n")
					  .append("					   ,APPR_DY 																																	\n")
					  .append("					   ,(SELECT max(ADM_YEAR) FROM SUPP_APPR_ITEM WHERE (CORP_ID,ARR_ID) IN (SELECT CORP_ID,MAX(ARR_ID) FROM SUPP_APPR WHERE DOC_CD IN ('01', '02') GROUP BY CORP_ID) and corp_id = aa.corp_id) ADM_YEAR					\n")
					  .append("				  FROM SUPP_APPR aa																																	\n")
					  .append("				 WHERE DOC_CD IN ( '01' , '02')																														\n")
					  .append("				   AND (CORP_ID,ARR_ID) IN (SELECT CORP_ID,MAX(ARR_ID) FROM SUPP_APPR WHERE DOC_CD IN ('01', '02') GROUP BY CORP_ID))    F                  		\n")
					  .append("     WHERE A.CORP_ID = B.CORP_ID                                     																							\n")
					  .append("       AND A.CORP_ID = C.CORP_ID                                   																								\n")
					  .append("       AND C.KIND_SMALL = D.MATERIALGROUP                                   																						\n")
					  .append("       AND D.MATERIALGROUP = E.MATERIALGROUP(+)                                   																				\n")
					  .append("       AND A.CORP_ID = F.CORP_ID(+)                                   																							\n")
					  .append("       AND DEFAULT_YN = 'Y'                                    																									\n")
					  .append("       AND JOB_DIV = 'P'                                         																								\n")
					  .append("       AND C.REPRE_YN = 'Y'                                       																								\n")
					  .append("       AND (A.STATUS != '00' OR A.STATUS != '')                                      																			\n");
            if(!params.get("corp_no").equals(""))
            {
                sqlStr.append("         AND A.CORP_REG_NO LIKE '%' || ? || '%'        \n");
            }
            if(!params.get("corp_name").equals(""))
            {
            	if(params.get("search_gb").equals("1")){ //업체명 + 의견
            		sqlStr.append("         AND (replace(upper(A.CORP_KOR), ' ', '') LIKE replace(upper('%' || ? || '%'), ' ','') OR replace(upper(A.\"COMMENT\"), ' ', '') LIKE replace(upper('%' || ? || '%'), ' ',''))        \n");
            	} else if(params.get("search_gb").equals("2")){ //업체명
            		sqlStr.append("         AND replace(upper(A.CORP_KOR), ' ', '') LIKE replace(upper('%' || ? || '%'), ' ','')        \n");
            	} else { //의견
            		sqlStr.append("         AND replace(upper(A.\"COMMENT\"), ' ', '') LIKE replace(upper('%' || ? || '%'), ' ','')        \n");
            	}
            }
            if(!params.get("corp_status").equals(""))
            {
                if(params.get("corp_status").equals("999"))
                {
                    sqlStr.append("         AND (A.STATUS = '03' OR A.STATUS = '02')  AND TO_CHAR(TO_DATE(A.USE_END_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD')  <  TO_CHAR(SYSDATE, 'YYYY-MM-DD')                            \n");
                }
                else
                {
                    sqlStr.append("         AND A.STATUS = ?                            \n");
                }
            }
            if(!params.get("material_Kind_Code_Lv1").equals(""))
            {
                sqlStr.append("         AND E.MATERIAL_GB = ?        \n");
            }
            if(!params.get("material_Kind_Code_Lv2").equals(""))
            {
                sqlStr.append("         AND D.PARENT_LEVEL = ?        \n");
            }
            if(!params.get("material_Kind_Code_Lv3").equals(""))
            {
                sqlStr.append("         AND D.MATERIALGROUP = ?        \n");
            }
            if(!params.get("in_out_gubun").equals(""))
            {
                sqlStr.append("         AND A.IN_OUT_DIV = ?        \n");
            }
            if(!params.get("nation_cd").equals(""))
            {
                sqlStr.append("         AND A.NATION_CD = ?        \n");
            }
            if(params.get("chk_all").equals("true"))          
            {
            	sqlStr.append(" AND A.CORP_ID IN (                                                  \n");
	            sqlStr.append(" SELECT A.CORP_ID                                                    \n");
	            sqlStr.append("   FROM PUR1000T A                                                   \n");
	            sqlStr.append("       ,SUPP_INFO B                                                  \n");
	            sqlStr.append("       ,(SELECT PUR_CONT_ID,  SUM(cur_req_amt) AMT                   \n"); 
	            sqlStr.append("       	  FROM PUR1100T                                             \n");
	            sqlStr.append("       	 WHERE REQ_STATUS = 10                                      \n");
	            sqlStr.append("       	 GROUP BY PUR_CONT_ID) C                                    \n");
	            sqlStr.append("       ,(SELECT PUR_CONT_ID, SUM(REQ_PREPAY_SUPPLY_AMT) AMT          \n");
	            sqlStr.append("       	  FROM PUR1030T                                             \n");
	            sqlStr.append("       	 WHERE PREPAY_PROC_GB = '10'                                \n");
	            sqlStr.append("       	 GROUP BY PUR_CONT_ID) D                                    \n");
	            sqlStr.append("       ,ETEC_ERPIF.ACZ10100 E                                             \n");
	            sqlStr.append("   WHERE A.CORP_ID = B.CORP_ID                                       \n");
	            sqlStr.append("     AND A.PUR_CONT_ID = C.PUR_CONT_ID(+)                            \n");  
	            sqlStr.append("     AND A.PUR_CONT_ID = D.PUR_CONT_ID(+)                            \n");
	            sqlStr.append("     AND A.DEPT_CD = E.DEPT_CD                                       \n");
	            sqlStr.append("     AND NVL(A.ORD_SUPPLY_AMT,0)- NVL(C.AMT,0) - NVL(D.AMT,0) > 0    \n"); 
	            sqlStr.append("     AND E.PRJ_OK = 0 --종료되지 않은현장                                                               \n");    
	            sqlStr.append("   GROUP BY A.CORP_ID                                                \n");   
	            sqlStr.append(" )                                                                   \n");
	        }            
            
            search.setStatement(sqlStr.toString());
            if(!params.get("corp_no").equals(""))
            {
            search.addParameter(params.get("corp_no").trim());
            }
            if(!params.get("corp_name").equals(""))
            {
            	if(params.get("search_gb").equals("1")){
            		search.addParameter(params.get("corp_name").trim());
            		search.addParameter(params.get("corp_name").trim());
            	} else {
            		search.addParameter(params.get("corp_name").trim());
            	}
            }
            if(!params.get("corp_status").equals(""))
            {
                if(!params.get("corp_status").equals("999"))
                {
                    search.addParameter(params.get("corp_status").trim().toString());
                }
            }
            if(!params.get("material_Kind_Code_Lv1").equals(""))
            {
            	search.addParameter(params.get("material_Kind_Code_Lv1").trim());
            }
            if(!params.get("material_Kind_Code_Lv2").equals(""))
            {
                search.addParameter(params.get("material_Kind_Code_Lv2").trim());
            }
            if(!params.get("material_Kind_Code_Lv3").equals(""))
            {
                search.addParameter(params.get("material_Kind_Code_Lv3").trim());
            }
            if(!params.get("in_out_gubun").equals(""))
            {
                search.addParameter(params.get("in_out_gubun").trim());
            }
            if(!params.get("nation_cd").equals(""))
            {
                search.addParameter(params.get("nation_cd").trim());
            }
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());
            
            rowSet = search.execute();
            
        }
        catch (Exception se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            WAFLogger.error("se==>" + se);
            se.printStackTrace();
            throw new Exception(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    
    // 자재분류 1레벨 코드 가져오기
    public RowSet getLv1MaterialKindCode() {
        Search search       = new SearchImpl();
        RowSet rowSet       = new RowSetImpl();
        StringBuffer sql    = new StringBuffer();
        
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      PARENT_LEVEL MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '0'           \n");            
            
            search.setStatement(sql);
            rowSet = search.execute();
            WAFLogger.debug(sql.toString());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    
    // 자재분류 2레벨 코드 가져오기
    public RowSet getLv2MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '1'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
//            sql.append("    ORDER BY MATERIALGROUP          \n");
            
            WAFLogger.debug(sql.toString());
            WAFLogger.debug(search.getParameters());
            
            
            search.setStatement(sql);
            rowSet = search.execute();
            
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    // 자재분류 3레벨 코드 가져오기
    public RowSet getLv3MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '2'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
            
            search.setStatement(sql);
            
            rowSet = search.execute();
            WAFLogger.debug(sql.toString());
            WAFLogger.debug(search.getParameters());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    
    private RowSet getSuppStatusRowSet(ValueObject Params) throws Exception
    {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        
        try
        {           
            sqlStr.append(" SELECT DETAILCD, DETAILNM FROM EBID_COM911T WHERE BASECD = 'SUPP_INFO_STATUS' AND DETAILCD NOT  IN('AA', '00')                                 \n");
            sqlStr.append(" UNION ALL                                                                                                                                                                                          \n");
            sqlStr.append(" SELECT '999' DETAILCD, '등록기간 만료' DETAILNM FROM DUAL                               \n");
            
            
            search.setStatement(sqlStr.toString());
            rowSet = search.execute();
        }
        catch (Exception se) {
        	WAFLogger.error(se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            se.printStackTrace();
            throw new Exception(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
	private ValueObject getfilelist(String filegb, String id, String returnid){
		ValueObject vo = new ValueObject();
		RowSet rowSet = FileManager.getFileList(filegb, id);
		String str="";
		System.out.println(filegb + '/'+ id);
	        for(int i=0;i<rowSet.size();i++){
	        	Row row=rowSet.getRow(i);
	        	str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
	        	if(i+1!=rowSet.size()){
	        		str +="\r\n";
	        	}
	        	System.out.println(row.get("FILE_NAME"));
	        }
	        vo.set(returnid, str);
		return vo;
	}
	
	private boolean test_all(ValueObject params) {
    	CallableStatement cs = null;
    	ValueObject rtnVo = new ValueObject();
    	try {
    		String[] status  		= super.gets("sheet1.STATUS");
			String[] corpId  		= super.gets("sheet1.CORP_ID");
			String[] corpNm  		= super.gets("sheet1.CORP_KOR");
			String   msg = "";
			
			conn.begin();
			
			if( status != null ) {
				for( int i = 0 ; i < status.length ; i++ ) {
					if( "U".equals(status[i]) ) {
						//System.out.println(i  + "/" + corpId[i] + corpNm[i]);
						msg = test_all_sub(conn.getConnection(), corpId[i], corpNm[i]);
						
						if(msg != "S"){
							throw new Exception(msg); 
						}
					}
				}
			}	
			return true;
        }
        catch(Exception e) {
            storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
            storage.setDetailMessage(e);
            return false;
            
        }
        finally {
        	 super.conn.end();
        }
	}
	private String test_all_sub(Connection con, String corp_id, String corp_nm ) {
    	CallableStatement cs = null;
    	try {
    		
			String sqlStr = "{call SP_REG_APPR_SET(?, ?, ?)}";
						
			cs = con.prepareCall(sqlStr);
			cs.setString(1, corp_id);
			cs.registerOutParameter(2, java.sql.Types.VARCHAR);
			cs.registerOutParameter(3, java.sql.Types.VARCHAR);
			cs.execute();
			         
			if(cs.getString(2).equals("S")){
				storage.setSuccess(true);
			}else{
				storage.setMessage(corp_nm  + ' ' + cs.getString(3));
				throw new Exception(corp_nm + ' ' + cs.getString(3)); 
			}
			return "S";
        }
        catch(Exception e) {
            storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
            conn.setRollback();
            storage.setDetailMessage(e);
            return e.getMessage();
        }
        finally {
        	try {
	            if(cs !=null ) cs.close();
	        }
	        catch (SQLException e) {
	            e.printStackTrace();
	        }
        }
	}
	private boolean sendData(ValueObject params) {

		try {

			conn.begin();
			
			String[] status  		= super.gets("sheet1.STATUS");
			String[] corpId  		= super.gets("sheet1.CORP_ID");
			
			if( status != null ) {
				for( int i = 0 ; i < status.length ; i++ ) {
					if( "U".equals(status[i]) ) {
						
						String sendEmail = getEmail(corpId[i]).get("EMAIL");
						
						String mailtitle = getContent().get("TITLE");
			     		//StringBuffer mailtitlein = new StringBuffer();
			     		StringBuffer mailContent = new StringBuffer();
			     		String content = getContent().get("SEND_EMAIL");
			     		
			            mailContent.append(content);
			            MessageSend messageSend = new MessageSend(storage);  //mailtitle.toString() mailtitlein.toString()
			            messageSend.sendEmail(sendEmail, "partner@sgc.co.kr",mailtitle ,mailtitle , mailContent);
			            storage.setSuccess(true);
					}
					
				}
			}
			return true;
        }
        catch(Exception e) {
            super.doException(e);
            super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
            super.conn.setRollback();
            return false;
        }
        finally {
        	super.conn.end();
        }
	}
	//ERP전송 
	private boolean erpsendData(ValueObject params) {
		try {
			//String[] status  		= super.gets("sheet1.STATUS");
			//String[] corpId  		= super.gets("sheet1.CORP_ID");
			//String[] corp_reg_no    = super.gets("sheet1.CORP_REG_NO");
			String corpId  		= params.get("p_corp_id"); 
			String corp_reg_no    = params.get("p_corp_reg_no");
			
			
			String aCount = "";
			
			
			WAFConnectionImpl erpConn = new WAFConnectionImpl();
			try {
				erpConn.setDataSource("jdbc/erpiu");
				erpConn.begin();
				
				//if( status != null ) {
					//for( int i = 0 ; i < status.length ; i++ ) {
						//if( "U".equals(status[i]) ) {							
							aCount = getErpSuppCountAll(corp_reg_no);
							ValueObject suppVo = getSuppInfo(corpId);
							if (aCount.equals("N")) {  //없으면 insert
								suppVo.set("CD_PARTNER", InterfaceOutERPiU.getCdPartner());
					            InterfacePurERPiU.insertErpSupp(erpConn.getConnection(), suppVo);
							}else {  //있으면 명칭 업데이트 
								suppVo.set("CD_PARTNER", aCount);
					            InterfacePurERPiU.updateErpSupp(erpConn.getConnection(), suppVo); 
							}
				            //구매담당자 ERP거래처 등록요청 메일발송
							sendSuppInfoMail(suppVo.get("TO_MAIL"), suppVo.get("CORP_REG_NO"), suppVo.get("LN_PARTNER"), suppVo.get("CORP_ID"),suppVo.get("CH_NAME"),suppVo.get("CH_TEL"),suppVo.get("JOB_DIV"));
						//}
					//}
				//}
				
			}catch(Exception e) {
				erpConn.setRollback();
				throw e;
			}finally {
				erpConn.close();
			}  
			return true;
        }
        catch(Exception e) {
            super.doException(e);
            super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
            super.conn.setRollback();
            return false;
        }
        finally {
        	super.conn.end();
        }
	}
	
    private String getErpSuppCountAll(String corpRegNo) throws Exception {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		StrBuffer sqlstr = new StrBuffer();
		String wCount = "";
		
		try{
			sqlstr.append("	SELECT  NVL(MAX(CD_PARTNER), 'N') AS CNT		        \n");
			sqlstr.append("	FROM    MA_PARTNER@ERPIU								\n");
			sqlstr.append("	WHERE   CD_COMPANY = 'ETEC'								\n");
			sqlstr.append("	  AND   (REPLACE(NO_COMPANY,'-','') = REPLACE(?,'-','')	\n");
			sqlstr.append("	OR   	CD_PARTNER = ?	)								\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(corpRegNo);
			search.addParameter(corpRegNo);
			rowSet = search.execute();
			if( rowSet != null && rowSet.size() > 0 ){
				valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
				wCount = valueobject.getString(0);
			}
			
		}catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
			throw se;
		}
		
		return wCount;
	}
	
	private ValueObject getSuppInfo(String corp_id) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        StrBuffer sqlstr = new StrBuffer();
        try{        
        	sqlstr.append(" SELECT '"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY    /*회사코드*/                                              \n");
            sqlstr.append("        ,A.CORP_KOR AS LN_PARTNER    /*거래처명(한)*/                                                                        \n");
            sqlstr.append("        ,A.CORP_KOR AS SN_PARTNER    /*거래처명(약어)*/                                                                       \n");
            sqlstr.append("        ,A.CORP_ENG AS EN_PARTNER    /*거래처명(영)*/                                                                        \n");
            sqlstr.append("        ,A.JOB_DIV                                                                                                         \n") ;
            sqlstr.append("        ,'EXT' AS TP_PARTNER    /*거래처타입*/                                                                               \n");
            sqlstr.append("        ,'001' AS FG_PARTNER    /*거래처구분*/                                                                               \n");
            sqlstr.append("        ,A.REP_NAME AS NM_CEO    /*대표자명*/                                                                                \n");
            sqlstr.append("        ,DECODE(A.IN_OUT_DIV,'O','9999999999',REPLACE(A.CORP_REG_NO,'-','')) AS NO_COMPANY    /*사업자등록번호*/              \n");
            sqlstr.append("        ,A.CORP_STS AS TP_JOB    /*업태*/                                                                                   \n");
            sqlstr.append("        ,A.BIZ_FORM AS CLS_JOB    /*종목*/                                                                                  \n");
            sqlstr.append("        ,'102' AS CLS_PARTNER    /*거래처분류*/                                                                              \n");
            sqlstr.append("        ,NVL(D.CHG_CODE10,'000') AS CD_NATION    /*국가코드*/                                                                \n");
            sqlstr.append("        ,B.EMAIL AS E_MAIL    /*담당자 E-MAIL*/                                                                              \n");
            sqlstr.append("        ,B.TEL_NUM AS NO_TEL    /*담당자전화번호*/                                                                             \n");
            sqlstr.append("        ,A.CORP_TEL AS NO_TEL1    /*본사전화번호*/                                                                            \n");
            sqlstr.append("        ,A.CORP_FAX AS NO_FAX1    /*본사FAX*/                                                                                \n");
            sqlstr.append("        ,B.USER_NAME AS CD_EMP_PARTNER    /*거래처담당자*/                                                                     \n");
            sqlstr.append("        ,B.HP1|| B.HP2||B.HP3 AS NO_HPEMP_PARTNER    /*핸드폰번호*/                                                           \n");
            sqlstr.append("        ,'N' AS USE_YN    /*사용유무*/                                                                                        \n");
            sqlstr.append("        ,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = B.CRE_BY) AS ID_INSERT    /*추가자*/                               \n");
            sqlstr.append("        ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT    /*등록일*/                                                     \n");
            sqlstr.append("        ,A.CORP_ID                                                                                                          \n");
            sqlstr.append("        ,E.CHG_CODE02 AS TO_MAIL                                                                                            \n");
            sqlstr.append("        ,CASE                                                                                                               \n");
            sqlstr.append("            WHEN     LENGTH(A.CORP_REG_NO) = 10                                                                             \n");
            sqlstr.append("            THEN    SUBSTR(A.CORP_REG_NO,1,3)||'-'||SUBSTR(A.CORP_REG_NO,4,2)||'-'||SUBSTR(A.CORP_REG_NO,6)                 \n");
            sqlstr.append("            ELSE    A.CORP_REG_NO                                                                                           \n");
            sqlstr.append("        END AS CORP_REG_NO                                                                                                  \n");
            sqlstr.append("        ,A.IN_OUT_DIV                                                                                                       \n");
            sqlstr.append("        ,A.POST_NO   NO_POST1                                                                                               \n");
            sqlstr.append("        ,A.ADDRESS   DC_ADS1_H                                                                                              \n");
            sqlstr.append("        ,A.BANK_ACCT NO_DEPOSIT                                                                                             \n");
            sqlstr.append("        ,A.BANK_CD   CD_BANK                                                                                                \n");
            sqlstr.append("        ,F.USER_NAME CH_NAME                                                                                                \n");
            sqlstr.append("        ,F.TEL_NO    CH_TEL                                                                                                 \n");
            sqlstr.append("        ,F.E_MAIL    CH_MAIL                                                                                                \n");
            sqlstr.append("   FROM  SUPP_INFO A                                                                                                        \n");
            sqlstr.append("        ,SUPP_CHARGE B                                                                                                      \n");
            sqlstr.append("        ,(SELECT DETAILCD, CHG_CODE10                                                                                       \n");
            sqlstr.append("            FROM EBID_COM911T                                                                                               \n");
            sqlstr.append("           WHERE BASECD = '911') D                                                                                          \n");
            sqlstr.append("        ,(SELECT DETAILCD, CHG_CODE02                                                                                      \n");
            sqlstr.append("            FROM EBID_COM911T                                                                                              \n");
            sqlstr.append("           WHERE BASECD = 'JOB_DIV') E                                                                                     \n");
            sqlstr.append("        ,PUB0120T F                                                                                                        \n");
            sqlstr.append("   WHERE A.CORP_ID = B.CORP_ID                                                                                             \n");
            sqlstr.append("     AND B.DEFAULT_YN = 'Y'                                                                                                \n");
            sqlstr.append("     AND A.NATION_CD = D.DETAILCD(+)                                                                                       \n");
            sqlstr.append("     AND A.JOB_DIV = E.DETAILCD                                                                                            \n");
            sqlstr.append("     AND F.ACCT_ID = ?                                                                                                     \n");
            sqlstr.append("     AND A.CORP_ID = ?                                                                                                     \n");
            
			search.setStatement(sqlstr.toString());
			search.addParameter(storage.getUser().get("ACCT_ID"));
            search.addParameter(corp_id);
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug("WAFSQLException : " + se.getMessage());
            System.out.println(sqlstr.toString());
            System.out.println(se.getMessage());
        }finally {
        	if( vo == null ) vo = new ValueObject();
            return vo;
        }
	}
	
	public boolean sendSuppInfoMail(String toMail, String corpRegNo, String corpName, String corpId, String chName, String chTel, String job_div) throws Exception {
		try {
			MessageSend mailSend = new MessageSend(storage);
			StringBuffer mailContent = new StringBuffer();
			String title = "ERP 협력업체 등록요청";
			
		    String fileLink = "https://ebid.sgcenc.co.kr/common/file/FileListNew.screen?file_gb=TERMS_AGREE_INFO&id="+corpId+"&is_modify=N";
	      	if (job_div.equals("O")){
	      		fileLink = "https://ebid.sgcenc.co.kr/common/file/FileListNew.screen?file_gb=TERMS_AGREE_INFO_"+corpId+"&is_modify=N";
		      	
	      	}
		    
      		mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
      		mailContent.append("</BR>");
      		mailContent.append("전자조달 계약을 위한 ERP 협력업체 등록을 진행해 주십시요.</BR>");
      		mailContent.append("</BR>");
      		mailContent.append("사업자번호: "+corpRegNo+"</BR>");
      		mailContent.append("업체명 : "+corpName+"</BR>");
      		mailContent.append("담당자 : " + chName + " " + chTel+"</BR>");
      		mailContent.append("첨부파일 : <a href='"+fileLink+"' target='_blank'>파일확인</a></BR>");
      		mailContent.append("</font>");
      		mailSend.sendEmail(toMail, "partner@sgc.co.kr", title, title, mailContent, "1");
      		
		    return true;
        }catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> sendSuppInfoMail() : " + e.getMessage());
            WAFLogger.error(e);
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }      
    }
	
    public ValueObject getEmail(String corpId) throws Exception {
		
		Search search = new SearchImpl();
		ValueObject valueObject = null;
		StringBuffer sqlstr = new StringBuffer();
		try {
			
			sqlstr.append( " SELECT  *                                          \n");
			sqlstr.append("  FROM    SUPP_CHARGE                                \n");
			sqlstr.append("  WHERE CORP_ID = '"+corpId+"'               \n");
			sqlstr.append("  AND CHARGE_SEQ = 1                                	\n");
			
			search.setStatement(sqlstr);
			valueObject = RowSetUtility.getValueObject(search.execute().getRow(0));
		}
		catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
		}
		finally {
			
			if( valueObject == null ) valueObject = new ValueObject();
			return valueObject;
		}
	}
	
	 public ValueObject getContent() throws Exception {

	        Search search = new SearchImpl();
	        ValueObject valueObject = null;
	        StringBuffer sqlstr = new StringBuffer();
	        try {

	            sqlstr.append( " SELECT  *                                            \n");
	            sqlstr.append("  FROM    SUPP_EMAIL                                 \n");
	            sqlstr.append("  WHERE JOB_DIV = 'P'                                 \n");

	            search.setStatement(sqlstr);
	            valueObject = RowSetUtility.getValueObject(search.execute().getRow(0));
	            valueObject.set("SEND_EMAIL",readClob(valueObject.getObject("SEND_EMAIL")));        
	        }
	        catch(WAFSQLException se) {
	        	super.doException(se);
	        	this.storage.setSuccess(false);
	        	this.storage.setMessage(se.getMessage());
	        	storage.setDetailMessage(se);
	        }
	        finally {

	            if( valueObject == null ) valueObject = new ValueObject();
	            return valueObject;
	        }
	    }
	 private String readClob(Object object) throws Exception {

	        if( object == null ) {
	        
	            return "";
	        }

	        Clob clob = (Clob)object;
	    /*  -- 2006.09.14 changed by SMH  
	        Reader reader = clob.getCharacterStream();

	        int size = (int)clob.length();
	        char[] clobData = new char[size];
	        reader.read(clobData, 0, size);
	        reader.close();

	        StringBuffer temp = new StringBuffer(); 
	        temp.append(clobData);                

	        return temp.toString();
	    */    
	        return getClob(clob);
	    }
	    
	    /**
	     * Clob 데이터 유형을 String으로 변경하여 반환한다.
	     * @param clob
	     * @return
	     * @throws Exception
	     */
	    public static String getClob(Clob clob) throws Exception{
	        String strClob = "";
	        Reader reader = null;
	        try {
	            if(clob != null){              
	                reader = clob.getCharacterStream();
	                char[] buffer = new char[1024];
	                int length = 0;
	                while( (length = reader.read(buffer)) != -1 ) {                  
	                    for( int j=0; j<length; j++ ) {
	                        strClob = strClob+buffer[j];
	                    }
	                }
	            }        
	        }
	        finally {
	            if(reader != null) reader.close();                    
	        }   
	        return strClob;
	    }

		/**
		 * EMAIL/SMS 사용 권한 확인
		 * @param Params
		 * @return
		 */
		private ValueObject getAuthReqeustValueObject(ValueObject Params) {

			Search search = new SearchImpl();
	        RowSet rowSet = new RowSetImpl();
	        ValueObject valueobject = null;
	     //   String sqlstr = "";
	        StrBuffer sqlstr = new StrBuffer();
	        try{
	            sqlstr.append("       SELECT DECODE(COUNT(*),'0','N','Y') AUTH_CHK           				\n");
			    sqlstr.append("          FROM PUB0120T  C,                                   				\n");
			    sqlstr.append("               PUB0350T  D,                                   				\n");
			    sqlstr.append("               PUB0340T  E                                    				\n");
			    sqlstr.append("         WHERE C.INACTIVE_DATE IS NULL										\n");
			    sqlstr.append("           AND UPPER (C.ACCT_ID) = '"+storage.getUser().get("ACCT_ID")+"'	\n");
			    sqlstr.append("           AND C.ACCT_ID = D.ACCT_ID(+)										\n");
				sqlstr.append("           AND D.RIGHT_ID = E.RIGHT_ID(+)									\n");
				sqlstr.append("           AND E.RIGHT_NAME IN('QUOT')										\n");
	        	search.setStatement(sqlstr.toString());
	            rowSet = search.execute();
	            
	            if( rowSet != null && rowSet.size() > 0 ){
	            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
	            }
	            	
	        }
	        catch(WAFSQLException se) {
	        	super.doException(se);
	            this.storage.setSuccess(false);
	            this.storage.setMessage(se.getMessage());
	            storage.setDetailMessage(se);
	        }
	        finally {

	        	if( valueobject == null ) valueobject = new ValueObject();
	            return valueobject;
	        }
		}
}