/**
 *
 * @(#)author  [ok]
 * @(#)version 1.0
 * @(#)date    2014. 06. 30
 * @(#)since   JDK 1.7.2
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of udapsoft, Inc.
 */
 package kr.co.udapsoft.ebid.supplier.portal.payment.biz;


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

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

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
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.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.ebid.buyer.common.file.FileManager;

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 BuyTaxListInitBiz extends Biz {
	
	public BuyTaxListInitBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		if(storage.compareEvent("")){
			
			//System.out.println("INIT TEST");
			
		}else if(storage.compareEvent("GET_LIST")){
			
			//System.out.println("[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"));
				//System.out.println(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();
			}
		}
		
	}

    /**
     *@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.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', '과세', '02', '면세') 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.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"
                    + 	"				 ,C.TMPSLIP_DT            																								\n"
                    + 	"				 ,C.TMPSLIP_NO            																								\n"
                    + 	"				 ,F_GET_USER_NM(NVL(D.UPD_BY,D.CRE_BY)) AS USER_NAME            														\n"
					+ 	"           FROM XXSB_DTI_MAIN@SMBLIF A 																								\n"
					+ 	" 				 ,XXSB_DTI_STATUS@SMBLIF B																								\n"
					+ 	" 			     ,ETEC_ERPIF.TAX_INT_INFO C																									\n"
					+ 	" 				 ,PUR1030T D																											\n"
					+ 	" 				 ,PUR2000T E																											\n"
					+ 	" 				 ,XXSB_DTI_NTS@SMBLIF F																									\n"
					+ 	" 		   WHERE C.PAY_DIV = 'P_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.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID																					\n"
					+ 	" 			 AND A.CONVERSATION_ID = F.CONVERSATION_ID(+)																				\n"
					+ 	"          UNION ALL																													\n"
					+ 	" 		  SELECT D.PAY_DIV AS PAY_DIV																									\n"                                                                                                                               
					+ 	" 				 ,E.DEPT_CD                   																							\n"                                                                                                                         
					+ 	" 				 ,E.DEPT_NAME                    																						\n"                                                                                                                         
					+ 	" 				 ,E.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', '과세', '02', '면세') 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.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"
                    + 	"				 ,C.TMPSLIP_DT            																								\n"
                    + 	"				 ,C.TMPSLIP_NO            																								\n"
                    + 	"				 ,F_GET_USER_NM(NVL(D.UPD_BY,D.CRE_BY)) AS USER_NAME            														\n"
					+ 	" 			FROM XXSB_DTI_MAIN@SMBLIF A 																								\n"
					+ 	" 				 ,XXSB_DTI_STATUS@SMBLIF B																								\n"
					+ 	" 			     ,ETEC_ERPIF.TAX_INT_INFO C																									\n"
					+ 	" 				 ,PUR1100T D																											\n"
					+ 	" 				 ,PUR2000T E																											\n"
					+ 	" 				 ,XXSB_DTI_NTS@SMBLIF F																									\n"
					+ 	" 		   WHERE C.PAY_DIV = 'P_PAYREQ'																									\n" 
					+ 	" 			 AND A.CONVERSATION_ID = B.CONVERSATION_ID																					\n"
					+ 	" 			 AND A.CONVERSATION_ID = C.CONVERSATION_ID																					\n"
					+ 	" 			 AND C.PAY_KEY = D.PAYREQ_ID																								\n"
					+ 	" 			 AND D.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID																					\n"
					+ 	" 			 AND A.CONVERSATION_ID = F.CONVERSATION_ID(+)																				\n" 
					+ 	" 		 ) A, CONT_PUR_SUPP C1		                			  																						\n"
        			+ 	"  WHERE 1=1	                			  																							\n"
        			+ 	"  AND A.PUR_CONT_MOD_ID = C1.PUR_CONT_MOD_ID																							\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 = '"+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";
        	}
        	if(!"".equals(params.get("userCorpId"))){
        		sqlstr += " AND C1.CORP_ID  = '"+Params.get("userCorpId")+"'														\n";
        	}
            sqlstr += " ORDER BY A.DTI_WDATE DESC, PAY_DIV, DEPT_CD, CST_DOC_NAME,  TMPSLIP_NO	\n"; 
            search.setStatement(sqlstr);
            //System.out.println(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);
            //System.out.println(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"
        			;
            
        	////System.out.println(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;
        	//System.out.println(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;
	}

}
