/**
 * @(#)file    UserListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.6.22
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
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 UserListBiz extends Biz {

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

	
	public void doBiz() throws Exception {
		
		UserCommon common = new UserCommon();
		
		if(storage.compareEvent("")){
	        RowSet acctTypeRowSet = common.getAcctTypeUseRowSet(storage.getUser().get("ACCT_TYPE_CODE"));
	        storage.addRowSet("AcctTypeRowSet", acctTypeRowSet);
		}
		else {
			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                                                                                   			\n"
        			+ "         ,A.USER_PW                                                                                     			\n"
        			+ "         ,NVL(B.USER_NAME_K, USER_NAME) AS USER_NAME                                                      		\n"
        			+ "         ,DEPART_DEPT_NM AS BUSEO_NM  					\n"
        			+ "         ,(SELECT D.DEPT_NM  FROM ACZ10100V D  WHERE D.DEPT_CD = A.PSOSO) AS  Assigned_Dept        				\n"
        			+ "         ,A.JOB_DSCR            																					\n"
        			+ "         ,NVL(B.GRADE_NAME,A.GRADE_NAME) AS POSITION_NAME                                             			\n"
        			+ "         ,C.RIGHT_NAME									                                            			\n"
        			+ "         ,A.TEL_NO                                                                                      			\n"
        			+ "         ,A.HAND_TEL_NO                                                                                 			\n"
        			+ "         ,A.E_MAIL                                                                                      			\n"
        			+ "         ,TO_CHAR(TO_DATE(A.CRE_DATE,'YYYYMMDDHH24MI'),'YYYY-MM-DD') AS CRE_DATE						   			\n"
        			+ "         ,A.ACCT_ID                                                                                     			\n"
        			+ "         ,D.ACCT_TYPE_NAME                                                                              			\n"
        			+ " FROM	PUB0120T A                                                                                     			\n"
        			+ " 		,PUB0130V B                                                                                   			\n"
        			+ " 		,(SELECT MAX(ACCT_ID) AS ACCT_ID, MAX(B.DSCR) AS  RIGHT_NAME                             				\n"
        			+ " 		FROM PUB0350T A, PUB0340T B                                                                   			\n"
        			+ " 		WHERE A.RIGHT_ID =  B.RIGHT_ID                                                                			\n"
        			+ " 		GROUP BY ACCT_ID			                                                                   			\n"
        			+ " 		) C		 					                                                                   			\n"
        			+ " 		,PUB0110T D		 					                                                           			\n"
        			+ " WHERE	A.USER_SABUN = B.USER_SABUN(+)                                                                  		\n"
        			+ " AND  	A.ACCT_ID = C.ACCT_ID(+)                                                           						\n"
        			+ " AND  	A.ACCT_TYPE_ID = D.ACCT_TYPE_ID(+)                                                 						\n";
 
     	
           	if( !params.get("p_user_sabun").equals("") ) {
           		sqlstr += "    AND  A.USER_SABUN = '" + params.get("p_user_sabun") + "' \n";
          	}

          	if( !params.get("p_user_name").equals("") ) {
          		sqlstr += "    AND  NVL(B.USER_NAME_K, USER_NAME) LIKE '%" + params.get("p_user_name") + "%' \n";
          	}
          	
          	if( !params.get("dept_cd2").equals("") ) {
          		//sqlstr += "    AND  B.PSOSO = '" + params.get("dept_cd2") + "' \n"
          		sqlstr += " AND     B.PSOSO = ?                                                      			\n";
    			search.addParameter(params.get("dept_cd2"));
          	}

          	if( !params.get("p_dept_cd").equals("") ) {
          		sqlstr += "    AND  a.depart_dept_cd = '" + params.get("p_dept_cd") + "' \n";
          	}
          	
          	if( !params.get("p_acct_type_id").equals("") ) {
          		sqlstr += " AND      A.ACCT_TYPE_ID = '" + params.get("p_acct_type_id") + "'  \n" ;
          	}
             
          	if( params.get("p_status").equals("Y")){
          		sqlstr += " AND      NVL(A.DUTYC,B.DUTYC) = '99'  \n" ;
          	}else if(params.get("p_status").equals("N")){
          		sqlstr += " AND      NVL(A.DUTYC,B.DUTYC) != '99'  \n" ;
          	}
            sqlstr += " ORDER BY A.ACCT_ID DESC 							\n" ; 
            	
            WAFLogger.debug(sqlstr.toString());
        	search.setStatement(sqlstr);
            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;
        }
	}
	
}