/**
 *
 * @(#)author  [Youngmi Im]
 * @(#)version 1.0
 * @(#)date    May 10, 2007
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.hwenc.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of Hanwha E&C, Inc.
 */
 package kr.co.udapsoft.ebid.buyer.payment.outside.biz;


import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
import kr.co.hsnc.common.config.WAFConfig;
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 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;

import oracle.toplink.internal.ox.StrBuffer;

import org.jsoup.Jsoup;
import	org.jsoup.nodes.Document;
import	org.jsoup.nodes.Element;
import	org.jsoup.select.Elements;

public class TaxListInitBiz extends Biz {
	
	public TaxListInitBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		if(storage.compareEvent("")){
			
			WAFLogger.debug("INIT TEST");
			
		}else if(storage.compareEvent("GET_LIST")){
			
			WAFLogger.debug("[TaxListBiz] GET_LIST");
			params.set("sheet1", getList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("AP_PROCESS")){	//역발행요청 배치ID설정
			
			boolean success = false;
			String[] status = super.gets("sheet1.STATUS");

			try {
				super.conn.begin();
				
				Connection con = super.conn.getConnection();
				
				//배치값 생성
				String batchId = getBatchId();
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = updateData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i), batchId);
					}
				}
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
				
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
			
		}else if(storage.compareEvent("RUN_AP_PROCESS")){	//역발행요청 배치ID설정
			
			try {
				super.conn.begin();
				//역발행요청 처리					
				ValueObject result = new ValueObject();
				result = apProcess(super.conn.getConnection(), params.get("convId"));
				WAFLogger.debug(result);
	    		storage.addValueObject("VALUEOBJECT", result);
	    		storage.setSuccess(true);
				
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}else if(storage.compareEvent("RUN_APP_PROCESS")){	//역발행요청 배치ID설정
			
			try {
				super.conn.begin();
				//역발행요청 처리					
				ValueObject result = new ValueObject();
				result = appProcess(super.conn.getConnection(), params.get("convId"));
				WAFLogger.debug(result);
	    		storage.addValueObject("VALUEOBJECT", result);
	    		storage.setSuccess(true);
				
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}else if(storage.compareEvent("CHG_AP_PROCESS")){


			boolean success = false;
			String[] status = super.gets("sheet1.STATUS");

			try {
				super.conn.begin();
				
				Connection con = super.conn.getConnection();
				
				//배치값 생성
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = chgApProcess(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
						sendaccount(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));//메일발송 2018.05.31
					}
				}
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
				
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}else if(storage.compareEvent("SEND_SMS")){  //2017.04.13 외주 역발행 세금계산서 SMS전송 

			boolean success = false;
			String[] status = super.gets("sheet1.STATUS");

			try {
				super.conn.begin();
				
				Connection con = super.conn.getConnection();
				
				//배치값 생성
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = sendsms(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
				}
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
				
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}
		
	}

    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 세금계산서발행리스트
     *@author OK
     *@version 2014. 9. 22.
     *@since JDK1.7
     */
    public RowSet getList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
        	sqlstr	+=	" SELECT A.*																																\n"
        			+ 	"   FROM (           			  																											\n"
        			+ 	"		  SELECT '0' AS PAY_DIV       																										\n"
        			+ 	"				 ,E.DEPT_CD             																									\n"      
                    + 	"				 , E.DEPT_NAME              																								\n"       
                    + 	"				 , E.BUILD_NAME CST_DOC_NAME       																							\n"                                                                                                                                 
                    + 	"				 , TO_CHAR(A.DTI_WDATE, 'YYYY-MM-DD') AS DTI_WDATE   --공급일자                                                        		\n"                                
                    + 	"				 , A.DTI_TYPE                                                                                                             	\n"                             
                    + 	"				 , DECODE(A.DTI_TYPE, '01', DECODE(A.TAX_AMOUNT, 0, '영세율', '과세'), '02', '면세', '03','과세') AS DTI_TYPE_NM                                                            \n"                             
                    + 	"				 , SUBSTR(A.SUP_COM_REGNO,0,3)||'-'||SUBSTR(A.SUP_COM_REGNO,4,2)||'-'||SUBSTR(A.SUP_COM_REGNO,6,5)  AS SUP_COM_REGNO_BAR	\n"                         
                    + 	"				 , A.SUP_COM_REGNO --공급자사업자번호                                                                                       \n"                         
                    + 	"				 , A.SUP_COM_NAME   --공급자 상호                                                                                           \n"                     
                    + 	"				 , A.TOTAL_AMOUNT   --합계금액                                                                                              \n"                   
                    + 	"				 , B.DTI_STATUS                                                                                                             \n"               
                    + 	"				 , A.DIRECTION                                                                                                              \n"             
                    + 	"				 , DECODE(A.DIRECTION, '1', '업체', '2', 'SGC이앤씨') AS DIRECTION_NM                                                          \n"          
                    + 	"				 , D.SUB_CONT_MOD_ID PUR_CONT_MOD_ID																						\n"
                    + 	"				 , A.CONVERSATION_ID                  	 																					\n"                                                                                                                   
                    + 	"				 , a.SUP_COM_ID     --공급자스마트빌ID 																						\n"                                                                                                                 
                    + 	"				 , a.BYR_COM_ID     --공급받는자스마트빌ID																					\n"                                                                                                              
                    + 	"				 , a.INTERFACE_BATCH_ID                      																				\n"                                                                                                            
                    + 	"				 , B.RETURN_CODE                                																			\n"                                                                                                        
                    + 	"				 , '1' As BILL                                     																			\n"                                                                                                       
                    + 	"				 ,NVL(F.SEND_REQUEST,'9') SEND_REQUEST                																		\n"                                                                                                
                    + 	"				 ,NVL(F.SEND_REQUEST_DESC,'미전송') SEND_REQUEST_DESC    																	\n"                                                                                            
                    + 	"				 ,A.BYR_COM_REGNO            																								\n"
                    + 	"				 ,C.TMPSLIP_DEPT            																								\n"
                    + 	"				 ,TO_CHAR(C.TMPSLIP_DT,'YYYY-MM-DD') TMPSLIP_DT           															  \n"
                    + 	"				 ,C.TMPSLIP_NO            																								\n"
                    + 	"				 ,F_GET_USER_NM(NVL(D.CRE_BY,D.UPD_BY)) AS USER_NAME   , C.PAY_KEY         														\n"
                    + 	"           FROM XXSB_DTI_MAIN@SMBLIF A 																									\n"
                    + 	"				 ,XXSB_DTI_STATUS@SMBLIF B																									\n"
                    + 	"				 ,ETEC_ERPIF.TAX_INT_INFO C																										\n"
                    + 	"				 ,SUB1030T D																												\n"
                    + 	"				 ,SUB2000T E																												\n"
                    + 	"				 ,XXSB_DTI_NTS@SMBLIF F																										\n"
                    + 	"          WHERE C.PAY_DIV = 'O_PREPAY' 																									\n"
                    + 	"     		 AND A.CONVERSATION_ID = B.CONVERSATION_ID																						\n"
                    + 	"     		 AND A.CONVERSATION_ID = C.CONVERSATION_ID																						\n"
                    + 	"     		 AND C.PAY_KEY = D.PREPAY_ID																									\n"
                    + 	"     		 AND D.SUB_CONT_MOD_ID = E.SUB_CONT_MOD_ID																						\n"
                    + 	"     		 AND A.CONVERSATION_ID = F.CONVERSATION_ID(+)																					\n"
                    + 	"     	   UNION ALL																														\n"
                    + 	"		  SELECT '1' AS PAY_DIV																												\n"                                                                                                                               
                    + 	"				 ,E.DEPT_CD      																											\n"                                                                                                                                      
                    + 	"				 ,E.DEPT_NAME       																										\n"                                                                                                                                      
                    + 	"				 ,E.BUILD_NAME CST_DOC_NAME																									\n"                                                                                                                                        
                    + 	"				 ,TO_CHAR(A.DTI_WDATE, 'YYYY-MM-DD') AS DTI_WDATE   --공급일자																\n"                                                                                        
                    + 	"				 ,A.DTI_TYPE                                                     															\n"                                                                                     
                    + 	"				 ,DECODE(A.DTI_TYPE, '01', DECODE(A.TAX_AMOUNT, 0, '영세율', '과세'), '02', '면세', '03', '과세') AS DTI_TYPE_NM     														\n"                                                                                     
                    + 	"				 ,SUBSTR(A.SUP_COM_REGNO,0,3)||'-'||SUBSTR(A.SUP_COM_REGNO,4,2)||'-'||SUBSTR(A.SUP_COM_REGNO,6,5)  AS SUP_COM_REGNO_BAR		\n"                                 
                    + 	"				 ,A.SUP_COM_REGNO --공급자사업자번호                                                                                      	\n"                                
                    + 	"				 ,A.SUP_COM_NAME   --공급자 상호                                                                                            \n"                           
                    + 	"				 ,A.TOTAL_AMOUNT   --합계금액                                                                                               \n"                        
                    + 	"				 ,B.DTI_STATUS                                                                                                              \n"                   
                    + 	"				 ,A.DIRECTION                                                                                                               \n"                
                    + 	"				 ,DECODE(A.DIRECTION, '1', '업체', '2', 'SGC이앤씨') AS DIRECTION_NM                                                           \n"            
                    + 	"				 ,E.SUB_CONT_MOD_ID PUR_CONT_MOD_ID																							\n"
                    + 	"				 ,A.CONVERSATION_ID                   																						\n"                                                                                                                   
                    + 	"				 ,a.SUP_COM_ID     --공급자스마트빌ID    																					\n"                                                                                                              
                    + 	"				 ,a.BYR_COM_ID     --공급받는자스마트빌ID   																				\n"                                                                                                           
                    + 	"				 ,a.INTERFACE_BATCH_ID                         																				\n"                                                                                                         
                    + 	"				 ,B.RETURN_CODE                                   																			\n"                                                                                                     
                    + 	"				 ,'1' As BILL                                        																		\n"                                                                                                    
                    + 	"				 ,NVL(F.SEND_REQUEST,'9') SEND_REQUEST                   																	\n"                                                                                             
                    + 	"				 ,NVL(F.SEND_REQUEST_DESC,'미전송') SEND_REQUEST_DESC       																\n"                                                                                         
                    + 	"				 ,A.BYR_COM_REGNO            																								\n"
                    + 	"				 ,C.TMPSLIP_DEPT            																								\n"
                    + 	"				 ,TO_CHAR(C.TMPSLIP_DT,'YYYY-MM-DD') TMPSLIP_DT           																								\n"
                    + 	"				 ,C.TMPSLIP_NO            																								\n"
                    + 	"				 ,F_GET_USER_NM(NVL(D.UPD_BY,D.CRE_BY)) AS USER_NAME ,C.PAY_KEY           														\n"
                    + 	"           FROM XXSB_DTI_MAIN@SMBLIF A 																									\n"
                    + 	"				 ,XXSB_DTI_STATUS@SMBLIF B																									\n"
                    + 	"				 ,ETEC_ERPIF.TAX_INT_INFO C																										\n"
                    + 	"				 ,SUB1040T D																												\n"
                    + 	"				 ,SUB2000T E																												\n"
                    + 	"				 ,XXSB_DTI_NTS@SMBLIF F																										\n"
                    + 	"          WHERE C.PAY_DIV = 'O_ACMP' 																										\n"
                    + 	"			 AND A.CONVERSATION_ID = B.CONVERSATION_ID																						\n"
                    + 	"			 AND A.CONVERSATION_ID = C.CONVERSATION_ID																						\n"
                    + 	"			 AND C.PAY_KEY = D.ACMP_ID																										\n"
                    + 	"			 AND D.SUB_CONT_ID = E.SUB_CONT_ID																								\n"
                    + 	"			 AND D.MOD_NO = E.MOD_NO																										\n"
                    + 	"			 AND A.CONVERSATION_ID = F.CONVERSATION_ID(+)																					\n"
                    + 	" 	     ) A		                			  																							\n"
        			+ 	"  WHERE 1=1	                			  																								\n"
                    ; 
        	if(!"".equals(params.get("selectDiv"))){	//구분
        		sqlstr += " AND A.PAY_DIV = '"+Params.get("selectDiv")+"'																												\n";
    		}
        	if(!"".equals(params.get("searchDateStart"))){	//공급일자
        		sqlstr += " AND A.DTI_WDATE BETWEEN '"+Params.get("searchDateStart")+"' AND '"+Params.get("searchDateEnd")+"'															\n";
        	}
        	if(!"".equals(params.get("searchDeptCd"))){		//현장
        		sqlstr	+=	"AND A.DEPT_CD = '"+Params.get("searchDeptCd")+"'	\n"	;	
        	}
        	if(!"".equals(params.get("searchBuildName"))){	//계약명
        		sqlstr += " AND A.CST_DOC_NAME LIKE '%'||UPPER('"+Params.get("searchBuildName")+"')||'%'																				\n";
    		}            
        	if(!"".equals(params.get("searchRegNo"))){		//사업자번호
        		sqlstr += " AND A.SUP_COM_REGNO_BAR = '"+Params.get("searchRegNo")+"'																									\n";
        	}
        	if(!"".equals(params.get("searchCorpNm"))){	//업체명
        		sqlstr += " AND A.SUP_COM_NAME LIKE '%'||UPPER('"+Params.get("searchCorpNm")+"')||'%'																					\n";
        	}            
        	if(!"".equals(params.get("selectBillStatus"))){	//상태
        		sqlstr += " AND A.DTI_STATUS = '"+Params.get("selectBillStatus")+"'																										\n";
        	}
        	if(!"".equals(params.get("selectSendRequest"))){	//국세청전송상태
        		sqlstr += " AND A.SEND_REQUEST = '"+Params.get("selectSendRequest")+"'	\n";
        	}
            sqlstr += " ORDER BY PAY_DIV, DEPT_CD, CST_DOC_NAME, DTI_WDATE,  TMPSLIP_NO      	  																											\n"; 
            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
           
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPrePaymentList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( listRowSet == null ) listRowSet = new RowSetImpl();
            return listRowSet;
        }
    }
    
	private boolean updateData(Connection connection, ValueObject sqlParams, String batchId) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			/*
			StringBuffer sql = new StringBuffer();
			sql.append("	UPDATE XXSB_DTI_MAIN@SMBLIF SET   					\n");
			sql.append("		INTERFACE_BATCH_ID  = ?  				      	\n");
			sql.append("	WHERE CONVERSATION_ID   = ?							\n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, batchId);
			pstmt.setString(paramIdx++, sqlParams.get("CONVERSATION_ID"));
			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);
			}
		}
	}
	
	private String getBatchId() throws Exception {

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        String batchId = "";
        
        try {
        	sqlstr += " SELECT TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS') AS INTERFACE_BATCH_ID FROM DUAL				\n";
 
            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
            
            batchId = listRowSet.getRow(0).get("INTERFACE_BATCH_ID");

            
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getBatchId() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            return batchId;
        }
	}	
	
	private ValueObject apProcess(Connection connection, String convId) throws Exception {
		
        BufferedReader    oBufReader = null;
        HttpURLConnection httpConn   = null;
        String strBuffer = "";
        String strRslt   = "";
        ValueObject result = new ValueObject();
        
        try{
            
            Search search = new SearchImpl();
            RowSet listRowSet = null;
            String sqlstr = "";
        	sqlstr += " SELECT INTERFACE_BATCH_ID							\n"
        			+ " FROM XXSB_DTI_MAIN@SMBLIF							\n"
        			+ " WHERE CONVERSATION_ID = "+convId+"					\n"
        			;
            
        	//WAFLogger.debug(sqlstr);
            search.setStatement(sqlstr);
            listRowSet = search.execute();
            String batchId = listRowSet.getRow(0).get("INTERFACE_BATCH_ID");
            
            String	sId		=	WAFConfig.get("waf.smb.id");
            String	sPw		=	WAFConfig.get("waf.smb.pw");
            String	sUrl	=	WAFConfig.get("waf.smb.url");
            
            //String strEncodeUrl = "http://211.38.140.131:10000/callSB_V3/XXSB_DTI_ISSUE.asp?BATCH_ID="+batchId+"&ID=sjpwtech&PASS=DUMP";
            String strEncodeUrl = sUrl+"/callSB_V3/XXSB_DTI_ISSUE.asp?BATCH_ID="+batchId+"&ID="+sId+"&PASS="+sPw;
            WAFLogger.debug(strEncodeUrl);
            URL oOpenURL = new URL(strEncodeUrl);
          
            httpConn =  (HttpURLConnection) oOpenURL.openConnection();          
            httpConn.setRequestMethod("POST");          
            httpConn.connect();          
            oBufReader = new BufferedReader(new InputStreamReader(oOpenURL.openStream(), "euc-kr"));
  
            //Buffer에 있는 내용을 읽어 차례로 화면에 뿌려준다.
            while((strBuffer = oBufReader.readLine()) != null)
            {
                if(strBuffer.length() > 1)
                {
                    strRslt += strBuffer;
                }
            }
            
            /* html parsing */
            String		sParam		=	"";
            Document 	doc			=	Jsoup.parse(strRslt);
            
            Elements	elHidden	=	doc.select("input");
            
            StringBuffer	sb	=	new	StringBuffer();
            for(Element e : elHidden)
            {
            	sb.append("&"+ e.attr("name")+"="+e.attr("value"));
            }
            result.set("result", sb.toString());  
        } catch( Exception ee) {
          ee.getMessage();
        }
  
        return result;
	}

	private ValueObject appProcess(Connection connection, String convId) throws Exception {
		
        BufferedReader    oBufReader = null;
        HttpURLConnection httpConn   = null;
        String strBuffer = "";
        String strRslt   = "";
        ValueObject result = new ValueObject();
        
        try{
            
            Search search = new SearchImpl();
            RowSet listRowSet = null;
            String sqlstr = "";
        	sqlstr += " SELECT INTERFACE_BATCH_ID							\n"
        			+ " FROM XXSB_DTI_MAIN@SMBLIF							\n"
        			+ " WHERE CONVERSATION_ID = "+convId+"					\n"
        			;
            
        	WAFLogger.debug(sqlstr);
            search.setStatement(sqlstr);
            listRowSet = search.execute();
            String batchId = listRowSet.getRow(0).get("INTERFACE_BATCH_ID");
            
            String	sId		=	WAFConfig.get("waf.smb.id");
            String	sPw		=	WAFConfig.get("waf.smb.pw");
            String	sUrl	=	WAFConfig.get("waf.smb.url");
            //: http://222.106.252.32:10002/callSB_V3/XXSB_DTI_STATUS_CHANGE.asp?BATCH_ID=20141113173438&STATUS=C&SIGNAL=APPROVE&ID=etecadmin&PASS=etec6346
            String strEncodeUrl = sUrl+"/callSB_V3/XXSB_DTI_STATUS_CHANGE.asp?BATCH_ID="+batchId+"&STATUS=C&SIGNAL=APPROVE&ID="+sId+"&PASS="+sPw;
            WAFLogger.debug(strEncodeUrl);
            URL oOpenURL = new URL(strEncodeUrl);
          
            httpConn =  (HttpURLConnection) oOpenURL.openConnection();          
            httpConn.setRequestMethod("POST");          
            httpConn.connect();          
            oBufReader = new BufferedReader(new InputStreamReader(oOpenURL.openStream(), "euc-kr"));
  
            //Buffer에 있는 내용을 읽어 차례로 화면에 뿌려준다.
            while((strBuffer = oBufReader.readLine()) != null)
            {
                if(strBuffer.length() > 1)
                {
                    strRslt += strBuffer;
                }
            }
            
            /* html parsing */
            String		sParam		=	"";
            Document 	doc			=	Jsoup.parse(strRslt);
            
            Elements	elHidden	=	doc.select("input");
            
            StringBuffer	sb	=	new	StringBuffer();
            for(Element e : elHidden)
            {
            	sb.append("&"+ e.attr("name")+"="+e.attr("value"));
            }
            result.set("result", sb.toString());
        } catch( Exception ee) {
          ee.getMessage();
        }
  
        return result;
	}
	
private boolean chgApProcess(Connection connection, ValueObject sqlParams) throws Exception {
		
        String message = "";
        String SPStateMent = "";
		CallableStatement cs = null;
		
		boolean rtn = false;
		try {
			SPStateMent = "{call SP_TAX_BILL_CHG(?,?,?)}";
            
            cs = connection.prepareCall(SPStateMent);
           
            cs.setString(1, sqlParams.get("CONVERSATION_ID"));     // 가불금 ID
            
            cs.registerOutParameter(2, java.sql.Types.VARCHAR);
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.execute();
            
            if(cs.getString(2).equals("S")){
            	rtn=  true;
            }else{
            	rtn = false;
            }
            //회계팀 담당자 메일발송 
            
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		}  finally{
			try { 
				if (cs != null) cs.close(); 
			} catch (Exception ex_close) {
				WAFLogger.error(ex_close);
			} 
        }
		
		return rtn;
	}

	private boolean sendsms(Connection connection, ValueObject sqlParams) throws Exception {
		MessageSend messageSend = new MessageSend(storage);
		
		ValueObject supp_info = cont_supp_info(sqlParams);
		
		String recvAddress = supp_info.get("HP").replace("-", "");
    	String smsContent = "역발행 세금계산서 승인요청.스마트빌 로그인하시어 확인바랍니다.SGC이앤씨(주)";
    	messageSend.sendSMS(recvAddress, "024899000", storage.getUser().get("ACCT_ID"), smsContent);  
		
    	//E-Mail
    	StringBuffer mailContent = new StringBuffer();
    	mailContent.append("안녕하십니까, SGC이앤씨(주)입니다. </BR>");
    	mailContent.append(sqlParams.get("SUP_COM_NAME") + "의 대금신청이 승인되어 전자(세금)계산서가 역발행 요청되었음을 안내드립니다 .</BR>");
    	mailContent.append("</BR>");
    	mailContent.append("본 메일을 수신하신 " + sqlParams.get("SUP_COM_NAME") + " 담당자분께서는 대금이 지급될 수 있도록 스마트빌(<a href=\"http://www.smartbill.co.kr\" style=\"font-weight:bold\">http://www.smartbill.co.kr</a>)로 접속, </BR>");
    	mailContent.append("역발행 요청된 전자(세금)계산서를 확인 및 발행하시기 바랍니다.</BR>");
    	mailContent.append("(※ 전자(세금)계산서는 국세청으로 즉시 전송될 수 있도록 하단의 매뉴얼을 참조하여 설정 요망)</BR>");
    	mailContent.append("</BR>");
    	mailContent.append("스마트빌에 가입되어있지 않은 협력업체는 즉시 가입 후, 아래 담당자에게 유선으로 연락하시기 바랍니다.</BR>");
    	mailContent.append("</BR>");
    	mailContent.append("■ 구매 계약</BR>");
    	mailContent.append("- 기계/배관 : 송영주 과장 ( 02-489-8251 / yjsong@sgc.co.kr)</BR>");
    	mailContent.append("- 전기/계장 : 권영훈 차장 ( 02-489-8242 / yhkwon@sgc.co.kr)</BR>");
    	mailContent.append("- 건축/토목 : 박은종 과장 ( 02-489-8264 / ejpark@sgc.co.kr)</BR>");
    	mailContent.append("</BR>");
    	mailContent.append("■ 외주 계약</BR>");
    	mailContent.append("- 외주/계약 : 유성호 대리 (02-489-8265 / shyoo@sgc.co.kr)</BR>");
    	mailContent.append("</BR>");
    	mailContent.append("<a href=\"http://fs.sgcenc.co.kr:80/ohd/pm/disk/linkdown.html?reginfo=20170414090256750465\" style=\"font-weight:bold\"><font color=\"red\">메뉴얼 다운로드(국세청 즉시전송 설정방법)</font></a></BR>");

    	messageSend.sendEmail( supp_info.get("EMAIL"), "partner@sgc.co.kr",sqlParams.get("SUP_COM_NAME") + " 전자(세금)계산서 발행 요청", sqlParams.get("SUP_COM_NAME") + " 전자(세금)계산서 발행 요청", mailContent);	
    	messageSend.sendEmail( supp_info.get("EMAIL2"), "partner@sgc.co.kr", sqlParams.get("SUP_COM_NAME") + " 전자(세금)계산서 발행 요청", sqlParams.get("SUP_COM_NAME") + " 전자(세금)계산서 발행 요청", mailContent);
    	return true;
	}
	public ValueObject cont_supp_info(ValueObject param) {

		Search search = new SearchImpl();
		 ValueObject valueobject = null;
		RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	
        	if (param.get("PAY_DIV").equals("0")){
        		sqlstr.append("	SELECT A.TAX_CHARG_HP HP, A.TAX_CHARG_EMAIL EMAIL , B.E_MAIL EMAIL2    \n");
            	sqlstr.append("	  FROM SUB1030T A                                    \n");
            	sqlstr.append("	      ,PUB0120T B                                    \n");
            	sqlstr.append("	 WHERE A.CRE_BY = B.ACCT_ID                          \n");
            	sqlstr.append("	   AND A.PREPAY_ID = ?                               \n");
        	}else{
        		sqlstr.append("	SELECT A.TAX_CHARG_HP HP, A.TAX_CHARG_EMAIL EMAIL , B.E_MAIL EMAIL2     \n");
            	sqlstr.append("	  FROM SUB1040T A                                    \n");
            	sqlstr.append("	      ,PUB0120T B                              \n");
            	sqlstr.append("	 WHERE A.UPD_BY = B.ACCT_ID	                         \n");
            	sqlstr.append("	   AND A.ACMP_ID = ?	                             \n");
        	}
        	
        	search.setStatement(sqlstr.toString());
			search.addParameter(param.get("PAY_KEY"));
			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);
        }
        return valueobject;
	}
	
	private boolean sendaccount(Connection connection, ValueObject sqlParams) throws Exception {
		MessageSend messageSend = new MessageSend(storage);
		ValueObject mail_info = getAccMail(sqlParams);
	 
		//E-Mail
    	StringBuffer mailContent = new StringBuffer();
    	mailContent.append(sqlParams.get("SUP_COM_NAME") + " 역발행 전자(세금)계산서가  취소 되었음을 안내드립니다 .</BR>");
    	mailContent.append("ERP이관된 경우 전표취소 처리 해 주십시요.</BR>");
    	mailContent.append("</BR>");
    	mailContent.append("현장명:" + sqlParams.get("DEPT_NAME") + "</BR>");
    	mailContent.append("공급자:" + sqlParams.get("SUP_COM_NAME") + "</BR>");
    	mailContent.append("계약명:" + sqlParams.get("CST_DOC_NAME") + "</BR>");
    	mailContent.append("</BR>");
    	
    	if (!mail_info.get("MAIL1").equals('N')) messageSend.sendEmail( mail_info.get("MAIL1"), "partner@sgc.co.kr",sqlParams.get("SUP_COM_NAME") + " 역발행 전자(세금)계산서 취소", sqlParams.get("SUP_COM_NAME") + " 전자(세금)계산서 취소", mailContent);	
    	if (!mail_info.get("MAIL2").equals('N')) messageSend.sendEmail( mail_info.get("MAIL2"), "partner@sgc.co.kr",sqlParams.get("SUP_COM_NAME") + " 역발행 전자(세금)계산서 취소", sqlParams.get("SUP_COM_NAME") + " 전자(세금)계산서 취소", mailContent);	
    	if (!mail_info.get("MAIL3").equals(mail_info.get("MAIL1")) ) messageSend.sendEmail( mail_info.get("MAIL3"), "partner@sgc.co.kr",sqlParams.get("SUP_COM_NAME") + " 역발행 전자(세금)계산서 취소", sqlParams.get("SUP_COM_NAME") + " 전자(세금)계산서 취소", mailContent);	
    	
    	return true;
	}
	
	public ValueObject getAccMail(ValueObject param) {

		Search search = new SearchImpl();
		ValueObject valueobject = null;
		RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	sqlstr.append("	SELECT NVL(CHG_CODE08 ,'N') MAIL1                   \n");
        	sqlstr.append("	      ,NVL(CHG_CODE09 ,'N') MAIL2                   \n");
        	sqlstr.append("	      ,F_GET_CODE_NAME('TAX_CHARGE', '01') MAIL3    \n");
            sqlstr.append("	  FROM ETEC_ERPIF.ACZ10100 A                             \n");
            sqlstr.append("	      ,EBID_COM911T B                              \n");
            sqlstr.append("	 WHERE B.BASECD = '101'                             \n");
            sqlstr.append("	   AND A.AC_UNIT_CD = B.DETAILCD                    \n");
            sqlstr.append("	   AND A.DEPT_CD = ?                                \n");
                        
        	search.setStatement(sqlstr.toString());
			search.addParameter(param.get("TMPSLIP_DEPT"));
		    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);
        }
        return valueobject;
	}
}
