/**
 * @(#)file    QuotPlanSaveBiz.java
 * @(#)author  hooonseok choi
 * @(#)date    2023.01.19
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.quot.purchase.biz;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.logger.Logger;
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.sender.MessageSend;
import oracle.toplink.internal.ox.StrBuffer;

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

public class QuotPurPlanSaveBiz extends Biz { 

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

	public void doBiz() throws Exception {
		boolean success = false;
		
		try {
			super.conn.begin();

			Connection connection = super.conn.getConnection();

			if (storage.compareEvent("SAVE_QUOT_DETAIL")) {  // 견적생성
				ValueObject voQuotId = getQuotId(super.conn.getConnection());
				
				String status_cd = params.get("STATUS_CD");
				if(status_cd.equals("00")){
					//최초 견적생성
				}else{	
					//견적복사시 quot_bid_no생성
					params.set("QUOT_BID_NO", getQuotBidNo(params));	
				}
				
				ValueObject voQuotBidDeg = getQuotBidDeg(super.conn.getConnection(),params);
				params.set("quot_id", voQuotId.get("quot_id"));
				params.set("quot_bid_deg", voQuotBidDeg.get("quot_bid_deg"));
				success = saveQuotDetail(connection, params);
				super.storage.setSuccess(success);
				//success = detailSave(connection, params); 
				//storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request)); 
			} else if (storage.compareEvent("UPDATE_QUOT_DETAIL")) {  //저장
				success = updateQuotDetail(connection, params);
				super.storage.setSuccess(success);
			} else if (storage.compareEvent("SEND_QUOT_DETAIL")) {  //견적요청
				String fileCnt = getFileCnt(params);
	            if( "0".equals(fileCnt) ){
	            	super.storage.setSuccess(false);
	            	storage.setMessage("첨부파일을 등록해 주시기 바랍니다.");
	            	return;
	            }else{
	            	success = updateQuotDetail(connection, params);
					if(success){					
						RowSet SuppInfo = quot_bid_supp_info(params);
						ValueObject email_info = email_info(params);
						
						MessageSend messageSend = new MessageSend(storage);
			     		StringBuffer mailtitle = new StringBuffer();
			     		StringBuffer mailtitlein = new StringBuffer();
			            StringBuffer mailContent = new StringBuffer();
			            StringBuffer mailtitle2 = new StringBuffer();
			     		StringBuffer mailtitlein2 = new StringBuffer();
			            StringBuffer mailContent2 = new StringBuffer();
			            StringBuffer mailtitle3 = new StringBuffer();
			     		StringBuffer mailtitlein3 = new StringBuffer();
			            StringBuffer mailContent3 = new StringBuffer();
			            String attc_url = "https://ebid.sgcenc.co.kr/common/file/FileListNew.screen?pop_win_id=_viewFileList&extFunction=viewFileListClose&file_gb=QUOT_FILE_OUT_"+email_info.get("QUOT_ID");
			            
			            //1. 협력업체포탈 등록업체 메일양식
			            mailtitle.append("* 견적서(견적지원) 제출요청에 대한 공지 메일입니다.");
			            mailtitlein.append("* 견적서(견적지원) 제출요청에 대한 공지 메일입니다.");
			         	mailContent.append("아래와 같은 내용으로 견적서 제출을 요청드립니다.<br>협력업체포탈시스템에 접속하시어 견적서 및 자료 제출바랍니다.<br><br>");
			         	mailContent.append("※ 본 안내는 당사가 진행하는 입찰에 대한 <font color='red'>견적지원</font>입니다.<br>공사발주를 위한 안내가 아님을 유의해 주시기 바랍니다.<br>");
			         	mailContent.append("※ 접속경로 : 협력업체포탈시스템 접속 → 견적지원<br>");
			        	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
			        	mailContent.append("<br>");
			        	mailContent.append("1.프로젝트 : " + email_info.get("DEPT_NM") + "<br>");
			         	mailContent.append("2.공고번호 : " + email_info.get("QUOT_BID_NO") + "<br>");
			         	mailContent.append("3.공고차수 : " + email_info.get("QUOT_BID_DEG") + "차"+"<br>");
			         	mailContent.append("4.공고건명 : " + email_info.get("QUOT_BID_MASTER_NM") + "<br>");
			         	mailContent.append("5.견적마감일시 : " + email_info.get("QUOT_BID_TO_DT") + "<br>");
			         	mailContent.append("6.견적담당자 : " + email_info.get("QUOT_MAN_NM")+" "+ email_info.get("QUOT_GRADE_NAME") +" (연락처 : " + email_info.get("QUOT_MAN_TELNO") + ", 이메일 : " + email_info.get("QUOT_MAN_EMAIL") +")<br>");
			         	
			         	if(params.get("QUOT_SITEDESC_FLAG").equals("Y")){
			         		mailContent.append("7.견적설명회 일시 : " + email_info.get("QUOT_SITEDESCDATE") + "<br>");
			         		mailContent.append("8.견적설명회 장소 : " + email_info.get("QUOT_SITEDESCPLACE") + "<br>");
			         	}
			         	mailContent.append("<br>");
			         	mailContent.append("※ 견적조건");
			         	mailContent.append("<br>");
			         	mailContent.append("<font color='blue'>1. 특이사항</font>"+ "<br>");
			         	mailContent.append(email_info.get("OUTER_REMARKS").replaceAll("\n", "<BR>") + "<br>");
			         	mailContent.append("<a href=\""+attc_url+"\">"+"<font color='blue'>2. 첨부자료 LINK Click</font></a><br>");
			         	if(email_info.get("URL_COL") != "")	mailContent.append("<a href=\""+email_info.get("URL_COL")+"\">"+"<font color='blue'>3. 대용량 첨부파일 LINK Click</font></a><br>");			         	
			         	mailContent.append("</font>");
			         	
			         	//2. 견적용 등록업체 메일양식
			            mailtitle2.append("* 견적서(견적지원) 제출요청에 대한 공지 메일입니다.");
			            mailtitlein2.append("* 견적서(견적지원) 제출요청에 대한 공지 메일입니다.");
			         	mailContent2.append("아래와 같은 내용으로 견적서 제출을 요청드립니다.<br>");
			         	mailContent2.append("※ 본 안내는 당사가 진행하는 입찰에 대한 <font color='red'>견적지원</font>입니다.<br>공사발주를 위한 안내가 아님을 유의해 주시기 바랍니다.<br>");
			         	mailContent2.append("※ 아래 견적담당자에게 회신해 주시기 바랍니다.<br>");
			        	mailContent2.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
			        	mailContent2.append("<br>");
			        	mailContent2.append("1.프로젝트 : " + email_info.get("DEPT_NM") + "<br>");
			         	mailContent2.append("2.공고번호 : " + email_info.get("QUOT_BID_NO") + "<br>");
			         	mailContent2.append("3.공고차수 : " + email_info.get("QUOT_BID_DEG") + "차"+"<br>");
			         	mailContent2.append("4.공고건명 : " + email_info.get("QUOT_BID_MASTER_NM") + "<br>");
			         	mailContent2.append("5.견적마감일시 : " + email_info.get("QUOT_BID_TO_DT") + "<br>");
			         	mailContent2.append("6.견적담당자 : " + email_info.get("QUOT_MAN_NM")+" "+ email_info.get("QUOT_GRADE_NAME") +" (연락처 : " + email_info.get("QUOT_MAN_TELNO") + ", 이메일 : " + email_info.get("QUOT_MAN_EMAIL") +")<br>");
			         	
			         	if(params.get("QUOT_SITEDESC_FLAG").equals("Y")){
			         		mailContent2.append("7.견적설명회 일시 : " + email_info.get("QUOT_SITEDESCDATE") + "<br>");
			         		mailContent2.append("8.견적설명회 장소 : " + email_info.get("QUOT_SITEDESCPLACE") + "<br>");
			         	}
			         	mailContent2.append("<br>");
			         	mailContent2.append("※ 견적조건");
			         	mailContent2.append("<br>");
			         	mailContent2.append("<font color='blue'>1. 특이사항</font>"+ "<br>");
			         	mailContent2.append(email_info.get("OUTER_REMARKS").replaceAll("\n", "<BR>") + "<br>");
			         	mailContent2.append("<a href=\""+attc_url+"\">"+"<font color='blue'>2. 첨부자료 LINK Click</font></a><br>");
			         	if(email_info.get("URL_COL") != "")	mailContent2.append("<a href=\""+email_info.get("URL_COL")+"\">"+"<font color='blue'>3. 대용량 첨부파일 LINK Click</font></a><br>");			         	
			         	mailContent2.append("</font>");
			         	
			         	//3. 견적용 등록업체 메일양식(해외업체)
			            mailtitle3.append("* This is a notification email regarding a request to submit a quotation (quotation support).");
			            mailtitlein3.append("* REQUEST TO SUBMIT A QUOTATION.");
			         	mailContent3.append("We request that you submit a quotation with the following information.<br>");
			         	mailContent3.append("※ This guide provides <font color='red'>quotation support</font> for the bidding conducted by our company.<br>");
			         	mailContent3.append("  Please note that this is not guidance for placing construction orders.<br>");
			         	mailContent3.append("※ Please reply to the person in charge of estimating below.<br>");
			        	mailContent3.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
			        	mailContent3.append("<br>");
			        	mailContent3.append("1.Estimator : " + email_info.get("QUOT_MAN_NM_E")+" (TEL : " + email_info.get("QUOT_MAN_TELNO") + ", E-MAIL : " + email_info.get("QUOT_MAN_EMAIL") +")<br>");
			         	mailContent3.append("2.Quote Deadline : " + email_info.get("QUOT_BID_TO_DT") + "<br>");

			         	mailContent3.append("<br>");
			         	mailContent3.append("※ Quotation conditions");
			         	mailContent3.append("<br>");
			         	mailContent3.append("<font color='blue'>1. Information</font>"+ "<br>");
			         	mailContent3.append(email_info.get("INNER_REMARKS").replaceAll("\n", "<BR>") + "<br>");
			         	mailContent3.append("<a href=\""+attc_url+"\">"+"<font color='blue'>2. Attachment File LINK Click</font></a><br>");
			         	if(email_info.get("URL_COL") != "")	mailContent3.append("<a href=\""+email_info.get("URL_COL")+"\">"+"<font color='blue'>3. Bulk Attachment File LINK Click</font></a><br>");
			         	mailContent3.append("</font>");
			         	
			         	for (int i = 0; i < SuppInfo.size(); i++) {
			         		Row row=SuppInfo.getRow(i);
			         		if(row.get("IN_OUT_DIV").equals("O")){
			         			if(row.get("CHARGE_EMAIL") != "")	messageSend.sendEmail(row.get("CHARGE_EMAIL"), "partner@sgc.co.kr", mailtitle3.toString(), mailtitlein3.toString(), mailContent3,"NO_LINK");
			         		}else{
			         			if(row.get("ON_SUPP_YN").equals("Y")){
				         			if(row.get("CHARGE_EMAIL") != "")	messageSend.sendEmail(row.get("CHARGE_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent);
				         			if(row.get("CHAGE_HP") != "")		messageSend.sendSMS(row.get("CHAGE_HP"),"", email_info.get("QUOT_MAN_ID"), "견적서(견적지원) 제출바랍니다.'||CHR(13)||CHR(10)||'협력업체포탈시스템에 접속/확인바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)");
				         		}else{
				         			if(row.get("CHARGE_EMAIL") != "" )	messageSend.sendEmail(row.get("CHARGE_EMAIL"), "partner@sgc.co.kr", mailtitle2.toString(), mailtitlein2.toString(), mailContent2,"NO_LINK");
				         			if(row.get("CHAGE_HP") != "" )		messageSend.sendSMS(row.get("CHAGE_HP"),"", email_info.get("QUOT_MAN_ID"), "견적서(견적지원) 제출바랍니다.'||CHR(13)||CHR(10)||'메일 확인바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)");
				         		}
			         		}
			         	}
			         	super.storage.setSuccess(true);
					}
	            }
			} else if (storage.compareEvent("SAVE_SUPP_DETAIL") || storage.compareEvent("SAVE_DETAIL")){	//업체등록(등록,수정,삭제)
				success = detailSave(connection, params);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} else if (storage.compareEvent("FORM_DELETE")) {		//견적삭제
            	QUOT_BID_INFO_DEL(conn.getConnection(), params);
			} else if (storage.compareEvent("SET_CORP_DELETE")) {	//업체삭제
				success = detailDel(connection, params);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} else if (storage.compareEvent("RESEND_QUOT_DETAIL")) {  //SMS, E-MAIL 재요청
				ValueObject email_info = email_info(params);
				
				MessageSend messageSend = new MessageSend(storage);
	     		StringBuffer mailtitle = new StringBuffer();
	     		StringBuffer mailtitlein = new StringBuffer();
	            StringBuffer mailContent = new StringBuffer();
	            StringBuffer mailtitle2 = new StringBuffer();
	     		StringBuffer mailtitlein2 = new StringBuffer();
	            StringBuffer mailContent2 = new StringBuffer();
	            StringBuffer mailtitle3 = new StringBuffer();
	     		StringBuffer mailtitlein3 = new StringBuffer();
	            StringBuffer mailContent3 = new StringBuffer();
	            String attc_url = "https://ebid.sgcenc.co.kr/common/file/FileListNew.screen?pop_win_id=_viewFileList&extFunction=viewFileListClose&file_gb=QUOT_FILE_OUT_"+email_info.get("QUOT_ID");
	            
	            //1. 협력업체포탈 등록업체 메일양식
	            mailtitle.append("* 견적서(견적지원) 제출요청에 대한 공지 메일입니다.");
	            mailtitlein.append("* 견적서(견적지원) 제출요청에 대한 공지 메일입니다.");
	         	mailContent.append("아래와 같은 내용으로 견적서 제출을 요청드립니다.<br>협력업체포탈시스템에 접속하시어 견적서 및 자료 제출바랍니다.<br><br>");
	         	mailContent.append("※ 본 안내는 당사가 진행하는 입찰에 대한 <font color='red'>견적지원</font>입니다.<br>공사발주를 위한 안내가 아님을 유의해 주시기 바랍니다.<br>");
	         	mailContent.append("※ 접속경로 : 협력업체포탈시스템 접속 → 견적지원<br>");
	        	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	        	mailContent.append("<br>");
	         	mailContent.append("1.프로젝트 : " + email_info.get("DEPT_NM") + "<br>");
	         	mailContent.append("2.공고번호 : " + email_info.get("QUOT_BID_NO") + "<br>");
	         	mailContent.append("3.공고차수 : " + email_info.get("QUOT_BID_DEG") + "차"+"<br>");
	         	mailContent.append("4.공고건명 : " + email_info.get("QUOT_BID_MASTER_NM") + "<br>");
	         	mailContent.append("5.견적마감일시 : " + email_info.get("QUOT_BID_TO_DT") + "<br>");
	         	mailContent.append("6.견적담당자 : " + email_info.get("QUOT_MAN_NM")+" "+ email_info.get("QUOT_GRADE_NAME") +" (연락처 : " + email_info.get("QUOT_MAN_TELNO") + ", 이메일 : " + email_info.get("QUOT_MAN_EMAIL") +")<br>");
	         	
	         	if(params.get("QUOT_SITEDESC_FLAG").equals("Y")){
	         		mailContent.append("7.견적설명회 일시 : " + email_info.get("QUOT_SITEDESCDATE") + "<br>");
	         		mailContent.append("8.견적설명회 장소 : " + email_info.get("QUOT_SITEDESCPLACE") + "<br>");
	         	}
	         	
	         	mailContent.append("<br>");
	         	mailContent.append("※ 견적조건");
	         	mailContent.append("<br>");
	         	mailContent.append("<font color='blue'>1. 특이사항</font>"+ "<br>");
	         	mailContent.append(email_info.get("OUTER_REMARKS").replaceAll("\n", "<BR>") + "<br>");
	         	mailContent.append("<a href=\""+attc_url+"\">"+"<font color='blue'>2. 첨부자료 LINK Click</font></a><br>");
	         	if(email_info.get("URL_COL") != "")	mailContent.append("<a href=\""+email_info.get("URL_COL")+"\">"+"<font color='blue'>3. 대용량 첨부파일 LINK Click</font></a><br>");			         	
	         	mailContent.append("</font>");
	         	
	         	//2. 견적용 등록업체  메일양식
	            mailtitle2.append("* 견적서(견적지원) 제출요청에 대한 공지 메일입니다.");
	            mailtitlein2.append("* 견적서(견적지원) 제출요청에 대한 공지 메일입니다.");
	         	mailContent2.append("아래와 같은 내용으로 견적서 제출을 요청드립니다.<br>");
	         	mailContent2.append("※ 본 안내는 당사가 진행하는 입찰에 대한 <font color='red'>견적지원</font>입니다.<br>공사발주를 위한 안내가 아님을 유의해 주시기 바랍니다.<br>");
	         	mailContent2.append("※ 아래 견적담당자에게 회신해 주시기 바랍니다.<br>");
	        	mailContent2.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	        	mailContent2.append("<br>");
	        	mailContent2.append("1.프로젝트 : " + email_info.get("DEPT_NM") + "<br>");
	         	mailContent2.append("2.공고번호 : " + email_info.get("QUOT_BID_NO") + "<br>");
	         	mailContent2.append("3.공고차수 : " + email_info.get("QUOT_BID_DEG") + "차"+"<br>");
	         	mailContent2.append("4.공고건명 : " + email_info.get("QUOT_BID_MASTER_NM") + "<br>");
	         	mailContent2.append("5.견적마감일시 : " + email_info.get("QUOT_BID_TO_DT") + "<br>");
	         	mailContent2.append("6.견적담당자 : " + email_info.get("QUOT_MAN_NM")+" "+ email_info.get("QUOT_GRADE_NAME") +" (연락처 : " + email_info.get("QUOT_MAN_TELNO") + ", 이메일 : " + email_info.get("QUOT_MAN_EMAIL") +")<br>");
	         	
	         	if(params.get("QUOT_SITEDESC_FLAG").equals("Y")){
	         		mailContent2.append("7.견적설명회 일시 : " + email_info.get("QUOT_SITEDESCDATE") + "<br>");
	         		mailContent2.append("8.견적설명회 장소 : " + email_info.get("QUOT_SITEDESCPLACE") + "<br>");
	         	}
	         	mailContent2.append("<br>");
	         	mailContent2.append("※ 견적조건");
	         	mailContent2.append("<br>");
	         	mailContent2.append("<font color='blue'>1. 특이사항</font>"+ "<br>");
	         	mailContent2.append(email_info.get("OUTER_REMARKS").replaceAll("\n", "<BR>") + "<br>");
	         	mailContent2.append("<a href=\""+attc_url+"\">"+"<font color='blue'>2. 첨부자료 LINK Click</font></a><br>");
	         	if(email_info.get("URL_COL") != "")	mailContent2.append("<a href=\""+email_info.get("URL_COL")+"\">"+"<font color='blue'>3. 대용량 첨부파일 LINK Click</font></a><br>");			         	
	         	mailContent2.append("</font>");
	         	
	         	//3. 견적용 등록업체 메일양식(해외업체)
	            mailtitle3.append("* This is a notification email regarding a request to submit a quotation (quotation support).");
	            mailtitlein3.append("* REQUEST TO SUBMIT A QUOTATION.");
	         	mailContent3.append("We request that you submit a quotation with the following information.<br>");
	         	mailContent3.append("※ This guide provides <font color='red'>quotation support</font> for the bidding conducted by our company.<br>");
	         	mailContent3.append("  Please note that this is not guidance for placing construction orders.<br>");
	         	mailContent3.append("※ Please reply to the person in charge of estimating below.<br>");
	        	mailContent3.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	        	mailContent3.append("<br>");
	        	mailContent3.append("1.Estimator : " + email_info.get("QUOT_MAN_NM_E")+" (TEL : " + email_info.get("QUOT_MAN_TELNO") + ", E-MAIL : " + email_info.get("QUOT_MAN_EMAIL") +")<br>");
	         	mailContent3.append("2.Quote Deadline : " + email_info.get("QUOT_BID_TO_DT") + "<br>");

	         	mailContent3.append("<br>");
	         	mailContent3.append("※ Quotation conditions");
	         	mailContent3.append("<br>");
	         	mailContent3.append("<font color='blue'>1. Information</font>"+ "<br>");
	         	mailContent3.append(email_info.get("INNER_REMARKS").replaceAll("\n", "<BR>") + "<br>");
	         	mailContent3.append("<a href=\""+attc_url+"\">"+"<font color='blue'>2. Attachment File LINK Click</font></a><br>");
	         	if(email_info.get("URL_COL") != "")	mailContent3.append("<a href=\""+email_info.get("URL_COL")+"\">"+"<font color='blue'>3. Bulk Attachment File LINK Click</font></a><br>");
	         	mailContent3.append("</font>");
	         	
	         	String[] status = super.gets("sheet1.STATUS");
	         	String[] HP = super.gets("sheet1.CHAGE_HP");
	         	String[] EMAIL = super.gets("sheet1.CHARGE_EMAIL");
	         	String[] ON_SUPP_YN = super.gets("sheet1.ON_SUPP_YN");
	         	String[] IN_OUT_DIV = super.gets("sheet1.IN_OUT_DIV");
	         	System.out.println("status.length : "+status.length);
	         	
	         	for (int i = 0; i < status.length; i++) {
	         		if( "D".equals(status[i]) )   {
	         			if("O".equals(IN_OUT_DIV[i])){
	         				if(EMAIL[i] != "")	messageSend.sendEmail(EMAIL[i], "partner@sgc.co.kr", mailtitle3.toString(), mailtitlein3.toString(), mailContent3,"NO_LINK");
	         			}else{
	         				if("Y".equals(ON_SUPP_YN[i])){
		         				if(EMAIL[i] != "")	messageSend.sendEmail(EMAIL[i], "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent);
		         				if(HP[i] != "")		messageSend.sendSMS(HP[i],"", email_info.get("QUOT_MAN_ID"), "견적서(견적지원) 제출바랍니다.'||CHR(13)||CHR(10)||'협력업체포탈시스템에 접속/확인바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)");
		         			}else{
		         				if(EMAIL[i] != "")	messageSend.sendEmail(EMAIL[i], "partner@sgc.co.kr", mailtitle2.toString(), mailtitlein2.toString(), mailContent2,"NO_LINK");
		         				if(HP[i] != "")		messageSend.sendSMS(HP[i],"", email_info.get("QUOT_MAN_ID"), "견적서(견적지원) 제출바랍니다.'||CHR(13)||CHR(10)||'메일 확인바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)");
		         			}
	         			}
	         		}
	         	}
	         	storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} else if (storage.compareEvent("STATUS_QUOT_DETAIL")) {  //상태변경
				success = change_quot_status(connection, params);
				super.storage.setSuccess(success);
			}
		} catch (Exception e) {
		    	System.out.println("doBiz" + e );
				WAFLogger.error(e);
				super.doException(e);
				storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
				super.storage.setMessage(e.getMessage());
				super.storage.setDetailMessage(e);

				super.conn.setRollback();
				super.storage.setSuccess(false);
		} finally {
			super.conn.end();
		}
	}
	
    private ValueObject getQuotId(Connection conn) throws Exception {
		
		Persistent persistent_q = null;
		
		ValueObject vo = new ValueObject();
		ValueObject row = null;
		String sqlstr = "";
		
		try{
			persistent_q = new PersistentImpl(conn);
			sqlstr = "SELECT QUOT_BID_MASTER_S.NEXTVAL QUOT_ID FROM DUAL";
			persistent_q.setStatement(sqlstr); 

			row = RowSetUtility.getValueObject(persistent_q.query().getRow(0));
			vo.set("quot_id", row.get("quot_id"));
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getQuotId() : " + e.getMessage());
			WAFLogger.error(e);
			throw e;
            
		}finally{
			if(vo == null) vo = new ValueObject();
		}
		
		return vo;
	}
    
    private ValueObject getQuotBidDeg(Connection conn, ValueObject params) throws Exception {
		
		Persistent persistent_q = null;
		
		ValueObject vo = new ValueObject();
		ValueObject row = null;
		String sqlstr = "";
		
		try{
			persistent_q = new PersistentImpl(conn);
			sqlstr = "SELECT NVL(MAX(QUOT_BID_DEG),0)+1 as quot_bid_deg FROM QUOT_BID_MASTER WHERE QUOT_BID_NO = '"+params.get("QUOT_BID_NO")+"'";
			persistent_q.setStatement(sqlstr); 

			row = RowSetUtility.getValueObject(persistent_q.query().getRow(0));
			vo.set("quot_bid_deg", row.get("quot_bid_deg"));
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getQuotBidDeg() : " + e.getMessage());
			WAFLogger.error(e);
			throw e;
            
		}finally{
			if(vo == null) vo = new ValueObject();
		}
		
		return vo;
	}
    
	public boolean saveQuotDetail(Connection connection, ValueObject params)	throws Exception {
		Persistent persistent = null;
		StringBuffer sql = new StringBuffer();
		
		sql.append("	INSERT INTO QUOT_BID_MASTER		\n");
		sql.append("	(                       		\n");
		sql.append("		QUOT_BID_NO           		\n");
		sql.append("		, QUOT_BID_DEG          	\n");
		sql.append("		, QUOT_ID             		\n");
		sql.append("		, QUOT_BID_MASTER_NM    	\n");
		sql.append("		, QUOT_TYPE      			\n");
		sql.append("		, DEPT_CD           		\n");
		sql.append("		, DEPT_NM        			\n");
		sql.append("		, REQUEST_DATE             	\n");
		sql.append("		, REQUEST_MAN_ID            \n");
		sql.append("		, REQUEST_MAN_NM         	\n");
		sql.append("		, REQUEST_DEPT_NM         	\n");
		sql.append("		, REQUEST_DEPT_CD         	\n");
		sql.append("		, START_WORK_DATE           \n");
		sql.append("		, END_WORK_DATE           	\n");
		sql.append("		, WORK_SIZE           		\n");
		sql.append("		, DEALER_NM           		\n");
		sql.append("		, SUPERVISOR           		\n");
		sql.append("		, QUOT_MAN_ID           	\n");
		sql.append("		, QUOT_MAN_NM           	\n");
		sql.append("		, QUOT_MAN_TELNO         	\n");
		sql.append("		, QUOT_MAN_EMAIL            \n");
		sql.append("		, QUOT_MAN_FAX         		\n");
		sql.append("		, QUOT_DEPT_CD              \n");
		sql.append("		, QUOT_VALIDATE        		\n");
		sql.append("		, WORK_PLACE              	\n");
		sql.append("		, ITEM_NM             		\n");
		sql.append("		, QUOT_BID_FROM_DT          \n");
		sql.append("		, QUOT_BID_TO_DT            \n");
		sql.append("		, INNER_REMARKS             \n");
		sql.append("		, OUTER_REMARKS             \n");
		sql.append("		, DOC_ETC_IN           		\n");
		sql.append("		, DOC_ETC_OUT             	\n");
		sql.append("		, URL_COL            		\n");
		sql.append("		, STATUS_CD            		\n");
		sql.append("		, QUOT_KIND            		\n");
		sql.append("		, QUOT_SITEDESCDATE  		\n");
		sql.append("		, QUOT_SITEDESCPLACE  		\n");
		sql.append("		, QUOT_SITEDESC_FLAG  		\n");
		sql.append("		, CRE_BY             		\n");
		sql.append("		, CRE_DATE             		\n");
		sql.append("	) VALUES (              		\n");
		sql.append("	'"+params.get("QUOT_BID_NO")+"'			\n");
		sql.append("	,'"+params.get("QUOT_BID_DEG")+"'		\n");
		sql.append("	,'"+params.get("QUOT_ID")+"'			\n");
		sql.append("	,'"+params.get("QUOT_BID_MASTER_NM")+"'	\n");
		sql.append("	,(SELECT CHG_CODE01 FROM EBID_COM911T WHERE BASECD = 'QUOT_DEPT' AND DETAILCD = '"+params.get("P_DEPT_CD")+"')			\n");
		sql.append("	,'"+params.get("P_DEPT_CD")+"'			\n");
		sql.append("	,'"+params.get("P_DEPT_NM")+"'			\n");
		sql.append("	,'"+params.get("REQUEST_DATE")+"'		\n");
		sql.append("	,'"+params.get("REQUEST_MAN_ID")+"'		\n");
		sql.append("	,'"+params.get("REQUEST_MAN_NM")+"'		\n");
		sql.append("	,'"+params.get("REQUEST_DEPT_NM")+"'	\n");
		sql.append("	,'"+params.get("REQUEST_DEPT_CD")+"'	\n");
		sql.append("	,'"+params.get("START_WORK_DATE")+"'	\n");
		sql.append("	,'"+params.get("END_WORK_DATE")+"'		\n");
		sql.append("	,'"+params.get("WORK_SIZE")+"'			\n");
		sql.append("	,'"+params.get("DEALER_NM")+"'			\n");
		sql.append("	,'"+params.get("SUPERVISOR")+"'			\n");
		sql.append("	,'"+params.get("QUOT_MAN_ID")+"'		\n");
		sql.append("	,'"+params.get("QUOT_MAN_NM")+"'		\n");
		sql.append("	,'"+params.get("QUOT_MAN_TELNO")+"'		\n");
		sql.append("	,'"+params.get("QUOT_MAN_EMAIL")+"'		\n");
		sql.append("	,'"+params.get("QUOT_MAN_FAX")+"'		\n");
		sql.append("	,'"+params.get("QUOT_DEPT_CD")+"'		\n");
		sql.append("	,'"+params.get("QUOT_VALIDATE")+"'		\n");
		sql.append("	,'"+params.get("WORK_PLACE")+"'			\n");
		sql.append("	,'"+params.get("ITEM_NM")+"'			\n");
//		sql.append("	,'"+params.get("QUOT_BID_FROM_DT")+"'	\n");
//		sql.append("	,'"+params.get("QUOT_BID_TO_DT")+"'		\n");
		sql.append("	, TO_DATE('"+StringUtils.replace(params.get("QUOT_BID_FROM_DT"),"-","")+"'||'"+params.get("QUOT_BID_FROM_HH")+"'||'"+params.get("QUOT_BID_FROM_MI")+"','YYYYMMDDHH24MI')			\n");
		sql.append("	, TO_DATE('"+StringUtils.replace(params.get("QUOT_BID_TO_DT"),"-","")+"'||'"+params.get("QUOT_BID_TO_HH")+"'||'"+params.get("QUOT_BID_TO_MI")+"','YYYYMMDDHH24MI')  	   			\n");
		sql.append("	,?		\n");
		sql.append("	,?		\n");
		sql.append("	,'"+params.get("DOC_ETC_IN")+"'			\n");
		sql.append("	,'"+params.get("DOC_ETC_OUT")+"'		\n");
		sql.append("	,?		\n");
		sql.append("	,'01'									\n");
		sql.append("	,'"+params.get("QUOT_KIND")+"'			\n");
		sql.append("	, TO_DATE('"+StringUtils.replace(params.get("QUOT_SITEDESCDATE"),"-","")+"'||'"+params.get("QUOT_SITEDESCDATE_HH")+"'||'"+params.get("QUOT_SITEDESCDATE_MI")+"','YYYYMMDDHH24MI')	\n");
		sql.append("	,'"+params.get("QUOT_SITEDESCPLACE")+"'	\n");
		sql.append("	,'"+params.get("QUOT_SITEDESC_FLAG")+"'	\n");
		sql.append("	,'"+storage.getUser().get("ACCT_ID")+"'	\n");
		sql.append("	,sysdate								\n");
		sql.append("	)                       				\n");
		try {
			persistent = new PersistentImpl(connection);
			persistent.setStatement(sql.toString());
			persistent.addParameter(params.get("INNER_REMARKS"));
			persistent.addParameter(params.get("OUTER_REMARKS"));
			persistent.addParameter(params.get("URL_COL"));
			WAFLogger.debug(sql.toString());
			persistent.execute();
			persistent.clearParameters();
		
			return true;
		} catch (Exception e) {
			throw e;
		}
	}
	
	public boolean detailSave(Connection connection, ValueObject params) throws Exception {
		try{
			String[] status = super.gets("SHEET1.status");
			boolean success = true;
			for (int i = 0; i < status.length; i++) {
				if (IBSheet7Utils.equalStatusInsert(status[i])) {
					success = saveQuotDetailSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
				if (IBSheet7Utils.equalStatusUpdate(status[i])) {
					success = updateQuotDetailSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
				if (IBSheet7Utils.equalStatusDelete(status[i])) {
					success = deleteQuotDetailSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
			}		 
			return success;
		} catch (Exception e) {
			System.out.println("detailSave" + e );
			throw e;
		}
	}

	public boolean detailDel(Connection connection, ValueObject params) throws Exception {
		try{
			String[] status = super.gets("SHEET1.status");
			boolean success = true;
			for (int i = 0; i < status.length; i++) {
				if (IBSheet7Utils.equalStatusDelete(status[i])) {
					success = deleteQuotDetailSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
			}		 
			return success;
		} catch (Exception e) {
			System.out.println("detailSave" + e );
			throw e;
		}
	}
	
	public boolean saveQuotDetailSheet(Connection connection,ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		String returnValue = "";
		try {	
			sql.append("	INSERT INTO QUOT_BID_SUPP			\n");
			sql.append("	(                              		\n");
			sql.append("		QUOT_BID_NO           			\n");
			sql.append("		, QUOT_BID_DEG                	\n");
			sql.append("		, VENDOR_CD                		\n");
			sql.append("		, VENDOR_NO                   	\n");
			sql.append("		, VENDOR_NAME                   \n");
			sql.append("		, REPRE_NO                     	\n");
			sql.append("		, CHARGE_NAME                	\n");
			sql.append("		, CHAGE_HP              		\n");
			sql.append("		, CHARGE_EMAIL                  \n");
			sql.append("		, SITEPARTI_YN                  \n");
			sql.append("		, ON_SUPP_YN                    \n");
			sql.append("		, IN_OUT_DIV                    \n");
			sql.append("		, FILE_GB                       \n");
			sql.append("		, CRE_BY                    	\n");
			sql.append("		, CRE_DATE                   	\n");
			sql.append("	) VALUES (                     		 \n");
			sql.append("	'"+params.get("QUOT_BID_NO")+"'      \n");
			sql.append("	,'"+params.get("QUOT_BID_DEG")+"'    \n");
			sql.append("	,'"+sqlParams.get("VENDOR_CD")+"'	 \n");
			sql.append("	,'"+sqlParams.get("VENDOR_NO")+"'	 \n");
			sql.append("	,'"+sqlParams.get("VENDOR_NAME")+"'	 \n");
			sql.append("	,'"+sqlParams.get("REPRE_NO")+"'   	 \n");
			sql.append("	,'"+sqlParams.get("CHARGE_NAME")+"'	 \n");
			sql.append("	,'"+sqlParams.get("CHAGE_HP")+"'     \n");
			sql.append("	,'"+sqlParams.get("CHARGE_EMAIL")+"' \n");
			sql.append("	,'"+sqlParams.get("SITEPARTI_YN")+"' \n");
			sql.append("	,'"+sqlParams.get("ON_SUPP_YN")+"'   \n");
			sql.append("	,'"+sqlParams.get("IN_OUT_DIV")+"'   \n");
			sql.append("	,'QUOT_SUPP_"+sqlParams.get("VENDOR_CD")+params.get("QUOT_ID")+"'  \n");
			sql.append("	,'"+storage.getUser().get("ACCT_ID")+"'	\n");
			sql.append("	,SYSDATE                       		\n");
			sql.append("	)                              		\n");
			pstmt = connection.prepareStatement(sql.toString());
			pstmt.executeUpdate(); 
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

	public boolean updateQuotDetailSheet(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		String returnValue = "";
		try {
			sql.append("   UPDATE QUOT_BID_SUPP SET 								\n");
			sql.append("		  VENDOR_NO = '"+sqlParams.get("VENDOR_NO")+"'   	\n");
			sql.append("		, VENDOR_NAME = '"+sqlParams.get("VENDOR_NAME")+"' 	\n");
			sql.append("		, REPRE_NO = '"+sqlParams.get("REPRE_NO")+"'       	\n");
			sql.append("		, CHARGE_NAME = '"+sqlParams.get("CHARGE_NAME")+"' 	\n");
			sql.append("		, CHAGE_HP = '"+sqlParams.get("CHAGE_HP")+"'       	\n");
			sql.append("		, CHARGE_EMAIL = '"+sqlParams.get("CHARGE_EMAIL")+"'\n");
			sql.append("		, SITEPARTI_YN = '"+sqlParams.get("SITEPARTI_YN")+"'\n");
			sql.append("		, ON_SUPP_YN = '"+sqlParams.get("ON_SUPP_YN")+"' 	\n");
			sql.append("		, IN_OUT_DIV = '"+sqlParams.get("IN_OUT_DIV")+"' 	\n");
			sql.append("		, UPD_BY = '"+storage.getUser().get("ACCT_ID")+"'	\n");
			sql.append("		, UPD_DATE = SYSDATE                 				\n");
			sql.append("	WHERE QUOT_BID_NO = '"+params.get("QUOT_BID_NO")+"'		\n");
			sql.append("	  AND QUOT_BID_DEG = '"+params.get("QUOT_BID_DEG")+"'	\n");
			sql.append("	  AND VENDOR_CD = '"+sqlParams.get("VENDOR_CD")+"'		\n");

			pstmt = connection.prepareStatement(sql.toString());
			pstmt.executeUpdate();
		
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

 
	public boolean deleteQuotDetailSheet(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		try {
			sql.append("   DELETE                      		    \n");
			sql.append("	 FROM QUOT_BID_SUPP   				\n");
			sql.append("	WHERE QUOT_BID_NO = '"+params.get("QUOT_BID_NO")+"'		\n");
			sql.append("	  AND QUOT_BID_DEG = '"+params.get("QUOT_BID_DEG")+"'	\n");
			sql.append("	  AND VENDOR_CD = '"+sqlParams.get("VENDOR_CD")+"'		\n");

			pstmt = connection.prepareStatement(sql.toString());
			pstmt.executeUpdate();
				
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

	public boolean updateQuotDetail(Connection connection, ValueObject params) throws Exception {
		Persistent persistent = null;
		StringBuffer sql = new StringBuffer();

		sql.append("	UPDATE QUOT_BID_MASTER SET 		\n");
		sql.append("		  QUOT_BID_MASTER_NM = '"+params.get("QUOT_BID_MASTER_NM")+"'	\n");
		sql.append("		, QUOT_TYPE = '"+params.get("QUOT_TYPE")+"'       				\n");
		sql.append("		, DEPT_CD = '"+params.get("P_DEPT_CD")+"'       				\n");
		sql.append("		, DEPT_NM = '"+params.get("P_DEPT_NM")+"'       				\n");
		sql.append("		, REQUEST_DATE = '"+params.get("REQUEST_DATE")+"'       		\n");
		sql.append("		, REQUEST_MAN_ID = '"+params.get("REQUEST_MAN_ID")+"'       	\n");
		sql.append("		, REQUEST_MAN_NM = '"+params.get("REQUEST_MAN_NM")+"'       	\n");
		sql.append("		, REQUEST_DEPT_NM = '"+params.get("REQUEST_DEPT_NM")+"'     	\n");
		sql.append("		, REQUEST_DEPT_CD = '"+params.get("REQUEST_DEPT_CD")+"'       	\n");
		sql.append("		, START_WORK_DATE = '"+params.get("START_WORK_DATE")+"'       	\n");
		sql.append("		, END_WORK_DATE = '"+params.get("END_WORK_DATE")+"'       		\n");
		sql.append("		, WORK_SIZE = '"+params.get("WORK_SIZE")+"'       				\n");
		sql.append("		, DEALER_NM = '"+params.get("DEALER_NM")+"'       				\n");
		sql.append("		, SUPERVISOR = '"+params.get("SUPERVISOR")+"'       			\n");
		sql.append("		, QUOT_MAN_ID = '"+params.get("QUOT_MAN_ID")+"'       			\n");
		sql.append("		, QUOT_MAN_NM = '"+params.get("QUOT_MAN_NM")+"'       			\n");
		sql.append("		, QUOT_MAN_TELNO = '"+params.get("QUOT_MAN_TELNO")+"'       	\n");
		sql.append("		, QUOT_MAN_EMAIL = '"+params.get("QUOT_MAN_EMAIL")+"'       	\n");
		sql.append("		, QUOT_MAN_FAX = '"+params.get("QUOT_MAN_FAX")+"'       		\n");
		sql.append("		, QUOT_SITEDESC_FLAG = '"+params.get("QUOT_SITEDESC_FLAG")+"'	\n");
		sql.append("		, QUOT_SITEDESCDATE=TO_DATE('"+StringUtils.replace(params.get("QUOT_SITEDESCDATE"),"-","")+"'||'"+params.get("QUOT_SITEDESCDATE_HH")+"'||'"+params.get("QUOT_SITEDESCDATE_MI")+"','YYYYMMDDHH24MI')     \n");
		sql.append("		, QUOT_SITEDESCPLACE= '"+params.get("QUOT_SITEDESCPLACE")+"'	\n");
		sql.append("		, QUOT_DEPT_CD = '"+params.get("QUOT_DEPT_CD")+"'       		\n");
		sql.append("		, QUOT_VALIDATE = '"+params.get("QUOT_VALIDATE")+"'       		\n");
		sql.append("		, WORK_PLACE = '"+params.get("WORK_PLACE")+"'       			\n");
		sql.append("		, ITEM_NM = '"+params.get("ITEM_NM")+"'       					\n");
		sql.append("		, QUOT_BID_FROM_DT = TO_DATE('"+StringUtils.replace(params.get("QUOT_BID_FROM_DT"),"-","")+"'||'"+params.get("QUOT_BID_FROM_HH")+"'||'"+params.get("QUOT_BID_FROM_MI")+"','YYYYMMDDHH24MI')     \n");
		sql.append("		, QUOT_BID_TO_DT = TO_DATE('"+StringUtils.replace(params.get("QUOT_BID_TO_DT"),"-","")+"'||'"+params.get("QUOT_BID_TO_HH")+"'||'"+params.get("QUOT_BID_TO_MI")+"','YYYYMMDDHH24MI')     		\n");
		sql.append("		, INNER_REMARKS = ?       										\n");
		sql.append("		, OUTER_REMARKS = ?       										\n");
		sql.append("		, DOC_ETC_IN = '"+params.get("DOC_ETC_IN")+"'       			\n");
		sql.append("		, DOC_ETC_OUT = '"+params.get("DOC_ETC_OUT")+"'       			\n");
		sql.append("		, URL_COL = ?       											\n");
		sql.append("		, STATUS_CD = '"+params.get("STATUS_CD")+"'       				\n");
		sql.append("		, UPD_BY = '"+storage.getUser().get("ACCT_ID")+"'       		\n");
		sql.append("		, UPD_DATE = sysdate       										\n");
		sql.append("	WHERE QUOT_ID = '"+params.get("QUOT_ID")+"'   						\n");

		try {
			persistent = new PersistentImpl(connection);
			persistent.setStatement(sql.toString());
			persistent.addParameter(params.get("INNER_REMARKS"));
			persistent.addParameter(params.get("OUTER_REMARKS"));
			persistent.addParameter(params.get("URL_COL"));
//			WAFLogger.debug(sql.toString());
			persistent.execute();
			persistent.clearParameters();

			return true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		}
	}
	
	/*private ValueObject getFileCnt(Connection conn, ValueObject params) throws Exception {
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		ValueObject user = storage.getUser();
		StrBuffer sqlstr = new StrBuffer();
		try{
			
			sqlstr.append(" select count(*) cnt                              \n");
			sqlstr.append("   from pub0500t a, pub0510t b                    \n");
			sqlstr.append("  where a.file_id = b.file_id                     \n");
			sqlstr.append("    and substr(b.file_gb,0,13) = 'QUOT_FILE_OUT'  \n");
			sqlstr.append("    and id = '"+params.get("QUOT_BID_NO")+"'      \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;
		}
	}*/
	
	private String getFileCnt(ValueObject params) {
        
        Search search = new SearchImpl();
        String sqlstr = "";
        try {
            sqlstr 	= 	"select NVL(count(*),0) CNT 							\n"
            		+	"  from pub0500t a, pub0510t b					\n"
		            +	"  where a.file_id = b.file_id					\n"
		            +	"  and substr(b.file_gb,0,13) = 'QUOT_FILE_OUT'	\n"
		            +	"  and id = '"+params.get("QUOT_ID")+"'		\n";

            search.setStatement(sqlstr);
            return search.execute().getRow(0).get("CNT");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileCnt() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }

	public void QUOT_BID_INFO_DEL(Connection connection, ValueObject params) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        Persistent persistent2 = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        StrBuffer sqlstr2 = new StrBuffer();
        try {
        	sqlstr.append("DELETE QUOT_BID_SUPP										\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");
        	
        	sqlstr2.append("DELETE QUOT_BID_MASTER									\n");
        	sqlstr2.append(" WHERE QUOT_BID_NO  = '"+params.get("QUOT_BID_NO")+"'	\n");
        	sqlstr2.append("   AND QUOT_BID_DEG = '"+params.get("QUOT_BID_DEG")+"'	\n");
        	
            persistent.setStatement(sqlstr.toString());
            persistent.execute();
            
            persistent2.setStatement(sqlstr2.toString());
            persistent2.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> QUOT_BID_INFO_DEL() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
	public ValueObject email_info(ValueObject param) {

		Search search = new SearchImpl();
		 ValueObject valueobject = null;
		RowSet rowSet = new RowSetImpl();
        
        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("       (SELECT USER_NAME_E FROM PUB0120T WHERE ACCT_ID = A.QUOT_MAN_ID) AS QUOT_MAN_NM_E,     \n");
        	sqlstr.append("       (SELECT GRADE_NAME FROM PUB0120T WHERE ACCT_ID =  A.QUOT_MAN_ID) AS QUOT_GRADE_NAME,   \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 HH24:MI') AS QUOT_SITEDESCDATE,   \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 HH24:MI') AS QUOT_BID_FROM_DT,   \n");
        	sqlstr.append("       TO_CHAR (A.QUOT_BID_TO_DT, 'YYYY-MM-DD HH24:MI') AS QUOT_BID_TO_DT,       \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.URL_COL,														        \n");
        	sqlstr.append("       A.STATUS_CD,                                                      \n");
        	sqlstr.append("       F_GET_CODE_NAME ('QUOT_STATUS_CD', STATUS_CD) AS STATUS_NM        \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());
			search.setStatement(sqlstr.toString());
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
        }
        return valueobject;
	}
	
	public RowSet quot_bid_supp_info(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	sqlstr.append("	SELECT CHARGE_NAME,CHARGE_EMAIL,charge_hp,VENDOR_NAME, CORP_TEL, ON_SUPP_YN, IN_OUT_DIV	 FROM (									\n");
        	//견적용 업체정보
        	sqlstr.append("	SELECT A.CHARGE_NAME,A.CHARGE_EMAIL,replace(A.CHAGE_HP,'-','') charge_hp,A.VENDOR_NAME, B.CORP_TEL, A.ON_SUPP_YN, A.IN_OUT_DIV	\n");
			sqlstr.append("	FROM QUOT_BID_SUPP A, QUOT_REG_SUPP_INFO B									\n");
			sqlstr.append("	WHERE A.VENDOR_CD = QUOT_REG_CORP_ID										\n");
			sqlstr.append("	 AND A.QUOT_BID_NO = '"+param.get("QUOT_BID_NO")+"'							\n");
			sqlstr.append("	 AND A.QUOT_BID_DEG = '"+param.get("QUOT_BID_DEG")+"'						\n");
			sqlstr.append("	 UNION																		\n");
			//온라인 등록 업체정보
			sqlstr.append("	SELECT A.CHARGE_NAME,A.CHARGE_EMAIL,replace(A.CHAGE_HP,'-','') charge_hp,A.VENDOR_NAME, B.CORP_TEL, A.ON_SUPP_YN, A.IN_OUT_DIV	\n");
			sqlstr.append("	FROM QUOT_BID_SUPP A, SUPP_INFO B											\n");
			sqlstr.append("	WHERE A.VENDOR_CD = TO_CHAR(B.CORP_ID)										\n");
			sqlstr.append("	 AND A.QUOT_BID_NO = '"+param.get("QUOT_BID_NO")+"'							\n");
			sqlstr.append("	 AND A.QUOT_BID_DEG = '"+param.get("QUOT_BID_DEG")+"'						\n");
			sqlstr.append("	 UNION																		\n");
			//온라인 등록 업체정보내 임직원들			
			sqlstr.append("	SELECT A.CHARGE_NAME,c.EMAIL,c.HP1||c.HP2||c.HP3,A.VENDOR_NAME, B.CORP_TEL, A.ON_SUPP_YN, A.IN_OUT_DIV	\n");
			sqlstr.append("	FROM QUOT_BID_SUPP A, SUPP_INFO B, SUPP_CHARGE C							\n");
			sqlstr.append("	WHERE A.VENDOR_CD = TO_CHAR(B.CORP_ID)										\n");
			sqlstr.append("	 AND B.CORP_ID = C.CORP_ID													\n");
			sqlstr.append("	 AND A.QUOT_BID_NO = '"+param.get("QUOT_BID_NO")+"'							\n");
			sqlstr.append("	 AND A.QUOT_BID_DEG = '"+param.get("QUOT_BID_DEG")+"'						\n");
			sqlstr.append("	 )																																\n");

			search.setStatement(sqlstr.toString());
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	
	public boolean change_quot_status(Connection connection, ValueObject params) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		try {
			sql.append("	UPDATE QUOT_BID_MASTER SET 								\n");
			sql.append("		  STATUS_CD = '"+params.get("STATUS_CD")+"'       	\n");
			sql.append("		, UPD_BY = '"+storage.getUser().get("ACCT_ID")+"'   \n");
			sql.append("		, UPD_DATE = sysdate       							\n");
			sql.append("	WHERE QUOT_ID = '"+params.get("QUOT_ID")+"'   			\n");

			pstmt = connection.prepareStatement(sql.toString());
			pstmt.executeUpdate();

			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	/**
	 * 견적번호 채번하여 리턴
	 * @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;
		}
	}

}
