/**
 * @(#)file    TermsAgreeBiz.java
 * @(#)author  이성민
 * @(#)version 1.0
 *
 */
package kr.co.udapsoft.ebid.supplier.portal.own.biz;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

import kr.co.hsnc.common.base.WAFLogger;
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.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 ChargeDetailSaveBiz extends Biz {

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

	/* (non-Javadoc)
	 * @see kr.co.hwenc.system.Biz#doBiz()
	 */
	public void doBiz() throws Exception 
	{
		
		if(storage.compareEvent("SAVE_CHARGE"))
		{
		    boolean success = false;
		    
		    
		    try 
            {
                conn.begin();
                
                
                ValueObject check_id = new ValueObject();
                
                //System.out.println("------------------->" + params.get("p_my_user_id"));
                
                check_id  = getSRegId(params);
                if(check_id.get("cnt").equals("0")) //2015.12.11
                {
                    if(params.get("p_my_user_id").equals(""))
                    {
                        success = insert_info(params); 
                    }
                    else
                    {
                        success = save_info(params); 
                    }
                }
                else
                {
                    success = false;
                }
                
                //System.out.println(success);
                
            } catch (Exception e) {
                //System.out.println("e======>" + e);
                e.printStackTrace();
                conn.setRollback();
            } finally {
                conn.end();
            }
		    
		    
		    if(success == true)
		    {
		        String message = "등록성공";
	            ValueObject errMsg = new ValueObject();
	            errMsg.set("errMsg", message);
	            storage.addValueObject("VALUEOBJECT", errMsg);
		    }
		    else
		    {
		        String message = "사용중인 아이디 또는 동일한 담당자명이 존재합니다.";
                ValueObject errMsg = new ValueObject();
                errMsg.set("errMsg", message);
                storage.addValueObject("VALUEOBJECT", errMsg);
		    }
		    
            super.storage.setSuccess(success);
		}
		else if(storage.compareEvent("DELETE_CHARGE"))
        {
            boolean success = false;
            
            
            try
            {
                conn.begin();
                
                success = deletge_charge(params); 
                
                if(success == true)
                {
                    String message = "삭제성공";
                    ValueObject errMsg = new ValueObject();
                    errMsg.set("errMsg", message);
                    storage.addValueObject("VALUEOBJECT", errMsg);
                }
                else
                {
                    String message = "삭제를 실패하였습니다. 관리자에게 문의하세요.";
                    ValueObject errMsg = new ValueObject();
                    errMsg.set("errMsg", message);
                    storage.addValueObject("VALUEOBJECT", errMsg);
                }
                
                
            } catch (Exception e) {
                //System.out.println("e======>" + e);
                e.printStackTrace();
                conn.setRollback();
            } finally {
                conn.end();
            }
            
            super.storage.setSuccess(success);
            
        }
	}
	
	public ValueObject getSuppJoinRequestList(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            sqlstr.append("             SELECT                  \n")
                   .append("                   CORP_ID              \n") 
                   .append("                 , USER_ID              \n")
                   .append("                 , USER_NAME                \n")
                   .append("                 , '' PASSWD                       \n")
                   .append("                 , USER_NAME            \n")
                   .append("                 , DIVISION            \n")
                   .append("                 , POSITION         \n")
                   .append("                 , TEL_NUM         \n")
                   .append("                 , FAX_NUM         \n")
                   .append("                 , HP1                          \n")
                   .append("                 , HP2                          \n")
                   .append("                 , HP3                          \n")
                   .append("                 , EMAIL                                        \n")
                   .append("                  FROM SUPP_CHARGE                          \n")
                   .append("                 WHERE USER_ID= ?                                                                                                    \n");
            
                                    
                                    
                                    
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("p_user_id"));
            rowSet = search.execute();
            
            
            //System.out.println(sqlstr.toString());
            //System.out.println(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;
        }
    } 

	/**
	 * 담당자 등록
	 */
	private boolean insert_info(ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(super.conn.getConnection());
            
    sqlstr.append("             INSERT INTO SUPP_CHARGE                   \n")
            .append("               (                                               \n")
            .append("                   CHARGE_SEQ                \n")
            .append("                 , USER_ID                \n")
            .append("                 , CORP_ID                \n")
            .append("                 , USER_NAME               \n")
            .append("                 , PASSWD                        \n")
            .append("                 , DIVISION            \n")
            .append("                 , POSITION         \n")
            .append("                 , TEL_NUM         \n")
            .append("                 , FAX_NUM         \n")
            .append("                 , HP1                          \n")
            .append("                 , HP2                          \n")
            .append("                 , HP3                          \n")
            .append("                 , EMAIL                                        \n")
            .append("                 , USE_YN                                        \n")
            .append("                 , DEFAULT_YN                                        \n")
            .append("                 , RIGHT_ID                                        \n")
            .append("                 , CRE_BY                                        \n")
            .append("                 , CRE_DATE                      \n")
            .append("               )VALUES(                            \n")
            .append("                    SUPP_CHARGE_S.nextval                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  F_SHA256(?)                               \n")//SHA256
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  (SELECT RIGHT_ID FROM SUPP_CHARGE WHERE DEFAULT_YN = 'Y' AND CORP_ID = ?)                                       \n")
            .append("                 ,  ?                                       \n")
            .append("                 ,  SYSDATE                            \n")
            .append("               )                                        \n");
            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("user_id"));
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(params.get("p_user_name"));
            persistent.addParameter(params.get("p_passwd"));
            persistent.addParameter(params.get("p_division"));
            persistent.addParameter(params.get("p_position"));
            persistent.addParameter(params.get("p_tel_num"));
            persistent.addParameter(params.get("p_fax_num"));
            persistent.addParameter(params.get("p_hp1"));
            persistent.addParameter(params.get("p_hp2"));
            persistent.addParameter(params.get("p_hp3"));
            persistent.addParameter(params.get("p_email"));
            persistent.addParameter("Y");
            persistent.addParameter("N");
            persistent.addParameter(params.get("p_corp_id"));
            persistent.addParameter(params.get("p_user_id"));
            //System.out.println(persistent.getParameters());
            //System.out.println(persistent);
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
            //System.out.println("e===>" +e);
            e.printStackTrace();
            return false;            
        }
    }
	
	
	/**
	 * 
	 * 담당자 삭제
	 */
	
	
	
	private boolean deletge_charge(ValueObject params) throws Exception
    {
	    Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(super.conn.getConnection());
            
    sqlstr.append("             DELETE FROM SUPP_CHARGE                  \n")
            .append("                 WHERE USER_ID = ?                  \n")
            .append("                 and CORP_ID = ?                    \n");  //2015.12.11 현재까지 중복된 아이디 존재. 삭제할때 회사 확인. 
    
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_my_user_id"));
            persistent.addParameter(params.get("p_corp_id"));
            
            
            //System.out.println(persistent.getParameters());
            //System.out.println(sqlstr.toString());
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
            //System.out.println("e===>" +e);
            e.printStackTrace();
            return false;            
        }
    }


	/**
	 * 
	 * 담당자 수정
	 */
	private boolean save_info(ValueObject params) throws Exception
    {
        
        Persistent persistent = null;
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            persistent = new PersistentImpl(super.conn.getConnection());
            
    sqlstr.append("             UPDATE SUPP_CHARGE SET                  \n")
            .append("                   USER_NAME  = ?              \n")
            .append("                 , PASSWD   = F_SHA256(?)                    \n")//SHA256
            .append("                 , DIVISION   = ?         \n")
            .append("                 , POSITION  = ?       \n")
            .append("                 , TEL_NUM   = ?      \n")
            .append("                 , FAX_NUM  = ?       \n")
            .append("                 , HP1    = ?                      \n")
            .append("                 , HP2    = ?                      \n")
            .append("                 , HP3    = ?                      \n")
            .append("                 , EMAIL   = ?                                     \n")
            .append("                 , USE_YN  = ?       \n")
            .append("                 , UPD_BY   = ?                                     \n")
            .append("                 , UPD_DATE   = SYSDATE                                     \n")
            .append("                 WHERE USER_ID= ?                                                                                                    \n");
            
            
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("p_user_name"));
            persistent.addParameter(params.get("p_passwd"));
            persistent.addParameter(params.get("p_division"));
            persistent.addParameter(params.get("p_position"));
            persistent.addParameter(params.get("p_tel_num"));
            persistent.addParameter(params.get("p_fax_num"));
            persistent.addParameter(params.get("p_hp1"));
            persistent.addParameter(params.get("p_hp2"));
            persistent.addParameter(params.get("p_hp3"));
            persistent.addParameter(params.get("p_email"));
            persistent.addParameter(params.get("use_yn"));
            persistent.addParameter(params.get("user_id"));
            persistent.addParameter(params.get("user_id"));
            
            
            
            //System.out.println(persistent.getParameters());
            //System.out.println(sqlstr.toString());
            persistent.execute();
            persistent.clearParameters();
            
            return true;
            
            
        } catch(Exception e) {
            //System.out.println("e===>" +e);
            e.printStackTrace();
            return false;            
        }
    }
	
	
	public ValueObject getSRegId(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            sqlstr.append("      SELECT                                         \n")                    
                   .append("              COUNT(*) CNT                          \n")
                   .append("            FROM SUPP_CHARGE                        \n");
                   if(!params.get("p_my_user_id").equals(""))  //수정
                   {
                       sqlstr.append("         WHERE NVL(USER_ID, 'null') != ? and CORP_ID = ? AND USER_NAME = ?   \n");
                       //2015.12.11 중복아이디 체크, 동일회사내 동일한 이름 체크(입찰서명시 오류발생)
                   }
                   else  //신규
                   {
                       sqlstr.append("         WHERE USER_ID  = ?                      \n")
                             .append("         OR (CORP_ID = ? AND USER_NAME  = ? )    \n");
                   }
             
                   
            search.setStatement(sqlstr.toString());
            //if(params.get("p_my_user_id").equals(""))  
            //{
            	search.addParameter(params.get("user_id").trim());
            //}
          
            search.addParameter(params.get("p_corp_id").trim());
            search.addParameter(params.get("p_user_name").trim());
            
            rowSet = search.execute();
            
            //System.out.println(sqlstr.toString());
            //System.out.println(search.getParameters());
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
        	 System.out.println(e.getMessage());
             e.printStackTrace();
            throw e;
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    } 
	
	
}