/**
 * @(#)file    DivContPopListBiz.java
 * @(#)author  chj
 * @(#)version 1.0
 * @(#)date    2015-02-24
 */
package kr.co.udapsoft.ebid.buyer.bid.purchase.biz;



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

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class DivContPopListBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
		
		if(storage.compareEvent("GET_VENDOR_LIST")){
			params.set("sheet1", getDivContList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}
	

	/**
	 * 입찰 업체 견적 조회
	 * @param Params
	 * @return
	 */
	private RowSet getDivContList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{ 


        	sqlstr.append("				SELECT A.MATERIAL_CD                                                                                                                                                                                    \n");
        	sqlstr.append("	                 , F_GET_MATERIAL_NAME(A.MATERIAL_CD) AS MATERIAL_NM                                                                                                                                                \n");
        	sqlstr.append("	                 , F_GET_MATL_SPEC_NAME(A.MATERIAL_CD) AS MATERIAL_SPEC                                                                                                                                             \n");
        	sqlstr.append("	                 , F_GET_UNIT_KIND_NAME(A.MATERIAL_CD) AS UNIT_KIND                                                                                                                                                 \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(DEMANDED_QTY,0)) FROM BID_ITEM WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD )   AS ESTI_QTY                                                       \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(SELECT_QTY,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V1) AS V1_SELECT_QTY                         \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(UNIT_COST,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V1)   AS V1_UNIT_COST                         \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(SELECT_QTY,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V2)  AS V2_SELECT_QTY                        \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(UNIT_COST,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V2)    AS V2_UNIT_COST                        \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(SELECT_QTY,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V3) AS V3_SELECT_QTY                         \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(UNIT_COST,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V3)   AS V3_UNIT_COST                         \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(SELECT_QTY,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V4)  AS V4_SELECT_QTY                        \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(UNIT_COST,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V4)    AS V4_UNIT_COST                        \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(SELECT_QTY,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V5)  AS V5_SELECT_QTY                        \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(UNIT_COST,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V5)    AS V5_UNIT_COST                        \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(SELECT_QTY,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V6)   AS V6_SELECT_QTY                       \n");
        	sqlstr.append("	                 , (SELECT SUM(NVL(UNIT_COST,0)) FROM BID_SUPP_ESTI WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG AND MATERIAL_CD = A.MATERIAL_CD AND VENDOR_CD = B.V6)     AS V6_UNIT_COST                       \n");
        	sqlstr.append("	                 , B.V1 AS V1_VENDOR_CD                      \n");
        	sqlstr.append("	                 , B.V2 AS V2_VENDOR_CD                      \n");
        	sqlstr.append("	                 , B.V3 AS V3_VENDOR_CD                      \n");
        	sqlstr.append("	                 , B.V4 AS V4_VENDOR_CD                      \n");
        	sqlstr.append("	                 , B.V5 AS V5_VENDOR_CD                      \n");
        	sqlstr.append("	                 , B.V6 AS V6_VENDOR_CD                      \n");
        	sqlstr.append("	            FROM (                                                                                                                                                                                                  \n");
        	sqlstr.append("	                    SELECT MATERIAL_CD                                                                                                                                                                              \n");
        	sqlstr.append("	                         , BID_NO                                                                                                                                                                                   \n");
        	sqlstr.append("	                           , BID_DEG                                                                                                                                                                                \n");
        	sqlstr.append("	                    FROM BID_SUPP_ESTI                                                                                                                                                                              \n");
        	sqlstr.append("	                    WHERE BID_NO = ?                                                                                                                                                                                \n");
        	sqlstr.append("	                    AND BID_DEG = ?                                                                                                                                                                                 \n");
        	sqlstr.append("	                    GROUP BY BID_NO , BID_DEG, MATERIAL_CD                                                                                                                                                          \n");
        	sqlstr.append("	                ) A                                                                                                                                                                                                 \n");
        	sqlstr.append("	              , (                                                                                                                                                                                                   \n");
        	sqlstr.append("	                    SELECT MAX(V1) AS V1                                                                                                                                                                            \n");
        	sqlstr.append("	                            , MAX(V2) AS V2                                                                                                                                                                         \n");
        	sqlstr.append("	                            , MAX(V3) AS V3                                                                                                                                                                         \n");
        	sqlstr.append("	                            , MAX(V4) AS V4                                                                                                                                                                         \n");
        	sqlstr.append("	                            , MAX(V5) AS V5                                                                                                                                                                         \n");
        	sqlstr.append("	                            , MAX(V6) AS V6                                                                                                                                                                         \n");
        	sqlstr.append("	                            , BID_NO                                                                                                                                                                                \n");
        	sqlstr.append("	                            ,  BID_DEG                                                                                                                                                                              \n");
        	sqlstr.append("	                    FROM (                                                                                                                                                                                          \n");
        	sqlstr.append("	                            SELECT DECODE(ESTI_NUM, 1, B.VENDOR_CD ) AS V1                                                                                                                                          \n");
        	sqlstr.append("	                                 , DECODE(ESTI_NUM , 2, B.VENDOR_CD) AS V2                                                                                                                                          \n");
        	sqlstr.append("	                                 , DECODE(ESTI_NUM , 3, B.VENDOR_CD) AS V3                                                                                                                                          \n");
        	sqlstr.append("	                                 , DECODE(ESTI_NUM , 4, B.VENDOR_CD) AS V4                                                                                                                                          \n");
        	sqlstr.append("	                                 , DECODE(ESTI_NUM , 5, B.VENDOR_CD) AS V5                                                                                                                                          \n");
        	sqlstr.append("	                                 , DECODE(ESTI_NUM , 6, B.VENDOR_CD) AS V6                                                                                                                                          \n");
        	sqlstr.append("	                                 , B.*                                                                                                                                                                              \n");
        	sqlstr.append("	                            FROM (                                                                                                                                                                                  \n");
        	sqlstr.append("	                                    SELECT ROWNUM AS ESTI_NUM , BB.*                                                                                                                                                \n");
        	sqlstr.append("	                                    FROM (                                                                                                                                                                          \n");
        	sqlstr.append("	                                                  SELECT AAA.*                                                                                                                                                      \n");
        	sqlstr.append("	                                                  FROM  BID_SUPP AAA                                                                                                                                                \n");
        	sqlstr.append("	                                                         ,  (                                                                                                                                                       \n");
        	sqlstr.append("	                                                               SELECT BID_NO                                                                                                                                        \n");
        	sqlstr.append("	                                                                     , BID_DEG                                                                                                                                      \n");
        	sqlstr.append("	                                                                     , VENDOR_CD                                                                                                                                    \n");
        	sqlstr.append("	                                                               FROM BID_SUPP                                                                                                                                        \n");
        	sqlstr.append("	                                                               WHERE BID_NO = ?                                                                                                                                     \n");
        	sqlstr.append("	                                                               AND BID_DEG = ?                                                                                                                                      \n");
        	sqlstr.append("	                                                               GROUP BY BID_NO, BID_DEG,  VENDOR_CD                                                                                                                 \n");
        	sqlstr.append("	                                                            ) BBB                                                                                                                                                   \n");
        	sqlstr.append("	                                                  WHERE AAA.BID_NO = BBB.BID_NO                                                                                                                                     \n");
        	sqlstr.append("	                                                  AND      AAA.BID_DEG= BBB.BID_DEG                                                                                                                                 \n");
        	sqlstr.append("	                                                  AND      AAA.VENDOR_CD = BBB.VENDOR_CD                                                                                                                            \n");  
        	sqlstr.append("	                                                  ORDER BY ESTI_TOT_AMT NULLS LAST, AAA.VENDOR_CD                                                                                                                   \n");
        	sqlstr.append("	                                               )    BB                                                                                                                                                              \n");
        	sqlstr.append("	                                    ) B                                                                                                                                                                             \n");
        	sqlstr.append("	                            WHERE ESTI_NUM < 7                                                                                                                                                                      \n");
        	sqlstr.append("	                         )                                                                                                                                                                                          \n");
        	sqlstr.append("	                        GROUP BY BID_NO , BID_DEG                                                                                                                                                                   \n");
        	sqlstr.append("	            )  B                                                                                                                                                                                                    \n");
        	sqlstr.append("	            WHERE A.BID_NO = B.BID_NO                                                                                                                                                                               \n");
        	sqlstr.append("	            AND   A.BID_DEG = B.BID_DEG   																																											\n");
        	sqlstr.append("	            ORDER BY MATERIAL_CD 																																											\n");
        	        	
			search.addParameter(params.get("BID_NO"));
			search.addParameter(params.get("BID_DEG"));
			search.addParameter(params.get("BID_NO"));
			search.addParameter(params.get("BID_DEG"));
        	search.setStatement(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;
	}
	
	
}