/**
 * @(#)file    PreRequestSignBiz.java
 * @(#)author  OK
 * @(#)version 1.0
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.bid.purchase.biz;

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.Row;
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.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;

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

/**
 * 
 * @version 1.0
 */
public class ReBidSignBiz extends Biz {
	BidCommonBiz BidCommonBiz = new BidCommonBiz(storage);
	public ReBidSignBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		
		ValueObject vo = new ValueObject();
		RowSet suppinfo = null;
		RowSet oldBidSignInfo = null;
		
		vo = getBidMaster(params.get("sign_bid_no"),params.get("sign_bid_deg"));	//입찰 정보 조회
		suppinfo = getSuppInfo(params.get("sign_bid_no"),params.get("sign_bid_deg"));	//입찰 업체 정보 조회
		oldBidSignInfo = getOldBidSignInfo(params.get("sign_bid_no"),params.getInt("sign_bid_deg"));	//기존 입찰계획서 조회
			
        //String doctitle = "-"+vo.get("BID_MASTER_NM")+vo.get("DEPT_NM");
		//2015.8.10 조재윤과장 요청. 타이틀 순서변경
		String doctitle = "["+vo.get("DEPT_NM")+"]" + vo.get("BID_MASTER_NM");
        vo.set("doctitle", doctitle);
        
        storage.addValueObject(vo.get("DOC_ETC_IN"), BidCommonBiz.getfilelist(vo.get("DOC_ETC_IN"),vo.get("DEMANDED_ID"),vo.get("DOC_ETC_IN")));
        storage.addValueObject(vo.get("DOC_ETC_OUT"), BidCommonBiz.getfilelist(vo.get("DOC_ETC_OUT"),vo.get("DEMANDED_ID"),vo.get("DOC_ETC_OUT")));
		storage.addValueObject("bidmaster", vo);
        storage.addRowSet("suppinfo", suppinfo);
        storage.addRowSet("oldBidSignInfo", oldBidSignInfo);
        storage.addValueObject("SIGN_INFO", getSignInfo(vo.get("DEMANDED_ID")));
   }
	

	public ValueObject getBidMaster(String sign_bid_no, String sign_bid_deg) {
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StrBuffer sqlstr = new StrBuffer();
		ValueObject rowObject = null;

		try {
			sqlstr.append("SELECT A.REQ_DEPT_NM				\n");
			sqlstr.append("       ,F_GET_USER_NM('"+params.get("ACCT_ID")+"')  AS CRE_NAME --작성자				\n");
			sqlstr.append("       ,TO_CHAR(SYSDATE,'YYYY-MM-DD') AS CRE_DT --작성일				\n");
			sqlstr.append("       ,(select REPLACE(ORDER_COMP_NM,'T_','') from ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = A.DEPT_CD) DEALER_NM -- 발주처				\n");
			sqlstr.append("       ,(SELECT DEPT_NM FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = A.DEPT_CD) AS DEPT_NM -- pjt명				\n");
			sqlstr.append("       ,TO_CHAR(A.SITEDESCDATE,'YYYY-MM-DD HH24:MI') AS SITEDESCDATE -- 현장설명 예정일				\n");
			sqlstr.append("       ,A.SITEDESCPLACE -- 현장설명 장소				\n");
			sqlstr.append("       ,A.BID_MASTER_NM  -- 계약명				\n");
			sqlstr.append("       ,A.BID_NO -- 발주의뢰번호				\n");
			sqlstr.append("       ,A.BID_DEG -- 발주의뢰번호				\n");
			sqlstr.append("       ,A.DEPT_CD -- 현장코드																																		\n");
			sqlstr.append("       ,F_GET_CODE_NAME('ONBID',A.ONBID_YN)||'/'||F_GET_CODE_NAME('CMPT_CD',A.CMPT_CD) AS BID_TYPE-- 입찰방식				\n");
			sqlstr.append("       ,A.ACT_AMT -- 예산금액				\n");
			sqlstr.append("       ,A.PUR_AMT -- 예산금액				\n");
			sqlstr.append("       ,TO_CHAR(A.BID_TO_DT,'YYYY-MM-DD') AS BID_TO_DT --입찰마감일				\n");
			sqlstr.append("       ,TO_CHAR(TO_DATE(A.DELI_FROM_DY,'YYYYMMDD'),'YYYY-MM-DD')	AS DELI_FROM_DY			\n");
			sqlstr.append("       ,TO_CHAR(TO_DATE(A.DELI_TO_DY,'YYYYMMDD'),'YYYY-MM-DD')	AS DELI_TO_DY			\n");
			sqlstr.append("       ,F_GET_CODE_NAME('PUR_LEAD_GB',A.DELIVERY_CONDITION) AS DELIVERY_CONDITION_NM --납품조건				\n");
			sqlstr.append("       ,DECODE(A.CBC_FIRST_RATE,'','',A.CBC_FIRST_RATE||'% ') || A.PBC_FIRST_CONDI AS FIRST_AMT-- 선급금				\n");
			sqlstr.append("       ,DECODE(A.CPC_MID_RATE,'','',A.CPC_MID_RATE||'% ') || A.PBC_MID_CONDI AS MID_AMT -- 중도금				\n");
			sqlstr.append("       ,DECODE(A.PBC_REST_RATE,'','',A.PBC_REST_RATE||'% ') || A.PBC_REST_CONDI AS REST_AMT-- 잔금				\n");
			sqlstr.append("       ,A.REBID_REASON			\n");
			sqlstr.append("       ,A.INNER_REMARKS			\n");
			sqlstr.append("       ,A.DOC_ETC_IN					\n");
			sqlstr.append("       ,A.OUTER_REMARKS			\n");
			sqlstr.append("       ,A.DOC_ETC_OUT				\n");
			sqlstr.append("       ,A.DEMANDED_ID				\n");
			sqlstr.append("       ,A.CMPT_CD						\n");
			sqlstr.append("       ,NVL(A.DELI_END_PLACE,(SELECT DELI_END_PLACE 																\n");
			sqlstr.append("       						   FROM PMM_DEMANDED_REQUEST 														\n");
			sqlstr.append("       						  WHERE DEMANDED_ID = A.DEMANDED_ID)) DELI_END_PLACE --	공사(납품)장소					\n");
			sqlstr.append("  FROM BID_MASTER A				\n");
			sqlstr.append("  WHERE A.BID_NO = '" + sign_bid_no + "'					\n");
			sqlstr.append("    AND A.BID_DEG = '" + sign_bid_deg + "'				\n");
			search.setStatement(sqlstr.toString());
			listRowSet = search.execute();
			rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
		} catch (Exception e) {
			WAFLogger.error(this.getClass().getName()
					+ " --> getBidMaster() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return null;
		} finally {
			if (rowObject == null)
				rowObject = new ValueObject();
			return rowObject;
		}
	}

	public RowSet getSuppInfo(String sign_bid_no, String sign_bid_deg) {
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StrBuffer sqlstr = new StrBuffer();
		try {
			/*sqlstr.append("SELECT A.VENDOR_NAME		\n");
			sqlstr.append(",A.RECM_DEPT_NM		\n");
			sqlstr.append(",B.CREDIT_GRADE		\n");
			sqlstr.append(",(SELECT F_GET_D_CURRENCY(NVL(X.ESTI_TOT_AMT,0), F_GET_CURRENCY_DIGIT(NVL(Y.CURRENCY_GB, 'KRW' ) )) FROM BID_SUPP X , BID_PUR_CONDI Y WHERE X.BID_NO = Y.BID_NO AND X.BID_DEG = Y.BID_DEG AND  X.VENDOR_CD= Y.VENDOR_CD AND  X.BID_NO = A.BID_NO AND X.BID_DEG = A.BID_DEG - 1 AND X.VENDOR_CD = A.VENDOR_CD) AS BE_ESTI_TOT_AMT	\n");
			sqlstr.append(",DECODE(D.PUR_AMT,'0',' ',REPLACE(TRIM(TO_CHAR( NVL(A.WON_ESTI_TOT_AMT, A.ESTI_TOT_AMT)/D.PUR_AMT * 100,'999,999,990.99')),'.00','')) AS PUR_RATE		\n");
			sqlstr.append(",(SELECT CST_DOC_NAME FROM PUR1000T WHERE PUR_CONT_ID = C.PUR_CONT_ID) AS CST_DOC_NAME		\n");
			sqlstr.append(",A.RECOMMEND_REASON						\n");
			sqlstr.append("FROM BID_SUPP A		\n");
			sqlstr.append(", SUPP_INFO B		\n");
			sqlstr.append(", (SELECT MAX(PUR_CONT_ID) AS PUR_CONT_ID,CORP_ID FROM PUR1000T GROUP BY CORP_ID) C		\n");
			sqlstr.append(", BID_MASTER D		\n");
			sqlstr.append(", (SELECT NVL(X.ESTI_TOT_AMT,0)  FROM BID_SUPP X  WHERE  X.BID_NO = A.BID_NO AND X.BID_DEG = A.BID_DEG - 1 AND X.VENDOR_CD = A.VENDOR_CD) AS BE_ESTI_TOT_AMT \n");
			sqlstr.append("WHERE A.VENDOR_CD = B.CORP_ID(+)		\n");
			sqlstr.append("AND A.VENDOR_CD = C.CORP_ID(+)		\n");
			sqlstr.append("AND A.BID_NO = D.BID_NO		\n");
			sqlstr.append("AND A.BID_DEG = D.BID_DEG  		\n");
			sqlstr.append("AND a.BID_NO = '" + sign_bid_no + "'			\n");
			sqlstr.append("AND a.BID_DEG = '" + sign_bid_deg + "'		\n");
			2015.8.26 변경 
			*/
			//2015.12.17 CURRENCY_GB추가
			sqlstr.append("						SELECT VENDOR_NAME                                                                                                                                                        \n");
			sqlstr.append("						          ,  RECM_DEPT_NM                                                                                                                                                 \n");
			sqlstr.append("						          ,  CREDIT_GRADE                                                                                                                                                 \n");
			sqlstr.append("						          ,  F_GET_D_CURRENCY(NVL(BE_ESTI_TOT_AMT,0), 0)   AS BE_ESTI_TOT_AMT                                                \n");
			sqlstr.append("						          , DECODE(PUR_AMT,'0',' ',REPLACE(TRIM(TO_CHAR( NVL(BE_ESTI_TOT_AMT, 0)/PUR_AMT * 100,'999,999,990.99')),'.00','')) AS PUR_RATE                                  \n");
			sqlstr.append("						          , CST_DOC_NAME                                                                                                                                                  \n");
			sqlstr.append("						          , RECOMMEND_REASON   , 'KRW' CURRENCY_GB                                                                                                                                                 \n");
			sqlstr.append("						FROM (                                                                                                                                                                    \n");
			sqlstr.append("						SELECT A.VENDOR_NAME                                                                                                                                                      \n");
			sqlstr.append("						,A.RECM_DEPT_NM                                                                                                                                                           \n");
			sqlstr.append("						,B.CREDIT_GRADE                                                                                                                                                           \n");
			sqlstr.append("						,(SELECT NVL(X.ESTI_TOT_AMT,0)* NVL(EXCHANGE,1)  FROM BID_SUPP X  WHERE  X.BID_NO = A.BID_NO AND X.BID_DEG = A.BID_DEG - 1 AND X.VENDOR_CD = A.VENDOR_CD) AS BE_ESTI_TOT_AMT               \n");
			sqlstr.append("						,E.CURRENCY_GB                                                                                                                                                            \n");
			sqlstr.append("						,D.PUR_AMT                                                                                                                                                                \n");
			//sqlstr.append("						,(SELECT DEPT_NAME FROM SUB2000T WHERE SUB_CONT_MOD_ID = (SELECT MAX(SUB_CONT_MOD_ID) FROM CONT_OUT_SUPP WHERE CORP_ID = A.VENDOR_CD)) CST_DOC_NAME                                                                                  \n");
			sqlstr.append("						,(SELECT CST_DOC_NAME FROM PUR2000T WHERE PUR_CONT_MOD_ID = (SELECT MAX(PUR_CONT_MOD_ID) FROM CONT_PUR_SUPP WHERE CORP_ID = A.VENDOR_CD)) CST_DOC_NAME                    \n");
			sqlstr.append("						,A.RECOMMEND_REASON                                                                                                                                                       \n");
			sqlstr.append("						FROM BID_SUPP A                                                                                                                                                           \n");
			sqlstr.append("						, SUPP_INFO B                                                                                                                                                             \n");
			sqlstr.append("						, BID_MASTER D                                                                                                                                                            \n");
			sqlstr.append("						, BID_PUR_CONDI E                                                                                                                                                              \n");
			sqlstr.append("						WHERE A.VENDOR_CD = B.CORP_ID(+)                                                                                                                                          \n");
			sqlstr.append("						AND A.BID_NO = D.BID_NO                                                                                                                                                   \n");
			sqlstr.append("						AND A.BID_DEG = D.BID_DEG                                                                                                                                                 \n");
			sqlstr.append("						AND A.BID_NO = E.BID_NO(+)                                                                                                                                                   \n");
			sqlstr.append("						AND A.BID_DEG-1 = E.BID_DEG(+)                                                                                                                                                 \n");
			sqlstr.append("						AND A.VENDOR_CD = E.VENDOR_CD(+)                                                                                                                                                \n");
			sqlstr.append("						AND a.BID_NO = '" + sign_bid_no + "'	                                                                                                                               \n");
			sqlstr.append("						AND a.BID_DEG = '" + sign_bid_deg + "'                                                                                                                                                       \n");
			sqlstr.append("						)                                                                                                                                                                         \n");
			sqlstr.append("						ORDER BY REPLACE(REPLACE(VENDOR_NAME,'(주)'),'㈜')        																																																								\n");			
			
			search.setStatement(sqlstr.toString());
			listRowSet = search.execute();
		} catch (Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> getSuppInfo() : "
					+ e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return null;
		} finally {
			if (listRowSet == null)
				listRowSet = null;
			return listRowSet;
		}
	}
	
	/**
	 * 발주의뢰 결재 정보 조회
	 * @param DEMANDED_ID
	 * @return
	 */
	private ValueObject getSignInfo(String DEMANDED_ID) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("SELECT DEMANDED_ID, SIGN_CLASS_CD, DOC_ID,YEAR,  SIGN_DECI_CLASS, APPROVAL_DATE, APPROVAL_SABUN, APPROVAL_USER                  \n");
        	sqlstr.append("FROM (                                                                                                                    \n");
        	sqlstr.append("          SELECT A.DEMANDED_ID, C.SIGN_CLASS_CD, C.DOC_ID, TO_CHAR(C.CRE_DATE,'YYYYMMDD') YEAR, SIGN_DECI_CLASS, APPROVAL_DATE, APPROVAL_SABUN, APPROVAL_USER  \n");
        	sqlstr.append("          FROM PMM_DEMANDED_REQUEST A, ETECEPC.MR_REQ_PO B, SIGN001T C                                              \n");
        	sqlstr.append("          WHERE A.DEMANDED_ID = B.BUYREQNO                                                      \n");
        	sqlstr.append("          AND B.APPR_ID = C.SIGN_INTERFACE_ID                                                                             \n");
        	sqlstr.append("          AND  C.SIGN_CLASS_CD = 'ebid_mrD'                                                                                    \n");
        	sqlstr.append("          AND C.SIGN_DECI_CLASS IN ( '14', '24')                                                                          \n");
        	sqlstr.append("          UNION ALL                                                                                                       \n");
        	sqlstr.append("          SELECT  A.DEMANDED_ID,  SIGN_CLASS_CD, DOC_ID,TO_CHAR(B.CRE_DATE,'YYYYMMDD') YEAR, SIGN_DECI_CLASS, APPROVAL_DATE, APPROVAL_SABUN, APPROVAL_USER    \n");
        	sqlstr.append("          FROM PMM_DEMANDED_REQUEST A, SIGN001T B                                                                   \n");
        	sqlstr.append("          WHERE  A.DEMANDED_KIND IN ('C', 'P',  'O')                                                                            \n");
        	sqlstr.append("          AND  B.SIGN_CLASS_CD = 'prD'                                                                                    \n");
        	sqlstr.append("          AND B.SIGN_DECI_CLASS IN ( '14', '24')                                                                          \n");
        	sqlstr.append("          AND A.DEMANDED_ID =  REGEXP_REPLACE(B.KEY_COL1,'[^0-9]')                                                                                  \n");
        	sqlstr.append(") A                                                                                                                       \n");
        	sqlstr.append("WHERE 1=1                                                                                                                 \n");
        	sqlstr.append("AND A.DEMANDED_ID = ?                                                                                                     \n");
        	sqlstr.append("ORDER BY DEMANDED_ID                                                                                                      \n");

        	
        	search.setStatement(sqlstr.toString());
        	search.addParameter(DEMANDED_ID);
            rowSet = search.execute();
            if(rowSet.size()>0){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        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();
            }
        }
        return vo;
	}
	
	public RowSet getOldBidSignInfo(String sign_bid_no, int sign_bid_deg) {
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StrBuffer sqlstr = new StrBuffer();
		try {
			sqlstr.append("SELECT A.BID_NO || '-' || A.BID_DEG AS OLD_BID_NO, B.SIGN_CLASS_CD, B.DOC_ID, TO_CHAR(B.CRE_DATE,'YYYYMMDD') YEAR, B.SIGN_DECI_CLASS, B.APPROVAL_DATE, B.APPROVAL_SABUN, B.APPROVAL_USER \n");
			sqlstr.append("  from BID_MASTER A, SIGN001T B \n");
			sqlstr.append(" where A.BID_NO = B.KEY_COL1 \n");
			sqlstr.append("   AND A.BID_DEG = B.KEY_COL2 \n");
			sqlstr.append("   AND B.SIGN_CLASS_CD IN ('ebid_pinqD', 'ebid_prebqD') \n");
			sqlstr.append("   AND B.SIGN_DECI_CLASS IN ( '14', '24')    \n");
			sqlstr.append("   AND A.BID_NO = ? -- BID_NO \n");
			sqlstr.append("   AND A.BID_DEG = ? -- BID_DEG \n");
			sqlstr.append(" ORDER BY A.BID_DEG    \n");

			search.setStatement(sqlstr.toString());
			search.addParameter(sign_bid_no);
			search.addParameter(sign_bid_deg - 1);
			
			listRowSet = search.execute();
		} catch (Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> getSuppInfo() : "
					+ e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return null;
		} finally {
			if (listRowSet == null)
				listRowSet = null;
			return listRowSet;
		}
	}	
}
