/**
 * @(#)file    PSRListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.09.03
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
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 oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
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.util.ValueObject;
import kr.co.udapsoft.common.util.PCMSUtil;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.util.DateUtil;
import com.udapsoft.waf.common.util.Formatter2;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class PSRListBiz extends Biz {

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

	public void doBiz() throws Exception {
		if (storage.compareEvent("")) {
			RowSet rowSet = getRowSet("PSR");
			storage.addRowSet("ROWSET", rowSet);
			rowSet = getRowSet("EXE");
			storage.addRowSet("ROWSET1", rowSet);
	        storage.setSuccess(true);
		}
		else if(storage.compareEvent("GET_PSR_LIST")){
			params.set("sheet1", getPSRList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("SAVE_PSR")){
            String[] status = super.gets("SHEET1.status");
            boolean success = true;
			try {
				super.conn.begin();
				Connection connection = super.conn.getConnection();
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusInsert(status[i])) {
						success = insertPSRSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = updatePSRSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						success = deletePSRSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
				}	
				CalDiscipline(connection,params);
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	            super.storage.setSuccess(false);
			} finally {
				super.conn.close();
			}
		}else if(storage.compareEvent("GET_PROC")){
			try {
				super.conn.begin();
				psrUpdate(super.conn.getConnection(), params);
			}catch (Exception e) {
				WAFLogger.error(e);
				super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	            super.storage.setSuccess(false);
			} finally {
				super.conn.close();
			}
			
		}else if(storage.compareEvent("SEND_MAIL")){
			
		}
	} 
	
	public boolean insertPSRSheet(Connection connection,ValueObject sqlParams) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
		StringBuffer sql = new StringBuffer();

		try {	
			sql.append("INSERT INTO PSR_MASTER2018              \n");
			sql.append("(                              		    \n");
			sql.append("    PSR_ID                              \n");
            sql.append("    ,DEPT_CD                            \n");
            sql.append("    ,PSR_KIND                           \n");
            sql.append("    ,PSR_PFA                            \n");
            sql.append("    ,DIS_CD                             \n");
            sql.append("    ,DIS_NM                             \n");
            sql.append("    ,MR_NO                              \n");
            sql.append("    ,TITLE                              \n");
            sql.append("    ,QTY                                \n");
            sql.append("    ,SHIPPING_MODE                      \n");
            sql.append("    ,ONOFF                              \n");
            sql.append("    ,MR_ISSUE                           \n");
            sql.append("    ,MR_APPROVAL                        \n");
            sql.append("    ,RFQ_ISSUE                          \n");
            sql.append("    ,QUO                                \n");
            sql.append("    ,TBE_ISSUE                          \n");
            sql.append("    ,TBE_APPROVAL                       \n");
            sql.append("    ,CBE                                \n");
            sql.append("    ,PO_ISSUE                           \n");
            sql.append("    ,VP_IN                              \n");
            sql.append("    ,VP_OUT                             \n");
            sql.append("    ,FINAL_VENDOR                       \n");
            sql.append("    ,MAJOR_RAW                          \n");
            sql.append("    ,RECEIPT_RAW                        \n");
            sql.append("    ,PIM                                \n");
            sql.append("    ,FAT                                \n");
            sql.append("    ,FAB_TIME                           \n");
            sql.append("    ,SHIPPING                           \n");
            sql.append("    ,LANDING                            \n");
            sql.append("    ,ONSITE                             \n");
            sql.append("    ,REMARKS                            \n");
            sql.append("    ,CORP_ID                            \n");
            sql.append("    ,PUR_CONT_ID                        \n");
            sql.append("    ,ITEM_NO                            \n");
            sql.append("    ,CRE_BY                             \n");
            sql.append("    ,CRE_DATE                           \n");
            sql.append("	) VALUES (                     		\n");
			sql.append("     psr_master_s.nextval               \n"); //PSR_ID       
            sql.append("    ,?                                  \n"); //DEPT_CD      
            sql.append("    ,?                                  \n"); //PSR_KIND     
            sql.append("    ,?                                  \n"); //PSR_PFA      
            sql.append("    ,?                                  \n"); //DIS_CD       
            sql.append("    ,?                                  \n"); //DIS_NM       
            sql.append("    ,?                                  \n"); //MR_NO        
            sql.append("    ,?                                  \n"); //TITLE        
            sql.append("    ,?                                  \n"); //QTY          
            sql.append("    ,?                                  \n"); //SHIPPING_MODE
            sql.append("    ,?                                  \n"); //ONOFF        
            sql.append("    ,?                                  \n"); //MR_ISSUE     
            sql.append("    ,?                                  \n"); //MR_APPROVAL  
            sql.append("    ,?                                  \n"); //RFQ_ISSUE    
            sql.append("    ,?                                  \n"); //QUO          
            sql.append("    ,?                                  \n"); //TBE_ISSUE    
            sql.append("    ,?                                  \n"); //TBE_APPROVAL 
            sql.append("    ,?                                  \n"); //CBE          
            sql.append("    ,?                                  \n"); //PO_ISSUE     
            sql.append("    ,?                                  \n"); //VP_IN        
            sql.append("    ,?                                  \n"); //VP_OUT       
            sql.append("    ,?                                  \n"); //FINAL_VENDOR 
            sql.append("    ,?                                  \n"); //MAJOR_RAW    
            sql.append("    ,?                                  \n"); //RECEIPT_RAW  
            sql.append("    ,?                                  \n"); //PIM          
            sql.append("    ,?                                  \n"); //FAT          
            sql.append("    ,?                                  \n"); //FAB_TIME     
            sql.append("    ,?                                  \n"); //SHIPPING     
            sql.append("    ,?                                  \n"); //LANDING      
            sql.append("    ,?                                  \n"); //ONSITE       
            sql.append("    ,?                                  \n"); //REMARKS      
            sql.append("    ,?                                  \n"); //CORP_ID      
            sql.append("    ,?                                  \n"); //PUR_CONT_ID  
            sql.append("    ,?                                  \n"); //ITEM_NO  
            sql.append("    ,?                                  \n"); //CRE_BY       
            sql.append("    ,sysdate )                          \n"); //CRE_DATE    

			persistent.setStatement(sql.toString());
			
			persistent.addParameter(params.get("DEPT_CD"));
			persistent.addParameter(sqlParams.get("PSR_KIND"));
			persistent.addParameter(sqlParams.get("PSR_PFA"));
			persistent.addParameter(sqlParams.get("DIS_CD"));
			persistent.addParameter(sqlParams.get("DIS_NM"));
			persistent.addParameter(sqlParams.get("MR_NO"));
			persistent.addParameter(sqlParams.get("TITLE"));
			persistent.addParameter(sqlParams.get("QTY"));
			persistent.addParameter(sqlParams.get("SHIPPING_MODE"));
			persistent.addParameter(sqlParams.get("ONOFF"));
			persistent.addParameter(sqlParams.get("MR_ISSUE"));
			persistent.addParameter(sqlParams.get("MR_APPROVAL"));
			persistent.addParameter(sqlParams.get("RFQ_ISSUE"));
			persistent.addParameter(sqlParams.get("QUO"));
			persistent.addParameter(sqlParams.get("TBE_ISSUE"));
			persistent.addParameter(sqlParams.get("TBE_APPROVAL"));
			persistent.addParameter(sqlParams.get("CBE"));
			persistent.addParameter(sqlParams.get("PO_ISSUE"));
			persistent.addParameter(sqlParams.get("VP_IN"));
			persistent.addParameter(sqlParams.get("VP_OUT"));
			persistent.addParameter(sqlParams.get("FINAL_VENDOR"));
			persistent.addParameter(sqlParams.get("MAJOR_RAW"));
			persistent.addParameter(sqlParams.get("RECEIPT_RAW"));
			persistent.addParameter(sqlParams.get("PIM"));
			persistent.addParameter(sqlParams.get("FAT"));
			persistent.addParameter(sqlParams.get("FAB_TIME"));
			persistent.addParameter(sqlParams.get("SHIPPING"));
			persistent.addParameter(sqlParams.get("LANDING"));
			persistent.addParameter(sqlParams.get("ONSITE"));
			persistent.addParameter(sqlParams.get("REMARKS"));
			persistent.addParameter(sqlParams.get("CORP_ID"));
			persistent.addParameter(sqlParams.get("PUR_CONT_ID"));
			persistent.addParameter(sqlParams.get("ITEM_NO"));
			persistent.addParameter(storage.getUser().get("ACCT_ID"));  
			persistent.execute();
	
			System.out.println(sql.toString());
			System.out.println(persistent.getParameters());
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} 
	}
	public boolean updatePSRSheet(Connection connection, ValueObject sqlParams) throws Exception {
		Persistent persistent = new PersistentImpl(connection); 
		StringBuffer sql = new StringBuffer(); 

		try {
			    sql.append("UPDATE PSR_MASTER2018  		                 \n");
			    sql.append("   SET PSR_KIND =?                           \n");
	            sql.append("      ,PSR_PFA = ?                           \n");
	            sql.append("      ,DIS_CD = ?                            \n");
	            sql.append("      ,DIS_NM = ?                            \n");
	            sql.append("      ,MR_NO  = ?                            \n");
	            sql.append("      ,TITLE  = ?                            \n");
	            sql.append("      ,QTY    = ?                            \n");
	            sql.append("      ,SHIPPING_MODE = ?                     \n");
	            sql.append("      ,ONOFF         = ?                     \n");
	            sql.append("      ,MR_ISSUE      = ?                     \n");
	            sql.append("      ,MR_APPROVAL   = ?                     \n");
	            sql.append("      ,RFQ_ISSUE     = ?                     \n");
	            sql.append("      ,QUO           = ?                     \n");
	            sql.append("      ,TBE_ISSUE     = ?                     \n");
	            sql.append("      ,TBE_APPROVAL  = ?                     \n");
	            sql.append("      ,CBE           = ?                     \n");
	            sql.append("      ,PO_ISSUE      = ?                     \n");
	            sql.append("      ,VP_IN         = ?                     \n");
	            sql.append("      ,VP_OUT        = ?                     \n");
	            sql.append("      ,FINAL_VENDOR  = ?                     \n");
	            sql.append("      ,MAJOR_RAW     = ?                     \n");
	            sql.append("      ,RECEIPT_RAW   = ?                     \n");
	            sql.append("      ,PIM           = ?                     \n");
	            sql.append("      ,FAT           = ?                     \n");
	            sql.append("      ,FAB_TIME      = ?                     \n");
	            sql.append("      ,SHIPPING      = ?                     \n");
	            sql.append("      ,LANDING       = ?                     \n");
	            sql.append("      ,ONSITE        = ?                     \n");
	            sql.append("      ,REMARKS       = ?                     \n");
	            sql.append("      ,CORP_ID       = ?                     \n");
	            sql.append("      ,PUR_CONT_ID   = ?                     \n");
	            sql.append("      ,ITEM_NO       = ?                     \n");
	            sql.append("      ,UPD_BY        = ?                     \n");
	            sql.append("      ,UPD_DATE      = SYSDATE               \n");
	        	sql.append("WHERE  PSR_ID        = ?                     \n");
	            sql.append("  AND  DEPT_CD       = ?                     \n");
	            
			persistent.setStatement(sql.toString());   
		
			persistent.addParameter(sqlParams.get("PSR_KIND"));
			persistent.addParameter(sqlParams.get("PSR_PFA"));
			persistent.addParameter(sqlParams.get("DIS_CD"));
			persistent.addParameter(sqlParams.get("DIS_NM"));
			persistent.addParameter(sqlParams.get("MR_NO"));
			persistent.addParameter(sqlParams.get("TITLE"));
			persistent.addParameter(sqlParams.get("QTY"));
			persistent.addParameter(sqlParams.get("SHIPPING_MODE"));
			persistent.addParameter(sqlParams.get("ONOFF"));
			persistent.addParameter(sqlParams.get("MR_ISSUE"));
			persistent.addParameter(sqlParams.get("MR_APPROVAL"));
			persistent.addParameter(sqlParams.get("RFQ_ISSUE"));
			persistent.addParameter(sqlParams.get("QUO"));
			persistent.addParameter(sqlParams.get("TBE_ISSUE"));
			persistent.addParameter(sqlParams.get("TBE_APPROVAL"));
			persistent.addParameter(sqlParams.get("CBE"));
			persistent.addParameter(sqlParams.get("PO_ISSUE"));
			persistent.addParameter(sqlParams.get("VP_IN"));
			persistent.addParameter(sqlParams.get("VP_OUT"));
			persistent.addParameter(sqlParams.get("FINAL_VENDOR"));
			persistent.addParameter(sqlParams.get("MAJOR_RAW"));
			persistent.addParameter(sqlParams.get("RECEIPT_RAW"));
			persistent.addParameter(sqlParams.get("PIM"));
			persistent.addParameter(sqlParams.get("FAT"));
			persistent.addParameter(sqlParams.get("FAB_TIME"));
			persistent.addParameter(sqlParams.get("SHIPPING"));
			persistent.addParameter(sqlParams.get("LANDING"));
			persistent.addParameter(sqlParams.get("ONSITE"));
			persistent.addParameter(sqlParams.get("REMARKS"));
			persistent.addParameter(sqlParams.get("CORP_ID"));
			persistent.addParameter(sqlParams.get("PUR_CONT_ID"));
			persistent.addParameter(sqlParams.get("ITEM_NO"));
			persistent.addParameter(storage.getUser().get("ACCT_ID"));
			persistent.addParameter(sqlParams.get("PSR_ID"));
			persistent.addParameter(sqlParams.get("DEPT_CD"));
            persistent.execute(); 
			
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		}  
	}
	public boolean deletePSRSheet(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		try {
			sql.append("   DELETE                      		\n");
			sql.append("	 FROM PSR_MASTER2018   		    \n");
			sql.append("	WHERE PSR_ID = ?          		\n");
			sql.append("	  AND DEPT_CD = ?     		    \n"); 
        
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("PSR_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("DEPT_CD")); 
			
			pstmt.executeUpdate();
				
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	public boolean CalDiscipline(Connection connection, ValueObject sqlParams) throws Exception {
		Persistent persistent = new PersistentImpl(connection); 
		StringBuffer sql = new StringBuffer(); 

		try {
			sql.append("MERGE INTO PSR_MASTER2018 A                         \n");      
	        sql.append("USING (SELECT DEPT_CD, MR_NO, DIS_CD, DIS_NM        \n");        
	        sql.append("              FROM PSR_MASTER2018                   \n");       
	        sql.append("           WHERE DEPT_CD = ?                        \n");      
	        sql.append("              AND PSR_PFA = 'P'    ) B              \n");      
	        sql.append(" ON (A.DEPT_CD = B.DEPT_CD                          \n");       
	        sql.append("    AND A.MR_NO = B.MR_NO                           \n");      
	        sql.append("    AND A.DEPT_CD = ?                               \n");  
	        sql.append("    AND A.PSR_PFA <> 'P')                           \n");
	        sql.append("WHEN MATCHED THEN UPDATE                            \n");  
	        sql.append("SET A.DIS_CD = B.DIS_CD                             \n"); 
	        sql.append("     ,A.DIS_NM = B.DIS_NM                           \n");
	        sql.append("     ,A.UPD_BY = ?                                  \n");
	        sql.append("     ,A.UPD_DATE = SYSDATE                          \n"); 
			persistent.setStatement(sql.toString());   
		
			persistent.addParameter(sqlParams.get("DEPT_CD"));
			persistent.addParameter(sqlParams.get("DEPT_CD"));
			persistent.addParameter(storage.getUser().get("ACCT_ID"));
			persistent.execute(); 
			
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		}  
	}
	
	/**
	 * 검수대상 List(전체)
	 * @param Params
	 * @return
	 */
	private RowSet getPSRList(ValueObject params) {

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

        try{
        	sqlstr.append("SELECT  A.PSR_ID, A.SEQ, A.PSR_KIND, A.PSR_PFA,  A.DIS_CD DIS_FACE, A.DIS_CD, A.DIS_NM, A.MR_NO                        \n");
        	sqlstr.append("        ,A.TITLE, A.CORP_NM, A.QTY, A.SHIPPING_MODE, A.ONOFF, A.MR_ISSUE, A.MR_APPROVAL, A.RFQ_ISSUE                   \n");
        	sqlstr.append("        ,A.QUO, A.TBE_ISSUE, A.TBE_APPROVAL, A.CBE, A.PO_ISSUE                                                         \n");
        	sqlstr.append("        ,A.VP_IN, A.VP_OUT, A.FINAL_VENDOR                                                                             \n");
        	sqlstr.append("        ,A.MAJOR_RAW, A.RECEIPT_RAW, A.PIM, A.FAT                                                                      \n");
        	sqlstr.append("        ,A.FAB_TIME                                                                                                    \n");
        	sqlstr.append("        ,A.SHIPPING, A.LANDING, A.ONSITE, A.REMARKS                                                                    \n");
        	sqlstr.append("        ,A.CORP_ID,  A.DEPT_CD ,A.PUR_CONT_ID                                                                          \n");               
        	sqlstr.append("        ,A.MR_NO MR_NO1, A.ITEM_NO                                                                                     \n");               
        	sqlstr.append("  FROM (                                                                                                               \n");
        	sqlstr.append("       SELECT A.PSR_ID, 1 SEQ, A.PSR_KIND, A.PSR_PFA,  A.DIS_CD DIS_FACE, A.DIS_CD, A.DIS_NM, A.MR_NO                  \n");
        	sqlstr.append("            , A.TITLE, '' CORP_NM, A.QTY, A.SHIPPING_MODE, A.ONOFF, A.MR_ISSUE, A.MR_APPROVAL, A.RFQ_ISSUE             \n");
        	sqlstr.append("            , A.QUO, A.TBE_ISSUE, A.TBE_APPROVAL, A.CBE, A.PO_ISSUE                                                    \n");
        	sqlstr.append("            , A.VP_IN, A.VP_OUT, A.FINAL_VENDOR                                                                        \n");
        	sqlstr.append("            , A.MAJOR_RAW, A.RECEIPT_RAW, A.PIM, A.FAT                                                                 \n");
        	sqlstr.append("            , ROUND(DECODE(NVL(B.FAB_TIME, 'D'),'D', A.FAT - A.PO_ISSUE, months_between(A.FAT,A.PO_ISSUE)),1) || DECODE(NVL(B.FAB_TIME, 'D'),'D','D', 'M' ) FAB_TIME \n");
        	sqlstr.append("            , A.SHIPPING, A.LANDING, A.ONSITE, A.REMARKS                                                               \n");
        	sqlstr.append("            , A.CORP_ID,  A.DEPT_CD ,A.PUR_CONT_ID ,A.ITEM_NO                                                          \n");
        	sqlstr.append("         FROM PSR_MASTER2018 A                                                                                         \n");
        	sqlstr.append("             ,PSR_DEFINE2018 B                                                                                         \n");
        	sqlstr.append("        WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                                 \n");
        	sqlstr.append("          AND A.DEPT_CD = ?                                                                                            \n");
        	sqlstr.append("          AND A.PSR_PFA = 'P'                                                                                          \n");
        	sqlstr.append("        UNION ALL                                                                                                      \n");
        	sqlstr.append("       SELECT A.PSR_ID, 2 SEQ, A.PSR_KIND, A.PSR_PFA,  A.DIS_CD DIS_FACE,A.DIS_CD, A.DIS_NM, A.MR_NO                   \n");
        	sqlstr.append("            , A.TITLE, '', A.QTY, A.SHIPPING_MODE, A.ONOFF, A.MR_ISSUE, A.MR_APPROVAL, A.RFQ_ISSUE                     \n");
        	sqlstr.append("            , A.QUO, A.TBE_ISSUE, A.TBE_APPROVAL, A.CBE, A.PO_ISSUE                                                    \n");
        	sqlstr.append("            , A.VP_IN, A.VP_OUT, A.FINAL_VENDOR                                                                        \n");
        	sqlstr.append("            , A.MAJOR_RAW, A.RECEIPT_RAW, A.PIM, A.FAT                                                                 \n");
        	sqlstr.append("            , ROUND(DECODE(NVL(B.FAB_TIME, 'D'),'D', A.FAT - A.PO_ISSUE, months_between(A.FAT,A.PO_ISSUE)),1) || DECODE(NVL(B.FAB_TIME, 'D'),'D','D', 'M' ) FAB_TIME \n");
        	sqlstr.append("            , A.SHIPPING, A.LANDING, A.ONSITE, A.REMARKS                                                               \n");
        	sqlstr.append("            , A.CORP_ID,  A.DEPT_CD ,A.PUR_CONT_ID,A.ITEM_NO                                                           \n");
        	sqlstr.append("         FROM PSR_MASTER2018 A                                                                                         \n");
        	sqlstr.append("             ,PSR_DEFINE2018 B                                                                                         \n");
        	sqlstr.append("        WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                                 \n");
        	sqlstr.append("          AND A.DEPT_CD = ?                                                                                            \n");
        	sqlstr.append("          AND A.PSR_PFA = 'F'                                                                                          \n");
        	sqlstr.append("        UNION ALL                                                                                                      \n");
            sqlstr.append("       SELECT A.PSR_ID, 3 SEQ, A.PSR_KIND,A.PSR_PFA,  A.DIS_CD DIS_FACE,A.DIS_CD, A.DIS_NM, A.MR_NO                    \n");
            sqlstr.append("            , A.TITLE, B.CORP_KOR  CORP_NM, A.QTY, A.SHIPPING_MODE, A.ONOFF, A.MR_ISSUE, A.MR_APPROVAL, A.RFQ_ISSUE    \n");
            sqlstr.append("            , A.QUO, A.TBE_ISSUE, A.TBE_APPROVAL, A.CBE, A.PO_ISSUE                                                    \n");
            sqlstr.append("            , A.VP_IN, A.VP_OUT, A.FINAL_VENDOR                                                                        \n");
            sqlstr.append("            , A.MAJOR_RAW, A.RECEIPT_RAW, A.PIM, A.FAT                                                                 \n");
            sqlstr.append("            , ROUND(DECODE(NVL(D.FAB_TIME, 'D'),'D', A.FAT - A.PO_ISSUE, months_between(A.FAT,A.PO_ISSUE)),1) || DECODE(NVL(D.FAB_TIME, 'D'),'D', 'D','M' ) FAB_TIME \n");
        	sqlstr.append("            , A.SHIPPING, A.LANDING, A.ONSITE, A.REMARKS                                                               \n");
        	sqlstr.append("            , A.CORP_ID,  A.DEPT_CD ,A.PUR_CONT_ID,A.ITEM_NO                                                           \n");
            sqlstr.append("         FROM PSR_MASTER2018 A                                                                                         \n");
            sqlstr.append("             ,SUPP_INFO B                                                                                              \n");                                      
            sqlstr.append("             ,PUR1000T C                                                                                               \n");                                      
            sqlstr.append("             ,PSR_DEFINE2018 D                                                                                         \n");
        	sqlstr.append("        WHERE A.CORP_ID = B.CORP_ID(+)                                                                                 \n");
            sqlstr.append("          AND A.MR_NO = C.REQ_DOC_NO(+)                                                                                \n");
            sqlstr.append("          AND (A.CORP_ID = C.CORP_ID OR NVL(A.CORP_ID,0) = 0 OR C.CORP_ID IS NULL)                                     \n");   
            sqlstr.append("          AND (A.PUR_CONT_ID = C.PUR_CONT_ID OR NVL(A.PUR_CONT_ID,0) = 0)                                              \n");   
            sqlstr.append("          AND A.DEPT_CD = D.DEPT_CD(+)                                                                                 \n");                                
            sqlstr.append("          AND A.DEPT_CD = ?                                                                                            \n");                                
            sqlstr.append("          AND A.PSR_PFA = 'A'                                                                                          \n");
        	sqlstr.append("     ) A                                                                                                               \n");
        	sqlstr.append("WHERE A.DEPT_CD = ?                                                                                                    \n");
        	
    		search.addParameter(params.get("dept_cd"));
    		search.addParameter(params.get("dept_cd"));
    		search.addParameter(params.get("dept_cd"));
    		search.addParameter(params.get("dept_cd")); 
    		
            if(!"".equals(params.get("p_discipline"))) {
        		sqlstr.append("		AND   A.DIS_NM = ?                                                                                                                                                                             		\n");
        		search.addParameter(params.get("p_discipline"));
        	}
        	
        	if(!"".equals(params.get("p_mr_no"))) {
        		sqlstr.append("		AND   A.MR_NO = UPPER(?)                                                                                                                                                                             		\n");
        		search.addParameter(params.get("p_mr_no"));
        		
        	}
        	
        	sqlstr.append(" ORDER BY A.DIS_CD,A.DIS_NM, A.MR_NO, A.SEQ , A.PUR_CONT_ID, A.CORP_NM                           \n");
        	
        	search.setStatement(sqlstr.toString());
			WAFLogger.debug(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;
	}

	public boolean psrUpdate(Connection conn, ValueObject sqlParams) throws Exception {
		CallableStatement cs 	= null;
		String pssql			= "";
		
		try {
        	pssql = "{call SP_PSRLIST_CAL(?,?,?,?,?)}";	
        	cs = conn.prepareCall(pssql);
        	cs.setString(1, sqlParams.get("dept_cd"));
            cs.setInt(2, storage.getUser().getInt("ACCT_ID"));
            cs.setString(3, sqlParams.get("proc_gb")); 
            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
            cs.registerOutParameter(5, java.sql.Types.VARCHAR);
            cs.execute();
            
            if (!cs.getString(4).equals("S")){
            	storage.setMessage(cs.getString(5));
				throw new Exception(cs.getString(5)); 
            }
            return true;
        }
        catch(Exception e) {
        	storage.setDetailMessage(e);
        	this.storage.setMessage(e.getMessage());
        	throw e;
        }
	}
	
	private RowSet getRowSet(String gb) {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr = new StringBuffer();
        try {
        	sqlstr.append("   SELECT GB, PAYITEM, ESTDESC                                              \n");
        	sqlstr.append("    FROM (                                                                  \n");
        	sqlstr.append("          SELECT 'EXE' GB                                                   \n");
        	sqlstr.append("                ,PAYITEM                                                    \n");
        	sqlstr.append("                ,'(' || PAYITEM || ')' || ESTDESC ESTDESC                   \n");
        	/**
        	 * 2018.11.23
        	 * 이인오
        	 * 안쓰는소스 경영정보 디비링크 삭제
        	 */
        	sqlstr.append("            FROM COP020T!CCSIF                                              \n");
        	sqlstr.append("           WHERE DEPT_CD = '" + storage.getUser().get("DEPT_CD") + "'       \n");
        	sqlstr.append("             AND COSTTYPE = 'A'                                             \n");
        	sqlstr.append("         	 AND PAYITEM LIKE 'P%'                                         \n");
        	sqlstr.append("	          UNION ALL                                                        \n");
        	sqlstr.append("          SELECT 'PSR'                                                      \n");
        	sqlstr.append("                ,DIS_CD                                                     \n");
        	sqlstr.append("                ,DIS_NM                                                     \n");
        	sqlstr.append("            FROM PSR_MASTER2018                                             \n");
        	sqlstr.append("           WHERE DEPT_CD = '" + storage.getUser().get("DEPT_CD") + "'       \n");
        	sqlstr.append("           GROUP BY DIS_CD, DIS_NM                                          \n");
        	sqlstr.append("   )                                                                        \n");
        	sqlstr.append("   WHERE GB = '"+ gb + "'                                                   \n");
        	sqlstr.append("   ORDER BY GB, PAYITEM                                                     \n");
            System.out.println(sqlstr.toString());
        	search.setStatement(sqlstr);
            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;
        }
    }
	
}


