/**
 * @(#)file    PoDeciSignDriver.java
 * @(#)author  Lee beomgeun
 * @(#)version 1.0
 * @(#)date    Aug 1, 2005
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.dcchem.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of dcchem, Inc.
 */
package kr.co.udapsoft.common.commonSign.driver;

import java.sql.CallableStatement;
import java.sql.Connection;

import com.udapsoft.waf.system.HandlerStorage;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.logger.Logger;
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.commonSign.CommonSignInterface;
import kr.co.udapsoft.common.sender.MessageSend;
import oracle.toplink.internal.ox.StrBuffer;

/**
 * 
 * @version 1.0
 * @author Beomgeun Lee
 */
public class OrebqSignDriver implements CommonSignInterface {

    /**
     * 테슽 파일
     * 
     */
    public OrebqSignDriver() {
        super();
    }

    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doCallSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doCallSign(Connection connection, ValueObject signDoc) throws Exception {
    }

    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doStartSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doStartSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!doStartSign");
    }

    /*
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doFirstFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doFirstFinishSign(Connection connection, ValueObject signDoc) throws Exception {
    	boolean success = true;
    	System.out.println("!!doFirstFinishSign");
    	System.out.println("### 결재  Strart");
    	
    	//success = saveProc(connection,signDoc);
    	updateStatus(connection, signDoc);
    	
    	ValueObject vo = getDemanded_kind(connection, signDoc);
    	if(success){
    		if("Y".equals(vo.get("ONBID_YN"))){
    			Send(signDoc,"10");
    		}
    		
            //BID_SUPP    				견적대상업 체 
            if("Y".equals(vo.get("TBE_FLAG"))){
            	callTbeSave(connection,signDoc);
            }
    	}
    	System.out.println("### 결재  End");
    }
    
    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doFinishSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!doFinishSign");
    }

    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doFirstRejectSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!반려");
    	updateStatus(connection, signDoc);
    }
    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doRejectSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!doRejectSign");
    	
    }

    /*
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doCancelSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doCancelSign(Connection connection, ValueObject signDoc) throws Exception {
    }
    
    /**
     * SAC01001 상태값을 업데이트해준다.
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void updateStatus(Connection connection, ValueObject vo) throws Exception {
 
        Persistent persistent = new PersistentImpl(connection);
        StringBuffer sqlstr = new StringBuffer();
        try {
        	String status = "";
        	
        	if(vo.get("SIGN_TYPE").equals("1")){
        		if(vo.get("SIGN_DECI_CLASS").equals("14")){
        			status = "04";
        		}else {
        			status = "30";
        		}
        	}
        	
   			sqlstr.append(" UPDATE  BID_MASTER SET        					                       \n");
    		sqlstr.append("         STATUS_CD  = DECODE(?,'30','30',DECODE(SITEDESC_FLAG, 'Y', '03', '04'))   \n"); //2018.07.06 현설추가 
    		sqlstr.append(" WHERE   BID_NO	   = ?    						                               \n");
    		sqlstr.append(" AND		BID_DEG	   = ?    						                               \n");
    		
    		persistent.setStatement(sqlstr.toString());
            persistent.addParameter(status);
            persistent.addParameter(vo.get("KEY_COL1"));
            persistent.addParameter(vo.get("KEY_COL2"));
                
            persistent.execute();
            persistent.clearParameters();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> updateStatus() : " + e.getMessage());
            WAFLogger.error(e);
            throw e;
        }
    }
	
    /**
     * SP_BID_TO_PURCONT 프로시저 호출
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean saveProc(Connection connection, ValueObject vo) throws Exception {
		ValueObject rtnVo = new ValueObject();
		CallableStatement cs 	= null;
		String pssql			= "";
		try{
			pssql = "{call SP_RE_BID_TO_TBE(?,?,?,?)}";
        	cs = connection.prepareCall(pssql);
        	cs.setString(1, vo.get("KEY_COL1"));
        	cs.setString(2, vo.get("KEY_COL2"));
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
            cs.execute();
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(3));
            rtnVo.set("OUT_MESSAGE",cs.getString(4));   
            System.out.println("CODE : " + cs.getString(3));
            System.out.println("MSG : " + cs.getString(4));
            if("N".equals(cs.getString(3))){
            	throw new Exception(cs.getString(4));
            }
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> saveProc() : " + e.getMessage());
            WAFLogger.error(e);
            return false;
        }
    }
	
	
	
	public void Send(ValueObject params, String event) throws Exception {
		HandlerStorage Stroage = new HandlerStorage();
		MessageSend messageSend = new MessageSend(Stroage);
     	RowSet SuppInfo = bid_supp_info(params);  //2018.07.06 재입찰 현설여부 추가 
     	ValueObject bid_Master = bid_Master_info(params);
     	for(int i=0;i < SuppInfo.size();i++){
     		Row row = SuppInfo.getRow(i);
     		if(!row.get("CHARGE_EMAIL").isEmpty()){
     			boolean email = false;
         		StringBuffer mailtitle = new StringBuffer();
         		StringBuffer mailtitlein = new StringBuffer();
                StringBuffer mailContent = new StringBuffer();
                //2018.07.06 재입찰 현설여부 추가 
                if (bid_Master.get("SITEDESC_FLAG").equals("Y")){
                	mailtitle.append("* 재 현장설명회에 대한 공지 메일입니다.");
                    mailtitlein.append("* 재 현장설명회에 대한 공지 메일입니다.");
                 	mailContent.append(row.get("VENDOR_NAME")+"님께 아래와 같은 내용으로 현장설명회 참석을 요청드립니다.<br>협력업체포탈시스템에 접속하시어 현장설명회 내용 확인 후 참석의사를 입력해 주시기바랍니다.<br>");
                 	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
                 	mailContent.append("<br>");
                 	mailContent.append("1.프로젝트 : " + bid_Master.get("DEPT_NM") + "<br>");
                 	mailContent.append("2.공고번호 : " + bid_Master.get("BID_NO") + "<br>");
                 	mailContent.append("3.공고건명 : " + bid_Master.get("BID_MASTER_NM") + "<br>");
                 	mailContent.append("4.현설일시 : " + bid_Master.get("SITEDESCDATE_NM") + "<br>");
                 	mailContent.append("5.현설장소 : " + bid_Master.get("SITEDESCPLACE") + "<br>");
                 	mailContent.append("6.현설담당자 : " +bid_Master.get("SITEDESC_BUSEO_NM")+" / "+ bid_Master.get("SITEDESC_CRG_NM") +" (연락처 : " + bid_Master.get("SITEDESCTELNO") + ", 이메일 : " + bid_Master.get("SITEDESCEMAIL") +")<br>");
                 	mailContent.append("7.입찰담당자 : " +bid_Master.get("BUSEO_NM")+" / "+ bid_Master.get("USER_NAME") +" (연락처 : " + bid_Master.get("PO_EMPNM") + ", 이메일 : " + bid_Master.get("E_MAIL") +")<br>");
                 	mailContent.append("</font>");
                }else{
                	mailtitle.append("* 재 입찰 요청에 대한 공지 메일입니다.");
                    mailtitlein.append("* 재 입찰 요청에 대한 공지 메일입니다.");
                 	mailContent.append(row.get("VENDOR_NAME")+"님께 아래와 같은 내용으로 견적서 제출을 요청드립니다.<br>협력업체포탈시스템에 접속하시어 견적서작성바랍니다.<br>견적서제출시 법인공인증서가 필요합니다.<br>");
                 	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
                 	mailContent.append("<br>");
                 	mailContent.append("1.프로젝트 : " + bid_Master.get("DEPT_NM") + "<br>");
                 	mailContent.append("2.공고번호 : " + bid_Master.get("BID_NO") + "<br>");
                 	mailContent.append("3.공고건명 : " + bid_Master.get("BID_MASTER_NM") + "<br>");
                 	mailContent.append("4.견적마감일시 : " + bid_Master.get("BID_TO_DT_NM") + "<br>");
                 	mailContent.append("5.입찰담당자 : " +bid_Master.get("BUSEO_NM")+" / "+ bid_Master.get("USER_NAME") +" (연락처 : " + bid_Master.get("PO_EMPNM") + ", 이메일 : " + bid_Master.get("E_MAIL") + ")<br>");
                 	mailContent.append("</font>");
                }
            	email = true;
  
                if(email){
                	messageSend.sendEmail(row.get("CHARGE_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent);
                }
     		}
     		if(!row.get("CHAGE_HP").isEmpty()){

     			String smsContent = "재입찰서 제출바랍니다.'||CHR(13)||CHR(10)||'협력업체포탈시스템에 접속/확인바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)";
     			if (bid_Master.get("SITEDESC_FLAG").equals("Y")){
     				smsContent = "현장설명회 공지'||CHR(13)||CHR(10)||'협력업체포탈시스템에 접속/확인바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)";
     			}
     			
     			String from_phon_no = bid_Master.get("PO_EMPNM").replace("-", "");
	            
     			String to_phon_no = row.get("CHAGE_HP").replace("-", "");
	            
     			if(!smsContent.isEmpty()){
	            	messageSend.sendSMS(to_phon_no,from_phon_no, "", smsContent); //2015.9.21 재입찰. 입찰담당자 
	            }
     		}
     	}
	}
		
	/**
	 * 견적 대상 업체 를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	public RowSet bid_supp_info(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	
        	sqlstr.append("	SELECT CHARGE_NAME,CHARGE_EMAIL,CHAGE_HP,VENDOR_NAME 					\n");
			sqlstr.append("	FROM BID_SUPP											\n");
			sqlstr.append("	WHERE BID_NO = ?											\n");
			sqlstr.append("	AND BID_DEG = ?											\n");
		
			search.setStatement(sqlstr.toString());
			search.addParameter(param.get("KEY_COL1"));
			search.addParameter(param.get("KEY_COL2"));
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        System.out.println("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	
	/**
	 * 발주의뢰(외주) 접수를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	public ValueObject bid_Master_info(ValueObject param) {

		Search search = new SearchImpl();
		 ValueObject valueobject = null;
		RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	sqlstr.append("	SELECT A.BID_NO, 														                    \n");
        	sqlstr.append("	    TO_CHAR(A.BID_TO_DT,'YYYY-MM-DD HH24:MI') AS BID_TO_DT_NM,			                    \n");
        	sqlstr.append("	    A.PO_EMPNM,															                    \n");
        	sqlstr.append("	    A.DEPT_CD,																                \n");
        	sqlstr.append("	    A.DEPT_NM,															                    \n");
        	sqlstr.append("	    A.BID_MASTER_NM,													                    \n");
        	sqlstr.append("	    B.USER_NAME,															                \n");//입찰담당자
        	sqlstr.append("	    B.E_MAIL,																                \n");//입찰담당자EMAIL
        	sqlstr.append("	    B.DEPART_DEPT_NM AS BUSEO_NM,		                                                    \n");//입찰담당자 부서 
        	sqlstr.append("	    A.SITEDESC_FLAG,														                \n");//현설여부 
        	sqlstr.append("	    TO_CHAR(A.SITEDESCDATE,'YYYY-MM-DD HH24:MI') AS SITEDESCDATE_NM,		                \n");//현설일시
        	sqlstr.append("	    A.SITEDESCPLACE,															            \n");//현설장소
        	sqlstr.append("	    A.SITEDESCTELNO,															            \n");//현설연락처
        	sqlstr.append("	    A.SITEDESCEMAIL,															            \n");//현설EMAIL
        	sqlstr.append("	    C.DEPART_DEPT_NM AS SITEDESC_BUSEO_NM,			                                        \n");//현설담당자부서
        	sqlstr.append("	    NVL(A.SITEDESC_CRG_NM,C.USER_NAME) AS 	SITEDESC_CRG_NM				                \n");//현설담당자 명 
        	sqlstr.append("	FROM BID_MASTER A    																\n");
        	sqlstr.append("	    ,PUB0120T B  																 	\n");//입찰 
        	sqlstr.append("	    ,PUB0120T C  																 	\n");//현설
        	sqlstr.append("	WHERE A.PUREMPNO = B.ACCT_ID																\n");
        	sqlstr.append("	  AND A.SITEDESCCHRGPRSNID = C.ACCT_ID(+)												    \n");//현설담당  DEPART_DEPT_NM
        	sqlstr.append("	  AND A.BID_NO = ?																			\n");
        	sqlstr.append("	  AND A.BID_DEG = ?																			\n");
         	 	
			search.setStatement(sqlstr.toString());
			search.addParameter(param.get("KEY_COL1"));
			search.addParameter(param.get("KEY_COL2"));
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
        }
        return valueobject;
	}
	
	
	 private ValueObject getDemanded_kind(Connection connection, ValueObject vo) throws Exception{
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StringBuffer sqlstr = new StringBuffer();       
        try {
            sqlstr.append(" SELECT DEMANDED_KIND,STATUS_CD,ONBID_YN, TBE_FLAG          \n");
            sqlstr.append(" FROM BID_MASTER                           \n");
            sqlstr.append(" WHERE BID_NO = ?										\n");
            sqlstr.append(" AND BID_DEG = ?											\n");
            search.setStatement(sqlstr.toString());
            search.addParameter(vo.get("KEY_COL1"));
            search.addParameter(vo.get("KEY_COL2"));
            rowSet = search.execute();         
            if (rowSet.size() > 0) {
            	valueobject = RowSetUtility.getValueObject(search.execute().getRow(0));
            }
        }
        catch(WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
            throw se;
        }
        finally {
            if (valueobject == null)
            	valueobject = new ValueObject();
            return valueobject;
        }
	}
	 
		/**
	     * 계약 완료 데이터 이동 처리
		 * @param connection
		 * @param valueObject
		 * @throws Exception
		 */
		public void callTbeSave(Connection connection, ValueObject valueObject) throws Exception {

	        CallableStatement cs = null;
	        ValueObject rtnVo = new ValueObject();
	        String sqlStr = "";
	        try {
	        	sqlStr = "{call SP_RE_BID_TO_TBE(?, ?, ?,?)}";
	        	
	        	//WAFLogger.debug("SQL : " + sqlStr); 
	        	
	            cs = connection.prepareCall(sqlStr);
	            cs.setString(1, valueObject.get("KEY_COL1"));
	            cs.setInt(2, valueObject.getInt("KEY_COL2"));

	        	cs.registerOutParameter(3, java.sql.Types.VARCHAR);
	            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
	            
	            
	            cs.execute();
	            
	        }
	        catch(Exception e) {
	            WAFLogger.error(this.getClass().getName() + " --> callTbeSave() : " + e.getMessage());
	            WAFLogger.error("SQL : " + e.getMessage());
	            WAFLogger.error(e);
	            throw e;
	        }
	    }	
		
}