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

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import kr.co.kihyun.beans.user.HttpSSOLogin;
import kr.co.kihyun.lang.Encoder;
import kr.co.kihyun.lang.MLong;
import kr.co.kihyun.lang.MString;
import kr.co.kihyun.text.html.ServletUtil;

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

@WebServlet("/servlet/kr.co.kihyun.beans.totsys.doc.HttpDeptSetMerge")
public class HttpDeptSetMerge extends HttpServlet {

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

    @Override
    public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
        doPost(req, res);
    }

    @Override
    public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
        res.setContentType("text/html;charset=UTF-8");
        PrintWriter out = res.getWriter();

        CommonDBManager db = new CommonDBManager();
        ResultSet rs = null;

        Long docID = MLong.parseLong(req.getParameter("docID"));
        Long reportID = MLong.parseLong(req.getParameter("reportID"));
        String usedType = MString.checkNull(req.getParameter("usedType"));
        String deptIDs = Encoder.toJava(req.getParameter("deptIDs"));
        String deptIDsplit = deptIDs.replace(",", "','");

        String types = Encoder.toJava(req.getParameter("types"));
        String return_msg = "";
        try {
        StringBuilder sql = new StringBuilder();

        sql.append(" UPDATE MOUMI_TOT_REPORT SET DEL_TYPE = 'Y' WHERE DOC_ID = '"+docID+"'  \n");
        System.out.println(sql.toString());
        db.execUpdate(sql.toString());
//        db.commit();
        sql.delete(0,sql.length());


        sql.append("	   MERGE INTO MOUMI_TOT_REPORT AA						\n")
        .append("	USING														\n")
        .append("	(															\n")
        .append("	    SELECT NVL(A.ID,B.ID) AS ID,							\n")
        .append("		   A.DEPT_ID, 											\n")
        .append("		   A.DEL_TYPE											\n")
        .append("	    FROM													\n")
        .append("	    (														\n")
        .append("	    SELECT A.ID, B.ID AS DEPT_ID, A.DEL_TYPE				\n")
        .append("      FROM MOUMI_DEPT B LEFT OUTER JOIN MOUMI_TOT_REPORT A		\n")
        .append("				     ON A.DEPT_ID = B.ID						\n")
        .append("				    AND A.DOC_ID = '"+ docID +"'				\n")
        .append("     WHERE 1=1													\n")
        .append("       AND B.ID IN ('"+ deptIDsplit +"')						\n")
        .append("    ) A FULL OUTER JOIN										\n")
        .append("    (															\n")
        .append("    SELECT A.ID, A.DEPT_ID, A.DEL_TYPE							\n")
        .append(" FROM MOUMI_TOT_REPORT A										\n")
        .append("    WHERE 1=1													\n")
        .append("      AND A.DOC_ID = '"+ docID +"'								\n")
        .append("   ) B															\n")
        .append("    ON A.ID = B.ID												\n")
        .append(" ) BB															\n")
        .append(" ON (AA.ID = BB.ID)											\n")
        .append(" WHEN NOT MATCHED THEN											\n")
        .append("  INSERT (														\n")
        .append("	  AA.ID,													\n")
        .append("	  AA.ACPT_CODE,												\n")
        .append("	  AA.APPRO,													\n")
        .append("	  AA.DOC_TYPE,												\n")
        .append("	  AA.ATTACHMENTS,											\n")
        .append("	  AA.CURRENT_REPOADM_ID,									\n")
        .append("	  AA.DATA,													\n")
        .append("	  AA.DEL_TYPE,												\n")
        .append("	  AA.DEPT_ID,												\n")
        .append(" AA.USER_ID,													\n")
        .append(" AA.NOTE,														\n")
        .append("  AA.NUM,														\n")
        .append("  AA.PRSS,														\n")
        .append("	  AA.RECOG,													\n")
        .append("	  AA.REG_DATE,												\n")
        .append("	  AA.REP_DOC_ID,											\n")
        .append("	  AA.DOWN_DOC_ID,											\n")
        .append("	  AA.SUBMIT_DATE,											\n")
        .append("	  AA.TABLE_NUM,												\n")
        .append("	  AA.TOT_DATA,												\n")
        .append("	  AA.DOC_ID,												\n")
        .append("	  AA.TYPE,													\n")
        .append("	  AA.MNG_CODE,												\n")
        .append("	  AA.OWNER_EMAIL,											\n")
        .append("	  AA.OWNER_NAME,											\n")
        .append("	  AA.OWNER_PHONE,											\n")
        .append("	  AA.ACPT_DOC_CODE,											\n")
        .append("	  AA.SECU_YN,												\n")
        .append("	  AA.ATTACHMENTS_PATH,										\n")
        .append("	  AA.FILE_CHECK												\n")
        .append("  )															\n")
        .append("  VALUES(														\n")
        .append("	  SQ_MOUMI.NEXTVAL ,								\n")
        .append("	  '',														\n")
        .append("	  0,														\n")
        .append("	  0,														\n")
        .append("	  NULL,														\n")
        .append("	  '',														\n")
        .append("	  null,														\n")
        .append("	  'N',														\n")
        .append("	  BB.DEPT_ID,												\n")
        .append("	  '',								\n")
        .append("	  null,								\n")
        .append("	  '',								\n")
        .append("	  0,								\n")
        .append("	  0,								\n")
        .append("	  SYSDATE ,							\n")
        .append("	  '',								\n")
        .append("	  '',								\n")
        .append("	  '',								\n")
        .append("	  1,								\n")
        .append("	  null,								\n")
        .append("	  '"+ docID +"',					\n")
        .append("	  0,								\n")
        .append("	  '',								\n")
        .append("	  '',								\n")
        .append("	  '',								\n")
        .append("	  '',								\n")
        .append("	  '',								\n")
        .append("	  'N',								\n")
        .append("	  '',								\n")
        .append("	  'N'								\n")
        .append("       )								\n")
        .append("	WHEN MATCHED  THEN  		\n")
        .append("		UPDATE SET AA.DEL_TYPE = 'N' WHERE BB.DEPT_ID IS NOT NULL	\n");

        System.out.println(sql.toString());
        db.execUpdate(sql.toString());

        db.commit();

        return_msg = "제출기관 변경이 완료되었습니다.";
		} catch (SQLException e) {
			db.rollback();
			return_msg = "제출기관 변경중 오류가 발생하였습니다.";
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
			return_msg = "제출기관 변경중 오류가 발생하였습니다.";
		} finally {
			db.execClose();
			out.println(ServletUtil.alert(return_msg));
			out.println(ServletUtil.getJavaScript("window.close();"));
		}

    }

}