/**
 * @(#)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.outside.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 OutSupplierApprListBiz extends Biz {

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

	
	public void doBiz() throws Exception {

		
		
		WAFLogger.debug("OutSupplierApprListBiz event==============>" + storage.getEvent());
		
		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);
		    
			WAFLogger.debug("========================");
            
		    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();
        
        /*
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      ITEM_CD  MATERIALGROUP               \n");
            sql.append("    , ITEM_NAME MATERIALGROUPNAME             \n");
            sql.append("    FROM OUT_ITEM          \n");
            sql.append("    WHERE DEPTH = '1'           \n");            
            sql.append("    ORDER BY ITEM_CD          \n");
            
            search.setStatement(sql);
            rowSet = search.execute();
            WAFLogger.debug(sql.toString());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    // 자재분류 2레벨 코드 가져오기
    public RowSet getLv2MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      ITEM_CD  MATERIALGROUP                 \n");
            sql.append("    , ITEM_NAME MATERIALGROUPNAME             \n");
            sql.append("    FROM OUT_ITEM          \n");
            sql.append("    WHERE DEPTH = '2'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND KIND_BIG = ?        \n");
                search.addParameter(Params);
            }
            sql.append("    ORDER BY ITEM_CD          \n");
            
            search.setStatement(sql);
            
            rowSet = search.execute();
            WAFLogger.debug(sql.toString());
        } 
        catch (Exception se) {
        	WAFLogger.error("getLv2MaterialKindCode====>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    // 자재분류 3레벨 코드 가져오기
    public RowSet getLv3MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      ITEM_CD MATERIALGROUP                 \n");
            sql.append("    , ITEM_NAME MATERIALGROUPNAME             \n");
            sql.append("    FROM OUT_ITEM          \n");
            sql.append("    WHERE DEPTH = '3'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND KIND_BIG = ?        \n");
                search.addParameter(Params);
            }
            sql.append("    ORDER BY ITEM_CD          \n");
            
            search.setStatement(sql);
            
            rowSet = search.execute();
            WAFLogger.debug(sql.toString());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    
    
    
    /**
     * 목록을 검색하여 리턴
     * @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("                        F.ITEM_CD KIND_BIG_CODE                          \n")                               
                   .append("                      , F.ITEM_NAME KIND_BIG                                                         \n")
                   .append("                      , E.ITEM_CD KIND_MID_CODE                                                         \n")
                   .append("                      , E.ITEM_NAME KIND_MID                                                         \n")
                   .append("                      , D.ITEM_CD KIND_SMALL_CODE                                                         \n")
                   .append("                      , D.ITEM_NAME 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("                      , G.ARR_ID                                                                                                                     \n")
                   .append("                      , TO_CHAR(TO_DATE(G.APPR_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD') APPR_DY                                                        \n")
                   .append("                      , A.CREDIT_CORP_CD                          \n")                          
                   .append("                      , DECODE(A.STATUS, '00', '가입요청', '01', '대기', '02', '가등록', '03', '등록', '91', '거래중지', '99', '거래취소') CORP_STATUS                          \n")
                   .append("                      , G.DOC_CD                                                                                                                     \n")
                   .append("                      , DECODE(G.DOC_CD, '11', '등록평가', '정기평가') DOC_GUBUN                                                                                                                     \n")
                   .append("                      , A.REG_DT                                                                                                                               \n")
                   .append("                      , G.APPR_TOT_RATE                                                                                                                      \n")
                   .append("                      , G.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("                  FROM SUPP_INFO A, SUPP_CHARGE B, SUPP_OUT_ITEM C, OUT_ITEM D, OUT_ITEM E, OUT_ITEM F,                                                 \n")
                   .append("                  (SELECT CORP_ID, APPR_TOT_RATE, MAX(APPR_DY) APPR_DY, MAX(ARR_ID) ARR_ID, DOC_CD ,                                                   \n")
                   .append("              CASE WHEN DOC_CD = '13' THEN                                                          \n")//2016.4.28
                   .append("                   DECODE(ITEM_STD_CD , 'D',                                                        \n")
                   .append("                     CASE WHEN NVL(APPR_TOT_RATE,0) >= 80 THEN '1등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 70 THEN '2등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 60 THEN '3등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 50 THEN '4등급'                               \n")
                   .append("                          ELSE '5등급'                                                               \n")
                   .append("                      END ,                                                                         \n")
                   .append("                     CASE WHEN NVL(APPR_TOT_RATE,0) >= 85 THEN '1등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 75 THEN '2등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 65 THEN '3등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 55 THEN '4등급'                               \n")
                   .append("                          ELSE '5등급'                                                               \n")
                   .append("                      END     )  --END DECODE                                                       \n")
                   .append("              ELSE  --설계,인허가                                                                                                                                          \n")
                   .append("                   DECODE(ITEM_STD_CD , 'A',                                                        \n")
                   .append("                     CASE WHEN NVL(APPR_TOT_RATE,0) >= 80 THEN '1등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 75 THEN '2등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 65 THEN '3등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 55 THEN '4등급'                               \n")
                   .append("                          ELSE '5등급'                                                               \n")
                   .append("                      END ,                                                                         \n")
                   .append("                     CASE WHEN NVL(APPR_TOT_RATE,0) >= 75 THEN '1등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 70 THEN '2등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 60 THEN '3등급'                               \n")
                   .append("                          WHEN NVL(APPR_TOT_RATE,0) >= 50 THEN '4등급'                               \n")
                   .append("                          ELSE '5등급'                                                               \n")
                   .append("                      END     )  --END DECODE                                                       \n")
                   .append("              END  AS ITEM_STD_CD                                                                   \n")
                   .append("                  FROM SUPP_APPR X                                                                  \n")
                   .append("                 WHERE DOC_CD in ( '11', '13','14')                                                 \n")
                   .append("                   AND ARR_ID IN (SELECT MAX(ARR_ID)                                                \n")
                   .append("                                    FROM SUPP_APPR Y                                                \n")
                   .append("                                   WHERE DOC_CD in ( '11', '13','14')                               \n")
                   .append("                                     AND Y.CORP_ID = X.CORP_ID                                      \n")
                   .append("                                   GROUP BY Y.CORP_ID)                                              \n")
                   .append("                 GROUP BY CORP_ID, APPR_TOT_RATE, DOC_CD, ITEM_STD_CD )  G,                         \n")
                   .append("                 (																				 	\n")
                   .append("                  SELECT CORP_ID,DOC_CD,ROUND(AVG(APPR_TOT_RATE)) APPR_TOT_RATE,						\n")
                   .append("                         CASE WHEN AVG(ITEM_STD_CD) >= 5 THEN 'S등급'									\n")
                   .append("                              WHEN AVG(ITEM_STD_CD) >= 4 THEN 'A등급'									\n")
                   .append("                              WHEN AVG(ITEM_STD_CD) >= 3 THEN 'B등급'									\n")
                   .append("                              WHEN AVG(ITEM_STD_CD) >= 2 THEN 'C등급'									\n")
                   .append("                              WHEN AVG(ITEM_STD_CD) < 2 THEN 'D등급'										\n")
                   .append("                              END ITEM_STD_CD															\n")
                   .append("                    FROM (																				\n")
	               .append("                        SELECT CORP_ID,DOC_CD,DECODE(DOC_CD,12, APPR_TOT_RATE,DECODE(YEAR_STD,'2022',APPR_TOT_RATE, ROUND(APPR_TOT_RATE / 3) )) AS APPR_TOT_RATE,  \n")	
	               .append("                               CASE WHEN YEAR_STD = '2022' THEN                                                                                   \n")
	               .append("                                    CASE WHEN DOC_CD = '12' THEN    --준공평가(공사)											                      \n")
	               .append("                                         CASE WHEN ROUND(APPR_TOT_RATE) >=92 THEN 5				                                                  \n")
	               .append("                                              WHEN ROUND(APPR_TOT_RATE) >=84 THEN 4				                                                  \n")				
	               .append("                                              WHEN ROUND(APPR_TOT_RATE) >=76 THEN 3				                                                  \n")				
	               .append("                                              WHEN ROUND(APPR_TOT_RATE) >=68 THEN 2				                                                  \n")				
	               .append("                                              WHEN ROUND(APPR_TOT_RATE) < 67 THEN 1				                                                  \n")			
	               .append("                                              END  									   				                                              \n")
	               .append("                                    ELSE 							--준공평가(설계/인허가)				                                              \n")							            
	               .append("                                         CASE WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) >=92 THEN 5                 \n")				
	               .append("                                              WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) >=84 THEN 4                 \n")				     
	               .append("                                              WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) >=76 THEN 3                 \n")				
	               .append("                                              WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) >=68 THEN 2                 \n")				
	               .append("                                              WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) < 67 THEN 1                 \n")				
	               .append("                                              END  																                                  \n")
	               .append("                                    END                                                                                                           \n")
	               .append("                               ELSE                                                                                                               \n")
	               .append("                                    CASE WHEN DOC_CD = '12' THEN    --준공평가(공사)											                      \n")
	               .append("                                         CASE WHEN ROUND(APPR_TOT_RATE) >=100 THEN 5				                                              \n")
	               .append("                                              WHEN ROUND(APPR_TOT_RATE) >=85 THEN 4				                                                  \n")
	               .append("                                              WHEN ROUND(APPR_TOT_RATE) >=65 THEN 3				                                                  \n")				
	               .append("                                              WHEN ROUND(APPR_TOT_RATE) >=45 THEN 2				                                                  \n")			
	               .append("                                              WHEN ROUND(APPR_TOT_RATE) < 44 THEN 1				                                                  \n")				
	               .append("                                              END  													                                              \n")						
	               .append("                                    ELSE 							--준공평가(설계/인허가)				                                              \n")							            
	               .append("                                         CASE WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) >=90 THEN 5                 \n")				
	               .append("                                              WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) >=75 THEN 4                 \n")				
	               .append("                                              WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) >=60 THEN 3                 \n")				
	               .append("                                              WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) >=45 THEN 2                 \n")				
	               .append("                                              WHEN ROUND ( DECODE(YEAR_STD,'2022',APPR_TOT_RATE, APPR_TOT_RATE / 3 )) < 44 THEN 1                 \n")				
	               .append("                                              END  																                                  \n")
	               .append("                                    END                                                                                                           \n")
	               .append("                               END ITEM_STD_CD											                                                          \n")
                   .append("                        FROM SUPP_APPR 																\n")
	               .append("                       WHERE DOC_CD in ( '12', '16')												\n");
	               if(!params.get("appr_dy_s").equals("") && !params.get("appr_dy_e").equals(""))
                   {
                           sqlStr.append("         AND APPR_DY BETWEEN  ?  and ?         \n");
                   }
	         sqlStr.append("                       )																			\n")
	               .append("                       GROUP BY CORP_ID, DOC_CD														\n")
                   .append("                  )  H   																			\n")
                   .append("                  WHERE A.CORP_ID = B.CORP_ID                                                       \n")
                   .append("                  AND B.CORP_ID = C.CORP_ID                                                         \n")
                   .append("                  AND C.OUT_ITEM_CD = D.ITEM_CD                                                     \n")
                   .append("                  AND D.PARENT_LEVEL = E.ITEM_CD                                                    \n")
                   .append("                  AND E.PARENT_LEVEL = F.ITEM_CD                                                    \n")
                   .append("                  AND A.CORP_ID = G.CORP_ID(+)                                                      \n")
                   .append("                  AND A.CORP_ID = H.CORP_ID(+)                                                      \n")
                   .append("                  AND B.DEFAULT_YN  = 'Y'                                                           \n")
                   .append("                  AND C.REPRE_YN = 'Y'                                                              \n")
                   .append("                  AND A.JOB_DIV = 'O'                                                               \n")
                   .append("                  AND A.STATUS = '03'                                                               \n")
                   .append("                  AND A.IN_OUT_DIV != 'O'                                                           \n");
           
            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_Lv1").equals(""))
            {
                sqlStr.append("         AND F.ITEM_CD = ?        \n");
            }
            if(!params.get("material_Kind_Code_Lv2").equals(""))
            {
                sqlStr.append("         AND E.ITEM_CD = ?        \n");
            }
            if(!params.get("material_Kind_Code_Lv3").equals(""))
            {
                sqlStr.append("         AND D.ITEM_CD = ?        \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().replace("-", "").toString());
                search.addParameter(params.get("appr_dy_e").trim().replace("-", "").toString());
            }
            
            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_Lv1").equals(""))
            {
            search.addParameter(params.get("material_Kind_Code_Lv1").trim());
            }
            if(!params.get("material_Kind_Code_Lv2").equals(""))
            {
                search.addParameter(params.get("material_Kind_Code_Lv2").trim());
            }
            if(!params.get("material_Kind_Code_Lv3").equals(""))
            {
                search.addParameter(params.get("material_Kind_Code_Lv3").trim().toString());
            }
            if(!params.get("appr_dy_s").equals("") && !params.get("appr_dy_e").equals(""))
            {
                search.addParameter(params.get("appr_dy_s").trim().replace("-", "").toString());
                search.addParameter(params.get("appr_dy_e").trim().replace("-", "").toString());
            }
            
            WAFLogger.debug(search.getParameters());
            WAFLogger.debug(sqlStr.toString()); 
              
            /*
            //Paging 처리를 위한 Helper Object 생성
            super.createPageAnchorInfo();
            //한페이지에 가져올 목록의 갯수 지정(미지정시  waf.properties 파일의 기본값을 사용)
            //super.anchor.range = 200;
            //Paging 처리에 사용될 Parameter를  세팅
            super.anchor.setParams(this.params);
            //검색할  SQL 세팅 : SQL은 Paging처리를 하지 않고 작성
            super.anchor.setSQL(sqlStr.toString());
            //전체 Record 숫자를 조회
            super.anchor.doTotalCountSQL(); // 전체 숫자를 조회
            
            //Paging처리가 된 SQL을 Helper Object에서 가져옴
            String temp = super.anchor.getPagingSQL();
            
            search.setStatement(temp);
            */
            
            
            rowSet = search.execute();
            
        }
        catch (Exception se) {
        	WAFLogger.error("se====>" + 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;
        }
    }
    
    
    
}