/**
 * @(#)file    SearchBizRegNoBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.7.15
 * @(#)Description: 거래처 찾기 
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
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.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class SearchBizRegNoBiz extends Biz {

	/**
	 * 2018.11.19
	 * 이인오
	 * 조달 소스상 사용 안함 경영정보 디비링크 삭제
	private final String PUR_CORP_TABLE = "PUR9000T_V!CCSIF"; // 협력업체 마스터(구매)
	private final String PUR_BIZ_TABLE = "PUR9100T!CCSIF"; // 사업장 정보(구매) 
	private final String SUB_CORP_TABLE = "SUB9000T_V!CCSIF"; // 협력업체 마스터(공무)
	private final String SUB_BIZ_TABLE = "SUB9010T!CCSIF"; // 사업장 정보(공무)
	 */
	private final String PUR_CORP_TABLE = "PUR9000T_V"; // 협력업체 마스터(구매)
	private final String PUR_BIZ_TABLE = "PUR9100T"; // 사업장 정보(구매) 
	private final String SUB_CORP_TABLE = "SUB9000T_V"; // 협력업체 마스터(공무)
	private final String SUB_BIZ_TABLE = "SUB9010T"; // 사업장 정보(공무) 

	private String corpTBL ;
	private String bizTBL ;
	private String inConf ;
	private String gbConf ;
	private boolean allGb = false;
	private boolean forAll = false;

    public SearchBizRegNoBiz(HandlerStorage storage) {

        super(storage);     
        
        params = storage.getParams();

		if( params.get("site_gb").equals("PUR") ) {
			corpTBL = PUR_CORP_TABLE ;
			bizTBL = PUR_BIZ_TABLE ;
			inConf = " '2' " ;
			gbConf = " DECODE(reg_gb,'1','신청','2','등록','3','취소') as gb " ;
		} else if (params.get("site_gb").equals("SUB") ) {
			corpTBL = SUB_CORP_TABLE ;
			bizTBL = SUB_BIZ_TABLE ;
			inConf = " '2','3','4' " ;
			gbConf = " DECODE(reg_gb,'1','신청','2','정규','3','관리','4','계약','5','등록') as gb " ;
		} else {
        
		}

		if (params.get("all_gb").equals("1") )
			allGb = true ;
		
		if(params.get("forAll").equals("1"))
			forAll = true;
    }


    public void doBiz() throws Exception {

		/*
		 * 사업장 찾기
		 */	
		if ( params.get("reg_gb").equals("0")) {
			params.set("sheet1", getBizRegNo());
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));

		/*
		 * 사업자 찾기
		 */	
		} else if (params.get("reg_gb").equals("1")) {

			if ( params.get("work_id").equals("") ) {

                if(!params.get("desc").equals("")){
    				params.set("sheet1", getRegNo());
    				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
                }else{
                    storage.setSuccess(false);
                }
                
			/* 공종 사업자 찾기 */
			} else { 
				params.set("sheet1", getWorkRegNo());
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			}
		}
    }

    /**
     * 사업장 검색을 처리한다.
     * @param params
     * @return <code>RowSet</code>
     */
    private RowSet getBizRegNo() {

        Search search = new SearchImpl();
        String sqlstr = "";
        
       
        try {
            sqlstr = " SELECT   BIZ_REG_NO as REG_NO, BIZ_NAME as NAME, '등록' as gb , BIZ_ID as ID ,''" 
                   + " FROM  " +   bizTBL ;

            if ( !params.get("ent_corp_id").equals("")) {
                sqlstr += " WHERE CORP_ID  = '" + params.get("ent_corp_id") + "' ";     
            }

            WAFLogger.debug("############################## ------------->"+ params.get("ent_corp_id") + "^" + params.get("condition2") );

            if( !params.get("desc").equals("") ) {
                
                if ( !params.get("ent_corp_id").equals("")) {
                
                    if( params.get("condition2").equals("0") ) {
                        sqlstr += " AND      BIZ_NAME like '%" + params.get("desc") + "%' ";            
                    } else if (params.get("condition2").equals("1")) {
                        sqlstr += " AND      BIZ_REG_NO like '" + params.get("desc") + "%' ";
                    }
                }else{
                    if( params.get("condition2").equals("0") ) {
                        sqlstr += " WHERE    BIZ_NAME like '%" + params.get("desc") + "%' ";            
                    } else if (params.get("condition2").equals("1")) {
                        sqlstr += " WHERE   S BIZ_REG_NO like '" + params.get("desc") + "%' ";
                    }
                }
            }

            sqlstr += " ORDER BY BIZ_NAME "; 
            
            WAFLogger.debug(sqlstr);
            search.setStatement(sqlstr);
            return search.execute();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getBizRegNo() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }


	/**
	 * 사업자 검색을 처리한다.
	 * @param params
	 * @return <code>RowSet</code>
	 */
	private RowSet getRegNo() {

		Search search = new SearchImpl();
		String sqlstr = "";
        
       
		try {
			
			if ( allGb) {
				sqlstr = " SELECT    corp_reg_no as reg_no, GET_CORP_NM(corp_ID) as name, "
					   + gbConf 
					   + " ,corp_ID as id ,'' FROM     "+ corpTBL ;

					if( params.get("condition2").equals("0") ) {
						sqlstr += " WHERE      UPPER(CORP_KOR) like UPPER('%" + params.get("desc") + "%') ";            
					} else if (params.get("condition2").equals("1")) {
						sqlstr += " WHERE      UPPER(CORP_REG_NO) like UPPER('" + params.get("desc") + "%') ";
					}
			} else {
				sqlstr = " SELECT    corp_reg_no as reg_no, GET_CORP_NM(corp_ID) as name, "
						+ gbConf
					   	+ " ,corp_ID as id ,'' FROM     "+ corpTBL;
				
					if( params.get("condition2").equals("0") ) {
						sqlstr += " where      UPPER(CORP_KOR) like UPPER('%" + params.get("desc") + "%') ";            
					} else if (params.get("condition2").equals("1")) {
						sqlstr += " where      UPPER(CORP_REG_NO) like UPPER('" + params.get("desc") + "%') ";
					}
			}

			sqlstr += " ORDER BY CORP_KOR "; 
			WAFLogger.debug(sqlstr);
			search.setStatement(sqlstr);
			return search.execute();
		}
		catch(Exception e) {

			WAFLogger.error(this.getClass().getName() + " --> getRegNo() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return null;
		}
	}

	/**
	 * 공종 사업자 검색을 처리한다.
	 * @param params
	 * @return <code>RowSet</code>
	 */
	private RowSet getWorkRegNo() {

		Search search = new SearchImpl();
		String sqlstr = "";
        
       
		try {
			
			if ( allGb ) {
				sqlstr = " SELECT   A.CORP_REG_NO AS REG_NO , A.CORP_KOR AS NAME, \n" 
						+ gbConf
						+ " , A.CORP_ID AS ID, C.ACCT_ID AS ACCT_ID FROM SUB9000T A, SUB9150T B, SUB9140T C \n" 
						+ " WHERE A.CORP_ID = B.CORP_ID AND B.CHARGE_ID = C.CHARGE_ID AND B.WORK_ID=" +params.get("work_id") ;  
			} else {
				sqlstr = " SELECT   A.CORP_REG_NO AS REG_NO, A.CORP_KOR AS NAME,  \n" 
						+ gbConf
						+ " , A.CORP_ID AS ID, C.ACCT_ID AS ACCT_ID FROM SUB9000T A, SUB9150T B, SUB9140T C " 
						+ " WHERE A.CORP_ID = B.CORP_ID AND B.CHARGE_ID = C.CHARGE_ID AND A.REG_GB IN ("+ inConf +") AND B.WORK_ID=" +params.get("work_id") ;  
			}
			
			if( !params.get("desc").equals("") ) {
				if( params.get("condition2").equals("0") ) {
					sqlstr += " AND      A.CORP_KOR like '%" + params.get("desc") + "%' ";            
				} else if (params.get("condition2").equals("1")) {
					sqlstr += " AND      A.CORP_REG_NO like '" + params.get("desc") + "%' ";
				}
			}

			sqlstr += " ORDER BY CORP_KOR "; 
			WAFLogger.debug(sqlstr);
			search.setStatement(sqlstr);
			return search.execute();
		}
		catch(Exception e) {

			WAFLogger.error(this.getClass().getName() + " --> getWorkRegNo() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return null;
		}
	}




}

