/**
 * @(#)file    ItemSectListBiz.java
 * @(#)author  pej
 * @(#)version 1.0
 * @(#)date    2007.06-13
 * @(#)since   JDK1.4.1
 *
 * Copyright (c) 2002-2003 www.hwenc.com, Inc.  
 * All rights reserved.
 * This software is the proprietary information of hwenc, Inc.
 */
package kr.co.udapsoft.ebid.buyer.sycs.common.biz;

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;

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;




public class MatlCategoryCalTypeSelectBiz extends Biz {
	
	private String SAVE_TABLE = "PMM_MATL_CAL_TYPE";
	

    public MatlCategoryCalTypeSelectBiz(HandlerStorage storage) {
        super(storage);        
    }
    
    public void doBiz() throws Exception {
    	
    	String systemId   = params.get("system_id");
    	
    	if(storage.getEvent().equals("GET_CALTYPE_LIST")){
    		
            params.set("sheet1", getItemSectList(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
            
    	}
    	else if(storage.getEvent().equals("SAVE_CALTYPE_LIST"))
    	{
    		try {
    			super.conn.begin();
    			
    			boolean success = saveData(params);
    			success = saveDataCCSIF(params);
        		super.storage.setSuccess(success);
        		storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
    		}catch (Exception e) {
    			super.doException(e);
                super.storage.setMessage(e.getMessage());
                super.storage.setDetailMessage(e);
                super.conn.setRollback();
                storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
    		} finally {
    			super.conn.end();
    		}
    	}
    		
    }  
    
    
    private boolean saveData(ValueObject params) {
    	try {
    		
    		Persistent persistent_i = null;
    		Persistent persistent_u = null;
    		Persistent persistent_d = null;
    		String sqlstr_i = "";
    		String sqlstr_u = "";
    		String sqlstr_d = "";
			
			persistent_i = new PersistentImpl(super.conn.getConnection());
			persistent_u = new PersistentImpl(super.conn.getConnection());
			persistent_d = new PersistentImpl(super.conn.getConnection());
			
			
			ValueObject temp = get_calType_cnt(params.get("calTpCode"));
			String count = temp.get("cnt");


			  sqlstr_i += "  INSERT INTO " + SAVE_TABLE + "            								\n"
					  	+ "   (          																				\n"		
				        + "   	  CAL_TP_CODE	              													\n"		
				        + "    , SPEC1           																	\n"		
				        + "    , SPEC2          																	\n"		
				        + "    , SPEC3          		        													\n"		
				        + "    , SPEC4			                  													\n"		
				        + " 	) VALUES                  															\n"
				        + "  	( 																						\n"
				        + "    ?,                     																	\n"
				        + "    ?,																						\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?                     																	\n"
				        + "   )         																				\n" ;
			  
			  sqlstr_u = " UPDATE " + SAVE_TABLE + "       														\n"
		              + " SET    CAL_TP_CODE    = ?        															\n"
		              + "        , SPEC1 = ?												        						\n"
		              + "        , SPEC2   = ?        																		\n"
		              + "        , SPEC3   = ?        																		\n"
		              + "        , SPEC4   = ?        																		\n"
		              + " WHERE  CAL_TP_CODE	      = ?      													\n" ;
			  
			  
			
			  sqlstr_d = 	" DELETE                   				\n"
		                  + " FROM   " + SAVE_TABLE + "			\n"
		                  + " WHERE  CAL_TP_CODE	 = ?        	\n" ;
		        
			  
			String[] status  					= super.gets("sheet1.STATUS");
			String[] calTpCode      			= super.gets("sheet1.CAL_TP_CODE");
			String[] spec1      		= super.gets("sheet1.SPEC1");
			String[] spec2      		= super.gets("sheet1.SPEC2");
			String[] spec3      		= super.gets("sheet1.SPEC3");
			String[] spec4      		= super.gets("sheet1.SPEC4");
			
			
			persistent_i.setStatement(sqlstr_i);
			persistent_u.setStatement(sqlstr_u);
			persistent_d.setStatement(sqlstr_d);
			
			
			if( status != null ) {
				for( int i = 0 ; i < status.length ; i++ ) {
					if( "I".equals(status[i]) ) {						
						persistent_i.addParameter(calTpCode[i].trim()); //vendor_cd
						persistent_i.addParameter(spec1[i].trim());
						persistent_i.addParameter(spec2[i].trim());
						persistent_i.addParameter(spec3[i].trim());
						persistent_i.addParameter(spec4[i].trim());
						
						persistent_i.execute();
						persistent_i.clearParameters();
					}
					
					else if( "U".equals(status[i]) ) {
						persistent_u.addParameter(calTpCode[i].trim());
						persistent_u.addParameter(spec1[i].trim());
						persistent_u.addParameter(spec2[i].trim());
						persistent_u.addParameter(spec3[i].trim());
						persistent_u.addParameter(spec4[i].trim());
						persistent_u.addParameter(calTpCode[i].trim());
						
						persistent_u.execute();
						persistent_u.clearParameters();
					}
					else if( "D".equals(status[i]) ) {
						if(!count.equals("0"))
						{
							String message = "사용중인 Type Code 는 삭제할 수 없습니다.";
//		                    WAFLogger.error(message);
		                    Exception e = new Exception(message);
		                    
		                    storage.setMessage(message.replace("\"", ""));
		                    super.storage.setSuccess(false);
		                    throw e;
						}else{
							persistent_d.addParameter(calTpCode[i].trim());
							persistent_d.execute();
							persistent_d.clearParameters();
						}
					}
				}
			}
			return true;
        }
		catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> saveData() : " + e.getMessage());
            WAFLogger.error(e);
            return false;
        }
    }
    private boolean saveDataCCSIF(ValueObject params) {
    	try {
    		
    		Persistent persistent_i = null;
    		Persistent persistent_u = null;
    		Persistent persistent_d = null;
    		String sqlstr_i = "";
    		String sqlstr_u = "";
    		String sqlstr_d = "";
    		
    		
    		persistent_i = new PersistentImpl(super.conn.getConnection());
    		persistent_u = new PersistentImpl(super.conn.getConnection());
    		persistent_d = new PersistentImpl(super.conn.getConnection());
    		
    		
    		ValueObject temp = get_calType_cnt(params.get("calTpCode"));
    		String count = temp.get("cnt");
    		
    		
    		sqlstr_i += "  INSERT INTO " + SAVE_TABLE + "@CCSIF            								\n"
    				+ "   (          																				\n"		
    				+ "   	  CAL_TP_CODE	              													\n"		
    				+ "    , SPEC1           																	\n"		
    				+ "    , SPEC2          																	\n"		
    				+ "    , SPEC3          		        													\n"		
    				+ "    , SPEC4			                  													\n"		
    				+ " 	) VALUES                  															\n"
    				+ "  	( 																						\n"
    				+ "    ?,                     																	\n"
    				+ "    ?,																						\n"
    				+ "    ?,                     																	\n"
    				+ "    ?,                     																	\n"
    				+ "    ?                     																	\n"
    				+ "   )         																				\n" ;
    		
    		sqlstr_u = " UPDATE " + SAVE_TABLE + "@CCSIF       														\n"
    				+ " SET    CAL_TP_CODE    = ?        															\n"
    				+ "        , SPEC1 = ?												        						\n"
    				+ "        , SPEC2   = ?        																		\n"
    				+ "        , SPEC3   = ?        																		\n"
    				+ "        , SPEC4   = ?        																		\n"
    				+ " WHERE  CAL_TP_CODE	      = ?      													\n" ;
    		
    		
    		
    		sqlstr_d = 	" DELETE                   				\n"
    				+ " FROM   " + SAVE_TABLE + "@CCSIF			\n"
    				+ " WHERE  CAL_TP_CODE	 = ?        	\n" ;
    		
    		
    		String[] status  					= super.gets("sheet1.STATUS");
    		String[] calTpCode      			= super.gets("sheet1.CAL_TP_CODE");
    		String[] spec1      		= super.gets("sheet1.SPEC1");
    		String[] spec2      		= super.gets("sheet1.SPEC2");
    		String[] spec3      		= super.gets("sheet1.SPEC3");
    		String[] spec4      		= super.gets("sheet1.SPEC4");
    		
    		
    		persistent_i.setStatement(sqlstr_i);
    		persistent_u.setStatement(sqlstr_u);
    		persistent_d.setStatement(sqlstr_d);
    		
    		
    		if( status != null ) {
    			for( int i = 0 ; i < status.length ; i++ ) {
    				if( "I".equals(status[i]) ) {						
    					persistent_i.addParameter(calTpCode[i].trim()); //vendor_cd
    					persistent_i.addParameter(spec1[i].trim());
    					persistent_i.addParameter(spec2[i].trim());
    					persistent_i.addParameter(spec3[i].trim());
    					persistent_i.addParameter(spec4[i].trim());
    					
    					persistent_i.execute();
    					persistent_i.clearParameters();
    				}
    				
    				else if( "U".equals(status[i]) ) {
    					persistent_u.addParameter(calTpCode[i].trim());
    					persistent_u.addParameter(spec1[i].trim());
    					persistent_u.addParameter(spec2[i].trim());
    					persistent_u.addParameter(spec3[i].trim());
    					persistent_u.addParameter(spec4[i].trim());
    					persistent_u.addParameter(calTpCode[i].trim());
    					
    					persistent_u.execute();
    					persistent_u.clearParameters();
    				}
    				else if( "D".equals(status[i]) ) {
    					if(!count.equals("0"))
    					{
    						String message = "사용중인 Type Code 는 삭제할 수 없습니다.";
//		                    WAFLogger.error(message);
    						Exception e = new Exception(message);
    						
    						storage.setMessage(message.replace("\"", ""));
    						super.storage.setSuccess(false);
    						throw e;
    					}else{
    						persistent_d.addParameter(calTpCode[i].trim());
    						persistent_d.execute();
    						persistent_d.clearParameters();
    					}
    				}
    			}
    		}
    		return true;
    	}
    	catch(Exception e) {
    		
    		WAFLogger.error(this.getClass().getName() + " --> saveData() : " + e.getMessage());
//            WAFLogger.error("SQL : " + persistent_d.getStatement());
    		WAFLogger.error(e);
    		return false;
    	}
    }
    
    
    
    public RowSet getItemSectList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr = new StringBuffer();
        
        
        try {
        		
        	sqlstr.append(" 	SELECT CAL_TP_CODE, TRIM(SPEC1) SPEC1, TRIM(SPEC2) SPEC2, TRIM(SPEC3) SPEC3, TRIM(SPEC4) SPEC4 FROM PMM_MATL_CAL_TYPE				\n")
        		   .append("			ORDER BY CAL_TP_CODE ASC																					\n");

        	
            search.setStatement(sqlstr);
            
            WAFLogger.debug(sqlstr);
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
        	WAFLogger.error(this.getClass().getName() + " --> getItemSectList() : \n" + se.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(se);
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
	}
    
    
    public ValueObject get_calType_cnt(String calTpCode) 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 CAL_TP_CODE = ?	\n");
            
        	search.setStatement(sql.toString());        	
        	search.addParameter(calTpCode);
            
        	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;
    }
    
    
}
