/**
 * @(#)file    SUBAcmpSignFormBiz.java
 * @(#)author  DaeJin Lee
 * @(#)modifier cutePrettyKara
 * @(#)modified date    2011.12.14
 * @(#)version 1.0
 * @(#)date    Jun 23, 2005
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.dcchem.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of dcchem, Inc.
 */
package kr.co.udapsoft.ebid.buyer.supplier.purchase.biz;


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;


import java.sql.Connection;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.logger.Logger;
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;

public class PuSuppFixApprPopBiz extends Biz {
	
	public PuSuppFixApprPopBiz(HandlerStorage storage) {
        super(storage);
    }

    public void doBiz() throws Exception {
           
        
        RowSet rowset = null;
        ValueObject vo = new ValueObject();
        ValueObject vo2 = new ValueObject();
        ValueObject vo3 = new ValueObject();
        
        vo = getCorpInfo(params);
        params.set("corp_no", vo.get("corp_reg_no"));
        params.set("corp_kor", vo.get("corp_kor"));
        params.set("doc_cd", vo.get("doc_cd"));
        
        vo2 = getAppr(params);
        params.set("chk_cnt", vo2.get("corp_id"));

        vo3 = getDeptNm(params);
        params.set("dept_nm", vo3.get("dept_nm"));
        params.set("user_id", vo3.get("acct_id"));
        params.set("user_nm", vo3.get("user_nm"));
        
        rowset = getApprSrdList(params);
        
        storage.addRowSet("getApprSrdList", rowset);
        
        //2016.4.20 이전 수정자 표기. 
        if (!params.get("chk_cnt").equals(""))
        {
         	params.set("user_id_before", rowset.getRow(0).get("USER_ID"));  
         	params.set("user_nm_before", rowset.getRow(0).get("USER_NAME"));  
            params.set("dt_before", rowset.getRow(0).get("APPR_DY"));
        }
        else{
        	params.set("user_id_before", vo3.get("acct_id")); 
        	params.set("user_nm_before", vo3.get("user_nm")); 
            params.set("dt_before", (new java.text.SimpleDateFormat ("yyyy년 MM월 dd일")).format (new java.util.Date()));
        }
        storage.addValueObject("getAppr", vo2);
        
    }
    
    
    public ValueObject getAppr(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        
        StringBuffer sqlstr = new StringBuffer();
        
        try {
        
            sqlstr.append("        SELECT CORP_ID, CORP_KOR, ARR_ID,  PAYREQ_ID , APPR_NOTE  ,WRITE_NO, WRITE_NM                        \n"); 
            sqlstr.append("              FROM SUPP_APPR WHERE CORP_ID = ?  AND DOC_CD = ? AND CONT_ID = ?                                \n");
            
            
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("p_corp_id"));
            search.addParameter(params.get("doc_cd"));
            search.addParameter(params.get("p_pur_cont_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){
        	WAFLogger.debug(this.getClass().getName() + " --> getAppr() : \n" + e.getMessage());
        	WAFLogger.debug("SQL : " + search.getStatement());
        	WAFLogger.debug(e);
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    }

    public ValueObject getDeptNm(ValueObject params){
    	Search search = new SearchImpl();
    	ValueObject vo = null;
    	RowSet rowSet = new RowSetImpl();
    	
    	
    	StringBuffer sqlstr = new StringBuffer();
    	
    	try {
    		
    		sqlstr.append("        SELECT GET_DEPT_NM(?) AS DEPT_NM , ACCT_ID, USER_NAME_K AS USER_NM     	\n"); 
    		sqlstr.append("              FROM PUB0130V                            							\n");
    		sqlstr.append("        WHERE USER_SABUN = ?                           							\n");
    		
    		
    		search.setStatement(sqlstr.toString());
    		search.addParameter(params.get("p_dept_cd"));
    		search.addParameter(params.get("p_user_sabun"));
    		
    		
    		rowSet = search.execute();
    		
    		
    		WAFLogger.debug(sqlstr.toString());
    		WAFLogger.debug(search.getParameters());
    		
    		
    		if(rowSet.size()>0){
    			vo = RowSetUtility.getValueObject(rowSet.getRow(0));
    		}
    		
    		
    	}catch(Exception e){
    		WAFLogger.error(e);
    	}finally{
    		if( vo == null) vo = new ValueObject();
    		return vo;
    	}
    }
    
    
    
    
    public ValueObject getCorpInfo(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        
        StringBuffer sqlstr = new StringBuffer();
        
        try {
        
            sqlstr.append("        SELECT CORP_KOR, DECODE(MFTR_DIV_CD, '1', '03', '2', '04', '04') DOC_CD, CORP_REG_NO  FROM SUPP_INFO WHERE 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){
        	WAFLogger.error(e);
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    }
    
    
    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, A.DISPLAY_YN          \n");
                       if(!params.get("chk_cnt").equals(""))
                       {
                           sqlStr.append("                , B.APPR_RATE, B.APPR_REASON                                  \n")
                                 .append("                , D.ACCT_ID USER_ID, D.USER_NAME, SUBSTR(C.APPR_DY,1,4) || '년 ' || SUBSTR(C.APPR_DY,5,2) || '월 ' || SUBSTR(C.APPR_DY,7,2) || '일' APPR_DY                      \n");
                           
                       }
               sqlStr.append("                  FROM                                                                                                                                                  \n")
                       .append("                        (                                                                                                                                             \n")
                       .append("                           SELECT DISTINCT                                                                                                                                            \n")
                       .append("                                 Z.APPR_LEVEL,    Z.APPR_ACLS,     Z.APPR_CD,  Z.DOC_CD,  Z.ADM_YEAR,                                                                              \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, Z.DISPLAY_YN            \n")        
                       .append("                                 FROM                                                                                                                                                                                        \n")
                       .append("                                 (                                                                                                                                                                                                 \n")
                       .append("                                      SELECT LEVEL APPR_LEVEL,    X.APPR_ACLS  , APPR_CD, X.STOCK_RATE,   X.DOC_CD,     X.ADM_YEAR,                                                             \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, X.DISPLAY_YN                                 \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("                                                            DISPLAY_YN                                                                  \n")       
                       .append("                                                        FROM APPR_STD                                                                                                                 \n")
                       .append("                                                        WHERE  DOC_CD = ?                                                                                                           \n");
                       if(!params.get("chk_cnt").equals(""))  //2016.4.19
                       {
                    	   sqlStr.append("  AND      ADM_YEAR = (SELECT MAX(ADM_YEAR) FROM SUPP_APPR_ITEM A,SUPP_APPR B WHERE A.CORP_ID = B.CORP_ID AND A.ARR_ID = B.ARR_ID AND B.CORP_ID = ? AND B.CONT_ID=? )            \n");
                       } else {
                    	   sqlStr.append("  AND      ADM_YEAR = (SELECT MAX(ADM_YEAR) FROM APPR_STD WHERE DOC_CD = ?)            \n");
                       }
                 sqlStr.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 order by appr_cd                                                                                                                                               \n");
                       if(params.get("chk_cnt").equals(""))
                       {
                           sqlStr.append("                    )    A                                                      \n")
                                   .append("                            WHERE A.GUBUN_2 IS NOT NULL                                      \n");
                                   //.append("                              and A.ADM_YEAR = (SELECT MAX(ADM_YEAR) FROM APPR_STD WHERE DOC_CD = ?)         \n");
                       }
                       else
                       {
                           sqlStr.append("                    )    A , SUPP_APPR_ITEM B, SUPP_APPR C , PUB0120T D                                                                   \n")
                                   .append("                         WHERE A.APPR_CD = B.APPR_CD AND A.ADM_YEAR = B.ADM_YEAR                                     \n")
                                   .append("                            AND B.CORP_ID = C.CORP_ID                                      \n")
                                   .append("                            AND B.ARR_ID = C.ARR_ID  AND C.APPR_BY_ID=D.ACCT_ID           \n")
                                   .append("                            AND B.CORP_ID = ?                                      \n")
                                   .append("                            AND C.CONT_ID = ?                                      \n")
                                   .append("                            AND A.GUBUN_2 IS NOT NULL                                      \n");
                       }
               sqlStr.append("                            AND A.APPR_LEVEL = '3'                               \n")
                     //.append("                            AND (A.APPR_CD LIKE '01%' OR A.APPR_CD LIKE '02%' OR A.APPR_CD LIKE '05%')        \n");
                     .append("                            AND A.DISPLAY_YN = 'Y'         \n");
                                             
            
            
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("doc_cd"));
            //search.addParameter(params.get("doc_cd"));
            if(!params.get("chk_cnt").equals(""))
            {
            	search.addParameter(params.get("p_corp_id"));
                search.addParameter(params.get("p_pur_cont_id"));
            	search.addParameter(params.get("p_corp_id"));
                search.addParameter(params.get("p_pur_cont_id"));
            }
            else
            {
            	search.addParameter(params.get("doc_cd"));
            }
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(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;
        }
    }

}
