/**
 * @(#)file    SignOnFilter.java
 * @(#)author  chj
 * @(#)version 1.0
 * @(#)date    2016. 6. 24
 * @(#)since   JDK 1.6
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package com.udapsoft.waf.system.filter;

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.StringTokenizer;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import kr.co.hsnc.common.base.WAFException;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
import kr.co.hsnc.common.event.FileListener;
import kr.co.hsnc.common.event.FileMonitor;
import kr.co.hsnc.common.logger.Logger;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.StringUtil;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.hsnc.j2ee.components.signon.web.SignOnDAO;
import kr.co.hsnc.j2ee.waf.controller.web.util.WebKeys;
import kr.co.hsnc.j2ee.waf.view.template.Screen;
import kr.co.hsnc.j2ee.waf.view.template.ScreenDefinitionDAO;
import kr.co.hsnc.j2ee.waf.view.template.Screens;

import com.udapsoft.waf.common.util.DateUtil;
import com.udapsoft.waf.common.util.IDEnDecoder;
import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.ParameterParser;
import com.udapsoft.waf.system.session.SignOnSession;

/**
 * 
 * @version 1.0
 */
public class SignOnFilter implements Filter, FileListener {

    public static final String FORM_SIGNON_URL = "/sign_on_check";
    public static final String FORM_USER_NAME = "login_id";
    public static final String FORM_PASSWORD = "login_pw";
    public static final String FORM_USER_TYPE = "j_usertype";

    public static final String FORM_SIGNON_PORTAL_URL = "/sign_on_portal_check";

    public static final String SLO_URL = "/slo_process";
    public static final String GATE_URL = "/gate";

    public static final String FORM_ADMIN_LOGIN_GB = "j_admin_login_gb";	// 관리자가 개인정보를 직접 접근할 수 없도록 하는 Flag

    public static final String USER_NAME = "j_signon_username";
    public static final String SIGNED_ON_USER  = "j_signon";
    public static final String ORIGINAL_URL = "j_signon_original_url";

    //화면 자동 이동
    public static final String SCREEN_NAME = "j_screenname";
    public static final String PATH1 = "j_path1";
    public static final String PATH2 = "j_path2";
    public static final String AUTO_YN = "j_auto_yn";
    public static final String MENU = "j_menu";
    
    private HashMap protectedResources;
    private HashMap allScreens;
    private FilterConfig config;
    private String defaultLocale;
    private String signOnErrorPage;
    private String signOnPage;
    private String aclErrorPage;

    // FileChangeListener Variables
    private long        interval = 2000L;
    private SignOnDAO   dao;
    private FileMonitor monitor;

    public void init(FilterConfig config) throws ServletException {
        this.config = config;
        URL protectedResourcesURL = null;
        String protectedResourcesRealPath = null;
        try {
            protectedResourcesURL = config.getServletContext().getResource("/WEB-INF/signon-config.xml");
            protectedResourcesRealPath = config.getServletContext().getRealPath("/WEB-INF/signon-config.xml");

            this.dao = new SignOnDAO(protectedResourcesURL);
            signOnPage             = this.dao.getSignOnPage();
            signOnErrorPage        = this.dao.getSignOnErrorPage();
            aclErrorPage           = this.dao.getAclErrorPage();

            protectedResources = this.dao.getProtectedResources();

            this.monitor = FileMonitor.getInstance();
            this.monitor.addFileListener(this, protectedResourcesRealPath, this.interval);


            allScreens = new HashMap();
            defaultLocale = config.getInitParameter("default_locale");
            if (defaultLocale == null) {
                defaultLocale = (Locale.getDefault()).toString();
            }
            String locales = config.getInitParameter("locales");

            if (locales != null) {
                StringTokenizer strTok = new StringTokenizer(locales, ",");
                while (strTok.hasMoreTokens()) {
                    initScreens(config.getServletContext(), strTok.nextToken());
                }
            }

            /*
            URL screenDefinitionURL = config.getServletContext().getResource("/WEB-INF/screendefinitions_ko_KR.xml");

            if( screenDefinitionURL != null) {
                this.monitor.addFileListener(this, screenDefinitionURL.getFile(), this.interval);
                screenDefinitions = ScreenDefinitionDAO.loadScreenDefinitions(screenDefinitionURL);
                if (screenDefinitions != null) {
                    Logger.sys.println("SignOnFilter.init(); screenDefinitions load...");
                }
                else {
                    Logger.sys.println("Template Servlet Error Loading Screen Definitions: Confirm that file at URL /WEB-INF/screendefinitions_ko_KR.xml contains the screen definitions");
                }
            }
            */
        }
        catch(java.net.MalformedURLException ex) {
            Logger.err.println("SignonFilter: malformed URL exception: " + ex);
        }
        catch(java.io.IOException ex) {
            Logger.err.println("SignonFilter: IOException: " + ex);
        }
    }

    /**
     * @param context
     * @param language
     */
    private synchronized void initScreens(ServletContext context, String language) {

        URL screenDefinitionURL = null;
        String screenDefinitionRealPath = null;
        try {
            screenDefinitionURL = context.getResource("/WEB-INF/screendefinitions_" + language + ".xml");
            screenDefinitionRealPath = context.getRealPath("/WEB-INF/screendefinitions_" + language + ".xml");
            this.monitor.addFileListener(this, screenDefinitionRealPath, this.interval);
        }
        catch (java.net.MalformedURLException ex) {
            WAFLogger.sys("FrontController.initScreens(): malformed URL exception: " + ex);
        }
        catch (java.io.IOException ex) {
            WAFLogger.sys("FrontController.initScreens(): IOException: " + ex);
        }
        if (screenDefinitionURL != null) {
            Screens screenDefinitions = ScreenDefinitionDAO.loadScreenDefinitions(screenDefinitionURL);
            if (screenDefinitions != null) {
                allScreens.put(language, screenDefinitions);
            }
            else {
                WAFLogger.sys("Template Servlet Error Loading Screen Definitions: Confirm that file at URL /WEB-INF/screendefinitions_" + language + ".xml contains the screen definitions");
            }
        }
        else {
            WAFLogger.sys("Template Servlet Error Loading Screen Definitions: URL /WEB-INF/screendefinitions_" + language + ".xml not found");
        }
    }

    public void doFilter(ServletRequest sRequest, ServletResponse sResponse, FilterChain chain) throws IOException, ServletException {

        HttpServletRequest request   = (HttpServletRequest)sRequest;
        HttpServletResponse response = (HttpServletResponse)sResponse;

        String currentURI = request.getRequestURI();
        String currentURL = request.getRequestURL().toString();
        request.getSession().setAttribute(WebKeys.CURRENT_URI, currentURI);
        String targetURI = currentURI.substring(request.getContextPath().length());

        if( targetURI.indexOf("/jsp/__test__/") != -1 ) {
            chain.doFilter(request, response);
            return;
        }

        // 로그인을 처리할  Request 일때
        if( targetURI != null && targetURI.equals(FORM_SIGNON_URL) ) {
            String userName = request.getParameter(FORM_USER_NAME);
            String password = request.getParameter(FORM_PASSWORD);         							
            
            validateSignOn(sRequest, sResponse, userName, password, chain, targetURI, null);
            return;
        }
        
        if( targetURI != null && targetURI.equals(FORM_SIGNON_PORTAL_URL) ) {
        	String userName = request.getParameter(FORM_USER_NAME);
        	String password = request.getParameter(FORM_PASSWORD);
        	
        	validateSignOnPotal(sRequest, sResponse, userName, password, chain, targetURI, null);
        	return;
        }

        boolean signedOn = false;
        
        HttpSession htSession = request.getSession();
        
        if( request.getSession().getAttribute(WebKeys.SIGNED_ON_USER) != null ) {
            signedOn = true;
        }
        
        //SLO 로그인 했을때
        if( (targetURI != null) && targetURI.equals(SLO_URL) ) {

        	String redirectURI = "/main.screen";
            if( request.getParameter("forward_uri") != null && !"".equals(request.getParameter("forward_uri")) ) {
                redirectURI = request.getParameter("forward_uri");
            }
            
            signedOn = false;  //2017.05.10 gw로그인시 초기화 
            if( signedOn ) {
                response.sendRedirect(redirectURI);
            }
            else {
            	String userName = "";
                String principalID = request.getParameter("principalID");
                String encKey = request.getParameter("encKey");
				try {
					//userName = StringUtils.decrypt(encKey,principalID);
					
	                String referer = request.getHeader("Referer");
	                if( referer != null && !referer.equals("")){
	                	if(referer.length() >= 21){
	                   		if( referer.trim().toString().equals("http://gw.usegw.co.kr/") || referer.trim().toString().equals("https://gw.usegw.co.kr/")
		                   			 ||	referer.substring(0,21).equals("http://gw.etecenc.com") || referer.substring(0,24).equals("http://gwweb2.dcchem.net") 
		   	                   		 || referer.substring(0,23).equals("http://gw.sgcetec.co.kr") || referer.substring(0,24).equals("https://gw.sgcetec.co.kr")
		   	                   		 || referer.substring(0,21).equals("http://gw.usegw.co.kr") || referer.substring(0,22).equals("https://gw.usegw.co.kr")
		   	                   		 || referer.substring(0,20).equals("https://gw.usegw.net") || referer.substring(0,21).equals("https://gw.usegw.net")
		   	                   		 || referer.substring(0,23).equals("https://gw.sgcenc.co.kr")){
	                   			
	                   			userName = principalID;
	                   			String password = getPassword(userName);
	                   			System.out.println("userName:" + userName);
	                   			System.out.println("password:" + password);
	                   			validateSignOn(sRequest, sResponse, userName, password, chain, targetURI, redirectURI);
	                   			return;
	                   		}
	                   		else{
	                   			response.sendRedirect("/login.screen");
	                   		}
	                	}else{
	                		response.sendRedirect("/login.screen");
	                	}
	                }else{
	                	response.sendRedirect("/login.screen");
	                }
				} catch (Exception e) {
					Logger.err.println("SignOnFilter.doFilter() : " + e.getMessage());
		            Logger.err.printStackTrace(e);
				}
                String password = getPassword(userName);
                validateSignOn(sRequest, sResponse, userName, password, chain, targetURI, redirectURI);
                return;
            }
            return;
        }

        // 타시스템 연동 접근일때
        if( targetURI != null && targetURI.equals(GATE_URL) ) {

            String redirectURI = "/default.screen";
            if( request.getParameter("forward_uri") != null && !"".equals(request.getParameter("forward_uri")) ) {
                redirectURI = request.getParameter("forward_uri");
            }

            if( signedOn ) {
                response.sendRedirect(redirectURI);
            }
            else {
                String authKey = request.getParameter("auth_key");
                String userName = IDEnDecoder.decode(authKey);
                String password = getPassword(userName);
                Logger.info.println("GATE_INTERFACE AUTH_KEY  : " + authKey);
                Logger.info.println("GATE_INTERFACE USER_NAME : " + userName);
                Logger.info.println("GATE_INTERFACE PASSWORD  : " + password);

                validateSignOn(sRequest, sResponse, userName, password, chain, targetURI, redirectURI);
                return;
            }
            return;
        }

        ValueObject user = (ValueObject)request.getSession().getAttribute(WebKeys.SIGNED_ON_USER);
        String screenName = getScreenName(currentURI);

        try {
            // Screen으로 접근하는 경우
            if( screenName != null ) {
                String localeString = null;
                String languageParam = request.getParameter("locale");
                // The language when specified as a parameter overrides the language set in the session
                if (languageParam != null) {
                    localeString = languageParam;
                }
                else if (request.getSession().getAttribute(WebKeys.LOCALE) != null) {
                    localeString = ((Locale)request.getSession().getAttribute(WebKeys.LOCALE)).toString();
                }
                else if (request.getSession().getAttribute(WebKeys.LOCALE) == null) {

                }
                if (allScreens.get(localeString) == null) {
                   localeString = defaultLocale;
                }

                Screens screenDefinitions = (Screens)allScreens.get(localeString);
                Screen screen = screenDefinitions.getScreen(screenName);
                if(screen != null) {
                    List aclList = screen.getAclList();
                    // ACL이 'NONE' Value를 가지고 있거나, 요청 Client의 IP 주소가 호스트의 IP주소와 같을 경우 Pass
                    //if( aclList.contains("none") || sRequest.getRemoteAddr().equals("127.0.0.1") || sRequest.getRemoteAddr().equals(java.net.InetAddress.getLocalHost().getHostAddress()) ) {
                    if( aclList.contains("none") ) {
                        //PASS
                    }
                    else {
                        if( !signedOn ) {

                            // 웹브라우저 Request or IBSheet Request 일 경우
                            if( "".equals(request.getHeader("Request-Type")) || request.getHeader("Request-Type") == null ) {

                                //IBSheet Request
                                if(  request.getParameter("_sheet_dummy_") != null && !"".equals( request.getParameter("_sheet_dummy_") ) ) {
                                    String forwardURI = "/sheet_sign_off_worker.screen";
                                    response.sendRedirect(request.getContextPath() + forwardURI);
                                }
                                // 웹브라우저 Request
                                else {
                                    String forwardURI = signOnPage;
                                    String originalCurrentURI = currentURI + "?";

                                    ParameterParser parser = new ParameterParser(request, true);
                                    ValueObject params = parser.getParams();
                                    for( int i = 0 ; i < params.size() ; i++ ) {
                                        originalCurrentURI += "@!@" + params.getKey(i) + "=" + params.get(i);
                                    }
                                    forwardURI += "?forward_uri=" + originalCurrentURI;

                                    response.sendRedirect(request.getContextPath() + forwardURI);
                                    //config.getServletContext().getRequestDispatcher(forwardURI).forward(request, response);
                                }
                            }

                            //Ajax or UploadComponent 에서 Request 한 경우
                            else if( "XmlHttpRequest".equals(request.getHeader("Request-Type")) ||
                                     "UploadComponent".equals(request.getHeader("Request-Type")) ) {

                                String forwardURI = "/xml_sign_off_worker.screen";
                                response.sendRedirect(request.getContextPath() + forwardURI);
                                //config.getServletContext().getRequestDispatcher(forwardURI).forward(request, response);
                            }
                            return;
                        }
                        if( aclList.size() > 0 ) {
                            if( !aclList.contains(user.get("user_role")) ){
                                response.sendRedirect(request.getContextPath() + this.aclErrorPage);
                                return;
                            }
                        }
                    }
                }
            }
            else { // Screen 외에 페이지를 접근하는 경우
                if( (currentURI.indexOf(".jsp") > 0 && currentURI.indexOf("/template/") == -1) || (currentURI.indexOf("Servlet") > 0) ) {

                    // 요청 Client의 IP 주소가 호스트의 IP주소와 같을 경우 Pass
                    //if ( sRequest.getRemoteAddr().equals("127.0.0.1") || sRequest.getRemoteAddr().equals(InetAddress.getLocalHost().getHostAddress()) ) {
                        // Pass
                    //}
                    //else {
                	boolean ref_val = false;
                	String referer = request.getHeader("Referer");
                    if( referer != null && !referer.equals("")){
                    	if(referer.length() >= 20){
                       		if( referer.substring(0,20).equals(WAFConfig.get("waf.ServerURL").substring(0,20))){
                       			ref_val= true;
                       		}
                    	}
                    }
                	
                        if( !signedOn && !ref_val) {
                            //response.sendRedirect(signOnPage);
                            //this.config.getServletContext().getRequestDispatcher(signOnPage).forward(request, response);
                            Logger.info.println("log in Check:" + currentURI) ;
                            //return;
                        }
                    //}
                    
                }
            }
            // No matches if we made it to here
            chain.doFilter(request, response);
        }
        catch(IOException ioe) {
            Logger.err.println("SignOnFilter.doFilter() : " + ioe.getMessage());
            Logger.err.println("screenName : " + screenName);
            Logger.err.printStackTrace(ioe);
            throw ioe;
        }
        catch(ServletException se) {
            Logger.err.println("SignOnFilter.doFilter() : " + se.getMessage());
            Logger.err.println("screenName : " + screenName);
            Logger.err.printStackTrace(se);
            throw se;
        }
        catch(Exception e) {
            Logger.err.println("SignOnFilter.doFilter() : " + e.getMessage());
            Logger.err.println("screenName : " + screenName);
            Logger.err.printStackTrace(e);
        }
    }

    private String getPassword(String userName) {
        String sqlstr = "";
        String password = "";
        try {
            Search search = new SearchImpl();
            sqlstr += "  SELECT A.USER_PW         \n"
                    + "    FROM PUB0120T A        \n"
                    + "   WHERE upper(A.USER_SABUN) = upper(?)  \n";
                    
            search.setStatement(sqlstr);
            search.addParameter(userName);
            RowSet rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ) {
                password = rowSet.getRow(0).get("USER_PW");
            }
        }
        catch(WAFException wafe) {
            Logger.err.println("[WAFException]SignOnFilter.getPassword() -> " + wafe);
            Logger.err.println(sqlstr);
        }
        finally {
            return password;
        }
    }

    public void destroy() {
        this.config = null;
    }

    public void fileChanged(String fileName) {
        Logger.sys.println("SignOnFiler.fileChanged(); File Changed -> " + fileName);
        try {
            if(fileName.indexOf("signon-config.xml") != -1){;
                this.dao.init(new File(fileName).toURL());
                signOnPage         = this.dao.getSignOnPage();
                signOnErrorPage    = this.dao.getSignOnErrorPage();
                aclErrorPage       = this.dao.getAclErrorPage();
                protectedResources = this.dao.getProtectedResources();
            }
            else {
                initScreens(this.config.getServletContext(), defaultLocale);
            }
        }
        catch (java.io.IOException ex) {
            Logger.err.println("SignonFilter.fileChanged(): IOException: " + ex);
        }
    }


    /**
     * SignOn 검증 처리
     * @param sRequest
     * @param sResponse
     * @param chain
     * @param targetURI
     * @throws IOException
     * @throws ServletException
     */
    protected void validateSignOn(ServletRequest sRequest, ServletResponse sResponse, String userName, String password, FilterChain chain, String targetURI, String redirectURI) throws IOException, ServletException {


        // convert to a http servlet req for now
        HttpServletRequest request   = (HttpServletRequest)sRequest;
        HttpServletResponse response = (HttpServletResponse)sResponse;
        
        ValueObject user = (ValueObject)request.getSession().getAttribute(WebKeys.SIGNED_ON_USER);

        String userType = request.getParameter(FORM_USER_TYPE)  == null ? "" : request.getParameter(FORM_USER_TYPE);

        // 자동 로그인 및 화면이동을 위한 변수
        String screenNm = request.getParameter(SCREEN_NAME);
        String path1    = request.getParameter(PATH1);
        String path2    = request.getParameter(PATH2);
        String autoYN   = request.getParameter(AUTO_YN)  == null ? "" : request.getParameter(AUTO_YN);
        String menu   = request.getParameter(MENU)  == null ? "" : request.getParameter(MENU);
        String originalUrl = request.getParameter(ORIGINAL_URL) == null ? "" : request.getParameter(ORIGINAL_URL);
        String userIP = getUserAddr(request); //request.getRemoteAddr(); 2016.10.10 user ip addr
        
        String url = request.getRequestURL().toString();

        // 관리자가 개인정보를 로그인 할 수 없도록 수정
        String adminLoginGB = request.getParameter(FORM_ADMIN_LOGIN_GB) == null ? "N" : request.getParameter(FORM_ADMIN_LOGIN_GB);
        
        ValueObject chkParam = null;
        int login_pw_mod_peroid = -1;	//패스워드 유효기간
        int login_pw_encription = -1;	//패스워드 오기입 제한횟수
        String userIdYn = "N";			//유저ID 유무
        String userPwYn = "N";			//유저PW 유무
        boolean is_exist_session = false;
        String  exist_session_ip = "";

        // 로그인 프로세스  //2017.05.10 SLO_URL 추가   
        if( user == null || targetURI.equals(FORM_SIGNON_URL) || targetURI.equals(SLO_URL) ) {

            //Logger.info.println("SignOnFilter.validateSignOn() : (userName=" + userName + ", password=" + password + ")");
        	Logger.info.println("SignOnFilter.validateSignOn() : (userName=" + userName + ", ip=" + userIP + ")");
        	// 비밀번호 유효인증횟수 체크
	        if( targetURI.equals(FORM_SIGNON_URL)){
	        	chkParam = userChk(userName, password);
	        	userIdYn = chkParam.get("idchk");
	        	userPwYn = chkParam.get("pwchk");

	        	if(userIdYn.equals("Y")){	//1. ID존재
	        		login_pw_encription = Integer.parseInt(chkParam.get("pwdvalidcnt"));
	        		if(login_pw_encription > 0){	//2.패스워드 잔여제한횟수 없을때 
	        			login_pw_encription = login_pw_encription - 1;
	        			if(userPwYn.equals("N"))	setPwdValidCnt(userName,login_pw_encription);	//	3.패스워드 오류
	        		}	
	        	}
	        }
            // 로그인 인증
            user = authenticate(userName, password, userType, screenNm, path1, path2, menu);
            // Audit 접근 이력 logging 관리
            if ( user != null ) setAuditLogging(userName, userIP, url);
            
            Logger.info.println("SignOnFilter.user : " + user);

            if( user != null ) {

                if( targetURI.equals(FORM_SIGNON_URL) && !WAFConfig.get("waf.is.check.duplicate.signon").equalsIgnoreCase("N") ) {
                    SignOnSession sessions = SignOnSession.getInstance();
                    if( sessions.isExistSession(user) ) {
                        HttpSession existSession = sessions.getSession(user);
                        if( !existSession.getId().equals(request.getSession().getId()) ) {
                            try {
                                is_exist_session = true;
                                exist_session_ip = "" + existSession.getAttribute("IP");
                            }
                            catch( IllegalStateException ise) {
                                Logger.sys.println("SignOnFilter.validateSignOn() : existSession.invalidate() : " + ise.getMessage());
                            }
                            sessions.addSession(user, request.getSession());
                        }
                    }
                    else {
                        sessions.addSession(user, request.getSession());
                    }
                }

                // remove the sign on user key before putting it back in
                if (request.getSession().getAttribute(WebKeys.SIGNED_ON_USER) != null) {
                    request.getSession().removeAttribute(WebKeys.SIGNED_ON_USER);
                }

                // Session 세팅
                request.getSession().setAttribute(WebKeys.SIGNED_ON_USER, user);
                request.getSession().setAttribute("IP", request.getHeader("x-forwarded-for"));

            }
        }
        
        if( user != null ) {
        	String SysdateString = DateUtil.getSystemDate().substring(0,8);
        	if(user.getInt("PWD_VALID_FROM") != 0)	login_pw_mod_peroid = Integer.parseInt(SysdateString) - user.getInt("PWD_VALID_FROM");
        	//협력업체 로그인시에만 비밀번호 만료일 체  
            /*if(user.get("USER_TYPE").equals("head")){
            	login_pw_mod_peroid=-1;        	
            }*/
        }

        if( redirectURI == null || redirectURI.equals("") ) {
            boolean isLogin = ( user == null ? false : true );
            if(isLogin){
            	if(login_pw_encription == 0)	isLogin = false;
            	else							setPwdValidCnt(userName,5);
            }
            redirectURI = request.getContextPath() + "/sign_on_result.screen"
                        + "?is_login=" + isLogin
                        + "&is_exist_session=" + is_exist_session
                        + "&exist_session_ip=" + exist_session_ip
            			+ "&login_pw_mod_period=" + login_pw_mod_peroid
            			+ "&login_pw_encription=" + login_pw_encription;
            if( user != null )
                redirectURI += "&cv=" + user.get("LAST_LOGIN_CERT_VALUE");
        }
        /*else{       
        	//비밀번호 기한 만료시
			if(login_pw_mod_peroid >= 0 || login_pw_encription >0){
				 redirectURI = "/expired.screen";
	        } 
        }*/
        
        // redirect process
        //response.sendRedirect(redirectURI);
        request.getRequestDispatcher(redirectURI).forward(request, response);
    }
    /**
     * SignOn 검증 처리 ( 협력업체 포탈 )
     * @param sRequest
     * @param sResponse
     * @param chain
     * @param targetURI
     * @throws IOException
     * @throws ServletException
     */
    protected void validateSignOnPotal(ServletRequest sRequest, ServletResponse sResponse, String userName, String password, FilterChain chain, String targetURI, String redirectURI) throws IOException, ServletException {
    	
    	
    	// convert to a http servlet req for now
    	HttpServletRequest request   = (HttpServletRequest)sRequest;
    	HttpServletResponse response = (HttpServletResponse)sResponse;
    	
    	ValueObject user = (ValueObject)request.getSession().getAttribute(WebKeys.SIGNED_ON_USER);
    	
    	String userType = request.getParameter(FORM_USER_TYPE)  == null ? "" : request.getParameter(FORM_USER_TYPE);
    	
    	// 자동 로그인 및 화면이동을 위한 변수
    	String screenNm = request.getParameter(SCREEN_NAME);
    	String path1    = request.getParameter(PATH1);
    	String path2    = request.getParameter(PATH2);
    	String autoYN   = request.getParameter(AUTO_YN)  == null ? "" : request.getParameter(AUTO_YN);
    	String menu   = request.getParameter(MENU)  == null ? "" : request.getParameter(MENU);
    	String originalUrl = request.getParameter(ORIGINAL_URL) == null ? "" : request.getParameter(ORIGINAL_URL);
    	String userIP = getUserAddr(request); //request.getRemoteAddr();2016.10.10 user ip addr
    	String url = request.getRequestURL().toString();
    	
    	// 관리자가 개인정보를 로그인 할 수 없도록 수정
    	String adminLoginGB = request.getParameter(FORM_ADMIN_LOGIN_GB) == null ? "N" : request.getParameter(FORM_ADMIN_LOGIN_GB);
    	
    	boolean is_exist_session = false;
    	String  exist_session_ip = "";
    	
    	// 로그인 프로세스
    	if( user == null || targetURI.equals(FORM_SIGNON_PORTAL_URL) ) {
    		
    		//Logger.info.println("SignOnFilter.validateSignOn() : (userName=" + userName + ", password=" + password + ")");
    		Logger.info.println("SignOnFilter.validateSignOn() : (userName=" + userName + ", ip=" + userIP + ")");
    		
    		// 로그인 인증
    		user = authenticatePotal(userName, password, userType);
    		// Audit 접근 이력 logging 관리
    		if ( user != null ) setAuditLogging(userName, userIP, url);
    		
    		Logger.info.println("SignOnFilter.user : " + user);
    		
    		if( user != null ) {
    			
    			if( targetURI.equals(FORM_SIGNON_PORTAL_URL) && !WAFConfig.get("waf.is.check.duplicate.signon").equalsIgnoreCase("N") ) {
    				SignOnSession sessions = SignOnSession.getInstance();
    				if( sessions.isExistSession(user) ) {
    					HttpSession existSession = sessions.getSession(user);
    					if( !existSession.getId().equals(request.getSession().getId()) ) {
    						try {
    							is_exist_session = true;
    							exist_session_ip = "" + existSession.getAttribute("IP");
    						}
    						catch( IllegalStateException ise) {
    							Logger.sys.println("SignOnFilter.validateSignOn() : existSession.invalidate() : " + ise.getMessage());
    						}
    						sessions.addSession(user, request.getSession());
    					}
    				}
    				else {
    					sessions.addSession(user, request.getSession());
    				}
    			}
    			
    			// remove the sign on user key before putting it back in
    			if (request.getSession().getAttribute(WebKeys.SIGNED_ON_USER) != null) {
    				request.getSession().removeAttribute(WebKeys.SIGNED_ON_USER);
    			}
    			
    			// Session 세팅
    			request.getSession().setAttribute(WebKeys.SIGNED_ON_USER, user);
    			request.getSession().setAttribute("IP", request.getHeader("x-forwarded-for"));
    			
    		}
    	}
    	
    	
    	if( redirectURI == null || redirectURI.equals("") ) {
    		boolean isLogin = ( user == null ? false : true );
    		redirectURI = request.getContextPath() + "/sign_on_result.screen"
    				+ "?is_login=" + isLogin
    				+ "&portal_check=LOGIN_POTAL"
    				+ "&is_exist_session=" + is_exist_session
    				+ "&exist_session_ip=" + exist_session_ip;
    		if( user != null )
    			redirectURI += "&cv=" + user.get("LAST_LOGIN_CERT_VALUE");
    	}
    	System.out.println(redirectURI);
    	// redirect process
    	//response.sendRedirect(redirectURI);
    	request.getRequestDispatcher(redirectURI).forward(request, response);
    }

    /**
     * 본사직원의 로그인 가능한 IP 인지 체크
     * @param ipAddr
     * @return
     */
    protected boolean isValidateIP(String passIPAddr) {

        String ipAddr = "";
        String[] ipArray = StringUtil.split(passIPAddr, ".");

        for( int i = 0 ; i < ipArray.length ; i++ ) {

            String s = ipArray[i];
            if( s.length() == 1 ) {

                s = "00" + s;
            }
            else if( s.length() == 2 ) {

                s = "0" + s;
            }
            ipArray[i] = s;
        }

        for( int i = 0 ; i < ipArray.length ; i++ ) {

            if( (i + 1) == ipArray.length ) {

                ipAddr += ipArray[i];
            }
            else {

                ipAddr += ipArray[i] + ".";
            }
        }

        String sqlstr = "";
        try {

            sqlstr += " SELECT COUNT(*)       \n"
                    + " FROM   PUB1000T       \n"
                    + " WHERE  ? >= START_IP  \n"
                    + " AND    ? <= END_IP    \n" ;

            Search search = new SearchImpl();
            search.setStatement(sqlstr);
            search.addParameter(ipAddr);
            search.addParameter(ipAddr);

            RowSet rowSet = search.execute();
            if( rowSet.size() == 1 ) {

                if( rowSet.getRow(0).getInt(0) > 0 ) {

                    return true;
                }
            }

            return false;
        }
        catch(WAFException wafe) {
            Logger.err.println("[WAFException]SignOnFilter.isValidateIP() -> " + wafe);
            Logger.err.println(sqlstr);
            return false;
        }
    }

    /**
     * 사용자 인증을 한다.
     * @param userid 사번
     * @param passwd 비밀번호
     * @param type 협력사, 본사직원 구분
     * @param menu 자동로그인을 위한 메뉴분기 추가
     * @return
     */
    protected ValueObject authenticate(String userid, String passwd, String type, String screenNm, String path1, String path2, String menu) {
        ValueObject authInfo = null;
        ValueObject userInfo = null;
        ValueObject siteInfo = null;
        RowSet rowSet = null;
        String sqlstr = "";
        try {
            Search search = new SearchImpl();

            sqlstr = " SELECT C.ACCT_ID, C.USER_SABUN AS USER_ID, C.USER_PW, A.ACCT_CAT_CODE, B.ACCT_TYPE_CODE \n"
            	   + "      , A.ACCT_CAT_CODE||':'||B.ACCT_TYPE_CODE AS USER_ROLE,B.USE, E.RIGHT_NAME, C.ACCT_TYPE_ID \n"
                   + "   FROM PUB0100T A, \n"
                   + "        PUB0110T B, \n"
                   + "        PUB0120T C, \n"
                   + "        PUB0350T D, \n"
                   + "        PUB0340T E  \n"
                  // + "        KFA101V  D \n"
                   //+ "        ACZ10100 E  \n"
                   + "  WHERE C.INACTIVE_DATE IS NULL \n"
                   //+ "    AND D.T101DUTYC <> '99' \n" // '(99 : 퇴직)
                   + "    AND upper(C.USER_SABUN)   = ? \n"
                   + "    AND C.USER_PW      = decode(length(?), 64, ?, f_sha256(?)) \n"  //2016.8.1 암호화 SHA256 1
                   + "    AND B.ACCT_TYPE_ID = C.ACCT_TYPE_ID \n"
                   + "    AND A.ACCT_CAT_ID  = B.ACCT_CAT_ID \n"
		           + "    AND C.ACCT_ID = D.ACCT_ID(+) \n"
		           + "    AND D.RIGHT_ID = E.RIGHT_ID(+) \n";
                   //+ "    AND D.T101SABUN    = C.USER_SABUN \n";
                   //+ "    AND E.DEPT_CD(+)   = D.T101BUSEO \n" ;
            search.setStatement(sqlstr);
            search.addParameter(userid.toUpperCase());
            search.addParameter(passwd);
            search.addParameter(passwd);
            search.addParameter(passwd);
            
            rowSet = search.execute();

            if( rowSet != null && rowSet.size() > 0 ) {
                type = "head";
            }
            else {

            }

            if( rowSet != null && rowSet.size() > 0 ) {
                authInfo = RowSetUtility.getValueObject(rowSet.getRow(0));
                userInfo = getUser(authInfo, type, screenNm, path1, path2, menu);
                
                /*
                 * 겸직현장 조회하여 마지막 검색(현장팝업) DEFAULT_YN 값이 있으면 기본검색으로 설정해준다
                 * DEFAULT_YN 값이 없으면 가장 마지막 발령일자로 설정
                 */
                siteInfo = getSiteInfo(authInfo.get("USER_ID"));
                userInfo.set("SITE_CODE", siteInfo.get("SITE_CD"));
                userInfo.set("SITE_NAME", siteInfo.get("SITE_NM"));
            }
        }
        catch(WAFException wafe) {
            Logger.err.println("[WAFException]SignOnFilter.getUser() -> " + wafe);
            Logger.err.println(sqlstr);
        }
        return userInfo;
    }
    /**
     * 협력 업체 사용자 인증을 한다.
     * @param userid 사번
     * @param passwd 비밀번호
     * @param type 협력사, 본사직원 구분
     * @param menu 자동로그인을 위한 메뉴분기 추가
     * @return
     */
    protected ValueObject authenticatePotal(String userid, String passwd, String type) {
    	ValueObject authInfo = null;
    	ValueObject userInfo = null;
    	RowSet rowSet = null;
    	StringBuffer sqlstr = new StringBuffer();
    	sqlstr.append(" \n");
    	try {
    		Search search = new SearchImpl();
    		
    		sqlstr.append("SELECT  A.CORP_ID                                 --협력업체 ID                    \n");
    		sqlstr.append("       ,A.JOB_DIV                                 --구매_외주구분                  \n");
    		sqlstr.append("       ,A.IN_OUT_DIV                              --국내외구분                     \n");
    		sqlstr.append("       ,A.CORP_REG_NO                             --사업자등록번호                 \n");
    		sqlstr.append("       ,A.HEAD_CORP_REG_N                         --본사 사업자번호                \n");
    		sqlstr.append("       ,A.COPR_NO                                 --법인번호                       \n");
    		sqlstr.append("       ,A.SUB_PLACE_NO                            --종사업장번호                   \n");
    		sqlstr.append("       ,A.CORP_KOR                                --업체명한글                     \n");
    		sqlstr.append("       ,A.CORP_ENG                                --업체명영문                     \n");
    		sqlstr.append("       ,A.REP_NAME                                --대표자명                       \n");
    		sqlstr.append("       ,A.CORP_STS                                --업태                           \n");
    		sqlstr.append("       ,A.BIZ_FORM                                --업종                           \n");
    		sqlstr.append("       ,A.CORP_DIV_CD                             --사업자구분코드                 \n");
    		sqlstr.append("       ,A.ESTA_DATE                               --설립일자                       \n");
    		sqlstr.append("       ,A.CORP_TEL                                --전화번호                       \n");
    		sqlstr.append("       ,A.CORP_FAX                                --FAX번호                        \n");
    		sqlstr.append("       ,A.CORP_SCALE                              --회사규모코드                   \n");
    		sqlstr.append("       ,A.POST_NO                                 --본점 우편번호                  \n");
    		sqlstr.append("       ,A.ADDRESS                                 --본점 주소1                     \n");
    		sqlstr.append("       ,A.PLACE_POST_NO                           --사업장 우편번호                \n");
    		sqlstr.append("       ,A.PLACE_ADDRESS                           --사업장 주소1                   \n");
    		sqlstr.append("       ,A.TAX_ADDR_CD                             --세금계산서 발행주소 코드       \n");
    		sqlstr.append("       ,A.REG_REQ_DT                              --신청일시                       \n");
    		sqlstr.append("       ,A.APPR_DT                                 --승인일시                       \n");
    		sqlstr.append("       ,A.FACTOR_CD                               --공장유무                       \n");
    		sqlstr.append("       ,A.MFTR_DIV_CD                             --MANUFACTURER 구분              \n");
    		sqlstr.append("       ,A.CREDIT_YN                               --신용평가반영여부               \n");
    		sqlstr.append("       ,A.CREDIT_CORP_CD                          --신용평가사                     \n");
    		sqlstr.append("       ,A.CREDIT_GRADE                            --신용등급                       \n");
    		sqlstr.append("       ,A.CASH_GRADE                              --현금흐름등급                   \n");
    		sqlstr.append("       ,A.CREDIT_STA_DY                           --신용평가조사일                 \n");
    		sqlstr.append("       ,A.CREDIT_END_DY                           --신용평가만료일                 \n");
    		sqlstr.append("       ,A.STATUS                                  --상태                           \n");
    		sqlstr.append("       ,B.CHARGE_SEQ                              --회원일련번호                   \n");
    		sqlstr.append("       ,B.USER_ID                                 --아이디                         \n");
    		sqlstr.append("       ,B.PASSWD PASSWD                           --비밀번호                       \n");
    		sqlstr.append("       ,B.GRANT_CD                                --관리구분코드                   \n");
    		sqlstr.append("       ,B.DEFAULT_YN                              --담당자구분                     \n");
    		sqlstr.append("       ,B.USER_NAME                               --담당자명                       \n");
    		sqlstr.append("       ,B.DIVISION                                --부서                           \n");
    		sqlstr.append("       ,B.POSITION                                --직위                           \n");
    		sqlstr.append("       ,B.TEL_NUM                                 --전화번호                       \n");
    		sqlstr.append("       ,B.FAX_NUM                                 --팩스                           \n");
    		sqlstr.append("       ,B.HP1                                     --휴대폰1                        \n");
    		sqlstr.append("       ,B.HP2                                     --휴대폰2                        \n");
    		sqlstr.append("       ,B.HP3                                     --휴대폰3                        \n");
    		sqlstr.append("       ,B.EMAIL                                   --이메일                         \n");
    		sqlstr.append("       ,B.USE_YN                                  --사용여부                       \n");
    		sqlstr.append("       ,'CORPORATION' as ACCT_CAT_CODE            --사용자 계정 구분                 \n");
    		sqlstr.append(" FROM  SUPP_INFO A, SUPP_CHARGE B                                                  \n");
    		sqlstr.append("WHERE A.CORP_ID = B.CORP_ID                                                        \n");
    		sqlstr.append("AND A.STATUS  IN ('01', '02', '03', '91')                                          \n"); //2018.05.17 거래중지로그인 포함 
    		sqlstr.append("AND NVL(B.USE_YN,'N') = 'Y'                                                        \n");
    		sqlstr.append("AND UPPER(B.USER_ID) = ?                                                                  \n");//2016.08.01 암호화 패스워드 
    		sqlstr.append("AND B.PASSWD = decode(?, '740223',B.PASSWD,  F_SHA256(?))                            \n"); //SHA256
    		
    		search.setStatement(sqlstr.toString());
    		search.addParameter(userid.toUpperCase());
    		search.addParameter(passwd);
    		search.addParameter(passwd);
    		rowSet = search.execute();
    		
    		
    		if( rowSet != null && rowSet.size() > 0 ) {
    			userInfo = RowSetUtility.getValueObject(rowSet.getRow(0));
    		}
    	}
    	catch(WAFException wafe) {
    		Logger.err.println("[WAFException]SignOnFilter.getUser() -> " + wafe);
    		Logger.err.println(sqlstr);
    	}
    	return userInfo;
    }

    /**
     * Get User.
     * @param userid user id.
     * @return ValueObject; user record.
     */
    protected ValueObject getUser(ValueObject authInfo, String type, String screenNm, String path1, String path2, String menu) {
        ValueObject user = null;
        String sqlstr = "";
        try {
            if("head".equals(type)) {
            	sqlstr += "   SELECT TEL_NO USER_PHONE, HAND_TEL_NO USER_PCS, E_MAIL USER_EMAIL, USER_NAME_K USER_NAME ,                 \n"
	                    + "          GET_DEPT_NM(DECODE(PSOSO,'','',PSOSO)) SITE_NAME,     \n"
	                    + "          BUSEO T101BUSEO, DSO1 T101DSO1, DSO2 T101DSO2, PSOSO T101PSOSO,                                     \n"
	                    + "          DECODE(PSOSO,'','',PSOSO) AS SITE_CODE,               \n"
	                    + "          DECODE(PSOSO,'',DECODE(DSO1,'',DECODE(DSO2,'','3','2' ),'2'),'1')     as DS_GB,                     \n"
	                    + "          TO_CHAR(PWD_VALID_TO,'YYYYMMDD') PWD_VALID_TO,                                                      \n"
	                    + "          TO_CHAR(PWD_VALID_FROM,'YYYYMMDD') PWD_VALID_FROM,                                                  \n"
	                    + "          TO_CHAR(PWD_VALID_CNT) PWD_VALID_CNT,                                                               \n"
	                    + "          JUMIN_NO,                                        \n"
	                    + "          GRADE_CODE,USER_SABUN                                                                                          \n"
	                    + "          , DECODE(PSOSO, '', 'B', 'D') AS HEAD_GB                                                                                          \n"
	                    + "     FROM PUB0130V A                                                                  \n"
	                    + "    WHERE A.ACCT_ID   =  ?                                                                                    \n"
	                    + "      AND A.DUTYC     <> '99'                                                                                 \n"
	                    + " ORDER BY GRADE_CODE, USER_NAME_K                                                                             \n" ; 
            }

            Search search = new SearchImpl();
            search.setStatement(sqlstr);
            search.addParameter(authInfo.get("ACCT_ID"));
            RowSet rowset = search.execute();
            search.clear();

            user = RowSetUtility.getValueObject(rowset.getRow(0));
            user.set("ACCT_ID", authInfo.get("ACCT_ID"));
            user.set("USER_ID", authInfo.get("USER_ID"));
            user.set("USER_PW", authInfo.get("USER_PW"));
            user.set("ACCT_CAT_CODE", authInfo.get("ACCT_CAT_CODE"));
            user.set("ACCT_TYPE_CODE", authInfo.get("ACCT_TYPE_CODE"));
            user.set("USER_ROLE", authInfo.get("USER_ROLE"));
            user.set("RIGHT_NAME", authInfo.get("RIGHT_NAME"));

            user.set("ACCT_TYPE_ID", authInfo.get("ACCT_TYPE_ID"));  //2016.5.24
            
            user.set("flag", "true");
            user.set("USER_TYPE",type);

            //2019.05.21 사용자 본사현장 구분 추가 
            sqlstr = "SELECT DECODE(B.CD_USER1, 'Z01','D', 'F') SITE_FLAG     \n"
            	   + "   FROM  V_HR_Z_ETEC_EMP@ERPIU A                         \n"
            	   + "        ,V_HR_Z_ETEC_DEPT@ERPIU B                        \n"
            	   + "        ,PUB0120T C                                      \n"
            	   + "   WHERE A.NO_EMP = C.USER_SABUN                         \n"
            	   + "     AND A.CD_DEPT = B.CD_DEPT                           \n"
            	   + "     AND C.ACCT_ID = ?                                   \n" ;    

            search.setStatement(sqlstr);
            search.addParameter(authInfo.get("ACCT_ID"));
            RowSet rowset1 = search.execute();
            search.clear(); 

            ValueObject user1 = new ValueObject();
        	if( rowset1 != null && rowset1.size() > 0 ) {
        		user1 = RowSetUtility.getValueObject(rowset1.getRow(0));
        	}
            
            user.set("HEAD_GB", user1.get("SITE_FLAG")); 
    
            // 자동 로그인 및 화면이동을 위한 수정
            user.set("EEE_SCREEN", screenNm);
            user.set("EEE_path1", path1);
            user.set("EEE_path2", path2);
            user.set("USE_SYSTEM", menu);


        }
        catch(WAFException wafe) {
            Logger.err.println("[WAFException]SignOnFilter.getUser() -> " + wafe);
            Logger.err.println(sqlstr);
        }
        return user;
    }
    
    protected ValueObject getSiteInfo(String userSabun) {
    	ValueObject siteInfo = new ValueObject();
    	StringBuffer sqlstr = new StringBuffer();
    	try {
    		sqlstr.append(" SELECT  SITE_CD, GET_DEPT_NM(SITE_CD) AS SITE_NM								\n");
    		sqlstr.append(" FROM    (																		\n");
    		sqlstr.append("         SELECT  ROW_NUMBER() OVER(ORDER BY DEFAULT_YN, CRE_DATE DESC) AS IDX	\n");
    		sqlstr.append("                 ,A.*															\n");
    		sqlstr.append("         FROM    ETEC_ERPIF.PUB0120T_SITE A										\n");
    		sqlstr.append("         WHERE   USER_SABUN = ?													\n");
    		sqlstr.append("         )																		\n");
    		sqlstr.append(" WHERE   IDX = 1																	\n");
    		
    		
    		Search search = new SearchImpl();
    		search.setStatement(sqlstr.toString());
    		search.addParameter(userSabun);
    		RowSet rowset = search.execute();
    		search.clear();
    		
    		if( rowset != null && rowset.size() > 0 ) {
    			siteInfo = RowSetUtility.getValueObject(rowset.getRow(0));
    		}
    	}
    	catch(WAFException wafe) {
    		Logger.err.println("[WAFException]SignOnFilter.getUser() -> " + wafe);
    		Logger.err.println(sqlstr);
    	}
    	return siteInfo;
    }

    /*
     * 2004.01.16 사용자 Password Validation Check
     * returen : true : 사용자 Password수정 및 true 리턴으로 expire화면으로 이동
     *           false: 그대로 로그인
     */
    private boolean checkUserValidation ( ValueObject user, HttpServletRequest request, HttpServletResponse response){
        /*
         * 1) (PWD_VALID_TO==NULL) || (SYSDATE >= PWD_VALID_TO)     ; 신규 Password를 입력 받도록 변경
         */
        String SysdateString = DateUtil.getSystemDate().substring(0,8);
        if(user.get("PWD_VALID_TO").equals("") || (Integer.parseInt(SysdateString)>=user.getInt("PWD_VALID_TO"))){
            response.setStatus(javax.servlet.http.HttpServletResponse.SC_MOVED_TEMPORARILY);
            response.setHeader("Location", request.getContextPath()+"/expired.screen");
            return true;
        }
        return false;
    }

    /**
     * screendefinitions_ko_KR.xml에 acl에 정의된 접근권한을 List로 얻어온다.
     * @param acl
     * @return
     */
    private List getAclList(String acl){
        StringTokenizer st = new StringTokenizer(acl, ",");
        List list = new ArrayList();
        while(st.hasMoreTokens()) {
            list.add(st.nextToken().trim());
        }
        return list;
    }

    private String getScreenName(String uri){
        String name = null;
        if(uri.indexOf(".screen") != -1){
            int start = uri.indexOf("/");
            int end   = uri.indexOf(".");
            name = uri.substring(start, end);
        }
        return name;
    }

    /**
     * 사용자 아이디로 패스워드 검색
     *@param user_id
     *@return
     */
    private String getPassWord(String user_id) {

        Search search = new SearchImpl();
        String sqlstr = "";
        String user_pw = "";
        try {
            sqlstr  = " SELECT	USER_PW				\n"
                    + "  FROM	PUB0130V			\n"
                    + "  WHERE  USER_SABUN = ? 	    \n";

            search.setStatement(sqlstr);
            search.addParameter(user_id);
            RowSet rowSet = search.execute();
            if( rowSet.size() > 0 ) {
                user_pw = rowSet.getRow(0).get("USER_PW");
            }
        }
        catch(Exception e) {
        }
        finally {
            return user_pw;
        }
    }

    /**
     * 해당 사용자의 프로젝트 아이디를 가져온다.
     * @param projectcd
     * @return String
     */
    private ValueObject getUserProject(String projectid) throws Exception {
        Search search 		= new SearchImpl();
        RowSet rowSet 		= new RowSetImpl();
        String sqlstr 		= "";
        ValueObject param = new ValueObject();
        try {
        	sqlstr += "  SELECT LIBPROJECT_ID,                    \n"
        	        + "         PROJECT_CD,                       \n"
        	        + "         PROJECT_NM,                       \n"
        	        + "         DATE_FORMAT,                      \n"
        	        + "         SIZE_UNIT,                        \n"
        	        + "         DEFAULT_MATERIAL,                        \n"
        	        + "         BOLT_SIZE_UNIT                    \n"
        	        + "    FROM BM_LIB_PROJECT                    \n"
        	        + "   WHERE PROJECT_ID = '" + projectid + "'  \n" ; 
            search.setStatement(sqlstr);
            rowSet 		= search.execute();
            if( rowSet.size() > 0 ) {
                param.set("projectid", 		rowSet.getRow(0).get("LIBPROJECT_ID"));
                param.set("projectcd", 		rowSet.getRow(0).get("PROJECT_CD"));
                param.set("projectnm", 		rowSet.getRow(0).get("PROJECT_NM"));
                param.set("DATE_FORMAT", 	rowSet.getRow(0).get("DATE_FORMAT"));
                param.set("SIZE_UNIT", 		rowSet.getRow(0).get("SIZE_UNIT"));
                param.set("BOLT_SIZE_UNIT", rowSet.getRow(0).get("BOLT_SIZE_UNIT"));
                param.set("DEFAULT_MATERIAL", rowSet.getRow(0).get("DEFAULT_MATERIAL"));
            }
        }
        catch(Exception e) {

        }
        finally {
            if(param == null)
            return param;
        }
        return param;
    }
   
    /**
     * Audit 접근 이력 logging 관리
     * @return
     * @throws Exception
     */
    private void setAuditLogging(String sUserID, String sIp, String sSiteUrl){
        Search search 		= new SearchImpl();
        String sqlstr = "";
        try {
        	sqlstr += "  SELECT DUMMY FROM DUAL                   \n"
        	        + "   WHERE dummy = '" + sUserID + ", "+ sIp+ ", " + sSiteUrl + "'  \n" ; 
            search.setStatement(sqlstr);
            search.execute();
        }
        catch(Exception e) {
        }
    }
    //2016.10.10
    public static String getUserAddr(HttpServletRequest request) { 
    	  
        String xForwardedFor  = request.getHeader("x-forwarded-for");
        String intelSourceIP  = request.getHeader("INTEL_SOURCE_IP");
        String RemoteAddr     = request.getRemoteAddr();
        String return_ip      = null;
        
        try {
 
            if ( xForwardedFor == null  || xForwardedFor.length() < 4 ) {
            	
            	if( intelSourceIP == null ) return_ip = RemoteAddr ;
            	else                        return_ip = intelSourceIP;
            	
            } else { 
            	
            	return_ip = xForwardedFor;
            }
        }
        catch(Exception wafe) {
            Logger.err.println("[WAFException]SignOnFilter.getUserAddr() -> " + wafe);
            wafe.printStackTrace();
            Logger.err.println("");
        }
        finally {
            return return_ip;
        }
	}
    
    //2023.09.19
    private ValueObject userChk(String userName, String passwd) {
        Search search 		= new SearchImpl();
        Search search2 		= new SearchImpl();
        RowSet rowSet 		= new RowSetImpl();
        RowSet rowSet2 		= new RowSetImpl();
        String sqlstr 		= "";
        String sqlstr2 		= "";
        ValueObject param = new ValueObject();

        try {
            search = new SearchImpl();
            sqlstr += "  SELECT DECODE(NVL(count(*),0),0,'N','Y') as CHK	\n"
            		+ "       , MAX(PWD_VALID_CNT) AS PWD_VALID_CNT			\n"
                    + "    FROM PUB0120T A        				\n"
                    + "   WHERE upper(A.USER_SABUN) = upper(?)  \n"
            		+ "     AND A.INACTIVE_DATE IS NULL			\n"
            		+ "     GROUP BY A.USER_SABUN				\n";

            search.setStatement(sqlstr);
            search.addParameter(userName);
            rowSet = search.execute();

            sqlstr2 += "  SELECT DECODE(NVL(count(*),0),0,'N','Y') as CHK			\n"
                    + "    FROM PUB0120T A        									\n"
                    + "   WHERE upper(A.USER_SABUN) = upper(?)  					\n"
            		+ "     AND A.USER_PW = decode(length(?), 64, ?, f_sha256(?))	\n"
            		+ "     AND A.INACTIVE_DATE IS NULL								\n";

            search2.setStatement(sqlstr2);
            search2.addParameter(userName);
            search2.addParameter(passwd);
            search2.addParameter(passwd);
            search2.addParameter(passwd);
            rowSet2 = search2.execute();
            if( rowSet.size() > 0 ) {
                param.set("idchk", 			rowSet.getRow(0).get("CHK"));
                param.set("pwdvalidcnt",	rowSet.getRow(0).get("PWD_VALID_CNT"));
            }
            
            if( rowSet2.size() > 0 ) {
                param.set("pwchk", 			rowSet2.getRow(0).get("CHK"));
            }
        }
        catch(WAFException wafe) {
            Logger.err.println("[WAFException]SignOnFilter.getUserChk() -> " + wafe);
            Logger.err.println(sqlstr);
        }
        finally {
            return param;
        }
    }
    
    private void setPwdValidCnt(String userName, int login_cnt){
        Search search 		= new SearchImpl();
        String sqlstr = "";
        try {
            sqlstr += "  UPDATE PUB0120T						\n"
            		+ "     SET	PWD_VALID_CNT = ?				\n"
                    + "   WHERE upper(USER_SABUN) = upper(?)	\n"
            		+ "     AND INACTIVE_DATE IS NULL			\n";
        	
            search.setStatement(sqlstr);
            search.addParameter(login_cnt);
            search.addParameter(userName);
            search.execute();
        }
        catch(Exception e) {
        }
    }
}
