package kr.co.kihyun.beans.tosys.etc1;

import java.sql.*;

import kr.co.kihyun.beans.entity.SysAuth;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import kr.co.kihyun.db.*;

public class TotsysEtc extends CommonDBManager
{
	private static final Logger LOG=LoggerFactory.getLogger(TotsysEtc.class);
	private String sql="";
	public int count = 0;
	public String[] popupId=null;
    public int tCount=0;

    public int  getCount(){
          return tCount;
      }

    public String[]  getPopupId(){
          return popupId;
     }

	public void getPopupNotice()
 	{
		ResultSet rs=null;

		try
		{
            sql = "SELECT ID, COUNT(*) OVER() AS CNT FROM MOUMI_BOARD WHERE GROUP_ID='ID_SYSTEM_NOTICE' AND POPUP_YN='Y'";
			rs = execQuery(sql);

			if(rs != null){
				int ii  = 0;
				while(rs.next()){
					if(ii == 0){
						count = rs.getInt("CNT");
						tCount = count;
						popupId = new String[count];
					}
                	popupId[ii] = rs.getString("ID");
                	ii ++;
				}
			}else{
				count  = 0;
                tCount = count;
			}
		}catch(SQLException sqlEx){
				LOG.error("\nSQLState-{}\nErr Code-{}\nmsg-{}\nsql-{}",
                new Object[] { sqlEx.getSQLState(),sqlEx.getErrorCode(),sqlEx.getMessage(),sql });
				throw new RuntimeException(sqlEx);
		}catch (Exception ex){
				throw new RuntimeException(ex);
		}finally{
			if(null != rs)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			execClose();
		}
	}

    public int getDocCount(String id,String dept,SysAuth level)
    {
        int retCnt=0;
		ResultSet rs = null;

        try
        {
        	String grade = "";

            sql = "SELECT GRADE FROM MOUMI_DEPT WHERE ID = ? ";
            rs=execQuery(sql, dept);
            if(rs.next()){
            	grade=rs.getString(1);
            }
            close(rs);
            pstmt.close();

            if(level==SysAuth.SYSADM)
            {
                sql =  "SELECT COUNT(*) ";
                sql += "FROM MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC ";
                sql += "WHERE DC.DEL_TYPE='N' AND DC.GROUP_ID=2 ";
                sql += "AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
                sql += "and DC.START_DATE < SYSDATE " ;
                sql += "and DC.ACPT_LIMIT = AC.ID ";
                sql += "AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
            }
            else if(level==SysAuth.SUB_SYSADM && (null == grade || "".equals(grade)))
            {
                sql="SELECT COUNT(*) ";
                sql+="FROM MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC ";
                sql+="WHERE DC.DEL_TYPE='N' AND DC.GROUP_ID=2 ";
                sql+="AND (DC.USER_ID='"+id+"' OR (DC.DEPT_ID='"+dept+"' AND DC.ACC_AUTH=1) OR DC.ACC_AUTH=2) ";
                sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
                sql+="and DC.START_DATE < SYSDATE " ;
                sql+="and DC.ACPT_LIMIT = AC.ID ";
                sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
            }
            else if(level==SysAuth.TOTPER && (null == grade || "".equals(grade)))
            {
                sql="SELECT COUNT(*) ";
                sql+="FROM MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC, MOUMI_DEPT DT, MOUMI_DEPT GR ";
                sql+="WHERE DC.DEL_TYPE='N' AND DC.GROUP_ID=2 ";
                sql+="AND DC.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID ";
                sql+="AND (DC.USER_ID='"+id+"' OR (DC.DEPT_ID='"+dept+"' AND DC.ACC_AUTH=1) ";
                sql+="OR (GR.ID = (SELECT DT.ORGAN FROM MOUMI_DEPT DT WHERE DT.ID = '"+dept+"') AND DC.ACC_AUTH=2)) ";
                sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
                sql+="and DC.START_DATE < SYSDATE " ;
                sql+="and DC.ACPT_LIMIT = AC.ID ";
                sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
            } else
            {
                sql="SELECT COUNT(*) ";
                sql+="FROM MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC, MOUMI_DEPT DT, MOUMI_DEPT GR ";
                sql+="WHERE DC.DEL_TYPE='N' AND DC.GROUP_ID=2 ";
                sql+="AND DC.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID ";
                sql+="AND (DC.USER_ID='"+id+"' OR (DC.DEPT_ID='"+dept+"' AND DC.ACC_AUTH=1)) ";
//                sql+="OR (GR.ID = (SELECT DT.ORGAN FROM MOUMI_DEPT DT WHERE DT.ID = '"+dept+"') AND DC.ACC_AUTH=2)) ";
                sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
                sql+="and DC.START_DATE < SYSDATE " ;
                sql+="and DC.ACPT_LIMIT = AC.ID ";
                sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
            }


            rs=execQuery(sql);
            if(rs.next()){
            	retCnt = rs.getInt(1);
            }
            close(rs);
            pstmt.close();
        }catch (SQLException sqlEx){
			LOG.error("\nSQLState-{}\nErr Code-{}\nmsg-{}\nsql-{}",
            new Object[] { sqlEx.getSQLState(),sqlEx.getErrorCode(),sqlEx.getMessage(),sql });
			throw new RuntimeException(sqlEx);
		}catch (Exception ex) {
			throw new RuntimeException(ex);
		}finally {
			if(null != rs)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			execClose();
			return retCnt;
		}
    }

    public int getReportCount(String id, String dept)
    {
        int retCnt=0;
        ResultSet rs = null;
        try
        {
            String grade = "";
            String organ = "";
            String upper = "";

        	sql = "SELECT GRADE,ORGAN,UPPER_DEPT FROM MOUMI_DEPT WHERE ID = ? ";
            rs = execQuery(sql, dept);
            if(rs.next()){
                grade = rs.getString(1);
                organ = rs.getString(2);
                upper = rs.getString(3);
            }
            close(rs);
            pstmt.close();

            if(grade==null || "".equals(grade))
            {
                sql="SELECT COUNT(*) FROM MOUMI_TOT_REPORT RT, MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC WHERE RT.DOC_ID=DC.ID AND DC.DEL_TYPE='N' ";
                sql+="AND RT.DEL_TYPE='N' AND RT.DOC_TYPE=0 AND RT.PRSS IN (0,1,2,3) ";
                sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
                sql+="AND DC.START_DATE < SYSDATE ";
                sql+="AND DC.ACPT_LIMIT = AC.ID ";
                sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
                //sql+="AND (RT.DEPT_ID='"+dept+"' OR RT.DEPT_ID='"+organ+"' OR RT.DEPT_ID='"+upper+"') ";
                sql+="AND (((RT.DEPT_ID='"+dept+"' OR RT.DEPT_ID='"+organ+"' OR RT.DEPT_ID='"+upper+"') and rt.user_id is null) or (rt.user_id = ('"+id+"'))) ";
            }
            else if(grade!=null || "".equals(grade))
            {
                sql="SELECT COUNT(*) FROM MOUMI_TOT_REPORT RT, MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC WHERE RT.DOC_ID=DC.ID AND DC.DEL_TYPE='N' ";
                sql+="AND RT.DEL_TYPE='N' AND RT.DOC_TYPE=0 AND RT.PRSS IN (0,1,2,3) ";
                sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE>SYSDATE) ";
                sql+="AND DC.START_DATE < SYSDATE ";
                sql+="AND DC.ACPT_LIMIT = AC.ID ";
                sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
                //sql+="AND RT.DEPT_ID='"+dept+"' ";
                sql+="AND ((rt.dept_id in ('"+dept+"') and rt.user_id is null) or (rt.user_id = ('"+id+"'))) ";
            }

            rs = execQuery(sql);
            if(rs.next()){
            	retCnt = rs.getInt(1);
            }
            close(rs);
            pstmt.close();
        }
        catch (SQLException sqlEx)
		{
			LOG.error("\nSQLState-{}\nErr Code-{}\nmsg-{}\nsql-{}",
            new Object[] { sqlEx.getSQLState(),sqlEx.getErrorCode(),sqlEx.getMessage(),sql });
			throw new RuntimeException(sqlEx);
		}
		catch (Exception ex) {
			throw new RuntimeException(ex);
		}
		finally {
			if(null != rs)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			execClose();
			return retCnt;
		}
    }

	public String getRootName(String id)
    {
        String sql="";
        String return_value = "";
        ResultSet rs = null;
        try
        {
            sql="select d1.id||'*'||d2.name||'-'||d1.name from moumi_dept d1,moumi_dept d2 ";
            sql+="where d1.id in ('"+id+"') and d2.id=d1.organ";
            rs = execQuery(sql);
            if(rs.next()){
            	return_value = rs.getString(1);
            }
        }catch (SQLException e){
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(null != rs)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			execClose();
			return return_value;
		}
    }

    public String[] getGroupElement(String[] id)
 	{
        int ii=0,jj=0;
        boolean flag=false;
        if(id==null || id.length==0) return null;
        String tmp="";
        String[] tmpArray=new String[id.length];
        for(ii=0;ii<id.length;ii++)
        {
            if(id[ii]==null || id[ii].length()!=10) continue;
            if(id[ii].substring(2).equals("00000000")||id[ii].substring(2).equals("00000001"))
            {
                tmpArray[jj++]=getRootName(id[ii]);
                flag = true;
            }
            if(ii==0) tmp="'"+id[ii]+"'";
            else tmp+=",'"+id[ii]+"'";
        }
        ii=jj;

        ResultSet rs = null;

		try
		{
            sql="select d1.id||'*'||d2.name||'-'||d3.name||'-'||d1.name from moumi_dept d1,moumi_dept d2,moumi_dept d3 ";
            sql+="where d1.id in ("+tmp+") and d2.id=d1.organ and d3.id=d1.upper_dept";
			rs=execQuery(sql);
			while(rs.next())
			{
				tmpArray[ii++]=rs.getString(1);
			}
		}
		catch (SQLException sqlEx)
		{
			LOG.error("\nSQLState-{}\nErr Code-{}\nmsg-{}\nsql-{}",
            new Object[] { sqlEx.getSQLState(),sqlEx.getErrorCode(),sqlEx.getMessage(),sql });
			throw new RuntimeException(sqlEx);
		}
		catch (Exception ex) { throw new RuntimeException(ex);	}
		finally
		{
			if(null != rs)
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			execClose();
            return tmpArray;
		}
	}

    public static String[] getDeliToArr(String str, String deli)
    {
        String[] arr=null;
        if(str==null || deli==null || deli.length()==0) return null;
        String temp=str;
        int start=0; int cnt=0;
        while(temp.indexOf(deli)>=0)
        {
            String test=temp.substring(start,temp.indexOf(deli));
            temp=temp.substring(temp.indexOf(deli)+1,temp.length());
            cnt++;
        }

        arr=new String[cnt]; int ii = 0; temp=str; start=0;
        while(temp.indexOf(deli)>=0)
        {
            arr[ii]=temp.substring(start,temp.indexOf(deli));
            temp=temp.substring(temp.indexOf(deli)+deli.length(),temp.length());
            ii++;
        }
       return arr;
    }
}