/**
 * @(#)file    UserListBiz.java
 * @(#)author  Joung Kyu Park
 * @(#)version 1.0
 * @(#)date    May 31, 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.quot.purchase.biz;



import com.udapsoft.waf.common.ui.HTMLMaker;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
import kr.co.hsnc.common.logger.Logger;
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 kr.co.udapsoft.common.sender.SmtpAuth;

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 QuotPurSuppRegBiz extends Biz {

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

	/* (non-Javadoc)
	 * @see kr.co.hwenc.system.Biz#doBiz()
	 */
	public void doBiz() throws Exception {

		
		
		WAFLogger.debug("event==============>" + storage.getEvent());
		ValueObject QuotPurSuppRegVo = new ValueObject();
		
		if(storage.compareEvent(""))
		{
		    params.set("job_div", params.get("job_div"));
		}
		else if(storage.compareEvent("GET_LIST"))
        {
		    params.set("sheet1", getList(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		else if(storage.compareEvent("DELETE_DATA"))
        {
	        boolean success = false;
            
            String[] status = super.gets("sheet1.STATUS");
            
            for (int i = 0; i < status.length; i++) 
            {
                if (IBSheet7Utils.equalStatusDelete(status[i])) 
                {
                    success = deleteChargeData(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
                }
            }
            
            super.storage.setSuccess(success);
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		else if(storage.compareEvent("QUOT_REG_SUPP"))
		{
		    ValueObject corp_id = getCorp_id(super.conn.getConnection()); 
            params.set("p_corp_id", corp_id.get("SEQ"));
            
            storage.addValueObject("QuotPurSuppReg", QuotPurSuppRegVo);
            
		}
		else if(storage.compareEvent("GET_DETAIL"))
        {
		    QuotPurSuppRegVo = getQuotPurSuppRegList(params);
		    params.set("p_corp_id", QuotPurSuppRegVo.get("QUOT_REG_CORP_ID"));
		    storage.addValueObject("QuotPurSuppReg", QuotPurSuppRegVo);
        }
	}	
	
	private boolean deleteChargeData(ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            
            sql.append("    DELETE FROM  QUOT_REG_SUPP_INFO          \n ");
            sql.append("        WHERE QUOT_REG_CORP_ID = ?                          \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(sqlParams.get("QUOT_REG_CORP_ID"));
            
            WAFLogger.debug(sql.toString());
            WAFLogger.debug(persistent.getParameters());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
//          e.printStackTrace();
            WAFLogger.error(this.getClass().getName() + " --> deleteChargeData() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.toString());
            WAFLogger.error(e);
            conn.setRollback();
            throw e;
            
        }
        
        finally {
            boolean sucss2 = deleteSuppInfo(sqlParams);
            if(sucss2 == false) conn.setRollback();
            super.conn.end();
        }
    }
	
	private boolean deleteSuppInfo(ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            
            sql.append("    DELETE FROM  SUPP_INFO          \n ");
            sql.append("        WHERE CORP_ID = ?                          \n ");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(sqlParams.get("CORP_ID"));
            
            WAFLogger.debug(sql.toString());
            WAFLogger.debug(persistent.getParameters());
            
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
//          e.printStackTrace();
            WAFLogger.error(this.getClass().getName() + " --> deleteSuppInfo() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.toString());
            WAFLogger.error(e);
            conn.setRollback();
            throw e;
            
        }
        
        finally {
            super.conn.end();
        }
    }
	
	
	/**
     * 목록을 검색하여 리턴
     * @param Params
     * @return
     */
    private RowSet getList(ValueObject Params) throws Exception {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
        	  sqlStr.append("SELECT A.QUOT_REG_CORP_ID,                                   \n")
		        	.append("       A.JOB_DIV,                                            \n")
		        	.append("       A.IN_OUT_DIV,                                         \n")
		        	.append("       DECODE(A.IN_OUT_DIV,'I','국내','O','국외',A.IN_OUT_DIV) AS IN_OUT_DIV_NM,   \n")
		        	.append("       A.CORP_REG_NO,                                        \n")
		        	.append("       A.CORP_KOR,                                           \n")
		        	.append("       A.REP_NAME,                                           \n")
		        	.append("       A.CORP_TEL,                                           \n")
		        	.append("       A.CHARGE_NAME,                                        \n")
		        	.append("       A.CHARGE_EMAIL,                                       \n")
		        	.append("       A.HP1||A.HP2||A.HP3 AS CHARGE_HP,                     \n")
		        	.append("       A.STATUS_CD,                                          \n")
		        	.append("       DECODE(A.STATUS_CD,'10','사용','미사용') AS STATUS_NM,   \n")
		        	.append("       A.CRE_BY,                                             \n")
		        	.append("       A.CRE_DATE                                            \n")
		        	.append("  FROM QUOT_REG_SUPP_INFO A                                  \n")
		        	.append(" WHERE A.JOB_DIV = '"+params.get("job_div").trim()+"'        \n");
            if(!params.get("corp_no").equals(""))
            {
                sqlStr.append("         AND A.CORP_REG_NO LIKE '%' || '"+params.get("corp_no").trim()+"' || '%'        \n");
            }
            if(!params.get("corp_name").equals(""))
            {
                sqlStr.append("         AND A.CORP_KOR LIKE '%' || '"+params.get("corp_name").trim()+"' || '%'        \n");
            }
            if(!params.get("corp_status").equals(""))
            {
            	sqlStr.append("         AND A.STATUS_CD = '"+params.get("corp_status").trim().toString()+"'                            \n");
            }
            
            sqlStr.append("         ORDER BY STATUS_CD, CRE_DATE DESC, QUOT_REG_CORP_ID DESC     	\n");
            
            WAFLogger.debug(sqlStr.toString());           
            search.setStatement(sqlStr.toString());
            WAFLogger.debug(search.getParameters());            
            
            rowSet = search.execute();
            
        }
        catch (Exception se) {
        	WAFLogger.error(se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            se.printStackTrace();
            throw new Exception(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }

    /**
     * 이메일 전송 메소드(Javamail 사용)
     * @param user
     * @param from_email
     * @param to_name
     * @param to_email
     */
	public RowSet getUserInfo(String acctId) throws Exception{
	    	
	    	Search search = new SearchImpl();
	    	RowSet rowSet = new RowSetImpl();
	    	
	    	StringBuffer sqlstr = new StringBuffer();       
	    	
	    	try {
	    		sqlstr.append(" SELECT  BUSEO       \n");
	    		sqlstr.append("         ,GRADE_NAME              \n");
	    		sqlstr.append("         ,USER_NAME             \n");
	    		sqlstr.append("         ,TEL_NO              \n");
	    		sqlstr.append("         ,HAND_TEL_NO         \n");
	    		sqlstr.append("         ,E_MAIL              \n");
	    		sqlstr.append(" FROM    PUB0120T             \n");
	    		sqlstr.append(" WHERE   ACCT_ID = "+acctId+" \n");
	    		
	    		search.setStatement(sqlstr.toString());
	    		rowSet = search.execute();            
	    	}
	    	catch(WAFSQLException se) {
	    		Logger.err.println("SQL : \n" + se.getStatement());
	    		Logger.err.println("PARAM : \n" + se.getParameter());
	    		throw se;
	    	}
	    	finally {
	    		if( rowSet == null ) rowSet = new RowSetImpl();
	    		return rowSet;
	    	}
	}

    public void sendSMS(String sendAddress, String recvAddress,  String sendUser, String mailContent) {
    	
    	
    	try{
    		super.conn.begin();    		
    		
    		if(!sendAddress.equals("")){
    			PersistentImpl persistent = new PersistentImpl(super.conn.getConnection());
    			
    			StringBuffer smsInsert = new StringBuffer();
    			
    			
    			smsInsert.append("				INSERT INTO T_SEND(NREGID,                                              \n");
    			smsInsert.append("				                   NSVCTYPE,                                            \n");
    			smsInsert.append("				                   NADDRTYPE,                                           \n");
    			smsInsert.append("				                   SADDRS,                                              \n");
    			smsInsert.append("				                   NCONTSTYPE,                                          \n");
    			smsInsert.append("				                   SCONTS,                                              \n");
    			smsInsert.append("				                   SFROM                                                \n");
    			smsInsert.append("				                   )VALUES(                                             \n");
    			smsInsert.append("				                   UMSBIZ_SENDSEQ.NEXTVAL,                              \n");
    			smsInsert.append("				                   3,                                                   \n");
    			smsInsert.append("				                   0,                                                   \n");
    			smsInsert.append("				                   ?,                                                   \n");
    			smsInsert.append("				                   0,                                                   \n");
    			smsInsert.append("				                   '"+mailContent+"',                                   \n");
    			smsInsert.append("				                   ?                                                    \n");
    			smsInsert.append("				                   )													\n");
    			
    			persistent.setStatement(smsInsert.toString());
    			
    			persistent.addParameter(sendAddress);
    			persistent.addParameter(recvAddress);
    			
    			persistent.execute();
    			persistent.clearParameters();
    		}
    	}catch(Exception e){
    		WAFLogger.error(e);
    		e.printStackTrace();
    		super.conn.setRollback();
    	}finally{
    		super.conn.end();
    	}
    }
    
private RowSet getSmtpInfo() {
        
    	Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();       
        
        try {
           	sqlstr.append(" SELECT  CHG_CODE01             \n");
           	sqlstr.append("         ,CHG_CODE02            \n");
           	sqlstr.append("         ,CHG_CODE03            \n");
           	sqlstr.append("         ,CHG_CODE04            \n");
           	sqlstr.append("         ,CHG_CODE05            \n");
           	sqlstr.append("         ,CHG_CODE06            \n");
           	sqlstr.append(" FROM    EBID_COM910T A              \n");
           	sqlstr.append("         ,EBID_COM911T B             \n");
           	sqlstr.append(" WHERE   A.BASECD = B.BASECD    \n");
           	sqlstr.append(" AND     A.BASECD = 'EMAIL'     \n");
           	sqlstr.append(" AND     B.DETAILCD = '001'     \n");
        	
            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();
            return rowSet;
        }
    }
    
public ValueObject getCorp_id(Connection conn) throws Exception{
    
    Persistent persistent_q = null;
    
    
    ValueObject vo = new ValueObject();
    ValueObject row = null;
    StringBuffer sqlstr =  new StringBuffer();
    
    try{
        persistent_q = new PersistentImpl(conn);
        
        sqlstr.append("         SELECT 'Q'||QUOT_REG_CORP_ID_S.NEXTVAL AS SEQ  FROM DUAL                                                 \n");
        
        persistent_q.setStatement(sqlstr.toString()); 
        row = RowSetUtility.getValueObject(persistent_q.query().getRow(0));
        vo.set("seq", row.get("seq"));
    }
    catch(Exception e) {
        e.printStackTrace();
    }finally{
        if(vo == null) vo = new ValueObject();
    }
    return vo;
}

public ValueObject getQuotPurSuppRegList(ValueObject params){
    Search search = new SearchImpl();
    ValueObject vo = null;
    RowSet rowSet = new RowSetImpl();
    
    StringBuffer sqlstr = new StringBuffer();
    
    
    try{
        
    	sqlstr.append("SELECT A.QUOT_REG_CORP_ID,                                   \n")
		    	.append("       A.JOB_DIV,                                            \n")
		    	.append("       A.IN_OUT_DIV,                                         \n")
		    	.append("       A.CORP_REG_NO,                                        \n")
		    	.append("       A.CORP_KOR,                                           \n")
		    	.append("       A.REP_NAME,                                           \n")
		    	.append("       A.CORP_TEL,                                           \n")
		    	.append("       A.CHARGE_NAME,                                        \n")
		    	.append("       A.CHARGE_EMAIL,                                       \n")
		    	.append("       A.HP1,                                                \n")
		    	.append("       A.HP2,                                                \n")
		    	.append("       A.HP3,                                                \n")
		    	.append("       A.STATUS_CD,                                          \n")
		    	.append("       DECODE(A.STATUS_CD,'01','사용','미사용') AS STATUS_NM,   \n")
		    	.append("       A.REMARK,                                             \n")
		    	.append("       A.CRE_BY,                                             \n")
		    	.append("       A.CRE_DATE                                            \n")
		    	.append("  FROM QUOT_REG_SUPP_INFO A                                  \n")
		    	.append(" WHERE A.JOB_DIV = '"+params.get("job_div").trim()+"'        \n")
		    	.append("   AND A.QUOT_REG_CORP_ID = '"+params.get("p_corp_id").trim()+"'        \n");
        
        search.setStatement(sqlstr.toString());
        rowSet = search.execute();
        
        
        WAFLogger.debug(sqlstr.toString());
        WAFLogger.debug(search.getParameters());
        
        if(rowSet.size()>0){
            vo = RowSetUtility.getValueObject(rowSet.getRow(0));
        }
        
    }catch(Exception e){
        e.printStackTrace();
        throw e;
    }finally{
        if( vo == null) vo = new ValueObject();
        return vo;
    }
} 
}