/**
 * @(#)file    PSRListMailBiz
 * @(#)author  lee mi kyoung
 * @(#)version 1.0
 * @(#)date    2018-02-27
 * @(#)since   JDK 1.4.2
 */
 
package kr.co.udapsoft.ebid.buyer.process.process.biz;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
import kr.co.hsnc.common.sql.Row;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.common.code.CommCodeManager;
import kr.co.udapsoft.common.sender.MessageSend;
import kr.co.udapsoft.common.util.PDFMaker;
import kr.co.udapsoft.common.util.Crosscert;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.ui.InitCombo;
import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class PSRListMailBiz extends Biz {

	public PSRListMailBiz(HandlerStorage storage) {
		super(storage);
	}
	 

	public void doBiz() throws Exception {
        	
		if(storage.compareEvent("SEND_MAIL")){ 
			Send(params, storage.getEvent());		//서명요청 메일과 SMS 전송
			super.storage.setSuccess(true);
		}else if(storage.compareEvent("GET_PSR_REPORT")){ 
			params.set("sheet2", getPSRReport(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}	
 
	public void Send(ValueObject params, String event) throws Exception {
		MessageSend messageSend = new MessageSend(storage); 
		RowSet SendInfo = getSendinfo(params.get("dept_cd"), "-10000", "14");
		//지연
		RowSet SendData = getSendData(params.get("dept_cd"), "-10000", "0"); 
		 
		boolean email = false;
 		StringBuffer mailtitle = new StringBuffer();
 		StringBuffer mailtitlein = new StringBuffer();
        StringBuffer mailContent = new StringBuffer();
        String ls_dept_name = "";
        
        if  (SendInfo.size()> 0){ 
        	ls_dept_name = SendInfo.getRow(0).get("dept_ini");
	    }else{
	    	ls_dept_name = params.get("dept_nm");
	    }
        
        mailtitle.append("* " + ls_dept_name + " 구매관련 일정 지연 알림");
        mailtitlein.append("* " + ls_dept_name + " 구매관련 일정 지연 알림");
     	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
     	mailContent.append("<br>");
     	mailContent.append("<table style='font-size:12px' width='98%' border='1' cellpadding='10' cellspacing='1'>");     	
     	mailContent.append("<tr bgcolor='#E8EAEB' style='text-align:center;'><td >MR No</td><td>Title</td><td>Stage</td><td>준수예정일자</td></tr>");     	
		
     	for (int i = 0; i< SendData.size(); i++ ){
			email = true;
			Row row=SendData.getRow(i); 
			mailContent.append("<tr><td>" + row.get("MR_NO") + "</td>");
			mailContent.append("<td>" + row.get("TITLE") + "&nbsp;</td>");
			mailContent.append("<td>" + row.get("MAIL_DESC") + "</td>"); 
			mailContent.append("<td style='text-align:center;'>" + row.get("LATE_DATE") + "</td></tr>");  
		}
		mailContent.append("</table></font>"); 
		if(email){ 
			for (int i = 0; i<SendInfo.size(); i++){ 
			   messageSend.sendEmail(SendInfo.getRow(i).get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent,"Y");
		    }
        	messageSend.sendEmail("mklee97@sgc.co.kr", "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent,"Y"); 
        }
		//1주일
		email = false;
		mailtitle.delete(0, mailtitle.length());
		mailtitlein.delete(0, mailtitlein.length());
		mailContent.delete(0, mailContent.length()); 
		SendData = getSendData(params.get("dept_cd"), "1", "7"); 
		mailtitle.append("* " + ls_dept_name + " 구매관련 일정 1주전 알림");
        mailtitlein.append("* " + ls_dept_name + " 구매관련 일정 1주전 알림");
     	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
     	mailContent.append("<br>");
     	mailContent.append("<table style='font-size:12px' width='98%' border='1' cellpadding='10' cellspacing='1'>");     	
        mailContent.append("<tr bgcolor='#E8EAEB' style='text-align:center;'><td >MR No</td><td>Title</td><td>Stage</td><td>준수예정일자</td></tr>");     	
		
     	for (int i = 0; i< SendData.size(); i++ ){
			email = true;
			Row row=SendData.getRow(i); 
			mailContent.append("<tr><td>" + row.get("MR_NO") + "</td>");
			mailContent.append("<td>" + row.get("TITLE") + "&nbsp;</td>");
			mailContent.append("<td>" + row.get("MAIL_DESC") + "</td>"); 
			mailContent.append("<td style='text-align:center;'>" + row.get("LATE_DATE") + "</td></tr>");  
		}
		mailContent.append("</table></font>");
		if(email){ 
			for (int i = 0; i<SendInfo.size(); i++){ 
			    messageSend.sendEmail(SendInfo.getRow(i).get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent,"Y");
			}
        	messageSend.sendEmail("mklee97@sgc.co.kr", "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent,"Y"); 
        }
		//2주일
		email = false;
		mailtitle.delete(0, mailtitle.length());
		mailtitlein.delete(0, mailtitlein.length());
		mailContent.delete(0, mailContent.length()); 
		SendData = getSendData(params.get("dept_cd"), "8", "14"); 
		mailtitle.append("* " + ls_dept_name + " 구매관련 일정 2주전 알림");
        mailtitlein.append("* " + ls_dept_name + " 구매관련 일정 2주전 알림");
     	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
     	mailContent.append("<br>");
     	mailContent.append("<table style='font-size:12px' width='98%' border='1' cellpadding='10' cellspacing='1'>");     	
        mailContent.append("<tr bgcolor='#E8EAEB' style='text-align:center;'><td >MR No</td><td>Title</td><td>Stage</td><td>준수예정일자</td></tr>");     	
		
     	for (int i = 0; i< SendData.size(); i++ ){
			email = true;
			Row row=SendData.getRow(i); 
			mailContent.append("<tr><td>" + row.get("MR_NO") + "</td>");
			mailContent.append("<td>" + row.get("TITLE") + "&nbsp;</td>");
			mailContent.append("<td>" + row.get("MAIL_DESC") + "</td>"); 
			mailContent.append("<td style='text-align:center;'>" + row.get("LATE_DATE") + "</td></tr>");  
		}
		mailContent.append("</table></font>");
		
		if(email){ 
			for (int i = 0; i<SendInfo.size(); i++){ 
			      messageSend.sendEmail(SendInfo.getRow(i).get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent,"Y");
		    }
        	messageSend.sendEmail("mklee97@sgc.co.kr", "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent,"Y"); 
        }
		
	}
	
	private RowSet getSendinfo(String dept_cd, String fromdt, String todt) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("SELECT DISTINCT B.E_MAIL  , C.DEPT_INI                                               \n");  
	        sqlstr.append("  FROM (                                                                                                                                                                                \n");  
	        sqlstr.append("	      SELECT A.MAIL_DESC                                                                                                                                                                         \n");  
	        sqlstr.append("	            ,RANK() OVER (PARTITION BY  A.MR_NO  ORDER BY  A.MAIL_DESC  ASC)  RNK                                                                                                      \n");  
	        sqlstr.append("	            ,CASE WHEN SUBSTR(A.MAIL_DESC,0,2) IN ('01','02','12','13','14','15') THEN A.PUR_ID --MR                                                                                   \n");  
	        sqlstr.append("	                  WHEN  SUBSTR(A.MAIL_DESC,0,2) IN ('03','04','07','08') THEN A.BID_ID --BID                                                                                           \n");  
	        sqlstr.append("	                  WHEN  SUBSTR(A.MAIL_DESC,0,2) IN ('05','06') THEN A.TBE_ID --TBE                                                                                                     \n");  
	        sqlstr.append("	                  WHEN  SUBSTR(A.MAIL_DESC,0,2) IN ('09','10','11') THEN A.DOC_ID --DOC                                                                                                \n");  
	        sqlstr.append("	                  WHEN  SUBSTR(A.MAIL_DESC,0,2) IN ('16','17','18') THEN A.LOG_ID --LOG                                                                                                \n");  
	        sqlstr.append("	             END  MAIL_ID                                                                                                                                                              \n");  
	        sqlstr.append("	        FROM (                                                                                                                                                                         \n");  
	        sqlstr.append("	             SELECT A.DEPT_CD, A.MR_NO, A.TITLE                                                                                                                                        \n"); 
	        sqlstr.append("	                   ,CASE WHEN to_char(A.MR_ISSUE,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.MR_ISSUE IS NULL AND A.PUR_CONT_ID IS NULL         THEN '01'     \n"); 
	        sqlstr.append("	                         WHEN to_char(A.MR_APPROVAL,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.MR_APPROVAL IS NULL AND A.PUR_CONT_ID IS NULL   THEN '02'     \n"); 
	        sqlstr.append("	                         WHEN to_char(A.RFQ_ISSUE,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.RFQ_ISSUE IS NULL AND A.PUR_CONT_ID IS NULL       THEN '03'     \n"); 
	        sqlstr.append("	                         WHEN to_char(A.QUO,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.QUO IS NULL AND A.PUR_CONT_ID IS NULL                   THEN '04'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.TBE_ISSUE,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.TBE_ISSUE IS NULL AND A.PUR_CONT_ID IS NULL       THEN '05'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.TBE_APPROVAL,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.TBE_APPROVAL IS NULL AND A.PUR_CONT_ID IS NULL THEN '06'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.CBE ,'yyyymmdd')BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.CBE IS NULL AND A.PUR_CONT_ID IS NULL                   THEN '07'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.PO_ISSUE ,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.PO_ISSUE IS NULL AND A.PUR_CONT_ID IS NULL        THEN '08'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.VP_IN,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.VP_IN IS NULL AND B.PUR_CONT_ID IS NOT NULL           THEN '09'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.VP_OUT,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.VP_OUT IS NULL  AND B.PUR_CONT_ID IS NOT NULL                                      THEN '10'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.FINAL_VENDOR,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.FINAL_VENDOR IS NULL  AND B.PUR_CONT_ID IS NOT NULL                          THEN '11'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.MAJOR_RAW,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.MAJOR_RAW IS NULL  AND B.PUR_CONT_ID IS NOT NULL                                THEN '12'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.RECEIPT_RAW,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.RECEIPT_RAW IS NULL  AND B.PUR_CONT_ID IS NOT NULL                            THEN '13'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.PIM ,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.PIM IS NULL  AND B.PUR_CONT_ID IS NOT NULL                                            THEN '14'    \n");  
	        sqlstr.append("	                         WHEN to_char(A.FAT ,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.FAT IS NULL   AND B.PUR_CONT_ID IS NOT NULL                                           THEN '15'    \n");  
	        sqlstr.append("	                         WHEN to_char(A.SHIPPING,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.SHIPPING IS NULL  AND B.PUR_CONT_ID IS NOT NULL                                  THEN '16'     \n");  
	        sqlstr.append("	                         WHEN to_char(A.LANDING,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.LANDING IS NULL    AND B.PUR_CONT_ID IS NOT NULL                                  THEN '17'     \n"); 
	        sqlstr.append("	                         WHEN to_char(A.ONSITE,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.ONSITE IS NULL       AND B.PUR_CONT_ID IS NOT NULL                                 THEN '18'     \n"); 
	        sqlstr.append("	                         ELSE '0'                                                                                                                                                      \n"); 
	        sqlstr.append("	                     END MAIL_DESC                                                                                                                                                     \n"); 
	        sqlstr.append("	                   , A.PUR_CONT_ID                                                                                                                                                     \n"); 
	        sqlstr.append("	                   ,C.PUR_ID, C.DOC_ID, C.LOG_ID, D.BID_ID, D.TBE_ID                                                                                                                   \n"); 
	        sqlstr.append("	               FROM PSR_MASTER2018 A --F                                                                                                                                                 \n");  
	        sqlstr.append("	                   ,PSR_MASTER2018 B --A                                                                                                                                                  \n");  
	        sqlstr.append("	                   ,PSR_DEFINE2018 C --DEFINE                                                                                                                                                  \n");  
	        sqlstr.append("	                   ,(SELECT A.DEPT_CD, A.DEMANDED_NO MR_NO, MAX(A.PUREMPNO) BID_ID, MAX(TO_NUMBER(B.EVAL_EMPID)) TBE_ID                                                                \n");  
	        sqlstr.append("	                       FROM BID_MASTER A                                                                                                                                               \n");  
	        sqlstr.append("	                           ,TBE_INFO B                                                                                                                                                 \n");  
	        sqlstr.append("	                      WHERE A.BID_NO = B.BID_NO(+)                                                                                                                                     \n");  
	        sqlstr.append("	                        AND A.BID_DEG = B.BID_DEG(+)                                                                                                                                   \n");  
	        sqlstr.append("	                        AND A.DEPT_CD = '"+dept_cd+"'                                                                                                                                  \n");  
	        sqlstr.append("	                      GROUP BY A.DEPT_CD, A.DEMANDED_NO ) D                                                                                                                            \n");  
	        sqlstr.append("	              WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                                                                                           \n");  
	        sqlstr.append("	                AND A.MR_NO = B.MR_NO(+)                                                                                                                                               \n");  
	        sqlstr.append("	                AND A.DEPT_CD = C.DEPT_CD                                                                                                                                              \n");  
	        sqlstr.append("	                AND A.MR_NO = D.MR_NO(+)                                                                                                                                               \n"); 
	        sqlstr.append("	                AND A.PSR_PFA = 'F'                                                                                                                                                    \n"); 
	        sqlstr.append("	                AND B.PSR_PFA(+) = 'A'                                                                                                                                                 \n"); 
	        sqlstr.append("	                AND A.DEPT_CD = '"+dept_cd+"'                                                                                                                                          \n"); 
	        sqlstr.append("	              ORDER BY MR_NO, MAIL_DESC  ) A                                                                                                                                           \n");  
	        sqlstr.append("	            WHERE MAIL_DESC <> '0'                                                                                                                                                  \n");  
	        sqlstr.append("	            UNION                                                                                                                                                \n");  
	        sqlstr.append("	            SELECT '01',1,PUR_ID                                                                                                                                                 \n");  
	        sqlstr.append("	              FROM PSR_DEFINE2018                                                                                                                                                 \n");  
	        sqlstr.append("	             WHERE DEPT_CD = '"+dept_cd+"'   ) A                                                                                                                                                 \n");  
	        sqlstr.append("	           ,PUB0120T B                                                                                                                                                                  \n");  
	        sqlstr.append("	           ,ACZ10100V C                                                                                                                                                               \n");  
	        sqlstr.append(" WHERE A.RNK = 1                                                                                                                                                                        \n");  
	        sqlstr.append("   AND A.MAIL_ID = B.ACCT_ID(+)                                                                                                                                                         \n");  
	        sqlstr.append("   AND C.DEPT_CD = '"+dept_cd+"'                                                                                                                                                        \n");  

			System.out.println(sqlstr.toString());
			search.setStatement(sqlstr.toString());
        	
            rowSet = search.execute();
            
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
         }
        finally {
        	if( rowSet == null ){
        		rowSet = new RowSetImpl();
            }
            
			return rowSet;
        }
	}
    
	private RowSet getSendData(String dept_cd, String fromdt, String todt) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();
        try{                                                                                                                                                                                                                                                   
	        sqlstr.append("SELECT DISTINCT A.MR_NO ,A.TITLE,SUBSTR(A.MAIL_DESC, 3,8) LATE_DATE,SUBSTR( A.MAIL_DESC, 11, LENGTH(A.MAIL_DESC)) MAIL_DESC,  B.E_MAIL                                                  \n");  
	        sqlstr.append("  FROM (                                                                                                                                                                                \n");  
	        sqlstr.append("	      SELECT A.*                                                                                                                                                                       \n");  
	        sqlstr.append("	            ,RANK() OVER (PARTITION BY  A.MR_NO  ORDER BY  A.MAIL_DESC  ASC)  RNK                                                                                                      \n");  
	        sqlstr.append("	            ,CASE WHEN SUBSTR(A.MAIL_DESC,0,2) IN ('01','02','12','13','14','15') THEN A.PUR_ID --MR                                                                                   \n");  
	        sqlstr.append("	                  WHEN  SUBSTR(A.MAIL_DESC,0,2) IN ('03','04','07','08') THEN A.BID_ID --BID                                                                                           \n");  
	        sqlstr.append("	                  WHEN  SUBSTR(A.MAIL_DESC,0,2) IN ('05','06') THEN A.TBE_ID --TBE                                                                                                     \n");  
	        sqlstr.append("	                  WHEN  SUBSTR(A.MAIL_DESC,0,2) IN ('09','10','11') THEN A.DOC_ID --DOC                                                                                                \n");  
	        sqlstr.append("	                  WHEN  SUBSTR(A.MAIL_DESC,0,2) IN ('16','17','18') THEN A.LOG_ID --LOG                                                                                                \n");  
	        sqlstr.append("	             END  MAIL_ID                                                                                                                                                              \n");  
	        sqlstr.append("	        FROM (                                                                                                                                                                         \n");  
	        sqlstr.append("	             SELECT A.DEPT_CD, A.MR_NO, A.TITLE                                                                                                                                        \n"); 
	        sqlstr.append("	                   ,CASE WHEN to_char(A.MR_ISSUE,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.MR_ISSUE IS NULL AND A.PUR_CONT_ID IS NULL         THEN '01' || to_char(a.mr_issue, 'yy-mm-dd') || 'MR ISSUE'          \n"); 
	        sqlstr.append("	                         WHEN to_char(A.MR_APPROVAL,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.MR_APPROVAL IS NULL AND A.PUR_CONT_ID IS NULL   THEN '02' || to_char(a.MR_APPROVAL, 'yy-mm-dd') || 'MR_APPROVAL'      \n"); 
	        sqlstr.append("	                         WHEN to_char(A.RFQ_ISSUE,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.RFQ_ISSUE IS NULL AND A.PUR_CONT_ID IS NULL       THEN '03' || to_char(a.RFQ_ISSUE, 'yy-mm-dd') || 'RFQ_ISSUE'          \n"); 
	        sqlstr.append("	                         WHEN to_char(A.QUO,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.QUO IS NULL AND A.PUR_CONT_ID IS NULL                   THEN '04' || to_char(a.QUO, 'yy-mm-dd') || 'QUO'                      \n");  
	        sqlstr.append("	                         WHEN to_char(A.TBE_ISSUE,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.TBE_ISSUE IS NULL AND A.PUR_CONT_ID IS NULL       THEN '05' || to_char(a.TBE_ISSUE, 'yy-mm-dd') || 'TBE_ISSUE'          \n");  
	        sqlstr.append("	                         WHEN to_char(A.TBE_APPROVAL,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.TBE_APPROVAL IS NULL AND A.PUR_CONT_ID IS NULL THEN '06' || to_char(a.TBE_APPROVAL, 'yy-mm-dd') || 'TBE_APPROVAL'    \n");  
	        sqlstr.append("	                         WHEN to_char(A.CBE ,'yyyymmdd')BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.CBE IS NULL AND A.PUR_CONT_ID IS NULL                   THEN '07' || to_char(a.CBE, 'yy-mm-dd') || 'CBE'                      \n");  
	        sqlstr.append("	                         WHEN to_char(A.PO_ISSUE ,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.PO_ISSUE IS NULL AND A.PUR_CONT_ID IS NULL        THEN '08' || to_char(a.PO_ISSUE, 'yy-mm-dd') || 'PO_ISSUE'            \n");  
	        sqlstr.append("	                         WHEN to_char(A.VP_IN,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.VP_IN IS NULL  AND B.PUR_CONT_ID IS NOT NULL          THEN '09' || to_char(a.VP_IN, 'yy-mm-dd') || 'VP_IN'                  \n");  
	        sqlstr.append("	                         WHEN to_char(A.VP_OUT,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.VP_OUT IS NULL   AND B.PUR_CONT_ID IS NOT NULL       THEN '10' || to_char(a.VP_OUT, 'yy-mm-dd') || 'VP_OUT'                \n");  
	        sqlstr.append("	                         WHEN to_char(A.FINAL_VENDOR,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.FINAL_VENDOR IS NULL AND B.PUR_CONT_ID IS NOT NULL THEN '11' || to_char(a.FINAL_VENDOR, 'yy-mm-dd') || 'FINAL_VENDOR'    \n");  
	        sqlstr.append("	                         WHEN to_char(A.MAJOR_RAW,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.MAJOR_RAW IS NULL AND B.PUR_CONT_ID IS NOT NULL   THEN '12' || to_char(a.MAJOR_RAW, 'yy-mm-dd') || 'MAJOR_RAW'          \n");  
	        sqlstr.append("	                         WHEN to_char(A.RECEIPT_RAW,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.RECEIPT_RAW IS NULL AND B.PUR_CONT_ID IS NOT NULL THEN '13' || to_char(a.RECEIPT_RAW, 'yy-mm-dd') || 'RECEIPT_RAW'      \n");  
	        sqlstr.append("	                         WHEN to_char(A.PIM ,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.PIM IS NULL  AND B.PUR_CONT_ID IS NOT NULL             THEN '14' || to_char(a.PIM, 'yy-mm-dd') || 'PIM'                      \n");  
	        sqlstr.append("	                         WHEN to_char(A.FAT ,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.FAT IS NULL  AND B.PUR_CONT_ID IS NOT NULL             THEN '15' || to_char(a.FAT, 'yy-mm-dd') || 'FAT'                      \n");  
	        sqlstr.append("	                         WHEN to_char(A.SHIPPING,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.SHIPPING IS NULL  AND B.PUR_CONT_ID IS NOT NULL    THEN '16' || to_char(a.SHIPPING, 'yy-mm-dd') || 'SHIPPING'            \n");  
	        sqlstr.append("	                         WHEN to_char(A.LANDING,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.LANDING IS NULL    AND B.PUR_CONT_ID IS NOT NULL    THEN '17' || to_char(a.LANDING, 'yy-mm-dd') || 'LANDING'              \n"); 
	        sqlstr.append("	                         WHEN to_char(A.ONSITE,'yyyymmdd') BETWEEN to_char(sysdate+"+fromdt+",'yyyymmdd') AND to_char(sysdate+"+todt+",'yyyymmdd') AND B.ONSITE IS NULL    AND B.PUR_CONT_ID IS NOT NULL      THEN '18' || to_char(a.ONSITE, 'yy-mm-dd') || 'ONSITE'                \n"); 
	        sqlstr.append("	                         ELSE '0'                                                                                                                                                      \n"); 
	        sqlstr.append("	                     END MAIL_DESC                                                                                                                                                     \n"); 
	        sqlstr.append("	                   , A.PUR_CONT_ID                                                                                                                                                     \n"); 
	        sqlstr.append("	                   ,C.PUR_ID, C.DOC_ID, C.LOG_ID, D.BID_ID, D.TBE_ID                                                                                                                   \n"); 
	        sqlstr.append("	               FROM PSR_MASTER2018 A                                                                                                                                                   \n");  
	        sqlstr.append("	                   ,PSR_MASTER2018 B                                                                                                                                                   \n");  
	        sqlstr.append("	                   ,PSR_DEFINE2018 C                                                                                                                                                   \n");  
	        sqlstr.append("	                   ,(SELECT A.DEPT_CD, A.DEMANDED_NO MR_NO, MAX(A.PUREMPNO) BID_ID, MAX(TO_NUMBER(B.EVAL_EMPID)) TBE_ID                                                                \n");  
	        sqlstr.append("	                       FROM BID_MASTER A                                                                                                                                               \n");  
	        sqlstr.append("	                           ,TBE_INFO B                                                                                                                                                 \n");  
	        sqlstr.append("	                      WHERE A.BID_NO = B.BID_NO(+)                                                                                                                                     \n");  
	        sqlstr.append("	                        AND A.BID_DEG = B.BID_DEG(+)                                                                                                                                   \n");  
	        sqlstr.append("	                        AND A.DEPT_CD = '"+dept_cd+"'                                                                                                                                  \n");  
	        sqlstr.append("	                      GROUP BY A.DEPT_CD, A.DEMANDED_NO ) D                                                                                                                            \n");  
	        sqlstr.append("	              WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                                                                                           \n");  
	        sqlstr.append("	                AND A.MR_NO = B.MR_NO(+)                                                                                                                                               \n");  
	        sqlstr.append("	                AND A.DEPT_CD = C.DEPT_CD                                                                                                                                              \n");  
	        sqlstr.append("	                AND A.MR_NO = D.MR_NO(+)                                                                                                                                               \n"); 
	        sqlstr.append("	                AND A.PSR_PFA = 'F'                                                                                                                                                    \n"); 
	        sqlstr.append("	                AND B.PSR_PFA(+) = 'A'                                                                                                                                                 \n"); 
	        sqlstr.append("	                AND A.DEPT_CD = '"+dept_cd+"'                                                                                                                                          \n"); 
	        sqlstr.append("	              ORDER BY MR_NO, MAIL_DESC  ) A                                                                                                                                           \n");  
	        sqlstr.append("	            WHERE MAIL_DESC <> '0' ) A                                                                                                                                                 \n");  
	        sqlstr.append("	           ,PUB0120T B                                                                                                                                                                 \n");  
	        sqlstr.append(" WHERE A.RNK = 1                                                                                                                                                                        \n");  
	        sqlstr.append("   AND A.MAIL_ID = B.ACCT_ID(+)                                                                                                                                                         \n");  
	                   
            search.setStatement(sqlstr.toString());
        	
            rowSet = search.execute(); 
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
         }
        finally {
        	if( rowSet == null ){
        		rowSet = new RowSetImpl();
            }
            
			return rowSet;
        }
	}	
	
	private RowSet getPSRReport(ValueObject params) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();

        try{

        	sqlstr.append("SELECT * FROM (                                                                                                                                                     \n");
        	sqlstr.append("SELECT 'A' || A.DIS_CD ORD,A.DIS_CD,A.DIS_NM ,COUNT(DIS_CD) CNT,  PSR_PFA                                                                                                  \n");
        	sqlstr.append("     ,SUM(P_MR_ISSUE) MR_ISSUE                                                                                                                                      \n");
        	sqlstr.append("     ,SUM(P_MR_APPROVAL) MR_APPROVAL                                                                                                                                \n");
        	sqlstr.append("     ,SUM(P_RFQ_ISSUE ) RFQ_ISSUE                                                                                                                                   \n");
        	sqlstr.append("     ,SUM(P_QUO ) QUO                                                                                                                                               \n");
        	sqlstr.append("     ,SUM(P_TBE_ISSUE )TBE_ISSUE                                                                                                                                    \n");
        	sqlstr.append("     ,SUM(P_TBE_APPROVAL ) TBE_APPROVAL                                                                                                                             \n");
        	sqlstr.append("     ,SUM(P_PO_ISSUE ) PO_ISSUE                                                                                                                                     \n");
        	sqlstr.append("     ,SUM(P_PIM ) PIM                                                                                                                                               \n");
        	sqlstr.append("     ,SUM(P_FAT ) FAT                                                                                                                                               \n");
        	sqlstr.append("     ,SUM(P_SHIPPING ) SHIPPING                                                                                                                                     \n");
        	sqlstr.append("     ,SUM(P_ONSITE ) ONSITE                                                                                                                                         \n");
        	sqlstr.append("     ,MAX(B.MR_ISSUE ) MR_ISSUE_YN                                                                                                                                  \n");
        	sqlstr.append("     ,MAX(B.MR_APPROVAL ) MR_APPROVAL_YN                                                                                                                            \n");
        	sqlstr.append("     ,MAX(B.QUO ) QUO_YN                                                                                                                                            \n");
        	sqlstr.append("     ,MAX(B.TBE_ISSUE ) TBE_ISSUE_YN                                                                                                                                \n");
        	sqlstr.append("     ,MAX(B.TBE_APPROVAL ) TBE_APPROVAL_YN                                                                                                                          \n");
        	sqlstr.append("     ,MAX(B.PIM ) PIM_YN                                                                                                                                            \n");
        	sqlstr.append(" FROM (                                                                                                                                                             \n");
        	sqlstr.append("      SELECT A.DEPT_CD, A.DIS_CD, A.DIS_NM,  A.PSR_PFA                                                                                                              \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.MR_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_ISSUE                                                                \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.MR_APPROVAL,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_APPROVAL                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.RFQ_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_RFQ_ISSUE                                                              \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.QUO,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_QUO                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.TBE_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_ISSUE                                                              \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.TBE_APPROVAL,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_APPROVAL                                                        \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.PO_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PO_ISSUE                                                                \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.PIM,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PIM                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.FAT,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_FAT                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.SHIPPING,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_SHIPPING                                                                \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.ONSITE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"'THEN 1 ELSE 0 END P_ONSITE                                                                     \n");
        	sqlstr.append("        FROM PSR_MASTER2018 A                                                                                                                                       \n");
        	sqlstr.append("       WHERE A.PSR_PFA = 'P'                                                                                                                                        \n");
        	sqlstr.append("         AND A.DEPT_CD = '"+params.get("dept_cd")+"'                                                                                                                                     \n");
        	sqlstr.append("         AND A.MR_NO IS NOT NULL                                                                                                                                    \n");
        	sqlstr.append("       UNION ALL                                                                                                                                                    \n");
        	sqlstr.append("      SELECT A.DEPT_CD, MAX(A.DIS_CD), MAX(A.DIS_NM),  MAX(A.PSR_PFA)                                                                                               \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.MR_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_ISSUE                                                           \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.MR_APPROVAL),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_APPROVAL                                                     \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.RFQ_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_RFQ_ISSUE                                                         \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.QUO),'yyyy-mm-dd') <= '"+params.get("CutDt")+"'THEN 1 ELSE 0 END P_QUO                                                                      \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.TBE_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_ISSUE                                                         \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.TBE_APPROVAL),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_APPROVAL                                                   \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.PO_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PO_ISSUE                                                           \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.PIM),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PIM                                                                     \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.FAT),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_FAT                                                                     \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.SHIPPING),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_SHIPPING                                                           \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.ONSITE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_ONSITE                                                               \n");
        	sqlstr.append("        FROM PSR_MASTER2018 A                                                                                                                                       \n");
        	sqlstr.append("       WHERE A.PSR_PFA = 'A'                                                                                                                                        \n");
        	sqlstr.append("         AND A.DEPT_CD = '"+params.get("dept_cd")+"'                                                                                                                                     \n");
        	sqlstr.append("         AND A.MR_NO IS NOT NULL                                                                                                                                    \n");
        	sqlstr.append("       GROUP BY DEPT_CD, MR_NO                                                                                                                                      \n");
        	sqlstr.append("       ) A                                                                                                                                                          \n");
        	sqlstr.append("       , PSR_DEFINE2018 B                                                                                                                                           \n");
        	sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                                                                                     \n");
        	sqlstr.append(" GROUP BY  A.DIS_CD, A.DIS_NM , PSR_PFA                                                                                                                                       \n");
        	sqlstr.append(" UNION ALL                                                                                                                                                          \n");
        	sqlstr.append("SELECT  'B' ORD,'', 'Procurement Total' , COUNT(DIS_CD) CNT,  PSR_PFA                                                                                                  \n");
        	sqlstr.append("     ,SUM(P_MR_ISSUE) MR_ISSUE                                                                                                                                      \n");
        	sqlstr.append("     ,SUM(P_MR_APPROVAL) MR_APPROVAL                                                                                                                                \n");
        	sqlstr.append("     ,SUM(P_RFQ_ISSUE ) RFQ_ISSUE                                                                                                                                   \n");
        	sqlstr.append("     ,SUM(P_QUO ) QUO                                                                                                                                               \n");
        	sqlstr.append("     ,SUM(P_TBE_ISSUE )TBE_ISSUE                                                                                                                                    \n");
        	sqlstr.append("     ,SUM(P_TBE_APPROVAL ) TBE_APPROVAL                                                                                                                             \n");
        	sqlstr.append("     ,SUM(P_PO_ISSUE ) PO_ISSUE                                                                                                                                     \n");
        	sqlstr.append("     ,SUM(P_PIM ) PIM                                                                                                                                               \n");
        	sqlstr.append("     ,SUM(P_FAT ) FAT                                                                                                                                               \n");
        	sqlstr.append("     ,SUM(P_SHIPPING ) SHIPPING                                                                                                                                     \n");
        	sqlstr.append("     ,SUM(P_ONSITE ) ONSITE                                                                                                                                         \n");
        	sqlstr.append("     ,MAX(B.MR_ISSUE ) MR_ISSUE_YN                                                                                                                                  \n");
        	sqlstr.append("     ,MAX(B.MR_APPROVAL ) MR_APPROVAL_YN                                                                                                                            \n");
        	sqlstr.append("     ,MAX(B.QUO ) QUO_YN                                                                                                                                            \n");
        	sqlstr.append("     ,MAX(B.TBE_ISSUE ) TBE_ISSUE_YN                                                                                                                                \n");
        	sqlstr.append("     ,MAX(B.TBE_APPROVAL ) TBE_APPROVAL_YN                                                                                                                          \n");
        	sqlstr.append("     ,MAX(B.PIM ) PIM_YN                                                                                                                                            \n");
        	sqlstr.append(" FROM (                                                                                                                                                             \n");
        	sqlstr.append("      SELECT A.DEPT_CD, A.DIS_CD, A.DIS_NM,  A.PSR_PFA                                                                                                              \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.MR_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_ISSUE                                                                \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.MR_APPROVAL,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_APPROVAL                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.RFQ_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_RFQ_ISSUE                                                              \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.QUO,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_QUO                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.TBE_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_ISSUE                                                              \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.TBE_APPROVAL,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_APPROVAL                                                        \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.PO_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PO_ISSUE                                                                \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.PIM,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PIM                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.FAT,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_FAT                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.SHIPPING,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_SHIPPING                                                                \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.ONSITE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"'THEN 1 ELSE 0 END P_ONSITE                                                                     \n");
        	sqlstr.append("        FROM PSR_MASTER2018 A                                                                                                                                       \n");
        	sqlstr.append("       WHERE A.PSR_PFA = 'P'                                                                                                                                        \n");
        	sqlstr.append("         AND A.DEPT_CD = '"+params.get("dept_cd")+"'                                                                                                                                     \n");
        	sqlstr.append("         AND A.MR_NO IS NOT NULL                                                                                                                                    \n");
        	sqlstr.append("       UNION ALL                                                                                                                                                    \n");
        	sqlstr.append("      SELECT A.DEPT_CD, MAX(A.DIS_CD), MAX(A.DIS_NM),  MAX(A.PSR_PFA)                                                                                               \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.MR_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_ISSUE                                                           \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.MR_APPROVAL),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_APPROVAL                                                     \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.RFQ_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_RFQ_ISSUE                                                         \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.QUO),'yyyy-mm-dd') <= '"+params.get("CutDt")+"'THEN 1 ELSE 0 END P_QUO                                                                      \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.TBE_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_ISSUE                                                         \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.TBE_APPROVAL),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_APPROVAL                                                   \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.PO_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PO_ISSUE                                                           \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.PIM),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PIM                                                                     \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.FAT),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_FAT                                                                     \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.SHIPPING),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_SHIPPING                                                           \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.ONSITE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_ONSITE                                                               \n");
        	sqlstr.append("        FROM PSR_MASTER2018 A                                                                                                                                       \n");
        	sqlstr.append("       WHERE A.PSR_PFA = 'A'                                                                                                                                        \n");
        	sqlstr.append("         AND A.DEPT_CD = '"+params.get("dept_cd")+"'                                                                                                                                     \n");
        	sqlstr.append("         AND A.MR_NO IS NOT NULL                                                                                                                                    \n");
        	sqlstr.append("       GROUP BY DEPT_CD, MR_NO                                                                                                                                      \n");
        	sqlstr.append("       ) A                                                                                                                                                          \n");
        	sqlstr.append("       , PSR_DEFINE2018 B                                                                                                                                           \n");
        	sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                                                                                     \n");
        	sqlstr.append(" GROUP BY   PSR_PFA                                                                                                                                                 \n");
        	sqlstr.append("  UNION ALL                                                                                                                                                                                         \n");
        	sqlstr.append("SELECT 'C', '', 'Procurement Total' , COUNT(DIS_CD)/2 CNT,  '진행율(%)'                                                                                                                                          \n");
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_MR_ISSUE, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_MR_ISSUE, 0))/SUM(DECODE(PSR_PFA, 'P', P_MR_ISSUE, 0))*100,2)) MR_ISSUE                               \n");                                                                             
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_MR_APPROVAL, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_MR_APPROVAL, 0))/SUM(DECODE(PSR_PFA, 'P', P_MR_APPROVAL, 0))*100,2)) MR_APPROVAL                   \n");                                                                                   
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_RFQ_ISSUE, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_RFQ_ISSUE, 0))/SUM(DECODE(PSR_PFA, 'P', P_RFQ_ISSUE, 0))*100,2)) RFQ_ISSUE                           \n");                                                                              
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_QUO, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_QUO, 0))/SUM(DECODE(PSR_PFA, 'P', P_QUO, 0))*100,2)) QUO                                                   \n");                                                                  
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_TBE_ISSUE, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_TBE_ISSUE, 0))/SUM(DECODE(PSR_PFA, 'P', P_TBE_ISSUE, 0))*100,2)) TBE_ISSUE                           \n");                                                                               
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_TBE_APPROVAL, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_TBE_APPROVAL, 0))/SUM(DECODE(PSR_PFA, 'P', P_TBE_APPROVAL, 0))*100,2)) TBE_APPROVAL               \n");                                                                                    
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_PO_ISSUE, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_PO_ISSUE, 0))/SUM(DECODE(PSR_PFA, 'P', P_PO_ISSUE, 0))*100,2)) PO_ISSUE                               \n");                                                                            
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_PIM, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_PIM, 0))/SUM(DECODE(PSR_PFA, 'P', P_PIM, 0))*100,2))  PIM                                                  \n");                                                                   
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_FAT, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_FAT, 0))/SUM(DECODE(PSR_PFA, 'P', P_FAT, 0))*100,2)) FAT                                                   \n");                                                                   
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_SHIPPING, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_SHIPPING, 0))/SUM(DECODE(PSR_PFA, 'P', P_SHIPPING, 0))*100,2))  SHIPPING                              \n");                                                                             
        	sqlstr.append("     ,DECODE(SUM(DECODE(PSR_PFA, 'P', P_ONSITE, 0)), 0, 0, ROUND(SUM(DECODE(PSR_PFA, 'A', P_ONSITE, 0))/SUM(DECODE(PSR_PFA, 'P', P_ONSITE, 0))*100,2))  ONSITE                                      \n");                                                                         
        	sqlstr.append("     ,MAX(B.MR_ISSUE ) MR_ISSUE_YN                                                                                                                                                                  \n");
        	sqlstr.append("     ,MAX(B.MR_APPROVAL ) MR_APPROVAL_YN                                                                                                                                                            \n");
        	sqlstr.append("     ,MAX(B.QUO ) QUO_YN                                                                                                                                                                            \n");
        	sqlstr.append("     ,MAX(B.TBE_ISSUE ) TBE_ISSUE_YN                                                                                                                                                                \n");
        	sqlstr.append("     ,MAX(B.TBE_APPROVAL ) TBE_APPROVAL_YN                                                                                                                                                          \n");
        	sqlstr.append("     ,MAX(B.PIM ) PIM_YN                                                                                                                                                                            \n");
        	sqlstr.append(" FROM (                                                                                                                                                                                             \n");
        	sqlstr.append("      SELECT A.DEPT_CD, A.DIS_CD, A.DIS_NM,  A.PSR_PFA                                                                                                                                              \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.MR_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_ISSUE                                                                                                \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.MR_APPROVAL,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_APPROVAL                                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.RFQ_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_RFQ_ISSUE                                                                                              \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.QUO,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_QUO                                                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.TBE_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_ISSUE                                                                                              \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.TBE_APPROVAL,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_APPROVAL                                                                                        \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.PO_ISSUE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PO_ISSUE                                                                                                \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.PIM,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PIM                                                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.FAT,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_FAT                                                                                                          \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.SHIPPING,'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_SHIPPING                                                                                                \n");
        	sqlstr.append("            ,CASE WHEN to_char(A.ONSITE,'yyyy-mm-dd') <= '"+params.get("CutDt")+"'THEN 1 ELSE 0 END P_ONSITE                                                                                                     \n");
        	sqlstr.append("        FROM PSR_MASTER2018 A                                                                                                                                                                       \n");
        	sqlstr.append("       WHERE A.PSR_PFA = 'P'                                                                                                                                                                        \n");
        	sqlstr.append("         AND A.DEPT_CD = '"+params.get("dept_cd")+"'                                                                                                                                                                     \n");
        	sqlstr.append("         AND A.MR_NO IS NOT NULL                                                                                                                                                                    \n");
        	sqlstr.append("       UNION ALL                                                                                                                                                                                    \n");
        	sqlstr.append("      SELECT A.DEPT_CD, MAX(A.DIS_CD), MAX(A.DIS_NM),  MAX(A.PSR_PFA)                                                                                                                               \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.MR_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_ISSUE                                                                                           \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.MR_APPROVAL),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_MR_APPROVAL                                                                                     \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.RFQ_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_RFQ_ISSUE                                                                                         \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.QUO),'yyyy-mm-dd') <= '"+params.get("CutDt")+"'THEN 1 ELSE 0 END P_QUO                                                                                                      \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.TBE_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_ISSUE                                                                                         \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.TBE_APPROVAL),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_TBE_APPROVAL                                                                                   \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.PO_ISSUE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PO_ISSUE                                                                                           \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.PIM),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_PIM                                                                                                     \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.FAT),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_FAT                                                                                                     \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.SHIPPING),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_SHIPPING                                                                                           \n");
        	sqlstr.append("            ,CASE WHEN to_char(min(A.ONSITE),'yyyy-mm-dd') <= '"+params.get("CutDt")+"' THEN 1 ELSE 0 END P_ONSITE                                                                                               \n");
        	sqlstr.append("        FROM PSR_MASTER2018 A                                                                                                                                                                       \n");
        	sqlstr.append("       WHERE A.PSR_PFA = 'A'                                                                                                                                                                        \n");
        	sqlstr.append("         AND A.DEPT_CD = '"+params.get("dept_cd")+"'                                                                                                                                                                     \n");
        	sqlstr.append("         AND A.MR_NO IS NOT NULL                                                                                                                                                                    \n");
        	sqlstr.append("       GROUP BY DEPT_CD, MR_NO                                                                                                                                                                      \n");
        	sqlstr.append("       ) A                                                                                                                                                                                          \n");
        	sqlstr.append("       , PSR_DEFINE2018 B                                                                                                                                                                           \n");
        	sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD(+)      )                                                                                                                                                              \n");
        	sqlstr.append(" ORDER BY ORD, DIS_CD, DIS_NM, PSR_PFA DESC                                                                                                                                                                 \n");
        	 
        	search.setStatement(sqlstr.toString());
			System.out.println(sqlstr.toString()); 
			rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            WAFLogger.error("### WAFSQLException : " + se.getMessage());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
}



