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

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

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

		
		if(storage.compareEvent("GET_LIST"))
        {           
            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                \n");                                                           
        	sqlStr.append("                     A.DEPT_CD -- 현장코드               \n");
        	sqlStr.append("                    ,A.DEPT_NAME -- 현장명               \n");                                                  
        	sqlStr.append("                    ,A.CST_DOC_NAME -- 계약명               \n");                                                  
        	sqlStr.append("                    ,A.MEMBER_NAME -- 업체명               \n");                                                                                                    
        	sqlStr.append("                    ,A.CORP_ID -- 업체코드               \n");
        	sqlStr.append("                    ,A.PLAN_APPR_STATUS -- 설계평가 상태               \n");                                                  
        	sqlStr.append("                    ,DECODE(A.PLAN_APPR_STATUS,'00','평가대상','01','작성중','02','평가완료',A.PLAN_APPR_STATUS) AS PLAN_APPR_STATUS_NM               \n");                                                  
        	sqlStr.append("                    ,A.PAYREQ_ID               \n");        	        	
        	sqlStr.append("                    ,A.PUR_CONT_ID               \n");
        	sqlStr.append("                    ,A.ORD_DOC_NO               \n");
        	sqlStr.append("                    ,A.PLAN_APPR_CHK_EMPNO               \n");
        	sqlStr.append("                    ,B.WRITE_NO               \n");                                                  
        	sqlStr.append("                    ,B.WRITE_NM               \n");
        	sqlStr.append("                    ,B.REGU_STATUS_CD               \n");
        	sqlStr.append("                    ,B.DOC_CD               \n");
        	sqlStr.append("                    ,B.APPR_TOT_RATE               \n");
        	sqlStr.append("                    ,B.APPR_NOTE               \n");
        	sqlStr.append("                    ,B.ARR_ID               \n");
        	sqlStr.append("                    ,CASE WHEN B.APPR_TOT_RATE >= 80 THEN '적극 재기용'               \n");
        	sqlStr.append("                          WHEN B.APPR_TOT_RATE >= 60 THEN '재기용 무방'               \n");
        	sqlStr.append("                          WHEN B.APPR_TOT_RATE >= 40 THEN '재기용시 평가항목에 대한 사전 주의'               \n");
        	sqlStr.append("                          WHEN B.APPR_TOT_RATE >= 20 THEN '경고 조치 및 거래 정지'               \n");
        	sqlStr.append("                          WHEN B.APPR_TOT_RATE >= 0  THEN '거래 중단 요망'               \n");
        	sqlStr.append("                     END AS ITEM_STD_CD               \n");
        	sqlStr.append("                    ,F_GET_PUR_STATUS ( A.DEPOSIT_CD, 'PUR_PAY_ACMP_GB' )|| '(' ||	F_GET_PUR_STATUS ( 	A.DEPOSIT_SEQ , 'PUR_PAY_ACMP_CHASU' ) || ')' DEPOSIT_NM               \n");
        	sqlStr.append("                    ,TO_CHAR(TO_DATE(B.APPR_DY,'YYYYMMDD'),'YYYY-MM-DD') APPR_DY               \n");
        	sqlStr.append("         FROM(               \n");                                                  
        	sqlStr.append("              SELECT C.DEPT_CD -- 현장코드               \n");                                                  
        	sqlStr.append("                    ,C.DEPT_NAME -- 현장명               \n");                                                  
        	sqlStr.append("                    ,C.CST_DOC_NAME -- 계약명               \n");                                                  
        	sqlStr.append("                    ,D.MEMBER_NAME -- 업체명               \n");                                                                                                                           
        	sqlStr.append("                    ,D.CORP_ID -- 업체코드               \n");
        	sqlStr.append("                    ,DEPOSIT_CD -- 지금구분코드               \n");                                                  
        	sqlStr.append("                    ,DEPOSIT_SEQ -- 지금구분SEQ               \n");
        	sqlStr.append("                    ,A.PAYREQ_ID               \n");                                                  
        	sqlStr.append("                    ,A.PUR_CONT_ID               \n");                                                                                                                                                  
        	sqlStr.append("                    ,NVL(A.PLAN_APPR_STATUS,'00') AS PLAN_APPR_STATUS -- 설계평가 상태               \n");
        	sqlStr.append("                    ,C.ORD_DOC_NO               \n");        	
        	sqlStr.append("                    ,B.PLAN_APPR_CHK_EMPNO               \n");
        	sqlStr.append("                    FROM PUR1100T A               \n");                                                  
        	sqlStr.append("                    ,CONT_PUR_PAYPLAN B               \n");                                                  
        	sqlStr.append("                    ,PUR2000T C               \n");                                                  
        	sqlStr.append("                    ,CONT_PUR_SUPP D               \n");                                                  
        	sqlStr.append("                    WHERE A.PAY_DIV = 2               \n");                                                  
        	sqlStr.append("                    AND D.SIGN_SEQ = 2               \n");                                                  
        	sqlStr.append("                    AND NVL(B.PLAN_APPR_YN,'N') = 'Y'               \n");                                                  
        	sqlStr.append("                    AND A.PAYREQ_ID = B.PAYREQ_ID               \n");                                                  
        	sqlStr.append("                    AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID               \n");                                                  
        	sqlStr.append("                    AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID               \n");                                                   
        	sqlStr.append("          )A ,               \n");                            
        	sqlStr.append("          (SELECT WRITE_NO               \n");
        	sqlStr.append("                , WRITE_NM         \n");
        	sqlStr.append("                , REGU_STATUS_CD         \n");
        	sqlStr.append("                , DOC_CD         \n");
        	sqlStr.append("                , APPR_TOT_RATE         \n");
        	sqlStr.append("                , APPR_NOTE         \n");
        	sqlStr.append("                , DEPT_CD         \n");
        	sqlStr.append("                , CORP_ID         \n");
        	sqlStr.append("                , CONT_ID         \n");
        	sqlStr.append("                , PAYREQ_ID         \n");
        	sqlStr.append("                , ARR_ID         \n");
        	sqlStr.append("                , APPR_DY         \n");
        	sqlStr.append("            FROM SUPP_APPR         \n");
        	sqlStr.append("           WHERE DOC_CD ='06'          \n");        	
        	sqlStr.append("          ) B               \n");
        	sqlStr.append("          WHERE 1=1               \n");                                                                                                   
        	sqlStr.append("          AND A.DEPT_CD = B.DEPT_CD(+)               \n");
        	sqlStr.append("          AND A.CORP_ID = B.CORP_ID(+)               \n");
        	sqlStr.append("          AND A.PUR_CONT_ID = B.CONT_ID(+)               \n");
        	sqlStr.append("          AND A.PAYREQ_ID = B.PAYREQ_ID(+)               \n");        	
	        	if(!params.get("searchDeptCd").equals(""))
	        	{
	        		sqlStr.append("         AND A.DEPT_CD = '"+params.get("searchDeptCd")+"'																																																				\n");
	        	}
	        	if(!params.get("build_name").equals(""))
	        	{
	        		sqlStr.append("         AND A.CST_DOC_NAME LIKE '%' ||'"+params.get("build_name")+"'|| '%'																																													\n");
	        	}
	        	if(!params.get("corp_kor").equals(""))
	        	{
	        		sqlStr.append("         AND A.MEMBER_NAME LIKE '%' ||'"+params.get("corp_kor")+"'|| '%'																																														\n");
	        	}
//	        	if(!"".equals(params.get("searchFromDate"))){
//	        		sqlStr.append( " AND TO_NUMBER(SUBSTR(B.APPR_DY, 1 ,6)) >= '"+Params.get("searchFromDate").replaceAll("-", "")+"'					\n");
//	        	}
//	        	if(!"".equals(params.get("searchToDate"))){
//	        		sqlStr.append(" AND TO_NUMBER(SUBSTR(B.APPR_DY, 1 ,6)) <= '"+Params.get("searchToDate").replaceAll("-", "")+"'					\n");
//	        	}
	        	if(!params.get("plan_appr_status").equals("")){
	        		sqlStr.append(" 		 AND NVL(A.PLAN_APPR_STATUS,'00') = ?                                                                                                         \n");
	        	}	        
//	        sqlStr.append(" 		 AND A.PLAN_APPR_CHK_EMPNO = '"+storage.getUser().get("ACCT_ID")+"'                                                                                                         \n");	
	        sqlStr.append("         ORDER BY DEPT_NAME, MEMBER_NAME DESC																																														\n");
        	
            
            search.setStatement(sqlStr.toString());  
            if(!params.get("plan_appr_status").equals("")){
            	search.addParameter( params.get("PLAN_APPR_STATUS"));
            }        	
          
            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;
        }
    }
    
    
    
    
}