/**
 * @(#)file    SuppDeptRequestBiz.java
 * @(#)author  mklee
 * @(#)version 1.0
 * @(#)date    2017-02-20
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.hwenc.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of Hanwha E&C, Inc.
 */
package kr.co.udapsoft.ebid.buyer.supplier.outside.biz;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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.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 SuppDeptRequestBiz extends Biz {

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

	/* (non-Javadoc)
	 * @see kr.co.hwenc.system.Biz#doBiz()
	 */
	public void doBiz() throws Exception {

		
		
		WAFLogger.debug("event==============>" + storage.getEvent());
		
		ValueObject SuppJoinRequestVo = new ValueObject();
		
		if(storage.compareEvent(""))
		{
		    ValueObject corp_id = getCorp_id(super.conn.getConnection()); 
		       
		    SuppJoinRequestVo.set("DEPT_NM", corp_id.get("DEPT_NM"));
		    SuppJoinRequestVo.set("REQ_EMP", corp_id.get("USER_NAME"));
		    //SuppJoinRequestVo.set("REQ_EMP_NM", corp_id.get("USER_NAME"));
		    SuppJoinRequestVo.set("REQ_DATE", corp_id.get("REQ_DATE"));
		    
            params.set("p_corp_id", corp_id.get("SEQ"));
            
            storage.addValueObject("SuppJoinRequest", SuppJoinRequestVo);
            
            RowSet getFileListRowSet = getFileListRowSet("SUPP_DEPT_REQ_"+corp_id.get("SEQ"), "");
            storage.addRowSet("getFileListRowSet", getFileListRowSet); 
     	}
		else if(storage.compareEvent("GET_DETAIL"))
        {
		    SuppJoinRequestVo = getSuppJoinRequestList(params);
		    
		    params.set("p_corp_id", SuppJoinRequestVo.get("corp_id"));
		    
		    storage.addValueObject("SuppJoinRequest", SuppJoinRequestVo);
		    
		    RowSet getFileListRowSet = getFileListRowSet("SUPP_DEPT_REQ_"+SuppJoinRequestVo.get("corp_id"), "");
            storage.addRowSet("getFileListRowSet", getFileListRowSet);            
        }else if(storage.compareEvent("GET_ATTACHFILE")){
			//첨부파일
    		ValueObject AttachFile = getfilelist("SUPP_DEPT_REQ_"+params.get("p_corp_id"),null,"SUPP_DEPT_REQ");
	        storage.addValueObject("VALUEOBJECT", AttachFile);
	        storage.setSuccess(true); 
        }		 
	}
	
	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")+")";
                System.out.println(row.get("FILE_NAME"));
                if(i+1!=rowSet.size()){
                    str +="\r\n";
                }
            }
            vo.set(returnid, str);
        return vo;
    }
	
	public RowSet getFileListRowSet(String id, 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_SUPP_REQ_FILE'			\n");
    		sql.append("	AND A.DETAILCD = B.ID(+)						\n");
    		sql.append("	AND B.FILE_GB(+) = '"+id+"'						\n");
    		sql.append("	ORDER BY A.DETAILCD								\n");
    		
            search.setStatement(sql.toString());
            
            listRowSet = search.execute();
            
            
        } 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;
    }
	
	public ValueObject getCorp_id(Connection conn) throws Exception{
        
        Persistent persistent_q = null;
        
        
        ValueObject vo = new ValueObject();
        ValueObject row = null;
        StringBuffer sqlstr =  new StringBuffer();
        
        try{
            persistent_q = new PersistentImpl(conn);
            
            sqlstr.append("         SELECT SUPP_INFO_REQ_S.NEXTVAL AS SEQ , sysdate REQ_DATE                 \n");
            sqlstr.append("               ,DEPART_DEPT_NM DEPT_NM, USER_NAME USER_NAME, USER_SABUN REQ_EMP   \n");
            sqlstr.append("           FROM PUB0120T                                                  \n");
            sqlstr.append("         WHERE ACCT_ID = ?                                                \n");
            
            persistent_q.addParameter(storage.getUser().get("acct_id"));
            persistent_q.setStatement(sqlstr.toString()); 
            row = RowSetUtility.getValueObject(persistent_q.query().getRow(0));
            vo.set("seq", row.get("SEQ"));
            vo.set("DEPT_NM", row.get("DEPT_NM"));
            vo.set("REQ_EMP", row.get("REQ_EMP"));
            vo.set("USER_NAME", row.get("USER_NAME"));
            vo.set("REQ_DATE", row.get("REQ_DATE"));
        }   
        catch(Exception e) {
            e.printStackTrace();
        }finally{
            if(vo == null) vo = new ValueObject();
        }
        return vo;
    }
	
	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.CRE_DATE                                                                                           \n")
                   .append("            , F_GET_CODE_NAME2('OUT_SUPP_REQ_STATUS', A.STATUS) STATUS                                             \n")
                   .append("            , A.STATUS STATUS_CODE                                                                                 \n")
                   .append("            , A.USER_NAME                                                                                          \n")
                   .append("            , A.EMAIL                                                                                              \n")
                   .append("            , A.HP1                                                                                                \n")
                   .append("            , A.HP2                                                                                                \n")
                   .append("            , A.HP3                                                                                                \n")
                   .append("            , A.CORP_ID                                                                                            \n")
                   .append("            , A.JOB_DIV                                                                                            \n")
                   .append("            , A.REQ_DATE, A.REQ_REMARK, A.REQ_EMP, A.DEPT_NM,  a.cre_by, A.REJECT_REASON    \n")
                   .append("            , A.CREDIT_CD                                                                                          \n")
                   .append("            , A.CREDIT_VALUE                                                                                       \n")
                   .append("            , SUBSTR(A.CREDIT_VALUE,0,1) CREDIT_VALUE1                                                             \n")
                   .append("            , A.SYEAR                                                                                              \n")
                   .append("            , A.DEBT_AMT                                                                                           \n")
                   .append("            , A.CAPITAL_AMT                                                                                        \n")
                   .append("            , A.DEBT_RATE                                                                                          \n")
                   .append("            , A.LIQUID_ASSET                                                                                       \n")
                   .append("            , A.LIQUID_DEBT                                                                                        \n")
                   .append("            , A.LIQUID_RATE                                                                                        \n")
                   .append("            , A.LICENCE_YN                                                                                         \n")
                   .append("            , A.ETC_YN                                                                                             \n")
                   .append("            , C.DOC_ID                                                                                             \n")
                   .append("            , TO_CHAR(C.CRE_DATE,'YYYY') YEAR                                                                      \n")
                   .append("            , B.USER_NAME WRITE_NAME                                                                               \n")
                   .append("            , A.CASH_GRADE                                                                                         \n")
                   .append("            , SUBSTR(A.CASH_GRADE,0,1) CASH_GRADE1                                                                 \n")
                   .append("        FROM SUPP_INFO_REQ A, PUB0120T B,  SIGN001T C                                                       \n")
                   .append("       WHERE A.CRE_BY = B.ACCT_ID                                                                             \n")
                   .append("         AND A.CORP_ID = C.KEY_COL1(+)                                                                             \n")
                   .append("         AND C.SIGN_CLASS_CD(+) = 'ebid_ocustD'                                                                         \n")
                   .append("         AND C.SIGN_DECI_CLASS(+) = '14'                                                                           \n")
                   .append("          and A.CORP_ID = ?                                                                                        \n");                                                                                 
                    
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("p_corp_id"));
            rowSet = search.execute();
            
            
            WAFLogger.debug(sqlstr.toString());
            WAFLogger.debug(search.getParameters());
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
            e.printStackTrace();
            throw e;
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    } 

}