/**
 * @(#)file    SuppChoicePopBiz.java
 * @(#)author  이성민
 */
package kr.co.udapsoft.ebid.buyer.common.corp.biz;



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

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.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 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 SuppChoicePopBiz extends Biz {

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

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

		
//		TestCommon common = new TestCommon();
		
		WAFLogger.debug("event==============>" + storage.getEvent());
		WAFLogger.debug("params==============>" + params);
		if(storage.compareEvent("")){
//	        RowSet acctTypeRowSet = common.getAcctTypeUseRowSet(storage.getUser().get("ACCT_TYPE_CODE"));
//	        storage.addRowSet("AcctTypeRowSet", acctTypeRowSet);
			
		}
		else if(storage.compareEvent("GET_CORP_LIST"))
		{
			
	        params.set("sheet1", getList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		    
		    
		    
		}
		else if(storage.compareEvent("GET_MATL_KIND_CODE_LV1")) 
		{
		    RowSet materialKindCodeLv1 = null;
		    
		    if(params.get("corp_status").equals("P"))
		    {
		        materialKindCodeLv1 = getLv1MaterialKindCode();
		    }
		    else
		    {
		        materialKindCodeLv1 = getLv1MaterialKindCode_2();
		    }
		    
            storage.addRowSet("ROWSET", materialKindCodeLv1);
            storage.setSuccess(true);
		}
		else if(storage.compareEvent("GET_MATL_KIND_CODE_LV2")) 
        {
            RowSet materialKindCodeLv2 = null;
            
            
            if(params.get("corp_status").equals("P"))
            {
                materialKindCodeLv2 = getLv2MaterialKindCode(params.get("materialgroup"));
            }
            else
            {
                materialKindCodeLv2 = getLv2MaterialKindCode_2(params.get("materialgroup"));
            }
            
            storage.addRowSet("ROWSET", materialKindCodeLv2);
            storage.setSuccess(true);
        }
		else if(storage.compareEvent("GET_MATL_KIND_CODE_LV3")) 
        {
            RowSet materialKindCodeLv3 = null;
            WAFLogger.debug(params.get("corp_status"));
            if(params.get("corp_status").equals("P"))
            {
                materialKindCodeLv3 = getLv3MaterialKindCode(params.get("materialgroup"));
            }
            else
            {
                materialKindCodeLv3 = getLv3MaterialKindCode_2(params.get("materialgroup"));
            }
            
            storage.addRowSet("ROWSET", materialKindCodeLv3);
            storage.setSuccess(true);
        }
	}
	
	
	
	
	
	/**
     * 협력업체 목록 
     * @param Params
     * @return
     */
    private RowSet getList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try { 
			sqlStr.append("SELECT DISTINCT 																	\n")                        
				  .append("		  A.CORP_ID              													\n")
			      .append("		  ,A.CORP_REG_NO      														\n")
				  .append("		  ,A.CORP_KOR             													\n")
				  .append("		  ,A.REP_NAME         														\n") 
				  .append("		  ,B.USER_NAME            													\n")
				  .append("		  ,B.TEL_NUM          														\n")
				  .append("		  ,DECODE(B.HP1, '', '', B.HP1 || '-' || B.HP2 || '-' || B.HP3) AS H_PHON	\n")
				  .append("		  ,B.EMAIL   																\n")
				  .append("		  ,A.IN_OUT_DIV																\n");
			if(params.get("menu_type").equals("QUOT")){
				sqlStr.append("		  ,A.\"COMMENT\"		\n");
			}else{
				sqlStr.append("		  ,DECODE(A.JOB_DIV, 'P', A.\"COMMENT\", A.O_COMMENT) AS \"COMMENT\"		\n");
			}
				  
			if(!params.get("corp_status").equals(""))
			{
				if(params.get("corp_status").equals("P"))
				{
					sqlStr.append("		  ,F.APPR_TOT_RATE   																\n");
					sqlStr.append("		  ,DECODE(G.PJT_VALID_BID_YN,'N','입찰불가',DECODE(F.VALID_BID_YN,'Y','입찰가능','N','입찰불가','미평가')) AS VALID_BID_YN_NM 		\n");
					sqlStr.append("		  ,F.VALID_BID_YN   																\n");
				}
			}
				  
			sqlStr.append("  FROM SUPP_INFO A																\n")
				  .append("		  ,SUPP_CHARGE B															\n");
			if(!params.get("corp_status").equals(""))
			{
				if(params.get("corp_status").equals("P"))
				{
					sqlStr.append("		  ,SUPP_PUR_ITEM C															\n")
					  	  .append("		  ,PMM_MATERIALGROUP_V D													\n")
					      .append("       ,PMM_MATERIALGROUP_V E													\n")
					      .append("		  ,(SELECT CORP_ID, MAX(APPR_TOT_RATE) AS APPR_TOT_RATE , MAX(APPR_DY) APPR_DY, MAX(ARR_ID) ARR_ID, DOC_CD,VALID_BID_YN    	\n")  
	                      .append("	          FROM SUPP_APPR 																							\n")
						  .append("	         WHERE DOC_CD IN ('01', '02')  AND (CORP_ID,ARR_ID) IN (SELECT CORP_ID,MAX(ARR_ID)             	        	\n")
						  .append("                                                                   FROM SUPP_APPR                     				\n")
						  .append("                                                                  WHERE DOC_CD IN ('01', '02')        				\n")
						  .append("                                                                  GROUP BY CORP_ID)                   				\n")
						  .append("          GROUP BY CORP_ID, DOC_CD,VALID_BID_YN ) F            	                                             		\n")
						  .append("          ,(SELECT CORP_ID																							\n")
						  .append("                 , CASE WHEN MAX(NONVALIDATE) > TO_CHAR(SYSDATE,'YYYYMMDD') THEN 'N'									\n")
						  .append("                        ELSE 'Y'																						\n")
						  .append("                    END PJT_VALID_BID_YN																				\n")
						  .append("                FROM SUPP_APPR																						\n")
						  .append("               WHERE DOC_CD IN ('03', '04') 																			\n")
						  .append("              GROUP BY CORP_ID) G 																					\n");
				}else
				{
					sqlStr.append("		  ,SUPP_OUT_ITEM C															\n")   
						  .append("		  ,OUT_ITEM D																\n")
					      .append("		  ,OUT_ITEM E																\n")
					      .append("		  ,OUT_ITEM F																\n");
				}
			}

			sqlStr.append(" WHERE A.CORP_ID = B.CORP_ID														\n")
				  .append("   AND A.STATUS IN ('02','03')													\n");
			
			if(!params.get("corp_status").equals(""))
			{
				sqlStr.append("   AND A.CORP_ID = C.CORP_ID														\n");
				if(params.get("corp_status").equals("P"))
				{
					sqlStr.append("   AND C.KIND_SMALL = D.MATERIALGROUP											\n")
					  	  .append("   AND E.MATERIALGROUP = D.PARENT_LEVEL											\n")
					      .append("   AND E.MATERIAL_LEVEL = '1'    												\n")
					      .append("   AND A.CORP_ID = F.CORP_ID(+)    												\n")
					      .append("   AND A.CORP_ID = G.CORP_ID(+)    												\n");
				}else
				{
					
					sqlStr.append("   AND C.OUT_ITEM_CD = D.ITEM_CD													\n")
						  .append("   AND D.PARENT_LEVEL = E.ITEM_CD												\n")
						  .append("   AND E.PARENT_LEVEL = F.ITEM_CD												\n");  
				}
			}

			if(!params.get("material_Kind_Code_Lv1").equals(""))
            {
				if(params.get("corp_status").equals("P"))
				{
					sqlStr.append("   AND E.PARENT_LEVEL = ?													\n");
				}else
				{
					sqlStr.append("   AND F.ITEM_CD = ?															\n");
				}
            }
			
			if(!params.get("material_Kind_Code_Lv2").equals(""))
            {
				if(params.get("corp_status").equals("P"))
				{
					sqlStr.append("   AND D.PARENT_LEVEL = ?													\n");
				}else
				{
					sqlStr.append("   AND E.ITEM_CD = ?															\n");
				}
				
            }
			
			if(!params.get("material_Kind_Code_Lv3").equals(""))
            {
				if(params.get("corp_status").equals("P"))
				{
					sqlStr.append("   AND C.KIND_SMALL = ?														\n");
				}else
				{
					sqlStr.append("   AND D.ITEM_CD = ?															\n");
				}
            }
			
			if(!params.get("corp_status").equals(""))
            {
				sqlStr.append("   AND A.JOB_DIV = ?															\n");
            }

            if(!params.get("corp_no").equals(""))
            {
                sqlStr.append("   AND A.CORP_REG_NO LIKE('%'|| ? ||'%')                                      \n");
             }
            if(!params.get("corp_name").equals(""))
            {
            	if(params.get("search_gb").equals("1")){ //업체명 + 의견
            		sqlStr.append("     AND (UPPER(A.CORP_KOR) LIKE ('%'|| ? ||'%') OR UPPER(DECODE(A.JOB_DIV, 'P', A.\"COMMENT\", A.O_COMMENT)) LIKE ('%'|| ? ||'%'))      \n");
            	} else if(params.get("search_gb").equals("2")){ //업체명
            		sqlStr.append("         AND UPPER(A.CORP_KOR) LIKE ('%'|| ? ||'%')        \n");
            	} else { //의견
            		sqlStr.append("         AND UPPER(DECODE(A.JOB_DIV, 'P', A.\"COMMENT\", A.O_COMMENT)) LIKE ('%'|| ? ||'%')        \n");
            	}                
            }
            sqlStr.append(" ORDER BY CORP_KOR ASC, CORP_ID DESC       									\n");
        	
           /* SELECT DISTINCT                                                                     
            A.CORP_ID                                                                  
            ,A.CORP_REG_NO                                                              
            ,A.CORP_KOR                                                                 
            ,A.REP_NAME                                                                 
            ,B.USER_NAME                                                                
            ,B.TEL_NUM                                                                  
            ,DECODE(B.HP1, '', '', B.HP1 || '-' || B.HP2 || '-' || B.HP3) AS H_PHON    
            ,B.EMAIL
            ,D.ITEM_CD
            ,E.ITEM_CD
            ,F.ITEM_CD                                                               
    FROM SUPP_INFO A                                                                
            ,SUPP_CHARGE B                                                            
            ,SUPP_OUT_ITEM C   
            ,OUT_ITEM D
            ,OUT_ITEM E
            ,OUT_ITEM F                                                   
   WHERE A.CORP_ID = B.CORP_ID                                                        
     AND A.CORP_ID = C.CORP_ID
     AND C.OUT_ITEM_CD = D.ITEM_CD
     AND D.PARENT_LEVEL = E.ITEM_CD
     AND E.PARENT_LEVEL = F.ITEM_CD                                                                                                                                      
     AND A.STATUS IN ('02','03')                                                    
     AND A.JOB_DIV = 'O'
            
            */
        	
        	/*
            sqlStr.append("     SELECT DISTINCT                         \n")
                   .append("             A.CORP_ID              \n")
                   .append("           , A.CORP_REG_NO      \n")
                   .append("           , A.CORP_KOR             \n")
                   .append("           , A.REP_NAME         \n")
                   .append("           , B.USER_NAME            \n")
                   .append("           , B.TEL_NUM          \n")
                   .append("           , DECODE(B.HP1, '', '', B.HP1 || '-' || B.HP2 || '-' || B.HP3) AS H_PHON         \n")
                   .append("           , B.EMAIL          \n")
                   .append("          FROM SUPP_INFO A, SUPP_CHARGE B           \n");
                    if(!params.get("material_Kind_Code_Lv1").equals(""))
                   {
                        if(params.get("corp_status").equals("P"))
                        {
                            sqlStr.append("            , SUPP_PUR_ITEM C, PMM_MATERIALGROUP D, PMM_MATERIALGROUP E, PMM_MATERIALGROUP F                                   \n");
                        }
                        else
                        {
                            sqlStr.append("             , SUPP_OUT_ITEM C, OUT_ITEM D, OUT_ITEM E, OUT_ITEM F                                   \n");
                        }
                    }
                    
            sqlStr.append("          WHERE A.CORP_ID = B.CORP_ID           \n");
            sqlStr.append("		       AND A.STATUS IN ('02','03')           \n");
              
                    if(!params.get("material_Kind_Code_Lv1").equals(""))
                    {
                        if(params.get("corp_status").equals("P"))
                        {
                            sqlStr.append("     AND B.CORP_ID = C.CORP_ID             \n");
                            sqlStr.append("     AND C.KIND_SMALL = D.MATERIALGROUP             \n");
                            sqlStr.append("     AND D.PARENT_LEVEL = E.MATERIALGROUP             \n");
                            sqlStr.append("     AND E.PARENT_LEVEL = F.MATERIALGROUP             \n");
                            //sqlStr.append("     AND C.REPRE_YN = 'Y'             \n");
                            sqlStr.append("     AND F.MATERIALGROUP = '1'        \n");
                            sqlStr.append("     AND F.MATERIAL_GB  = ?            \n");
                        }
                        else
                        {
                            sqlStr.append("     AND B.CORP_ID = C.CORP_ID             \n");
                            sqlStr.append("     AND C.OUT_ITEM_CD = D.ITEM_CD             \n");
                            sqlStr.append("     AND D.PARENT_LEVEL = E.ITEM_CD             \n");
                            sqlStr.append("     AND E.PARENT_LEVEL = F.ITEM_CD             \n");
                            //sqlStr.append("     AND C.REPRE_YN = 'Y'             \n");
                            sqlStr.append("     AND F.ITEM_CD = ?            \n");
                        }
                     }
                    
                    if(!params.get("material_Kind_Code_Lv2").equals(""))
                    {
                        if(params.get("corp_status").equals("P"))
                        {
                            sqlStr.append("     AND B.CORP_ID = C.CORP_ID             \n");
                            sqlStr.append("     AND C.KIND_SMALL = D.MATERIALGROUP             \n");
                            sqlStr.append("     AND D.PARENT_LEVEL = E.MATERIALGROUP             \n");
                            sqlStr.append("     AND E.PARENT_LEVEL = F.MATERIALGROUP             \n");
                            //sqlStr.append("     AND C.REPRE_YN = 'Y'             \n");
                            sqlStr.append("     AND E.MATERIALGROUP = '2'            \n");
                            sqlStr.append("     AND F.MATERIAL_GB  = ?            \n");
                        }
                        else
                        {
                            sqlStr.append("     AND B.CORP_ID = C.CORP_ID             \n");
                            sqlStr.append("     AND C.OUT_ITEM_CD = D.ITEM_CD             \n");
                            sqlStr.append("     AND D.PARENT_LEVEL = E.ITEM_CD             \n");
                            sqlStr.append("     AND E.PARENT_LEVEL = F.ITEM_CD             \n");
                            //sqlStr.append("     AND C.REPRE_YN = 'Y'             \n");
                            sqlStr.append("     AND E.ITEM_CD = ?            \n");
                        }
                     }
                    
                    if(!params.get("material_Kind_Code_Lv3").equals(""))
                    {
                        if(params.get("corp_status").equals("P"))
                        {
                            sqlStr.append("     AND B.CORP_ID = C.CORP_ID             \n");
                            sqlStr.append("     AND C.KIND_SMALL = D.MATERIALGROUP             \n");
                            sqlStr.append("     AND D.PARENT_LEVEL = E.MATERIALGROUP             \n");
                            sqlStr.append("     AND E.PARENT_LEVEL = F.MATERIALGROUP             \n");
                            //sqlStr.append("     AND C.REPRE_YN = 'Y'             \n");
                            sqlStr.append("     AND D.MATERIALGROUP = '3'            \n");
                            sqlStr.append("     AND D.MATERIAL_GB = ?            \n");
                        }
                        else
                        {
                            sqlStr.append("     AND B.CORP_ID = C.CORP_ID             \n");
                            sqlStr.append("     AND C.OUT_ITEM_CD = D.ITEM_CD             \n");
                            sqlStr.append("     AND D.PARENT_LEVEL = E.ITEM_CD             \n");
                            sqlStr.append("     AND E.PARENT_LEVEL = F.ITEM_CD             \n");
                            //sqlStr.append("     AND C.REPRE_YN = 'Y'             \n");
                            sqlStr.append("     AND D.ITEM_CD = ?            \n");
                        }
                     }
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    if(!params.get("corp_status").equals(""))
                    {
                        sqlStr.append("            AND JOB_DIV = ?                                      \n");
                     }
                    if(!params.get("corp_no").equals(""))
                    {
                        sqlStr.append("            AND A.CORP_REG_NO LIKE('%'|| ? ||'%')                                      \n");
                     }
                    if(!params.get("corp_name").equals(""))
                    {
                        sqlStr.append("            AND A.CORP_KOR LIKE ('%'|| ? ||'%')                                     \n");
                     }
                    
                    
                    
            sqlStr.append("         -- AND B.REPRE_YN = 'Y'            \n");
            sqlStr.append("        ORDER BY CORP_KOR ASC, CORP_ID DESC       \n");*/
            
            
            search.setStatement(sqlStr.toString());
            
            
            if(!params.get("material_Kind_Code_Lv1").equals(""))
            {
                search.addParameter(params.get("material_Kind_Code_Lv1"));
            }
                
            if(!params.get("material_Kind_Code_Lv2").equals(""))
            {
                search.addParameter(params.get("material_Kind_Code_Lv2"));
            }
            
            if(!params.get("material_Kind_Code_Lv3").equals(""))
            {
                search.addParameter(params.get("material_Kind_Code_Lv3"));
            }
            if(!params.get("corp_status").equals(""))
            {
                search.addParameter(params.get("corp_status"));
            }
            if(!params.get("corp_no").equals(""))
            {
                search.addParameter(params.get("corp_no"));
             }
            if(!params.get("corp_name").equals(""))
            {
            	if(params.get("search_gb").equals("1")){
            		search.addParameter(params.get("corp_name").trim().toUpperCase());  //2015.8.17 대소문자 
            		search.addParameter(params.get("corp_name").trim().toUpperCase());  //2015.8.17 대소문자 
            	} else {
            		search.addParameter(params.get("corp_name").trim().toUpperCase());  //2015.8.17 대소문자 
            	}            	
             }
            
            WAFLogger.debug(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;
        }
    }
	
	
	

	
	
	
	 // 자재분류 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;
    }
    
    
    
    /**
     * 자재분류 1레벨 코드 가져오기(외주)
     * @return
     */
    public RowSet getLv1MaterialKindCode_2() {
        Search search       = new SearchImpl();
        RowSet rowSet       = new RowSetImpl();
        StringBuffer sql    = new StringBuffer();
        
        /*
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      ITEM_CD  MATERIALGROUP               \n");
            sql.append("    , ITEM_NAME MATERIALGROUPNAME             \n");
            sql.append("    FROM OUT_ITEM          \n");
            sql.append("    WHERE DEPTH = '1'           \n");            
            sql.append("    ORDER BY ITEM_CD          \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);
            }
            sql.append("    order by materialgroup          \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_2(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("      ITEM_CD  MATERIALGROUP                 \n");
            sql.append("    , ITEM_NAME MATERIALGROUPNAME             \n");
            sql.append("    FROM OUT_ITEM          \n");
            sql.append("    WHERE DEPTH = '2'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND KIND_BIG = ?        \n");
                search.addParameter(Params);
            }
            sql.append("    ORDER BY ITEM_CD          \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;
    }
    
    
    
 // 자재분류 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());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
    
    
    
    // 자재분류 3레벨 코드 가져오기
    public RowSet getLv3MaterialKindCode_2(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("      ITEM_CD MATERIALGROUP                 \n");
            sql.append("    , ITEM_NAME MATERIALGROUPNAME             \n");
            sql.append("    FROM OUT_ITEM          \n");
            sql.append("    WHERE DEPTH = '3'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND KIND_BIG = ?        \n");
                search.addParameter(Params);
            }
            sql.append("    ORDER BY ITEM_CD          \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;
    }
	
}