package net.kldp.logviewer.model;

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

import net.kldp.logviewer.common.log.LogViews;
import net.kldp.logviewer.db.LogViewerDB;

import org.apache.log4j.Logger;

public class GetLogDBInfo {
	private static final Logger logger = LogViews.getLogger(GetLogDBInfo.class);
	
	/**
	 * Group의 나무잎을 추가한다.
	 * 
	 * @param group
	 * @param groupName
	 */
	public LogViewerEntry getLeaf(String seqSearch) {
		LogViewerEntry tmpViewEntry = null;
		String selGroup = "SELECT * FROM log_detail WHERE seq = ? AND status_yn = '00'";
		Connection conn = null;
		PreparedStatement stmtGrp = null;
	    ResultSet rsetGrp = null;
	
	    try {
	        conn = LogViewerDB.getInstance().getConnection();
	        stmtGrp = conn.prepareStatement(selGroup);
	        stmtGrp.setString(1, seqSearch);
	        
	        rsetGrp = stmtGrp.executeQuery();	        
	        
	        if(rsetGrp.next()) {
	        	long groupSeq 	= rsetGrp.getBigDecimal("GROUP_SEQ").longValue();
	        	long seq 		= rsetGrp.getBigDecimal("SEQ").longValue();
	        	String name 	= rsetGrp.getString("NAME");
	        	String type 	= rsetGrp.getString("type");
	        	String ftpIP 	= rsetGrp.getString("ftpIP");
	        	String ftpPort 	= rsetGrp.getString("ftpPort");
	        	String ftpID 	= rsetGrp.getString("ftpID");
	        	String ftpPass 	= rsetGrp.getString("ftpPass");
	        	String ftpMode 	= rsetGrp.getString("ftpMode");	        
	        	
	        	String path 	= rsetGrp.getString("PATH");
	        	long delaySecond = rsetGrp.getBigDecimal("DELAY_SECOND").longValue();
	        	
	        	String font 		= rsetGrp.getString("FONT");
	        	String fontSize 	= rsetGrp.getString("font_size");
	        	String fontColor 	= rsetGrp.getString("font_color");
	        	String backgroundColor 	= rsetGrp.getString("background_color");
	        	
	        	String curBackColor 	= rsetGrp.getString("cur_back_color");
	          	String lineCount  		= rsetGrp.getString("line_count");
	          	String alalrmData		= rsetGrp.getString("alalrm_data");
	          	String alalrmBackColor  = rsetGrp.getString("alalrm_back_color");
	          	String alarmType 		= rsetGrp.getString("alarm_type");
	  
	        	tmpViewEntry = new LogViewerEntry(null, groupSeq, seq, name, type, ftpIP, ftpPort, ftpID, 
    					ftpPass, ftpMode, path, delaySecond, font, fontSize, 
    					fontColor, backgroundColor,
    					curBackColor, lineCount, alalrmData, alalrmBackColor, alarmType
    			);
	        }
	        
	    } catch(SQLException e) {
	        e.printStackTrace();
	    } finally {
	        try { rsetGrp.close(); } catch(Exception e) { }
	        try { stmtGrp.close(); } catch(Exception e) { }
	        try { conn.close(); } catch(Exception e) { }
	    }
	    
	    return tmpViewEntry;
	}
	
	/**
	 * 로그정보에 로그 그룹에 로그 이름이 있는지 검사한다.
	 * @param groupName
	 * @param name
	 * @return
	 */
	public LogViewerEntry getLogViewerEntry(String groupNm, String nm) {
		LogViewerEntry tmpViewEntry = null;
		String selGroup = "SELECT * FROM log_detail WHERE group_name = ? AND name = ?";
		Connection conn = null;
		PreparedStatement stmtGrp = null;
	    ResultSet rsetGrp = null;
	
	    try {
	        conn = LogViewerDB.getInstance().getConnection();
	        stmtGrp = conn.prepareStatement(selGroup);
	        stmtGrp.setString(1, groupNm);
	        stmtGrp.setString(2, nm);
	        
	        rsetGrp = stmtGrp.executeQuery();	        
	        
	        if(rsetGrp.next()) {
	        	long groupSeq 	= rsetGrp.getBigDecimal("GROUP_SEQ").longValue();
	        	long seq 		= rsetGrp.getBigDecimal("SEQ").longValue();
	        	String name 	= rsetGrp.getString("NAME");
	        	String type 	= rsetGrp.getString("type");
	        	String ftpIP 	= rsetGrp.getString("ftpIP");
	        	String ftpPort 	= rsetGrp.getString("ftpPort");
	        	String ftpID 	= rsetGrp.getString("ftpID");
	        	String ftpPass 	= rsetGrp.getString("ftpPass");
	        	String ftpMode 	= rsetGrp.getString("ftpMode");	        
	        	
	        	String path 	= rsetGrp.getString("PATH");
	        	long delaySecond = rsetGrp.getBigDecimal("DELAY_SECOND").longValue();
	        	
	        	String font 		= rsetGrp.getString("FONT");
	        	String fontSize 	= rsetGrp.getString("font_size");
	        	String fontColor 	= rsetGrp.getString("font_color");
	        	String backgroundColor 	= rsetGrp.getString("background_color");
	        	
	        	String curBackColor 	= rsetGrp.getString("cur_back_color");
	          	String lineCount  		= rsetGrp.getString("line_count");
	          	String alalrmData		= rsetGrp.getString("alalrm_data");
	          	String alalrmBackColor  = rsetGrp.getString("alalrm_back_color");
	          	String alarmType 		= rsetGrp.getString("alarm_type");
	  
	          	tmpViewEntry = new LogViewerEntry(null, groupSeq, seq, name, type, ftpIP, ftpPort, ftpID, 
    					ftpPass, ftpMode, path, delaySecond, font, fontSize, 
    					fontColor, backgroundColor,
    					curBackColor, lineCount, alalrmData, alalrmBackColor, alarmType
    			);
	        }
	        
	    } catch(SQLException e) {
	        e.printStackTrace();
	    } finally {
	        try { rsetGrp.close(); } catch(Exception e) { }
	        try { stmtGrp.close(); } catch(Exception e) { }
	        try { conn.close(); } catch(Exception e) { }
	    }
	    
	    return tmpViewEntry;
	}
	
	/**
	 * 로그정보를 저장한다.
	 * 
	 * 
	 * 
	 * @param viewEntry LogViewerEntry 객체
	 * @return
	 */
	public void saveLogViewerEntry(LogViewerEntry viewEntry) {

		logger.debug("[저장]" + viewEntry.toString());
		
		String selSeq = "insert into log_detail values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '00', ?, ?, ?, ?, ?)";
		Connection conn = null;
		PreparedStatement stmt = null;
	    ResultSet rsetGrp = null;
	
	    try {
	        conn = LogViewerDB.getInstance().getConnection();
	        stmt = conn.prepareStatement(selSeq);
	        
	        stmt.setLong(1, viewEntry.getGroupSeq());
	        stmt.setLong(2, viewEntry.getSeq());
	        stmt.setString(3, viewEntry.getName());
	        
	        stmt.setString(4, viewEntry.getType());
	        stmt.setString(5, viewEntry.getFtpIP());
	        stmt.setString(6, viewEntry.getFtpPort());
	        stmt.setString(7, viewEntry.getFtpID());
	        stmt.setString(8, viewEntry.getFtpPass());
	        stmt.setString(9, viewEntry.getFtpMode());	        
	        
	        stmt.setString(10, viewEntry.getPath());
	        stmt.setLong(11, viewEntry.getDelaySecond());
	        
	        stmt.setString(12, viewEntry.getFont());
	        stmt.setString(13, viewEntry.getFontSize());
	        stmt.setString(14, viewEntry.getFontcolor());
	        stmt.setString(15, viewEntry.getBackGroundColor());
	        
	        stmt.setString(16, viewEntry.getCurBackColor());
	        stmt.setString(17, viewEntry.getLineCount());
	        stmt.setString(18, viewEntry.getAlalrmData());
	        stmt.setString(19, viewEntry.getAlalrmBackColor());
	        stmt.setString(20, viewEntry.getAlarmType());
	        
	        stmt.execute();
	        conn.commit();
	        
	        logger.debug("저장 되었습니다");
	    } catch(SQLException e) {
	    	try {conn.rollback(); } catch(Exception ee){e.printStackTrace();}
	        e.printStackTrace();
	    } finally {
	        try { rsetGrp.close(); } catch(Exception e) { }
	        try { stmt.close(); } catch(Exception e) { }
	        try { conn.close(); } catch(Exception e) { }
	    }
	}

	/**
	 * 로그정보를 저장한다.
	 * 
	 * @param tmpViewEntry LogViewerEntry 객체
	 * @return
	 */
	public void updateLogViewerEntry(LogViewerEntry viewEntry) {
		logger.debug("[수정]" + viewEntry.toString());

		String selSeq = "update log_detail " +
		" set name = ?, type = ?, path = ?, delay_second = ?, font=?,font_size=?,font_color=?, background_color=?, cur_back_color = ?, line_count = ?, alalrm_data = ?,alalrm_back_color = ?, alarm_type = ? " + 
		"     ftpIP= ?, ftpPort = ?, ftpID = ?, ftpPass = ?, ftpMode=? where seq = ?";
		Connection conn = null;
		PreparedStatement stmt = null;
	    ResultSet rsetGrp = null;
	
	    try {
	        conn = LogViewerDB.getInstance().getConnection();
	        stmt = conn.prepareStatement(selSeq);

	        stmt.setString(1, viewEntry.getName());
	        stmt.setString(2, viewEntry.getType());	        
	        stmt.setString(3, viewEntry.getPath());
	        stmt.setLong(4, viewEntry.getDelaySecond());
	        
	        stmt.setString(5, viewEntry.getFont());
	        stmt.setString(6, viewEntry.getFontSize());
	        stmt.setString(7, viewEntry.getFontcolor());
	        stmt.setString(8, viewEntry.getBackGroundColor());   
	        
	        stmt.setString(9, viewEntry.getCurBackColor());
	        stmt.setString(10, viewEntry.getLineCount());
	        stmt.setString(11, viewEntry.getAlalrmData());
	        stmt.setString(12, viewEntry.getAlalrmBackColor());
	        stmt.setString(13, viewEntry.getAlarmType());
	        
	        stmt.setString(14, viewEntry.getFtpIP());
	        stmt.setString(15, viewEntry.getFtpPort());
	        stmt.setString(16, viewEntry.getFtpID());
	        stmt.setString(17, viewEntry.getFtpPass());
	        stmt.setString(18, viewEntry.getFtpMode());	
	        
	        stmt.setLong(19, viewEntry.getSeq());
	        
	        int retSeq = stmt.executeUpdate();
	        
	        conn.commit();
	        logger.debug("[결과반영수]" + retSeq);
	    } catch(SQLException e) {
	    	try {conn.rollback();} catch(Exception ee) {}
	        e.printStackTrace();
	    } finally {
	        try { rsetGrp.close(); } catch(Exception e) { }
	        try { stmt.close(); } catch(Exception e) { }
	        try { conn.close(); } catch(Exception e) { }
	    }
	}

	/**
	 * 로그정보를 저장한다.
	 * 
	 * @param tmpViewEntry LogViewerEntry 객체
	 * @return
	 */
	public void delLog(long seq) {

		String selSeq = "update log_detail set status_yn = '01' where seq = ?";
		Connection conn = null;
		PreparedStatement stmtGrp = null;
	    ResultSet rsetGrp = null;
	
	    try {
	        conn = LogViewerDB.getInstance().getConnection();
	        stmtGrp = conn.prepareStatement(selSeq);
	        stmtGrp.setLong(1, seq);	        
	        int retSeq = stmtGrp.executeUpdate();
	        
	        conn.commit();
	        logger.debug("[결과반영수]" + retSeq);
	    } catch(SQLException e) {
	    	try {conn.rollback();} catch(Exception ee) {}
	        e.printStackTrace();
	    } finally {
	        try { rsetGrp.close(); } catch(Exception e) { }
	        try { stmtGrp.close(); } catch(Exception e) { }
	        try { conn.close(); } catch(Exception e) { }
	    }
	}
	
	/**
	 * 로그정보를 저장한다.
	 * 
	 * @param tmpViewEntry LogViewerEntry 객체
	 * @return
	 */
	public void delLogGroup(String groupNm) {

		String selSeq = "update log_group set stats_yn = '01' where group_name = ?";
		Connection conn = null;
		PreparedStatement stmtGrp = null;
	    ResultSet rsetGrp = null;
	
	    try {
	        conn = LogViewerDB.getInstance().getConnection();
	        stmtGrp = conn.prepareStatement(selSeq);
	        stmtGrp.setString(1, groupNm);	        
	        int retSeq = stmtGrp.executeUpdate();
	        
	        conn.commit();
	        logger.debug("[결과반영수]" + retSeq);
	    } catch(SQLException e) {
	    	try {conn.rollback();} catch(Exception ee) {}
	        e.printStackTrace();
	    } finally {
	        try { rsetGrp.close(); } catch(Exception e) { }
	        try { stmtGrp.close(); } catch(Exception e) { }
	        try { conn.close(); } catch(Exception e) { }
	    }
	}
	
	/**
	 * 로그정보에 로그 그룹에 로그 이름이 있는지 검사한다.
	 * @param groupName
	 * @param name
	 * @return
	 */
	public long getMaxEntry() {
		long seq = 0l;
		String selGroup = "SELECT count(*) + 1 MAX_SEQ FROM log_detail";
		Connection conn = null;
		PreparedStatement stmtGrp = null;
	    ResultSet rsetGrp = null;
	
	    try {
	        conn = LogViewerDB.getInstance().getConnection();
	        stmtGrp = conn.prepareStatement(selGroup);	        
	        rsetGrp = stmtGrp.executeQuery();	        
	        
	        if(rsetGrp.next()) seq = rsetGrp.getBigDecimal("MAX_SEQ").longValue();
	        logger.debug("[max]" + seq);
	        
	    } catch(SQLException e) {
	        e.printStackTrace();
	    } finally {
	        try { rsetGrp.close(); } catch(Exception e) { }
	        try { stmtGrp.close(); } catch(Exception e) { }
	        try { conn.close(); } catch(Exception e) { }
	    }
	    
	    return seq;
	}
}
