/**
 * @(#)file    UserListBiz.java
 * @(#)author  Joung Kyu Park
 * @(#)version 1.0
 * @(#)date    May 31, 2007
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.hwenc.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of Hanwha E&C, Inc.
 */
package kr.co.udapsoft.ebid.buyer.test.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;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;






public class TestList2Biz extends Biz {

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

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

		
//		TestCommon common = new TestCommon();
		
		if(storage.compareEvent("")){
//	        RowSet acctTypeRowSet = common.getAcctTypeUseRowSet(storage.getUser().get("ACCT_TYPE_CODE"));
//	        storage.addRowSet("AcctTypeRowSet", acctTypeRowSet);
			
//			ValueObject form_data = get_form_data();
//			System.out.println(form_data.get("DEPT"));
//			System.out.println(form_data.get("TYPE"));
//			
//			storage.addValueObject("formData", form_data);
			
			RowSet materialKindCodeLv1 = getLv1MaterialKindCode();
			storage.addRowSet("materialKindCodeLv1", materialKindCodeLv1);
			
			System.out.println("materialKindCodeLv1===>" + materialKindCodeLv1);
			
			
		}
		else if(storage.compareEvent("GET_MATL_KIND_CODE_LV2"))
        {
		    //ValueObject vo = getMaterialKindCode();
	        //storage.addValueObject("VALUEOBJECT", vo);
		    
		    RowSet materialKindCodeLv2 = getLv2MaterialKindCode(params.get("materialgroup"));
            storage.addRowSet( "ROWSET", materialKindCodeLv2 );
	        storage.setSuccess(true);
        }
		else if(storage.compareEvent("GET_MATL_KIND_CODE_LV3"))
        {
		    /*
            ValueObject vo = getMaterialKindCode();
            
            storage.addValueObject("VALUEOBJECT", vo);
            */
		    
		    RowSet materialKindCodeLv3 = getLv3MaterialKindCode(params.get("materialgroup"));
		    storage.addRowSet( "ROWSET", materialKindCodeLv3 );
            storage.setSuccess(true);
        }
		else if(storage.compareEvent("GET_LIST"))
		{
			
				
			RowSet rowset = getList(params);
			
			Document xmlData = getXml(rowset);
			
			XMLOutputter xmlOutputter = new XMLOutputter();
		    xmlOutputter.setFormat(Format.getRawFormat());
		    
		    System.out.println(xmlOutputter.outputString(xmlData));
			
			storage.addObject("ibsheet7Data", xmlData);
			
//			params.set("sheet1", getList(params));
//            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
			
		}
		else if(storage.compareEvent("SAVE_LIST"))
		{
			boolean success = false;
			String[] status = super.gets("sheet1.STATUS");
			
			try {
				super.conn.begin();
				
				
				success = deleteForm();
				success = saveForm(params);
				
				
				for (int i = 0; i < status.length; i++) {
					
					if (IBSheet7Utils.equalStatusInsert(status[i])) {
						
//						success = saveForm(params);
								
						success = saveData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
					/*
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
//						success = updateData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
					
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
//						success = deleteData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
					*/
				}
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}
		else if(storage.compareEvent("SAVE_FORM"))
		{
			boolean success = false;
			
			try 
			{
				super.conn.begin();
				
				System.out.println("params====>" + params);
				
				success = saveForm(params);
				super.storage.setSuccess(success);
				
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}	
		}
			
	}
	
	/*
	public ValueObject getMaterialKindCode(){
        ValueObject vo            = new ValueObject();
        String temp               = params.get("materiallevel");
        InitCombo initCombo       = new InitCombo();
        RowSet materialKindCodeLv = null;
        try {
            if ( "2".equals(temp) ) {
                materialKindCodeLv = getLv2MaterialKindCode(params.get("materialgroup"));
            } else if ( "3".equals(temp) ) {
                materialKindCodeLv = getLv3MaterialKindCode(params.get("materialgroup"));
            }
            
            initCombo.makeComboData(materialKindCodeLv, "materialgroupname", "materialgroup");
            
            vo.set("materialgroup", initCombo.getCode());
            vo.set("materialgroupname", initCombo.getCodeName());
            
            //storage.addRowSet("materialKindCodeLv", materialKindCodeLv);
        } catch(Exception e) {
            
        } finally {
            return vo;
        }
    }
	*/
	
	
	
	public Document getXml(RowSet rowset)
	{
		
		  Document doc = new Document();  
			
		  Element root = new Element("Grid");
		  
		  Element pack = new Element("DATA");
		  
		  Element pack_tr = new Element("TR");
		  
		  Element pack_td = new Element("TD");
		  
		try {

			  root.addContent(pack);//root element 의 하위 element 를 만들기
			  pack.addContent(pack_tr);
			  pack_tr.addContent(pack_td);
			  
			  for(int i=0; i < rowset.size(); i++)
			  {
				  if( i != 0)
				  {
					  pack_tr = new Element("TR");
					  pack.addContent(pack_tr);
				  }
				  
				  pack_tr.setAttribute("LEVEL", rowset.getRow(i).get("MATERIAL_LEVEL"));
				  
				  if( i != 0)
				  {
					  pack_td = new Element("TD");
					  pack_tr.addContent(pack_td);
				  }
				 
				  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("MATERIALGROUPNAME") + "]]>");
				  
				  pack_td = new Element("TD");
				  pack_tr.addContent(pack_td);
				  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("GUBUN_1") + "]]>");
				  
				  pack_td = new Element("TD");
				  pack_tr.addContent(pack_td);
				  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("GUBUN_2") + "]]>");
				  
				  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("GUBUN_3") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("MATERIAL_LEVEL") + "]]>");
                  
				  
//				  System.out.println(rowset.getRow(i).get("GUBUN_1") + "/" + rowset.getRow(i).get("GUBUN_2") + "/" + rowset.getRow(i).get("GUBUN_3"));
			  }
			  
			  doc.setRootElement(root);
			  
			  
			      
			  } catch (Exception e) {                                         
			      System.err.println(e);                                        
			  }
		return doc;
		
	}
	

	/**
	 * 사용자 목록을 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
        	sqlStr.append("	    SELECT 																													\n")
					.append("        MATERIAL_LEVEL,    MATERIALGROUPNAME  , 																\n")
					.append("        LAST_VALUE(GUBUN_1 IGNORE NULLS) OVER(ORDER BY MATERIALGROUP) GUBUN_1,			\n")
					.append("        LAST_VALUE(GUBUN_2 IGNORE NULLS) OVER(ORDER BY MATERIALGROUP) GUBUN_2,			\n")
					.append("        LAST_VALUE(GUBUN_3 IGNORE NULLS) OVER(ORDER BY MATERIALGROUP) GUBUN_3 			\n")
					.append("        FROM																													\n")
					.append("        (																															\n")
					.append("            SELECT LEVEL-1 MATERIAL_LEVEL,    MATERIALGROUPNAME  , MATERIALGROUP,				\n")  
					.append("                        DECODE(LEVEL, '1', MATERIALGROUPNAME, '') GUBUN_1,									\n")
					.append("                        DECODE(LEVEL, '2', MATERIALGROUPNAME, '') GUBUN_2,									\n")
					.append("                        DECODE(LEVEL, '3', MATERIALGROUPNAME, '') GUBUN_3									\n")
					.append("                        FROM(                                                                                                  \n")                                                                                           
					.append("                                SELECT MATERIAL_ID,                                                                     \n")                                                       
					.append("                                           MATERIALGROUP,                                                                 \n")                      
					.append("                                           PARENT_LEVEL,                                                                   \n")
					.append("                                           MATERIALLEVEL,                                                                  \n")
					.append("                                           MATERIALGROUPNAME,                                                          \n")
					.append("                                           MATERIAL_GB                                                                      \n")
					.append("                                       FROM PMM_MATERIALGROUP                                                      \n")
					.append("                                       WHERE MATERIAL_GB = 'C'                                                         \n")
					.append("                                )                                                                                                  \n")
					.append("                 START WITH PARENT_LEVEL = 'C'                                                                      \n")
					.append("                     CONNECT BY PRIOR MATERIALGROUP = PARENT_LEVEL                                      \n")
					.append("                     AND LEVEL <= 3                                                                                         \n")
					.append("                     ORDER SIBLINGS BY PARENT_LEVEL ASC, MATERIALGROUP ASC							\n")
					.append("            )              																										\n")
					.append("            UNION ALL																											\n")
					.append("                SELECT 																											\n")
					.append("        MATERIAL_LEVEL,    MATERIALGROUPNAME,																	\n") 
					.append("        LAST_VALUE(GUBUN_1 IGNORE NULLS) OVER(ORDER BY MATERIALGROUP) GUBUN_1,			\n")
					.append("        LAST_VALUE(GUBUN_2 IGNORE NULLS) OVER(ORDER BY MATERIALGROUP) GUBUN_2,			\n")
					.append("        LAST_VALUE(GUBUN_3 IGNORE NULLS) OVER(ORDER BY MATERIALGROUP) GUBUN_3				\n")
					.append("        FROM																													\n")
					.append("        (																															\n")
					.append("            SELECT LEVEL-1 MATERIAL_LEVEL,    MATERIALGROUPNAME  , MATERIALGROUP,				\n")  
					.append("                        DECODE(LEVEL, '1', MATERIALGROUPNAME, '') GUBUN_1,									\n")
					.append("                        DECODE(LEVEL, '2', MATERIALGROUPNAME, '') GUBUN_2,									\n")
					.append("                        DECODE(LEVEL, '3', MATERIALGROUPNAME, '') GUBUN_3									\n")
					.append("                        FROM(                                                                                                   \n")                                                                                           
					.append("                                SELECT MATERIAL_ID,                                                                      \n")                                                       
					.append("                                           MATERIALGROUP,                                                                  \n")                      
					.append("                                           PARENT_LEVEL,                                                                    \n")
					.append("                                           MATERIALLEVEL,                                                                   \n")
					.append("                                           MATERIALGROUPNAME,                                                           \n")
					.append("                                           MATERIAL_GB                                                                       \n")
					.append("                                       FROM PMM_MATERIALGROUP                                                       \n")
					.append("                                       WHERE MATERIAL_GB = 'P'                                                          \n")
					.append("                                )                                                                                                   \n")
					.append("                 START WITH PARENT_LEVEL = 'P'                                                                       \n")
					.append("                     CONNECT BY PRIOR MATERIALGROUP = PARENT_LEVEL                                       \n")
					.append("                     AND LEVEL <= 3                                                                                          \n")
					.append("                     ORDER SIBLINGS BY PARENT_LEVEL ASC, MATERIALGROUP ASC							 \n")
					.append("            )                         																								 \n");
        	
            	
        	//Paging 처리를 위한 Helper Object 생성
            super.createPageAnchorInfo();
            //한페이지에 가져올 목록의 갯수 지정(미지정시  waf.properties 파일의 기본값을 사용)
            super.anchor.range = 10000;
            //Paging 처리에 사용될 Parameter를  세팅
            super.anchor.setParams(this.params);
            //검색할  SQL 세팅 : SQL은 Paging처리를 하지 않고 작성
            super.anchor.setSQL(sqlStr.toString());
            //전체 Record 숫자를 조회
            super.anchor.doTotalCountSQL(); // 전체 숫자를 조회
        	
            //Paging처리가 된 SQL을 Helper Object에서 가져옴
            String temp = super.anchor.getPagingSQL();
            
        	search.setStatement(temp);
        	System.out.println(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;
        }
	}
	
	
	/**
	 * Persistent 를 이용한 Save 처리
	 * @param params
	 * @return
	 * * 입력받은 프로그램 등록 정보를 등록합니다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean saveData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		System.out.println("sqlParams===>"+  sqlParams.get("TITLE"));
		
		try {
			StringBuffer sql = new StringBuffer();
			
			sql.append("	INSERT INTO TEST_TABLE			\n ");
			sql.append("	(                   						\n ");
			sql.append("		  SEQ            						\n ");
			sql.append("		, TITLE        						\n ");
			sql.append("		, CREATE_DATE					\n ");
			sql.append("		) VALUES (      					\n ");
			sql.append("		 (SELECT MAX(SEQ)+1 FROM TEST_TABLE)                						\n ");
			sql.append("		,?                						\n ");
			sql.append("		,TO_CHAR(SYSDATE, 'YYYYMMDD'),         			\n ");
			sql.append("		)                 						\n ");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("TITLE"));
			
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	private boolean saveForm(ValueObject params) throws Exception
	{
		
		Persistent persistent = null;
		StringBuffer sqlstr = new StringBuffer();
		
		
		try{
			
			conn.begin();
			persistent = new PersistentImpl(super.conn.getConnection());
			
			
			sqlstr.append("	INSERT INTO TEST_MAIN_TABLE (	\n")
					.append("	DEPT, TYPE )VALUES( ?, ?)	\n");
			
			
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(params.get("dept"));
			persistent.addParameter(params.get("type"));
			
			WAFLogger.debug(persistent.getParameters());
			persistent.execute();
			persistent.clearParameters();
			
			return true;
			
			
		} catch(Exception e) {
//			e.printStackTrace();
			WAFLogger.error(this.getClass().getName() + " --> saveData() : " + e.getMessage());
			WAFLogger.error("SQL : " + persistent.toString());
			WAFLogger.error(e);
			conn.setRollback();
			throw e;
            
        }
		
        finally {
        	super.conn.end();
        }
	}
	
	private boolean deleteForm() throws Exception
	{
		
		Persistent persistent = null;
		StringBuffer sqlstr = new StringBuffer();
		
		
		try{
			
			conn.begin();
			persistent = new PersistentImpl(super.conn.getConnection());
			
			
			sqlstr.append("	DELETE FROM TEST_MAIN_TABLE (	\n");
			
			
			persistent.setStatement(sqlstr.toString());
			
			WAFLogger.debug(persistent.getParameters());
			persistent.execute();
			persistent.clearParameters();
			
			return true;
			
			
		} catch(Exception e) {
//			e.printStackTrace();
			WAFLogger.error(this.getClass().getName() + " --> saveData() : " + e.getMessage());
			WAFLogger.error("SQL : " + persistent.toString());
			WAFLogger.error(e);
			conn.setRollback();
			throw e;
            
        }
		
        finally {
        	super.conn.end();
        }
	}
	
	
	public ValueObject get_form_data() throws Exception {
		Search search = new SearchImpl();
		ValueObject vo = null;
		RowSet rowSet = new RowSetImpl();
		
        StringBuffer sql = new StringBuffer();
        
        try {
        	sql.append("	SELECT DEPT, TYPE				\n")
    			.append("		 FROM TEST_MAIN_TABLE			\n");
            
        	search.setStatement(sql.toString());
			rowSet = search.execute();
			
			if(rowSet.size()>0){
				vo = RowSetUtility.getValueObject(rowSet.getRow(0));
			}
			
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getUserName() : \n" + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
		}finally{
			if( vo == null) vo = new ValueObject();
			return vo;
		}
    }
	
	
	// 자재분류 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  MATERIAL_ID             \n");
            sql.append("    , MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUP || ' : ' || MATERIALGROUPNAME MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP          \n");
            sql.append("    WHERE MATERIALLEVEL = '1'           \n");            
            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(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  MATERIAL_ID             \n");
            sql.append("    , MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP          \n");
            sql.append("    WHERE MATERIALLEVEL = '2'           \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;
    }
    
    // 자재분류 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  MATERIAL_ID             \n");
            sql.append("    , MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP          \n");
            sql.append("    WHERE MATERIALLEVEL = '3'           \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;
    }
	
}