/**
 * @(#)file    PuSupplierBiz.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.outside.biz;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

import kr.co.hsnc.common.base.WAFLogger;
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.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.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;






public class OutSupplierBiz extends Biz {

	public OutSupplierBiz(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("GET_DETAIL"))
		{
//	        RowSet acctTypeRowSet = common.getAcctTypeUseRowSet(storage.getUser().get("ACCT_TYPE_CODE"));
//	        storage.addRowSet("AcctTypeRowSet", acctTypeRowSet);
		    
		    
		    ValueObject basicVo = new ValueObject();
		    ValueObject corpInfoVo = new ValueObject();
            
            basicVo = getSuppJoinRequestList(params);
            corpInfoVo = getCorpInfoList(params);
            
            RowSet rowset = null;
            rowset = getOutMainList(params);
            
            
            RowSet rowset2 = null;
            rowset2 = getApprYear(params);
            
            RowSet getFileListRowSet = getFileListRowSet("TERMS_AGREE_INFO_"+basicVo.get("corp_id"), "");
            RowSet rowSet = getOutLicMaster_searchCombo();
            storage.addRowSet("licMasterList", rowSet);
            storage.addValueObject("corpInfoVo", corpInfoVo);
            storage.addValueObject("InfoBaseList", basicVo);
            storage.addValueObject("TERMS_AGREE_INFO", getfilelist("TERMS_AGREE_INFO",basicVo.get("corp_id"),"TERMS_AGREE_INFO"));
            storage.addRowSet("out_main", rowset);
            storage.addRowSet("getApprYearList", rowset2);
            storage.addRowSet("getFileListRowSet", getFileListRowSet);
            storage.addRowSet("g_base_year",getBaseYear(params));
            
		}
		else if(storage.compareEvent("GET_OUT_DETAIL"))
        {
            String yyyy = (new java.text.SimpleDateFormat ("yyyy")).format (new java.util.Date());
            params.set("p_base_year", Integer.parseInt(yyyy) -1);
            
            ValueObject basicVo = new ValueObject();
            ValueObject corpInfoVo = new ValueObject();
            RowSet nationRowSet = getNationList(params);        
            
            basicVo = getSuppJoinRequestList(params);
            corpInfoVo = getCorpInfoList(params);
            
            RowSet rowset = null;
            rowset = getOutMainList(params);
            
            
            RowSet rowset2 = null;
            rowset2 = getApprYear(params);
            
            RowSet rowSet3 = getOutLicMaster_searchCombo();
            storage.addRowSet("licMasterList", rowSet3);
            storage.addRowSet("out_main", rowset);
            storage.addRowSet("getApprYearList", rowset2);
            storage.addValueObject("corpInfoVo", corpInfoVo);
            storage.addValueObject("InfoBaseList", basicVo);
            RowSet getFileListRowSet = getFileListRowSet("TERMS_AGREE_INFO_"+params.get("p_corp_id"), "");
            storage.addRowSet("nationRowSet", nationRowSet);
            storage.addValueObject("TERMS_AGREE_INFO", getfilelist("TERMS_AGREE_INFO",basicVo.get("corp_id"),"TERMS_AGREE_INFO"));
            storage.addRowSet("getFileListRowSet", getFileListRowSet);
            storage.addRowSet("g_base_year",getBaseYear(params));
        }
		else if(storage.compareEvent("REG_DATA"))
        {
            ValueObject corp_id = getCorp_id(super.conn.getConnection()); 
            params.set("p_corp_id", corp_id.get("SEQ"));
            
            String yyyy = (new java.text.SimpleDateFormat ("yyyy")).format (new java.util.Date());
            params.set("p_base_year", Integer.parseInt(yyyy) -1);
            
            ValueObject basicVo = new ValueObject();
            ValueObject corpInfoVo = new ValueObject();
            
            basicVo = getSuppJoinRequestList(params);
            corpInfoVo = getCorpInfoList(params);
            
            
            RowSet nationRowSet = getNationList(params);
            
            
            RowSet rowset = null;
            rowset = getOutMainList(params);
            
            
            RowSet rowset2 = null;
            rowset2 = getApprYear(params);
            RowSet getFileListRowSet = getFileListRowSet("TERMS_AGREE_INFO_"+params.get("p_corp_id"), "");
            RowSet rowSet3 = getOutLicMaster_searchCombo();
            storage.addRowSet("licMasterList", rowSet3);
            storage.addValueObject("corpInfoVo", corpInfoVo);
            storage.addValueObject("InfoBaseList", basicVo);
            storage.addValueObject("TERMS_AGREE_INFO", getfilelist("TERMS_AGREE_INFO",params.get("p_corp_id"),"TERMS_AGREE_INFO"));
            storage.addRowSet("out_main", rowset);
            storage.addRowSet("getApprYearList", rowset2);
            storage.addRowSet("nationRowSet", nationRowSet);
            storage.addRowSet("getFileListRowSet", getFileListRowSet);
            storage.addRowSet("g_base_year",getBaseYear(params));
        }
		else if(storage.compareEvent("GET_LIST"))
        {
            RowSet rowset = null;
            
            rowset = getList(params);
            
            
            //getList2
            
            Document xmlData = getXml(rowset);
//            WAFLogger.debug(xmlData);
            
            XMLOutputter xmlOutputter = new XMLOutputter();
            xmlOutputter.setFormat(Format.getRawFormat());
            
//            WAFLogger.debug(xmlOutputter.outputString(xmlData));
            
            storage.addObject("ibsheet7Data", xmlData);
        }
		else if(storage.compareEvent("GET_MY_LIST"))
        {
		    
			WAFLogger.debug("-=---------------------------------------------------------");
            
            params.set("sheet2", getList2(params));
            params.set("sheet3", geApprtList(params));
            
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		else if(storage.compareEvent("GET_CHARGE_LIST"))
        {
		    params.set("sheet4", getSuppCarge(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		else if(storage.compareEvent("GET_CON_RESULT_LIST"))
        {
            params.set("sheet5", getContResult(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		else if(storage.compareEvent("GET_CON_ABILLTY_LIST"))
        {
            params.set("sheet6", getConAillty(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		else if(storage.compareEvent("GET_TECH_MAN_LIST"))
        {
            params.set("sheet7", getSuppTechMan(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		else if(storage.compareEvent("GET_CRET_LIST"))
        {
            params.set("sheet8", getCertList(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		//2015.3.11 입찰이력 추가
		else if(storage.compareEvent("GET_BID_LIST"))
        {
            params.set("sheet9", getBidList(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
	}
	
	
	
	/**
     * rowset 데이터를 ibsheet에 맞는 xml 형식으로 변환한다.(트리메뉴 사용시)
     * @param rowset
     * @return
     */
    public Document getXml(RowSet rowset)
    {
        
          Document doc = new Document();  
            
          Element root = new Element("Grid");
          
          Element pack = new Element("DATA");
          
          Element pack_tr = new Element("TR");
          
          Element pack_td = new Element("TD");
          
        try {

              root.addContent(pack);//root element 의 하위 element 를 만들기
              pack.addContent(pack_tr);
              pack_tr.addContent(pack_td);
              
              for(int i=0; i < rowset.size(); i++)
              {
                  if( i != 0)
                  {
                      pack_tr = new Element("TR");
                      pack.addContent(pack_tr);
                  }
                  
                  pack_tr.setAttribute("LEVEL", rowset.getRow(i).get("MATERIAL_LEVEL"));
                  
                  if( i != 0)
                  {
                      pack_td = new Element("TD");
                      pack_tr.addContent(pack_td);
                  }
                 
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("MATERIALGROUPNAME") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("GUBUN_1") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("GUBUN_2") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("GUBUN_3") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("MATERIAL_LEVEL") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("MATERIALGROUP") + "]]>");
                  
                  
//                WAFLogger.debug(rowset.getRow(i).get("GUBUN_1") + "/" + rowset.getRow(i).get("GUBUN_2") + "/" + rowset.getRow(i).get("GUBUN_3"));
              }
              
              doc.setRootElement(root);
              
              
                  
              } catch (Exception e) {                                         
                  System.err.println(e);                                        
              }
        return doc;
        
    }
    
    
    
    /**
     * 업체별 평가년도 가져오기
     * 
     */
     private RowSet getApprYear(ValueObject Params) {
         
         
         Search search = new SearchImpl();
         RowSet rowSet = new RowSetImpl();
         StringBuffer sqlStr = new StringBuffer();
         
         try {
             sqlStr.append("     SELECT SUBSTR(APPR_DY, 0, 4) APPR_DY, ARR_ID, DOC_CD , YEAR_STD FROM          \n")  //2018.04.20 기준년도추가
                    .append("             SUPP_APPR                                                    \n")
                    .append("             WHERE CORP_ID = ? and doc_cd in ('11', '13', '14', '15')         \n"); // '15'
       
             search.setStatement(sqlStr.toString());
             search.addParameter(params.get("p_corp_id").trim());
             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;
         }
     }
     
     
     
    
    /**
     * 사용자 목록을 검색하여 리턴
     * @param Params
     * @return
     */
    private RowSet getList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("     SELECT                                                                                                                  \n")
                    .append("        MATERIAL_LEVEL,    MATERIALGROUPNAME  ,   MATERIALGROUP,                                   \n")
                    .append("        LAST_VALUE(GUBUN_1 IGNORE NULLS) OVER(ORDER BY MATERIALGROUP) GUBUN_1,         \n")
                    .append("        LAST_VALUE(GUBUN_2 IGNORE NULLS) OVER(ORDER BY MATERIALGROUP) GUBUN_2,         \n")
                    .append("        LAST_VALUE(GUBUN_3 IGNORE NULLS) OVER(ORDER BY MATERIALGROUP) GUBUN_3          \n")
                    .append("        FROM                                                                                                                   \n")
                    .append("        (                                                                                                                          \n")
                    .append("            SELECT LEVEL-1 MATERIAL_LEVEL,    ITEM_CD AS MATERIALGROUP  , ITEM_NAME AS MATERIALGROUPNAME,              \n")  
                    .append("                        DECODE(LEVEL, '1', ITEM_NAME, '') GUBUN_1,                                 \n")
                    .append("                        DECODE(LEVEL, '2', ITEM_NAME, '') GUBUN_2,                                 \n")
                    .append("                        DECODE(LEVEL, '3', ITEM_NAME, '') GUBUN_3                                  \n")
                    .append("                        FROM(                                                                                                  \n")                                                                                           
                    .append("                                SELECT ITEM_CD,                                                                     \n")                                                       
                    .append("                                           KIND_BIG,                                                                 \n")                      
                    .append("                                           KIND_MID,                                                                   \n")
                    .append("                                           ITEM_NAME,                                                                  \n")
                    .append("                                           PARENT_LEVEL,                                                          \n")
                    .append("                                           DEPTH                                                                      \n")
                    .append("                                       FROM OUT_ITEM                                                      \n")
                    .append("                                )                                                                                                  \n")
                    .append("                 START WITH PARENT_LEVEL = 'S'                                                                      \n")
                    .append("                     CONNECT BY PRIOR ITEM_CD = PARENT_LEVEL                                      \n")
                    .append("                     AND LEVEL <= 3                                                                                         \n")
                    .append("                     ORDER SIBLINGS BY PARENT_LEVEL ASC, ITEM_NAME ASC                         \n")
                    .append("            )                                                                                                                       \n");
            
                
            
            search.setStatement(sqlStr.toString());
            WAFLogger.debug(sqlStr.toString());
            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;
        }
    }
    
    
    
    /**
     * 등록한 구분 목록
     * @param Params
     * @return
     */
    private RowSet getList2(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("         SELECT                          \n")
                   .append("              D.ITEM_NAME               \n")
                   .append("            , D.ITEM_NAME KIND_BIG_NAME               \n")
                   .append("            , C.ITEM_CD               \n")
                   .append("            , C.ITEM_NAME KIND_MID_NAME               \n")
                   .append("            , B.ITEM_CD               \n")
                   .append("            , A.OUT_ITEM_CD ITEM_CODE              \n")
                   .append("            , A.CORP_ID              \n")
                   .append("            , B.ITEM_NAME KIND_SMALL_NAME              \n")
                   .append("            , DECODE(A.REPRE_YN, 'Y', '1', '0') MAIN_ITEM               \n")
                   .append("        FROM SUPP_OUT_ITEM A, OUT_ITEM B, OUT_ITEM C, OUT_ITEM D             \n")
                   .append("        WHERE A.OUT_ITEM_CD = B.ITEM_CD            \n")
                   .append("            AND B.PARENT_LEVEL = C.ITEM_CD            \n")
                   .append("            AND C.PARENT_LEVEL = D.ITEM_CD               \n")
                   .append("            AND A.CORP_ID = ?               \n");
            
            
            /*   
            //Paging 처리를 위한 Helper Object 생성
            super.createPageAnchorInfo();
            //한페이지에 가져올 목록의 갯수 지정(미지정시  waf.properties 파일의 기본값을 사용)
            super.anchor.range = 10000;
            //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(sqlStr.toString());
            search.addParameter(params.get("p_corp_id"));
            WAFLogger.debug(sqlStr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error("getList2=========>" + 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 RowSet geApprtList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("            SELECT     \n")
            .append("                     'Y' CREDIT_YN        \n")
            .append("                    ,TO_CHAR( TO_DATE(MAX(A.SUVDT), 'YYYY-MM-DD'), 'YYYY-MM-DD') CREDIT_STA_DY        \n")
            .append("                    , DECODE(A.EVAL_CMP, 'E', '이크레더블', 'S', '서울신용평가', 'N', '나이스디앤비', 'K', '한국기업데이터','M','나이스평가정보', '없음') CREDIT_CORP_CD     \n")
            .append("                    , LASTGRD2 CREDIT_GRADE        \n")
            .append("                    , DECISION CASH_GRADE      \n")
            .append("                    , EXPIRE_DATE CREDIT_END_DY     \n")
            .append("                    , CHK_CREDIT(C.CORP_ID) CREDIT_GUBUN     \n")
            .append("                    , A.EVAL_CMP GUBUN_CODE     \n")
            .append("                     FROM DCC_PACKET01 A, DCC_PACKET11 B, SUPP_INFO C     \n")
            .append("                    WHERE A.RESNO = ?      \n")
            .append("                    AND A.RESNO = B.RESNO(+)     \n")
            .append("                    AND A.SUVDT = B.SUVDT(+)     \n")
            .append("                    AND A.EVAL_CMP = B.EVAL_CMP(+)    \n")
            .append("                    AND A.RESNO = C.CORP_REG_NO   \n")
            .append("                    AND A.EXPIRE_DATE > TO_CHAR(SYSDATE, 'YYYYMMDD')   \n")
            .append("                    AND C.JOB_DIV = 'O'   \n")
            .append("                    GROUP BY A.EVAL_CMP, A.LASTGRD2, B.DECISION, A.EXPIRE_DATE, C.CORP_ID   \n");
                                               
            
            
            /*   
            //Paging 처리를 위한 Helper Object 생성
            super.createPageAnchorInfo();
            //한페이지에 가져올 목록의 갯수 지정(미지정시  waf.properties 파일의 기본값을 사용)
            super.anchor.range = 10000;
            //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(sqlStr.toString());
            search.addParameter(params.get("p_corp_reg_no"));
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error("getList2=========>" + 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;
        }
    }
    
    
    /**
     * 회사정보
     */
    public ValueObject getSuppJoinRequestList(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
        	sqlstr.append("SELECT A.CORP_REG_NO                             								\n")
               	  .append("       ,A.CORP_KOR                             									\n")
               	  .append("       ,A.REP_NAME                             									\n")
               	  .append("       ,A.CORP_DIV_CD                             								\n")
               	  .append("       ,TO_CHAR(A.REG_REQ_DT, 'YYYY-MM-DD') REG_REQ_DT                           \n")
               	  .append("       ,TO_CHAR(A.BAN_BID, 'YYYY-MM-DD') BAN_BID                                 \n")
               	  .append("       ,A.CRE_DATE                             									\n")
	              .append("       ,A.HEAD_CORP_REG_N                             							\n")
	              .append("       ,A.SUB_PLACE_NO                             								\n")
	              .append("       ,A.TAX_ADDR_CD                             								\n")
	              .append("       ,F_GET_CODE_NAME('SUPP_INFO_STATUS',A.STATUS) STATUS                      \n")
	              .append("       ,A.CORP_ID                             									\n")
	              .append("       ,A.COPR_NO                             									\n")
	              .append("       ,A.CORP_STS                             									\n")
	              .append("       ,A.BIZ_FORM                             									\n")
	              .append("       ,TO_CHAR(A.ESTA_DATE, 'YYYY-MM-DD') ESTA_DATE                             \n")
	              .append("       ,A.CORP_TEL                             									\n")
	              .append("       ,A.CORP_FAX                             									\n")
	              .append("       ,A.CORP_SCALE                             								\n")
	              .append("       ,A.POST_NO                             									\n")
	              .append("       ,A.ADDRESS                             									\n")
	              .append("       ,A.PLACE_POST_NO                             								\n")
	              .append("       ,A.PLACE_ADDRESS                             								\n")
	              .append("       ,A.CREDIT_CORP_CD                             							\n")
	              .append("       ,A.CREDIT_GRADE                             								\n")
	              .append("       ,A.CASH_GRADE                             								\n")
	              .append("       ,A.CREDIT_STA_DY                             								\n")
	              .append("       ,A.CREDIT_END_DY                             								\n")
	              .append("       ,A.FACTOR_CD                             									\n")
	              .append("       ,A.MFTR_DIV_CD                             								\n")
	              .append("       ,A.CREDIT_YN                             									\n")
	              .append("       ,B.APPR_DY                             									\n")
	              .append("       ,B.ITEM_STD_CD                             								\n")
	              .append("       ,B.ARR_ID                             									\n")
	              .append("       ,B.DOC_CD                             									\n")
	              .append("       ,B.YEAR_STD                             									\n")
	              .append("       ,A.STATUS STATUS_CODE                             						\n")
	              .append("       ,TO_CHAR(TO_DATE(A.USE_END_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD') USE_END_DY	\n")
	              .append("       ,TO_CHAR(A.REG_DT, 'YYYY-MM-DD') REG_DT                             		\n")
	              .append("       ,TO_CHAR(A.APPR_DT, 'YYYY-MM-DD') APPR_DT                             	\n")
	              .append("       ,TO_CHAR(A.CAN_DT, 'YYYY-MM-DD') CAN_DT                             		\n")
	              .append("       ,TO_CHAR(A.BAN_BID, 'YYYY-MM-DD') BAN_BID                             	\n") //2018.05.24 입찰금지추가 
	              .append("       ,\"COMMENT\"                             									\n")
	              .append("       ,O_COMMENT                                       						    \n") //2016.4.28 외주의견추가
	              .append("       ,MANAGE_NO                              									\n")
	              .append("       ,NATION_CD                              									\n")
	              .append("       ,F_GET_USER_NM(A.UPD_BY ) MANAGE_NAME                              		\n")
	              .append("       ,A.UPD_BY                              									\n")
	              .append("       ,A.BANK_CD                              								\n") //2019.04.24 은행코드,계좌 지정
                  .append("       ,A.BANK_NM                              								\n")
                  .append("       ,A.BANK_ACCT                              								\n")
                  .append("       ,A.HOMEPAGE_ADDR                          								\n");
                if(storage.compareEvent("GET_OUT_DETAIL"))
                {
                      sqlstr.append("                     ,  C.USER_NAME                             \n")
                            .append("                     ,  C.DIVISION                              \n")
                            .append("                     ,  C.POSITION                              \n")
                            .append("                     ,  C.EMAIL                                 \n")
                            .append("                     ,  C.TEL_NUM                               \n")
                            .append("                     ,  C.FAX_NUM                               \n")
                            .append("                     ,  C.HP1                                   \n")
                            .append("                     ,  C.HP2                                   \n")
                            .append("                     ,  C.HP3                                   \n")
                            .append("                     ,  D.BASE_YEAR O_BASE_YEAR                 \n")  //2015.10.14
                            .append("                     ,  D.ITEM                                  \n") 
                            .append("                     ,  D.G_LEVEL                               \n") 
                            .append("                     ,  D.ADDR                                  \n") 
                            .append("                     ,  D.AREA                                  \n") 
                            .append("                     ,  D.ADMIN_CNT1                            \n") 
                            .append("                     ,  D.ADMIN_CNT2                            \n")
                            .append("                     ,  D.ENG_CNT1                              \n") 
                            .append("                     ,  D.ENG_CNT2                              \n") 
                            .append("                     ,  D.NOR_CNT1                              \n") 
                            .append("                     ,  D.NOR_CNT2                              \n") 
                            .append("                     ,  D.ETC_CNT1                              \n") 
                            .append("                     ,  D.ETC_CNT2                              \n")
                            .append("                     ,  D.ADD_MANG_NO                           \n"); 
                }
          sqlstr.append("                FROM SUPP_INFO A,                                             \n")
                .append("                    (SELECT ARR_ID, SUBSTR(APPR_DY, 0, 4) APPR_DY, CORP_ID, ITEM_STD_CD, DOC_CD,YEAR_STD  FROM SUPP_APPR                 \n")
                .append("                      WHERE CORP_ID = ?                                                                                                  \n")
                .append("                        AND ARR_ID = (SELECT MAX(ARR_ID) FROM SUPP_APPR WHERE CORP_ID = ? AND DOC_CD IN ('11', '13', '14', '15'))        \n")
                .append("                        AND DOC_CD IN ('11', '13', '14', '15')                                                                           \n")
                .append("                            ) B                                                                                                          \n");
                 if(storage.compareEvent("GET_OUT_DETAIL"))
                {
                     sqlstr.append("          , SUPP_CHARGE C                                        \n");  
                     sqlstr.append("          , SUPP_O_INFO D                                        \n");  
                }
        sqlstr.append("                WHERE A.CORP_ID = B.CORP_ID(+)                            \n");
                if(storage.compareEvent("GET_OUT_DETAIL"))
                {
                     sqlstr.append("                AND A.CORP_ID = C.CORP_ID     \n");  
                     sqlstr.append("                AND C.DEFAULT_YN = 'Y'    \n"); 
                     sqlstr.append("                AND A.CORP_ID = D.CORP_ID(+)    \n"); 
                }
        sqlstr.append("                   AND A.CORP_ID = ?                             \n");        
                
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("p_corp_id").trim());
            search.addParameter(params.get("p_corp_id").trim());
            search.addParameter(params.get("p_corp_id").trim());
            
            
            WAFLogger.debug(sqlstr.toString());
            WAFLogger.debug(search.getParameters());
            
            
            rowSet = search.execute();
            
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
        	WAFLogger.error("getSuppJoinRequestList===>" + e);
            e.printStackTrace();
            throw e;
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    } 
    
    /**
     * 첨부파일
     * @param filegb
     * @param id
     * @param returnid
     * @return
     */
    private ValueObject getfilelist(String filegb,String id,String returnid){
        ValueObject vo = new ValueObject();
        RowSet rowSet = FileManager.getFileList(filegb, id);
        String str="";
            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";
                }
            }
            vo.set(returnid, str);
        return vo;
    }
    
    /**
     * 담당자 정보
     * @param Params
     * @return
     */
    private RowSet getSuppCarge(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("            SELECT     \n")
                   .append("                      CORP_ID        \n")
                   .append("                    , USER_ID        \n")
                   .append("                    , PASSWD     \n")
                   .append("                    , CHARGE_SEQ        \n")
                   .append("                    , USER_NAME        \n")
                   .append("                    , DIVISION        \n")
                   .append("                    , POSITION     \n")
                   .append("                    , TEL_NUM     \n")
                   .append("                    , FAX_NUM     \n")
                   .append("                    , HP1     \n")
                   .append("                    , HP2     \n")
                   .append("                    , HP3     \n")
                   .append("                    , EMAIL     \n")
                   .append("                    , NOTE     \n")
                   .append("                    , '-' BAR1     \n")
                   .append("                    , '-' BAR2     \n")
                   .append("                     FROM SUPP_CHARGE    \n")
                   .append("                    WHERE CORP_ID = ?      \n");
                                               
            
            
            /*   
            //Paging 처리를 위한 Helper Object 생성
            super.createPageAnchorInfo();
            //한페이지에 가져올 목록의 갯수 지정(미지정시  waf.properties 파일의 기본값을 사용)
            super.anchor.range = 10000;
            //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(sqlStr.toString());
            search.addParameter(params.get("p_corp_id"));
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());
            
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error("getList2=========>" + 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 RowSet getOutMainList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("     SELECT                                                                                                                  \n")
                    .append("        CORP_ID,    OUT_MAIN_CD                          \n")
                    .append("        FROM   SUPP_OUT_MAIN                                  \n")
                    .append("        WHERE CORP_ID = ?                                                                                                   \n");
            
                
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("p_corp_id"));
            WAFLogger.debug(sqlStr.toString());
            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 RowSet getContResult(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
                sqlStr.append("          SELECT BASE_YEAR-- 기준년도                     \n")
                        .append("                 ,CONT_NM -- 공사명                     \n")
                        .append("                 ,ORDR_ORG_NM -- 발주자명                     \n")
                        .append("                 ,ORDR_NM -- 도급자명                     \n")
                        .append("                 ,CONT_AMT-- 당해년도 계약금액                     \n")
                        .append("                 ,CONT_DY -- 계약년월                     \n")
                        .append("                 ,CONT_STA_DY -- 착공년월                     \n")
                        .append("                 ,CONT_END_DY -- 준공년월                     \n")
                        .append("                 ,CONT_SEQ                               \n")
                        .append("                 ,CREDIT_END_DY                               \n")
                        .append("                 ,CREDIT_YN                               \n")
                        .append("                 ,ETEC_YN                               \n")
                        .append("                 FROM SUPP_CONT_RESULT                     \n")
                        .append("                 WHERE CORP_ID = ?   ORDER BY BASE_YEAR DESC                   \n");
            
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("p_corp_id").trim());
            
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());
            
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error("getContResult=========>" + 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 RowSet getConAillty(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            
            sqlStr.append("         SELECT  BASE_YEAR       -- 기준년도                   \n")
                    .append("                    , LIC_CD       -- 면허명                   \n")
                    .append("                    , LIC_NM       -- 면허명                   \n")
                    .append("                    , LIC_NO       -- 면허번호                   \n")
                    .append("                    , CON_ABI_AMT      -- 시공능력평가액                   \n")
                    .append("                    , AREA_LANK        -- 지역순위                   \n")
                    .append("                    , AREA_ALL_LANK        -- 지역순위 전체                   \n")
                    .append("                    , NATION_LANK  -- 전국순위                   \n")
                    .append("                    , NATION_ALL_LANK  --전국순위전체                   \n")
                    .append("                    , CON_SEQ                            \n")
                    .append("                	 , CREDIT_END_DY                               \n")
                    .append("               		 , CREDIT_YN                               \n")
                    .append("               		 , CONREYR                               \n")
                    .append("                    FROM SUPP_CON_ABILLTY                   \n")
                    .append("                    WHERE CORP_ID = ?                         \n");
            search.addParameter(params.get("p_corp_id").trim());
            
            if(!params.get("G_BASE_YEAR").isEmpty()){
            	sqlStr.append("		AND		BASE_YEAR	=	?								");
            	search.addParameter(params.get("G_BASE_YEAR"));
            }
            
            sqlStr.append("                    ORDER BY BASE_YEAR DESC                                        \n");
            search.setStatement(sqlStr.toString());
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());
            
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error("getConAillty=========>" + 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 RowSet getSuppTechMan(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            
                sqlStr.append("         SELECT  NM      -- 이름                                      \n")
                        .append("                 ,BIRTHDAY     -- 생년월일                             \n")
                        .append("                 ,POSITION     -- 지위                             \n")
                        .append("                 ,QUAL_ITEM_CLASS       -- 자격종목 및 등급                             \n")
                        .append("                 ,REG_NO    -- 등록번호                             \n")
                        .append("                 ,FIELD        -- 분야                             \n")
                        .append("                 ,TECH_CLASS       -- 기술등급                             \n")
                        .append("                 ,SEQ                                    \n")
                        .append("                 ,CREDIT_END_DY                               \n")
                        .append("               	  ,CREDIT_YN                               \n")
                        .append("                 FROM SUPP_TECH_MAN                             \n")
                        .append("                 WHERE CORP_ID = ?                             \n");
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("p_corp_id").trim());
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());
            
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error("getSuppTechMan=========>" + 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 RowSet getCertList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            
                sqlStr.append("            SELECT CERT_DIV_CD -- 종류코드                             \n")
                        .append("                     , CERT_NO -- 인증번호                             \n")
                        .append("                     , CERT_CTNT -- 인증명                             \n")
                        .append("                     , CERT_SCOPE -- 인증범위                             \n")
                        .append("                     , CERT_GOV -- 인증기관                             \n")
                        .append("                     , EXP_STA_DY -- 취득일                             \n")
                        .append("                     , EXP_END_DY-- 만료일                             \n")
                        .append("                     , CERT_SEQ                                         \n")
                        .append("               		  , CREDIT_END_DY                               \n")
                        .append("               		  , CREDIT_YN                               \n")                        
                        .append("                     FROM SUPP_CERT                             \n")
                        .append("                     WHERE CORP_ID = ?                             \n");
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("p_corp_id"));
            
            
            WAFLogger.debug(sqlStr.toString());
            
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error("getCertList=========>" + 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;
        }
    }
    
    //2015.3.11 입찰이력추가 : 최종입찰기준, 순위는 금액기준(TBE적합여부 무관) 
    private RowSet getBidList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
        	sqlStr.append("SELECT TO_CHAR(B.BID_FROM_DT, 'YYYY-MM-DD') BID_DT                                                            \n")
                  .append("      ,A.RECM_DEPT_NM                                                                                         \n")
                  .append("      ,B.DEPT_NM                                                                                              \n")
                  .append("      ,B.DEMANDED_TITLE                                                                                       \n")
                  .append("       ,F_GET_D_CURRENCY(B.ACT_AMT, 0 ) AS ACT_AMT                                                            \n")
                  .append("         ,F_GET_D_CURRENCY(DECODE(B.CURRENCY_GB, 'KRW', A.ESTI_TOT_AMT, A.WON_ESTI_TOT_AMT), F_GET_CURRENCY_DIGIT(NVL(A.CURRENCY_GB,'KRW'))) AS ESTI_TOT_AMT                                                                                                   \n")
                  .append("        ,DECODE(NVL(B.ACT_AMT,0), 0, 0, ROUND( DECODE(B.CURRENCY_GB, 'KRW', A.ESTI_TOT_AMT, A.WON_ESTI_TOT_AMT)/B.ACT_AMT * 100, 2)) RATE                                                   \n") 
                  .append("      ,A.RK || '/' || A.CNT  RANK                                                                             \n")
                  .append("      ,F_GET_CODE_NAME('TBE_EVAL',C.EVAL_RESULT) TBE                                                          \n")
                  .append("      ,DECODE(A.SUCC_YN, 'Y', '낙찰', '') SUCC_YN                                                              \n")
                  .append("      ,B.BID_NO                                                                                               \n")
                  .append("      ,B.BID_DEG                                                                                              \n")
                  .append("      ,F_GET_CODE_NAME('BID_STATUS_CD',B.STATUS_CD) AS STATUS_NM                                              \n")
                  .append("  FROM (SELECT BID_NO, BID_DEG, ESTI_TOT_AMT, VENDOR_NO, VENDOR_CD, SUCC_YN, RK , RECM_DEPT_NM, CNT, CURRENCY_GB,WON_ESTI_TOT_AMT           \n")
                  .append("          FROM(                                                                                               \n")
                  .append("               SELECT A.BID_NO, A.BID_DEG, A.ESTI_TOT_AMT, A.VENDOR_NO, A.VENDOR_CD, A.SUCC_YN, CURRENCY_GB,WON_ESTI_TOT_AMT                \n")
                  .append("                     ,A.RECM_DEPT_NM, rank() OVER(PARTITION BY A.BID_NO, A.BID_DEG order by  A.ESTI_TOT_AMT Asc) as rk, C.CNT \n")
                  .append("                 FROM BID_SUPP A                                                                              \n")
                  //.append("                    ,(SELECT A.BID_NO, MAX(A.BID_DEG) BID_DEG                                                 \n")2018.06.28 최종->모든입찰 표기
                  //.append("                        FROM BID_SUPP A                                                                       \n")
                  //.append("                            ,BID_SUPP B                                                                       \n")
                  //.append("                       WHERE A.BID_NO = B.BID_NO                                                              \n")
                  //.append("                         AND B.VENDOR_CD = ?                                                                  \n")
                  //.append("                       GROUP BY A.BID_NO) B                                                                   \n")
                  .append("                    ,(SELECT A.BID_NO, A.BID_DEG, COUNT(A.BID_NO) CNT                                         \n")
                  .append("                        FROM BID_SUPP A                                                                       \n")
                  .append("                       GROUP BY A.BID_NO, A.BID_DEG) C                                                        \n")
                  //.append("                WHERE A.BID_NO = B.BID_NO                                                                     \n")
                  //.append("                  AND A.BID_DEG = B.BID_DEG                                                                   \n") 
                  .append("                where A.BID_NO = C.BID_NO                                                                     \n")
                  .append("                  AND A.BID_DEG = C.BID_DEG ) A                                                               \n")
                  .append("                WHERE A.VENDOR_CD = ?                                                                         \n")
                  .append("                ) A                                                                                           \n")
                  .append("       ,BID_MASTER B                                                                                          \n")
                  .append("       ,TBE_SUPP C                                                                                            \n")
                  .append(" WHERE A.BID_NO = B.BID_NO                                                                                    \n")
                  .append("   AND A.BID_DEG = B.BID_DEG                                                                                  \n")
                  .append("   AND  A.BID_NO = C.BID_NO(+)                                                                                \n")
                  .append("   AND A.BID_DEG = C.BID_DEG(+)                                                                               \n")
                  .append("   AND A.VENDOR_CD = C.VENDOR_CD(+)                                                                           \n")
                  .append("   AND B.STATUS_CD in( '10','91','92')                                                                        \n")//2018.06.28 유찰(재입찰),취소 포함
                  .append("   AND A.VENDOR_CD = ?                                                                                        \n");
                
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("p_corp_id"));
            search.addParameter(params.get("p_corp_id"));
            //search.addParameter(params.get("p_corp_id"));
            
            WAFLogger.debug(sqlStr.toString());
            
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error("getBidList=========>" + 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;
        }
    }
    /**
     * 재무정보
     * @param params
     * @return
     */
    public ValueObject getCorpInfoList(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            sqlstr.append(" SELECT                                                                                                                                          \n")
                   .append( "                BASE_YEAR                                                                                                                 \n")
                   .append( "                 ,CAPITAL_AMT                                                                                                                   \n")
                   .append( "                 ,FLOAT_ASSET                                                                                                                \n")
                   .append( "                 ,FLOAT_DEBT                                                                                                                \n")
                   .append( "                 ,OWN_CAP                                                                                                                \n")
                   .append( "                 ,CUR_PROFIT                                                                                                                \n")
                   .append( "                 ,TOT_CAP                                                                                                                \n")
                   .append( "                 ,GROSS_ASSET                                                                                                                \n")
                   .append( "                 ,SALES_AMT                                                                                                                \n")
                   .append( "                 ,CUR_SALES_AMT                                                                                                                \n")
                   .append( "                 ,PRI_SALES_AMT                                                                                                                \n")
                   .append("            		  ,CREDIT_END_DY                          																				     \n")
                   .append("            		  ,CREDIT_YN                            																						   \n")
                   .append("                  ,CASE WHEN NVL(CREDIT_END_DY, TO_CHAR(SYSDATE+1,'YYYYMMDD')) > TO_CHAR(SYSDATE,'YYYYMMDD') THEN 'Y' ELSE 'N' END AS RE_YN                      \n")
                   .append( "         FROM SUPP_FINANCE                                                                                               \n")
                   .append( "           WHERE CORP_ID = ?                                                                                                           \n");
            
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("p_corp_id"));
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
            e.printStackTrace();
            WAFLogger.error("getCorpInfoList===>" + e);
            
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    }
    
    private RowSet getApprSrdList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            
            
               sqlStr.append("        SELECT  A.APPR_LEVEL,    A.APPR_ACLS,     A.APPR_CD,  A.GUBUN_1,  A.GUBUN_2,  A.GUBUN_3,  A.STOCK_RATE,  A.A_CONTENT,                                     \n")
                       .append("                        A.A_RATE, A.B_CONTENT, A.B_RATE, A.C_CONTENT, A.C_RATE, A.D_CONTENT, A.D_RATE, A.E_CONTENT, A.E_RATE, A.NOTI , A.DOC_CD,                    \n")
                       .append("                        B.APPR_RESULT_CD,  B.APPR_RATE                                                                                                      \n")
                       .append("                         FROM                                                                                                                   \n")   
                       .append("                               (                                                                                                              \n")
                       .append("                                  SELECT                                                                                                             \n")
                       .append("                                        Z.APPR_LEVEL,    Z.APPR_ACLS,     Z.APPR_CD,  Z.DOC_CD,                                                  \n")
                       .append("                                        LAST_VALUE(Z.GUBUN_1 IGNORE NULLS) OVER(ORDER BY Z.APPR_CD) GUBUN_1,                                               \n")
                       .append("                                        LAST_VALUE(Z.GUBUN_2 IGNORE NULLS) OVER(ORDER BY Z.APPR_CD) GUBUN_2,                                           \n")    
                       .append("                                        LAST_VALUE(Z.GUBUN_3 IGNORE NULLS) OVER(ORDER BY Z.APPR_CD) GUBUN_3,                                      \n")
                       .append("                                        A_CONTENT, A_RATE, B_CONTENT, B_RATE, C_CONTENT, C_RATE, D_CONTENT, D_RATE, E_CONTENT, E_RATE, NOTI, STOCK_RATE             \n")                                      
                       .append("                                        FROM                                                                                                                                                         \n")
                       .append("                                        (                                                                                                                                                                  \n")
                       .append("                                             SELECT LEVEL APPR_LEVEL,    X.APPR_ACLS  , X.APPR_CD, X.STOCK_RATE,   X.DOC_CD,                                   \n")
                       .append("                                                DECODE(LEVEL, '1', X.APPR_ACLS || '(' || X.STOCK_RATE || ')' , '') GUBUN_1,                                                                 \n")
                       .append("                                                DECODE(TRIM(X.APPR_ACLS), '', ' ', DECODE(LEVEL, '2', X.APPR_ACLS  || '(' || X.STOCK_RATE || ')', '')) GUBUN_2,                                              \n")            
                       .append("                                                DECODE(X.APPR_ACLS, '', '', DECODE(LEVEL, '3', X.APPR_ACLS, '')) GUBUN_3,                                                                                              \n")                                     
                       .append("                                                X.A_CONTENT, X.A_RATE, X.B_CONTENT, X.B_RATE, X.C_CONTENT, X.C_RATE, X.D_CONTENT, X.D_RATE, X.E_CONTENT, X.E_RATE, NOTI             \n")                                                                  
                       .append("                                                FROM(                                                                                      \n")                                                   
                       .append("                                                        SELECT ADM_YEAR,                                                       \n")                                                     
                       .append("                                                                   DOC_CD,                                                     \n")                                                   
                       .append("                                                                   PARENT_LEVEL,                                       \n")
                       .append("                                                                   APPR_CD,                                                          \n")                                
                       .append("                                                                   APPR_MCD,                                                   \n")                                                      
                       .append("                                                                   APPR_ACLS,                                             \n")                                                    
                       .append("                                                                   STOCK_RATE,                                      \n")
                       .append("                                                                   A_CONTENT,                                     \n") 
                       .append("                                                                   A_RATE,                                      \n")
                       .append("                                                                   B_CONTENT,                                     \n") 
                       .append("                                                                   B_RATE,                                      \n")
                       .append("                                                                   C_CONTENT,                                     \n") 
                       .append("                                                                   C_RATE,                                      \n")
                       .append("                                                                   D_CONTENT,                                     \n") 
                       .append("                                                                   D_RATE,                                      \n")
                       .append("                                                                   E_CONTENT,                                     \n") 
                       .append("                                                                   E_RATE,                                      \n")
                       .append("                                                                   NOTI                                                                                     \n")
                       .append("                                                               FROM APPR_STD X                                                                                   \n")    
                       .append("                                                               WHERE  DOC_CD = ?                                                                            \n")       
                       .append("                                                        )  X                                                                                                         \n")                               
                       .append("                                         START WITH PARENT_LEVEL = 'S'                                                                            \n")                                 
                       .append("                                             CONNECT BY PRIOR APPR_CD = PARENT_LEVEL                                                     \n")                        
                       .append("                                             AND LEVEL <= 3                                                                                               \n")                                 
                       .append("                                             ORDER SIBLINGS BY PARENT_LEVEL ASC, APPR_CD ASC                                      \n")
                       .append("                                    )   Z                                                                                                                 \n")                  
                       .append("                           ) A, SUPP_APPR_ITEM B, SUPP_APPR  C                                                                      \n")
                       .append("                           WHERE A.DOC_CD = B.DOC_CD                                                                            \n")
                       .append("                                AND A.APPR_CD = B.APPR_CD                                                                           \n")
                       .append("                                AND B.DOC_CD = C.DOC_CD                                                                             \n")
                       .append("                                AND B.ARR_ID = C.ARR_ID                                                                                 \n")
                       .append("                                AND B.CORP_ID = C.CORP_ID                                                                                \n")
                       .append("                                AND A.GUBUN_3 IS NOT NULL                                                                            \n")                       
                       .append("                                AND A.A_CONTENT IS NOT NULL                                                                          \n")
                       .append("                                AND C.CORP_ID = ?                                                                          \n");
                                               
            
            
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("p_doc_cd"));
            search.addParameter(params.get("p_corp_id"));
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());
            
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error("getApprSrdList=========>" + 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;
        }
    }
    
    public ValueObject getCorp_id(Connection conn) throws Exception{
        
        Persistent persistent_q = null;
        
        
        ValueObject vo = new ValueObject();
        ValueObject row = null;
        StringBuffer sqlstr =  new StringBuffer();
        
        try{
            persistent_q = new PersistentImpl(conn);
            
            sqlstr.append("         SELECT SUPP_INFO_S.NEXTVAL AS SEQ  FROM DUAL                                                 \n");
            
            persistent_q.setStatement(sqlstr.toString()); 
            row = RowSetUtility.getValueObject(persistent_q.query().getRow(0));
            vo.set("seq", row.get("seq"));
        }
        catch(Exception e) {
            e.printStackTrace();
        }finally{
            if(vo == null) vo = new ValueObject();
        }
        return vo;
    }
    
    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");
            

            search.setStatement(sqlStr.toString());
            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;
        }
        
    }
    
    /** 첨부서류조회 
     * @return
     * @throws Exception
     */
    public RowSet getFileListRowSet(String corpId, String code) throws Exception {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        try {

    		sql.append("	SELECT distinct A.DETAILCD,						\n");
    		sql.append(" 		   A.DETAILNM,								\n");
    		sql.append(" 		   A.DETAILSORTNM,							\n");
    		sql.append(" 		   B.ID AS DOC_CD							\n");
    		sql.append("	FROM EBID_COM911T A, PUB0510T B				\n");
    		sql.append("	WHERE A.BASECD = 'OUT_SUPLLIER_FILE'					\n");
    		sql.append("	AND A.DETAILCD = B.ID(+)						\n");
    		sql.append("	AND B.FILE_GB(+) = '"+corpId+"'				\n");
    		sql.append("	ORDER BY A.DETAILCD								\n");
    		
    		System.out.println(sql);
        	
            search.setStatement(sql.toString());
            
            listRowSet = search.execute();
            WAFLogger.debug(sql.toString());
            
           
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if( listRowSet == null ) listRowSet = new RowSetImpl();
           
        }
        
        return listRowSet;
    }
    
    public RowSet getOutLicMaster_searchCombo(){
        
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr = " SELECT LIC_CD, LIC_NM  FROM OUT_LIC_MASTER ORDER BY LIC_CD ASC                 \n";
             
            search.setStatement(sqlstr);
            
            //System.out.println("search=======>" + sqlstr);
            listRowSet = search.execute();
            
        }catch(Exception e){
            //System.out.println(e);
            e.printStackTrace();
        }finally{
    
            if( listRowSet == null ){
                listRowSet = new RowSetImpl();
            }
            
            return listRowSet;
        }
    }
    
    private RowSet getBaseYear(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
        	sqlStr.append("   	SELECT                                                                                                                 			       			\n")
		          .append("   		CASE                                                                                                                                 		\n")
		          .append("   	    	WHEN TO_CHAR(SYSDATE,'MM') >= TO_NUMBER((SELECT CHG_CODE01 FROM EBID_COM911T WHERE BASECD='SUPP_STD_MONTH')) THEN TO_CHAR(SYSDATE,'YYYY')    		\n")
		          .append("    	   		ELSE TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')                                                                                      		\n")
		          .append("			END G_BASE_YEAR                                                                                                                    			\n")
		          .append("		FROM DUAL                                                                                                                              			\n");
                
            search.setStatement(sqlStr.toString());
            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;
        }
    }
}