/**
 * @(#)file    PreRequestSignBiz.java
 * @(#)author  OK
 * @(#)version 1.0
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.payment.outside.biz;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.Row;
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.util.ValueObject;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;

import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;


/**
 * 
 * @version 1.0
 */
public class PayItemOutDashBoardPopBiz extends Biz {

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

    public void doBiz() throws Exception {
    	
        if(storage.getEvent().equals(""))
        {
            ValueObject vo = new ValueObject();
            RowSet DashList1 = null;
            RowSet DashList2 = null;
            RowSet DashList3 = null;
            RowSet DashList4 = null;
            
            DashList1 = getDashOutList1(params);
            DashList2 = getDashOutList2(params);
            DashList3 = getDashOutList3(params);
            DashList4 = getDashOutList4(params); //보증
            
            storage.addRowSet("DashList1", DashList1);
            storage.addRowSet("DashList2", DashList2);
            storage.addRowSet("DashList3", DashList3);
            storage.addRowSet("DashList4", DashList4);
        }
        else if(storage.getEvent().equals("GET_FILE_LIST"))
        {
            RowSet fileList = null;

            fileList = getTemplateFileOut(params.get("p_pur_cont_mod_id"));
            
            storage.addRowSet("fileList", fileList);
        }
   }
    
    
    public RowSet getTemplateFile(String pur_cont_cd){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            sql.append(" SELECT FILE_ID, UNIQUE_FILE_NAME, FILE_PATH, FILE_EXT,                                                                  		\n");
            sql.append("     TRIM(                                                                                                               		\n");
            sql.append("        CASE                                                                                                             		\n");
            sql.append("       WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte'                                               		\n");
            sql.append("       ELSE                                                                                                              		\n");
            sql.append("            CASE                WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte'		\n");
            sql.append("             ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte'                                    		\n");
            sql.append("            END                                                                                                          		\n");
            sql.append("      END ) AS FILE_SIZE, FILE_NAME                                                                                      		\n");
            sql.append(" FROM CONT_PUR_FILE                                                                                                      		\n");
            sql.append(" WHERE PUR_CONT_MOD_ID = ?                                                                                               		\n");
            sql.append(" ORDER BY FILE_ID ASC                                                                                                    		\n");
            
            search.addParameter(pur_cont_cd);
            
            WAFLogger.debug(sql);
            search.setStatement(sql);
            listRowSet = search.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{

            if( listRowSet == null ){
                listRowSet = new RowSetImpl();
            }
            
            return listRowSet;
        }
    }
    
    
    public RowSet getTemplateFileOut(String sub_cont_cd){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        
        try {
            sql.append(" SELECT FILE_ID, UNIQUE_FILE_NAME, FILE_PATH, FILE_EXT,                                                   		\n");
            sql.append("     TRIM(                                                                                                		\n");
            sql.append("        CASE                                                                                              		\n");
            sql.append("       WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte'                                		\n");
            sql.append("       ELSE                                                                                               		\n");
            sql.append("            CASE WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte'		\n");
            sql.append("             ELSE TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte'                      		\n");
            sql.append("            END                                                                                           		\n");
            sql.append("      END ) AS FILE_SIZE, FILE_NAME, SUB_CONT_MOD_ID                                                       		\n");
            sql.append(" FROM CONT_OUT_FILE                                                                                       		\n");
            sql.append(" WHERE SUB_CONT_MOD_ID = ?                                                                                		\n");
            sql.append(" ORDER BY FILE_ID ASC                                                                                     		\n");
            
            search.addParameter(sub_cont_cd);
            WAFLogger.debug(sql);
            search.setStatement(sql);
            listRowSet = search.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{

            if( listRowSet == null ){
                listRowSet = new RowSetImpl();
            }
            
            return listRowSet;
        }
    }
    
    
    
    /**
     * 첨부파일
     * @param filegb
     * @param id
     * @param returnid
     * @return
     */
    private ValueObject getfilelist(String filegb,String id,String returnid){
        ValueObject vo = new ValueObject();
        RowSet rowSet = FileManager.getFileList(filegb, id);
        String str="";
            for(int i=0;i<rowSet.size();i++){
                Row row=rowSet.getRow(i);
                str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
                if(i+1!=rowSet.size()){
                    str +="\r\n";
                }
            }
            vo.set(returnid, str);
        return vo;
    }
    
    private ValueObject getfilelistALL(String filegb,String id,String returnid){
        ValueObject vo = new ValueObject();
        RowSet rowSet = getFileListRowSet(filegb, id, null, null, null, null, null);
        String str="";
            for(int i=0;i<rowSet.size();i++){
                Row row=rowSet.getRow(i);
                str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
                if(i+1!=rowSet.size()){
                    str +="\r\n";
                }
            }
            vo.set(returnid, str);
        return vo;
    }
    
    
    private RowSet getFileListRowSet(String fileGB, String ID, String openCorpYN, String useConYN, String addGB, String isTechreq, String modNo) {

        Search search = new SearchImpl();
        RowSet rowSet = null;
        StringBuffer sql = new StringBuffer();
        try {
            sql.append(" SELECT   B.FILE_ID,                                                                                     		\n");
            sql.append("           B.FILE_NAME,                                                                                  		\n");
            sql.append("           B.REAL_FILE_NAME,                                                                             		\n");
            sql.append("           TRIM(                                                                                         		\n");
            sql.append("           CASE                                                                                          		\n");
            sql.append("           WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte'                           		\n");
            sql.append("           ELSE                                                                                          		\n");
            sql.append("                CASE                                                                                     		\n");
            sql.append("                WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte'		\n");
            sql.append("                ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte'                 		\n");
            sql.append("                END                                                                                      		\n");
            sql.append("           END ) AS FILE_SIZE,                                                                           		\n");
            sql.append("           B.CRE_DATE AS CRE_DATE,                                                                       		\n");
            sql.append("           A.FILE_MAP_ID, A.FILE_GB, A.ID, A.OPEN_CORP_YN,                                               		\n");
            sql.append("           A.O_FILE_GB, O_ID, USE_CON_YN, TECHREQ_OPEN_YN,                                               		\n");
            sql.append("           A.CRE_BY, A.UPD_BY, B.FILE_EXT,B.CONTENT_TYPE,                                                		\n");
            sql.append("           B.FILE_PATH                                                                                   		\n");
            sql.append("  FROM     PUB0510T A ,                                                                                  		\n");
            sql.append("           PUB0500T B                                                                                    		\n");
            sql.append("  WHERE    A.FILE_ID = B.FILE_ID                                                                         		\n");
            sql.append("  AND      A.FILE_GB = ?                                                                                 		\n");
            search.addParameter(fileGB);
            

            if( openCorpYN != null && !openCorpYN.equalsIgnoreCase("") && !openCorpYN.equalsIgnoreCase("ALL") ) {
                   sql.append("	AND      A.OPEN_CORP_YN = ?	\n");
                   search.addParameter(openCorpYN);
            }          

            if( useConYN != null && !useConYN.equalsIgnoreCase("") && !useConYN.equalsIgnoreCase("ALL") ) {
	            	sql.append("	AND      A.USE_CON_YN = ?	\n");
	                search.addParameter(useConYN);
            }        

            if( isTechreq != null && !isTechreq.equals("") && !isTechreq.equalsIgnoreCase("ALL") ) {//기술검토의뢰 할때 보여지는 파일
                   sql.append("	AND      A.TECHREQ_OPEN_YN = ?	\n");
	                search.addParameter(isTechreq);
            } 

            if( addGB == null || addGB.equalsIgnoreCase("") ) {
                addGB = "F";
            }

            if( addGB != null && !addGB.equalsIgnoreCase("") && !addGB.equalsIgnoreCase("ALL") ) {
	            	sql.append("	AND      A.ADD_GB = ?	\n");
	                search.addParameter(addGB);
            }
            
            //추가 2004.08.02
            if( (fileGB.equals("PUR_M_CST")||fileGB.equals("SUB_M_CST")) && !modNo.equals("") ) {
            	sql.append("	AND      A.MOD_NO = ?	\n");
                search.addParameter(modNo);
            }

            sql.append("	ORDER BY A.FILE_ID	\n");

            search.setStatement(sql);
            WAFLogger.debug("=========FileManager::::첨부파일 목록 start==========");
            WAFLogger.debug(sql);
            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;
        }
    }
    
    private RowSet getDashOutList1(ValueObject Params) throws Exception
    {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        
        try
        {           
        	sqlStr.append("             SELECT                                                                                                                                                                                                                                                                                                                          			\n");
            sqlStr.append("           DEPOSIT_NM                                                                                                                                                                                                                                                                                                                        			\n");
            sqlStr.append("          ,PREPAY_PROC_GB                                                                                                                                                                                                                                                                                                                    			\n");
            sqlStr.append("          ,STATUS_NM                                                                                                                                                                                                                                                                                                                         			\n");
            sqlStr.append("          ,PREPAY_ID                                                                                                                                                                                                                                                                                                                         			\n");
            sqlStr.append("          ,ACMP_ID                                                                                                                                                                                                                                                                                                                         			\n");
            sqlStr.append("          ,PUR_CONT_MOD_ID                                                                                                                                                                                                                                                                                                                   			\n");
            sqlStr.append("          ,SUB_CONT_ID                                                                                                                                                                                                                                                                                                                       			\n");
            sqlStr.append("          ,TAX_METHOD_CD                                                                                                                                                                                                                                                                                                                     			\n");
            sqlStr.append("          ,CONVERSATION_ID1                                                                                                                                                                                                                                                                                                                  			\n");
            sqlStr.append("          ,CONVERSATION_ID2                                                                                                                                                                                                                                                                                                                  			\n");
            sqlStr.append("          ,(SELECT COUNT(*) CNT  FROM  PUB0510T A,  PUB0500T B WHERE A.FILE_ID = B.FILE_ID AND A.FILE_GB = 'SUPP_PREPAYMENTS_GIVEUP'  AND   A.ADD_GB = 'F'  AND      A.ID      = NVL(PREPAY_ID, A.ID) )  FILE_NAME                                                                                                                           			\n");
            sqlStr.append("          ,(SELECT COUNT(*) CNT  FROM  PUB0510T A,  PUB0500T B WHERE A.FILE_ID = B.FILE_ID AND A.FILE_GB = 'RESOLUTION_' || ACMP_ID  AND   A.ADD_GB = 'F'  )  FILE_NAME                                                                                                                                                                    			\n");
//            sqlStr.append("          ,(SELECT COUNT(*) CNT  FROM  PUB0510T A,  PUB0500T B WHERE A.FILE_ID = B.FILE_ID AND A.FILE_GB = 'RESOLUTION_' || PREPAY_ID  AND   A.ADD_GB = 'F'  )  FILE_NAME                                                                                                                                                                    			\n");
            sqlStr.append("          ,WARR_GUBUN                                                                                                                                                                                                                                                                                                                        			\n");
            sqlStr.append("          ,WARR_GUBUN_NM                                                                                                                                                                                                                                                                                                                     			\n");
            sqlStr.append("          ,GUAR_ONOFF_YN                                                                                                                                                                                                                                                                                                                     			\n");
            sqlStr.append("          ,WARR_SEQ                                                                                                                                                                                                                                                                                                                          			\n");
            sqlStr.append("          ,CAUSECODE                                                                                                                                                                                                                                                                                                                         			\n");
            sqlStr.append("          ,GUAR_STATUS                                                                                                                                                                                                                                                                                                                       			\n");
            sqlStr.append("          ,WARR_GOFFICE_ID                                                                                                                                                                                                                                                                                                                   			\n");
            sqlStr.append("          ,GRTREQNO                                                                                                                                                                                                                                                                                                                          			\n");
            sqlStr.append("          ,MOD_NO                                                                                                                                                                                                                                                                                                                            			\n");
            sqlStr.append("          ,DOC_ID,YEAR                                                                                                                                                                                                                                                                                                                            			\n");
            sqlStr.append("             FROM (                                                                                                                                                                                                                                                                                                                          			\n");
            sqlStr.append("             --외주 선급금 리스트                                                                                                                                                                                                                                                                                                            			\n");
            sqlStr.append("               SELECT F_GET_PUR_STATUS ('1', 'PUR_PAY_ACMP_GB') AS DEPOSIT_NM                                                                                    \n");
            sqlStr.append("                      ,A.STATUS_CD AS PREPAY_PROC_GB                                  --선급진행상태                                                            \n");
            sqlStr.append("                      ,F_GET_CODE_NAME ('PREPAY_STATUS_CD', NVL (A.STATUS_CD, '00')) AS STATUS_NM                                                 --선급상태명  \n");
            sqlStr.append("                      ,A.PREPAY_ID AS PREPAY_ID                                        --선급내역D                                                              \n");
            sqlStr.append("                      ,0 AS  ACMP_ID                                                                                                     \n");
            sqlStr.append("                      ,A.SUB_CONT_MOD_ID AS PUR_CONT_MOD_ID                         -- 발주계약변경D                                                            \n");
            sqlStr.append("                     ,A.SUB_CONT_ID AS SUB_CONT_ID                                   -- 발주계약D                                                               \n");
            sqlStr.append("                     ,A.TAX_METHOD_CD AS TAX_METHOD_CD                            -- 정발행역발행구분                                                           \n");
            sqlStr.append("                     ,(SELECT MIN(A1.CONVERSATION_ID)                                                                                                           \n");
            sqlStr.append("                         FROM XXSB_DTI_MAIN@SMBLIF A1                                                                                                           \n");
            sqlStr.append("                                 ,ETEC_ERPIF.TAX_INT_INFO B1                                                                                                         \n");
            sqlStr.append("                         WHERE A1.CONVERSATION_ID = B1.CONVERSATION_ID                                                                                          \n");
            sqlStr.append("                             AND B1.PAY_DIV = 'O_PREPAY'                                                                                                        \n");
            sqlStr.append("                             AND B1.PAY_KEY = A.PREPAY_ID                                                                                                       \n");
            sqlStr.append("                             AND A1.DTI_TYPE = '01'                                                                                                             \n");
            sqlStr.append("                             ) CONVERSATION_ID1     --세금계산서D                                                                                               \n");
            sqlStr.append("                        ,(SELECT MIN(A1.CONVERSATION_ID)                                                                                                        \n");
            sqlStr.append("                         FROM XXSB_DTI_MAIN@SMBLIF A1                                                                                                           \n");
            sqlStr.append("                                 ,ETEC_ERPIF.TAX_INT_INFO B1                                                                                                         \n");
            sqlStr.append("                         WHERE A1.CONVERSATION_ID = B1.CONVERSATION_ID                                                                                          \n");
            sqlStr.append("                             AND B1.PAY_DIV = 'O_PREPAY'                                                                                                        \n");
            sqlStr.append("                             AND B1.PAY_KEY = A.PREPAY_ID                                                                                                       \n");
            sqlStr.append("                             AND A1.DTI_TYPE = '02'                                                                                                             \n");
            sqlStr.append("                             )  CONVERSATION_ID2   --계산서D                                                                                                    \n");
            sqlStr.append("                              ,'' AS FILE_NAME                                                  --포기각서                                                      \n");
            sqlStr.append("                              ,'' AS ATTCH_NAME                                                 --첨부문서                                                      \n");
            sqlStr.append("                              ,B.WARR_GUBUN                                                                                                                     \n");
            sqlStr.append("                              , F_GET_CODE_NAME ('GAUR_KIND', B.WARR_GUBUN) AS WARR_GUBUN_NM,                                                                   \n");
            sqlStr.append("                     B.GUAR_ONOFF_YN,                                                                                                                           \n");
            sqlStr.append("                     B.WARR_SEQ,                                                                                                                                \n");
            sqlStr.append("                     B.CAUSECODE,                                                                                                                               \n");
            sqlStr.append("                     B.GUAR_STATUS,                                                                                                                             \n");
            sqlStr.append("                      F_GET_CODE_NAME ('GUAROFFICEID', B.GUAR_ORGAN_CODE) AS WARR_GOFFICE_ID,                                                                   \n");
            sqlStr.append("                     B.GRTREQNO,                                                                                                                                \n");
            sqlStr.append("                     B.MOD_NO                                                                                                                                   \n");
            sqlStr.append("                     ,C.DOC_ID  ,C.YEAR                                                                                                                                \n");
            sqlStr.append("              FROM SUB1030T A                                                                                                                                   \n");
            sqlStr.append("              LEFT OUTER JOIN CONT_OUT_WARR B ON A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID AND B.WARR_GUBUN = '3'                                                   \n");
            sqlStr.append("              LEFT OUTER JOIN (SELECT KEY_COL2 AS PREPAY_ID, MAX (DOC_ID) AS DOC_ID, TO_CHAR(MAX(CRE_DATE), 'YYYYMMDD')  YEAR                                                                          \n");
            sqlStr.append("                          FROM SIGN001T                                                                                                                   \n");
            sqlStr.append("                         WHERE SIGN_CLASS_CD = 'ebid_oprpyD' AND SIGN_DECI_CLASS = '14'                                                                              \n");
            sqlStr.append("                      GROUP BY KEY_COL2) C ON A.PREPAY_ID = C.PREPAY_ID                                                                                         \n");
            sqlStr.append("              WHERE  A.PREPAY_AMT > 0                                                                                                                           \n");
            sqlStr.append("              AND A.STATUS_CD IN('10', '04', '11')                                                                                                                    \n");
            sqlStr.append("              UNION ALL                                                                                                                                                                                                                                                                                                                      			\n");
            sqlStr.append("              --외주 기성 리스트                                                                                                                                                                                                                                                                                                             			\n");
            sqlStr.append(" SELECT                                                                                                                                                                                                                                                                                                                                      			\n");
            sqlStr.append("                    F_GET_PUR_STATUS ( '2', 'PUR_PAY_ACMP_GB' )|| '(' ||   CC.ACMP_NO || '차)' DEPOSIT_NM   --선급금, 중도금 구분                                                                                                                                                                                                            			\n");
            sqlStr.append("                    , CC.STATUS_CD   AS PREPAY_PROC_GB                  --기성 진행상태                                                                                                                                                                                                                                                      			\n");
            sqlStr.append("                    , F_GET_CODE_NAME('ACMP_STATUS_CD', CC.STATUS_CD) AS STATUS_NM          --대금지행상태명                                                                                                                                                                                                                                 			\n");
            sqlStr.append("                    , 0                       AS PREPAY_ID                                                                                                                                                                                                                                               			\n");
            sqlStr.append("                    , CC.ACMP_ID               AS ACMP_ID                           --기성ID                                                                                                                                                                                                                                               			\n");
            sqlStr.append("                    , A.SUB_CONT_MOD_ID AS PUR_CONT_MOD_ID            -- 외주계약변경ID                                                                                                                                                                                                                                                      			\n");
            sqlStr.append("                    , CC.SUB_CONT_ID         AS SUB_CONT_ID                     -- 외주계약서ID                                                                                                                                                                                                                                              			\n");
            sqlStr.append("                    , CC.TAX_METHOD_CD   AS TAX_METHOD_CD   -- 정발행 역발행 구분                                                                                                                                                                                                                                                            			\n");
            sqlStr.append("                     ,(SELECT MIN(A1.CONVERSATION_ID)                                                                                                           \n");
            sqlStr.append("                         FROM XXSB_DTI_MAIN@SMBLIF A1                                                                                                           \n");
            sqlStr.append("                                 ,ETEC_ERPIF.TAX_INT_INFO B1                                                                                                         \n");
            sqlStr.append("                         WHERE A1.CONVERSATION_ID = B1.CONVERSATION_ID                                                                                          \n");
            sqlStr.append("                             AND B1.PAY_DIV = 'O_ACMP'                                                                                                        \n");
            sqlStr.append("                             AND B1.PAY_KEY = CC.ACMP_ID                                                                                                       \n");
            sqlStr.append("                             AND A1.DTI_TYPE = '01'                                                                                                             \n");
            sqlStr.append("                             ) CONVERSATION_ID1     --세금계산서D                                                                                               \n");
            sqlStr.append("                        ,(SELECT MIN(A1.CONVERSATION_ID)                                                                                                        \n");
            sqlStr.append("                         FROM XXSB_DTI_MAIN@SMBLIF A1                                                                                                           \n");
            sqlStr.append("                                 ,ETEC_ERPIF.TAX_INT_INFO B1                                                                                                         \n");
            sqlStr.append("                         WHERE A1.CONVERSATION_ID = B1.CONVERSATION_ID                                                                                          \n");
            sqlStr.append("                             AND B1.PAY_DIV = 'O_ACMP'                                                                                                        \n");
            sqlStr.append("                             AND B1.PAY_KEY = CC.ACMP_ID                                                                                                       \n");
            sqlStr.append("                             AND A1.DTI_TYPE = '02'                                                                                                             \n");
            sqlStr.append("                             )  CONVERSATION_ID2   --계산서D                                                                                                    \n");
            sqlStr.append("                    , ''                                AS  FILE_NAME        --포기각서                                                                                                                                                                                                                                                      			\n");
            sqlStr.append("                    , ''                                AS  ATTCH_NAME    --첨부문서                                                                                                                                                                                                                                                         			\n");
            sqlStr.append("                                     ,D.WARR_GUBUN                                                                                                                                                                                                                                                                                           			\n");
            sqlStr.append("                                     ,D.WARR_GUBUN_NM                                                                                                                                                                                                                                                                                        			\n");
            sqlStr.append("                                     ,D.GUAR_ONOFF_YN                                                                                                                                                                                                                                                                                        			\n");
            sqlStr.append("                                     ,D.WARR_SEQ                                                                                                                                                                                                                                                                                             			\n");
            sqlStr.append("                                     ,D.CAUSECODE                                                                                                                                                                                                                                                                                            			\n");
            sqlStr.append("                                     ,D.GUAR_STATUS                                                                                                                                                                                                                                                                                          			\n");
            sqlStr.append("                                     ,D.WARR_GOFFICE_ID                                                                                                                                                                                                                                                                                      			\n");
            sqlStr.append("                                     ,D.GRTREQNO                                                                                                                                                                                                                                                                                             			\n");
            sqlStr.append("                                     ,D.MOD_NO                                                                                                                                                                                                                                                                                               			\n");
            sqlStr.append("                                     ,SIGN.DOC_ID   ,SIGN.YEAR                                                                                                                                                                                                                                                                                            			\n");
            sqlStr.append("              FROM  SUB2000T A, SUB1020T B, SUB1040T CC                                                                                                                                                                                                                                                                                      			\n");
            sqlStr.append("                         ,(                   SELECT WARR_GUBUN, F_GET_CODE_NAME('GAUR_KIND',WARR_GUBUN) AS WARR_GUBUN_NM,  GUAR_ONOFF_YN, SUB_CONT_ID, SUB_CONT_MOD_ID, WARR_SEQ, CAUSECODE, GUAR_STATUS                                                                                                                                     			\n");
            sqlStr.append("                                             ,F_GET_CODE_NAME('GUAROFFICEID',GUAR_ORGAN_CODE) AS WARR_GOFFICE_ID, GRTREQNO, MOD_NO                                                                                                                                                                                                           			\n");
            sqlStr.append("                                                 FROM  CONT_OUT_WARR  WHERE WARR_GUBUN IN ('5')                                                                                                                                                                                                                                              			\n");
            sqlStr.append("                                  ) D                                                                                                                                                                                                                                                                                                        			\n");
            sqlStr.append("                 			 ,(SELECT KEY_COL3 AS ACMP_ID, MAX(DOC_ID) AS DOC_ID  , TO_CHAR(MAX(CRE_DATE), 'YYYYMMDD')  YEAR        \n");
            sqlStr.append("                 			   FROM SIGN001T                              \n");
            sqlStr.append("                  			   WHERE SIGN_CLASS_CD IN ('erp_opycD', 'erp_opyeD')                     \n");
            sqlStr.append("                  			   AND SIGN_DECI_CLASS in ( '14','24')                        \n");//2018.06.27 준공기성 24 추가
            sqlStr.append("                  			   GROUP BY KEY_COL3 ) SIGN                \n");
            sqlStr.append("              WHERE 1=1                                                                                                                                                                                                                                                                                                                      			\n");
            sqlStr.append("              AND A.SUB_CONT_ID = CC.SUB_CONT_ID                                                                                                                                                                                                                                                                                             			\n");
            sqlStr.append("              AND A.MOD_NO = CC.MOD_NO                                                                                                                                                                                                                                                                                                       			\n");
            sqlStr.append("              AND CC.CON_CORP_ID = B.CON_CORP_ID                                                                                                                                                                                                                                                                                             			\n");
            sqlStr.append("              AND A.SUB_CONT_MOD_ID = D.SUB_CONT_MOD_ID(+)                                                                                                                                                                                                                                                                                   			\n");
            sqlStr.append("              AND CC.ACMP_ID = SIGN.ACMP_ID(+)                                                                                                                                                                                                                                                                                   			\n");
            sqlStr.append("              ) A                                                                                                                                                                                                                                                                                                                            			\n");
            sqlStr.append("              where 1=1                                                                                                                                                                                                                                                                                                                      			\n");
            sqlStr.append("              AND A.SUB_CONT_ID = ?                                                                                                                                                                                                                                                                                                     			\n");
            sqlStr.append("              ORDER BY prepay_id desc,ACMP_ID	\n");
                        
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("subContId"));
            
            
            WAFLogger.debug(sqlStr.toString());
            
            rowSet = search.execute();
        }
        catch (Exception se) {
        	WAFLogger.error(se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            se.printStackTrace();
            throw new Exception(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
        }
        return rowSet;
    }
    
    private RowSet getDashOutList2(ValueObject Params) throws Exception
    {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try
        {           
            sqlStr.append("        SELECT DECODE(A.MOD_NO,0,'당초계약','변경계약') MOD_DESC -- 구분                                         \n");
            sqlStr.append("                ,A.MOD_NO -- 차수                                         \n");
            sqlStr.append("                ,A.CON_DOC_NO AS ORD_DOC_NO                                         \n");
            sqlStr.append("                ,A.BUILD_NAME AS CST_DOC_NAME -- 계약명                                         \n");
            sqlStr.append("                ,A.SUB_CONT_MOD_ID -- 변경계약일련번호                                         \n");
            sqlStr.append("                ,(SELECT COUNT(*) CNT FROM  CONT_OUT_FILE  WHERE SUB_CONT_MOD_ID =  A.SUB_CONT_MOD_ID) AUTO_CNT                       \n");
            sqlStr.append("                ,(SELECT COUNT(*) CNT  FROM  PUB0510T A,  PUB0500T B WHERE A.FILE_ID = B.FILE_ID AND A.FILE_GB = 'SUB_CONT_MOD_ID'  AND   A.ADD_GB = 'F'  AND A.ID      = NVL(A.SUB_CONT_MOD_ID, A.ID) )  FILE_CNT         \n");
            sqlStr.append("                ,DECODE(A.MOD_NO, 0, SIGN3.DOC_ID, SIGN4.DOC_ID) AS DOC_ID0                                                                                                                                                           \n");
            sqlStr.append("                ,DECODE(A.MOD_NO, 0, SIGN3.YEAR, SIGN4.YEAR ) AS YEAR0                                                                                                                                                           \n");
            sqlStr.append("                ,DECODE(A.MOD_NO, 0, SIGN.DOC_ID, nvl(SIGN23.DOC_ID,SIGN23.DOC_ID )) AS DOC_ID                                                                                                                                                           \n");
            sqlStr.append("                ,DECODE(A.MOD_NO, 0, SIGN.YEAR, nvl(SIGN23.YEAR,SIGN23.YEAR )) AS YEAR                                                                                                                                                           \n");
            sqlStr.append("                FROM SUB2000T A                                      \n");
            sqlStr.append("                  ,(SELECT KEY_COL1 AS BID_NO, KEY_COL2 AS BID_DEG, MAX(DOC_ID) AS DOC_ID , TO_CHAR(MAX(CRE_DATE), 'YYYYMMDD')  YEAR    \n");
            sqlStr.append("                    FROM SIGN001T                              \n");
            sqlStr.append("                   WHERE SIGN_CLASS_CD = 'ebid_opoD'                     \n");
            sqlStr.append("                   AND SIGN_DECI_CLASS = '14'                        \n");
            sqlStr.append("                   GROUP BY KEY_COL1, KEY_COL2 ) SIGN                \n");
            sqlStr.append("                  ,(SELECT KEY_COL1 AS SUB_CONT_MOD_ID, MAX(DOC_ID) AS DOC_ID, TO_CHAR(MAX(CRE_DATE), 'YYYYMMDD')  YEAR     \n");
            sqlStr.append("                    FROM SIGN001T                              \n");
            sqlStr.append("                   WHERE SIGN_CLASS_CD = 'ebid_ocpoD'                     \n");
            sqlStr.append("                   AND SIGN_DECI_CLASS = '14'                        \n");
            sqlStr.append("                   GROUP BY KEY_COL1, KEY_COL2 ) SIGN2                \n");
            sqlStr.append("                  , SUB3000T SIGN21             \n");
            sqlStr.append("                  , SUB2000T SIGN22           \n");
            sqlStr.append("                  ,(SELECT KEY_COL1 AS SUB_CONT_MOD_ID, KEY_COL2  AS SUB_CONT_REQ_ID,  MAX(DOC_ID) AS DOC_ID  , TO_CHAR(MAX(CRE_DATE), 'YYYYMMDD')  YEAR                  \n");
            sqlStr.append("                    FROM SIGN001T                 \n");
            sqlStr.append("                    WHERE SIGN_CLASS_CD = 'ebid_ocpoD'                     \n");
            sqlStr.append("                    AND SIGN_DECI_CLASS = '14'                     \n");
            sqlStr.append("                    GROUP BY KEY_COL1, KEY_COL2 ) SIGN23               \n");
            sqlStr.append("                  --외주  발주의뢰                 \n"); 
            sqlStr.append("                  ,(SELECT MAX(DOC_ID) DOC_ID,  A.SUB_CONT_MOD_ID  , TO_CHAR(MAX(C.CRE_DATE), 'YYYYMMDD')  YEAR                \n");
            sqlStr.append("                  FROM SUB2000T A                 \n");
            sqlStr.append("                  ,BID_MASTER B                 \n");
            sqlStr.append("                  ,SIGN001T C                 \n");
            sqlStr.append("                  WHERE  A.BID_NO = B.BID_NO                 \n");
            sqlStr.append("                  AND B.BID_DEG = B.BID_DEG                 \n");
            sqlStr.append("                  AND C.SIGN_CLASS_CD = 'prD'                 \n");
            sqlStr.append("                  AND C.KEY_COL1 = B.DEMANDED_ID                 \n");
            sqlStr.append("                  AND C.KEY_COL2 = B.DEPT_CD                 \n");
            sqlStr.append("                  AND A.SUB_CONT_ID = ?                  \n");
            sqlStr.append("                  AND C.SIGN_DECI_CLASS = 14                  \n");
            sqlStr.append("                  GROUP BY A.SUB_CONT_MOD_ID )SIGN3                  \n");
            sqlStr.append("                  --외주변경계약                 \n");
            sqlStr.append("                  ,(SELECT  MAX(B.ERP_SIGN_DOC_ID) AS DOC_ID, A.SUB_CONT_MOD_ID , MAX(B.ERP_SIGN_YEAR) AS YEAR                \n");
            sqlStr.append("                  FROM SUB2000T A                  \n");
            sqlStr.append("                  ,SUB2000T A1                 \n");
            sqlStr.append("                  ,SUB3000T B                 \n");
            sqlStr.append("                  WHERE A.SUB_CONT_ID = A1.SUB_CONT_ID                 \n");
            sqlStr.append("                  AND A.MOD_NO-1 = A1.MOD_NO                  \n");
            sqlStr.append("                  AND A1.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                 \n");
            sqlStr.append("                  AND A.SUB_CONT_ID = ?                 \n");
            sqlStr.append("                  GROUP BY A.SUB_CONT_MOD_ID    )SIGN4               \n");
            sqlStr.append("                WHERE A.SUB_CONT_ID = ?                                         \n");
            sqlStr.append("                  AND A.BID_NO = SIGN.BID_NO(+)                                                                                                                             \n");
            sqlStr.append("                  AND A.BID_DEG = SIGN.BID_DEG(+)                                                                                                                             \n");
            sqlStr.append("                  AND A.SUB_CONT_MOD_ID = SIGN2.SUB_CONT_MOD_ID(+)                                                                                                                             \n");
            sqlStr.append("                AND A.MOD_NO - 1  = SIGN22.MOD_NO(+)                                        \n");
            sqlStr.append("                AND A.SUB_CONT_ID = SIGN22.SUB_CONT_ID(+)                                        \n");
            sqlStr.append("                AND SIGN22.SUB_CONT_MOD_ID = SIGN21.SUB_CONT_MOD_ID(+)                                        \n");
            sqlStr.append("                AND SIGN21.SUB_CONT_MOD_ID = SIGN23.SUB_CONT_MOD_ID(+)                                         \n");
            sqlStr.append("                AND SIGN21.SUB_CONT_REQ_ID = SIGN23.SUB_CONT_REQ_ID(+)                                           \n");
            sqlStr.append("                AND A.SUB_CONT_MOD_ID = SIGN3.SUB_CONT_MOD_ID(+)                                                                                                                             \n");
            sqlStr.append("                AND A.SUB_CONT_MOD_ID = SIGN4.SUB_CONT_MOD_ID(+)                                                                                                                             \n");
            sqlStr.append("                AND (A.STATUS_CD = '50' OR A.CON_CLS_YN = '9')                                         \n");
            sqlStr.append("                ORDER BY A.MOD_NO                                                                       \n");
                        
            search.setStatement(sqlStr.toString());
            //search.addParameter(storage.getUser().getInt("ACCT_ID"));
            search.addParameter(params.get("subContId"));
            search.addParameter(params.get("subContId"));
            search.addParameter(params.get("subContId"));
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());
            
            rowSet = search.execute();
        }
        catch (Exception se) {
        	WAFLogger.error(se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            se.printStackTrace();
            throw new Exception(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    
    private RowSet getDashOutList4(ValueObject Params) throws Exception
    {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try
        {           
            sqlStr.append("                SELECT WARR_GUBUN \n");
            sqlStr.append("                    , F_GET_CODE_NAME('GAUR_KIND',WARR_GUBUN) AS WARR_GUBUN_NM   \n");
            sqlStr.append("                    , MOD_NO \n");
            sqlStr.append("                    , WARR_SEQ   \n");
            sqlStr.append("                    , WARR_WAMT  \n");
            sqlStr.append("                    , VIEW_DIV   \n");
            sqlStr.append("                    , CAUSECODE  \n");
            sqlStr.append("                    , F_GET_CODE_NAME('CAUSECODE',CAUSECODE) AS CAUSECODE_NM \n");
            sqlStr.append("                    , GUAR_ORGAN_CODE    \n");
            sqlStr.append("                    , F_GET_CODE_NAME('GUAROFFICEID',GUAR_ORGAN_CODE) AS WARR_GOFFICE_ID \n");
            sqlStr.append("                    , F_GET_CODE_NAME('GUARORGAN',GUAR_ORGAN_CODE) AS WARR_GOFFICE   \n");
            sqlStr.append("                    , GUAR_STATUS    \n");
            sqlStr.append("                    , F_GET_CODE_NAME('GUAR_STATUS',GUAR_STATUS) AS GUAR_STATUS_NM   \n");
            sqlStr.append("                    , TO_CHAR(TO_DATE(PAPERDATE, 'YYYYMMDD'), 'YYYY-MM-DD') PAPERDATE  \n");
            sqlStr.append("                    , WARR_WGNO  \n");
            sqlStr.append("                    , TO_CHAR(TO_DATE(WARR_SDATE, 'YYYYMMDD'), 'YYYY-MM-DD') WARR_SDATE  \n");
            sqlStr.append("                    , TO_CHAR(TO_DATE(WARR_EDATE, 'YYYYMMDD'), 'YYYY-MM-DD') WARR_EDATE     \n");
            sqlStr.append("                    , DECODE(GUAR_ONOFF_YN,'Y','온라인','N','오프라인') AS GUAR_ONOFF_NM    \n");
            sqlStr.append("                    , GUAR_ONOFF_YN  \n");
            sqlStr.append("                    , GRTREQNO   \n");
            sqlStr.append("                    , SUB_CONT_MOD_ID AS PUR_CONT_MOD_ID   \n");
            sqlStr.append("                 FROM CONT_OUT_WARR  \n");
            sqlStr.append("                WHERE SUB_CONT_ID = ?     \n");
            sqlStr.append("                ORDER BY WARR_GUBUN ASC,MOD_NO      \n");
                        
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("subContId"));

            WAFLogger.debug(sqlStr.toString());
            
            rowSet = search.execute();
        }
        catch (Exception se) {
        	WAFLogger.error(se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            se.printStackTrace();
            throw new Exception(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
        }
        return rowSet;
    }
    
    
    private RowSet getDashOutList3(ValueObject Params) throws Exception
    {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        
        try
        {           
            sqlStr.append("        SELECT B.BID_NO -- 입찰번호                                          \n");
            sqlStr.append("                ,B.BID_DEG -- 입찰차수                                          \n");
            sqlStr.append("                ,BID_MASTER_NM -- 입찰명                                          \n");
            sqlStr.append("                ,B.DEMANDED_ID                                         \n");
            sqlStr.append("                ,B.DOC_ETC_IN                                         \n");
            sqlStr.append("                ,B.DOC_ETC_OUT                                         \n");
            sqlStr.append("                ,C.FILE_GB                                         \n");
            sqlStr.append("                ,A.SUB_CONT_ID                                                                                                               \n");
            sqlStr.append("                ,A.SUB_CONT_MOD_ID                                                                                                               \n");
            sqlStr.append("                ,B.PBC_CBE_TYPE_CD                                         \n");
            sqlStr.append("                ,(SELECT COUNT(*) CNT  FROM  PUB0510T A,  PUB0500T B WHERE A.FILE_ID = B.FILE_ID AND A.FILE_GB = 'BID_IN_1'  AND   A.ADD_GB = 'F'   AND A.ID  = NVL(B.DEMANDED_ID, A.ID) ) BID_IN_CNT                                         \n");
            sqlStr.append("                ,(SELECT COUNT(*) CNT  FROM  PUB0510T A,  PUB0500T B WHERE A.FILE_ID = B.FILE_ID AND A.FILE_GB = 'BID_OUT_1'  AND   A.ADD_GB = 'F'   AND A.ID  = NVL(B.DEMANDED_ID, A.ID) ) BID_OUT_CNT                                         \n");
            sqlStr.append("                ,(SELECT COUNT(*) CNT  FROM  PUB0510T A,  PUB0500T B WHERE A.FILE_ID = B.FILE_ID AND A.FILE_GB = C.FILE_GB  AND   A.ADD_GB = 'F'   AND A.ID  = NVL(B.DEMANDED_ID, A.ID) ) CORP_CNT                                         \n");
            sqlStr.append("                ,SIGN.DOC_ID , SIGN.YEAR                                        \n");
            sqlStr.append("                FROM SUB2000T A                                          \n");
            sqlStr.append("                 ,BID_MASTER B                                          \n");
            sqlStr.append("                  ,BID_SUPP C                                          \n");
            sqlStr.append("                  ,(SELECT KEY_COL1 AS BID_NO, KEY_COL2 AS BID_DEG, MAX(DOC_ID) AS DOC_ID, TO_CHAR(MAX(CRE_DATE), 'YYYYMMDD')  YEAR     \n");
            sqlStr.append("                    FROM SIGN001T                              \n");
            sqlStr.append("                   WHERE SIGN_CLASS_CD = 'ebid_oinqD'                     \n");
            sqlStr.append("                   AND SIGN_DECI_CLASS = '14'                        \n");
            sqlStr.append("                   GROUP BY KEY_COL1, KEY_COL2 ) SIGN                \n");
            
            sqlStr.append("                WHERE A.BID_NO = B.BID_NO                                          \n");
            sqlStr.append("                AND A.BID_DEG = B.BID_DEG                                          \n");
            sqlStr.append("                AND A.BID_NO = C.BID_NO                                         \n");
            sqlStr.append("                AND A.BID_DEG = C.BID_DEG                                         \n");
            sqlStr.append("                AND A.BID_NO = SIGN.BID_NO (+)                                         \n");
            sqlStr.append("                AND A.BID_DEG = SIGN.BID_DEG (+)                                        \n");
            sqlStr.append("                AND C.SUCC_YN = 'Y'                                         \n");
            sqlStr.append("                AND A.SUB_CONT_MOD_ID = (SELECT MIN(SUB_CONT_MOD_ID) FROM SUB2000T WHERE SUB_CONT_ID = ?)                                                                \n");
                          
                        
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("subContId"));
            
            WAFLogger.debug(sqlStr.toString());
            
            rowSet = search.execute();
        }
        catch (Exception se) {
        	WAFLogger.error(se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            se.printStackTrace();
            throw new Exception(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
        }
        return rowSet;
    }
    
    
    
}

