/**
 *
 * @(#)author  [ok]
 * @(#)version 1.0
 * @(#)date    2014. 07. 24
 * @(#)since   JDK 1.7.2
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of udapsoft, Inc.
 */
 package kr.co.udapsoft.ebid.buyer.payment.purchase.biz;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
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.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.StringUtil;
import kr.co.hsnc.common.util.ValueObject;

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

public class PrepayRequestListBiz extends Biz {
	public PrepayRequestListBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		
		
		WAFLogger.debug("전자조달.대금관리.구매.대금신청 신규등록 프로세스처리.[PurchaseRequestListBiz]");

		if(storage.compareEvent("")){
			
		}else if(storage.compareEvent("GET_LIST")){
			
			WAFLogger.debug("[PurchaseRequestListBiz] GET_LIST");
			WAFLogger.debug(params.get("searchDeptCd"));
			
			params.set("sheet1", getPrePaymentList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));

		}else if(storage.compareEvent("SAVE_DATA")){
			
			boolean success = false;
			String[] status = super.gets("sheet1.STATUS");

			try {
				super.conn.begin();
				
				
				for (int i = 0; i < status.length; i++) {
					//구매_대금지금신청 SEQ채번
					saveData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));	
				}
				
				super.storage.setSuccess(true);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		} 
	}
	
    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 대금신청 대상 리스트 조회
     *@author OK
     *@version 2014. 7. 28.
     *@since JDK1.7
     */
    public RowSet getPrePaymentList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sqlstr = new StringBuffer();
        
        try {

        	sqlstr.append("											SELECT GET_DEPT_NM(A.DEPT_CD) AS DEPT_NM                                                                    \n");
        	sqlstr.append("											     , A.CST_DOC_NAME AS CST_DOC_NAME                                                                       \n");
        	sqlstr.append("											     , GET_CORP_NM_EBID(C.CORP_ID) AS CORP_KOR                                                              \n");
        	sqlstr.append("											     , A.CURRENCY_GB                                                                                        \n");
        	sqlstr.append("											     , F_GET_D_CURRENCY(A.ORD_SUPPLY_AMT, F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) AS ORD_SUPPLY_AMT             \n");
        	sqlstr.append("											     , A.DEPT_CD                                                                                     		\n");
        	sqlstr.append("											     , A.PUR_CONT_ID                                                                                     	\n");
        	sqlstr.append("											     , A.PUR_CONT_MOD_ID                                                                                    \n");
        	sqlstr.append("											     , A.MOD_NO                                                                                    \n");
        	sqlstr.append("											     , A.ORD_DOC_NO                                                                                    \n");
        	sqlstr.append("											FROM PUR2000T A                                                                                             \n");
        	sqlstr.append("											   , CONT_PUR_SUPP C                                                                                        \n");
        	sqlstr.append("											WHERE A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                                                                 \n");
        	sqlstr.append("										    AND A.PUR_CONT_MOD_ID NOT IN (SELECT PUR_CONT_MOD_ID FROM PUR1030T)                                            \n");
        	sqlstr.append("											AND C.SIGN_SEQ = 2                                                                                          \n");
        	sqlstr.append("											AND A.CON_CLS_YN = '9'                                                                                      \n");
        	sqlstr.append("											AND A.STATUS_CD = '50'                                                                                      \n");
        	//sqlstr.append("											AND A.MOD_NO > 0                                                                                           	\n");
        	sqlstr.append("											AND A.PREPAY_AMT > 0                                                                                           	\n");
        	//sqlstr.append("											AND A.PUR_CONT_MOD_ID = (SELECT MAX(PUR_CONT_MOD_ID ) FROM PUR2000T WHERE PUR_CONT_ID = A.PUR_CONT_ID)		\n");
        	sqlstr.append("											AND A.DEPT_CD = '" + params.get("searchDeptCd")+ "'                                                       	\n");
        	
        	if(!params.get("searchCstNm").isEmpty()){
     			sqlstr.append("  AND UPPER(A.CST_DOC_NAME) LIKE '%'||'"+params.get("searchCstNm").toUpperCase()+"'||'%'					\n");
        	}

        	if(!params.get("searchCorpKor").isEmpty()){
    			sqlstr.append("  AND UPPER(GET_CORP_NM_EBID(C.CORP_ID))LIKE '%'||'"+params.get("searchCorpKor").toUpperCase()+"'||'%'	    \n");   
        	}
        	
            search.setStatement(sqlstr.toString());
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
            
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPrePaymentList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

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

    public RowSet getDept() {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        StringBuffer sql = new StringBuffer();
        
        try {
           	sqlstr += " SELECT  DEPT_CD ,A.DEPT_NM ,A.AC_UNIT_CD ,F_GET_CODE_NAME('101', A.AC_UNIT_CD) AS AC_UNIT_NM			\n"
        			+ " 		,A.STD_CURRENCY_CD ,F_GET_CURRENCY_DIGIT(A.STD_CURRENCY_CD) AS STD_DIGIT						\n"        			
           			+ " FROM    ACZ10100V A																						\n"        			
           			+ " WHERE	SUBSTR(DEPT_CD,1,4) = '"+params.get("searchDeptCd")+"'											\n"     			
           			+ " AND A.AC_UNIT_CD != '50' 																				\n"        			
           			+ "  AND	DEPT_CD != '0000'																				\n"        			
           			;

            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
            
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPrePaymentList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( listRowSet == null ) listRowSet = new RowSetImpl();
            return listRowSet;
        }
    }
    
    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 구매)대금지급 
     *@author OK
     *@version 2014. 7. 28.
     *@since JDK1.7
     */
	private void saveData(Connection connection, ValueObject sqlParams) throws Exception {
		
		Persistent persistent_i	= new PersistentImpl(connection);
		
		try {
			
			StringBuffer sql = new StringBuffer();

			sql.append("        	 INSERT INTO PUR1030T (                                                                                     								\n");
			sql.append("                    PREPAY_ID -- 선급내역ID                                                                         									\n");
			sql.append("                    ,PUR_CONT_ID -- 발주계약ID                                                                      									\n");
			sql.append("                    ,PUR_CONT_MOD_ID -- 발주계약변경ID                                                               									\n");
			sql.append("                    ,PREPAY_NO -- 선급차수                                                                                                                                 									\n");
			sql.append("                    ,DEPT_CD -- 현장코드                                                                                                                                    									\n");
			sql.append("                    ,ORD_DOC_NO -- 발주계약번호                                                                                                                              									\n");
			sql.append("                    ,CORP_ID -- 협력업체 ID                                                                            								\n");
			sql.append("                    ,REG_NO -- 사업자등록번호                                                                                                                                    								\n");
			sql.append("                    ,CORP_KOR -- 업체명한글                                                                                                                                     								\n");
			sql.append("                    ,BOSS_NAME -- 대표자                                                                                                                                              								\n");
			sql.append("                    ,PREPAY_AMT -- 선급금액                                                                                                                                           							\n");
			sql.append("                    ,PREPAY_SUPPLY_AMT -- 선급공급c가                                                                                                                                 							\n");
			sql.append("                    ,PREPAY_SURTAX_AMT -- 선급부가세액                                                                                                                                							\n");
			sql.append("                    ,TAX_METHOD_CD -- 세금계산서_발행방법                                                                                                                             							\n");
			sql.append("                    ,PREPAY_PROC_GB -- 선급진행상태                                                                                                                                   							\n");
			sql.append("                    ,CRE_BY -- 등록자                                                                                                                                                 								\n");
			sql.append("                    ,CRE_DATE -- 등록일시                                                                                                                                             								\n");
			sql.append("                    ,TAX_CHARG_NM -- 담당자명                                                                                                                                         							\n");
			sql.append("                    ,TAX_CHARG_HP -- 담당자HP                                                                                                 		\n");
			sql.append("                    ,TAX_CHARG_EMAIL -- EMAIL                                                                                               		\n");
			sql.append("                    ,CHG_DIV_CD -- 신규변경구분                                                      	\n");//2019.05.10 변경추가 
			sql.append("                    ,ORG_AMT -- 최초선급                                                                                              													\n");
			sql.append("                    ,ORG_SUPPLY_AMT -- 최초공급                                                                                              													\n");
			sql.append("                    ,ORG_SURTAX_AMT -- 최초부가                                                                                              													\n");
			sql.append("                )                                                                                                                           		\n");
			sql.append("                SELECT PUR1100T_S.NEXTVAL PREPAY_ID -- 선급내역ID                                                                       			\n");
			sql.append("                     , PUR_CONT_ID -- 발주계약ID                                                                                        				\n");
			sql.append("                     , PUR_CONT_MOD_ID -- 발주계약변경ID                                                                                 				\n");
			sql.append("                     , PREPAY_NO -- 선급차수                                                                                                                                    								\n");
			sql.append("                     , DEPT_CD -- 현장코드                                                                                                                                      								\n");
			sql.append("                     , ORD_DOC_NO -- 발주계약번호                                                                                                                               								\n");
			sql.append("                     , CORP_ID -- 협력업체 ID                                                                                           				\n");
			sql.append("                     , REG_NO -- 사업자등록번호                                                                                                                                 								\n");
			sql.append("                     , CORP_KOR -- 업체명한글                                                                                                                                   								\n");
			sql.append("                     , BOSS_NAME -- 대표자                                                                                                                                      								\n");
			sql.append("                     , CASE WHEN CHG_CODE10 = 'INPUT_VAT' THEN PREPAY_SUPPLY_AMT + ROUND(PREPAY_SUPPLY_AMT/10, CHG_CODE01)                          \n");
			sql.append("                       ELSE  PREPAY_SUPPLY_AMT END PREPAY_AMT -- 선급금액                                                                                            							\n");
			sql.append("                     , PREPAY_SUPPLY_AMT -- 선급공급가                                                                                                                          							\n");
			sql.append("                     , CASE WHEN CHG_CODE10 = 'INPUT_VAT' THEN ROUND(PREPAY_SUPPLY_AMT/10,CHG_CODE01) 												\n");
			sql.append("                        ELSE  0 END PREPAY_SURTAX_AMT -- 선급부가세액				 																	\n");
			sql.append("                     , TAX_METHOD_CD -- 세금계산서_발행방법                                                                                                                     							\n");
			sql.append("                     , PREPAY_PROC_GB -- 선급진행상태                                                                                                                           								\n");
			sql.append("                     , ?  -- 등록자                                                                                                                                         									\n");
			sql.append("                     , sysdate                                                                                                                   								\n");
			sql.append("                     , TAX_CHARG_NM -- 담당자명                                                                                                                                 								\n");
			sql.append("                     , TAX_CHARG_HP -- 담당자HP                                                                                         				\n");
			sql.append("                     , TAX_CHARG_EMAIL -- EMAIL                                                                                              		\n");
			sql.append("                     , 'Y' AS CHG_DIV_CD -- 신규변경구분                                                                                              											\n");
			sql.append("                     , CASE WHEN CHG_CODE10 = 'INPUT_VAT' THEN PREPAY_SUPPLY_AMT + ROUND(PREPAY_SUPPLY_AMT/10, CHG_CODE01)                          \n");
			sql.append("                       ELSE  PREPAY_SUPPLY_AMT END ORG_AMT -- 선급금액                                                                                            							\n");
			sql.append("                     , PREPAY_SUPPLY_AMT ORG_SUPPLY_AMT -- 선급공급가                                                                                                                          							\n");
			sql.append("                     , CASE WHEN CHG_CODE10 = 'INPUT_VAT' THEN ROUND(PREPAY_SUPPLY_AMT/10,CHG_CODE01) 												\n");
			sql.append("                        ELSE  0 END ORG_SURTAX_AMT -- 선급부가세액				 																	\n");
			sql.append("                FROM(                                                                                                                       		\n");
			sql.append("                		SELECT PUR_CONT_ID -- 발주계약ID                                                                                      			\n");
			sql.append("                             , ? PUR_CONT_MOD_ID-- 발주계약변경ID                                                             							\n");
			sql.append("                             , (SELECT NVL(MAX(PREPAY_NO),0)+1 FROM PUR1030T WHERE PUR_CONT_ID = A.PUR_CONT_ID) PREPAY_NO -- 선급차수               			\n");
			sql.append("                             , DEPT_CD -- 현장코드                                                                                                                                      						\n");
			sql.append("                             , ORD_DOC_NO -- 발주계약번호                                                                                                                               						\n");
			sql.append("                             , a.CORP_ID -- 협력업체 ID                                                                                        		\n");
			sql.append("                             , (SELECT CORP_REG_NO FROM SUPP_INFO WHERE CORP_ID = A.CORP_ID) REG_NO -- 사업자등록번호                                                		\n");
			sql.append("                             , (SELECT CORP_KOR FROM SUPP_INFO WHERE CORP_ID = A.CORP_ID) CORP_KOR -- 업체명한글                                                       		\n");
			sql.append("                             , (SELECT REP_NAME FROM SUPP_INFO WHERE CORP_ID = A.CORP_ID) BOSS_NAME -- 대표자                                                          		\n");
			sql.append("                             , PREPAY_AMT PREPAY_SUPPLY_AMT -- 선급공급가                                                                                                               						\n");
			sql.append("                             , '2' TAX_METHOD_CD -- 세금계산서_발행방법                                                                                                                 					\n");
			sql.append("                             , '00' PREPAY_PROC_GB -- 선급진행상태                                                                                                                      					\n");
			sql.append("                             , a.CRE_BY -- 등록자                                                                                                                                       						\n");
			sql.append("                             , a.CRE_DATE -- 등록일시                                                                                                                                   						\n");
			sql.append("                             , b.USER_NAME TAX_CHARG_NM -- 담당자명                                                                                                                     					\n");
			sql.append("                             , b.HP TAX_CHARG_HP -- 담당자HP                                                                                          \n");
			sql.append("                             , b.EMAIL TAX_CHARG_EMAIL -- EMAIL                                                                                     \n");
			sql.append("                             , (SELECT NVL(MAX(CHG_CODE10),'INPUT_VAT')                                                                             \n");
			sql.append("                                FROM EBID_COM911T                                                                                                  \n");
			sql.append("                                WHERE BASECD = 'PROOF_CD'                                                                                                \n");
			sql.append("                                AND DETAILCD = (select GUA_GB from PUR2000T where PUR_CONT_MOD_ID = a.PUR_CONT_MOD_ID)) CHG_CODE10 -- 과세/면세 구분       \n");
			sql.append("                             , (SELECT NVL(MAX(CHG_CODE01),0)                                                                                       \n");
			sql.append("                                FROM EBID_COM911T                                                                                                  \n");
			sql.append("                                WHERE BASECD = '911'                                                                                                \n");
			sql.append("                                AND DETAILCD = (select CURRENCY_GB from PUR2000T Where PUR_CONT_MOD_ID = a.PUR_CONT_MOD_ID)) CHG_CODE01             \n");
			sql.append("                  FROM PUR2000T A                                                                                                                   \n");
			sql.append("                            ,cont_pur_supp b                                                                                                        \n");
			sql.append("                WHERE a.PUR_CONT_MOD_ID = ?                                                                                        					\n");
			sql.append("                     and b.sign_seq = 2                                                                                                             \n");
			sql.append("                     and a.pur_cont_mod_id = b.pur_cont_mod_id                                                                                      \n");
			sql.append("            )																																																																																					\n");
			
			
			
				persistent_i.setStatement(sql.toString());
				WAFLogger.debug(sql.toString());
				persistent_i.addParameter(storage.getUser().get("ACCT_ID"));
				persistent_i.addParameter(sqlParams.get("PUR_CONT_MOD_ID"));
				persistent_i.addParameter(sqlParams.get("PUR_CONT_MOD_ID"));
				WAFLogger.debug(persistent_i.getParameters());
	    		
				persistent_i.execute();
				persistent_i.clearParameters();	
				
				//updatePayplan(connection, sqlParams);
				
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} 
	}
	
	/**
	 * 
	 * @param 	
	 * @return	ValueObject
	 */
	private void updatePayplan(Connection connection, ValueObject vo) throws Exception {
		
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		String sqlstr = "";
		String resultPurContModId = "";
		String resultPaySeq = "";
		
		Persistent persistent1 = new PersistentImpl(connection);
		StringBuffer updateSqlstr1 = null;
		
		try {
			
			updateSqlstr1 = new StringBuffer();
			updateSqlstr1.append(" UPDATE  CONT_PUR_PAYPLAN		 SET					\n");
			updateSqlstr1.append("         PAY_YN				= 'Y' ,					\n");
			updateSqlstr1.append("         PAYREQ_ID			= ?						\n");
			updateSqlstr1.append(" WHERE   PUR_CONT_MOD_ID	    = ?						\n");
			updateSqlstr1.append(" AND     PAY_SEQ			    = ?						\n");
			
			sqlstr += " 																\n"
					+ " SELECT   A1.PUR_CONT_ID											\n"
					+ " 		,A1.PUR_CONT_MOD_ID										\n"
					+ " 		,A1.PAY_SEQ												\n"
					+ " FROM (SELECT A.*												\n"
					+ " 	  FROM CONT_PUR_PAYPLAN A									\n"
					+ " 	  	   ,(SELECT PUR_CONT_MOD_ID, MIN(PAY_SEQ) PAY_SEQ		\n"
					+ " 	    	 FROM CONT_PUR_PAYPLAN								\n"
					+ " 	         WHERE NVL(PAY_YN,'N') = 'N'						\n"
					+ " 	         AND PAY_DIV_CD != '01'								\n"
					+ " 	         GROUP BY PUR_CONT_MOD_ID) B						\n"
					+ " 	  WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID				\n"
					+ "       AND A.PAY_SEQ = B.PAY_SEQ) A1								\n"
					+ " WHERE A1.PUR_CONT_ID = '"+vo.get("PUR_CONT_ID")+"'				\n"
					;
			
			search.setStatement(sqlstr);
			WAFLogger.debug(sqlstr);
			listRowSet = search.execute();
			for(int i=0; i<listRowSet.size(); i++){
				
				ValueObject row = null;
				row = RowSetUtility.getValueObject(listRowSet.getRow(i));
				resultPurContModId = row.get("PUR_CONT_MOD_ID");
				resultPaySeq = row.get("PAY_SEQ");
				
				persistent1.setStatement(updateSqlstr1.toString());
				persistent1.addParameter(params.get("seq"));
				persistent1.addParameter(resultPurContModId);
				persistent1.addParameter(resultPaySeq);
				persistent1.execute();
				persistent1.clearParameters();
			
			}
			
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getStockId() : " + e.getMessage());
			WAFLogger.error(e);
			throw e;
			
		}finally{
			//if(resultStockId == null) resultStockId = "";
		}
		
		//return resultStockId;
	}
	
}
