/**
 *
 * @(#)author  [ok]
 * @(#)version 1.0
 * @(#)date    2014. 06. 25
 * @(#)since   JDK 1.7.2
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of udapsoft, Inc.
 */

package kr.co.udapsoft.ebid.buyer.common.lib.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.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;


public class SearchZipCodeBiz extends Biz {

	public SearchZipCodeBiz(HandlerStorage storage) {
		super(storage);
	}
 
    public void doBiz() throws Exception {
    	
    	WAFLogger.debug("우편번호");
    	
    	if(storage.compareEvent(""))
    	{
    	    RowSet sido = getSido(params);
    	    storage.addRowSet("sidoList", sido);
    	}
    	else if(storage.compareEvent("GET_ZIP_LIST"))
    	{
			params.set("sheet1",  getZipCode(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
    	}
    }
    
    
    private RowSet getSido(ValueObject Params)
    {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        
        try {
            
            sql.append(" SELECT DISTINCT SIDO FROM ZIPCODE ORDER BY SIDO ASC   \n");
            search.setStatement(sql.toString());
            rowSet = search.execute();
            
        } catch (Exception se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
        
    }
    

    /**
     * 우편번호 조회
     * @param params
     * @return <code>RowSet</code>
     */
    private RowSet getZipCode(ValueObject Params) {

         Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();

        try {
	        if("g".equals(Params.getObject("addDiv"))){
	        	
				sql.append(" SELECT SUBSTR(ZIPCODE,1,3) ||'-'|| SUBSTR(ZIPCODE,4,3) AS ZIP,							     																													\n");
				sql.append(" SIDO || ' ' || GUGUN || ' ' || MYUNDONG || DECODE(RI, NULL, '', ' ' || RI ||' ') || DECODE(BUILDINGNAME, NULL, '', ' ' || BUILDINGNAME ||' ')  AS ADDR,	    												\n");
				sql.append(" SEARCHADDR AS OLDADDR             									 																																			\n");
				sql.append(" FROM ZIPCODE              									 																																					\n");
				sql.append(" WHERE SIDO='"+params.get("SIDO")+"' AND (MYUNDONG LIKE '%'|| '"+params.get("Field2")+"' ||'%' OR RI LIKE '%'|| '"+params.get("Field2")+"' ||'%' OR BUILDINGNAME LIKE '%'|| '"+params.get("Field2")+"' ||'%')	\n");
	        	search.setStatement(sql.toString());
	            rowSet = search.execute();

	        }else{
	        	
				sql.append(" SELECT SUBSTR(ZIPCODE,1,3) ||'-'|| SUBSTR(ZIPCODE,4,3) AS ZIP,							     																																													\n");
				sql.append(" 	    SIDO || ' ' || GUGUN || DECODE(RI, NULL, ' ', ' ' || MYUNDONG ||' ') || ROADNAME || ' ' || BUILDINGH || DECODE(BUILDINGS, 0, '', '-' || BUILDINGS ||' ') || DECODE(BUILDINGNAME, NULL, '', ' (' || BUILDINGNAME ||')')  AS ADDR,										\n");
				sql.append("        SIDO || ' ' || GUGUN || ' ' || MYUNDONG || DECODE(RI, NULL, ' ', ' ' || RI ||' ') || JIBUNH || DECODE(JIBUNS, 0, '', '-' || JIBUNS ||' ') || DECODE(BUILDINGNAME, NULL, '', ' ' || BUILDINGNAME ||' ')  AS OLDADDR														\n");
				sql.append("   FROM ZIPCODE_ROAD A              																																																											\n");
				sql.append("  WHERE SIDO='"+params.get("SIDO")+"' 																																																											\n");
				sql.append("    AND (SIDO || ' ' || GUGUN || DECODE(RI, NULL, ' ', ' ' || MYUNDONG ||' ') || ROADNAME || ' ' || BUILDINGH || DECODE(BUILDINGH, 0, '', '-' || BUILDINGS ||' ') || DECODE(BUILDINGNAME, NULL, '', ' (' || BUILDINGNAME ||')')) LIKE '%'|| '"+params.get("Field2")+"' ||'%'	\n");
	        	search.setStatement(sql.toString());
	            rowSet = search.execute();
	        }

        }
        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();
            return rowSet;
        }
    }
}
