/**
 * @(#)file    BidIngBiz.java
 * @(#)author  Son Hyung Min 
 * @(#)version 1.0
 * @(#)date    2014-06-23
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.bid.purchase.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.Row;
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 kr.co.udapsoft.common.code.CommCodeManager;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

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

   
public class BidHistoryListBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
        String event = storage.getEvent();
		if(event.equals("SHEET_SELECT")){
			params.set("sheet1", getBoardList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if (event.equals("GET_MATL_KIND_CODE_LV2")) {
			RowSet materialKindCodeLv2 = getLv2MaterialKindCode(params.get("material_Kind_Code_Lv1"));
            storage.addRowSet( "ROWSET", materialKindCodeLv2 );
            storage.setSuccess(true);
            
    	}else if (event.equals("GET_MATL_KIND_CODE_LV3")) {
    		RowSet materialKindCodeLv3 = getLv3MaterialKindCode(params.get("material_Kind_Code_Lv2"));
            storage.addRowSet( "ROWSET", materialKindCodeLv3 );
            storage.setSuccess(true);
    	}else {
			// 공종 콤보박스
    		RowSet materialKindCodeLv1 = getLv1MaterialKindCode();
            storage.addRowSet("materialKindCodeLv1", materialKindCodeLv1);
		}
	}
	
	/**
	 * 발주의뢰(외주) 접수를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getBoardList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	sqlstr.append(" SELECT A.BID_NO, 	                                                                                   \n");
        	sqlstr.append("        A.BID_DEG, 	                                                                                   \n"); 
        	sqlstr.append("        B1.MATERIALGROUPNAME || '/' || B2.MATERIALGROUPNAME || '/' || B3.MATERIALGROUPNAME ITEM_NAME,                           \n");
        	sqlstr.append("        C.VENDOR_NAME,                                                                                  \n");
        	sqlstr.append("        A.DEPT_NM,                                                                                      \n");
        	sqlstr.append("        A.BID_MASTER_NM,                                                                                \n");
        	sqlstr.append("        ESTI_TOT_AMT AMT,                                                                               \n");
        	sqlstr.append("        DECODE(SUCC_YN, 'Y', '낙찰', '') SUCC_YN,                                                        \n");
        	sqlstr.append("        C.RNK || '/' || D.CNT as rnk ,        \n");
        	sqlstr.append("        DECODE(NVL(A.ACT_AMT,0), 0, 0, ROUND((C.ESTI_TOT_AMT/A.ACT_AMT)*100,2)) ACT_RATE      ,         \n");//실행대비
        	sqlstr.append("        DECODE(NVL(A.PUR_AMT,0), 0, 0, ROUND((C.ESTI_TOT_AMT/A.PUR_AMT)*100,2)) PUR_RATE      ,         \n");//외주예산대비
        	sqlstr.append("        F_GET_CODE_NAME2('BID_STATUS_CD',A.STATUS_CD) STATUS_NM                                         \n");//status
        	sqlstr.append("   FROM BID_MASTER A,                                                                                   \n");
        	sqlstr.append("        PMM_MATERIALGROUP_V B3,                                                                                    \n");
        	sqlstr.append("        PMM_MATERIALGROUP_V B2,                                                                                    \n");
        	sqlstr.append("        PMM_MATERIALGROUP_V B1,                                                                                    \n");
            sqlstr.append("        (SELECT A.*, RANK() OVER(PARTITION BY A.BID_NO, A.BID_DEG order by A.ESTI_TOT_AMT ASC) RNK FROM BID_SUPP A )  C,    \n");
            sqlstr.append("        (SELECT A.BID_NO, A.BID_DEG, COUNT(A.BID_NO) CNT                                                \n");
            sqlstr.append("           FROM BID_SUPP A                                                                              \n");
            sqlstr.append("          GROUP BY A.BID_NO, A.BID_DEG) D   ,                                                            \n");
            sqlstr.append("        (SELECT A.*, RANK() OVER(PARTITION BY A.BID_NO, A.BID_DEG ORDER BY A.MATERIAL_CD) RNK FROM BID_ITEM A) E                                                                                   \n");
            sqlstr.append("   WHERE A.BID_NO = E.BID_NO                                                                            \n");
            sqlstr.append("     AND A.BID_DEG = E.BID_DEG                                                                          \n");
            sqlstr.append("     AND E.RNK = 1                                                                                      \n");
            sqlstr.append("     AND SUBSTR(E.MATERIAL_CD,1,3)  = B3.MATERIALGROUP                                                   \n"); 
            sqlstr.append("     AND B3.PARENT_LEVEL  = B2.MATERIALGROUP                                                   \n"); 
            sqlstr.append("     AND B2.PARENT_LEVEL= B1.PARENT_LEVEL                                                            \n");
            sqlstr.append("     AND B1.MATERIAL_LEVEL = 0 AND B2.MATERIAL_LEVEL = 1 AND B3.MATERIAL_LEVEL = 2                      \n");
            sqlstr.append("     AND A.BID_NO = C.BID_NO                                                                            \n");
            sqlstr.append("     AND A.BID_DEG = C.BID_DEG                                                                          \n");
            sqlstr.append("     AND A.BID_NO = D.BID_NO                                                                            \n");
            sqlstr.append("     AND A.BID_DEG = D.BID_DEG                                                                          \n");
            sqlstr.append("     AND A.STATUS_CD in ('06','07','10')                                         \n"); //2016.8.29 윤현석과장 요청. 낙찰선정도 포함
            sqlstr.append("     AND A.BID_FROM_DT >= TO_DATE('" + params.get("signDt_from") + "')                                  \n");
            sqlstr.append("     AND A.BID_FROM_DT <= TO_DATE('" + params.get("signDt_to") + "')                                    \n");
            if(!"".equals(params.get("dept_nm"))) 
        		sqlstr.append(" AND A.DEPT_NM LIKE '%" +  params.get("dept_nm") +  "%'	                                           \n");
         	
        	if(!param.get("searchCorp").isEmpty())
        		sqlstr.append("     AND UPPER(C.VENDOR_NAME) LIKE UPPER('%"+param.get("searchCorp")+"%')                                         \n");
        	if(!param.get("searchBid").isEmpty())
        		sqlstr.append("     AND UPPER(A.BID_MASTER_NM) LIKE UPPER('%"+param.get("searchBid")+"%')                                         \n");
        	
        	if(!param.get("material_Kind_Code_Lv1").isEmpty()){
            	if(!param.get("material_Kind_Code_Lv2").isEmpty()){
            		if(!param.get("material_Kind_Code_Lv3").isEmpty())
            			sqlstr.append("     AND E.MATERIAL_CD LIKE '"+param.get("material_Kind_Code_Lv3")+"%'                                      \n");
            		else
            			sqlstr.append("     AND E.MATERIAL_CD LIKE '"+param.get("material_Kind_Code_Lv2")+"%'                                  \n");
            	}
            	else
            		sqlstr.append("     AND B1.PARENT_LEVEL LIKE '%"+param.get("material_Kind_Code_Lv1")+"%'                                      \n");
            }
            sqlstr.append("ORDER BY A.BID_MASTER_NM,  A.DEPT_NM, C.RNK               \n");
            //sqlstr.append("   ORDER BY  B1.MATERIALGROUPNAME,B2.MATERIALGROUPNAME,B3.MATERIALGROUPNAME,C.VENDOR_NAME	                                    \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;
	}	
	
	// 자재분류 1레벨 코드 가져오기
    public RowSet getLv1MaterialKindCode() {
        Search search       = new SearchImpl();
        RowSet rowSet       = new RowSetImpl();
        StringBuffer sql    = new StringBuffer();
        
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      PARENT_LEVEL MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '0'           \n");            
            
            search.setStatement(sql);
            rowSet = search.execute();
            WAFLogger.debug(sql.toString());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
	 
	public RowSet getLv2MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '1'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
//            sql.append("    ORDER BY MATERIALGROUP          \n");
            
            WAFLogger.debug(sql.toString());
            WAFLogger.debug(search.getParameters());
            
            
            search.setStatement(sql);
            rowSet = search.execute();
            
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
	
	// 자재분류 3레벨 코드 가져오기
    public RowSet getLv3MaterialKindCode(String Params) {
        Search search           = new SearchImpl();
        RowSet rowSet           = new RowSetImpl();
        StringBuffer sql        = new StringBuffer();
        /*
         * MATERIAL_ID          : 
         * MATERIALGROUP        : 자재그룹코드
         * PARENT_LEVEL         : 상위그룹
         * MATERIALLEVEL        : 레벨
         * MATERIALGROUPNAME    :
         * MATERIAL_GB          :
         */
        try {
            sql.append("    SELECT               \n");
            sql.append("      MATERIALGROUP                 \n");
            sql.append("    , MATERIALGROUPNAME             \n");
            sql.append("    FROM PMM_MATERIALGROUP_V          \n");
            sql.append("    WHERE MATERIAL_LEVEL = '2'           \n");
            if (!"".equals(Params) && null != Params) {
                sql.append("    AND PARENT_LEVEL = ?        \n");
                search.addParameter(Params);
            }
            
            search.setStatement(sql);
            
            rowSet = search.execute();
            WAFLogger.debug(sql.toString());
            WAFLogger.debug(search.getParameters());
        } 
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return rowSet;
    }
}