/**
 * @(#)file    UserListBiz.java
 * @(#)author  Joung Kyu Park
 * @(#)version 1.0
 * @(#)date    May 31, 2007
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.hwenc.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of Hanwha E&C, Inc.
 */
package kr.co.udapsoft.ebid.buyer.supplier.purchase.biz;



import java.sql.Connection;
import java.sql.PreparedStatement;
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 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 PuSupplierApprListBiz extends Biz {

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

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

		
		
		
		if(storage.compareEvent(""))
		{
//	        RowSet acctTypeRowSet = common.getAcctTypeUseRowSet(storage.getUser().get("ACCT_TYPE_CODE"));
//	        storage.addRowSet("AcctTypeRowSet", acctTypeRowSet);
		    
		    RowSet materialKindCodeLv1 = getLv1MaterialKindCode();
            storage.addRowSet("materialKindCodeLv1", materialKindCodeLv1);
		}
		else if(storage.compareEvent("GET_LIST"))
        {
//          RowSet acctTypeRowSet = common.getAcctTypeUseRowSet(storage.getUser().get("ACCT_TYPE_CODE"));
//          storage.addRowSet("AcctTypeRowSet", acctTypeRowSet);
            
            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("materialgroup"));
            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("materialgroup"));
            storage.addRowSet( "ROWSET", materialKindCodeLv3 );
            storage.setSuccess(true);
        }
		
		
	}
	
	// 자재분류 1레벨 코드 가져오기
    public RowSet getLv1MaterialKindCode() {
        Search search       = new SearchImpl();
        RowSet rowSet       = new RowSetImpl();
        StringBuffer sql    = new StringBuffer();
        
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      PARENT_LEVEL MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '0'           \n");  
            
            search.setStatement(sql);
            rowSet = search.execute();
            WAFLogger.debug(sql.toString());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    
    // 자재분류 2레벨 코드 가져오기
    public RowSet getLv2MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '1'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
            
            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;
    }
    
    // 자재분류 3레벨 코드 가져오기
    public RowSet getLv3MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '2'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
            
            search.setStatement(sql);
            
            rowSet = search.execute();
            WAFLogger.debug(sql.toString());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    
    
    /**
     * 목록을 검색하여 리턴
     * @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("                            D.GUBUN_1 KIND_BIG                                    \n")
                   .append("                          , D.GUBUN_2 KIND_MID                                    \n")
                   .append("                          , D.GUBUN_3 KIND_SMALL                                  \n")
                   .append("                          , A.CORP_ID                                             \n")
                   .append("                          , A.CORP_KOR                                            \n")
                   .append("                          , A.REP_NAME                                            \n")
                   .append("                          , A.CORP_REG_NO                                         \n")
                   .append("                          , A.ADDRESS                                             \n")
                   .append("                          , B.TEL_NUM                                             \n")
                   .append("                          , B.USER_NAME                                           \n")
                   .append("                          , B.FAX_NUM                                             \n")
                   .append("                          , B.HP1 || '-' || HP2 || '-' || HP3 H_PHON              \n")
                   .append("                          , B.EMAIL                                               \n")
                   .append("                          , DECODE(A.CREDIT_GRADE, '', '', A.CREDIT_GRADE || '(' || A.CREDIT_CORP_CD || ')'  ) AS CREDIT_GRADE                                                       \n")
                   .append("                          , A.CREDIT_CORP_CD                                                       \n")
                   .append("                          , DECODE(A.STATUS, '00', '가입요청', '01', '대기', '02', '가등록', '03', '등록', '91', '거래중지', '99', '거래취소') CORP_STATUS                                             \n")
                   .append("                          , G.APPR_TOT_RATE                                                                                       \n")
                   .append("                          , G.APPR_DY                                                                                                                     \n")
                   .append("                          , G.ARR_ID                                                                                                                     \n")
                   .append("                          , G.DOC_CD                                                                                                                     \n")
                   .append("                          , A.REG_DT                                                                                                                               \n")
                   .append("                          , G.APPR_TOT_RATE                                                                                                                      \n")
//                   .append("                          , G.ITEM_STD_CD                                                                                                                                    \n")
                   .append("                          , DECODE(PJT_VALID_BID_YN,'N','입찰불가',G.ITEM_STD_CD) AS ITEM_STD_CD                                                                         \n")
                   .append("                          , A.CREDIT_GRADE                                                                                                                        \n")
                   .append("                          , A.CASH_GRADE                                                                                                                           \n")
                   .append("                          , H.APPR_TOT_RATE AS  APPR_TOT_RATE2                                                                                                                     \n")
                   .append("                          , H.ITEM_STD_CD AS ITEM_STD_CD2                                                                                                                     \n")
                   .append("                          , H.CNT AS CNT                                                                                                                     \n")
                   .append("                          , I.APPR_TOT_RATE AS  APPR_TOT_RATE3                                                                                                                     \n")
                   .append("                          , I.ITEM_STD_CD AS ITEM_STD_CD3                                                                                                                     \n")                                      
                   .append("                          , J.APPR_TOT_MIN_RATE AS  APPR_TOT_RATE4                                                                                                                     \n")
                   .append("                          , J.ITEM_MIN_STD_CD AS ITEM_STD_CD4                                                                                                                     \n")
                   .append("                          , J.CNT AS CNT2                                                                                                                     \n")
                   .append("                          , J.APPR_TOT_AVG_RATE AS  APPR_TOT_RATE5                                                                                                                     \n")
                   .append("                          , J.ITEM_AVG_STD_CD AS ITEM_STD_CD5                                                                                                                     \n")                   
                   .append("                      FROM SUPP_INFO A, SUPP_CHARGE B, SUPP_PUR_ITEM C, PMM_MATERIALGROUP_V D ,                                      \n")
                   /*.append("                   (SELECT CORP_ID, MAX(APPR_TOT_RATE) AS APPR_TOT_RATE , MAX(APPR_DY) APPR_DY, MAX(ARR_ID) ARR_ID, DOC_CD,                                        \n")
                   .append("                         CASE WHEN MAX(ITEM_STD_CD) = '03' THEN  --배관                                                                  \n")
	        	   .append("                             CASE WHEN MAX(APPR_TOT_RATE) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                    \n")
	        	   .append("                              END                                                                 \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '04' THEN    --전기                                                                \n")
	        	   .append("                             CASE WHEN MAX(APPR_TOT_RATE) >= 85 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                    \n")
	        	   .append("                              END                                                                 \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '05' THEN  --계장                                                                    \n")
	        	   .append("                             CASE WHEN MAX(APPR_TOT_RATE) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")
	        	   .append("                              END                                                                   \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '06' THEN    --토건A                               \n")
	        	   .append("                             CASE WHEN MAX(APPR_TOT_RATE) >= 90 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 85 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")
	        	   .append("                              END                                                                   \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '07' THEN  --토건B                                 \n")
	        	   .append("                             CASE WHEN MAX(APPR_TOT_RATE) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 75 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")                                
	        	   .append("                              END                                                                   \n")                                 
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '09' THEN   --토건D                                \n")                                                    
	        	   .append("                             CASE WHEN MAX(APPR_TOT_RATE) >= 83 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")
	        	   .append("                              END                                                                   \n")              
	        	   .append("                             ELSE                          --기계S, 기계R, 토건C                       \n")
	        	   .append("                             CASE WHEN MAX(APPR_TOT_RATE) >= 85 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 80 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN MAX(APPR_TOT_RATE) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")                                          
	        	   .append("                              END                                                                    \n")                             
	        	   .append("                            END       ITEM_STD_CD                                                     \n")    
	        	   .append("                       FROM SUPP_APPR 																												\n")
	               .append("                   WHERE DOC_CD IN ('01', '02')  AND ARR_ID IN (SELECT MAX(ARR_ID)                                                        \n")
                   .append("                                                                                   FROM SUPP_APPR                                         \n")
                   .append("                                                                                  WHERE DOC_CD IN ('01', '02')                            \n")
                   .append("                                                                                  GROUP BY CORP_ID)                                       \n")
                   .append("                   GROUP BY CORP_ID, DOC_CD    )   G ,                                                                                    \n")*/
                   .append("					(SELECT CORP_ID, MAX(APPR_TOT_RATE) AS APPR_TOT_RATE , MAX(APPR_DY) APPR_DY, MAX(ARR_ID) ARR_ID, DOC_CD    				\n")  
					.append("	        	          , DECODE(VALID_BID_YN,'Y','입찰가능','N','입찰불가','미평가') AS ITEM_STD_CD 											\n")
                    .append("	        	                          FROM SUPP_APPR 																					\n")								
					.append("	                                  WHERE DOC_CD IN ('01', '02')  AND (CORP_ID,ARR_ID) IN (SELECT CORP_ID,MAX(ARR_ID)                     \n")                     
					.append("                                                                                                      FROM SUPP_APPR                       \n")                      
					.append("                                                                                                     WHERE DOC_CD IN ('01', '02')          \n")                      
					.append("                                                                                                     GROUP BY CORP_ID)                     \n")                      
					.append("                                      GROUP BY CORP_ID, DOC_CD,VALID_BID_YN ) G,                                                           \n")
	               /*.append("                   (  SELECT CORP_ID,COUNT(*) CNT, ROUND(AVG(APPR_TOT_RATE)) APPR_TOT_RATE,		\n")
	               .append("                         CASE WHEN MAX(ITEM_STD_CD) = '03' THEN  --배관                                                                  \n")
	        	   .append("                             CASE WHEN ROUND(AVG(APPR_TOT_RATE)) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                    \n")
	        	   .append("                              END                                                                 \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '04' THEN    --전기                                                                \n")
	        	   .append("                             CASE WHEN ROUND(AVG(APPR_TOT_RATE)) >= 85 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                    \n")
	        	   .append("                              END                                                                 \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '05' THEN  --계장                                                                    \n")
	        	   .append("                             CASE WHEN ROUND(AVG(APPR_TOT_RATE)) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")
	        	   .append("                              END                                                                   \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '06' THEN    --토건A                               \n")
	        	   .append("                             CASE WHEN ROUND(AVG(APPR_TOT_RATE)) >= 90 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 85 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")
	        	   .append("                              END                                                                   \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '07' THEN  --토건B                                 \n")
	        	   .append("                             CASE WHEN ROUND(AVG(APPR_TOT_RATE)) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 75 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")                                
	        	   .append("                              END                                                                   \n")                                 
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '09' THEN   --토건D                                \n")                                                    
	        	   .append("                             CASE WHEN ROUND(AVG(APPR_TOT_RATE)) >= 83 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")
	        	   .append("                              END                                                                   \n")              
	        	   .append("                             ELSE                          --기계S, 기계R, 토건C                       \n")
	        	   .append("                             CASE WHEN ROUND(AVG(APPR_TOT_RATE)) >= 85 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 80 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(AVG(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")                                          
	        	   .append("                              END                                                                    \n")                             
	        	   .append("                            END       ITEM_STD_CD                                                     \n")  */
	               .append("                   (  SELECT CORP_ID,COUNT(*) CNT, ROUND(AVG(APPR_TOT_RATE)) APPR_TOT_RATE,		\n")
	               .append("                     CASE WHEN ROUND(AVG(APPR_TOT_RATE)) >= 55 THEN 'S등급'                                 \n")
	        	   .append("                              WHEN ROUND(AVG(APPR_TOT_RATE)) >= 50 THEN 'A등급'                             \n")
	        	   .append("                              WHEN ROUND(AVG(APPR_TOT_RATE)) >= 45 THEN 'B등급'                             \n")
	        	   .append("                              WHEN ROUND(AVG(APPR_TOT_RATE)) >= 35 THEN 'C등급'                             \n")
	        	   .append("                              ELSE 'D등급'                                                                  \n")
	        	   .append("                          END       ITEM_STD_CD,                                                           \n")
	        	   .append("                          CASE WHEN MAX(NONVALIDATE) > TO_CHAR(SYSDATE,'YYYYMMDD') THEN 'N'                \n")
	        	   .append("                               ELSE 'Y'                                                                    \n")
	        	   .append("                          END PJT_VALID_BID_YN                                                             \n")
	        	   .append("                       FROM SUPP_APPR 																												\n")
	               .append("                       WHERE DOC_CD IN ('03', '04')					 							\n");
	               if(!params.get("appr_dy_s").equals("") && !params.get("appr_dy_e").equals(""))
		            {
		                sqlStr.append("         AND APPR_DY BETWEEN  ?  and ?         \n");
		            }
             sqlStr.append("                       GROUP BY CORP_ID																												\n")
                   .append("                  )  H ,   																																		\n")
	               /*.append("                     SELECT CORP_ID,COUNT(*) CNT, ROUND(MIN(APPR_TOT_RATE)) APPR_TOT_RATE,		\n")
	               .append("                         CASE WHEN MAX(ITEM_STD_CD) = '03' THEN  --배관                                                                  \n")
	        	   .append("                             CASE WHEN ROUND(MIN(APPR_TOT_RATE)) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                    \n")
	        	   .append("                              END                                                                 \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '04' THEN    --전기                                                                \n")
	        	   .append("                             CASE WHEN ROUND(MIN(APPR_TOT_RATE)) >= 85 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                    \n")
	        	   .append("                              END                                                                 \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '05' THEN  --계장                                                                    \n")
	        	   .append("                             CASE WHEN ROUND(MIN(APPR_TOT_RATE)) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")
	        	   .append("                              END                                                                   \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '06' THEN    --토건A                               \n")
	        	   .append("                             CASE WHEN ROUND(MIN(APPR_TOT_RATE)) >= 90 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 85 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")
	        	   .append("                              END                                                                   \n")
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '07' THEN  --토건B                                 \n")
	        	   .append("                             CASE WHEN ROUND(MIN(APPR_TOT_RATE)) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 75 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")                                
	        	   .append("                              END                                                                   \n")                                 
	        	   .append("                             WHEN MAX(ITEM_STD_CD) = '09' THEN   --토건D                                \n")                                                    
	        	   .append("                             CASE WHEN ROUND(MIN(APPR_TOT_RATE)) >= 83 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 78 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")
	        	   .append("                              END                                                                   \n")              
	        	   .append("                             ELSE                          --기계S, 기계R, 토건C                       \n")
	        	   .append("                             CASE WHEN ROUND(MIN(APPR_TOT_RATE)) >= 85 THEN 'A등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 80 THEN 'B등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 70 THEN 'C등급'                            \n")
	        	   .append("                                  WHEN ROUND(MIN(APPR_TOT_RATE)) >= 65 THEN 'D등급'                            \n")
	        	   .append("                                  ELSE '등급 외'                                                      \n")                                          
	        	   .append("                              END                                                                    \n")                             
	        	   .append("                            END       ITEM_STD_CD                                                     \n") */
	               .append("                  (   SELECT CORP_ID,COUNT(*) CNT, ROUND(MIN(APPR_TOT_RATE)) APPR_TOT_RATE,		\n")
	               .append("                             CASE WHEN ROUND(MIN(APPR_TOT_RATE)) >= 55 THEN 'S등급'            \n")                           
	        	   .append("                                      WHEN ROUND(MIN(APPR_TOT_RATE)) >= 50 THEN 'A등급'        \n")                    
	        	   .append("                                      WHEN ROUND(MIN(APPR_TOT_RATE)) >= 45 THEN 'B등급'        \n")                    
	        	   .append("                                      WHEN ROUND(MIN(APPR_TOT_RATE)) >= 35 THEN 'C등급'        \n")                    
	        	   .append("                                      ELSE 'D등급'                                             \n")       
	        	   .append("                                  END       ITEM_STD_CD                                      \n")
	        	   .append("                       FROM SUPP_APPR 																												\n")
	               .append("                       WHERE DOC_CD IN ('03', '04')					 							\n");
	               if(!params.get("appr_dy_s").equals("") && !params.get("appr_dy_e").equals(""))
		            {
		                sqlStr.append("         AND APPR_DY BETWEEN  ?  and ?         \n");
		            }
             sqlStr.append("                       GROUP BY CORP_ID																												\n")
                   .append("                  )  I,   																																		\n")
                   .append("                  (																																									\n")
	               .append("                     SELECT CORP_ID,COUNT(*) CNT, 					\n")
	               .append("                            ROUND (MIN (APPR_TOT_RATE))    APPR_TOT_MIN_RATE,		\n")
	               .append("                            ROUND (AVG (APPR_TOT_RATE))    APPR_TOT_AVG_RATE,		\n")	               
	        	   .append("                            CASE WHEN ROUND (MIN (APPR_TOT_RATE)) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                 WHEN ROUND (MIN (APPR_TOT_RATE)) >= 60 THEN 'B등급'                            \n")
	        	   .append("                                 WHEN ROUND (MIN (APPR_TOT_RATE)) >= 40 THEN 'C등급'                            \n")
	        	   .append("                                 WHEN ROUND (MIN (APPR_TOT_RATE)) >= 20 THEN 'D등급'                            \n")
	        	   .append("                                 ELSE '등급 외'                                                                          \n")
	        	   .append("                            END ITEM_MIN_STD_CD,                                                                       \n") 
	        	   .append("                            CASE WHEN ROUND (AVG (APPR_TOT_RATE)) >= 80 THEN 'A등급'                            \n")
	        	   .append("                                 WHEN ROUND (AVG (APPR_TOT_RATE)) >= 60 THEN 'B등급'                            \n")
	        	   .append("                                 WHEN ROUND (AVG (APPR_TOT_RATE)) >= 40 THEN 'C등급'                            \n")
	        	   .append("                                 WHEN ROUND (AVG (APPR_TOT_RATE)) >= 20 THEN 'D등급'                            \n")
	        	   .append("                                 ELSE '등급 외'                                                                          \n")
	        	   .append("                            END ITEM_AVG_STD_CD                                                                       \n")
		           .append("                       FROM SUPP_APPR 																												\n")
		           .append("                       WHERE DOC_CD = '06'				 							\n");
		           if(!params.get("appr_dy_s").equals("") && !params.get("appr_dy_e").equals(""))
			          {
			              sqlStr.append("         AND APPR_DY BETWEEN  ?  and ?         \n");
			          }
		     sqlStr.append("                       GROUP BY CORP_ID																												\n")
		           .append("                  )  J				\n")
                   .append("                      WHERE A.CORP_ID = B.CORP_ID                                          \n")
                   .append("                      AND B.CORP_ID = C.CORP_ID                                        \n")
                   .append("                      AND C.KIND_SMALL = D.MATERIALGROUP                                   \n")
                   .append("                      AND A.CORP_ID = G.CORP_ID(+)                                             \n")
                   .append("                      AND A.CORP_ID = H.CORP_ID(+)                                             \n")
                   .append("                      AND A.CORP_ID = I.CORP_ID(+)                                             \n")
                   .append("                      AND A.CORP_ID = J.CORP_ID(+)                                             \n")
                   .append("                      AND B.DEFAULT_YN  = 'Y'                                             \n")
                   .append("                      AND C.REPRE_YN = 'Y'                                    \n")
                   .append("                      AND A.JOB_DIV = 'P'                                    \n");
                   //.append("                    AND A.STATUS = '03'                                    \n")
                  // .append("                 	  AND A.IN_OUT_DIV != 'O'                                \n");2016.5.12 해외업체, 등록 해제
                   
		     
            if(!params.get("corp_no").equals(""))
            {
                sqlStr.append("         AND A.CORP_REG_NO LIKE '%' || ? || '%'        \n");
            }
            if(!params.get("corp_name").equals(""))
            {
                sqlStr.append("         AND A.CORP_KOR LIKE '%' || ? || '%'        \n");
            }
            if(!params.get("material_Kind_Code_Lv3").equals(""))
            {
                sqlStr.append("         AND D.MATERIALGROUP = ?        \n");
            }else if(!params.get("material_Kind_Code_Lv2").equals(""))
            {
                sqlStr.append("         AND D.MATERIALGROUP IN(SELECT MATERIALGROUP FROM PMM_MATERIALGROUP_V        \n");
                sqlStr.append("                                 WHERE PARENT_LEVEL IN(SELECT MATERIALGROUP FROM PMM_MATERIALGROUP_V WHERE MATERIALGROUP = ?)        \n");
                sqlStr.append("                                   AND MATERIAL_LEVEL = '2')         \n");
            }else if(!params.get("material_Kind_Code_Lv1").equals(""))
            {
            	sqlStr.append("         AND D.MATERIALGROUP IN(SELECT MATERIALGROUP FROM PMM_MATERIALGROUP_V        \n");
                sqlStr.append("                                 WHERE PARENT_LEVEL IN(SELECT MATERIALGROUP FROM PMM_MATERIALGROUP_V WHERE PARENT_LEVEL = ?)        \n");
                sqlStr.append("                                   AND MATERIAL_LEVEL = '2')         \n");
            }          
            if(!params.get("appr_dy_s").equals("") && !params.get("appr_dy_e").equals(""))
            {
                sqlStr.append("         AND G.APPR_DY BETWEEN  ?  and ?         \n");
            }
            sqlStr.append("         ORDER BY A.CORP_ID DESC, G.ARR_ID ASC         \n");
            
            
            
            
            search.setStatement(sqlStr.toString());
            if(!params.get("appr_dy_s").equals("") && !params.get("appr_dy_e").equals(""))
            {
                search.addParameter(params.get("appr_dy_s").trim().toString().replace("-", ""));
                search.addParameter(params.get("appr_dy_e").trim().toString().replace("-", ""));
                
                search.addParameter(params.get("appr_dy_s").trim().toString().replace("-", ""));
                search.addParameter(params.get("appr_dy_e").trim().toString().replace("-", ""));
                search.addParameter(params.get("appr_dy_s").trim().toString().replace("-", ""));
                search.addParameter(params.get("appr_dy_e").trim().toString().replace("-", ""));                
            }
            if(!params.get("corp_no").equals(""))
            {
            search.addParameter(params.get("corp_no").trim());
            }
            if(!params.get("corp_name").equals(""))
            {
                search.addParameter(params.get("corp_name").trim());
            }
            if(!params.get("material_Kind_Code_Lv3").equals(""))
            {
                search.addParameter(params.get("material_Kind_Code_Lv3").trim().toString());
            }else if(!params.get("material_Kind_Code_Lv2").equals(""))
            {
                search.addParameter(params.get("material_Kind_Code_Lv2").trim());
            }else if(!params.get("material_Kind_Code_Lv1").equals(""))
            {
            search.addParameter(params.get("material_Kind_Code_Lv1").trim());
            }                                  
            if(!params.get("appr_dy_s").equals("") && !params.get("appr_dy_e").equals(""))
            {
                search.addParameter(params.get("appr_dy_s").trim().toString().replace("-", ""));
                search.addParameter(params.get("appr_dy_e").trim().toString().replace("-", ""));
            }            
            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);
            
            se.printStackTrace();
            throw new Exception(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    
    
    
}