package kr.co.kihyun.beans.totsys.doc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import kr.co.kihyun.beans.entity.MoumiEntity;
import kr.co.kihyun.beans.entity.TotDoc;
import kr.co.kihyun.beans.entity.TotDocProcess;
import kr.co.kihyun.beans.entity.TotDocType;
import kr.co.kihyun.beans.entity.TotReport;
import kr.co.kihyun.beans.entity.TotTable;

import kr.co.kihyun.db.DBManager;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class Doc extends DBManager {

	private static final Logger LOG = LoggerFactory.getLogger(Doc.class);
	private ResultSet rs = null;

	public int getCount(Long docID) {
		StringBuilder sql = new StringBuilder(150);
		int count = 0;

		try {
			sql.append("select count(*) from ").append(MoumiEntity.getTableName(TotReport.class)).append(" where doc_id=? and type=0");

			rs = execQuery(sql.toString(), docID);
			rs.next();
			count = rs.getInt(1);

			return count;
		} catch (SQLException sqlEx) {
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[]{sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			throw new RuntimeException(sqlEx);
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			close(rs);
			execClose();
		}
	}

	public String getRepID(Long docID) {
		StringBuilder sql = new StringBuilder(150);
		try {
			sql.append("select case rep_id when null then id else rep_id end dc_rep_id from ").append(MoumiEntity.getTableName(TotDoc.class)).append(" where id=?");
			rs = execQuery(sql.toString(), docID);

			if (rs.next()) {
				return rs.getString(1);
			}
			return "";
		} catch (SQLException sqlEx) {
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[]{sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			throw new RuntimeException(sqlEx);
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			close(rs);
			execClose();
		}
	}

	public String getRegisterDept(Long docID) {
		StringBuilder sql = new StringBuilder(120);

		try {
			sql.append("select dept_id from ").append(MoumiEntity.getTableName(TotDoc.class)).append(" where id=?");
			rs = execQuery(sql.toString(), docID);

			if (rs.next()) {
				return rs.getString(1);
			}
			return "";
		} catch (SQLException sqlEx) {
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[]{sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			throw new RuntimeException(sqlEx);
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			close(rs);
			execClose();
		}
	}

    public String getRegisterID(Long docID){
		String sql = "";
		String userID = "";
		try{
			sql = "select user_id from moumi_tot_doc where id=?";
			rs = execQuery(sql, docID);
			if(rs.next()){
				userID = rs.getString(1);
			}
			return userID;
		}catch(SQLException sqlEx){
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[] {sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			return "NOREGISTERID";
		}catch(Exception ex){
			ex.printStackTrace();
			return "NOREGISTERID";
		}finally{
			close(rs);
			execClose();
		}
	}

	public int getTableType(Long docID) {
		StringBuilder sql = new StringBuilder(120);

		try {
			sql.append("select type from ").append(MoumiEntity.getTableName(TotTable.class)).append(" where doc_id=?");
			rs = execQuery(sql.toString(), docID);

			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (SQLException sqlEx) {
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[]{sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			throw new RuntimeException(sqlEx);
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			close(rs);
			execClose();
		}
	}

	public String getTableTypes(Long docID) {
		StringBuilder sql = new StringBuilder(120);

		try {
			sql.append("select types from ").append(MoumiEntity.getTableName(TotTable.class)).append(" where doc_id=?");
			rs = execQuery(sql.toString(), docID);

			if (rs.next()) {
				return rs.getString(1);
			}
			return "";
		} catch (SQLException sqlEx) {
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[]{sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			throw new RuntimeException(sqlEx);
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			close(rs);
			execClose();
		}
	}

	public int getTotRange(Long docID) {
		StringBuilder sql = new StringBuilder(120);

		try {
			sql.append("select tot_range from ").append(MoumiEntity.getTableName(TotDoc.class)).append(" where id=?");
			rs = execQuery(sql.toString(), docID);

			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (SQLException sqlEx) {
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[]{sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			throw new RuntimeException(sqlEx);
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			close(rs);
			execClose();
		}
	}

	public boolean isDynaTable(Long docID) {
		boolean isDynaTable = false;
		if (getTableTypes(docID).indexOf("1") > -1) {
			isDynaTable = true;
		}
		return isDynaTable;
	}

	public boolean isEndDate(Long docID) throws SQLException {
		StringBuilder sql = new StringBuilder(150);

		sql.append("select end_date from ").append(MoumiEntity.getTableName(TotDoc.class))
				.append(" where id=? and end_date < ?");
		rs = execQuery(sql.toString(), docID, new Date());
		boolean isEndDate = rs.next();
		close(rs);
		execClose();

		return isEndDate;
	}

	public Date getEndDate(Long docID) {
		StringBuilder sql = new StringBuilder(120);

		try {
			sql.append("select end_date from ").append(MoumiEntity.getTableName(TotDoc.class)).append(" where id=?");
			rs = execQuery(sql.toString(), docID);
			if (rs.next()) {
				return rs.getDate(1);
			}
			return null;
		} catch (SQLException sqlEx) {
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[]{sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			throw new RuntimeException(sqlEx);
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			close(rs);
			execClose();
		}
	}

	public String getDocName(Long docID) {
		StringBuilder sql = new StringBuilder(120);

		try {
			sql.append("select name from ").append(MoumiEntity.getTableName(TotDoc.class)).append(" where id=?");
			rs = execQuery(sql.toString(), docID);
			if (rs.next()) {
				return rs.getString(1);
			}
			return "";
		} catch (SQLException sqlEx) {
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[]{sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			throw new RuntimeException(sqlEx);
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			close(rs);
			execClose();
		}
	}

	public int getAccAuth(Long docID) {
		StringBuilder sql = new StringBuilder(120);

		try {
			sql.append("select acc_auth from ").append(MoumiEntity.getTableName(TotDoc.class)).append(" where id=?");
			rs = execQuery(sql.toString(), docID);
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (SQLException sqlEx) {
			LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}",
					new Object[]{sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage(), sql});
			throw new RuntimeException(sqlEx);
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			close(rs);
			execClose();
		}
	}
}