/**
 * @(#)file    OutSupplierListBiz.java
 * @(#)author  이성민
 * @(#)version 1.0
 *
 */
package kr.co.udapsoft.ebid.buyer.supplier.outside.biz;


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

import kr.co.hsnc.common.base.WAFLogger;
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.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 com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;
import oracle.toplink.internal.ox.StrBuffer;






public class OutSupplierListBiz extends Biz {

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

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

		
		
		WAFLogger.debug("event==============>" + storage.getEvent());
		
		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);
		    RowSet materialKindCodeLv1 = getLv1MaterialKindCode();
		    
		    storage.addRowSet("SuppStatusRowSet", suppStatusRowSet);
            storage.addRowSet("materialKindCodeLv1", materialKindCodeLv1);
            
		}
		else if(storage.compareEvent("GET_LIST") || storage.compareEvent("GET_LIST_READ"))
        {
            params.set("sheet1", getList(params));
            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("EMAIL_SEND")){
        	boolean success = sendData(params);
        	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("QUOT_EMAIL_SEND")){
        	boolean success = quotSendData(params);
        	storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }else if(storage.compareEvent("QUOT_SMS_SEND")){
        	boolean success = quotSendSmsData(params);
        	storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
	}
	
	
	
	/**
     * 목록을 검색하여 리턴
     * @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("                , NVL(H.ADD_MANG_NO, A.CORP_REG_NO) CORP_REG_NO                                                               \n")
                    .append("                , A.CORP_KOR                                                                                                  \n")                                                                                                                                             
                    .append("                , A.REP_NAME                                                                                                  \n")                          
                    .append("                , B.USER_NAME                                                                                                 \n")                         
                    .append("                , B.EMAIL                                                                                                     \n")                         
                    .append("                , DECODE(A.CREDIT_GRADE, '', '', A.CREDIT_GRADE || '(' || CREDIT_CORP_CD || ')' ) CREDIT_GRADE                \n")                                                                                                                                                 
                    .append("                , CASE WHEN SUBSTR(CASH_GRADE,1,1) = 'A' THEN 'CR-1'                                                          \n")                           
                    .append("                       WHEN SUBSTR(CASH_GRADE,1,1) = 'B'  THEN 'CR-2'                                                         \n")                           
                    .append("                       WHEN SUBSTR(CASH_GRADE,1,1) = 'C' THEN  DECODE(SUBSTR(CASH_GRADE,1,2), 'C-', 'CR-4', 'CR-3')           \n")                           
                    .append("                       WHEN SUBSTR(CASH_GRADE,1,1) = 'D'  THEN 'CR-5'                                                         \n")                           
                    .append("                       WHEN SUBSTR(CASH_GRADE,1,1) = 'E'  THEN 'CR-6'                                                         \n")                           
                    .append("                       ELSE                                                                                                   \n")                           
                    .append("                            CASE WHEN INSTR(CASH_GRADE,'CR-') = 2  THEN SUBSTR(CASH_GRADE, 2,4)                               \n")                           
                    .append("                                 ELSE CASH_GRADE                                                                              \n")                           
                    .append("                       END                                                                                                    \n")                                                             
                    .append("                  END  CASH_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') REG_REQ_DT                                                               \n")                                                                                                      
                    .append("                , TO_CHAR(A.CRE_DATE, 'YYYY-MM-DD') AS CRE_DATE                                                                \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("                , F.ITEM_CD CODE_1                                                                                                  \n")
                    .append("                , E.ITEM_CD CODE_2                                                                                                  \n")
                    .append("                , D.ITEM_CD CODE_3                                                                                                  \n")
                    .append("                , F.ITEM_NAME  GUBUN_1                                                                                              \n")
                    .append("                , E.ITEM_NAME GUBUN_2                                                                                               \n")
                    .append("                , D.ITEM_NAME GUBUN_3                                                                                               \n")
//                    .append("                , CHK_CREDIT(C.CORP_ID) EVAL_CMP                                                                                    \n")
                    .append("                , NVL(I.EVAL_CMP,'X') AS EVAL_CMP                                                                                    \n")	//2020.06.10 최훈석 수정
                    .append("                , DECODE(A.IN_OUT_DIV, 'O', '15', DECODE(SUBSTR(C.OUT_ITEM_CD,1,2), '06', '14', '07', '14', '08', '14', '13')) DOC_CD     \n")  //2015.10.13 07->15
                    .append("                , G.APPR_TOT_RATE                                                                                                  \n")
                    .append("                , G.ARR_ID                                                                                                         \n")
                    .append("                , G.APPR_DY                                                                                                        \n")
                    .append("                , G.YEAR_STD                                                                                                       \n")
                    .append("                , CASE WHEN DOC_CD = '13' THEN CASE WHEN BID_AMT < 1   THEN TO_CHAR(1,   '99,990.9') || ' 억' ELSE TO_CHAR(G.BID_AMT, '99,990.9') || ' 억' END   \n")//2018.08.22 입찰상한액
                    .append("                       WHEN DOC_CD = '14' THEN CASE WHEN BID_AMT < 0.1 THEN TO_CHAR(0.1, '99,990.9') || ' 억' ELSE TO_CHAR(G.BID_AMT, '99,990.9') || ' 억' END   \n")
                    .append("                       ELSE TO_CHAR(G.BID_AMT, '99,990.9')                                                                         \n")
                    .append("                  END AS BID_AMT                                                                                                   \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("                , TO_CHAR(NVL(NVL(A.REG_DT,A.REG_REQ_DT),A.CRE_DATE), '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("                , (SELECT MAX(TO_CHAR(TO_DATE(EXPIRE_DATE), 'YYYY-MM-DD')) FROM SUPP_INFO_V WHERE CORP_REG_NO = A.CORP_REG_NO AND EXPIRE_DATE >= TO_CHAR (SYSDATE, 'YYYYMMDD')AND CREDIT_CORP_CD = CHK_CREDIT(A.CORP_ID) ) CREDIT_DATE   \n")	
                    .append("                , (SELECT MAX(TO_CHAR(TO_DATE(EXPIRE_DATE), 'YYYY-MM-DD')) FROM SUPP_INFO_V WHERE CORP_REG_NO = A.CORP_REG_NO AND EXPIRE_DATE >= TO_CHAR (SYSDATE, 'YYYYMMDD')AND CREDIT_CORP_CD = NVL(I.EVAL_CMP,'X') ) CREDIT_DATE   \n")	//2020.06.10 최훈석 수정
                    .append("                , A.CORP_TEL, A.CORP_FAX                                                                                           \n")
                    .append("                , NVL((SELECT DISTINCT COUNT(*) OVER(PARTITION BY BID2.VENDOR_CD)                                               \n")
                    .append("                     FROM BID_MASTER BID1, BID_SUPP BID2                                                                           \n")
                    .append("                    WHERE BID1.DEMANDED_KIND = 'O' AND BID1.BID_NO = BID2.BID_NO AND BID1.BID_DEG = BID2.BID_DEG                   \n")
                    .append("                      AND BID2.VENDOR_CD = A.CORP_ID                                                                               \n")
                    .append("                   GROUP BY BID1.BID_NO, BID2.VENDOR_CD),0) BID_CNT                                                                         \n")
                    .append("                , (SELECT COUNT(*)																                         \n");
                  if (storage.compareEvent("GET_LIST_READ")){
                	  sqlStr.append("                                   || ':'|| listagg(DEPT_NAME, ',') within group(order by DEPT_NAME)		    				 \n");
	                }
                  sqlStr.append("                     FROM SUB2000T SUB1                                                                                         \n")
	                    .append("                         ,CONT_OUT_SUPP SUB2                                                                                    \n")
	                    .append("                    WHERE SUB1.SUB_CONT_MOD_ID = SUB2.SUB_CONT_MOD_ID                                                           \n")
	                    .append("                      AND SUB1.MOD_NO = 0                                                                                       \n")
	                    .append("                      AND SUB2.SIGN_SEQ = 2                                                                                     \n")
	                    .append("                      AND SUB2.CORP_ID = A.CORP_ID ) CONT_CNT                                                                   \n");
              sqlStr.append("                FROM SUPP_INFO A, SUPP_CHARGE B, SUPP_OUT_ITEM C, OUT_ITEM D, OUT_ITEM E, OUT_ITEM F                                \n")
                    .append("                ,   ( SELECT A.DOC_CD                                                                                               \n")
                    .append("                    		, A.CORP_ID                                                                                              \n")
                    .append("                    		, A.APPR_TOT_RATE                                                                                        \n")
                    .append("                   		, A.ARR_ID                                                                                               \n")
                    .append("                   		, A.APPR_DY                                                                                              \n")
                    .append("                   		, A.YEAR_STD                                                                                             \n")
                    .append("                   		, ROUND((C.PYEAR_CONT_CNT * NVL(C.CUR_SALES_AMT,0))/100000000, 1) BID_AMT                                \n")//2018.08.22 매출액
                    .append("                    	FROM SUPP_APPR  A,                                                                                           \n")
                    .append("                     		( SELECT  CORP_ID                                                                                        \n")
                    .append("                    				, MAX(ARR_ID ) AS ARR_ID                                                                         \n")
                    .append("                      			FROM SUPP_APPR                                                                                       \n")
                    .append("                       	  WHERE DOC_CD in ('11','13','14','15')                                                                  \n")  //2015.07.27 평가표 분리. 
                    .append("                        	  GROUP BY CORP_ID                                                                                       \n")
                    .append("                       	) B,                                                                                                     \n")
                    .append("                           SUPP_OUT_APPR_DETAIL C                                                                                   \n") //2018.08.22 입찰상한액표기 
                    .append("                    	WHERE A.CORP_ID = B.CORP_ID                                                                                  \n")
                    .append("                         AND A.ARR_ID = B.ARR_ID                                                                                    \n")
                    .append("                    	  AND A.CORP_ID = C.CORP_ID(+)                                                                               \n")
                    .append("                         AND A.ARR_ID = C.ARR_ID(+)                                                                                 \n")
                    .append("                     )    G, SUPP_O_INFO H                                                                                          \n")
                    .append(",(SELECT RESNO,                                                    \n")	//2020.06.10 최훈석 추가
					.append("                               CASE                                \n")
					.append("                                   WHEN E_CNT > 0 THEN 'E'         \n")
					.append("                                   WHEN N_CNT > 0 THEN 'N'         \n")
					.append("                                   WHEN M_CNT > 0 THEN 'M'         \n")
					.append("                                   WHEN K_CNT > 0 THEN 'K'         \n")
					.append("                                   WHEN S_CNT > 0 THEN 'S'         \n")
					.append("                                   ELSE 'X'                        \n")
					.append("                               END    EVAL_CMP                     \n")
					.append("                          FROM (                                   \n")       
					.append("                        SELECT RESNO,                              \n")
					.append("                               SUM (E_CNT)     E_CNT,              \n")
					.append("                               SUM (S_CNT)     S_CNT,              \n")
					.append("                               SUM (N_CNT)     N_CNT,              \n")
					.append("                               SUM (K_CNT)     K_CNT,              \n")
					.append("                               SUM (M_CNT)     M_CNT               \n")
					.append("                          FROM (                                   \n")                         
					.append("                                SELECT A.RESNO,                    \n")
					.append("                                               DECODE (A.EVAL_CMP, 'E', 1, 0)     E_CNT,                           \n")
					.append("                                               DECODE (A.EVAL_CMP, 'S', 1, 0)     S_CNT,                           \n")
					.append("                                               DECODE (A.EVAL_CMP, 'N', 1, 0)     N_CNT,                           \n")
					.append("                                               DECODE (A.EVAL_CMP, 'K', 1, 0)     K_CNT,                           \n")
					.append("                                               DECODE (A.EVAL_CMP, 'M', 1, 0)     M_CNT                            \n")
					.append("                                          FROM DCC_PACKET01 A                                                      \n")
					.append("                                             , (SELECT RESNO, NVL(MAX (SUBSTR (ESETDT, 0, 4)),'N') AS ESETDT       \n")
					.append("                                                  FROM DCC_PACKET01                                                \n")
					.append("                                              GROUP BY RESNO) B                                                    \n")
					.append("                                         WHERE TO_DATE (A.SUVDT, 'YYYYMMDD') + 365 > SYSDATE                       \n")
					.append("                                           AND A.RESNO = B.RESNO                                                   \n")
					.append("                                           AND NVL (SUBSTR (A.ESETDT, 0, 4), 'N') = B.ESETDT)                      \n")        
					.append("                        GROUP BY RESNO) ) I                                                                        \n")
                    .append("             WHERE A.CORP_ID = B.CORP_ID                                                                                            \n")
                    .append("                AND A.CORP_ID = C.CORP_ID                                                                                           \n")
                    .append("                AND C.OUT_ITEM_CD = D.ITEM_CD                                                                                       \n")
                    .append("                AND D.PARENT_LEVEL = E.ITEM_CD                                                                                      \n")
                    .append("                AND E.PARENT_LEVEL = F.ITEM_CD                                                                                      \n")
                    .append("                AND A.CORP_ID = G.CORP_ID(+)                                                                                        \n")
                    .append("                AND A.CORP_ID = H.CORP_ID(+)                                                                                        \n")
                    .append("                AND A.CORP_REG_NO = I.RESNO(+)                                                                                        \n")
                    .append("                AND DEFAULT_YN = 'Y'                                                                                                \n")
                    .append("                AND JOB_DIV = 'O'                                                                                                   \n")
                    .append("                AND (A.STATUS != '00' OR A.STATUS != '')                                                                            \n");
            if(params.get("chk_all").equals("false"))                                                                                                           
            {                                                                                                                                                   
            	sqlStr.append("                AND C.REPRE_YN = 'Y'                                                                                              \n");
            }                                                                                                                                                    
            if(!params.get("corp_no").equals(""))                                                                                                                
            {                                                                                                                                                    
                sqlStr.append("         AND A.CORP_REG_NO LIKE '%' || ? || '%'                                                                                   \n");
                search.addParameter(params.get("corp_no").trim());                                                                                              
            }                                                                                                                                                   
            if(!params.get("nation_cd").equals(""))                                                                                                             
            {                                                                                                                                                   
            	sqlStr.append("         AND A.NATION_CD = ?                                                                                                      \n");
            	search.addParameter(params.get("nation_cd").trim());                                                                                            
            }                                                                                                                                                   
            if(!params.get("corp_name").equals(""))                                                                                                             
            {                                                                                                                                                   
                sqlStr.append("         AND replace(UPPER(A.CORP_KOR), ' ', '') LIKE replace('%' || ? || '%', ' ', '')                                           \n");
                search.addParameter(params.get("corp_name").trim().toUpperCase());                                                                              
            }
            if(!params.get("chk_all_status").equals("true"))	//2020.06.10 최훈석 추가                                                                                                           
            {                                                                                                                                                   
            	if(!params.get("corp_status").equals(""))                                                                                                           
                {                                                                                                                                                   
                	if (params.get("corp_status").equals("999")){                                                                                                   
                		sqlStr.append("         AND (A.STATUS = '03' OR A.STATUS = '02')  AND  USE_END_DY < TO_CHAR(SYSDATE, 'YYYYMMDD')                             \n");
                	}                                                                                                                                               
                	else{  
                		if(params.get("corp_status").equals("all"))	
                			sqlStr.append("   AND A.STATUS in ('01','02','03')				\n");
                		else{
	                		sqlStr.append("         AND A.STATUS = ?                                                                                              \n");
	                        search.addParameter(params.get("corp_status").trim().toString());
                		}
                	}
                    
                }
            }            
            if(!params.get("material_Kind_Code_Lv1").equals(""))
            {
                sqlStr.append("         AND F.ITEM_CD = ?        \n");
                search.addParameter(params.get("material_Kind_Code_Lv1").trim());
            }
            if(!params.get("material_Kind_Code_Lv2").equals(""))
            {
                sqlStr.append("         AND E.ITEM_CD = ?        \n");
                search.addParameter(params.get("material_Kind_Code_Lv2").trim());
            }
            if(!params.get("material_Kind_Code_Lv3").equals(""))
            {
                sqlStr.append("         AND D.ITEM_CD = ?        \n");
                search.addParameter(params.get("material_Kind_Code_Lv3").trim().toString());
            }
            //	국내/해외
            if(!params.get("in_out_gubun").equals(""))
            {
                sqlStr.append("         AND A.IN_OUT_DIV = ?        \n");
                search.addParameter(params.get("in_out_gubun").trim().toString());
            }
            sqlStr.append("        ORDER BY CORP_ID DESC      \n");
            
            search.setStatement(sqlStr.toString());
            
            WAFLogger.debug(search.getParameters());
            WAFLogger.debug(sqlStr.toString());
              
            /*
            //Paging 처리를 위한 Helper Object 생성
            super.createPageAnchorInfo();
            //한페이지에 가져올 목록의 갯수 지정(미지정시  waf.properties 파일의 기본값을 사용)
            //super.anchor.range = 200;
            //Paging 처리에 사용될 Parameter를  세팅
            super.anchor.setParams(this.params);
            //검색할  SQL 세팅 : SQL은 Paging처리를 하지 않고 작성
            super.anchor.setSQL(sqlStr.toString());
            //전체 Record 숫자를 조회
            super.anchor.doTotalCountSQL(); // 전체 숫자를 조회
            
            //Paging처리가 된 SQL을 Helper Object에서 가져옴
            String temp = super.anchor.getPagingSQL();
            
            search.setStatement(temp);
            */
            
            
            rowSet = search.execute();
            
        }
        catch (Exception se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            WAFLogger.debug("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();
        
        /*
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      ITEM_CD  MATERIALGROUP               \n");
            sql.append("    , ITEM_NAME MATERIALGROUPNAME             \n");
            sql.append("    FROM OUT_ITEM          \n");
            sql.append("    WHERE DEPTH = '1'           \n");            
            sql.append("    ORDER BY ITEM_CD          \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("      ITEM_CD  MATERIALGROUP                 \n");
            sql.append("    , ITEM_NAME MATERIALGROUPNAME             \n");
            sql.append("    FROM OUT_ITEM          \n");
            sql.append("    WHERE DEPTH = '2'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
            sql.append("    ORDER BY ITEM_CD          \n");
            
            search.setStatement(sql);
            
            WAFLogger.debug(sql.toString());
            WAFLogger.debug(search.getParameters());
            
            
            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;
    }
    
    // 자재분류 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("      ITEM_CD MATERIALGROUP                 \n");
            sql.append("    , ITEM_NAME MATERIALGROUPNAME             \n");
            sql.append("    FROM OUT_ITEM          \n");
            sql.append("    WHERE DEPTH = '3'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
            sql.append("    ORDER BY ITEM_CD          \n");
            
            search.setStatement(sql);
            
            WAFLogger.debug(sql.toString());
            WAFLogger.debug(search.getParameters());
            
            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;
    }
    
    
    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 boolean test_all(ValueObject params) {
    	try {
    		conn.begin();
    		String[] status  		= super.gets("sheet1.STATUS");
			String[] corpId  		= super.gets("sheet1.CORP_ID");
			String[] corpNm  		= super.gets("sheet1.CORP_KOR");
			String   msg = "";
			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);
            //System.out.println("test_all  " + 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);
            //System.out.println("test_all_sub  " +e);
            return e.getMessage();
        }
        finally {
        	try {
	            if(cs !=null ) cs.close();
	        }
	        catch (SQLException e) {
	            e.printStackTrace();
	        }
        	
        }
	}
    /**
	 * Persistent 를 이용한 Save 처리
	 * @param params
	 * @return
	 * * 입력받은 프로그램 등록 정보를 등록합니다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	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();
        }
	}
	
	private boolean quotSendData(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 = getQuotContent().get("TITLE");
			     		//StringBuffer mailtitlein = new StringBuffer();
			     		StringBuffer mailContent = new StringBuffer();
			     		String content = getQuotContent().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();
        }
	}
	
	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 = 'O'                                 \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;
	        }
	    }
	 
	 public ValueObject getQuotContent() 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 = 'Q'                                 \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;
	        }
	    }
	 
	 /**
	     * Clob Object에서 데이터를 읽어 리턴한다.
	     * @param object
	     * @return
	     * @throws Exception
	     */
	    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;
	    }
    
	    private boolean quotSendSmsData(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 sendSms = getEmail(corpId[i]).get("hp1")+getEmail(corpId[i]).get("hp2")+getEmail(corpId[i]).get("hp3");	//수신번호
				     		StringBuffer mailContent = new StringBuffer();
				     		String content = getQuotSmsContent().get("SEND_SMS");
				            mailContent.append(content);
				            MessageSend messageSend = new MessageSend(storage);  //mailtitle.toString() mailtitlein.toString()
							messageSend.sendSMS(sendSms,"", "99999", content);
							
				            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();
	        }
		}
	    
	    public ValueObject getQuotSmsContent() throws Exception {

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

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

	            search.setStatement(sqlstr);
	            valueObject = RowSetUtility.getValueObject(search.execute().getRow(0));
	            valueObject.set("SEND_SMS",readClob(valueObject.getObject("SEND_SMS")));        
	        }
	        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;
	        }
	    }
	    
	    /**
		 * 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;
	        }
		}
}