/**
 * @(#)file    ExceptionalUserListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.6.22
 */
package kr.co.udapsoft.ebid.buyer.sycs.security.biz;

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 ExceptionalUserListBiz extends Biz {

	public ExceptionalUserListBiz(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                                                                                   \n"
                + "          A.USER_SABUN SABUN, A.USER_NAME NAME,                                               \n"
                + "          A.JUMIN_NO JUMIN, A.DUTYC,                                                   \n"
                + "          A.POSITIONS_CODE P_CODE,    				                                               \n"
                + "          C.DEPT_NM DEPT_NAME, 	                      			                                   \n"
                + "          B.DEPT_NM, A.TEL_NO TEL, A.HAND_TEL_NO HPTEL, A.PWD_VALID_FROM ENABLEFROM, A.PWD_VALID_TO ENABLETO, A.BUSEO, \n"
                + "          A.BUSEO REAL_DEPT_CD,                                                         \n"
                + "          POSITIONS_NAME,                      			                                   \n"
                + "          A.DEPT_CODE D_CODE,		                      			                               \n"
                + "          CASE WHEN LENGTH(JUMIN_NO) > 6 THEN                                              \n"
                + "                 SUBSTR(JUMIN_NO,0,6)                                                      \n"
                + "               ELSE                                                                         \n"
                + "                 JUMIN_NO                                                                  \n"
                + "          END JUMIN1                                                                        \n"
                + "          ,                                                                                 \n"
                + "          CASE WHEN LENGTH(JUMIN_NO) > 6 THEN                                              \n"
                + "                 SUBSTR(JUMIN_NO,7,7)                                                      \n"
                + "               ELSE                                                                         \n"
                + "                 ''                                                                         \n"
                + "          END JUMIN2,                                                                        \n"
                + "          A.SEQ                                                                        \n"
                + "     FROM PUB0130T A, ACZ10100V B,  ZEHR_COM_DEPT@HRIF C                                         \n"  //b : 현장 C;본사 ETEC_ERPIF.ACZ10101
                + "    WHERE A.BUSEO = B.DEPT_CD(+)                                                           \n"
                + "      AND A.DEPT_CODE = C.DEPT_HR_CD          					                       \n"
                + "      AND C.END_DATE IS NULL         					                       \n"
                + "      AND A.USER_SABUN     LIKE     '%"+ params.get("p_sabun") +"%'                          \n"
                + "      AND A.USER_NAME      LIKE     '%"+ params.get("p_name") +"%'                           \n"
                + "      AND C.DEPT_HR_CD     LIKE     '%"+ params.get("p_dept_cd") +"%'                        \n"
                + "    ORDER BY A.USER_NAME                                                                     \n" ; 
            
        	search.setStatement(sqlstr);
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }catch (Exception e){
        	 super.doException(e);
             this.storage.setSuccess(false);
             this.storage.setMessage(e.getMessage());
             storage.setDetailMessage(e);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
	}
}
