/**
 *
 * @(#)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.purchase.biz;


import kr.co.hsnc.common.base.WAFLogger;
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.util.ValueObject;
import kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class VendorPrintCheckViewBiz extends Biz {
	
	private static String ACCT_TABLE     = "PUB0120T";
    private static String R_TABLE        = "PUB0340T";
    private static String R_LIST_TABLE   = "PUB0350T";
    private static String PGM_TABLE      = "PUB0320T";
    private static String PGM_GR_TABLE   = "PUB0310T";
    private static String PGM_LIST_TABLE = "PUB0300T";
    private static String PGM_SYS_TABLE  = "PUB0390T";
    
	public VendorPrintCheckViewBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		WAFLogger.debug("SEARCH TEST");
		
		UserCommon common = new UserCommon();
		
		if(storage.compareEvent("")){
	        RowSet acctTypeRowSet = common.getAcctTypeUseRowSet(storage.getUser().get("ACCT_TYPE_CODE"));
	        storage.addRowSet("AcctTypeRowSet", acctTypeRowSet);
		}
		else {
			params.set("sheet1", getUserList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		
		}
		
		//boolean success = saveData(params);
		//super.storage.setSuccess(true);
	}

	/**
	 * Persistent 를 이용한 Save 처리
	 * @param params
	 * @return
	 * * 입력받은 프로그램 등록 정보를 등록합니다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean saveData(ValueObject params) {
		Persistent persistent_i = null;
		Persistent persistent_u = null;
		Persistent persistent_d = null;
		String sqlstr_i = "";
		String sqlstr_u = "";
		String sqlstr_d = "";

		try {

			conn.begin();
			
			persistent_i = new PersistentImpl(super.conn.getConnection());
			persistent_u = new PersistentImpl(super.conn.getConnection());
			persistent_d = new PersistentImpl(super.conn.getConnection());


			  sqlstr_i += "  INSERT INTO " + PGM_TABLE + "             \n"
				        + "   ( PGM_ID,              \n"
				        + "    PGM_NAME,              \n"
				        + "    SCREEN_NAME,           \n"
				        + "    SYSTEM_ID,             \n"
				        + "    SYSTEM_CODE,           \n"
				        + "    DSCR,                  \n"
				        + "    CRE_BY,                \n"
				        + "    CRE_DATE )             \n"
				        + " VALUES                    \n"
				        + "    ( PUB0320T_S.NEXTVAL,  \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    ?,                     \n"
				        + "    TO_CHAR(sysdate, 'YYYYMMDDHH24MISS') )              \n" ; 
			  
			  sqlstr_u = " UPDATE " + PGM_TABLE + "       \n"
		              + " SET    PGM_NAME    = ?       , \n"
		              + "        SCREEN_NAME = ?       , \n"
		              + "        SYSTEM_ID   = ?       , \n"
		              + "        SYSTEM_CODE = ?       , \n"
		              + "        DSCR        = ?       , \n"
		              + "        UPD_BY      = ?       , \n"
		              + "        UPD_DATE    = TO_CHAR(sysdate, 'YYYYMMDDHH24MISS')   \n"
		              + " WHERE  PGM_ID      = ?         \n" ;
			  
			
			  sqlstr_d = 	" DELETE                   \n"
		                  + " FROM   " + PGM_TABLE + " \n"
		                  + " WHERE  PGM_ID = ?        \n" ;
		        
			/* 중요
			 * system_id 값 형식은  "1@SYS" "[SYSTEM_ID@SYSTEM_CODE]" 
			 * 테이블명  PGM_SYS_TABLE 의 SYSTEM_CODE 값을 저장해야 한다.
			 */
			  
			String[] status  		= super.gets("STATUS");
			String[] pgm_name      	= super.gets("PGM_NAME");
			String[] screen_name    = super.gets("SCREEN_NAME");
			String[] system_id    	= super.gets("SYSTEM_ID");
			String[] dscr     		= super.gets("DSCR");
			String[] pgm_id     	= super.gets("PGM_ID");

			persistent_i.setStatement(sqlstr_i);
			persistent_u.setStatement(sqlstr_u);
			persistent_d.setStatement(sqlstr_d);
			
			if( status != null ) {
				for( int i = 0 ; i < status.length ; i++ ) {
					if( "I".equals(status[i]) ) {						
						persistent_i.addParameter(pgm_name[i].trim());
						persistent_i.addParameter(screen_name[i].trim());
						String[] system_code = system_id[0].split("[@]");
						persistent_i.addParameter(system_code[0]); //<== system_id
						persistent_i.addParameter(system_code[1]); //<== system_code
						persistent_i.addParameter(dscr[i].trim());
						persistent_i.addParameter(params.get("USER_ID"));
						persistent_i.execute();
						persistent_i.clearParameters();
					}
					
					else if( "U".equals(status[i]) ) {
						persistent_u.addParameter(pgm_name[i].trim());
						persistent_u.addParameter(screen_name[i].trim());
						String[] system_code = system_id[0].split("[@]");
						persistent_u.addParameter(system_code[0]); //<== system_id
						persistent_u.addParameter(system_code[1]); //<== system_code
						persistent_u.addParameter(dscr[i].trim());
						persistent_u.addParameter(params.get("USER_ID"));
						persistent_u.addParameter(pgm_id[i]);
						persistent_u.execute();
						persistent_u.clearParameters();
					}
					else if( "D".equals(status[i]) ) {
			
						persistent_d.addParameter(pgm_id[i]);
						persistent_d.execute();
						persistent_d.clearParameters();
					}
				}
			}
			return true;
        }
        catch(Exception e) {
            super.doException(e);
            super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
            super.conn.setRollback();
            return false;
        }
        finally {
        	super.conn.end();
        }
	}

	/**
	 * 사용자 목록을 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getUserList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";
        try {
        	sqlstr += " SELECT	A.USER_SABUN                                                                                   			\n"
        			+ "         ,A.USER_PW                                                                                     			\n"
        			+ "         ,NVL(B.T101NAMEK, USER_NAME) AS USER_NAME                                                      			\n"
        			//+ "         ,(SELECT C.DEPT_NM FROM ETEC_ERPIF.ACZ10101 C WHERE C.ACC_DEPT_CD = NVL(B.DEPT_CODE, A.BUSEO)) AS BUSEO_NM   \n"
        			+ "         ,(SELECT C.DEPT_NM FROM ETEC_ERPIF.ACZ10101 C WHERE C.ACC_DEPT_CD = A.BUSEO) AS BUSEO_NM  					\n"
        			+ "         ,(SELECT A.DEPT_NM  FROM ACZ10100V A  WHERE A.DEPT_CD = A.PSOSO) AS  Assigned_Dept            			\n"
        			+ "         ,NVL(B.T101GRADENM,A.GRADE_NAME) AS POSITION_NAME                                             			\n"
        			+ "         ,C.RIGHT_NAME									                                            			\n"
        			+ "         ,A.TEL_NO                                                                                      			\n"
        			+ "         ,A.HAND_TEL_NO                                                                                 			\n"
        			+ "         ,A.E_MAIL                                                                                      			\n"
        			+ "         ,TO_CHAR(TO_DATE(A.CRE_DATE,'YYYYMMDDHH24MI'),'YYYY-MM-DD') AS CRE_DATE						   			\n"
        			+ "         ,A.ACCT_ID                                                                                     			\n"
        			+ " FROM	PUB0120T A                                                                                     			\n"
        			+ " 		,KFA101V B                                                                                     			\n"
        			//+ " 		,(SELECT MAX(ACCT_ID) AS ACCT_ID, MAX(B.RIGHT_NAME) AS  RIGHT_NAME                             			\n"
        			+ " 		,(SELECT MAX(ACCT_ID) AS ACCT_ID, MAX(B.DSCR) AS  RIGHT_NAME                             				\n"
        			+ " 		FROM PUB0350T A, PUB0340T B                                                                   			\n"
        			+ " 		WHERE A.RIGHT_ID =  B.RIGHT_ID                                                                			\n"
        			+ " 		GROUP BY ACCT_ID			                                                                   			\n"
        			+ " 		) C							                                                                   			\n"
        			+ " WHERE	A.USER_SABUN = B.T101SABUN(+)                                                                  			\n"
        			+ " AND  	A.ACCT_ID = C.ACCT_ID(+)                                                           						\n"
        			+ " AND 	NVL(B.T101DUTYC,A.DUTYC) <> '99'                                                               			\n";
 
     	
           	if( !params.get("user_sabun").equals("") ) {
           		sqlstr += "    AND  A.USER_SABUN = '" + params.get("user_sabun") + "' \n";
          	}

          	if( !params.get("user_name").equals("") ) {
          		sqlstr += "    AND  NVL(B.T101NAMEK, USER_NAME) LIKE '%" + params.get("user_name") + "%' \n";
          	}
          	
          	if( !params.get("dept_cd").equals("") ) {
          		sqlstr += "    AND  NVL(B.T101BUSEO, A.PSOSO) = '" + params.get("dept_cd") + "' \n";
          	}

          	if( !params.get("p_dept_cd").equals("") ) {
          		sqlstr += "    AND  NVL(B.DEPT_CODE, A.BUSEO) = '" + params.get("p_dept_cd") + "' \n";
          	}
          	
          	if( !params.get("acct_type_id").equals("") )
             
          	sqlstr += " AND      A.ACCT_TYPE_ID = '" + params.get("acct_type_id") + "'  \n" ;
            sqlstr += " ORDER BY A.ACCT_ID DESC 							\n" ; 
            	
        	//Paging 처리를 위한 Helper Object 생성
            super.createPageAnchorInfo();
            //한페이지에 가져올 목록의 갯수 지정(미지정시  waf.properties 파일의 기본값을 사용)
            //super.anchor.range = 200;
            //Paging 처리에 사용될 Parameter를  세팅
            super.anchor.setParams(this.params);
            //검색할  SQL 세팅 : SQL은 Paging처리를 하지 않고 작성
            super.anchor.setSQL(sqlstr);
            //전체 Record 숫자를 조회
            super.anchor.doTotalCountSQL(); // 전체 숫자를 조회
        	
            //Paging처리가 된 SQL을 Helper Object에서 가져옴
            String temp = super.anchor.getPagingSQL();
            
        	search.setStatement(temp);
        	WAFLogger.debug(sqlstr);
            rowSet = search.execute();
            /* beoms edit;
             * 리스트성 개인정보 노출 부분은 전사 공통으로 암호 된 내용으로 조회 되게 한다.
            httpsGroup htgroup = new httpsGroup();
            ValueObject vo = new ValueObject();
            RowSetUtility rsu = new RowSetUtility();
            String tel_no = null;
            String hand_tel_no = null;
            String email = null;
            String fax_no = null;
            
            for(int i=0; i<rowSet.size(); i++){
            	Row row = new RowImpl();
            	vo =  rsu.getValueObject(rowSet.getRow(i));

            	tel_no = vo.get("tel_no");
            	hand_tel_no = vo.get("hand_tel_no");
            	email = vo.get("e_mail");
            	
            	tel_no = tel_no.equals("") || tel_no.length()<=8 ? tel_no : htgroup.slitTelNo(tel_no);
            	hand_tel_no = hand_tel_no.equals("") || hand_tel_no.length()<=8 ? hand_tel_no : htgroup.slitTelNo(hand_tel_no);
            	email = email.equals("") || email.length() <= 2 ? email : htgroup.SetDecEmailGroup(email, "");
            	
            	vo.set("tel_no", tel_no);
            	vo.set("hand_tel_no", hand_tel_no);
            	vo.set("e_mail", email);
            	
            	for(int j=0;j<vo.size();j++){
            		row.addColumn(vo.getKey(j), vo.get(vo.getKey(j)));	
            	}
            	rowSet.setRow(i, row);
            }
            */
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
	}
}
