/**
 *
 * @(#)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.config.WAFConfig;
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 oracle.toplink.internal.ox.StrBuffer;

import java.sql.Connection;

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

public class CommMatlCodeTagSectListSaveBiz extends Biz {
	
	private static String SAVE_TABLE     = "PMM_COMM_MATERIAL_MASTER";
	private static String MATERIAL_ID_SELECT	= " SELECT DECODE(MAX(MATERIAL_ID) + 1, '', ? || '001' , MAX(MATERIAL_ID) + 1) FROM PMM_COMM_MATERIAL_MASTER WHERE KIND_BIG = ? AND KIND_MID = ? AND KIND_SMALL = ?";
    
	public CommMatlCodeTagSectListSaveBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		try {
			boolean success = saveData(params);
			
			boolean successErp = saveDataErp(params);
			
			super.storage.setSuccess(success);
			
	        storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
		catch(Exception e) {
			super.storage.setSuccess(false); 
			super.storage.setMessage(e.getMessage());
		}
	}
	
	public String getMaterialId_i(ValueObject params) {

        Search search = new SearchImpl();
        RowSet rowSet = null;
        String sqlstr = "";
        String material_code="";
        
        String kind_big = params.get("kind_big");
		String kind_mid = params.get("kind_mid");
		String kind_small = params.get("kind_small");
		
        try {
        	sqlstr += MATERIAL_ID_SELECT;
        	
            search.setStatement(sqlstr);
            search.addParameter(kind_small.trim());
            search.addParameter(kind_big.trim());
            search.addParameter(kind_mid.trim());
            search.addParameter(kind_small.trim());
            
            rowSet = search.execute();
            material_code = rowSet.getRow(0).get(0);
        }
        catch(Exception e) {
    
            System.out.println("SQL : " + search.getStatement());
            System.out.println(e);
        }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            
        }
        return material_code;
    }

	/**
	 * Persistent 
	 * @param params
	 * @return
	 * * 
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean saveData(ValueObject params) {
		
		Persistent persistent_i = null;
		Persistent persistent_u = null;
		Persistent persistent_d = null;
		String sqlstr_i = "";
		String sqlstr_u = "";
		String sqlstr_d = "";
		
		
		String kind_big = params.get("kind_big");
		String kind_mid = params.get("kind_mid");
		String kind_small = params.get("kind_small");
		
		//String I_material_id = getMaterialId_i(params);
		
//		System.out.println("I_material_id====>"+  I_material_id);
		

		try {

			conn.begin();
			
			persistent_i = new PersistentImpl(super.conn.getConnection());
			persistent_u = new PersistentImpl(super.conn.getConnection());
			persistent_d = new PersistentImpl(super.conn.getConnection());


			  sqlstr_i += "  INSERT INTO " + SAVE_TABLE + "            								\n"
					  	+ "   (          																				\n"		
				        + "   	  MATERIAL_CD              														\n"		//자재코드
				        + "    , KIND_BIG              															\n"		//대분류
				        + "    , KIND_MID          																\n"		//중분류
				        + "    , KIND_SMALL             															\n"		//소분류
				        + "    , MATERIAL_NAME           														\n"		//자재명
				        + "    , SPEC1					           													\n"
				        + "    , SPEC2					           													\n"
				        + "    , SPEC3					           													\n"
				        + "    , SPEC4					           													\n"
				        + "    , IS_USE	             																\n"		//사용여부
				        + "    , CRE_BY             																\n"		//등록자
				        + "    , CRE_DATE          																\n"		//등록일자
				        + "    , MATERIAL_SPEC   																\n"		//규격
				        + "    , PAY_DIV   																\n"		//규격
				        + "    , MATERIAL_ID   																\n"		//2019.05.22 정렬을 위해 추가 
				        + " ) VALUES                  																\n"
				        + "  ( 																						\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,				         																\n"
			  			+ "    sysdate,		         																\n"
			  			+ "    ?,                                                                                       \n"
			            + "    ?,                                                                                       \n"
			            + "    MATERIAL_ID_S.NEXTVAL       )                                                                                \n";
		                
			  
			  sqlstr_u = " UPDATE " + SAVE_TABLE + "       				\n"
		              + " SET    MATERIAL_CD    = ?        					\n"
		              + "        , MATERIAL_NAME = ?        					\n"
		              + "        , SPEC1 = ?    			    					\n"
		              + "        , SPEC2 = ?        								\n"
		              + "        , SPEC3 = ?       			 					\n"
		              + "        , SPEC4 = ?        								\n"
		              + "        , IS_USE        = ?        						\n"
		              + "        , UPD_BY      = ?        						\n"
		              + "        , UPD_DATE    = sysdate   					\n"
		              + "        , MATERIAL_SPEC    = ?   					\n"
		              + "        , PAY_DIV    = ?   					\n"
		              + " WHERE  MATERIAL_CD      = ?      				\n" ;
			  
			  //+ "        UPD_DATE    = TO_CHAR(sysdate, 'YYYYMMDDHH24MISS')   \n"
			  
			
			  sqlstr_d = 	" DELETE                   				\n"
		                  + " FROM   " + SAVE_TABLE + "			\n"
		                  + " WHERE  MATERIAL_CD = ?        	\n" ;
		        
			  
			String[] status  		= super.gets("sheet1.STATUS");
			String[] material_cd      	= super.gets("sheet1.MATERIAL_CD");
			String[] material_name    = super.gets("sheet1.MATERIAL_NAME");
			String[] spec1    = super.gets("sheet1.SPEC1");
			String[] spec2    = super.gets("sheet1.SPEC2");
			String[] spec3    = super.gets("sheet1.SPEC3");
			String[] spec4    = super.gets("sheet1.SPEC4");
			String[] is_use     	= super.gets("sheet1.IS_USE");
			String[] material_spec     	= super.gets("sheet1.MATERIAL_SPEC");
			String[] pay_div     	= super.gets("sheet1.PAY_DIV");
//			String[] code_id     	= super.gets("CODE_ID");
			
			
			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(material_cd[i].trim());
						persistent_i.addParameter(kind_big.trim());
						persistent_i.addParameter(kind_mid.trim());
						persistent_i.addParameter(kind_small.trim());
						persistent_i.addParameter(material_name[i].trim());
						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.addParameter(is_use[i].trim());
						persistent_i.addParameter(storage.getUser().get("ACCT_ID"));	
						persistent_i.addParameter(material_spec[i].trim());
						persistent_i.addParameter(pay_div[i].trim());
								
						persistent_i.execute();
						persistent_i.clearParameters();
					}
					
					else if( "U".equals(status[i]) ) {
						persistent_u.addParameter(material_cd[i].trim());
						persistent_u.addParameter(material_name[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(is_use[i].trim());
						persistent_u.addParameter(storage.getUser().get("ACCT_ID"));	
						persistent_u.addParameter(material_spec[i].trim());
						persistent_u.addParameter(pay_div[i].trim());
						persistent_u.addParameter(material_cd[i].trim());
						
						System.out.println(persistent_u.getParameters());
						System.out.println(sqlstr_u.toString());
						
						persistent_u.execute();
						persistent_u.clearParameters();
					}
					else if( "D".equals(status[i]) ) {
			
						persistent_d.addParameter(material_cd[i]);
						persistent_d.execute();
						persistent_d.clearParameters();
					}
				}
			}
			
			return true;
        }
		catch(Exception e) {
            super.storage.setMessage(e.getMessage());
            return false;
        }
        finally {
        	super.conn.end();
        }
	}
	
	private boolean saveDataErp(ValueObject params) {
		
		Persistent persistent_i = null;
		Persistent persistent_u = null;
		Persistent persistent_d = null;
		StringBuffer sqlstr = new StringBuffer();
		StringBuffer sqlstr_i = new StringBuffer();
		StringBuffer sqlstr_u = new StringBuffer();
		StringBuffer sqlstr_d = new StringBuffer();
		
		
		String kind_big = params.get("kind_big");
		String kind_mid = params.get("kind_mid");
		String kind_small = params.get("kind_small");
		
		try {
			conn.setDataSource("jdbc/erpiu");
			conn.begin();
			
			persistent_i = new PersistentImpl(conn.getConnection());
			persistent_u = new PersistentImpl(conn.getConnection());
			persistent_d = new PersistentImpl(conn.getConnection());
			
			
			String[] status  		= super.gets("sheet1.STATUS");
			String[] material_cd      	= super.gets("sheet1.MATERIAL_CD");
			String[] material_name    = super.gets("sheet1.MATERIAL_NAME");
			String[] is_use = super.gets("sheet1.IS_USE");
			String[] spec1    = super.gets("sheet1.SPEC1");
			String[] spec2    = super.gets("sheet1.SPEC2");
			String[] spec3    = super.gets("sheet1.SPEC3");
			String[] spec4    = super.gets("sheet1.SPEC4");
			
			boolean saveYn = false;
			
			if( status != null ) {
				for( int i = 0 ; i < status.length ; i++ ) {
					// 자재코드가 14자리이고 8번째 자리부터 10번째 자리까지가 "000"인 플랜트코드 
					// or
					// 자재코드가 13자리이고 7번째 자리부터 9번째 자리까지가 "000"인 플랜트코드 
					// or
					// 자재코드가 9,10자리인 공통 (토건) 코드
					if( (material_cd[i].trim().length() == 14 && material_cd[i].trim().substring(7, 10).equals("000")) ||(material_cd[i].trim().length() == 13 && material_cd[i].trim().substring(6, 9).equals("000")) || material_cd[i].trim().length() == 9 || material_cd[i].trim().length() == 10 ) {
						String materialCd = "";
						if( material_cd[i].trim().length() == 13 ) {
							materialCd = material_cd[i].trim().substring(0, 6)+material_cd[i].trim().substring(9, 13);
						}else {
							materialCd = material_cd[i].trim();
						}
						
						if( "I".equals(status[i]) ) {
							sqlstr_i = new StringBuffer();
							sqlstr_i.append(" INSERT  INTO WM_S_MATERIAL (		\n");
							sqlstr_i.append("         CD_COMPANY/*회사코드*/				\n");
							sqlstr_i.append("         ,CD_MAT/*자재코드*/					\n");
							sqlstr_i.append("         ,NM_MAT/*자재명*/					\n");
							sqlstr_i.append("         ,CD_SORT/*분류코드*/					\n");
							sqlstr_i.append("         ,USE_YN/*사용여부*/					\n");
							sqlstr_i.append("         ,TAG_COST/*투입여부*/				\n");
							sqlstr_i.append("         ,USE_SPEC/*개별규격사용여부*/			\n");
							sqlstr_i.append("         ,FIELD_SY1/*가변필드1*/				\n");
							sqlstr_i.append("         ,FIELD_SY2/*가변필드2*/				\n");
							sqlstr_i.append("         ,FIELD_SY3/*가변필드3*/				\n");
							sqlstr_i.append("         ,FIELD_SY4/*가변필드4*/				\n");

							sqlstr_i.append(" ) VALUES (								\n");
							sqlstr_i.append("         '"+WAFConfig.get("waf.erp.cdCompany")+"'							\n");
							sqlstr_i.append("         ,'"+materialCd+"'					\n");
							sqlstr_i.append("         ,'"+material_name[i].trim()+"'	\n");
							sqlstr_i.append("         ,'"+kind_small.trim()+"'			\n");
							sqlstr_i.append("         ,'"+is_use[i]+"'					\n");
							sqlstr_i.append("         ,'Y'								\n");
							sqlstr_i.append("         ,'N'								\n");
							sqlstr_i.append("         ,'"+spec1[i]+"'					\n");
							sqlstr_i.append("         ,'"+spec2[i]+"'					\n");
							sqlstr_i.append("         ,'"+spec3[i]+"'					\n");
							sqlstr_i.append("         ,'"+spec4[i]+"'					\n");
							sqlstr_i.append(" )											\n");
							persistent_i.setStatement(sqlstr_i.toString());
							persistent_i.execute();
							persistent_i.clear();
							saveYn = true;
							
						}else if( "U".equals(status[i]) ) {
							sqlstr_u = new StringBuffer();
							sqlstr_u.append(" UPDATE 	WM_S_MATERIAL	\n");
							sqlstr_u.append(" SET		NM_MAT = '"+material_name[i].trim()+"'	\n");
							sqlstr_u.append("         	,USE_YN = '"+is_use[i]+"'				\n");
							sqlstr_u.append("         	,FIELD_SY1 = '"+spec1[i]+"'				\n");
							sqlstr_u.append("         	,FIELD_SY2 = '"+spec2[i]+"'				\n");
							sqlstr_u.append("         	,FIELD_SY3 = '"+spec3[i]+"'				\n");
							sqlstr_u.append("         	,FIELD_SY4 = '"+spec4[i]+"'				\n");
							sqlstr_u.append(" WHERE		CD_MAT = '"+materialCd+"'				\n");
							sqlstr_u.append(" AND		CD_COMPANY = '"+WAFConfig.get("waf.erp.cdCompany")+"'						\n");
							persistent_u.setStatement(sqlstr_u.toString());
							persistent_u.execute();
							persistent_u.clear();
							saveYn = true;
							
						}else if( "D".equals(status[i]) ) {
							sqlstr_d = new StringBuffer();
							sqlstr_d.append(" DELETE	FROM WM_S_MATERIAL	\n");
							sqlstr_d.append(" WHERE		CD_MAT = '"+materialCd+"'	\n");
							sqlstr_d.append(" AND		CD_COMPANY = '"+WAFConfig.get("waf.erp.cdCompany")+"'			\n");
							persistent_d.setStatement(sqlstr_d.toString());
							persistent_d.execute();
							persistent_d.clear();
						}
						
					}
				}
			}
			
			/**
			 * 저장이 완료되었을때 ERPIU 의 분류코드 (WM_S_MATERIAL_SORT) 테이블을 조회하여 없으면 INSERT
			 */
			if(saveYn) {
				String bigCnt = getErpSortCount(conn.getConnection(), kind_big);
				String midCnt = getErpSortCount(conn.getConnection(), kind_mid);
				String smallCnt = getErpSortCount(conn.getConnection(), kind_small);
				
				if( "0".equals(bigCnt) ) {
					String kindName = getKindName(kind_big);
					sqlstr = new StringBuffer();
					sqlstr.append("INSERT	INTO WM_S_MATERIAL_SORT (	\n");
					sqlstr.append("			CD_COMPANY/*회사코드*/			\n");
					sqlstr.append("			,CD_SORT/*분류코드*/			\n");
					sqlstr.append("			,NM_SORT/*분류명*/			\n");
					sqlstr.append("			,LVL_SORT/*분류레벨*/			\n");
					sqlstr.append("			,UCD_SORT/*상위분류코드*/		\n");
					sqlstr.append("			,USE_YN/*사용여부*/			\n");
					sqlstr.append(") VALUES (							\n");
					sqlstr.append("			'"+WAFConfig.get("waf.erp.cdCompany")+"'						\n");
					sqlstr.append("			,'"+kind_big+"'				\n");
					sqlstr.append("			,'"+kindName+"'				\n");
					sqlstr.append("			,'10'						\n");
					sqlstr.append("			,NULL						\n");
					sqlstr.append("			,'Y'						\n");
					sqlstr.append(")									\n");
					persistent_i.setStatement(sqlstr.toString());
					persistent_i.execute();
					persistent_i.clear();
				}
				
				if( "0".equals(midCnt) ) {
					String kindName = getKindName(kind_mid);
					sqlstr = new StringBuffer();
					sqlstr.append("INSERT	INTO WM_S_MATERIAL_SORT (	\n");
					sqlstr.append("			CD_COMPANY/*회사코드*/			\n");
					sqlstr.append("			,CD_SORT/*분류코드*/			\n");
					sqlstr.append("			,NM_SORT/*분류명*/			\n");
					sqlstr.append("			,LVL_SORT/*분류레벨*/			\n");
					sqlstr.append("			,UCD_SORT/*상위분류코드*/		\n");
					sqlstr.append("			,USE_YN/*사용여부*/			\n");
					sqlstr.append(") VALUES (							\n");
					sqlstr.append("			'"+WAFConfig.get("waf.erp.cdCompany")+"'						\n");
					sqlstr.append("			,'"+kind_mid+"'				\n");
					sqlstr.append("			,'"+kindName+"'				\n");
					sqlstr.append("			,'20'						\n");
					sqlstr.append("			,'"+kind_big+"'				\n");
					sqlstr.append("			,'Y'						\n");
					sqlstr.append(")									\n");
					persistent_i.setStatement(sqlstr.toString());
					persistent_i.execute();
					persistent_i.clear();
				}
				
				if( "0".equals(smallCnt) ) {
					String kindName = getKindName(kind_small);
					sqlstr = new StringBuffer();
					sqlstr.append("INSERT	INTO WM_S_MATERIAL_SORT (	\n");
					sqlstr.append("			CD_COMPANY/*회사코드*/			\n");
					sqlstr.append("			,CD_SORT/*분류코드*/			\n");
					sqlstr.append("			,NM_SORT/*분류명*/			\n");
					sqlstr.append("			,LVL_SORT/*분류레벨*/			\n");
					sqlstr.append("			,UCD_SORT/*상위분류코드*/		\n");
					sqlstr.append("			,USE_YN/*사용여부*/			\n");
					sqlstr.append(") VALUES (							\n");
					sqlstr.append("			'"+WAFConfig.get("waf.erp.cdCompany")+"'						\n");
					sqlstr.append("			,'"+kind_small+"'			\n");
					sqlstr.append("			,'"+kindName+"'				\n");
					sqlstr.append("			,'30'						\n");
					sqlstr.append("			,'"+kind_mid+"'				\n");
					sqlstr.append("			,'Y'						\n");
					sqlstr.append(")									\n");
					persistent_i.setStatement(sqlstr.toString());
					persistent_i.execute();
					persistent_i.clear();
				}
				
				
			}
			
			return true;
		}
		catch(Exception e) {
			super.storage.setMessage(e.getMessage());
			conn.setRollback();
			return false;
		}
		finally {
			conn.end();
		}
	}
	
	public String getCodeName(String basecd, String code) {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();
        String rtnStr = "";
        try{
        	
        	sqlstr.append("SELECT F_GET_CODE_NAME(?,?) AS RTNVAL FROM DUAL");
        	search.setStatement(sqlstr.toString());
        	search.addParameter(basecd);
        	search.addParameter(code);
            rowSet = search.execute();
            
            if( rowSet != null ) {
            	rtnStr = rowSet.getRow(0).get("RTNVAL");
            }
            
        }catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        
        return rtnStr;
        
	}
	
	public String getKindName(String kindCode) {
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		StrBuffer sqlstr = new StrBuffer();
		String rtnStr = "";
		try{
			
			sqlstr.append("SELECT MATERIALGROUPNAME AS RTNVAL FROM PMM_MATERIALGROUP WHERE MATERIALGROUP = ?");
			search.setStatement(sqlstr.toString());
			search.addParameter(kindCode);
			rowSet = search.execute();
			
			if( rowSet != null ) {
				rtnStr = rowSet.getRow(0).get("RTNVAL");
			}
			
		}catch (WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);  
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
		}
		
		return rtnStr;
		
	}
	
	public String getErpSortCount(Connection connection,String cdSort) {
		
		Persistent persistent = null;
		RowSet rowSet = new RowSetImpl();
		StrBuffer sqlstr = new StrBuffer();
		String rtnStr = "";
		try{
			persistent = new PersistentImpl(connection);
			sqlstr.append("SELECT	COUNT(*) AS CNT				\n");
			sqlstr.append("FROM		WM_S_MATERIAL_SORT	\n");
			sqlstr.append("WHERE	CD_SORT = ?			\n");
			
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(cdSort);
			rowSet = persistent.query();
			
			
			if( rowSet != null ) {
				rtnStr = rowSet.getRow(0).get("CNT");
			}
			
		}catch (WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);  
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
		}
		
		return rtnStr;
		
	}
	 
	
}
