package kr.co.kihyun.beans.entity;

import java.sql.ResultSet;
import java.sql.SQLException;
//import java.io.ByteArrayOutputStream;
//import java.io.ByteArrayInputStream;
//import java.io.BufferedReader;
//import java.util.Arrays;
//import java.sql.Blob;
import java.sql.Clob;
//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.SysAuth;
//import kr.co.kihyun.beans.entity.TotDoc;
//import kr.co.kihyun.beans.entity.TotReport;
//import kr.co.kihyun.beans.entity.TotReportProcess;
//import kr.co.kihyun.beans.entity.TotReportType;
//import java.util.ArrayList;
//import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
//import java.util.HashSet;
import java.util.List;
import java.util.Map;
//import java.util.Set;
//import javax.jdo.JDOObjectNotFoundException;
//import javax.jdo.PersistenceManager;
//import javax.jdo.Query;
//import javax.jdo.annotations.IdentityType;
//import javax.jdo.annotations.NullValue;
//import javax.jdo.annotations.PersistenceCapable;
//import javax.jdo.annotations.Persistent;
//import kr.co.kihyun.beans.entity.ecross.IRemotePersistable;
//import kr.co.kihyun.beans.entity.util.QueryImpl;
//import kr.co.kihyun.io.IUploadable;
//import kr.co.kihyun.lang.MString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import kr.co.kihyun.db.DBManager;

public class TotDoc2  extends DBManager {

   private static final Logger LOG = LoggerFactory.getLogger(TotDoc2.class);

	private ResultSet rs = null;
	private int count = 0;
	private String[] nameList = null;
    private String[] nameUp   = null;
	private String[] nameDept = null;
	private String[] ownerName = null;
    private String[] id = null;
    private String[] phone = null;
    private Date[] submitDate = null;
    private String[] founDation = null;
    private int[] grade = null;
    private int[] prss = null;
    private int[] downDocId = null;
    private byte[] attachments = null;
    private int tCount;
    private String[] nullNameup = null;
    private int[] reportId = null;
    private String[] deptId = null;
    private int[] docId = null;
    private String[] organ = null;
    private String[] upperDept = null;
    private Clob[] clob = null;
    private String[] note = null;
    private int[] recog = null;
    private String[] attachment = null;
    private String[] delType = null;
    private int[] type = null;
    private Map<String, List<Byte>> attachmente=null;

  /*private String[] data = null;
    private String[] totData = null;*/

	/******** 카테고리 전체 리스트 ********/
	public void executeQuery() {
		StringBuilder sql = new StringBuilder(250);
		try {

              

			sql.append(" SELECT GR.NAME,DT.NAME,CASE WHEN SP_CM_DEC_FU(RT.OWNER_NAME) IS NULL THEN '&NBSP;' ELSE SP_CM_DEC_FU(RT.OWNER_NAME) END")
			   .append("FROM ")
               .append(MoumiEntity.getTableName(TotReport.class)).append(" rt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" dt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" gr ")
			   .append(" WHERE DT.ID=RT.DEPT_ID  AND DT.ORGAN=GR.ID ORDER BY ")
               .append("  CASE WHEN DT.GRADE IS NULL THEN 0 ELSE 1 END ")
			   .append(" ,DT.ORGAN, DT.GRADE DESC,DT.FOUNDATION,DT.ORGAN,DT.NAME");

          //System.out.println(sql);
			rs = execQuery(sql);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			nameList = new String[count];
			nameDept = new String[count];
			ownerName = new String[count];

			for (int i = 0; rs.next(); i++) {
				nameList[i] = rs.getString(1);
				nameDept[i] = rs.getString(2);
				ownerName[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 executeQuery(Long docID) {

		StringBuilder sql = new StringBuilder(250);

        String strDocid = Long.toString(docID);

        try {

	         sql.append("SELECT GR.NAME,DT.NAME,CASE WHEN SP_CM_DEC_FU(RT.OWNER_NAME) IS NULL THEN '&NBSP;' ELSE SP_CM_DEC_FU(RT.OWNER_NAME) END")
			   .append(" FROM ")
               .append(MoumiEntity.getTableName(TotReport.class)).append(" rt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" dt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" gr ")
			   .append("WHERE RT.DOC_ID = '"+strDocid+"'  AND   DT.ID=RT.DEPT_ID")
               .append(" AND DT.ORGAN=GR.ID ORDER BY CASE WHEN DT.GRADE IS NULL THEN 0 ELSE 1 END")
			   .append(" ,DT.ORGAN,DT.GRADE DESC,DT.FOUNDATION,DT.ORGAN,DT.NAME");

          //System.out.println(sql);
			rs = execQuery(sql);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}
			nameList = new String[count];
			nameDept = new String[count];
			ownerName = new String[count];

			for (int i = 0; rs.next(); i++) {
				nameList[i] = rs.getString(1);
				nameDept[i] = rs.getString(2);
				ownerName[i] = rs.getString(3);
			}
            //System.out.println(sql);

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

    /******** 카테고리 전체 리스트 ********/
	public void executeQuery2() {

		StringBuilder sql = new StringBuilder(250);

        try {
            sql.append("SELECT GR.NAME  ")
               .append(",UP.NAME  ")
               .append(",DT.NAME  ")
               .append(",SP_CM_DEC_FU(RT.OWNER_NAME) ")
               .append(",RT.USER_ID  ")
               .append(",DT.PHONE  ")
               .append(",RT.SUBMIT_DATE ")
               .append(",DT.FOUNDATION   ")
               .append(",DT.GRADE ")
               .append(",RT.PRSS ")
               .append(",RT.DOWN_DOC_ID  ")
               .append(",RT.ID  ")
               .append(",RT.DEPT_ID  ")
               .append(",RT.DOC_ID  ")
               .append(",DT.ORGAN  ")
               .append(",DT.UPPER_DEPT  ")              
               .append(",CAST(CAST(RT.NOTE AS CLOB(4096)) as varchar(5000)) ")
               .append(",RT.RECOG ")
               .append(",CAST(CAST(RT.ATTACHMENTS AS BLOB(4096)) as varchar(5000))")
               .append(",RT.DEL_TYPE  ")
               .append(",RT.TYPE ")
               .append(" FROM ")
               .append(MoumiEntity.getTableName(TotReport.class)).append(" rt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" dt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" gr, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" up ")
               .append(" WHERE    ")
               .append(" AND DT.ID=RT.DEPT_ID   ")
               .append(" AND DT.ORGAN=GR.ID  ")
               .append(" AND DT.UPPER_DEPT=UP.ID  ")
             //.append(" AND RT.PRSS ='"+findWord+"'")
               .append(" ORDER BY CASE WHEN DT.GRADE IS NULL THEN 0 ELSE 1 END,DT.ORGAN,DT.GRADE 	");

          //System.out.println(sql);
			rs = execQuery(sql);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			nameList   = new String[count];
            nameUp     = new String[count];
			nameDept   = new String[count];
			ownerName  = new String[count];
            id         = new String[count];
            phone      = new String[count];
            submitDate = new Date[count];
            founDation = new String[count];
            grade      = new int[count];
            prss       = new int[count];
            downDocId  = new int[count];
            reportId   = new int[count];
            deptId     = new String[count];
            docId      = new int[count];
            organ      = new String[count];
            upperDept  = new String[count];
            note       = new String[count];
            recog      = new int[count];
            attachment = new String[count];
            delType    = new String[count];
            type       = new int[count];
            tCount     = count;

			for (int i = 0; rs.next(); i++) {
				nameList[i]   = rs.getString(1);
                nameUp[i]     = rs.getString(2);
				nameDept[i]   = rs.getString(3);
				ownerName[i]  = rs.getString(4);
                id[i]         = rs.getString(5);
                phone[i]      = rs.getString(6);
                submitDate[i] = rs.getDate(7);
                founDation[i] = rs.getString(8);
                grade[i]      = rs.getInt(9);
                prss[i]       = rs.getInt(10);
                downDocId[i]  = rs.getInt(11);
                reportId[i]   = rs.getInt(12);
                deptId[i]     = rs.getString(13);
                docId[i]      = rs.getInt(14);
                organ[i]      = rs.getString(15);
                upperDept[i]  = rs.getString(16);
                note[i]       = rs.getString(17);              
                recog[i]      = rs.getInt(18);
                attachment[i] = rs.getString(19);
                delType[i]    = rs.getString(20);
                type[i]       = rs.getInt(21);
			}
            //System.out.println(sql+"  /T-count "+tCount);

		} 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 executeQuery2(Long docID,String findOption,String findWord) {

		StringBuilder sql = new StringBuilder(250);

        String strDocid = Long.toString(docID);

         String tmpAppend;
               if(findOption.equals("all") || findOption.equals("")){
                   tmpAppend = "";
               }else if(findOption.equals("notcomp")){
                   tmpAppend =  " AND RT.PRSS <> '"+findWord+"'";
               }else if(findOption.equals("howSort")){
                   tmpAppend =  " AND (RT.PRSS = '6' AND RT.SUBMIT_DATE IS NOT NULL) ";
               }else if(findOption.equals("mDept.name")){
                   tmpAppend =  "AND (GR.NAME LIKE '%"+findWord+"%' OR UP.NAME LIKE '%"+findWord+"%' OR DT.NAME LIKE '%"+findWord+"%')";
               }else {
                   tmpAppend =  " AND RT.PRSS = '"+findWord+"'";
               }

        try {

            sql.append(" SELECT GR.NAME  ")
               .append(",UP.NAME  ")
               .append(",DT.NAME  ")
               .append(",SP_CM_DEC_FU(RT.OWNER_NAME) ")
               .append(",RT.USER_ID  ")
               .append(",DT.PHONE  ")
               .append(",RT.SUBMIT_DATE ")
               .append(",DT.FOUNDATION   ")
               .append(",DT.GRADE ")
               .append(",RT.PRSS ")
               .append(",RT.DOWN_DOC_ID  ")
               .append(",RT.ID  ")
               .append(",RT.DEPT_ID  ")
               .append(",RT.DOC_ID  ")
               .append(",DT.ORGAN  ")
               .append(",DT.UPPER_DEPT  ")
               .append(",CAST(CAST(RT.NOTE AS CLOB(4096)) as varchar(5000)) ")
               .append(",RT.RECOG ")
               .append(",CAST(CAST(RT.ATTACHMENTS AS BLOB(4096)) as varchar(5000))")
               .append(",RT.DEL_TYPE  ")
               .append(",RT.TYPE ")
               .append(" FROM ")
               .append(MoumiEntity.getTableName(TotReport.class)).append(" rt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" dt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" gr, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" up ")
               .append(" WHERE    ")
               .append(" RT.DOC_ID = '"+strDocid+"'")
               .append(" AND DT.ID=RT.DEPT_ID ")
               .append(" AND DT.ORGAN=GR.ID ")
               .append(" AND DT.UPPER_DEPT=UP.ID ")
               .append(" "+tmpAppend+" ")
               .append(" ORDER BY CASE WHEN DT.GRADE IS NULL THEN 0 ELSE 1 END,DT.ORGAN,DT.GRADE ");

          //System.out.println(sql);
			rs = execQuery(sql);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			nameList   = new String[count];
            nameUp     = new String[count];
			nameDept   = new String[count];
			ownerName  = new String[count];
            id         = new String[count];
            phone      = new String[count];
            submitDate = new Date[count];
            founDation = new String[count];
            grade      = new int[count];
            prss       = new int[count];
            downDocId  = new int[count];
            reportId   = new int[count];
            deptId     = new String[count];
            docId      = new int[count];
            organ      = new String[count];
            upperDept  = new String[count];
            note       = new String[count];
            recog      = new int[count];
            attachment = new String[count];
            delType    = new String[count];
            type       = new int[count];
            tCount     = count;

			for (int i = 0; rs.next(); i++) {
				nameList[i]   = rs.getString(1);
                nameUp[i]     = rs.getString(2);
				nameDept[i]   = rs.getString(3);
				ownerName[i]  = rs.getString(4);
                id[i]         = rs.getString(5);
                phone[i]      = rs.getString(6);
                submitDate[i] = rs.getDate(7);
                founDation[i] = rs.getString(8);
                grade[i]      = rs.getInt(9);
                prss[i]       = rs.getInt(10);
                downDocId[i]  = rs.getInt(11);
                reportId[i]   = rs.getInt(12);
                deptId[i]     = rs.getString(13);
                docId[i]      = rs.getInt(14);
                organ[i]      = rs.getString(15);
                upperDept[i]  = rs.getString(16);
                note[i]       = rs.getString(17);
                recog[i]      = rs.getInt(18);
                attachment[i] = rs.getString(19);
                delType[i]    = rs.getString(20);
                type[i]       = rs.getInt(21);
			}
            //System.out.println(sql+"  tCount "+tCount+"::findoption::"+findOption+"::::findword::::"+findWord);

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


    /******** 카테고리 전체 리스트 ********/
	public void executeQuery3(Long docID,String findOption,String findWord) {

		StringBuilder sql = new StringBuilder(250);

        String strDocid = Long.toString(docID);

             String tmpAppend;
               if(findOption.equals("all") || findOption.equals("")){
                   tmpAppend = "";
               }else if(findOption.equals("notcomp")){
                   tmpAppend =  " AND RT.PRSS <> '"+findWord+"'";
               }else if(findOption.equals("howSort")){
                   tmpAppend =  " AND (RT.PRSS = '6' AND RT.SUBMIT_DATE IS NOT NULL) ";
               }else if(findOption.equals("mDept.name")){
                   tmpAppend =  "AND (GR.NAME LIKE '%"+findWord+"%' OR UP.NAME LIKE '%"+findWord+"%' OR DT.NAME LIKE '%"+findWord+"%')";
               }else {
                   tmpAppend =  " AND RT.PRSS = '"+findWord+"'";
               }

        try {
            sql.append("  SELECT GR.NAME ")
               .append(",NULL	")
               .append(",DT.NAME	")
               .append(",SP_CM_DEC_FU(RT.OWNER_NAME) ")
               .append(",RT.USER_ID   ")
               .append(",DT.PHONE	")
               .append(",RT.SUBMIT_DATE   ")
               .append(",DT.FOUNDATION	 ")
               .append(",DT.GRADE	 ")
               .append(",RT.PRSS     ")
               .append(",RT.DOWN_DOC_ID	 ")
               .append(",RT.ID	 ")
               .append(",RT.DEPT_ID    ")
               .append(",RT.DOC_ID   ")
               .append(",DT.ORGAN     ")
               .append(",DT.UPPER_DEPT   ")
               .append(",CAST(CAST(RT.NOTE AS CLOB(4096)) as varchar(5000)) ")
               .append(",RT.RECOG ")
               .append(",CAST(CAST(RT.ATTACHMENTS AS BLOB(4096)) as varchar(5000))")
               .append(",RT.DEL_TYPE  ")
               .append(",RT.TYPE ")
               .append(" FROM     ")
               .append(MoumiEntity.getTableName(TotReport.class)).append(" rt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" dt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" gr, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" up ")
               .append(" WHERE     ")
               .append(" RT.DOC_ID = '"+strDocid+"'	")
               .append(" AND DT.ID=RT.DEPT_ID   ")
               .append(" AND DT.ORGAN=GR.ID ")
               .append(" AND DT.UPPER_DEPT=UP.ID   ")
               .append(" "+tmpAppend+" ")
               .append(" ORDER BY CASE WHEN DT.GRADE IS NULL THEN 0 ELSE 1 END,DT.ORGAN,DT.GRADE ");

          //System.out.println(sql);
			rs = execQuery(sql);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			nameList   = new String[count];
            nameUp     = new String[count];
			nameDept   = new String[count];
			ownerName  = new String[count];
            id         = new String[count];
            phone      = new String[count];
            submitDate = new Date[count];
            founDation = new String[count];
            grade      = new int[count];
            prss       = new int[count];
            downDocId  = new int[count];
            reportId   = new int[count];
            deptId     = new String[count];
            docId      = new int[count];
            organ      = new String[count];
            upperDept  = new String[count];
            note       = new String[count];
            recog      = new int[count];
            attachment = new String[count];
            delType    = new String[count];
            type       = new int[count];
            tCount     = count;

			for (int i = 0; rs.next(); i++) {
				nameList[i]   = rs.getString(1);
                nameUp[i]     = rs.getString(2);
				nameDept[i]   = rs.getString(3);
				ownerName[i]  = rs.getString(4);
                id[i]         = rs.getString(5);
                phone[i]      = rs.getString(6);
                submitDate[i] = rs.getDate(7);
                founDation[i] = rs.getString(8);
                grade[i]      = rs.getInt(9);
                prss[i]       = rs.getInt(10);
                downDocId[i]  = rs.getInt(11);
                reportId[i]   = rs.getInt(12);
                deptId[i]     = rs.getString(13);
                docId[i]      = rs.getInt(14);
                organ[i]      = rs.getString(15);
                upperDept[i]  = rs.getString(16);
                note[i]       = rs.getString(17);
                recog[i]      = rs.getInt(18);
                attachment[i] = rs.getString(19);
                delType[i]    = rs.getString(20);
                type[i]       = rs.getInt(21);
			}
             //System.out.println(sql+"  tCount "+tCount+"::findoption::"+findOption+"::::findword::::"+findWord);

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

     /******** 카테고리 전체 리스트 ********/
	public void executeSortschool() {

		StringBuilder sql = new StringBuilder(250);

        try {
               sql.append("	SELECT GR.NAME	")
                  .append(",UP.NAME ")
                  .append(",DT.NAME ")
                  .append(",SP_CM_DEC_FU(RT.OWNER_NAME) ")
                  .append(",RT.USER_ID ")
                  .append(",DT.PHONE ")
                  .append(",RT.SUBMIT_DATE	")
                  .append(",DT.FOUNDATION	")
                  .append(",DT.GRADE ")
                  .append(",RT.PRSS ")
                  .append(",RT.DOWN_DOC_ID ")
                  .append(",RT.ID ")
                  .append(",RT.DEPT_ID ")
                  .append(",RT.DOC_ID ")
                  .append(",DT.ORGAN ")
                  .append(",DT.UPPER_DEPT	")
                  .append(",CAST(CAST(RT.NOTE AS CLOB(4096)) as varchar(5000)) ")
                  .append(",RT.RECOG ")
                  .append(",CAST(CAST(RT.ATTACHMENTS AS BLOB(4096)) as varchar(5000))")
                  .append(",RT.DEL_TYPE  ")
                  .append(",RT.TYPE ")
                  .append(" FROM  ")
                  .append(MoumiEntity.getTableName(TotReport.class)).append(" rt, ")
                  .append(MoumiEntity.getTableName(MDept.class)).append(" dt, ")
                  .append(MoumiEntity.getTableName(MDept.class)).append(" gr, ")
                  .append(MoumiEntity.getTableName(MDept.class)).append(" up ")
                  .append(" WHERE  ")
                  .append("  DT.ID=RT.DEPT_ID 	")
                  .append(" AND DT.ORGAN=GR.ID  ")
                  .append(" AND DT.UPPER_DEPT=UP.ID ")
                  //.append(" AND RT.PRSS ='"+findWord+"'")
                  .append(" ORDER BY DT.GRADE ASC,DT.NAME ASC ");

          //System.out.println(sql);
			rs = execQuery(sql);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			nameList   = new String[count];
            nameUp     = new String[count];
			nameDept   = new String[count];
			ownerName  = new String[count];
            id         = new String[count];
            phone      = new String[count];
            submitDate = new Date[count];
            founDation = new String[count];
            grade      = new int[count];
            prss       = new int[count];
            downDocId  = new int[count];
            reportId   = new int[count];
            deptId     = new String[count];
            docId      = new int[count];
            organ      = new String[count];
            upperDept  = new String[count];
            note       = new String[count];
            recog      = new int[count];
            attachment = new String[count];
            delType    = new String[count];
            type       = new int[count];
            tCount     = count;

			for (int i = 0; rs.next(); i++) {
				nameList[i]   = rs.getString(1);
                nameUp[i]     = rs.getString(2);
				nameDept[i]   = rs.getString(3);
				ownerName[i]  = rs.getString(4);
                id[i]         = rs.getString(5);
                phone[i]      = rs.getString(6);
                submitDate[i] = rs.getDate(7);
                founDation[i] = rs.getString(8);
                grade[i]      = rs.getInt(9);
                prss[i]       = rs.getInt(10);
                downDocId[i]  = rs.getInt(11);
                reportId[i]   = rs.getInt(12);
                deptId[i]     = rs.getString(13);
                docId[i]      = rs.getInt(14);
                organ[i]      = rs.getString(15);
                upperDept[i]  = rs.getString(16);
                note[i]       = rs.getString(17);
                recog[i]      = rs.getInt(18);
                attachment[i] = rs.getString(19);
                delType[i]    = rs.getString(20);
                type[i]       = rs.getInt(21);
			}
              //System.out.println(sql+"  tCount "+tCount);

		} 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 executeSortschool2(Long docID,String findOption,String findWord) {

		StringBuilder sql = new StringBuilder(250);

        String strDocid = Long.toString(docID);

            String tmpAppend;
               if(findOption.equals("all") || findOption.equals("")){
                   tmpAppend = "";
               }else if(findOption.equals("notcomp")){
                   tmpAppend =  " AND RT.PRSS <> '"+findWord+"'";
               }else if(findOption.equals("howSort")){
                   tmpAppend =  " AND (RT.PRSS = '6' AND RT.SUBMIT_DATE IS NOT NULL) ";
               }else if(findOption.equals("mDept.name")){
                   tmpAppend =  "AND (GR.NAME LIKE '%"+findWord+"%' OR UP.NAME LIKE '%"+findWord+"%' OR DT.NAME LIKE '%"+findWord+"%')";
               }else {
                   tmpAppend =  " AND RT.PRSS = '"+findWord+"'";
               }

        try {

           sql.append("	SELECT GR.NAME	")
              .append(",UP.NAME ")
              .append(",DT.NAME ")
              .append(",SP_CM_DEC_FU(RT.OWNER_NAME) ")
              .append(",RT.USER_ID ")
              .append(",DT.PHONE ")
              .append(",RT.SUBMIT_DATE	")
              .append(",DT.FOUNDATION	")
              .append(",DT.GRADE ")
              .append(",RT.PRSS ")
              .append(",RT.DOWN_DOC_ID ")
              .append(",RT.ID ")
              .append(",RT.DEPT_ID ")
              .append(",RT.DOC_ID ")
              .append(",DT.ORGAN ")
              .append(",DT.UPPER_DEPT	")
              .append(",CAST(CAST(RT.NOTE AS CLOB(4096)) as varchar(5000)) ")
              .append(",RT.RECOG ")
              .append(",CAST(CAST(RT.ATTACHMENTS AS BLOB(4096)) as varchar(5000))")
              .append(",RT.DEL_TYPE  ")
              .append(",RT.TYPE ")
              .append("FROM  ")
              .append(MoumiEntity.getTableName(TotReport.class)).append(" rt, ")
              .append(MoumiEntity.getTableName(MDept.class)).append(" dt, ")
              .append(MoumiEntity.getTableName(MDept.class)).append(" gr, ")
              .append(MoumiEntity.getTableName(MDept.class)).append(" up ")
              .append("WHERE  ")
              .append(" RT.DOC_ID = '"+strDocid+"'")
              .append(" AND DT.ID=RT.DEPT_ID 	")
              .append(" AND DT.ORGAN=GR.ID  ")
              .append(" AND DT.UPPER_DEPT=UP.ID ")
              .append(" "+tmpAppend+" ")
              .append("ORDER BY DT.GRADE ASC,DT.NAME ASC ");


          //System.out.println(sql);
			rs = execQuery(sql);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}


			nameList   = new String[count];
            nameUp     = new String[count];
			nameDept   = new String[count];
			ownerName  = new String[count];
            id         = new String[count];
            phone      = new String[count];
            submitDate = new Date[count];
            founDation = new String[count];
            grade      = new int[count];
            prss       = new int[count];
            downDocId  = new int[count];
            reportId   = new int[count];
            deptId     = new String[count];
            docId      = new int[count];
            organ      = new String[count];
            upperDept  = new String[count];
            note       = new String[count];
            recog      = new int[count];
            attachment = new String[count];
            delType    = new String[count];
            type       = new int[count];
            tCount     = count;

			for (int i = 0; rs.next(); i++) {
				nameList[i]   = rs.getString(1);
                nameUp[i]     = rs.getString(2);
				nameDept[i]   = rs.getString(3);
				ownerName[i]  = rs.getString(4);
                id[i]         = rs.getString(5);
                phone[i]      = rs.getString(6);
                submitDate[i] = rs.getDate(7);
                founDation[i] = rs.getString(8);
                grade[i]      = rs.getInt(9);
                prss[i]       = rs.getInt(10);
                downDocId[i]  = rs.getInt(11);
                reportId[i]   = rs.getInt(12);
                deptId[i]     = rs.getString(13);
                docId[i]      = rs.getInt(14);
                organ[i]      = rs.getString(15);
                upperDept[i]  = rs.getString(16);
                note[i]       = rs.getString(17);
                recog[i]      = rs.getInt(18);
                attachment[i] = rs.getString(19);
                delType[i]    = rs.getString(20);
                type[i]       = rs.getInt(21);
			}
             //System.out.println(sql+"  tCount "+tCount+"::findoption::"+findOption+"::::findword::::"+findWord);

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


    /******** 카테고리 전체 리스트 ********/
	public void executeSortschool3(Long docID,String findOption,String findWord) {

		StringBuilder sql = new StringBuilder(250);

        String strDocid = Long.toString(docID);

          String tmpAppend;
               if(findOption.equals("all") || findOption.equals("")){
                   tmpAppend = "";
               }else if(findOption.equals("notcomp")){
                   tmpAppend =  " AND RT.PRSS <> '"+findWord+"'";
               }else if(findOption.equals("howSort")){
                   tmpAppend =  " AND (RT.PRSS = '6' AND RT.SUBMIT_DATE IS NOT NULL) ";
               }else if(findOption.equals("mDept.name")){
                   tmpAppend =  "AND (GR.NAME LIKE '%"+findWord+"%' OR UP.NAME LIKE '%"+findWord+"%' OR DT.NAME LIKE '%"+findWord+"%')";
               }else {
                   tmpAppend =  " AND RT.PRSS = '"+findWord+"'";
               }

        try {
            sql.append("  SELECT GR.NAME ")
               .append(",NULL	")
               .append(",DT.NAME	")
               .append(",SP_CM_DEC_FU(RT.OWNER_NAME) ")
               .append(",RT.USER_ID   ")
               .append(",DT.PHONE	")
               .append(",RT.SUBMIT_DATE   ")
               .append(",DT.FOUNDATION	 ")
               .append(",DT.GRADE	 ")
               .append(",RT.PRSS     ")
               .append(",RT.DOWN_DOC_ID	 ")
               .append(",RT.ID	 ")
               .append(",RT.DEPT_ID    ")
               .append(" ,RT.DOC_ID   ")
               .append(",DT.ORGAN     ")
               .append(",DT.UPPER_DEPT   ")
               .append(",CAST(CAST(RT.NOTE AS CLOB(4096)) as varchar(5000)) ")
               .append(",RT.RECOG ")
               .append(",CAST(CAST(RT.ATTACHMENTS AS BLOB(4096)) as varchar(5000))")
               .append(",RT.DEL_TYPE  ")
               .append(",RT.TYPE ")
               .append("  FROM     ")
               .append(MoumiEntity.getTableName(TotReport.class)).append(" rt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" dt, ")
               .append(MoumiEntity.getTableName(MDept.class)).append(" gr")
               .append(" WHERE     ")
               .append("     RT.DOC_ID = '"+strDocid+"'	")
               .append(" AND DT.ID=RT.DEPT_ID   ")
               .append(" AND DT.ORGAN=GR.ID ")
               .append(" "+tmpAppend+" ")
               .append("ORDER BY DT.GRADE ASC,DT.NAME ASC ");

          //System.out.println(sql);
			rs = execQuery(sql);
			if (rs != null) {
				rs.last();
				count = rs.getRow();
				rs.beforeFirst();
			} else {
				return;
			}

			nameList   = new String[count];
            nameUp     = new String[count];
			nameDept   = new String[count];
			ownerName  = new String[count];
            id         = new String[count];
            phone      = new String[count];
            submitDate = new Date[count];
            founDation = new String[count];
            grade      = new int[count];
            prss       = new int[count];
            downDocId  = new int[count];
            reportId   = new int[count];
            deptId     = new String[count];
            docId      = new int[count];
            organ      = new String[count];
            upperDept  = new String[count];
            note       = new String[count];
            recog      = new int[count];
            attachment = new String[count];
            delType    = new String[count];
            type       = new int[count];
            tCount     = count;

			for (int i = 0; rs.next(); i++) {
				nameList[i]   = rs.getString(1);
                nameUp[i]     = rs.getString(2);
				nameDept[i]   = rs.getString(3);
				ownerName[i]  = rs.getString(4);
                id[i]         = rs.getString(5);
                phone[i]      = rs.getString(6);
                submitDate[i] = rs.getDate(7);
                founDation[i] = rs.getString(8);
                grade[i]      = rs.getInt(9);
                prss[i]       = rs.getInt(10);
                downDocId[i]  = rs.getInt(11);
                reportId[i]   = rs.getInt(12);
                deptId[i]     = rs.getString(13);
                docId[i]      = rs.getInt(14);
                organ[i]      = rs.getString(15);
                upperDept[i]  = rs.getString(16);
                note[i]       = rs.getString(17);
                recog[i]      = rs.getInt(18);
                attachment[i] = rs.getString(19);
                delType[i]    = rs.getString(20);
                type[i]       = rs.getInt(21);
			}
             //System.out.println(sql+"  tCount "+tCount+"::findoption::"+findOption+"::::findword::::"+findWord);

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

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

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

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

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

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

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

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

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

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


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

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

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

   public int  getCount(){
          return tCount;
      }

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

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

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

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

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

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


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

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

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

     public Map<String, List<Byte>> getAttachmente() {
		if (this.attachmente == null)
			return Collections.unmodifiableMap(new HashMap<String, List<Byte>>());
		else
			return Collections.unmodifiableMap(attachmente);
	}

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

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


}