/**
 * @(#)file    TermsAgreeBiz.java
 * @(#)author  이성민
 * @(#)version 1.0
 *
 */
package kr.co.udapsoft.ebid.supplier.portal.own.biz;



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

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

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

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

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

		
		//System.out.println("SuppOwnBiz event==============>" + storage);
		
		if(storage.compareEvent(""))
		{
		    //System.out.println("======================자사정보관리===========================" +  "===>" + storage.getUser());
		    
		    
		    String corp_reg_no=  storage.getUser().get("corp_reg_no");
		    String job_div = storage.getUser().get("job_div");
		    
		    
		    //System.out.println("corp_reg_no=======>" + corp_reg_no);
		    //System.out.println("job_div=======>" + job_div);
		    
		    //구매구분
		    params.set("job_div", job_div);
		    params.set("p_corp_reg_no", corp_reg_no);
		    
		    ValueObject userVo = new ValueObject();
		    userVo = getUserData(params);
		    
		    
		    params.set("p_doc_cd", userVo.get("DOC_CD"));
		    params.set("p_eval_cmp", userVo.get("EVAL_CMP"));
		    params.set("p_corp_id", userVo.get("CORP_ID"));
		    /*
		    p_doc_cd
		    p_corp_id
		    p_eval_cmp
		    p_corp_reg_no
		    */
		    
		    String yyyy = (new java.text.SimpleDateFormat ("yyyy")).format (new java.util.Date());
            params.set("p_base_year", Integer.parseInt(yyyy) -1);
            
            ValueObject basicVo = new ValueObject();
            ValueObject corpInfoVo = new ValueObject();

            RowSet rowset = null;
            rowset = getOutMainList(params);
            
//            rowset = getApprSrdList(params);
            corpInfoVo = getCorpInfoList(params);
            basicVo = getSuppJoinRequestList(params);
            RowSet getFileListRowSet = getFileListRowSet("TERMS_AGREE_INFO_"+basicVo.get("corp_id"), "");
            RowSet rowSet = getOutLicMaster_searchCombo();
            storage.addRowSet("licMasterList", rowSet);
//            storage.addRowSet("getApprSrdList", rowset);
            storage.addRowSet("out_main", rowset);
            storage.addValueObject("corpInfoVo", corpInfoVo);
            storage.addValueObject("InfoBaseList", basicVo);
            
            storage.addValueObject("TERMS_AGREE_INFO", getfilelist("TERMS_AGREE_INFO",basicVo.get("corp_id"),"TERMS_AGREE_INFO"));
            
            storage.addRowSet("getFileListRowSet", getFileListRowSet);
            storage.addRowSet("g_base_year",getBaseYear(params));
		}
		
	}
	
	
	private RowSet getApprSrdList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            
            
            sqlStr.append("        SELECT  A.APPR_LEVEL,    A.APPR_ACLS,     A.APPR_CD,  A.GUBUN_1,  A.GUBUN_2,  A.GUBUN_3,  A.STOCK_RATE,  A.A_CONTENT,                                     \n")
                    .append("                        A.A_RATE, A.B_CONTENT, A.B_RATE, A.C_CONTENT, A.C_RATE, A.D_CONTENT, A.D_RATE, A.E_CONTENT, A.E_RATE, A.NOTI , A.DOC_CD,                    \n")
                    .append("                        B.APPR_RESULT_CD,  B.APPR_RATE                                                                                                      \n")
                    .append("                         FROM                                                                                                                   \n")   
                    .append("                               (                                                                                                              \n")
                    .append("                                  SELECT                                                                                                             \n")
                    .append("                                        Z.APPR_LEVEL,    Z.APPR_ACLS,     Z.APPR_CD,  Z.DOC_CD,                                                  \n")
                    .append("                                        LAST_VALUE(Z.GUBUN_1 IGNORE NULLS) OVER(ORDER BY Z.APPR_CD) GUBUN_1,                                               \n")
                    .append("                                        LAST_VALUE(Z.GUBUN_2 IGNORE NULLS) OVER(ORDER BY Z.APPR_CD) GUBUN_2,                                           \n")    
                    .append("                                        LAST_VALUE(Z.GUBUN_3 IGNORE NULLS) OVER(ORDER BY Z.APPR_CD) GUBUN_3,                                      \n")
                    .append("                                        A_CONTENT, A_RATE, B_CONTENT, B_RATE, C_CONTENT, C_RATE, D_CONTENT, D_RATE, E_CONTENT, E_RATE, NOTI, STOCK_RATE             \n")                                      
                    .append("                                        FROM                                                                                                                                                         \n")
                    .append("                                        (                                                                                                                                                                  \n")
                    .append("                                             SELECT LEVEL APPR_LEVEL,    X.APPR_ACLS  , X.APPR_CD, X.STOCK_RATE,   X.DOC_CD,                                   \n")
                    .append("                                                DECODE(LEVEL, '1', X.APPR_ACLS || '(' || X.STOCK_RATE || ')' , '') GUBUN_1,                                                                 \n")
                    .append("                                                DECODE(TRIM(X.APPR_ACLS), '', ' ', DECODE(LEVEL, '2', X.APPR_ACLS  || '(' || X.STOCK_RATE || ')', '')) GUBUN_2,                                              \n")            
                    .append("                                                DECODE(X.APPR_ACLS, '', '', DECODE(LEVEL, '3', X.APPR_ACLS, '')) GUBUN_3,                                                                                              \n")                                     
                    .append("                                                X.A_CONTENT, X.A_RATE, X.B_CONTENT, X.B_RATE, X.C_CONTENT, X.C_RATE, X.D_CONTENT, X.D_RATE, X.E_CONTENT, X.E_RATE, NOTI             \n")                                                                  
                    .append("                                                FROM(                                                                                      \n")                                                   
                    .append("                                                        SELECT ADM_YEAR,                                                       \n")                                                     
                    .append("                                                                   DOC_CD,                                                     \n")                                                   
                    .append("                                                                   PARENT_LEVEL,                                       \n")
                    .append("                                                                   APPR_CD,                                                          \n")                                
                    .append("                                                                   APPR_MCD,                                                   \n")                                                      
                    .append("                                                                   APPR_ACLS,                                             \n")                                                    
                    .append("                                                                   STOCK_RATE,                                      \n")
                    .append("                                                                   A_CONTENT,                                     \n") 
                    .append("                                                                   A_RATE,                                      \n")
                    .append("                                                                   B_CONTENT,                                     \n") 
                    .append("                                                                   B_RATE,                                      \n")
                    .append("                                                                   C_CONTENT,                                     \n") 
                    .append("                                                                   C_RATE,                                      \n")
                    .append("                                                                   D_CONTENT,                                     \n") 
                    .append("                                                                   D_RATE,                                      \n")
                    .append("                                                                   E_CONTENT,                                     \n") 
                    .append("                                                                   E_RATE,                                      \n")
                    .append("                                                                   NOTI                                                                                     \n")
                    .append("                                                               FROM APPR_STD X                                                                                   \n")    
                    .append("                                                               WHERE  DOC_CD = ?                                                                            \n")           
                    .append("                                                        )  X                                                                                                         \n")                               
                    .append("                                         START WITH PARENT_LEVEL = 'S'                                                                            \n")                                 
                    .append("                                             CONNECT BY PRIOR APPR_CD = PARENT_LEVEL                                                     \n")                        
                    .append("                                             AND LEVEL <= 3                                                                                               \n")                                 
                    .append("                                             ORDER SIBLINGS BY PARENT_LEVEL ASC, APPR_CD ASC                                      \n")
                    .append("                                    )   Z                                                                                                                 \n")                  
                    .append("                           ) A, SUPP_APPR_ITEM B, SUPP_APPR  C                                                                      \n")
                    .append("                           WHERE A.DOC_CD = B.DOC_CD                                                                            \n")
                    .append("                                AND A.APPR_CD = B.APPR_CD                                                                           \n")
                    .append("                                AND B.DOC_CD = C.DOC_CD                                                                             \n")
                    .append("                                AND B.ARR_ID = C.ARR_ID                                                                                 \n")
                    .append("                                AND A.GUBUN_3 IS NOT NULL                                                                            \n")                       
                    .append("                                AND A.A_CONTENT IS NOT NULL                                                                          \n");
                                               
            
            
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("p_doc_cd"));
            
            //System.out.println(sqlStr.toString());
            //System.out.println(search.getParameters());
            
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            //System.out.println("getApprSrdList=========>" + 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 getCorpInfoList(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
                sqlstr.append("     SELECT        BASE_YEAR-- 기준년도                                \n")
                        .append("                     ,CAPITAL_AMT -- 자본금                                \n")
                        .append("                     ,FLOAT_ASSET -- 유동자산                                \n")
                        .append("                     ,FLOAT_DEBT -- 유동부채                                \n")
                        .append("                     ,OWN_CAP-- 자기자본                                \n")
                        .append("                     ,TOT_CAP -- 총자본                                \n")
                        .append("                     ,CUR_PROFIT-- 당기순이익                                \n")
                        .append("                     ,GROSS_ASSET-- 총자산                                \n")
                        .append("                     ,SALES_AMT-- 매출액                                \n")
                        .append("                     ,CUR_SALES_AMT -- 당기매출액                                \n")
                        .append("                     ,PRI_SALES_AMT -- 전기매출액                                \n")
                        .append("            		  ,CREDIT_END_DY                          																				     \n")
                        .append("            		  ,CREDIT_YN                            																						   \n")
                        .append("                  	  ,CASE WHEN CREDIT_END_DY > TO_CHAR(SYSDATE,'YYYYMMDD') THEN 'Y' ELSE 'N' END AS RE_YN                      \n")
                        .append("                     FROM SUPP_FINANCE                                \n")
                        .append("                     WHERE  CORP_ID = ?                                \n");
                       // .append("                     AND BASE_YEAR = ?                                \n");
            
            
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("p_corp_id"));
           // search.addParameter(params.get("p_base_year"));
            
            //System.out.println(sqlstr.toString());
            //System.out.println(search.getParameters());
            
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
            e.printStackTrace();
            //System.out.println("getCorpInfoList===>" + e);
            
        }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 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("       ,NVL(A.FACTOR_CONFIRM,'N') AS FACTOR_CONFIRM								\n")
                  .append("       ,DECODE(NVL(A.FACTOR_CONFIRM,'N'),'Y','승인','미승인') AS FACTOR_CONFIRM_NM	\n")
                  .append("       ,A.MFTR_DIV_CD                             								\n")
                  .append("       ,A.CREDIT_YN                             									\n")
                  .append("       ,B.APPR_DY                             									\n")
                  .append("       ,B.ARR_ID                             									\n")
                  .append("       ,B.ITEM_STD_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("       ,O_COMMENT                             									\n")
                  .append("       ,A.BANK_CD                              								\n") //2019.04.24 은행코드,계좌 지정
                  .append("       ,A.BANK_NM                              								\n")
                  .append("       ,A.BANK_ACCT                              								\n")
                  .append("  FROM SUPP_INFO A                             									\n")
                  .append("       ,(SELECT ARR_ID 															\n")
				  .append("				   ,SUBSTR(APPR_DY, 0, 4) APPR_DY									\n")
				  .append("				   ,CORP_ID															\n")
				  .append("				   ,ITEM_STD_CD  													\n")
				  .append("			  FROM SUPP_APPR                         								\n")
                  .append("          WHERE CORP_ID = ?     													\n")
                  .append("			   AND ARR_ID = (SELECT MAX(ARR_ID) 									\n")
                  .append("							  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());
            
            
            //System.out.println(sqlstr.toString());
            //System.out.println(search.getParameters());
            
            
            rowSet = search.execute();
            
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
            //System.out.println("getSuppJoinRequestList===>" + e);
            e.printStackTrace();
            throw e;
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    } 
	
    
    
    public ValueObject getUserData(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
        sqlstr.append("    SELECT A.CORP_ID,  DECODE(SUBSTR(NVL(C.OUT_ITEM_CD, '01'),1,2), '06', '14', '07', '14', '08', '14', '01') DOC_CD        \n")
              .append("               , CHK_CREDIT(A.CORP_ID) EVAL_CMP, A.CORP_REG_NO                          \n")
              .append("             FROM SUPP_INFO A, SUPP_APPR B , SUPP_OUT_ITEM C                            \n")
              .append("             WHERE A.CORP_ID = B.CORP_ID(+)                                     \n")
              .append("             AND A.CORP_ID = C.CORP_ID(+)                                     \n")
              .append("             AND C.REPRE_YN(+) = 'Y'                                                    \n")
              .append("             AND A.CORP_REG_NO = ?                                                    \n")
              .append("             AND JOB_DIV = ?                                                            \n");

        
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("p_corp_reg_no").trim());
            search.addParameter(params.get("job_div").trim());
            
            //System.out.println(sqlstr.toString());
            //System.out.println(search.getParameters());
            
            
            rowSet = search.execute();
            
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
            //System.out.println("getUserData===>" + e);
            e.printStackTrace();
            throw e;
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    } 
    
    
    
    /**
     * 첨부파일
     * @param filegb
     * @param id
     * @param returnid
     * @return
     */
    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")+")";
                if(i+1!=rowSet.size()){
                    str +="\r\n";
                }
            }
            vo.set(returnid, str);
        return vo;
    }
    
    
    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"));
            //System.out.println(sqlStr.toString());
            //System.out.println(search.getParameters());
            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;
        }
    }
	
    /** 첨부서류조회 
     * @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;
    }
	
    public RowSet getOutLicMaster_searchCombo(){
        
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr = " SELECT LIC_CD, LIC_NM  FROM OUT_LIC_MASTER ORDER BY LIC_CD ASC                 \n";
             
            search.setStatement(sqlstr);
            
            //System.out.println("search=======>" + sqlstr);
            listRowSet = search.execute();
            
        }catch(Exception e){
            //System.out.println(e);
            e.printStackTrace();
        }finally{
    
            if( listRowSet == null ){
                listRowSet = new RowSetImpl();
            }
            
            return listRowSet;
        }
    }
    
    
    
    private RowSet getBaseYear(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
        	sqlStr.append("   	SELECT                                                                                                                 			       			\n")
		          .append("   		CASE                                                                                                                                 		\n")
		          .append("   	    	WHEN TO_CHAR(SYSDATE,'MM') >= TO_NUMBER((SELECT CHG_CODE01 FROM EBID_COM911T WHERE BASECD='SUPP_STD_MONTH')) THEN TO_CHAR(SYSDATE,'YYYY')    		\n")
		          .append("    	   		ELSE TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')                                                                                      		\n")
		          .append("			END G_BASE_YEAR                                                                                                                    			\n")
		          .append("		FROM DUAL                                                                                                                              			\n");
                
            search.setStatement(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;
        }
    }
}