/**
 * @(#)file    DivContPopListSaveBiz.java
 * @(#)author  chj
 * @(#)version 1.0
 * @(#)date    2015-02-25
 * @(#)since   JDK 1.6
 */
package kr.co.udapsoft.ebid.buyer.bid.purchase.biz;



import java.sql.Connection;

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.ebid.buyer.common.file.FileManager;
import oracle.toplink.internal.ox.StrBuffer;

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

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

	public void doBiz() throws Exception {
		boolean success = false;
		
		if(storage.compareEvent("SAVE_DATA")){//저장

			success = saveData();
			 super.storage.setSuccess(success);
		}else if(storage.compareEvent("SAVE_APPR")){ //결재전 저장
			
			success = saveData();
			super.storage.setSuccess(success);
		}else if(storage.compareEvent("PO_CHECK")){
			ValueObject info = getPoCheck(params.get("CONF_NO").trim().toUpperCase());
			storage.addValueObject("VALUEOBJECT",info);
		}else if(storage.compareEvent("GET_ATTACHFILE")){
			//첨부파일
			ValueObject AttachFile = getfilelist("AttachFile");
	        storage.addValueObject("VALUEOBJECT", AttachFile);
        }else if(storage.compareEvent("SET_REPORT_EMP_DELETE")){
			String[] status = super.gets("sheet3.STATUS");
			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						success = report_emp_delete(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET3", params, i));
					}
				}
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}else if(storage.compareEvent("REPORT_EMP_UPDATE")){
            //유관부서 담당자 정보
			try{
				super.conn.begin();
	        	String[] status3 = super.gets("sheet3.STATUS");
	            for (int i = 0; i < status3.length; i++) {
					if (IBSheet7Utils.equalStatusInsert(status3[i])) {
						saveData3(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET3", params, i));
					}
					if (IBSheet7Utils.equalStatusDelete(status3[i])) {
						deleteData3(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET3", params, i));
					}
				}
	            super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			}catch(Exception e){	            
	        	WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
			}finally {
				super.conn.close();
			}
    	}
	}
	
	  /**
     * BID_SUPP_ESTI 저장
	 * @param event
	 * @param valueObject
	 * @return
	 */
	public boolean saveData() {
        try {
            conn.begin();
            
			doSave(super.conn.getConnection());
            return true;
        }
        catch(Exception e) {
        	storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
            WAFLogger.error(this.getClass().getName() + " --> saveProc() : " + e.getMessage());
            WAFLogger.error(e);
            conn.setRollback();

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

	
	private ValueObject getPoCheck(String confNo) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        try{
			sqlstr.append("			   SELECT COUNT(*) AS CNT											\n");
			sqlstr.append("			   FROM PUR2000T      								\n");
			sqlstr.append("			   WHERE UPPER(ORD_DOC_NO) = '"+confNo+"'      								\n");
	        
				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);
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug("WAFSQLException : " + se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//WAFLogger.debug("### valueobject size : " + valueobject.size());
            return valueobject;
        }
	}
	
	
	/**
     * BID_SUPP_ESTI 업데이트
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void doSave(Connection connection) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        Persistent persistent2 = new PersistentImpl(connection);
        Persistent persistent3 = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        StrBuffer sqlstr2 = new StrBuffer();
        StrBuffer sqlstr3 = new StrBuffer();
        try {
        	
        	sqlstr.append("  UPDATE BID_SUPP				\n");
        	sqlstr.append("  SET CONT_AMT = ?					\n");
        	sqlstr.append("    , ACT_AMT  = ?				\n");
        	sqlstr.append("    , PUR_AMT  = ?				\n");
        	sqlstr.append("    , CON_REMARK  = ?				\n");
        	sqlstr.append("    , CONF_NO  = ?				\n");
        	
        	if(params.get("DIV_PO_YN").equals("Y")){
        		sqlstr.append("    , SELECT_TOT_AMT  = ?				\n");
        		sqlstr.append("    , ESTI_TOT_AMT  = ?				\n");
        		sqlstr.append("    , WON_ESTI_TOT_AMT  = ?				\n");
        		sqlstr.append("    , WON_SELECT_TOT_AMT  = ?				\n");
        	}
        	
        	sqlstr.append("  WHERE BID_NO = ?					\n");
        	sqlstr.append("  AND   BID_DEG = ?					\n");
        	sqlstr.append("  AND   VENDOR_CD = ?				\n");

        	
        	persistent.setStatement(sqlstr.toString());
        	
    		persistent.addParameter(params.get("CONT_AMT").replaceAll(",", ""));
    		persistent.addParameter(params.get("ACT_AMT").replaceAll(",", ""));
    		persistent.addParameter(params.get("PUR_AMT").replaceAll(",", ""));
    		persistent.addParameter(params.get("CON_REMARK"));
    		persistent.addParameter(params.get("CONF_NO").trim().toUpperCase());
    		
    		if(params.get("DIV_PO_YN").equals("Y")){
    			
    			persistent.addParameter(params.get("SELECT_TOT_AMT").replaceAll(",", ""));
    			persistent.addParameter(params.get("SELECT_TOT_AMT").replaceAll(",", ""));
    			persistent.addParameter(params.get("WON_SELECT_TOT_AMT").replaceAll(",", ""));
    			persistent.addParameter(params.get("WON_SELECT_TOT_AMT").replaceAll(",", ""));
    		}
    		
    		persistent.addParameter(params.get("BID_NO"));
    		persistent.addParameter(params.get("BID_DEG"));
    		persistent.addParameter(params.get("VENDOR_CD"));
    		persistent.execute();
    		
    		
    		sqlstr2.append("				UPDATE BID_PUR_ORDER              \n");
    		sqlstr2.append("				SET    CBC_DAMAGE_RATE     = ?,             \n");  //CBC_DAMAGE_RATE2
    		sqlstr2.append("				       CBC_DAMAGE_MAXRATE  = ?,             \n");
    		sqlstr2.append("				       CBC_DAMAGE_CD       = ?,             \n");
    		sqlstr2.append("				       CBC_FIRST_RATE      = ?,             \n");
    		sqlstr2.append("				       PBC_FIRST_CONDI     = ?,             \n");
    		
    		sqlstr2.append("				       CPC_MID_RATE        = ?,             \n"); //CPC_MID_RATE
    		sqlstr2.append("				       PBC_MID_CONDI       = ?,             \n");
    		sqlstr2.append("				       PBC_REST_RATE       = ?,             \n");
    		sqlstr2.append("			      	   PBC_REST_CONDI      = ?,             \n");
    		sqlstr2.append("				       PBC_FRSTGUAR_RATE   = ?,             \n");
    		
    		sqlstr2.append("				       PBC_FRSTGUAR_CONDI  = ?,             \n");  //PBC_FRSTGUAR_CONDI
    		sqlstr2.append("				       CONTFUL_RATE        = ?,             \n");
    		sqlstr2.append("				       CONTFUL_CONDI       = ?,             \n");
    		sqlstr2.append("				       WARR_RATE           = ?,         	\n");
    		sqlstr2.append("				       WARR_CONDI          = ?,         	\n");
    		
    		sqlstr2.append("				       PBC_DEFECT_CONDI    = ?,         	\n"); //PBC_DEFECT_CONDI_O
    		sqlstr2.append("				       CBC_DEFECT_DAY      = ?,         	\n");
    		sqlstr2.append("				       CBC_DERECT_TERM_CD  = ?,         	\n");
    		sqlstr2.append("				       PBC_DEFECT_CONDI2   = ?,         	\n");
    		sqlstr2.append("				       CBC_DEFECT_DAY2     = ?,         	\n");
    		
    		sqlstr2.append("				       CBC_DERECT_TERM_CD2 = ?,         	\n");  //CBC_DERECT_TERM_CD2_O
    		sqlstr2.append("				       DELI_FROM_DY        = ?,         	\n");
    		sqlstr2.append("				       DELI_TO_DY          = ?,         	\n");
    		sqlstr2.append("				       UPD_BY              = ?,         	\n");
    		sqlstr2.append("				       UPD_DATE            = sysdate       	\n");
    		
    		sqlstr2.append("				WHERE  BID_NO              = ?          	\n");
    		sqlstr2.append("				AND    BID_DEG             = ?          	\n");
    		sqlstr2.append("				AND    VENDOR_CD           = ?				\n");
    		
    
    		persistent2.setStatement(sqlstr2.toString());
    		persistent2.addParameter(params.get("CBC_DAMAGE_RATE2").replaceAll(",", ""));
    		persistent2.addParameter(params.get("CBC_DAMAGE_MAXRATE").replaceAll(",", ""));
    		persistent2.addParameter(params.get("CBC_DAMAGE_CD"));
    		persistent2.addParameter(params.get("CBC_FIRST_RATE"));
    		persistent2.addParameter(params.get("PBC_FIRST_CONDI"));
    		
    		persistent2.addParameter(params.get("CPC_MID_RATE"));
    		persistent2.addParameter(params.get("PBC_MID_CONDI"));
    		persistent2.addParameter(params.get("PBC_REST_RATE"));
    		persistent2.addParameter(params.get("PBC_REST_CONDI"));
    		persistent2.addParameter(params.get("PBC_FRSTGUAR_RATE2"));
    		
    		persistent2.addParameter(params.get("PBC_FRSTGUAR_CONDI"));
    		persistent2.addParameter(params.get("CONTFUL_RATE2"));
    		persistent2.addParameter(params.get("CONTFUL_CONDI"));
    		persistent2.addParameter(params.get("WARR_RATE2"));
    		persistent2.addParameter(params.get("WARR_CONDI"));
    		
    		persistent2.addParameter(params.get("PBC_DEFECT_CONDI_O"));
    		persistent2.addParameter(params.get("CBC_DEFECT_DAY_O"));
    		persistent2.addParameter(params.get("CBC_DERECT_TERM_CD_O"));
    		persistent2.addParameter(params.get("PBC_DEFECT_CONDI2_O"));
    		persistent2.addParameter(params.get("CBC_DEFECT_DAY2_O"));
    		
    		persistent2.addParameter(params.get("CBC_DERECT_TERM_CD2_O"));
    		persistent2.addParameter(params.get("DELI_FROM_DY").replaceAll("-", ""));
    		persistent2.addParameter(params.get("DELI_TO_DY").replaceAll("-", ""));
    		persistent2.addParameter(storage.getUser().get("acct_id"));
    		
    		persistent2.addParameter(params.get("BID_NO"));
    		persistent2.addParameter(params.get("BID_DEG"));
    		persistent2.addParameter(params.get("VENDOR_CD"));
    		
    		persistent2.execute();
    		
    		//2016.6.7추가 CBE레포트 양식변경
    		sqlstr3.append("  UPDATE BID_MASTER				        \n");
        	sqlstr3.append("     SET PBC_CBE_TYPE_CD = ?,			\n");
        	sqlstr3.append("         MAIN_PR_CONTENTS  = ?,         \n");
        	sqlstr3.append("         BID_SUPP_SELECT_REASON  = ?,   \n");
        	sqlstr3.append("         PO_PRC  = ?,			        \n");
        	sqlstr3.append("         RES_PRC  = ?			        \n");
        	sqlstr3.append("    WHERE BID_NO = ?					\n");
        	sqlstr3.append("      AND BID_DEG = ?					\n");
        	
        	persistent3.setStatement(sqlstr3.toString());
        	
    		persistent3.addParameter(params.get("PBC_CBE_TYPE_CD"));
    		persistent3.addParameter(params.get("MAIN_PR_CONTENTS"));
    		persistent3.addParameter(params.get("BID_SUPP_SELECT_REASON"));
    		persistent3.addParameter(params.get("PO_PRC"));
    		persistent3.addParameter(params.get("RES_PRC"));
    		persistent3.addParameter(params.get("BID_NO"));
    		persistent3.addParameter(params.get("BID_DEG"));
    		
    		persistent3.execute(); 
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

	private ValueObject getfilelist(String returnid){
		ValueObject vo = new ValueObject();
		String str="";
		
		RowSet totRow = new RowSetImpl();
		RowSet rowSet = FileManager.getFileList("PUR_SUCCESSFILE_GB_" + params.get("BID_NO") + "_" + params.get("BID_DEG"), "");
        for(int i=0;i<rowSet.size();i++){
        	totRow.addRow(rowSet.getRow(i));   	
        }

        //TBE 자료
        rowSet = FileManager.getFileList(params.get("TOT_FILE_GB"), params.get("DEMANDED_ID"));
        for(int i=0;i<rowSet.size();i++){
        	totRow.addRow(rowSet.getRow(i));
        }
        
        //외부 자료
        rowSet = FileManager.getFileList(params.get("DOC_ETC_OUT"), params.get("DEMANDED_ID"));
        for(int i=0;i<rowSet.size();i++){
        	totRow.addRow(rowSet.getRow(i));
        }
        
        for(int i=0;i<totRow.size();i++){
        	Row row=totRow.getRow(i);
        	str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
        	if(i+1!=totRow.size()){
        		str +="\r\n";
        	}        	
        }
        
        vo.set(returnid, str);
        
		return vo;
	}
	
	private boolean report_emp_delete(Connection connection, ValueObject valueObject) throws Exception{
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        
		try{
			sqlstr.append("DELETE BID_DECIDE_MAIL_LIST					\n");
			sqlstr.append("WHERE BID_NO = ?							\n");
			sqlstr.append("AND BID_DEG = ?							\n");
			sqlstr.append("AND EMP_ID = ?							\n");
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(params.get("BID_NO"));
			persistent.addParameter(params.get("BID_DEG"));
			persistent.addParameter(valueObject.get("ACCT_ID"));
			persistent.execute();
			return true;
		} catch (Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> report_emp_delete() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
		}
	}
	
	/**
     * 유관부서 담당자 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void saveData3(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	String seq = "SELECT DECODE(MAX(OPEN_NO)+1, '', 1, MAX(OPEN_NO)+1) FROM BID_DECIDE_MAIL_LIST WHERE BID_NO='" + params.get("BID_NO") + "' AND BID_DEG='" + params.get("BID_DEG") + "'";
        	
        	sqlstr.append(" INSERT									\n");
        	sqlstr.append("   INTO BID_DECIDE_MAIL_LIST					\n");
        	sqlstr.append("        (								\n");
        	sqlstr.append("           BID_NO						\n");
        	sqlstr.append("          ,BID_DEG						\n");
        	sqlstr.append("          ,EMP_ID						\n");
        	sqlstr.append("          ,USER_NAME						\n");
        	sqlstr.append("          ,GRADE_NAME					\n");
        	sqlstr.append("          ,DEPART_DEPT_NM				\n");
        	sqlstr.append("          ,OPEN_NO						\n");
        	sqlstr.append("          ,CRE_BY						\n");
        	sqlstr.append("          ,CRE_DATE						\n");
        	sqlstr.append("        )								\n");
        	sqlstr.append("        VALUES							\n");
        	sqlstr.append("        ( ?								\n");
        	sqlstr.append("          ,?								\n");
        	sqlstr.append("          ,?								\n");
        	sqlstr.append("          ,?								\n");
        	sqlstr.append("          ,?								\n");
        	sqlstr.append("          ,?								\n");
        	sqlstr.append("          ,(" + seq + ")					\n");
        	sqlstr.append("          ,?								\n");
        	sqlstr.append("          ,SYSDATE						\n");
        	sqlstr.append("        )								\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(valueObject.get("ACCT_ID"));
            persistent.addParameter(valueObject.get("USER_NAME"));
            persistent.addParameter(valueObject.get("GRADE_NAME"));
            persistent.addParameter(valueObject.get("BUSEO_NAME"));
            persistent.addParameter(storage.getUser().get("ACCT_ID"));
            persistent.execute();
            System.out.println(sqlstr);
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> saveData3() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

	
	/**
     * 보고자 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void deleteData3(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM BID_DECIDE_MAIL_LIST				\n");
        	sqlstr.append(" WHERE  BID_NO = ?						\n");
        	sqlstr.append(" AND BID_DEG = ?							\n");
        	sqlstr.append(" AND EMP_ID = ?					\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(valueObject.get("ACCT_ID"));
            persistent.execute();
            System.out.println(sqlstr);
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> deleteData() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

}