/**
 * @(#)file    TermsAgreeBiz.java
 * @(#)author  이성민
 * @(#)version 1.0
 *
 */
package kr.co.udapsoft.ebid.supplier.portal.result.biz;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

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.WAFSQLException;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.ui.InitCombo;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;






public class PurResultTargetListBiz extends Biz {

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

	/* (non-Javadoc)
	 * @see kr.co.hwenc.system.Biz#doBiz()
	 */
	public void doBiz() throws Exception 
	{
	    //System.out.println(":::::::::::::::::::::::::::::::::::::::::::::::::::::::ResultTargetListBiz::::::::::::::::::::::::::::::::::::::::::::::::::::::::" + storage.getEvent());
	    //System.out.println(storage.getUser());
	    
	    if(storage.compareEvent(""))
        {
	        InitCombo initCombo      = new InitCombo();
	        RowSet suppResultIssueRowSet = null;
	        
	        suppResultIssueRowSet = getSuppResultIssueRowSet(params);
	        initCombo.makeComboData(suppResultIssueRowSet, "detailnm", "detailcd", "", "선택");
	        
	        storage.addObject("issueCode", initCombo.getCode());
	        storage.addObject("issueName", initCombo.getCodeName());
	        
        }
	    else if(storage.compareEvent("GET_TARGET_LIST"))
	    {
	        if(params.get("acmp_yyyy").equals(""))
            {
                Calendar cal = Calendar.getInstance();
                cal.add(cal.YEAR,-1);
                
                String yyyy = (new java.text.SimpleDateFormat ("yyyy")).format (cal.getTime());
                params.set("acmp_yyyy", yyyy);
            }
            
            
            params.set("sheet1", getResultTargetList(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
	    }
	    
	}
	
	
	private RowSet getSuppResultIssueRowSet(ValueObject Params)
	{
	    Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
	    try
	    {
	        
	        sqlStr.append("      SELECT DETAILCD, DETAILNM FROM EBID_COM911T WHERE BASECD = 'SUPP_RESULT_ISSUE'      ");
	        
	        search.setStatement(sqlStr.toString());
	        rowSet = search.execute(); 
	        
	        
	    }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 rowSet;
        }
	}
	
	
	private RowSet getResultTargetList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {                                                                                                                                                                    
            sqlStr.append("            SELECT PUR_CONT_MOD_ID -- 구매계약변경ID                                                    \n")                                                                                                          
                   .append("                         ,ACMP_YYYY -- 기성년도                                                                                                               \n")  
                   .append("                         ,DEPT_CD -- 현장코드                                                                                                                   \n")
                   .append("                         ,DEPT_NAME -- 현장명                                                                                                                  \n")                                                                                                               
                   .append("                         ,CORP_ID                                                                    \n")  
                   .append("                        ,ISU_VENDCD -- 신청인발급요청자) 사업자번호                                                                              \n")
                   .append("                        ,ISU_VENDNM -- 신청인(발급요청자) 상호                                                                                      \n")                  
                   .append("                        ,ISU_PRES_NM -- 신청인(발급요청자) 대표자명                                                                              \n")                       
                   .append("                        ,ISU_ADDR -- 신청인(발급요청자) 주소                                                                                          \n")
                   .append("                        ,ISU_PHONE -- 신청인(발급요청자) 전화번호                                                                                  \n")
                   .append("                        ,CONT_NM -- 공종(계약)명                                                                                                               \n")
                   .append("                        ,WORK_PLACE -- 현장소재지 (공사현장)                                              \n")                                         
                   .append("                        ,CON_AMT -- 공종(계약)금액                                                                                                            \n")
                   .append("                        ,CONT_CDATE -- 계약년월일 (YYYYMMDD)                                            \n")                                                               
                   .append("                        ,CONT_SDATE -- 착공년월일 (YYYYMMDD)                                            \n")                                                           
                   .append("                        ,CONT_EDATE-- 준공년월일 (YYYYMMDD)                                             \n")                                                       
                   .append("                        ,CONT_TAMT --  총계약금액 (총공사금액)                                            \n")                                                      
                   .append("                        ,CUR_PAY_AMT -- 당년도 기성액                                                                                                       \n")
                   .append("                        ,ORD_NM -- 발주자                                                                                                                            \n")
                   .append("                        ,CUR_CONT_AMT -- 당년도 계약액 (이월액)                                           \n")                                                        
                   .append("                        ,CUR_PAY_NAMT -- 당년도 미기성액                                                                                                  \n")
                   .append("                        ,CASE WHEN NVL(PRV_CONT_AMT,0) > 0 THEN CON_AMT - (NVL(PRV_CONT_AMT,0)/1000) ELSE 0 END CUR_CONT_AAMT -- 당년도추가(변경)계약액                                         \n")
                   .append("                        ,PRV_FUND_AMT-- 전년도까지 누계기성액                                                                                                                                                                                                                               \n")
                   .append("                        ,CASE WHEN SUBSTR(CONT_SDATE,0,4) < ? THEN NVL(PRV_CONT_AMT,0) - PRV_FUND_AMT ELSE 0 END PRV_PAY_NAMT -- 전년도까지 미기성액                                                \n")
                   .append("                        ,NVL(PRV_CONT_AMT,0) - PRV_FUND_AMT PRV_TRANS_AMT -- 전년도이월액 or 전년도이월공사액                                                                                                                                  \n")
                   .append("                        ,(SELECT CORP_KOR FROM HEAD_OFFICE_INFO WHERE CORP_REG_NO = ?) REG_VENDNM -- 발급자 상호                                                                                                                     \n")
                   .append("                        ,(SELECT REP_NAME FROM HEAD_OFFICE_INFO WHERE CORP_REG_NO = ?) REG_PRES_NM -- 발급자 대표자명                                                                                                             \n")
                   .append("                        ,(SELECT ADDRESS FROM HEAD_OFFICE_INFO WHERE CORP_REG_NO = ?) REG_ADDR -- 발급자 주소                                                                                                                            \n")
                   .append("                        ,(SELECT CORP_TEL FROM HEAD_OFFICE_INFO WHERE CORP_REG_NO = ?) REG_PHONE -- 발급자 전화번호                                                                                                                  \n")
                   .append("                        ,'00' PROG_STAT_CD  -- 진행상태 코드                                                                                                                                     \n")
                   .append("                        ,CNT_SOC_CD_01                                                                                      \n")
                   .append("                        ,CNT_SOC_CD_02                                                                                      \n")
                   .append("                        ,CNT_SOC_CD_03                                                                                      \n")
                   .append("                        ,CNT_SOC_CD_04                                                                                      \n")
                   .append("                        ,CNT_SOC_CD_05                                                                                      \n")
                   .append("             FROM(                                                                                                          \n")
                   .append("             SELECT A.PUR_CONT_MOD_ID -- 구매계약변경ID                                                                       \n")
                   .append("                         ,? 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.CST_DOC_NAME CONT_NM -- 공종(계약)명                                                                                                                                 \n")
                   .append("                        ,A.LEAD_PLACE WORK_PLACE -- 현장소재지 (공사현장)                                                                 \n")
                   .append("                        ,a.ORD_AMT	CON_AMT -- 공종(계약)명                                                                                                                                                       \n")
                   .append("                        ,TO_CHAR(F.ORD_CON_DATE,'YYYYMMDD') CONT_CDATE -- 계약년월일 (YYYYMMDD)                                   \n")
                   .append("                        ,TO_CHAR(A.ORD_START_DATE,'YYYYMMDD') CONT_SDATE -- 착공년월일 (YYYYMMDD)                            \n")
                   .append("                        ,TO_CHAR(A.ORD_END_DATE,'YYYYMMDD') CONT_EDATE-- 준공년월일 (YYYYMMDD)                               \n")
                   .append("                        ,FLOOR(A.ORD_AMT / 1000)  CONT_TAMT --  총계약금액 (총공사금액)                                         \n")
            //       .append("                        ,FLOOR(NVL(D.FUND_AMT,0)/1000) CUR_PAY_AMT -- 당년도 기성액                                                                                    \n")
                   .append("                        ,FLOOR((NVL(D.FUND_AMT,0)+NVL(G.THIS_PRE_AMT,0))/1000) CUR_PAY_AMT -- 당년도 기성액                                        \n") //2016.1.19
                   .append("                        ,A.DEALER_NM ORD_NM -- 발주자                                                                                                                                                 \n")
                   .append("                        ,FLOOR((A.ORD_AMT - NVL(E.FUND_AMT,0)) / 1000) CUR_CONT_AMT -- 당년도 계약액 (이월액)                    \n")
                   .append("                        ,FLOOR((A.ORD_AMT - (NVL(D.FUND_AMT,0) + NVL(E.FUND_AMT,0))) / 1000) CUR_PAY_NAMT -- 당년도 미기성액        \n")
                   .append("                        ,(SELECT ORD_AMT FROM PUR2000T WHERE PUR_CONT_MOD_ID = (SELECT MAX(PUR_CONT_MOD_ID) FROM PUR2000T WHERE TO_CHAR(A.ORD_CON_DATE,'YYYYMMDD') = ?-1)) PRV_CONT_AMT                                                \n")
                   .append("                        ,FLOOR(NVL(E.FUND_AMT,0) / 1000) PRV_FUND_AMT-- 전년도까지 누계기성액                                                                                                                 \n")
                   .append("                        ,(SELECT COUNT(*) FROM PUR_RESULT_PLOOF_INFO WHERE PUR_CONT_MOD_ID = A.PUR_CONT_MOD_ID AND ACMP_YYYY = D.ACMP_YYYY AND CORP_ID = ?) PLOOF_INFO_CNT                                                \n")
                   .append("                        ,(SELECT COUNT(*) FROM PUR_RESULT_PLOOF_INFO WHERE PUR_CONT_MOD_ID = A.PUR_CONT_MOD_ID AND ACMP_YYYY = ? AND SOC_CD='01') CNT_SOC_CD_01                                                \n")
                   .append("                        ,(SELECT COUNT(*) FROM PUR_RESULT_PLOOF_INFO WHERE PUR_CONT_MOD_ID = A.PUR_CONT_MOD_ID AND ACMP_YYYY = ? AND SOC_CD='02') CNT_SOC_CD_02                                                \n")
                   .append("                        ,(SELECT COUNT(*) FROM PUR_RESULT_PLOOF_INFO WHERE PUR_CONT_MOD_ID = A.PUR_CONT_MOD_ID AND ACMP_YYYY = ? AND SOC_CD='03') CNT_SOC_CD_03                                                \n")
                   .append("                        ,(SELECT COUNT(*) FROM PUR_RESULT_PLOOF_INFO WHERE PUR_CONT_MOD_ID = A.PUR_CONT_MOD_ID AND ACMP_YYYY = ? AND SOC_CD='04') CNT_SOC_CD_04                                                \n")
                   .append("                        ,(SELECT COUNT(*) FROM PUR_RESULT_PLOOF_INFO WHERE PUR_CONT_MOD_ID = A.PUR_CONT_MOD_ID AND ACMP_YYYY = ? AND SOC_CD='05') CNT_SOC_CD_05                                                \n")
                   .append("             FROM PUR2000T A                                                                                  \n")
                   .append("                 ,(SELECT MAX(PUR_CONT_MOD_ID) PUR_CONT_MOD_ID                                                \n")
                   .append("                     FROM PUR2000T                                                                            \n")
                   .append("                    WHERE CON_CLS_YN = '9'                                                                    \n")
                   .append("                      and to_char(ord_end_date,'yyyy') >= ?                                                  \n")
                   .append("                    GROUP BY PUR_CONT_ID) B                                                                   \n")
                   .append("                 ,CONT_PUR_SUPP C                                                                             \n")
                   .append("                 ,(SELECT PUR_CONT_ID,TO_CHAR(REQ_DATE,'YYYY') ACMP_YYYY, FLOOR(SUM(CUR_REQ_APPLYAMT)) FUND_AMT       \n")
                   .append("                     FROM PUR1100T                                                                            \n")
                   .append("                    WHERE TO_CHAR(REQ_DATE,'YYYY') = ?                                                         \n")
                   .append("                    GROUP BY PUR_CONT_ID, TO_CHAR(REQ_DATE,'YYYY')) D                                          \n")
                   .append("                 ,(SELECT PUR_CONT_ID, FLOOR(SUM(CUR_REQ_APPLYAMT)) FUND_AMT                                          \n")
                   .append("                     FROM PUR1100T                                                                            \n")
                   .append("                    WHERE TO_CHAR(REQ_DATE,'YYYY') < ?                                                         \n")
                   .append("                    GROUP BY PUR_CONT_ID) E                                                                   \n")
                   .append("                 ,PUR2000T F                                                                                  \n")
		           .append("                 ,(SELECT PUR_CONT_ID                                                                         \n")
		           .append("                          ,?                                                                                  \n")
		           .append("                          ,SUM(NVL(REQ_PREPAY_AMT,0))  THIS_PRE_AMT                                           \n")
		           //.append("                         ,DECODE(SUBSTR(TAX_DY, 0, 4), ?,SUM(NVL(REQ_PREPAY_AMT,0)), 0)  THIS_PRE_AMT       \n")//2017.01.17 선급금 2회이상 지급 수정 
		           //.append("                         ,DECODE(SUBSTR(TAX_DY, 0, 4), ?,0, SUM(NVL(REQ_PREPAY_AMT,0)))   LAST_PRE_AMT      \n")
		           .append("                     FROM PUR1030T                                                                            \n")
		           .append("                    WHERE SUBSTR(TAX_DY, 0, 4) = ?                                                            \n")
		           .append("                    GROUP BY PUR_CONT_ID ) G                                                                  \n")
		           .append("             WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID                                                      \n")
                   .append("               AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                                                      \n")
                   .append("               AND A.PUR_CONT_ID = D.PUR_CONT_ID(+)                                                           \n")
                   .append("               AND A.PUR_CONT_ID = E.PUR_CONT_ID(+)                                                           \n")
                   .append("               AND A.PUR_CONT_ID = F.PUR_CONT_ID                                                              \n")
                   .append("               AND A.PUR_CONT_ID = G.PUR_CONT_ID(+)                                                           \n")
                   .append("               AND F.MOD_NO = 0                                                                               \n")
                   .append("               AND C.SIGN_SEQ = 2                                                                             \n")
                   .append("               AND NVL(A.TEMPLATE_CD,0)  NOT IN (2)                                                      \n")//2017.1.17 자유서식 포함 (1,5,7,8)
                   .append("             )                                                                                                \n");
                   if(storage.getEvent().equals("GET_LIST"))
                   {
                       sqlStr.append("             WHERE PLOOF_INFO_CNT != 0                                                              \n");
                   }
                   else
                   {
                       sqlStr.append("            WHERE (CNT_SOC_CD_01+CNT_SOC_CD_02+CNT_SOC_CD_03+CNT_SOC_CD_04+CNT_SOC_CD_05) != 5      \n");
                       //sqlStr.append("            WHERE PLOOF_INFO_CNT = 0                                                              \n");
                   }
           sqlStr.append("             AND CORP_ID  = ?                                                                                   \n");
           
           
           
           
                   if(!params.get("p_yyyy").trim().equals(""))
                   {
                       sqlStr.append("              AND ACMP_YYYY = ?                                                                     \n");
                   }
                   if(!params.get("p_dept_nm").trim().equals(""))
                   {
                       sqlStr.append("              AND DEPT_NAME LIKE '%' ||  ? || '%'                                                   \n");
                   }
                   if(!params.get("p_cont_nm").trim().equals(""))
                   {
                       sqlStr.append("              AND CONT_NM LIKE '%' ||  ? || '%'                                                     \n");
                   }
            
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(storage.getUser().get("CORP_REG_NO"));
            search.addParameter(storage.getUser().get("CORP_REG_NO"));
            search.addParameter(storage.getUser().get("CORP_REG_NO"));
            search.addParameter(storage.getUser().get("CORP_REG_NO"));
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(storage.getUser().get("CORP_ID"));
            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(params.get("acmp_yyyy"));
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(params.get("acmp_yyyy"));
            search.addParameter(params.get("acmp_yyyy"));//G 2016.1.19
            search.addParameter(params.get("acmp_yyyy"));//G 2016.1.19
            
            search.addParameter(storage.getUser().get("CORP_ID"));
            if(!params.get("p_yyyy").trim().equals(""))
            {
                search.addParameter(params.get("p_yyyy").trim());
            }
            if(!params.get("p_dept_nm").trim().equals(""))
            {
                search.addParameter(params.get("p_dept_nm").trim());
            }
            if(!params.get("p_cont_nm").trim().equals(""))
            {
                search.addParameter(params.get("p_cont_nm").trim());
            }
            
            
            System.out.println(sqlStr.toString());
            System.out.println(search.getParameters());
            rowSet = search.execute();
        }
        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 rowSet;
        }
    }
	
	
}