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




import java.sql.Connection;

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.ebid.buyer.common.file.FileManager;

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 TBERequestSaveBiz extends Biz {

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

	public void doBiz() throws Exception {
		if(storage.compareEvent("SAVE") || storage.compareEvent("TBE_SAVE")){
			//현설 참석의사 저장
			boolean success = false;
			success = save();
		   super.storage.setSuccess(success);
		   if(success)
			   storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}
	
	  /**
     * 사용자 정보 저장
	 * @param event
	 * @param valueObject
	 * @return
	 */
	public boolean save() {
        try {
            conn.begin();
            if(tbesuppcnt("TBE_SUPP","") == 0){
            	tbesuppsave(conn.getConnection());
            }else{
           		tbesuppupdate(conn.getConnection());
            }
            
            if(tbesuppcnt("TBE_SUPP_CHASU",params.get("REQ_DEG")) == 0){
            	tbesave(conn.getConnection());
            }
            	
            
            String[] status = super.gets("sheet2.STATUS");
            for (int i = 0; i < status.length; i++) {
				if (IBSheet7Utils.equalStatusInsert(status[i])) {
					saveSheet(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
				}
				if(IBSheet7Utils.equalStatusUpdate(status[i])){
					updateSheet(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
				}
				
			}

            if(storage.compareEvent("TBE_SAVE")){
            	tbeupdate(super.conn.getConnection());//문서 제출일 등록
            	updatefileStatus(super.conn.getConnection());
            }
            return true;
        }
        catch(Exception e) {
        	storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
            WAFLogger.error(this.getClass().getName() + " --> save() : " + e.getMessage());
            WAFLogger.error(e);
            conn.setRollback();

            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	
            conn.end();
        }
    }
	
	

			
	/**
	 * TBE_SUPP 등록유무 확인
	 * @param Params
	 * @return
	 */
	public int tbesuppcnt(String table,String REQ_DEG) throws Exception {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();
        ValueObject valueobject = null;
        try {
        	sqlstr.append("		SELECT COUNT(*) CNT FROM "+table+"	\n");
			sqlstr.append("		WHERE BID_NO = ?	\n");
			sqlstr.append("		AND BID_DEG = ?	\n");
			sqlstr.append("		AND VENDOR_CD = ?	\n");
			if(!REQ_DEG.isEmpty())
				sqlstr.append("		AND REQ_DEG = ?		\n");
			
			search.setStatement(sqlstr.toString());
			search.addParameter(params.get("BID_NO"));
			search.addParameter(params.get("BID_DEG"));
			search.addParameter(params.get("VENDOR_CD"));
			if(!REQ_DEG.isEmpty())
				search.addParameter(params.get("REQ_DEG"));
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 )
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            return valueobject.getInt("CNT");
        }
        catch(Exception e) {
            throw e;
        }
	}
	/**
	 * TBE_SUPP 등록
	 * @param Params
	 * @return
	 */
	public void tbesuppsave(Connection connection) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		INSERT INTO TBE_SUPP( 	\n");
        	sqlstr.append("		BID_NO							\n");
        	sqlstr.append("		,BID_DEG						\n");
        	sqlstr.append("		,VENDOR_CD					\n");
        	sqlstr.append("		,REQ_DY							\n");
        	sqlstr.append("		,STATUS_CD 					\n");
        	sqlstr.append("		,REG_NM 						\n");
        	sqlstr.append("		,REG_TEL 						\n");
        	sqlstr.append("		,REG_EMAIL 					\n");
        	sqlstr.append("		) VALUES ( 						\n");
        	sqlstr.append("		?									\n");
        	sqlstr.append("		, ?									\n");
        	sqlstr.append("		, ?									\n");
        	sqlstr.append("		, (SELECT SUPP_REQ_DY FROM TBE_INFO WHERE BID_NO = ? AND BID_DEG = ?)		\n");
        	sqlstr.append("		, ?									\n");
        	sqlstr.append("		, ?									\n");
        	sqlstr.append("		, ?									\n");
        	sqlstr.append("		, ?									\n");
        	sqlstr.append("		) 									\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(params.get("VENDOR_CD"));
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            if(storage.compareEvent("SAVE")){
            	persistent.addParameter("00");
            }else{
            	persistent.addParameter("01");
            }
            persistent.addParameter(storage.getUser().get("USER_NAME"));
            persistent.addParameter(storage.getUser().get("HP1")+"-"+storage.getUser().get("HP2")+"-"+storage.getUser().get("HP3"));
            persistent.addParameter(storage.getUser().get("EMAIL"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> tbesuppsave() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
	}
	
	/**
	 * TBE_SUPP 수정
	 * @param Params
	 * @return
	 */
	public void tbesuppupdate(Connection connection) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		UPDATE TBE_SUPP				\n");
       		sqlstr.append("		SET STATUS_CD = ?			\n");
        	sqlstr.append("		,REG_NM = ? 						\n");
        	sqlstr.append("		,REG_TEL = ? 						\n");
        	sqlstr.append("		,REG_EMAIL = ? 					\n");
			sqlstr.append("		WHERE BID_NO = ? AND BID_DEG = ? AND VENDOR_CD = ?		\n");
            persistent.setStatement(sqlstr.toString());
            if(storage.compareEvent("TBE_SAVE")){
            	persistent.addParameter("01");//문서제출
            }else{
            	persistent.addParameter("00");//저장
            }
            persistent.addParameter(storage.getUser().get("USER_NAME"));
            persistent.addParameter(storage.getUser().get("HP1")+"-"+storage.getUser().get("HP2")+"-"+storage.getUser().get("HP3"));
            persistent.addParameter(storage.getUser().get("EMAIL"));
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(params.get("VENDOR_CD"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> tbesuppsave() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
	}
	
	/**
	 * TBE_SUPP_CHASU 등록
	 * @param Params
	 * @return
	 */
	public void tbesave(Connection connection) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		INSERT INTO TBE_SUPP_CHASU ( BID_NO,BID_DEG,VENDOR_CD,REQ_DEG )												\n");
        	sqlstr.append("		VALUES ( ?, ?, ?, ?)				\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(params.get("VENDOR_CD"));
            persistent.addParameter(params.get("REQ_DEG"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> tbesave() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
	}
	
	/**
	 * TBE_SUPP_CHASU 수정
	 * @param Params
	 * @return
	 */
	public void tbeupdate(Connection connection) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		UPDATE TBE_SUPP_CHASU 						\n");
        	sqlstr.append("		SET DOC_DT= SYSDATE						\n");
        	sqlstr.append("		WHERE BID_NO =?    						\n");
        	sqlstr.append("		AND BID_DEG =?    						\n");
        	sqlstr.append("		AND VENDOR_CD =?    						\n");
        	sqlstr.append("		AND REQ_DEG =?    						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(params.get("VENDOR_CD"));
            persistent.addParameter(params.get("REQ_DEG"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> tbeupdate() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
	}
	
	/**
	 * TBE_SUPP_ATTFILE 등록
	 * @param Params
	 * @return
	 */
	public void saveSheet(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		INSERT INTO TBE_SUPP_ATTFILE ( BID_NO,BID_DEG,VENDOR_CD,REQ_DEG,EVAL_FILE_GB,EVAL_FILE_ID,DOC_NM,STATUS_CD )				\n");
        	sqlstr.append("		VALUES ( ?, ?, ?, ?, ?, ?, ?,  ?)																																				\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(params.get("VENDOR_CD"));
            persistent.addParameter(valueObject.get("REQ_DEG"));
            persistent.addParameter(valueObject.get("EVAL_FILE_GB"));
            persistent.addParameter(valueObject.get("EVAL_FILE_ID"));
            persistent.addParameter(valueObject.get("DOC_NM"));
            if(storage.compareEvent("SAVE")){
            	persistent.addParameter("00");
            }else{
            	persistent.addParameter("01");
            }
            
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> saveSheet() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
	}
	
	
	/**
	 * TBE_SUPP_ATTFILE 업데이트
	 * @param Params
	 * @return
	 */
	public void updateSheet(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		UPDATE TBE_SUPP_ATTFILE						\n"); 
        	sqlstr.append("		SET DOC_NM= ?								\n");
        	sqlstr.append("		, STATUS_CD= ? 								\n");
        	sqlstr.append("		WHERE BID_NO = ?							\n");
        	sqlstr.append("		AND BID_DEG = ?								\n");
        	sqlstr.append("		AND VENDOR_CD = ?							\n");
        	sqlstr.append("		AND REQ_DEG = ?								\n");
        	sqlstr.append("		AND EVAL_FILE_GB= ?							\n");
        	sqlstr.append("		AND EVAL_FILE_ID= ?							\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("DOC_NM"));
            if(storage.compareEvent("SAVE")){
            	persistent.addParameter("00");
            }else{
            	persistent.addParameter("01");
            }
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(params.get("VENDOR_CD"));
            persistent.addParameter(params.get("REQ_DEG"));
            persistent.addParameter(valueObject.get("EVAL_FILE_GB"));
            persistent.addParameter(valueObject.get("EVAL_FILE_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> saveSheet() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
	}
	
	
	/**
	 * TBE_SUPP_ATTFILE 업데이트
	 * @param Params
	 * @return
	 */
	public void updatefileStatus(Connection connection) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		UPDATE TBE_SUPP_ATTFILE				\n"); 
        	sqlstr.append("		SET STATUS_CD= '01'						\n");
        	sqlstr.append("		WHERE BID_NO = ?							\n");
        	sqlstr.append("		AND BID_DEG = ?								\n");
        	sqlstr.append("		AND VENDOR_CD = ?							\n");
        	sqlstr.append("		AND REQ_DEG = ?								\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(params.get("VENDOR_CD"));
            persistent.addParameter(params.get("REQ_DEG"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updatefileStatus() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
	}
	
}