/**
 * @(#)file    SYSRightPgmListAddFormBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.7.2
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.buyer.sycs.security.biz;

import java.sql.Connection;

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

	public void doBiz() throws Exception {
        if( storage.compareEvent("") ){
        	String rightID = params.get("right_id");
        	String pgmGrSeq = params.get("pgm_gr_id");
        	RowSet pgmGrList = getPgmGrList(rightID);
        	String pgmGrSelectStr = getPgmGrSelectTag(pgmGrList, pgmGrSeq);
        	storage.addObject("PgmGrSelectStr", pgmGrSelectStr);
        	
        	SYSRightCommon common = new SYSRightCommon();
        	String systemSelectTag =  common.getSystemSelectTag("", "style='width:150px;height=18;'");
        	storage.addObject("SystemSelectTag", systemSelectTag);
        	
        } else if ( storage.compareEvent("GET_SYS_RIGHT_PGM_LIST_ADD_FORM") ) {
        	String systemId   = params.get("system_id");
            String pgmName    = params.get("pgm_name");
            String screenName = params.get("screen_name");
            String searchGb   = params.get("search_gb_value");
            String rightID    = params.get("right_id");
        	
            params.set("sheet1", gePgmList(systemId, pgmName, screenName, searchGb, rightID));
        	storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        } else if ( storage.compareEvent("SAVE_SYS_RIGHT_PGM_LIST_ADD_FORM") ) {
        	boolean success = false;
			String[] status = super.gets("sheet1.status");

			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					if(params.get("gubun").equals("L")) {
						success = addPgmList(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					} else {
						if (IBSheet7Utils.equalStatusUpdate(status[i])) {
							success = addPgmList(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
						}
					}
					
				}
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}
	}

    /**
     * 프로그램 그룹 리스트를 SELECT Html Tag로 변환해서 리턴한다. 
	 * @param sysRowSet
	 * @param selectedValue
	 * @return
	 */
	private static String getPgmGrSelectTag(RowSet sysRowSet, String selectedValue) {
    
		 String selectStr ="<SELECT id='pgm_gr_seq_1' name='pgm_gr_seq' style='width:150px;height=18;'>";

        ValueObject rowObject = new ValueObject();

        selectStr += "<OPTION value=''>-- 전체 --</OPTION>";

        boolean newGrID = false;

        int size = sysRowSet.size();
        for( int i = 0 ; i < size ; i++ ) {
            rowObject = RowSetUtility.getValueObject(sysRowSet.getRow(i));

            String pgmGrSeq  = rowObject.get("PGM_GR_SEQ");
            String pgmGrName = rowObject.get("PGM_GR_NAME");

            if( pgmGrSeq.equals("") ) {

                newGrID = true;
                pgmGrSeq = "NEW_" + rowObject.get("PGM_GR_ID");
            }


            selectStr += "<OPTION value='" 
                       + pgmGrSeq + "' " + (pgmGrSeq.equals(selectedValue) ? "selected" : "") + ">" 
                       + ( newGrID ? "신규" : ("" + (i + 1)) ) + " : "   
                       + pgmGrName + "</OPTION>";
        }
    
        selectStr += "</SELECT>";
        
        return selectStr;
    }

    /**
     * 프로그램 그룹 리스트 RowSet을 리턴한다.
	 * @param rightID
	 * @return
	 */
	private RowSet getPgmGrList(String rightID) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        try {

            sqlstr = " SELECT    A.PGM_GR_SEQ, B.PGM_GR_ID, B.PGM_GR_NAME  \n"
                   + " FROM      ( SELECT   DISTINCT PGM_GR_ID, PGM_GR_SEQ \n"
                   + "             FROM     PUB0300T         \n"
                   + "             WHERE    RIGHT_ID = ?                  \n"
                   + "             ORDER BY PGM_GR_SEQ                     \n"
                   + "           ) A ,                                     \n"
                   + "           PUB0310T B                    \n"
                   + " WHERE    A.PGM_GR_ID(+) = B.PGM_GR_ID               \n"
                   + " ORDER BY A.PGM_GR_SEQ, B.PGM_GR_NAME                \n" ;
            search.setStatement(sqlstr);
            search.addParameter(rightID);
            listRowSet = search.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPgmGrList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {
            if( listRowSet == null ) listRowSet = new RowSetImpl();
            return listRowSet;
        }
    }
	
	private RowSet gePgmList(String systemId, String pgmName, String screenName, String searchGb, String rightID) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        try {
            sqlstr = " SELECT A.*, B.NAME                   \n"
                   + " FROM   PUB0320T A ,     \n"
                   + "        PUB0390T B       \n"
                   + " WHERE  A.SYSTEM_ID = B.SYSTEM_ID \n" ;

            if( !systemId.equals("") )            
                sqlstr += " AND    A.SYSTEM_ID = '" + systemId + "' \n";

            if( !pgmName.equals("") )
                sqlstr += " AND    A.PGM_NAME LIKE '%" + pgmName + "%' \n";

            if( !screenName.equals("") )
                sqlstr += " AND    A.SCREEN_NAME LIKE '%" + screenName + "%' \n";

             if( searchGb.equalsIgnoreCase("REG") ) {	// 화면 검색조건 type 의add

                sqlstr += " AND    A.PGM_ID IN ( SELECT DISTINCT PGM_ID              \n"
                        + "                      FROM   PUB0300T       \n"
                        + "                      WHERE  RIGHT_ID = " + rightID + "   \n"
                        + "                      AND    PGM_ID IS NOT NULL         ) \n";
            }
            else if( searchGb.equalsIgnoreCase("NOT") ) {	// 화면 검색조건 type 의 미등록

                sqlstr += " AND    A.PGM_ID NOT IN ( SELECT DISTINCT PGM_ID              \n"
                        + "                          FROM   PUB0300T       \n"
                        + "                          WHERE  RIGHT_ID = " + rightID + "   \n"
                        + "                          AND    PGM_ID IS NOT NULL         ) \n";
            }

            sqlstr += " ORDER BY A.PGM_NAME ";
            
            search.setStatement(sqlstr);

            listRowSet = search.execute();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getPgmList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( listRowSet == null ) listRowSet = new RowSetImpl();
            return listRowSet;
        }
    }

    /**
     * 권한별 프로그램 목록을 등록한다.
	 * @param gubun
	 * @param pgmGrID
	 * @param pgmGrSeq
	 * @param pgmID
	 * @param pgmSeq
	 * @param rightID
	 * @param acctID
	 * @return
	 */
	private boolean addPgmList(Connection connection, ValueObject sqlParams) {
        String sqlstr = "";
        
        String pgmGrID = "";
        int acctID = 0;
        
        if( storage.getUser() != null ) {
            acctID = storage.getUser().getInt("ACCT_ID");
        }
        
        String rightID  = params.get("right_id");
        String pgmGrSeq = params.get("pgm_gr_seq");
        String gubun    = params.get("gubun");
        String pgmID   =  sqlParams.get("pgm_id");
        

        if( pgmGrSeq.indexOf("NEW_") != -1 ) {
            pgmGrID = pgmGrSeq.substring(4, pgmGrSeq.length());
            pgmGrSeq = getNextPgmGrSeq(rightID);
        }
        else {
            pgmGrID = getPgmGrID(rightID, pgmGrSeq);
        }
        
        if( gubun.equals("L") ) {
            String temp = "";
            pgmID = temp;
        }
        
        try {
            conn.begin();
            
            sqlstr = " INSERT INTO PUB0300T                   \n"
                   + " ( PGM_LIST_ID, GUBUN, PGM_GR_ID, PGM_GR_SEQ, PGM_ID, \n"
                   + "   PGM_SEQ, RIGHT_ID, CRE_BY, CRE_DATE )              \n"
                   + " VALUES                                               \n"
                   + " ( PUB0300T_S.NEXTVAL, ?, ?, ?, ?,                    \n"
                   + "   ?, ?, ?, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') )                                 \n" ;

            Persistent persistent = new PersistentImpl(conn.getConnection());
            persistent.setStatement(sqlstr);
 
            int	pgmSeq = getNextPgmSeq(rightID, pgmGrID, pgmGrSeq);

            persistent.addParameter(gubun);
            persistent.addParameter(pgmGrID);
            persistent.addParameter(pgmGrSeq);
            persistent.addParameter(pgmID);

            persistent.addParameter(pgmSeq);
            persistent.addParameter(rightID);
            persistent.addParameter(acctID);
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> addPgmList() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            conn.setRollback();
            storage.setDetailMessage(e);
            return false;
        }
        finally {
            conn.end();
        }
    }

    /**
     * 프로그램 그룹 일련번호를 입력받아 프로그램 그룹 ID를 리턴한다.
	 * @param rightID
	 * @param pgmGrSeq
	 * @return
	 */
	private String getPgmGrID(String rightID, String pgmGrSeq) {
        
        Search search = new SearchImpl();
        String sqlstr = "";
        try {
            sqlstr = " SELECT PGM_GR_ID              \n"
                   + " FROM   PUB0300T \n"
                   + " WHERE  RIGHT_ID   = ?         \n"
                   + " AND    PGM_GR_SEQ = ?         \n"
                   + " ORDER BY PGM_GR_ID DESC       \n" ;
            search.setStatement(sqlstr);
            
            search.addParameter(rightID);
            search.addParameter(pgmGrSeq);

            return search.execute().getRow(0).get("PGM_GR_ID");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getPgmList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }

    /**
     * 새로운(Next) 프로그램 그룹의 일련번호를 리턴한다.
	 * @param rightID
	 * @return
	 */
	private String getNextPgmGrSeq(String rightID) {
        
        Search search = new SearchImpl();
        String sqlstr = "";
        try {
            sqlstr = " SELECT NVL(MAX(PGM_GR_SEQ), 0) + 1 AS NEXT_PGM_GR_SEQ \n"
                   + " FROM   PUB0300T                         \n"
                   + " WHERE  RIGHT_ID = ?                                   \n" ;

            search.setStatement(sqlstr);
            search.addParameter(rightID);

            return search.execute().getRow(0).get("NEXT_PGM_GR_SEQ");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getNextPgmGrSeq() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }

    /**
     * 새로운(Next) 프로그램 목록의 일련번호를 리턴한다.
	 * @param rightID
	 * @param pgmGrID
	 * @param pgmGrSeq
	 * @return
	 */
	private int getNextPgmSeq(String rightID, String pgmGrID, String pgmGrSeq) throws Exception {
        
        Search search = new SearchImpl();
        String sqlstr = "";
        try {
            sqlstr = " SELECT NVL(MAX(PGM_SEQ), 0) + 1 AS NEXT_PGM_SEQ  \n"
                   + " FROM   PUB0300T                    \n"
                   + " WHERE  RIGHT_ID = ?                              \n"
                   + " AND    PGM_GR_ID = ?                             \n"
                   + " AND    PGM_GR_SEQ = ?                            \n" ;
            search.setStatement(sqlstr);
            
            search.addParameter(rightID);
            search.addParameter(pgmGrID);
            search.addParameter(pgmGrSeq);

            return search.execute().getRow(0).getInt("NEXT_PGM_SEQ");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getPgmList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            throw e;
        }
    }
	
}

