/**
 * @(#)file    PuSupplierBiz.java
 * @(#)author  이성민
 * @(#)version 1.0
 * @(#)date    2014.06.25
 *
 * 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.supplier.outside.biz;



import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
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.util.ValueObject;
import kr.co.udapsoft.common.sender.MessageSend;

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






public class OutSupplierSaveBiz extends Biz {

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

	/* (non-Javadoc)
	 * @see kr.co.hwenc.system.Biz#doBiz()
	 */
	public void doBiz() throws Exception {

		
		
		WAFLogger.debug("event==============>" + storage.getEvent());
		System.out.println("event==============>" + storage.getEvent());
		if(storage.compareEvent("SAVE_DATA"))
		{
			saveData();
		}
		else if(storage.compareEvent("UPDATE_DATA2"))
        {
			updateData2();
        }
		else if(storage.compareEvent("SAVE_DATA2"))
        {
			saveData2();
        }
		else if(storage.compareEvent("SAVE_RESULT"))
		{
			saveResult();
		}
		else if(storage.compareEvent("SAVE_ABILLTY"))
        {
			saveAbillty();
        }
		else if(storage.compareEvent("SAVE_TECH_MAN"))
        {
			saveTechMan();
        }
		else if(storage.compareEvent("SAVE_CERT"))
        {
			saveCert();
        }
		else if(storage.compareEvent("UPDATE_APPRV"))
        {
			updateApprv();
        }
		else if(storage.compareEvent("DELETE_DATA"))
        {
			deleteData();
        }
		else if(storage.compareEvent("SUPP_TEST"))
        {
			suppTest();
        }
		else if(storage.compareEvent("SUPP_TEST2"))
		{
			suppApprSet2();
		}
		else if(storage.compareEvent("SAVE_CHARGE"))
        {
			updateCharge();
        }
		
	}
	
	private void saveData() throws Exception{

		WAFLogger.debug("=================================================");
        boolean success = false;
        boolean success2 = false;
        
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
            success = updateInfo(con, params);
            success2 = update_apprv_charge(con, params);
            
            String[] chk_val = params.get("out_main_check").split(",");
            
            
            deleteOutMain(con);
            for(int k=0; k < chk_val.length; k++)
            {
                params.set("out_main_code", chk_val[k]);
                success2 =  saveOutMain(con, params);
            }
            
            
            if(params.get("finance_gbn").equals(""))
            {
                success = saveFinance(con, params);
            }
            else
            {
                success = updateFinance(con, params);
            }
            
            
            
            if(success == true && success2 == true)
            {
                String[] status = super.gets("sheet2.STATUS");
                for (int i = 0; i < status.length; i++) 
                {
                    ValueObject vo = IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i);
                	if (IBSheet7Utils.equalStatusInsert(status[i])) 
                    {
                        success2 = saveItemData(con, vo);
                    }
                    
                    if (IBSheet7Utils.equalStatusUpdate(status[i])) 
                    {
                        success2 = updateItemData(con, vo);
                    }
                    
                    if (IBSheet7Utils.equalStatusDelete(status[i])) 
                    {
                        success2 = deleteItemData(con, vo);
                    }
                    updateMainItemData(con, vo);
                }
                	
            }
            
            
            if(success == true)
            {
                super.storage.setSuccess(success);
                con.commit();
                
                storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
                
                //2016.8.19 대기->등록,가등록 변경시 메일발송 
                if("01".endsWith(params.get("past_status"))){
	                if(params.get("p_status").equals("02") || params.get("p_status").equals("03"))
	                {
	                	//sendSMS_EMAIL();
	                }
                }
            }
            
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 
	}
	private void updateData2() throws Exception{
		

		WAFLogger.debug("======================UPDATE_DATA2===========================");
        boolean success = false;
        boolean success2 = false;
        
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
        	success = updateInfo(con, params);
        	if(params.get("o_info_gbn").equals(""))
            {
        		success2 = save_supp_o_info(con, params);//2015.10.14 update supp_o_info 추가 
            }
            else
            {
            	success2 = update_supp_o_info(con, params);//2015.10.14 update supp_o_info 추가 
            }
        	//success2 = update_charge(con, params); //2015.10.12

            
            if(success == true)
            {
                String[] status = super.gets("sheet2.STATUS");
                
                for (int i = 0; i < status.length; i++) 
                {
                    ValueObject vo = IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i);
                	if (IBSheet7Utils.equalStatusInsert(status[i])) 
                    {
                        success2 = saveItemData(con, vo);
                    }
                    
                    if (IBSheet7Utils.equalStatusUpdate(status[i])) 
                    {
                        success2 = updateItemData(con, vo);
                    }
                    
                    if (IBSheet7Utils.equalStatusDelete(status[i])) 
                    {
                        success2 = deleteItemData(con, vo);
                    }
                    
                    updateMainItemData(con, vo);
                }
            }
     
            //2015.10.13
            if(params.get("finance_gbn").equals(""))
            {
                success = saveFinance(con, params);
            }
            else
            {
                success = updateFinance(con, params);
            }
            
            if(success == true)
            {
            	con.commit();
                if("00".endsWith(params.get("past_status"))){
	            	if(params.get("p_status").equals("02") || params.get("p_status").equals("03"))
	                {
	                	//sendSMS_EMAIL();
	                }
                }
            }
        
            super.storage.setSuccess(success);
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
            
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 
	}
	
	private void saveData2(){

		WAFLogger.debug("======================SAVE_DATA2===========================");
        boolean success = false;
        boolean success2 = false;
        
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
        	
        	String yyyymmdd = (new java.text.SimpleDateFormat ("yyyyMMdd")).format (new java.util.Date());
            params.set("user_id", yyyymmdd);
            
            
            success = save_info(con, params);
            success2 = save_supp_o_info(con, params);  //2015.10.14 insert supp_o_info
            
            success2 = save_charge(con, params); //기본 담당자 생성 2015.10.14
            
            if(params.get("finance_gbn").equals(""))
            {
                success = saveFinance(con, params);
            }
            else
            {
                success = updateFinance(con, params);
            }
            
            if(success == true)
            {
                String[] status = super.gets("sheet2.STATUS");
                
                for (int i = 0; i < status.length; i++) 
                {
                    ValueObject vo = IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i);
                	if (IBSheet7Utils.equalStatusInsert(status[i])) 
                    {
                        success2 = saveItemData(con, vo);
                    }
                    
                    if (IBSheet7Utils.equalStatusUpdate(status[i])) 
                    {
                        success2 = updateItemData(con, vo);
                    }
                    
                    if (IBSheet7Utils.equalStatusDelete(status[i])) 
                    {
                        success2 = deleteItemData(con, vo);
                    }
                    /** 2014-12-10 대표 아이템을 제외한 모든 아이템 REPRE_YN(대표아이템) N으로 업데이트 **/
                    updateMainItemData(con, vo);
                }
            }
            
            if(success == true)
            {
            	con.commit();
                if("00".endsWith(params.get("past_status"))){
	            	if(params.get("p_status").equals("02") || params.get("p_status").equals("03"))
	                {
	                	//sendSMS_EMAIL();
	                }
                }
            }

            super.storage.setSuccess(success);
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));	
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 
        
        
	}
	
	private void saveResult(){

		WAFLogger.debug("======================SAVE_RESULT===========================");
        boolean success = false;
        
        String[] status = super.gets("sheet5.STATUS");
        
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
        	
        	for (int i = 0; i < status.length; i++) 
            {
                if (IBSheet7Utils.equalStatusInsert(status[i])) 
                {
                    success = saveConResultData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET5", params, i));
                }
                
                if (IBSheet7Utils.equalStatusUpdate(status[i])) 
                {
                    success = updateConResultData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET5", params, i));
                }
                
                if (IBSheet7Utils.equalStatusDelete(status[i])) 
                {
                    success = deleteConResultData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET5", params, i));
                }
            }

            super.storage.setSuccess(success);
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        	
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 
	}
	
	private void saveAbillty(){
		

		WAFLogger.debug("=======================SAVE_ABILLTY==========================");
        boolean success = false;
        
        String[] status = super.gets("sheet6.STATUS");
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
        	
        	for (int i = 0; i < status.length; i++) 
            {
                if (IBSheet7Utils.equalStatusInsert(status[i])) 
                {
                    success = saveAbilltyData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET6", params, i));
                }
                
                if (IBSheet7Utils.equalStatusUpdate(status[i])) 
                {
                    success = updateAbilltyData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET6", params, i));
                }
                
                if (IBSheet7Utils.equalStatusDelete(status[i])) 
                {
                    success = deleteAbilltyData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET6", params, i));
                }
            }

            super.storage.setSuccess(success);
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 
        
	}
	
	private void saveTechMan(){

		WAFLogger.debug("========================SAVE_TECH_MAN=========================");
        boolean success = false;
        
        String[] status = super.gets("sheet7.STATUS");
        
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
        
        	
        	  for (int i = 0; i < status.length; i++) 
              {
                  if (IBSheet7Utils.equalStatusInsert(status[i])) 
                  {
                      success = saveThechManData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET7", params, i));
                  }
                  
                  if (IBSheet7Utils.equalStatusUpdate(status[i])) 
                  {
                      success = updateThechManData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET7", params, i));
                  }
                  
                  if (IBSheet7Utils.equalStatusDelete(status[i])) 
                  {
                      success = deleteThechManData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET7", params, i));
                  }
              }

              super.storage.setSuccess(success);
              storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 	
      
	}
	
	private void saveCert(){

		WAFLogger.debug("========================SAVE_CERT=========================");
        boolean success = false;
        
        String[] status = super.gets("sheet8.STATUS");
        
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
        	
        	for (int i = 0; i < status.length; i++) 
            {
                if (IBSheet7Utils.equalStatusInsert(status[i])) 
                {
                    success = saveCertData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET8", params, i));
                }
                
                if (IBSheet7Utils.equalStatusUpdate(status[i])) 
                {
                    success = updateCertData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET8", params, i));
                }
                
                if (IBSheet7Utils.equalStatusDelete(status[i])) 
                {
                    success = deleteCertData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET8", params, i));
                }
            }

            super.storage.setSuccess(success);
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        	
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 
	}
	
	private void updateApprv(){

        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
        	
        	update_apprv_info(con, params);
            //update_apprv_charge(con, params);
            
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 	
        
        
	}
	private void deleteData(){

	    boolean success = false;
	    
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
        	
        	success = delete_out_supp_item(con, params);
    	    
	        success = delete_charge(con, params);
    	        
            success = delete_info(con, params);
    	            
    	    super.storage.setSuccess(success);
    	    storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 
	    
	    
	}
	
	/**
	 * 신용평가서 정보 적용 하여 평가 자료 집계실행
	 */
	private void suppTest(){

	    boolean supp_test = false;
        conn.begin();
        try 
        {
        	Connection con = conn.getConnection();
	    
        	supp_test = sp_reg_appr_set(con, params);
            
            super.storage.setSuccess(supp_test);
            //storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
            
        } catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 
        
	}
	
	/**
	 * 
	 */
	private void suppApprSet2(){
		
		CallableStatement cs = null;
		ValueObject rtnVo = new ValueObject();
		conn.begin();
		try 
		{
			Connection con = conn.getConnection();
			
            String corp_id   = params.get("p_corp_id");
            
            String sqlStr = "{call SP_REG_APPR_SET2(?, ?, ?)}";
            
            cs = con.prepareCall(sqlStr);
            cs.setString(1, corp_id);
            cs.registerOutParameter(2, java.sql.Types.VARCHAR);
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.execute();
            
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(2));
            rtnVo.set("OUT_MESSAGE",cs.getString(3));
            
			
			storage.setSuccess(true);
			//storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		} catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
            try {
                if(cs !=null ) cs.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
			conn.end();
		} 
		
	}
	
	
	public boolean sp_reg_appr_set(Connection con, ValueObject Params) throws Exception { 
        
        CallableStatement cs = null;
        ValueObject rtnVo = new ValueObject();
        
        try {
            String corp_id   = params.get("p_corp_id");
            
            String sqlStr = "{call SP_REG_APPR_SET(?, ?, ?)}";
            
            cs = con.prepareCall(sqlStr);
            cs.setString(1, corp_id);
            cs.registerOutParameter(2, java.sql.Types.VARCHAR);
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.execute();
            
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(2));
            rtnVo.set("OUT_MESSAGE",cs.getString(3));
            
            return true;
        }
        catch(Exception e) {
        	throw e;
        }
		finally {
	        try {
	            if(cs !=null ) cs.close();
	        }
	        catch (SQLException e) {
	            e.printStackTrace();
	        }
		} 
    }

	
	
	private boolean saveItemData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    INSERT INTO SUPP_OUT_ITEM          \n ");
            sql.append("    (                                           \n ");
            sql.append("          CORP_ID                                   \n ");
            sql.append("        , OUT_ITEM_CD                             \n ");
            sql.append("        , REPRE_YN                   \n ");
            sql.append("        , CRE_BY                   \n ");
            sql.append("        , CRE_DATE                   \n ");
            sql.append("        ) VALUES (                          \n ");
            sql.append("          ?                                       \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("        ,SYSDATE                          \n ");
            sql.append("        )                                       \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("ITEM_CODE"));
            persistent.addParameter(sqlParams.get("MAIN_ITEM").equals("1") ? "Y" : "N");
            persistent.addParameter(params.get("p_corp_reg_no"));
            
            WAFLogger.debug(persistent.getParameters());
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
	
	
	private boolean updateItemData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    UPDATE SUPP_OUT_ITEM SET          \n ");
            sql.append("          OUT_ITEM_CD = ?                            \n ");
            sql.append("        , REPRE_YN = ?                            \n ");
            sql.append("        , UPD_BY       = ?            \n ");
            sql.append("        , UPD_DATE   = SYSDATE                \n ");
            sql.append("        WHERE CORP_ID = ?  AND OUT_ITEM_CD = ?                        \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(sqlParams.get("ITEM_CODE"));
            persistent.addParameter(sqlParams.get("MAIN_ITEM").equals("1") ? "Y" : "N");
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("ITEM_CODE"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }   
    
	private void updateMainItemData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        if("1".equals(sqlParams.get("MAIN_ITEM"))){
	        
	        try {
	            
	            persistent = new PersistentImpl(con);
	            
	            sql.append("    UPDATE SUPP_OUT_ITEM SET          \n ");
	            sql.append("        REPRE_YN = ?                            \n ");
	            sql.append("        , UPD_BY       = ?            \n ");
	            sql.append("        , UPD_DATE   = SYSDATE                \n ");
	            sql.append("        WHERE CORP_ID = ?  AND OUT_ITEM_CD <> ?                        \n ");
	            
	            persistent.setStatement(sql.toString());
	            persistent.addParameter("N");
	            persistent.addParameter(storage.getUser().get("acct_id"));
	            persistent.addParameter(params.get("p_corp_id"));
	            persistent.addParameter(sqlParams.get("ITEM_CODE"));
	            
	            persistent.execute();
	            persistent.clearParameters();
	        } catch(Exception e) {
	            throw e;
	        }
        }
    }   
	
    
    private boolean deleteItemData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    DELETE FROM  SUPP_OUT_ITEM          \n ");
            sql.append("        WHERE CORP_ID = ?                          \n ");
            sql.append("        AND OUT_ITEM_CD = ?                          \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("ITEM_CODE"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
            
        }
    }   
	
	
	
	/**
     * 회사정보 저장
     * @param params
     * @return
     * @throws Exception
     */
    private boolean updateInfo(Connection con, ValueObject params) throws Exception
    {   
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        try{
            persistent = new PersistentImpl(con);
     
             sqlstr.append("     UPDATE SUPP_INFO SET            \n")
                   .append("           HEAD_CORP_REG_N = ?           \n")
                   .append("          ,COPR_NO = ?           \n")
                   .append("          ,SUB_PLACE_NO = ?           \n")
                   .append("          ,CORP_KOR = ?           \n")
                   .append("          ,REP_NAME = ?           \n")
                   .append("          ,CORP_STS = ?           \n")
                   .append("          ,BIZ_FORM = ?           \n")
                   .append("          ,CORP_DIV_CD = ?           \n")
                   .append("          ,ESTA_DATE = ?           \n")
                   .append("          ,CORP_TEL = ?           \n")
                   .append("          ,CORP_FAX = ?           \n")
                   .append("          ,CORP_SCALE = ?           \n")
                   .append("          ,POST_NO = ?                     \n")
                   .append("          ,ADDRESS = ?                  \n")
                   .append("          ,PLACE_POST_NO = ?           \n")
                   .append("          ,PLACE_ADDRESS = ?        \n")
                   .append("          ,TAX_ADDR_CD = ?              \n")
                   .append("          ,UPD_BY = ?                       \n")
                   .append("          ,UPD_DATE = sysdate           \n")
                   .append("          ,USE_END_DY = ?           \n")
                   .append("          ,REG_REQ_DT = ?           \n")
                   .append("          ,REG_DT = ?           \n")
                   .append("          ,APPR_DT = ?           \n")
                   .append("          ,CAN_DT = ?           \n")
                   .append("          ,BAN_BID = ?           \n")//2018.05.24
                   .append("          ,STATUS = ?           \n")
                   .append("          ,\"COMMENT\" = ?           \n")
                   .append("          ,O_COMMENT = ?           \n")
                   .append("          ,MANAGE_NO = ?           \n")
                   .append("          ,NATION_CD = ?           \n")
                   .append("          ,HOMEPAGE_ADDR = ?           \n")  //2015.10.13
                   .append("          ,CORP_REG_NO = ?           \n")  //사업자번호2015.10.13
                   .append("          ,BANK_CD = ?           \n")
                   .append("          ,BANK_NM = ?           \n")
                   .append("          ,BANK_ACCT = ?           \n")
                   .append("    WHERE CORP_ID = ?                 \n");
            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("head_corp_reg_n"));
            persistent.addParameter(params.get("copr_no"));
            persistent.addParameter(params.get("sub_place_no"));
            persistent.addParameter(params.get("corp_kor"));
            persistent.addParameter(params.get("rep_name"));
            persistent.addParameter(params.get("corp_sts"));
            persistent.addParameter(params.get("biz_form"));
            persistent.addParameter(params.get("corp_div_cd"));
            persistent.addParameter(params.get("esta_date"));
            persistent.addParameter(params.get("corp_tel"));
            persistent.addParameter(params.get("corp_fax"));
            persistent.addParameter(params.get("corp_scale"));
            persistent.addParameter(params.get("post_no_1") + "" + params.get("post_no_2"));
            persistent.addParameter(params.get("address"));
            persistent.addParameter(params.get("place_post_no_1") + "" + params.get("place_post_no_2"));
            persistent.addParameter(params.get("place_address"));
            persistent.addParameter(params.get("tax_addr_cd"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter("20991231");//2016.8.9 params.get("use_end_dy").trim().replace("-", "")
            persistent.addParameter(params.get("reg_req_dt").trim().replace("-", ""));
            persistent.addParameter(params.get("reg_dt").trim().replace("-", ""));
            persistent.addParameter(params.get("appr_dt").trim().replace("-", ""));
            persistent.addParameter(params.get("can_dt").trim().replace("-", ""));
            persistent.addParameter(params.get("ban_bid").trim().replace("-", ""));
            persistent.addParameter(params.get("p_status").trim());
            persistent.addParameter(params.get("comment").trim());
            persistent.addParameter(params.get("o_comment").trim());
            if (params.get("p_in_out_div").equals("O")) 
            	persistent.addParameter(params.get("CORP_REG_NO").trim());//2015.10.13
            else
            	persistent.addParameter(params.get("manage_no").trim());//2015.10.13
            
            persistent.addParameter(params.get("nation_cd").trim());
            persistent.addParameter(params.get("HOMEPAGE_ADDR").trim());//2015.10.13
            if (params.get("p_in_out_div").equals("O")) 
            	if (params.get("CORP_REG_NO").length() > 10) 
            		persistent.addParameter(params.get("CORP_REG_NO").trim().substring(0,10));//2016.03.7
            	else
            	    persistent.addParameter(params.get("CORP_REG_NO").trim());//2016.03.7
            else
            	persistent.addParameter(params.get("p_corp_reg_no").trim());//2015.10.13
            

            persistent.addParameter(params.get("bank_cd").trim());
            persistent.addParameter(params.get("bank_nm").trim());
            persistent.addParameter(params.get("bank_acct").trim());
            persistent.addParameter(params.get("p_corp_id"));
            
            WAFLogger.debug(persistent.getParameters());
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
            throw e;
            
        }
    }
    
    private void deleteOutMain(Connection con) throws Exception{
        
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    DELETE FROM SUPP_OUT_MAIN          \n ");
            sql.append("        WHERE   CORP_ID = ?                                  \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            
            WAFLogger.debug(persistent.getParameters());
            persistent.execute();
            persistent.clearParameters();
            
        } catch(Exception e) {
            throw e;
        }
        
    }
    
    
    
    private boolean saveConResultData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    INSERT INTO SUPP_CONT_RESULT          \n ");
            sql.append("    (                                           \n ");
            sql.append("          CORP_ID                                   \n ");
            sql.append("        , CONT_SEQ                             \n ");
            sql.append("        , CONT_NM                   \n ");
            sql.append("        , ORDR_ORG_NM                   \n ");
            sql.append("        , ORDR_NM                      \n ");
            sql.append("        , CONT_AMT                      \n ");
            sql.append("        , CONT_DY                      \n ");
            sql.append("        , CONT_STA_DY                      \n ");
            sql.append("        , CONT_END_DY                      \n ");
            sql.append("        , CREDIT_YN                      \n ");
            sql.append("        , CRE_BY                      \n ");
            sql.append("        , CRE_DATE                      \n ");
            sql.append("        , BASE_YEAR                      \n ");
            sql.append("        , ETEC_YN                      \n ");
            sql.append("        ) VALUES (                          \n ");
            sql.append("          ?                                       \n ");
            sql.append("         ,SUPP_CONT_RESULT_S.nextval          \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,SYSDATE                                      \n ");
            sql.append("         ,?                          \n ");
            sql.append("         ,?                          \n ");
            sql.append("        )                                       \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("CONT_NM"));
            persistent.addParameter(sqlParams.get("ORDR_ORG_NM"));
            persistent.addParameter(sqlParams.get("ORDR_NM"));
            persistent.addParameter(sqlParams.get("CONT_AMT"));
            persistent.addParameter(sqlParams.get("CONT_DY").replace("-", ""));
            persistent.addParameter(sqlParams.get("CONT_STA_DY"));
            persistent.addParameter(sqlParams.get("CONT_END_DY"));
            persistent.addParameter(params.get("credit_yn"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter(sqlParams.get("BASE_YEAR"));
            persistent.addParameter(sqlParams.get("ETEC_YN"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    private boolean updateConResultData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        try {

        	persistent = new PersistentImpl(con);
            
            sql.append("    UPDATE SUPP_CONT_RESULT  SET        \n ");
            sql.append("          CONT_NM   = ?                 \n ");
            sql.append("        , ORDR_ORG_NM   = ?                 \n ");
            sql.append("        , ORDR_NM          = ?            \n ");
            sql.append("        , CONT_AMT        = ?              \n ");
            sql.append("        , CONT_DY          = ?            \n ");
            sql.append("        , CONT_STA_DY     = ?                 \n ");
            sql.append("        , CONT_END_DY   = ?                   \n ");
            sql.append("        , UPD_BY  = ?                    \n ");
            sql.append("        , UPD_DATE   = SYSDATE                   \n ");
            sql.append("        , BASE_YEAR   = ?                   \n ");
            sql.append("        , ETEC_YN   = ?                   \n ");
            sql.append("        WHERE CORP_ID = ?   AND CONT_SEQ = ?                                    \n ");
            
            persistent.setStatement(sql.toString());
            
            persistent.addParameter(sqlParams.get("CONT_NM"));
            persistent.addParameter(sqlParams.get("ORDR_ORG_NM"));
            persistent.addParameter(sqlParams.get("ORDR_NM"));
            persistent.addParameter(sqlParams.get("CONT_AMT"));
            persistent.addParameter(sqlParams.get("CONT_DY").replace("-", ""));
            persistent.addParameter(sqlParams.get("CONT_STA_DY"));
            persistent.addParameter(sqlParams.get("CONT_END_DY"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter(sqlParams.get("BASE_YEAR"));
            persistent.addParameter(sqlParams.get("ETEC_YN"));
            
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("CONT_SEQ"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    
    private boolean deleteConResultData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("   DELETE FROM SUPP_CONT_RESULT WHERE CORP_ID = ?  AND CONT_SEQ = ?     \n");
            
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("CONT_SEQ"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    
    
    
    
    private boolean saveAbilltyData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    INSERT INTO SUPP_CON_ABILLTY          \n ");
            sql.append("    (                                           \n ");
            sql.append("          CORP_ID                                   \n ");
            sql.append("        , CON_SEQ                             \n ");
            sql.append("        , BASE_YEAR                   \n ");
            sql.append("        , LIC_CD                   \n ");
            sql.append("        , LIC_NM                   \n ");  //2015.8.10
            sql.append("        , LIC_NO                      \n ");
            sql.append("        , CON_ABI_AMT                      \n ");
            sql.append("        , AREA_LANK                      \n ");
            sql.append("        , AREA_ALL_LANK                      \n ");
            sql.append("        , NATION_LANK                      \n ");
            sql.append("        , NATION_ALL_LANK                      \n ");
            sql.append("        , CREDIT_YN                      \n ");
            sql.append("        , CRE_BY                      \n ");
            sql.append("        , CONREYR                      \n ");
            sql.append("        , CRE_DATE                      \n ");
            sql.append("        ) VALUES (                          \n ");
            sql.append("          ?                                       \n ");
            sql.append("         ,SUPP_CON_ABILLTY_S.nextval          \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,SYSDATE                          \n ");
            sql.append("        )                                       \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("BASE_YEAR"));
            persistent.addParameter(sqlParams.get("LIC_CD"));
            persistent.addParameter(sqlParams.get("LIC_NM"));
            persistent.addParameter(sqlParams.get("LIC_NO"));
            persistent.addParameter(sqlParams.get("CON_ABI_AMT"));
            persistent.addParameter(sqlParams.get("AREA_LANK"));
            persistent.addParameter(sqlParams.get("AREA_ALL_LANK"));
            persistent.addParameter(sqlParams.get("NATION_LANK"));
            persistent.addParameter(sqlParams.get("NATION_ALL_LANK"));
            persistent.addParameter(params.get("credit_yn"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter(sqlParams.get("CONREYR"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    private boolean updateAbilltyData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    UPDATE SUPP_CON_ABILLTY  SET        \n ");
            sql.append("          BASE_YEAR      = ?              \n ");
            sql.append("        , LIC_CD          = ?          \n ");
            sql.append("        , LIC_NM          = ?          \n ");
            sql.append("        , LIC_NO           = ?            \n ");
            sql.append("        , CON_ABI_AMT  = ?                    \n ");
            sql.append("        , AREA_LANK        = ?               \n ");
            sql.append("        , AREA_ALL_LANK     = ?                  \n ");
            sql.append("        , NATION_LANK       = ?                \n ");
            sql.append("        , NATION_ALL_LANK  = ?                     \n ");
            sql.append("        , UPD_BY  = ?                    \n ");
            sql.append("        , CONREYR  = ?                    \n ");
            sql.append("        , UPD_DATE   = SYSDATE                   \n ");
            sql.append("        WHERE CORP_ID = ?   AND CON_SEQ = ?                                    \n ");
            
            persistent.setStatement(sql.toString());
            
            persistent.addParameter(sqlParams.get("BASE_YEAR"));
            persistent.addParameter(sqlParams.get("LIC_CD"));
            persistent.addParameter(sqlParams.get("LIC_NM"));
            persistent.addParameter(sqlParams.get("LIC_NO"));
            persistent.addParameter(sqlParams.get("CON_ABI_AMT"));
            persistent.addParameter(sqlParams.get("AREA_LANK"));
            persistent.addParameter(sqlParams.get("AREA_ALL_LANK"));
            persistent.addParameter(sqlParams.get("NATION_LANK"));
            persistent.addParameter(sqlParams.get("NATION_ALL_LANK"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter(sqlParams.get("CONREYR"));

            
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("CON_SEQ"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    
    private boolean deleteAbilltyData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("   DELETE FROM SUPP_CON_ABILLTY WHERE CORP_ID = ?  AND CON_SEQ = ?     \n");
            
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("CON_SEQ"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    
    
    private boolean saveThechManData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    INSERT INTO SUPP_TECH_MAN          \n ");
            sql.append("    (                                           \n ");
            sql.append("          CORP_ID                                   \n ");
            sql.append("        , SEQ                             \n ");
            sql.append("        , NM                   \n ");
            sql.append("        , BIRTHDAY                   \n ");
            sql.append("        , POSITION                      \n ");
            sql.append("        , QUAL_ITEM_CLASS                      \n ");
            sql.append("        , REG_NO                      \n ");
            sql.append("        , FIELD                      \n ");
            sql.append("        , TECH_CLASS                      \n ");
            sql.append("        , CREDIT_YN                      \n ");
            sql.append("        , CRE_BY                      \n ");
            sql.append("        , CRE_DATE                      \n ");
            sql.append("        ) VALUES (                          \n ");
            sql.append("          ?                                       \n ");
            sql.append("         ,SUPP_TECH_MAN_S.nextval          \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,SYSDATE                          \n ");
            sql.append("        )                                       \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("NM"));
            persistent.addParameter(sqlParams.get("BIRTHDAY"));
            persistent.addParameter(sqlParams.get("POSITION"));
            persistent.addParameter(sqlParams.get("QUAL_ITEM_CLASS"));
            persistent.addParameter(sqlParams.get("REG_NO"));
            persistent.addParameter(sqlParams.get("FIELD"));
            persistent.addParameter(sqlParams.get("TECH_CLASS"));
            persistent.addParameter(params.get("credit_yn"));
            persistent.addParameter(params.get("p_corp_reg_no"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    private boolean updateThechManData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    UPDATE SUPP_TECH_MAN  SET        \n ");
            sql.append("         NM              = ?          \n ");
            sql.append("        , BIRTHDAY         = ?               \n ");
            sql.append("        , POSITION          = ?                 \n ");
            sql.append("        , QUAL_ITEM_CLASS    = ?                       \n ");
            sql.append("        , REG_NO        = ?                   \n ");
            sql.append("        , FIELD           = ?                \n ");
            sql.append("        , TECH_CLASS   = ?                        \n ");
            sql.append("        , UPD_BY  = ?                    \n ");
            sql.append("        , UPD_DATE   = SYSDATE                   \n ");
            sql.append("        WHERE CORP_ID = ?   AND SEQ = ?                                    \n ");
            
            persistent.setStatement(sql.toString());
            
            persistent.addParameter(sqlParams.get("NM"));
            persistent.addParameter(sqlParams.get("BIRTHDAY"));
            persistent.addParameter(sqlParams.get("POSITION"));
            persistent.addParameter(sqlParams.get("QUAL_ITEM_CLASS"));
            persistent.addParameter(sqlParams.get("REG_NO"));
            persistent.addParameter(sqlParams.get("FIELD"));
            persistent.addParameter(sqlParams.get("TECH_CLASS"));
            persistent.addParameter(storage.getUser().get("acct_id"));

            
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("SEQ"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    
    private boolean deleteThechManData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("   DELETE FROM SUPP_TECH_MAN WHERE CORP_ID = ?  AND SEQ = ?     \n");
            
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("SEQ"));
            
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    
    
    private boolean saveCertData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    INSERT INTO SUPP_CERT          \n ");
            sql.append("    (                                           \n ");
            sql.append("          CORP_ID                                   \n ");
            sql.append("        , CERT_SEQ                             \n ");
            sql.append("        , CERT_DIV_CD                   \n ");
            sql.append("        , CERT_NO                   \n ");
            sql.append("        , CERT_CTNT                      \n ");
            sql.append("        , CERT_SCOPE                      \n ");
            sql.append("        , CERT_GOV                      \n ");
            sql.append("        , EXP_STA_DY                      \n ");
            sql.append("        , EXP_END_DY                      \n ");
            sql.append("        , CREDIT_YN                      \n ");
            sql.append("        , CRE_BY                      \n ");
            sql.append("        , CRE_DATE                      \n ");
            sql.append("        ) VALUES (                          \n ");
            sql.append("          ?                                       \n ");
            sql.append("         ,SUPP_CERT_S.nextval           \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,SYSDATE                          \n ");
            sql.append("        )                                       \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("CERT_DIV_CD"));
            persistent.addParameter(sqlParams.get("CERT_NO"));
            persistent.addParameter(sqlParams.get("CERT_CTNT"));
            persistent.addParameter(sqlParams.get("CERT_SCOPE"));
            persistent.addParameter(sqlParams.get("CERT_GOV"));
            persistent.addParameter(sqlParams.get("EXP_STA_DY"));
            persistent.addParameter(sqlParams.get("EXP_END_DY"));
            persistent.addParameter(params.get("credit_yn"));
            
            
            persistent.addParameter(params.get("corp_reg_no"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    private boolean updateCertData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {

        	persistent = new PersistentImpl(con);
            
            sql.append("    UPDATE SUPP_CERT SET          \n ");
            sql.append("          CERT_DIV_CD = ?                            \n ");
            sql.append("        , CERT_NO = ?                            \n ");
            sql.append("        , CERT_CTNT = ?                            \n ");
            sql.append("        , CERT_SCOPE = ?                            \n ");
            sql.append("        , CERT_GOV = ?                            \n ");
            sql.append("        , EXP_STA_DY = ?                            \n ");
            sql.append("        , EXP_END_DY = ?                            \n ");
            sql.append("        , UPD_BY       = ?            \n ");
            sql.append("        , UPD_DATE   = SYSDATE                \n ");
            sql.append("        WHERE CORP_ID = ?  AND CERT_SEQ = ?                        \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(sqlParams.get("CERT_DIV_CD"));
            persistent.addParameter(sqlParams.get("CERT_NO"));
            persistent.addParameter(sqlParams.get("CERT_CTNT"));
            persistent.addParameter(sqlParams.get("CERT_SCOPE"));
            persistent.addParameter(sqlParams.get("CERT_GOV"));
            persistent.addParameter(sqlParams.get("EXP_STA_DY"));
            persistent.addParameter(sqlParams.get("EXP_END_DY"));
            
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("CERT_SEQ"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }   
    
    
    
    
    
    
    private boolean deleteCertData(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    DELETE FROM  SUPP_CERT          \n ");
            sql.append("        WHERE CORP_ID = ?                          \n ");
            sql.append("        AND CERT_SEQ = ?                          \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(sqlParams.get("CERT_SEQ"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
        }
    }
    
    
    
    private boolean saveOutMain(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    INSERT INTO SUPP_OUT_MAIN          \n ");
            sql.append("    (                                           \n ");
            sql.append("          CORP_ID                                   \n ");
            sql.append("        , OUT_MAIN_CD                             \n ");
            sql.append("        , CRE_BY                   \n ");
            sql.append("        , CRE_DATE                   \n ");
            sql.append("        ) VALUES (                          \n ");
            sql.append("          ?                                       \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("        ,SYSDATE                          \n ");
            sql.append("        )                                       \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(params.get("out_main_code"));
            persistent.addParameter("111");
            
            WAFLogger.debug(persistent.getParameters());
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
            
        }
    }
    
	
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    private boolean saveFinance(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    INSERT INTO SUPP_FINANCE          \n ");
            sql.append("    (                                           \n ");
            sql.append("          CORP_ID                                   \n ");
            sql.append("        , BASE_YEAR                             \n ");
            sql.append("        , CAPITAL_AMT                   \n ");
            sql.append("        , FLOAT_ASSET                   \n ");
            sql.append("        , FLOAT_DEBT                      \n ");
            sql.append("        , OWN_CAP                      \n ");
            sql.append("        , TOT_CAP                      \n ");
            sql.append("        , CUR_PROFIT                      \n ");
            sql.append("        , GROSS_ASSET                      \n ");
            sql.append("        , SALES_AMT                      \n ");
            sql.append("        , CUR_SALES_AMT                      \n ");
            sql.append("        , PRI_SALES_AMT                      \n ");
            sql.append("        , CREDIT_YN                      \n ");
            sql.append("        , CRE_BY                      \n ");
            sql.append("        , CRE_DATE                      \n ");
            sql.append("        ) VALUES (                          \n ");
            sql.append("          ?                                       \n ");
            sql.append("         ,NVL(?, TO_CHAR(SYSDATE, 'YYYY'))       \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,?                                      \n ");
            sql.append("         ,SYSDATE                          \n ");
            sql.append("        )                                       \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(params.get("base_year"));
            persistent.addParameter(params.get("capital_amt").replace(",", ""));
            persistent.addParameter(params.get("float_asset").replace(",", ""));
            persistent.addParameter(params.get("float_debt").replace(",", ""));
            persistent.addParameter(params.get("own_cap").replace(",", ""));
            persistent.addParameter(params.get("tot_cap").replace(",", ""));
            persistent.addParameter(params.get("cur_profit").replace(",", ""));
            persistent.addParameter(params.get("gross_asset").replace(",", ""));
            persistent.addParameter(params.get("sales_amt").replace(",", ""));
            persistent.addParameter(params.get("cur_sales_amt").replace(",", ""));
            persistent.addParameter(params.get("pri_sales_amt").replace(",", ""));
            persistent.addParameter(params.get("credit_yn"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            
            WAFLogger.debug(persistent.getParameters());
            WAFLogger.debug(sql.toString());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
            
        }
    }
    
    
    
    private boolean updateFinance(Connection con, ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            persistent = new PersistentImpl(con);
            
            sql.append("    UPDATE SUPP_FINANCE SET          \n ");
            sql.append("          CAPITAL_AMT    = ?               \n ");
            sql.append("        , BASE_YEAR  = ?                 \n ");
            sql.append("        , FLOAT_ASSET  = ?                 \n ");
            sql.append("        , FLOAT_DEBT      = ?                 \n ");
            sql.append("        , OWN_CAP         = ?             \n ");
            sql.append("        , TOT_CAP          = ?            \n ");
            sql.append("        , CUR_PROFIT       = ?               \n ");
            sql.append("        , GROSS_ASSET     = ?                 \n ");
            sql.append("        , SALES_AMT      = ?                \n ");
            sql.append("        , CUR_SALES_AMT  = ?                    \n ");
            sql.append("        , PRI_SALES_AMT    = ?                  \n ");
            sql.append("        , CREDIT_YN        = ?             \n ");
            sql.append("        , UPD_BY  = ?                     \n ");
            sql.append("        , UPD_DATE = SYSDATE                      \n ");
            sql.append("     WHERE CORP_ID = ?                                       \n ");
            //sql.append("     AND    BASE_YEAR = ?                                       \n ");
            
            persistent.setStatement(sql.toString());
            
            persistent.addParameter(params.get("capital_amt").replace(",", ""));
            persistent.addParameter(params.get("base_year")); 
            persistent.addParameter(params.get("float_asset").replace(",", ""));
            persistent.addParameter(params.get("float_debt").replace(",", ""));
            persistent.addParameter(params.get("own_cap").replace(",", ""));
            persistent.addParameter(params.get("tot_cap").replace(",", ""));
            persistent.addParameter(params.get("cur_profit").replace(",", ""));
            persistent.addParameter(params.get("gross_asset").replace(",", ""));
            persistent.addParameter(params.get("sales_amt").replace(",", ""));
            persistent.addParameter(params.get("cur_sales_amt").replace(",", ""));
            persistent.addParameter(params.get("pri_sales_amt").replace(",", ""));
            persistent.addParameter(params.get("credit_yn"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter(params.get("p_corp_id"));
            
            
            WAFLogger.debug(persistent.getParameters());
            WAFLogger.debug(sql.toString());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
            throw e;
            
        }
    }
    
    
    private boolean update_apprv_info(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("     UPDATE SUPP_INFO SET              \n")
                   .append("          STATUS = ?                            \n")
                   .append("         ,UPD_BY = ?                                \n")
                   .append("         ,UPD_DATE = TO_CHAR(SYSDATE, 'YYYY-MM-DD')                            \n")
                   .append("     WHERE CORP_ID = ?               \n");
            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_status").trim());
            persistent.addParameter(storage.getUser().get("acct_id").trim());
            persistent.addParameter(params.get("p_corp_id").trim());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }
    
    private boolean update_apprv_charge(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("     UPDATE SUPP_CHARGE SET              \n")
                   .append("          RIGHT_ID = ?                            \n")
                   .append("         ,UPD_BY = ?                                \n")
                   .append("         ,UPD_DATE = TO_CHAR(SYSDATE, 'YYYY-MM-DD')                            \n")
                   .append("     WHERE CORP_ID = ?               \n");
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_right_id").trim());
            persistent.addParameter(storage.getUser().get("acct_id").trim());
            persistent.addParameter(params.get("p_corp_id").trim());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }
    
    
    private boolean save_info(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("     INSERT INTO SUPP_INFO              \n")
                   .append("     (                                              \n")
                   .append("          CORP_ID                               \n")
                   .append("         ,JOB_DIV --P:구매. O:외주           \n")
                   .append("         ,IN_OUT_DIV --I:국내, O:국외     \n")
                   .append("         ,MANAGE_NO                         \n")
                   .append("         ,NATION_CD                          \n")
                   .append("         ,CORP_KOR                            \n")
                   .append("         ,REP_NAME                            \n")
                   .append("         ,CORP_TEL                            \n")
                   .append("         ,CORP_FAX                            \n")
                   .append("         ,POST_NO                            \n")
                   .append("         ,ADDRESS                            \n")
                   .append("         ,STATUS                               \n")
                   .append("         ,USE_END_DY                         \n")
                   .append("         ,CAN_DT                               \n")
                   .append("         ,BAN_BID                             \n")
                   .append("         ,\"COMMENT\"                        \n")
                   .append("         ,O_COMMENT                         \n") //2016.4.28 외주의견추가 
                   .append("         ,CRE_BY                                \n")
                   .append("         ,CRE_DATE                            \n")
                   .append("         ,HOMEPAGE_ADDR                            \n")  //홈페이지2015.10.13
                   .append("         ,CORP_REG_NO                            \n")    //사업자번호2015.10.13
                   .append("         ,ESTA_DATE                            \n")      //설립일자2015.10.13
                   .append("     )VALUES(                                  \n")
                   .append("          ?                                        \n")      //CORP_ID      
                   .append("         ,?                                        \n")      //JOB_DIV --P:구
                   .append("         ,?                                        \n")      //IN_OUT_DIV --
                   .append("         ,?                                        \n")      //MANAGE_NO    
                   .append("         ,?                                        \n")      //NATION_CD    
                   .append("         ,?                                        \n")      //CORP_KOR     
                   .append("         ,?                                        \n")      //REP_NAME     
                   .append("         ,?                                        \n")      //CORP_TEL     
                   .append("         ,?                                        \n")      //CORP_FAX     
                   .append("         ,?                                        \n")      //POST_NO      
                   .append("         ,?                                        \n")      //ADDRESS      
                   .append("         ,?                                        \n")      //STATUS       
                   .append("         ,?                                        \n")      //USE_END_DY   
                   .append("         ,?                                        \n")      //CAN_DT       
                   .append("         ,?                                        \n")      //BAN_BID      
                   .append("         ,?                                        \n")      //\"COMMENT\"  
                   .append("         ,?                                        \n")      //O_COMMENT    
                   .append("         ,?                                        \n")      //CRE_BY       
                   .append("         ,SYSDATE                                  \n")     //CRE_DATE     
                   .append("         ,?                                        \n")//홈페이지HOMEPAGE_ADDR
                   .append("         ,?                                        \n")//사업자번호CORP_REG_NO  
                   .append("         ,?                                        \n")//설립일자ESTA_DATE    
                   .append("     )                                             \n");
            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_corp_id").trim());
            persistent.addParameter(params.get("job_div").trim());
            persistent.addParameter("O");
            persistent.addParameter(params.get("CORP_REG_NO").trim()); //2015.10.15 manage_no
            persistent.addParameter(params.get("nation_cd").trim());
            persistent.addParameter(params.get("corp_kor").trim());
            persistent.addParameter(params.get("rep_name").trim());
            persistent.addParameter(params.get("corp_tel").trim());
            persistent.addParameter(params.get("corp_fax").trim());
            persistent.addParameter(params.get("post_no_1").trim() + "" + params.get("post_no_2").trim());
            persistent.addParameter(params.get("address").trim());
            persistent.addParameter(params.get("p_status").trim());
            persistent.addParameter("20991231");//2016.8.9 params.get("use_end_dy").trim().replace("-", "").trim()
            persistent.addParameter(params.get("can_dt").trim().replace("-", "").trim());
            persistent.addParameter(params.get("ban_bid").trim().replace("-", "").trim());
            persistent.addParameter(params.get("comment").trim());
            persistent.addParameter(params.get("o_comment").trim());
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter(params.get("HOMEPAGE_ADDR").trim()); //2015.10.13
            persistent.addParameter(params.get("CORP_REG_NO").trim()); //2015.10.13
            persistent.addParameter(params.get("ESTA_DATE").trim()); //2015.10.13
            
            WAFLogger.debug(persistent.getParameters());
            WAFLogger.debug(sqlstr.toString());
            persistent.execute();
            persistent.clearParameters();
            
            
            return true;
            
            
        } catch(Exception e) {
           throw e;            
        }
    }
    
    /**
     * 협력업체 담당자정보 저장
     * @param params
     * @return
     * @throws Exception
     */
    private boolean save_charge(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("                                                        \n")
                   .append("         INSERT INTO SUPP_CHARGE          \n")
                   .append("         (                                              \n")
                   .append("             CHARGE_SEQ                          \n")
                   .append("            ,CORP_ID                                \n")
                   .append("            ,USER_ID                                \n")
                   .append("            ,DEFAULT_YN -- Y                    \n")
                   .append("            ,USER_NAME                            \n")
                   .append("            ,DIVISION                                 \n")
                   .append("            ,POSITION                                 \n")
                   .append("            ,EMAIL                                      \n")
                   .append("            ,TEL_NUM                                 \n")
                   .append("            ,FAX_NUM                                 \n")
                   .append("            ,HP1                                 \n")
                   .append("            ,HP2                                 \n")
                   .append("            ,HP3                                 \n")
                   .append("            ,USE_YN                                  \n")
                   .append("            ,RIGHT_ID                                \n")
                   .append("            ,CRE_BY                                  \n")
                   .append("            ,CRE_DATE                              \n")
                   .append("         )VALUES(                                   \n")
                   .append("            (SELECT DECODE(MAX(CHARGE_SEQ), '', 1, MAX(CHARGE_SEQ)+1)  SEQ FROM SUPP_CHARGE WHERE CORP_ID = ?  )          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,TO_CHAR(SYSDATE, 'YYYY-MM-DD')                                          \n")
                   .append("         )                                              \n");

            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(params.get("user_id") + "_" +  params.get("p_corp_id"));
            persistent.addParameter("Y");
            persistent.addParameter(params.get("user_name"));
            persistent.addParameter(params.get("division"));
            persistent.addParameter(params.get("position"));
            persistent.addParameter(params.get("email"));
            persistent.addParameter(params.get("tel_num"));
            persistent.addParameter(params.get("fax_num"));
            persistent.addParameter(params.get("h_phon_1"));
            persistent.addParameter(params.get("h_phon_2"));
            persistent.addParameter(params.get("h_phon_3"));
            persistent.addParameter("Y");
            persistent.addParameter(params.get("p_right_id"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }
    
    private boolean delete_info(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("     DELETE FROM  SUPP_INFO               \n")
                   .append("     WHERE CORP_ID = ?               \n");
            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_corp_id").trim());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }
    
    private boolean delete_charge(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("     DELETE FROM  SUPP_CHARGE          \n")
                    .append("     WHERE CORP_ID = ?               \n");
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_corp_id").trim());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }
    
    private boolean delete_out_supp_item(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("     DELETE FROM  SUPP_OUT_ITEM               \n")
                   .append("     WHERE CORP_ID = ?               \n");
            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_corp_id").trim());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }
    
    
    private boolean update_charge(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("     UPDATE SUPP_CHARGE SET              \n")
                    .append("          USER_NAME = ?                            \n")
                    .append("         ,DIVISION = ?                            \n")
                    .append("         ,POSITION = ?                            \n")
                    .append("         ,EMAIL = ?                            \n")
                    .append("         ,TEL_NUM = ?                            \n")
                    .append("         ,FAX_NUM = ?                            \n")
                    .append("         ,NOTE = ?                            \n")
                    .append("         ,RIGHT_ID = ?                            \n")
                    .append("         ,UPD_BY = ?                                \n")
                    .append("         ,UPD_DATE = TO_CHAR(SYSDATE, 'YYYY-MM-DD')                            \n")
                    .append("     WHERE CORP_ID = ?               \n");
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("user_name"));
            persistent.addParameter(params.get("division"));
            persistent.addParameter(params.get("position"));
            persistent.addParameter(params.get("email"));
            persistent.addParameter(params.get("tel_num"));
            persistent.addParameter(params.get("fax_num"));
            persistent.addParameter(params.get("NOTE"));
            persistent.addParameter(params.get("p_right_id").trim());
            persistent.addParameter(storage.getUser().get("acct_id").trim());
            persistent.addParameter(params.get("p_corp_id").trim());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }
    
    public RowSet getUserInfo(String corp_id) throws Exception{
        
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();       
        
        try {
            sqlstr.append(" SELECT USER_NAME, HP1, HP2, HP3, A.REP_NAME, B.EMAIL, A.CORP_KOR       \n");
            sqlstr.append(" FROM SUPP_INFO A, SUPP_CHARGE B            \n");
            sqlstr.append(" WHERE A.CORP_ID = B.CORP_ID            \n");
            sqlstr.append(" AND B.DEFAULT_YN = 'Y'            \n");
            sqlstr.append(" AND A.CORP_ID = "+corp_id+" \n");
            
            
            search.setStatement(sqlstr.toString());
            rowSet = search.execute();            
        }
        catch(WAFSQLException se) {
            se.printStackTrace();
            throw se;
        }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    private void updateCharge(){
  		
		boolean success = false;
		conn.begin();
		try 
		{
			Connection con = conn.getConnection();
			
	        String[] status = super.gets("sheet4.STATUS");
	        
	        for (int i = 0; i < status.length; i++) 
          {
	        	if (IBSheet7Utils.equalStatusInsert(status[i])) 
                {
	        		success = saveChargeData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET4", params, i));
                }
	        	
              if (IBSheet7Utils.equalStatusUpdate(status[i])) 
              {
            	  success = updateChargeData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET4", params, i));
              }
              
              if (IBSheet7Utils.equalStatusDelete(status[i])) 
              {
            	  success = deleteChargeData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET4", params, i));
              }
          }
	        
	        super.storage.setSuccess(success);
          storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} catch (Exception e) {
			super.doException(e);
			super.storage.setDetailMessage(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setSuccess(false);
			super.conn.setRollback();
		} finally {
			conn.end();
		} 
		
	}
    
	private boolean saveChargeData(Connection con, ValueObject params) throws Exception {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("                                                        \n")
                   .append("         INSERT INTO SUPP_CHARGE          \n")
                   .append("         (                                              \n")
                   .append("             CHARGE_SEQ                          \n")
                   .append("            ,CORP_ID                                \n")
                   .append("            ,USER_ID                                \n")
                   .append("            ,DEFAULT_YN -- Y                    \n")
                   .append("            ,USER_NAME                            \n")
                   .append("            ,DIVISION                                 \n")
                   .append("            ,POSITION                                 \n")
                   .append("            ,EMAIL                                      \n")
                   .append("            ,TEL_NUM                                 \n")
                   .append("            ,FAX_NUM                                 \n")
                   .append("            ,NOTE                                 \n")
                   .append("            ,HP1                                 \n")
                   .append("            ,HP2                                 \n")
                   .append("            ,HP3                                 \n")
                   .append("            ,USE_YN                                  \n")
                   .append("            ,CRE_BY                                  \n")
                   .append("            ,CRE_DATE                              \n")
                   .append("         )VALUES(                                   \n")
                   .append("            (SELECT DECODE(MAX(CHARGE_SEQ), '', 1, MAX(CHARGE_SEQ)+1)  SEQ FROM SUPP_CHARGE WHERE CORP_ID = ?  )          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,?                                          \n")
                   .append("            ,TO_CHAR(SYSDATE, 'YYYY-MM-DD')                                          \n")
                   .append("         )                                              \n");

            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("CORP_ID"));
            persistent.addParameter(params.get("CORP_ID"));
            persistent.addParameter(params.get("USER_ID"));
            persistent.addParameter("N");
            persistent.addParameter(params.get("USER_NAME"));
            persistent.addParameter(params.get("DIVISION"));
            persistent.addParameter(params.get("POSITION"));
            persistent.addParameter(params.get("EMAIL"));
            persistent.addParameter(params.get("TEL_NUM"));
            persistent.addParameter(params.get("FAX_NUM"));  
            persistent.addParameter(params.get("NOTE"));   
            persistent.addParameter(params.get("HP1"));
            persistent.addParameter(params.get("HP2"));
            persistent.addParameter(params.get("HP3"));
            persistent.addParameter("Y");
            persistent.addParameter(storage.getUser().get("acct_id"));
       	
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
        	WAFLogger.debug("event==============>" + e.getMessage());
            throw e;
        }
    }
	
    
	private boolean updateChargeData(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);            
            
            sqlstr.append("     UPDATE SUPP_CHARGE SET              \n")
                    .append("            USER_NAME = ?                           \n")
            		.append("            ,DIVISION  = ?                               \n")
            		.append("            ,POSITION  = ?                               \n")
                    .append("         ,EMAIL = ?                		            \n")
                    .append("         ,TEL_NUM = ?                            \n")
                    .append("         ,FAX_NUM = ?                            \n")
                    .append("         ,NOTE = ?                            \n")
                    .append("         ,HP1 = ?                            \n")
                    .append("         ,HP2 = ?                            \n")
                    .append("         ,HP3 = ?                            \n")
                    .append("         ,UPD_BY = ?                                \n")
                    .append("         ,UPD_DATE = SYSDATE                            \n")
                    .append("     WHERE CORP_ID = ?               \n")
                    .append("     AND CHARGE_SEQ = ?               \n");
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("USER_NAME"));
            persistent.addParameter(params.get("DIVISION"));
            persistent.addParameter(params.get("POSITION"));
            persistent.addParameter(params.get("email"));
            persistent.addParameter(params.get("tel_num"));
            persistent.addParameter(params.get("fax_num"));
            persistent.addParameter(params.get("NOTE"));
            persistent.addParameter(params.get("HP1"));
            persistent.addParameter(params.get("HP2"));
            persistent.addParameter(params.get("HP3"));
            persistent.addParameter(storage.getUser().get("acct_id").trim());
            persistent.addParameter(params.get("CORP_ID"));
            persistent.addParameter(params.get("CHARGE_SEQ"));
            
            
            WAFLogger.debug(persistent.getParameters());
            WAFLogger.debug(sqlstr.toString());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }	            
	            
	private boolean deleteChargeData(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
            sqlstr.append("     DELETE FROM  SUPP_CHARGE               \n")
                    .append("     WHERE CORP_ID = ?                    \n")
            		.append("     AND CHARGE_SEQ = ?                   \n");
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("CORP_ID").trim());
            persistent.addParameter(params.get("CHARGE_SEQ").trim());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }
	
	
	private void sendSMS_EMAIL(){
  	  try{
  		  if( params.get("taget") == null ){
  			  MessageSend messageSend = new MessageSend(storage);

  			  /**************SMS, EMAIL*********************/
  			  //사용자 정보 가져오기
  			  RowSet userInfo = getUserInfo(params.get("p_corp_id")); 

  			  //SMS
  			  String smsContent = WAFMessages.getMessage("Complete.approved.Supplier");

  			  String phon_no = userInfo.getRow(0).get("HP1") + userInfo.getRow(0).get("HP2") + userInfo.getRow(0).get("HP3");
  			  String re_phon_no = storage.getUser().get("USER_PHONE"); //SGC이앤씨 번호"024899000"

  			  messageSend.sendSMS(phon_no, re_phon_no, params.get("p_corp_id"), smsContent); //2015.9.21 등록완료메일 담당자번호  re_phon_no

  			  //EMAIL
  			  String subject = "협력업체 등록 완료";//WAFMessages.getMessage();
  			  String subject_in = "협력업체 등록 완료";//WAFMessages.getMessage();

  			  String email = userInfo.getRow(0).get("EMAIL");

  			  StringBuffer mailContent = new StringBuffer();
  			  //mailContent.append( WAFMessages.getMessage("Complete.approved.Supplier.Mail.Content") );
  			  //2016.8.22
  			  mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
         	  mailContent.append("<br>");
         	  mailContent.append("안녕하십니까, SGC이앤씨(주) 외주팀입니다. <br>");
         	  mailContent.append("귀사의 협력업체 등록요청이 승인되어 다음과 같이 안내드립니다.<br>");
         	  mailContent.append("<br>");
        	  mailContent.append("1. 협력업체 유효기간 <br>");
         	  mailContent.append("&nbsp;1) 최초 등록시 : 등록일 ~ 협력업체 정기평가일<br>");
         	  mailContent.append("&nbsp;2) 협력업체 정기평가시 : 당해 정기평가일 ~ 차기 정기평가일 <br>");
         	  mailContent.append("&nbsp;(※ 협력업체 정기평가는 연중 1회 실시하며 일정은 개별 공지 드립니다.)<br>");
           	  mailContent.append("<br>");
       	  	  mailContent.append("2. 유의사항 <br>");
         	  mailContent.append("&nbsp;1) 협력업체 담당자께서는 협력업체포탈시스템-고객센터-자료실에 업로드되어있는 매뉴얼을  <br>");
         	  mailContent.append("&nbsp;&nbsp;&nbsp;&nbsp;참고하시어 당사 업무를 진행하여 주시기 바랍니다.<br>");
        	  mailContent.append("&nbsp;2) 입찰, 계약, 대금 관련 사항은 등록된 담당자에게 e-Mail 및 SMS로 안내 되오니 담당자 정보에 <br>");
        	  mailContent.append("&nbsp;&nbsp;&nbsp;&nbsp;변동사항이 있을 경우 반드시 수정하시기 바랍니다.<br>");
         	  mailContent.append("&nbsp;3) 기타 요청사항은 외주팀에 문의하시기 바랍니다. (T. 02-489-8265)<br>");
         	  mailContent.append("</font>");
         	  messageSend.sendEmail(email, "partner@sgc.co.kr", subject, subject_in, mailContent); //
     		  
  		  }
  	  }catch(Exception e){
  		  WAFLogger.debug(e.getMessage());
  	  }
    }
	
	private boolean save_supp_o_info(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(con);
            
            
             sqlstr.append("                                                     \n")
                   .append("         INSERT INTO SUPP_O_INFO                     \n")
                   .append("         (                                           \n")
                   .append("             CORP_ID                                 \n")
                   .append("            ,BASE_YEAR                               \n")
                   .append("            ,ITEM                                    \n")
                   .append("            ,G_LEVEL                                 \n")
                   .append("            ,ADDR                                    \n")
                   .append("            ,AREA                                    \n")
                   .append("            ,ADMIN_CNT1                              \n")
                   .append("            ,ADMIN_CNT2                              \n")
                   .append("            ,ENG_CNT1                                \n")
                   .append("            ,ENG_CNT2                                \n")
                   .append("            ,NOR_CNT1                                \n")
                   .append("            ,NOR_CNT2                                \n")
                   .append("            ,ETC_CNT1                                \n")
                   .append("            ,ETC_CNT2                                \n")
                   .append("            ,CRE_BY                                  \n")
                   .append("            ,CRE_DATE                                \n")
                   .append("            ,ADD_MANG_NO                             \n")
                   .append("         )VALUES(                                    \n")
                   .append("             ?                                          \n") //CORP_ID
                   .append("            ,TO_CHAR(SYSDATE, 'YYYY')                   \n") //BASE_YEAR
                   .append("            ,?                                          \n") //ITEM
                   .append("            ,?                                          \n") //G_LEVEL
                   .append("            ,?                                          \n") //ADDR
                   .append("            ,?                                          \n") //AREA
                   .append("            ,?                                          \n") //ADMIN_CNT1
                   .append("            ,?                                          \n") //ADMIN_CNT2
                   .append("            ,?                                          \n") //ENG_CNT1
                   .append("            ,?                                          \n") //ENG_CNT2
                   .append("            ,?                                          \n") //NOR_CNT1
                   .append("            ,?                                          \n") //NOR_CNT2
                   .append("            ,?                                          \n") //ETC_CNT1
                   .append("            ,?                                          \n") //ETC_CNT2                   
                   .append("            ,?                                          \n") //CRE_BY
                   .append("            ,TO_CHAR(SYSDATE, 'YYYY-MM-DD')             \n") //CRE_DATE
                   .append("            ,?                                          \n") //ADD_MANG_NO
                   .append("         )                                              \n");

            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(params.get("item"));
            persistent.addParameter(params.get("g_level"));
            persistent.addParameter(params.get("shop_addr"));
            persistent.addParameter(params.get("shop_area").replace(",", ""));
            persistent.addParameter(params.get("admin_cnt1").replace(",", ""));
            persistent.addParameter(params.get("admin_cnt2").replace(",", ""));
            persistent.addParameter(params.get("eng_cnt1").replace(",", ""));
            persistent.addParameter(params.get("eng_cnt2").replace(",", ""));
            persistent.addParameter(params.get("nor_cnt1").replace(",", ""));
            persistent.addParameter(params.get("nor_cnt2").replace(",", ""));
            persistent.addParameter(params.get("etc_cnt1").replace(",", ""));
            persistent.addParameter(params.get("etc_cnt2").replace(",", ""));
            persistent.addParameter(storage.getUser().get("acct_id"));
            persistent.addParameter(params.get("manage_no"));
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	throw e;
        }
    }
    
	private boolean update_supp_o_info(Connection con, ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            persistent = new PersistentImpl(con);
            
              sqlstr.append("      UPDATE SUPP_O_INFO SET                              \n")
                    .append("             BASE_YEAR  = TO_CHAR(SYSDATE, 'YYYY')        \n")
                    .append("            ,ITEM       = ?                               \n")
                    .append("            ,G_LEVEL    = ?                               \n")
                    .append("            ,ADDR       = ?                               \n")
                    .append("            ,AREA       = ?                               \n")
                    .append("            ,ADMIN_CNT1 = ?                               \n")
                    
                    .append("            ,ADMIN_CNT2 = ?                               \n")
                    .append("            ,ENG_CNT1   = ?                               \n")
                    .append("            ,ENG_CNT2   = ?                               \n")
                    .append("            ,NOR_CNT1   = ?                               \n")
                    .append("            ,NOR_CNT2   = ?                               \n")
                    
                    .append("            ,ETC_CNT1   = ?                               \n")
                    .append("            ,ETC_CNT2   = ?                               \n")
                    .append("            ,UPD_BY     = ?                               \n")
                    .append("            ,UPD_DATE   = TO_CHAR(SYSDATE, 'YYYY-MM-DD')  \n")
                    .append("            ,ADD_MANG_NO     = ?                          \n")
                    .append("      WHERE CORP_ID = ?                                   \n");
            
              
            persistent.setStatement(sqlstr.toString());
            
            persistent.addParameter(params.get("item"));
            persistent.addParameter(params.get("g_level"));
            persistent.addParameter(params.get("shop_addr"));
            persistent.addParameter(params.get("shop_area").replace(",", ""));  
            persistent.addParameter(params.get("admin_cnt1").replace(",", ""));
            
            persistent.addParameter(params.get("admin_cnt2").replace(",", ""));
            persistent.addParameter(params.get("eng_cnt1").replace(",", ""));
            persistent.addParameter(params.get("eng_cnt2").replace(",", ""));
            persistent.addParameter(params.get("nor_cnt1").replace(",", ""));
            persistent.addParameter(params.get("nor_cnt2").replace(",", ""));
            
            persistent.addParameter(params.get("etc_cnt1").replace(",", ""));
            persistent.addParameter(params.get("etc_cnt2").replace(",", ""));            
            persistent.addParameter(storage.getUser().get("acct_id").trim());
            persistent.addParameter(params.get("manage_no"));
            persistent.addParameter(params.get("p_corp_id"));
          
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
        	WAFLogger.debug(e.getMessage());
        	throw e;
        }
    }
    
}