/***********************************************************************************
 * @@ Program Name : UserView.java Description : 한명의 사용자에대한 상세 정보 Author : 강원중 Create Date : 2004.10.13 History :
 * @@
 ***********************************************************************************/

package kr.co.kihyun.beans.user;

import java.sql.ResultSet;
import java.sql.SQLException;
import kr.co.kihyun.beans.entity.Emul;
import kr.co.kihyun.beans.entity.MDept;
import kr.co.kihyun.beans.entity.MUser;
import kr.co.kihyun.beans.entity.MoumiEntity;

import kr.co.kihyun.db.DBManager;

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

public class UserView extends DBManager {
	private static final Logger LOG = LoggerFactory.getLogger(UserView.class);

	private ResultSet rs = null;

	private String id = "";
	private String passwd = "";
	private String name = "";
	private String slNo1 = "";
	private String slNo2 = "";
	private String email = "";
	private String phone = "";
	private String cellPhone = "";
	private int sysAuth = 0;
	private String deptID = "";
	private String deptName = "";
	private String passcode = "";
	private String partID = "";
	private String partName = "";
    private String note = "";
	private int partSysAuth = 0;
    private String docId = "";

	/******** 사용자의 소속 기관 한글 이름 ********/
	public void executeQuery(String id) {
		StringBuilder sql = new StringBuilder(300);
		try {
			sql.append("select us.passwd, sp_cm_dec_fu(us.name), us.sl_no_1, us.sl_no_2, us.email, us.phone, ")
					.append("us.cell_phone, us.sys_auth, dp.id, dp.name, dp.passcode, pt.id, pt.name, pt.sys_auth ")
					.append("from ").append(MoumiEntity.getTableName(MUser.class)).append(" us, ")
					.append(MoumiEntity.getTableName(MDept.class)).append(" dp, ")
					.append(MoumiEntity.getTableName(MDept.class)).append(" pt ")
					.append("where us.dept_id = dp.id and dp.organ=pt.id and us.id=?");
			rs = execQuery(sql, id);
			if (rs.next()) {
				this.id = id;
				passwd = rs.getString(1);
				name = rs.getString(2);
				slNo1 = rs.getString(3);
				slNo2 = rs.getString(4);
				email = rs.getString(5);
				phone = rs.getString(6);
				cellPhone = rs.getString(7);
				sysAuth = rs.getInt(8);
				deptID = rs.getString(9);
				deptName = rs.getString(10);
				passcode = rs.getString(11);
				partID = rs.getString(12);
				partName = rs.getString(13);
				partSysAuth = rs.getInt(14);

			}
             // System.out.println("::::::userView executeQuery:::::::"+sql);
		} 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 void executeQuery(String userID, String deptID) {
		StringBuilder sql = new StringBuilder(350);
		try {
			sql.append("select us.passwd, sp_cm_dec_fu(us.name), us.sl_no_1, us.sl_no_2, us.email, us.phone, ")
					.append("us.cell_phone, us.sys_auth, dp.id, dp.name, dp.passcode, pt.id, pt.name, pt.sys_auth ")
					.append("from ").append(MoumiEntity.getTableName(MUser.class)).append(" us, ")
					.append(MoumiEntity.getTableName(MDept.class)).append(" dp, ")
					.append(MoumiEntity.getTableName(MDept.class)).append(" pt, ")
					.append(MoumiEntity.getTableName(Emul.class)).append(" em ")
					.append("where dp.organ=pt.id and us.id=em.user_id and dp.id=em.dept_id ")
					.append("and em.user_id=? and em.dept_id=?");

			rs = execQuery(sql, userID, deptID);
			if (rs.next()) {
				id = userID;
				passwd = rs.getString(1);
				name = rs.getString(2);
				slNo1 = rs.getString(3);
				slNo2 = rs.getString(4);
				email = rs.getString(5);
				phone = rs.getString(6);
				cellPhone = rs.getString(7);
				sysAuth = rs.getInt(8);
				this.deptID = rs.getString(9);
				deptName = rs.getString(10);
				passcode = rs.getString(11);
				partID = rs.getString(12);
				partName = rs.getString(13);
				partSysAuth = rs.getInt(14);

			}
            //System.out.println("::::::userView executeQuery:::::::"+sql);

		} 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 void getNoteReson(String id) {
		StringBuilder sql = new StringBuilder(250);
		try {

//			sql.append("SELECT COALESCE(CAST(CAST(NOTE AS CLOB(4096)) as varchar(5000)),' '),DOC_ID FROM  MOUMI_TOT_REPORT WHERE ID ='"+id+"'"); //DB2
			sql.append("SELECT NOTE AS note, DOC_ID FROM  MOUMI_TOT_REPORT WHERE ID ='"+id+"'");
		   rs = execQuery(sql);
          // System.out.println(sql);

           if (rs.next()) {
			   note = rs.getString(1);
               docId = rs.getString(2);
			}

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

	public String getID() {
		return id;
	}

	public String getPasswd() {
		return passwd;
	}

	public String getName() {
		return name;
	}

	public String getSlNo1() {
		return slNo1;
	}

	public String getSlNo2() {
		return slNo2;
	}

	public String getEmail() {
		return email;
	}

	public String getPhone() {
		return phone;
	}

	public String getCellPhone() {
		return cellPhone;
	}

	public int getSysAuth() {
		return sysAuth;
	}

	public String getDeptID() {
		return deptID;
	}

	public String getDeptName() {
		return deptName;
	}

	public String getPasscode() {
		return passcode;
	}

	public String getPartID() {
		return partID;
	}

	public String getPartName() {
		return partName;
	}

    public String getNote() {
		return note;
	}

	public int getPartSysAuth() {
		return partSysAuth;
	}

    public String getDocId() {
		return docId;
	}

}