/**
 * @(#)file    PSRSaveBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.09.05
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.process.process.biz;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

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;
import oracle.toplink.internal.ox.StrBuffer;

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

   
public class PSRSaveBiz extends Biz {
	public static int psr_id = 0;
	
	public PSRSaveBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		boolean success = false;
		
		if(storage.compareEvent("SAVE_PSR")){
			String[] status = super.gets("SHEET2.status");
			
			try {
				super.conn.begin();

				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusInsert(status[i])) {
						psrUpdate2(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
					}
				}
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	            super.storage.setSuccess(false);
			} finally {
				super.conn.close();
			}
		} else if(storage.compareEvent("UPDATE_PSR")){
			String[] status = super.gets("SHEET2.status");
			
			try {
				super.conn.begin();
				
				updatePSRMaster(params);
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = updatePSR(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
					}
				}
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	            super.storage.setSuccess(false);
			} finally {
				super.conn.close();
			}
		} else if ( storage.compareEvent("DELETE_PSR") ) {
			String[] status = super.gets("SHEET1.status");
			
			try {
				super.conn.begin();
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						success = deletePSR(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
				}
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
				super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	            super.storage.setSuccess(false);
			} finally {
				super.conn.close();
			}
		} else if(storage.compareEvent("SAVE_TEMP_EXCEL_TABLE")){
			String[] status = super.gets("SHEET3.status");
			
			try {
				super.conn.begin();
				Connection connection = super.conn.getConnection();
				
				ValueObject vo = getTempExcelTableSeq(connection);
				params.set("temp_excel_table_seq", vo.get("temp_excel_table_seq"));
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusInsert(status[i])) {
						success = savePSRExcelUpload(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET3", params, i));
					}
				}

				Map<String, String> tempMap = new HashMap<String, String>();
				tempMap.put("ETC-DATA", params.get("temp_excel_table_seq"));
				params.set("sheet3.modular.web.ibsheet7.etcdata", tempMap);
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	            super.storage.setSuccess(false);
			} finally {
				super.conn.close();
			}
		} else if ( storage.compareEvent("SAVE_PSR_EXCEL") ) {
			String[] status = super.gets("SHEET1.status2");
			
			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					psrUpdate(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
				super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	            super.storage.setSuccess(false);
			} finally {
				super.conn.close();
			}
		} else if ( storage.compareEvent("RECOUNT") ) {
			try {
				super.conn.begin();
				psrRecount(super.conn.getConnection(), params);
				super.storage.setSuccess(true);
				
			} catch (Exception e) {
				WAFLogger.error(e);
				super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	            super.storage.setSuccess(false);
			} finally {
				super.conn.end();
			}
		}
	}
	
	public boolean updatePSR(Connection connection, ValueObject sqlParams) throws Exception {
	   PreparedStatement pstmt = null;
	   StringBuffer sql   = new StringBuffer();
	   
	   try {
		   sql.append("	UPDATE PSR SET          			\n");
		   sql.append("		PLAN_DT = TO_DATE(?)			\n");
		   sql.append("		, FORECASTING_DT = TO_DATE(?)	\n");
		   sql.append("		, ACTUAL_DT = TO_DATE(?)		\n");
		   sql.append("		, UPD_BY = ?          		\n");
		   sql.append("		, UPD_DATE = SYSDATE  		\n");
		   sql.append("	WHERE PSR_ID = ?        		\n");
		   sql.append("	AND PSR_ACTIVITY_CD = ?        		\n");
		   
	        pstmt = connection.prepareStatement(sql.toString());
	        int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("plan_dt"));
			pstmt.setString(paramIdx++, sqlParams.get("forecasting_dt"));
			pstmt.setString(paramIdx++, sqlParams.get("actual_dt"));
			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.setString(paramIdx++, params.get("psr_id"));
			pstmt.setString(paramIdx++, sqlParams.get("psr_activity_cd"));
			pstmt.executeUpdate();
			
			return true;
        }
        catch(Exception e) {
        	WAFLogger.error(e);
        	storage.setDetailMessage(e);
        	this.storage.setMessage(e.getMessage());
			throw e;
        }
        finally {
        	try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
        }
    }
	
	public boolean updatePSRMaster(ValueObject params) throws Exception {
		Persistent persistent = null;
	   StringBuffer sql   = new StringBuffer();
	   
	   try {
		   sql.append("	UPDATE PSR_MASTER SET   		\n");
		   sql.append("		MR_NO = ?           		\n");
		   sql.append("		, REMARKS = ?           		\n");
		   sql.append("		, DISCIPLINE = ?           		\n");
		   sql.append("		, UPD_BY = ?          		\n");
		   sql.append("		, UPD_DATE = SYSDATE  		\n");
		   sql.append("	WHERE PSR_ID = ?        		\n");

		   persistent = new PersistentImpl(super.conn.getConnection());
           persistent.setStatement(sql.toString());
           
           persistent.addParameter(params.get("p_mr_no2"));
           persistent.addParameter(params.get("remarks2"));
           persistent.addParameter(params.get("p_discipline2"));
           persistent.addParameter(storage.getUser().get("ACCT_ID"));
           persistent.addParameter(params.get("psr_id"));

           WAFLogger.debug(sql.toString());
           persistent.execute();
           persistent.clearParameters();
             
           return true;
        }
        catch(Exception e) {
        	WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
			throw e;
        } 
    }
	
	public int getPsrId(ValueObject params) throws Exception {
	   Search search 	  = new SearchImpl();
       RowSet rowSet 	  = new RowSetImpl();
       StringBuffer sql  = new StringBuffer();
       int returnValue = 0;
       
       try {
    	   sql.append("	SELECT PSR_MASTER_S.NEXTVAL PSR_ID FROM DUAL \n");
       	
		   WAFLogger.debug(sql.toString());
           search.setStatement(sql.toString());
           rowSet = search.execute();
           if(rowSet.size()>0){
        	   returnValue = rowSet.getRow(0).getInt("PSR_ID");
           }
           return returnValue;
       } catch (WAFSQLException se) {
       	WAFLogger.error(this.getClass().getName() + " --> getPsrId() : \n" + se.getMessage());
           WAFLogger.error("SQL : " + search.getStatement());
           WAFLogger.error(se);
           storage.setDetailMessage(se);
       }
       finally {    
           return returnValue;
       }
    }
		
	public boolean savePSRExcelUpload(Connection connection, ValueObject sqlParams) throws Exception {
	   PreparedStatement pstmt = null;
	   StringBuffer sql   = new StringBuffer();
	   
	   try {
		   sql.append("	INSERT INTO TEMP_EXCEL_TABLE (              			    \n");
			  sql.append("		COL1, COL2, COL3, COL4, COL5, COL6, COL7  			\n");
			  sql.append("		, COL8, COL9, COL10, COL11, COL12, COL13  			\n");
			  sql.append("		, COL14, COL15, COL16, COL17, COL18, COL19			\n");
			  sql.append("		, COL20, COL21, COL22, COL23, COL24, COL25			\n");
			  sql.append("		, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33 )            			\n");
			  sql.append("		VALUES (                                  			\n");
			  sql.append("		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,    			\n");
			  sql.append("		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,      			\n");
			  sql.append("		?,                                	   		        \n");
			  sql.append("		( SELECT NVL(DETAILCD, '100')                       \n");
			  sql.append("		    FROM EBID_COM911T                               \n");
			  sql.append("		   WHERE BASECD = 'BAS_LVL02' AND DETAILNM = ?),    \n");
			  sql.append("		?, ?, ?, ?, ?, ?                               	    \n");
			  sql.append("		)                                       			\n");
			  
	        pstmt = connection.prepareStatement(sql.toString());
	        int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("discipline_nm"));
			pstmt.setString(paramIdx++, sqlParams.get("mr_no"));
			pstmt.setString(paramIdx++, sqlParams.get("ord_doc_no"));
			pstmt.setString(paramIdx++, sqlParams.get("mr_desc"));
			pstmt.setString(paramIdx++, sqlParams.get("corp_name"));
			pstmt.setString(paramIdx++, sqlParams.get("ir_rcv_h"));
			pstmt.setString(paramIdx++, sqlParams.get("inq_iss_h"));
			pstmt.setString(paramIdx++, sqlParams.get("bid_due_h"));
			pstmt.setString(paramIdx++, sqlParams.get("quot_rcv_h"));
			pstmt.setString(paramIdx++, sqlParams.get("tbe_rcv_h"));
			pstmt.setString(paramIdx++, sqlParams.get("cbe_com_h"));
			pstmt.setString(paramIdx++, sqlParams.get("pr_rcv_h"));
			pstmt.setString(paramIdx++, sqlParams.get("po_iss_h"));
			pstmt.setString(paramIdx++, sqlParams.get("po_del_h"));
			pstmt.setString(paramIdx++, sqlParams.get("final_inspection_h"));
			pstmt.setString(paramIdx++, sqlParams.get("ship_h"));
			pstmt.setString(paramIdx++, sqlParams.get("pod_h"));
			pstmt.setString(paramIdx++, sqlParams.get("pol_h"));
			pstmt.setString(paramIdx++, sqlParams.get("onsite_h"));
			pstmt.setString(paramIdx++, sqlParams.get("vp_in_h"));
			pstmt.setString(paramIdx++, sqlParams.get("vp_out_h"));
			pstmt.setString(paramIdx++, sqlParams.get("sub_order_h"));
			pstmt.setString(paramIdx++, sqlParams.get("receive/material_h"));
			pstmt.setString(paramIdx++, sqlParams.get("fabrication_h"));
			pstmt.setString(paramIdx++, sqlParams.get("delivery_h"));
			pstmt.setString(paramIdx++, sqlParams.get("remarks"));
			pstmt.setString(paramIdx++, sqlParams.get("discipline_nm"));//2017.07.21 수정필요. 사용자가 코드값 모름.text mapping으로 수정 discipline->discipline_nm 
			pstmt.setString(paramIdx++, sqlParams.get("psr_id"));
			pstmt.setString(paramIdx++, sqlParams.get("corp_id"));
			pstmt.setString(paramIdx++, sqlParams.get("demanded_id"));
			pstmt.setString(paramIdx++, sqlParams.get("pur_cont_id"));
			pstmt.setString(paramIdx++, params.get("dept_cd"));
			//pstmt.setString(paramIdx++, sqlParams.get("dept_cd"));//2017.07.21 params.get("dept_cd")으로 변경
			pstmt.setString(paramIdx++, params.get("temp_excel_table_seq"));
			pstmt.executeUpdate();
			
			return true;
	    }
	    catch(Exception e) {
	    	WAFLogger.error(e);
	    	storage.setDetailMessage(e);
        	this.storage.setMessage(e.getMessage());
			throw e;
	    }
	    finally {
	    	try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
	    }
	}
	
	public boolean psrUpdate(Connection conn, ValueObject sqlParams) throws Exception {
		CallableStatement cs 	= null;
		ValueObject rtnVo = new ValueObject();
		String pssql			= "";
		
		try {
        	pssql = "{call SP_REG_PSR_STATUS(?,?,?,?,?,?,?,?,?,?,?,?)}";
        	/*
        	 *    
        	 *       VI_DEPT_CD       IN VARCHAR2,         --PROJECT_CD
   VI_ACTIVITY_CD   IN VARCHAR2,         --PSR_ACTIVITY_CD
   VI_ACTIVITY_KIND IN VARCHAR2,         --1=PLAN, 2=FORECASTING,3=ACTUAL
   VI_MR_NO         IN VARCHAR2,         --MR_NO
   VI_DEMANDED_ID   IN NUMBER,           --DEMANDED_ID
   VI_PUR_CONT_ID   IN NUMBER,           --PUR_CONT_ID
   VI_DISCIPLINE IN VARCHAR2,            -- DISCIPLINE
   VI_IN_USER_CD    IN NUMBER,           --작성자 사번  
   VI_ACTIVITY_DATE IN VARCHAR2,         --일자
   VI_REMARKS       IN VARCHAR2,         --일자
   VO_SUCCESS_CD    OUT VARCHAR2,        --성공여부 S:성공 , N:실폐
   VO_MESSAGE       OUT VARCHAR2
        	 */
        	
        	cs = conn.prepareCall(pssql);
        	
        	PSRBiz biz = new PSRBiz(storage);
 		   RowSet rowSet = biz.getNewActivityCode();
 		   
        	for(int i=0; i<rowSet.size(); i++) {
        		cs.setString(1, sqlParams.get("dept_cd"));
            	cs.setString(2, rowSet.getRow(i).get("psr_activity_cd"));
            	cs.setString(3, "1");
            	cs.setString(4, sqlParams.get("mr_no"));
//            	cs.setInt(5, 0);
//            	cs.setInt(6, 0);
            	cs.setInt(5, sqlParams.getInt("demanded_id"));
            	cs.setInt(6, sqlParams.getInt("pur_cont_id"));

            	cs.setString(7, sqlParams.get("discipline"));
            	cs.setInt(8, storage.getUser().getInt("ACCT_ID"));
            	cs.setString(9, sqlParams.get(rowSet.getRow(i).get("psr_activity_save_name")+"_H"));
            	cs.setString(10, sqlParams.get("remarks"));
            	
            	cs.registerOutParameter(11, java.sql.Types.VARCHAR);
                cs.registerOutParameter(12, java.sql.Types.VARCHAR);
            	
                cs.execute();
                
/*
                
                WAFLogger.debug("~~~~~~~~~~~~~~~~~~~~~~~~");
            	WAFLogger.debug("1 ==> " + sqlParams.get("dept_cd"));
            	WAFLogger.debug("2 ==> " + rowSet.getRow(i).get("psr_activity_cd"));
            	WAFLogger.debug("3 ==> " + "1");
            	WAFLogger.debug("4 ==> " + sqlParams.get("mr_no"));
//            	WAFLogger.debug("5 ==> " + 0);
//            	WAFLogger.debug("6 ==> " + 0);
            	WAFLogger.debug("5 ==> " + sqlParams.getInt("demanded_id"));
            	WAFLogger.debug("6 ==> " + sqlParams.getInt("pur_cont_id"));
            	WAFLogger.debug("7 ==> " + sqlParams.get("discipline"));
            	WAFLogger.debug("8 ==> " + storage.getUser().getInt("ACCT_ID"));
            	WAFLogger.debug("9 ==> " + sqlParams.get(rowSet.getRow(i).get("psr_activity_save_name")+"_H"));
            	WAFLogger.debug("10 ==> " + sqlParams.get("remarks"));
            	WAFLogger.debug("11 ==> " + cs.getString(11));
            	WAFLogger.debug("12 ==> " + cs.getString(12));
            	WAFLogger.debug("~~~~~~~~~~~~~~~~~~~~~~~~");

            	*/
            	
            	
//            	rtnVo.set("VO_SUCCESS_CD",cs.getString(11));
//                rtnVo.set("VO_MESSAGE",cs.getString(12));
        	}
            return true;
        }
        catch(Exception e) {
        	storage.setDetailMessage(e);
        	this.storage.setMessage(e.getMessage());
        	throw e;
        }
	}
	
	public boolean psrUpdate2(Connection conn, ValueObject sqlParams) throws Exception {
		CallableStatement cs 	= null;
		ValueObject rtnVo = new ValueObject();
		String pssql			= "";
		
		try {
        	pssql = "{call SP_REG_PSR_STATUS(?,?,?,?,?,?,?,?,?,?,?,?)}";
        	/*
        	 *    VI_DEPT_CD       IN VARCHAR2,         --PROJECT_CD
				   VI_ACTIVITY_CD   IN VARCHAR2,         --PSR_ACTIVITY_CD
				   VI_ACTIVITY_KIND IN VARCHAR2,         --1=PLAN, 2=FORECASTING,3=ACTUAL
				   VI_MR_NO         IN VARCHAR2,         --MR_NO
				   VI_DEMANDED_ID   IN NUMBER,           --DEMANDED_ID
				   VI_PUR_CONT_ID   IN NUMBER,           --PUR_CONT_ID
				   VI_DISCIPLINE   IN NUMBER,
				   VI_IN_USER_CD    IN NUMBER,           --�묒꽦���щ쾲  
				   VI_ACTIVITY_DATE IN VARCHAR2,         --�쇱옄
				   VI_REMARKS    IN VARCHAR2,
				   VO_SUCCESS_CD    OUT VARCHAR2,        --�깃났�щ� S:�깃났 , N:�ㅽ룓
   					VO_MESSAGE       OUT VARCHAR2
        	 */
        	
        	cs = conn.prepareCall(pssql);
        	
        	for(int i=1; i<4; i++) {
        		cs.setString(1, params.get("dept_cd"));
            	cs.setString(2, sqlParams.get("psr_activity_cd"));
            	cs.setString(3, i+"");
            	cs.setString(4, params.get("p_mr_no2"));
//            	cs.setInt(5, 0);
//            	cs.setInt(6, 0);
            	cs.setObject(5, null);
            	cs.setObject(6, null);
//            	cs.setNull(6, java.sql.Types.INTEGER);
            	cs.setString(7, params.get("p_discipline2"));
            	cs.setInt(8, storage.getUser().getInt("ACCT_ID"));
            	if(i == 1) {
            		cs.setString(9, sqlParams.get("plan_dt"));
            		
            	}else if (i == 2) {
            		cs.setString(9, sqlParams.get("forecasting_dt"));
            		
            	} else if (i == 3) {
            		cs.setString(9, sqlParams.get("actual_dt"));
            		
            	}
            	
            	cs.setString(10, params.get("remarks2"));
            	
            	cs.registerOutParameter(11, java.sql.Types.VARCHAR);
                cs.registerOutParameter(12, java.sql.Types.VARCHAR);
            
            	
            	cs.execute();
            	
//            	rtnVo.set("VO_SUCCESS_CD",cs.getString(11));
//                rtnVo.set("VO_MESSAGE",cs.getString(12));

               /*
                WAFLogger.debug("~~~~~~~~~~~~~~~~~~~~~~~~");
            	WAFLogger.debug("1 ==> " + params.get("dept_cd"));
            	WAFLogger.debug("2 ==> " + sqlParams.get("psr_activity_cd"));
            	WAFLogger.debug("3 ==> " + i+"");
            	WAFLogger.debug("4 ==> " + params.get("p_mr_no2"));
            	WAFLogger.debug("5 ==> " + 0);
            	WAFLogger.debug("6 ==> " + 0);
            	WAFLogger.debug("7 ==> " + params.get("p_discipline2"));
            	WAFLogger.debug("8 ==> " + storage.getUser().getInt("ACCT_ID"));
            	WAFLogger.debug("9 plan ==> " + sqlParams.get("plan_dt"));
            	WAFLogger.debug("9 fore ==> " + sqlParams.get("forecasting_dt"));
            	WAFLogger.debug("9 actu ==> " + sqlParams.get("actual_dt"));
            	WAFLogger.debug("10 ==> " + params.get("remarks2"));
            	WAFLogger.debug("11 ==> " + cs.getString(11));
            	WAFLogger.debug("12 ==> " + cs.getString(12));
            	WAFLogger.debug("~~~~~~~~~~~~~~~~~~~~~~~~");
        	*/
        	}
            return true;
        }
        catch(Exception e) {
        	storage.setDetailMessage(e);
        	this.storage.setMessage(e.getMessage());
        	throw e;
        }
	}
	
	
	public boolean deletePSR(Connection connection, ValueObject sqlParams) throws Exception {
		Persistent persistent = null;
	   StringBuffer sql   = new StringBuffer();
	   StringBuffer sql2   = new StringBuffer();
	   
	   try {
		   RowSet returnRowSet = getActualDtCnt(sqlParams);
		   if( returnRowSet.getRow(0).getInt("CNT") > 0 ) {
			   String message = "완료일 데이터가 있으면 삭제할 수 없습니다.";
               Exception e = new Exception(message);
               
               storage.setMessage(StringUtils.replace(message, "\"", ""));
               throw e;
		   }
		   
		   sql.append("	DELETE FROM PSR WHERE PSR_ID = ?	\n");

		   persistent = new PersistentImpl(connection);
           persistent.setStatement(sql.toString());
           
           persistent.addParameter(sqlParams.get("psr_id"));
           WAFLogger.debug(sql.toString());
           persistent.execute();
           persistent.clearParameters();
           
           sql2.append("	DELETE FROM PSR_MASTER WHERE PSR_ID = ?	\n");

           persistent.setStatement(sql2.toString());
           
           persistent.addParameter(sqlParams.get("psr_id"));
           WAFLogger.debug(sql2.toString());
           persistent.execute();
           persistent.clearParameters();
             
           return true;
        }
        catch(Exception e) {
        	storage.setDetailMessage(e);
        	this.storage.setMessage(e.getMessage());
			throw e;
        } 
    }
	
	
	private ValueObject getTempExcelTableSeq(Connection conn) throws Exception {
		
		Persistent persistent_q = null;
		
		ValueObject vo = new ValueObject();
		ValueObject row = null;
		String sqlstr = "";
		
		try{
			persistent_q = new PersistentImpl(conn);
			sqlstr = "SELECT TEMP_EXCEL_TABLE_S.NEXTVAL TEMP_EXCEL_TABLE_SEQ FROM DUAL";
			persistent_q.setStatement(sqlstr); 
			row = RowSetUtility.getValueObject(persistent_q.query().getRow(0));
			vo.set("temp_excel_table_seq", row.get("temp_excel_table_seq"));
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getTempExcelTableSeq() : " + e.getMessage());
			WAFLogger.error(e);
			throw e;
            
		}finally{
			if(vo == null) vo = new ValueObject();
		}
		
		return vo;
	}
	
	private RowSet getActualDtCnt(ValueObject params) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();

        try{
        	sqlstr.append(" SELECT  COUNT(ACTUAL_DT) CNT FROM PSR WHERE PSR_ID = ?		\n");
        	
        	search.setStatement(sqlstr.toString());
        	search.addParameter(params.get("psr_id"));
        	WAFLogger.debug(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	public boolean psrRecount(Connection conn, ValueObject sqlParams) throws Exception {
		CallableStatement cs 	= null;
		ValueObject rtnVo = new ValueObject();
		String pssql			= "";
		
		try {
        	pssql = "{call SP_CERAT_PSR(?,?,?,?)}";
        	/*
        	 *       VI_DEPT_CD       IN VARCHAR2,         --PROJECT_CD
					   VI_IN_USER_CD    IN NUMBER,           --작성자 사번  
					   VO_SUCCESS_CD    OUT VARCHAR2,        --성공여부 S:성공 , N:실폐
					   VO_MESSAGE       OUT VARCHAR2
        	 *    
        	 */
        	
        	cs = conn.prepareCall(pssql);
        	
        	cs.setString(1, params.get("dept_cd"));
        	cs.setInt(2, storage.getUser().getInt("ACCT_ID"));
        	
        	cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
        	
        	cs.execute();
        	
            return true;
        }
        catch(Exception e) {
        	storage.setDetailMessage(e);
        	this.storage.setMessage(e.getMessage());
        	throw e;
        }
	}
	
}




