/**
 * @(#)file    BidIngBiz.java
 * @(#)author  Son Hyung Min 
 * @(#)version 1.0
 * @(#)date    2014-06-23
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.bid.purchase.biz;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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.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 kr.co.udapsoft.common.code.CommCodeManager;
import kr.co.udapsoft.common.sender.MessageSend;
import kr.co.udapsoft.ebid.buyer.bid.outside.biz.ScrchInfoBiz;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

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;

import kr.co.udapsoft.ebid.buyer.bid.outside.biz.ScrchInfoBiz;
   
public class TBECheckSaveBiz extends Biz {

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

	public void doBiz() throws Exception {
		boolean success = false;
        success = saveProc(storage.getEvent());
			
		if(success){
			 // SMS // // E-MAIL //
            ScrchInfoBiz ScrchInfo = new ScrchInfoBiz(storage);
            ScrchInfo.Send(params, storage.getEvent(),params.get("FILE_REQUEST_SUPP"));
            if(storage.getEvent().equals("TBE_FIN_SAVE")){
            	BidCommonBiz BidCommonBiz = new BidCommonBiz(storage);
            	BidCommonBiz.SP_REG_PSR_STATUS("16",params.get("DEMANDED_ID"),storage.getUser().get("acct_id"));
            	
            	//2015.3.24 TBE완료시 메일발송 
            	ValueObject email_info = email_info();
            	MessageSend messageSend = new MessageSend(storage);
            	StringBuffer mailtitle = new StringBuffer();
            	StringBuffer mailtitlein = new StringBuffer();
            	StringBuffer mailContent = new StringBuffer();
            	mailtitle.append("[기술검토완료]"+ email_info.get("DEPT_NM") + ", " +email_info.get("DEMANDED_TITLE")+" ");
            	mailtitlein.append("* 기술문서검토 완료 메일입니다.");
            	mailContent.append("다음의 입찰 건에 대해 기술검토가 완료되었음을 알려드립니다.<br>");
            	mailContent.append("전자조달 시스템에 로그인하시어 "+'"'+"낙찰업체선정"+'"'+" 메뉴에서 확인 바랍니다. ");
            	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
            	mailContent.append("<br>");
            	mailContent.append("<br>");
            	mailContent.append("1.프로젝트 : " + email_info.get("DEPT_NM") + "<br>");
            	mailContent.append("2.입 찰 명 : " + email_info.get("DEMANDED_TITLE") + " ( " + email_info.get("BID_NO") +" )<br>");
            	mailContent.append("3.기술검토자 : " +email_info.get("GRADE_NAME")+" "+ email_info.get("USER_NAME") +" (사내번호 : " + email_info.get("TEL_NO") + ")<br>");
            	mailContent.append("</font>");
            	messageSend.sendEmail(email_info.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent,"N");
            	//2015.3.24 TBE완료시 메일발송 END
            }
		}
		storage.setSuccess(success);
	}
	
	public ValueObject email_info() {

		Search search = new SearchImpl();
		 ValueObject valueobject = null;
		RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	sqlstr.append("		SELECT B.E_MAIL                                                                         \n");
        	sqlstr.append("		     , B.USER_NAME  AS OPEN_EMPNM                                                     	\n");
        	sqlstr.append("		     , A.DEPT_NM                                                                       	\n");
        	sqlstr.append("		     , A.DEMANDED_TITLE                                                                	\n");
        	sqlstr.append("		     , A.BID_NO                                                                      	\n");
        	sqlstr.append("		     , F_GET_D_CURRENCY(A.ACT_AMT, F_GET_CURRENCY_DIGIT('KRW')) AS ACT_AMT                                                                      	\n");
        	sqlstr.append("		     , 'KRW' AS CURRENCY_GB                                                            	\n");
        	sqlstr.append("		     ,  DECODE(A.DEMANDED_KIND , 'O', '외주' , '구매') AS DEMANDED_KIND_NM              	\n");
        	sqlstr.append("		     ,  C.USER_NAME                                                                     \n");
        	sqlstr.append("		     ,  C.GRADE_NAME                                                                    \n");
        	sqlstr.append("		     ,  C.TEL_NO                                                                        \n");
        	sqlstr.append("		FROM    BID_MASTER A                                                                    \n");
        	sqlstr.append("		   ,    PUB0120T  B                                                                    	\n");
        	sqlstr.append("		   ,    PUB0120T C                                                                     	\n");
        	sqlstr.append("		   ,    TBE_INFO D                                                                     	\n");
        	sqlstr.append("		WHERE A.PUREMPNO  = B.ACCT_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   D.EVAL_EMPID = C.ACCT_ID                                                          \n");
        	sqlstr.append("		AND   A.BID_NO = ?                                                                      \n");
        	sqlstr.append("		AND   A.BID_DEG = ?    																	\n");

			search.setStatement(sqlstr.toString());
			search.addParameter(params.get("BID_NO"));
			search.addParameter(params.get("BID_DEG"));
			
			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);
        }
        return valueobject;
	}
	
	
    /**
     * TBE 저장
	 * @param event
	 * @param valueObject
	 * @return
	 */
	public boolean saveProc(String event) {
        try {
            conn.begin();
            String[] vendorcd = null;
            String[] eval_opin = null;
            if( event.equals("SAVE") || event.equals("TBE_FIN_SAVE")) {//저장
            	if(params.getInt("TBESUPP_CNT")>1){
            		vendorcd = (String[]) params.getObject("VENDOR_CD");//corp_id
            		eval_opin = (String[]) params.getObject("EVAL_OPIN");//c평가의견
        		}else{
        			vendorcd = new String[]{params.get("VENDOR_CD")};
        			eval_opin = new String[]{params.get("EVAL_OPIN")};
        		}
            	
            	//TBE_INFO 담당자 정보 저장
        		tbe_info_eval_svae(conn.getConnection());
            	
    			for(int i=0; i< vendorcd.length;i++){
    				if(!vendorcd[i].isEmpty()){
    					tbe_eval_save(conn.getConnection(),vendorcd[i],eval_opin[i]);
    				}
    			}
    			
            	//조회된 심사자 아이디와 현재 심사자 아이디가 다른경우 저장
            	if(!params.get("EVAL_EMPID").equals(params.get("EVAL_EMPID_O"))){
            		tbe_info_eval_svae(conn.getConnection());
            	}
    			
    			tbe_info_save(conn.getConnection());
    			if(event.equals("TBE_FIN_SAVE")){
    				bidMaster_svae(conn.getConnection());
    			}
    		}
            if( event.equals("FILE_REQUEST")) {//파일요청
            	tbe_eval_save(conn.getConnection(),params.get("FILE_REQUEST_SUPP"),"");
            	if("04".equals(params.get("SUPP_STATUS_CD"))){//문서요청 차수 증가를 해준다.
            		params.set("REQ_DEG",params.getInt("REQ_DEG")+1);
            	}
            	tbe_filerequest_supp(conn.getConnection());
            	tbe_info_save(conn.getConnection());
            }
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> saveProc() : " + e.getMessage());
            WAFLogger.error(e);
            conn.setRollback();
            storage.setDetailMessage(e);
            return false;
        }
        finally {
            conn.end();
        }
    }
	
	
	/**
	 * TBE_INFO 심사자 변경
	 * @param Params
	 * @return
	 */
	private void tbe_info_eval_svae(Connection connection) {
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" UPDATE TBE_INFO					\n");
        	sqlstr.append(" SET EVAL_EMPID = ?				\n");
			sqlstr.append(" ,EVAL_EMPNM = ?					\n");
        	sqlstr.append(" ,EVAL_TEL = ?						\n");
        	sqlstr.append(" ,EVAL_EMAIL = ?						\n");
        	sqlstr.append(" WHERE BID_NO = ?		\n");
        	sqlstr.append(" AND BID_DEG = ?		\n");
        	persistent.setStatement(sqlstr.toString());
        	persistent.addParameter(params.get("EVAL_EMPID"));
        	persistent.addParameter(params.get("EVAL_EMPNM"));
        	persistent.addParameter(params.get("EVAL_TEL"));
    		persistent.addParameter(params.get("EVAL_EMAIL"));
        	persistent.addParameter(params.get("BID_NO"));
        	persistent.addParameter(params.get("BID_DEG"));
            persistent.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
	}
	
	/**
	 * TBE 업체별 의견 저장
	 * @param Params
	 * @return
	 */
	private void tbe_eval_save(Connection connection,String vendor_cd,String eval_opin) {
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" MERGE INTO TBE_SUPP T	USING																										\n");
        	sqlstr.append(" (SELECT ? AS BID_NO,																														\n");
        	sqlstr.append(" ? AS BID_DEG,																																\n");
        	sqlstr.append(" ? AS VENDOR_CD,																															\n");
        	sqlstr.append(" ? AS EVAL_OPIN,																																\n");
        	if(!params.get("SUPP_STATUS_CD").isEmpty()){
        		sqlstr.append(" ? AS STATUS_CD,																														\n");
        	}
        	sqlstr.append(" ? AS EVAL_RESULT																															\n");
        	sqlstr.append(" FROM DUAL) D ON (T.BID_NO = D.BID_NO AND T.BID_DEG = D.BID_DEG AND T.VENDOR_CD = D.VENDOR_CD)	\n");
        	sqlstr.append(" WHEN MATCHED THEN																														\n");
        	sqlstr.append(" UPDATE 																																		\n");
        	sqlstr.append(" SET EVAL_OPIN = D.EVAL_OPIN																											\n");		
        	sqlstr.append(" ,EVAL_RESULT = D.EVAL_RESULT																										\n");
        	if(!params.get("SUPP_STATUS_CD").isEmpty()){
        		sqlstr.append("  ,STATUS_CD = D.STATUS_CD																										\n");
        		if("04".equals(params.get("SUPP_STATUS_CD"))){
        			sqlstr.append("  ,REQ_YN = 'Y'																														\n");
        		}
        	}
        	sqlstr.append(" WHEN NOT MATCHED THEN																												\n");
        	sqlstr.append(" INSERT ( BID_NO,BID_DEG,VENDOR_CD,EVAL_OPIN,EVAL_RESULT																\n");
        	if(!params.get("SUPP_STATUS_CD").isEmpty()){
        		sqlstr.append(" ,STATUS_CD											\n");
        		if("04".equals(params.get("SUPP_STATUS_CD"))){
        			sqlstr.append("  ,REQ_YN																																\n");
        		}
        	}
        	sqlstr.append(" )											\n");
        	
        	sqlstr.append(" VALUES ( D.BID_NO,D.BID_DEG,D.VENDOR_CD,D.EVAL_OPIN,D.EVAL_RESULT												\n");
        	if(!params.get("SUPP_STATUS_CD").isEmpty()){
        		sqlstr.append(" ,D.STATUS_CD											\n");
        		if("04".equals(params.get("SUPP_STATUS_CD"))){
        			sqlstr.append("  ,'Y'																																		\n");
        		}
        	}
        	sqlstr.append(" )											\n");
        	persistent.setStatement(sqlstr.toString());
        	persistent.addParameter(params.get("BID_NO"));
        	persistent.addParameter(params.get("BID_DEG"));
        	persistent.addParameter(vendor_cd);
        	persistent.addParameter(eval_opin);
        	if(!params.get("SUPP_STATUS_CD").isEmpty()){
        		persistent.addParameter(params.get("SUPP_STATUS_CD"));
        	}
			if(params.get("EVAL_RESULT_"+vendor_cd).length() == 2){
				persistent.addParameter(params.get("EVAL_RESULT_"+vendor_cd));
			}else{
				persistent.addParameter("");
			}
            persistent.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
	}
	
	
	/**
	 * TBE_INFO 종합의견 , 상태 저장
	 * @param Params
	 * @return
	 */
	private void tbe_info_save(Connection connection) {
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" UPDATE TBE_INFO		\n");
        	sqlstr.append(" SET TOT_OPIN = ?		\n");
			sqlstr.append(" ,TOT_FILE_GB = ?		\n");
        	sqlstr.append(" ,TOT_FILE_ID = ?		\n");
        	if(!params.get("TBE_FIN_STATUS_CD").isEmpty()){//상태값이 있는경우 상태값 업데이트
        		sqlstr.append(" ,STATUS_CD = ?		\n");
        		if("10".equals(params.get("TBE_FIN_STATUS_CD"))){//검토완료일경우만 완료일 업데이트
        			sqlstr.append(" ,TBE_FIN_DT = SYSDATE		\n");
        		}
        	}
        	sqlstr.append(" WHERE BID_NO = ?		\n");
        	sqlstr.append(" AND BID_DEG = ?		\n");
        	persistent.setStatement(sqlstr.toString());
        	persistent.addParameter(params.get("TOT_OPIN"));
        	persistent.addParameter(params.get("TOT_FILE_GB"));
        	persistent.addParameter(params.get("TOT_FILE_ID"));
        	if(!params.get("TBE_FIN_STATUS_CD").isEmpty())
        		persistent.addParameter(params.get("TBE_FIN_STATUS_CD"));
        	persistent.addParameter(params.get("BID_NO"));
        	persistent.addParameter(params.get("BID_DEG"));
        	
            persistent.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
	}
	
	/**
	 * TBE 업체별 의견 저장
	 * @param Params
	 * @return
	 */
	private void tbe_filerequest_supp(Connection connection) {
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" MERGE INTO TBE_SUPP_CHASU T	USING																							\n");
        	sqlstr.append(" (SELECT ? AS BID_NO,																														\n");
        	sqlstr.append(" ? AS BID_DEG,																																\n");
        	sqlstr.append(" ? AS VENDOR_CD,																															\n");
        	sqlstr.append(" ? AS REQ_DEG																																\n");
        	sqlstr.append(" FROM DUAL) D ON (T.BID_NO = D.BID_NO AND T.BID_DEG = D.BID_DEG AND T.VENDOR_CD = D.VENDOR_CD AND T.REQ_DEG = D.REQ_DEG)	\n");
        	sqlstr.append(" WHEN MATCHED THEN																														\n");
        	sqlstr.append(" UPDATE 																																		\n");
        	sqlstr.append(" SET REG_DT = SYSDATE																													\n");
        	sqlstr.append(" WHEN NOT MATCHED THEN																												\n");
        	sqlstr.append(" INSERT ( BID_NO,BID_DEG,VENDOR_CD,REQ_DEG,REG_DT )																		\n");
        	sqlstr.append(" VALUES ( D.BID_NO,D.BID_DEG,D.VENDOR_CD,NVL(D.REQ_DEG,1),SYSDATE )												\n");
        	persistent.setStatement(sqlstr.toString());
        	persistent.addParameter(params.get("BID_NO"));
        	persistent.addParameter(params.get("BID_DEG"));
        	persistent.addParameter(params.get("FILE_REQUEST_SUPP"));
        	persistent.addParameter(params.get("REQ_DEG"));
            persistent.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
	}
	
	/**
	 * BID_MASTER 업체선정대상으로 업데이트
	 * @param Params
	 * @return
	 */
	private void bidMaster_svae(Connection connection) {
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try{
        	if(!params.get("OPEN_DT").isEmpty()){
	        	sqlstr.append(" UPDATE BID_MASTER 															\n");
	        	sqlstr.append(" SET UPD_DATE = SYSDATE															\n");
				sqlstr.append(" ,UPD_BY = ?																		\n");
				sqlstr.append(" , STATUS_CD = '06'															\n");
				sqlstr.append(" WHERE BID_NO = ?																\n");
				sqlstr.append(" AND BID_DEG = ?    																\n");
	        	persistent.setStatement(sqlstr.toString());
	        	persistent.addParameter(storage.getUser().get("ACCT_ID"));
	        	persistent.addParameter(params.get("BID_NO"));
	        	persistent.addParameter(params.get("BID_DEG"));
	            persistent.execute();
        	}
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
	}
}