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

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.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.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class WPRListBiz extends Biz {

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

	public void doBiz() throws Exception {
		
		if(storage.compareEvent("GET_WPR_LIST")){
			params.set("sheet1", getWPRList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("GET_WPR_DETAIL")){
			params.set("sheet2", getWPRDetailList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}
	

	/**
	 * 검수대상 List(전체)
	 * @param Params
	 * @return
	 */
	private RowSet getWPRList(ValueObject params) {

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

        try{
        	
        	sqlstr.append("	SELECT  A.PSR_ID  PSR_ID,                                            		\n");
        	sqlstr.append("	           F_GET_CODE_NAME('BAS_LVL02',A.DISCIPLINE) DISCIPLINE_NM,  		\n");
        	sqlstr.append("	          A.DISCIPLINE,                                              		\n");
        	sqlstr.append("	          A.MR_NO,                                                   		\n");
        	sqlstr.append("	          C.CST_DOC_NAME,                                                 		\n");
        	sqlstr.append("	          C.ORD_DOC_NO,                                              		\n");
        	sqlstr.append("	          GET_CORP_NM_EBID(C.CORP_ID)VENDOR_NAME,                             		\n");
        	sqlstr.append("	          D.WF_PROGRESS,                                              		\n");
        	sqlstr.append("	          A.DEPT_CD,                                              		\n");
        	sqlstr.append("	          MAX(E.FLAG_YN) FLAG_YN                                              		\n");
        	sqlstr.append("	 FROM    PSR_MASTER A, PUR1000T C            		\n");
        	sqlstr.append("	 ,                                                                   		\n");
        	sqlstr.append("	 (                                                                   		\n");
        	sqlstr.append("	    SELECT SUM(AA.WEIGHT_FACTOR) WF_PROGRESS, PSR_ID                 		\n");
        	sqlstr.append("	    FROM PSR AA                                                      		\n");
        	sqlstr.append("	    WHERE AA.ACTUAL_DT IS NOT NULL                                   		\n");
        	sqlstr.append("	    GROUP BY AA.PSR_ID                                               		\n");
        	sqlstr.append("	 )D                                                                  		\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("	    WHERE PSR_ACTIVITY_CD BETWEEN '31' AND '39'                                                      		\n");
        	sqlstr.append("	    GROUP BY PSR_ID, PLAN_DT, ACTUAL_DT                                               		\n");
        	sqlstr.append("	 )E                                                                  		\n");
        	sqlstr.append("	 WHERE     A.PUR_CONT_ID = C.PUR_CONT_ID                            		\n");
        	sqlstr.append("	 AND A.PSR_ID = D.PSR_ID                                             		\n");
        	sqlstr.append("	 AND A.PSR_ID = E.PSR_ID                                             		\n");
        	sqlstr.append("	 AND     A.PUR_CONT_ID IS NOT NULL	\n");
        	if(!params.get("dept_cd").equals("")) {
        		sqlstr.append("		AND     A.DEPT_CD = ?                                                              						\n");
    			search.addParameter(params.get("dept_cd"));
        	}
        	
        	if(!"all".equals(params.get("endYn")) && !"".equals(params.get("endYn"))) {
        		if("end".equals(params.get("endYn"))) {
        			sqlstr.append(" AND     D.WF_PROGRESS = 100	\n");
        		} else {
        			sqlstr.append(" AND     D.WF_PROGRESS <> 100	\n");
        		}
        		
        	}
        	
        	if(!"".equals(params.get("p_discipline"))) {
        		sqlstr.append(" AND     A.DISCIPLINE = ?	\n");
        		search.addParameter(params.get("p_discipline"));
        	}
        	
        	if(!"".equals(params.get("p_mr_no"))) {
        		sqlstr.append(" AND     A.MR_NO = UPPER(?)	\n");
        		search.addParameter(params.get("p_mr_no"));
        	}
        	
        	if(!"".equals(params.get("corp_name"))) {
        		sqlstr.append(" 	AND GET_CORP_NM_EBID(C.CORP_ID) LIKE '%' || ? || '%'	\n");
        		search.addParameter(params.get("corp_name"));
        	}

        	sqlstr.append(" GROUP BY A.PUR_CONT_ID, A.PSR_ID, A.DISCIPLINE, A.MR_NO, C.CST_DOC_NAME, C.ORD_DOC_NO, C.CORP_ID, D.WF_PROGRESS, A.DEPT_CD									\n");
        	sqlstr.append(" ORDER BY A.DISCIPLINE,A.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;
	}
	 
	/**
	 * 검수상세 : PSR ID
	 * @param Params
	 * @return
	 */
	
	private RowSet getWPRDetailList(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_ACTIVITY_CD BETWEEN '31' AND '39'  								\n");
        	sqlstr.append(" AND     A.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;
	}

	
}