/**
 * @(#)file   StampListSaveBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.6.22
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */

 package kr.co.udapsoft.ebid.buyer.cont.purchase.biz;

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

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;

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

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

	public void doBiz() throws Exception {
		if (storage.compareEvent("SAVE_STAMP_LIST")) {
			
			boolean success = false;
			String[] status = super.gets("sheet2.STATUS");

			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					
					if (IBSheet7Utils.equalStatusInsert(status[i])) {
						success = saveData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
						//updateStampDutyGb(super.conn.getConnection(), params);
					}
					
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = updateData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
					}
					
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						FileManager fileManager = new FileManager();
						
		            	fileManager.delFileMapProc(conn.getConnection(), "PUR_STAMP_DUTY", IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i).get("STAMP_DUTY_ID"));
		            	
						success = deleteData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
					}
				}
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}
	}

	private boolean saveData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		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("        STAMP_DUTY_S.NEXTVAL                                 \n");
			sql.append("       ,'10'                                                 \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++, sqlParams.get("STAMP_DUTY_ISSUE_GB"));
			pstmt.setString(paramIdx++, params.get("PUR_CONT_MOD_ID"));
			if(sqlParams.get("STAMP_DUTY_ISSUE_GB").equals("10")){
				pstmt.setString(paramIdx++, params.get("O_VENDCD"));
				pstmt.setString(paramIdx++, params.get("O_MEMBER_NAME"));
			}else if(sqlParams.get("STAMP_DUTY_ISSUE_GB").equals("20")){
				pstmt.setString(paramIdx++, params.get("C_VENDCD"));
				pstmt.setString(paramIdx++, params.get("C_MEMBER_NAME"));
			}
			pstmt.setString(paramIdx++, params.get("ORD_DOC_NO"));
			pstmt.setString(paramIdx++, params.get("MOD_NO"));
			pstmt.setString(paramIdx++, sqlParams.get("STAMP_DUTY_NO"));
			pstmt.setString(paramIdx++, params.get("CST_DOC_NAME"));
			pstmt.setString(paramIdx++, params.get("CURRENCY_GB"));
			pstmt.setString(paramIdx++, params.get("ORD_AMT").replaceAll(",", ""));
			pstmt.setString(paramIdx++, sqlParams.get("STAMP_DUTY_TAX"));
			pstmt.setString(paramIdx++, sqlParams.get("STAMP_DUTY_DATE"));
			pstmt.setString(paramIdx++, sqlParams.get("REMARK"));
			
			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);
			}
		}
	}
	
	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("STAMP_DUTY_ISSUE_GB"));
			pstmt.setString(paramIdx++, params.get("PUR_CONT_MOD_ID"));
			if(sqlParams.get("STAMP_DUTY_ISSUE_GB").equals("10")){
				pstmt.setString(paramIdx++, params.get("O_VENDCD"));
				pstmt.setString(paramIdx++, params.get("O_MEMBER_NAME"));
			}else if(sqlParams.get("STAMP_DUTY_ISSUE_GB").equals("20")){
				pstmt.setString(paramIdx++, params.get("C_VENDCD"));
				pstmt.setString(paramIdx++, params.get("C_MEMBER_NAME"));
			}
			pstmt.setString(paramIdx++, params.get("ORD_DOC_NO"));
			pstmt.setString(paramIdx++, params.get("MOD_NO"));
			pstmt.setString(paramIdx++, sqlParams.get("STAMP_DUTY_NO"));
			pstmt.setString(paramIdx++, params.get("CST_DOC_NAME"));
			pstmt.setString(paramIdx++, params.get("CURRENCY_GB"));
			pstmt.setString(paramIdx++, params.get("ORD_AMT").replaceAll(",", ""));
			pstmt.setString(paramIdx++, sqlParams.get("STAMP_DUTY_TAX"));
			pstmt.setString(paramIdx++, sqlParams.get("STAMP_DUTY_DATE"));
			pstmt.setString(paramIdx++, sqlParams.get("REMARK"));
			pstmt.setString(paramIdx++, sqlParams.get("STAMP_DUTY_ID"));
			//pstmt.setInt(paramIdx++, sqlParams.getInt("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);
			}
		}
	}

	private boolean updateStampDutyGb(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("  UPDATE PUR2000T SET                           	 \n");
			sql.append("          STAMP_DUTY_GB= ?                    \n");
			sql.append("  WHERE PUR_CONT_MOD_ID = ?                          	 \n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("p_issue_gb"));
			pstmt.setString(paramIdx++, sqlParams.get("PUR_CONT_MOD_ID"));
			//pstmt.setInt(paramIdx++, sqlParams.getInt("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);
			}
		}
	}
	
	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("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);
			}
		}
	}
}
