/**
 * @(#)file    ContIntBiz.java
 * @(#)author  Kim Myung Ho
 * @(#)version 1.0
 * @(#)date    2014-08-11
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.outside.biz;


import java.sql.Connection;

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 oracle.toplink.internal.ox.StrBuffer;

import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class ContResultInsurBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();

		
		if(storage.compareEvent("")){
			
            user = getContRequestValueObject(params.get("SUB_CONT_MOD_ID"));
            storage.addValueObject("ContInfo", user);
			
		}			
	}
	
	
	/**
	 * 계약 상세 정보
	 * @param Params
	 * @return
	 */
	private ValueObject getContRequestValueObject(String SUB_CONT_MOD_ID) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
	        	sqlstr.append(" 	SELECT A.SUB_CONT_MOD_ID	\n");
	        	sqlstr.append(" 	     , A.CON_DOC_NO	\n");
	        	sqlstr.append(" 	     , A.MOD_NO	\n");	
	        	sqlstr.append(" 	     , DECODE(A.MOD_GB, NULL, '00', A.MOD_GB) AS MOD_GB	\n");	
	        	sqlstr.append(" 	     , A.DEPT_NAME	\n");	
	        	sqlstr.append(" 	     , A.BUILD_NAME	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE, 'YYYY-MM-DD') AS CON_DATE	\n");				//수정 (2014-09-19)
	        	sqlstr.append(" 	     , A.DEALER_NM	\n");	
	        	sqlstr.append(" 	     , A.CON_AMT	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE, 'YYYY-MM-DD') AS START_WORK_DATE	\n");				//수정 (2014-09-19)
	        	sqlstr.append(" 	     , A.ELEC_CON_YN	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'YYYY') AS START_WORK_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'MM') AS START_WORK_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'DD') AS START_WORK_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') AS END_WORK_DATE	\n");				//수정 (2014-09-19)
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'YYYY') AS END_WORK_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'MM') AS END_WORK_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'DD') AS END_WORK_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.CON_GB 	\n");
	        	sqlstr.append(" 	     , A.CON_CUR_CD 	\n");														//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.UND_CON_NAME 	\n");	
	        	sqlstr.append(" 	     , A.SUB_UND_CON_NAME 	\n");	
	        	sqlstr.append(" 	     , A.WORK_PLACE 	\n");	
	        	sqlstr.append(" 	     , A.CON_SUPPLY_AMT 	\n");	
	        	sqlstr.append(" 	     , A.LABOR_AMT 	\n");	     	
	        	sqlstr.append(" 	     , A.CON_SUPPLY_TAMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_SUPPLY_NAMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_SURTAX_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_RATE 	\n");	
	        	sqlstr.append(" 	     , A.PAY_WAY_BILL 	\n");	
	        	sqlstr.append(" 	     , A.PAY_WAY_CASH 	\n");	
	        	
	        	sqlstr.append(" 	     , A.STAMP_DUTY_GB	\n");

	        	sqlstr.append(" 	     , A.PREPAY_SUPPLY_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_SUPPLY_TAMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_SUPPLY_NAMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_SURTAX_AMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_EST_GUAR_AMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_EST_GUAR_RATE 	\n");	
	        	sqlstr.append(" 	     , A.CON_GUAR_TERM 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.DEF_EST_GUAR_RATE 	\n");	
	        	sqlstr.append(" 	     , A.DEF_GUAR_AMT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.DEF_EST_TERM 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.DELAY_RATE 	\n");	
	        	sqlstr.append(" 	     , A.PAY_COND_GB 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , F_GET_CODE_NAME('SUB_ACMP_PAY_TYPE', A.PAY_COND_GB) PAY_COND_GB_NM 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.PAY_CONTENT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.CON_SPE_TXT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.LABOR_GUAR_JOIN_AMT_GB 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'YYYY') AS CON_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'MM') AS CON_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'DD') AS CON_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.STATUS_CD 	\n");	
	        	sqlstr.append(" 	     , A.MONTH_ACMP_CNT 	\n");	
	        	sqlstr.append(" 	     , A.OBJ_RCPT_DAY 	\n");	
	        	sqlstr.append(" 	     , A.TEMPLATE_CD 	\n");	
	        	sqlstr.append(" 	     , A.TRUE_RANDOM 	\n");	
	        	sqlstr.append(" 	     , A.CONT_HASH 	\n");	
	        	sqlstr.append(" 	     , A.MODI_REQ_REASON 	\n");	
	        	sqlstr.append(" 	     , A.MODI_REQ_DT 	\n");	
	        	sqlstr.append(" 	     , A.CON_CLS_YN 	\n");	
	      
	        	sqlstr.append(" 	     , B.DEMANDED_NO	\n");	
	        	sqlstr.append(" 	     , B.DEMANDED_TITLE	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(TO_DATE(B.DEMANDED_DATE),'YYYY-MM-DD') AS DEMANDED_DATE	\n");	
	        	sqlstr.append(" 	     , B.REQ_DEPT_NM	\n");	
	        	sqlstr.append(" 	     , B.EMP_NO	\n");	
	        	sqlstr.append(" 	     , B.EMP_NM	\n");	
	        	sqlstr.append("       	 , GET_OUT_ITEM_NM(B.OUT_ITEM_CD) AS OUT_ITEM_NM						\n");			//2014*-10-02 공종 추가
	        	/*분개 유형*/
	        	sqlstr.append(" 	     , A.ACCT_GB 	\n");	
	        	sqlstr.append(" 	     , A.PAY_GROUP 	\n");	
	        	sqlstr.append(" 	     , A.PROOF_CD 	\n");	
	        	sqlstr.append(" 	     , A.PAYMENT_TERMS 	\n");	
	        	sqlstr.append(" 	     , A.PAY_ITEM 	\n");	
	        	sqlstr.append(" 	     , A.COST_CODE 	\n");	
	        	sqlstr.append(" 	     , A.COST_TYPE 	\n");	
	        	sqlstr.append(" 	     , A.DEPT_CD 	\n");	
	        	sqlstr.append(" 	     , F_GET_PAY_ITEM_EST_DESC(A.PAY_ITEM,A.COST_CODE,A.COST_TYPE,A.DEPT_CD) AS PAY_ITEM_NM	\n");	
	        	sqlstr.append(" 	     , (SELECT AC_UNIT_CD FROM ACZ10100V Z WHERE  Z.DEPT_CD = A.DEPT_CD) AS  AC_UNIT_CD	\n");
	        	//승인자 추가
	        	sqlstr.append(" 	     , A.CON_CONFIRM_ID 	\n");	
	        	sqlstr.append(" 	     , A.CON_CONFIRM_NAME 	\n");	
	        	
	        	sqlstr.append(" 	     , (SELECT MAX(MOD_NO) FROM SUB2000T WHERE SUB_CONT_ID=A.SUB_CONT_ID) AS MAX_MOD_NO	\n");		//변경 계약을 위해 차수 Max 값을 조회 한다.        	
	        	
	        	sqlstr.append(" 	     , DECODE(D.CORP_ID, '', 0, D.CORP_ID)  AS O_CORP_ID	\n");	
	        	sqlstr.append(" 	     , DECODE(D.MEMBER_NAME, '', C.CORP_KOR, D.MEMBER_NAME)  AS O_MEMBER_NAME	\n");	
	        	sqlstr.append(" 	     , DECODE(D.ADDRESS, '', C.ADDRESS, D.ADDRESS) AS O_ADDRESS	\n");	
	        	sqlstr.append(" 	     , DECODE(D.BOSS_NAME, '', C.REP_NAME, D.BOSS_NAME) AS O_BOSS_NAME	\n");
	        	sqlstr.append(" 	     , DECODE(D.VENDCD, '', C.CORP_REG_NO, D.VENDCD) AS O_REG_NO	\n");
	        	sqlstr.append(" 	     , D.USER_NAME AS O_USER_NAME	\n");	
	        	sqlstr.append(" 	     , D.TEL_NUM AS O_TEL_NUM	\n");	
	        	sqlstr.append(" 	     , D.DIVISION AS O_DIVISION	\n");	
	        	sqlstr.append(" 	     , D.POSITION AS O_POSITION	\n");	
	        	sqlstr.append(" 	     , D.HP AS O_HP	\n");	
	        	sqlstr.append(" 	     , D.EMAIL AS O_EMAIL	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(D.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS O_SIGN_DATE	\n");	
	        	sqlstr.append(" 	     , D.VENDCD AS O_VENDCD	\n");		        	
	        	sqlstr.append(" 	     , E.CORP_ID  AS C_CORP_ID	\n");	
	        	sqlstr.append(" 	     , E.MEMBER_NAME AS C_MEMBER_NAME	\n");	
	        	sqlstr.append(" 	     , E.ADDRESS AS C_ADDRESS	\n");	
	        	sqlstr.append(" 	     , E.BOSS_NAME AS C_BOSS_NAME	\n");	
	        	sqlstr.append(" 	     , E.USER_NAME AS C_USER_NAME	\n");	
	        	sqlstr.append(" 	     , E.TEL_NUM AS C_TEL_NUM	\n");	
	        	sqlstr.append(" 	     , E.DIVISION AS C_DIVISION	\n");	
	        	sqlstr.append(" 	     , E.POSITION AS C_POSITION	\n");	
	        	sqlstr.append(" 	     , E.HP AS C_HP	\n");		        	
	        	sqlstr.append(" 	     , E.VENDCD AS C_VENDCD	\n");		        	
	        	sqlstr.append(" 	     , E.EMAIL AS C_EMAIL	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(E.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS C_SIGN_DATE	\n");	

	        	sqlstr.append(" 	     , F.INSUR_EMPL_YN  	\n");	
	        	sqlstr.append(" 	     , F.INSUR_ACC_YN 	\n");	
	        	sqlstr.append(" 	     , F.WORK_MNGR_NO 	\n");	
	        	sqlstr.append(" 	     , F.BIZ_REG_NO	\n");	
	        	sqlstr.append(" 	     , F.CORP_REG_NO	\n");	
	        	sqlstr.append(" 	     , F.LCNS_NM	\n");	
	        	sqlstr.append(" 	     , F.LCNS_REG_NO 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(F.LCNS_ACQT_DATE, 'YYYY-MM-DD') AS LCNS_ACQT_DATE	\n");	
	        	sqlstr.append("          , F.MTIL_AMT 	\n");	
	        	sqlstr.append("          , F.STATUS_CD AS INSUR_STATUS_CD 	\n");	
	        	sqlstr.append(" 	     , F.WORK_PLACE AS INSUR_WORK_PLACE 	\n");	
	        	sqlstr.append(" 	     , F_GET_CURRENCY_DIGIT(A.CON_CUR_CD) AS SOSUNUM 	\n");	
	        	sqlstr.append(" 	     , (SELECT SUB_PLACE_NO FROM SUPP_INFO WHERE CORP_ID = E.CORP_ID AND JOB_DIV = 'O' ) SUB_PLACE_NO	\n");  //2020.7.13 사업장 관리번호
				sqlstr.append("  FROM SUB2000T A, BID_MASTER B, HEAD_OFFICE_INFO C, CONT_OUT_SUPP D, CONT_OUT_SUPP E , CONT_OUT_INSUR F	\n");
				sqlstr.append("  WHERE A.BID_NO = B.BID_NO(+) 	\n");
				sqlstr.append("  AND A.BID_DEG = B.BID_DEG(+)	\n");
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = D.SUB_CONT_MOD_ID(+)	\n");
				sqlstr.append("  AND D.SIGN_SEQ(+) = 1 --원사업자	\n");
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = E.SUB_CONT_MOD_ID(+)	\n");
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID	\n");
				sqlstr.append("  AND E.SIGN_SEQ(+) = 2 --계약업체정보  	\n");
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = '"+SUB_CONT_MOD_ID+"'							\n");

	            search.setStatement(sqlstr.toString());
	            rowSet = search.execute();

            
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug("WAFSQLException : " + se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//WAFLogger.debug("### valueobject size : " + valueobject.size());
            return valueobject;
        }
	}
	
}