/**
 * @(#)file    FileManager.java
 * @(#)author  chj
 * @(#)version 1.0
 * @(#)date    2014-06-24
 * @(#)since   JDK1.6
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.common.file;

import java.io.File;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Random;

import utils.system;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
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 com.udapsoft.waf.common.util.FileUtil;

/**
 * 첨부파일 관련 공통 모듈 
 * 
 */
public class FileManager {

	public FileManager() {

    }
	
    /**
     * 첨부파일 목록을 TextArea에 넣을수 있는 Text로 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @return
	 */
	public static String getFileListForTextarea(String fileGB, String ID) {

        return getFileListForTextarea(fileGB, ID, null, null);
    }

    /**
     * 첨부파일 목록을 TextArea에 넣을수 있는 Text로 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @return
	 */
	public static String getFileListForTextarea(String fileGB, String ID, String openCorpYN) {

        return getFileListForTextarea(fileGB, ID, openCorpYN, null);
    }

    /**
     * 첨부파일 목록을 TextArea에 넣을수 있는 Text로 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @param openCorpYN
	 * @return
	 */
	public static String getFileListForTextarea(String fileGB, String ID, String openCorpYN, String useConYN) {

        RowSet rowSet = getFileList(fileGB, ID, openCorpYN, useConYN);

        String str = "";
        for( int i = 0 ; i < rowSet.size() ; i++ ) {

            str += (i + 1) + " : " ;

            if( openCorpYN != null && openCorpYN.equals("ALL") )
                str += "업체공개[" + rowSet.getRow(i).get("OPEN_CORP_YN") + "]  ";  

            if( useConYN != null && useConYN.equals("ALL") )
                str += "계약사용[" + rowSet.getRow(i).get("USE_CON_YN") + "]  ";  

            str += rowSet.getRow(i).get("FILE_NAME") 
                 + " (" + rowSet.getRow(i).get("FILE_SIZE").trim() + ") "
                 + "\n";
        }

        return str;
    }

	public static String getFileListForDiv(String fileGB, String ID){
		RowSet rowSet = getFileList(fileGB, ID);
		String str = "<ul>\n";
		for( int i = 0 ; i < rowSet.size() ; i++ ) {
			str += "<li><a href=\"javascript:fileDown('"+ rowSet.getRow(i).get("FILE_ID") + "');\">";
			str += (i + 1) + " : " ;
			str += rowSet.getRow(i).get("FILE_NAME") + " ( " + rowSet.getRow(i).get("FILE_SIZE").trim() + " ) " ;
			str += "</a></li>\n" ;
		}
		str += "</ul>\n";
		return str;
	}
	
	public static String getFileListForSpan(String fileGB, String ID){
		RowSet rowSet = getFileList(fileGB, ID);
		String str = "<ul style='margin-left:5mm;'>\n";
		for( int i = 0 ; i < rowSet.size() ; i++ ) {
			str += "<li><span onclick=\"fileDown('"+ rowSet.getRow(i).get("FILE_ID") + "');\" class=\"button\" style=\"color:#4b70fd;text-decoration:underline;\">";
			str += (i + 1) + " : " ;
			str += rowSet.getRow(i).get("FILE_NAME") + " ( " + rowSet.getRow(i).get("FILE_SIZE").trim() + " ) " ;
			str += "</span></li>\n" ;
		}
		str += "</ul>\n";
		return str;
	}
	
    /**
     * 첨부파일 목록을 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @return
	 */
	public static RowSet getFileList(String fileGB, String ID) {

        return getFileList(fileGB, ID, null, null);
    }

    /**
     * 첨부파일 목록을 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @param openCorpYN
	 * @return
	 */
	public static RowSet getFileList(String fileGB, String ID, String openCorpYN) {

        return getFileList(fileGB, ID, openCorpYN, null);
    }

    /**
     * 첨부파일 목록을 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @param openCorpYN
	 * @return
	 */
	public static RowSet getFileList(String fileGB, String ID, String openCorpYN, String useConYN) {

        return getFileList(fileGB, ID, openCorpYN, useConYN, null);
    }

    /**
     * 첨부파일 목록을 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @param openCorpYN
	 * @param useConYN
	 * @param addGB
	 * @return
	 */
	public static RowSet getFileList(String fileGB, String ID, String openCorpYN, String useConYN, String addGB) {

        return getFileList(fileGB, ID, openCorpYN, useConYN, addGB, null);
        
        //수정 2004.08.02
        //FileManager fileManager = new FileManager();
        //return fileManager.getFileListRowSet(fileGB, ID, openCorpYN, useConYN, addGB);
    }
    /**
     * 첨부파일 목록을 리턴한다.
     * @param fileGB
     * @param ID
     * @param openCorpYN
     * @param useConYN
     * @param addGB
     * @param modNo
     * @return
     */
    public static RowSet getFileList(String fileGB, String ID, String openCorpYN, String useConYN, String addGB, String modNo) {
        return getFileList(fileGB, ID, openCorpYN, useConYN, addGB, null, modNo);
        
        //수정 2005.03.23
        //FileManager fileManager = new FileManager();
        //return fileManager.getFileListRowSet(fileGB, ID, openCorpYN, useConYN, addGB, modNo);
    }
    
    /**
     * 첨부파일 목록을 리턴한다.
     * @param fileGB
     * @param ID
     * @param openCorpYN
     * @param useConYN
     * @param addGB
     * @param modNo
     * @return
     */
    public static RowSet getFileList(String fileGB, String ID, String openCorpYN, String useConYN, String addGB,  String isTechreq, String modNo) {

        //FileManager fileManager = new FileManager();
        //return fileManager.getFileListRowSet(fileGB, ID, openCorpYN, useConYN, addGB, modNo);
        FileManager fileManager = new FileManager();
        return fileManager.getFileListRowSet(fileGB, ID, openCorpYN, useConYN, addGB, isTechreq, modNo);
    }
    
    /**
     * 첨부파일 목록을 리턴한다.
     * @param fileGB
     * @param ID
     * @param openCorpYN
     * @param useConYN
     * @param addGB
     * @param modNo
     * @return
     */
/*    
    public static RowSet getFileList(String fileGB, String ID, String openCorpYN, String useConYN, String addGB, String modNo) {

        FileManager fileManager = new FileManager();
        return fileManager.getFileListRowSet(fileGB, ID, openCorpYN, useConYN, addGB, modNo);
    }
*/    
    /**
     * 첨부파일 맵을 복사해 다른 문서에 연결해서 복사
	 * @param connection
	 * @param srcFileGB
	 * @param srcID
	 * @param targetFileGB
	 * @param targetID
	 * @return
	 * @throws Exception
	 */
	public static boolean copyFileList(Connection connection, String srcFileGB, String srcID, 
                                              String targetFileGB, String targetID) throws Exception {

        return copyFileList(connection, srcFileGB, srcID, targetFileGB, targetID, null);
    }
    /**
     * 첨부파일 맵을 복사해 다른 문서에 연결해서 복사
	 * @param connection
	 * @param srcFileGB
	 * @param srcID
	 * @param targetFileGB
	 * @param targetID
	 * @return
	 * @throws Exception
	 */
	public static boolean copyFileInfo(Connection connection, String srcMapID, 
                                              String targetFileGB, String targetID) throws Exception {

        return copyFileInfo(connection, srcMapID, targetFileGB, targetID, null);
    }

    /**
     * 첨부파일 맵을 복사해 다른 문서에 연결해서 복사
	 * @param connection
	 * @param srcFileGB
	 * @param srcID
	 * @param targetFileGB
	 * @param targetID
	 * @param openCorpYN
	 * @return
	 * @throws Exception
	 */
	public static boolean copyFileList(Connection connection, String srcFileGB, String srcID, 
                                          String targetFileGB, String targetID, String openCorpYN) throws Exception {
        FileManager fileManager = new FileManager();
        return  fileManager.copyFileData(connection, srcFileGB, srcID, targetFileGB, targetID, openCorpYN);
    }

    /**
	 * 첨부파일 맵을 복사해 다른 문서에 연결해서 복사
	 * 
	 * @param connection
	 * @param srcMapID
	 * @param targetFileGB
	 * @param targetID
	 * @param openCorpYN
	 * @return
	 * @throws Exception
	 */
	public static boolean copyFileInfo(Connection connection, String srcMapID,
			String targetFileGB, String targetID, String openCorpYN)
			throws Exception {
        FileManager fileManager = new FileManager();
        return  fileManager.copyFileInfoData(connection, srcMapID, targetFileGB, targetID, openCorpYN);
    }

    /**
     * 파일정보를 리턴한다.
     * @param fileID
     * @return
     */
    public static ValueObject getFileInfo(String fileID) {

        FileManager fileManager = new FileManager();
        return fileManager.getFileValueObject(fileID);
    }

    /**
     * 첨부파일의 File 객체를 List 객체에 담아서 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @return
	 */
	public static List getFiles(Connection connection, String fileGB, String ID) {

        FileManager fileManager = new FileManager();
        return fileManager.getFilesInArrayList(connection, fileGB, ID);
    }


    /**
     * 변경차수반영(수정2004.8.02) 첨부파일 목록을 리턴한다.
     * @param fileGB
     * @param ID
     * @param openCorpYN
     * @return
     */
    private RowSet getFileListRowSet(String fileGB, String ID, String openCorpYN, String useConYN, String addGB, String isTechreq, String modNo) {

        Search search = new SearchImpl();
        RowSet rowSet = null;
        String sqlstr = "";
        try {

            sqlstr = " SELECT   B.FILE_ID,          \n"
                   + "          B.FILE_NAME,        \n"
                   + "          B.REAL_FILE_NAME,   \n"
                   + "          TRIM(               \n"
                   + "          CASE                \n"
                   + "          WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte' \n"
                   + "          ELSE \n"
                   + "               CASE "
                   + "               WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte' \n"
                   + "               ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte' \n"
                   + "               END                                                \n"
                   + "          END ) AS FILE_SIZE,                                     \n"
                   + "          B.CRE_DATE AS CRE_DATE,                                 \n"
                   + "          A.FILE_MAP_ID, A.FILE_GB, A.ID, A.OPEN_CORP_YN,         \n"
                   + "          A.O_FILE_GB, O_ID, USE_CON_YN, TECHREQ_OPEN_YN,         \n"
                   + "          A.CRE_BY, A.UPD_BY, B.FILE_EXT,B.CONTENT_TYPE,	        \n"
                   + "          B.FILE_PATH   \n"
                   + " FROM     PUB0510T A , \n"
                   + "          PUB0500T B \n"
                   + " WHERE    A.FILE_ID = B.FILE_ID \n"
                   + " AND      A.FILE_GB = ? \n"
                   + " AND      A.ID      = NVL(?,A.ID)  \n";

            if( openCorpYN != null && !openCorpYN.equalsIgnoreCase("") && !openCorpYN.equalsIgnoreCase("ALL") ) {

                   sqlstr += " AND      A.OPEN_CORP_YN = '" + openCorpYN + "' \n";
            }          

            if( useConYN != null && !useConYN.equalsIgnoreCase("") && !useConYN.equalsIgnoreCase("ALL") ) {

                   sqlstr += " AND      A.USE_CON_YN = '" + useConYN + "' \n";
            }        

            if( isTechreq != null && !isTechreq.equals("") && !isTechreq.equalsIgnoreCase("ALL") ) {//기술검토의뢰 할때 보여지는 파일

                   sqlstr += " AND      A.TECHREQ_OPEN_YN = '"  + isTechreq + "'           \n";
            } 

            if( addGB == null || addGB.equalsIgnoreCase("") ) {

                addGB = "F";
            }

            if( addGB != null && !addGB.equalsIgnoreCase("") && !addGB.equalsIgnoreCase("ALL") ) {

                   sqlstr += " AND      A.ADD_GB = '" + addGB + "' \n";
            }
            
            //추가 2004.08.02
            if( (fileGB.equals("PUR_M_CST")||fileGB.equals("SUB_M_CST")) && !modNo.equals("") ) {

                   sqlstr += " AND      A.MOD_NO = " + modNo + " \n";
            }

            sqlstr += " ORDER BY A.FILE_ID \n";

            search.setStatement(sqlstr);
            search.addParameter(fileGB);
            search.addParameter(ID);
            WAFLogger.debug("=========FileManager::::첨부파일 목록 start==========");
            WAFLogger.debug(sqlstr);
            WAFLogger.debug(search.getParameters());
            WAFLogger.debug("=========FileManager::::첨부파일 목록 end==========");
            rowSet = search.execute();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileListRowSet() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
        }
        finally {

            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }

    /**
     * 변경차수반영(수정2004.8.02) 첨부파일 목록을 리턴한다.
     * @param fileGB
     * @param ID
     * @param openCorpYN
     * @return
     */
    private RowSet getFileListRowSet1(String fileGB, String ID, String openCorpYN, String useConYN, String addGB, String isTechreq, String modNo) {

        Search search = new SearchImpl();
        RowSet rowSet = null;
        String sqlstr = "";
        try {

            sqlstr = " SELECT   B.FILE_ID,          \n"
                   + "          B.FILE_NAME,        \n"
                   + "          B.REAL_FILE_NAME,   \n"
                   + "          B.FILE_SIZE AS FILE_SIZE,                                     \n"
                   + "          B.CRE_DATE AS CRE_DATE,                                 \n"
                   + "          A.FILE_MAP_ID, A.FILE_GB, A.ID, A.OPEN_CORP_YN,         \n"
                   + "          A.O_FILE_GB, O_ID, USE_CON_YN, TECHREQ_OPEN_YN,         \n"
                   + "          A.CRE_BY, A.UPD_BY, B.FILE_EXT,B.CONTENT_TYPE	        \n"
                   + " FROM     PUB0510T A , \n"
                   + "          PUB0500T B \n"
                   + " WHERE    A.FILE_ID = B.FILE_ID \n"
                   + " AND      A.FILE_GB = ? \n"
                   + " AND      A.ID      = ? \n";

            if( openCorpYN != null && !openCorpYN.equalsIgnoreCase("") && !openCorpYN.equalsIgnoreCase("ALL") ) {

                   sqlstr += " AND      A.OPEN_CORP_YN = '" + openCorpYN + "' \n";
            }          

            if( useConYN != null && !useConYN.equalsIgnoreCase("") && !useConYN.equalsIgnoreCase("ALL") ) {

                   sqlstr += " AND      A.USE_CON_YN = '" + useConYN + "' \n";
            }        

            if( isTechreq != null && !isTechreq.equals("") && !isTechreq.equalsIgnoreCase("ALL") ) {//기술검토의뢰 할때 보여지는 파일

                   sqlstr += " AND      A.TECHREQ_OPEN_YN = '"  + isTechreq + "'           \n";
            } 

            if( addGB == null || addGB.equalsIgnoreCase("") ) {

                addGB = "F";
            }

            if( addGB != null && !addGB.equalsIgnoreCase("") && !addGB.equalsIgnoreCase("ALL") ) {

                   sqlstr += " AND      A.ADD_GB = '" + addGB + "' \n";
            }
            
            //추가 2004.08.02
            if( (fileGB.equals("PUR_M_CST")||fileGB.equals("SUB_M_CST")) && !modNo.equals("") ) {

                   sqlstr += " AND      A.MOD_NO = " + modNo + " \n";
            }

            sqlstr += " ORDER BY A.FILE_ID \n";

            search.setStatement(sqlstr);
            search.addParameter(fileGB);
            search.addParameter(ID);
            WAFLogger.debug("=========FileManager::::첨부파일 목록 start==========");
            WAFLogger.debug(sqlstr);
            WAFLogger.debug(search.getParameters());
            WAFLogger.debug("=========FileManager::::첨부파일 목록 end==========");
            rowSet = search.execute();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileListRowSet() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
        }
        finally {

            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    /**
     * 첨부파일의 File 객체를 List 객체에 담아서 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @return
	 */
	private List getFilesInArrayList(Connection connection, String fileGB, String ID) {

        Persistent persistent = new PersistentImpl(connection);
        List files = new ArrayList();
        List fileName = new ArrayList();
        String sqlstr = "";
        try {

            sqlstr = " SELECT A.*                   "
                   + " FROM   PUB0500T A,           "
                   + "        PUB0510T B            "
                   + " WHERE  A.FILE_ID = B.FILE_ID "
                   + " AND    B.FILE_GB = ?         "
                   + " AND    B.ID = ?              " 
                   + " ORDER BY B.FILE_ID ";
            persistent.setStatement(sqlstr);
            persistent.addParameter(fileGB);
            persistent.addParameter(ID);
            RowSet rowSet = persistent.query();

            for( int i = 0 ; i < rowSet.size() ; i++ ) {

                String path = StringUtil.replace(WAFConfig.get("waf.repository.path") + rowSet.getRow(i).get("FILE_PATH"), "/", File.separator);

                File file = new File(path, rowSet.getRow(i).get("REAL_FILE_NAME"));
                
                files.add(i, file);
            }
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileListRowSet() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.getStatement());
            WAFLogger.error(e);
        }
        finally {

            if( files == null ) files = new ArrayList();
            return files;
        }
    }

	/**
     * 첨부파일의 클라이언트에서 업로드했던 파일명을 List 객체에 담아서 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @return
	 */
	public static List getFileNamesInArrayList(Connection connection, String fileGB, String ID) {

        Persistent persistent = new PersistentImpl(connection);
        List files = new ArrayList();
        List fileName = new ArrayList();
        String sqlstr = "";
        try {

            sqlstr = " SELECT A.*                   "
                   + " FROM   PUB0500T A,           "
                   + "        PUB0510T B            "
                   + " WHERE  A.FILE_ID = B.FILE_ID "
                   + " AND    B.FILE_GB = ?         "
                   + " AND    B.ID = ?              " ;
            persistent.setStatement(sqlstr);
            persistent.addParameter(fileGB);
            persistent.addParameter(ID);
            RowSet rowSet = persistent.query();

            for( int i = 0 ; i < rowSet.size() ; i++ ) {

                String path = StringUtil.replace(WAFConfig.get("waf.repository.path") + rowSet.getRow(i).get("FILE_PATH"), "/", File.separator);

                File file = new File(path, rowSet.getRow(i).get("REAL_FILE_NAME"));
                
                files.add(i, file);
            }
        }
        catch(Exception e) {

            WAFLogger.error(" getFileListRowSet() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.getStatement());
            WAFLogger.error(e);
        }
        finally {

            if( files == null ) files = new ArrayList();
            return files;
        }
    }

    /**
     * 첨부파일 맵을 복사해 다른 문서에 연결해서 복사
	 * @param connection
	 * @param srcFileGB
	 * @param srcID
	 * @param targetFileGB
	 * @param targetID
	 * @param openCorpYN
	 * @return
	 * @throws Exception
	 */
	private boolean copyFileData(Connection connection, String srcFileGB, String srcID, 
                                   String targetFileGB, String targetID, String openCorpYN) throws Exception {
        String sqlstr = "";
        try {

            Persistent persistent = new PersistentImpl(connection);

            sqlstr += " INSERT INTO PUB0510T(      \n"
                    + "        FILE_MAP_ID,        \n"
                    + "        FILE_ID,            \n"
                    + "        FILE_GB,            \n"
                    + "        ID,                 \n"
                    + "        OPEN_CORP_YN,       \n"
                    + "        ADD_GB,             \n"
                    + "        CRE_BY,             \n"
                    + "        CRE_DATE )          \n"
                    + " SELECT PUB0510T_S.NEXTVAL, \n"
                    + "        FILE_ID,            \n"
                    + "        ?,                  \n"
                    + "        ?,                  \n"
                    + "        OPEN_CORP_YN,       \n"
                    + "        ADD_GB,             \n"
                    + "        CRE_BY,             \n"
                    + "        CRE_DATE            \n"
                    + " FROM   PUB0510T            \n"
                    + " WHERE  FILE_GB      = ?    \n"
                    + " AND    ID           = ?    \n";

            if( openCorpYN != null && !openCorpYN.equals("") )
                sqlstr += " AND    OPEN_CORP_YN = " + openCorpYN + " \n";

            sqlstr += " AND    FILE_ID NOT IN ( SELECT FILE_ID      \n"
                    + "                         FROM   PUB0510T     \n"
                    + "                         WHERE  FILE_GB = ?  \n"
                    + "                         AND    ID      = ?) \n" ;

            persistent.setStatement(sqlstr);
            persistent.addParameter(targetFileGB);
            persistent.addParameter(targetID);
            persistent.addParameter(srcFileGB);
            persistent.addParameter(srcID);
            persistent.addParameter(targetFileGB);
            persistent.addParameter(targetID);

            persistent.execute();

            WAFLogger.debug("견적의뢰서작성:협력업체 선정요청서를 견적의뢰서로 파일목록 복사 :"+persistent.getStatement());
            WAFLogger.debug("parameter :"+ persistent.getParameters());

            return true;
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> copyFileData() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

    /**
	 * 첨부파일 맵을 복사해 다른 문서에 연결해서 복사
	 * 
	 * @param connection
	 * @param srcMapID
	 * @param targetFileGB
	 * @param targetID
	 * @param openCorpYN
	 * @return
	 * @throws Exception
	 */
	private boolean copyFileInfoData(Connection connection, String srcMapID,
			String targetFileGB, String targetID, String openCorpYN)
			throws Exception {
		
        String sqlstr = "";
        
        try {

            Persistent persistent = new PersistentImpl(connection);

            sqlstr += " INSERT INTO PUB0510T(      \n"
                    + "        FILE_MAP_ID,        \n"
                    + "        FILE_ID,            \n"
                    + "        FILE_GB,            \n"
                    + "        ID,                 \n"
                    + "        OPEN_CORP_YN,       \n"
                    + "        ADD_GB,             \n"
                    + "        CRE_BY,             \n"
                    + "        CRE_DATE )          \n"
                    + " SELECT PUB0510T_S.NEXTVAL, \n"
                    + "        FILE_ID,            \n"
                    + "        ?,                  \n"
                    + "        ?,                  \n"
                    + "        OPEN_CORP_YN,       \n"
                    + "        ADD_GB,             \n"
                    + "        CRE_BY,             \n"
                    + "        CRE_DATE            \n"
                    + " FROM   PUB0510T            \n"
                    + " WHERE  FILE_MAP_ID  = ?    \n";

            if( openCorpYN != null && !openCorpYN.equals("") )
                sqlstr += " AND    OPEN_CORP_YN = " + openCorpYN + " \n";

            sqlstr += " AND    FILE_ID NOT IN ( SELECT FILE_ID      \n"
                    + "                         FROM   PUB0510T     \n"
                    + "                         WHERE  FILE_GB = ?  \n"
                    + "                         AND    ID      = ?) \n" ;

            persistent.setStatement(sqlstr);
            persistent.addParameter(targetFileGB);
            persistent.addParameter(targetID);
            persistent.addParameter(srcMapID);
            persistent.addParameter(targetFileGB);
            persistent.addParameter(targetID);

            persistent.execute();

            WAFLogger.debug("견적의뢰서작성:협력업체 선정요청서를 견적의뢰서로 파일목록 복사 :"+persistent.getStatement());
            WAFLogger.debug("parameter :"+ persistent.getParameters());

            return true;
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> copyFileInfoData() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

    /**
     * 파일정보를 리턴한다.
	 * @param fileID
	 * @return
	 */
	private ValueObject getFileValueObject(String fileID) {

        Search search = new SearchImpl();
        ValueObject valueObject = null;
        String sqlstr = "";
        try {

            sqlstr = " SELECT *           "
                   + " FROM   PUB0500T    " 
                   + " WHERE  FILE_ID = ? " ;

            search.setStatement(sqlstr);
            search.addParameter(fileID);
            valueObject = RowSetUtility.getValueObject(search.execute().getRow(0));
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileValueObject() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
        }
        finally {

            if( valueObject == null ) valueObject = new ValueObject();
            return valueObject;
        }
    }



    /**
     * 해당 파일구분과 ID에 해당하는 파일맵을 삭제하고 연결이 끝어진 파일 정보를 삭제한다.
	 * @param connection
	 * @param fileMapID
	 * @return boolean
	 * @throws Exception
	 */
	public boolean delFileMapInfo(Connection connection, String fileMapID) throws Exception {
        RowSet fileIDrowSet = getFileIDRowSet(connection, fileMapID);
        if( fileIDrowSet.size() == 0 )
            return true;
        delFileMap(connection, fileMapID);
        delDisConnectFileProc(connection, fileIDrowSet);
        return true;
    }

    /**
     * 해당 파일구분과 ID에 해당하는 파일맵을 삭제하고 연결이 끝어진 파일 정보를 삭제한다.
	 * @param connection
	 * @param fileGB
	 * @param ID
	 * @return
	 * @throws Exception
	 */
	public boolean delFileMapProc(Connection connection, String fileGB, String ID) throws Exception {

        RowSet fileIDrowSet = getFileIDRowSet(connection, fileGB, ID);

        if( fileIDrowSet.size() == 0 )
            return true;

        delFileMap(connection, fileGB, ID);

        delDisConnectFileProc(connection, fileIDrowSet);

        return true;
    }
	
	/**
	 * 해당 파일구분과 ID에 해당하는 파일맵을 삭제하고 연결이 끝어진 파일 정보를 삭제한다.
	 * @param connection
	 * @param fileGB
	 * @param ID
	 * @return
	 * @throws Exception
	 */
	public boolean delFileMapProcNew(Connection connection, String fileGB) throws Exception {
		
		RowSet fileIDrowSet = getFileIDRowSetNew(connection, fileGB);
		
		if( fileIDrowSet.size() == 0 )
			return true;
		
		delFileMapNew(connection, fileGB);
		
		delDisConnectFileProc(connection, fileIDrowSet);
		
		return true;
	}

	
	
	
    /**
     * 해당 파일구분과 ID에 해당하는 파일 ID를 리턴한다.
	 * @param connection
	 * @param fileGB
	 * @param ID
	 * @return
	 * @throws Exception
	 */
	public RowSet getFileIDRowSet(Connection connection, String fileGB, String ID) throws Exception {

        String sqlstr = "";
        try {

            Persistent persistent = new PersistentImpl(connection);

            sqlstr += " SELECT FILE_ID     \n"
                    + " FROM   PUB0510T    \n"
                    + " WHERE  FILE_GB = ? \n"
                    + " AND    ID      = ? \n" ;

            persistent.setStatement(sqlstr);
            persistent.addParameter(fileGB);
            persistent.addParameter(ID);

            WAFLogger.debug("구매신청서 조회: 파일삭제 할 ID얻기 :"+persistent.getStatement());
            WAFLogger.debug("parameter :"+ persistent.getParameters());

            return persistent.query();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileIDRowSet() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

    /**
     * 해당 파일구분과 ID에 해당하는 파일 ID를 리턴한다.
	 * @param connection
	 * @param fileGB
	 * @param ID
	 * @return
	 * @throws Exception
	 */
	public RowSet getFileIDRowSetNew(Connection connection, String fileGB) throws Exception {

        String sqlstr = "";
        try {
            Persistent persistent = new PersistentImpl(connection);

            sqlstr += " SELECT FILE_ID     \n"
                    + " FROM   PUB0510T    \n"
                    + " WHERE  FILE_GB = ? \n" ;
                    //+ " AND    ID      = ? \n" ;

            persistent.setStatement(sqlstr);
            persistent.addParameter(fileGB);
            //persistent.addParameter(ID);

            WAFLogger.debug(sqlstr);
            WAFLogger.debug(persistent.getParameters());

            return persistent.query();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileIDRowSet() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
    /**
	 * @param connection
	 * @param fileMapID
	 * @return RowSet
	 * @throws Exception
	 */
	public RowSet getFileIDRowSet(Connection connection, String fileMapID) throws Exception {
        String sqlstr = "";
        try {
            Persistent persistent = new PersistentImpl(connection);
            sqlstr += " SELECT FILE_ID         \n"
                    + " FROM   PUB0510T        \n"
                    + " WHERE  FILE_MAP_ID = ? \n" ;
            persistent.setStatement(sqlstr);
            persistent.addParameter(fileMapID);
            WAFLogger.debug("구매신청서 조회: 파일삭제 할 ID얻기 :"+persistent.getStatement());
            WAFLogger.debug("parameter :"+ persistent.getParameters());
            return persistent.query();
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getFileIDRowSet() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

    /**
     * 해당 문서의 첨부파일을 삭제한다.
     * @param connection
     * @param fileGB
     * @param ID
     * @return
     * @throws Exception
     */
    private boolean delFileMap(Connection connection, String fileGB, String ID) throws Exception {

        String sqlstr = "";
        try {

            Persistent persistent = new PersistentImpl(connection);

            sqlstr += " DELETE             \n"
                    + " FROM   PUB0510T    \n"
                    + " WHERE  FILE_GB = ? \n"
                    + " AND    ID      = ? \n" ;

            persistent.setStatement(sqlstr);
            persistent.addParameter(fileGB);
            persistent.addParameter(ID);

            persistent.execute();

            return true;
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> delFileMap() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

    /**
     * 해당 문서의 첨부파일을 삭제한다.
     * @param connection
     * @param fileGB
     * @param ID
     * @return
     * @throws Exception
     */
    private boolean delFileMapNew(Connection connection, String fileGB) throws Exception {

        String sqlstr = "";
        try {

            Persistent persistent = new PersistentImpl(connection);

            sqlstr += " DELETE             \n"
                    + " FROM   PUB0510T    \n"
                    + " WHERE  FILE_GB = ? \n" ;
                    //+ " AND    ID      = ? \n" ;

            persistent.setStatement(sqlstr);
            persistent.addParameter(fileGB);
            //persistent.addParameter(ID);

            persistent.execute();

            return true;
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> delFileMap() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
    
    /**
     * @param connection
     * @param fileMapID
     * @return boolean
     * @throws Exception
     */
    private boolean delFileMap(Connection connection, String fileMapID) throws Exception {
        String sqlstr = "";
        try {
            Persistent persistent = new PersistentImpl(connection);
            sqlstr += " DELETE                 \n"
                    + " FROM   PUB0510T        \n"
                    + " WHERE  FILE_MAP_ID = ? \n";
            persistent.setStatement(sqlstr);
            persistent.addParameter(fileMapID);
            persistent.execute();
            return true;
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> delFileMap() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

	/**
     * 파일맵과 연결이 끈어진 파일 정보를 삭제한다.
     * 같은 트랜젝션 안에서 파일맵을 삭제후에 본 Method를 Call 해야 한다.
	 * @param connection
	 * @param fileIDrowSet
	 * @return
	 * @throws Exception
	 */
	public boolean delDisConnectFileProc(Connection connection, RowSet fileIDrowSet) throws Exception {

        // 파일맵 삭제후 연결이 끈어진 파일정보의 목록을 리턴한다.
        RowSet delFileInfoRowSet = getFileRowSetForDel(connection, fileIDrowSet);

        if( delFileInfoRowSet.size() > 0 ) {

            // 파일맵과 연결이 끈어진 파일 정보 삭제
            delFile(connection, delFileInfoRowSet);
    
            // 실제 파일 삭제 처리
            deleteFile(delFileInfoRowSet);
        }

        return true;
    }

    /**
     * 연결이 끈어진 파일정보의 목록을 리턴한다.
	 * @param connection
	 * @param rowSet
	 * @return
	 * @throws Exception
	 */
	public RowSet getFileRowSetForDel(Connection connection, RowSet rowSet) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        String sqlstr = "";
        try {

            String fileIDS = "";
            for( int i = 0 ; i < rowSet.size() ; i++ ) {

                fileIDS += rowSet.getRow(i).get("FILE_ID");

                if( i < rowSet.size() - 1 ) {

                    fileIDS += " , ";
                }
            }

            sqlstr = " SELECT *                                           \n"
                   + " FROM   ( SELECT A.*, B.FILE_MAP_ID                 \n"
                   + "          FROM   PUB0500T A ,                       \n"
                   + "                 PUB0510T B                         \n"
                   + "          WHERE  A.FILE_ID = B.FILE_ID(+)           \n"
                   + "          AND    A.FILE_ID IN ( " + fileIDS + " ) ) \n"
                   + " WHERE  FILE_MAP_ID IS NULL                         \n" ;
            persistent.setStatement(sqlstr);
            
            WAFLogger.debug(sqlstr);
            
            return persistent.query();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileRowSetForDel() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

    /**
     * 파일맵과 연결이 끈어진 파일 정보 삭제
	 * @param connection
	 * @param delFileInfoRowSet
	 * @throws Exception
	 */
	public void delFile(Connection connection, RowSet delFileInfoRowSet) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        String sqlstr = "";
        try {

            String fileIDS = "";
            for( int i = 0 ; i < delFileInfoRowSet.size() ; i++ ) {

                fileIDS += delFileInfoRowSet.getRow(i).get("FILE_ID");

                if( i < delFileInfoRowSet.size() - 1 ) {

                    fileIDS += " , ";
                }
            }

            sqlstr = " DELETE "
                   + " FROM   PUB0500T "
                   + " WHERE  FILE_ID IN ( SELECT FILE_ID "
                   + "                     FROM   ( SELECT A.FILE_ID, B.FILE_MAP_ID "
                   + "                              FROM   PUB0500T A , "
                   + "                                     PUB0510T B "
                   + "                              WHERE  A.FILE_ID = B.FILE_ID(+) "
                   + "                              AND    A.FILE_ID IN ( " + fileIDS + " ) ) "
                   + "                     WHERE  FILE_MAP_ID IS NULL ) " ;
            persistent.setStatement(sqlstr);
            persistent.execute();

        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> delFile() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

    /**
     * 실제 파일 삭제 처리
	 * @param delFileInfoRowSet
	 * @throws Exception
	 */
	public void deleteFile(RowSet delFileInfoRowSet) throws Exception {

        // 실제 첨부파일 삭제
        String delFilePath = "";
        for( int i = 0 ; i < delFileInfoRowSet.size() ; i++ ) {

            delFilePath = StringUtil.replace(WAFConfig.get("waf.repository.path") + delFileInfoRowSet.getRow(i).get("FILE_PATH"), "/", File.separator);
            delFilePath = delFilePath + File.separator + delFileInfoRowSet.getRow(i).get("REAL_FILE_NAME");
            if( !FileUtil.delFile(delFilePath) ) {
            	
                WAFLogger.error("FILE_ID : " + delFilePath );
            }
        }
    }


    /**
     * 해당 파일구분과 ID에 해당하는 파일의 갯수를 리턴한다.
	 * @param fileGB
	 * @param ID
	 * @return int(파일갯수)
	 * @throws Exception
	 */
	public static RowSet getFileTotal(String fileGB, String ID) throws Exception {
        FileManager fileManager = new FileManager();
		return fileManager.getFileTotalList(fileGB, ID);
	}
    
	private RowSet getFileTotalList(String fileGB, String ID) throws Exception {
        Search search = new SearchImpl();
        RowSet rowSet = null;
        StringBuffer sqlstr = new StringBuffer();
        
        try {
        	sqlstr.append(" SELECT COUNT(*) CNT, FILE_GB, ID  \n");
        	sqlstr.append(" FROM   PUB0510T                   \n");
        	sqlstr.append(" WHERE  FILE_GB = ?                \n");
			if (ID != null && !ID.equals("")) {
				sqlstr.append(" AND    ID      = ?            \n");
			}
			sqlstr.append(" GROUP  BY FILE_GB, ID             \n");

			search.setStatement(sqlstr.toString());
            search.addParameter(fileGB);
            if(ID != null && !ID.equals("")){
                search.addParameter(ID);
            }
            rowSet = search.execute();
        } catch(Exception e) {
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
        } finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
	
	/**
     * 외주 자동계약파일(2021.5.31) 첨부파일 목록을 리턴한다.
     * @param SUB_CONT_MOD_ID
     * @return
     */
	public static RowSet getAutoFileOutList(String subContModId) {

        Search search = new SearchImpl();
        RowSet rowSet = null;
        String sqlstr = "";
        try {

            sqlstr = " SELECT   FILE_ID,          \n"
                   + "          FILE_NAME,        \n"
                   + "          UNIQUE_FILE_NAME,   \n"
                   + "          TRIM(               \n"
                   + "          CASE                \n"
                   + "          WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte' \n"
                   + "          ELSE \n"
                   + "               CASE "
                   + "               WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte' \n"
                   + "               ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte' \n"
                   + "               END                                                \n"
                   + "          END ) AS FILE_SIZE,                                     \n"
                   + "          CRE_DATE AS CRE_DATE,                                 \n"
                   + "          FILE_EXT,	        \n"
                   + "          FILE_PATH   \n"
                   + " FROM     CONT_OUT_FILE  \n"
                   + " WHERE    SUB_CONT_MOD_ID = ? \n";
            sqlstr += " ORDER BY FILE_ID \n";

            search.setStatement(sqlstr);
            search.addParameter(subContModId);
            WAFLogger.debug("=========FileManager::::첨부파일 목록 start==========");
            WAFLogger.debug(sqlstr);
            WAFLogger.debug(search.getParameters());
            WAFLogger.debug("=========FileManager::::첨부파일 목록 end==========");
            rowSet = search.execute();
        }
        catch(Exception e) {
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
        }
        finally {

            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
	
	/**
     * 구매 자동계약파일(2021.5.31) 첨부파일 목록을 리턴한다.
     * @param SUB_CONT_MOD_ID
     * @return
     */
	public static RowSet getAutoFilePurList(String purContModId) {

        Search search = new SearchImpl();
        RowSet rowSet = null;
        String sqlstr = "";
        try {

            sqlstr = " SELECT   FILE_ID,          \n"
                   + "          FILE_NAME,        \n"
                   + "          UNIQUE_FILE_NAME,   \n"
                   + "          TRIM(               \n"
                   + "          CASE                \n"
                   + "          WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte' \n"
                   + "          ELSE \n"
                   + "               CASE "
                   + "               WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte' \n"
                   + "               ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte' \n"
                   + "               END                                                \n"
                   + "          END ) AS FILE_SIZE,                                     \n"
                   + "          CRE_DATE AS CRE_DATE,                                 \n"
                   + "          FILE_EXT,	        \n"
                   + "          FILE_PATH   \n"
                   + " FROM     CONT_PUR_FILE  \n"
                   + " WHERE    PUR_CONT_MOD_ID = ? \n";
            sqlstr += " ORDER BY FILE_ID \n";

            search.setStatement(sqlstr);
            search.addParameter(purContModId);
            WAFLogger.debug("=========FileManager::::첨부파일 목록 start==========");
            WAFLogger.debug(sqlstr);
            WAFLogger.debug(search.getParameters());
            WAFLogger.debug("=========FileManager::::첨부파일 목록 end==========");
            rowSet = search.execute();
        }
        catch(Exception e) {
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
        }
        finally {

            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
}
