/**
 * @(#)file    SalNewPopBiz.java
 * @(#)author  김예진
 * @(#)version 1.0
 * @(#)date    2015-01-12
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.outside.biz;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;

import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class stampDutyPopBiz extends Biz {

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

	public void doBiz() throws Exception {
		boolean success = false;
		
		if(storage.compareEvent("")){
			
			if(params.get("p_who").equals("E")){
				success = updateDutyGB(super.conn.getConnection(), params); 
				super.storage.setSuccess(success);
			}
			
			storage.addRowSet("CODE911", getCode911List());
			
			if(params.get("p_move").equals("I")){
				ValueObject p_stamp_duty_id = getSelectInit(super.conn.getConnection(), params.get("p_cont_mod_id")); 
				storage.addValueObject("VALUEOBJECT", p_stamp_duty_id);
			}else{
				ValueObject salstamp = getSalStamp(params.get("p_stamp_duty_id")); 
				storage.addValueObject("VALUEOBJECT", salstamp);	
			}
		}else if(storage.compareEvent("INSERT_DATA")){  
			success = saveData(super.conn.getConnection(), params); 
			super.storage.setSuccess(success);
		}else if(storage.compareEvent("UPDATE_DATA")){
			success = updateData(super.conn.getConnection(), params); 
			super.storage.setSuccess(success);
		}else if(storage.compareEvent("DELETE_DATA")){
			success = deleteData(super.conn.getConnection(), params); 
			super.storage.setSuccess(success);
		}else if(storage.compareEvent("BEFORE_ATTACH")){  
			success = saveData(super.conn.getConnection(), params); 
			super.storage.setSuccess(success); 
		}
	}
	
	public boolean updateDutyGB(Connection conn, ValueObject sqlParams) throws Exception{
        PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("  UPDATE SUB2000T SET                           	 \n");
			sql.append("          STAMP_DUTY_GB = ?                          \n");
			sql.append("  WHERE SUB_CONT_MOD_ID = ?                        	 \n");
			System.out.println(sql.toString());
			pstmt = conn.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("p_issue_gb"));
			pstmt.setString(paramIdx++, sqlParams.get("p_cont_mod_id"));
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			System.out.println(e.getMessage());
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
    }
	
	public ValueObject getSelectInit(Connection conn, String p_cont_mod_id) throws Exception{
		Search search = new SearchImpl();
        
        ValueObject vo = new ValueObject();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr =  new StringBuffer();
        
        try{
            sqlstr = null;
            sqlstr =  new StringBuffer();
            sqlstr.append("   SELECT 0 STAMP_DUTY_ID                                               \n");
			sqlstr.append("          ,'20' STAMP_DUTY_TYPE                                                                         \n");
			sqlstr.append("          ,'20' STAMP_DUTY_ISSUE_GB                                                                     \n");
			sqlstr.append("          ,'" + p_cont_mod_id + "' CONT_MOD_ID                                                          \n");
			sqlstr.append("          ,B.VENDCD AS CORP_REG_NO                                         			                   \n");
			sqlstr.append("          ,'' CORP_NAME                                                                                 \n");
			sqlstr.append("          ,A.CON_DOC_NO CONT_NO                                                                         \n");
			sqlstr.append("          ,A.MOD_NO                                                                                     \n");
			sqlstr.append("          ,'' STAMP_DUTY_NO                                                                             \n");
			sqlstr.append("          ,A.SUB_UND_CON_NAME CONT_NAME                                                                 \n");
			sqlstr.append("          ,F_GET_D_CURRENCY(A.CON_AMT, F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS CONT_AMT                  \n");
			sqlstr.append("          ,A.CON_CUR_CD CURRENCY_GB                                                                     \n");
			sqlstr.append("          ,0 AS STAMP_DUTY_TAX                                                                          \n");
			sqlstr.append("          ,'' AS STAMP_DUTY_DATE                                                                        \n");
			sqlstr.append("          ,'' REMARK                                                                                    \n");
			sqlstr.append("   FROM SUB2000T A                                                                                      \n");
			sqlstr.append("       ,CONT_OUT_SUPP B                                                                                 \n");
			sqlstr.append("   WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                          \n");
			sqlstr.append("     AND B.SIGN_SEQ = 2                                                                                 \n");
			sqlstr.append("     AND A.SUB_CONT_MOD_ID = '"+p_cont_mod_id+"'                                                        \n");
		
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch(Exception e) {
        	System.out.println(e.getMessage());
            e.printStackTrace();
        }finally{
            if(vo == null) vo = new ValueObject();
        }
        return vo;
    }
	
	/**
	 * 영업_인지세 상세를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private ValueObject getSalStamp(String Stamp_duty_id) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     
        StrBuffer sqlstr = new StrBuffer();
        
        try{
			sqlstr.append("   SELECT                                                                                               \n");
			sqlstr.append("           STAMP_DUTY_ID                                                                                \n");
			sqlstr.append("          ,STAMP_DUTY_TYPE                                                                              \n");
			sqlstr.append("          ,STAMP_DUTY_ISSUE_GB                                                                          \n");
			sqlstr.append("          ,CONT_MOD_ID                                                                                  \n");
			sqlstr.append("          ,CORP_REG_NO                                                                                  \n");
			sqlstr.append("          ,CORP_REG_NO AS CORP_REG_NO                                         			   \n");
			sqlstr.append("          ,CORP_NAME                                                                                    \n");
			sqlstr.append("          ,CONT_NO                                                                                      \n");
			sqlstr.append("          ,MOD_NO                                                                                       \n");
			sqlstr.append("          ,STAMP_DUTY_NO                                                                                \n");
			sqlstr.append("          ,CONT_NAME                                                                                    \n");
			sqlstr.append("          ,F_GET_D_CURRENCY(CONT_AMT, F_GET_CURRENCY_DIGIT(CURRENCY_GB)) AS CONT_AMT                    \n");
			sqlstr.append("          ,CURRENCY_GB                                                                                  \n");
			sqlstr.append("          ,F_GET_D_CURRENCY(STAMP_DUTY_TAX,0) AS STAMP_DUTY_TAX                                         \n");
			sqlstr.append("          ,TO_CHAR(TO_DATE(STAMP_DUTY_DATE), 'YYYY-MM-DD') AS STAMP_DUTY_DATE                           \n");
			sqlstr.append("          ,REMARK                                                                                       \n");
			sqlstr.append("   FROM STAMP_DUTY                                                                                      \n");
			sqlstr.append("   WHERE STAMP_DUTY_ID = '"+Stamp_duty_id+"'                                                            \n");

			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {
        	if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
	}
	
	private RowSet getCode911List() {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();
        try{
			sqlstr.append("  SELECT DETAILCD,DETAILNM,DETAILSORTNM,CHG_CODE01,CHG_CODE03 FROM EBID_COM911T WHERE BASECD = '911'	 ORDER BY SORT_NO 	\n");
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }finally{    
            if(rowSet == null)rowSet = new RowSetImpl();
        }
        return rowSet;
	}
	
	private boolean saveData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		String sStampDutyId = getStampDutyId(connection);
		super.storage.setMessage(sStampDutyId);
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("   INSERT INTO STAMP_DUTY(                                   \n");
			sql.append("        STAMP_DUTY_ID                                        \n");
			sql.append("       ,STAMP_DUTY_TYPE                                      \n");
			sql.append("       ,STAMP_DUTY_ISSUE_GB                                  \n");
			sql.append("       ,CONT_MOD_ID                                          \n");
			sql.append("       ,CORP_REG_NO                                          \n");
			sql.append("       ,CORP_NAME                                            \n");
			sql.append("       ,CONT_NO                                              \n");
			sql.append("       ,MOD_NO                                               \n");
			sql.append("       ,STAMP_DUTY_NO                                        \n");
			sql.append("       ,CONT_NAME                                            \n");
			sql.append("       ,CURRENCY_GB                                          \n");
			sql.append("       ,CONT_AMT                                             \n");
			sql.append("       ,STAMP_DUTY_TAX                                       \n");
			sql.append("       ,STAMP_DUTY_DATE                                      \n");
			sql.append("       ,REMARK                                               \n");
			sql.append("   )VALUES(                                                  \n");
			sql.append("        ?                                                    \n");
			sql.append("       ,'20'                                                 \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("       ,?                                                    \n");
			sql.append("   )                                                         \n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sStampDutyId);
			pstmt.setString(paramIdx++, sqlParams.get("p_stamp_duty_issue"));
			pstmt.setString(paramIdx++, sqlParams.get("p_cont_mod_id"));
			if(sqlParams.get("p_stamp_duty_issue").equals("10")){
				pstmt.setString(paramIdx++, sqlParams.get("O_VENDCD"));
				pstmt.setString(paramIdx++, sqlParams.get("O_MEMBER_NAME"));
			}else if(sqlParams.get("p_stamp_duty_issue").equals("20")){
				pstmt.setString(paramIdx++, sqlParams.get("p_corp_reg_no"));
				pstmt.setString(paramIdx++, "");
			}
			pstmt.setString(paramIdx++, sqlParams.get("p_cont_no"));
			pstmt.setString(paramIdx++, sqlParams.get("p_mod_no"));
			pstmt.setString(paramIdx++, sqlParams.get("p_stamp_duty_no"));
			pstmt.setString(paramIdx++, sqlParams.get("p_cont_name"));
			pstmt.setString(paramIdx++, sqlParams.get("p_currency_gb"));
			pstmt.setString(paramIdx++, sqlParams.get("p_cont_amt").replaceAll(",", ""));
			pstmt.setString(paramIdx++, sqlParams.get("p_stamp_duty_tax").replaceAll(",", ""));
			pstmt.setString(paramIdx++, sqlParams.get("p_stamp_duty_date").replaceAll("-", ""));
			pstmt.setString(paramIdx++, sqlParams.get("p_remark"));
	
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			System.out.println(e.getMessage());
			
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	public String getStampDutyId(Connection conn) throws Exception{
	        
		Persistent persistent_q = null;
	    
		ValueObject vo = new ValueObject();
	    ValueObject row = null;
	    StringBuffer sqlstr =  new StringBuffer();
	        
	    try{
	    	persistent_q = new PersistentImpl(conn);
	        sqlstr.append("         SELECT STAMP_DUTY_S.NEXTVAL AS STAMP_DUTY_ID  FROM DUAL                                                 \n");
	            
	        persistent_q.setStatement(sqlstr.toString()); 
	        row = RowSetUtility.getValueObject(persistent_q.query().getRow(0));
	        vo.set("STAMP_DUTY_ID", row.get("STAMP_DUTY_ID"));
	    }
	    catch(Exception e) {
	        e.printStackTrace();
	    }finally{
	        if(vo == null) vo = new ValueObject();
	    }
	        return vo.get("STAMP_DUTY_ID");
	}

	private boolean updateData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("  UPDATE STAMP_DUTY SET                           	 \n");
			sql.append("          STAMP_DUTY_ISSUE_GB= ?                    \n");
			sql.append("         ,CONT_MOD_ID        = ?                    \n");
			sql.append("         ,CORP_REG_NO        = ?                    \n");
			sql.append("         ,CORP_NAME          = ?                    \n");
			sql.append("         ,CONT_NO            = ?                    \n");
			sql.append("         ,MOD_NO             = ?                    \n");
			sql.append("         ,STAMP_DUTY_NO      = ?                    \n");
			sql.append("         ,CONT_NAME          = ?                    \n");
			sql.append("         ,CURRENCY_GB        = ?                    \n");
			sql.append("         ,CONT_AMT           = ?                    \n");
			sql.append("         ,STAMP_DUTY_TAX     = ?                    \n");
			sql.append("         ,STAMP_DUTY_DATE    = ?                    \n");
			sql.append("         ,REMARK             = ?                    \n");
			sql.append("  WHERE STAMP_DUTY_ID = ?                          	 \n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("p_stamp_duty_issue"));
			pstmt.setString(paramIdx++, sqlParams.get("p_cont_mod_id"));
			if(sqlParams.get("p_stamp_duty_issue").equals("10")){
				pstmt.setString(paramIdx++, sqlParams.get("O_VENDCD"));
				pstmt.setString(paramIdx++, sqlParams.get("O_MEMBER_NAME"));
			}else if(sqlParams.get("p_stamp_duty_issue").equals("20")){
				pstmt.setString(paramIdx++, sqlParams.get("p_corp_reg_no"));
				pstmt.setString(paramIdx++, "");
			}
			pstmt.setString(paramIdx++, sqlParams.get("p_cont_no"));
			pstmt.setString(paramIdx++, sqlParams.get("p_mod_no"));
			pstmt.setString(paramIdx++, sqlParams.get("p_stamp_duty_no"));
			pstmt.setString(paramIdx++, sqlParams.get("p_cont_name"));
			pstmt.setString(paramIdx++, sqlParams.get("p_currency_gb"));
			pstmt.setString(paramIdx++, sqlParams.get("p_cont_amt").replaceAll(",", ""));
			pstmt.setString(paramIdx++, sqlParams.get("p_stamp_duty_tax").replaceAll(",", ""));
			pstmt.setString(paramIdx++, sqlParams.get("p_stamp_duty_date").replaceAll("-", ""));
			pstmt.setString(paramIdx++, sqlParams.get("p_remark"));
			pstmt.setString(paramIdx++, sqlParams.get("p_stamp_duty_id")); 
			
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			System.out.println(e.getMessage());
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	private boolean deleteData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	DELETE FROM STAMP_DUTY WHERE STAMP_DUTY_ID = ?			\n ");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setInt(paramIdx++, sqlParams.getInt("p_stamp_duty_id"));
	
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

}