/**
 * @(#)file    SuppNotiListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.7.18
 * @(#)Description: 협력업체 공지사항
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.supplier.portal.biz;

import java.util.Calendar;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;

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


public class SuppNotiListBiz extends Biz {

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

	
	public void doBiz() throws Exception {
		if(storage.compareEvent("")){
			RowSet rowset = getSuppNotiList(params);
			storage.addRowSet("notiRowSet", rowset);
			
			
			
			
			/***실적증명***/
			Calendar cal = Calendar.getInstance();
            cal.add(cal.YEAR,-1);
            cal.add(cal.MONTH,0);
            
            String yyyy = (new java.text.SimpleDateFormat ("yyyy")).format (cal.getTime());
            params.set("acmp_yyyy", yyyy);
            
            String month = (new java.text.SimpleDateFormat ("MM")).format (cal.getTime());
            
            params.set("yyyyMM", yyyy+month);
            params.set("yyyy-MM", yyyy+"-"+month);
            
            params.set("yyyymm", Integer.toString(Integer.parseInt(yyyy)+1)+month);
            
            ValueObject getResultTargetCnt = new ValueObject();
            getResultTargetCnt = getResultTargetList(params);
            
            storage.addValueObject("getResultTargetCnt", getResultTargetCnt);
            
            
            
            /****전자입찰*****/
            ValueObject getBidCnt = new ValueObject();
            getBidCnt = getBidCnt(params);
            
            storage.addValueObject("getBidCnt", getBidCnt);
            
            
            /***전자계약***/
            ValueObject getSuppContCnt = new ValueObject();
            getSuppContCnt =getSuppContCnt(params);
            
            storage.addValueObject("getSuppContCnt", getSuppContCnt);
            
            
            /***대금지급****/
            ValueObject getPayCnt = new ValueObject();
            getPayCnt =getPayCnt(params);
            
            storage.addValueObject("getPayCnt", getPayCnt);
            
            
            
            
		} 
		else if(storage.compareEvent("USER_APPR_CHK")){
		    
		    ValueObject getApprUserChk = new ValueObject();
		    ValueObject getApprUserChk2 = new ValueObject();
		    getApprUserChk = getApprUserChk(params);
		    
		    if(getApprUserChk == null)
		    {
		        getApprUserChk2.set("SEARCH_GUBUN", "N");
		        storage.addValueObject("VALUEOBJECT", getApprUserChk2);
		    }
		    else
		    {
		        storage.addValueObject("VALUEOBJECT", getApprUserChk);
		    }
		}
	}
	
	
	private ValueObject getApprUserChk(ValueObject Params) {
	    
	    Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        ValueObject vo = null;
        
        try {   
            sqlStr.append("    SELECT                                                                                                                       \n");
           sqlStr.append("             CASE                                                                                                                                   \n");
           sqlStr.append("                    WHEN A.STATUS = '02' AND TO_CHAR(TO_DATE(USE_END_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD')  <  TO_CHAR(SYSDATE, 'YYYY-MM-DD')  THEN  'END_DATE'        \n");
           sqlStr.append("                    WHEN A.STATUS = '03' AND TO_CHAR(TO_DATE(USE_END_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD')  <  TO_CHAR(SYSDATE, 'YYYY-MM-DD')  THEN  'END_DATE'                \n");
           sqlStr.append("            END  AS CORP_STATUS_NAME                                                                                                                                                                                                                    \n");
           sqlStr.append("            ,STATUS STATUS_CODE                                                                                                                                                                                                                                         \n");
           sqlStr.append("            ,\"COMMENT\"                                                                                                                                                                                                                                                       \n");
           sqlStr.append("          FROM SUPP_INFO A, SUPP_CHARGE B                                                                                                                                                                                                                           \n");
           sqlStr.append("          WHERE A.CORP_ID = B.CORP_ID                                                                                                                                                                                                               \n");
           sqlStr.append("          AND B.USER_ID = ?                                                                                                                                                                                                  \n");

            
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("login_id"));
            
            
            //System.out.println(sqlStr.toString());
            //System.out.println(search.getParameters());
            
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            //System.out.println("getResultIngList=========>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return vo;
        }
	}
	
	
	private ValueObject getResultTargetList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        ValueObject vo = null;
        
        try {
            sqlStr.append("    SELECT                                               \n")
                    .append("    MIN(DECODE(GUBUN, '1', TARGET_CNT, 0)) RESULT_TARGET_CNT,                                                \n")
                    .append("    MIN(DECODE(GUBUN, '2', TARGET_CNT, 0)) RESULT_ING_CNT,                                                \n")
                    .append("    MIN(DECODE(GUBUN, '3', TARGET_CNT, 0)) RESULT_END_CNT FROM (                                            \n")
                    .append("    SELECT COUNT(SUB_CONT_MOD_ID) TARGET_CNT, '1' GUBUN                                           \n")
                   .append("             FROM(                                                                                                          \n")
                   .append("             SELECT A.SUB_CONT_MOD_ID -- 외주계약변경ID                                                \n")
                   .append("                         ,D.ACMP_YYYY -- 기성년도                                                \n")
                   .append("                         ,A.DEPT_CD -- 현장코드                                                \n")
                   .append("                         ,A.dept_name -- 현장명                                                \n")
                   .append("                         ,C.CORP_ID                                                \n")
                   .append("                        ,C.VENDCD ISU_VENDCD -- 신청인발급요청자) 사업자번호                                                \n") 
                   .append("                        ,C.MEMBER_NAME ISU_VENDNM -- 신청인(발급요청자) 상호                                                \n")
                   .append("                        ,C.BOSS_NAME ISU_PRES_NM -- 신청인(발급요청자) 대표자명                                                \n")
                   .append("                        ,C.ADDRESS ISU_ADDR -- 신청인(발급요청자) 주소                                                \n")
                   .append("                        ,C.TEL_NUM ISU_PHONE -- 신청인(발급요청자) 전화번호                                                \n")
                   .append("                        ,a.BUILD_NAME CONT_NM -- 공종(계약)명                                                \n")
                   .append("                        ,A.WORK_PLACE -- 현장소재지 (공사현장)                                                \n")
                   .append("                        ,a.CON_AMT -- 공종(계약)명                                                \n")
                   .append("                        ,TO_CHAR(A.CON_DATE,'YYYYMMDD') CONT_CDATE -- 계약년월일 (YYYYMMDD)                                                \n")
                   .append("                        ,TO_CHAR(A.START_WORK_DATE,'YYYYMMDD') CONT_SDATE -- 착공년월일 (YYYYMMDD)                                                \n")
                   .append("                        ,TO_CHAR(A.end_work_date,'YYYYMMDD') CONT_EDATE-- 준공년월일 (YYYYMMDD)                                                \n")
                   .append("                        ,FLOOR(A.CON_AMT / 1000)  CONT_TAMT --  총계약금액 (총공사금액)                                                \n")
                   .append("                        ,FLOOR(NVL(D.FUND_AMT,0)/1000) CUR_PAY_AMT -- 당년도 기성액                                                \n")
                   .append("                        ,A.DEALER_NM ORD_NM -- 발주자                                                \n")
                   .append("                        ,FLOOR((A.CON_AMT - NVL(E.FUND_AMT,0)) / 1000) CUR_CONT_AMT -- 당년도 계약액 (이월액)                                                \n")
                   .append("                        ,FLOOR((A.CON_AMT - (NVL(D.FUND_AMT,0) + NVL(E.FUND_AMT,0))) / 1000) CUR_PAY_NAMT -- 당년도 미기성액                                                \n")
                   .append("                        ,(SELECT CON_AMT FROM SUB2000T WHERE SUB_CONT_MOD_ID = (SELECT MAX(SUB_CONT_MOD_ID) FROM SUB2000T WHERE TO_CHAR(A.CON_DATE,'YYYYMMDD') = ?-1)) PRV_CONT_AMT                                                \n")
                   .append("                        ,FLOOR(NVL(E.FUND_AMT,0) / 1000) PRV_FUND_AMT-- 전년도까지 누계기성액                                                \n")
                   .append("                        ,(SELECT COUNT(*) FROM RESULT_PLOOF_INFO WHERE SUB_CONT_MOD_ID = A.SUB_CONT_MOD_ID AND ACMP_YYYY = D.ACMP_YYYY) PLOOF_INFO_CNT                                                \n")
                   .append("             FROM SUB2000T A                                                \n")
                   .append("                      ,(SELECT MAX(SUB_CONT_MOD_ID) SUB_CONT_MOD_ID                                                \n")
                   .append("             FROM SUB2000T                                                \n")
                   .append("             WHERE CON_CLS_YN = '9'                                                \n")
                   .append("             AND to_char(start_work_date,'yyyymm') >= ?                                                \n")
                   .append("                and  to_char(end_work_date,'yyyymm') >= ?                                                \n")
                   .append("             GROUP BY SUB_CONT_ID) B                                                \n")
                   .append("             ,CONT_OUT_SUPP C                                                \n")
                   .append("             ,(SELECT SUB_CONT_ID, SUBSTR(ACMP_YYYYMM,0,4) ACMP_YYYY, FLOOR(SUM(FUND_AMT)) FUND_AMT                                                \n")
                   .append("             FROM SUB1040T                                                \n")
                   .append("             WHERE SUBSTR(ACMP_YYYYMM,0,4) = ?                                                \n")
                   .append("             GROUP BY SUB_CONT_ID, SUBSTR(ACMP_YYYYMM,0,4)) D                                                \n")
                   .append("             ,(SELECT SUB_CONT_ID, FLOOR(SUM(FUND_AMT)) FUND_AMT                                                \n")
                   .append("             FROM SUB1040T                                                \n")
                   .append("             WHERE SUBSTR(ACMP_YYYYMM,0,4) < ?                                                \n")
                   .append("             GROUP BY SUB_CONT_ID) E                                                \n")
                   .append("             WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                \n")
                   .append("             AND A.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID                                                \n")
                   .append("             AND A.SUB_CONT_ID = D.SUB_CONT_ID                                                \n")
                   .append("             AND A.SUB_CONT_ID = E.SUB_CONT_ID(+)                                                \n")
                   .append("             AND C.SIGN_SEQ = 2                                                \n")
                   .append("             )     WHERE PLOOF_INFO_CNT = 0                                           \n")
                   .append("             AND CORP_ID  = ?                                                 \n")
                   .append("           UNION ALL                                        \n")
                   .append("           SELECT COUNT(*) TARGET_CNT, '2' GUBUN                                        \n")
                   .append("           FROM RESULT_PLOOF_INFO                                        \n")
                   .append("           WHERE CORP_ID  = ?    AND PROG_STAT_CD != '30'                                     \n")
                   .append("           UNION ALL                                        \n")
                   .append("           SELECT COUNT(*) TARGET_CNT, '3' GUBUN                                        \n")
                   .append("           FROM RESULT_PLOOF_INFO                                        \n")
                   .append("           WHERE CORP_ID  = ?    AND PROG_STAT_CD = '30'       )                             \n");

            
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));

            
            
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            //System.out.println("getResultIngList=========>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return vo;
        }
    }
	
	private ValueObject getBidCnt(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        ValueObject vo = null;
        
        try {
            sqlStr.append("    SELECT                                                                           \n");
            sqlStr.append("                        MIN(DECODE(GUBUN, '1', CNT) ) CNT_1,                                                                         \n");
            sqlStr.append("                        MIN(DECODE(GUBUN, '2', CNT) ) CNT_2,                                                                         \n");
            sqlStr.append("                        MIN(DECODE(GUBUN, '3', CNT) ) CNT_3,                                                                         \n");
            sqlStr.append("                        MIN(DECODE(GUBUN, '4', CNT) ) CNT_4                                                                         \n");
            sqlStr.append("                        FROM                                                                         \n");
            sqlStr.append("                        (                                                                         \n");
            sqlStr.append("                        SELECT COUNT(*) CNT, '1' GUBUN                                                                         \n");
            sqlStr.append("                        FROM(                                                                         \n");
            sqlStr.append("                        SELECT A.BID_NO                                                                                                                                         \n");                
            sqlStr.append("                              ,A.BID_DEG                                                                                                                                                             \n");
            sqlStr.append("                              ,A.DEMANDED_TITLE                                                                                                                                                 \n");
            sqlStr.append("                              ,A.BID_MASTER_NM                                                                                                                                                 \n");
            sqlStr.append("                              ,TO_CHAR(A.SITEDESCDATE,'YYYY-MM-DD HH24:MI') AS SITEDESCDATE                                                                             \n");
            sqlStr.append("                              ,TO_CHAR(A.BID_TO_DT,'YYYY-MM-DD HH24:MI') AS BID_TO_DT                                                                                     \n");
            sqlStr.append("                              ,B.SITE_ATTEND_YN                                                                                                                                                 \n");
            sqlStr.append("                              ,NVL(B.SITEPARTI_YN,'-') AS SITEPARTI_YN                                                                                                                 \n");
            sqlStr.append("                              ,A.STATUS_CD                                                                                                                                                         \n");
            sqlStr.append("                              ,A.DEMANDED_ID                                                                                                                                                     \n");
            sqlStr.append("                              ,A.DEMANDED_KIND                                                                                                                                                 \n");
            sqlStr.append("                           FROM BID_MASTER A, BID_SUPP B                                                                                                                                 \n");
            sqlStr.append("                              WHERE A.BID_NO=B.BID_NO(+)                                                                                                                                     \n");
            sqlStr.append("                              AND A.BID_DEG=B.BID_DEG(+)                                                                                                                                     \n");
            sqlStr.append("                            AND B.VENDOR_CD(+)= ?                                                                                                     \n");
            sqlStr.append("                              AND A.STATUS_CD IN ('03','04')                                                                                                                                     \n");
            sqlStr.append("                            AND A.CMPT_CD = '01'                                                                                                                                                 \n");
            sqlStr.append("                            AND A.ONBID_YN = 'Y'                                                                                                                                                 \n");
            sqlStr.append("                            AND A.NOTI_DY <= TO_CHAR(SYSDATE,'YYYYMMDD')                                                                                                         \n");
            sqlStr.append("                            AND (A.OUT_ITEM_CD IN (SELECT OUT_ITEM_CD FROM SUPP_OUT_ITEM WHERE CORP_ID =  ?)                                                                                                     \n");
            sqlStr.append("                            OR A.MATERIAL_GB IN (SELECT SC.PARENT_LEVEL FROM SUPP_PUR_ITEM SA , PMM_MATERIALGROUP_V SB , PMM_MATERIALGROUP_V SC WHERE SA.KIND_SMALL=SB.MATERIALGROUP  AND SB.PARENT_LEVEL = SC.MATERIALGROUP AND SA.CORP_ID =  ? GROUP BY SC.PARENT_LEVEL))                                                                         \n");                            
            sqlStr.append("                           AND A.STATUS_CD = '03'                                                                                                                                                                                                     \n");
            if(storage.getUser().get("JOB_DIV").equals("O"))
            {
                sqlStr.append("                         AND DEMANDED_KIND = 'O'                                                                                            \n");
            }
            else
            {
                sqlStr.append("                         AND DEMANDED_KIND != 'O'                                                                                            \n");
            }
            sqlStr.append("                         UNION ALL                                                                                                                                                                     \n");
            sqlStr.append("                         SELECT A.BID_NO                                                                                                                                                         \n");
            sqlStr.append("                              ,A.BID_DEG                                                                                                                                                             \n");
            sqlStr.append("                              ,A.DEMANDED_TITLE                                                                                                                                                 \n");
            sqlStr.append("                              ,A.BID_MASTER_NM                                                                                                                                                 \n");
            sqlStr.append("                              ,TO_CHAR(A.SITEDESCDATE,'YYYY-MM-DD HH24:MI') AS SITEDESCDATE                                                                             \n");
            sqlStr.append("                              ,TO_CHAR(A.BID_TO_DT,'YYYY-MM-DD HH24:MI') AS BID_TO_DT                                                                                     \n");
            sqlStr.append("                              ,B.SITE_ATTEND_YN                                                                                                                                                 \n");
            sqlStr.append("                              ,NVL(B.SITEPARTI_YN,'-') AS SITEPARTI_YN                                                                                                                 \n");
            sqlStr.append("                              ,A.STATUS_CD                                                                                                                                                         \n");
            sqlStr.append("                              ,A.DEMANDED_ID                                                                                                                                                     \n");
            sqlStr.append("                              ,A.DEMANDED_KIND                                                                                                                                                 \n");
            sqlStr.append("                           FROM BID_MASTER A                                                                                                                                                     \n");
            sqlStr.append("                              , BID_SUPP B                                                                                                                                                             \n");
            sqlStr.append("                          WHERE A.BID_NO=B.BID_NO                                                                                                                                             \n");
            sqlStr.append("                            AND A.BID_DEG=B.BID_DEG                                                                                                                                         \n");
            sqlStr.append("                            AND B.VENDOR_CD= ?                                                                                                         \n");
            sqlStr.append("                            AND A.SITEDESC_FLAG = 'Y'                                                                                                                                         \n");
            sqlStr.append("                            AND A.ONBID_YN = 'Y'                                                                                                                                                 \n");
            sqlStr.append("                            AND A.NOTI_DY <= TO_CHAR(SYSDATE,'YYYYMMDD')                                                                                                         \n");
            sqlStr.append("                            AND A.CMPT_CD <> '01'                                                                                                                                                 \n");
            sqlStr.append("                         AND A.STATUS_CD IN ('03','04')                                                                                                                                         \n");
            sqlStr.append("                         AND A.STATUS_CD = '03'                                                                                                                                                                                                     \n");
            if(storage.getUser().get("JOB_DIV").equals("O"))
            {
                sqlStr.append("                         AND DEMANDED_KIND = 'O'                                                                                            \n");
            }
            else
            {
                sqlStr.append("                         AND DEMANDED_KIND != 'O'                                                                                            \n");
            }
            sqlStr.append("                        )                                                                         \n");
            sqlStr.append("                        UNION ALL                                                                         \n");
            sqlStr.append("                        SELECT COUNT(*) CNT, '2' GUBUN                                                                         \n");
            sqlStr.append("                        FROM(                                                                         \n");
            sqlStr.append("                        SELECT *                                                                                                                                                         \n");
            sqlStr.append("                         FROM (                                                                                                                                                                                 \n");
            sqlStr.append("                         SELECT A.BID_TO_DT,                                                                                                                                                             \n");
            sqlStr.append("                                A.STATUS_CD,                                                                                                                                                                 \n");
            sqlStr.append("                                A.BID_NO,                                                                                                                                                                     \n");
            sqlStr.append("                                A.BID_DEG,                                                                                                                                                                     \n");
            sqlStr.append("                                A.BID_MASTER_NM,                                                                                                                                                         \n");
            sqlStr.append("                                A.DEMANDED_TITLE,                                                                                                                                                         \n");
            sqlStr.append("                                A.SITEDESC_FLAG,                                                                                                                                                         \n");
            sqlStr.append("                                A.BID_FROM_DT,                                                                                                                                                             \n");
            sqlStr.append("                                 CASE WHEN A.BID_TO_DT > SYSDATE AND A.STATUS_CD = '04' THEN '04'                                                                                 \n");
            sqlStr.append("                                 WHEN A.BID_TO_DT < SYSDATE AND A.STATUS_CD = '04' THEN '04E'                                                                                     \n");
            sqlStr.append("                                 WHEN A.STATUS_CD = '05' THEN '05'                                                                                                                                 \n");
            sqlStr.append("                                 ELSE '' END AS STATUS_NM,                                                                                                                                             \n");
            sqlStr.append("                                B.SITEPARTI_YN,                                                                                                                                                             \n");
            sqlStr.append("                                B.BID_RNNC_ID,                                                                                                                                                             \n");
            sqlStr.append("                                B.BID_RNNC_NM,                                                                                                                                                             \n");
            sqlStr.append("                                B.BID_RNNC_DT,                                                                                                                                                             \n");
            sqlStr.append("                                B.RNNC_REASON,                                                                                                                                                             \n");
            sqlStr.append("                                B.VENDOR_CD,                                                                                                                                                                 \n");
            sqlStr.append("                                A.DEMANDED_ID,                                                                                                                                                             \n");
            sqlStr.append("                                A.DEMANDED_KIND,                                                                                                                                                         \n");
            sqlStr.append("                                DECODE(B.BID_RNNC_DT,NULL,DECODE(B.SIGN_DT,NULL,'미제출',TO_CHAR(B.SIGN_DT,'YYYY-MM-DD HH24:MI:SS')),'입찰포기') AS SIGN_DT,                                                                             \n");
            sqlStr.append("                                DECODE(A.POSTPONE_REASON,'','N','Y') AS POSTPONE_YN,                                                                                                     \n");
            sqlStr.append("                                DECODE(A.MODIFY_REASON,'','N','Y') AS MODIFY_YN                                                                                                             \n");
            sqlStr.append("                         FROM BID_MASTER A  ,BID_SUPP B                                                                                                                                             \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.ONBID_YN = 'Y'                                                                                                                                                             \n");
            sqlStr.append("                         AND A.NOTI_DY <= TO_CHAR(SYSDATE,'YYYYMMDD')                                                                                                                 \n");
            sqlStr.append("                         AND A.CMPT_CD = '01'                                                                                                                                                             \n");
            sqlStr.append("                         AND (A.OUT_ITEM_CD IN (SELECT OUT_ITEM_CD FROM SUPP_OUT_ITEM WHERE CORP_ID =  ?)                                                                                                             \n");
            sqlStr.append("                            OR A.MATERIAL_GB IN (SELECT SC.PARENT_LEVEL FROM SUPP_PUR_ITEM SA , PMM_MATERIALGROUP_V SB , PMM_MATERIALGROUP_V SC WHERE SA.KIND_SMALL=SB.MATERIALGROUP AND SB.PARENT_LEVEL = SC.MATERIALGROUP AND SA.CORP_ID =  ? GROUP BY SC.PARENT_LEVEL))                                                                         \n");                            
            sqlStr.append("                         AND A.STATUS_CD IN ('04','05','06')                                                                                                                                                             \n");
            sqlStr.append("                         AND B.VENDOR_CD(+) = ?) AA WHERE 1=1                                                                             \n");
            sqlStr.append("                         AND ((AA.SITEDESC_FLAG = 'Y' AND AA.SITEPARTI_YN = 'Y') OR AA.SITEDESC_FLAG = 'N')                                                                                            \n");         
            if(storage.getUser().get("JOB_DIV").equals("O"))
            {
                sqlStr.append("                         AND DEMANDED_KIND = 'O'                                                                                            \n");
            }
            else
            {
                sqlStr.append("                         AND DEMANDED_KIND != 'O'                                                                                            \n");
            }
            sqlStr.append("                         UNION ALL                                                                               \n");
            sqlStr.append("                         SELECT *                                                                         \n");                                                                           
            sqlStr.append("                         FROM (                                                                                                                                                            \n");                     
            sqlStr.append("                         SELECT A.BID_TO_DT,                                                                                                                                                             \n");
            sqlStr.append("                                A.STATUS_CD,                                                                                                                                                                 \n");
            sqlStr.append("                                A.BID_NO,                                                                                                                                                                     \n");
            sqlStr.append("                                A.BID_DEG,                                                                                                                                                                     \n");
            sqlStr.append("                                A.BID_MASTER_NM,                                                                                                                                                         \n");
            sqlStr.append("                                A.DEMANDED_TITLE,                                                                                                                                                         \n");
            sqlStr.append("                                A.SITEDESC_FLAG,                                                                                                                                                         \n");
            sqlStr.append("                                A.BID_FROM_DT,                                                                                                                                                             \n");
            sqlStr.append("                                 CASE WHEN A.BID_TO_DT > SYSDATE AND A.STATUS_CD IN ('04','05','06') THEN '04'                                                                                 \n");
            sqlStr.append("                                 WHEN A.BID_TO_DT < SYSDATE AND A.STATUS_CD IN ('04','05','06') THEN '04E'                                                                                     \n");
            sqlStr.append("                                 ELSE '' END AS STATUS_NM,                                                                                                                                             \n");
            sqlStr.append("                                B.SITEPARTI_YN,                                                                                                                                                             \n");
            sqlStr.append("                                B.BID_RNNC_ID,                                                                                                                                                             \n");
            sqlStr.append("                                B.BID_RNNC_NM,                                                                                                                                                             \n");
            sqlStr.append("                                B.BID_RNNC_DT,                                                                                                                                                             \n");
            sqlStr.append("                                B.RNNC_REASON,                                                                                                                                                             \n");
            sqlStr.append("                                B.VENDOR_CD,                                                                                                                                                                 \n");
            sqlStr.append("                                A.DEMANDED_ID,                                                                                                                                                             \n");
            sqlStr.append("                                A.DEMANDED_KIND,                                                                                                                                                         \n");
            sqlStr.append("                                DECODE(A.POSTPONE_REASON,'','N','Y') AS POSTPONE_YN,                                                                                                     \n");
            sqlStr.append("                                DECODE(A.MODIFY_REASON,'','N','Y') AS MODIFY_YN,                                                                                                             \n");
            sqlStr.append("                                DECODE(B.BID_RNNC_DT,NULL,DECODE(B.SIGN_DT,NULL,'미제출',TO_CHAR(B.SIGN_DT,'YYYY-MM-DD HH24:MI:SS')),'입찰포기') AS SIGN_DT                                                                           \n");  
            sqlStr.append("                         FROM BID_MASTER A  ,BID_SUPP B                                                                                                                                             \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.ONBID_YN = 'Y'                                                                                                                                                             \n");
            sqlStr.append("                         AND A.NOTI_DY <= TO_CHAR(SYSDATE,'YYYYMMDD')                                                                                                                 \n");
            sqlStr.append("                         AND A.CMPT_CD <> '01'                                                                                                                                                         \n");
            sqlStr.append("                         AND A.STATUS_CD IN ('04','05','06')                                                                                                                                                             \n");
            sqlStr.append("                         AND B.VENDOR_CD = ?) AA                                                                         \n");
            sqlStr.append("                          WHERE 1=1                                                                             \n");
            sqlStr.append("                         AND ((AA.SITEDESC_FLAG = 'Y' AND AA.SITEPARTI_YN = 'Y') OR AA.SITEDESC_FLAG = 'N')                                                                         \n");         
            if(storage.getUser().get("JOB_DIV").equals("O"))
            {
                sqlStr.append("                         AND DEMANDED_KIND = 'O'                                                                                            \n");
            }
            else
            {
                sqlStr.append("                         AND DEMANDED_KIND != 'O'                                                                                            \n");
            }
            sqlStr.append("                         )                                                                         \n");
            sqlStr.append("                         UNION ALL                                                                         \n");
            sqlStr.append("                          SELECT COUNT(*) CNT, '3' GUBUN                                                                         \n");
            sqlStr.append("                            FROM BID_MASTER A, BID_SUPP B, ACZ10100V C , TBE_INFO D, TBE_SUPP E                                                                                 \n");
            sqlStr.append("                              ,(SELECT MAX(REQ_DEG) AS REQ_DEG,BID_DEG,BID_NO,VENDOR_CD FROM TBE_SUPP_CHASU WHERE 1=1 GROUP BY BID_NO,BID_DEG,VENDOR_CD) SC                                                                         \n"); 
            sqlStr.append("                              ,TBE_SUPP_CHASU TSC                                                                                                                                                 \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.DEPT_CD=C.DEPT_CD                                                                                                                                         \n");
            sqlStr.append("                              AND A.BID_NO = D.BID_NO                                                                                                                                             \n");
            sqlStr.append("                              AND A.BID_DEG=D.BID_DEG                                                                                                                                         \n");
            sqlStr.append("                              AND B.BID_NO=E.BID_NO(+)                                                                                                                                         \n");
            sqlStr.append("                              AND B.BID_DEG=E.BID_DEG(+)                                                                                                                                     \n");
            sqlStr.append("                              AND E.BID_NO=SC.BID_NO(+)                                                                                                                                         \n");
            sqlStr.append("                              AND E.BID_DEG=SC.BID_DEG(+)                                                                                                                                     \n");
            sqlStr.append("                              AND E.VENDOR_CD=SC.VENDOR_CD(+)                                                                                                                             \n");
            sqlStr.append("                              AND SC.BID_NO=TSC.BID_NO(+)                                                                                                                                     \n");
            sqlStr.append("                              AND SC.BID_DEG=TSC.BID_DEG(+)                                                                                                                                 \n");
            sqlStr.append("                              AND SC.VENDOR_CD=TSC.VENDOR_CD(+)                                                                                                                         \n");
            sqlStr.append("                              AND SC.REQ_DEG=TSC.REQ_DEG(+)                                                                                                                                 \n");
            sqlStr.append("                              AND B.VENDOR_CD=E.VENDOR_CD(+)                                                                                                                             \n");
            sqlStr.append("  							AND A.TBE_FLAG = 'Y'													\n");
            sqlStr.append("  							AND A.ONBID_YN = 'Y'													\n");
            sqlStr.append("                            AND B.VENDOR_CD= ?                                                                                                         \n");
            sqlStr.append("                              AND A.STATUS_CD IN ('04','05','06','07')                                                                                 \n");
            if(storage.getUser().get("JOB_DIV").equals("O"))
            {
                sqlStr.append("                         AND DEMANDED_KIND = 'O'                                                                                            \n");
            }
            else
            {
                sqlStr.append("                         AND DEMANDED_KIND != 'O'                                                                                            \n");
            }
            sqlStr.append("                              UNION ALL                                                                         \n");
            sqlStr.append("                        SELECT COUNT(*) CNT, '4' GUBUN   FROM    (                                                                                                                            \n");
            sqlStr.append("                        SELECT A.*, B.*, DECODE(A.STATUS_CD,'10',B.SUCC_YN,'') AS SUCCESS_YN                                                                                                                            \n");        
            sqlStr.append("                         FROM BID_MASTER A  ,BID_SUPP B                                                                                                                                         \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.OPEN_DT IS NOT NULL                                                                                                                                             \n");
            sqlStr.append(" 						AND A.STATUS_CD IN ( '10')																	\n");
            sqlStr.append("                         AND B.VENDOR_CD =  ?                                                                                           \n");
//            sqlStr.append("                         AND STATUS_CD = '10'                                                                                      \n");
//            sqlStr.append("                         --AND SUCC_YN = 'Y'                                                                                      \n");
            
            if(storage.getUser().get("JOB_DIV").equals("O"))
            {
                sqlStr.append("                         AND DEMANDED_KIND = 'O'                                                                                            \n");
            }
            else
            {
                sqlStr.append("                         AND DEMANDED_KIND != 'O'                                                                                            \n");
            }
            sqlStr.append("                )         )                                                                                                                   \n");

            
            
            search.setStatement(sqlStr.toString());
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            
            
            //System.out.println(sqlStr.toString());
            //System.out.println(search.getParameters());
            
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            //System.out.println("getBidCnt=========>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return vo;
        }
    }
	
	
	
    
    private ValueObject getSuppContCnt(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        ValueObject vo = null;
        
        try {
            sqlStr.append("    SELECT                                                                      \n"); 
            sqlStr.append("                        MIN(DECODE(GUBUN, '1', CNT) ) CNT_1,                                           \n");
            sqlStr.append("                        MIN(DECODE(GUBUN, '2', CNT) ) CNT_2,                                           \n");
            sqlStr.append("                        MIN(DECODE(GUBUN, '3', CNT) ) CNT_3,                                           \n");
            sqlStr.append("                        MIN(DECODE(GUBUN, '4', CNT) ) CNT_4                                           \n");
            sqlStr.append("                    FROM(                                                \n");
            sqlStr.append("                         SELECT COUNT(*) CNT, '1' GUBUN                                           \n");
            sqlStr.append("                           FROM SUB2000T A, CONT_OUT_HTML B, CONT_TEMPLATE C, CONT_OUT_SUPP F                                           \n");    
            sqlStr.append("                       WHERE 1=1                                                               \n");
            sqlStr.append("                           AND A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                       \n");        
            sqlStr.append("                           AND A.TEMPLATE_CD = C.TEMPLATE_CD                                                               \n");
            sqlStr.append("                           AND B.SEQ=0                                                               \n");
            sqlStr.append("                           AND C.SEQ=0                                                               \n");
            sqlStr.append("                           AND A.SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID                                                           \n");
            sqlStr.append("                           AND F.SIGN_SEQ = 2                                                           \n");
            sqlStr.append("                           AND F.CORP_ID = ?                                                       \n");
            sqlStr.append("                           AND MOD_NO = 0                                                       \n");   
            sqlStr.append("                       AND A.STATUS_CD IN (20,30,40,41)                                                         \n");
            sqlStr.append("                       UNION ALL                                                                                               \n");
            sqlStr.append("                            SELECT COUNT(*) CNT, '2' GUBUN                                           \n");
            sqlStr.append("                           FROM SUB2000T A, CONT_OUT_HTML B, CONT_TEMPLATE C, CONT_OUT_SUPP F                                           \n");    
            sqlStr.append("                       WHERE 1=1                                                               \n");
            sqlStr.append("                           AND A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                       \n");        
            sqlStr.append("                           AND A.TEMPLATE_CD = C.TEMPLATE_CD                                                               \n");
            sqlStr.append("                           AND B.SEQ=0                                                               \n");
            sqlStr.append("                           AND C.SEQ=0                                                               \n");
            sqlStr.append("                           AND A.SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID                                                           \n");
            sqlStr.append("                           AND F.SIGN_SEQ = 2                                                           \n");
            sqlStr.append("                           AND F.CORP_ID = ?                                                       \n");
            sqlStr.append("                           AND MOD_NO = 0                                                       \n");
            sqlStr.append("                       AND A.STATUS_CD IN (50)                                                           \n");
            sqlStr.append("                       UNION ALL                                                                                               \n");
            sqlStr.append("                         SELECT COUNT(*) CNT, '3' GUBUN                                           \n");
            sqlStr.append("                           FROM SUB2000T A, CONT_OUT_HTML B, CONT_TEMPLATE C, CONT_OUT_SUPP F                                           \n");    
            sqlStr.append("                       WHERE 1=1                                                               \n");
            sqlStr.append("                           AND A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                       \n");        
            sqlStr.append("                           AND A.TEMPLATE_CD = C.TEMPLATE_CD                                                               \n");
            sqlStr.append("                           AND B.SEQ=0                                                               \n");
            sqlStr.append("                           AND C.SEQ=0                                                               \n");
            sqlStr.append("                           AND A.SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID                                                           \n");
            sqlStr.append("                           AND F.SIGN_SEQ = 2                                                           \n");
            sqlStr.append("                           AND F.CORP_ID = ?                                                       \n");
            sqlStr.append("                           AND MOD_NO > 0                                                       \n");
            sqlStr.append("                       AND A.STATUS_CD IN (20,30,40,41)                                                         \n");
            sqlStr.append("                       UNION ALL                                                                                               \n");
            sqlStr.append("                            SELECT COUNT(*) CNT, '4' GUBUN                                           \n");
            sqlStr.append("                           FROM SUB2000T A, CONT_OUT_HTML B, CONT_TEMPLATE C, CONT_OUT_SUPP F                                           \n");    
            sqlStr.append("                       WHERE 1=1                                                               \n");
            sqlStr.append("                           AND A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                       \n");        
            sqlStr.append("                           AND A.TEMPLATE_CD = C.TEMPLATE_CD                                                               \n");
            sqlStr.append("                           AND B.SEQ=0                                                               \n");
            sqlStr.append("                           AND C.SEQ=0                                                               \n");
            sqlStr.append("                           AND A.SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID                                                           \n");
            sqlStr.append("                           AND F.SIGN_SEQ = 2                                                           \n");
            sqlStr.append("                           AND F.CORP_ID = ?                                                       \n");
            sqlStr.append("                           AND MOD_NO > 0                                                       \n");
            sqlStr.append("                       AND A.STATUS_CD IN (50)                                                           \n");
            sqlStr.append("                    )                                           \n");
            
            
            search.setStatement(sqlStr.toString());
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            search.addParameter(storage.getUser().get("CORP_ID"));

            
            
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            //System.out.println("getSuppContCnt=========>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return vo;
        }
    }
	
    
    
    private ValueObject getPayCnt(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        ValueObject vo = null;
        
        try {
            if(storage.getUser().get("JOB_DIV").equals("P"))
            {
                sqlStr.append("         SELECT                                                                                      \n");
                sqlStr.append("                            MIN(DECODE(GUBUN, '1', CNT) ) CNT_1,                        \n");                                  
                sqlStr.append("                            MIN(DECODE(GUBUN, '2', CNT) ) CNT_2,                                             \n");             
                sqlStr.append("                            MIN(DECODE(GUBUN, '3', CNT) ) CNT_3,                                                          \n");
                sqlStr.append("                            MIN(DECODE(GUBUN, '4', CNT) ) CNT_4                                                          \n");
                sqlStr.append("                        FROM(                                                           \n");
                sqlStr.append("                         SELECT     COUNT(*) CNT, '1' GUBUN                                     \n");
                sqlStr.append("                         FROM PUR2000T A, PUR1030T B    ,CONT_PUR_SUPP C1                                  \n");                                                                               
                sqlStr.append("                         WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID                                                          \n");                                                         
                sqlStr.append("                         AND A.PUR_CONT_MOD_ID = C1.PUR_CONT_MOD_ID                                                                                 \n");                                  
                sqlStr.append("                         AND C1.SIGN_SEQ = 2                                                                                                                                           \n");
                sqlStr.append("                         AND A.CON_CLS_YN = '9'                                                                                                                                           \n");
                sqlStr.append("                         AND B.PREPAY_AMT > 0                                                                                                                                       \n");
                sqlStr.append("                         AND C1.CORP_ID  = ?                                                                                                                        \n");
                sqlStr.append("                         AND B.PREPAY_PROC_GB NOT IN( '10', '11')                                                                                                       \n");
                sqlStr.append("                         UNION ALL                                                                                                                                          \n");
                sqlStr.append("                          SELECT     COUNT(*) CNT, '2' GUBUN                                                        \n");
                sqlStr.append("                         FROM PUR2000T A, PUR1030T B    ,CONT_PUR_SUPP C1                                                      \n");                                                           
                sqlStr.append("                         WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID                                                                              \n");                                     
                sqlStr.append("                         AND A.PUR_CONT_MOD_ID = C1.PUR_CONT_MOD_ID                                                                                                     \n");              
                sqlStr.append("                         AND C1.SIGN_SEQ = 2                                                                                                                                           \n");
                sqlStr.append("                         AND A.CON_CLS_YN = '9'                                                                                                                                           \n");
                sqlStr.append("                         AND B.PREPAY_AMT > 0                                                                                                                                       \n");
                sqlStr.append("                         AND C1.CORP_ID  = ?                                 \n");
                sqlStr.append("                        AND B.PREPAY_PROC_GB IN( '10', '11')                                 \n");                                        
                sqlStr.append("                         UNION ALL                                                                                                              \n");
                sqlStr.append("                        SELECT COUNT(*) CNT, '3' GUBUN                                                                                                          \n");
                sqlStr.append("                         FROM PUR1000T A, PUR1100T B, CONT_PUR_SUPP C1                                                                                                          \n");
                sqlStr.append("                         WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                                                                                                          \n");
                sqlStr.append("                         AND C1.SIGN_SEQ = 2                                                                                                          \n");
                sqlStr.append("                         AND B.PUR_CONT_MOD_ID = C1.PUR_CONT_MOD_ID                                                                                                          \n");
                sqlStr.append("                         AND TO_CHAR(B.REQ_DATE,'YYYY-MM-DD') BETWEEN '" + params.get("yyyy-MM") + "-01'    AND '" + params.get("yyyy-MM") + "-31'                                                 \n");
                sqlStr.append("                         AND B.REQ_STATUS != '10'                                                    \n");
                sqlStr.append("                         AND C1.CORP_ID  = ?                                                        \n");
                sqlStr.append("                         UNION ALL                                                          \n");
                sqlStr.append("                         SELECT COUNT(*) CNT, '4' GUBUN                                          \n");
                sqlStr.append("                         FROM PUR1000T A, PUR1100T B, CONT_PUR_SUPP C1                \n");                                       
                sqlStr.append("                         WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                                         \n");                              
                sqlStr.append("                         AND C1.SIGN_SEQ = 2                                                                             \n");         
                sqlStr.append("                         AND B.PUR_CONT_MOD_ID = C1.PUR_CONT_MOD_ID                                              \n");              
                sqlStr.append("                         AND TO_CHAR(B.REQ_DATE,'YYYY-MM-DD') BETWEEN '" + params.get("yyyy-MM") + "-01'    AND '" + params.get("yyyy-MM") + "-31'                       \n");   
                sqlStr.append("                         AND B.REQ_STATUS = '10'                                                                 \n");
                sqlStr.append("                         AND C1.CORP_ID  = ?                                                                             \n");
                sqlStr.append("                        )                                                                                                             \n");
                
                search.setStatement(sqlStr.toString());
                
                search.addParameter(storage.getUser().get("CORP_ID"));
                search.addParameter(storage.getUser().get("CORP_ID"));
                search.addParameter(storage.getUser().get("CORP_ID"));
                search.addParameter(storage.getUser().get("CORP_ID"));
                
            }
            else
            {
                sqlStr.append("      SELECT                                                                                                     \n");
                sqlStr.append("                        MIN(DECODE(GUBUN, '1', CNT) ) CNT_1,                                          \n"); 
                sqlStr.append("                        MIN(DECODE(GUBUN, '2', CNT) ) CNT_2,                                    \n");       
                sqlStr.append("                        MIN(DECODE(GUBUN, '3', CNT) ) CNT_3,                              \n");             
                sqlStr.append("                        MIN(DECODE(GUBUN, '4', CNT) ) CNT_4                         \n");                  
                sqlStr.append("                    FROM(                                                                        \n");
                sqlStr.append("                     SELECT      COUNT(*) CNT, '1' GUBUN                                                  \n");
                sqlStr.append("                     FROM                                                                                                                           \n");
                sqlStr.append("                          SUB2000T A1,                                                                                                              \n");
                sqlStr.append("                          SUB1030T B1                                                                                                              \n");
                sqlStr.append("                          ,CONT_OUT_SUPP C1                                                                                                          \n");
                sqlStr.append("                     WHERE A1.SUB_CONT_MOD_ID = B1.SUB_CONT_MOD_ID                                                                      \n");                  
                sqlStr.append("                     AND A1.CON_CLS_YN = '9'                                                                                                        \n");
                sqlStr.append("                     AND A1.PREPAY_AMT > 0                                                                                                   \n");     
                sqlStr.append("                     AND B1.STATUS_CD NOT IN('10', '11')                                                                            \n");                
                sqlStr.append("                     AND A1.SUB_CONT_MOD_ID = C1.SUB_CONT_MOD_ID                                                 \n");                                   
                sqlStr.append("                     AND C1.SIGN_SEQ = 2                                                                                     \n");                       
                sqlStr.append("                     AND C1.CORP_ID  = ?                                                                        \n");
                sqlStr.append("                    UNION ALL                                                                                                                            \n");
                sqlStr.append("                      SELECT      COUNT(*) CNT, '2' GUBUN                                                                                     \n");                                
                sqlStr.append("                     FROM                                                                                                                           \n");
                sqlStr.append("                          SUB2000T A1,                                                                                                              \n");
                sqlStr.append("                          SUB1030T B1                                                                                                           \n");   
                sqlStr.append("                          ,CONT_OUT_SUPP C1                                                                                          \n");                
                sqlStr.append("                     WHERE A1.SUB_CONT_MOD_ID = B1.SUB_CONT_MOD_ID                                          \n");                                              
                sqlStr.append("                     AND A1.CON_CLS_YN = '9'                                                                            \n");                            
                sqlStr.append("                     AND A1.PREPAY_AMT > 0                                                                       \n");                                 
                sqlStr.append("                     AND B1.STATUS_CD IN('10', '11')                                                       \n");                                         
                sqlStr.append("                     AND A1.SUB_CONT_MOD_ID = C1.SUB_CONT_MOD_ID                     \n");                                                               
                sqlStr.append("                     AND C1.SIGN_SEQ = 2                                                         \n");
                sqlStr.append("                      AND C1.CORP_ID  = ?                                                  \n");                                                                                 
                sqlStr.append("                    UNION ALL                                                                                \n");
                sqlStr.append("                    SELECT COUNT(*) CNT, '3' GUBUN                                             \n");
                sqlStr.append("                     FROM  SUB2000T A, SUB1020T B, SUB1040T C,                                                     \n");   
                sqlStr.append("                           CONT_OUT_SUPP C1                                                                            \n");
                sqlStr.append("                     WHERE 1=1                                                                                        \n");
                sqlStr.append("                     AND A.SUB_CONT_ID = C.SUB_CONT_ID                                                                \n");
                sqlStr.append("                     AND A.MOD_NO = C.MOD_NO                                                                           \n"); 
                sqlStr.append("                     AND C.CON_CORP_ID = B.CON_CORP_ID                                                \n");
                sqlStr.append("                     AND C1.SUB_CONT_MOD_ID = A.SUB_CONT_MOD_ID                         \n");                               
                sqlStr.append("                     AND B.MAIN_CORP = 'Y'                                                         \n");                   
                sqlStr.append("                     AND C.ACMP_YYYYMM = ?                                               \n");
                sqlStr.append("                     AND C1.CORP_ID  = ?                                                           \n");
                sqlStr.append("                     AND C.STATUS_CD != '10'                                                                                                  \n");
                sqlStr.append("                    UNION ALL                                                                            \n");
                sqlStr.append("                    SELECT COUNT(*) CNT, '4' GUBUN                                             \n");
                sqlStr.append("                     FROM  SUB2000T A, SUB1020T B, SUB1040T C,                                                     \n");   
                sqlStr.append("                           CONT_OUT_SUPP C1                                                                            \n");
                sqlStr.append("                     WHERE 1=1                                                                                        \n");
                sqlStr.append("                     AND A.SUB_CONT_ID = C.SUB_CONT_ID                                                                \n");
                sqlStr.append("                     AND A.MOD_NO = C.MOD_NO                                                                            \n");
                sqlStr.append("                     AND C.CON_CORP_ID = B.CON_CORP_ID                                                                                             \n");
                sqlStr.append("                     AND C1.SUB_CONT_MOD_ID = A.SUB_CONT_MOD_ID                                                                                             \n");
                sqlStr.append("                     AND B.MAIN_CORP = 'Y'                                                                                                        \n");                                                                 
                sqlStr.append("                     AND C.ACMP_YYYYMM = ?                                                                                                    \n");
                sqlStr.append("                     AND C.STATUS_CD = '10'                                                                                                  \n");
                sqlStr.append("                     AND C1.CORP_ID  = ?                                                                                        \n"); 
                sqlStr.append("                     )                                                                                        \n");
                
                
                search.setStatement(sqlStr.toString());
                
                search.addParameter(storage.getUser().get("CORP_ID"));
                search.addParameter(storage.getUser().get("CORP_ID"));
                search.addParameter(params.get("yyyymm"));
                search.addParameter(storage.getUser().get("CORP_ID"));
                search.addParameter(params.get("yyyymm"));
                search.addParameter(storage.getUser().get("CORP_ID"));
                
                
            }
                
            
            //System.out.println(sqlStr.toString());
            //System.out.println(search.getParameters());
                
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            //System.out.println("getAdvencePayCnt=========>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return vo;
        }
    }
    
    
    
	
	private RowSet getSuppNotiList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr = new StringBuffer();
        try {
        	sqlstr.append("	SELECT          	\n");
        	sqlstr.append("			NOTI_DY    	\n");
        	sqlstr.append("		, NOTI_DIV    	\n");
        	sqlstr.append("		, SEQ         	\n");
        	sqlstr.append("		, DECODE(SUPP_OPEN_YN, 'Y', '공개', '비공개') SUPP_OPEN_YN	\n");
        	sqlstr.append("		, TITLE       	\n");
        	sqlstr.append("		, WRITE_ID    	\n");
        	sqlstr.append("		, WRITE_NM    	\n");
        	sqlstr.append("	FROM NOTI       	\n");
        	sqlstr.append("	WHERE NOTI_DIV = '1'			\n");
        	sqlstr.append("	AND SUPP_OPEN_YN = 'Y'	\n");
        	
        	if(!params.get("p_title").trim().equals("")) {
        		sqlstr.append("	AND TITLE LIKE '%' || ? || '%'       	\n");
        		search.addParameter(params.get("p_title").trim());
        	}
        	
        	sqlstr.append("	ORDER BY NOTI_DY DESC, SEQ DESC	\n");
             
            WAFLogger.debug(sqlstr.toString());
        	search.setStatement(sqlstr);
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
	}
}

