/**
 *
 * @(#)author  [Youngmi Im]
 * @(#)version 1.0
 * @(#)date    May 10, 2007
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.hwenc.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of Hanwha E&C, Inc.
 */
 package kr.co.udapsoft.ebid.buyer.payment.purchase.biz;


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.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.util.ValueObject;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class VendorPrintCheckListInitBiz extends Biz {
	
	private static String ACCT_TABLE     = "PUB0120T";
    private static String R_TABLE        = "PUB0340T";
    private static String R_LIST_TABLE   = "PUB0350T";
    private static String PGM_TABLE      = "PUB0320T";
    private static String PGM_GR_TABLE   = "PUB0310T";
    private static String PGM_LIST_TABLE = "PUB0300T";
    private static String PGM_SYS_TABLE  = "PUB0390T";
    
	public VendorPrintCheckListInitBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		if(storage.getEvent().equals(""))
		{
		  //검색 조건에 ComboBox데이터 설정 [상태]
	        RowSet rowSet = getPurchaseStatus_searchCombo();
	        storage.addRowSet("statusComboList", rowSet);
		}
		else if(storage.getEvent().equals("GET_LIST"))
        {
			WAFLogger.debug("=========================GET_LIST==========================");
            
            params.set("sheet1", getList(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        } 
	}
	
	
	
	
	
	/**
     * 대금관리 상태Combox 데이터를 조회 한다.
     * @param 
     * @param 
     * @return ValueObject
     */
    public RowSet getPurchaseStatus_searchCombo(){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT DETAILCD, DECODE(DETAILNM, '대금신청', '검토대상', DETAILNM) DETAILNM                   \n"
                    + " FROM EBID_COM911T                           \n"
                    + " WHERE BASECD = 'PURCHASE_STATUS_CD'         \n"
                    + "  AND DETAILCD IN ('40', '50', '60', '03')                        \n"
                    + " ORDER BY DETAILCD                           \n";
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
            
        }catch(Exception e){
            e.printStackTrace();
        }finally{

            if( listRowSet == null ){
                listRowSet = new RowSetImpl();
            }
            
            return listRowSet;
        }
    }
	
    
    
    
    
    
    
    
    
    public RowSet getList(ValueObject Params) throws Exception{

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("         SELECT A.DEPT_CD -- 현장코드                                                           \n");
            sqlStr.append("                    ,A.DEPT_NAME -- 현장명                                                  \n");
            sqlStr.append("                    ,A.CST_DOC_NAME -- 계약명                                                  \n");
            sqlStr.append("                    ,A.MEMBER_NAME -- 업체명                                                  \n");
            sqlStr.append("                    ,A.VP_DESC -- VP 검토 항목                                                  \n");
            sqlStr.append("                    ,A.VP_END_DY                                                  \n");
            sqlStr.append("                    ,A.DEPOSIT_CD -- 지금구분코드                                                  \n");
            sqlStr.append("                    ,A.DEPOSIT_NM -- 지급구분명                                                  \n");
            sqlStr.append("                    ,A.DEPOSIT_SEQ -- 지금차수                                                  \n");
            sqlStr.append("                    ,A.CURRENCY_GB -- 통화구분                                                  \n");
            sqlStr.append("                    ,FLOOR(A.CUR_ACMP_APPLYAMT) CUR_ACMP_APPLYAMT -- 금회기성고                                                  \n");
            sqlStr.append("                    ,A.VP_STATUS -- vp 상태                                                  \n");
            sqlStr.append("                    ,CASE WHEN A.VP_STATUS = '03' THEN '검토대상'                                                  \n");
            sqlStr.append("                    WHEN A.VP_STATUS = '40' THEN 'VP심사중'                                                  \n");
            sqlStr.append("                    WHEN A.VP_STATUS = '50' THEN 'VP반려'                                                  \n");
            sqlStr.append("                    WHEN A.VP_STATUS = '60' THEN 'VP승인'                                                   \n");
            sqlStr.append("                    WHEN A.VP_STATUS = '10' THEN 'VP승인'                                                   \n");
            sqlStr.append("                    WHEN A.VP_STATUS = '04' THEN 'VP승인'                                                   \n");
            sqlStr.append("                    END VP_STATUS_NM                                                  \n");
            sqlStr.append("                    ,A.PAYREQ_ID                                                  \n");
            sqlStr.append("                    ,A.PUR_CONT_ID                                                  \n");
            sqlStr.append("                    ,B.VP_CHK_EMPNO                                                  \n");
            sqlStr.append("         FROM(                                                  \n");
            sqlStr.append("              SELECT C.DEPT_CD -- 현장코드                                                  \n");
            sqlStr.append("                    ,C.DEPT_NAME -- 현장명                                                  \n");
            sqlStr.append("                    ,C.CST_DOC_NAME -- 계약명                                                  \n");
            sqlStr.append("                    ,D.MEMBER_NAME -- 업체명                                                  \n");
            sqlStr.append("                    ,B.VP_DESC -- VP 검토 항목                                                  \n");
            sqlStr.append("                    ,TO_CHAR(TO_DATE(A.VP_END_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD') VP_END_DY                          \n");
            sqlStr.append("                    ,DEPOSIT_CD -- 지금구분코드                                                  \n");
            sqlStr.append("                    ,A.PAYREQ_ID                                                  \n");
            sqlStr.append("                    ,A.PUR_CONT_ID                                                  \n");
            sqlStr.append("                    ,CASE WHEN DEPOSIT_CD = 2 THEN '중도금'                                                  \n");
            sqlStr.append("                    ELSE '잔금' END DEPOSIT_NM -- 지급구분명                                                  \n");
            sqlStr.append("                    ,A.DEPOSIT_SEQ -- 지금차수                                                  \n");
            sqlStr.append("                    ,C.CURRENCY_GB -- 통화구분                                                  \n");
            sqlStr.append("                    ,A.CUR_ACMP_APPLYAMT -- 금회기성고                                                  \n");
            sqlStr.append("                    ,CASE WHEN A.REQ_STATUS IN ('00','01','02','03','99') THEN '00'                                                  \n");
            sqlStr.append("                    WHEN A.REQ_STATUS = '10' THEN '60'                                                  \n");
            sqlStr.append("                    ELSE A.REQ_STATUS END VP_STATUS -- vp 상태                                                  \n");
            //sqlStr.append("                    ,A.REQ_STATUS AS VP_STATUS -- vp 상태                                                  \n");
            sqlStr.append("                    FROM PUR1100T A                                                  \n");
            sqlStr.append("                    ,CONT_PUR_PAYPLAN B                                                  \n");
            sqlStr.append("                    ,PUR2000T C                                                  \n");
            sqlStr.append("                    ,CONT_PUR_SUPP D                                                  \n");
            sqlStr.append("                    WHERE A.PAY_DIV = 2                                                  \n");
            sqlStr.append("                    AND D.SIGN_SEQ = 2                                                  \n");
            sqlStr.append("                    AND NVL(B.VP_YN,'N') = 'Y'                                                  \n");
            sqlStr.append("                    AND A.PAYREQ_ID = B.PAYREQ_ID                                                  \n");
            sqlStr.append("                    AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                                                  \n");
            sqlStr.append("                    AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID                                                  \n");
            sqlStr.append("          )A ,CONT_PUR_PAYPLAN B                                                                                                  \n");
            sqlStr.append("          WHERE 1=1                                                                                                   \n");
            sqlStr.append("          AND A.PAYREQ_ID = B.PAYREQ_ID                                                                                                   \n");
            
            if(!params.get("searchDeptCd").trim().equals(""))
            {
                sqlStr.append(" AND DEPT_CD = "+params.get("searchDeptCd")+"                                                     \n");
            }
            if(!params.get("searchDateStart").trim().equals(""))
            {
                sqlStr.append(" AND VP_END_DY BETWEEN '"+params.get("searchDateStart")+"' AND '"+params.get("searchDateEnd")+"'                                                                                                  \n");  
            }
            if(!params.get("searchBuildName").trim().equals(""))
            {
                sqlStr.append(" AND CST_DOC_NAME LIKE '%' || '"+params.get("searchBuildName")+"' || '%'                                                                                                         \n");
            }
            if(!params.get("searchStatusCd").trim().equals(""))
            {
            	if(params.get("searchStatusCd").equals("60")){
            		sqlStr.append(" AND VP_STATUS IN  ('04','60','10')                                                                                                        \n");
            	}else{
            		sqlStr.append(" AND VP_STATUS = "+params.get("searchStatusCd")+"                                                                                                        \n");
            	}
            }else{
            	sqlStr.append(" AND VP_STATUS IN ('00', '03','04','40','50','60','10')										  \n");
            }
            if(!"".equals(params.get("searchCorp"))){
        		sqlStr.append(" AND UPPER(A.MEMBER_NAME) LIKE UPPER('%" + params.get("searchCorp") + "%')														\n");
        	}
            
            sqlStr.append(" AND B.VP_CHK_EMPNO = '"+storage.getUser().get("ACCT_ID")+"'                                                                                                         \n");

            WAFLogger.debug(storage.getUser().get("ACCT_ID"));
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());

            search.setStatement(sqlStr.toString());
            listRowSet = search.execute();
            
        }catch(Exception e){
        	WAFLogger.error("e===>" + e);
            e.printStackTrace();
        }finally{

            if( listRowSet == null ){
                listRowSet = new RowSetImpl();
            }
            
            return listRowSet;
        }
    }
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

	/**
	 * Persistent 를 이용한 Save 처리
	 * @param params
	 * @return
	 * * 입력받은 프로그램 등록 정보를 등록합니다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean saveData(ValueObject params) {
		Persistent persistent_i = null;
		Persistent persistent_u = null;
		Persistent persistent_d = null;
		String sqlstr_i = "";
		String sqlstr_u = "";
		String sqlstr_d = "";

		try {

			conn.begin();
			
			persistent_i = new PersistentImpl(super.conn.getConnection());
			persistent_u = new PersistentImpl(super.conn.getConnection());
			persistent_d = new PersistentImpl(super.conn.getConnection());


			  sqlstr_i += "  INSERT INTO " + PGM_TABLE + "             \n"
				        + "   ( PGM_ID,              \n"
				        + "    PGM_NAME,              \n"
				        + "    SCREEN_NAME,           \n"
				        + "    SYSTEM_ID,             \n"
				        + "    SYSTEM_CODE,           \n"
				        + "    DSCR,                  \n"
				        + "    CRE_BY,                \n"
				        + "    CRE_DATE )             \n"
				        + " VALUES                    \n"
				        + "    ( PUB0320T_S.NEXTVAL,  \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    TO_CHAR(sysdate, 'YYYYMMDDHH24MISS') )              \n" ; 
			  
			  sqlstr_u = " UPDATE " + PGM_TABLE + "       \n"
		              + " SET    PGM_NAME    = ?       , \n"
		              + "        SCREEN_NAME = ?       , \n"
		              + "        SYSTEM_ID   = ?       , \n"
		              + "        SYSTEM_CODE = ?       , \n"
		              + "        DSCR        = ?       , \n"
		              + "        UPD_BY      = ?       , \n"
		              + "        UPD_DATE    = TO_CHAR(sysdate, 'YYYYMMDDHH24MISS')   \n"
		              + " WHERE  PGM_ID      = ?         \n" ;
			  
			
			  sqlstr_d = 	" DELETE                   \n"
		                  + " FROM   " + PGM_TABLE + " \n"
		                  + " WHERE  PGM_ID = ?        \n" ;
		        
			/* 중요
			 * system_id 값 형식은  "1@SYS" "[SYSTEM_ID@SYSTEM_CODE]" 
			 * 테이블명  PGM_SYS_TABLE 의 SYSTEM_CODE 값을 저장해야 한다.
			 */
			  
			String[] status  		= super.gets("STATUS");
			String[] pgm_name      	= super.gets("PGM_NAME");
			String[] screen_name    = super.gets("SCREEN_NAME");
			String[] system_id    	= super.gets("SYSTEM_ID");
			String[] dscr     		= super.gets("DSCR");
			String[] pgm_id     	= super.gets("PGM_ID");

			persistent_i.setStatement(sqlstr_i);
			persistent_u.setStatement(sqlstr_u);
			persistent_d.setStatement(sqlstr_d);
			
			if( status != null ) {
				for( int i = 0 ; i < status.length ; i++ ) {
					if( "I".equals(status[i]) ) {						
						persistent_i.addParameter(pgm_name[i].trim());
						persistent_i.addParameter(screen_name[i].trim());
						String[] system_code = system_id[0].split("[@]");
						persistent_i.addParameter(system_code[0]); //<== system_id
						persistent_i.addParameter(system_code[1]); //<== system_code
						persistent_i.addParameter(dscr[i].trim());
						persistent_i.addParameter(params.get("USER_ID"));
						persistent_i.execute();
						persistent_i.clearParameters();
					}
					
					else if( "U".equals(status[i]) ) {
						persistent_u.addParameter(pgm_name[i].trim());
						persistent_u.addParameter(screen_name[i].trim());
						String[] system_code = system_id[0].split("[@]");
						persistent_u.addParameter(system_code[0]); //<== system_id
						persistent_u.addParameter(system_code[1]); //<== system_code
						persistent_u.addParameter(dscr[i].trim());
						persistent_u.addParameter(params.get("USER_ID"));
						persistent_u.addParameter(pgm_id[i]);
						persistent_u.execute();
						persistent_u.clearParameters();
					}
					else if( "D".equals(status[i]) ) {
			
						persistent_d.addParameter(pgm_id[i]);
						persistent_d.execute();
						persistent_d.clearParameters();
					}
				}
			}
			return true;
        }
        catch(Exception e) {
            super.doException(e);
            super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
            super.conn.setRollback();
            return false;
        }
        finally {
        	super.conn.end();
        }
	}

}
