package kr.co.udapsoft.ebid.buyer.supplier.outside.biz;

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

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.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 oracle.toplink.internal.ox.StrBuffer;

public class OutSuppFixApprPop2Biz extends Biz {
	
	public OutSuppFixApprPop2Biz(HandlerStorage storage) {
		super(storage);
	}
	
	public void doBiz() throws Exception {
		
		RowSet rowset = null;
        ValueObject vo = new ValueObject();
        ValueObject suppVo = new ValueObject();
        
        ValueObject defaultParam = getDefaultParam(params.get("ACMP_ID"));
        
		params.set("corp_no", defaultParam.get("CORP_REG_NO"));
		params.set("corp_id", defaultParam.get("CORP_ID"));
		params.set("dept_cd", defaultParam.get("DEPT_CD"));
		params.set("dept_name", defaultParam.get("DEPT_NM"));
		params.set("acmp_id", defaultParam.get("ACMP_ID"));
		params.set("subContId", defaultParam.get("SUB_CONT_ID"));
		params.set("status_cd", defaultParam.get("STATUS_CD"));
		params.set("startWorkDate", defaultParam.get("START_WORK_DATE"));
		params.set("endWorkDate", defaultParam.get("END_WORK_DATE"));
		params.set("con_supply_tamt", defaultParam.get("CON_SUPPLY_TAMT"));
		params.set("con_supply_amt", defaultParam.get("CON_SUPPLY_AMT"));
		
        params.set("p_doc_cd", "16");  //2015.07.29 설계.인허가 
        
        vo = getAppr(params);
        params.set("chk_cnt", vo.get("corp_id"));
        
        
        if(params.get("chk_cnt").equals("")) {
            suppVo = getSupp(params);
        }
        
        
        rowset = getApprSrdList(params);
        
        storage.addRowSet("getApprSrdList", rowset);
        
        if(params.get("chk_cnt").equals("")) {
            storage.addValueObject("getAppr", suppVo);
        } else {
            storage.addValueObject("getAppr", vo);
        }
			
	}
	
	public ValueObject getSupp(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        try {
        
			sqlstr.append("      SELECT CORP_KOR, CORP_TEL, REP_NAME,                 \n");
			sqlstr.append("      (SELECT (SELECT F_GET_OUT_ITEM_NAME(OUT_ITEM_CD) FROM BID_MASTER WHERE BID_NO = X.BID_NO AND BID_DEG = X.BID_DEG) OUT_ITEM_NO                 \n");
			sqlstr.append("					FROM SUB2000T X                 \n");
			sqlstr.append("				WHERE X.SUB_CONT_ID = ?                \n");
			sqlstr.append("					AND X.MOD_NO = 0) ITEM_NAME                  \n"); 
			sqlstr.append("      FROM SUPP_INFO                                                                         \n");
			sqlstr.append("      WHERE CORP_ID = ?                                                               \n");
            
			search.setStatement(sqlstr.toString());
            search.addParameter(params.get("subContId"));
            search.addParameter(params.get("corp_id"));
            
            rowSet = search.execute();

            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        
        }catch(Exception e){
            System.out.println(this.getClass().getName() + " --> getUserName() : \n" + e.getMessage());
            System.out.println("SQL : " + search.getStatement());
            System.out.println(e);
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    }
	
	
	public ValueObject getAppr(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        ValueObject user = storage.getUser();
        
        StringBuffer sqlstr = new StringBuffer();
        
        try {
        
            sqlstr.append("        SELECT CORP_ID, CORP_KOR, CORP_TEL, CORP_MANAGER, REP_NAME, SALES_3YEAR, ITEM_NAME, CON_AMT, APPR_NOTE,                               \n");
            sqlstr.append("                   TO_CHAR(TO_DATE(START_WORK_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD' ) START_WORK_DATE,                           \n");
            sqlstr.append("                   TO_CHAR(TO_DATE(END_WORK_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') END_WORK_DATE,                           \n");
            sqlstr.append("                   TO_CHAR(TO_DATE(START_REAL_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD' ) START_REAL_DATE,                           \n");
            sqlstr.append("                   TO_CHAR(TO_DATE(END_REAL_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD' ) END_REAL_DATE,                           \n");
            sqlstr.append("                   ARR_ID,  ACMP_ID, REGU_STATUS_CD,                           \n"); 
            sqlstr.append("         		  DECODE(APPR_BY_ID, '0', '', F_GET_USER_NM(APPR_BY_ID))  USER_NAME								\n");		//작성자
            sqlstr.append("              FROM SUPP_APPR WHERE CORP_ID = ?  AND DOC_CD = ? AND CONT_ID = ?                                \n");
            
            search.setStatement(sqlstr.toString());
            //search.addParameter(user.get("USER_NAME"));
            search.addParameter(params.get("corp_id"));
            search.addParameter(params.get("p_doc_cd"));
            search.addParameter(params.get("subContId")); 
            
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
            System.out.println(this.getClass().getName() + " --> getUserName() : \n" + e.getMessage());
            System.out.println("SQL : " + search.getStatement());
            System.out.println(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");
               sqlStr.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");
               if(!params.get("chk_cnt").equals("")) {
            	   sqlStr.append("      , B.APPR_RATE, B.APPR_REASON                                 \n");
               }
               sqlStr.append("                  FROM                                                                                                                                                  \n");
               sqlStr.append("                        (                                                                                                                                             \n");
               sqlStr.append("                           SELECT                                                                                                                                            \n");
               sqlStr.append("                                 Z.APPR_LEVEL,    Z.APPR_ACLS,     Z.APPR_CD,  Z.DOC_CD,                                                                                 \n");
               sqlStr.append("                                 LAST_VALUE(Z.GUBUN_1 IGNORE NULLS) OVER(ORDER BY Z.APPR_CD) GUBUN_1,                                                                              \n");
               sqlStr.append("                                 LAST_VALUE(Z.GUBUN_2 IGNORE NULLS) OVER(ORDER BY Z.APPR_CD) GUBUN_2,                                                                          \n");
               sqlStr.append("                                 LAST_VALUE(Z.GUBUN_3 IGNORE NULLS) OVER(ORDER BY Z.APPR_CD) GUBUN_3,                                                                     \n");
               sqlStr.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");        
               sqlStr.append("                                 FROM                                                                                                                                                                                        \n");
               sqlStr.append("                                 (                                                                                                                                                                                                 \n");
               sqlStr.append("                                      SELECT LEVEL APPR_LEVEL,    X.APPR_ACLS  , APPR_CD, X.STOCK_RATE,   X.DOC_CD,                                                                  \n");
               sqlStr.append("                                         DECODE(LEVEL, '1', X.APPR_ACLS || '(' || X.STOCK_RATE || ')' , '') GUBUN_1,                                                                                                \n");
               sqlStr.append("                                         DECODE(TRIM(X.APPR_ACLS), '', ' ', DECODE(LEVEL, '2', X.APPR_ACLS , '')) GUBUN_2,     -- || '(' || X.STOCK_RATE || ')'                          \n");
               sqlStr.append("                                         DECODE(X.APPR_ACLS, '', '', DECODE(LEVEL, '3', X.APPR_ACLS, '')) GUBUN_3,                                                                                                                                           \n");
               sqlStr.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");
               sqlStr.append("                                         FROM(                                                                                                                     \n");
               sqlStr.append("                                                 SELECT ADM_YEAR,                                                                                      \n");
               sqlStr.append("                                                            DOC_CD,                                                                                    \n");
               sqlStr.append("                                                            PARENT_LEVEL,                                                                      \n");
               sqlStr.append("                                                            APPR_CD,                                                                                         \n");
               sqlStr.append("                                                            APPR_MCD,                                                                                  \n");
               sqlStr.append("                                                            APPR_ACLS,                                                                            \n");
               sqlStr.append("                                                            STOCK_RATE,                                                                     \n");
               sqlStr.append("                                                            A_CONTENT,                                                                    \n");
               sqlStr.append("                                                            A_RATE,                                                                     \n");
               sqlStr.append("                                                            B_CONTENT,                                                                    \n");
               sqlStr.append("                                                            B_RATE,                                                                     \n");
               sqlStr.append("                                                            C_CONTENT,                                                                    \n");
               sqlStr.append("                                                            C_RATE,                                                                     \n");
               sqlStr.append("                                                            D_CONTENT,                                                                    \n");
               sqlStr.append("                                                            D_RATE,                                                                     \n");
               sqlStr.append("                                                            E_CONTENT,                                                                    \n");
               sqlStr.append("                                                            E_RATE,                                                                     \n");
               sqlStr.append("                                                            NOTI                                                                       \n");       
               sqlStr.append("                                                        FROM APPR_STD                                                              \n");
               sqlStr.append("                                                       WHERE DOC_CD = DECODE(?, '06', '16', '12') --2015.07.29 12                          \n");
               sqlStr.append("                                                         AND ADM_YEAR IN(SELECT CHG_CODE01 FROM EBID_COM911T WHERE BASECD = 'SUPP_REPORT' AND DETAILCD = '16' )    \n");	//2022.05.17 기준년월 추가
               sqlStr.append("                                                 )  X                                                                                      \n");
               sqlStr.append("                                  START WITH PARENT_LEVEL = 'S'                                                                          \n");
               sqlStr.append("                                      CONNECT BY PRIOR APPR_CD = PARENT_LEVEL                                                             \n");
               sqlStr.append("                                      AND LEVEL <= 2                                                                                       \n");
               sqlStr.append("                                      ORDER SIBLINGS BY PARENT_LEVEL ASC, APPR_CD ASC                                                      \n");
               sqlStr.append("                             )   Z                                                                                                         \n");
                       if(!params.get("chk_cnt").equals(""))
                       {
                       sqlStr.append("                    )    A, SUPP_APPR_ITEM B, SUPP_APPR C                                                                   \n")
                               .append("                         WHERE A.APPR_CD = B.APPR_CD                                      \n")
                               .append("                            AND B.CORP_ID = C.CORP_ID                                      \n")
                               .append("                            AND B.ARR_ID = C.ARR_ID                                      \n")
                               .append("                            AND B.CORP_ID = ?                                      \n")
                               .append("                            AND C.CONT_ID = ?                                      \n")
                               .append("                            AND GUBUN_2 IS NOT NULL                                      \n");
                       }
                       else
                       {
                           sqlStr.append("                    )    A                                                              \n")
                                   .append("                            WHERE GUBUN_2 IS NOT NULL                                      \n");
                       }
                       sqlStr.append("                            AND APPR_LEVEL = '2'                               \n");
                                               
            
            
            
            search.setStatement(sqlStr.toString());
            search.addParameter("06");
            if(!params.get("chk_cnt").equals(""))
            {
            	search.addParameter(params.get("corp_id"));
                search.addParameter(params.get("subContId"));
            }
            
            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;
        }
    }

    private ValueObject getDecryptParam(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("SELECT	F_DECRYPT(?) AS ACMP_ID						\n");
        	sqlstr.append("			,F_DECRYPT(?) AS OPEN_TIME							\n");
        	sqlstr.append("			,TO_CHAR(SYSDATE,'YYYYMMDDHH24MI')  AS CURR_TIME	\n");
        	sqlstr.append("FROM	DUAL													\n");
        	
			search.setStatement(sqlstr.toString());
			search.addParameter(params.get("acmpId"));
			search.addParameter(params.get("openTime"));
			
            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;
        }
	}
    
    private ValueObject getDefaultParam(String acmpId) {
    	
    	Search search = new SearchImpl();
    	RowSet rowSet = new RowSetImpl();
    	ValueObject valueobject = null;
    	StrBuffer sqlstr = new StrBuffer();
    	try{
    		
    		sqlstr.append("SELECT  A.ACMP_ID\n");
    		sqlstr.append("        ,A.DEPT_CD\n");
    		sqlstr.append("        ,GET_DEPT_NM(A.DEPT_CD) AS DEPT_NM\n");
    		sqlstr.append("        ,A.SUB_CONT_ID\n");
    		sqlstr.append("        ,C.CORP_REG_NO\n");
    		sqlstr.append("        ,TO_CHAR(B.START_WORK_DATE, 'YYYY-MM-DD') AS START_WORK_DATE\n");
    		sqlstr.append("        ,TO_CHAR(B.END_WORK_DATE, 'YYYY-MM-DD') AS END_WORK_DATE\n");
    		sqlstr.append("        ,B.CON_SUPPLY_TAMT\n");
    		sqlstr.append("        ,A.STATUS_CD\n");
    		sqlstr.append("        ,C.CORP_ID\n");
    		sqlstr.append("        ,B.CON_SUPPLY_AMT\n");		//2023.07.12 공급가액(과세+면세) 추가
    		sqlstr.append("FROM    SUB1040T A\n");
    		sqlstr.append("        ,SUB2000T B\n");
    		sqlstr.append("        ,SUB1020T C\n");
    		sqlstr.append("WHERE   A.SUB_CONT_ID = B.SUB_CONT_ID\n");
    		sqlstr.append("AND     A.MOD_NO = B.MOD_NO\n");
    		sqlstr.append("AND     A.CON_CORP_ID = C.CON_CORP_ID\n");
    		sqlstr.append("AND     A.ACMP_ID = ?\n");
    
    		search.setStatement(sqlstr.toString());
    		search.addParameter(acmpId);
    		
    		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;
    	}
    }
}
