/**
 * @(#)file    InspectionBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.09.01
 * @(#)Description: Inspection
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */

package kr.co.udapsoft.ebid.buyer.process.process.biz;

import oracle.toplink.internal.ox.StrBuffer;
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.common.util.DateUtil;
import com.udapsoft.waf.common.util.Formatter2;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class InspectionListBiz extends Biz {

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

	public void doBiz() throws Exception {
		if(storage.compareEvent("")){
			String stDate = "";
			
			stDate = DateUtil.formatDateTime(DateUtil.getToday(), "yyyyMMdd", "yyyyMM") + "01";
			stDate = Formatter2.formatDate(stDate, "yyyymmdd", "yyyy-mm-dd");

			String endDate = DateUtil.getLastDate(stDate.replaceAll("-", ""),
					"yyyy-mm-dd");

			if (params.get("sYmdSt").equals("")) {
				params.set("sYmdSt", stDate);
			} else {
				params.set("sYmdSt", params.get("sYmdSt")); // 시작일
			}

			if (params.get("sYmdEd").equals("")) {
				params.set("sYmdEd", endDate);
			} else {
				params.set("sYmdEd", params.get("sYmdEd")); // 종료일
			}
			
		} else if(storage.compareEvent("GET_INSPECTION_LIST1")){
			params.set("sheet1", getInspectionList1(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("GET_INSPECTION_LIST2")){
			params.set("sheet2", getInspectionList2(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}
	

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

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

        try{
        	sql.append("	SELECT  A.PSR_ID  PSR_ID,                                                                                                                            		\n");
        	sql.append("	        F_GET_CODE_NAME('BAS_LVL02',A.DISCIPLINE) DISCIPLINE_NM,                                                                                     		\n");
        	sql.append("	        A.DISCIPLINE,                                                                                                                                		\n");
        	sql.append("	        A.MR_NO,                                                                                                                                     		\n");
        	sql.append("	        A.MR_DESC,                                                                                                                                   		\n");
        	sql.append("	        C.ORD_DOC_NO,                                                                                                                                		\n");
        	sql.append("	        GET_CORP_NM_EBID(C.CORP_ID)CORP_NAME ,                                                                                                            		\n");
        	sql.append("	        D.PLAN_DT,                                                                                                                                   		\n");
        	sql.append("	        D.FORECASTING_DT,                                                                                                                            		\n");
        	sql.append("	        E.IRN_DATE,                                                                                                                                  		\n");
        	sql.append("	        DECODE(E.FINAL_FLAG, 'Y', '예', '아니오') FINAL_FLAG,                                                                                           \n");
        	sql.append("	        DECODE(E.INSPECTION_END_FLAG, 'Y', '예', '아니오') INSPECTION_END_FLAG                                                                          \n");
        	sql.append("	        , C.PUR_CONT_ID                                                                                                                       					\n");
        	sql.append("	        , A.DEMANDED_ID                                                                                                                       					\n");
        	sql.append("	        , A.DEPT_CD                                                                                                                       							\n");
        	sql.append("	        , C.CST_DOC_NAME                                                                                                                       							\n");
        	sql.append("	FROM    PSR_MASTER A, PUR1000T C, PSR D,                                                                                     		\n");
        	sql.append("	      ( 																																																																								\n");
        	sql.append("	      	SELECT PSR_ID,INSPECTION_NO, INSPECTION_DATE, FINAL_FLAG, INSPECTION_END_FLAG,IRN_DATE  FROM INSPECTION where PSR_ACTIVITY_CD = '45'      			\n");
        	sql.append("	      	AND INSPECTION_NO IN (SELECT MAX(INSPECTION_NO) INSPECTION_NO FROM INSPECTION GROUP BY PSR_ID )																									\n");
        	if(params.get("date_all_value").equals("N")) {
        		if(!params.get("sYmdSt").equals("") && !params.get("sYmdEd").equals("")) {
        			sql.append("	AND INSPECTION_DATE BETWEEN ? AND ?	\n");
        			search.addParameter(params.get("sYmdSt"));
        			search.addParameter(params.get("sYmdEd"));
        		}
        	}
        	sql.append("	      ) E																																																																								\n");
        	sql.append("	WHERE   A.PSR_ID = D.PSR_ID                                                                                                                          		\n");
        	sql.append("	AND     D.PSR_ACTIVITY_CD = '45'                                                                                                                     		\n");
        	sql.append("	AND     A.PUR_CONT_ID = C.PUR_CONT_ID                                                                                                             			\n");
        	sql.append("	AND     A.PSR_ID = E.PSR_ID(+)                                                                                                                       		\n");
        	sql.append("	AND     A.PUR_CONT_ID IS NOT NULL																																																												\n");
        	
        	if(!params.get("dept_cd").equals("")) {
        		sql.append("		AND     A.DEPT_CD = ?                                                              						\n");
    			search.addParameter(params.get("dept_cd"));
        	}
			
			if(!"".equals(params.get("p_discipline"))) {
        		sql.append(" AND     A.DISCIPLINE = ?	\n");
        		search.addParameter(params.get("p_discipline"));
        	}
        	
        	if(!"".equals(params.get("p_mr_no"))) {
        		sql.append(" AND     A.MR_NO = UPPER(?)	\n");
        		search.addParameter(params.get("p_mr_no"));
        	}
        	
        	if(!"".equals(params.get("p_corp_name"))) {
        		sql.append(" 	AND GET_CORP_NM_EBID(C.CORP_ID) LIKE '%' || ? || '%'	\n");
        		search.addParameter(params.get("p_corp_name"));
        	}
			
			sql.append("	ORDER BY A.DISCIPLINE, A.MR_NO                                                                                                                       		\n");
			search.setStatement(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;
	}
	
	/**
	 * 검수상세 : PSR ID
	 * @param Params
	 * @return
	 */
	
	private RowSet getInspectionList2(ValueObject params) {

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

        try{
        	sqlstr.append(" SELECT  INSPECTION_NO, 												\n");
        	sqlstr.append("   		TO_CHAR((INSPECTION_DATE) ,'YYYY-MM-DD')INSPECTION_DATE , 	\n");
        	sqlstr.append("   		DECODE(FINAL_FLAG, 'Y', '예', '아니오') FINAL_FLAG ,  		\n");
        	sqlstr.append("   		TO_CHAR((IRN_DATE),'YYYY-MM-DD') IRN_DATE , 				\n");
        	sqlstr.append("   		DECODE(INSPECTION_END_FLAG, 'Y', '예', '아니오') INSPECTION_END_FLAG , \n");
        	sqlstr.append("   		REMARKS 													\n");
        	sqlstr.append(" FROM INSPECTION																	\n");
        	sqlstr.append(" WHERE PSR_ID = ?											    				\n");
        	sqlstr.append(" ORDER BY INSPECTION_DATE DESC													\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(params.get("psr_id"));
            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;
	}


	
}