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

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

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

		
		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));
             
        }
		
	}

	
	/**
     * 목록을 검색하여 리턴
     * @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 B.CORP_ID, A.APPR_TOT_RATE, A.APPR_DY,	B.CORP_KOR ,               \n");
	        	sqlStr.append(" CASE WHEN D.ADM_YEAR = '2014' THEN                                          \n");
	        	sqlStr.append("     CASE WHEN A.APPR_TOT_RATE >= 90 THEN 'A등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 80 THEN 'B등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 70 THEN 'C등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 65 THEN 'D등급'                            \n");
	        	sqlStr.append("          ELSE '등급 외'                                                      \n");
	        	sqlStr.append("      END                                                                    \n");
	        	sqlStr.append(" 	WHEN D.ADM_YEAR = '2023' THEN                                                                        \n");
	        	sqlStr.append("     CASE WHEN A.APPR_TOT_RATE >= 55 THEN 'S등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 50 THEN 'A등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 45 THEN 'B등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 35 THEN 'C등급'                            \n");
	        	sqlStr.append("          ELSE 'D등급'                                                      \n");
	        	sqlStr.append("      END                                                                    \n");
	        	sqlStr.append("ELSE                                                                        \n");
	        	sqlStr.append(" CASE WHEN A.ITEM_STD_CD = '03' THEN  --배관                                                                  \n");
	        	sqlStr.append("     CASE WHEN A.APPR_TOT_RATE >= 80 THEN 'A등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 78 THEN 'B등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 70 THEN 'C등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 65 THEN 'D등급'                            \n");
	        	sqlStr.append("          ELSE '등급 외'                                                      \n");
	        	sqlStr.append("      END                                                                    \n");
	        	sqlStr.append("     WHEN A.ITEM_STD_CD = '04' THEN    --전기                                                                 \n");
	        	sqlStr.append("     CASE WHEN A.APPR_TOT_RATE >= 85 THEN 'A등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 78 THEN 'B등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 70 THEN 'C등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 65 THEN 'D등급'                            \n");
	        	sqlStr.append("          ELSE '등급 외'                                                      \n");
	        	sqlStr.append("      END                                                                   \n");
	        	sqlStr.append("     WHEN A.ITEM_STD_CD = '05' THEN  --계장                                                                    \n");
	        	sqlStr.append("     CASE WHEN A.APPR_TOT_RATE >= 80 THEN 'A등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 78 THEN 'B등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 70 THEN 'C등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 65 THEN 'D등급'                            \n");
	        	sqlStr.append("          ELSE '등급 외'                                                      \n");
	        	sqlStr.append("      END                                                                   \n");
	        	sqlStr.append("     WHEN A.ITEM_STD_CD = '06' THEN    --토건A                               \n");
	        	sqlStr.append("     CASE WHEN A.APPR_TOT_RATE >= 90 THEN 'A등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 85 THEN 'B등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 70 THEN 'C등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 65 THEN 'D등급'                            \n");
	        	sqlStr.append("          ELSE '등급 외'                                                      \n");
	        	sqlStr.append("      END                                                                   \n");
	        	sqlStr.append("     WHEN A.ITEM_STD_CD = '07' THEN  --토건B                                 \n");
	        	sqlStr.append("     CASE WHEN A.APPR_TOT_RATE >= 80 THEN 'A등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 75 THEN 'B등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 70 THEN 'C등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 65 THEN 'D등급'                            \n");
	        	sqlStr.append("          ELSE '등급 외'                                                      \n");                                
	        	sqlStr.append("      END                                                                   \n");                                 
	        	sqlStr.append("     WHEN A.ITEM_STD_CD = '09' THEN   --토건D                                \n");                                                    
	        	sqlStr.append("     CASE WHEN A.APPR_TOT_RATE >= 83 THEN 'A등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 78 THEN 'B등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 70 THEN 'C등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 65 THEN 'D등급'                            \n");
	        	sqlStr.append("          ELSE '등급 외'                                                      \n");
	        	sqlStr.append("      END                                                                   \n");              
	        	sqlStr.append("     ELSE                          --기계S, 기계R, 토건C                       \n");
	        	sqlStr.append("     CASE WHEN A.APPR_TOT_RATE >= 85 THEN 'A등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 80 THEN 'B등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 70 THEN 'C등급'                            \n");
	        	sqlStr.append("          WHEN A.APPR_TOT_RATE >= 65 THEN 'D등급'                            \n");
	        	sqlStr.append("          ELSE '등급 외'                                                      \n");                                          
	        	sqlStr.append("      END                                                                    \n");                              
	        	sqlStr.append("    END                                                                     \n");                                    
	        	sqlStr.append("  END ITEM_STD_CD                                                           \n");                                    
	        	sqlStr.append("         , A.ARR_ID, A.DEPT_CD, C.DEPT_NAME, APPR_BY_ID,																																			\n");
	        	sqlStr.append("         DECODE(REGEXP_INSTR(F_GET_USER_NM(APPR_BY_ID),'^[0-9]'), '1',  F_GET_USER_NM(APPR_BY_ID), F_GET_USER_NM(APPR_BY_ID))      USER_NAME,						\n");
	        	sqlStr.append("         DECODE(A.REGU_STATUS_CD, '', '작성대상',  '00', '작성대상', '01', '작성중', '02', '작성완료', '작성대상')  STATUS_CODE,															\n");
	        	//sqlStr.append("         DECODE(B.STATUS, '', '작성중',  '00', '가입요청', '01', '대기', '02', '가등록', '03', '등록', '91', '거래중지', '99', '거래취소', '작성중')  STATUS_CODE,															\n");
	        	sqlStr.append("         C.CST_DOC_NAME, A.DOC_CD , D.ADM_YEAR,																																																	\n");
	        	sqlStr.append(" CASE WHEN D.ADM_YEAR = '2014' THEN                                       \n");
	        	sqlStr.append("     CASE WHEN A.ITEM_STD_CD = '01' THEN '기계'                            \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '02' THEN '배관'                            \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '03' THEN '전기'                            \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '04' THEN '계장'                            \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '05' THEN '토건'                            \n");
	        	sqlStr.append("      END                                                                 \n");
	        	sqlStr.append(" ELSE                                                                     \n");
	        	sqlStr.append("     CASE WHEN A.ITEM_STD_CD = '01' THEN '기계(S)'                         \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '02' THEN '기계(R)'                         \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '03' THEN '배관'                            \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '04' THEN '전기'                            \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '05' THEN '계장'                            \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '06' THEN '토건A'                           \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '07' THEN '토건B'                           \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '08' THEN '토건C'                           \n");
	        	sqlStr.append("          WHEN A.ITEM_STD_CD = '09' THEN '토건D'                           \n");
	        	sqlStr.append("      END                                                                 \n");
	        	sqlStr.append(" END  ITEM_STD_NM  , C.PUR_CONT_ID, C.ORD_DOC_NO                          \n");
	        	sqlStr.append("         ,A.WRITE_NM, A.APPR_NOTE, A.REGU_STATUS_CD                       \n");
	        	sqlStr.append("         FROM SUPP_APPR A, SUPP_INFO B, PUR1000T C																																											\n");
	        	sqlStr.append("              , (SELECT CORP_ID, DOC_CD, ARR_ID, MAX(ADM_YEAR) ADM_YEAR FROM SUPP_APPR_ITEM WHERE DOC_CD IN ('03', '04') GROUP BY CORP_ID, DOC_CD, ARR_ID )	D  \n"); 
	        	sqlStr.append("         WHERE A.CORP_ID = B.CORP_ID																																																	\n");
	        	sqlStr.append("         AND A.ORD_DOC_NO = C.ORD_DOC_NO																																														\n");
	        	sqlStr.append("         AND A.CORP_ID = D.CORP_ID																																														\n");
	        	sqlStr.append("         AND A.DOC_CD = D.DOC_CD																																													\n");
	        	sqlStr.append("         AND A.ARR_ID = D.ARR_ID																																														\n");
	        	sqlStr.append("         AND A.DOC_CD IN ('03', '04')																																																					\n");
	        	sqlStr.append("         AND B.JOB_DIV = 'P'																																																				\n");
	        	//sqlStr.append("         AND B.IN_OUT_DIV != 'O'					 \n");  2016.5.12 조재윤과장 요청. 해외업체 포함 
	        	if(!params.get("searchDeptCd").equals(""))
	        	{
	        		sqlStr.append("         AND A.DEPT_CD = ?																																																				\n");
	        	}
	        	if(!params.get("build_name").equals(""))
	        	{
	        		sqlStr.append("         AND C.CST_DOC_NAME LIKE '%' ||  ?	|| '%'																																													\n");
	        	}
	        	if(!params.get("corp_kor").equals(""))
	        	{
	        		sqlStr.append("         AND B.CORP_KOR LIKE '%' ||  ?	|| '%'																																														\n");
	        	}
	        	if(!"".equals(params.get("searchFromDate"))){
	        		sqlStr.append( " AND TO_NUMBER(SUBSTR(A.APPR_DY, 1 ,6)) >= '"+Params.get("searchFromDate").replaceAll("-", "")+"'					\n");
	        	}
	        	if(!"".equals(params.get("searchToDate"))){
	        		sqlStr.append(" AND TO_NUMBER(SUBSTR(A.APPR_DY, 1 ,6)) <= '"+Params.get("searchToDate").replaceAll("-", "")+"'					\n");
	        	}
	        	
	        	sqlStr.append("         ORDER BY DEPT_NAME, CORP_ID DESC, ARR_ID ASC																																														\n");
        	
            
            search.setStatement(sqlStr.toString());
            
            if(!params.get("searchDeptCd").equals(""))
        	{
            	search.addParameter(params.get("searchDeptCd"));
        	}
            if(!params.get("build_name").equals(""))
        	{
            	search.addParameter(params.get("build_name"));
        	}
            if(!params.get("corp_kor").equals(""))
        	{
            	search.addParameter(params.get("corp_kor"));
        	}
            
            
            
            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;
        }
    }
    
    
    
    
}