/**
 * @(#)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.outside.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.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;

   
public class TBECheckPopBiz extends Biz {

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

	public void doBiz() throws Exception {
		RowSet tbe_supp = new RowSetImpl();
		 ValueObject user = new ValueObject();
		 String filegb ="";
		 String file_id = ""; 
		if(storage.compareEvent("")){
			tbe_supp = getFileList();
			user = getOrderReqeustValueObject();
			storage.addValueObject("Order", user);
			storage.addRowSet("TBE_INFO", tbe_supp);
			if(user.get("TOT_FILE_GB").isEmpty()){
				filegb = "TBE_TOT"+params.get("BID_NO") + params.get("BID_DEG");
				user.set("TOT_FILE_GB", filegb);
			}
			storage.addValueObject(user.get("TOT_FILE_GB"), getfilelist(user.get("TOT_FILE_GB"),user.get("TOT_FILE_ID"),user.get("TOT_FILE_GB")));
            storage.setSuccess(true);
		}else if(storage.compareEvent("RTN_UPDATE")){
			boolean Success = false;
			Success = saveProc(storage.getEvent());
			//2016.12.2 TBE담당자에게 반려메일 전송**
			MessageSend mailSend = new MessageSend(storage);
        	StringBuffer mailContent = new StringBuffer();
        	ValueObject vo = getMailinfo(params.get("BID_NO"), params.get("BID_DEG"));
        	
        	String title = "[알림]TBE반려";
        	mailContent.append("하기 발주의뢰건에 대해 TBE가 반려되었으니 사유를 확인하시고 재등록하시기 바랍니다. </BR>");
      		mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
      		mailContent.append("</BR>");
      		mailContent.append("1.프로젝트: "+ vo.get("DEPT_NM")+"</BR>");
      		mailContent.append("2.공사명: " + vo.get("BID_MASTER_NM")+"</BR>");
      		mailContent.append("3.외주담당자:"+ vo.get("USER_NAME")+ "</BR>");
      		mailContent.append("4.반려사유:"+ vo.get("RTN_REASON")+ "</BR>");
      		mailContent.append("</font>");
      	    
      	    mailSend.sendEmail(vo.get("EVAL_EMAIL") , "partner@sgc.co.kr", title, title, mailContent, "1"); //
			storage.setSuccess(Success);
		}
	}
	
	
	private ValueObject getMailinfo(String BID_NO,String BID_DEG) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer(); 
        try{ 
        	sqlstr.append(" SELECT DEPT_NM                        \n");
        	sqlstr.append("       ,BID_MASTER_NM                  \n");
        	sqlstr.append("       ,C.USER_NAME                    \n");
        	sqlstr.append("       ,B.RTN_REASON                   \n");
        	sqlstr.append("       ,B.EVAL_EMAIL                   \n");
        	sqlstr.append("   FROM BID_MASTER A                   \n");
        	sqlstr.append("       ,TBE_INFO B                     \n");
        	sqlstr.append("       ,PUB0120T C                     \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.BID_NO = '" + BID_NO + "'    \n");
        	sqlstr.append("    AND A.BID_DEG = '" + BID_DEG + "'  \n");
        	sqlstr.append("    AND A.PUREMPNO = C.ACCT_ID \n");
        	//System.out.println(sqlstr.toString());
	        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);
        }
        finally {
        	if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
	}
	
	private ValueObject getfilelist(String filegb,String id,String returnid){
		ValueObject vo = new ValueObject();
		RowSet rowSet = FileManager.getFileList(filegb, id);
		String str="";
	        for(int i=0;i<rowSet.size();i++){
	        	Row row=rowSet.getRow(i);
	        	str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
	        	if(i+1!=rowSet.size()){
	        		str +="\r\n";
	        	}
	        }
	        vo.set(returnid, str);
		return vo;
	}

	/**
	 * 업체별기술문서 _ 업체정보
	 * @param Params
	 * @return
	 */
	private ValueObject getOrderReqeustValueObject() {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	
        	sqlstr.append("SELECT A.BID_NO,  				  							\n");
        	sqlstr.append("A.BID_DEG,  				  							\n");
        	sqlstr.append("A.EVAL_EMPID,  				  							\n");
        	sqlstr.append("A.EVAL_EMPNM,  				  							\n");
        	sqlstr.append("A.EVAL_TEL,  				  							\n");
        	sqlstr.append("A.EVAL_EMAIL,  				  							\n");
        	sqlstr.append("A.SUPP_REQ_DY,  				  							\n");
        	sqlstr.append("A.TBE_FIN_DT,  				  							\n");
        	sqlstr.append("A.TOT_OPIN,  				  							\n");
        	sqlstr.append("A.TOT_FILE_GB,  				  							\n");
        	sqlstr.append("NVL(A.TOT_FILE_ID,(SELECT DEMANDED_ID FROM BID_MASTER B WHERE A.BID_NO = B.BID_NO AND A.BID_DEG = B.BID_DEG)) AS TOT_FILE_ID,  				  							\n");
        	sqlstr.append("A.STATUS_CD,  				  							\n");
        	sqlstr.append("F_GET_CODE_NAME('TBE_SUPP_STATUS_CD',A.STATUS_CD) AS STATUS_NM  				  							\n");
        	sqlstr.append("FROM TBE_INFO A  				  							\n");
        	sqlstr.append("WHERE 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"));
            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);
        }
        finally {
        	if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
	}
	/**
	 * 업체별기술문서 _ 문서리스트 조회
	 * @param Params
	 * @return
	 */
	private RowSet getFileList() {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("SELECT B.VENDOR_NO																						\n");
        	sqlstr.append(",B.VENDOR_NAME																								\n");
        	sqlstr.append(",A.EVAL_RESULT																								\n");
        	sqlstr.append(",CASE WHEN RNNC_SIGNDATA IS NOT NULL THEN '입찰포기'										\n"); 
        	sqlstr.append("ELSE F_GET_CODE_NAME('TBE_EVAL',A.EVAL_RESULT) END  AS EVAL_RESULT_NM		\n"); 
        	sqlstr.append(",A.EVAL_OPIN 																									\n");
        	sqlstr.append("FROM TBE_SUPP A ,																							\n");
        	//sqlstr.append(",BID_SUPP B 																									\n");
        	sqlstr.append("(SELECT A.* FROM BID_SUPP A , (SELECT BID_NO,MAX(BID_DEG) AS BID_DEG,VENDOR_CD FROM BID_SUPP GROUP BY BID_NO,VENDOR_CD) B \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.VENDOR_CD = B.VENDOR_CD 																	\n"); 
        	sqlstr.append("AND A.BID_NO = ?) B						  																\n");
        	sqlstr.append("WHERE B.BID_NO = A.BID_NO(+)																			\n");
        	sqlstr.append("AND B.BID_DEG = A.BID_DEG(+)																			\n");
        	sqlstr.append("AND B.VENDOR_CD = A.VENDOR_CD(+)																	\n");
            //sqlstr.append("AND A.STATUS_CD='10' 																					\n");
        	//sqlstr.append("AND A.BID_NO=? 																							\n");
        	//sqlstr.append("AND A.BID_DEG=? 																							\n");
			sqlstr.append("ORDER BY B.VENDOR_CD 																					\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(params.get("BID_NO"));
			//search.addParameter(params.get("BID_DEG"));
            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();
            }
        }
        
        return rowSet;
	}
	
	
	   /**
     * TBE 저장
	 * @param event
	 * @param valueObject
	 * @return
	 */
	public boolean saveProc(String event) {
        try {
            conn.begin();
            //TBE_INFO 반려 정보 저장
            tbe_info_Save(conn.getConnection());
            //BID_MASTER에 상태 기술검토중 으로 변경
            bid_master_update(conn.getConnection());
            //문서요청여부 초기화
            tbe_supp_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_Save(Connection connection) {
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" UPDATE TBE_INFO		\n");
        	sqlstr.append(" SET STATUS_CD = '91'		\n");
        	sqlstr.append(" , RTN_REASON = ?		\n");
        	sqlstr.append(" , RTN_DATE = SYSDATE		\n");
        	sqlstr.append(" , RTN_EMPNO = ?		\n");
        	sqlstr.append(" WHERE BID_NO = ?		\n");
        	sqlstr.append(" AND BID_DEG = ?		\n");
        	persistent.setStatement(sqlstr.toString());
        	persistent.addParameter(params.get("RTN_REASON"));
        	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);
        }
	}
	
	/**
	 * TBE_INFO 종합의견 , 상태 저장
	 * @param Params
	 * @return
	 */
	private void bid_master_update(Connection connection) {
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" UPDATE BID_MASTER		\n");
        	//sqlstr.append(" SET STATUS_CD = '05'		\n");
        	sqlstr.append(" SET STATUS_CD = DECODE(STATUS_CD, '04', '04', '05')	\n");  //2015.3.12 입찰중 기술문서 반려하는 경우 
        	sqlstr.append(" , UPD_DATE = SYSDATE		\n");
        	sqlstr.append(" , UPD_BY = ?		\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);
        }
	}
	
	/**
	 * TBE_INFO 문서요청전송여부
	 * @param Params
	 * @return
	 */
	private void tbe_supp_Save(Connection connection) {
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" UPDATE TBE_SUPP		\n");
        	sqlstr.append(" SET REQ_YN = ''		\n");
        	sqlstr.append(" WHERE BID_NO = ?		\n");
        	sqlstr.append(" AND BID_DEG = ?		\n");
        	persistent.setStatement(sqlstr.toString());
        	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);
        }
	}
	
}