/**
 * @(#)file    ItemSectListBiz.java
 * @(#)author  pej
 * @(#)version 1.0
 * @(#)date    2007.06-13
 * @(#)since   JDK1.4.1
 *
 * Copyright (c) 2002-2003 www.hwenc.com, Inc.  
 * All rights reserved.
 * This software is the proprietary information of hwenc, Inc.
 */
package kr.co.udapsoft.ebid.buyer.sycs.common.biz;

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.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.util.ValueObject;

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

public class CommMatlCodeTagListBiz extends Biz {

    public CommMatlCodeTagListBiz(HandlerStorage storage) {

        super(storage);        
    }
    public void doBiz() throws Exception {

        RowSet rowSet = getItemSectList(params);
        storage.addRowSet("rsSectList", rowSet);
        
    }
	
    
    
    public RowSet getItemSectList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";
        
        
        try {

            

        	
        	sqlstr += "    SELECT MATERIAL_ID, MATERIALGROUP, PARENT_LEVEL, REPLACE(MATERIALGROUPNAME, '\"', '''') as MATERIALGROUPNAME, MATERIALLEVEL, MATERIAL_GB, SPECCODE, CAL_TP_CODE		\n"
					+  	 "	            FROM(																																																	\n"              
					+  	 "	                    SELECT MATERIAL_ID, 																																										\n"
					+	 "								   MATERIALGROUP,																																									\n"                                                                                                
					+  	 "	                               PARENT_LEVEL,																																										\n"    
					+  	 "	                               MATERIALLEVEL,																																									\n"   
					+  	 "	                               MATERIALGROUPNAME,																																							\n" 
					+	 "								   MATERIAL_GB,																																										\n" 
					+	 "								   (TRIM(NVL(B.SPEC1,'SPEC1')) || '-' || TRIM(NVL(B.SPEC2,'SPEC2') )|| '-' || TRIM(NVL(B.SPEC3,'SPEC3')) || '-' || TRIM(NVL(B.SPEC4,'SPEC4'))) AS SPECCODE,   										\n"
					+	 "								   A.CAL_TP_CODE																																									\n"
					+  	 "	                           FROM PMM_MATERIALGROUP A, PMM_MATL_CAL_TYPE B																													\n"
					+  	 "	                           WHERE A.CAL_TP_CODE = B.CAL_TP_CODE (+)																																\n"
					+  	 "	                           AND MATERIAL_GB = 'P' 																																								\n"
					+  	 "	                    )                                                                                                                                                          											\n"  
					+  	 "	     START WITH PARENT_LEVEL = 'P'                                                                                                                              											\n"  
					+  	 "	         CONNECT BY PRIOR MATERIALGROUP = PARENT_LEVEL  																																		\n"  
					+  	 "	         AND LEVEL <= 3																																															\n"
					+  	 "	         ORDER SIBLINGS BY PARENT_LEVEL ASC, MATERIAL_ID ASC																																\n";

        	
            search.setStatement(sqlstr);
            rowSet = search.execute();
            WAFLogger.debug(sqlstr.toString());
        }
        catch (WAFSQLException se) {
        	WAFLogger.error(this.getClass().getName() + " --> getUserName() : \n" + se.getMessage());
        	WAFLogger.error("SQL : " + search.getStatement());
        	WAFLogger.error(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
	}
}
