/**
 * @(#)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 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 ForeignCheckPopBiz extends Biz {

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

	public void doBiz() throws Exception {
		boolean success = false;
        ValueObject user = new ValueObject();
        if(storage.compareEvent("")){ 
	            user = getOrderReqeustValueObject();
	            storage.addValueObject("Order", user);
	    }
        else if(storage.compareEvent("FORM_SAVE")){ 
        	success = saveData();
        	super.storage.setSuccess(success);
        }
	}
 
	/**
	 * 발주의뢰 입찰 기본정보 가져오기
	 * @param Params
	 * @return
	 */
	private ValueObject getOrderReqeustValueObject() {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
       
        String who5 = params.get("WHO5");  //who5값이없는 결재문서 조회  2016.12.12 이전자료 
        if (who5 == null || who5 == "" || who5.equals("")){
        	who5 = "P";
        }
        StrBuffer sqlstr = new StrBuffer();
        try{
        	//2016.11.10 이전 시행결의서 조회때문에 남겨둠. 
        	sqlstr.append("SELECT A.BID_NO                                       \n");
        	sqlstr.append("     , A.BID_DEG                                      \n");
        	sqlstr.append("     , NVL(B.CHK1, 'N') CHK1                          \n");
        	sqlstr.append("     , NVL(B.CHK2, 'N') CHK2                          \n");
        	sqlstr.append("     , NVL(B.CHK3, 'N') CHK3                          \n");
        	sqlstr.append("     , NVL(B.CHK4, 'N') CHK4                          \n");
        	sqlstr.append("     , NVL(B.CHK5, 'N') CHK5                          \n");
        	sqlstr.append("     , NVL(B.CHK6, 'N') CHK6                          \n");
        	sqlstr.append("     , DECODE(NVL(B.CHK6, 'N'), 'N', '아니요', 'Y','예') CHK6_NM                          \n");
        	sqlstr.append("  FROM BID_MASTER A,BID_FOREIGN_CHK 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.BID_NO = '"+params.get("BID_NO")+"'          \n");
        	sqlstr.append("   AND A.BID_DEG =  '"+params.get("BID_DEG")+"'       \n");
        	sqlstr.append("   AND B.VENDOR_CD(+) =  '"+params.get("VENDOR_CD")+"'       \n");
        	if (who5.equals("O")) {
        		sqlstr.append("  UNION ALL      \n");
            	sqlstr.append("SELECT TO_CHAR(A.SUB_CONT_ID) BID_NO                           \n");
            	sqlstr.append("     , A.ACMP_ID    BID_DEG                          \n");
            	sqlstr.append("     , NVL(B.CHK1, 'N') CHK1                          \n");
            	sqlstr.append("     , NVL(B.CHK2, 'N') CHK2                          \n");
            	sqlstr.append("     , NVL(B.CHK3, 'N') CHK3                          \n");
            	sqlstr.append("     , NVL(B.CHK4, 'N') CHK4                          \n");
            	sqlstr.append("     , '"+who5+"' WHO5                                \n");
            	sqlstr.append("     , NVL(B.CHK6, 'N') CHK6                          \n");
            	sqlstr.append("     , DECODE(NVL(B.CHK6, 'N'), 'N', '아니요', 'Y','예') CHK6_NM                          \n");
            	sqlstr.append("  FROM SUB1040T A,BID_FOREIGN_CHK B                 \n");
            	sqlstr.append(" WHERE TO_CHAR(A.SUB_CONT_ID) = B.BID_NO(+)                         \n");
            	sqlstr.append("   AND A.ACMP_ID = B.BID_DEG(+)                       \n");
            	sqlstr.append("   AND A.SUB_CONT_ID = '"+params.get("BID_NO")+"'          \n");
            	sqlstr.append("   AND A.ACMP_ID =  '"+params.get("BID_DEG")+"'       \n");
            	sqlstr.append("   AND B.CHK5(+) =  '"+ who5 +"'       \n");
        	}else if (who5.equals("P")){
        		sqlstr.append("  UNION ALL      \n");
	        	sqlstr.append("SELECT TO_CHAR(A.PUR_CONT_ID) BID_NO                           \n");
	        	sqlstr.append("     , A.PAYREQ_ID   BID_DEG                          \n");
	        	sqlstr.append("     , NVL(B.CHK1, 'N') CHK1                          \n");
	        	sqlstr.append("     , NVL(B.CHK2, 'N') CHK2                          \n");
	        	sqlstr.append("     , NVL(B.CHK3, 'N') CHK3                          \n");
	        	sqlstr.append("     , NVL(B.CHK4, 'N') CHK4                          \n");
	        	sqlstr.append("     , '"+ who5 +"' WHO5                      \n");
	        	sqlstr.append("     , NVL(B.CHK6, 'N') CHK6                          \n");
	        	sqlstr.append("     , DECODE(NVL(B.CHK6, 'N'), 'N', '아니요', 'Y','예') CHK6_NM                          \n");
	        	sqlstr.append("  FROM PUR1100T A,BID_FOREIGN_CHK B                 \n");
	        	sqlstr.append(" WHERE TO_CHAR(A.PUR_CONT_ID) = B.BID_NO(+)                         \n");
	        	sqlstr.append("   AND A.PAYREQ_ID = B.BID_DEG(+)                       \n");
	        	sqlstr.append("   AND A.PUR_CONT_ID = '"+params.get("BID_NO")+"'          \n");
	        	sqlstr.append("   AND A.PAYREQ_ID =  '"+params.get("BID_DEG")+"'       \n");
	        	sqlstr.append("   AND B.CHK5(+) =  '"+ who5 +"'       \n");
        	}else if (who5.equals("Q")){
        		sqlstr.append("  UNION ALL      \n");
	        	sqlstr.append("SELECT TO_CHAR(A.SUB_CONT_ID) BID_NO                           \n");
	        	sqlstr.append("     , A.PREPAY_ID   BID_DEG                          \n");
	        	sqlstr.append("     , NVL(B.CHK1, 'N') CHK1                          \n");
	        	sqlstr.append("     , NVL(B.CHK2, 'N') CHK2                          \n");
	        	sqlstr.append("     , NVL(B.CHK3, 'N') CHK3                          \n");
	        	sqlstr.append("     , NVL(B.CHK4, 'N') CHK4                          \n");
	        	sqlstr.append("     , '"+ who5 +"' WHO5                      \n");
	        	sqlstr.append("     , NVL(B.CHK6, 'N') CHK6                          \n");
	        	sqlstr.append("     , DECODE(NVL(B.CHK6, 'N'), 'N', '아니요', 'Y','예') CHK6_NM                          \n");
	        	sqlstr.append("  FROM SUB1030T A,BID_FOREIGN_CHK B                 \n");
	        	sqlstr.append(" WHERE TO_CHAR(A.SUB_CONT_ID) = B.BID_NO(+)                         \n");
	        	sqlstr.append("   AND A.PREPAY_ID = B.BID_DEG(+)                       \n");
	        	sqlstr.append("   AND A.SUB_CONT_ID = '"+params.get("BID_NO")+"'          \n");
	        	sqlstr.append("   AND A.PREPAY_ID =  '"+params.get("BID_DEG")+"'       \n");
	        	sqlstr.append("   AND B.CHK5(+) =  'Q'       \n");
        	}else if (who5.equals("R")){
        		sqlstr.append("  UNION ALL      \n");
	        	sqlstr.append("SELECT TO_CHAR(A.PUR_CONT_ID) BID_NO                           \n");
	        	sqlstr.append("     , A.PREPAY_ID   BID_DEG                          \n");
	        	sqlstr.append("     , NVL(B.CHK1, 'N') CHK1                          \n");
	        	sqlstr.append("     , NVL(B.CHK2, 'N') CHK2                          \n");
	        	sqlstr.append("     , NVL(B.CHK3, 'N') CHK3                          \n");
	        	sqlstr.append("     , NVL(B.CHK4, 'N') CHK4                          \n");
	        	sqlstr.append("     , '"+ who5 +"' WHO5                      \n");
	        	sqlstr.append("     , NVL(B.CHK6, 'N') CHK6                          \n");
	        	sqlstr.append("     , DECODE(NVL(B.CHK6, 'N'), 'N', '아니요', 'Y','예') CHK6_NM                          \n");
	        	sqlstr.append("  FROM PUR1030T A,BID_FOREIGN_CHK B                 \n");
	        	sqlstr.append(" WHERE TO_CHAR(A.PUR_CONT_ID) = B.BID_NO(+)                         \n");
	        	sqlstr.append("   AND A.PREPAY_ID = B.BID_DEG(+)                       \n");
	        	sqlstr.append("   AND A.PUR_CONT_ID = '"+params.get("BID_NO")+"'          \n");
	        	sqlstr.append("   AND A.PREPAY_ID =  '"+params.get("BID_DEG")+"'       \n");
	        	sqlstr.append("   AND B.CHK5(+) =  'R'       \n");
        	}
        	
        	search.setStatement(sqlstr.toString());
            System.out.println(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;
        }
	}

	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();
        }
    }
	
	public void doSave(Connection connection) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
      
        try {
        	sqlstr.append("MERGE INTO BID_FOREIGN_CHK T1 USING (							\n");
        	sqlstr.append("          SELECT ? AS	BID_NO									\n");
        	sqlstr.append("                ,? AS	BID_DEG									\n");
        	sqlstr.append("                ,? AS	VENDOR_CD								\n");
        	sqlstr.append("                ,? AS	CHK1									\n"); 
        	sqlstr.append("                ,? AS	CHK2									\n"); 
        	sqlstr.append("                ,? AS	CHK3									\n"); 
        	sqlstr.append("                ,? AS	CHK4									\n"); 
        	sqlstr.append("                ,? AS	WHO5 									\n"); 
        	sqlstr.append("                ,? AS	CHK6									\n"); 
        	sqlstr.append("                ,? AS	CRE_USER								\n"); 
        	sqlstr.append("		        FROM DUAL											\n");
        	sqlstr.append("		    ) T ON (T.BID_NO = T1.BID_NO AND T.BID_DEG = T1.BID_DEG AND T.VENDOR_CD = T1.VENDOR_CD AND T.WHO5 = T1.CHK5 )	\n");
        	sqlstr.append("		WHEN MATCHED THEN											\n");
        	sqlstr.append("		    UPDATE SET 												\n");
        	sqlstr.append("               T1.CHK1  = T.CHK1					   				\n");
        	sqlstr.append("             , T1.CHK2  = T.CHK2				        			\n");
        	sqlstr.append("             , T1.CHK3  = T.CHK3				        			\n");
        	sqlstr.append("             , T1.CHK4  = T.CHK4				        			\n");
        	sqlstr.append("             , T1.CHK6  = T.CHK6				        			\n");
        	sqlstr.append("             , T1.UP_USER   = T.CRE_USER				    	    \n");
        	sqlstr.append("             , T1.UPD_DATE  = SYSDATE					        \n");
        	sqlstr.append("		WHEN NOT MATCHED THEN										\n");
        	sqlstr.append("			INSERT (T1.BID_NO, T1.BID_DEG, T1.VENDOR_CD, T1.CHK1, T1.CHK2,T1.CHK3,T1.CHK4,T1.CHK5, T1.CHK6, T1.CRE_USER,T1.CRE_DATE,T1.UP_USER,T1.UPD_DATE)	 	\n");
        	sqlstr.append("			VALUES (T.BID_NO,  T.BID_DEG,  T.VENDOR_CD,  T.CHK1,  T.CHK2, T.CHK3, T.CHK4, T.WHO5, T.CHK6,  T.CRE_USER, SYSDATE,    T.CRE_USER, SYSDATE ) 		\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("CHK1"));
        	persistent.addParameter(params.get("CHK2"));
        	persistent.addParameter(params.get("CHK3"));
        	persistent.addParameter(params.get("CHK4"));
    		persistent.addParameter(params.get("WHO5"));
    		persistent.addParameter(params.get("CHK6"));
    		persistent.addParameter(params.get("P_USER"));
    		
    		//System.out.println(sqlstr.toString());
    		persistent.execute();
        }
        catch(Exception e) {
        	System.out.println(sqlstr.toString());
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
}