/**
 * @(#)file    SuppDeptRequestListBiz.java
 * @(#)author  mklee
 * @(#)version 1.0
 * @(#)date    2017-02-20
 * @(#)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.supplier.outside.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 SuppDeptRequestListBiz extends Biz {

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

	/* (non-Javadoc)
	 * @see kr.co.hwenc.system.Biz#doBiz()
	 */
	public void doBiz() throws Exception {		
	    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 = deleteSuppInfo(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
                }
            }
            
            super.storage.setSuccess(success);
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		else if(storage.compareEvent("ALL_CONFIRM"))
        {
	        boolean success = false;
            
            String[] status = super.gets("sheet1.STATUS");
            
            for (int i = 0; i < status.length; i++) 
            {
                if (IBSheet7Utils.equalStatusUpdate(status[i])) 
                {
                    success = updateStatus(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
                }
            }
            
            super.storage.setSuccess(success);
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
		
	}
	
	
	private boolean updateStatus(ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            
            sql.append("    UPDATE SUPP_INFO_REQ SET          \n ");
            sql.append("    STATUS = '00'          \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() + " --> deleteChargeData() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.toString());
            WAFLogger.error(e);
            conn.setRollback();
            throw e;
            
        }
        
        finally {
            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_REQ          \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                                                          \n")
                    .append("             A.CORP_ID                                            \n")
                    .append("           , A.CORP_REG_NO                                            \n")
                    .append("           , A.CORP_KOR                                                       \n")
                    .append("           , A.REP_NAME                                                        \n")
                    .append("           , A.USER_NAME                                                        \n")
                    .append("           , A.EMAIL                                                        \n")
                    .append("           , A.HP1 || '-' ||  A.HP2 || '-' ||  A.HP3 HP_NUM                                                        \n")
                    .append("           , DECODE(A.CORP_DIV_CD, '1', '법인', '개인') CORP_DIV_CD           \n")
                    .append("           , A.STATUS  STATUS_CODE           \n")
                    .append("           , F_GET_CODE_NAME2('OUT_SUPP_REQ_STATUS', A.STATUS)  STATUS_NM           \n")
                    .append("           , A.REQ_DATE, A.REQ_REMARK, A.REQ_EMP, A.DEPT_NM, B.USER_NAME REQ_EMP_NM  , a.cre_by                                \n")
                    .append("           FROM SUPP_INFO_REQ A, PUB0120T B                                       \n")
                    .append("        WHERE A.REQ_EMP = B.USER_SABUN(+)                                           \n");
                    //.append("          AND A.JOB_DIV = ?                                           \n")
            if(!params.get("corp_no").equals(""))
            {
                sqlStr.append("         AND A.CORP_REG_NO LIKE '%' || ? || '%'        \n");
            }
            if(!params.get("corp_name").equals(""))
            {
                sqlStr.append("         AND A.CORP_KOR LIKE '%' || ? || '%'        \n");
            }
            if(!params.get("corp_status").equals(""))
            {
                sqlStr.append("         AND A.STATUS = ?                            \n");
            }
            
            sqlStr.append("         ORDER BY NVL(STATUS_CODE,'AA') ,  REQ_DATE DESC, CORP_ID DESC   	\n");
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(params.get("corp_status").trim());
            
            //search.addParameter(params.get("job_div").trim());
            
            if(!params.get("corp_no").equals(""))
            {
            search.addParameter(params.get("corp_no").trim());
            }
            if(!params.get("corp_name").equals(""))
            {
                search.addParameter(params.get("corp_name").trim());
            }
            if(!params.get("corp_status").equals(""))
            {
                search.addParameter(params.get("corp_status").trim().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;
        }
    }
	
}