/**
 * @(#)file    SearchUserBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.7.2
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.buyer.sycs.user.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 SearchUserBiz extends Biz {

	public SearchUserBiz(HandlerStorage storage) {
		super(storage);
	}
 
    public void doBiz() throws Exception {
    	params.set("sheet1", getUserList(params));
		storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
    }

    
    private RowSet  getUserList(ValueObject Params)  {

        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";  

        try {
            sqlstr += " SELECT A.USER_SABUN AS USER_SABUN,                    \n"
	                + "        A.USER_NAME AS USER_NAME,	                  \n"
	                + "        A.PSOSO AS DEPT_CD,		                      \n"
	                + "        B.DEPT_NM AS SITE_NAME,		 	              \n"
	                + "        A.GRADE_CODE AS POSITION_CODE,	              \n"
	                + "        A.GRADE_NAME AS POSITION_NAME,	              \n"
	                + "        A.TEL_NO AS TEL_NO,                     		  \n"
	                + "        A.HAND_TEL_NO AS HAND_TEL_NO,	              \n"                
	                + "        A.JUMIN_NO AS JUMIN,                     	  \n"
	                + "        A.ACCT_ID AS ACCT_ID,                     	  \n"
	                + "        A.DUTYC AS DUTYC,                 			  \n"
	                + "        B.DEPT_INI,								  	  \n"
	                + "        '1' AS ISCHECK,								  \n"
	                + "        A.E_MAIL,									  \n"
	                + "        A.BUSEO AS 	DEPT_CODE,						  \n"
	                + "        (SELECT C.DEPT_NM FROM ETEC_ERPIF.ACZ10101 C WHERE C.ACC_DEPT_CD =A.BUSEO) AS DEPT_NAME		  \n"
	                + " FROM PUB0120T A, ACZ10100V B					      \n"
	                + " WHERE   1=1                         				  \n"
	                + " AND B.DEPT_CD(+)     = A.PSOSO							  				\n";
	                if(!params.get("user_name").equals("")){
	                	sqlstr += " AND A.USER_NAME LIKE '%"+ params.get("user_name")  +"%'         \n";
	                }
	                if(!params.get("user_sabun").equals("")){
	                	sqlstr += " AND A.USER_SABUN LIKE '%"+ params.get("user_sabun") +"%'        \n";
	                }
	                sqlstr += " UNION                            								\n" 
	                		+ " SELECT  A.USER_SABUN,        			  			\n"	
	                		+ "       	A.USER_NAME,          			  			\n"	
	                		+ "         A.BUSEO   AS DEPT_CD,          			  			\n"	
	                		+ "        	C.DEPT_NM     AS SITE_NAME,        			  			\n"	
	                		+ "         A.POSITIONS_CODE AS POSITION_CODE,    			  			\n"	
	                		+ "         A.POSITIONS_NAME AS POSITION_NAME,    			  			\n"	
	                		+ "         A.TEL_NO,         			  			\n"	
	                		+ "         A.HAND_TEL_NO,							\n"	
	                		+ "         A.JUMIN_NO   AS  JUMIN,           			  			\n"	
	                		+ "         (SELECT ACCT_ID  FROM PUB0120T WHERE  USER_SABUN = A.USER_SABUN	AND  ROWNUM  = 1) ACCT_ID,         			  			\n"	
	                		+ "         A.DUTYC,	              			  			\n"	
	                		+ "         C.DEPT_INI,									  			\n"	
	                		+ "         TO_CHAR((SELECT COUNT(1) CNT FROM PUB0120T WHERE USER_SABUN=A.USER_SABUN)) ISCHECK,				   			  			\n"	
	                		+ "         '' AS E_MAIL,			           			  			\n"	
	                		+ "         A.BUSEO AS DEPT_CODE,          			  			\n"	
	                		+ "         (SELECT C.DEPT_NM FROM ETEC_ERPIF.ACZ10101 C WHERE C.ACC_DEPT_CD =A.BUSEO) AS DEPT_NAME             			  			\n"	
	                		+ "  FROM     PUB0130T A, ACZ10100V C	       			  			\n"	
	                		+ "  WHERE   1=1			                 			  			\n"	
	                		+ "  AND      C.DEPT_CD     = A.BUSEO      			  			\n"	
	                		+ "  AND      A.DUTYC  != '99'		      			  			\n";
	    	                if(!params.get("user_name").equals("")){
	    		                sqlstr += " AND      USER_NAME LIKE '%"+ params.get("user_name")  +"%'         \n";
	    		                }
	    		                if(!params.get("user_sabun").equals("")){
	    		                sqlstr += " AND      USER_SABUN LIKE UPPER('%"+ params.get("user_sabun") +"%')         \n";
	    		                }
	    		                sqlstr += " ORDER BY USER_NAME                            \n" ;         

            
	        WAFLogger.debug(sqlstr.toString());
            search.setStatement(sqlstr);
            rowSet = search.execute();
            return rowSet;
        }
        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;
        }
    }
}

