/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package kr.co.kihyun.service;

import java.util.Map;
import javax.jdo.PersistenceManager;
import kr.co.kihyun.beans.entity.AccessAuth;
import kr.co.kihyun.beans.entity.SysAuth;
import kr.co.kihyun.lang.MString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
 *
 * @author KWON,HAN
 */
public class FormService extends BaseService {

    /**
     * 기본생성자
     */
    public FormService() {
    }

    /**
     * 생성자
     *
     * @param pm PersistenceManager
     */
    public FormService(PersistenceManager pm) {
        super(pm);
    }

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

   /**
     * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(제출완료)
     *
     * @param params - 검색조건맵
     *         - userId
     *         - deptId
     *         - sysauth
     *         - findOption
     *         - filterValue
     * @param docType - ALL:전체, PRSS:집계진행자료, REG:미진행자료, END:종료자료,
     * @param exeType - A:전체, D:집계자료, R:제출자료
     * @참조페이지 - doc_list.jsp
     */
    public void createFormListSearchQuery(Map params) {
        StringBuilder sbSearch = new StringBuilder();

      //String userId = (String) params.get("userId");
      //String deptId = (String) params.get("deptId");
        SysAuth sysauth 	= SysAuth.values()[(Integer) params.get("sysAuth")];
        String findOption 	= (String) params.get("findOption");
        String filterValue 	= (String) params.get("filterValue");
        String stDate 		= (String) params.get("stDate");
        String edDate 		= (String) params.get("edDate");

                sbSearch.append(" SELECT A0.ID                                              \n");
                sbSearch.append("      , A0.MNG_CODE                     AS mngCode         \n");
                sbSearch.append("      , A0.OWNER_EMAIL                  AS ownerEmail      \n");
                sbSearch.append("      , SP_CM_DEC_FU(A0.OWNER_NAME)     AS ownerName       \n");
                sbSearch.append("      , A0.OWNER_PHONE                  AS ownerPhone      \n");
                sbSearch.append("      , A0.ACC_AUTH                     AS accAuth         \n");
                sbSearch.append("      , A0.APPEND_TYPE                  AS appendType      \n");
                sbSearch.append("      , A0.AUTO_RTN_YN                  AS autoRtnYn       \n");
                sbSearch.append("      , A0.END_DATE                     AS endDate         \n");
                sbSearch.append("      , A0.EXEC_CODE                    AS execCode        \n");
//                sbSearch.append("      , A0.GROUP                                           \n");
                sbSearch.append("      , A0.FIX_NUM                      AS fixNum          \n");
                sbSearch.append("      , A0.HANDY_NAME                   AS handyName       \n");
                sbSearch.append("      , A0.DEL_TYPE                     AS delType         \n");
                sbSearch.append("      , A0.USER_ID                      AS userId          \n");
             // sbSearch.append("    --, ( SELECT sp_cm_dec_fu(user_nm_encpt) FROM TN_SYMUSMUM001 WHERE usrid = A0.USER_ID ) AS userName  \n");

                sbSearch.append("      , A0.DEPT_ID                      AS deptId          \n");
                sbSearch.append("      , ( SELECT B0.NAME FROM MOUMI_DEPT B0 WHERE B0.ID = A0.DEPT_ID AND ROWNUM = 1)  AS deptName        \n");
                sbSearch.append("      , A0.NAME                                            \n");
                sbSearch.append("      , A0.PERIODICAL_DOC_YN            AS periodicalDocYn \n");
                sbSearch.append("      , A0.REG_DATE                     AS regDate         \n");
                sbSearch.append("      , A0.START_DATE                   AS startDate       \n");
                sbSearch.append("      , A0.TOT_RANGE                    AS totRange        \n");
                sbSearch.append("      , A0.GROUP_ID                     AS groupId         \n");
//                sbSearch.append("      , ROWNUMBER() OVER()  AS rowNo                       \n");
                sbSearch.append("   FROM MOUMI_TOT_DOC A0, MOUMI_DEPT DT, MOUMI_DEPT GR     \n");
                sbSearch.append("  WHERE A0.DEL_TYPE = 'N'                                  \n");
                sbSearch.append("    AND A0.GROUP_ID = 1                                    \n");
                sbSearch.append("    AND A0.DEPT_ID = DT.ID                                 \n");
                sbSearch.append("    AND DT.ORGAN = GR.ID                                   \n");
                sbSearch.append("    AND A0.APPEND_TYPE != 1                                \n");    //0.서식+파일, 1.파일, 2.서식함활용, 3.서식복사(시스템서식)

            if(sysauth != SysAuth.SYSADM) {
                sbSearch.append("    AND ((A0.USER_ID = :userId)         \n");
                sbSearch.append("          OR                             \n");
                sbSearch.append("          (A0.DEPT_ID = :deptId AND A0.ACC_AUTH = " + AccessAuth.DEPT.ordinal() + ")     \n");

                sbSearch.append("          OR                             \n");
                sbSearch.append("          (A0.ACC_AUTH = " + AccessAuth.PART.ordinal() + "     \n");
                sbSearch.append("          AND GR.ID = ( SELECT GR.ID FROM MOUMI_MUSER US, MOUMI_DEPT DT, MOUMI_DEPT GR     \n");
                sbSearch.append("          WHERE US.ID = :userId AND US.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID )    \n");
                sbSearch.append("         )                               \n");
                sbSearch.append("         OR (A0.ACC_AUTH = " + AccessAuth.ALL.ordinal() + ")                            \n");
                sbSearch.append("        )                                \n");
            }

            if (!MString.isNull(findOption)) {
            	if (findOption.equals("name")  && !MString.isNull(filterValue)) {
                sbSearch.append("    AND A0.NAME LIKE '%' || :filterValue || '%'  ESCAPE '\\'  \n");
            	} else if (findOption.equals("totDoc.mUser.name")  && !MString.isNull(filterValue)) {
                sbSearch.append("    AND A0.OWNER_NAME = SP_CM_ENC_FU(:filterValue)  \n");
            	} else if (findOption.equals("regDate") && !MString.isNull(stDate) && !MString.isNull(edDate) ) {
                sbSearch.append("    AND A0.REG_DATE BETWEEN TO_DATE(:stDate,'YYYY-MM-DD') AND TO_DATE(:edDate,'YYYY-MM-DD') + 1        \n");
            	}
            }

        //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장
        //페이징처리함수 호출전에 등록 필요.
        this.setCountQuery(sbSearch);
        this.setParams(params);

                sbSearch.append("   ORDER BY A0.ID DESC                   \n");

        //페이징처리쿼리생성함수 호출
        this.setSearchQuery(sbSearch);

//        if( LOG.isDebugEnabled() ) {
//            LOG.debug("DocReportService.createFormListSearchQuery ======================\nquery=\n{}\n===============================", sbSearch.toString());
//            LOG.debug("params={}", params);
//            LOG.debug("params={}", this.getParams());
//        }

    }

}