/**
 * @(#)file    SUBAcmpSignFormBiz.java
 * @(#)author  DaeJin Lee
 * @(#)modifier cutePrettyKara
 * @(#)modified date    2011.12.14
 * @(#)version 1.0
 * @(#)date    Jun 23, 2005
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.dcchem.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of dcchem, Inc.
 */
package kr.co.udapsoft.ebid.buyer.payment.purchase.biz;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Calendar;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.logger.Logger;
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 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 PuSuppPlanApprPopSaveBiz extends Biz {
	
	public PuSuppPlanApprPopSaveBiz(HandlerStorage storage) {
        super(storage);
    }

    public void doBiz() throws Exception {
    	ValueObject vo = new ValueObject(); 
    	boolean success = save(params);
               	
        if(success == true )
        {
            ValueObject getArr_id = getArr_id(params.get("corp_id"), params.get("cont_id"));
            params.set("arr_id", getArr_id.get("arr_id"));
                
//            vo = regurAppr(params);
            updateAppr(params);
        }
        super.storage.setSuccess(success);
    }
    
    
    public boolean deleteSuppAppr(Connection con, ValueObject params) throws Exception
    {
        StringBuffer sqlStr = new StringBuffer();

        Persistent persistent = null;
        
        try 
        {
            
            persistent = new PersistentImpl(con);
            
            
            sqlStr.append(" DELETE FROM SUPP_APPR WHERE CORP_ID = ? AND DOC_CD = ?  AND ARR_ID = ?  AND CONT_ID = ?     \n");
            
            
            
            persistent.setStatement(sqlStr.toString());
            persistent.addParameter(params.get("corp_id"));
            persistent.addParameter(params.get("doc_cd"));
            persistent.addParameter(params.get("arr_id"));
            persistent.addParameter(params.get("cont_id"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
        } catch(Exception e){
        	throw e;
        }
    }
    
    public boolean deleteSuppApprItem(Connection con, ValueObject params) throws Exception
    {
        StringBuffer sqlStr = new StringBuffer();

        Persistent persistent = null;
        
        try 
        {
            
            persistent = new PersistentImpl(con);
            
            
            sqlStr.append(" DELETE FROM SUPP_APPR_ITEM WHERE CORP_ID = ? AND DOC_CD = ? AND ARR_ID = ?   \n");
            
            
            
            persistent.setStatement(sqlStr.toString());
            persistent.addParameter(params.get("corp_id"));
            persistent.addParameter(params.get("doc_cd"));
            persistent.addParameter(params.get("arr_id"));
            
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
        } catch(Exception e){
        	throw e;
        }
    }
    
    
    private boolean save(ValueObject params) 
    {
        StringBuffer sqlStr = new StringBuffer();

        Persistent persistent = null;
        
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
        	
            deleteSuppApprItem(con, params);
            deleteSuppAppr(con, params);
            
            ValueObject getCredit_yn = getCredit_yn(params.get("corp_id"));
            
            
            if(getCredit_yn.get("CREDIT_YN").equals("Y"))
            {
                params.set("credit_yn", "Y");
            }
            else
            {
                params.set("credit_yn", "N");
            }
            
            persistent = new PersistentImpl(con);
            
            if(storage.getEvent().equals("GET_SAVE"))
            {
                sqlStr.append("          INSERT INTO SUPP_APPR                       \n");
                sqlStr.append("                    (                                                        \n");
                sqlStr.append("                    DEPT_CD         --현장ID                             \n");          
                sqlStr.append("                   ,DEPT_NAME       --현장명                             \n");          
                sqlStr.append("                   ,CORP_ID         --협력업체ID                             \n");          
                sqlStr.append("                   ,ARR_ID          --평가표 일련번호                              \n");
                sqlStr.append("                   ,DOC_CD          --문서관리코드                                  \n");
                sqlStr.append("                   ,APPR_DY         --평가일                                          \n");
                sqlStr.append("                   ,APPR_BY_ID      --평가자 ID                          \n");
                sqlStr.append("                   ,APPR_BY_NM      --평가자명                                \n");
                sqlStr.append("                   ,ITEM_STD_CD     --공종별등급기준코드              \n");
                sqlStr.append("                   ,APPR_TOT_RATE   --평가합계점수                   \n");
                sqlStr.append("                   ,NOTI            --비고                                                    \n");
                sqlStr.append("                   ,CREDIT_YN       --신용평가여부                                  \n");
                sqlStr.append("                   ,CORP_KOR        --협력업체명                                      \n");
                sqlStr.append("                   ,CORP_TEL        --전화번호                                           \n");
                sqlStr.append("                   ,CORP_MANAGER    --협력업체소장                           \n");
                sqlStr.append("                   ,REP_NAME        --대표자                                              \n");  
                sqlStr.append("                   ,SALES_3YEAR     --3년평균 매출액(억원)               \n");
                sqlStr.append("                   ,ITEM_NAME       --공종명                                             \n");
                sqlStr.append("                   ,CON_AMT         --계약금액                                             \n");
                sqlStr.append("                   ,START_WORK_DATE --계약공기(시작일)            \n"); 
                sqlStr.append("                   ,END_WORK_DATE   --계약공기(종료일)                \n");
                sqlStr.append("                   ,START_REAL_DATE --실공기(시작일)                 \n");                   
                sqlStr.append("                   ,END_REAL_DATE   --실공기(종료일)                     \n");
                sqlStr.append("                   ,APPR_NOTE       --평가자 종합의견                                 \n");
                sqlStr.append("                   ,ORD_DOC_NO      --ORD_DOC_NO                                                        \n");
                sqlStr.append("                   ,CONT_ID         --CONT_ID                                                     \n");
                sqlStr.append("                   ,REGU_STATUS_CD  --상태                                 \n");
                sqlStr.append("                   ,PAYREQ_ID       --대금지급ID                                 \n");
                sqlStr.append("                   ,WRITE_NO        --작성자사번                                 \n");
                sqlStr.append("                   ,WRITE_NM        --작성자사번                                 \n");
                sqlStr.append("                   ,CRE_BY          --등록자                                                      \n");
                sqlStr.append("                   ,CRE_DATE        --등록일자                                              \n");
                sqlStr.append("                )VALUES(                                                        \n");
                sqlStr.append("                    ?                                                               \n");    //현장ID  
                sqlStr.append("                   ,?                                                               \n");    //현장명   
                sqlStr.append("                   ,?                                                               \n");    //협력업체ID
                if(params.get("arr_id").equals(""))
                {
                    sqlStr.append("                   ,(SELECT DECODE(MAX(ARR_ID), '', 1, MAX(ARR_ID)+1) FROM SUPP_APPR WHERE CORP_ID = ? )               \n");//평가표 일련번호
                }
                else
                {
                    sqlStr.append("                   ,?              \n");//평가표 일련번호
                }
                sqlStr.append("                   ,?                                                                \n");     //문서관리코드                   
                sqlStr.append("                   ,?                                                                \n");     //평가일                      
                sqlStr.append("                   ,?                                                                \n");     //평가자 ID-현장평가자 2016.4.21                 
                sqlStr.append("                   ,?                                                                \n");     //평가자명  -현장평가자 2016.4.21                   
                sqlStr.append("                   ,?                                                                \n");     //공종별등급기준코드              
                sqlStr.append("                   ,?                                                                \n");     //평가합계점수                   
                sqlStr.append("                   ,?                                                                \n");     //비고                       
                sqlStr.append("                   ,?                                                                \n");     //신용평가여부                   
                sqlStr.append("                   ,?                                                                \n");     //협력업체명                    
                sqlStr.append("                   ,?                                                                \n");     //전화번호                     
                sqlStr.append("                   ,?                                                                \n");     //협력업체소장                   
                sqlStr.append("                   ,?                                                                \n");     //대표자                      
                sqlStr.append("                   ,?                                                                \n");     //3년평균 매출액(억원)             
                sqlStr.append("                   ,?                                                                \n");     //공종명                      
                sqlStr.append("                   ,?                                                                \n");     //계약금액                     
                sqlStr.append("                   ,?                                                                \n");     //계약공기(시작일)            
                sqlStr.append("                   ,?                                                                \n");     //계약공기(종료일)                
                sqlStr.append("                   ,?                                                                \n");     //실공기(시작일)                 
                sqlStr.append("                   ,?                                                                \n");     //실공기(종료일)                 
                sqlStr.append("                   ,?                                                                \n");     //평가자 종합의견    --본사담당자 평가              
                sqlStr.append("                   ,(SELECT MAX(ORD_DOC_NO) FROM PUR2000T WHERE PUR_CONT_ID = ?)     \n");     //ORD_DOC_NO               
                sqlStr.append("                   ,?                                                                \n");     //CONT_ID                  
                sqlStr.append("                   ,?                                                                \n");     //상태
                sqlStr.append("                   ,?                                                                \n");     //대금지급ID 
                sqlStr.append("                   ,?                                                                \n");     //작성자사번              --본사평가자 2016.4.21      
                sqlStr.append("                   ,?                                                                \n");     //작성자명                 --본사평가자 2016.4.21      
                sqlStr.append("                   ,?                                                                \n");     //등록자                      
                sqlStr.append("                   ,SYSDATE                                                     		\n");     //등록일자                     
                sqlStr.append("                )                                                                    \n");
                
                
                
                persistent.setStatement(sqlStr.toString());
                persistent.addParameter(params.get("dept_cd"));         //현장ID  
                persistent.addParameter(params.get("dept_name"));       //현장명   
                persistent.addParameter(params.get("corp_id"));         //협력업체ID
                if(params.get("arr_id").equals(""))
                {
                    persistent.addParameter(params.get("corp_id"));
                }
                else
                {
                    persistent.addParameter(params.get("arr_id"));
                }
                persistent.addParameter(params.get("doc_cd"));                                                  //문서관리코드                                      
                persistent.addParameter(params.get("appr_date"));                                               //평가일  
                if (params.get("CHARGE_CD").equals("C")) {
                	persistent.addParameter(params.get("user_id_before"));                                      //평가자 ID-현장평가자 2016.4.21                      
                    persistent.addParameter(params.get("user_nm_before"));                                      //평가자명  -현장평가자 2016.4.21     
                }
                else
                {
                	persistent.addParameter(params.get("user_id"));                                             //평가자 ID-현장평가자 2016.4.21                      
                    persistent.addParameter(params.get("USER_NAME"));                                           //평가자명  -현장평가자 2016.4.21     
                }                
                persistent.addParameter(params.get("item_std_cd"));                                             //공종별등급기준코드                                   
                persistent.addParameter(params.get("appr_tot_rate"));                                           //평가합계점수                                      
                persistent.addParameter("");                                                                    //비고                                          
                persistent.addParameter(params.get("credit_yn"));                                               //신용평가여부                                      
                persistent.addParameter(params.get("corp_kor"));                                                //협력업체명                                       
                persistent.addParameter(params.get("corp_tel"));                                                //전화번호                                        
                persistent.addParameter(params.get("corp_manager"));                                            //협력업체소장                                      
                persistent.addParameter(params.get("rep_name"));                                                //대표자                                         
                persistent.addParameter(params.get("sales_3year"));                                             //3년평균 매출액(억원)                                
                persistent.addParameter(params.get("item_name"));                                               //공종명                                         
                persistent.addParameter(params.get("con_amt"));                                                 //계약금액                                        
                persistent.addParameter(params.get("start_work_date").replace("-", ""));                        //계약공기(시작일)                                   
                persistent.addParameter(params.get("end_work_date").replace("-", ""));                          //계약공기(종료일)                                   
                persistent.addParameter(params.get("start_real_date").replace("-", ""));                        //실공기(시작일)                                    
                persistent.addParameter(params.get("end_real_date").replace("-", ""));                          //실공기(종료일)                                    
                persistent.addParameter(params.get("appr_note"));                                               //평가자 종합의견    --본사담당자 평가                      
                persistent.addParameter(params.get("cont_id"));                                                 //ORD_DOC_NO                                  
                persistent.addParameter(params.get("cont_id"));                                                 //CONT_ID  
                if (params.get("p_status").equals("02")) {
                	persistent.addParameter("02"); 
                }
                else{
                	persistent.addParameter("01"); 
                }		
                persistent.addParameter(params.get("PAYREQ_ID"));                                                //작성자사번              --본사평가자 2016.4.21
                persistent.addParameter(params.get("WRITE_NO"));                                                //작성자사번              --본사평가자 2016.4.21                                                          
                persistent.addParameter(params.get("WRITE_NM"));                                                //작성자명                 --본사평가자 2016.4.21               
                persistent.addParameter(params.get("user_id"));                                                 //등록자                                                  
                                                                                                                
                                                                                                                                                      
                WAFLogger.debug(persistent.getParameters());
                WAFLogger.debug(sqlStr.toString());
                persistent.execute();
                persistent.clearParameters();
                
                
                
                int listSize = Integer.parseInt(params.get("listSize"));
                for(int i=0; i < listSize; i++)
                {
                    
                    
                    
                    params.set("appr_cd", params.get("appr_cd_" + i));
                    params.set("chkResultCd", params.get("chkResultCd_" + i));
                    params.set("test", params.get("test_" + i));
                    
                    saveApprItem(con, params);
                    
                    WAFLogger.debug("params===>" + params);
                    
                }
                
            }
        } catch(Exception e){
            WAFLogger.error(e);
            storage.setMessage(e.getMessage());
            storage.setSuccess(false);
            conn.setRollback();
            return false;
        } finally{
            conn.end();
        }               
        return true;
    }
    
    
    public ValueObject getCredit_yn(String corp_id) throws Exception {
        Search search = new SearchImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        
        try {
            sql.append("    SELECT CREDIT_YN FROM SUPP_INFO WHERE CORP_ID = ? \n");
            
            search.setStatement(sql.toString());            
            search.addParameter(corp_id);
            
            WAFLogger.debug(search.getParameters());
            
            vo = RowSetUtility.getValueObject(search.execute().getRow(0));
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> get_conDocNo_cnt() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if(vo == null) vo = new ValueObject();
        }
        
        return vo;
    }
    
    public ValueObject getArr_id(String corp_id, String cont_id) throws Exception {
        Search search = new SearchImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        
        try {
            sql.append("   SELECT DECODE(MAX(ARR_ID), '', 1, MAX(ARR_ID)) ARR_ID FROM SUPP_APPR WHERE CORP_ID = ? AND CONT_ID  = ? \n");
            
            search.setStatement(sql.toString());            
            search.addParameter(corp_id);
            search.addParameter(cont_id);
            
            WAFLogger.debug(search.getParameters());
            
            vo = RowSetUtility.getValueObject(search.execute().getRow(0));
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> get_conDocNo_cnt() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if(vo == null) vo = new ValueObject();
        }
        
        return vo;
    }
    
    
    private void saveApprItem(Connection con, ValueObject params) throws Exception 
    {
        
        Persistent persistent2 = null;
        StringBuffer sqlStr2 = new StringBuffer();
        
        try
        {
        
        persistent2 = new PersistentImpl(con);
        
            if(storage.getEvent().equals("GET_SAVE"))
            {
                sqlStr2.append("          INSERT INTO SUPP_APPR_ITEM                       \n");
                sqlStr2.append("          (                                                                           \n");
                sqlStr2.append("             CORP_ID                                                                         \n");
                sqlStr2.append("            ,ARR_ID                                                                         \n");
                sqlStr2.append("            ,ADM_YEAR                                                                         \n");
                sqlStr2.append("            ,DOC_CD                                                                         \n");
                sqlStr2.append("            ,APPR_CD                                                                         \n");
                sqlStr2.append("            ,APPR_RESULT_CD                                                              \n");
                sqlStr2.append("            ,APPR_RATE                                                              \n");
                sqlStr2.append("            ,CRE_BY                                                              \n");
                sqlStr2.append("            ,CRE_DATE                                                              \n");
                sqlStr2.append("            )VALUES(                                                              \n");
                sqlStr2.append("             ?                                                                          \n");
                if(params.get("arr_id").equals(""))
                {
                    sqlStr2.append("            ,(SELECT DECODE(MAX(ARR_ID), '', 1, MAX(ARR_ID)) FROM SUPP_APPR WHERE CORP_ID = ? )                                                                          \n");
                }
                else
                {
                    sqlStr2.append("            ,?                                                                         \n");
                }
                sqlStr2.append("            ,(SELECT MAX(ADM_YEAR) FROM APPR_STD WHERE DOC_CD = ?)                         \n");
                sqlStr2.append("            ,?                                                                          \n");
                sqlStr2.append("            ,?                                                                          \n");
                sqlStr2.append("            ,?                                                                          \n");
                sqlStr2.append("            ,?                                                                          \n");
                sqlStr2.append("            ,?                                                                          \n");
                sqlStr2.append("            ,SYSDATE                                                               \n");
                sqlStr2.append("            )                                                                           \n");
                
                
                persistent2.setStatement(sqlStr2.toString());
                persistent2.addParameter(params.get("corp_id"));
                if(params.get("arr_id").equals(""))
                {
                    persistent2.addParameter(params.get("corp_id"));
                }
                else
                {
                    persistent2.addParameter(params.get("arr_id"));
                }
                persistent2.addParameter(params.get("doc_cd"));
                persistent2.addParameter(params.get("doc_cd"));
                persistent2.addParameter(params.get("appr_cd"));
                persistent2.addParameter(params.get("chkResultCd"));
                persistent2.addParameter(params.get("test").equals("") ? "0" : params.get("test"));
                persistent2.addParameter(params.get("user_id"));
                
                
                
                persistent2.execute();
                persistent2.clearParameters();
                    
            }
            
            
        } catch(Exception e){
            throw e;
        }    
    }

    
    
public ValueObject regurAppr(ValueObject Params) throws Exception { 
        
        CallableStatement cs = null;
        ValueObject rtnVo = new ValueObject();
        Calendar calendar = Calendar.getInstance();
        
        try {
            conn.begin();                       
            String corp_id     = params.get("corp_id");
            String arr_id      = params.get("arr_id");
            String adm_year    = params.get("appr_date").substring(0, 4);
            String doc_cd      = params.get("doc_cd");
            
            
            WAFLogger.debug("corp_id====>" + corp_id);
            WAFLogger.debug("arr_id====>" + arr_id);
            WAFLogger.debug("adm_year====>" + adm_year);
            WAFLogger.debug("doc_cd====>" + doc_cd);
            
            String sqlStr = "{call SP_REGUR_APPR_SET(?, ?, ?, ?, ?)}";
            
            cs = super.conn.getConnection().prepareCall(sqlStr);
            cs.setString(1, corp_id);
            cs.setString(2, arr_id);
            cs.setString(3, doc_cd);
            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
            cs.registerOutParameter(5, java.sql.Types.VARCHAR);
            cs.execute();
            
            rtnVo.set("out_success_cd",cs.getString(4));
            rtnVo.set("out_message",cs.getString(5));
            return rtnVo;
            
        }
        catch(Exception e) {
            super.doException(e);
            super.conn.setRollback();
            this.storage.setSuccess(false);
            this.storage.setMessage(e.getMessage());
            return rtnVo;
        }
        finally {
            super.conn.end();
        }
    }

public void updateAppr(ValueObject params) throws Exception { 
    
	Persistent persistent3 = null;
	Persistent persistent4 = null;
    StringBuffer sqlStr3 = new StringBuffer();
    String sqlstr4 = "";    
    
    conn.begin();
       try
        {
        Connection con = conn.getConnection();
        persistent3 = new PersistentImpl(con);
        persistent4 = new PersistentImpl(con);
        
        sqlStr3.append("          UPDATE SUPP_APPR                       \n");
        sqlStr3.append("          SET APPR_TOT_RATE = (SELECT SUM(APPR_RATE) FROM SUPP_APPR_ITEM WHERE CORP_ID = ? AND ARR_ID =  ? AND ADM_YEAR = (SELECT MAX(ADM_YEAR) FROM APPR_STD WHERE DOC_CD = '06') AND DOC_CD = '06')         \n");
//        sqlStr3.append("            , ITEM_STD_CD = V_ITEM_STD_CD                                                                         \n");
        sqlStr3.append("          WHERE CORP_ID = ?                                                                         \n");
        sqlStr3.append("            AND ARR_ID = ?                                                                         \n");                        
        
        persistent3.setStatement(sqlStr3.toString());

        persistent3.addParameter(params.get("CORP_ID"));
        persistent3.addParameter(params.get("ARR_ID"));
//        persistent3.addParameter(params.get("appr_cd"));        
        persistent3.addParameter(params.get("CORP_ID"));
        persistent3.addParameter(params.get("ARR_ID"));
        
//        WAFLogger.debug(persistent3.getParameters());
//        WAFLogger.debug(sqlStr3.toString());
               
        persistent3.execute();
        persistent3.clearParameters();
        
        //설계평가 상태변경
        sqlstr4 = " UPDATE PUR1100T      														\n"
	          + " SET    PLAN_APPR_STATUS			= ?       ,									\n"
	          + "        UPD_BY      				= ?       , 					            \n"
	          + "        UPD_DATE    				= sysdate									\n"
	          + " WHERE  PAYREQ_ID      			= ?        									\n" ;
        
        persistent4.setStatement(sqlstr4);
        
        if(params.get("gubun").equals("1")){
        	persistent4.addParameter("01");
        }else{
        	persistent4.addParameter("02");
        }
        
        persistent4.addParameter(params.get("USER_ID"));
        persistent4.addParameter(params.get("PAYREQ_ID"));
        
        persistent4.execute();
        persistent4.clearParameters();
            
        } catch(Exception e) {
            e.printStackTrace();
            WAFLogger.error(e);
            super.doException(e);
            super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
            super.conn.setRollback();
        }
        finally {
        	super.conn.end();
        }
	}



}
