/**
 * @(#)file    SalNewPopBiz.java
 * @(#)author  김예진
 * @(#)version 1.0
 * @(#)date    2015-01-12
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.outside.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.ebid.buyer.common.file.FileManager;

import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class ContTargetPopupBiz extends Biz {

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

	public void doBiz() throws Exception {
		
		if(storage.compareEvent("")){
			
			ValueObject basicVo = getSuppJoinRequestList(params);
			storage.addValueObject("InfoBaseList", basicVo);
			
			RowSet rowset = getOutMainList(params);
            storage.addRowSet("out_main", rowset);
            
            RowSet getFileListRowSet = getFileListRowSet("TERMS_AGREE_INFO_"+basicVo.get("corp_id"), "");
            storage.addRowSet("getFileListRowSet", getFileListRowSet);
            
            RowSet rowset2 = null;
            rowset2 = getApprYear(params);
            storage.addRowSet("getApprYearList", rowset2);
            
		}
	}

	
    private RowSet getOutMainList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("     SELECT                                                                                                                  \n")
                    .append("        CORP_ID,    OUT_MAIN_CD                          \n")
                    .append("        FROM   SUPP_OUT_MAIN                                  \n")
                    .append("        WHERE CORP_ID = ?                                                                                                   \n");
            
                
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("p_corp_id"));
            WAFLogger.debug(sqlStr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
	
	
    /**
     * 회사정보
     */
    public ValueObject getSuppJoinRequestList(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
        	sqlstr.append("SELECT                              								\n")
               	  .append("        A.CORP_REG_NO                             									\n")
               	  .append("       ,A.CORP_KOR                             									\n")
               	  .append("       ,A.REP_NAME                             									\n")
               	  .append("       ,A.CORP_DIV_CD                             								\n")
               	  .append("       ,TO_CHAR(A.REG_REQ_DT, 'YYYY-MM-DD') REG_REQ_DT                           \n")
               	  .append("       ,A.CRE_DATE                             									\n")
	              .append("       ,A.HEAD_CORP_REG_N                             							\n")
	              .append("       ,A.SUB_PLACE_NO                             								\n")
	              .append("       ,A.TAX_ADDR_CD                             								\n")
	              .append("       ,F_GET_CODE_NAME('SUPP_INFO_STATUS',A.STATUS) STATUS                      \n")
	              .append("       ,A.CORP_ID                             									\n")
	              .append("       ,A.COPR_NO                             									\n")
	              .append("       ,A.CORP_STS                             									\n")
	              .append("       ,A.BIZ_FORM                             									\n")
	              .append("       ,TO_CHAR(A.ESTA_DATE, 'YYYY-MM-DD') ESTA_DATE                             \n")
	              .append("       ,A.CORP_TEL                             									\n")
	              .append("       ,A.CORP_FAX                             									\n")
	              .append("       ,A.CORP_SCALE                             								\n")
	              .append("       ,A.POST_NO                             									\n")
	              .append("       ,A.ADDRESS                             									\n")
	              .append("       ,A.PLACE_POST_NO                             								\n")
	              .append("       ,A.PLACE_ADDRESS                             								\n")
	              .append("       ,A.CREDIT_CORP_CD                             							\n")
	              .append("       ,A.CREDIT_GRADE                             								\n")
	              .append("       ,A.CASH_GRADE                             								\n")
	              .append("       ,A.CREDIT_STA_DY                             								\n")
	              .append("       ,A.CREDIT_END_DY                             								\n")
	              .append("       ,A.FACTOR_CD                             									\n")
	              .append("       ,A.MFTR_DIV_CD                             								\n")
	              .append("       ,A.CREDIT_YN                             									\n")
	              .append("       ,B.APPR_DY                             									\n")
	              .append("       ,B.ITEM_STD_CD                             								\n")
	              .append("       ,B.ARR_ID                             									\n")
	              .append("       ,B.DOC_CD                             									\n")
	              .append("       ,A.STATUS STATUS_CODE                             						\n")
	              .append("       ,TO_CHAR(TO_DATE(A.USE_END_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD') USE_END_DY	\n")
	              .append("       ,TO_CHAR(A.REG_DT, 'YYYY-MM-DD') REG_DT                             		\n")
	              .append("       ,TO_CHAR(A.APPR_DT, 'YYYY-MM-DD') APPR_DT                             	\n")
	              .append("       ,TO_CHAR(A.CAN_DT, 'YYYY-MM-DD') CAN_DT                             		\n")
	              .append("       ,\"COMMENT\"                             									\n")
	              .append("       ,MANAGE_NO                              									\n")
	              .append("       ,NATION_CD                              									\n")
	              .append("       ,F_GET_USER_NM(A.UPD_BY ) MANAGE_NAME                              		\n")
	              .append("       ,A.UPD_BY                              									\n")
                  .append("       FROM SUPP_INFO A,                             \n")
                  .append("            (SELECT ARR_ID, SUBSTR(APPR_DY, 0, 4) APPR_DY, CORP_ID, ITEM_STD_CD, DOC_CD  FROM SUPP_APPR                         \n")
                  .append("             WHERE CORP_ID = ?     AND ARR_ID = (SELECT MAX(ARR_ID) FROM SUPP_APPR WHERE CORP_ID = ? )                                                                                                                                                                              \n")
                  .append("            ) B                                                                                                         \n")
        	  	  .append("       WHERE A.CORP_ID = B.CORP_ID(+)                            \n")
        	  	  .append("          AND A.CORP_ID = ?                             \n");        
                
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("p_corp_id").trim());
            search.addParameter(params.get("p_corp_id").trim());
            search.addParameter(params.get("p_corp_id").trim());
            
            
            WAFLogger.debug(sqlstr.toString());
            WAFLogger.debug(search.getParameters());
            
            
            rowSet = search.execute();
            
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
        	WAFLogger.error("getSuppJoinRequestList===>" + e);
            e.printStackTrace();
            throw e;
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    } 
	
    /** 첨부서류조회 
     * @return
     * @throws Exception
     */
    public RowSet getFileListRowSet(String corpId, String code) throws Exception {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        try {

    		sql.append("	SELECT distinct A.DETAILCD,						\n");
    		sql.append(" 		   A.DETAILNM,								\n");
    		sql.append(" 		   A.DETAILSORTNM,							\n");
    		sql.append(" 		   B.ID AS DOC_CD							\n");
    		sql.append("	FROM EBID_COM911T A, PUB0510T B				\n");
    		sql.append("	WHERE A.BASECD = 'OUT_SUPLLIER_FILE'					\n");
    		sql.append("	AND A.DETAILCD = B.ID(+)						\n");
    		sql.append("	AND B.FILE_GB(+) = '"+corpId+"'				\n");
    		sql.append("	ORDER BY A.DETAILCD								\n");
    		
    		System.out.println(sql);
        	
            search.setStatement(sql.toString());
            
            listRowSet = search.execute();
            WAFLogger.debug(sql.toString());
            
           
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if( listRowSet == null ) listRowSet = new RowSetImpl();
           
        }
        
        return listRowSet;
    }
	
    
    /**
     * 업체별 평가년도 가져오기
     * 
     */
     private RowSet getApprYear(ValueObject Params) {
         
         
         Search search = new SearchImpl();
         RowSet rowSet = new RowSetImpl();
         StringBuffer sqlStr = new StringBuffer();
         
         try {
             sqlStr.append("     SELECT SUBSTR(APPR_DY, 0, 4) APPR_DY, ARR_ID  FROM          \n") 
                    .append("             SUPP_APPR                                                    \n")
                    .append("             WHERE CORP_ID = ?                                        \n");
             

             search.setStatement(sqlStr.toString());
             search.addParameter(params.get("p_corp_id").trim());
             rowSet = search.execute();
         }
         catch (WAFSQLException se) {
             super.doException(se);
             this.storage.setSuccess(false);
             this.storage.setMessage(se.getMessage());
             storage.setDetailMessage(se);
             se.printStackTrace();
         }
         finally {    
             if(rowSet == null) rowSet = new RowSetImpl();
             return rowSet;
         }
     }
     

}