/**
 * @(#)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.outside.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 BidHistoryDeptListBiz extends Biz {

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

	public void doBiz() throws Exception {
        String event = storage.getEvent();
        boolean success = false;
		if(event.equals("SHEET_SELECT")){
			params.set("sheet1", getBoardList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
		else if(event.equals("DEPTPOP")){
			params.set("sheet1", getDeptList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
		else if(event.equals("DEPT_SAVE")){
			success=getDeptSave(params); 
			super.storage.setSuccess(success);
		}else {
			// 공종 콤보박스
			RowSet outItemTag1 = getOutItemRowSet("1");
			storage.addRowSet("outItemTag1", outItemTag1);
			RowSet outItemTag2 = getOutItemRowSet("2");
			storage.addRowSet("outItemTag2", outItemTag2);
			RowSet outItemTag3 = getOutItemRowSet("3");
			storage.addRowSet("outItemTag3", outItemTag3);
			RowSet deptName = getDeptNameRowSet();
			storage.addRowSet("DeptName", deptName);
		}
	}
	private boolean getDeptSave(ValueObject param) {
		 Persistent persistent = null;
		 String sqlstr = "";
        
		 try{       
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            sqlstr += "UPDATE EBID_COM911T                                \n"
        	        + "   SET DETAILSORTNM = ?                            \n"
        	        + "      ,DETAILNM = ?                                \n"
        	        + "      ,UPD_BY = ?                                  \n"
        	        + "      ,UPD_DATE = SYSDATE                          \n"
        	        + " WHERE BASECD = 'BID_HIS_DEPT'                     \n"
        	        + "   AND DETAILCD = ?                                \n";
          
        	String[] status  		= super.gets("sheet1.STATUS");
    		String[] detailcd  		= super.gets("sheet1.detailcd");
    		String[] dept_cd  		= super.gets("sheet1.dept_cd");
    		String[] dept_nm  		= super.gets("sheet1.dept_nm");
    		
    		persistent.setStatement(sqlstr);
        	
    		if( status != null ) {
    			for( int i = 0 ; i < status.length ; i++ ) {
    				if( "U".equals(status[i]) ) {
    					persistent.addParameter(dept_cd[i].trim());
    					persistent.addParameter(dept_nm[i].trim());
    					persistent.addParameter(storage.getUser().get("ACCT_ID"));
    					persistent.addParameter(detailcd[i].trim());
    					
    					persistent.execute();
                        persistent.clearParameters();
    				}
    			}
    		}	
    		return true;
        }
        catch (Exception e) {
        	System.out.println(this.getClass().getName() + " --> saveData() : " + e.getMessage());
            System.out.println("SQL : " + persistent.getStatement());
            System.out.println(e);
            return false;
        }
        finally {    
        	super.conn.end();
        }
	}		
	
	private RowSet getDeptList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{                                                                                                                              
        	sqlstr.append("SELECT DETAILCD                                               \n");
        	sqlstr.append("      ,DETAILNM dept_nm                                       \n");
        	sqlstr.append("      ,DETAILSORTNM dept_cd                                   \n");
        	sqlstr.append("      ,1 img_gb                                               \n");
        	sqlstr.append("      ,2 img_del                                              \n");
        	sqlstr.append("  FROM EBID_COM911T                                           \n");
        	sqlstr.append(" WHERE BASECD = 'BID_HIS_DEPT'                                \n");
        	sqlstr.append("   AND NVL(DETAILNM, '1') LIKE '%"+param.get("dept_nm")+"%'   \n");
            sqlstr.append(" ORDER BY DETAILCD                                            \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;
	}		
	/**
	 * 발주의뢰(외주) 접수를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getBoardList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{                                                                                                                              
        	sqlstr.append("SELECT ITEM_NAME, VENDOR_NAME                                                       \n");
        	sqlstr.append("      ,MAX(AMT1)   AMT1, MAX(RATE1)   RATE1, MAX(SUCC_YN1)  SUCC_YN1                \n");
        	sqlstr.append("      ,MAX(AMT2)   AMT2, MAX(RATE2)   RATE2, MAX(SUCC_YN2)  SUCC_YN2                \n");
        	sqlstr.append("      ,MAX(AMT3)   AMT3, MAX(RATE3)   RATE3, MAX(SUCC_YN3)  SUCC_YN3                \n");
        	sqlstr.append("      ,MAX(AMT4)   AMT4, MAX(RATE4)   RATE4, MAX(SUCC_YN4)  SUCC_YN4                \n");
        	sqlstr.append("      ,MAX(AMT5)   AMT5, MAX(RATE5)   RATE5, MAX(SUCC_YN5)  SUCC_YN5                \n");
        	sqlstr.append("      ,MAX(AMT6)   AMT6, MAX(RATE6)   RATE6, MAX(SUCC_YN6)  SUCC_YN6                \n");
        	sqlstr.append("      ,MAX(AMT7)   AMT7, MAX(RATE7)   RATE7, MAX(SUCC_YN7)  SUCC_YN7                \n");
        	sqlstr.append("      ,MAX(AMT8)   AMT8, MAX(RATE8)   RATE8, MAX(SUCC_YN8)  SUCC_YN8                \n");
        	sqlstr.append("      ,MAX(AMT9)   AMT9, MAX(RATE9)   RATE9, MAX(SUCC_YN9)  SUCC_YN9                \n");
        	sqlstr.append("      ,MAX(AMT10) AMT10, MAX(RATE10) RATE10, MAX(SUCC_YN10) SUCC_YN10               \n");
        	sqlstr.append("      ,MAX(AMT11) AMT11, MAX(RATE11) RATE11, MAX(SUCC_YN11) SUCC_YN11               \n");
        	sqlstr.append("      ,MAX(AMT12) AMT12, MAX(RATE12) RATE12, MAX(SUCC_YN12) SUCC_YN12               \n");
        	sqlstr.append("      ,MAX(AMT13) AMT13, MAX(RATE13) RATE13, MAX(SUCC_YN13) SUCC_YN13               \n");
        	sqlstr.append("      ,MAX(AMT14) AMT14, MAX(RATE14) RATE14, MAX(SUCC_YN14) SUCC_YN14               \n");
        	sqlstr.append("      ,MAX(AMT15) AMT15, MAX(RATE15) RATE15, MAX(SUCC_YN15) SUCC_YN15               \n");
        	sqlstr.append("      ,MAX(AMT16) AMT16, MAX(RATE16) RATE16, MAX(SUCC_YN16) SUCC_YN16               \n");
        	sqlstr.append("      ,MAX(AMT17) AMT17, MAX(RATE17) RATE17, MAX(SUCC_YN17) SUCC_YN17               \n");
        	sqlstr.append("      ,MAX(AMT18) AMT18, MAX(RATE18) RATE18, MAX(SUCC_YN18) SUCC_YN18               \n");
        	sqlstr.append("      ,MAX(AMT19) AMT19, MAX(RATE19) RATE19, MAX(SUCC_YN19) SUCC_YN19               \n");
        	sqlstr.append("      ,MAX(AMT20) AMT20, MAX(RATE20) RATE20, MAX(SUCC_YN20) SUCC_YN20               \n");
        	sqlstr.append("FROM (                                                                                                                     \n");
        	sqlstr.append("WITH TMP AS (                                                                                                              \n");
        	sqlstr.append("		SELECT A.BID_NO,                                                                                                      \n");
        	sqlstr.append("		       A.BID_DEG,                                                                                                     \n");
        	sqlstr.append("		       B1.ITEM_NAME || '/' || B2.ITEM_NAME || '/' || B3.ITEM_NAME ITEM_NAME,                                          \n");
        	sqlstr.append("		       C.VENDOR_NAME,                                                                                                 \n");
        	sqlstr.append("		       A.DEPT_NM,                                                                                                     \n");
        	sqlstr.append("		       A.DEPT_CD,                                                                                                     \n");
        	sqlstr.append("		       A.BID_MASTER_NM,                                                                                               \n"); 
        	sqlstr.append("		       ROUND(ESTI_TOT_AMT/100000000, 2) AMT,                                                                          \n");          
        	sqlstr.append("		       --DECODE(SUCC_YN, 'Y', '낙찰', '') SUCC_YN,                                                                     \n");
        	sqlstr.append("		       SUCC_YN,                                                                                                       \n");
        	sqlstr.append("		       rank() OVER(PARTITION BY A.BID_NO order by  C.ESTI_TOT_AMT Asc) || '/' || 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("		       B1.ITEM_CD ITEM_CD1,                                                                                           \n");
        	sqlstr.append("		       B2.ITEM_CD ITEM_CD2,                                                                                           \n");
        	sqlstr.append("		       B3.ITEM_CD ITEM_CD3,                                                                                           \n");
        	sqlstr.append("		       A.OUT_ITEM_CD OUT_ITEM_CD ,                                                                                     \n");
        	sqlstr.append("		       NVL(A.PAY_DIV, '1') PAY_DIV                                                                                      \n");
        	sqlstr.append("		  FROM BID_MASTER A,                                                                                                  \n");
        	sqlstr.append("		       OUT_ITEM B3,                                                                                                   \n");
        	sqlstr.append("		       OUT_ITEM B2,                                                                                                   \n");
        	sqlstr.append("		       OUT_ITEM B1,                                                                                                   \n");
        	sqlstr.append("		       BID_SUPP 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("		  WHERE A.OUT_ITEM_CD = B3.ITEM_CD                                                                                    \n");
        	sqlstr.append("		    AND B2.ITEM_CD = SUBSTR(B3.ITEM_CD,1,4)                                                                           \n");
        	sqlstr.append("		    AND B1.ITEM_CD = SUBSTR(B3.ITEM_CD,1,2)                                                                           \n");
        	sqlstr.append("		    AND B1.DEPTH = 1 AND B2.DEPTH = 2 AND B3.DEPTH = 3                                                                \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 = '10'                                                                                            \n");
        	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");
        	sqlstr.append("SELECT B.ITEM_NAME, B.VENDOR_NAME ,B.OUT_ITEM_CD                                                                           \n");
        	sqlstr.append("		, DECODE(A.CNT, 1, B.AMT) AMT1,  DECODE(A.CNT, 1, ACT_RATE)  RATE1,   DECODE(A.CNT, 1, SUCC_YN) SUCC_YN1        \n");
        	sqlstr.append("		, DECODE(A.CNT, 2, B.AMT) AMT2,  DECODE(A.CNT, 2, ACT_RATE)  RATE2,   DECODE(A.CNT, 2, SUCC_YN) SUCC_YN2        \n");
        	sqlstr.append("		, DECODE(A.CNT, 3, B.AMT) AMT3,  DECODE(A.CNT, 3, ACT_RATE)  RATE3,   DECODE(A.CNT, 3, SUCC_YN) SUCC_YN3        \n");
        	sqlstr.append("		, DECODE(A.CNT, 4, B.AMT) AMT4,  DECODE(A.CNT, 4, ACT_RATE)  RATE4,   DECODE(A.CNT, 4, SUCC_YN) SUCC_YN4        \n");
        	sqlstr.append("		, DECODE(A.CNT, 5, B.AMT) AMT5,  DECODE(A.CNT, 5, ACT_RATE)  RATE5,   DECODE(A.CNT, 5, SUCC_YN) SUCC_YN5        \n");
        	sqlstr.append("		, DECODE(A.CNT, 6, B.AMT) AMT6,  DECODE(A.CNT, 6, ACT_RATE)  RATE6,   DECODE(A.CNT, 6, SUCC_YN) SUCC_YN6        \n");
        	sqlstr.append("		, DECODE(A.CNT, 7, B.AMT) AMT7,  DECODE(A.CNT, 7, ACT_RATE)  RATE7,   DECODE(A.CNT, 7, SUCC_YN) SUCC_YN7        \n");
        	sqlstr.append("		, DECODE(A.CNT, 8, B.AMT) AMT8,  DECODE(A.CNT, 8, ACT_RATE)  RATE8,   DECODE(A.CNT, 8, SUCC_YN) SUCC_YN8        \n");
        	sqlstr.append("		, DECODE(A.CNT, 9, B.AMT) AMT9,  DECODE(A.CNT, 9, ACT_RATE)  RATE9,   DECODE(A.CNT, 9, SUCC_YN) SUCC_YN9        \n");
        	sqlstr.append("		, DECODE(A.CNT, 10, B.AMT) AMT10,  DECODE(A.CNT, 10, ACT_RATE)  RATE10,   DECODE(A.CNT, 10, SUCC_YN) SUCC_YN10  \n");
        	sqlstr.append("		, DECODE(A.CNT, 11, B.AMT) AMT11,  DECODE(A.CNT, 11, ACT_RATE)  RATE11,   DECODE(A.CNT, 11, SUCC_YN) SUCC_YN11  \n");
        	sqlstr.append("		, DECODE(A.CNT, 12, B.AMT) AMT12,  DECODE(A.CNT, 12, ACT_RATE)  RATE12,   DECODE(A.CNT, 12, SUCC_YN) SUCC_YN12  \n");
        	sqlstr.append("		, DECODE(A.CNT, 13, B.AMT) AMT13,  DECODE(A.CNT, 13, ACT_RATE)  RATE13,   DECODE(A.CNT, 13, SUCC_YN) SUCC_YN13  \n");
        	sqlstr.append("		, DECODE(A.CNT, 14, B.AMT) AMT14,  DECODE(A.CNT, 14, ACT_RATE)  RATE14,   DECODE(A.CNT, 14, SUCC_YN) SUCC_YN14  \n");
        	sqlstr.append("		, DECODE(A.CNT, 15, B.AMT) AMT15,  DECODE(A.CNT, 15, ACT_RATE)  RATE15,   DECODE(A.CNT, 15, SUCC_YN) SUCC_YN15  \n");
        	sqlstr.append("		, DECODE(A.CNT, 16, B.AMT) AMT16,  DECODE(A.CNT, 16, ACT_RATE)  RATE16,   DECODE(A.CNT, 16, SUCC_YN) SUCC_YN16  \n");
        	sqlstr.append("		, DECODE(A.CNT, 17, B.AMT) AMT17,  DECODE(A.CNT, 17, ACT_RATE)  RATE17,   DECODE(A.CNT, 17, SUCC_YN) SUCC_YN17  \n");
        	sqlstr.append("		, DECODE(A.CNT, 18, B.AMT) AMT18,  DECODE(A.CNT, 18, ACT_RATE)  RATE18,   DECODE(A.CNT, 18, SUCC_YN) SUCC_YN18  \n");
        	sqlstr.append("		, DECODE(A.CNT, 19, B.AMT) AMT19,  DECODE(A.CNT, 19, ACT_RATE)  RATE19,   DECODE(A.CNT, 19, SUCC_YN) SUCC_YN19  \n");
        	sqlstr.append("		, DECODE(A.CNT, 20, B.AMT) AMT20,  DECODE(A.CNT, 20, ACT_RATE)  RATE20,   DECODE(A.CNT, 20, SUCC_YN) SUCC_YN20  \n");
        	sqlstr.append("		FROM (SELECT DETAILCD CNT,DETAILSORTNM DEPT_CD, DETAILNM  DEPT_NM                                               \n");
        	sqlstr.append("		        FROM EBID_COM911T                                                                                       \n");
        	sqlstr.append("		       WHERE BASECD = 'BID_HIS_DEPT'                                                                            \n");
        	sqlstr.append("		       ORDER BY DETAILCD) A,                                                                                    \n");
        	sqlstr.append("		TMP B                                                                                                           \n");
        	sqlstr.append("		WHERE A.DEPT_CD = B.DEPT_CD                                                                                     \n");
        	sqlstr.append("     AND NVL(B.PAY_DIV, '1') LIKE '"+param.get("pay_div")+"%'                                         \n"); //2016.5.17
        	if(!param.get("searchCorp").isEmpty())
        		sqlstr.append("     AND UPPER(B.VENDOR_NAME) LIKE '%"+param.get("searchCorp").toUpperCase()+"%'                                         \n");
            	
            if(!param.get("outItemTag1").isEmpty()){
            	if(!param.get("outItemTag2").isEmpty()){
            		if(!param.get("outItemTag3").isEmpty())
            			sqlstr.append("     AND B.OUT_ITEM_CD = '"+param.get("outItemTag3")+"'                                      \n");
            		else
            			sqlstr.append("     AND B.OUT_ITEM_CD LIKE '"+param.get("outItemTag2")+"%'                                  \n");
            	}
            	else
            		sqlstr.append("     AND B.OUT_ITEM_CD LIKE '"+param.get("outItemTag1")+"%'                                      \n");
            }
            sqlstr.append("   ORDER BY  CNT                                      \n");
        	sqlstr.append(")                                                                                                        \n");
        	sqlstr.append("GROUP BY  ITEM_NAME, VENDOR_NAME , OUT_ITEM_CD                                                           \n");
        	sqlstr.append("ORDER BY    OUT_ITEM_CD , 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;
	}	
	
	public RowSet getOutItemRowSet(String depth) {
		Search search = new SearchImpl();
		RowSet sysRowSet = null;
		String sqlstr = "";
		try {
			sqlstr = " SELECT  ITEM_CD, ITEM_NAME		\n"
					+ " FROM   OUT_ITEM            	    \n"
					+ " WHERE DEPTH = ?	                \n"
					+ " ORDER BY ITEM_CD ASC            \n";
			
			search.setStatement(sqlstr);
			WAFLogger.debug(sqlstr.toString());
			search.addParameter(depth);
			sysRowSet = search.execute();
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> getOutItemRowSet() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return null;
		}
		finally {
			if( sysRowSet == null ) sysRowSet = new RowSetImpl();
			return sysRowSet;
		}
	}
	
	public RowSet getDeptNameRowSet() {
		Search search = new SearchImpl();
		RowSet sysRowSet = null;
		String sqlstr = "";
		try {
			sqlstr = " SELECT DETAILCD DEPT_CD, detailnm  DEPT_NM		\n"
					+ "  FROM EBID_COM911T            	                \n"
					+ " WHERE BASECD = 'BID_HIS_DEPT'                   \n"
					+ " ORDER BY  SORT_NO                               \n";
		
			search.setStatement(sqlstr);
			WAFLogger.debug(sqlstr.toString());
			sysRowSet = search.execute();
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> getDeptNameRowSet() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return null;
		}
		finally {
			if( sysRowSet == null ) sysRowSet = new RowSetImpl();
			return sysRowSet;
		}
	}	
}