/**
 *
 * @(#)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.WAFConnection;
import kr.co.hsnc.common.sql.WAFConnectionImpl;
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 com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class CommMatlCodeSectListSaveBiz 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 CommMatlCodeSectListSaveBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		boolean success = saveData(params);
		boolean successErp = saveDataErp(params);
		
		//boolean success2 = saveData2(params);
		
		
		 super.storage.setSuccess(success);
         storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
	}
	
	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====>????");
		
		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"		//자재명
				        + "    , UNIT_KIND                  														\n"		//단위
				        + "    , LEAD_TIME                														\n"		//lead time
				        + "    , IS_USE	             																\n"		//사용여부
				        + "    , STOCK_FLAG             														\n"		//입고계획여부
				        + "    , STOCK_PLAN_FLAG	            												\n"		//중점관리여부
//				        + "    , MATERIAL_GROUP            													\n"		//메뉴코드
				        + "    , CRE_BY             																\n"		//등록자
				        + "    , CRE_DATE          																\n"		//등록일자
				        + "    , MATERIAL_SPEC    																\n"		//규격
				        + "    , PRICE_FLG		    																\n"		//가입고여부
				        + "    , PAY_DIV                                                                          \n"     //납품구분
				        + " ) VALUES                  																\n"
				        + "  ( 																						\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
//				        + "    ?,                     																	\n"
				        + "    ?,                     																	\n"
				        + "    sysdate,		          																\n" 
			  			+ "    ?,		         																		\n"
			  			+ "    ?,                                                                                       \n" 
			  			+ "    ?		 )         																		\n" ;
				        //+ "    TO_CHAR(sysdate, 'YYYYMMDDHH24MISS') )              \n" ; 
			  
			  sqlstr_u = " UPDATE " + SAVE_TABLE + "       				\n"
		              + " SET    MATERIAL_CD    = ?        					\n"
		              + "        , MATERIAL_NAME = ?        					\n"
		              + "        , UNIT_KIND   = ?        						\n"
		              + "        , LEAD_TIME = ?        							\n"
		              + "        , IS_USE        = ?        						\n"
		              + "        , STOCK_FLAG        = ?        				\n"
		              + "        , STOCK_PLAN_FLAG        = ?        		\n"
		              + "        , UPD_BY      = ?        						\n"
		              + "        , UPD_DATE    = sysdate   					\n"
		              + "        , MATERIAL_SPEC    = ?	  					\n"
		              + "        , PRICE_FLG    = ?	  							\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_id      	= super.gets("sheet1.MATERIAL_ID");
			String[] material_cd      	= super.gets("sheet1.MATERIAL_CD");
			String[] material_name    = super.gets("sheet1.MATERIAL_NAME");
			String[] unit_kind    	= super.gets("sheet1.UNIT_KIND");
			String[] lead_time     		= super.gets("sheet1.LEAD_TIME");
			String[] is_use     	= super.gets("sheet1.IS_USE");
			String[] stock_flag     	= super.gets("sheet1.STOCK_FLAG");
			String[] stock_plan_flag     	= super.gets("sheet1.STOCK_PLAN_FLAG");
			String[] material_spec = super.gets("sheet1.MATERIAL_SPEC");
			String[] price_flg = super.gets("sheet1.PRICE_FLG");
			String[] pay_div = super.gets("sheet1.PAY_DIV");
//			String[] material_group     	= super.gets("MATERIALGROUP");
//			String[] code_id     	= super.gets("CODE_ID");
			
			
			persistent_i.setStatement(sqlstr_i);
			persistent_u.setStatement(sqlstr_u);
			persistent_d.setStatement(sqlstr_d);
			
			System.out.println("status=======>" + material_name[0]);
			
			if( status != null ) {
				for( int i = 0 ; i < status.length ; i++ ) {
					if( "I".equals(status[i]) ) {						
//						persistent_i.addParameter(kind_small.trim());
						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(unit_kind[i].trim());
						persistent_i.addParameter(lead_time[i].trim());
						persistent_i.addParameter(is_use[i].trim());
						persistent_i.addParameter(stock_flag[i].trim());
						persistent_i.addParameter(stock_plan_flag[i].trim());
//						persistent_i.addParameter(params.get("sect_id"));
						persistent_i.addParameter(storage.getUser().get("ACCT_ID"));
						persistent_i.addParameter(material_spec[i]);	
						persistent_i.addParameter(price_flg[i]);	
						persistent_i.addParameter(pay_div[i]);  
						
						
						
						persistent_i.execute();
						persistent_i.clearParameters();
					}
					
					else if( "U".equals(status[i]) ) {
						
						System.out.println("stock_plan_flag====>" + stock_plan_flag[i]);
						
						
						persistent_u.addParameter(material_cd[i].trim());
						persistent_u.addParameter(material_name[i].trim());
						persistent_u.addParameter(unit_kind[i].trim());
						persistent_u.addParameter(lead_time[i].trim());
						persistent_u.addParameter(is_use[i].trim());
						persistent_u.addParameter(stock_flag[i].trim());
						persistent_u.addParameter(stock_plan_flag[i].trim());
						persistent_u.addParameter(storage.getUser().get("ACCT_ID"));	
						persistent_u.addParameter(material_spec[i]);	
						persistent_u.addParameter(price_flg[i]);	
						persistent_u.addParameter(pay_div[i]);
						persistent_u.addParameter(material_cd[i].trim());
						
						
						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) {

            System.out.println(this.getClass().getName() + " --> saveData() : " + e.getMessage());
            System.out.println("SQL : " + persistent_i.getStatement());
            System.out.println(e);
            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(super.conn.getConnection());
			persistent_u = new PersistentImpl(super.conn.getConnection());
			persistent_d = new PersistentImpl(super.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[] unit_kind    	= super.gets("sheet1.UNIT_KIND");
			String[] material_spec = super.gets("sheet1.MATERIAL_SPEC");
			String[] is_use     	= super.gets("sheet1.IS_USE");
			
			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 ) {
					// 공통자재코드 (9,10 자리만 등록)
					if(material_cd[i].trim().length() == 9 || material_cd[i].trim().length() == 10 ) {					// 공통자재코드 저장시 9,10 자리만 등록하도록 수정(13,14자리는 공통자재코드로 등록되지 않기에 로직 제외함) - 23년 6월 23일
						String materialCd = "";
						String unitMat = "";
						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();
						}
						
						unitMat = getCodeName("UNIT_KIND_GB",unit_kind[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("         ,SIZE_MAT/*자재규격*/				\n");
							sqlstr_i.append("         ,UNIT_MAT/*자재단위*/				\n");
							sqlstr_i.append("         ,USE_YN/*사용여부*/					\n");
							sqlstr_i.append("         ,TAG_COST/*투입여부*/				\n");
							sqlstr_i.append("         ,USE_SPEC/*개별규격사용여부*/			\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("         ,'"+material_spec[i]+"'			\n");
							sqlstr_i.append("         ,'"+unitMat+"'					\n");
							sqlstr_i.append("         ,'"+is_use[i]+"'					\n");
							sqlstr_i.append("         ,'Y'								\n");
							sqlstr_i.append("         ,'N'								\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("         	,SIZE_MAT = '"+material_spec[i]+"'		\n");
							sqlstr_u.append("         	,UNIT_MAT = '"+unitMat+"'				\n");
							sqlstr_u.append("         	,USE_YN = '"+is_use[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(kind_big);
				String midCnt = getErpSortCount(kind_mid);
				String smallCnt = getErpSortCount(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) {
			
			System.out.println(this.getClass().getName() + " --> saveDataErp() : " + e.getMessage());
			System.out.println("SQL : " + persistent_i.getStatement());
			System.out.println(e);
			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(String cdSort) {
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		StrBuffer sqlstr = new StrBuffer();
		String rtnStr = "";
		try{
			
			sqlstr.append("SELECT	COUNT(*) AS CNT				\n");
			sqlstr.append("FROM		WM_S_MATERIAL_SORT@ERPIU	\n");
			sqlstr.append("WHERE	CD_COMPANY = '"+WAFConfig.get("waf.erp.cdCompany")+"'			\n");
			sqlstr.append("AND		CD_SORT = '"+cdSort+"'			\n");
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
			
			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;
		
	}
}
