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

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

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.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
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 SYSRightPgmListBiz extends Biz {
    public SYSRightPgmListBiz(HandlerStorage storage) {

        super(storage);
    }

    public void doBiz() throws Exception {

        String rightID    = params.get("right_id");
        String systemId   = params.get("system_id");
        String pgmName    = params.get("pgm_name");
        String pgmGrName  = params.get("pgm_gr_name");
        String screenName = params.get("screen_name");
        
        SYSRightCommon common = new SYSRightCommon();
        
        if( storage.compareEvent("") ){
        	String systemSelectTag =  common.getSystemSelectTag(systemId, "width:80%;height=18;");
            storage.addObject("SystemSelectTag", systemSelectTag);
        } else if( storage.compareEvent("GET_RIGHT_PGM_LIST") ){
            params.set("sheet1", getPgmList(rightID, systemId, pgmName, pgmGrName, screenName));
    		storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        } else if ( storage.compareEvent("DELETE_RIGHT_PGM_LIST") ) {
        	boolean success = false;
			String[] status = super.gets("sheet1.STATUS");

			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						success = deleteData(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();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
        }
    }

    private RowSet getPgmList(String rightID, String systemId, String pgmName, String pgmGrName, String screenName) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        try {

            sqlstr = " SELECT   A.PGM_GR_ID, A.PGM_GR_SEQ, A.PGM_ID, A.PGM_SEQ, C.PGM_GR_NAME, \n"
                   + "          NVL(B.PGM_NAME, 'LINE') AS PGM_NAME, B.SCREEN_NAME, A.GUBUN,   \n"
                   + "          B.SYSTEM_CODE, D.NAME, A.PGM_LIST_ID                           \n"
                   + " FROM     PUB0300T A ,   --프로그램목록                                  \n"
                   + "          PUB0320T B ,   --프로그램                                  \n"
                   + "          PUB0310T C ,   --프로그램그룹                                  \n"
                   + "          PUB0390T D     --시스템                                  \n"
                   + " WHERE    A.PGM_ID = B.PGM_ID(+)                                         \n"
                   + " AND      A.PGM_GR_ID = C.PGM_GR_ID                                      \n"
                   + " AND      B.SYSTEM_ID = D.SYSTEM_ID(+)                                   \n"
                   + " AND      A.RIGHT_ID  = ?                                                \n" ;

            if( !systemId.equals("") )
	           sqlstr += "  AND      ( B.SYSTEM_ID = '" + systemId + "' OR A.PGM_ID IS NULL )    \n"
	                   + "  AND      C.PGM_GR_ID IN ( SELECT DISTINCT X.PGM_GR_ID                \n"
	                   + "                              FROM PUB0300T X, PUB0320T Y, PUB0390T Z  \n"
	                   + "                             WHERE 1 = 1                               \n"
	                   + "                               AND X.PGM_ID = Y.PGM_ID                 \n"
	                   + "                               AND Y.SYSTEM_CODE = Z.SYSTEM_CODE       \n"
	                   + "                               AND Z.SYSTEM_ID = '" + systemId + "'    \n"
	                   + "                               AND X.RIGHT_ID = A.RIGHT_ID             \n"
	                   + "                               AND X.PGM_ID IS NOT NULL )              \n" ; 

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

            if( !pgmGrName.equals("") )
                sqlstr += " AND      C.PGM_GR_NAME LIKE '%" + pgmGrName + "%' \n";

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

            sqlstr += " ORDER BY PGM_GR_SEQ, PGM_SEQ \n";

            search.setStatement(sqlstr);
            search.addParameter(rightID);
            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;
        }
    }

    private boolean deleteData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	DELETE FROM PUB0300T WHERE PGM_LIST_ID = ?			\n ");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setInt(paramIdx++, sqlParams.getInt("pgm_list_id"));
			
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
    
    
}
