/**
 * @(#)file    ContPriceSearch.java
 * @(#)author  Seokjin
 * @(#)version 1.0
 * @(#)date    2015-01-09
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.purchase.biz;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Random;

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
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.common.code.CommCodeManager;
import kr.co.udapsoft.common.util.PDFMaker;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.ui.InitCombo;
import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class ContPriceSearchBiz extends Biz {

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

	public void doBiz() throws Exception {
        if(storage.compareEvent("")){
			RowSet materialKindCodeLv1 = getLv1MaterialKindCode();
			storage.addRowSet("materialKindCodeLv1", materialKindCodeLv1); 
		}else if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getPayList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("GET_MATL_KIND_CODE_LV2"))
        {
			System.out.println("GET_MATL_KIND_CODE_LV2");
            RowSet materialKindCodeLv2 = getLv2MaterialKindCode(params.get("material_Kind_Code_Lv1"));
            storage.addRowSet( "ROWSET", materialKindCodeLv2 );
            storage.setSuccess(true);
        }
        else if(storage.compareEvent("GET_MATL_KIND_CODE_LV3"))
        {
            RowSet materialKindCodeLv3 = getLv3MaterialKindCode(params.get("material_Kind_Code_Lv2"));
            storage.addRowSet( "ROWSET", materialKindCodeLv3 );
            storage.setSuccess(true);
        }else if(storage.compareEvent("BUDGET_CONTROL")){
			params.set("sheet1", getBudgetList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
	}
	
	// 자재분류 1레벨 코드 가져오기
    public RowSet getLv1MaterialKindCode() {
        Search search       = new SearchImpl();
        RowSet rowSet       = new RowSetImpl();
        StringBuffer sql    = new StringBuffer();
        
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      PARENT_LEVEL MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '0'           \n");            
            
            search.setStatement(sql);
            rowSet = search.execute();
            //WAFLogger.debug(sql.toString());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
 // 자재분류 2레벨 코드 가져오기
    public RowSet getLv2MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '1'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
            
            //WAFLogger.debug(sql.toString());
            //WAFLogger.debug(search.getParameters());
            
            
            search.setStatement(sql);
            rowSet = search.execute();
            
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    // 자재분류 3레벨 코드 가져오기
    public RowSet getLv3MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '2'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
            
            search.setStatement(sql);
            
            rowSet = search.execute();
            //WAFLogger.debug(sql.toString());
            //WAFLogger.debug(search.getParameters());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
	/**
	 * PayMentList 조회
	 * @param Params
	 * @return
	 */
	private RowSet getPayList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT C.MATERIAL_CD                                                                                \n");
        	sqlstr.append("       ,C.ITEM_NAME                                                                                  \n");                                                                         
        	sqlstr.append("       ,C.ITEM_SPEC                                                                                  \n");                                                                           
        	sqlstr.append("       ,C.ITEM_UNIT                                                                                  \n");                                                                           
        	sqlstr.append("       ,A.ORD_DOC_NO                                                                                 \n");                                                                            
        	sqlstr.append("       ,A.MOD_NO                                                                                     \n");                                                                       
        	sqlstr.append("       ,A.CST_DOC_NAME                                                                               \n");                                                                             
        	sqlstr.append("       ,A.CURRENCY_GB                                                                                \n");                                                                            
        	sqlstr.append("       ,F_GET_D_CURRENCY(C.ITEM_PRICE, F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) ITEM_PRICE               \n");
        	sqlstr.append("       ,F_GET_D_CURRENCY(C.ITEM_PRICE * DECODE(A.CURRENCY_GB, 'KRW', 1, NVL(E.EXCHANGE, 1)), 0) ITEM_PRICE_KRW     \n");
        	sqlstr.append("       ,D.MEMBER_NAME                                                                                \n");
        	sqlstr.append("       ,A.DEPT_NAME                                                                                  \n");
        	sqlstr.append("       ,TO_CHAR(B.ORD_CON_DATE, 'YYYY-MM-DD') ORD_CON_DATE                                           \n");
        	sqlstr.append("       ,A.PUR_CONT_ID, A.PUR_CONT_MOD_ID                                                             \n");
        	sqlstr.append("   FROM (SELECT AA.*                                                                                 \n");
        	sqlstr.append("    	          ,RANK() OVER (PARTITION BY AA.PUR_CONT_ID ORDER BY AA.MOD_NO DESC) RNK                \n");
        	sqlstr.append("           FROM PUR2000T AA                                                                          \n");
        	sqlstr.append("          WHERE AA.STATUS_CD = '50') A  --기준계약                                                                                                         \n");
        	sqlstr.append("       ,PUR2000T B           --최초계약                                                                                                                               \n");
        	sqlstr.append("       ,CONT_PUR_ITEM C --자재                                                                                                                                                \n");
        	sqlstr.append("       ,CONT_PUR_SUPP D --업체                                                                                                                                                 \n");
        	sqlstr.append("       ,BID_SUPP E  --환율                                                                                                                                                         \n");
        	sqlstr.append("       ,PMM_COMM_MATERIAL_MASTER F                                                                    \n");
        	sqlstr.append("       ,PMM_MATERIALGROUP_V    G                                                                      \n");
        	sqlstr.append("  WHERE A.RNK = 1                                                                                     \n");
        	sqlstr.append("    AND A.PUR_CONT_ID = B.PUR_CONT_ID                                                                 \n");
        	sqlstr.append("    AND B.MOD_NO = 0                                                                                  \n");
        	sqlstr.append("    AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                                                         \n");
        	sqlstr.append("    AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID                                                         \n");
        	sqlstr.append("    AND D.SIGN_SEQ = 2                                                                                \n");
        	sqlstr.append("    AND A.BID_NO = E.BID_NO                                                                           \n");
        	sqlstr.append("    AND A.BID_DEG = E.BID_DEG                                                                         \n");
        	sqlstr.append("    AND D.CORP_ID = E.VENDOR_CD                                                                       \n");
        	sqlstr.append("    AND C.MATERIAL_CD =  F.MATERIAL_CD                                                                \n");
        	sqlstr.append("    AND F.KIND_BIG = G.MATERIALGROUP                                                                  \n"); 
        	sqlstr.append("    AND C.ITEM_PRICE <> 0                                                                             \n"); 
        	if(!param.get("P_DEPT_CD").isEmpty()) {
        		sqlstr.append(" AND A.DEPT_CD = '" + params.get("P_DEPT_CD") + "'													 \n");
    		}
        	if(!param.get("P_ITEM_NAME").isEmpty()){
        		sqlstr.append("    AND C.ITEM_NAME LIKE '%" + param.get("P_ITEM_NAME") + "%'                                     \n");
        	}
        	if(!param.get("P_ITEM_SPEC").isEmpty()){
        		sqlstr.append("    AND C.ITEM_SPEC LIKE '%" + param.get("P_ITEM_SPEC") + "%'                                     \n");
        	}
        	if(!param.get("P_MEMBER_NAME").isEmpty()){
        		sqlstr.append("    AND D.MEMBER_NAME LIKE '%" + param.get("P_MEMBER_NAME") + "%'                                  \n");
        	}
        	if(!param.get("p_cst_doc_name").isEmpty()){
        		sqlstr.append("    AND A.CST_DOC_NAME LIKE '%" + param.get("p_cst_doc_name") + "%'                                  \n");
        	}
        	if(!param.get("material_Kind_Code_Lv3").isEmpty()){
        		sqlstr.append("    AND C.MATERIAL_CD LIKE '%" + param.get("material_Kind_Code_Lv3") + "%'                          \n");
        	}
        	else {
        		if(!param.get("material_Kind_Code_Lv2").isEmpty()){
        			sqlstr.append("    AND C.MATERIAL_CD LIKE '" + param.get("material_Kind_Code_Lv2") + "%'                       \n");
        		}
        		else{
        			sqlstr.append("    AND G.PARENT_LEVEL  LIKE '%" + param.get("material_Kind_Code_Lv1") + "%'                        \n");
        		}        		
        	}
        	
        	sqlstr.append("    AND B.ORD_CON_DATE BETWEEN TO_DATE('" + param.get("P_FROM_DT") + "') AND TO_DATE('" + param.get("P_TO_DT") + "')  \n");
        	
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug(search.getParameters());
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	} 
	
	
	private RowSet getBudgetList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT C.REQ_DOC_NO                                                                                \n");
        	sqlstr.append("       ,C.CST_DOC_NAME                                                                              \n");
        	sqlstr.append("       ,TO_CHAR(C.CHG_CON_AMT/1000,'FM99,999,999,999,990') CONT_AMT                                 \n");
        	sqlstr.append("       ,TO_CHAR(C.CHG_EXE_AMT/1000,'FM99,999,999,999,990') EXE_AMT                                  \n");
        	sqlstr.append("       ,TO_CHAR(C.ORD_SUPPLY_AMT/1000,'FM99,999,999,999,990') ORD_SUPPLY_AMT                        \n");
        	sqlstr.append("       ,TO_CHAR((NVL(D.EXCHANGE,1)*C.ORD_SUPPLY_AMT)/1000,'FM99,999,999,999,990' )  ORD_AMT_KRW     \n");
        	sqlstr.append("       ,TO_CHAR(C.ORD_CON_DATE, 'YYYY/MM/DD') ORD_CON_DATE                                          \n");
        	sqlstr.append("       ,DECODE(NVL(C.CHG_EXE_AMT,0),0,0,ROUND(C.ORD_SUPPLY_AMT * NVL(D.EXCHANGE,1) / C.CHG_EXE_AMT * 100,2)) RATE        \n");
        	sqlstr.append("       ,TO_CHAR((C.CHG_EXE_AMT - C.ORD_SUPPLY_AMT*NVL(D.EXCHANGE,1))/1000,'FM99,999,999,999,990') ADV_AMT             \n");
        	sqlstr.append("       ,C.CURRENCY_GB CUR_GB                                                                        \n");
        	sqlstr.append("       ,B.MEMBER_NAME                                                                               \n");
        	sqlstr.append(" FROM (SELECT PUR_CONT_MOD_ID,  RANK() OVER(PARTITION BY PUR_CONT_ID ORDER BY MOD_NO DESC  )  RNK   \n");
        	sqlstr.append("         FROM PUR2000T                                                                              \n");
        	sqlstr.append("        WHERE CON_CLS_YN = 9 ) A                                                                    \n");
        	sqlstr.append("       ,CONT_PUR_SUPP B                                                                             \n");
        	sqlstr.append("       ,PUR2000T C                                                                                  \n");
        	sqlstr.append("       ,BID_SUPP D                                                                                  \n");
        	sqlstr.append(" WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID                                                        \n");
        	sqlstr.append("   AND B.SIGN_SEQ = 2                                                                               \n");
        	sqlstr.append("   AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                                                        \n");
        	sqlstr.append("   AND A.RNK = 1                                                                                    \n");
        	sqlstr.append("   AND C.BID_NO = D.BID_NO                                                                          \n");
        	sqlstr.append("   AND C.BID_DEG = D.BID_DEG                                                                        \n");
        	sqlstr.append("   AND B.CORP_ID = D.VENDOR_CD                                                                      \n");
        	sqlstr.append("   AND D.SUCC_YN = 'Y'                                                                              \n");
        	if(!param.get("P_DEPT_CD").isEmpty()) {
        		sqlstr.append(" AND C.DEPT_CD ='" + params.get("P_DEPT_CD") + "'							   \n");
    		}
        	if(!param.get("P_MEMBER_NAME").isEmpty()){
        		sqlstr.append("    AND upper(B.MEMBER_NAME) LIKE upper('%" + param.get("P_MEMBER_NAME") + "%')                                  \n");
        	}
        	if(!param.get("p_cst_doc_name").isEmpty()){
        		sqlstr.append("    AND upper(C.CST_DOC_NAME) LIKE upper('%" + param.get("p_cst_doc_name") + "%')                                  \n");
        	}
        	sqlstr.append("    AND C.ORD_CON_DATE BETWEEN TO_DATE('" + param.get("P_FROM_DT") + "') AND TO_DATE('" + param.get("P_TO_DT") + "')  \n");
        	sqlstr.append(" ORDER BY REQ_DOC_NO                                                                                \n");
        	
        	
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug(search.getParameters());
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	} 
}