/**
 * @(#)file    ContChgRcvListBiz.java
 * @(#)author  kyj
 * @(#)version 1.0
 * @(#)date    2015-01-22
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.outside.biz;


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.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.util.ValueObject;
import oracle.toplink.internal.ox.StrBuffer;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class ContChgRcvListBiz extends Biz {

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

	public void doBiz() throws Exception {
		
		if(storage.compareEvent("")){
			
		}else if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getContChgRcvList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}
	}


	/**
	 * 계약 대상리스트 조회
	 * @param Params
	 * @return
	 */
	private RowSet getContChgRcvList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();
       
        try{
			
        	/*sqlstr.append("   SELECT                                                                                                                \n");
			sqlstr.append("          A.SUB_CONT_REQ_ID                                                                                              \n");
			sqlstr.append("         ,A.SUB_CONT_MOD_ID                                                                                              \n");
			sqlstr.append("         ,B.DEPT_CD                                                                                                      \n");
			sqlstr.append("         ,GET_DEPT_NM(B.DEPT_CD) AS DEPT_NAME                                                                            \n");
			sqlstr.append("         ,B.CON_DOC_NO                                                                                                   \n");
			sqlstr.append("         ,B.MOD_NO+1 AS MOD_NO                                                                                           \n");
			sqlstr.append("         ,B.BUILD_NAME                                                                                                   \n");
			sqlstr.append("         ,C.CORP_ID                                                                                                      \n");
			sqlstr.append("         ,C.MEMBER_NAME                                                                                                  \n");
			sqlstr.append("         ,F_GET_D_CURRENCY(A.AFT_SUPP_AMT, F_GET_CURRENCY_DIGIT(B.CON_CUR_CD)) AS AFT_SUPP_AMT                           \n");
			sqlstr.append("         ,B.CON_CUR_CD                                                                                                   \n");
			sqlstr.append("         ,TO_CHAR(TO_DATE(B.CON_DATE), 'YYYY-MM-DD') AS CON_DATE                                                         \n");
			sqlstr.append("         , '~' || TO_CHAR(TO_DATE(A.AFT_CONT_DATE), 'YYYY-MM-DD') AS AFT_CONT_DATE                                       \n");
			sqlstr.append("         ,F_GET_CODE_NAME('MOD_GB', A.MOD_GB) AS MOD_GB                                                                  \n");
			sqlstr.append("         ,F_GET_CODE_NAME('SUB_REQSTATUS_CD', A.STATUS_CD) AS STATUS_CD                                                  \n");
			sqlstr.append("   FROM SUB3000T A,                                                                                                      \n");
			sqlstr.append("        SUB2000T B,                                                                                                      \n");
			sqlstr.append("        CONT_OUT_SUPP C                                                                                                  \n");
			sqlstr.append("   WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                                           \n");
			sqlstr.append("   AND   B.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID(+)                                                                        \n");
			sqlstr.append("   AND   A.STATUS_CD IN (SELECT DETAILCD FROM EBID_COM911T WHERE BASECD = 'SUB_REQSTATUS_CD' AND CHG_CODE01 = '1')       \n");
			sqlstr.append("   AND   C.SIGN_SEQ(+) = 2                                                                      							\n");*/
        	
        	
        	
			sqlstr.append("   SELECT                                                                                                                                       \n");
			sqlstr.append("          A.SUB_CONT_REQ_ID                                                                                                                     \n");
			sqlstr.append("         ,A.SUB_CONT_MOD_ID                                                                                                                     \n");
			sqlstr.append("         ,B.DEPT_CD                                                                                                                             \n");
			sqlstr.append("         ,GET_DEPT_NM(B.DEPT_CD) AS DEPT_NAME                                                                                                   \n");
			sqlstr.append("         ,B.CON_DOC_NO                                                                                                                          \n");
			sqlstr.append("         ,B.MOD_NO+1 AS MOD_NO                                                                                                                  \n");
			sqlstr.append("         ,B.BUILD_NAME                                                                                                                          \n");
			sqlstr.append("         ,C.CORP_ID                                                                                                                             \n");
			sqlstr.append("         ,C.MEMBER_NAME                                                                                                                         \n");
			sqlstr.append("         ,F_GET_D_CURRENCY(A.AFT_SUPP_AMT, F_GET_CURRENCY_DIGIT(B.CON_CUR_CD)) AS AFT_SUPP_AMT                                                  \n");
			sqlstr.append("         ,B.CON_CUR_CD                                                                                                                          \n");
			sqlstr.append("         ,TO_CHAR(TO_DATE(B.CON_DATE), 'YYYY-MM-DD') AS CON_DATE                                                                                \n");
			sqlstr.append("         , '~' || TO_CHAR(TO_DATE(A.AFT_CONT_DATE), 'YYYY-MM-DD') AS AFT_CONT_DATE                                                              \n");
			sqlstr.append("         ,'1' AS IMG_GB                                                                                                                         \n");
			sqlstr.append("         ,(SELECT MAX(DOC_ID)  FROM SIGN001T WHERE  REGEXP_REPLACE(KEY_COL1,'[^0-9]') = A.SUB_CONT_REQ_ID                                  \n");
			sqlstr.append("                AND REGEXP_REPLACE(KEY_COL2,'[^0-9]') = A.SUB_CONT_MOD_ID AND SIGN_CLASS_CD = 'ocreqD' AND  SIGN_DECI_CLASS = '14')  AS DOC_ID  \n");                                                                                          
			sqlstr.append("         ,(SELECT max(TO_CHAR(CRE_DATE,'YYYYMMDD'))  FROM SIGN001T WHERE  REGEXP_REPLACE(KEY_COL1,'[^0-9]') = A.SUB_CONT_REQ_ID                                  \n");
			sqlstr.append("                AND REGEXP_REPLACE(KEY_COL2,'[^0-9]') = A.SUB_CONT_MOD_ID AND SIGN_CLASS_CD = 'ocreqD' AND  SIGN_DECI_CLASS = '14')  AS year  \n");                                                                                          
			sqlstr.append("         ,F_GET_CODE_NAME('MOD_GB', A.MOD_GB) AS MOD_GB                                                                                         \n");
			sqlstr.append("         ,F_GET_CODE_NAME('SUB_REQSTATUS_CD', A.STATUS_CD) AS STATUS_CD                                                                         \n");
			sqlstr.append("   FROM SUB3000T A,                                                                                                                             \n");
			sqlstr.append("        SUB2000T B,                                                                                                                             \n");
			sqlstr.append("        CONT_OUT_SUPP C                                                                                                                         \n");
			sqlstr.append("   WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                                                                  \n");
			sqlstr.append("   AND   B.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID(+)                                                                                               \n");
			sqlstr.append("   AND   A.STATUS_CD IN (SELECT DETAILCD FROM EBID_COM911T WHERE BASECD = 'SUB_REQSTATUS_CD' AND CHG_CODE01 = '1')                              \n");
			sqlstr.append("   AND   C.SIGN_SEQ(+) = 2                                                                                                                      \n");
			//sqlstr.append("   AND A.STATUS_CD = '30'                                                                                                                       \n");
        	
        	// 현장
        	if(!param.get("P_DEPT_CD").isEmpty()) {
        		sqlstr.append(" AND B.DEPT_CD = ? 																												\n");
    			search.addParameter(params.get("P_DEPT_CD"));
        	}
        	if(!param.get("P_BUILD_NM").isEmpty())	//계약명
        		sqlstr.append("   AND UPPER(B.BUILD_NAME) LIKE '%"+param.get("P_BUILD_NM").toUpperCase()+"%'				\n");
        	if(!param.get("p_dealer_nm").isEmpty())		//업체명
        		sqlstr.append("   AND UPPER(C.MEMBER_NAME) LIKE '%'|| '"+param.get("p_dealer_nm").toUpperCase()+"' ||'%'				\n");
        	if(!param.get("ContFromDt").isEmpty())		//계약기간
        		sqlstr.append("   AND TO_CHAR(B.CON_DATE, 'YYYY-MM-DD') >= '"+param.get("ContFromDt")+"'				\n");
        	if(!param.get("ContToDt").isEmpty())
        		sqlstr.append("   AND TO_CHAR(B.CON_DATE, 'YYYY-MM-DD') <= '"+param.get("ContToDt")+"'				\n");
        	if(!param.get("P_CONT_REQ_STATUS").equals("00"))	//변경계약상태
        		sqlstr.append("   AND A.STATUS_CD = '"+param.get("P_CONT_REQ_STATUS")+"'				\n");
        	
        	sqlstr.append("   ORDER BY B.CON_DOC_NO,B.MOD_NO DESC,B.BUILD_NAME ASC 	\n");
			
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	
}