/***********************************************************************************
 * @@ Program Name : AssignList.java Description : 보고자 이관 List Author : Create Date : History :
 * @@
 ***********************************************************************************/

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

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
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.beans.entity.TotReport;
import kr.co.kihyun.beans.entity.UserHistory;

import kr.co.kihyun.db.DBManager;

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

public class RepTransfer extends DBManager {
	private static final Logger LOG = LoggerFactory.getLogger(RepTransfer.class);
	private ResultSet rs = null;

	private int count = 0;
	private String[] userIDList = null;
	private String[] userNameList = null;
	private String[] deptNameList = null;
	private String[] deptIDList = null;
	private String[] userPhoneList = null;
	private String[] changeUserIDList = null;
	private String[] changeUserDateList = null;
	private String[] upperDeptIDList = null;

	public void transListQuery(String deptID, int sysAuth) {
		StringBuilder sql = new StringBuilder(300);

		try {
			sql.append("SELECT us.id, sp_cm_dec_fu(us.name), em.dept_id, dp.name, us.phone FROM ")
					.append(MoumiEntity.getTableName(Emul.class)).append(" em, ")
					.append(MoumiEntity.getTableName(MUser.class)).append(" us, ")
					.append(MoumiEntity.getTableName(MDept.class)).append(" dp ")
					.append("WHERE em.user_id=us.id and em.dept_id=dp.id AND em.dept_id=? ")
					.append("AND us.sys_auth <= ? and (us.del_type='0' or us.del_type='N')");
			rs = execQuery(sql.toString(), deptID, sysAuth);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			userIDList = new String[count];
			userNameList = new String[count];
			userPhoneList = new String[count];
			deptNameList = new String[count];
			deptIDList = new String[count];

			for (int i = 0; rs.next(); i++) {
				userIDList[i] = rs.getString(1);
				userNameList[i] = rs.getString(2);
				deptIDList[i] = rs.getString(3);
				deptNameList[i] = rs.getString(4);
				userPhoneList[i] = rs.getString(5);
			}
		} 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 searchListQuery(int sysAuth, String addString, String findWord) {
		searchListQuery(null, sysAuth, addString, findWord);
	}

	public void searchListQuery(String deptID, int sysAuth, String findOption, String findWord) {
		StringBuilder sql = new StringBuilder(300);
		ArrayList<Object> paramList = new ArrayList<Object>();

		try {
			sql.append("SELECT us.id, sp_cm_dec_fu(us.name), us.dept_id, dp.name, us.phone, dp.upper_dept FROM ")
					.append(MoumiEntity.getTableName(Emul.class)).append(" em, ")
					.append(MoumiEntity.getTableName(MUser.class)).append(" us, ")
					.append(MoumiEntity.getTableName(MDept.class)).append(" dp ")
					.append("WHERE em.user_id=us.id and (us.del_type='0' or us.del_type='N') ")
					.append("and em.dept_id=dp.id AND us.sys_auth < 3 AND us.sys_auth >= 0 ")
					.append("AND dp.name NOT LIKE '%테스트%' AND lower(dp.name) NOT LIKE '%test%' AND ");
			if (deptID != null) {
				sql.append("em.dept_id=? AND ");
				paramList.add(deptID);
			}
			if ("dept".equals(findOption)) {
				sql.append("dp.name like ? ");
				paramList.add("%" + findWord + "%");
			} else if ("name".equals(findOption)) {
				sql.append("sp_cm_dec_fu(us.name) like ? ");
				paramList.add("%" + findWord + "%");
			} else if ("id".equals(findOption)) {
				sql.append("us.id like ? ");
				paramList.add("%" + findWord + "%");
			} else {
				sql.append("(dp.name like ? OR sp_cm_dec_fu(us.name) like ? OR us.id like ?) ");
				paramList.add("%" + findWord + "%");
				paramList.add("%" + findWord + "%");
				paramList.add("%" + findWord + "%");
			}
			sql.append("order by us.priority");

			rs = execQuery(sql.toString(), paramList.toArray());
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			userIDList = new String[count];
			userNameList = new String[count];
			deptIDList = new String[count];
			deptNameList = new String[count];
			userPhoneList = new String[count];
			upperDeptIDList = new String[count];

			for (int i = 0; rs.next(); i++) {
				userIDList[i] = rs.getString(1);
				userNameList[i] = rs.getString(2);
				deptIDList[i] = rs.getString(3);
				deptNameList[i] = rs.getString(4);
				userPhoneList[i] = rs.getString(5);
				upperDeptIDList[i] = rs.getString(6);
			}
		} 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 getDocType(Long docID, String deptID) {
		StringBuilder sql = new StringBuilder(90);

		int doc_type = 0;
		try {
			sql.append("select doc_type from ").append(MoumiEntity.getTableName(TotReport.class))
					.append(" where doc_id=? and dept_id=?");

			rs = execQuery(sql.toString(), docID, deptID);
			if (rs.next()) {
				doc_type = rs.getInt(1);
			}
			return doc_type;
		} 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 userTransListQuery(Long docID, String deptID) {
		StringBuilder sql = new StringBuilder(90);

		try {
			sql.append("SELECT user_id, change_id, write_date FROM ")
					.append(MoumiEntity.getTableName(UserHistory.class))
					.append(" where doc_id=? and dept_id=?");

			rs = execQuery(sql.toString(), docID, deptID);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			userIDList = new String[count];
			changeUserIDList = new String[count];
			changeUserDateList = new String[count];

			for (int i = 0; rs.next(); i++) {
				userIDList[i] = rs.getString(1);
				changeUserIDList[i] = rs.getString(2);
				changeUserDateList[i] = rs.getString(3);
			}
		} 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 usertransListQuery(Long docID, String deptID) {
		StringBuilder sql = new StringBuilder(90);

		try {
			sql.append("SELECT user_id, change_id, write_date FROM ")
					.append(MoumiEntity.getTableName(UserHistory.class))
					.append(" where doc_id=? and dept_id=?");

			rs = execQuery(sql.toString(), docID, deptID);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			userIDList = new String[count];
			changeUserIDList = new String[count];
			changeUserDateList = new String[count];

			for (int i = 0; rs.next(); i++) {
				userIDList[i] = rs.getString(1);
				changeUserIDList[i] = rs.getString(2);
				changeUserDateList[i] = rs.getString(3);
			}

		} 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 getCount() {
		return count;
	}

	public String[] getUserIDList() {
            //24.Public 메소드로부터 반환된 Private배열_CWE-495 : Update by KWON,HAN
	    //	return userIDList;
                
            String[] ret = null;
            if( this.userIDList != null) {
                ret = new String[userIDList.length];
                for (int i=0; i<userIDList.length; i++) {
                    ret[i] = this.userIDList[i];
                }
            }
            return ret;
            //====================================================================                  
	}

	public String[] getUserNameList() {
	    //	return userNameList;
                
            String[] ret = null;
            if( this.userNameList != null) {
                ret = new String[userNameList.length];
                for (int i=0; i<userNameList.length; i++) {
                    ret[i] = this.userNameList[i];
                }
            }
            return ret;                
	}

	public String[] getUserPhoneList() {
	    //	return userPhoneList;
                
            String[] ret = null;
            if( this.userPhoneList != null) {
                ret = new String[userPhoneList.length];
                for (int i=0; i<userPhoneList.length; i++) {
                    ret[i] = this.userPhoneList[i];
                }
            }
            return ret;                  
	}

	public String[] getDeptIDList() {
	    //	return deptIDList;
                
            String[] ret = null;
            if( this.deptIDList != null) {
                ret = new String[deptIDList.length];
                for (int i=0; i<deptIDList.length; i++) {
                    ret[i] = this.deptIDList[i];
                }
            }
            return ret;                   
	}

	public String[] getDeptNameList() {
	    //	return deptNameList;
                
            String[] ret = null;
            if( this.deptNameList != null) {
                ret = new String[deptNameList.length];
                for (int i=0; i<deptNameList.length; i++) {
                    ret[i] = this.deptNameList[i];
                }
            }
            return ret;                 
	}

	public String[] getChangeUserIDList() {
	    //	return changeUserIDList;
                
            String[] ret = null;
            if( this.changeUserIDList != null) {
                ret = new String[changeUserIDList.length];
                for (int i=0; i<changeUserIDList.length; i++) {
                    ret[i] = this.changeUserIDList[i];
                }
            }
            return ret;                 
	}

	public String[] getChangeUserDateList() {
	    //	return changeUserDateList;
                
            String[] ret = null;
            if( this.changeUserDateList != null) {
                ret = new String[changeUserDateList.length];
                for (int i=0; i<changeUserDateList.length; i++) {
                    ret[i] = this.changeUserDateList[i];
                }
            }
            return ret;                  
	}

	public String[] getUpperDeptIDList() {
	    //	return upperDeptIDList;
                
            String[] ret = null;
            if( this.upperDeptIDList != null) {
                ret = new String[upperDeptIDList.length];
                for (int i=0; i<upperDeptIDList.length; i++) {
                    ret[i] = this.upperDeptIDList[i];
                }
            }
            return ret;                 
	}
}