/**
 * @(#)file    BidPlanBiz.java
 * @(#)author  Son Hyung Min 
 * @(#)version 1.0
 * @(#)date    2014-06-23
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.quot.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.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class QuotResultBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
        ValueObject vo_file_in = new ValueObject();
        ValueObject vo_file_out = new ValueObject();
		
		if(storage.compareEvent("SHEET_SELECT")){	//1. 견적 결과 리스트 조회
			params.set("sheet1", getBoardList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("REG_QUOT_DETAIL")){//2. 견적 현황 상세 등록
			
			ValueObject vo = new ValueObject();
			vo.set("DEPT_CD", params.get("p_dept_cd"));
			vo.set("DEPT_NM", params.get("p_dept_nm"));
			vo.set("QUOT_BID_NO", getQuotBidNo(params));        		
			vo.set("QUOT_BID_DEG", "1");	
			vo.set("STATUS_CD", "00");	//00 : 작성대상 , 01 : 작성중, 02 : 견적요청, 10 : 견적마감
			
			storage.addValueObject("Order", vo);
			
		}else if(storage.compareEvent("GET_QUOT_DETAIL")){//3. 견적 현황 상세 조회
			ValueObject vo = getOrderReqeustValueObject(params);
	        storage.addValueObject("Order", vo);
	         
	        /**		파일 목록 조회			 **/
	        RowSet rowSet = FileManager.getFileList("QUOT_FILE_IN_" + params.get("quot_id"), "");
	        RowSet rowSet2 = FileManager.getFileList("QUOT_FILE_OUT_" + params.get("quot_id"), "");
	        String str="";
	        String str2="";
		       for(int i=0;i<rowSet.size();i++){
		       	Row row=rowSet.getRow(i);
		       	str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
		       	if(i+1!=rowSet.size()){
		       		str +="\r\n";
		       	}
		       }
		       
		       for(int i=0;i<rowSet2.size();i++){
		       	Row row=rowSet2.getRow(i);
		       	str2 += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
		       	if(i+1!=rowSet2.size()){
		       		str2 +="\r\n";
		       	}
		       }
	        vo_file_in.set("DOC_ETC_IN", str);
	        vo_file_out.set("DOC_ETC_OUT", str2);
		}else if(storage.compareEvent("GET_CORP_LIST")){
			params.set("sheet1", getCorpList(params));
	        storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
		
		storage.addValueObject("DOC_ETC_IN", vo_file_in);
		storage.addValueObject("DOC_ETC_OUT", vo_file_out);	
	}
	
	private ValueObject getfilelist(String filegb,String id,String returnid){
		ValueObject vo = new ValueObject();
		RowSet rowSet = FileManager.getFileList(filegb, id);
		String str="";
	        for(int i=0;i<rowSet.size();i++){
	        	Row row=rowSet.getRow(i);
	        	str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
	        	if(i+1!=rowSet.size()){
	        		str +="\r\n";
	        	}
	        }
	        vo.set(returnid, str);
		return vo;
	}

	/**
	 * 견적현황 리스트를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getBoardList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("SELECT A.QUOT_BID_NO || '-' || A.QUOT_BID_DEG AS BID_ID,                 \n");
        	sqlstr.append("       A.QUOT_BID_NO,                                                    \n");
        	sqlstr.append("       A.QUOT_BID_DEG,                                                   \n");
        	sqlstr.append("       A.QUOT_BID_MASTER_NM,                                             \n");
        	sqlstr.append("       A.QUOT_ID,                                                        \n");
        	sqlstr.append("       TO_CHAR (A.REQUEST_DATE, 'YYYY-MM-DD') AS REQUEST_DATE,           \n");
        	sqlstr.append("       A.QUOT_TYPE,                                                      \n");
        	sqlstr.append("       A.DEPT_CD,                                                        \n");
        	sqlstr.append("       A.DEPT_NM,                                                        \n");
        	sqlstr.append("       TO_CHAR (A.START_WORK_DATE, 'YYYY-MM-DD') AS START_WORK_DATE,     \n");
        	sqlstr.append("       TO_CHAR (A.END_WORK_DATE, 'YYYY-MM-DD') AS END_WORK_DATE,         \n");
        	sqlstr.append("       TO_CHAR (A.QUOT_BID_FROM_DT, 'YYYY-MM-DD') AS QUOT_BID_FROM_DT,   \n");
        	sqlstr.append("       TO_CHAR (A.QUOT_BID_TO_DT, 'YYYY-MM-DD') AS QUOT_BID_TO_DT,       \n");
        	sqlstr.append("       QUOT_MAN_ID,                                                      \n");
        	sqlstr.append("       QUOT_MAN_NM,                                                      \n");
        	sqlstr.append("       A.STATUS_CD,                                                      \n");
        	sqlstr.append("       F_GET_CODE_NAME ('QUOT_STATUS_CD', STATUS_CD) AS STATUS_NM,        \n");
        	sqlstr.append("       (SELECT COUNT(*) FROM QUOT_BID_SUPP WHERE QUOT_BID_NO = A.QUOT_BID_NO AND QUOT_BID_DEG = A.QUOT_BID_DEG AND BID_STATUS_CD = '10')||'/'||(SELECT COUNT(*) FROM QUOT_BID_SUPP WHERE QUOT_BID_NO = A.QUOT_BID_NO AND QUOT_BID_DEG = A.QUOT_BID_DEG) SEND_CNT        \n");
        	sqlstr.append("  FROM QUOT_BID_MASTER A                                                 \n");
        	sqlstr.append(" WHERE 1=1                                                               \n");
        	sqlstr.append("   AND QUOT_KIND = 'O'                                                   \n");
        	if(!param.get("P_DEPT_CD").isEmpty())
        	        sqlstr.append("   AND A.DEPT_CD = '"+param.get("P_DEPT_CD")+"'																\n");
        	if(!param.get("CHG_CODE01").isEmpty())
    	        sqlstr.append("   AND A.QUOT_TYPE = '"+param.get("CHG_CODE01")+"'																\n");
        	if(!param.get("P_QUOT_BID_MASTER_NM").isEmpty())
        	        sqlstr.append("   AND UPPER(A.QUOT_BID_MASTER_NM) LIKE '%'|| '"+param.get("P_QUOT_BID_MASTER_NM").toUpperCase()+"' ||'%'	\n"); 
        	if(!param.get("p_request_from_dt").isEmpty())
        	        sqlstr.append("   AND A.REQUEST_DATE >=  TO_DATE('"+param.get("p_request_from_dt")+"'||'000000','YYYY-MM-DDHH24MISS') 		\n");
        	if(!param.get("p_request_to_dt").isEmpty())
        	        sqlstr.append("   AND A.REQUEST_DATE <= TO_DATE('"+param.get("p_request_to_dt")+"'||'235959','YYYY-MM-DDHH24MISS') 			\n");
        	if(!param.get("P_QUOT_MAN_NM").isEmpty())
        			sqlstr.append("   AND UPPER(A.QUOT_MAN_NM) LIKE '%'|| '"+param.get("P_QUOT_MAN_NM").toUpperCase()+"' ||'%'	\n");
        	sqlstr.append("   AND A.STATUS_CD = '10'																							\n");
        	        
        	sqlstr.append("   ORDER BY QUOT_BID_TO_DT DESC																						\n");
			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;
	}

	/**
	 * 견적번호 채번하여 리턴
	 * @param Params
	 * @return
	 */
	public String getQuotBidNo(ValueObject params) throws Exception {
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		StringBuffer sql = new StringBuffer();
		String returnValue = "";

		try {

			sql.append("  SELECT DECODE (NVL (MAX (QUOT_BID_NO), 'NONE'), 'NONE', 'QUOT_' || '"+params.get("p_dept_cd")+"' || '_' || '0001',  \n"); 
			sql.append(" 		  'QUOT_'|| MAX (DEPT_CD)|| '_'|| LPAD (TO_NUMBER (SUBSTR (MAX (QUOT_BID_NO), -4))+1, 4, '0')) as quot_bid_no   \n"); 
			sql.append("    FROM QUOT_BID_MASTER                                                                                              \n");   
			sql.append("   WHERE DEPT_CD = '"+params.get("p_dept_cd")+"'                                                                      \n");    

			search.setStatement(sql.toString());
			rowSet = search.execute();
			if (rowSet.size() > 0) {
				returnValue = rowSet.getRow(0).get("QUOT_BID_NO");
			}
			return returnValue;
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getQuotBidNo() : \n"
					+ se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		} finally {
			return returnValue;
		}
	}
	
	/**
	 * 견적 현황 상세를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private ValueObject getOrderReqeustValueObject(ValueObject params) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("SELECT A.QUOT_BID_NO,                                                    \n");
        	sqlstr.append("       A.QUOT_BID_DEG,                                                   \n");
        	sqlstr.append("       A.QUOT_BID_MASTER_NM,                                             \n");
        	sqlstr.append("       A.QUOT_ID,                                                        \n");
        	sqlstr.append("       TO_CHAR (A.REQUEST_DATE, 'YYYY-MM-DD') AS REQUEST_DATE,           \n");
        	sqlstr.append("       A.QUOT_TYPE,                                                      \n");
        	sqlstr.append("       A.DEPT_CD,                                                        \n");
        	sqlstr.append("       A.DEPT_NM,                                                        \n");
        	sqlstr.append("       A.REQUEST_MAN_ID,                                                 \n");
        	sqlstr.append("       A.REQUEST_MAN_NM,                                                 \n");
        	sqlstr.append("       A.REQUEST_DEPT_NM,                                                \n");
        	sqlstr.append("       A.REQUEST_DEPT_CD,                                                \n");
        	sqlstr.append("       TO_CHAR (A.START_WORK_DATE, 'YYYY-MM-DD') AS START_WORK_DATE,     \n");
        	sqlstr.append("       TO_CHAR (A.END_WORK_DATE, 'YYYY-MM-DD') AS END_WORK_DATE,         \n");
        	sqlstr.append("       A.WORK_SIZE,                                                      \n");
        	sqlstr.append("       A.DEALER_NM,                                                      \n");
        	sqlstr.append("       A.SUPERVISOR,                                                     \n");
        	sqlstr.append("       A.QUOT_MAN_ID,                                                    \n");
        	sqlstr.append("       A.QUOT_MAN_NM,                                                    \n");
        	sqlstr.append("       A.QUOT_MAN_TELNO,                                                 \n");
        	sqlstr.append("       A.QUOT_MAN_EMAIL,                                                 \n");
        	sqlstr.append("       A.QUOT_MAN_FAX,                                                   \n");
        	
        	sqlstr.append("       A.QUOT_SITEDESC_FLAG,                                             \n");
        	sqlstr.append("       TO_CHAR(A.QUOT_SITEDESCDATE,'YYYY-MM-DD') AS QUOT_SITEDESCDATE,   \n");
        	sqlstr.append("       TO_CHAR(A.QUOT_SITEDESCDATE,'HH24') AS QUOT_SITEDESCDATE_HH, \n");
        	sqlstr.append("       TO_CHAR(A.QUOT_SITEDESCDATE,'MI') AS QUOT_SITEDESCDATE_MI,   \n");
        	sqlstr.append("       A.QUOT_SITEDESCPLACE,                                             \n");
        	
        	sqlstr.append("       A.QUOT_DEPT_CD,                                                   \n");
        	sqlstr.append("       A.QUOT_VALIDATE,                                                  \n");
        	sqlstr.append("       A.WORK_PLACE,                                                     \n");
        	sqlstr.append("       A.ITEM_NM,                                                        \n");
        	
        	sqlstr.append("       TO_CHAR(A.QUOT_BID_FROM_DT,'YYYY-MM-DD') AS QUOT_BID_FROM_DT,     \n");
        	sqlstr.append("       TO_CHAR(A.QUOT_BID_FROM_DT,'HH24') AS QUOT_BID_FROM_HH,           \n");
        	sqlstr.append("       TO_CHAR(A.QUOT_BID_FROM_DT,'MI') AS QUOT_BID_FROM_MI,             \n");
        	sqlstr.append("       TO_CHAR(A.QUOT_BID_TO_DT,'YYYY-MM-DD') AS QUOT_BID_TO_DT,         \n");
        	sqlstr.append("       TO_CHAR(A.QUOT_BID_TO_DT,'HH24') AS QUOT_BID_TO_HH,               \n");
        	sqlstr.append("       TO_CHAR(A.QUOT_BID_TO_DT,'MI') AS QUOT_BID_TO_MI,                 \n");
        	
        	sqlstr.append("       A.INNER_REMARKS,                                                  \n");
        	sqlstr.append("       A.OUTER_REMARKS,                                                  \n");
        	sqlstr.append("       A.DOC_ETC_IN,                                                     \n");
        	sqlstr.append("       A.DOC_ETC_OUT,                                                    \n");
        	sqlstr.append("       A.STATUS_CD,                                                      \n");
        	sqlstr.append("       F_GET_CODE_NAME ('QUOT_STATUS_CD', STATUS_CD) AS STATUS_NM,       \n");
        	sqlstr.append("       A.URL_COL,                                                         \n");
        	sqlstr.append("       (select max(quot_bid_deg) from quot_bid_master where quot_bid_no = a.quot_bid_no) as max_quot_bid_deg,	\n");
        	sqlstr.append("       (select status_cd from quot_bid_master where (quot_bid_no,quot_bid_deg) in(select quot_bid_no,max(quot_bid_deg) from quot_bid_master where quot_bid_no = a.quot_bid_no group by quot_bid_no)) max_status_cd	\n");
        	sqlstr.append("  FROM QUOT_BID_MASTER A                                                 \n");
        	sqlstr.append(" WHERE 1=1                                                               \n");
        	sqlstr.append("   AND A.QUOT_ID = '"+params.get("QUOT_ID")+"'                         \n");
        	search.setStatement(sqlstr.toString());
        	
            rowSet = search.execute();
            
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
	}
	
	/**
	 * 견적대상업체 를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getCorpList(ValueObject params) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT A.QUOT_BID_NO,					\n");
        	sqlstr.append("        A.QUOT_BID_DEG,					\n");
        	sqlstr.append("        A.VENDOR_CD,						\n");
        	sqlstr.append("        A.VENDOR_NO,						\n");
        	sqlstr.append("        A.VENDOR_NAME,					\n");
        	sqlstr.append("        A.REPRE_NO,						\n");
        	sqlstr.append("        A.CHARGE_NAME,					\n");
        	sqlstr.append("        A.CHAGE_HP,						\n");
        	sqlstr.append("        A.CHARGE_EMAIL,					\n");
        	sqlstr.append("        A.BID_STATUS_CD,					\n");
        	sqlstr.append("        A.ATT_ID,						\n");
        	sqlstr.append("        A.FILE_GB,						\n");
        	sqlstr.append("        A.REMARK,						\n");
        	sqlstr.append("        DECODE(A.FILE_GB,'','','[다운로드]') as FILE_DOWN,			\n");
        	sqlstr.append("        DECODE(BID_STATUS_CD,'10',TO_CHAR (A.ESTI_TOT_AMT, '999,999,999,999,999'),'') AS ESTI_TOT_AMT,	\n");
        	sqlstr.append("        DECODE(A.SUBMIT_DT,'','미제출',TO_CHAR(A.SUBMIT_DT,'YYYY-MM-DD HH24:MI:SS')) AS SUBMIT_DT,			\n");
        	sqlstr.append("        A.CRE_BY,						\n");
        	sqlstr.append("        A.CRE_DATE,						\n");
        	sqlstr.append("        A.UPD_BY,						\n");
        	sqlstr.append("        A.UPD_DATE,						\n");
        	sqlstr.append("        B.CORP_TEL  TEL_NUM,				\n");
        	sqlstr.append("        A.SITE_ATTEND_YN,				\n");
        	sqlstr.append("        A.SITEPARTI_YN,					\n");
        	sqlstr.append("        A.SITE_ATTEND_REASON				\n");
        	sqlstr.append("   FROM QUOT_BID_SUPP A					\n");
        	sqlstr.append("   	  , SUPP_INFO B						\n");
        	sqlstr.append(" WHERE QUOT_BID_NO = '"+params.get("QUOT_BID_NO")+"'		\n");
        	sqlstr.append("   AND QUOT_BID_DEG = '"+params.get("QUOT_BID_DEG")+"'	\n");
        	sqlstr.append("   AND A.VENDOR_CD=B.CORP_ID								\n");
        	System.out.println(sqlstr);
			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;
	}	
}