package kr.co.kihyun.beans.batch;

import java.sql.ResultSet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import kr.co.kihyun.lang.MString;
import kr.co.kihyun.db.DBPool;

public class UserInfoUp extends DBPool { 
    
    public int dataCnt=1;
    public Connection con=null;
    public PrintWriter out=null;
    public ArrayList list=new ArrayList();
    public StringBuffer sbuf=new StringBuffer();

    public UserInfoUp(HttpServletRequest req,HttpServletResponse res) {
        
        //44.적절하지 않은 예외처리(광범위예외클래스)_CWE-754 : Update by YOUNGJUN,CHO
        try {
            res.setContentType("text/html;charset=UTF-8");
            out = res.getWriter();            
            String startId = req.getParameter("startId");
            String gubun = MString.checkNull(req.getParameter("gubun"), "ORGAN");
            String keyWord = req.getParameter("keyWord");

        } catch (IOException ioex) {
            ioex.printStackTrace();
        } catch(Exception ex) { 
            //System.out.println("error exception1:"+ex); 
            ex.printStackTrace();
        } 
        //================================================
    }

    public void deleteDuplecateData(String keyWord) {
        
        //44.적절하지 않은 예외처리(광범위예외클래스)_CWE-754 : Update by YOUNGJUN,CHO
        PreparedStatement stmt = null;
        String sql = null;
        ResultSet rs = null;
        
        try {
            rs = null;
            stmt = null;
            //String sql="SELECT UPPER_DEPT FROM MOUMI_DEPT WHERE SEL_TYPE='Y' AND NAME LIKE '%"+keyWord+"%'";
            //18.SQL 삽입(메소드파라미터)_CWE-89 : Update by KWON,HAN
          //sql = "SELECT UPPER_DEPT FROM MOUMI_DEPT WHERE NAME LIKE '%" + keyWord + "%'";
            sql = "SELECT UPPER_DEPT FROM MOUMI_DEPT WHERE NAME LIKE '%' || " + "?" + " || '%'";
            //=====================================================
            
            con = getConnection();
            stmt = con.prepareStatement(sql);
            
            //18.SQL 삽입(메소드파라미터)_CWE-89 : Add by KWON,HAN
            stmt.setString(1, keyWord);
            //+++++++++++++++++++++++++++++++++++++++
            
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                String tmp = rs.getString(1);
                if (tmp == null || tmp.equals("")) {
                    continue;
                }
                list.add("#" + tmp);
                dataCnt++;
                if (dataCnt > 500) {
                    return;
                }
                getSearchLoop(tmp);
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
        } catch(Exception ex) { 
            //System.out.println("error exception2:"+ex); 
            ex.printStackTrace();
        } finally { 
            //42.비어있는 if문 : Update by YOUNGJUN,CHO
            if(rs!=null)   { 
                try { 
                    rs.close();	
                    rs=null; 
                } catch (SQLException sqlex) {
                    sqlex.printStackTrace();
                } catch(Exception ex){ 
                    //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO
                    ex.printStackTrace();
                    //++++++++++++++++++++++++++++++++++++++++++++++++
                } 
            }
            if(stmt!=null) { 
                try { 
                    stmt.close(); 
                    stmt=null; 
                } catch (SQLException sqlex) {
                    sqlex.printStackTrace();
                } catch(Exception ex){ 
                    //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO
                    ex.printStackTrace();
                    //++++++++++++++++++++++++++++++++++++++++++++++++
                } 
            } 
            if(con!=null)  { 
                try { 
                    con.close(); 
                    con=null; 
                } catch (SQLException sqlex) {
                    sqlex.printStackTrace();
                } catch(Exception ex) { 
                    //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO
                    ex.printStackTrace();
                    //++++++++++++++++++++++++++++++++++++++++++++++++
                } 
            }
            //================================================
        }
        //================================================
    }

    public void selectInsertUserData(String keyWord) {
        
        //44.적절하지 않은 예외처리(광범위예외클래스)_CWE-754 : Update by YOUNGJUN,CHO
        PreparedStatement stmt = null;
        String sql = null;
        ResultSet rs = null;
        
        try {
            rs = null;
            stmt = null;
            //18.SQL 삽입(메소드파라미터)_CWE-89 : Update by KWON,HAN
          //sql = "SELECT PART_ID FROM MOUMI_USER_DEPT WHERE DEL_TYPE='N' AND NAME LIKE '%" + keyWord + "%'";
            sql = "SELECT PART_ID FROM MOUMI_USER_DEPT WHERE DEL_TYPE='N' AND NAME LIKE '%' || " + "?" + " || '%'";
            //=================================================
            
            con = getConnection();
            stmt = con.prepareStatement(sql);
            
            //19.SQL 삽입(메소드파라미터)_CWE-89  : Add by KWON,HAN
            stmt.setString(1, keyWord);
            //+++++++++++++++++++++++++++++++++++++++    
            
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                String tmp = rs.getString(1);
                if (tmp == null || tmp.equals("")) {
                    continue;
                }
                list.add("#" + tmp);
                dataCnt++;
                if (dataCnt > 500) {
                    return;
                }
                getSearchLoop(tmp);
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
        } catch(Exception ex) { 
            //System.out.println("error exception2:"+ex); 
            ex.printStackTrace();
        } finally { 
            //42.비어있는 if문 : Update by YOUNGJUN,CHO
            if(rs!=null)   { 
                try { 
                    rs.close();	
                    rs=null; 
                } catch (SQLException sqlex) {
                    sqlex.printStackTrace();
                } catch(Exception ex){ 
                    //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO
                    ex.printStackTrace();
                    //++++++++++++++++++++++++++++++++++++++++++++++++
                } 
            }
            if(stmt!=null) { 
                try { 
                    stmt.close(); 
                    stmt=null; 
                } catch (SQLException sqlex) {
                    sqlex.printStackTrace();    
                } catch(Exception ex){ 
                    //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO
                    ex.printStackTrace();
                    //++++++++++++++++++++++++++++++++++++++++++++++++
                } 
            } 
            if(con!=null)  { 
                try { 
                    con.close(); 
                    con=null; 
                } catch (SQLException sqlex) {
                    sqlex.printStackTrace();
                } catch(Exception ex) { 
                    //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO
                    ex.printStackTrace();
                    //++++++++++++++++++++++++++++++++++++++++++++++++
                } 
            }
            //================================================
        }
        //================================================
    }

    public void getSearchLoop(String tmpp) {
        if(dataCnt>500) {
            return;
        }

        //44.적절하지 않은 예외처리(광범위예외클래스)_CWE-754 : Update by YOUNGJUN,CHO
        PreparedStatement stmt = null;
        String sql = null;
        ResultSet rs = null;
        
        try {
            rs = null;
            stmt = null;

            /*String sql="SELECT UPPER_DEPT FROM MOUMI_DEPT WHERE SEL_TYPE='Y' AND ID='"+tmpp+"' ";
             sql+="AND (UPPER_DEPT IS NOT NULL AND UPPER_DEPT<>'"+tmpp+"')";*/
            //17.SQL 삽입(Database)_CWE-89 : Update by KWON.HAN
          //sql = "SELECT UPPER_DEPT FROM MOUMI_DEPT WHERE ID='" + tmpp + "' ";
          //sql += "AND (UPPER_DEPT IS NOT NULL AND UPPER_DEPT<>'" + tmpp + "')";            
            sql = "SELECT UPPER_DEPT FROM MOUMI_DEPT WHERE ID= " + "?" + " ";
            sql += "AND (UPPER_DEPT IS NOT NULL AND UPPER_DEPT <>  " + "?" + " ) ";
            //=================================================
            
            stmt = con.prepareStatement(sql);
            
            //17.SQL 삽입(Database)_CWE-89 : Add by KWON,HAN
            //18.SQL 삽입(메소드파라미터)_CWE-89 
            stmt.setString(1, tmpp);
            stmt.setString(2, tmpp);
            //+++++++++++++++++++++++++++++++++++++++            
            
            rs = stmt.executeQuery();
            if (rs.next()) {
                String tmp1 = rs.getString(1);                
                if (tmp1 == null || tmp1.equals("")) {
                    return;
                }
                list.add("#" + tmp1);
                dataCnt++;
                getSearchLoop(tmp1);
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
        } catch(Exception ex) { 
            ex.printStackTrace();
        } finally { 
            //42.비어있는 if문 : Update by YOUNGJUN,CHO
            if(rs!=null)   { 
                try { 
                    rs.close();	
                    rs=null; 
                } catch (SQLException sqlex) {
                    sqlex.printStackTrace();
                } catch(Exception ex){ 
                    //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO
                    ex.printStackTrace();
                    //++++++++++++++++++++++++++++++++++++++++++++++++
                } 
            }

            if(stmt!=null) { 
                try { 
                    stmt.close(); 
                    stmt=null; 
                } catch (SQLException sqlex) {
                    sqlex.printStackTrace();    
                } catch(Exception ex){ 
                    //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO
                    ex.printStackTrace();
                    //++++++++++++++++++++++++++++++++++++++++++++++++
                } 
            }
            //================================================
        }
        //================================================
    }
}