/**
 *
 * @(#)author  [lsm]
 * @(#)version 1.0
 * @(#)date   2014. 3. 5
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.hwenc.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of Hanwha E&C, Inc.
 */
 package kr.co.udapsoft.ebid.buyer.sycs.common.biz;


import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class CommMatlCodeDeleteBiz extends Biz {
	
	private static String DELETE_TABLE     = "PMM_MATERIALGROUP";
	private static String DELETE_DETAIL_TABLE     = "PMM_COMM_MATERIAL_MASTER";
    
	public CommMatlCodeDeleteBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		
		ValueObject temp = null;
		boolean result = false;
		
		if(params.get("level").equals("1"))
		{
			temp = get_material_group_cnt(params.get("kind_big"));
			String count = temp.get("cnt");
			if (!"0".equals(count)) {
				String message = "등록된하위코드가 있습니다. 삭제할 수 없습니다.";
//              WAFLogger.error(message);
              Exception e = new Exception(message);
              result = false;
              
              storage.setMessage(message.replace("\"", ""));
              super.storage.setSuccess(false);
              throw e;
			}else{
				result = true;
			}
		}
		else if(params.get("level").equals("2"))
		{
			temp = get_material_group_cnt(params.get("kind_mid"));
			String count = temp.get("cnt");
			if (!"0".equals(count)) {
				String message = "등록된하위코드가 있습니다. 삭제할 수 없습니다.";
//              WAFLogger.error(message);
              Exception e = new Exception(message);
              result = false;
              
              storage.setMessage(message.replace("\"", ""));
              super.storage.setSuccess(false);
              throw e;
			}else{
				result = true;
			}
		}
		else if(params.get("level").equals("3"))
		{
			temp = get_material_master_cnt(params.get("kind_small"));
			String count = temp.get("cnt");
			if (!"0".equals(count)) {
				String message = "등록된자재코드가 있습니다. 삭제할 수 없습니다.";
//              WAFLogger.error(message);
              Exception e = new Exception(message);
              result = false;
              
              storage.setMessage(message.replace("\"", ""));
              super.storage.setSuccess(false);
              throw e;
			}else{
				result = true;
			}
		}
		
		if(result == true)
		{
			try {
				super.conn.begin();
				
				
				boolean success = deleteData(params);
				success = deleteDataCCSIF(params);
				super.storage.setSuccess(success);
			} catch (Exception e) {
				
				 super.doException(e);
	            super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	            super.storage.setSuccess(false);
				
			} finally {
				super.conn.end();
			}
		}
	}
	
	
	public ValueObject get_material_master_cnt(String code) throws Exception {
        Search search = new SearchImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        
        try {
        	sql.append("	 SELECT count(*)	cnt								\n");
            sql.append("       FROM PMM_COMM_MATERIAL_MASTER		\n");
            sql.append("      WHERE KIND_SMALL = ?						\n");
            
        	search.setStatement(sql.toString());        	
        	search.addParameter(code);
            
        	vo = RowSetUtility.getValueObject(search.execute().getRow(0));
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> get_conDocNo_cnt() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
        	if(vo == null) vo = new ValueObject();
        }
        
        return vo;
    }
	
	public ValueObject get_material_group_cnt(String code) throws Exception {
        Search search = new SearchImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        
        try {
        	sql.append("	 SELECT count(*)	cnt								\n");
            sql.append("       FROM PMM_MATERIALGROUP					\n");
            sql.append("      WHERE PARENT_LEVEL = ?						\n");
            
        	search.setStatement(sql.toString());        	
        	search.addParameter(code);
            
        	vo = RowSetUtility.getValueObject(search.execute().getRow(0));
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> get_conDocNo_cnt() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
        	if(vo == null) vo = new ValueObject();
        }
        
        return vo;
    }
	
	

	/**
	 * Persistent 
	 * @param params
	 * @return
	 * * 
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean deleteData(ValueObject params) {
		
		Persistent persistent_d = null;
		Persistent persistent_d2 = null;
		
		String sqlstr_d = "";
		String sqlstr_d2 = "";
		
		
		String kind_big = params.get("kind_big");
		String kind_mid = params.get("kind_mid");
		String kind_small = params.get("kind_small");
		String level = params.get("level");
		
		try {

			persistent_d = new PersistentImpl(super.conn.getConnection());
			persistent_d2 = new PersistentImpl(super.conn.getConnection());


			  sqlstr_d = 	" DELETE                   \n"
		                  + " FROM   " + DELETE_TABLE + " \n"
		                  + " WHERE  MATERIALGROUP = ?        \n" ;
			  if(level.equals("2"))
			  {
				  sqlstr_d += " OR PARENT_LEVEL = ?";
			  }
			  if(level.equals("1"))
			  {
				  sqlstr_d += " OR PARENT_LEVEL LIKE  ? || '%'";
			  }
			  
			  
			  
			persistent_d.setStatement(sqlstr_d);
			if(level.equals("1"))
			{
				persistent_d.addParameter(kind_big);
				persistent_d.addParameter(kind_big);
			}
			else if(level.equals("2"))
			{
				persistent_d.addParameter(kind_mid);
				persistent_d.addParameter(kind_mid);
			}
			else if(level.equals("3"))
			{
				persistent_d.addParameter(kind_small);
			}
			
			persistent_d.execute();
			persistent_d.clearParameters();
			
			persistent_d2 = new PersistentImpl(super.conn.getConnection());


			sqlstr_d2 = 	" DELETE                   \n"
	                  + " FROM   " + DELETE_DETAIL_TABLE + " \n";
	                 if(level.equals("1"))
					{
	                	 sqlstr_d2 += "WHERE KIND_BIG = ?  \n";
					}
					else if(level.equals("2"))
					{
						sqlstr_d2 += "WHERE KIND_MID =  ?  \n";
					}
					else if(level.equals("3"))
					{
						sqlstr_d2 += "WHERE KIND_SMALL = ?  \n";
					}
		 
	                 persistent_d2.setStatement(sqlstr_d2);
			
			 if(level.equals("1"))
			{
				 persistent_d2.addParameter(kind_big);
			}
			else if(level.equals("2"))
			{
				persistent_d2.addParameter(kind_mid);
			}
			else if(level.equals("3"))
			{
				persistent_d2.addParameter(kind_small);
			}
			 
			 persistent_d2.execute();
			 persistent_d2.clearParameters();
			
			return true;
        }
		catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> deleteData() : " + e.getMessage());
            WAFLogger.error(e);
            return false;
        }
	}
	private boolean deleteDataCCSIF(ValueObject params) {
		
		Persistent persistent_d = null;
		Persistent persistent_d2 = null;
		
		String sqlstr_d = "";
		String sqlstr_d2 = "";
		
		
		
		String kind_big = params.get("kind_big");
		String kind_mid = params.get("kind_mid");
		String kind_small = params.get("kind_small");
		String level = params.get("level");
		
		try {
			persistent_d = new PersistentImpl(super.conn.getConnection());
			persistent_d2 = new PersistentImpl(super.conn.getConnection());
			
			
			sqlstr_d = 	" DELETE                   \n"
					+ " FROM   " + DELETE_TABLE + "@CCSIF \n"
					+ " WHERE  MATERIALGROUP = ?        \n" ;
			if(level.equals("2"))
			{
				sqlstr_d += " OR PARENT_LEVEL = ?";
			}
			if(level.equals("1"))
			{
				sqlstr_d += " OR PARENT_LEVEL LIKE  ? || '%'";
			}
			
			
			
			persistent_d.setStatement(sqlstr_d);
			if(level.equals("1"))
			{
				persistent_d.addParameter(kind_big);
				persistent_d.addParameter(kind_big);
			}
			else if(level.equals("2"))
			{
				persistent_d.addParameter(kind_mid);
				persistent_d.addParameter(kind_mid);
			}
			else if(level.equals("3"))
			{
				persistent_d.addParameter(kind_small);
			}
			
			persistent_d.execute();
			persistent_d.clearParameters();
			
			persistent_d2 = new PersistentImpl(super.conn.getConnection());
			
			
			sqlstr_d2 = 	" DELETE                   \n"
					+ " FROM   " + DELETE_DETAIL_TABLE + "@CCSIF \n";
			if(level.equals("1"))
			{
				sqlstr_d2 += "WHERE KIND_BIG = ?  \n";
			}
			else if(level.equals("2"))
			{
				sqlstr_d2 += "WHERE KIND_MID =  ?  \n";
			}
			else if(level.equals("3"))
			{
				sqlstr_d2 += "WHERE KIND_SMALL = ?  \n";
			}
			
			persistent_d2.setStatement(sqlstr_d2);
			
			if(level.equals("1"))
			{
				persistent_d2.addParameter(kind_big);
			}
			else if(level.equals("2"))
			{
				persistent_d2.addParameter(kind_mid);
			}
			else if(level.equals("3"))
			{
				persistent_d2.addParameter(kind_small);
			}
			
			
			persistent_d2.execute();
			persistent_d2.clearParameters();
			
			return true;
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> deleteData() : " + e.getMessage());
			WAFLogger.error(e);
			return false;
		}
	}
}
