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

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

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 kr.co.udapsoft.common.util.PCMSUtil;

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;

   
public class PSRBiz extends Biz {

	public PSRBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		boolean success =  false;
		if(storage.compareEvent("")){
			 RowSet rowSet = getNewActivityCode();
		     storage.addRowSet("psrActivityCodeRowSet", rowSet);
		} else if(storage.compareEvent("GET_PSR")){
			params.set("sheet1", getPSR(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} else if(storage.compareEvent("GET_PSR_DETAIL")){
			params.set("sheet2", getPSRDetail(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} else if(storage.compareEvent("GET_TEMP_EXCEL_TABLE")){
			params.set("sheet1", getTempExcelTable(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
			success = deleteTempExcelTable(params);	// �꾩떆 T ��젣
			storage.setSuccess(success);
		} else if (storage.compareEvent("DELETE_TEMP_EXCEL_TABLE")) {
			success = deleteTempExcelTable(params);	// �꾩떆 T ��젣
			storage.setSuccess(success);
		}
		
	}
	

	/**
	 * 寃�닔��긽 List(�꾩껜)
	 * @param Params
	 * @return
	 */
	private RowSet getPSR(ValueObject params) {

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

        try{
        	
        	sqlstr.append("		SELECT  B.PSR_ID,                                                                                                                                                                                                  		\n");
        	sqlstr.append("		        F_GET_CODE_NAME('BAS_LVL02',B.DISCIPLINE) DISCIPLINE_NM,                                                                                                                                                   		\n");
        	sqlstr.append("		        B.DISCIPLINE,                                                                                                                                                                                              		\n");
        	sqlstr.append("		        B.MR_NO,                                                                                                                                                                                                   		\n");
        	sqlstr.append("		        B.MR_DESC,                                                                                                                                                                                                 		\n");
        	sqlstr.append("		        D.ORD_DOC_NO,                                                                                                                                                                                              		\n");
        	sqlstr.append("		        B.DEMANDED_ID,                                                                                                                                                                             		\n");
        	sqlstr.append("		        D.CORP_ID,                                                                                                                                                                             		\n");
        	sqlstr.append("		        GET_CORP_NM_EBID(D.CORP_ID)CORP_NAME,                                                                                                                                                                             		\n");
        	sqlstr.append("		        B.REMARKS,                                                                                                                                                                                                 		\n");
        	sqlstr.append("		        B.PUR_CONT_ID,                                                                                                                                                                                             		\n");
        	sqlstr.append("		        B.DEPT_CD,                                                                                                                                                                                             		\n");
        	sqlstr.append("		        E.POD,                                                                                                                                                                                                     		\n");
        	sqlstr.append("	DECODE(A.FLAG, '1', 'P', '2', 'F', 'A') FLAG          		\n");
        	sqlstr.append("	, A.A11 IR_RCV  		\n");
        	sqlstr.append("	, A.A13 INQ_ISS 		\n");
        	sqlstr.append("	, A.A14 BID_DUE 		\n");
        	sqlstr.append("	, A.A15 QUOT_RCV		\n");
        	sqlstr.append("	, A.A16 TBE_RCV 		\n");
        	sqlstr.append("	, A.A17 CBE_COM 		\n");
        	sqlstr.append("	, A.A22 PR_RCV  		\n");
        	sqlstr.append("	, A.A23 PO_ISS  		\n");
        	sqlstr.append("	, A.A25 PO_DEL  		\n");
        	sqlstr.append("	, A.A51 PO_SHIP 		\n");
        	sqlstr.append("	, A.A53 ETD     		\n");
        	sqlstr.append("	, A.A55 ATD     		\n");
        	sqlstr.append("	, A.A31 VP_IN   		\n");
        	sqlstr.append("	, A.A32 VP_OUT  		\n");
        	sqlstr.append("	, A.A33 SO      		\n");
        	sqlstr.append("	, A.A35 RM      		\n");
        	sqlstr.append("	, A.A36 FAB     		\n");
        	sqlstr.append("	, A.A45 INSPECTION		\n");
        	sqlstr.append("	, A.A39 DELIVERY		\n");
        	sqlstr.append("	, A.A59 ONSITE		\n");
        	sqlstr.append("	, A.A11 IR_RCV_H  		\n");
        	sqlstr.append("	, A.A13 INQ_ISS_H 		\n");
        	sqlstr.append("	, A.A14 BID_DUE_H 		\n");
        	sqlstr.append("	, A.A15 QUOT_RCV_H		\n");
        	sqlstr.append("	, A.A16 TBE_RCV_H 		\n");
        	sqlstr.append("	, A.A17 CBE_COM_H 		\n");
        	sqlstr.append("	, A.A22 PR_RCV_H  		\n");
        	sqlstr.append("	, A.A23 PO_ISS_H  		\n");
        	sqlstr.append("	, A.A25 PO_DEL_H  		\n");
        	sqlstr.append("	, A.A51 SHIP_H 		\n");
        	sqlstr.append("	, A.A53 POD_H     		\n");
        	sqlstr.append("	, A.A55 POL_H     		\n");
        	sqlstr.append("	, A.A31 VP_IN_H   		\n");
        	sqlstr.append("	, A.A32 VP_OUT_H  		\n");
        	sqlstr.append("	, A.A33 SUB_ORDER_H      		\n");
        	sqlstr.append("	, A.A35 \"RECEIVE/MATERIAL_H\"      		\n");
        	sqlstr.append("	, A.A36 FABRICATION_H     		\n");
        	sqlstr.append("	, A.A39 DELIVERY_H     		\n");
        	sqlstr.append("	, A.A45 FINAL_INSPECTION_H		\n");
        	sqlstr.append("	, A.A59 ONSITE_H		\n");
        	sqlstr.append("	, MAX(F.FLAG_YN) FLAG_YN		\n");
        	//sqlstr.append("		        A.FLAG,A.A11,A.A13,A.A14,A.A15,A.A16,A.A17,A.A22,A.A23,A.A25,A.A51,A.A53,A.A55,A.A31,A.A32,A.A33,A.A35,A.A36,A.A59                                                                                         		\n");
        	sqlstr.append("		FROM(                                                                                                                                                                                                              		\n");
        	sqlstr.append("		    SELECT PSR_ID,'1' FLAG,A11,A13,A14,A15,A16,A17,A22,A23,A25,A31,A32,A33,A35,A36,A39,A45,A51,A53,A55,A59                                                                                                         		\n");
        	sqlstr.append("		    FROM(                                                                                                                                                                                                          		\n");
        	sqlstr.append("		    SELECT                                                                                                                                                                                                         		\n");
        	sqlstr.append("		          PSR_ID,PSR_ACTIVITY_CD,PLAN_DT                                                                                                                                                                           		\n");
        	sqlstr.append("		    FROM PSR                                                                                                                                                                                                      		\n");
        	sqlstr.append("		    ORDER BY PSR_ACTIVITY_CD, PLAN_DT DESC                                                                                                                                                                                                      		\n");
        	sqlstr.append("		    )                                                                                                                                                                                                      		\n");
        	sqlstr.append("		    PIVOT                                                                                                                                                                                                          		\n");
        	sqlstr.append("		    (                                                                                                                                                                                                              		\n");
        	sqlstr.append("		        MAX(PLAN_DT)                                                                                                                                                                                               		\n");
        	sqlstr.append("		        FOR PSR_ACTIVITY_CD IN('11' A11,'13' A13,'14' A14,'15' A15,'16' A16,'17' A17,'22' A22,'23' A23,'25' A25,'31' A31,'32' A32,'33' A33,'35' A35,'36' A36,'39' A39,'45' A45,'51' A51,'53' A53,'55' A55,'59' A59)		\n");
        	sqlstr.append("		    )                                                                                                                                                                                                              		\n");
        	sqlstr.append("		)   A,                                                                                                                                                                                                             		\n");
        	sqlstr.append("		    PSR_MASTER B, PMM_DEMANDED_REQUEST C, PUR1000T D,                                                                                                                                                              		\n");
        	sqlstr.append("		    (                                                                                                                                                                                                              		\n");
        	sqlstr.append("		      SELECT BB.PUR_CONT_ID, MAX(AA.POD)POD  FROM PSR_SHIPPING AA, PSR_SHIPPING_DETAIL BB WHERE AA.SHIPPING_ID = BB.SHIPPING_ID GROUP BY BB.PUR_CONT_ID                                   		\n");
        	sqlstr.append("		    ) E                                                                                                                                                                                                            		\n");
        	sqlstr.append("		    ,                                                                                                                                                                                                             		\n");
        	sqlstr.append("		    (                                                                                                                                                                                                            		\n");
        	sqlstr.append("		    	SELECT CASE WHEN TO_CHAR(PLAN_DT, 'YYYYMMDD') < TO_CHAR(SYSDATE, 'YYYYMMDD') AND ACTUAL_DT IS NULL THEN 'Y' END FLAG_YN, PSR_ID                                                                                                                                                                                                            		\n");
        	sqlstr.append("		    	FROM PSR                                                                                                                                                                                                            		\n");
        	sqlstr.append("		    	GROUP BY PSR_ID, PLAN_DT, ACTUAL_DT                                                                                                                                                                                                            		\n");
        	sqlstr.append("		    ) F                                                                                                                                                                                                            		\n");
        	sqlstr.append("		WHERE A.PSR_ID = B.PSR_ID                                                                                                                                                                                          		\n");
        	sqlstr.append("		AND   B.DEMANDED_ID = C.DEMANDED_ID(+)                                                                                                                                                                             		\n");
        	sqlstr.append("		AND   B.PUR_CONT_ID = D.PUR_CONT_ID(+)                                                                                                                                                                             		\n");
        	sqlstr.append("		AND   B.PUR_CONT_ID = E.PUR_CONT_ID(+)                                                                                                                                                                             		\n");
        	sqlstr.append("		AND   A.PSR_ID = F.PSR_ID                                                                                                                                                                             		\n");
        	
        	if(!"".equals(params.get("dept_cd"))) {
        		sqlstr.append("		AND     B.DEPT_CD = ?                                                              						\n");
    			search.addParameter(params.get("dept_cd"));
        	}
        	if(!"".equals(params.get("p_discipline"))) {
        		sqlstr.append("		AND   B.DISCIPLINE = ?                                                                                                                                                                             		\n");
        		search.addParameter(params.get("p_discipline"));
        	}
        	if(!"".equals(params.get("p_mr_no"))) {
        		sqlstr.append("		AND   B.MR_NO = UPPER(?)                                                                                                                                                                             		\n");
        		search.addParameter(params.get("p_mr_no"));
        	}
        	sqlstr.append("		GROUP BY A.PSR_ID, B.PSR_ID, B.DISCIPLINE, B.MR_NO, B.MR_DESC, D.ORD_DOC_NO, B.DEMANDED_ID, D.CORP_ID, B.REMARKS, B.PUR_CONT_ID, B.DEPT_CD,                                                                                                                                                                		\n");
        	sqlstr.append("		E.POD, A.FLAG, A.A11, A.A13, A.A14, A.A15, A.A16, A.A17, A.A22, A.A23, A.A25, A.A51, A.A53, A.A55, A.A31, A.A32, A.A33, A.A35, A.A36                                                                                                                                                                		\n");
        	sqlstr.append("		, A.A45, A.A39, A.A59, A.A11, A.A13, A.A14, A.A15, A.A16, A.A17, A.A22, A.A23, A.A25, A.A51, A.A53, A.A55, A.A31, A.A32, A.A33, A.A35, A.A36                                                                                                                                                                		\n");
        	sqlstr.append("		, A.A39, A.A45, A.A59                                                                                                                                                                		\n");
        	sqlstr.append("		ORDER BY B.DISCIPLINE, B.MR_NO                                                                                                                                                               		\n");
        	
        	search.setStatement(sqlstr.toString());
			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);
            WAFLogger.error("### WAFSQLException : " + se.getMessage());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	
	private RowSet getPSRDetail(ValueObject params) {

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

        try{
        	sqlstr.append(" SELECT  C.PSR_ACTIVITY_NAME PSR_ACTIVITY_NM, 	\n");
        	sqlstr.append("   		A.PSR_ACTIVITY_CD,													\n");
        	sqlstr.append("   		TO_CHAR((A.PLAN_DT) ,'YYYY-MM-DD') PLAN_DT,  							\n");
        	sqlstr.append("   		TO_CHAR((A.FORECASTING_DT) ,'YYYY-MM-DD') FORECASTING_DT,				\n");
        	sqlstr.append("   		TO_CHAR((A.ACTUAL_DT) ,'YYYY-MM-DD') ACTUAL_DT, 						\n");
        	sqlstr.append("   		A.WEIGHT_FACTOR,														\n");
        	sqlstr.append("   		A.PSR_ID,														\n");
        	sqlstr.append("   		B.MR_NO,														\n");
        	sqlstr.append("   		B.DEMANDED_ID,														\n");
        	sqlstr.append("   		B.PUR_CONT_ID,														\n");
        	sqlstr.append("   		B.DEPT_CD														\n");
        	sqlstr.append(" FROM 	PSR	A, PSR_MASTER B, PSR_ACTIVITY_CODE C																\n");
        	sqlstr.append(" WHERE A.PSR_ID = B.PSR_ID	\n");
        	sqlstr.append(" AND A.PSR_ACTIVITY_CD = C.PSR_ACTIVITY_CD	\n");
        	sqlstr.append(" AND     A.PSR_ID = ?															\n");
        	sqlstr.append(" ORDER BY C.DISPLAY_SORT															\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 RowSet getNewActivityCode() {

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

        try{
        	sqlstr.append(" SELECT  						\n");
        	sqlstr.append(" 		PSR_ACTIVITY_CD			\n");
        	sqlstr.append(" 		, PSR_ACTIVITY_NAME		\n");
        	sqlstr.append(" 		, PSR_ACTIVITY_STD_WF		\n");
        	sqlstr.append(" 		, PSR_ACTIVITY_SAVE_NAME		\n");
        	sqlstr.append(" FROM PSR_ACTIVITY_CODE			\n");
        	sqlstr.append(" ORDER BY PSR_ACTIVITY_CD		\n");
        	
        	search.setStatement(sqlstr.toString());
        	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;
	}
	

	private RowSet getTempExcelTable(ValueObject params) {

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

        try{
        	sql.append("	SELECT                                         	\n");
        	sql.append("		DECODE(NVL(B.MR_NO, ''), '', 'I', 'U') STATUS2	\n");
        	sql.append("		, COL1 DISCIPLINE_NM                         	\n");
        	sql.append("		, COL2 MR_NO                                 	\n");
        	sql.append("		, COL3 ORD_DOC_NO                            	\n");
        	sql.append("		, COL4 MR_DESC                               	\n");
        	sql.append("		, COL5 CORP_NAME                             	\n");
        	sql.append("		, COL6 IR_RCV_H                                	\n");
        	sql.append("		, COL7 INQ_ISS_H                               	\n");
        	sql.append("		, COL8 BID_DUE_H                               	\n");
        	sql.append("		, COL9 QUOT_RCV_H                              	\n");
        	sql.append("		, COL10 TBE_RCV_H                              	\n");
        	sql.append("		, COL11 CBE_COM_H                              	\n");
        	sql.append("		, COL12 PR_RCV_H                               	\n");
        	sql.append("		, COL13 PO_ISS_H                               	\n");
        	sql.append("		, COL14 PO_DEL_H                               	\n");
        	sql.append("		, COL15 FINAL_INSPECTION_H                           	\n");
        	sql.append("		, COL16 SHIP_H                              	\n");
        	sql.append("		, COL17 POD_H                                  	\n");
        	sql.append("		, COL18 POL_H                                  	\n");
        	sql.append("		, COL19 ONSITE_H                                  	\n");
        	sql.append("		, COL20 VP_IN_H                                	\n");
        	sql.append("		, COL21 VP_OUT_H                               	\n");
        	sql.append("		, COL22 SUB_ORDER_H                                   	\n");
        	sql.append("		, COL23 \"RECEIVE/MATERIAL_H\"                                   	\n");
        	sql.append("		, COL24 FABRICATION_H                                  	\n");
        	sql.append("		, COL25 DELIVERY_H                             	\n");
        	sql.append("		, COL26 REMARKS                              	\n");
        	sql.append("		, COL27 DISCIPLINE                           	\n");
        	sql.append("		, COL28 PSR_ID                               	\n");
        	sql.append("		, COL29 CORP_ID                              	\n");
        	sql.append("		, COL30 DEMANDED_ID                          	\n");
        	sql.append("		, COL31 PUR_CONT_ID                          	\n");
        	sql.append("		, COL32 DEPT_CD									\n");
        	sql.append("		, COL6 IR_RCV                                	\n");
        	sql.append("		, COL7 INQ_ISS                               	\n");
        	sql.append("		, COL8 BID_DUE                               	\n");
        	sql.append("		, COL9 QUOT_RCV                              	\n");
        	sql.append("		, COL10 TBE_RCV                              	\n");
        	sql.append("		, COL11 CBE_COM                              	\n");
        	sql.append("		, COL12 PR_RCV                               	\n");
        	sql.append("		, COL13 PO_ISS                               	\n");
        	sql.append("		, COL14 PO_DEL                               	\n");
        	sql.append("		, COL15 INSPECTION                           	\n");
        	sql.append("		, COL16 PO_SHIP                               	\n");
        	sql.append("		, COL17 ATD                                 	\n");
        	sql.append("		, COL18 ETD                                 	\n");
        	sql.append("		, COL19 ONSITE                                  	\n");
        	sql.append("		, COL20 VP_IN                                	\n");
        	sql.append("		, COL21 VP_OUT                               	\n");
        	sql.append("		, COL22 SO                                   	\n");
        	sql.append("		, COL23 RM                                   	\n");
        	sql.append("		, COL24 FAB                                  	\n");
        	sql.append("		, COL25 DELIVERY                             	\n");
        	sql.append("	FROM TEMP_EXCEL_TABLE A, PSR_MASTER B          	\n");
        	sql.append("	WHERE A.COL2 = B.MR_NO(+)                      	\n");
        	sql.append("	AND A.COL30 = B.PUR_CONT_ID(+)                 	\n");
        	sql.append("		ORDER BY A.COL26, A.COL2 DESC, A.COL3 DESC, A.COL27 DESC		\n");
        	
        	
        	search.setStatement(sql.toString());
			WAFLogger.debug(sql.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 deleteTempExcelTable(ValueObject params) {
	  Persistent persistent_d = null;
	  StringBuffer sql   = new StringBuffer();
	  
	  sql.append("	DELETE FROM TEMP_EXCEL_TABLE WHERE COL33 = ?	\n");

	  try {
		  conn.begin();
		  
		  persistent_d = new PersistentImpl(super.conn.getConnection());
		  persistent_d.setStatement(sql.toString());
		  persistent_d.addParameter(params.get("temp_excel_table_seq"));
		  
		  
		  WAFLogger.debug(sql.toString());
		  persistent_d.execute();
		  persistent_d.clearParameters();
		  
		  return true;
	  }
	  catch(Exception e) {
		  super.doException(e);
		  super.storage.setMessage(e.getMessage());
		  super.storage.setDetailMessage(e);
		  super.conn.setRollback();
		  return false;
	  }
	  finally {
		  super.conn.end();
	  }
  }
	
}



