/**
 * @(#)file    SSRListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.08.12
 * @(#)Description: SSR List
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.buyer.process.process.biz;

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

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

import oracle.toplink.internal.ox.StrBuffer;

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.util.ValueObject;

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

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

    public void doBiz() throws Exception {
    	boolean success =  false;
    	
    	if ( storage.compareEvent("") ){
	  
		}else if(storage.compareEvent("DEPTPOP")){
			params.set("sheet1", getDeptList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
		else if(storage.compareEvent("DEPT_SAVE")){
			success=getDeptSave(params); 
			super.storage.setSuccess(success);
		}
    	
    }
    
    private RowSet getDeptList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{                                                                                                                              
        	sqlstr.append("SELECT A.DEPT_CD dept_cd                                     \n");
        	sqlstr.append("      ,B.DEPT_NM dept_nm                                     \n");
        	sqlstr.append("      ,PUR_ID                                                \n");
        	sqlstr.append("      ,DOC_ID                                                \n");
        	sqlstr.append("      ,LOG_ID                                                \n");
        	sqlstr.append("      ,MR_ISSUE                                              \n");
        	sqlstr.append("      ,MR_APPROVAL                                           \n");
        	sqlstr.append("      ,QUO                                                   \n");
        	sqlstr.append("      ,TBE_ISSUE                                             \n");
        	sqlstr.append("      ,TBE_APPROVAL                                          \n");
        	sqlstr.append("      ,PIM                                                   \n");
        	sqlstr.append("      ,FAB_TIME                                              \n");
        	sqlstr.append("      ,C.USER_NAME PUR_NAME                                  \n");
        	sqlstr.append("      ,D.USER_NAME DOC_NAME                                  \n");
        	sqlstr.append("      ,E.USER_NAME LOG_NAME                                  \n");
        	sqlstr.append("  FROM PSR_DEFINE2018 A                                      \n"); 
        	sqlstr.append("      ,ACZ10100V B                                           \n"); 
        	sqlstr.append("      ,PUB0120T C                                            \n"); 
        	sqlstr.append("      ,PUB0120T D                                            \n"); 
        	sqlstr.append("      ,PUB0120T E                                            \n"); 
        	sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD                                 \n"); 
        	sqlstr.append("   AND A.PUR_ID = C.ACCT_ID(+)                               \n"); 
        	sqlstr.append("   AND A.DOC_ID = D.ACCT_ID(+)                               \n"); 
        	sqlstr.append("   AND A.LOG_ID = E.ACCT_ID(+)                               \n"); 
        	sqlstr.append("   AND B.DEPT_CD = '"+storage.getUser().get("dept_cd") + "'  \n");
            search.setStatement(sqlstr.toString());
      
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            WAFLogger.debug(sqlstr.toString());
            WAFLogger.debug(se.getMessage());
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}		
    private boolean getDeptSave(ValueObject param) {
		 Persistent persistent = null;
		 String sqlstr_i = "";
		 String sqlstr_u = "";
		 String sqlstr_d = "";
       
		 try{       
           conn.begin();
           persistent = new PersistentImpl(super.conn.getConnection());
           sqlstr_i += "insert into PSR_DEFINE2018                                             \n"
       	        + "                (DEPT_CD, PUR_ID,DOC_ID, LOG_ID                             \n"
       	        + "                ,MR_ISSUE,MR_APPROVAL,QUO,TBE_ISSUE,TBE_APPROVAL,PIM        \n"
       	        + "                ,FAB_TIME, CRE_BY, CRE_DATE )                               \n"
    	        + "    values (?, ?, ? ,?                                                      \n"
    	        + "           ,? ,? ,? ,? ,? ,?                                                \n"
    	        + "           ,?  ,? ,SYSDATE )                                                \n";
    	       	   
       	      
           sqlstr_u += "UPDATE PSR_DEFINE2018                            \n"
      	        + "   SET PUR_ID = ?                                     \n"
      	        + "      ,DOC_ID = ?                                     \n"
      	        + "      ,LOG_ID = ?                                     \n"
      	        + "      ,MR_ISSUE = ?                                   \n"
      	        + "      ,MR_APPROVAL = ?                                \n"
      	        + "      ,QUO = ?                                        \n"
      	        + "      ,TBE_ISSUE = ?                                  \n"
      	        + "      ,TBE_APPROVAL = ?                               \n"
      	        + "      ,PIM = ?                                        \n"
      	        + "      ,FAB_TIME = ?                                   \n"
                + "      ,UPD_BY = ?                                     \n"
      	        + "      ,UPD_DATE = SYSDATE                             \n"
      	        + " WHERE DEPT_CD =  ?                                   \n";
           
           sqlstr_d += "DELETE FROM PSR_DEFINE2018                       \n"
     	            + " WHERE DEPT_CD = ?                                \n";
           
       	String[] status  		= super.gets("sheet1.STATUS");
   		String   dept_cd  		= storage.getUser().get("dept_cd");
   		String[] chg_code01  	= super.gets("sheet1.PUR_ID");
   		String[] chg_code02  	= super.gets("sheet1.DOC_ID");
   		String[] chg_code03  	= super.gets("sheet1.LOG_ID");
   		String[] chg_code04  	= super.gets("sheet1.MR_ISSUE");
   		String[] chg_code05  	= super.gets("sheet1.MR_APPROVAL");
   		String[] chg_code06  	= super.gets("sheet1.QUO");
   		String[] chg_code07  	= super.gets("sheet1.TBE_ISSUE");
   		String[] chg_code08  	= super.gets("sheet1.TBE_APPROVAL");
   		String[] chg_code09  	= super.gets("sheet1.PIM");
   		String[] chg_code10  	= super.gets("sheet1.FAB_TIME");    		
   		
       	
   		if( status != null ) {
   			for( int i = 0 ; i < status.length ; i++ ) {
   				if( "I".equals(status[i]) ) {
   					persistent.setStatement(sqlstr_i);
   					persistent.addParameter(dept_cd.trim());
   					persistent.addParameter(chg_code01[i].trim());
   					persistent.addParameter(chg_code02[i].trim());
   					persistent.addParameter(chg_code03[i].trim());
   					persistent.addParameter(chg_code04[i].trim());
   					persistent.addParameter(chg_code05[i].trim());
   					persistent.addParameter(chg_code06[i].trim());
   					persistent.addParameter(chg_code07[i].trim());
   					persistent.addParameter(chg_code08[i].trim());
   					persistent.addParameter(chg_code09[i].trim());
   					persistent.addParameter(chg_code10[i].trim());
   					persistent.addParameter(storage.getUser().get("ACCT_ID")); 
   					
   					persistent.execute();
                    persistent.clearParameters();
   				}
   				if( "U".equals(status[i]) ) {
   					persistent.setStatement(sqlstr_u);
   					persistent.addParameter(chg_code01[i].trim());
   					persistent.addParameter(chg_code02[i].trim());
   					persistent.addParameter(chg_code03[i].trim());
   					persistent.addParameter(chg_code04[i].trim());
   					persistent.addParameter(chg_code05[i].trim());
   					persistent.addParameter(chg_code06[i].trim());
   					persistent.addParameter(chg_code07[i].trim());
   					persistent.addParameter(chg_code08[i].trim());
   					persistent.addParameter(chg_code09[i].trim());
   					persistent.addParameter(chg_code10[i].trim());
   					persistent.addParameter(storage.getUser().get("ACCT_ID"));
   					persistent.addParameter(dept_cd.trim());
   					 
   					persistent.execute();
                    persistent.clearParameters();
   				}
   				if( "D".equals(status[i]) ) {
   					persistent.setStatement(sqlstr_d);
   					persistent.addParameter(dept_cd.trim());
   					 
   					persistent.execute();
                    persistent.clearParameters();
   				}
   			}
   		}	
   		return true;
       }
       catch (Exception e) {
       	System.out.println(this.getClass().getName() + " --> saveData() : " + e.getMessage());
           System.out.println("SQL : " + persistent.getStatement());
           System.out.println(e);
           return false;
       }
       finally {    
       	super.conn.end();
       }
	}	 
    
 }

