package kr.co.udapsoft.ebid.buyer.supplier.outside.biz;
import java.sql.Connection;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

import kr.co.hsnc.common.base.WAFLogger;
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;

public class OutSuppFixApprPopSaveBiz extends Biz {

	public OutSuppFixApprPopSaveBiz(HandlerStorage storage) {
		super(storage);
	}
	
	public void doBiz() throws Exception {
		boolean success = save(params);
		storage.setSuccess(success);
	}
	
	private boolean save(ValueObject params) 
    {
        StringBuffer sqlStr = new StringBuffer();

        Persistent persistent = null;
        
        
        boolean succes1 = false;
        boolean succes2 = false;
        boolean succes3 = false;
        
        try 
        {
        	conn.begin();
        	
            Connection con = conn.getConnection();
            
            succes1 = deleteSuppApprItem(con, params);
            succes2 = deleteSuppApprDetail(con, params);
            succes3 = deleteSuppAppr(con, params);

            
            
            if(succes1 == true && succes2 == true && succes1 == true && succes3 == true)
            {
                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");
                }
                
                
                String newArrId = getArrId(params.get("corp_id"));
                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("                   ,ACMP_ID --기성 ID                                     \n");
                    sqlStr.append("                   ,WRITE_NO --작성자사번                                         \n");
                    sqlStr.append("                   ,CRE_BY --등록자                                                      \n");
                    sqlStr.append("                   ,CRE_DATE --등록일자                                              \n");
                    sqlStr.append("                   ,ORD_DOC_NO --계약번호                                              \n");
                    sqlStr.append("                   ,CONT_ID --계약id                                              \n");
                    sqlStr.append("                   ,REGU_STATUS_CD --상태값                                              \n");
                    sqlStr.append("                   ,YEAR_STD --평가기준년도                                              \n");
                    sqlStr.append("                )VALUES(                                                        \n");
                    sqlStr.append("                    ?                                                               \n");
                    sqlStr.append("                   ,?                                                               \n");
                    sqlStr.append("                   ,?                                                               \n");
                    sqlStr.append("            		  ,?                                                                   \n");
                    sqlStr.append("                   ,?                                                                \n");
                    sqlStr.append("                   ,?                                                                \n");
                    sqlStr.append("                   ,(SELECT MAX(ACCT_ID) FROM PUB0120T WHERE USER_SABUN = ?)			\n");
                    sqlStr.append("                   ,(SELECT MAX(USER_NAME) FROM PUB0120T WHERE USER_SABUN = ?)		\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");
                    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");
                    sqlStr.append("                   ,?                                                                \n");
                    sqlStr.append("                   ,(SELECT MAX(ACCT_ID) FROM PUB0120T WHERE USER_SABUN = ?)			\n");
                    sqlStr.append("                   ,SYSDATE                                                     			\n");
                    sqlStr.append("                   ,(SELECT MAX(CON_DOC_NO) FROM SUB2000T WHERE SUB_CONT_ID = ?)  \n");
                    sqlStr.append("                   ,?                                                                	\n");
                    sqlStr.append("                   ,?                                                                	\n");
                    sqlStr.append("                   ,(SELECT CHG_CODE01 FROM EBID_COM911T WHERE BASECD = 'SUPP_REPORT' AND DETAILCD = ? )  \n");
                    sqlStr.append("                )                                                                    	\n");
                    
                    
                    persistent.setStatement(sqlStr.toString());
                    persistent.addParameter(params.get("dept_cd"));
                    persistent.addParameter(params.get("dept_name"));
                    persistent.addParameter(params.get("corp_id"));
                    persistent.addParameter(newArrId);
                    persistent.addParameter(params.get("doc_cd"));//2015.8.7  12
                    persistent.addParameter(params.get("appr_date"));
                    persistent.addParameter(params.get("USER_SABUN"));
                    persistent.addParameter(params.get("USER_SABUN"));
                    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").replace(",", ""));
                    persistent.addParameter(params.get("item_name"));
                    persistent.addParameter(params.get("con_amt").replace(",", ""));
                    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("acmp_id"));
                    persistent.addParameter(params.get("USER_SABUN"));
                    persistent.addParameter(params.get("USER_SABUN"));
                    persistent.addParameter(params.get("subContId"));
                    persistent.addParameter(params.get("subContId"));
                    persistent.addParameter("01");
                    persistent.addParameter(params.get("doc_cd"));//2022.5.19
                    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));
                        params.set("appr_reason", params.get("appr_reason_" + i));
                        params.set("newArrId", newArrId);
                        
                        //2017.03.06 체크공백확인 
                        if (params.get("chkResultCd").length() < 1 ) {
                        	storage.setMessage("평가하지 않은 항목이 있습니다. ");
                        	conn.setRollback();
                            return false;
                        }
                        if (params.get("appr_reason").equals("")) {
                        	if(params.get("chkResultCd").equals("S") || params.get("chkResultCd").equals("B")){	//S = Exceed, B = Below
                        		storage.setMessage("Exceed 또는 Below 평가에 대한 사유를 입력하지 않은 항목이 있습니다. ");
                            	conn.setRollback();
                                return false;	
                        	}
                        }
                        saveApprItem(con, params);
                        
                    }
                }
            }
        } catch(Exception e){
        	System.out.println(e);
            e.printStackTrace();
            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() + " --> getCredit_yn() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if(vo == null) vo = new ValueObject();
        }
        
        return vo;
    }
    
    
    public String getArrId(String corp_id) throws Exception {
    	Search search = new SearchImpl();
    	StringBuffer sql = new StringBuffer();
    	ValueObject vo = new ValueObject();
    	String rtnStr = "";
    	
    	try {
    		sql.append(" SELECT DECODE(MAX(ARR_ID), '', 1, MAX(ARR_ID)+1) AS ARR_ID FROM SUPP_APPR WHERE CORP_ID = ? \n");
    		
    		search.setStatement(sql.toString());            
    		search.addParameter(corp_id);
    		
    		WAFLogger.debug(search.getParameters());
    		
    		vo = RowSetUtility.getValueObject(search.execute().getRow(0));
    		
    		rtnStr = vo.get("ARR_ID");
    		
    	} catch(Exception e) {
    		WAFLogger.error(this.getClass().getName() + " --> getArrId() : \n" + e.getMessage());
    		WAFLogger.error("SQL : " + search.getStatement());
    		WAFLogger.error(e);
    		
    	} finally {
    		if(rtnStr == null) rtnStr = "";
    	}
    	
    	return rtnStr;
    }
    
    public ValueObject getArr_id(String corp_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 = ?  \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() + " --> getArr_id() : \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("            ,APPR_REASON                                                              \n");
                sqlStr2.append("            ,CRE_BY                                                              \n");
                sqlStr2.append("            ,CRE_DATE                                                              \n");
                sqlStr2.append("            )VALUES(                                                              \n");
                sqlStr2.append("             ?                                                                          \n");
                if(params.get("newArrId").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 CHG_CODE01 FROM EBID_COM911T WHERE BASECD = 'SUPP_REPORT' AND DETAILCD = ?  )   \n");//2018.04.13
                sqlStr2.append("            ,?                                                                          \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("newArrId").equals("")){
                	persistent2.addParameter(params.get("corp_id"));
                }else{
                	persistent2.addParameter(params.get("newArrId"));
                }
                persistent2.addParameter(params.get("doc_cd"));//2018.04.13
                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("appr_reason"));
                persistent2.addParameter(params.get("USER_SABUN"));
                persistent2.execute();
                persistent2.clearParameters();
            }
            
        } catch(Exception e){
            e.printStackTrace();
            throw e;
        }    
    }

    
    
    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("subContId"));
            persistent.execute();
            persistent.clearParameters();
            return true;
        } catch(Exception e){
        	System.out.println(e);
            e.printStackTrace();
            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){
            System.out.println(e);
            e.printStackTrace();
            throw e;
        }
    }
    
    
    public boolean deleteSuppApprDetail(Connection con, ValueObject params) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        Persistent persistent = null;
        try {
            persistent = new PersistentImpl(con);            
            sqlStr.append(" DELETE FROM SUPP_OUT_APPR_DETAIL WHERE CORP_ID = ?  AND ARR_ID = ?   \n");           
            persistent.setStatement(sqlStr.toString());
            persistent.addParameter(params.get("corp_id"));
            persistent.addParameter(params.get("arr_id"));
            persistent.execute();
            persistent.clearParameters();
            return true;
        } catch(Exception e){
            System.out.println(e);
            e.printStackTrace();
            throw e;
        }
    }
}
