/**
 * @(#)file    UserListBiz.java
 * @(#)author  Joung Kyu Park
 * @(#)version 1.0
 * @(#)date    May 31, 2007
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.hwenc.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of Hanwha E&C, Inc.
 */
package kr.co.udapsoft.ebid.buyer.supplier.outside.biz;



import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import m2soft.ers.invoker.InvokerException;
import m2soft.ers.invoker.http.ReportingServerInvoker;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
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.sender.MessageSend;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import kr.co.udapsoft.ebid.buyer.common.file.biz.FileListWorkerDelBiz;
import kr.co.udapsoft.ebid.buyer.common.file.biz.FileListWorkerSaveHtmlBiz;

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


public class OutSupplierApprListFileBiz extends Biz {

	public OutSupplierApprListFileBiz(HandlerStorage storage) {
		super(storage);
	}
	
	public void doBiz() throws Exception {
		boolean success = false;
		if (storage.getUser().get("right_name").equals("ADMIN NEW")) {
			success = getListAdmin(params);	 //2018.06.28 한번에 생성 
		}else{
			success = getList(params);	 
		}
	    storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		storage.setSuccess(success);	     
	}
	
    /**
     * 목록을 검색하여 리턴
     * @param Params
     * @return
     */
    private boolean getList(ValueObject Params) throws Exception {
    	String[] status  		= super.gets("sheet1.STATUS");
		String[] corpId  		= super.gets("sheet1.CORP_ID");
		
		try {
			if( status != null ) {
				for( int i = 0 ; i < status.length ; i++ ) {
					if( "U".equals(status[i]) ) {
						RowSet ApprRowSet = getApprRowSet(corpId[i]);
						if (ApprRowSet.size() > 0)
				        {
							createPdf(corpId[i],ApprRowSet.getRow(0).get("APPR_DY").substring(0,4), ApprRowSet.getRow(0).get("ARR_ID"), ApprRowSet.getRow(0).get("CORP_KOR"));
			            }
					}				
				}//end for
			}
		}
        catch (Exception se) {
        	WAFLogger.error("se====>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            se.printStackTrace();
            throw new Exception(se);
        }         
        return true;
    }

    private boolean getListAdmin(ValueObject Params) throws Exception {
    	
		try {
			RowSet ApprRowSet = getApprRowSet("%");
			for( int i = 0 ; i < ApprRowSet.size() ; i++ ) {
				createPdf(ApprRowSet.getRow(i).get("CORP_ID"),ApprRowSet.getRow(i).get("APPR_DY").substring(0,4), ApprRowSet.getRow(i).get("ARR_ID"), ApprRowSet.getRow(i).get("CORP_KOR"));
		    }
		}
        catch (Exception se) {
        	WAFLogger.error("se====>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            se.printStackTrace();
            throw new Exception(se);
        }         
        return true;
    }
    
	private RowSet getApprRowSet(String corp_id) {
	
	    Search search = new SearchImpl();
	    RowSet rowSet = null;
	    StringBuffer sqlStr = new StringBuffer();
	    try {
	
	    	  sqlStr.append(" SELECT A.CORP_ID, A.CORP_KOR, B.ARR_ID, B.APPR_DY, A.APPR_DT           \n")                                                                              
		            .append("   FROM SUPP_INFO A                                                     \n")                                                                              
		            .append("       ,SUPP_APPR B                                                     \n")                                                                              
		            .append("  WHERE A.CORP_ID = B.CORP_ID                                           \n")                                                                              
		            .append("    AND A.JOB_DIV = 'O'                                                 \n")                                                                              
		            .append("    AND A.STATUS = '03' --등록상태                                                                               \n")                                                                              
		            .append("    AND B.DOC_CD IN ( 13,14)  --정기평가  공사,인허가                                               \n")                                                                              
		            .append("    AND B.APPR_DY LIKE to_char(sysdate, 'yyyy') || '%'                  \n");
	    	        if (!corp_id.equals("%")) {
	    	        	sqlStr.append("    AND A.CORP_ID = " + corp_id + "                           \n");
	    	        }
	    	        sqlStr.append("  ORDER BY A.CORP_ID, B.APPR_DY                                   \n");
		                       
	        search.setStatement(sqlStr.toString());	  
	        rowSet = search.execute();
	    }
	    catch(Exception e) {
	
	        WAFLogger.error(this.getClass().getName() + " --> getFileListRowSet() : " + e.getMessage());
	        WAFLogger.error("SQL : " + search.getStatement());
	        WAFLogger.error(e);
	    }
	    finally {
	        if( rowSet == null ) rowSet = new RowSetImpl();
	        return rowSet;
	    }
	}
    
    public void createPdf(String corp_id, String appr_dy, String arr_id, String corp_name) throws Exception
	{
	    ReportingServerInvoker invoker = new ReportingServerInvoker(WAFConfig.get("waf.rd_report.searver"));
	      invoker.setCharacterEncoding("euc-kr"); //캐릭터셋
	      invoker.setReconnectionCount(3);        //재접속 시도 회수
	      invoker.setConnectTimeout(10000);           //커넥션 타임아웃
	      invoker.setReadTimeout(30000);             //송수신 타임아웃
	      
	      String sever_root = WAFConfig.get("waf.ServerURL");

	      invoker.addParameter("opcode", "500");
	      
	      invoker.addParameter("mrd_path", sever_root + "/report/mrd/buyer/supplier/outside/OutSuppRegisterReport.mrd");
	      invoker.addParameter("mrd_param", "/rp ["+corp_id+"] ["+appr_dy+"] ["+arr_id+"] ");  // CORP_ID YYYY arr_id
	      invoker.addParameter("export_type", "pdf");
	      invoker.addParameter("export_name","");
	      invoker.addParameter("protocol", "file");
	      try
	      {
	    	  FileListWorkerSaveHtmlBiz fileSave = new FileListWorkerSaveHtmlBiz(storage);
	          FileListWorkerDelBiz delFile = new FileListWorkerDelBiz(storage);
	          
              // 방법 2 : 파일 객체 전달
	           String path = WAFConfig.get("waf.repository.path") + "/";
	           String yyyymmdd = (new java.text.SimpleDateFormat ("yyyyMMdd")).format (new java.util.Date());
	           String yyyymm = (new java.text.SimpleDateFormat ("yyyyMM")).format (new java.util.Date());
		          
		        String fileName = "";
		        String saveFileName = "";
		        fileName = "협력업체 등록증_" + appr_dy  ;
	            saveFileName ="corp_reg";
	            
	            RowSet fileRowSet = getFileListRowSet("TERMS_AGREE_INFO_"+corp_id, "180",appr_dy );
	            
	            for (int i=0; i<fileRowSet.size(); i++)
		        {
		           	String file_map_id[] =  {fileRowSet.getRow(i).get("FILE_MAP_ID")};
	           	   	delFile.delFiles(file_map_id);
		        }
		        
		        File file = new File(path + "supp/" + corp_id );
		        if(!file.exists()){
		           //없다면 생성
		           file.mkdirs(); 
		        }
		        invoker.invoke(new File(path + "supp/" + corp_id + "/" + saveFileName + "_" + yyyymmdd + "_" + corp_id + ".pdf"));
		        boolean success = fileSave.addFilesProc(new File(path + "supp/" + corp_id + "/" + saveFileName + "_" + yyyymmdd + "_" + corp_id + ".pdf"), fileName + "_" + corp_id + ".pdf", "", "TERMS_AGREE_INFO_"+corp_id, "180", "");
	      }
	      catch(InvokerException e)
	      {
	    	  WAFLogger.error("=================>" + e);
	    	  e.printStackTrace();
	    	  invoker.disconnect();
	      }
	      invoker.disconnect();
	}
    
    private RowSet getFileListRowSet(String fileGB, String ID, String yyyy) {

        Search search = new SearchImpl();
        RowSet rowSet = null;
        String sqlstr = "";
        try {

            sqlstr = " SELECT   B.FILE_ID,          \n"
                   + "          B.FILE_NAME,        \n"
                   + "          B.REAL_FILE_NAME,   \n"
                   + "          TRIM(               \n"
                   + "          CASE                \n"
                   + "          WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte' \n"
                   + "          ELSE \n"
                   + "               CASE "
                   + "               WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte' \n"
                   + "               ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte' \n"
                   + "               END                                                \n"
                   + "          END ) AS FILE_SIZE,                                     \n"
                   + "          B.CRE_DATE AS CRE_DATE,                                 \n"
                   + "          A.FILE_MAP_ID, A.FILE_GB, A.ID, A.OPEN_CORP_YN,         \n"
                   + "          A.O_FILE_GB, O_ID, USE_CON_YN, TECHREQ_OPEN_YN,         \n"
                   + "          A.CRE_BY, A.UPD_BY, B.FILE_EXT,B.CONTENT_TYPE,	        \n"
                   + "          B.FILE_PATH   \n"
                   + " FROM     PUB0510T A , \n"
                   + "          PUB0500T B \n"
                   + " WHERE    A.FILE_ID = B.FILE_ID \n"
                   + " AND      A.FILE_GB = ? \n"
                   + " AND      A.ID      = NVL(?,A.ID)  \n"
                   + " AND      B.FILE_NAME LIKE ? \n" ;          
                       
            search.setStatement(sqlstr);
            search.addParameter(fileGB);
            search.addParameter(ID);
            search.addParameter("협력업체 등록증_" + yyyy + "%");
            rowSet = search.execute();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileListRowSet() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
        }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
}